Kalozera wa Nkhani
MySQL databasekujowina mkati ntchito multitable? kumanzere kumanzere kujowina kugwiritsa ntchito bwino
Mysql kugwiritsa ntchito mgwirizano
M'mitu yapitayi, taphunzira momwe tingawerengere deta kuchokera patebulo limodzi, lomwe ndi losavuta, koma muzogwiritsira ntchito zenizeni nthawi zambiri zimakhala zofunikira kuwerenga deta kuchokera ku matebulo angapo a deta.
M'mutu uno tikuwonetsani momwe mungagwiritsire ntchito JOIN ya MySQL kuti mufufuze deta pamagome awiri kapena kuposerapo.
Mutha kugwiritsa ntchito JOIN ya Mysql mu SELECT, UPDATE ndi DELETE mawu kuti mulowe nawo pamafunso ambiri.
JOIN yagawidwa m'magulu atatu otsatirawa malinga ndi ntchito yake:
- INNER JOIN (kujowina mkati, kapena kujowina): Pezani zolemba zokhala ndi magawo ofanana pamatebulo awiri.
- LEFT JOIN (kujowina kumanzere):Pezani zolemba zonse patebulo lakumanzere, ngakhale palibe zolemba zofananira patebulo lakumanja.
- KULIMBIKITSA JOIN (kumanja kujowina): Mosiyana ndi LEFT JOIN, imagwiritsidwa ntchito kupeza zolembedwa zonse patebulo lakumanja, ngakhale patebulo lakumanzere palibe zofananira.
Kugwiritsa ntchito INNER JOIN mu Command Prompt
Tili ndi matebulo awiri tcount_tbl ndi chenweiliang_tbl munkhokwe ya chenweiliang.Deta ya matebulo awiri a data ndi awa:
Chitsanzo
Yesani zitsanzo zotsatirazi:
data yoyeserera
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)
Kenako tidzagwiritsa ntchito MySQLINNER JOIN (mutha kusiyanso INNER ndikugwiritsa ntchito JOIN, zotsatira zake ndi zofanana)Kulumikiza magome awiri omwe ali pamwambawa kuti muwerenge mtengo wa chenweiliang_count munda wolingana ndi minda yonse ya chenweiliang_author mu tebulo la chenweiliang_tbl mu tebulo la 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)
Mawu omwe ali pamwambapa a SQL ndi ofanana ndi:
KUTI 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
Kujowina kumanzere kwa MySQL ndikosiyana ndi kujowina. MySQL LEFT JOIN iwerenga zonse zomwe zili patsamba lakumanzere, ngakhale tebulo lakumanja lilibe deta yofananira.
Chitsanzo
Yesani zitsanzo zotsatirazi chenweiliang_tbl kwa tebulo lakumanzere,tcount_tbl Pagome lakumanja, mvetsetsani kugwiritsa ntchito MySQL LEFT JOIN:
KUCHOKERA 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)
Muchitsanzo chapamwambachi, LEFT JOIN ikugwiritsidwa ntchito, ndipo mawuwa awerengera zonse zomwe zasankhidwa pa tebulo la chenweiliang_tbl kumanzere, ngakhale palibe mtengo wofanana wa chenweiliang_author patebulo tcount_tbl kumanja.
MySQL RIGHT JOIN
MySQL RIGHT JOIN iwerenga zonse zomwe zili patebulo lakumanja, ngakhale palibe deta yofananira patebulo kumanzere.
Chitsanzo
Yesani zitsanzo zotsatirazi chenweiliang_tbl kwa tebulo lakumanzere,tcount_tbl Pagome lolondola, mvetsetsani kugwiritsa ntchito MySQL RIGHT JOIN:
KULIMBIKITSA JOINANI
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)
KUGWIRITSA NTCHITO KWAMBIRI kukugwiritsidwa ntchito mu chitsanzo chapamwambachi, mawuwa awerengera zonse zomwe zasankhidwa pa tebulo lamanja la tcount_tbl, ngakhale palibe chenweiliang_author field value patebulo lakumanzere chenweiliang_tbl.
Kugwiritsa ntchito JOIN mu PHP script
Ntchito ya mysqli_query() imagwiritsidwa ntchito mu PHP pochita mawu a SQL, mutha kugwiritsa ntchito mawu omwewo a SQL monga pamwambapa ngati parameter ku mysqli_query() ntchito.
Yesani chitsanzo ichi:
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);
?>Hope Chen Weiliang Blog ( https://www.chenweiliang.com/ ) adagawana "Kugwiritsa ntchito ma tebulo angapo a MySQL database yolowa mkati? kumanzere Kumanzere Kujowina Kugwiritsa Ntchito Mwachangu", zikuthandizani.
Takulandirani kugawana ulalo wa nkhaniyi:https://www.chenweiliang.com/cwl-488.html
Takulandilani panjira ya Telegraph yabulogu ya Chen Weiliang kuti mupeze zosintha zaposachedwa!
📚 Bukuli lili ndi phindu lalikulu, 🌟Uwu ndi mwayi wosowa, musaphonye! ⏰⌛💨
Share ndi like ngati mukufuna!
Kugawana kwanu ndi zomwe mumakonda ndizomwe zimatilimbikitsa nthawi zonse!