常用SQL整理
2021/9/11 2:04:47
本文主要是介绍常用SQL整理,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
本篇文章目的1.整理和练习常用SQL的编写以应付考试试题。2.也想以次为入口方便记录以后对SQL的理解,故该文章会以不同的技术点进行标题分类,以便日后追加
其中SQL脚本为借鉴他人的
CREATE TABLE `Student` ( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY (`s_id`) );
CREATE TABLE `Course` ( `c_id` VARCHAR(20), `c_name` VARCHAR(20) NOT NULL DEFAULT '', `t_id` VARCHAR(20) NOT NULL, PRIMARY KEY (`c_id`) );
CREATE TABLE `Teacher` ( `t_id` VARCHAR(20), `t_name` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY (`t_id`) );
CREATE TABLE `Score` ( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY (`s_id` , `c_id`) );
预备数据脚本
insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'); insert into Student values('03' , '孙风' , '1990-05-20' , '男'); insert into Student values('04' , '李云' , '1990-08-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '女'); insert into Student values('06' , '吴兰' , '1992-03-01' , '女'); insert into Student values('07' , '郑竹' , '1989-07-01' , '女'); insert into Student values('08' , '王菊' , '1990-01-20' , '女');
insert into Course values('01' , '语文' , '02'); insert into Course values('02' , '数学' , '01'); insert into Course values('03' , '英语' , '03');
insert into Teacher values('01' , '张三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五');
insert into Score values('01' , '01' , 80); insert into Score values('01' , '02' , 90); insert into Score values('01' , '03' , 99); insert into Score values('02' , '01' , 70); insert into Score values('02' , '02' , 60); insert into Score values('02' , '03' , 80); insert into Score values('03' , '01' , 80); insert into Score values('03' , '02' , 80); insert into Score values('03' , '03' , 80); insert into Score values('04' , '01' , 50); insert into Score values('04' , '02' , 30); insert into Score values('04' , '03' , 20); insert into Score values('05' , '01' , 76); insert into Score values('05' , '02' , 87); insert into Score values('06' , '01' , 31); insert into Score values('06' , '03' , 34); insert into Score values('07' , '02' , 89); insert into Score values('07' , '03' , 98);
一:常用SQL篇
1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。
select * from (select s1.s_id,s1.s_score from Score s1 where s1.c_id = '01') t1 left join (select s2.s_id, s2.s_score from Score s2 where s2.c_id = '02') t2 on t1.s_id = t2.s_id where t1.s_score >t2.s_score
2.查询平均成绩大于60分的学生的学号和平均成绩
SELECT s1.s_id, AVG(s1.s_score) FROM Score s1 GROUP BY s1.s_id HAVING AVG(s1.s_score) > 60
3.查询没学过“张三”老师课的学生的学号、姓名
SELECT * FROM Student st1 LEFT JOIN (SELECT st.s_id FROM Student st LEFT JOIN Score sc ON st.s_id = sc.s_id WHERE sc.c_id = '01') t1 ON st1.s_id = t1.s_id where t1.s_id is null
4.查询每科成绩前3名的学生号
select * from Score sc1 where (select sc2.s_score from Score sc2 where sc1.c_id = sc2.c_id order by sc2.s_score desc limit 2,1) <= sc1.s_score order by sc1.c_id,sc1.s_score desc;
备注1:where后的select查询为“”有于查询每科成绩第三名的分数(排序后取第三个)
备注2:查询计划(explain)
5.每科成绩的最高分
select c_id,max(sc1.s_score) from Score sc1 group by sc1.c_id
备注--问题分类:这类查询为分组后搜索组内数据问题,类似还有求平均数,最小值,分组后的子集。这类问题可通过HAVING语句利用聚合函数求解。
备注--聚合函数语法:aggregateName,其中aggregateName为聚会函数名称,例如常用:AGV,MAX,COUNT,GROUP_CONCAT,SUM.....。其中e x p r为布尔表达式(1:true,0:false)
举例1:每科成绩80分以上的人数
select sum(sc1.s_score >= 80) from Score sc1 group by sc1.c_id
6.根据(100-85]-A,(85-60]-B,(60-0]-C转换分数
select *, case when sc1.s_score >= 85 then 'A' when sc1.s_score >= 60 then 'B' else 'C' end AS '等级' from Score sc1
7.根据(100-85],(85-60],(60-0]为分数区间统计每科的人数
select *, sum(case when sc1.s_score >= 85 then 1 else 0 end) as 'A', sum(case when sc1.s_score < 85 and sc1.s_score >= 60 then 1 else 0 end)AS 'B', sum(case when sc1.s_score < 60 then 1 else 0 end) AS 'C' from Score sc1 group by sc1.c_id;
二:索引--索引失效篇
1.参数类型与字段类型不一致
以Student表为例,索引为PRIMARY KEY (s_id
)
索引失效sql: select * from Student where s_id = 01 索引失效sql: select * from Student where s_id = '01'
2.组合索引失效问题
以Score表为例,PRIMARY KEY (s_id
, c_id
)
索引失效sql: select * from Score sc1 where c_id = '01'; #以第二个属性为条件
说明:以多个属性联合建立联合索引,会以联合索引的顺序计算权重值
故1:当我们以顺序第一个属性进行查询时,可用到索引,查询类型type=ref(相当于like "value%"即开头精确匹配后模糊匹配),like查询type=rank.
故2:当我们以顺序非第一个属性进行查询时,索引失效,查询类型type=ALL(相当于like "%value%"即模糊匹配)。
故3:当我们以全部属性进行查询时,查询类型type=const(相当于key = "value")。
三:SQL优化篇
这篇关于常用SQL整理的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-10-01基于Python+Vue开发的医院门诊预约挂号系统
- 2024-10-01基于Python+Vue开发的旅游景区管理系统
- 2024-10-01RestfulAPI入门指南:打造简单易懂的API接口
- 2024-10-01初学者指南:了解和使用Server Action
- 2024-10-01Server Component入门指南:搭建与配置详解
- 2024-10-01React 中使用 useRequest 实现数据请求
- 2024-10-01使用 golang 将ETH账户的资产平均分散到其他账户
- 2024-10-01JWT用户校验课程:从入门到实践
- 2024-10-01Server Component课程入门指南
- 2024-09-30Dnd-Kit学习:新手快速入门指南