mysql初識（六） 多表關聯

標籤：

/**
mysql 多表關聯

 

*/

一對一的關聯 比如城市和區號的關聯*******************

先是一個city表
cid city coid
1 北京1
2 上海2
3 廣州 3

再一個區號表
coid code
1 010
2 020
3 0755

這樣通過 cid和coid的關聯將兩張表連在一起
一對一的類型 他的關聯欄位可以隨便放在兩個表中
mysql> select * from city,code where city.coid=code.coid;
+-----+------+------+------+------+
| cid | city | coid | coid | code |
+-----+------+------+------+------+
| 1 | 北京 | 1 | 1 | 010 |
| 2 | 上海 | 2 | 2 | 020 |
| 3 | 廣州 | 3 | 3 | 0755 |
+-----+------+------+------+------+

一對N的關聯 比如說學生和班級的關聯***************
一個學生表
mysql> select * from student;
+----+------------+------+------+-------+
| id | name | sex | age | class |
+----+------------+------+------+-------+
| 1 | 小明 | 男 | 14 | 1 |
| 2 | 李雷 | 男 | 14 | 1 |
| 3 | 韓梅梅 | 女 | 20 | 1 |
| 4 | aboy | 男 | 10 | 1 |
| 6 | 小明 | 男 | 14 | 1 |
| 7 | 李大鎚 | 女 | 17 | 2 |
| 8 | MrJoker | 男 | 42 | 2 |
| 9 | mingzdi | 男 | 19 | 2 |
| 10 | 新人 | 男 | 20 | 2 |
| 11 | 又一個新人 | 女 | 22 | 2 |
| 12 | newboy | 男 | 19 | 3 |
| 13 | oldboy | 男 | 19 | 1 |
| 14 | as | 男 | 17 | 3 |
+----+------------+------+------+-------+

一個班級表
mysql> select * from class;
+-----+-----------+
| cid | classname |
+-----+-----------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
| 4 | 四班 |
+-----+-----------+

一對多的關聯表關聯條件給多的那一方

查出每個人對應的班級
select * from student,class where student.class=class.cid;

用內部連結的方法 inner join ******************
select * from student as s inner join class as c on s.class=c.cid;
on 是配合inner join 使用 進行條件限制

 

找到二班的全部同學
select * from student as s inner join class as c on s.class=c.cid where class="2";

左關聯和右關聯****************************************
左關聯
mysql> select * from student as s left join class as c on s.class=c.cid;
+----+------------+------+------+-------+------+-----------+
| id | name | sex | age | class | cid | classname |
+----+------------+------+------+-------+------+-----------+
| 1 | 小明 | 男 | 14 | 1 | 1 | 一班 |
| 2 | 李雷 | 男 | 14 | 1 | 1 | 一班 |
| 3 | 韓梅梅 | 女 | 20 | 1 | 1 | 一班 |
| 4 | aboy | 男 | 10 | 1 | 1 | 一班 |
| 6 | 小明 | 男 | 14 | 1 | 1 | 一班 |
| 7 | 李大鎚 | 女 | 17 | 2 | 2 | 二班 |
| 8 | MrJoker | 男 | 42 | 2 | 2 | 二班 |
| 9 | mingzdi | 男 | 19 | 2 | 2 | 二班 |
| 10 | 新人 | 男 | 20 | 2 | 2 | 二班 |
| 11 | 又一個新人 | 女 | 22 | 2 | 2 | 二班 |
| 12 | newboy | 男 | 19 | 3 | 3 | 三班 |
| 13 | oldboy | 男 | 19 | 1 | 1 | 一班 |
| 14 | as | 男 | 17 | 3 | 3 | 三班 |
+----+------------+------+------+-------+------+-----------+
右關聯
mysql> select * from student as s right join class as c on s.class=c.cid;
+------+------------+------+------+-------+-----+-----------+
| id | name | sex | age | class | cid | classname |
+------+------------+------+------+-------+-----+-----------+
| 1 | 小明 | 男 | 14 | 1 | 1 | 一班 |
| 2 | 李雷 | 男 | 14 | 1 | 1 | 一班 |
| 3 | 韓梅梅 | 女 | 20 | 1 | 1 | 一班 |
| 4 | aboy | 男 | 10 | 1 | 1 | 一班 |
| 6 | 小明 | 男 | 14 | 1 | 1 | 一班 |
| 13 | oldboy | 男 | 19 | 1 | 1 | 一班 |
| 7 | 李大鎚 | 女 | 17 | 2 | 2 | 二班 |
| 8 | MrJoker | 男 | 42 | 2 | 2 | 二班 |
| 9 | mingzdi | 男 | 19 | 2 | 2 | 二班 |
| 10 | 新人 | 男 | 20 | 2 | 2 | 二班 |
| 11 | 又一個新人 | 女 | 22 | 2 | 2 | 二班 |
| 12 | newboy | 男 | 19 | 3 | 3 | 三班 |
| 14 | as | 男 | 17 | 3 | 3 | 三班 |
| NULL | NULL | NULL | NULL | NULL | 4 | 四班 |
+------+------------+------+------+-------+-----+-----------+

通過以上可以看出
在左關聯時 左表student是全部顯示，而沒有人的四班是不會顯示的
右關聯時 右表class是全部顯示 沒有人的四班也會顯示出來
通過以上總結出：向哪個表關聯則哪個表的資料全部顯示，另一個表全部去配合被關聯的表

 

統計每個班級有多少人
mysql> select count(*),c.classname from student as s inner join class as c on s.
class=c.cid group by c.classname;
+----------+-----------+
| count(*) | classname |
+----------+-----------+
| 6 | 一班 |
| 2 | 三班 |
| 5 | 二班 |
+----------+-----------+
遇見
這個語句有三個關鍵點：
1.count(*) 是計算資訊總數
2.inner join 將多表關聯起來 on設定關聯條件
3.group by 將表通過某資訊分組

 

尋找和李雷同班的同學
分三個步驟
1.先查出李雷對應的班級
mysql> select class from student where name=‘李雷‘; //是1
2.再查出李雷班級的所有同學
mysql> select * from student where class="1";
3.將李雷去除
mysql> select * from student where class="1" and name !=‘李雷‘;

將以上合并成一條資料就是：
select * from student where class=(select class from student where name=‘李雷‘) and name !=‘李雷‘;

 

多表關聯***********************************
建立文章表 article
+-----+----------------+
| aid | title |
+-----+----------------+
| 1 | 百度上市了 |
| 2 | 明天開始放假了 |
| 3 | 周末不休息 |
| 4 | 明天上午上課 |
| 5 | 黃曉明離婚了 |
+-----+----------------+
建立中間表
+------+------+
| aid | tid |
+------+------+
| 1 | 2 |
| 1 | 1 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
| 3 | 3 |
| 4 | 4 |
| 4 | 3 |
| 5 | 1 |
| 5 | 2 |
| 5 | 3 |
+------+------+
建立標籤表
+-----+-------+
| tid | tname |
+-----+-------+
| 1 | 熱門 |
| 2 | 火爆 |
| 3 | 贊 |
| 4 | 苦惱 |
| 5 | 生氣 |
+-----+-------+

三張表關聯的基本思路是 (檢索出所有文章對應的標籤名)
select * from article as a join arc_tag as at on a.aid=at.aid join tag as t on at.tid=t.tid;

檢索出文章對應的標籤名
select * from article as a join art_tag as at on a.aid=at.aid;
+-----+----------------+------+------+
| aid | title | aid | tid |
+-----+----------------+------+------+
| 1 | 百度上市了 | 1 | 2 |
| 1 | 百度上市了 | 1 | 1 |
| 1 | 百度上市了 | 1 | 3 |
| 2 | 明天開始放假了 | 2 | 2 |
| 2 | 明天開始放假了 | 2 | 3 |
| 3 | 周末不休息 | 3 | 3 |
| 4 | 明天上午上課 | 4 | 4 |
| 4 | 明天上午上課 | 4 | 3 |
| 5 | 黃曉明離婚了 | 5 | 1 |
| 5 | 黃曉明離婚了 | 5 | 2 |
| 5 | 黃曉明離婚了 | 5 | 3 |
+-----+----------------+------+------+

檢索出百度對應的標籤名
mysql> select * from article as a join arc_tag as at on a.aid=at.aid join tag as t on at.tid=t.tid where title like ‘%百度%‘;
+-----+--------------+------+------+-----+-------+
| aid | title | aid | tid | tid | tname |
+-----+--------------+------+------+-----+-------+
| 1 | 百度上市了 | 1 | 1 | 1 | 熱門 |
| 1 | 百度上市了 | 1 | 2 | 2 | 火爆 |
| 1 | 百度上市了 | 1 | 3 | 3 | 贊 |
+-----+--------------+------+------+-----+-------+

 

檢索出和‘百度’擁有一樣標籤的文章
先檢索出百度對應的標籤id
select at.tid from article as a join arc_tag as at on a.aid=at.aid where a.title like ‘%百度%‘;
+------+
| tid |
+------+
| 2 |
| 1 |
| 3 |
+------+
再將這個作為條件進行查詢 順便將百度對應的文章屏蔽
mysql> select * from article as a join arc_tag as at on a.aid=at.aid join tag as t on at.tid=t.tid where at.tid in (1,2,3) and a.aid !=1;
+-----+----------------+------+------+-----+-------+
| aid | title | aid | tid | tid | tname |
+-----+----------------+------+------+-----+-------+
| 5 | 黃曉明離婚了 | 5 | 1 | 1 | 熱門 |
| 2 | 明天開始放假了 | 2 | 2 | 2 | 火爆 |
| 5 | 黃曉明離婚了 | 5 | 2 | 2 | 火爆 |
| 2 | 明天開始放假了 | 2 | 3 | 3 | 贊 |
| 3 | 周末不休息 | 3 | 3 | 3 | 贊 |
| 4 | 明天上午上課 | 4 | 3 | 3 | 贊 |
| 5 | 黃曉明離婚了 | 5 | 3 | 3 | 贊 |
+-----+----------------+------+------+-----+-------+

將以上兩條結合在一起就是個完整的語句
select * from article as a join arc_tag as at on a.aid=at.aid join tag as t on t.tid=at.tid where at.tid in (select at.tid from article as a join arc_tag as at on a.aid=at.aid where a.title like ‘%百度%‘) and a.title not like ‘%百度%‘;
+-----+----------------+------+------+-----+-------+
| aid | title | aid | tid | tid | tname |
+-----+----------------+------+------+-----+-------+
| 5 | 黃曉明離婚了 | 5 | 1 | 1 | 熱門 |
| 2 | 明天開始放假了 | 2 | 2 | 2 | 火爆 |
| 5 | 黃曉明離婚了 | 5 | 2 | 2 | 火爆 |
| 2 | 明天開始放假了 | 2 | 3 | 3 | 贊 |
| 3 | 周末不休息 | 3 | 3 | 3 | 贊 |
| 4 | 明天上午上課 | 4 | 3 | 3 | 贊 |
| 5 | 黃曉明離婚了 | 5 | 3 | 3 | 贊 |
+-----+----------------+------+------+-----+-------+

檢索出每個標籤對應文章的數量
select count(*),tname from article as a join arc_tag as at on a.aid=at.aid join tag as t on t.tid=at.tid group by tname;
+----------+-------+
| count(*) | tname |
+----------+-------+
| 3 | 火爆 |
| 2 | 熱門 |
| 1 | 苦惱 |
| 5 | 贊 |
+----------+-------+

mysql初識（六） 多表關聯