本篇文章目的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);
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
SELECT s1.s_id, AVG(s1.s_score) FROM Score s1 GROUP BY s1.s_id HAVING AVG(s1.s_score) > 60
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
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)
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
select *, case when sc1.s_score >= 85 then 'A' when sc1.s_score >= 60 then 'B' else 'C' end AS '等级' from Score sc1
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;
以Student表为例,索引为PRIMARY KEY (s_id
)
索引失效sql: select * from Student where s_id = 01 索引失效sql: select * from Student where s_id = '01'
以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")。