MySQL表与表之间的联系

数据准备

CREATE TABLE `stu` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(25) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(6) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `groupLeaderId` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=1012 DEFAULT CHARSET=utf8;

CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `caddress` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

多表查询

表与表之间的关系

一对一

一张表中的一条记录只能与另一张表的一条记录相对应。

比如，一个人只能对应一个身份证号码。

一对一两种建表原则：

• 外键唯一：主表的主键和从表的外键(唯一)，形成主外键关系，外键加上唯一约束unique。
• 外键是主键：主表的主键和从表的主键形成主外健关系，就是让一张表的主键同时作为另一张表的主键，同时这一张表的主键不能自增。

一对多

一张表中有一条记录可以对应另外一张表中的多条记录：但是反过来，另外—张表的一条记录只能对应第—张表的一条记录，这种关系就是一对多或多对一。如下：

多对多

一对表中(A)的一条记录能够对应另外一张表(B)中的多条记录；同时B表中的一条记录也能对应A表中的多条记录。要表示多对多的关系，必须创建第三个表，改表通常称为联接表，它将对多对关系划分为两个一对一的关系，并且两个多对多的表的主键都要插入联结表中。如下：

联合查询

联合查询就是将多个查询结果，纵向拼接在一起。关键字union

语法

-- union 会将完全一致的数据去重
select * from tb_a
union
select * from tb_b

-- union all 会将所有数据保留
select id,name,age from tb_a
union all
select id,name,age from tb_b

注意：

在合并两个表时，两个表的列数、数据类型都要一致！！！！

连接查询

连接查询就是将多个表的查询结果横向拼接后展现。

内连接

语法

select * 
from 表1 inner join 表2
on 表1.字段 = 表2.字段;

-- 简化写法
select *
from 表1, 表2
where 表.字段 = 表2.字段;

注意：

内连接只会保留完全符合关联条件的数据。

案例：

-- 查询学生编号为1007的学生名称、学生成绩、班级名称、班级地址
select sid,sname,score,cname,caddress
from stu,class
where stu.cid = class.cid and stu.sid = 1007;

外连接

外连接分为左外连接(left join)和右外连接(right join)。

连接会保留不符合关联条件的数据，左外保留左表中不符合条件的数据，右外表刘右表中不符合条件的数据。

左外连接

语法

-- 左外连接
select *
from 表1 left outer join 表2
on 表1.cid = 表2.cid;

-- outer 可以省略不写
select *
from 表1 left join 表2
on 表1.cid = 表2.cid;

案例：

-- 查询学生信息,如果有班级信息就查询班级信息
select *
from stu left outer join class
on stu.cid = class.cid;

右外连接

语法

-- 右外连接
select *
from 表1 right outer join 表2
on 表1.cid = 表2.cid;

-- outer 可以省略不写
select *
from 表1 right join 表2
on 表1.cid = 表2.cid;

案例：

-- 查询学生信息,如果有班级信息就查询班级信息
select *
from stu right outer join class
on stu.cid = class.cid;

自连接

在一张表里引用自己的字段，这个表可以和它自身做连接运算。

语法

对表设置别名

案例：

-- 求1008学生编号、姓名、组长编号和组长姓名
select s.sid,s.sname,z.sid,z.sname
from stu s, stu z
where s.groupLeaderId = z.sid and s.sid = 1008;

子查询(subquery)

子查询就是嵌套查询。嵌套查询的书写顺序，一般是先书写内层查询，再书写外层查询。

嵌套查询，

​ 1、可以嵌套在where后面做为条件

​ 2、可以嵌套在from后面作为一张表

案例：

-- 子查询
-- 查询与张三同一个班级的学生。
select *
from stu
where cid = (
			select cid
			from stu
			where sname = '张三'
);

-- 成绩高于3号班级所有人的学生信息
select *
from stu
where score > (
				select MAX(score)
				from stu
				where cid = 3
);

-- 有2个以上直接组员的学生信息
-- 1)先查询到有两个组员的学生学号
select groupLeaderId
from stu
group by groupLeaderId
having count(groupLeaderId) > 2;
-- 2)再根据学号查询信息
select *
from stu
where sid in (
				select groupLeaderId
				from stu
				group by groupLeaderId
				having count(groupLeaderId) > 2
);

-- 变式2：子查询当做表
select *
from stu s,(select groupLeaderId from stu group by groupLeaderId having count(groupLeaderId) > 2) z
where s.sid = z.groupLeaderId;

stu
group by groupLeaderId
having count(groupLeaderId) > 2
);

– 变式2：子查询当做表
select *
from stu s,(select groupLeaderId from stu group by groupLeaderId having count(groupLeaderId) > 2) z
where s.sid = z.groupLeaderId;