Ìwé Directory
MySQL databaseakojọpọ parapo olona-tabili lilo? osi osi darapo lilo ṣiṣe
Miṣa lilo ti asopọ
Ninu awọn ipin ti tẹlẹ, a ti kọ bi a ṣe le ka data lati tabili kan, eyiti o rọrun pupọ, ṣugbọn ni awọn ohun elo gidi o jẹ pataki nigbagbogbo lati ka data lati awọn tabili data lọpọlọpọ.
Ninu ori yii a yoo fihan ọ bi o ṣe le lo MySQL's JOIN lati beere data ni awọn tabili meji tabi diẹ sii.
O le lo Mysql's JOIN ni Yan, imudojuiwọn ati PA awọn alaye lati darapọ mọ awọn ibeere tabili pupọ.
JOIN ti pin ni aijọju si awọn ẹka mẹta wọnyi gẹgẹbi iṣẹ rẹ:
- DARA ARA INU (Idapọ inu, tabi equijoin): Gba awọn igbasilẹ pẹlu awọn aaye ti o baamu ni awọn tabili meji.
- DỌ̀RỌ̀ OSI (darapọ̀ osi):Gba gbogbo awọn igbasilẹ ni tabili osi, paapaa ti ko ba si awọn igbasilẹ ti o baamu ni tabili ọtun.
- Darapọ mọ ẹtọ (darapọ ọtun): Ni idakeji si Isopọ Osi, a lo lati gba gbogbo awọn igbasilẹ ni tabili ọtun, paapaa ti ko ba si awọn igbasilẹ ti o baamu ni tabili osi.
Lilo INU INER ni Command Prompt
A ni meji tabili tcount_tbl ati chenweiliang_tbl ni chenweiliang database.Awọn data ti awọn tabili data meji jẹ bi atẹle:
Apeere
Gbiyanju awọn apẹẹrẹ wọnyi:
igbeyewo apẹẹrẹ data
mysql> use chenweiliang; Database changed mysql> SELECT * FROM tcount_tbl; +---------------+--------------+ | chenweiliang_author | chenweiliang_count | +---------------+--------------+ | 陈沩亮博客 | 10 | | chenweiliang.com | 20 | | Google | 22 | +---------------+--------------+ 3 rows in set (0.01 sec) mysql> SELECT * from chenweiliang_tbl; +-----------+---------------+---------------+-----------------+ | chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date | +-----------+---------------+---------------+-----------------+ | 1 | 学习 PHP | 陈沩亮博客 | 2017-04-12 | | 2 | 学习 MySQL | 陈沩亮博客 | 2017-04-12 | | 3 | 学习 Java | chenweiliang.com | 2015-05-01 | | 4 | 学习 Python | chenweiliang.com | 2016-03-06 | | 5 | 学习 C | FK | 2017-04-05 | +-----------+---------------+---------------+-----------------+ 5 rows in set (0.01 sec)
Nigbamii a yoo lo MySQLINU INU (o tun le fi INER silẹ ki o lo JOIN, ipa naa jẹ kanna)Lati so awọn tabili meji ti o wa loke lati ka iye aaye chenweiliang_count ti o baamu si gbogbo awọn aaye chenweiliang_author ninu tabili chenweiliang_tbl ni tabili tcount_tbl:
akojọpọ da
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a INNER JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.00 sec)
Alaye SQL ti o wa loke jẹ deede si:
NIBI gbolohun ọrọ
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a, tcount_tbl b WHERE a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.01 sec)
OSI MySQL Darapọ mọ
Apapọ osi MySQL yatọ si idapọ. MySQL LEFT JOIN yoo ka gbogbo data ti o wa ninu tabili data osi, paapaa ti tabili ọtun ko ni data ti o baamu.
Apeere
Gbiyanju awọn apẹẹrẹ wọnyi si chenweiliang_tbl fun tabili osi,tcount_tbl Fun tabili ọtun, loye ohun elo ti MySQL LEFT JOIN:
OSI DARA
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a LEFT JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | | 5 | FK | NULL | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
Ni awọn loke apẹẹrẹ, Osi JOIN ti lo, ki o si yi gbólóhùn yoo ka gbogbo awọn ti a ti yan aaye data ti awọn data tabili chenweiliang_tbl lori osi, paapa ti o ba nibẹ ni ko si bamu aaye iye ti chenweiliang_author ninu tabili tcount_tbl ọtun.
MySQL ọtun Darapọ mọ
Asopọmọra ọtun MySQL yoo ka gbogbo data ti o wa ninu tabili data ni apa ọtun, paapaa ti ko ba si data ti o baamu ninu tabili ni apa osi.
Apeere
Gbiyanju awọn apẹẹrẹ wọnyi si chenweiliang_tbl fun tabili osi,tcount_tbl Fun tabili ọtun, loye ohun elo ti MySQL RIGHT JOIN:
Darapọ mọ ẹtọ
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a RIGHT JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | | NULL | NULL | 22 | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
Ọtun JOIN ti lo ninu awọn loke apẹẹrẹ, yi gbólóhùn yoo ka gbogbo awọn ti a ti yan aaye data ti awọn data tabili tcount_tbl lori ọtun, paapa ti o ba nibẹ ni ko si bamu aaye iye ti chenweiliang_author ninu tabili chenweiliang_tbl osi.
Lilo JOIN ni PHP akosile
Iṣẹ mysqli_query() ni a lo ni PHP lati mu awọn alaye SQL ṣiṣẹ, o le lo alaye SQL kanna gẹgẹbi oke bi paramita ti iṣẹ mysqli_query ().
Gbiyanju apẹẹrẹ wọnyi:
Ibere MySQL NIPA idanwo:
<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
$sql = 'SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a INNER JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author';
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 MySQL JOIN 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>作者</td><td>登陆次数</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
echo "<tr><td> {$row['chenweiliang_id']}</td> ".
"<td>{$row['chenweiliang_author']} </td> ".
"<td>{$row['chenweiliang_count']} </td> ".
"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>Ireti Chen Weiliang Blog ( https://www.chenweiliang.com/ ) pín "Lilo tabili pupọ ti MySQL database akojọpọ inu? Osi Darapọ mọ Lilo ṣiṣe”, yoo ran ọ lọwọ.
Kaabo lati pin ọna asopọ ti nkan yii:https://www.chenweiliang.com/cwl-488.html