Maqolalar katalogi
MySQL ma'lumotlar bazasiichki birlashma ko'p jadval foydalanish? chap chapga qo'shilishdan foydalanish samaradorligi
Mysql ulanishdan foydalanish
Oldingi boblarda biz bir jadvaldagi ma'lumotlarni o'qishni o'rgandik, bu nisbatan sodda, lekin real ilovalarda ko'pincha bir nechta ma'lumotlar jadvallaridagi ma'lumotlarni o'qish kerak bo'ladi.
Ushbu bobda biz sizga MySQL-ning JOIN-dan ikki yoki undan ortiq jadvaldagi ma'lumotlarni so'rash uchun qanday foydalanishni ko'rsatamiz.
Ko'p jadvalli so'rovlarga qo'shilish uchun Mysql-ning JOIN-dan SELECT, UPDATE va DELETE iboralarida foydalanishingiz mumkin.
JOIN funktsiyasiga ko'ra taxminan quyidagi uchta toifaga bo'linadi:
- INNER JOIN (ichki qo'shilish yoki teng qo'shilish): Ikki jadvaldagi mos keladigan maydonlar bilan yozuvlarni oling.
- LEFT JOIN (chapga qo'shilish):O'ng jadvalda mos yozuvlar bo'lmasa ham, chap jadvaldagi barcha yozuvlarni oling.
- O'ngga qo'shilish (o'ngga qo'shilish): LEFT JOIN dan farqli o'laroq, chap jadvalda mos keladigan yozuvlar bo'lmasa ham, o'ng jadvaldagi barcha yozuvlarni olish uchun ishlatiladi.
Buyruqlar satrida INNER JOIN dan foydalanish
Chenweiliang ma'lumotlar bazasida ikkita tcount_tbl va chenweiliang_tbl jadvallari mavjud.Ikki ma'lumot jadvalining ma'lumotlari quyidagicha:
Misol
Quyidagi misollarni sinab ko'ring:
sinov namunasi ma'lumotlari
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)
Keyinchalik MySQL dan foydalanamizINNER JOIN (shuningdek, siz INNERni qoldirib, JOIN dan foydalanishingiz mumkin, effekt bir xil)Tcount_tbl jadvalidagi chenweiliang_tbl jadvalidagi barcha chenweiliang_author maydonlariga mos keladigan chenweiliang_count maydonining qiymatini o'qish uchun yuqoridagi ikkita jadvalni ulash uchun:
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)
Yuqoridagi SQL bayonoti quyidagilarga teng:
WHERE bandi
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 CHIQISHNI CHAPLADI
MySQL chap qo'shilish qo'shilishdan farq qiladi. MySQL LEFT JOIN chap ma'lumotlar jadvalidagi barcha ma'lumotlarni o'qiydi, hatto o'ng jadvalda tegishli ma'lumotlar bo'lmasa ham.
Misol
Quyidagi misollarni sinab ko'ring chenweiliang_tbl chap stol uchun,tcount_tbl To'g'ri jadval uchun MySQL LEFT JOIN ilovasini tushuning:
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)
Yuqoridagi misolda LEFT JOIN ishlatiladi va bu ibora chapdagi chenweiliang_tbl ma'lumotlar jadvalining barcha tanlangan maydon ma'lumotlarini o'qiydi, hatto o'ngdagi tcount_tbl jadvalida chenweiliang_author ning tegishli maydon qiymati bo'lmasa ham.
MySQL TO'G'RI QO'SHILING
MySQL RIGHT JOIN o'ngdagi ma'lumotlar jadvalidagi barcha ma'lumotlarni o'qiydi, hatto chapdagi jadvalda tegishli ma'lumotlar bo'lmasa ham.
Misol
Quyidagi misollarni sinab ko'ring chenweiliang_tbl chap stol uchun,tcount_tbl To'g'ri jadval uchun MySQL RIGHT JOIN ilovasini tushuning:
TO'G'RI QO'SHILING
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)
Yuqoridagi misolda RIGHT JOIN ishlatiladi, bu ibora tcount_tbl o'ng ma'lumotlar jadvalining barcha tanlangan maydon ma'lumotlarini o'qiydi, hatto chenweiliang_tbl chap jadvalida mos keladigan chenweiliang_author maydoni qiymati bo'lmasa ham.
PHP skriptida JOIN dan foydalanish
Mysqli_query() funksiyasi PHP da SQL operatorlarini bajarish uchun ishlatiladi, siz mysqli_query() funksiyasining parametri sifatida yuqoridagi SQL operatoridan foydalanishingiz mumkin.
Quyidagi misolni sinab ko'ring:
MySQL-ni sinov bo'yicha BUYURTISH:
<?
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);
?>Umid qilamanki, Chen Veyliang blogi ( https://www.chenweiliang.com/ ) birgalikda "MySQL ma'lumotlar bazasi ichki birlashmasidan ko'p jadvalli foydalanish? chapga qo'shilishdan foydalanish samaradorligi", bu sizga yordam beradi.
Ushbu maqolaning havolasini baham ko'rish uchun xush kelibsiz:https://www.chenweiliang.com/cwl-488.html