/**
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 | 赞 |
+----------+-------+