基础知识学习
基础知识学习网站：w3school
经典SQL习题50道
一些SQL语句常识
1.一定要记住，SQL 对大小写不敏感！
2.分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的语句。如果您使用的是 MS Access 和 SQL Server 2000，则不必在每条 SQL 语句之后使用分号。
3.可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)
SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。
查询和更新指令构成了 SQL 的 DML 部分：
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据
SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。
SQL 中最重要的 DDL 语句:
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
4.NULL 用作未知的或不适用的值的占位符，无法比较 NULL 和 0，它们是不等价的。且也无法使用比较运算符来测试 NULL 值，比如 =, <, 或者 <>。我们必须使用 IS NULL 和 IS NOT NULL 操作符。
创建习题中需要的表格
（创建表是基于MySQL数据库，后面的习题解答也是，如果没有安装MySQL数据库的，我其他博客中有安装教程）
1.建立学生表
–SId 学生编号, Sname 学生姓名, Sage 出生年月, Ssex 学生性别
create table Student (SId varchar(10), Sname varchar(10), Sage datetime, Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
2.建立课程表
–CId 课程编号, Cname 课程名称, TId 教师编号
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
3.建立教师表
–TId 教师编号, Tname 教师姓名
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
4.建立成绩表
–SId 学生编号, CId 课程编号, score 分数
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
SQL50道习题及答案
1 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
select sd.*, c.score from student as sd, (select a.sid,a.score from (select * from sc where cid='01') a,
(select * from sc where cid='02') b where a.sid=b.sid and a.score > b.score) as c where sd.sid=c.sid;
输出结果如下：
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select a.sid, a.sname,b.avg from student as a, (select sid, avg(score) as avg from sc group by sid) as b where
a.sid=b.sid and b.avg>=60;
输出结果如下：
3.查询在 SC 表存在成绩的学生信息
法一：
select sd.* from student sd, (select distinct(sc.sid) as sid from sc) as a where sd.sid=a.sid;
法二
select student.* from student where sid in (select distinct(sid) from sc);
输出结果如下：
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没选课和成绩的显示为 null )
步骤1选择已选课和有成绩学生的sid，选课总数tcid，总成绩tscore
select sid, count(cid) as tcid, sum(score) as tscore from sc group by sid;
步骤2结合步骤1写出最终代码
select sd.sid as 学生编号, sd.sname as 学生姓名, a.tcid as 选课总数, a.tscore as 总成绩 from student as sd left join
(select sid, count(cid) as tcid, sum(score) as tscore from sc group by sid) as a on sd.sid=a.sid;
输出结果如下：
5.查询「李」姓老师的数量
select count(tname) as '李姓教师数量' from teacher where tname like '李%';
输出结果如下：
6.查询学过「张三」老师授课的同学的信息
法一：四张表的连接，一层层拆分下去
select sd.* from student as sd ,(select sid from sc ,(select cid from course where tid=(select tid from teacher where tname='张三')) as a where sc.cid=a.cid) as b where sd.sid=b.sid;
法二：直接多表连接找到上张三老师课的同学Sid
select Student.* from Student where Sid in (select Sid from SC, Course, Teacher where Teacher.Tname = '张三' and Teacher.Tid = Course.Tid and Course.Cid = SC.Cid);
输出结果如下：
7.查询没有学全所有课程的同学的信息
select Student.* from Student where Sid not in (select Sid from (select Sid, count(distinct Cid) as s_course_cnt from SC group by Sid) a, (select count(distinct Cid) as tot_course_cnt from Course) b where a.s_course_cnt = b.tot_course_cnt);
输出结果如下：
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select Student.* from Student where Sid in (select Sid from SC where Cid in
(select Cid from SC where Sid = '01') and Sid <> '01');
9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select Student.* from Student where Sid in (select Sid from (select Sid,count(Cid) as scid from SC group by Sid) as a, (select count(Cid) as scid from SC where Sid=‘01’) as b where a.scid=b.scid and a.sid <>‘01’ and a.Sid not in (select Sid from SC where Cid not in (select Cid from SC where Sid = ‘01’ ));
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select Sname from Student where Sid not in (select Sid from SC where Cid in
(select c.Cid from Teacher as t, Course c where t.Tname='张三' and c.Tid=t.Tid));
输出结果如下：
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
选取不及格同学的数据
select Sid, Cid, Score from SC where Score < 60;
选取有至少两门都不及格同学的数据
select Sid ,avg(Score) as avg_score from (select Sid, Cid, Score from SC where Score < 60) as a group by a.Sid having count(a.Cid)>1;
最总选取结果
select b.Sid ,b.Sname, c.avg_score from Student b, (select Sid ,avg(Score) as avg_score from (select Sid, Cid, Score from SC where Score < 60) as a group by a.Sid having count(a.Cid)>1) c where b.Sid=c.Sid;
输出结果如下：
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select Student.*, Cid, score from Student, SC where score < 60 and Cid = '01' and SC.Sid = Student.Sid order by score desc;
输出结果如下：
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select SC.*, avg_score from SC right join (select Sid, avg(score) as avg_score from SC group by Sid ) a on SC.Sid = a.Sid order by avg_score desc;
输出结果如下:
14. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率（及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select SC.Cid as '课程编号', Course.Cname as '课程名称', max(score) as '最高分', min(score) as '最低分', avg(score) as '平均分', sum(case when score >= 60 then 1 else 0 end)/count(score) as '及格率', sum(case when score >= 70 and score < 80 then 1 else 0 end)/count(score) as '中等率',sum(case when score >= 80 and score < 90 then 1 else 0 end)/count(score) as '优良率',sum(case when score >= 90 then 1 else 0 end)/count(score) as '优秀率' from SC, Course where SC.Cid=Course.Cid group by SC.Cid;
输出结果如下：
14.1要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select Cid, count(Sid) as '选修人数' from SC group by Cid order by count(Sid) desc;
输出结果如下：
15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
步骤1：
法一：（自写count法）
select * from SC a left join SC b on a.Cid=b.Cid and a.score<b.score;
输出内容是:
循环a表中的全部行, 与b表第一行比, 显示出满足条件的行, 行的列包括b第一行内容循环a表中的全部行, 与b表中第二行比, 显示出满足条件的行, 行的列包括b第二行内容……选好a表中的全部行, 与b表中最后一行比。
输出部分结果如下：
步骤2：
select * from sc a left join sc b on a.cid = b.cid and a.score < b.score order by a.sid,a.cid;
输出部分结果如下：
由输出结果可以看出，01号同学是学霸，3科成绩没人比他高的，02号同学01科目比他高的有3个（01、05、03），02科目比他高的有4个（05、01、03、07），03科目比他高的有2个（01、07），剩下的依次类推；
步骤3：
01学生的01课程分数为80分， 因为没有人比他高，所以， b. score都是null。那01 学生的01课程80分自然就是排名第一。也就是count(b.score) + 1；
02学生的01课程为70分, 这个前缀的行数是3.。证明整张sc表中, 有三个该课程的学生比他分数高, 那他的排名自然就是 3+1 =4。现在明白排名的原理了，就可以做题了。
select a.cid, a.sid, a.score, count(b.score)+1 as rank1 from sc as a left join sc as b on a.score<b.score and a.cid = b.cid group by a.cid, a.sid order by a.cid, rank1;
注：如果使用Mysql数据库，代码中不能是count(b.score)+1 as rank，因为rank本身就是一个函数，不能用作别名。
法二：rank()函数
select cid, sid, score, rank() over (partition by cid order by score desc) as rank1 from sc;
输出结果相同，如下：
15.1 按各科成绩进行排序，并显示排名，Score 重复时合并名次
法一：（自写count法，通用，不限数据库）
select a.Cid, a.Sid, a.score ,count(b.score)+1 rank1 from sc a left join sc b on a.cid = b.cid and (a.score < b.score or (a.score = b.score and a.sid > b.sid)) group by a.cid,a.sid order by a.cid,rank1;
法二：函数row_number()
select cid, sid, score, row_number() over (partition by cid order by score desc) as rank1 from sc;
输出结果相同，如下：
注：row_number()函数排序相同时不顾重复数，意思是出现1、2、3、4这样的排序结果；rank()函数排序相同时会重复，但总数不会变，意思是出现1、1、3这样的排序结果；还有一个dense_rank()函数，它可以在排序相同时会重复，总数会变少，意思就是会出现1、1、2这样的排序结果。
16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select a.Sid, a.总成绩, count(b.总成绩)+1 as rank1 from (select Sid, sum(score) as '总成绩' from SC group by Sid order by sum(score) desc) a left join (select Sid, sum(score) as '总成绩' from SC group by Sid order by sum(score) desc) b on a.总成绩<b.总成绩 or (a.总成绩 = b.总成绩 and a.Sid > b.Sid) group by a.Sid, a.总成绩 order by a.Sid;
输出结果如下：
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select a.Sid, a.总成绩, count(b.总成绩)+1 as rank1 from (select Sid, sum(score) as '总成绩' from SC group by Sid order by sum(score) desc) a left join (select Sid, sum(score) as '总成绩' from SC group by Sid order by sum(score) desc) b on a.总成绩<b.总成绩 group by a.Sid, a.总成绩 order by a.Sid;
输出结果如下：
17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比(和14类似)
select SC.Cid, Course.Cname, sum(case when score >= 85 then 1 else 0 end) / count(score) as '[100-85]', sum(case when score >= 70 and score < 85 then 1 else 0 end) / count(score) as '[85-70]', sum(case when score >= 60 and score < 70 then 1 else 0 end) / count(score) as '[70-60]', sum(case when score >= 0 and score < 60 then 1 else 0 end) / count(score) as '[60-0]' from SC, Course where SC.Cid = Course.Cid group by Cid;
输出结果如下：
18. 查询各科成绩前三名的记录
法一：
(select Cid, score from SC where Cid='01' order by score desc limit 3) union all (select Cid,score from SC where Cid='02' order by score desc limit 3) union all
(select Cid, score from SC where Cid='03' order by score desc limit 3) ;
输出结果如下:
法二：
select * from (select a.Cid, a.Sid, a.score ,count(b.score)+1 rank1 from sc a left join sc b on a.cid = b.cid and (a.score < b.score or (a.score = b.score and a.sid > b.sid)) group by a.cid,a.sid order by a.cid,rank1) c where c.rank1 in (1, 2, 3);
输出结果如下：
19.查询出只选修两门课程的学生学号、姓名
select a.Sid, b.Sname, count(a.Cid) as '选修课程数' from SC a,Student b where a.Sid=b.Sid group by a.Sid having count(a.Cid)=2;
输出结果如下：
20.查询男生、女生人数
select Ssex as '性别', count(Ssex) as '人数' from Student group by Ssex;
输出结果如下：
21.查询名字中含有「风」字的学生信息
select * from Student where Sname like '%风%';
输出结果如下：
22.查询同名同姓学生名单，并统计同名人数
select Sname, count(Sname) '同名人数' from Student group by Sname having count(Sname) > 1;
输出结果如下：
23.查询 1990 年出生的学生名单
select * from Student where year(Sage) = 1990;
输出结果如下：
法二：（提取字符串的方法）
select * from Student where substring(Sage, 1, 4)=1990;
select * from Student where left(Sage, 4)=1990;
select * from Student where substring(Sage, -19, 4) = 1990;
输出结果如上。
24.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select Cid, avg(score) as avg_score from SC group by Cid order by avg_score desc, Cid;
输出结果如下：
25. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select a.sid, b.sname, avg(a.score) as avg_score from sc a, student b where a.sid=b.sid group by a.sid having avg(a.score) >= 85;
输出结果如下：
26.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select b.sname, a.score from sc a, student b where a.cid = (select cid from course where cname = '数学') and a.score < 60 and a.sid = b.sid;
输出结果如下：
27. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select a.*, b.cid, b.score from student a left join sc b on a.sid=b.sid;
输出结果如下：
28.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select a.sname, b.cname, c.score from student a, course b, (select sid, cid, score from sc where score > 70) c where a.sid=c.sid and b.cid=c.cid;
29.查询不及格的学号及课程
select sid, cid from sc where score < 60;
输出结果如下:
30.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号、姓名和分数
select a.sid, b.sname, a.score from sc a, student b where a.cid = 01 and a.sid = b.sid and a.score >= 80;
输出结果如下：
31.求每门课程的学生人数
select Cid, count(Sid) from SC group by Cid;
32.在成绩不重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
法一：
select c.*, d.score as max_score from student c, sc d where c.sid = d.sid and d.cid = (select a.cid from course a, teacher b where a.tid = b.tid and b.tname = '张三') order by d.score desc limit 1;
法二：
select c.*, max(d.score) as max_score from student c, sc d where c.sid = d.sid and d.cid = (select a.cid from course a, teacher b where a.tid = b.tid and b.tname = '张三') ;
输出结果相同，如下：
33. 在成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select Student.*, SC.score from Student, SC where Student.Sid = SC.Sid and score = (select SC.score from SC, Course, Teacher where SC.Cid = Course.Cid and Course.Tid = Teacher.Tid and Teacher.Tname = '张三 order by SC.score desc
limit 1);
34.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select Sid, Cid, score from SC where score in (select score from SC group by score having count(distinct Cid) > 1);
输出结果如下：
35.查询每门课程成绩最好的前两名
select a.*, count(b.score) + 1 as rank1 from SC a left join SC b on a.Cid = b.Cid and a.score < b.score group by a.Cid, a.score, a.Sid having count(b.score) < 2 order by a.cid, rank1;
输出结果如下：
36. 统计每门课程的学生选修人数（超过 5 人的课程才统计）
select Cid, count(Sid) as '选修人数' from SC group by Cid having count(Sid) > 5;
输出结果如下:
37.检索至少选修两门课程的学生学号
select sid from sc group by sid having count(distinct cid) >= 2;
输出结果如下：
38.查询选修了全部课程的学生信息
select a.* from student a, sc b where a.sid = b.sid group by b.sid having count(distinct b.cid) = (select count(distinct cid) from course);
输出结果如下:
39.查询各学生的年龄，只按年份来算
select sid, sname, year(curdate())-year(sage) as age from student;
输出结果如下：
40.按照出生日期来算，当前月日 < 出生年月的月日，则年龄减一
select sid, sname, timestampdiff(year, sage, curdate()) age from student;
输出结果如下：
41.查询本周过生日的学生
select * from student where weekofyear(sage) = weekofyear(curdate());
输出结果（与当前日期有关，可能你查询的结果就不是空集）如下：
42.查询下周过生日的学生
select * from student where weekofyear(sage) = weekofyear(curdate()) + 1;
43.查询本月过生日的学生
select * from student where month(sage) = month(curdate());
输出结果如下：
44.查询下下个月过生日的学生
select * from student where month(sage) = month(curdate()) + 2;
输出结果如下：
45.查询不及格数最少的课程名称、代课老师姓名及不及格数目
select b.cname as '课程名称', c.tname as '教师姓名', d.不及格数目 from course b, teacher c, (select cid, min(low_score) as '不及格数目' from (select a.cid, count(a.score) as low_score from (select cid, score from sc where score < 60) a group by a.cid) q) d where b.cid = d.cid and b.tid = c.tid;
输出结果如下：
46.查询同姓氏学生的信息
select * from student where substring(sname, 1, 1) in (select a.xing from (select substring(sname, 1, 1) as xing from student) a group by xing having count(a.xing) > 1);
输出结果如下：
47.查询同性别同姓氏学生的信息
select a.* from student a, (select a.xing, a.ssex from (select substring(sname, 1, 1) as xing, ssex from student) a group by xing, ssex having count(a.xing) > 1) b where substring(a.sname, 1, 1) =b.xing and a.ssex=b.ssex;
输出结果如下：
更多推荐
SQL学习之经典50题--详细的求解答案过程和结果输出
发布评论