Artikel Direktori
Kepiye carane nggunakake inner join karo pirang-pirang tabel ing database MySQL ? Pira efisiensi left join?
MySQL nggunakake sambungan
Ing bab sadurunge, kita wis sinau carane maca data saka siji tabel, kang relatif prasaja, nanging ing aplikasi nyata asring perlu kanggo maca data saka macem-macem tabel data.
Ing bab iki kita bakal nuduhake sampeyan carane nggunakake MySQL JOIN kanggo query data ing loro utawa luwih tabel.
Sampeyan bisa nggunakake Mysql's JOIN ing SELECT, UPDATE lan DELETE statements kanggo nggabungake pitakon multi-tabel.
JOIN kira-kira dipérang dadi telung kategori miturut fungsine:
- INNER JOIN (gabungan batin, utawa equijoin): Entuk cathetan kanthi kolom sing cocog ing rong tabel.
- KIRI GABUNG (KIRI GABUNG):Entuk kabeh cathetan ing meja kiwa, sanajan ora ana cathetan sing cocog ing tabel sisih tengen.
- TEngen gabung (tengen gabung): Nalisir LEFT JOIN, digunakake kanggo njaluk kabeh cathetan ing meja tengen, sanajan ora ana cathetan cocog cocog ing meja kiwa.
Nggunakake INNER JOIN ing Command Prompt
Kita duwe rong tabel tcount_tbl lan chenweiliang_tbl ing database chenweiliang.Data saking kalih tabel data inggih menika:
Kadadeyan
Coba conto ing ngisor iki:
data conto tes
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)
Sabanjure, kita bakal nggunakake INNER JOIN MySQL (sampeyan bisa ngilangi INNER lan nggunakake JOIN, efek kasebut padha) kanggo nggabungake rong tabel ing ndhuwur lan maca nilai kolom chenweiliang_author ing tabel chenweiliang_tbl lan nilai kolom chenweiliang_count sing cocog ing tabel 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)
Pernyataan SQL ing ndhuwur padha karo:
Klausa WHERE
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 KIRIM GABUNGAN
Gabung kiwa MySQL beda karo gabung. MySQL LEFT JOIN bakal maca kabeh data ing tabel data kiwa, sanajan tabel tengen ora ana data sing cocog.
Kadadeyan
Coba conto ing ngisor iki, nganggo chenweiliang_tbl minangka tabel kiwa lan tcount_tbl minangka tabel tengen, kanggo mangerteni aplikasi MySQL LEFT JOIN:
KIRA NGGABUNGA
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)
Ing conto ing ndhuwur, LEFT JOIN digunakake, lan statement iki bakal maca kabeh data lapangan sing dipilih saka tabel data chenweiliang_tbl ing sisih kiwa, sanajan ora ana nilai lapangan sing cocog karo chenweiliang_author ing tcount_tbl tabel ing sisih tengen.
MySQL RIGHT JOIN
MySQL RIGHT JOIN bakal maca kabeh data ing tabel data ing sisih tengen, sanajan ora ana data sing cocog ing tabel ing sisih kiwa.
Kadadeyan
Coba conto ing ngisor iki, nganggo chenweiliang_tbl minangka tabel kiwa lan tcount_tbl minangka tabel tengen, kanggo mangerteni aplikasi MySQL RIGHT JOIN:
TEngen gabung
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 digunakake ing conto ing ndhuwur, statement iki bakal maca kabeh data lapangan sing dipilih saka tcount_tbl tabel data tengen, sanajan ora ana Nilai lapangan chenweiliang_author cocog ing meja kiwa chenweiliang_tbl.
Nggunakake JOIN ing skrip PHP
Fungsi mysqli_query () digunakake ing PHP kanggo nglakokaké statement SQL, sampeyan bisa nggunakake statement SQL padha ing ndhuwur minangka parameter saka mysqli_query () fungsi.
Coba conto ing ngisor iki:
MySQL ORDER BY tes:
<?
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);
?>Muga-muga, artikel "Nggunakake MySQL Database inner join ing pirang-pirang tabel? Efisiensi left join" sing dienggo bareng ing blog Chen Weiliang ( https://www.chenweiliang.com/ ) bisa migunani kanggo sampeyan.
Monggo dipunbagi pranala artikel punika: https://www.chenweiliang.com/cwl-488.html