CREATE DATABASE db1;
USE db1;-- 班级表
CREATE TABLE class (cid INT(11) NOT NULL AUTO_INCREMENT,caption VARCHAR(32) NOT NULL,PRIMARY KEY (cid)
);INSERT INTO class VALUES
(1, '三年二班'),
(2, '三年三班'),
(3, '一年二班'),
(4, '二年九班');-- 老师表
CREATE TABLE teacher(tid INT(11) NOT NULL AUTO_INCREMENT,tname VARCHAR(32) NOT NULL,PRIMARY KEY (tid)
);INSERT INTO teacher VALUES
(1, '刘正风老师'),
(2, '东方不败老师'),
(3, '风清扬老师'),
(4, '绿竹翁老师'),
(5, '令狐冲老师');-- 课程表
CREATE TABLE course(cid INT(11) NOT NULL AUTO_INCREMENT,cname VARCHAR(32) NOT NULL,teacher_id INT(11) NOT NULL,PRIMARY KEY (cid),CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ;INSERT INTO course VALUES
(1, '生物', 1),
(2, '物理', 2),
(3, '体育', 3),
(4, '美术', 2);-- 学生表
CREATE TABLE student(sid INT(11) NOT NULL AUTO_INCREMENT,gender CHAR(1) NOT NULL,class_id INT(11) NOT NULL,sname VARCHAR(32) NOT NULL,PRIMARY KEY (sid),CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
);
INSERT INTO student VALUES
(1, '男', 1, '理解'),
(2, '女', 1, '钢蛋'),
(3, '男', 1, '张三'),
(4, '男', 1, '张一'),
(5, '女', 1, '张二'),
(6, '男', 1, '张四'),
(7, '女', 2, '铁锤'),
(8, '男', 2, '李三'),
(9, '男', 2, '李一'),
(10, '女', 2, '李二'),
(11, '男', 2, '李四'),
(12, '女', 3, '如花'),
(13, '男', 3, '刘三'),
(14, '男', 3, '刘一'),
(15, '女', 3, '刘二'),
(16, '男', 3, '刘四');-- 选课表
CREATE TABLE score (sid INT(11) NOT NULL AUTO_INCREMENT,student_id INT(11) NOT NULL,course_id INT(11) NOT NULL,num INT(11) NOT NULL,PRIMARY KEY (sid),CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
);INSERT INTO score VALUES
(1, 1, 1, 10),
(2, 1, 2, 9),
(5, 1, 4, 66),
(6, 2, 1, 8),
(8, 2, 3, 68),
(9, 2, 4, 99),
(10, 3, 1, 77),
(11, 3, 2, 66),
(12, 3, 3, 87),
(13, 3, 4, 99),
(14, 4, 1, 79),
(15, 4, 2, 11),
(16, 4, 3, 67),
(17, 4, 4, 100),
(18, 5, 1, 79),
(19, 5, 2, 11),
(20, 5, 3, 67),
(21, 5, 4, 100),
(22, 6, 1, 9),
(23, 6, 2, 100),
(24, 6, 3, 67),
(25, 6, 4, 100),
(26, 7, 1, 9),
(27, 7, 2, 100),
(28, 7, 3, 67),
(29, 7, 4, 88),
(30, 8, 1, 9),
(31, 8, 2, 100),
(32, 8, 3, 67),
(33, 8, 4, 88),
(34, 9, 1, 91),
(35, 9, 2, 88),
(36, 9, 3, 67),
(37, 9, 4, 22),
(38, 10, 1, 90),
(39, 10, 2, 77),
(40, 10, 3, 43),
(41, 10, 4, 87),
(42, 11, 1, 90),
(43, 11, 2, 77),
(44, 11, 3, 43),
(45, 11, 4, 87),
(46, 12, 1, 90),
(47, 12, 2, 77),
(48, 12, 3, 43),
(49, 12, 4, 87),
(52, 13, 3, 87);-- 1、查询平均成绩大于八十分的同学的姓名和平均成绩
-- 2、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
-- 3、查询没有报东方不败老师课的学生姓名
-- 4、查询挂科超过两门(包括两门)的学生姓名和班级
-- 5、查询选修了所有课程的学生姓名
-- 6、查询东方不败老师教的课程的所有成绩记录
-- 7、查询全部学生都选修了的课程号和课程名
-- 8、查询之选修了一门课程的学生姓名和学号
-- 9、查询平均成绩大于85的学生姓名和平均成绩
-- 10、查询生物成绩不及格的学生姓名和对应生物分数
-- 1、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECTstudent.sname '学生姓名',(sum(score.num)/(SELECT COUNT(course.cid) FROM course)) '平均成绩'
FROM-- 没参加考试的学生不考虑, 直接选择内连接score, student
WHERE-- 连接条件score.student_id = student.sid
GROUP BY-- 通过学生id分组score.student_id
HAVING-- 每个学生的平均分 (总分数 / 总课程科目数) 并大于 80(sum(score.num) / (SELECT COUNT(course.cid) FROM course)) >= 80-- 2、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
SELECT-- 获取选课数与总成绩student.sid '学号',student.sname '姓名',IFNULL(COUNT(score.course_id),0) '选课数',IFNULL(SUM(score.num),0) '总分数'-- student.sid '学号',student.sname '姓名'
FROM-- 由于要所有学生, 所以我们使用外连接student
LEFT JOIN score ON score.student_id = student.sid
GROUP BYstudent.sid-- 3、查询没有报东方不败老师课的学生姓名
SELECT student.sname '学生姓名'
FROM student
WHERE
-- 使用排除法,获取没有报东方老师课的学生student.sid
NOT IN
-- 获取东方不败老师的所带科目
(SELECT score.student_id FROM teacher,course,score WHERE teacher.tname='东方不败老师' AND teacher.tid = course.teacher_id AND course.cid = score.course_id GROUP BY score.student_id)-- 4、查询挂科超过两门(包括两门)的学生姓名和班级
SELECTstudent.sname '学生姓名',class.caption '班级'
FROM student,class
WHERE-- 全部学生id排除掉合格学生的id, 然和进行和对应班级内连接student.sid NOT IN (-- 考试合格的学生的id
www.959xb.cn
FROM-- 单个学生已合格数(SELECT score.student_id id,COUNT(score.student_id) count FROM score WHERE score.num > 60 GROUP BY score.student_id) nb
WHERE-- 总合格总数 - 已合格数 = 不合格数-- (不合格数>=2 代表本学生不合格, 不合格数 < 2 代表本学生合格)((SELECT COUNT(*) FROM course) - nb.count) < 2)
ANDstudent.class_id = class.cid-- 5、查询选修了所有课程的学生姓名
SELECTstudent.sname '姓名'
FROMscore,student
WHEREscore.student_id = student.sid
GROUP BYscore.student_id
HAVING-- 学生已选课程总数 = 课程总数COUNT(score.course_id) = (SELECT COUNT(*) FROM course)-- 6、查询东方不败老师教的课程的所有成绩记录
SELECTscore.*
FROMteacher,course,score
WHEREteacher.tname = '东方不败老师' AND course.teacher_id = teacher.tid AND course.cid = score.course_id-- 7、查询全部学生都选修了的课程号和课程名
SELECTcourse.cid '课程号', course.cname '课程名'
FROMscore,course
WHEREcourse.cid = score.course_id
GROUP BY score.course_id
HAVING-- 查询被选科目的总数与全部学生比较,看看哪些课程被全部学生所选COUNT(score.course_id) = (SELECT COUNT(student.sid) FROM student)-- 8、查询之选修了一门课程的学生姓名和学号
SELECTstudent.sname '学生姓名',student.sid '学号'
FROMscore,student
WHEREscore.student_id = student.sid
GROUP BYscore.student_id
HAVINGCOUNT(score.course_id) = 1-- 9、查询平均成绩大于85的学生姓名和平均成绩
SELECTstudent.sname '学生姓名', (SUM(score.num) / (SELECT COUNT(course.cid) FROM course)) '平均成绩'
FROMscore, student
WHEREscore.student_id = student.sid
GROUP BYscore.student_id
HAVING-- 平均分 (总分数 / 课程科目数 ) 并大于 85(SUM(score.num) / (SELECT COUNT(course.cid) FROM course)) > 85-- 10、查询生物成绩不及格的学生姓名和对应生物分数
SELECTstudent.sname '不及格生物学生',IFNULL(score.num,0) '分数'
FROMstudent
LEFT JOINscore
ON-- 匹配分数表中的生物分数集 , 外连接到学生表上 score.course_id = (SELECT course.cid FROM course WHERE course.cname = '生物') AND student.sid = score.student_id
WHERE-- 查询分数不及格的学生, 以及没有参加考试的学生score.num NOT BETWEEN 60 AND 100 OR ISNULL(score.num)
