Littafin Adireshi
MySQL databaseYadda ake amfani da teburi da yawa? Hagu hagu haɗa ingantaccen amfani
Mysql amfani da haɗin gwiwa
A cikin surori da suka gabata, mun koyi yadda ake karanta bayanai daga tebur ɗaya, wanda yake da sauƙin sauƙi, amma a aikace-aikace na gaske sau da yawa ya zama dole don karanta bayanai daga tebur ɗin bayanai da yawa.
A cikin wannan babin za mu nuna muku yadda ake amfani da JOIN na MySQL don neman bayanai a cikin tebur biyu ko fiye.
Kuna iya amfani da JOIN na Mysql a cikin KYAUTA, KYAUTA da SHAFE kalamai don haɗa tambayoyin tebur da yawa.
JOIN ya kasu kusan zuwa kashi uku masu zuwa gwargwadon aikinsa:
- SHIGA CIKI (haɗin ciki, ko equijoin): Samo bayanai tare da filayen da suka dace a cikin tebur biyu.
- HAGUWAR HAGU (hagu na hagu):Samo duk bayanan a tebur na hagu, koda kuwa babu madaidaitan bayanan a teburin dama.
- SHIGA DAMAN (haɗin dama): Sabanin JOIN HAGU, ana amfani da shi don samun duk bayanan da ke cikin teburin dama, koda kuwa babu daidaitattun bayanan da suka dace a teburin hagu.
Amfani da CIN JOIN a cikin Saurin Umurni
Muna da teburi biyu tcount_tbl da chenweiliang_tbl a cikin bayanan chenweiliang.Bayanan bayanan tebur guda biyu sune kamar haka:
misali
Gwada misalai masu zuwa:
gwajin misali data
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)
Na gaba za mu yi amfani da MySQLJOIN CIKI (zaku iya barin INNER kuma kuyi amfani da JOIN, tasirin iri ɗaya ne)Don haɗa tebur biyu na sama don karanta ƙimar filin chenweiliang_count wanda ya dace da duk filayen chenweiliang_author a cikin tebur na chenweiliang_tbl a cikin tcount_tbl:
ciki 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)
Bayanin SQL na sama yayi daidai da:
INA magana
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)
Shiga hagu na MySQL
Shigar hagu na MySQL ya bambanta da haɗawa. MySQL LEFT JOIN zai karanta duk bayanan da ke cikin teburin bayanan hagu, koda kuwa tebur na dama ba shi da bayanan da ya dace.
misali
Gwada misalan masu zuwa zuwa chenweiliang_tbl ga teburin hagu,tcount_tbl Don teburin dama, fahimci aikace-aikacen haɗin gwiwar hagu na MySQL:
SHIGA HAGU
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 cikin misalin da ke sama, ana amfani da JOIN HAGU, kuma wannan bayanin za ta karanta duk bayanan filin da aka zaɓa na teburin bayanai chenweiliang_tbl a hagu, koda kuwa babu darajar filin chenweiliang_author a cikin tebur tcount_tbl a hannun dama.
MySQL DAMAN SHIGA
MySQL RIGHT JOIN zai karanta duk bayanan da ke cikin tebur ɗin bayanai a hannun dama, koda kuwa babu daidaitattun bayanai a cikin tebur a hagu.
misali
Gwada misalan masu zuwa zuwa chenweiliang_tbl ga teburin hagu,tcount_tbl Don teburin da ya dace, fahimci aikace-aikacen MySQL RIGHT JOIN:
SHIGA DAMAN
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)
Ana amfani da DAMAN JOIN a cikin misalin da ke sama, wannan bayanin zai karanta duk bayanan filin da aka zaɓa na teburin bayanan dama tcount_tbl, koda kuwa babu madaidaicin darajar filin chenweiliang_author a cikin tebur na hagu chenweiliang_tbl.
Amfani da JOIN a cikin rubutun PHP
Ana amfani da aikin mysqli_query() a cikin PHP don aiwatar da maganganun SQL, zaku iya amfani da bayanin SQL iri ɗaya kamar na sama azaman siga zuwa aikin mysqli_query().
Gwada misali mai zuwa:
MySQL ORDER TA gwaji:
<?
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 "Multi-tebur amfanin MySQL database ciki haɗa? Hagu Hagu Haɓaka Ingantaccen Amfani", zai taimake ku.
Barka da zuwa raba hanyar haɗin wannan labarin:https://www.chenweiliang.com/cwl-488.html
Barka da zuwa tashar Telegram na Chen Weiliang's blog don samun sabbin abubuwa!
📚 Wannan jagorar ya ƙunshi ƙima mai yawa, 🌟Wannan dama ce da ba kasafai ba, kar a rasa ta! ⏰⌛💨
Share da like idan kuna so!
Rarraba ku da abubuwan so sune ci gaba da ƙarfafa mu!