mysql数据库挂科_MySQL数据库--练习

学生选课系统

设计表关系

创建表和插入数据

/*Navicat MySQL Data Transfer

Source Server : localhost_3306

Source Server Version : 50719

Source Host : localhost:3306

Source Database : oldbo

Target Server Type : MYSQL

Target Server Version : 50719

File Encoding : 65001

Date: 2017-07-26 15:46:16*/SET FOREIGN_KEY_CHECKS=0;-- ----------------------------

-- Table structure for class

-- ----------------------------DROP TABLE IF EXISTS `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', '二年九班');-- ----------------------------

-- Table structure forcourse-- ----------------------------DROP TABLE IF EXISTS `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', '美术', '2');-- ----------------------------

-- Table structure forscore-- ----------------------------DROP TABLE IF EXISTS `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', '10');

INSERT INTO `score` VALUES ('2', '1', '2', '9');

INSERT INTO `score` VALUES ('5', '1', '4', '66');

INSERT INTO `score` VALUES ('6', '2', '1', '8');

INSERT INTO `score` VALUES ('8', '2', '3', '68');

INSERT INTO `score` VALUES ('9', '2', '4', '99');

INSERT INTO `score` VALUES ('10', '3', '1', '77');

INSERT INTO `score` VALUES ('11', '3', '2', '66');

INSERT INTO `score` VALUES ('12', '3', '3', '87');

INSERT INTO `score` VALUES ('13', '3', '4', '99');

INSERT INTO `score` VALUES ('14', '4', '1', '79');

INSERT INTO `score` VALUES ('15', '4', '2', '11');

INSERT INTO `score` VALUES ('16', '4', '3', '67');

INSERT INTO `score` VALUES ('17', '4', '4', '100');

INSERT INTO `score` VALUES ('18', '5', '1', '79');

INSERT INTO `score` VALUES ('19', '5', '2', '11');

INSERT INTO `score` VALUES ('20', '5', '3', '67');

INSERT INTO `score` VALUES ('21', '5', '4', '100');

INSERT INTO `score` VALUES ('22', '6', '1', '9');

INSERT INTO `score` VALUES ('23', '6', '2', '100');

INSERT INTO `score` VALUES ('24', '6', '3', '67');

INSERT INTO `score` VALUES ('25', '6', '4', '100');

INSERT INTO `score` VALUES ('26', '7', '1', '9');

INSERT INTO `score` VALUES ('27', '7', '2', '100');

INSERT INTO `score` VALUES ('28', '7', '3', '67');

INSERT INTO `score` VALUES ('29', '7', '4', '88');

INSERT INTO `score` VALUES ('30', '8', '1', '9');

INSERT INTO `score` VALUES ('31', '8', '2', '100');

INSERT INTO `score` VALUES ('32', '8', '3', '67');

INSERT INTO `score` VALUES ('33', '8', '4', '88');

INSERT INTO `score` VALUES ('34', '9', '1', '91');

INSERT INTO `score` VALUES ('35', '9', '2', '88');

INSERT INTO `score` VALUES ('36', '9', '3', '67');

INSERT INTO `score` VALUES ('37', '9', '4', '22');

INSERT INTO `score` VALUES ('38', '10', '1', '90');

INSERT INTO `score` VALUES ('39', '10', '2', '77');

INSERT INTO `score` VALUES ('40', '10', '3', '43');

INSERT INTO `score` VALUES ('41', '10', '4', '87');

INSERT INTO `score` VALUES ('42', '11', '1', '90');

INSERT INTO `score` VALUES ('43', '11', '2', '77');

INSERT INTO `score` VALUES ('44', '11', '3', '43');

INSERT INTO `score` VALUES ('45', '11', '4', '87');

INSERT INTO `score` VALUES ('46', '12', '1', '90');

INSERT INTO `score` VALUES ('47', '12', '2', '77');

INSERT INTO `score` VALUES ('48', '12', '3', '43');

INSERT INTO `score` VALUES ('49', '12', '4', '87');

INSERT INTO `score` VALUES ('52', '13', '3', '87');-- ----------------------------

-- Table structure forstudent-- ----------------------------DROP TABLE IF EXISTS `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', '理解');

INSERT INTO `student` VALUES ('2', '女', '1', '钢蛋');

INSERT INTO `student` VALUES ('3', '男', '1', '张三');

INSERT INTO `student` VALUES ('4', '男', '1', '张一');

INSERT INTO `student` VALUES ('5', '女', '1', '张二');

INSERT INTO `student` VALUES ('6', '男', '1', '张四');

INSERT INTO `student` VALUES ('7', '女', '2', '铁锤');

INSERT INTO `student` VALUES ('8', '男', '2', '李三');

INSERT INTO `student` VALUES ('9', '男', '2', '李一');

INSERT INTO `student` VALUES ('10', '女', '2', '李二');

INSERT INTO `student` VALUES ('11', '男', '2', '李四');

INSERT INTO `student` VALUES ('12', '女', '3', '如花');

INSERT INTO `student` VALUES ('13', '男', '3', '刘三');

INSERT INTO `student` VALUES ('14', '男', '3', '刘一');

INSERT INTO `student` VALUES ('15', '女', '3', '刘二');

INSERT INTO `student` VALUES ('16', '男', '3', '刘四');-- ----------------------------

-- Table structure forteacher-- ----------------------------DROP TABLE IF EXISTS `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', '李杰老师');

SET FOREIGN_KEY_CHECKS=1;

创建表和插入数据

查询条件

-- -- 1、将所有的课程的名称以及对应的任课老师姓名打印出来

SELECT cname,tname FROM course INNER JOIN teacher ON course.teacher_id=teacher.tid;

-- -- 2、查询学生表中男女生各有多少人

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

-- -- 3、查询物理成绩等于100的学生的姓名

-- -- 查询物理课程cid：SELECT cid FROM course WHERE cname='物理'

-- -- SELECT sid,sname FROM student WHERE sid in (SELECT student_id FROM score

-- -- WHERE course_id=(SELECT cid FROM course WHERE cname='物理') AND num=100)

-- -- 简化

SELECT sid,sname FROM student WHERE sid in (SELECT student_id FROM score WHERE course_id=2 AND num=100);

-- -- 4、查询平均成绩大于八十分的同学的姓名和平均成绩

SELECT student_id,sname,avg(num) FROM score INNER JOIN student on score.student_id=student.sid GROUP BY student_id HAVING avg(num)>80;

-- -- 5、查询所有学生的学号，姓名，选课数，总成绩

SELECT student_id,sname,COUNT(course_id) 选课数, SUM(num) 总成绩 FROM score INNER JOIN student on score.student_id=student.sid GROUP BY student_id ;

-- -- 6、查询姓李老师的个数

SELECT COUNT(tname) FROM teacher WHERE tname LIKE '李%';

-- -- 7、查询没有报李平老师课的学生姓名

-- -- 李平老师的tid：SELECT tid FROM teacher WHERE tname='李平老师';

-- -- 李平老师的课程cid：(2,4) SELECT cid FROM course WHERE teacher_id =(SELECT tid FROM teacher WHERE tname='李平老师');

-- -- 选了李平老师课程的学生id：

SELECT DISTINCT student_id FROM score WHERE course_id in

(SELECT cid FROM course WHERE teacher_id =(SELECT tid FROM teacher WHERE tname LIKE '李平%'));

-- -- 简化

SELECT sid,sname FROM student WHERE sid NOT in (

SELECT DISTINCT student_id FROM score WHERE course_id in (2,4));

-- -- 方法二：

SELECT student.sid,sname from student left JOIN score on student.sid=score.student_id

WHERE student.sid NOT IN (SELECT DISTINCT student_id from score where course_id = 2 or course_id=4);

-- 8、查询物理课程比生物课程高的学生的学号

-- 物理表：SELECT student_id,num FROM score WHERE course_id=2;

-- 生物表：SELECT student_id,num FROM score WHERE course_id=1;

--

SELECT phy.student_id resid,phy.num phy,bio.num bio FROM

(SELECT student_id,num FROM score WHERE course_id=2) AS phy

INNER JOIN

(SELECT student_id,num FROM score WHERE course_id=1) AS bio

on phy.student_id=bio.student_id WHERE phy.num>bio.num;

SELECT sid,sname,res.phy,res.bio FROM student

INNER JOIN (

SELECT phy.student_id resid,phy.num phy,bio.num bio FROM

(SELECT student_id,num FROM score WHERE course_id=2) AS phy

INNER JOIN

(SELECT student_id,num FROM score WHERE course_id=1) AS bio

on phy.student_id=bio.student_id WHERE phy.num>bio.num) AS res

ON student.sid=res.resid;

-- -- 9、查询没有同时选修物理课程和体育课程的学生姓名

SELECT sid,sname FROM student WHERE sid NOT in (

SELECT phy.student_id FROM (SELECT student_id FROM score WHERE course_id=2) as phy INNER JOIN (SELECT student_id FROM score WHERE course_id=3) as spo ON phy.student_id=spo.student_id);

-- 方法二

SELECT sid,sname FROM student WHERE sid NOT in (

SELECT student_id FROM (SELECT student_id FROM score WHERE course_id=2) as phy WHERE phy.student_id in (SELECT student_id FROM score WHERE course_id=3));

-- 10、查询挂科超过两门(包括两门)的学生姓名和班级

SELECT sid,sname,caption FROM student INNER JOIN class ON student.class_id=class.cid WHERE sid in (

SELECT student_id FROM score WHERE num<60 GROUP BY student_id HAVING count(sid)>=2 );

-- 11 、查询选修了所有课程的学生姓名

SELECT sid,sname FROM student WHERE sid in (

SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)=(SELECT COUNT(cid) FROM course));

-- 12、查询李平老师教的课程的所有成绩记录

SELECT course_id,num FROM score WHERE course_id in (

SELECT cid FROM course WHERE teacher_id =(SELECT tid FROM teacher WHERE tname='李平老师')

) ORDER BY course_id;

SELECT tid,tname,cid,cname,res.student_id,student.sname,course_id,num FROM (

SELECT tid,tname,cid,cname,student_id,course_id,num FROM (SELECT tid,tname,cid,cname FROM teacher INNER JOIN course on teacher.tid=course.teacher_id WHERE tname='李平老师') as tea

RIGHT JOIN (SELECT student_id,course_id,num FROM score WHERE course_id in (

SELECT cid FROM course WHERE teacher_id =(SELECT tid FROM teacher WHERE tname='李平老师')

) ORDER BY course_id) as cor ON tea.cid=cor.course_id) AS res INNER JOIN student ON res.student_id=student.sid;

-- 13、查询全部学生都选修了的课程号和课程名

SELECT cid,cname FROM course INNER JOIN (

SELECT course_id FROM score GROUP BY course_id HAVING COUNT(student_id)=(SELECT COUNT(sid) FROM student )) as res on course.cid=res.course_id;

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

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

SELECT count(DISTINCT student_id) FROM score;

-- 14、查询每门课程被选修的次数

SELECT course_id,cname,COUNT(student_id) FROM score INNER JOIN course on score.course_id=course.cid GROUP BY course_id

-- 15、查询之选修了一门课程的学生姓名和学号

SELECT sid,sname FROM student WHERE sid in (

SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)=1);

-- 16、查询所有学生考出的成绩并按从高到低排序(成绩去重)

SELECT DISTINCT num FROM score ORDER BY num DESC

-- 17、查询平均成绩大于85的学生姓名和平均成绩

SELECT student_id,sname,AVG(num) FROM score INNER JOIN student on score.student_id=student.sid GROUP BY student_id HAVING AVG(num)>85

-- 18、查询生物成绩不及格的学生姓名和对应生物分数

SELECT student_id,sname,cname,num FROM (

SELECT student_id,cname,num FROM score INNER JOIN course on score.course_id=course.cid WHERE cname='生物' AND num<60) AS res

INNER JOIN

student

ON student.sid=res.student_id

-- 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

SELECT res.student_id,sname,avg(num) FROM (

SELECT student_id,num FROM score WHERE course_id in (2,4)) as res

INNER JOIN

student

on res.student_id=student.sid

GROUP BY res.student_id ORDER BY avg(num) DESC LIMIT 1

-- 20、查询每门课程成绩最好的前两名学生姓名

SELECT student_id,num FROM score WHERE course_id=2 ORDER BY num DESC LIMIT 2

-- 21、查询不同课程但成绩相同的学号，课程号，成绩

-- 22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称；

select res.id,sname,cname FROM course INNER JOIN

(

select student.sid id,sname,course_id FROM student INNER JOIN score ON student.sid=score.student_id

WHERE course_id NOT in(select cid FROM teacher INNER JOIN course on teacher.tid=course.teacher_id WHERE tname LIKE '李平%')

) AS res

ON course.cid=res.course_id

-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

SELECT sid,sname FROM student WHERE sid in (

SELECT DISTINCT student_id FROM score WHERE course_id in (SELECT course_id FROM score WHERE student_id=1)

);

-- 24、任课最多的老师中学生单科成绩最高的学生姓名

# SELECT teacher_id,count(cid) FROM course GROUP BY teacher_id

-- -- 10、查询有课程成绩小于60分的同学的学号、姓名

SELECT sid,sname,course_id,num FROM student INNER JOIN (

SELECT student_id,course_id,num FROM score WHERE num<60) AS res ON student.sid=res.student_id;

/*Navicat MySQL Data Transfer

Source Server : localhost_3306

Source Server Version : 50719

Source Host : localhost:3306

Source Database : oldbo

Target Server Type : MYSQL

Target Server Version : 50719

File Encoding : 65001

Date: 2017-07-26 15:46:16*/

SET FOREIGN_KEY_CHECKS=0;--------------------------------Table structure for class------------------------------

DROP TABLE IF EXISTS`class`;CREATE TABLE`class` (

`cid`int(11) NOT NULLAUTO_INCREMENT,

`caption`varchar(32) NOT NULL,