Tusitusiga Tusitusiga
MySQL databasefa'aoga i totonu e tele laulau? agavale agavale fa'atasi lelei fa'aoga
Mysql fa'aogaina o feso'ota'iga
I mataupu ua mavae, ua matou aʻoaʻoina pe faʻapefea ona faitau faʻamatalaga mai le laulau e tasi, e faigofie lava, ae i faʻaoga moni e masani ona manaʻomia le faitau faʻamatalaga mai le tele o laulau faʻamaumauga.
I lenei mataupu o le a matou faʻaali atu ia te oe le faʻaogaina o MySQL's JOIN e suʻe ai faʻamatalaga i ni laulau se lua pe sili atu.
E mafai ona e fa'aogaina le Mysql's JOIN ile SELECT, UPDATE ma DELETE fa'amatalaga e fa'atasi ai ma fesili e tele laulau.
O LE AUAI o lo'o vaevaeina i vaega nei e tolu e tusa ai ma lana galuega:
- FA'I totonu (tu'i totonu, po'o le fa'atasi): Maua fa'amaumauga ma fa'atutusa fa'atasi i laulau e lua.
- AGA VALE AUAI (agavale alu):Aumai fa'amaumauga uma ile laulau agavale, tusa lava pe leai ni fa'amaumauga tutusa ile laulau taumatau.
- TAUMAU AUAI (taumatau auai): Fa'afeagai ma le LEFT JOIN, e fa'aaogaina e maua uma ai fa'amaumauga i le laulau taumatau, tusa lava pe leai ni fa'amaumauga tutusa i le laulau agavale.
Fa'aaogā le INNER JOIN i le Poloaiga Fa'atonu
E lua a matou laulau tcount_tbl ma chenweiliang_tbl i le chenweiliang database.O fa'amaumauga o fa'amaumauga e lua e fa'apea:
Fa'ata'ita'iga
Fa'ata'ita'i fa'ata'ita'iga nei:
fa'amatalaga fa'ata'ita'iga
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)
Le isi o le a matou faʻaogaina MySQLINNER JOIN (e mafai foi ona e aveese INNER ma faʻaoga JOIN, o le aʻafiaga e tutusa)Ina ia faʻafesoʻotaʻi laulau e lua o loʻo i luga e faitau ai le tau o le chenweiliang_count fanua e fetaui ma chenweiliang_author fanua uma i le chenweiliang_tbl laulau i le tcount_tbl laulau:
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)
O le faʻamatalaga SQL o loʻo i luga e tutusa ma:
WHERE fuaiupu
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 AGA AGA
MySQL left join e ese mai join. MySQL LEFT JOIN o le a faitau uma faʻamatalaga i le itu agavale laulau faʻamaumauga, tusa lava pe leai ni faʻamatalaga tutusa i le laulau taumatau.
Fa'ata'ita'iga
Taumafai fa'ata'ita'iga nei e chenweiliang_tbl mo le laulau agavale,tcount_tbl Mo le laulau saʻo, malamalama i le faʻaogaina ole MySQL LEFT JOIN:
LEFT 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)
I le faʻataʻitaʻiga o loʻo i luga, e faʻaaogaina le LEFT JOIN O lenei faʻamatalaga o le a faitau uma ai faʻamatalaga fanua filifilia o le laulau faʻamaumauga chenweiliang_tbl i le agavale, e tusa lava pe leai se tau o le fanua talafeagai a chenweiliang_author i le laulau tcount_tbl i le taumatau.
MySQL SA'O AUAI
MySQL RIGHT JOIN o le a faitau uma faʻamatalaga i le laulau faʻamaumauga i le itu taumatau, e tusa lava pe leai ni faʻamatalaga tutusa i le laulau i le agavale.
Fa'ata'ita'iga
Taumafai fa'ata'ita'iga nei e chenweiliang_tbl mo le laulau agavale,tcount_tbl Mo le laulau saʻo, malamalama i le faʻaogaina o MySQL RIGHT JOIN:
TAUMAU AUAI
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)
FA'AALIGA TAUMATA o lo'o fa'aaogaina i le fa'ata'ita'iga o lo'o i luga, o lenei fa'amatalaga o le a faitau uma fa'amatalaga fanua filifilia o le laulau fa'amaumauga tcount_tbl i le itu taumatau, e tusa lava pe leai se tau talafeagai o fanua o chenweiliang_author i le laulau chenweiliang_tbl i le agavale.
Fa'aaogā le JOIN ile PHP script
O le mysqli_query() galuega o loʻo faʻaaogaina i le PHP e faʻatino ai faʻamatalaga SQL, e mafai ona e faʻaogaina le faʻamatalaga SQL tutusa e pei ona i luga e fai ma faʻamau i le mysqli_query() galuega.
Taumafai le fa'ata'ita'iga lenei:
MySQL ORDER BY suʻega:
<? 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/ ) faʻasoa "Faʻaogaina le tele o laulau o MySQL database i totonu auai? left Left Join Usage Efficiency", o le a fesoasoani ia te oe.
Fa'afeiloa'i e fa'asoa le so'otaga o lenei tusitusiga:https://www.chenweiliang.com/cwl-488.html
Faʻafeiloaʻi i le Telegram channel o le blog a Chen Weiliang e maua ai faʻamatalaga lata mai!
📚 O lenei taʻiala o loʻo i ai le taua tele, 🌟O se avanoa e seasea maua, aua le misia! ⏰⌛💨
Faasoa ma fiafia pe a e fiafia i ai!
O lau fefa'asoaa'i ma le fiafia o la matou fa'aosofiaga faifaipea!