常用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整理

上一篇:2. tcp stream graph


下一篇:Parsix GNU/Linux 项目宣布即将终止