mySQL多表联查语句 && 详细子查询语句

最新推荐文章于 2024-03-07 21:29:42 发布

CodeMonkey-D

最新推荐文章于 2024-03-07 21:29:42 发布

阅读量111

点赞数 1

分类专栏： MySQL 文章标签： mysql adb android

本文链接：https://blog.csdn.net/HakerDONG/article/details/132680053

版权

MySQL 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

多表联查

多表联查可以通过连接运算实现，即将多张表通过主外键关系关联在一起进行查询。下图提供了多表联查时用到的数据库表之间的关系

等值查询和非等值查询

非等值查询

-- 非等值查询 （结果是笛卡尔积）
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特点：

字段数量必须相同
字段类型可以不同
起别名必须在第一个结果集写
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