Tuhinga Tuhinga
pātengi raraunga MySQLuru o roto te whakamahi ripanga maha? maui maui hono uru pai te whakamahi
Mysql te whakamahi hononga
I roto i nga upoko o mua, kua ako matou ki te panui raraunga mai i te ripanga kotahi, he mea ngawari noa, engari i roto i nga tono tuuturu he maha nga wa e tika ana kia panuihia nga raraunga mai i nga ripanga raraunga maha.
I tenei upoko ka whakaatu matou ki a koe me pehea te whakamahi i te JOIN a MySQL ki te uiui raraunga i roto i nga ripanga e rua, neke atu ranei.
Ka taea e koe te whakamahi i a Mysql's JOIN i roto i te SELECT, UPDATE and DELETE tauākī ki te uru atu ki nga uiui maha-tepu.
Ko te JOIN kua wehewehea ki nga waahanga e toru e whai ake nei i runga i tana mahi:
- KAUPAPA ROTO (whakauru o roto, hono tahi ranei): Tikina nga rekoata me nga mara e rite ana ki nga ripanga e rua.
- HUA MAUI (maui hono):Tikina nga rekoata katoa ki te ripanga maui, ahakoa karekau he rekoata rite ki te ripanga matau.
- RIGHT JOIN (tika uru): He rereke ki te HUA MAUI, ka whakamahia hei tiki i nga rekoata katoa ki te ripanga matau, ahakoa karekau he rekoata e rite ana ki te ripanga maui.
Te whakamahi i INNER JOIN i te Whakatairanga Whakatairanga
E rua nga ripanga tcount_tbl me te chenweiliang_tbl kei roto i te putunga raraunga chenweiliang.Ko nga raraunga o nga ripanga raraunga e rua e whai ake nei:
Akoranga
Whakamātauria ngā tauira e whai ake nei:
raraunga tauira whakamatautau
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)
I muri mai ka whakamahia e matou a MySQLKAUPAPA KAUPAPA (ka taea hoki e koe te whakakore i a INNER me te whakamahi i te JOIN, he rite tonu te paanga)Hei hono i nga ripanga e rua i runga ake nei hei panui i te uara o te mara chenweiliang_count e rite ana ki nga mara chenweiliang_author katoa i te ripanga chenweiliang_tbl i te ripanga 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)
Ko te korero SQL i runga ake nei he rite ki:
WHERE rara
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 MAHUI WHAKATAHI
He rereke te hono maui MySQL mai i te hono. Ka panuihia e MySQL LEFT JOIN nga raraunga katoa i te ripanga raraunga maui, ahakoa karekau he raraunga o te ripanga matau.
Akoranga
Whakamātauria ngā tauira e whai ake nei chenweiliang_tbl mo te tepu maui,tcount_tbl Mo te ripanga tika, mohio ki te tono a MySQL LEFT JOIN:
MAUUU WHAKANUI
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 roto i te tauira i runga ake nei, ka whakamahia te LEFT JOIN, ka panuitia e tenei korero nga raraunga mara kua tohua o te ripanga raraunga chenweiliang_tbl kei te taha maui, ahakoa karekau he uara mara o te chenweiliang_author i te ripanga tcount_tbl kei te taha matau.
MySQL KAUPAPA KAUPAPA
Ka panuihia e MySQL RIGHT JOIN nga raraunga katoa kei te ripanga raraunga kei te taha matau, ahakoa kaore he raraunga e rite ana ki te ripanga kei te taha maui.
Akoranga
Whakamātauria ngā tauira e whai ake nei chenweiliang_tbl mo te tepu maui,tcount_tbl Mo te ripanga tika, kia mohio koe ki te tono a MySQL RIGHT JOIN:
KAUPAPA KAUPAPA
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)
Ka whakamahia te RIGHT JOIN i te tauira o runga ake nei, ka panuitia e tenei tauākī nga raraunga mara kua tohua o te ripanga raraunga tika tcount_tbl, ahakoa karekau he uara mara chenweiliang_author i te ripanga maui chenweiliang_tbl.
Te whakamahi i te JOIN i te tuhinga PHP
Ka whakamahia te mahi mysqli_query() i roto i te PHP hei whakahaere i nga tauākī SQL, ka taea e koe te whakamahi i te tauākī SQL rite i runga ake nei hei tawhā ki te mahi mysqli_query().
Whakamātauria te tauira e whai ake nei:
MySQL ORDER MA te whakamatautau:
<? 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/ ) shared "Te whakamahi ripanga maha o MySQL pātengi raraunga uru ki roto? maui Mauī Hono Hononga Whakamahi", ka awhina koe.
Nau mai ki te tohatoha i te hono o tenei tuhinga:https://www.chenweiliang.com/cwl-488.html
Nau mai ki te hongere Telegram o te blog a Chen Weiliang ki te tiki i nga korero hou!
📚 He uara nui tenei aratohu, 🌟He waahi onge tenei, kaua e ngaro! ⏰⌛💨
Tohaina me te pai ki te pai koe!
Ko to tiritiri me o hiahia ko to maatau hihiri tonu!