Akwụkwọ ndekọ ederede
MySQL nchekwa dataKedu otu esi eji njikọ ime ime nwere ọtụtụ tebụl? aka ekpe aka ekpe sonyere arụmọrụ ojiji
Mysql Ojiji nke njikọ
N'isiakwụkwọ ndị gara aga, anyị amụtala ka esi agụ data site na otu tebụl, nke dị mfe, ma na ngwa ngwa ọ na-adịkarị mkpa ịgụ data sitere na tebụl data dị iche iche.
N'isiakwụkwọ a, anyị ga-ewebata gị otu esi eji MySQL's JOIN iji jụọ data na tebụl abụọ ma ọ bụ karịa.
Ị nwere ike iji Mysql's JOIN na Họrọ, Mmelite na Hichapụ nkwupụta iji sonyere ọtụtụ ajụjụ tebụl.
E kewara JOIN n'ụzọ zuru oke n'ụdị atọ ndị a dịka ọrụ ya siri dị:
- Jikọọ INNER (isonye n'ime, ma ọ bụ nsonye dakọtara): Nweta ndekọ nke mmekọrịta dabara na mpaghara na tebụl abụọ.
- Jikọọ aka ekpe (njikọ aka ekpe):Nweta ndekọ niile na tebụl aka ekpe, ọbụlagodi na enweghị ndekọ kwekọrọ na tebụl aka nri.
- Jikọọ aka nri (njikọ aka nri): N'adịghị ka njikọ aka ekpe, a na-eji ya nweta ndekọ niile na tebụl aka nri, ọbụlagodi na enweghị ndekọ kwekọrọ na tebụl aka ekpe.
Iji INNER JOIN na ngwa ngwa
Anyị nwere tebụl abụọ tcount_tbl na chenweiliang_tbl na nchekwa data chenweiliang. Data dị na tebụl data abụọ bụ ndị a:
ihe atụ
Gbalịa ihe atụ ndị a:
Nwalee data atụ
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)
Ọzọ anyị ga-eji MySQLJINỌỌ INU (Inwekwara ike ịhapụ INNER wee jiri JOIN, mmetụta ya bụ otu)Iji jikọọ tebụl abụọ a dị n'elu ka ị gụọ ụkpụrụ ubi chenweiliang_count kwekọrọ na mpaghara chenweiliang_author niile dị na tebụl chenweiliang_tbl na tebụl tcount_tbl:
AKWỤKWỌ NDỊ ỌZỌ
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)
Okwu SQL dị n'elu bụ nke:
Ebe okwu
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)
Jikọọ aka ekpe MySQL
Njikọ aka ekpe MySQL dị iche na nsonye. MySQL LEFT JOIN ga-agụ data niile dị na tebụl data aka ekpe, ọbụlagodi na enweghị data kwekọrọ na tebụl aka nri.
ihe atụ
Gbalịa mee ihe atụ na-esonụ chenweiliang_tbl bụ tebụl n'aka ekpe,tcount_tbl Maka tebụl dị n'aka nri, ghọta ngwa MySQL LEFT JOIN:
Jikọọ aka ekpe
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)
A na-eji njikọ aka ekpe mee ihe atụ a dị n'elu, nkwupụta a ga-agụta data ubi niile ahọpụtara na tebụl data chenweiliang_tbl n'aka ekpe, ọbụlagodi na enweghị uru ubi chenweiliang_author kwekọrọ na tebụl tcount_tbl n'aka nri.
Jikọọ aka nri MySQL
MySQL RIGHT JOIN ga-agụ data niile dị na tebụl data ziri ezi, ọbụlagodi na enweghị data kwekọrọ na tebụl aka ekpe.
ihe atụ
Gbalịa mee ihe atụ na-esonụ chenweiliang_tbl bụ tebụl n'aka ekpe,tcount_tbl Maka tebụl ziri ezi, ghọta ngwa MySQL RIGHT JOIN:
Jikọọ aka nri
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)
A na-eji njikọ aka nri na ihe atụ dị n'elu. Okwu a ga-agụ data ubi niile ahọpụtara na tebụl data ziri ezi tcount_tbl, ọbụlagodi na enweghị uru ubi chenweiliang_author kwekọrọ na tebụl ekpe chenweiliang_tbl.
Iji JOIN na ederede PHP
A na-eji ọrụ mysqli_query() na PHP iji mezuo nkwupụta SQL. Ị nwere ike iji otu nkwupụta SQL dị n'elu dị ka parameter nke mysqli_query() ọrụ.
Gbalịa ihe atụ a:
MySQL ORDER site n'ule:
<? 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/ ) kesara "Otu esi eji n'ime sonyere multiple tebụl na MySQL nchekwa data?" "Orụmọrụ nke iji njikọ aka ekpe" ga-enyere gị aka.
Nnọọ ka ị kesaa njikọ nke akụkọ a:https://www.chenweiliang.com/cwl-488.html
Nabata na ọwa Telegram nke blọgụ Chen Weiliang ka ị nweta mmelite kachasị ọhụrụ!
📚 Ntuziaka a nwere nnukwu uru, 🌟Nke a bụ ohere dị ụkọ, echefula ya! ⏰⌛💨
Kekọrịta na-amasị ma ọ bụrụ na-amasị gị!
Ịkekọrịta na mmasị gị bụ mkpali anyị na-aga n'ihu!