数据库-自由编程

小鱼儿 2023-06-24 06:24 46阅读 0赞

现有班级、学生以及成绩三张表：

备注：表名称和字段名称可以参考表格内单词设置

![20191221020656172.jpg][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzMyODI5_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzMyODI5_size_16_color_FFFFFF_t_70 1][]

根据表格信息，按要求完成下面SQL语句的编写：

1、使用SQL分别创建班级表、学生表以及成绩表的表结构，表内数据可以一条一条的插入也可以批量插入

2、查询每个班级中每一科的平均成绩，显示数据包括班级名称，课程以及平均分数，并按照班ID升序排列

3、查询所有同学的学生ID，姓名，性别以及总分，并按照成绩从高到低排序

4、查询课程成绩小于75分的学生ID，姓名，班级，课程以及分数

5、将李米米的数学成绩修改为88分

6、计算重点班中每一科的平均成绩，显示数据包括：重点班级ID，班级名称，课程，平均分数，按照降序排列

1、
    -- 班级表
    CREATE TABLE class(
      c_id TINYINT(3) UNSIGNED ZEROFILL AUTO_INCREMENT KEY COMMENT '班级ID',
      name VARCHAR(50) NOT NULL UNIQUE COMMENT '名称',
      descrip VARCHAR(200) DEFAULT '' COMMENT '备注'
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT class(name,descrip) VALUES('一年级一班','重点班'),
    ('一年级二班','重点班'),
    ('二年级一班','重点班'),
    ('二年级二班','普通班');
    
    
    -- 学生表
    CREATE TABLE student(
      s_id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '学生ID',
      name VARCHAR(50) NOT NULL COMMENT '姓名',
      gender ENUM('男','女') NOT NULL DEFAULT '男' COMMENT '性别',
      class_id TINYINT UNSIGNED NOT NULL COMMENT '班级',
      CONSTRAINT `fk_class_id` FOREIGN KEY(class_id) REFERENCES class(c_id)
    )ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;
    
    INSERT student(name,gender,class_id) VALUES ('赵晓明','男','001'),
    ('王晓红','女','001'),
    ('张晓晓','女','001'),
    ('孙琪琪','女','003'),
    ('李米米','女','004'),
    ('赵晓刚','男','003'),
    ('张大宝','男','002'),
    ('张兰','女','004'),
    ('孙好','男','001');
    
    
    -- 成绩表
    CREATE TABLE score(
      sc_id INT(3) UNSIGNED ZEROFILL AUTO_INCREMENT KEY COMMENT '成绩ID',
      s_id INT UNSIGNED NOT NULL COMMENT '学生ID',
      course VARCHAR(50) NOT NULL COMMENT '课程',
      mark TINYINT UNSIGNED NOT NULL COMMENT '分数',
      CONSTRAINT `fk_s_id` FOREIGN KEY(s_id) REFERENCES student(s_id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT score(s_id,course,mark) VALUES ('1001','数学','98'),
    ('1001','语文','90'),
    ('1001','英语','97'),
    ('1002','数学','96'),
    ('1002','语文','88'),
    ('1003','语文','88'),
    ('1002','英语','91'),
    ('1003','数学','96'),
    ('1003','英语','86'),
    ('1004','数学','89'),
    ('1004','语文','82'),
    ('1004','英语','83'),
    ('1005','数学','75'),
    ('1005','语文','86'),
    ('1005','英语','77'),
    ('1006','数学','81'),
    ('1006','语文','77'),
    ('1006','英语','60'),
    ('1007','数学','89'),
    ('1007','语文','56'),
    ('1007','英语','70'),
    ('1008','数学','87'),
    ('1008','语文','55'),
    ('1008','英语','66'),
    ('1009','数学','78'),
    ('1009','语文','60'),
    ('1009','英语','52');
    
    
    
    2、
    -- 查询每个班级中每一科的平均成绩，显示数据包括班级名称，课程以及平均分数，并按照班ID升序排列
    SELECT c.name AS '班级',
    sc.course AS '课程',
    AVG(sc.mark) AS '平均分'
    FROM score AS sc
    JOIN student AS s
    ON sc.s_id=s.s_id
    JOIN class AS c
    ON s.class_id=c.c_id
    GROUP BY c.name,sc.course
    ORDER BY c.c_id;
    
    3、
    -- 查询所有同学的学生ID，姓名，性别以及总分，并按照成绩从高到低排序
    SELECT sc.s_id AS '学生ID',
    s.name AS '姓名',
    s.gender AS '性别',
    SUM(sc.mark) AS '总分'
    FROM score AS sc
    JOIN student AS s
    ON sc.s_id=s.s_id
    GROUP BY sc.s_id
    ORDER BY '总分' DESC;
    
    4、
    -- 查询课程成绩小于75分的学生ID，姓名，班级，课程以及分数
    SELECT sc.s_id AS '学生ID',
    s.name AS '姓名',
    c.name AS '班级',
    sc.course AS '课程',
    sc.mark AS '分数'
    FROM score AS sc
    JOIN student AS s
    ON sc.s_id=s.s_id
    JOIN class AS c
    ON s.class_id=c.c_id
    WHERE sc.mark <75;
    
    5、
    -- 将李米米的数学成绩修改为88分
    UPDATE score SET mark=88
    WHERE s_id IN (
      SELECT s_id FROM student WHERE name='李米米'
    ) AND course='数学';
    
    6、
    -- 计算重点班中每一科的平均成绩，显示数据包括：重点班级ID，班级名称，课程，平均分数，按照降序排列
    SELECT c.c_id AS '班级ID',
    c.name AS '班级名',
    sc.course AS '课程',
    AVG(sc.mark) AS '平均分'
    FROM score AS sc
    JOIN student AS s
    ON sc.s_id=s.s_id
    JOIN class AS c
    ON s.class_id=c.c_id
    WHERE c.descrip='重点班'
    GROUP BY sc.course,c.name
    ORDER BY sc.course DESC,AVG(sc.mark) DESC;

[20191221020656172.jpg]: https://img-blog.csdnimg.cn/20191221020656172.jpg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzMyODI5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191221020706258.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzMyODI5,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzMyODI5_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191221020715958.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NzMyODI5,size_16,color_FFFFFF,t_70