Tsab ntawv teev npe
MySQL databasesab hauv koom nrog ntau lub rooj siv? sab laug sab laug koom siv efficiency
Mysql siv kev sib txuas
Hauv tshooj dhau los, peb tau kawm yuav ua li cas nyeem cov ntaub ntawv los ntawm ib lub rooj, uas yog qhov yooj yim, tab sis hauv kev siv tiag tiag nws yog qhov yuav tsum tau nyeem cov ntaub ntawv los ntawm ntau lub rooj.
Hauv tshooj no peb yuav qhia koj yuav ua li cas siv MySQL's JOIN los nug cov ntaub ntawv hauv ob lossis ntau lub rooj.
Koj tuaj yeem siv Mysql's JOIN hauv SELECT, UPDATE thiab DELETE cov lus los koom nrog ntau cov lus nug.
JOIN yog kwv yees muab faib ua peb pawg hauv qab no raws li nws txoj haujlwm:
- INNER JOIN (sab hauv koom, lossis equijoin): Tau cov ntaub ntawv nrog cov teb sib txuam hauv ob lub rooj.
- LEFT JOIN (sab laug koom):Tau txais tag nrho cov ntaub ntawv nyob rau sab laug lub rooj, txawm tias tsis muaj cov ntaub ntawv sib txuam hauv lub rooj sab xis.
- RIGHT JOIN (txoj cai koom): Contrary to LEFT JOIN, nws yog siv kom tau txais tag nrho cov ntaub ntawv nyob rau hauv lub rooj sab xis, txawm tias tsis muaj cov ntaub ntawv sib raug zoo nyob rau hauv lub rooj sab laug.
Siv INNER JOIN hauv Command Prompt
Peb muaj ob lub rooj tcount_tbl thiab chenweiliang_tbl hauv chenweiliang database.Cov ntaub ntawv ntawm ob lub rooj ntaub ntawv yog raws li nram no:
piv txwv
Sim ua piv txwv hauv qab no:
test cov ntaub ntawv
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)
Tom ntej no peb yuav siv MySQLINNER JOIN (koj tuaj yeem tshem tawm sab hauv thiab siv JOIN, cov txiaj ntsig zoo ib yam)Txhawm rau txuas ob lub rooj saum toj no los nyeem tus nqi ntawm chenweiliang_count teb sib raug rau tag nrho chenweiliang_author teb hauv chenweiliang_tbl lub rooj hauv tcount_tbl lub rooj:
NYOB RAU HAUV
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)
Cov nqe lus SQL saum toj no yog sib npaug rau:
THAUM clause
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)
MySQL LEFT JOIN
MySQL sab laug koom yog txawv ntawm koom. MySQL LEFT JOIN yuav nyeem tag nrho cov ntaub ntawv hauv cov ntaub ntawv sab laug, txawm tias lub rooj sab xis tsis muaj cov ntaub ntawv sib raug.
piv txwv
Sim ua piv txwv hauv qab no rau chenweiliang_tbl rau sab laug rooj,tcount_tbl Rau lub rooj zoo, nkag siab txog daim ntawv thov ntawm MySQL LEFT JOIN:
LEFT JOIN
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)
Hauv qhov piv txwv saum toj no, LEFT JOIN yog siv cov nqe lus no yuav nyeem tag nrho cov ntaub ntawv xaiv teb ntawm cov ntaub ntawv cov ntaub ntawv chenweiliang_tbl ntawm sab laug, txawm tias tsis muaj nqi teb ntawm chenweiliang_author hauv lub rooj tcount_tbl ntawm sab xis.
MySQL RIGHT JOIN
MySQL RIGHT JOIN yuav nyeem tag nrho cov ntaub ntawv hauv cov ntaub ntawv cov ntaub ntawv ntawm sab xis, txawm tias tsis muaj cov ntaub ntawv sib thooj hauv cov lus ntawm sab laug.
piv txwv
Sim ua piv txwv hauv qab no rau chenweiliang_tbl rau sab laug rooj,tcount_tbl Rau lub rooj zoo, nkag siab txog daim ntawv thov ntawm MySQL RIGHT JOIN:
TXOJ HAUJ LWM
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)
RIGHT JOIN yog siv nyob rau hauv cov piv txwv saum toj no, nqe lus no yuav nyeem tag nrho cov xaiv teb cov ntaub ntawv ntawm cov ntaub ntawv rooj tcount_tbl ntawm sab xis, txawm tias tsis muaj nqi teb nqi ntawm chenweiliang_author nyob rau hauv lub rooj chenweiliang_tbl ntawm sab laug.
Siv JOIN hauv PHP tsab ntawv
Lub mysqli_query() muaj nuj nqi yog siv hauv PHP los ua SQL nqe lus, koj tuaj yeem siv tib nqe lus SQL raws li saum toj no raws li qhov parameter rau mysqli_query() muaj nuj nqi.
Sim ua piv txwv hauv qab no:
MySQL ORDER BY test:
<?
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);
?>Cia siab Chen Weiliang Blog ( https://www.chenweiliang.com/ ) qhia "Multi- rooj pab ntawm MySQL database puab koom? sab laug sab laug Koom Nrog Kev Siv Ua Haujlwm", nws yuav pab koj.
Zoo siab txais tos los qhia qhov txuas ntawm kab lus no:https://www.chenweiliang.com/cwl-488.html
Zoo siab txais tos rau Telegram channel ntawm Chen Weiliang blog kom tau txais qhov hloov tshiab tshiab!
📚 Daim ntawv qhia no muaj nuj nqis loj, 🌟Qhov no yog lub sijhawm tsis tshua muaj, tsis txhob nco nws! ⏰⌛💨
Share thiab like yog tias koj nyiam!
Koj qhov kev sib koom thiab kev nyiam yog peb qhov kev txhawb nqa tas mus li!