MySql 练习题

MySql 练习题

1.创建测试数据

CREATE TABLE score(
sid int auto_increment PRIMARY KEY,
student_id int,
course_id int,
number int,
UNIQUE u1(student_id,course_id),
CONSTRAINT fk_sco_stu FOREIGN KEY (student_id) REFERENCES student(sid),
CONSTRAINT fk_sco_cou FOREIGN KEY (course_id) REFERENCES course(cid)
)ENGINE=INNODB DEFAULT charset=utf8;

**2.查询平均成绩大于60分的同学的学号和平均成绩； **

SELECT student_id,avg(number) FROM score GROUP BY student_id HAVING avg(number)>60;

3.查询所有同学的学号、姓名、选课数、总成绩；

SELECT student.sid,sname,count(course_id),sum(number)
from student
LEFT JOIN score ON score.student_id=student.sid
GROUP BY student_id ORDER BY sid asc;

4.查询姓“李”的老师的个数；

select count(tid) FROM teacher WHERE tname like ‘李%’;

5.查询没学过“叶平”老师课的同学的学号、姓名；

SELECT student.sid,student.sname
FROM student
right JOIN
(SELECT student_id
FROM score
WHERE score.student_id NOT in
(SELECT score.student_id
FROM score
WHERE score.course_id in
(SELECT cid
FROM course
where teacher_id in (SELECT tid from teacher where tname=‘叶平’)))
GROUP BY score.student_id)
as B
ON student.sid=B.student_id;

//从里到外一点一点写，套娃

6.查询“生物”课程比“物理”课程成绩高的所有学生的学号；

SELECT A.student_id FROM
(SELECT student_id,cname,number
FROM score
LEFT JOIN course ON score.course_id = course.cid
WHERE cname = ‘生物’) as A
INNER JOIN
(SELECT student_id,cname,number
FROM score
LEFT JOIN course ON score.course_id = course.cid
WHERE cname = ‘物理’) as B
ON A.student_id = B.student_id
WHERE A.number > B.number ;

//两个临时表的进行连接

7.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

SELECT student.sid,student.sname
FROM student
INNER JOIN
(SELECT A.student_id FROM
(SELECT student_id,course_id FROM score WHERE course_id = 1)AS A
INNER JOIN
(SELECT student_id,course_id FROM score WHERE course_id = 2)AS B
ON A.student_id = B.student_id)AS C
ON student.sid = C.student_id;

8.查询有课程成绩小于60分的同学的学号、姓名；

SELECT student.sid,student.sname
FROM student
INNER JOIN
(SELECT student_id
FROM score
WHERE number < 60
GROUP BY student_id)AS B
on student.sid = B.student_id;

9.查询学过“叶平”老师所教的所有课的同学的学号、姓名；

SELECT sid,sname
FROM student
INNER JOIN
(SELECT score.student_id,count(student_id)
FROM score
WHERE course_id in
(SELECT cid
FROM course
LEFT JOIN teacher ON course.teacher_id = teacher.tid
where tname = ‘叶平’)
GROUP BY student_id
HAVING count(student_id) =
(SELECT count(cid)
FROM course
LEFT JOIN teacher ON course.teacher_id = teacher.tid
where tname = ‘叶平’
GROUP BY teacher_id))AS B
ON student.sid = B.student_id;

//这个薛微有点难

先建立一个子查询，查询李平老师上的所有课的id，再以它为条件查询score表中所有上过李平老师课的学生信息（包括只上一节和所有课都上过的学生），同时以student_id为条件进行分组并记录每组学生上的课的个数；可以这样想，我已经查到的所有上李平老师的课的学生以及他们上了几节李平老师的课；那么如果学生上的课数正好等于李平老师教的课数，那么该学生就上过所有李平老师的课；所以应该再建立一个子查询查李平老师上过的课数，如果学生课数等于老师教的棵树，那么该学生即为所求。-------------------------这是我自己想到的笨方法很绕，应该有更好的。。。

10.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

SELECT sid,sname
FROM student
INNER JOIN
(SELECT A.student_id
FROM
(SELECT student_id,course_id,number
FROM score
WHERE course_id = 2)AS A
INNER JOIN
(SELECT student_id,course_id,number
FROM score
WHERE course_id = 1)AS B
ON A.student_id = B.student_id
WHERE A.number < B.number)AS C
ON student.sid = C.student_id;

11.查询没有学全所有课的同学的学号、姓名；

SELECT sid,sname
FROM student
INNER JOIN
(SELECT student_id,count(student_id)
FROM score
GROUP BY student_id
HAVING count(student_id)!=(SELECT count(cid) FROM course))AS B
ON student.sid = B.student_id;

12.查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；

SELECT sid,sname
FROM student
INNER JOIN
(SELECT A.student_id
FROM (SELECT *
FROM score
WHERE student_id != 1)AS A
WHERE A.course_id in (SELECT course_id
FROM score
WHERE student_id = 1))AS B
on student.sid = B.student_id
GROUP BY student.sid;

注意题目条件为其他同学,要先建立一张没有1号同学的临时表

13.删除学习“叶平”老师课的SC表记录；

DELETE
FROM score
WHERE course_id in ( SELECT cid
FROM course
LEFT JOIN teacher ON course.teacher_id = teacher.tid
WHERE tname = ‘叶平’);

14.查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

SELECT sid,sname
FROM student
INNER JOIN6
(SELECT student_id
FROM (SELECT *
FROM score
WHERE student_id !=2)AS A
WHERE course_id in
(SELECT course_id
FROM score
WHERE student_id = 2)
GROUP BY student_id
HAVING count(course_id) = (SELECT count(course_id)
FROM score
WHERE student_id = 2))AS B
on student.sid = B.student_id;

15.向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；

INSERT INTO
score(student_id,course_id,number)
SELECT student_id,2,
(SELECT avg(number)
FROM score
WHERE course_id = 2)
FROM score
WHERE student_id NOT in
(SELECT student_id
FROM score
WHERE course_id = 2)
GROUP BY student_id;

16.按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

SELECT D.student_id,D.语文,D.数学,D.英语,E.有效课程数,E.有效平均分
FROM
(SELECT A.student_id,A.语文,B.数学,C.英语
FROM
(SELECT student_id,number AS ‘语文’
FROM score
WHERE course_id =
(SELECT cid
FROM course
WHERE cname = ‘语文’)
ORDER BY number ASC) AS A
LEFT JOIN
(SELECT student_id,number AS ‘数学’
FROM score
WHERE course_id =
(SELECT cid
FROM course
WHERE cname = ‘数学’)
ORDER BY number ASC)AS B
ON A.student_id = B.student_id
LEFT JOIN
(SELECT student_id,number AS ‘英语’
FROM score
WHERE course_id =
(SELECT cid
FROM course
WHERE cname = ‘英语’)
ORDER BY number ASC) AS C
ON A.student_id = C.student_id) AS D
LEFT JOIN
(SELECT student_id,count(course_id) AS ‘有效课程数’,avg(number)
AS ‘有效平均分’
FROM score
WHERE course_id in
(SELECT cid
FROM course
WHERE cname in (‘语文’,‘数学’,‘英语’))
GROUP BY student_id) AS E
ON D.student_id = E.student_id;

17.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

SELECT course_id,MAX(number),MIN(number)
FROM score
GROUP BY course_id;

18.按各科平均成绩从低到高和及格率的百分数从高到低顺序；

(1)按各科平均成绩从低到高

SELECT course_id,avg(number)
FROM score
GROUP BY course_id
ORDER BY avg(number) ASC;

(2)按及格率的百分数从高到低

法一：SELECT C.course_id,(C.及格人数/C.总人数) AS ‘及格率’
FROM
(SELECT A.course_id,B.及格人数,A.总人数
FROM
(SELECT course_id,count(course_id) AS ‘总人数’
FROM score
GROUP BY course_id) AS A
LEFT JOIN
(SELECT course_id,count(number) AS ‘及格人数’
FROM score
WHERE number >= 60
GROUP BY course_id) AS B
ON A.course_id = B.course_id) AS C
ORDER BY (C.及格人数/C.总人数) DESC;

法二：SELECT course_id,number,sum(CASE WHEN number < 60 THEN 0 ELSE 1 END)/sum(1) AS ‘及格率’
FROM score
GROUP BY course_id
ORDER BY sum(CASE WHEN number < 60 THEN 0 ELSE 1 END)/sum(1) DESC;

19.课程平均分从高到低显示（现实任课老师）

SELECT A.course_id,A.AVG,teacher.tname
FROM
(SELECT course_id,avg(number) AS AVG
FROM score
GROUP BY course_id
ORDER BY avg(number) DESC) AS A
LEFT JOIN course
ON A.course_id = course.cid
LEFT JOIN teacher
ON course.teacher_id = teacher.tid;

**20.查询各科成绩前三名的记录:(不考虑成绩并列情况) **

SELECT *
FROM
(SELECT student_id,course_id,number,
(SELECT number
FROM score as s2
WHERE s2.course_id = s1.course_id
GROUP BY number
ORDER BY number DESC
LIMIT 3,1) as ‘第四名成绩’
FROM score as s1) AS B
WHERE B.number > B.第四名成绩;

//先查到第四名的成绩，将其插入表中，再把这个整体当作一个临时表，查到成绩大于第四名成绩的记录

21.查询每门课程被选修的学生数；

SELECT course_id,count(student_id)
FROM score
GROUP BY course_id;

22.查询出只选修了一门课程的全部学生的学号和姓名；

SELECT sid,sname
FROM student
WHERE sid in
(SELECT student_id
FROM score
GROUP BY student_id
HAVING count(course_id) = 1);

23.查询男生、女生的人数；

SELECT gender,count(sid)
FROM student
GROUP BY gender;

24.查询姓“张”的学生名单；

SELECT *
FROM student
WHERE sname LIKE ‘张%’;

24.查询同名同姓学生名单，并统计同名人数；

SELECT sid,sname,count(sid)
FROM student
GROUP BY sname
HAVING count(sid) > 1;

25.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

SELECT course_id,avg(number)
FROM score
GROUP BY course_id
ORDER BY avg(number) ASC,course_id DESC;

26.查询课程名称为“数学”，且分数低于60的学生姓名和分数

SELECT student.sname,C.number
FROM student
inner JOIN
(SELECT *
FROM
(SELECT *
FROM score
WHERE course_id =
(SELECT cid
FROM course
WHERE cname = ‘数学’)) AS B
WHERE B.number < 60) AS C
ON student.sid = C.student_id;

27.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；

SELECT student.sid,student.sname
FROM student
INNER JOIN
(SELECT *
FROM score
WHERE course_id = 3
and number > 80) AS B
ON student.sid = B.student_id;

28.查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

SELECT A.student_id,A.course_id,A.MaxNumber,student.sname
FROM
(SELECT student_id,course_id,MAX(number) AS MaxNumber
FROM score
WHERE course_id in
(SELECT cid
FROM course
WHERE teacher_id =
(SELECT tid
FROM teacher
WHERE tname = ‘杨艳’))
GROUP BY course_id) AS A
INNER JOIN student
on A.student_id = student.sid;

29.查询每门课程成绩最好的前两名；

SELECT *
FROM
(SELECT s1.student_id,s1.course_id,s1.number,
(SELECT s2.number
FROM score AS s2
WHERE s2.course_id = s1.course_id
ORDER BY s2.number DESC
LIMIT 2,1) AS ‘thrid’
FROM score AS s1) AS A
WHERE A.number > A.thrid;

30.检索至少选修两门课程的学生学号；

SELECT student_id
FROM score
GROUP BY student_id
HAVING count(course_id) > 1;