Akwụkwọ ndekọ ederede
Kedu otu esi eji njikọ dị n'ime ya na ọtụtụ tebụl na nchekwa data MySQL ? Kedu ihe bụ arụmọrụ nke njikọ aka ekpe?
Mysql Ojiji nke njikọ
N'isiakwụkwọ ndị gara aga, anyị amụtala ka esi agụ data site na otu tebụl, nke dị mfe, ma na ngwa ngwa ọ na-adịkarị mkpa ịgụ data sitere na tebụl data dị iche iche.
N'isiakwụkwọ a, anyị ga-ewebata gị otu esi eji MySQL's JOIN iji jụọ data na tebụl abụọ ma ọ bụ karịa.
Ị nwere ike iji Mysql's JOIN na Họrọ, Mmelite na Hichapụ nkwupụta iji sonyere ọtụtụ ajụjụ tebụl.
E kewara JOIN n'ụzọ zuru oke n'ụdị atọ ndị a dịka ọrụ ya siri dị:
- Jikọọ INNER (isonye n'ime, ma ọ bụ nsonye dakọtara): Nweta ndekọ nke mmekọrịta dabara na mpaghara na tebụl abụọ.
- Jikọọ aka ekpe (njikọ aka ekpe):Nweta ndekọ niile na tebụl aka ekpe, ọbụlagodi na enweghị ndekọ kwekọrọ na tebụl aka nri.
- Jikọọ aka nri (njikọ aka nri): N'adịghị ka njikọ aka ekpe, a na-eji ya nweta ndekọ niile na tebụl aka nri, ọbụlagodi na enweghị ndekọ kwekọrọ na tebụl aka ekpe.
Iji INNER JOIN na ngwa ngwa
Anyị nwere tebụl abụọ tcount_tbl na chenweiliang_tbl na nchekwa data chenweiliang. Data dị na tebụl data abụọ bụ ndị a:
ihe atụ
Gbalịa ihe atụ ndị a:
Nwalee data atụ
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-esote, anyị ga-eji INNER JOIN nke MySQL (ị nwere ike ịhapụ INNER wee jiri JOIN, mmetụta ya bụ otu) iji jikọọ tebụl abụọ dị n'elu wee gụọ ụkpụrụ nke ubi chenweiliang_author na tebụl chenweiliang_tbl na ụkpụrụ ubi chenweiliang_count ha kwekọrọ na tebụl tcount_tbl:
AKWỤKWỌ NDỊ ỌZỌ
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)
Okwu SQL dị n'elu bụ nke:
Ebe okwu
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)
Jikọọ aka ekpe MySQL
Njikọ aka ekpe MySQL dị iche na nsonye. MySQL LEFT JOIN ga-agụ data niile dị na tebụl data aka ekpe, ọbụlagodi na enweghị data kwekọrọ na tebụl aka nri.
ihe atụ
Nwaa ihe atụ a, jiri chenweiliang_tbl dị ka tebụl aka ekpe na tcount_tbl dị ka tebụl aka nri, iji ghọta ngwa MySQL LEFT JOIN:
Jikọọ aka ekpe
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 na-eji njikọ aka ekpe mee ihe atụ a dị n'elu, nkwupụta a ga-agụta data ubi niile ahọpụtara na tebụl data chenweiliang_tbl n'aka ekpe, ọbụlagodi na enweghị uru ubi chenweiliang_author kwekọrọ na tebụl tcount_tbl n'aka nri.
Jikọọ aka nri MySQL
MySQL RIGHT JOIN ga-agụ data niile dị na tebụl data ziri ezi, ọbụlagodi na enweghị data kwekọrọ na tebụl aka ekpe.
ihe atụ
Nwaa ihe atụ a, jiri chenweiliang_tbl dị ka tebụl aka ekpe na tcount_tbl dị ka tebụl aka nri, iji ghọta ngwa MySQL RIGHT JOIN:
Jikọọ aka nri
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)
A na-eji njikọ aka nri na ihe atụ dị n'elu. Okwu a ga-agụ data ubi niile ahọpụtara na tebụl data ziri ezi tcount_tbl, ọbụlagodi na enweghị uru ubi chenweiliang_author kwekọrọ na tebụl ekpe chenweiliang_tbl.
Iji JOIN na ederede PHP
A na-eji ọrụ mysqli_query() na PHP iji mezuo nkwupụta SQL. Ị nwere ike iji otu nkwupụta SQL dị n'elu dị ka parameter nke mysqli_query() ọrụ.
Gbalịa ihe atụ a:
MySQL ORDER site n'ule:
<?
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);
?>Enwere m olileanya na isiokwu a bụ "Ịji njikọ MySQL dị n'ime data gafee ọtụtụ tebụl? Ịdị irè nke njikọ aka ekpe" nke e kesara na blọgụ Chen Weiliang ( https://www.chenweiliang.com/ ) ga-abara gị uru.
Nwere onwe gị ịkọrọ njikọ akụkọ a: https://www.chenweiliang.com/cwl-488.html