多表联查
多表联查可以通过连接运算实现,即将多张表通过主外键关系关联在一起进行查询。下图提供了多表联查时用到的数据库表之间的关系
等值查询和非等值查询
-
非等值查询
-- 非等值查询 (结果是笛卡尔积) select * from student,class;
-
等值查询
-- 等值查询(交集) 内敛查询 (效率上推荐) select * from student,class,sc where student.classid = class.classid and student.sid = sc.sid and ssex = '男';
-
总结
- 与单表查询类似,都是SELECT语句
- 把多个表放到FROM后,并用逗号隔开
- 可使用AS关键字取别名,便于引用
- 如无重名查询字段则可省略数据表的指定
连接查询
内敛查询
-
INNER JOIN
在表中至少一个匹配时,则返回记录
-- 内联查询方式二(语法结构上推荐写这种) select * from student inner join class on student.classid = class.classid inner join sc on student.sid = sc.sid where ssex = '男'
外联查询
重点:找准主查表
案例:所有的学生和对应的班级信息 和 成绩
-
LEFT JOIN
从左表(表1)中返回所有的记录,即便在右(表2)中没有匹配的行
-- 内联查询方式二(语法结构上推荐写这种) select * from student inner join class on student.classid = class.classid inner join sc on student.sid = sc.sid where ssex = '男'
-
RIGHT JOIN
从右表(table_2)中返回所有的记录,即便在左(table_1)中没有匹配的行
select student.*,class.*,sc.* from class right join student on student.classid = class.classid left join sc on student.sid = sc.sid
案例:
-- 没有班级的学生信息
select * from student
left join class on student.classid = class.classid
where class.classid is null;
-- 没有学生的班级
select * from class
left join student on class.classid = student.classid
where student.sid is null;
union合并
union是求两个查询的并集。
union合并的是结果集,不区分来自于哪一张表,所以可以合并多张表查询出来的数据
- 字段数量必须相同
- 字段类型可以不同
- 起别名必须在第一个结果集写
- union自动去重,去重机制和distinct相同
- 不想去重 union all 显示并集所有数据
-- 学校的人名
select sname from student
union -- 将两个结果集并一个结果集中
select tname from teacher
select sname from student
union all -- 不去重,显示并集所有数据
select tname from teacher
-- 没有学生的班级和没有班级的学生
-- 方式1
select student.*, class.* from student
left join class on student.classid = class.classid
where class.classid is null
union
select student.* , class.* from class
left join student on class.classid = student.classid
where student.sid is null
-- 方式2
select * from student
left join class on student.classid = class.classid
where class.classid is null
union
select * from student
right join class on class.classid = student.classid
where student.sid is null;
-- 学生的班级信息和班级的学生信息
select * from student
left join class on student.classid = class.classid
where class.classid is null
union
select * from student
right join class on class.classid = student.classid
where student.sid is null;
子查询
- 查询语句中又有一个查询(里面的查询称为子查询),也叫内部查询
- 先执行子句,再执行父句
- 子句必须要用小括号括起来
where 子查询
-- 查询id最大的一个学生(使用排序+分页实现)
select * from student order by sid desc limit 1
-- 查询id最大的一个学生(使用where子查询实现)
select * from student where sid = (select max(sid) from student)
-- 查询每个班下id最大的学生(使用where子查询实现)
select * from student where sid in (
select max(sid) from student group by classid
)
-- 子查询(where 子查询)学过张三老师课程的学生
select * from student where sid in(
select sid from sc where cid = (
select cid from course where tid=(
select tid from teacher where tname = '张三'
)
)
)
-- 没学过张三老师课程的学生信息
select * from student where sid not in(
select sid from sc where cid = (
select cid from course where tid=(
select tid from teacher where tname = '张三'
)
)
)
from 子查询
-- from 子查询
-- 查询大于5人的班级名称和人数(不使用子查询)
select classname,count(sid) from class
left join student on class.classid = student.classid
group by classname having count(sid) > 5
-- 查询大于5人的班级名称和人数(使用from型子查询)
select classname,c from class
left join (select classid,count(sid) c from student group by classid) t
on class.classid = t.classid
where c > 5
exists 子查询
子句有结果父句执行,子句没结果父句不执行
-- 学生表中有性别为外星人的同学就查询出所有的老师
select * from teacher
where exists (select * from student where ssex = '外星人')
any/some 子查询
表示满足其中任意一个条件
假设any内部的查询语句返回的结果个数是三个,如:result1,result2,result3,那么,
select …from … where a > any(…);
->相当于:
select …from … where a > result1 or a > result2 or a > result3;
some 是 any的别名,所以用法是一样的,可以替代使用
# 题:查询出一班成绩比二班最低成绩高的学生
-- 方式1
select DISTINCT student.* from student
inner join sc on student.sid = sc.sid
where classid = 1
and score > (select min(score) from student
inner join sc on student.sid = sc.sid where classid = 2)
-- 方式2
select DISTINCT student.* from student
inner join sc on student.sid = sc.sid
where classid = 1 and (
score > 70 or score > 60 or score > 80 or score > 50.0 or
score > 30.0 or score > 20.0 or score > 31.0 or score > 34.0
)
-- 方式3:any
select DISTINCT student.* from student
inner join sc on student.sid = sc.sid
where classid = 1 and score > any(
select score from student
inner join sc on student.sid = sc.sid where classid = 2
)
all 子查询
表示满足其中所有条件条件,ALL关键字与any关键字类似,只不过上面的or改成and。
假设any内部的查询语句返回的结果个数是三个,
select …from … where a > all(…);
->
select …from … where a > result1 and a > result2 and a > result3;
# 题:查询出一班成绩比二班最低成绩高的学生
-- 方式1
select DISTINCT student.* from student
inner join sc on student.sid = sc.sid
where classid = 1
and score > (select max(score) from student
inner join sc on student.sid = sc.sid where classid = 2)
-- 方式2
select DISTINCT student.* from student
inner join sc on student.sid = sc.sid
where classid = 1 and
score > 70 and score > 60 and score > 80 and score > 50.0 and
score > 30.0 and score > 20.0 and score > 31.0 and score > 34.0
-- 方式3:all
select DISTINCT student.* from student
inner join sc on student.sid = sc.sid
where classid = 1 and score > all(
select score from student
inner join sc on student.sid = sc.sid where classid = 2
)
流程控制语句
-
IF(expr1,expr2,expr3)
expr1 表达式
expr2 成立结果
expr3 不成立结果
select tid,tname,if(tsex=1,'男','女') tsex,tbirthday,taddress from teacher
- IFNULL(expr1,expr2)
expr1 字段
expr2 如果字段为null时显示
select sid,sname,ifnull(birthday,'此人无生日')bir,ssex from student
case when then end 语句
-
简单case
select tid,tname, case tsex when 1 then '男' when 0 then '女' else '未知' end , tmoney from teacher
-
搜索case
select tid,tname, case when tsex > 1 then '男' when tsex = 1 then '女' when tsex < 1 then '零' else '意外' end ,tmoney from teacher
案例:
/* 查询学生的成绩, 并将大于90分的用A显示, 大于80分的用B显示, 大于70分的用C显示, 大于60分的用D显示, 小于60分的显示不及格 */ select student.sid,sname,cid, case when score > 90 then 'A' when score > 80 then 'B' when score > 70 then 'C' when score > 60 then 'D' else '不及格' end from sc left join student on student.sid = sc.sid