数据库建表语句:
--建表
--学生表
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);
题目:
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT st.*,sc1.s_score AS "课程01",sc2.s_score AS "课程02"
FROM student st JOIN score sc1 ON st.s_id=sc1.s_id AND sc1.c_id="01"
JOIN score sc2 ON st.s_id=sc2.s_id AND sc2.c_id="02"
WHERE sc1.s_score>sc2.s_score;
注:表使用别名as可省,大小写不敏感,字段加别名as不可省,且要加双引号。 因为score表一行只有一门课成绩,所以要join两次。
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT st.s_id AS "学生编号",st.s_name AS "学生姓名",ROUND(AVG(sc.s_score),2) AS "平均成绩"
FROM student st JOIN score sc ON st.s_id=sc.s_id
GROUP BY st.s_id
HAVING ROUND(AVG(sc.s_score),2)>=60;
注:ROUND(AVG(sc.s_score),2),2的意思是保留两位小数
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
SELECT st1.s_id AS "学生编号",st1.s_name AS "学生姓名",ROUND(AVG(sc.s_score)) AS "平均成绩"
FROM student st1 JOIN score sc ON st1.s_id=sc.s_id
GROUP BY st1.s_id
HAVING ROUND(AVG(sc.s_score))<60
UNION
SELECT st2.s_id AS "学生编号",st2.s_name AS "学生姓名",0 AS "平均成绩"
FROM student st2
WHERE st2.s_id NOT IN(
SELECT DISTINCT s_id FROM score
);
5.查询所有同学的学生编号,学生姓名,选课总数,所有课程的总成绩
SELECT st.s_id,st.s_name,COUNT(sc.c_id) AS "选课总数",SUM(sc.s_score)-1 AS "总成绩"
FROM student st LEFT JOIN score sc ON st.s_id=sc.s_id
GROUP BY st.s_id;
注:因为有人没有成绩,所以用left join。同时count()函数内的内容也不能随便填,刚开始我写的“*”,发现没有成绩的也会有至少条。可以填sc.c_id或者sc.s_id
6.查询‘李’姓老师的数量
SELECT COUNT(*) FROM teacher WHERE t_name LIKE "李%";
注:这个简单, 因为只有一个表,所以t_name前面可不加表名
7.查询学过’张三‘老师授课的同学的信息
写法一:
SELECT st.* FROM student st JOIN score sc ON st.s_id=sc.s_id
JOIN course c ON sc.c_id=c.c_id
JOIN teacher t ON c.t_id=t.t_id AND t.t_name="张三";
写法二:
SELECT st1.*
FROM student st1 ,score sc ,course c,teacher t
WHERE st1.s_id=sc.s_id AND sc.c_id=c.c_id AND c.t_id=t.t_id AND t.t_name="张三"
8.查询没学过"张三"老师授课的同学的信息
注:上题我们查询了学过张三老师授课的同学,所以只要根据id使用not in排除以上同学即可。
SELECT st.* FROM student st
WHERE st.s_id NOT IN(
SELECT st.s_id FROM student st JOIN score sc ON st.s_id=sc.s_id
JOIN course c ON sc.c_id=c.c_id
JOIN teacher t ON c.t_id=t.t_id AND t.t_name="张三"
);
9.查询学过01也学过02的课程的同学的信息
SELECT st.* FROM student st JOIN score sc1 ON st.s_id=sc1.s_id AND sc1.c_id="01"
JOIN score sc2 ON st.s_id=sc2.s_id AND sc2.c_id="02";
注:题目意思要求两门课都学过的同学的信息,所以使用内连接。因为score表一行只有一门课所以要把两张score表内联起来。语句倒是很简单。
10.查询学过01但是没有学过02的同学的信息
自己写的:
SELECT st.* FROM student st
JOIN score sc1 ON st.s_id=sc1.s_id AND sc1.c_id="01" AND st.s_id NOT IN(
SELECT st.s_id FROM student st JOIN score sc ON st.s_id=sc.s_id AND sc.c_id="02"
);
思路:首先内部先查出选了02的同学的s_id。外部查出选了01的同学的s_id,然后排除内部查询的结果。
发现:not in内部其实还可以简化一下,没必要联表
SELECT st.* FROM student st
JOIN score sc1 ON st.s_id=sc1.s_id AND sc1.c_id="01" AND st.s_id NOT IN(
SELECT s_id FROM score WHERE c_id="02"
);
11.查询没有学全所有课程的同学的信息
SELECT st.* FROM student st JOIN score sc ON st.s_id=sc.s_id
GROUP BY sc.s_id
HAVING COUNT(*) = (SELECT COUNT(c.c_id) FROM course c);
思路:选课数量=所有课程数量。先把student表和score表内联成一个表,接着按照s_id分组,然后统计每组的数量是否等于所有课程数量,使用count(*)=全部课程数量。看了网上答案基本都是事先已知课程数量,这有点不严谨。
12.查询至少有一门课与学号为’01‘的同学所学相同的同学的信息
SELECT DISTINCT st.*
FROM student st JOIN score sc
ON st.s_id=sc.s_id AND sc.c_id IN (
SELECT sc.c_id FROM score sc WHERE sc.s_id="01"
) AND sc.s_id NOT IN ("01");
思路:查询出‘01’的同学选了什么课,然后使用in即可表示“至少”。最后记得排除”01“自身。
13.查询和01号的同学学习的课程完全相同的其他同学的信息
思路:这题有难度呀。我的做法是双重否定。最内层否定:获取所选课程包含01同学没选的课程的同学。次内层否定:排除这些同学。即剩下所选课程包含在01同学所选课程的之中的同学。如:01选了“A”,“B”,"C"课程,则剩下的这些同学不能选ABC课程之外的课程且所选的课程数<=01选的课程数。所以还需要加上一个个数判断,留下满足选的课程数等于01同学所选课程数的同学,这就是group by和having count的作用。
SELECT DISTINCT st.*
FROM student st JOIN score sc ON st.s_id=sc.s_id
WHERE st.s_id NOT IN (
SELECT s_id FROM score WHERE c_id NOT IN(
SELECT c_id FROM score WHERE s_id="01"
)
)
GROUP BY s_id
HAVING COUNT(*)=(SELECT COUNT(*) FROM score WHERE s_id="01");
14.查询没学过‘张三’老师教的任一门课程的学生姓名
思路:查找出选过张三课程的,然后排除。
SELECT st.s_name FROM student st WHERE st.s_id NOT IN(
SELECT st.s_id FROM student st JOIN score sc ON st.s_id=sc.s_id
JOIN course c ON sc.c_id=c.c_id
JOIN teacher t ON c.t_id=t.t_id AND t.t_name="张三"
);
15.查询两门及以上不及格课程的同学的学号姓名及平均成绩
思路:
SELECT st.s_id,st.s_name,ROUND(AVG(sc.s_score)) AS "平均分"
FROM student st JOIN score sc ON st.s_id=sc.s_id
WHERE st.s_id IN (
SELECT sc.s_id FROM score sc
WHERE sc.s_score<60
GROUP BY sc.s_id
HAVING COUNT(*)>=2
)
GROUP BY sc.s_id;
16.检索01分数小于60,按分数降序排列的学生信息
SELECT st.*
FROM student st JOIN score sc ON st.s_id=sc.s_id
WHERE sc.c_id="01" AND sc.s_score<60
ORDER BY sc.s_score DESC;
17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
一次性写了17道题目,剩下有时间再补充~