mysql -u root -p
show databases;
create database larken;
use larken;
show tables;
-- ----------------------------
-- Table structure for class
-- ----------------------------
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '高一一班');
INSERT INTO `class` VALUES ('2', '高一二班');
INSERT INTO `class` VALUES ('3', '高一三班');
INSERT INTO `class` VALUES ('4', '高一四班');
INSERT INTO `class` VALUES ('5', '高一五班');
-- ----------------------------
-- Table structure for student
-- ----------------------------
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`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '男', '1', 'aaa');
INSERT INTO `student` VALUES ('2', '女', '2', 'bbb');
INSERT INTO `student` VALUES ('3', '男', '4', 'ccc');
INSERT INTO `student` VALUES ('4', '男', '2', 'ddd');
INSERT INTO `student` VALUES ('5', '女', '5', 'eee');
INSERT INTO `student` VALUES ('6', '男', '1', 'fff');
INSERT INTO `student` VALUES ('7', '女', '4', 'ggg');
INSERT INTO `student` VALUES ('8', '男', '2', 'hhh');
INSERT INTO `student` VALUES ('9', '男', '1', 'iii');
INSERT INTO `student` VALUES ('10', '女', '5', 'jjj');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '张磊老师');
INSERT INTO `teacher` VALUES ('2', '李平老师');
INSERT INTO `teacher` VALUES ('3', '刘海燕老师');
INSERT INTO `teacher` VALUES ('4', '朱云海老师');
INSERT INTO `teacher` VALUES ('5', '李杰老师');
-- ----------------------------
-- Table structure for course
-- ----------------------------
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文', '1');
INSERT INTO `course` VALUES ('2', '数学', '2');
INSERT INTO `course` VALUES ('3', '外语', '3');
INSERT INTO `course` VALUES ('4', '物理', '4');
INSERT INTO `course` VALUES ('5', '化学', '5');
-- ----------------------------
-- Table structure for score
-- ----------------------------
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`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '78');
INSERT INTO `score` VALUES ('2', '2', '2', '91');
INSERT INTO `score` VALUES ('3', '3', '4', '66');
INSERT INTO `score` VALUES ('4', '4', '5', '83');
INSERT INTO `score` VALUES ('5', '5', '3', '68');
INSERT INTO `score` VALUES ('6', '6', '4', '99');
INSERT INTO `score` VALUES ('7', '7', '1', '77');
INSERT INTO `score` VALUES ('8', '8', '2', '66');
INSERT INTO `score` VALUES ('9', '9', '3', '87');
INSERT INTO `score` VALUES ('10', '10', '4', '99');
查询条件
1、查询所有的课程的名称以及对应的任课老师姓名
SELECT cname,tname
FROM course LEFT JOIN teacher
on course.teacher_id=teacher.tid;
2、查询学生表中男女生各有多少人
SELECT gender 性别 ,COUNT(gender) 人数
from student GROUP BY gender;
3、查询物理成绩等于100的学生的姓名
SELECT sname from student where sid in
(SELECT student_id from score LEFT JOIN course
ON score.course_id=course.cid
WHERE num=100 AND course.cname="物理");
4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT sname 姓名,平均成绩 from student RIGHT JOIN
(SELECT student_id,avg(num) 平均成绩
from score GROUP BY student_id
HAVING avg(num)>80) as A
on student.sid=A.student_id;
5、查询所有学生的学号，姓名，选课数，总成绩
SELECT student_id 学号,sname 姓名,COUNT(course_id) 课程数,SUM(num) 总分
FROM score
LEFT JOIN student ON score.student_id=student.sid GROUP BY student_id;
6、 查询姓李老师的个数
7、 查询没有报李平老师课的学生姓名
SELECT * FROM student
WHERE sid not in
(SELECT student_id FROM score WHERE course_id in
(SELECT cid FROM course LEFT JOIN teacher ON teacher.tid=course.teacher_id
WHERE tname like "李平%"))
8、 查询物理课程比生物课程高的学生的学号
SELECT * FROM
(SELECT * FROM score LEFT JOIN course
ON score.course_id=course.cid WHERE cname="生物") as A INNER JOIN
(SELECT * FROM score LEFT JOIN course
ON score.course_id=course.cid WHERE cname="物理") as B
ON A.student_id=B.student_id
WHERE a.num<b.num
9、 查询没有同时选修物理课程和体育课程的学生姓名
SELECT sname from student
WHERE sid NOT IN (SELECT student_id FROM score LEFT JOIN course
ON score.course_id=course.cid
WHERE cname='体育' OR cname='物理'
GROUP BY student_id
HAVING COUNT(course_id)>1) ;
10、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT sname,gender from student
WHERE sid in (SELECT student_id from score WHERE num<60
GROUP BY student_id
HAVING COUNT(student_id)>1);
11 、查询选修了所有课程的学生姓名
SELECT sid 学号,sname 姓名 from student
WHERE sid in (SELECT student_id FROM score
GROUP BY student_id
HAVING COUNT(student_id)!= (SELECT COUNT(cid) FROM course))
12、查询李平老师教的课程的所有成绩记录
SELECT * FROM score
WHERE course_id NOT IN (SELECT cid from teacher INNER JOIN course
on teacher.tid=course.teacher_id
WHERE teacher.tname LIKE "李平%");
13、查询全部学生都选修了的课程号和课程名
SELECT course_id from score
GROUP BY course_id
HAVING COUNT(course_id)=(SELECT COUNT(sid) FROM student);
14、查询每门课程被选修的次数
SELECT course_id,COUNT(course_id) from score GROUP BY course_id;
15、查询只选修了一门课程的学生姓名和学号
SELECT sid,sname FROM student
WHERE sid in (SELECT student_id from score
GROUP BY student_id
HAVING COUNT(sid)=1);
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT num FROM score ORDER BY num DESC;
17、查询平均成绩大于85的学生姓名和平均成绩
SELECT sname,AVG(num) FROM student LEFT JOIN score
ON student.sid=score.student_id
GROUP BY student_id HAVING AVG(num)>85;
18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT sname 姓名,num 生物成绩 FROM score
LEFT JOIN course ON score.course_id=course.cid
LEFT JOIN student ON score.student_id=student.sid
WHERE score.num<60 AND cname="生物"；
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT student_id,avg(num),sname FROM score
LEFT JOIN student ON score.student_id=student.sid
WHERE course_id in (SELECT cid FROM course
LEFT JOIN teacher ON teacher.tid=course.teacher_id WHERE tname LIKE "李平%")
GROUP BY student_id
ORDER BY AVG(num) DESC
LIMIT 1
