articulus Directory
Quomodo iunctura interna cum pluribus tabulis in basi datorum MySQL utatur ? Quae est efficacia iuncturae sinistrae?
Mysql usus connexionis
In praecedentibus capitibus notitias ex una tabula, quae simplex est relative, didicimus legere, sed in applicationibus realibus saepe datas ex multiplici notitia tabularum legere necesse est.
In hoc capite ostendemus te quomodo utaris MySQL JOIN ad interrogationes datas in duabus vel pluribus tabulis.
Potes uti Mysql's JOIN in SELECT, UPDATE et delere propositiones ad multi- tudinem quaestionis iungere.
CONVENIO dure dividitur in haec tria genera secundum suum munus;
- INTERIOR JOIN (iungere interiorem, seu aequijoin)Accipe tabulas cum matchings agris in duabus tabulis.
- SINISTRUM JOIN (sinistra copulare);Accipe omnia monumenta in tabula sinistra, etiamsi nullae sint tabulae congruentes in mensa dextra.
- JURE JOIN (jus join) ; CONTRA IUNCTUM sinistrum, tabulas omnes in tabula dextra accipere adhibetur, etsi nullae sunt congruentes monumenta in mensa sinistra.
Per interiorem JOIN in To order Promptus
Duas tabulas habemus tcount_tbl et chenweiliang_tbl in datorum chenweiliang.Data in duabus tabulis data sunt haec:
Exempli gratia
Conare haec exempla:
test exempli gratia 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)
Deinde, INNER JOIN MySQL utemur (INNER omittere et JOIN uti potes, effectus idem est) ad duas tabulas supra coniungendas et valores campi chenweiliang_author in tabula chenweiliang_tbl et valores campi chenweiliang_count correspondentes in tabula tcount_tbl legendos:
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)
SQL Quod supra dicitur aequivalet isti:
UBI 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 EGRESSUS JOIN
MySQL junctura sinistra differt a junctura. MySQL SINISTRA JOIN leget omnia indicia in tabella sinistra data, etiamsi dextra mensa non habet datam correspondentem.
Exempli gratia
Exemplum sequens experire, "chenweiliang_tbl" ut tabula sinistra et "tcount_tbl" ut tabula dextra utens, ut applicationem MySQL LEFT JOIN intellegas:
EGRESSUS 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)
In superiori exemplo, SINISTRUM JOIN adhibetur, et haec enuntiatio omnia delectorum notitiarum tabularum chenweiliang_tbl a sinistris leget, etiamsi nullus adest valoris campi chenweiliang_authoris in dextra mensa respondens.
MySQL IURE JOIN
MySQL IURE JOIN leget a dextra omnia notitias in tabella datas, etiamsi nulla in mensa a sinistris data sit correspondentia.
Exempli gratia
Exemplum sequens experire, "chenweiliang_tbl" ut tabula sinistra et "tcount_tbl" ut tabula dextra utens, ut applicationem MySQL RIGHT JOIN intellegas:
DEXTERA JOIN'
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)
IURE CONIUNCTIO adhibetur in exemplo superiore, haec enuntiatio leget omnem campum delectum notitiarum tabellarum tcount_tbl in dextera, etiamsi nulla sit congruens valoris agri chenweiliang_author in tabula chenweiliang_tbl ad sinistram.
Join in PHP script
Munus mysqli_query() in PHP ad declarationes SQL faciendas adhibetur, eadem SQL sententia uti potes quae supra ut parametri ad munus mysqli_query().
Conare hoc exemplum:
ORDINE MySQL 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);
?>Speramus te utilem fore articulum "Using MySQL Database inner joint trans multiplices tabulas? Efficiency of left joint" in diario Chen Weiliang ( https://www.chenweiliang.com/ ) communicatum.
Libere hunc articulum communicare potes: https://www.chenweiliang.com/cwl-488.html