Artikel Direktori
database MySQLgabung ing panggunaan multi-meja? kiwa kiwa gabung efisiensi panggunaan
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 MySQLINNER JOIN (sampeyan uga bisa ngilangi INNER lan nggunakake JOIN, efeke padha)Kanggo nyambungake rong tabel ing ndhuwur kanggo maca nilai kolom chenweiliang_count sing cocog karo kabeh kolom chenweiliang_author ing tabel chenweiliang_tbl 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 kanggo chenweiliang_tbl kanggo meja kiwa,tcount_tbl Kanggo tabel tengen, ngerti 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 kanggo chenweiliang_tbl kanggo meja kiwa,tcount_tbl Kanggo tabel tengen, ngerti 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);
?>Blog Hope Chen Weiliang ( https://www.chenweiliang.com/ ) bareng "Multi-table usage of MySQL database inner join? ngiwa Efisiensi Panggunaan Gabung Ngiwa", bakal mbantu sampeyan.
Sugeng rawuh kanggo nuduhake link artikel iki:https://www.chenweiliang.com/cwl-488.html
Sugeng rawuh ing saluran Telegram blog Chen Weiliang kanggo entuk update paling anyar!
📚 Pandhuan iki ngemot nilai gedhe, 🌟Iki minangka kesempatan langka, aja kantun! ⏰⌛💨
Share lan seneng yen sampeyan seneng!
Nuduhake lan seneng sampeyan minangka motivasi terus-terusan!