复杂SQL_01

目录

• 1.外联和内联★
• 2.sql语句的执行顺序★★
• 3.单标嵌套查询★★★★
• 4.limit★★
• 5.ifnull()的使用和having★★
• 6.多表联查
  • 6.1 两表联查
  • 6.2 内外联交叉★★★
  • 6.3 三表联查
  • 6.4 两表联查where
  • 6.5 两表联查having
• 7.思路转换 大小转换
• 8.嵌套删除
• 9.多字段分组★
• 10.Case..When★★★★★

1.外联和内联★

left join/right join: 
特点: 主表所有的数据都显示,另外一张表中有匹配的数据有匹配的数据则显示,若无匹配的数据,该字段显示为null

inner join:
特点: 查询的所有数据是满足所有条件的数据,若没有满足所有条件的数据,则结果集为空

例题:

#查询所有公司人员的信息
SELECT u.*,r.name FROM USER U LEFT JOIN user_role r ON
u.id =r.user.id

2.sql语句的执行顺序★★

select distinct ... from ... (left/ right) join ... on ...
where ... group by ... having ... order by... limit ...

1. from  join	--> 产生虚拟表v1,产生的结果是笛卡尔积(m*n)
2. on  		    --> 根据产生的条件筛选, 产生虚拟表v2

3. left/ right join --> 若是外链接,将主表所有的数据进行补充到v2
4. where 		--> 根据查询条件,v2中进行筛选,产生虚拟表v3

5. group by		--> 对v3进行分组,产生虚拟表v4
6. having 		--> 对分组的结果v4 再次进行筛选产生虚拟表v5

7. select		--> 选择需要最终显示的字段,产生虚拟表v6
8. distinct 	--> 对显示的结果进行去重, 产生虚拟表v7
9. order by		--> 对结果某字段进行升序/降序排列
10. limit		--> 选择需要的数据进行分页展示

3.单标嵌套查询★★★★

考点:group by 能查到什么

可以查询到:

• 分组后的cola(列A),
• 与cola具有一对一关系的列
• 相关的聚合函数,
• 除此之外,其他字段都查询不到

错误示例

#获取班级的最后一名
select bj,min(score)
from stu_score 
group by bj         X

正确示例

正确的思路:

1. 查询出每个班级的最低分
2. 根据上述的查询结果,再次到stu_Score表中查询,查询某个班级最低分对应的学生是谁

# 获取班级的最后一名
# 解题思路: 
# 1.查询每个班级的最低分数
select bj,min(score) from stu_score group by bj 
# 2.查询最低分数对应的学生是谁
select ss.* from stu_scoure ss join 
(select bj,min(score) ms from stu_score group by bj)temp 
on temp.ms = ss.score and temp.bj = ss.bj

4.limit★★

between...and

# 表user，字段有：id（非自增，不连续），age，name，sex，addTime。求创建时间（addTime）在2020-
05-22至2020-06-17之间所有数据
select * from user
where addTime between '2020-05-22' and '2020-06-17'

limit 细节

第10条至第20条连续分页数据

# 基于表user，查出表中第10条至第20条连续分页数据的sql语句
select * from user limit 9,11

limit m,n    从索引m出开始筛选出n条数据 --实现分页
limit n      从第一条数据开始筛选前n条

5.ifnull()的使用和having★★

5.1 ifnull()的使用

• 若结果为null,想替换为其他值,则可以使用数据库提供的函数 ifnull(exp1,exp2)

# 查询所有学生的学号,姓名,选课数,总成绩
select 
	s.sno,s.name,count(cno)'选课数',ifnull(sum(score),0)
from 
	student s 
left join 
	score sc on s.sno = sc.sno
group by 
	s.sno

5.2 查询没有学完所有课程的学生学号，姓名

# 查询选课数小于总课程数的学生
1.查询总课程数
select count(*) from course

2.统计每个学生的选课数
select count(cno)
from student s 
left join course1  c on s.sno = c.sno
group by s.sno 

3.合并
select s.sno,s.name
from student s 
left join course1  c on s.sno = c.sno
group by s.sno 
having count(cno) < (select count(*) from course)

6.多表联查 6.1 两表联查

# 查询所有学生的信息（学号，姓名，性别，班级名称）
select stu_no ,stu_name,stu_gender,class_name
from student s join class c on s.class_id = c.id

6.2 内外联交叉★★★

• join on 和left join on

# 查询所有人(包括没有成绩的学生)的课程分数（学号，姓名，性别，班级名称，语文分数，数学分数）
select  stu_no,stu_name,stu_gender,class_name,chinese,math
from student s 
join class c on s.class_id = c.id
left join score o on o.sno = s.sno

- join...join...on...and
- join...on...left join... on

6.3 三表联查

# 查询语文分数比“张三”高的学生（学号，姓名，性别，班级名称，语文分 数）
# 解题思路
# 1.查询张三的分数
select chinese
from student s join score sc on sc.stu_id = s.id
WHERE stu_name = '张三'
# 2.查询比张三分数高的学生
select s.*,class_name,chinese
from student s
join score sc on sc.stu_id = s.id
join class c on s.class_id = c.id
where chinese > (select chinese
from student s join score sc on sc.stu_id = s.id
WHERE stu_name = '张三')

6.4 两表联查where

# 查询各科都合格（分数>=60）的学生（学号，姓名，语文分数，数学分数）
select stu_id,stu_name,chinese,math
from student s join score sc on sc.stu_id = s.id 
where chinese > 60 and math >60

6.5 两表联查having

#查询班级人数>=2的班级（班级编号，班级名称，人数）
select class_id ,class_name,count(s.stu_no) cou
from student s join class c on s.class_id = c.id
group by class_id
having cou>=2

7.思路转换 大小转换

#查询出每门课程都大于等于80分的学生信息 --> -最低分大于80
# step 1
select  s.*
from score
group by score
having min(score) >= 80
# step 2
select s.*
from score s
where name 
not in (select distinct name
        from score
        where score < 80)

8.嵌套删除

• 有一张表student，包括字段id和name，请将表中name字段中重复记录删除，只保留重复数据中的id最大的那一条数据。

# 查询每个名字的最大id
select max(id) from student  group by name

delete from student 
where id not in (select max(id) from student  group by name)

9.多字段分组★

# 删除除了自动编号不同,其它都相同的学生冗余信息
delete from student2 
where id not in(
select min(id) from student 
group by stu_id,stu_name,course_id,course_name,score)

10.Case..When★★★★★

高频考试题 :

​ case when经常和聚合函数配合使用 -- 经常考察

注意点:case when 和sum()和count()使用时,用法不同(意义不同)

• sum()配合使用,满足条件返回1,表示进行+1  else 0表示+0
• count()配合使用,返回的值只要不是null,就进行一次计数,所以使用count()必须注意:若满足条件想进行一次计数,不满足条件不计数,应写成:

count(case when ....then 1 else null end)  或 
count(case when ...then 1 end) 没有else,表示不满足条件,返回null

select match_date'比赛日期',
sum(case when result ='胜' then 1 else 0 end)'胜',
sum(case when result ='负'then 1 else 0 end)'负'
from Match_record
group by Match_date

# 获取每个班级的最后一名
select bj,min(score) m 
from stu_score
group by bj

select ss.*
from stu_score ss
join  (select bj,min(score) m 
from stu_score
group by bj)temp 
on temp.bj = ss.bj and ss.score = temp.m

【本文转自：美国服务器 https://www.68idc.cn 复制请保留原URL】