复杂SQL_01如何优化改写为长尾词？
- 内容介绍
- 相关推荐
本文共计2034个文字，预计阅读时间需要9分钟。
目录
1.外联和内联
2.SQL语句的执行顺序
3.单表嵌套查询
4.LIMIT的使用
5.IFNULL()的使用与HAVING
6.多表联查
6.1. 两表联查 6.2. 外部联交 6.3. 三表联查 6.4. 两表联查WHERE 目录- 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★★★★★
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
正确示例
正确的思路:
- 查询出每个班级的最低分
- 根据上述的查询结果,再次到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
本文共计2034个文字，预计阅读时间需要9分钟。
目录
1.外联和内联
2.SQL语句的执行顺序
3.单表嵌套查询
4.LIMIT的使用
5.IFNULL()的使用与HAVING
6.多表联查
6.1. 两表联查 6.2. 外部联交 6.3. 三表联查 6.4. 两表联查WHERE 目录- 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★★★★★
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
正确示例
正确的思路:
- 查询出每个班级的最低分
- 根据上述的查询结果,再次到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