sql题目练习数据库脚本和答案

「爱情、让人受尽委屈。」 2023-01-21 10:25 209阅读 0赞

### 文章目录 ###

*   *  一、班级、学生、成绩表查询
     *  二、学生、课程、成绩、教师表查询
     *  三、学生成绩表查询
     *  四、成绩表（4\_score）查询
     *  五、 学生表（student）查询
     *  六、 部门员工表（emp、dept）查询
     *  七、SQL数据库表名为guest，请简答
     *  数据库脚本

> 不带答案版本，访问这个  
> [https://blog.csdn.net/shuai8624/article/details/116836863][https_blog.csdn.net_shuai8624_article_details_116836863]

## 一、班级、学生、成绩表查询 ##

已知以下表结构

班级表（1\_class）

<table> 
 <thead> 
  <tr> 
   <th>编号</th> 
   <th>班级名称</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>class_name</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>一班</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>二班</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>三班</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>四班</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>五班</td> 
  </tr> 
 </tbody> 
</table>

学生表（1\_student）

<table> 
 <thead> 
  <tr> 
   <th>编号</th> 
   <th>学号</th> 
   <th>姓名</th> 
   <th>性别</th> 
   <th>所属班级</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>stu_no</td> 
   <td>stu_name</td> 
   <td>stu_gender</td> 
   <td>class_id</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>2020001</td> 
   <td>张三</td> 
   <td>男</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2020002</td> 
   <td>李四</td> 
   <td>男</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2020003</td> 
   <td>李丽</td> 
   <td>女</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>2020004</td> 
   <td>赵婷</td> 
   <td>女</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>2020005</td> 
   <td>王五</td> 
   <td>男</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

成绩表（1\_score）

<table> 
 <thead> 
  <tr> 
   <th>学生</th> 
   <th>语文</th> 
   <th>数学</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>stu_id</td> 
   <td>chinese</td> 
   <td>math</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>70</td> 
   <td>47</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>80</td> 
   <td>60</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>50</td> 
   <td>82</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>80</td> 
   <td>90</td> 
  </tr> 
 </tbody> 
</table>

业务场景限制：

1.  一个班级有多名学生，一名学生只属于一个班级
2.  学生有可能没有成绩

题目：

1.  查询所有学生的信息（学号，姓名，性别，班级名称）

SELECT
           stu_no,
           stu_name,
           stu_gender,
           class_name 
       FROM
           1_student s
           JOIN 1_class c ON s.class_id = c.id

1.  查询所有人(包括没有成绩的学生)的课程分数（学号，姓名，性别，班级名称，语文分数，数学分数）
    
        SELECT
            stu_no,
            stu_name,
            stu_gender,
            class_name,
        	se.chiness,
        	se.math
        FROM
            1_student s
            JOIN 1_class c ON s.class_id = c.id
        	LEFT JOIN 1_score se on se.stu_id = s.id
2.  查询语文分数比“张三”高的学生（学号，姓名，性别，班级名称，语文分 数）
    
        # 先查询出张三的语文分数，再查比他分数高的
        SELECT
        	stu_no,
        	stu_name,
        	stu_gender,
        	class_name,
        	se.chiness 
        FROM
        	1_student s
        	JOIN 1_class c ON s.class_id = c.id
        	LEFT JOIN 1_score se ON se.stu_id = s.id 
        WHERE
        	se.chiness > (
        	SELECT
        		se.chiness 
        	FROM
        		1_student s
        		JOIN 1_class c ON s.class_id = c.id
        		LEFT JOIN 1_score se ON se.stu_id = s.id 
        	WHERE
        	s.stu_name = '张三' 
        	)
3.  查询各科都合格（分数>=60）的学生（学号，姓名，语文分数，数学分数）
    
        SELECT
        	stu_no,
        	stu_name,
        	se.chiness,
        	se.math 
        FROM
        	1_student s
        	LEFT JOIN 1_score se ON se.stu_id = s.id 
        WHERE
        	se.chiness >= 60 
        	AND se.math >= 60
4.  查询出所有班级的人数（若没有人，人数显示为0）
    
        SELECT
        	c.class_name,
        	count( s.id ) 
        FROM
        	1_class c
        	LEFT JOIN 1_student s ON c.id = s.class_id 
        GROUP BY
        	c.id
5.  查询班级人数>=2的班级（班级编号，班级名称，人数）
    
        SELECT
        	c.class_name,
        	count( s.id ) 
        FROM
        	1_class c
        	LEFT JOIN 1_student s ON c.id = s.class_id 
        GROUP BY
        	c.id HAVING count( s.id ) >= 2

## 二、学生、课程、成绩、教师表查询 ##

有以下四个表，表名称及表结构如下：

student(sno,sname,sage,ssex) 学生表
    course(cno,cname,tno) 课程表
    sc(sno,cno,score) 成绩表
    teacher(tno,tname) 教师表

1.  查询课程1的成绩比课程2的成绩高的所有学生的信息
    
        # 本题使用表的自连接查询，将sc1表看成课程1的成绩表，将sc2表看成课程2的成绩表，表连接后，再条件查询部分实现这一步，具体为：where sc1.cno=1 and sc2.cno=2
        SELECT
            s.sno,
            sname,
            sage,
            ssex,
            sc1.score '课程1',
            sc2.score '课程2' 
        FROM
            student s
            JOIN sc sc1
            JOIN sc sc2 ON sc1.sno = sc2.sno 
            AND sc1.sno = s.sno 
        WHERE
            sc1.cno = 1 
            AND sc2.cno = 2 
            AND sc1.score > sc2.score
2.  查询平均成绩大于60分的同学的学号和平均成绩
    
        # 对分组后的数据筛选，使用having
        SELECT
        	sno,
        	AVG( DISTINCT score ) AS avg_score 
        FROM sc 
        GROUP BY sno 
        HAVING avg_score >= 60
3.  查询学过‘李四’老师所教课程的所有同学的学号，姓名
    
        SELECT DISTINCT
            s.sno,
            sname 
        FROM
            teacher t
            JOIN course c
            JOIN sc
            JOIN student s ON t.tno = c.tno 
            AND c.cno = sc.cno 
            AND sc.sno = s.sno 
        WHERE
            tname = '李四'
4.  查询姓“李”得老师的个数
    
        SELECT
        	count( tno ) 
        FROM
        	teacher
        WHERE
        	tname LIKE '李%'
5.  查询每门课程的选修人数(课程名称，学生数量)–存在没有人选的课程
    
        SELECT
        	c.cname,
        	count( sc.cno ) 
        FROM
        	sc
        	JOIN course c ON c.cno = sc.cno 
        GROUP BY
        	sc.cno
6.  删除“1002”同学的“1”课程的成绩
    
        delete from sc where sno='1002' and cno=1
7.  查询选修人数最多的课程（课程id，课程名称，学生数量）–考虑有多门课程都是选修最多的情况
    
        1. 查询出每个课程的选修人数，找到最大的选修人数
        select count(sno) cou from sc group by cno order by cou desc limit 1;
        2. 写主sql -- 查询选修人数值=max的课程信息
        SELECT
            c.cno,
            cname,
            count( sno ) cou 
        FROM
            course c
            JOIN sc ON c.cno = sc.cno 
        GROUP BY
            sc.cno 
        HAVING
            cou = ( SELECT count( sno ) cou FROM sc GROUP BY cno ORDER BY cou DESC LIMIT 1 )

## 三、学生成绩表查询 ##

下面是学生成绩表(3\_student\_score)结构说明

<table> 
 <thead> 
  <tr> 
   <th>字段名称</th> 
   <th>字段解释</th> 
   <th>字段类型</th> 
   <th>字段长度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>student_id</td> 
   <td>学号</td> 
   <td>字符</td> 
   <td>8</td> 
  </tr> 
  <tr> 
   <td>student_name</td> 
   <td>姓名</td> 
   <td>字符</td> 
   <td>50</td> 
  </tr> 
  <tr> 
   <td>student_gender</td> 
   <td>性别</td> 
   <td>字符(男/女)</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>course_id</td> 
   <td>课程号</td> 
   <td>字符</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>score</td> 
   <td>分数</td> 
   <td>数值</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>ismakeup</td> 
   <td>当前考试是否为补考</td> 
   <td>字符(补考:1;非补考:0)</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

下面是课程表(3\_course)说明

<table> 
 <thead> 
  <tr> 
   <th>字段名称</th> 
   <th>字段解释</th> 
   <th>字段类型</th> 
   <th>字段长度</th> 
   <th>约束</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>course_id</td> 
   <td>课程号</td> 
   <td>字符</td> 
   <td>5</td> 
   <td>PK</td> 
  </tr> 
  <tr> 
   <td>course_name</td> 
   <td>课程名</td> 
   <td>字符</td> 
   <td>30</td> 
   <td>Not null</td> 
  </tr> 
  <tr> 
   <td>course_desc</td> 
   <td>课程介绍</td> 
   <td>字符</td> 
   <td>60</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

1、查找第一次考试后所有需要补考(小于60分)的学生姓名和这门课程的名称和成绩；

SELECT
    	s.student_name,
    	c.course_name,
    	s.score 
    FROM
    	3_student_score s
    	JOIN 3_course c ON c.course_id = s.course_id 
    WHERE
    	s.ismakeup = 0 
    	AND s.score < 60

2、查询每个学生第一次考试后需要补考(小于60分)的课程平均分和科目数

SELECT
    	student_name,
    	avg( score ),
    	count( course_id ) 
    FROM
    	3_student_score 
    WHERE
    	ismakeup = 0 
    	AND score < 60 
    GROUP BY
    	student_id

3、查询所有参加了补考的学生的学生姓名，课程名称，补考成绩和非补考成绩；

SELECT
    	s.student_name,
    	c.course_name,
    	s.score AS '非补考成绩',
    	s2.score '补考成绩' 
    FROM
    	3_student_score s
    	JOIN 3_student_score s2 ON s.course_id = s2.course_id 
    	AND s.student_id = s2.student_id
    	JOIN 3_course c ON c.course_id = s.course_id 
    WHERE
    	s.ismakeup = 0 
    	AND s2.ismakeup = 1

## 四、成绩表（4\_score）查询 ##

有一张表4\_score，三个字段名，姓名，课程，分数，数据如下，请写一条sql语句，查询出每门课程都大于等于80分的学生信息

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>course</th> 
   <th>score</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>张三</td> 
   <td>语文</td> 
   <td>81</td> 
  </tr> 
  <tr> 
   <td>张三</td> 
   <td>数学</td> 
   <td>75</td> 
  </tr> 
  <tr> 
   <td>李四</td> 
   <td>语文</td> 
   <td>76</td> 
  </tr> 
  <tr> 
   <td>王五</td> 
   <td>语文</td> 
   <td>81</td> 
  </tr> 
  <tr> 
   <td>王五</td> 
   <td>数学</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>王五</td> 
   <td>英语</td> 
   <td>90</td> 
  </tr> 
 </tbody> 
</table>

SELECT NAME 
    FROM
    	`score` 
    GROUP BY
    NAME 
    HAVING
    	min( score ) >= 80

## 五、 学生表（student）查询 ##

有一张表5\_student，包括字段id和name，请写一条sql语句，将表中name字段中重复的记录删除，只保留重复数据中的id最大的那一条数据。

要求只留下：2 张三， 3 李四， 6 王五 这三条记录

SELECT  max(id), name FROM `5_student` GROUP BY name

## 六、 部门员工表（emp、dept）查询 ##

emp:

empno int(员工编号) ,ename varchar(50)(员工姓名) ，job varchar(100) （工作岗位），mgr int (上级领导编号)，hiredate date（雇佣日期），sal int（薪金），comm int（佣金） deptno int (部门编号)

提示：工资=薪金+佣金

dept表：

deptno int (部门编号) ， dname 部门名称 loc 地点

1.  列出在每个部门工作的员工数量，平均工资
    
        SELECT
        	d.dname,
        	count(e.empno),
        	avg( e.sal + e.comm )
        FROM
        	emp e
        	right JOIN dept d ON d.deptno = e.deptno 
        GROUP BY
        	d.deptno
2.  列出所有员工的姓名，部门名称和工资
    
        SELECT
        	e.ename,
        	d.dname,
        	e.sal + e.comm 
        FROM
        	emp e
        	INNER JOIN dept d ON d.deptno = e.deptno
3.  列出所有部门的详细信息和部门人数
    
        SELECT
        	d.*,
        	count( e.empno ) '部门人数' 
        FROM
        	emp e
        	INNER JOIN dept d ON d.deptno = e.deptno 
        GROUP BY
        	d.deptno
4.  列出各种工作的最低工资
    
        SELECT
        	e.job,
        	min( e.sal + e.comm ) '工资'
        FROM
        	emp e 
        GROUP BY
        	e.job
5.  列出各个部门的manager的最低薪金(若是manager,其job的值为manageer)
    
        SELECT
        	e.ename,
        	e.job,
        	min( e.sal + e.comm ) '工资' 
        FROM
        	emp e 
        WHERE
        	e.job = 'manageer' 
        GROUP BY
        	deptno
6.  列出受雇日期早于其直接上级的所有员工
    
        # join自己 行转列
        SELECT
        	e1.* 
        FROM
        	emp e1
        	JOIN emp e2 ON e1.mgr = e2.empno 
        WHERE
        	e1.hiredate < e2.hiredate
7.  列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
    
        # 因为要列出那些没有员工的部门，所以部门为主表 左连接
        SELECT
        	d.dname,
        	e.* 
        FROM
        	dept d
        	LEFT JOIN emp e ON d.deptno = e.deptno 
        ORDER BY
        	d.deptno
8.  列出所有‘clerk’（办事员）岗位的姓名以及部门名称
    
        SELECT
        	e.ename,
        	d.dname 
        FROM
        	emp e
        	JOIN dept d ON d.deptno = e.deptno 
        WHERE
        	e.job = 'clerk'
9.  列出最低薪金大于6500的各种工作
    
        SELECT
        	job 
        FROM
        	emp 
        GROUP BY
        	job 
        HAVING
        	min( sal + comm ) > 6500
10. 列出在研发部工作的员工的姓名，假定不知道研发部的部门编号
    
        SELECT
        	e.*,
        	d.dname 
        FROM
        	emp e
        	JOIN dept d ON d.deptno = e.deptno 
        WHERE
        	d.dname = '研发部'

## 七、SQL数据库表名为guest，请简答 ##

<table> 
 <thead> 
  <tr> 
   <th>账号</th> 
   <th>消费</th> 
   <th>时间</th> 
   <th>金额</th> 
   <th>班次</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>accounts</td> 
   <td>details</td> 
   <td>date</td> 
   <td>money</td> 
   <td>class</td> 
  </tr> 
  <tr> 
   <td>s0001</td> 
   <td>房费</td> 
   <td>2020-01-01</td> 
   <td>280</td> 
   <td>001</td> 
  </tr> 
  <tr> 
   <td>s0001</td> 
   <td>酒水</td> 
   <td>2020-01-02</td> 
   <td>120</td> 
   <td>001</td> 
  </tr> 
  <tr> 
   <td>s0001</td> 
   <td>房费</td> 
   <td>2020-01-08</td> 
   <td>300</td> 
   <td>003</td> 
  </tr> 
  <tr> 
   <td>s0002</td> 
   <td>酒水</td> 
   <td>2020-01-29</td> 
   <td>50</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>s0003</td> 
   <td>房费</td> 
   <td>2020-01-31</td> 
   <td>180</td> 
   <td>002</td> 
  </tr> 
  <tr> 
   <td>s0004</td> 
   <td>房费</td> 
   <td>2020-02-01</td> 
   <td>230</td> 
   <td>001</td> 
  </tr> 
  <tr> 
   <td>s0005</td> 
   <td>酒水</td> 
   <td>2020-02-01</td> 
   <td>100</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>s0005</td> 
   <td>房费</td> 
   <td>2020-02-02</td> 
   <td>128</td> 
   <td>001</td> 
  </tr> 
 </tbody> 
</table>

1.  查询出房费都大于200的账号
    
        # 先过滤出所有的房费记录，然后按账号分组，取 最小的金额 >200 的账号
        SELECT
        	accounts 
        FROM
        	guest 
        WHERE
        	details = '房费' 
        GROUP BY
        	accounts 
        HAVING
        	min( money ) > 200
2.  查询出1月份每个账号酒水和房费的总金额
    
        查找某段时间内的数据，可以使用between ..and..,也可以使用> <
        数据库中between..and..会涉及到2个边界值，2个边界值都包括
        
        select accounts,sum(money) from guest where date between '2020-01-01' and '2020-01-31' group by accounts
3.  将不是房费的班次都更改为‘001’
    
        update guest set class='001' where details!='房费'
4.  查询出消费都大于100的账号
    
        # 思路：查询出每个账户的最低消费金额，判断最低消费金额是否大于100
        # 步骤：1. 求每个账户的最低消费金额
        	select accounts from guest group by accounts having min(money)>100

## 数据库脚本 ##

-- ----------------------------
    -- Table structure for 1_class
    -- ----------------------------
    DROP TABLE IF EXISTS `1_class`;
    CREATE TABLE `1_class`  (
      `id` int(11) NOT NULL,
      `class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of 1_class
    -- ----------------------------
    INSERT INTO `1_class` VALUES (1, '一班');
    INSERT INTO `1_class` VALUES (2, '二班');
    INSERT INTO `1_class` VALUES (3, '三班');
    INSERT INTO `1_class` VALUES (4, '四班');
    INSERT INTO `1_class` VALUES (5, '五班');
    
    -- ----------------------------
    -- Table structure for 1_score
    -- ----------------------------
    DROP TABLE IF EXISTS `1_score`;
    CREATE TABLE `1_score`  (
      `stu_id` int(11) NULL DEFAULT NULL,
      `chiness` int(3) NULL DEFAULT NULL,
      `math` int(3) NULL DEFAULT NULL
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of 1_score
    -- ----------------------------
    INSERT INTO `1_score` VALUES (1, 70, 47);
    INSERT INTO `1_score` VALUES (2, 80, 60);
    INSERT INTO `1_score` VALUES (3, 50, 82);
    INSERT INTO `1_score` VALUES (4, 80, 90);
    
    -- ----------------------------
    -- Table structure for 1_student
    -- ----------------------------
    DROP TABLE IF EXISTS `1_student`;
    CREATE TABLE `1_student`  (
      `id` int(11) NOT NULL,
      `stu_no` int(11) NULL DEFAULT NULL,
      `stu_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `stu_gender` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `class_id` int(11) NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of 1_student
    -- ----------------------------
    INSERT INTO `1_student` VALUES (1, 2020001, '张三', '男', 1);
    INSERT INTO `1_student` VALUES (2, 2020002, '李四', '男', 1);
    INSERT INTO `1_student` VALUES (3, 2020003, '李丽', '女', 2);
    INSERT INTO `1_student` VALUES (4, 2020004, '赵婷', '女', 3);
    INSERT INTO `1_student` VALUES (5, 2020005, '王五', '男', 3);
    
    -- ----------------------------
    -- Table structure for 3_course
    -- ----------------------------
    DROP TABLE IF EXISTS `3_course`;
    CREATE TABLE `3_course`  (
      `course_id` int(11) NOT NULL COMMENT '课程号',
      `course_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '课程名',
      `course_desc` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程介绍',
      PRIMARY KEY (`course_id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of 3_course
    -- ----------------------------
    INSERT INTO `3_course` VALUES (1, 'Java基础', NULL);
    INSERT INTO `3_course` VALUES (2, 'Java高级', NULL);
    INSERT INTO `3_course` VALUES (3, 'C#', NULL);
    INSERT INTO `3_course` VALUES (4, '计算机网络', NULL);
    INSERT INTO `3_course` VALUES (5, '算法与结构', NULL);
    INSERT INTO `3_course` VALUES (6, '数字模拟电子', NULL);
    
    -- ----------------------------
    -- Table structure for 3_student_score
    -- ----------------------------
    DROP TABLE IF EXISTS `3_student_score`;
    CREATE TABLE `3_student_score`  (
      `student_id` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学号',
      `student_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
      `student_gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
      `course_id` int(11) NULL DEFAULT NULL COMMENT '课程号',
      `score` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分数',
      `ismakeup` int(11) NULL DEFAULT NULL COMMENT '是否为补考(补考:1;非补考:0)'
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of 3_student_score
    -- ----------------------------
    INSERT INTO `3_student_score` VALUES ('1', '张三', '男', 1, '80', 1);
    INSERT INTO `3_student_score` VALUES ('1', '张三', '男', 1, '50', 0);
    INSERT INTO `3_student_score` VALUES ('1', '张三', '男', 2, '50', 0);
    INSERT INTO `3_student_score` VALUES ('1', '张三', '男', 3, '80', 0);
    INSERT INTO `3_student_score` VALUES ('3', '王二', '女', 1, '20', 0);
    INSERT INTO `3_student_score` VALUES ('1', '张三', '男', 2, '90', 1);
    INSERT INTO `3_student_score` VALUES ('3', '王二', '女', 1, '50', 1);
    INSERT INTO `3_student_score` VALUES ('4', '李四', '男', 4, '50', 0);
    
    -- ----------------------------
    -- Table structure for 5_student
    -- ----------------------------
    DROP TABLE IF EXISTS `5_student`;
    CREATE TABLE `5_student`  (
      `id` int(11) NOT NULL,
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of 5_student
    -- ----------------------------
    INSERT INTO `5_student` VALUES (1, '张三');
    INSERT INTO `5_student` VALUES (2, '张三');
    INSERT INTO `5_student` VALUES (3, '李四');
    INSERT INTO `5_student` VALUES (4, '王五');
    INSERT INTO `5_student` VALUES (5, '王五');
    INSERT INTO `5_student` VALUES (6, '王五');
    
    -- ----------------------------
    -- Table structure for course
    -- ----------------------------
    DROP TABLE IF EXISTS `course`;
    CREATE TABLE `course`  (
      `cno` int(11) NOT NULL,
      `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `tno` int(11) NULL DEFAULT NULL,
      PRIMARY KEY (`cno`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of course
    -- ----------------------------
    INSERT INTO `course` VALUES (1, 'Java基础', 1);
    INSERT INTO `course` VALUES (2, 'Java高级', 1);
    INSERT INTO `course` VALUES (3, 'C#', 2);
    INSERT INTO `course` VALUES (4, '计算机网络', 2);
    INSERT INTO `course` VALUES (5, '算法与结构', 4);
    INSERT INTO `course` VALUES (6, '数字模拟电子', 3);
    
    -- ----------------------------
    -- Table structure for dept
    -- ----------------------------
    DROP TABLE IF EXISTS `dept`;
    CREATE TABLE `dept`  (
      `deptno` int(11) NOT NULL COMMENT '部门编号',
      `dname` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `loc` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地点',
      PRIMARY KEY (`deptno`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of dept
    -- ----------------------------
    INSERT INTO `dept` VALUES (1, '研发部', NULL);
    INSERT INTO `dept` VALUES (2, '部门2', NULL);
    INSERT INTO `dept` VALUES (3, '部门3', NULL);
    INSERT INTO `dept` VALUES (4, '部门4', NULL);
    
    -- ----------------------------
    -- Table structure for emp
    -- ----------------------------
    DROP TABLE IF EXISTS `emp`;
    CREATE TABLE `emp`  (
      `empno` int(11) NOT NULL COMMENT '员工编号',
      `ename` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
      `job` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工作岗位',
      `mgr` int(11) NULL DEFAULT NULL COMMENT '上级领导编号',
      `hiredate` date NULL DEFAULT NULL COMMENT '雇佣日期',
      `sal` int(11) NULL DEFAULT NULL COMMENT '薪金',
      `comm` int(11) NULL DEFAULT NULL COMMENT '佣金',
      `deptno` int(11) NULL DEFAULT NULL COMMENT '部门编号',
      PRIMARY KEY (`empno`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of emp
    -- ----------------------------
    INSERT INTO `emp` VALUES (1, '员工1', 'clerk', 3, '2021-05-11', 1100, 100, 1);
    INSERT INTO `emp` VALUES (2, '员工2', '2', 3, '2021-05-12', 1200, 200, 1);
    INSERT INTO `emp` VALUES (3, '领导3', 'manageer', NULL, '2021-05-12', 6000, 600, 1);
    INSERT INTO `emp` VALUES (4, '员工4', 'clerk', 5, '2021-05-14', 1400, 400, 2);
    INSERT INTO `emp` VALUES (5, '领导5', 'manageer', 6, '2021-05-13', 6300, 600, 2);
    INSERT INTO `emp` VALUES (6, '领导6', 'manageer', NULL, '2021-05-14', 7300, 700, 2);
    
    -- ----------------------------
    -- Table structure for guest
    -- ----------------------------
    DROP TABLE IF EXISTS `guest`;
    CREATE TABLE `guest`  (
      `accounts` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '账号',
      `details` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '消费',
      `date` date NULL DEFAULT NULL COMMENT '时间',
      `money` int(11) NULL DEFAULT NULL COMMENT '金额',
      `class` int(11) NULL DEFAULT NULL COMMENT '班次'
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of guest
    -- ----------------------------
    INSERT INTO `guest` VALUES ('s0001', '房费', '2020-01-01', 280, 1);
    INSERT INTO `guest` VALUES ('s0001', '酒水', '2020-01-02', 120, 1);
    INSERT INTO `guest` VALUES ('s0001', '房费', '2020-01-08', 300, 3);
    INSERT INTO `guest` VALUES ('s0002', '酒水', '2020-01-29', 50, NULL);
    INSERT INTO `guest` VALUES ('s0003', '房费', '2020-01-31', 180, 2);
    INSERT INTO `guest` VALUES ('s0004', '房费', '2020-02-01', 230, 1);
    INSERT INTO `guest` VALUES ('s0005', '酒水', '2020-02-01', 100, NULL);
    INSERT INTO `guest` VALUES ('s0005', '房费', '2020-02-02', 128, 1);
    
    -- ----------------------------
    -- Table structure for sc
    -- ----------------------------
    DROP TABLE IF EXISTS `sc`;
    CREATE TABLE `sc`  (
      `sno` int(11) NOT NULL,
      `cno` int(11) NULL DEFAULT NULL,
      `score` int(255) NULL DEFAULT NULL
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of sc
    -- ----------------------------
    INSERT INTO `sc` VALUES (1001, 1, 90);
    INSERT INTO `sc` VALUES (1001, 2, 80);
    INSERT INTO `sc` VALUES (1001, 3, 60);
    INSERT INTO `sc` VALUES (1002, 1, 56);
    INSERT INTO `sc` VALUES (1002, 2, 90);
    INSERT INTO `sc` VALUES (1002, 4, 66);
    INSERT INTO `sc` VALUES (1003, 1, 90);
    INSERT INTO `sc` VALUES (1003, 2, 70);
    INSERT INTO `sc` VALUES (1003, 4, 60);
    INSERT INTO `sc` VALUES (1003, 3, 80);
    
    -- ----------------------------
    -- Table structure for score
    -- ----------------------------
    DROP TABLE IF EXISTS `score`;
    CREATE TABLE `score`  (
      `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `course` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `score` int(200) NULL DEFAULT NULL
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of score
    -- ----------------------------
    INSERT INTO `score` VALUES ('张三', '语文', 81);
    INSERT INTO `score` VALUES ('张三', '数学', 75);
    INSERT INTO `score` VALUES ('李四', '语文', 76);
    INSERT INTO `score` VALUES ('王五', '语文', 81);
    INSERT INTO `score` VALUES ('王五', '数学', 100);
    INSERT INTO `score` VALUES ('王五', '英语', 90);
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student`  (
      `sno` int(11) NOT NULL,
      `sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `sage` int(255) NULL DEFAULT NULL,
      `ssex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`sno`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES (1001, 'tom', 22, '男');
    INSERT INTO `student` VALUES (1002, 'jack', 23, '男');
    INSERT INTO `student` VALUES (1003, 'rose', 24, '女');
    
    -- ----------------------------
    -- Table structure for teacher
    -- ----------------------------
    DROP TABLE IF EXISTS `teacher`;
    CREATE TABLE `teacher`  (
      `tno` int(11) NOT NULL,
      `tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`tno`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of teacher
    -- ----------------------------
    INSERT INTO `teacher` VALUES (1, '张力');
    INSERT INTO `teacher` VALUES (2, '李四');
    INSERT INTO `teacher` VALUES (3, '王强');
    INSERT INTO `teacher` VALUES (4, '李晓燕');

[https_blog.csdn.net_shuai8624_article_details_116836863]: https://blog.csdn.net/shuai8624/article/details/116836863