每一条语句都认真编写与调试,我把建库建表插入数据一同整理了,方便各位调试与练习,但是难免不了存在纰漏,感谢大家的指正与理解!觉的写的不错的小伙伴儿,一键三连支持一下,后期会有持续更新!!抱拳了罒ω罒
1. 创建数据库stu_test2
CREATE DATABASE stu_test2;
USE stu_test2;
SHOW TABLES ;
2. 分别创建四个数据表
# 学生信息表
CREATE TABLE Student(
s_no INT, # 学号
sname VARCHAR(20), # 姓名
sage INT, # 年龄
sex VARCHAR(20) # 性别
);
# 课程表
CREATE TABLE Course(
c_no INT, # 课程号
cname VARCHAR(20), # 课程名
t_no INT # 任课老师编号
);
# 分数表
CREATE TABLE Sc(
s_no INT, # 学号
c_no INT, # 课程号
score INT # 分数
);
# 教师信息表
CREATE TABLE Teacher(
t_no INT, # 教师编号
tname VARCHAR(20) # 教师姓名
)
3. 分别向四个数据表插入数据
INSERT INTO Student VALUES
(1001,'刘一',18,'男'),
(1002,'钱二',19,'女'),
(1003,'张三',17,'男'),
(1004,'李四',18,'女'),
(1005,'王五',17,'男'),
(1006,'赵六',19,'女');
INSERT INTO Course VALUES
(2001,'语文',1 ),
(2002,'数学',2 ),
(2003,'英语',3 ),
(2004,'物理',4 );
INSERT INTO Sc VALUES
(1001,2001,56),
(1001,2002,78),
(1001,2003,67),
(1001,2004,58),
(1002,2001,79),
(1002,2002,81),
(1002,2003,92),
(1002,2004,68),
(1003,2001,91),
(1003,2002,47),
(1003,2003,88),
(1003,2004,56),
(1004,2002,88),
(1004,2003,90),
(1004,2004,93),
(1005,2001,46),
(1005,2003,78),
(1005,2004,53),
(1006,2001,35),
(1006,2002,68),
(1006,2004,71);
INSERT INTO Teacher VALUES
(1,'叶平' ),
(2,'贺高' ),
(3,'杨艳' ),
(4,'周磊' );
4. 重点题目练习
# 1、查询每门课程被选修的学生数
SELECT c_no,COUNT(1) FROM sc GROUP BY c_no
# 2、查询男生、女生人数
SELECT sex ,COUNT(1)FROM student GROUP BY sex
# 3、查询姓“张”的学生名单
SELECT * FROM student WHERE sname LIKE '张%'
# 4、查询同名同性学生名单,并统计同名人数。
SELECT * ,COUNT(1)FROM student GROUP BY sname HAVING COUNT(1) > 1
# 5、查询2003年出生的学生名单
SELECT * FROM student WHERE sage = YEAR(CURDATE()) - 2003
# 6、查询不及格的课程,并按课程号从大到小排序。
SELECT * FROM sc WHERE score < 60 ORDER BY c_no DESC
# 7、检索至少选修两门课程的学生学号。
SELECT s_no FROM Sc GROUP BY s_no HAVING COUNT(*)>2;
# 8、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分。
SELECT c_no 课程ID ,MAX(score)最高分,MIN(score)最低分
FROM sc GROUP BY c_no;
# 9、查询课程平均分从高到低显示。
SELECT AVG(score) avg_score FROM sc
GROUP BY c_no
ORDER BY avg_score DESC;
# 10、查询姓李的老师的个数。
SELECT COUNT(1)FROM teacher WHERE tname LIKE '李%'
# 11、删除“叶平”老师课的sc表记录。
DELETE FROM sc
WHERE c_no =(SELECT c_no FROM teacher,course
WHERE teacher.t_no = course.t_no AND tname = '叶平')
# 12、向sc表中插入一些记录:没有上过编号“2003”课程的同学学号和分数
INSERT INTO Sc SELECT s_no,2003,0 FROM Student
WHERE s_no NOT IN (SELECT s_no FROM Sc WHERE c_no='2003');
# 13、查询出只选修了两门课程的全部学生的学号和姓名
SELECT student.s_no,sname FROM sc,student
WHERE sc.s_no = student.s_no GROUP BY s_no HAVING COUNT(1) = 2
# 14、查询所有同学的学号、姓名、选课数、总成绩。注意:*和1的区别
SELECT p.s_no,sname,COUNT(1),SUM(score) FROM student p,sc t
WHERE p.s_no = t.s_no GROUP BY s_no
# 15、查询每门课程的平均成绩,结果按平均成绩升序排列,
平均成绩相同时,按课程号降序排列。
SELECT c_no ,AVG(score)FROM Sc GROUP BY c_no
ORDER BY AVG(score) ASC,c_no DESC;
# 16、查询平均成绩大于85的所有学生的学号,姓名和平均成绩
SELECT student.s_no ,sname,AVG(score)FROM sc,student
WHERE sc.s_no = student.s_no GROUP BY s_no
HAVING AVG(score) > 85 ;
# 17、查询课程名称为“英语”且分数低于60的学生姓名和分数
SELECT sname ,score FROM student,sc,course
WHERE student.s_no = sc.s_no AND sc.c_no = course.c_no
AND cname = '英语' AND score < 60
# 18、查询所有学生的选课情况
SELECT student.s_no,sname,cname FROM student,sc,course
WHERE student.s_no=sc.s_no AND sc.c_no=course.c_no;
# 19、查询课程编号为2003且课程成绩在80分以上的学生的学号和姓名。
SELECT student.s_no,sname FROM sc,student
WHERE sc.s_no =student.s_no AND c_no = 2003 AND score > 80
# 20、求选修了课程的学生人数。
SELECT COUNT(DISTINCT(s_no))FROM sc
# 21、查询每门课程最好的前两名。
SELECT * FROM sc a
WHERE (SELECT COUNT(DISTINCT score) FROM sc b
WHERE a.c_no = b.c_no AND b.score >= a.score) <= 2
ORDER BY a.c_no
# 22、查询全部学生都选修的课程的课程号和课程名。
SELECT course.c_no,cname FROM sc,course
WHERE sc.c_no = course.c_no GROUP BY c_no
HAVING COUNT(1) >= (SELECT COUNT(1) FROM student)
# 23、查询两门及以上不及格课程的同学的学号及其平均成绩。
SELECT s_no,AVG(score) FROM sc
WHERE s_no IN
(SELECT s_no FROM sc WHERE score < 60 GROUP BY s_no
HAVING COUNT(1) >= 2) GROUP BY s_no
#24、查询所有课程成绩小于60分的同学的学号、姓名。
SELECT s_no,sname FROM student
WHERE s_no NOT IN (SELECT DISTINCT(s_no) FROM sc WHERE score > 60)
# 25、查询没有学全所有课的同学的学号、姓名。
SELECT s_no,sname FROM student WHERE s_no IN
(SELECT s_no FROM sc GROUP BY s_no
HAVING COUNT(1) < (SELECT COUNT(1) FROM course))
# 26、查询“2001”课程比“2002”课程成绩高的所有学生的学号。
SELECT p.s_no FROM (SELECT * FROM sc WHERE c_no = 2001) p,
(SELECT * FROM sc WHERE c_no = 2002) t
WHERE p.s_no = t.s_no AND p.score > t.score
# 27、查询平均成绩大于60分的同学的学号和平均成绩。
SELECT s_no,AVG(score) FROM Sc GROUP BY s_no HAVING AVG(score)>60;
# 28、查询没学过“叶平”老师课的同学的学号、姓名
SELECT s_no,sname FROM student
WHERE s_no NOT IN (SELECT DISTINCT(s_no) FROM sc,teacher,course
WHERE tname = '叶平' AND teacher.t_no = course.t_no
AND sc.c_no=course.c_no)
# 29、查询学过“2001”并且也学过编号“2002”课程的同学的学号、姓名。
SELECT p.s_no,sname FROM (SELECT s_no FROM sc WHERE c_no = 2001)p,
(SELECT s_no FROM sc WHERE c_no = 2002)t, student
WHERE p.s_no = t.s_no AND p.s_no = student.s_no
# 30、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
SELECT DISTINCT student.s_no,sname FROM student,sc
WHERE student.s_no = sc.s_no AND c_no IN
(SELECT c_no FROM sc WHERE s_no = 1001 )
# 31、把“sc”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩。
# [坑](https://blog.csdn.net/u012767761/article/details/84997962)
UPDATE sc SET score = (SELECT p.id FROM (SELECT AVG(score) id
FROM sc,course,teacher
WHERE teacher.t_no = course.t_no AND course.c_no = sc.c_no
AND teacher.tname = '叶平' ) AS p )
WHERE c_no = (SELECT c_no FROM course,teacher
WHERE teacher.t_no = course.t_no AND teacher.tname = '叶平' )
# 32、查询学习过“1005”号同学的所有课程的其他同学学号和姓名。
SELECT sc.s_no,sname FROM sc,student
WHERE sc.s_no = student.s_no AND student.s_no != 1005
AND c_no IN (SELECT c_no FROM sc WHERE s_no = 1005)
GROUP BY s_no HAVING COUNT(1) =
(SELECT COUNT(1)FROM sc WHERE s_no = 1005)
# 33、统计各科成绩,各分数段人数:
课程ID,课程名称,【100-85】,【85-70】,【70-60】,【<60】
SELECT sc.c_no,cname,
COUNT(CASE WHEN score > 85 AND score <= 100 THEN 1 END) '【100-85】',
COUNT(CASE WHEN score > 70 AND score <= 85 THEN 1 END) '【85-70】',
COUNT(CASE WHEN score > 60 AND score <= 70 THEN 1 END) '【70-60】',
COUNT(CASE WHEN score <= 60 THEN 1 END) '【<60】'
FROM sc,course WHERE sc.c_no = course.c_no GROUP BY c_no
# 34、查询选修了“杨艳”老师所授课程的学生中,成绩最高的学生姓名及其成绩。
SELECT sname,score FROM student,sc
WHERE student.s_no = sc.s_no AND c_no =
(SELECT c_no FROM teacher,course WHERE teacher.t_no = course.t_no
AND tname = '杨艳') ORDER BY score DESC LIMIT 1;
# 35、修改18题的表结构,如下:
可参考:https://blog.csdn.net/ZYC88888/article/details/87925897
SELECT s_no,sname,
SUM(IF(a.cname = '语文',1,0)) '语文',
SUM(IF(a.cname = '数学',1,0))'数学',
SUM(IF(a.cname = '英语',1,0))'英语',
SUM(IF(a.cname = '物理',1,0))'物理'
FROM (SELECT student.s_no,student.sname,course.cname FROM
student,sc,course WHERE student.s_no=sc.s_no
AND sc.c_no=course.c_no) a GROUP BY s_no;
参考文章:https://www.cnblogs.com/qluzzh/p/10782993.html