常用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")。
