• 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 for course
-- ----------------------------
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 for score
-- ----------------------------
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 for student
-- ----------------------------
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 for teacher
-- ----------------------------
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;
    View Code

    查询条件

    ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    1、查询所有的课程的名称以及对应的任课老师姓名
     
    2、查询学生表中男女生各有多少人
     
    3、查询物理成绩等于100的学生的姓名
     
    4、查询平均成绩大于八十分的同学的姓名和平均成绩
     
    5、查询所有学生的学号，姓名，选课数，总成绩
     
    6、 查询姓李老师的个数
     
    7、 查询没有报李平老师课的学生姓名
     
    8、 查询物理课程比生物课程高的学生的学号
     
    9、 查询没有同时选修物理课程和体育课程的学生姓名
     
    10、查询挂科超过两门(包括两门)的学生姓名和班级
     
    11 、查询选修了所有课程的学生姓名
     
    12、查询李平老师教的课程的所有成绩记录
      
    13、查询全部学生都选修了的课程号和课程名
     
    14、查询每门课程被选修的次数
     
    15、查询之选修了一门课程的学生姓名和学号
     
    16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
     
    17、查询平均成绩大于85的学生姓名和平均成绩
     
    18、查询生物成绩不及格的学生姓名和对应生物分数
     
    19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

    参考答案

    + View Code?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    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、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;
     
    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
     
     
           SELECT cname,COUNT(course_id) FROM score LEFT 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(sid)=1);
     
     
    16、SELECT DISTINCT num FROM score ORDER BY num DESC；
     
    17、SELECT sname from student
                        WHERE sid in (SELECT student_id FROM score
                        GROUP BY student_id 
                        HAVING AVG(num)>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
  • 相关阅读:
    Jenkins结合.net平台工具之Msbuild
    Jenkins入门之执行定时任务
    Jenkins入门之执行Powershell脚本
    Jenkins入门之新建任务
    Redis主体流程分析
    【ELK】Centos7 安装 ELK 7.6.2 和 UI 管理界面以及测试例子
    德佑地产房产经纪人区域总监访谈：王凯：怎样做一个优秀的管理者？
    Thinkphp. Tp5路由实现api开发版本管理
    Mysql覆盖索引的概念及注意事项
    java大文件(视频)上传方法
  • 原文地址：https://www.cnblogs.com/xiaotaiyanghhh/p/8510097.html
Copyright © 2020-2023  润新知