Anyè Atik
baz done MySQLenteryè rantre nan itilizasyon milti-tab? gòch gòch rantre nan efikasite itilizasyon
Mysql itilizasyon koneksyon
Nan chapit anvan yo, nou te aprann ki jan yo li done ki sòti nan yon tab, ki se relativman senp, men nan aplikasyon reyèl li se souvan nesesè yo li done ki soti nan tab done miltip.
Nan chapit sa a nou pral montre w kouman pou w itilize JOIN MySQL la pou w chèche done nan de oswa plis tab.
Ou ka sèvi ak JOIN Mysql nan deklarasyon SELECT, UPDATE ak DELETE pou rantre nan demann plizyè tab.
JOIN se apeprè divize an twa kategori sa yo dapre fonksyon li:
- INNER JOIN (inner join, oswa equijoin): Jwenn dosye ak jaden matche nan de tab.
- LEFT JOIN (gòch rantre):Jwenn tout dosye ki nan tablo gòch la, menm si pa gen okenn dosye matche nan tablo dwat la.
- RIGHT JOIN (adwat rantre): Kontrèman ak LEFT JOIN, li itilize pou jwenn tout dosye ki nan tablo dwat la, menm si pa gen okenn dosye ki koresponn nan tablo gòch la.
Sèvi ak INNER JOIN nan èd kòmand
Nou gen de tab tcount_tbl ak chenweiliang_tbl nan baz done chenweiliang.Done de tab done yo jan sa a:
Enstans
Eseye egzanp sa yo:
done egzanp tès yo
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)
Apre sa, nou pral sèvi ak MySQLINNER JOIN (ou ka tou omite INNER epi itilize JOIN, efè a se menm)Pou konekte de tablo ki anwo yo pou li valè jaden chenweiliang_count ki koresponn ak tout jaden chenweiliang_author nan tablo chenweiliang_tbl nan tab tcount_tbl:
INNER JOIN
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)
Deklarasyon SQL ki pi wo a ekivalan a:
KOTE kloz
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 kite rantre nan
MySQL left join diferan de join. MySQL LEFT JOIN pral li tout done ki nan tablo done gòch la, menm si tab dwat la pa gen okenn done korespondan.
Enstans
Eseye egzanp sa yo chenweiliang_tbl pou tab gòch la,tcount_tbl Pou tab dwat la, konprann aplikasyon MySQL LEFT JOIN:
GÒCH 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)
Nan egzanp ki anwo a, yo itilize LEFT JOIN, epi deklarasyon sa a pral li tout done jaden yo chwazi nan tab done chenweiliang_tbl sou bò gòch la, menm si pa gen okenn valè jaden ki koresponn nan chenweiliang_author nan tablo tcount_tbl sou bò dwat la.
MySQL DWA JOIN
MySQL RIGHT JOIN pral li tout done ki nan tablo done sou bò dwat la, menm si pa gen okenn done korespondan nan tablo ki sou bò gòch la.
Enstans
Eseye egzanp sa yo chenweiliang_tbl pou tab gòch la,tcount_tbl Pou tab dwat la, konprann aplikasyon MySQL RIGHT JOIN:
DWA JOIN
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 yo itilize nan egzanp ki anwo a, deklarasyon sa a pral li tout done jaden yo chwazi nan tab la dwa done tcount_tbl, menm si pa gen okenn valè jaden korespondan chenweiliang_author nan tablo gòch la chenweiliang_tbl.
Sèvi ak JOIN nan script PHP
Fonksyon mysqli_query() itilize nan PHP pou egzekite deklarasyon SQL, ou ka itilize menm deklarasyon SQL kòm pi wo a kòm yon paramèt nan fonksyon mysqli_query().
Eseye egzanp sa a:
MySQL ORDER BY tès:
<?
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);
?>Hope Chen Weiliang Blog ( https://www.chenweiliang.com/ ) pataje "Itilizasyon milti-tab nan baz done MySQL anndan rantre? kite Left Join Itilizasyon Efikasite", li pral ede w.
Byenvini pataje lyen atik sa a:https://www.chenweiliang.com/cwl-488.html