SQL语句练习
练习环境搭建
CREATE DATABASE sql_test CHARSET='utf8';
USE sql_test;
CREATE TABLE tab_student(
stu_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生id,起始2021001',
stu_name VARCHAR(50) COMMENT '学生姓名',
stu_age INT COMMENT '学生年龄',
stu_sex CHAR(1) COMMENT '学生性别'
) COMMENT '学生表';
CREATE TABLE tab_teacher(
tea_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '教师id,起始10001',
tea_name VARCHAR(50) COMMENT '教师姓名'
) COMMENT '教师表';
CREATE TABLE tab_course(
cou_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '课程id,起始1',
cou_name VARCHAR(50) COMMENT '课程名称',
tea_id INT COMMENT '教师id',
CONSTRAINT fk_cou_tea_id FOREIGN KEY(tea_id) REFERENCES tab_teacher(tea_id)
) COMMENT '课程表';
CREATE TABLE tab_score(
stu_id INT COMMENT '学生id',
cou_id INT COMMENT '课程id',
sco_score DOUBLE(4,1) COMMENT '成绩表',
PRIMARY KEY(stu_id, cou_id),
CONSTRAINT fk_sco_stu FOREIGN KEY(stu_id) REFERENCES tab_student(stu_id),
CONSTRAINT fk_sco_cou FOREIGN KEY(cou_id) REFERENCES tab_course(cou_id)
) COMMENT '成绩表';
INSERT INTO tab_student VALUES(2021001,'赵一',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
(NULL,'钱二',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
(NULL,'孙三',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
(NULL,'李四',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
(NULL,'周五',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
(NULL,'吴六',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
(NULL,'郑七',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
(NULL,'王八',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
(NULL,'冯九',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女')),
(NULL,'陈十',CEIL(RAND()*7+18),IF((RAND()>0.5), '男' ,'女'));
/*查询学生表中数据是否正确被插入*/
SELECT * FROM tab_student;
INSERT INTO tab_teacher VALUES(10001,'褚一'),
(NULL,'卫二'),
(NULL,'卫二二'),
(NULL,'蒋三'),
(NULL,'蒋三三'),
(NULL,'蒋三三三');
/*查询老师表中数据是否正确被插入*/
SELECT * FROM tab_teacher;
INSERT INTO tab_course VALUES(1,'C语言',10001),
(NULL,'Java',10002),
(NULL,'操作系统',10003),
(NULL,'MySQL',10004),
(NULL,'计算机网络',10005),
(NULL,'数据结构',10006);
/*查询课程表中数据是否正确被插入*/
SELECT * FROM tab_course;
/*随机学生id,课程id,分数,执行多次*/
INSERT INTO tab_score VALUES(CEIL(RAND()*10+2021000),CEIL(RAND()*6),ROUND(RAND()*100,1));
/*查询分数表中数据是否正确被插入*/
SELECT * FROM tab_score;
语句练习
-- 1、查询id为1的课程比id为2的课程，成绩高的所有学生的学号
/*
思路：
1、分别查出课程id为1以及课程id为2的课程，并做笛卡尔积
2、根据要求制定条件，找出学号相同且课程1的分数大于课程2的分数的同学
*/
SELECT score1.stu_id 学生id
FROM
(SELECT stu_id,
sco_score
FROM tab_score
WHERE cou_id=1) score1,
(SELECT stu_id,
sco_score
FROM tab_score
WHERE cou_id=2) score2
WHERE score1.stu_id=score2.stu_id
AND
score1.sco_score>score2.sco_score;
-- 2、查询平均成绩大于60分的同学的学号和平均成绩
/*
思路：
1、在成绩表中根据学号进行分组
2、求出每个学号对应的平均分
3、选出平均分大于60的学生
*/
SELECT stu_id 学号,
AVG(sco_score) 平均分
FROM tab_score
GROUP BY stu_id
HAVING AVG(sco_score)>60;
-- 3、查询所有学生的学号、姓名、选课数、总成绩
/*
思路：
1、将学生表中的全部信息与成绩表中的全部信息汇总为一张表，保持学生表的完整性，借助学生id清洗数据
2、利用学生id的唯一性进行分组，每组的课程id总数正好为选课数，分数总数正好为总分数
*/
SELECT stu.stu_id 学号,
stu.stu_name 姓名,
COUNT(sco.cou_id) 选课数 ,
SUM(sco.sco_score) 总成绩
FROM (tab_student stu LEFT JOIN tab_score sco
ON stu.stu_id=sco.stu_id)
GROUP BY stu.stu_id;
-- 4、查询姓“卫”的老师的个数
/*
思路：
使用模糊查询，查询姓名第一个字是卫的老师，名字的总长不定因此应该使用%
*/
SELECT COUNT(tea_name) 卫姓老师总数
FROM tab_teacher
WHERE tea_name LIKE '卫%';
-- 5、查询没学过“褚一”老师课程的同学的学号、姓名
/*
思路：
1、找出褚一老师的教师id
2、根据教师id寻找褚一老师所教的课程id
3、将学生根据学号进行分组
4、找出分组后学生课程号组成的集合中不包含褚一老师课程的学生id
5、根据学生id在学生表中寻找数据
*/
SELECT stu_id 学号,
stu_name 姓名
FROM tab_student
WHERE stu_id IN
(SELECT stu_id
FROM
(SELECT stu_id,
cou_id
FROM tab_score
GROUP BY stu_id
HAVING (
(SELECT cou_id
FROM tab_course
WHERE tea_id=
(SELECT tea_id
FROM tab_teacher
WHERE tea_name='褚一'))) NOT IN (cou_id)) student);
-- 6、查询学过课程号1和学习过课程号2的同学的学号、姓名
/*
思路：
1、分别查出课程id为1以及课程id为2的课程，并做笛卡尔积
2、两张表中学号一致的列为同时学习id为1以及id为2课程的学生
3、根据步骤二查询出的学号在学生表中找出对应学生
*/
SELECT stu_id 学号,
stu_name 姓名
FROM tab_student
WHERE stu_id IN
(SELECT score1.stu_id
FROM
(SELECT *
FROM tab_score
WHERE cou_id=1) score1,
(SELECT *
FROM tab_score
WHERE cou_id=2) score2
WHERE score1.stu_id=score2.stu_id);
-- 7、查询学过“褚一”老师课程的同学的学号、姓名
/*
思路：
先找到褚一老师的教师id
根据教师id找到褚一老师的所教课程id
根据课程id在成绩表找到选择该课程的学生学号
最后根据学生学号在学生表中找到对应学生
*/
SELECT stu_id 学生学号,
stu_name 学生姓名
FROM tab_student
WHERE stu_id IN
(SELECT stu_id
FROM tab_score
WHERE cou_id=
(SELECT cou_id
FROM tab_course
WHERE tea_id=
(SELECT tea_id
FROM tab_teacher
WHERE tea_name='褚一')));
-- 8、查询课程号2的成绩比课程号1的成绩低的所有同学的学号、姓名
/*
思路：
1、分别查出课程id为1以及课程id为2的课程，并做笛卡尔积
2、根据要求制定条件，找出学号相同且课程2的分数低于课程1的分数的同学
*/
SELECT stu_id,
stu_name
FROM tab_student
WHERE stu_id IN
(SELECT score1.stu_id FROM
(SELECT *
FROM tab_score
WHERE cou_id=1) score1,
(SELECT *
FROM tab_score
WHERE cou_id=2) score2
WHERE score2.sco_score<score1.sco_score
AND score1.stu_id=score2.stu_id);
-- 9、查询所有课程成绩小于60分的同学的学号、姓名
/*
思路：
最大分数小于60的同学其他分数一定小于60
*/
SELECT tab_student.stu_id 学号,
stu_name 姓名
FROM tab_student,
(SELECT stu_id,
MAX(sco_score)
FROM tab_score
GROUP BY stu_id
HAVING MAX(sco_score)<60) student1
WHERE student1.stu_id=tab_student.stu_id;
-- 10、查询没有学全所有课的同学的学号、姓名
/*
先查看所有的课程一共有多少门
根据学号分组，查看学生的选课总数是否等于课程总数
*/
SELECT stu_id 学号,
stu_name 姓名
FROM tab_student
WHERE stu_id IN
(SELECT stu_id
FROM tab_score
GROUP BY stu_id
HAVING COUNT(cou_id)!=(SELECT COUNT(*)
FROM tab_course));
