数据准备
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;