【0928 | Day 39】三大范式/数据库查询练习

目录

一、数据库的三大设计范式

1. 第一范式：数据中所有字段都是不可分割的原子值

倘若字段可以继续拆分，就不满足第一范式，举个例子：

==》创建一个表student2，再向里面插入数据，结果如下

mysql> select * from student2;
+----+--------+-----------------------------+
| id | name   | address                     |
+----+--------+-----------------------------+
|  1 | 张三   | 安徽省合肥市蜀山区          |
|  2 | 李四   | 安徽省合肥市庐阳区          |
|  3 | 王二   | 安徽省合肥市包河区          |
+----+--------+-----------------------------+

3 rows in set (0.11 sec)

分析：其实还是可以拆分的可以再分成省份、城市、区，上图就是可拆分，不满足第一范式。我们要把表拆的详细一点，后期方便统计。

注意：范式设计的越详细，对某些实际操作可能更好，但是不一定都是好处。

2. 第二范式：必须满足第一范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖于主键

如果出现不完全依赖，只可能发送在联合主键的情况下。
==》下面我们创建一个表，用来当做订单

mysql> create table myorser(
    -> product_id int,           #产品号
    -> customer_id int,          #用户号
    -> product_name varchar(20),
    -> customer_name varchar(20),
    -> primary key(product_id,customer_id)     #产品号和用户号形成联合主键
    -> );
    
Query OK, 0 rows affected (1.21 sec)

分析：除主键外其他列，只依赖于主键的部分字段。产品的名字只和产品号有关、用户的名字只和用户号有关，就是不完全依赖于主键，比满足第二范式！！
　　

解决方法如下，拆表：

mysql> create table myorder2(     #订单id表
    -> order_id int primary key,
    -> product_id int,
    -> customer_id int
    -> );
    
Query OK, 0 rows affected (0.99 sec)

mysql> create table product(    #产品名表，依赖于产品id
    -> id int primary key,
    -> name varchar(20)
    -> );
    
Query OK, 0 rows affected (0.93 sec)

mysql> create table customer(   #顾客名表，依赖于顾客id
    -> id int primary key,
    -> name varchar(20)
    -> );
    
Query OK, 0 rows affected (0.82 sec)

3. 第三范式：必须满足第二范式，除主键列的其他列之间不能有传递依赖关系

==》看这个例子：

mysql> create table myorder2(    
    -> order_id int primary key,
    -> product_id int,
    -> customer_id int，
    -> customer_phone varchar(20)
    -> );
    
Query OK, 0 rows affected (0.99 sec)

分析：这里相比上面订单id表，多了一个顾客手机，很明显customer_phone和order_id主键有关系，但是customer_phone还依赖于customer_id（除主键外的其他键）。这就不满足第三范式了，应该将顾客的手机放入顾客表中才满足第三范式。

二、查询练习

1. 准备

==》准备创建几个表：

  • 学生表（Student）：学号、姓名、性别、出生年月日、班级
  • 课程表（Course）：课程号、课程名称、教师编号
  • 成绩表（Score）　：学号、课程号、成绩
  • 教师表（Teacher）：教师编号、教师性别、教师性别、出生年月日、职称、所在部门
#创建一个test2新数据库
mysql> create database `test2` character set utf8;    
Query OK, 1 row affected, 1 warning (0.67 sec)

#创建学生表
mysql> create table student(        
    -> snumber varchar(20) primary key,
    -> sname varchar(20) not null,
    -> ssex varchar(20) not null,
    -> sbirthday datetime,
    -> class varchar(20)
    -> );
    
Query OK, 0 rows affected (1.06 sec)

#创建老师表
mysql> create table teacher(
    -> tnumber varchar(20) primary key,
    -> tname varchar(20) not null,
    -> tsex varchar(20) not null,
    -> tbirthday datetime,
    -> prof varchar(20) not null,
    -> depart varchar(20) not null
    -> );
    
Query OK, 0 rows affected (0.88 sec)

#创建课程表
mysql> create table course(
    -> cnumber varchar(20) primary key,
    -> cname varchar(20) not null,
    -> tnumber varchar(20) not null,
    　　　#其中tnumber和老师表中的tnumber一样，使用外键
    -> foreign key(tnumber) references teacher(tnumber) 
    -> );
    
Query OK, 0 rows affected (1.02 sec)

#创建成绩表
mysql> create table score(
    -> snumber varchar(20) not null,
    -> cnumber varchar(20) not null,
    -> degree decimal,
    -> foreign key(snumber) references student(snumber),
    -> foreign key(cnumber) references course(cnumber),
       #一个联合主键，学生号和课程号不重复就好
    -> primary key(snumber,cnumber) 
    -> );
    
Query OK, 0 rows affected (1.01 sec)

2. 导入数据

mysql> select * from student;
+---------+-----------+------+---------------------+--------+
| snumber | sname     | ssex | sbirthday           | class  |
+---------+-----------+------+---------------------+--------+
| 100     | 张三      | 男   | 1999-09-01 00:00:00 | 一班   |
| 101     | 李四      | 男   | 1999-02-11 00:00:00 | 一班   |
| 102     | 王二      | 女   | 1999-09-23 00:00:00 | 一班   |
| 103     | 王尼玛    | 男   | 1988-01-11 00:00:00 | 一班   |
| 104     | 张全蛋    | 男   | 2000-09-03 00:00:00 | 一班   |
| 105     | 赵铁柱    | 男   | 1983-04-05 00:00:00 | 二班   |
| 106     | 木子      | 女   | 2000-12-16 00:00:00 | 二班   |
+---------+-----------+------+---------------------+--------+
7 rows in set (0.00 sec)

mysql> select * from teacher;
+---------+--------+------+---------------------+-----------+-----------------+
| tnumber | tname  | tsex | tbirthday           | prof      | depart          |
+---------+--------+------+---------------------+-----------+-----------------+
| 111     | 古一   | 女   | 0000-01-01 00:00:00 | 教授      | 化学系          |
| 112     | 王     | 男   | 2000-09-03 00:00:00 | 副教授    | 计算机系        |
| 113     | 春丽   | 女   | 1988-11-05 00:00:00 | 助教      | 英语系          |
| 114     | 刘邦   | 男   | 1978-12-03 00:00:00 | 助教      | 通信工程系      |
+---------+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)

mysql> select * from course;
+---------+--------------+---------+
| cnumber | cname        | tnumber |
+---------+--------------+---------+
| 3-105   | 数据结构     | 112     |
| 3-245   | 模拟电路     | 113     |
| 6-166   | 人工智能     | 111     |
| 9-888   | 数字电路     | 114     |
+---------+--------------+---------+
4 rows in set (0.00 sec)

mysql> select * from score;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 100     | 3-245   |     85 |
| 101     | 3-245   |     95 |
| 102     | 3-105   |     83 |
| 103     | 3-105   |     89 |
| 104     | 3-245   |     66 |
| 105     | 6-166   |     60 |
| 106     | 6-166   |     92 |
+---------+---------+--------+
7 rows in set (0.00 sec)

3. 练习

1)

  • 查询student表中的所有记录
  • 用法：mysql> select * from 　student;
mysql> select * from student;
# 其中 * 表示所有字段的意思

+---------+-----------+------+---------------------+--------+
| snumber | sname     | ssex | sbirthday           | class  |
+---------+-----------+------+---------------------+--------+
| 100     | 张三      | 男   | 1999-09-01 00:00:00 | 一班   |
| 101     | 李四      | 男   | 1999-02-11 00:00:00 | 一班   |
| 102     | 王二      | 女   | 1999-09-23 00:00:00 | 一班   |
| 103     | 王尼玛    | 男   | 1988-01-11 00:00:00 | 一班   |
| 104     | 张全蛋    | 男   | 2000-09-03 00:00:00 | 一班   |
| 105     | 赵铁柱    | 男   | 1983-04-05 00:00:00 | 二班   |
| 106     | 木子      | 女   | 2000-12-16 00:00:00 | 二班   |
+---------+-----------+------+---------------------+--------+
7 rows in set (0.00 sec)

2)

  • 查询student表中所有记录的sname、ssex、class列
  • 用法：mysql> select + 要查询的列（多个用逗号隔开） + from + 表名;
mysql> select sname,ssex,class from student;
+-----------+------+--------+
| sname     | ssex | class  |
+-----------+------+--------+
| 张三      | 男   | 一班   |
| 李四      | 男   | 一班   |
| 王二      | 女   | 一班   |
| 王尼玛    | 男   | 一班   |
| 张全蛋    | 男   | 一班   |
| 赵铁柱    | 男   | 二班   |
| 木子      | 女   | 二班   |
+-----------+------+--------+
7 rows in set (0.00 sec)

3)

  • 查询教师的所有单位，即不重复的depart列
  • 用法：mysql> select distinct depart from teacher;
mysql> select depart from teacher;
+-----------------+
| depart          |
+-----------------+
| 化学系          |
| 计算机系        |
| 通信工程系      |
| 通信工程系      |
+-----------------+
4 rows in set (0.00 sec)


mysql> select distinct depart from teacher;
+-----------------+
| depart          |
+-----------------+
| 化学系          |
| 计算机系        |
| 通信工程系      |
+-----------------+
3 rows in set (0.10 sec)

4)

  • 查询score表中成绩 60到90 之间的所有记录
  • 用法一：mysql> select * from score where degree between 60 and 80;（between包括端点值）
  • 用法二：mysql> select * from score where degree >= 60 and degree<= 90;
mysql> select * from score where degree between 60 and 90;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 100     | 3-245   |     85 |
| 102     | 3-105   |     83 |
| 103     | 3-105   |     89 |
| 104     | 3-245   |     66 |
| 105     | 6-166   |     60 |
+---------+---------+--------+
5 rows in set (0.00 sec)

5)

  • 查询score表中85、95或83的记录
  • 用法：in
mysql> select * from score where degree in (85,95,83);
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 100     | 3-245   |     85 |
| 101     | 3-245   |     95 |
| 102     | 3-105   |     83 |
+---------+---------+--------+
3 rows in set (0.00 sec)

6)

  • 查询student表中班级为一班或性别为女的同学记录
  • 用法：or
mysql> select * from student where class='一班' or ssex='女';
+---------+-----------+------+---------------------+--------+
| snumber | sname     | ssex | sbirthday           | class  |
+---------+-----------+------+---------------------+--------+
| 100     | 张三       | 男   | 1999-09-01 00:00:00 | 一班   |
| 101     | 李四       | 男   | 1999-02-11 00:00:00 | 一班   |
| 102     | 王二       | 女   | 1999-09-23 00:00:00 | 一班   |
| 103     | 王尼玛     | 男   | 1988-01-11 00:00:00 | 一班   |
| 104     | 张全蛋     | 男   | 2000-09-03 00:00:00 | 一班   |
| 106     | 木子       | 女   | 2000-12-16 00:00:00 | 二班   |
+---------+-----------+------+---------------------+--------+
6 rows in set (0.00 sec)

7)

  • 按照学号（snumber）升序降序的方式查询student表中的记录
  • 用法（降序）：mysql> select * from student order by snumber（什么字段） desc（降序）;
  • 用法一（升序）：mysql> select * from student order by snumber（什么字段） asc（升序）;
  • 用法二（升序）：mysql> select * from student order by snumber;（升序两种方式一样）
mysql> select * from student order by snumber desc;
+---------+-----------+------+---------------------+--------+
| snumber | sname     | ssex | sbirthday           | class  |
+---------+-----------+------+---------------------+--------+
| 106     | 木子      | 女   | 2000-12-16 00:00:00 | 二班   |
| 105     | 赵铁柱    | 男   | 1983-04-05 00:00:00 | 二班   |
| 104     | 张全蛋    | 男   | 2000-09-03 00:00:00 | 一班   |
| 103     | 王尼玛    | 男   | 1988-01-11 00:00:00 | 一班   |
| 102     | 王二      | 女   | 1999-09-23 00:00:00 | 一班   |
| 101     | 李四      | 男   | 1999-02-11 00:00:00 | 一班   |
| 100     | 张三      | 男   | 1999-09-01 00:00:00 | 一班   |
+---------+-----------+------+---------------------+--------+

7 rows in set (0.00 sec)

8)

  • 按照教师号（cnumber）升序、成绩（degree）降序查询score表中的记录
  • 用法：mysql> select * from score order by cnumber asc ,degree desc;
ysql> select * from score order by cnumber asc ,degree desc;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 103     | 3-105   |     89 |
| 102     | 3-105   |     83 |
| 101     | 3-245   |     95 |
| 100     | 3-245   |     85 |
| 104     | 3-245   |     66 |
| 106     | 6-166   |     92 |
| 105     | 6-166   |     60 |
+---------+---------+--------+
7 rows in set (0.00 sec)

分析：这条语句会先按照教师号升序排列，遇到相同的教师号再按照成绩降序进行排列（order by 先按照第一个排，再考虑第二个排列）

注意：当两个同时排序的话，asc（升序）必须要写（sql8.0不写也没事）

9)

  • 查询一班的人数
  • 用法：count
mysql> select count(*) from student where class='一班';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.15 sec)

10)

  • 查询score表中最高分的学生学号和课程号
  • 用法：mysql> select snumber,cnumber from score where degree=(select max(degree) from score);
mysql> select snumber,cnumber from score where degree=(select max(degree) from score);
+---------+---------+
| snumber | cnumber |
+---------+---------+
| 101     | 3-245   |
+---------+---------+
1 row in set (0.35 sec)

对于这个复合语句进行拆分：

  1. 找到最高分
    select max(degree) from score
  2. 找到最高分的学号和课程号
    mysql> select snumber,cnumber from score where degree=(select max(degree) from score);

11)

  • 排序的做法

  • 用法：mysql> select snumber,cnumber,degree from score order by degree desc limit 0,1;

mysql> select snumber,cnumber,degree from score order by degree desc  limit 0,1;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 101     | 3-245   |     95 |
+---------+---------+--------+
1 row in set (0.00 sec)

分析：这里的 limit 0,1** 表示取表中从第0条取到第一条（也就是取出第一条数据）

注意：limit 的第一个数字表示从哪里开始查，第二个数字表示查几条

12)

  • 查询每门课的平均成绩
  • 用法：avg（degree）
mysql> select * from course;
+---------+--------------+---------+
| cnumber | cname        | tnumber |
+---------+--------------+---------+
| 3-105   | 数据结构     | 112     |
| 3-245   | 模拟电路     | 113     |
| 6-166   | 人工智能     | 111     |
| 9-888   | 数字电路     | 114     |
+---------+--------------+---------+
4 rows in set (0.00 sec)
  • 查询3-105老师带的数据结构这门课的平均成绩
#先看一下这门课的学生所有成绩
mysql> select degree from score where cnumber='3-105';
+--------+
| degree |
+--------+
|     83 |
|     89 |
+--------+
2 rows in set (0.11 sec)

#计算平均成绩
mysql> select avg(degree) from score where cnumber='3-105';
+-------------+
| avg(degree) |
+-------------+
|     86.0000 |
+-------------+
1 row in set (0.02 sec)
  • 但是我们这只计算了一门，怎么计算每一门呢？一条一条语句的写是可以的，但是比较麻烦，下面写在一条语句中：
  • 用法： group by ，先把课程号分组再进行计算
mysql> select cnumber,avg(degree) from score group by cnumber;
+---------+-------------+
| cnumber | avg(degree) |
+---------+-------------+
| 3-105   |     86.0000 |
| 3-245   |     82.0000 |
| 6-166   |     76.0000 |
+---------+-------------+
3 rows in set (0.00 sec)

13)

  • 查询score表中至少有两名学生选修，并以3开头的课程平均成绩（分组条件与模糊查询）

  • 用法：group by + having + 条件（分组后跟条件要使用having）

==》第一步：score表中至少有两名学生选修

mysql> select cnumber from score 
    ->  group by cnumber  #分组
    -> having count(cnumber)>=2; #条件
+---------+
| cnumber |
+---------+
| 3-105   |
| 3-245   |
| 6-166   |
+---------+
3 rows in set (0.00 sec)

mysql> select cnumber from score group by cnumber
    -> having count(cnumber)>=4;#条件
Empty set (0.00 sec)

问题：有个疑问，为什么要count（cnumber）？

  • 因为这里cnumber是课程号，count是求和关键字，score表中课程数大于等于2的就是至少两人选的课程

注意：以3开头，这里可以用 模糊查询（使用 like）

==》第二步：以3开头的课程平均成绩

mysql> select cnumber from score group by cnumber
    -> having count(cnumber)>=2 and cnumber like '3%'; 
   　　　　　　　　　　  #3%表示以3开头， %为3后面的任意匹配
+---------+
| cnumber |
+---------+
| 3-105   |
| 3-245   |
+---------+
2 rows in set (0.35 sec)

==》第三步：计算平均值和这门课的人数

mysql> select cnumber,avg(degree),count(*) from score group by cnumber
    -> having count(cnumber)>=2 and cnumber like '3%';
+---------+-------------+----------+
| cnumber | avg(degree) | count(*) |
+---------+-------------+----------+
| 3-105   |     86.0000 |        2 |
| 3-245   |     82.0000 |        3 |
+---------+-------------+----------+
2 rows in set (0.00 sec)

14)

  • 查询成绩大于70，小于90的列
  • 用法一：where + 条件
mysql> select snumber,degree from score
    -> where degree>70 and degree<90;
+---------+--------+
| snumber | degree |
+---------+--------+
| 100     |     85 |
| 102     |     83 |
| 103     |     89 |
+---------+--------+
3 rows in set (0.00 sec)
  • 用法二：between...and...
mysql> select snumber,degree from score
    -> where degree between 70 and 90;

15)

  • 查询所有学生的sname、cnumber、degree（多表查询）
  • 当要查询的内容不在一张表中时，我们可以分开查询，但是太麻烦了。

==》第一步：多表查询

mysql> select snumber,cnumber,degree from score;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 100     | 3-245   |     85 |
| 101     | 3-245   |     95 |
| 102     | 3-105   |     83 |
| 103     | 3-105   |     89 |
| 104     | 3-245   |     66 |
| 105     | 6-166   |     60 |
| 106     | 6-166   |     92 |
+---------+---------+--------+
7 rows in set (0.00 sec)

mysql> select snumber,sname from student;
+---------+-----------+
| snumber | sname     |
+---------+-----------+
| 100     | 张三      |
| 101     | 李四      |
| 102     | 王二      |
| 103     | 王尼玛    |
| 104     | 张全蛋    |
| 105     | 赵铁柱    |
| 106     | 木子      |
+---------+-----------+
7 rows in set (0.00 sec)

==》第二步：把score表中snumber替换成对应的姓名sname

mysql> select sname,cnumber,degree from student,score
    -> where student.snumber=score.snumber;  #加上限制条件，不然会乱
+-----------+---------+--------+
| sname     | cnumber | degree |
+-----------+---------+--------+
| 张三      | 3-245   |     85 |
| 李四      | 3-245   |     95 |
| 王二      | 3-105   |     83 |
| 王尼玛    | 3-105   |     89 |
| 张全蛋    | 3-245   |     66 |
| 赵铁柱    | 6-166   |     60 |
| 木子      | 6-166   |     92 |
+-----------+---------+--------+
7 rows in set (0.00 sec)

16)

  • 查询所有学生的snumber、cname、degree（多表查询）

==》第一步：先查询一下这两个表中的内容

mysql> select * from score;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 100     | 3-245   |     85 |
| 101     | 3-245   |     95 |
| 102     | 3-105   |     83 |
| 103     | 3-105   |     89 |
| 104     | 3-245   |     66 |
| 105     | 6-166   |     60 |
| 106     | 6-166   |     92 |
+---------+---------+--------+
7 rows in set (0.00 sec)

mysql> select * from course;
+---------+--------------+---------+
| cnumber | cname        | tnumber |
+---------+--------------+---------+
| 3-105   | 数据结构     | 112     |
| 3-245   | 模拟电路     | 113     |
| 6-166   | 人工智能     | 111     |
| 9-888   | 数字电路     | 114     |
+---------+--------------+---------+
4 rows in set (0.00 sec)

==》第二步：cnumber是一样的，通过这个来写条件

mysql> select cname,snumber,degree from score,course
    -> where score.cnumber=course.cnumber;
+--------------+---------+--------+
| cname        | snumber | degree |
+--------------+---------+--------+
| 模拟电路     | 100     |     85 |
| 模拟电路     | 101     |     95 |
| 数据结构     | 102     |     83 |
| 数据结构     | 103     |     89 |
| 模拟电路     | 104     |     66 |
| 人工智能     | 105     |     60 |
| 人工智能     | 106     |     92 |
+--------------+---------+--------+
7 rows in set (0.00 sec)

17)

  • 查询所有学生的cname、sname，degree（三表关联查询）

==》第一步：找两两之间的相同之处，写条件

mysql> select sname,cname,degree from student,course,score #三个数据来自三个表
    -> where student.snumber=score.snumber  #利用score表中的重复字段来查询
    ->and course.cnumber=score.cnumber;
+-----------+--------------+--------+
| sname     | cname        | degree |
+-----------+--------------+--------+
| 张三      | 模拟电路     |     85 |
| 李四      | 模拟电路     |     95 |
| 王二      | 数据结构     |     83 |
| 王尼玛    | 数据结构     |     89 |
| 张全蛋    | 模拟电路     |     66 |
| 赵铁柱    | 人工智能     |     60 |
| 木子      | 人工智能     |     92 |
+-----------+--------------+--------+
7 rows in set (0.00 sec)

==》第二步：再查询下cnumber和snumber

mysql> select sname,cname,degree,student.snumber,course.cnumber from student,course,score
    -> where student.snumber=score.snumber and course.cnumber=score.cnumber;
+-----------+--------------+--------+---------+---------+
| sname     | cname        | degree | snumber | cnumber |
+-----------+--------------+--------+---------+---------+
| 张三      | 模拟电路     |     85 | 100     | 3-245   |
| 李四      | 模拟电路     |     95 | 101     | 3-245   |
| 王二      | 数据结构     |     83 | 102     | 3-105   |
| 王尼玛    | 数据结构     |     89 | 103     | 3-105   |
| 张全蛋    | 模拟电路     |     66 | 104     | 3-245   |
| 赵铁柱    | 人工智能     |     60 | 105     | 6-166   |
| 木子      | 人工智能     |     92 | 106     | 6-166   |
+-----------+--------------+--------+---------+---------+
7 rows in set (0.00 sec)

注意：这里要查询的snumber和cnumber都加了条件。

  • 因为这两个数据出现在多个表中，如果不指定电脑不知道找哪一个表中的（尽管都相同），但是会报错：
    ERROR 1052 (23000): Column 'snumber' in field list is ambiguous

我们可以验证一下：

mysql> select sname,cname,degree,student.snumber as stu_num,score.snumber ,course.cnumber from student,course,score
    -> where student.snumber=score.snumber and course.cnumber=score.cnumber;
+-----------+--------------+--------+---------+---------+---------+
| sname     | cname        | degree | stu_num | snumber | cnumber |
+-----------+--------------+--------+---------+---------+---------+
| 张三      | 模拟电路     |     85 | 100     | 100     | 3-245   |
| 李四      | 模拟电路     |     95 | 101     | 101     | 3-245   |
| 王二      | 数据结构     |     83 | 102     | 102     | 3-105   |
| 王尼玛    | 数据结构     |     89 | 103     | 103     | 3-105   |
| 张全蛋    | 模拟电路     |     66 | 104     | 104     | 3-245   |
| 赵铁柱    | 人工智能     |     60 | 105     | 105     | 6-166   |
| 木子      | 人工智能     |     92 | 106     | 106     | 6-166   |
+-----------+--------------+--------+---------+---------+---------+
7 rows in set (0.00 sec)

其中的student.snumber as stu_num可以给要显示的列进行更名，但是仅仅局限于此次查询。通过结果可发现结果是相同的

18)

  • 查询一班学生每门课的平均成绩

==》第一步：

mysql> select avg(degree) from score
          where snumber in (select snumber from student where class='一班');
+-------------+
| avg(degree) |
+-------------+
|     83.6000 |
+-------------+
1 row in set (0.18 sec)

但是这是所有课程的平均成绩，我们要求的是每门课的平均成绩，其实就是按照老师号进行分组即可 group by cnumber

mysql> select cnumber, avg(degree) from score where snumber in (select snumber from student where class='一班')
    -> group by cnumber;  #按照老师号进行分组
+---------+-------------+
| cnumber | avg(degree) |
+---------+-------------+
| 3-245   |     82.0000 |
| 3-105   |     86.0000 |
+---------+-------------+
2 rows in set (0.16 sec)

19)

  • 查询选修‘3-105’课程中成绩高于102号同学成绩的同学记录（子查询）

==》第一步：先把102号同学的3-105课程的成绩导出来，用到了and（同时）

mysql> select degree from score where snumber='102' and cnumber='3-105';
+--------+
| degree |
+--------+
|     83 |
+--------+
1 row in set (0.00 sec)

==》再加一个3-105课程就可以筛选出来

mysql> select snumber,degree from score where
     #成绩条件
    -> degree>(select degree from score where snumber='102' and cnumber='3-105')   
     #课程号条件
    -> and cnumber='3-105'; 
+---------+--------+
| snumber | degree |
+---------+--------+
| 103     |     89 |
+---------+--------+
1 row in set (0.03 sec)

20)

  • 查询所有课程的成绩高于‘3-105’课程中成绩高于102号同学成绩的同学记录
mysql> select snumber,degree from score where
    -> degree>(select degree from score where snumber='102' and cnumber='3-105');
+---------+--------+
| snumber | degree |
+---------+--------+
| 100     |     85 |
| 101     |     95 |
| 103     |     89 |
| 106     |     92 |
+---------+--------+
4 rows in set (0.00 sec)

21)

  • 查询学号为100、104 的同学同年出生的所有学生的snumber、sname和sbirthday

==》第一步：先看一眼student表

mysql> select * from student;
+---------+-----------+------+---------------------+--------+
| snumber | sname     | ssex | sbirthday           | class  |
+---------+-----------+------+---------------------+--------+
| 100     | 张三      | 男   | 1999-09-01 00:00:00 | 一班   |
| 101     | 李四      | 男   | 1999-02-11 00:00:00 | 一班   |
| 102     | 王二      | 女   | 1999-09-23 00:00:00 | 一班   |
| 103     | 王尼玛    | 男   | 1988-01-11 00:00:00 | 一班   |
| 104     | 张全蛋    | 男   | 2000-09-03 00:00:00 | 一班   |
| 105     | 赵铁柱    | 男   | 1983-04-05 00:00:00 | 二班   |
| 106     | 木子      | 女   | 2000-12-16 00:00:00 | 二班   |
+---------+-----------+------+---------------------+--------+
7 rows in set (0.00 sec)

==》第二步：通过year（）函数

mysql> select year(sbirthday) from student where snumber in (100,104);
+-----------------+
| year(sbirthday) |
+-----------------+
|            1999 |
|            2000 |
+-----------------+
2 rows in set (0.04 sec)

==》第三步：有了年份，就可以进行筛选了，注意这里不能用 = 来做条件因为这里的年份是两个值，应该用 in，有一个条件用 =，两个以上条件用 in

mysql> select snumber,sname,sbirthday from student
  -> where year(sbirthday) in (select year(sbirthday) from student where snumber in (100,104));
+---------+-----------+---------------------+
| snumber | sname     | sbirthday           |
+---------+-----------+---------------------+
| 100     | 张三      | 1999-09-01 00:00:00 |
| 101     | 李四      | 1999-02-11 00:00:00 |
| 102     | 王二      | 1999-09-23 00:00:00 |
| 104     | 张全蛋    | 2000-09-03 00:00:00 |
| 106     | 木子      | 2000-12-16 00:00:00 |
+---------+-----------+---------------------+
5 rows in set (0.04 sec)

22)

  • 查询‘古一’老师任课的学生成绩（多层嵌套子查询）

==》第一步：先看一下古一的个人信息

mysql> select * from teacher where tname='古一';
+---------+--------+------+---------------------+--------+-----------+
| tnumber | tname  | tsex | tbirthday           | prof   | depart    |
+---------+--------+------+---------------------+--------+-----------+
| 111     | 古一   | 女   | 0000-01-01 00:00:00 | 教授   | 化学系    |
+---------+--------+------+---------------------+--------+-----------+
1 row in set (0.00 sec)

==》第二步：从这个表中得到古一的tnumber，再根据tnumner在course表中找到她教的课程的cnumber号

mysql> select cnumber from course 
     > where tnumber=(select tnumber from teacher where tname='古一');
+---------+
| cnumber |
+---------+
| 6-166   |
+---------+
1 row in set (0.00 sec)

==》第三步：知道了cnumber号就可以从score表中得到她教这门课的平均成绩了

mysql> select avg(degree) from score
    -> where cnumber=( select cnumber from course 
    -> where tnumber=(select tnumber from teacher where tname='古一') );
+-------------+
| avg(degree) |
+-------------+
|     76.0000 |
+-------------+
1 row in set (0.01 sec)

总结：多层嵌套的子查询，查询结果作为另一个的条件

23)

  • 查询选修某门课人数多于2人的教师姓名
  • 先查询人数多于2人的课程号，再查询老师的tnumber，再查询老师的姓名，步步嵌套
mysql> select tname from teacher
    #条件3：以条件2查找老师的名字
    -> where tnumber = 
    #条件2：以条件1位条件找到 老师的tnumber，
    ->(select tnumber from course  where cnumber=
    #条件1：人数多于2人的课程号
    -> ( select cnumber from score  group by cnumber having count(*)>2 ) );
+--------+
| tname  |
+--------+
| 春丽   |
+--------+
1 row in set (0.00 sec)

24)

  • 查询一班二班全体学生记录
  • 用法：in
mysql> select * from student where class in ('一班','二班');
+---------+-----------+------+---------------------+--------+
| snumber | sname     | ssex | sbirthday           | class  |
+---------+-----------+------+---------------------+--------+
| 100     | 张三      | 男   | 1999-09-01 00:00:00 | 一班   |
| 101     | 李四      | 男   | 1999-02-11 00:00:00 | 一班   |
| 102     | 王二      | 女   | 1999-09-23 00:00:00 | 一班   |
| 103     | 王尼玛    | 男   | 1988-01-11 00:00:00 | 一班   |
| 104     | 张全蛋    | 男   | 2000-09-03 00:00:00 | 一班   |
| 105     | 赵铁柱    | 男   | 1983-04-05 00:00:00 | 二班   |
| 106     | 木子      | 女   | 2000-12-16 00:00:00 | 二班   |
+---------+-----------+------+---------------------+--------+
7 rows in set (0.25 sec)

25)

  • 查询存在85分以上成绩的课程号
mysql> select * from score;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 100     | 3-245   |     85 |
| 101     | 3-245   |     95 |
| 102     | 3-105   |     83 |
| 103     | 3-105   |     89 |
| 104     | 3-245   |     66 |
| 105     | 6-166   |     60 |
| 106     | 6-166   |     92 |
+---------+---------+--------+
7 rows in set (0.05 sec)


mysql> select cnumber from score where degree >85;
+---------+
| cnumber |
+---------+
| 3-245   |
| 3-105   |
| 6-166   |
+---------+
3 rows in set (0.12 sec)

26)

  • 查询通信工程系教师所教课程的成绩表
mysql> select * from score where cnumber in (select cnumber from course where tnumber in (select tnumber from teacher where depart='通信工程系') );
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 100     | 3-245   |     85 |
| 101     | 3-245   |     95 |
| 104     | 3-245   |     66 |
+---------+---------+--------+
3 rows in set (0.00 sec)

27)

  • 查询计算机系 与化学系 不同职称的教师的tname和prof（职称）

==》第一步：查询计算机系与化学系中职称不相同的老师 ，用到not in，他们的职称在其他系没有出现过的老师

mysql> select * from teacher where depart='计算机系' 
       and prof not in(select prof from teacher where depart='通信工程系');
+---------+-------+------+---------------------+-----------+--------------+
| tnumber | tname | tsex | tbirthday           | prof      | depart       |
+---------+-------+------+---------------------+-----------+--------------+
| 112     | 王    | 男   | 2000-09-03 00:00:00 | 副教授    | 计算机系     |
+---------+-------+------+---------------------+-----------+--------------+
1 row in set (0.51 sec)

==》第二步：再反过来查通信工程系中与计算机系中不重复的

mysql> select * from teacher where depart='计算机系' 
       and prof not in(select prof from teacher where depart='通信工程系');
+---------+-------+------+---------------------+-----------+--------------+
| tnumber | tname | tsex | tbirthday           | prof      | depart       |
+---------+-------+------+---------------------+-----------+--------------+
| 112     | 王    | 男   | 2000-09-03 00:00:00 | 副教授    | 计算机系     |
+---------+-------+------+---------------------+-----------+--------------+
1 row in set (0.51 sec)

==》第三步：这两个语句可以通过union连接在一起，求并集

mysql> select * from teacher where depart='计算机系' and prof not in(select prof from teacher where depart='通信工程系')
   -> union   #连接在一起
   -> select * from teacher where depart='通信工程系' and prof not in(select prof from teacher where depart='计算机系');
+---------+--------+------+---------------------+-----------+-----------------+
| tnumber | tname  | tsex | tbirthday           | prof      | depart          |
+---------+--------+------+---------------------+-----------+-----------------+
| 112     | 王     | 男   | 2000-09-03 00:00:00 | 副教授    | 计算机系        |
| 113     | 春丽   | 女   | 1988-11-05 00:00:00 | 助教      | 通信工程系      |
| 114     | 刘邦   | 男   | 1978-12-03 00:00:00 | 助教      | 通信工程系      |
+---------+--------+------+---------------------+-----------+-----------------+
3 rows in set (0.38 sec)

28)

  • 查询编号为‘3-105’课程且成绩至少高于编号为‘3-245’的成绩，这些人的cnumber，snumber和degree，并且按照degree的大小进行由高到低的排序（any）
mysql> select * from score
    -> where cnumber='3-105' #条件一
    -> and degree>any(select degree from score where cnumber = '3-245')#条件二
    -> order by degree desc;  #排序
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 103     | 3-105   |     89 |
| 102     | 3-105   |     83 |
+---------+---------+--------+
2 rows in set (0.39 sec)

注意：至少 = any

29)

  • 查询编号为‘3-105’的课程且成绩高于课程‘3-245’课程的同学的信息？（all）
	mysql> select * from score
    -> where cnumber='3-105' #条件一
    -> and degree>all(select degree from score where cnumber = '3-245');

30)

  • 查询所有教师和同学的name、sex和birthday（union、as）
mysql> select tname,tsex,tbirthday from teacher
    -> union
    -> select sname,ssex,sbirthday from student;
+-----------+------+---------------------+
| tname     | tsex | tbirthday           |
+-----------+------+---------------------+
| 古一      | 女   | 0000-01-01 00:00:00 |
| 王        | 男   | 2000-09-03 00:00:00 |
| 春丽      | 女   | 1988-11-05 00:00:00 |
| 刘邦      | 男   | 1978-12-03 00:00:00 |
| 张三      | 男   | 1999-09-01 00:00:00 |
| 李四      | 男   | 1999-02-11 00:00:00 |
| 王二      | 女   | 1999-09-23 00:00:00 |
| 王尼玛    | 男   | 1988-01-11 00:00:00 |
| 张全蛋    | 男   | 2000-09-03 00:00:00 |
| 赵铁柱    | 男   | 1983-04-05 00:00:00 |
| 木子      | 女   | 2000-12-16 00:00:00 |
+-----------+------+---------------------+
11 rows in set (0.06 sec)

但是会发现，上面的字段不对是tname，这里就要取 别名 …as…

mysql> select tname as name,tsex as sex,tbirthday as birthday from teacher
    -> union
    -> select sname,ssex,sbirthday from student;
+-----------+-----+---------------------+
| name      | sex | birthday            |
+-----------+-----+---------------------+
| 古一      | 女  | 0000-01-01 00:00:00 |
| 王        | 男  | 2000-09-03 00:00:00 |
| 春丽      | 女  | 1988-11-05 00:00:00 |
| 刘邦      | 男  | 1978-12-03 00:00:00 |
| 张三      | 男  | 1999-09-01 00:00:00 |
| 李四      | 男  | 1999-02-11 00:00:00 |
| 王二      | 女  | 1999-09-23 00:00:00 |
| 王尼玛    | 男  | 1988-01-11 00:00:00 |
| 张全蛋    | 男  | 2000-09-03 00:00:00 |
| 赵铁柱    | 男  | 1983-04-05 00:00:00 |
| 木子      | 女  | 2000-12-16 00:00:00 |
+-----------+-----+---------------------+
11 rows in set (0.00 sec)

注意：第二排可以不用取别名，默认按第一排取别名

31)

  • 查询所有女教师和女同学的name、sex和birthday

==》在两个查询语句后面加上where条件就可以，在用union连接where tsex='女'+where ssex=‘女’

32)

  • 查询成绩比课程平均成绩低的同学的成绩表

==》第一步：先查一下各门课的平均成绩

mysql> select avg(degree) from score group by cnumber;
+-------------+
| avg(degree) |
+-------------+
|     86.0000 |
|     82.0000 |
|     76.0000 |
+-------------+
3 rows in set (0.56 sec)

==》第二步：求某一门课的平均成绩

mysql> select avg(degree) from score where cnumber='3-105';
+-------------+
| avg(degree) |
+-------------+
|     86.0000 |
+-------------+
1 row in set (0.00 sec)

==》第三步：把score复制成a、b两个表（不需要语句，直接写）

mysql> select * from score a where
    -> degree<(select avg(degree) from score b where a.cnumber=b.cnumber);
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 102     | 3-105   |     83 |
| 104     | 3-245   |     66 |
| 105     | 6-166   |     60 |
+---------+---------+--------+
3 rows in set (0.40 sec)

33)

  • 查询所有任课老师的tname和depart
mysql> select tname,depart from teacher
    -> where tnumber in (select tnumber from course);
+--------+-----------------+
| tname  | depart          |
+--------+-----------------+
| 古一   | 化学系          |
| 王     | 计算机系        |
| 春丽   | 通信工程系      |
| 刘邦   | 通信工程系      |
+--------+-----------------+
4 rows in set (0.57 sec)

分析：这一题乍一看很简单，其实有点小条件，就是任课老师的名字。因为老师表里面可能有不任课的老师，所以要和course表进行比较，找到teccher表中教师号在课程表中存在的任课老师

34)

  • 查询至少有2个男生的班号

==》第一步：查看学生表

mysql> select * from student;
+---------+-----------+------+---------------------+--------+
| snumber | sname     | ssex | sbirthday           | class  |
+---------+-----------+------+---------------------+--------+
| 100     | 张三      | 男   | 1999-09-01 00:00:00 | 一班   |
| 101     | 李四      | 男   | 1999-02-11 00:00:00 | 一班   |
| 102     | 王二      | 女   | 1999-09-23 00:00:00 | 一班   |
| 103     | 王尼玛    | 男   | 1988-01-11 00:00:00 | 一班   |
| 104     | 张全蛋    | 男   | 2000-09-03 00:00:00 | 一班   |
| 105     | 赵铁柱    | 男   | 1983-04-05 00:00:00 | 二班   |
| 106     | 木子      | 女   | 2000-12-16 00:00:00 | 二班   |
+---------+-----------+------+---------------------+--------+
7 rows in set (0.00 sec)

==》第二步：count(*)统计男生的个数

mysql> select class from student 
       where ssex='男' group by class having count(*)>=2;
+--------+
| class  |
+--------+
| 一班   |
+--------+
1 row in set (0.00 sec)

35)

  • 查询student表中不姓 ‘王’ 的同学记录
  • 用法：not like（模糊查询）
mysql> select * from student where sname not like '王%';
+---------+-----------+------+---------------------+--------+
| snumber | sname     | ssex | sbirthday           | class  |
+---------+-----------+------+---------------------+--------+
| 100     | 张三      | 男   | 1999-09-01 00:00:00 | 一班   |
| 101     | 李四      | 男   | 1999-02-11 00:00:00 | 一班   |
| 104     | 张全蛋    | 男   | 2000-09-03 00:00:00 | 一班   |
| 105     | 赵铁柱    | 男   | 1983-04-05 00:00:00 | 二班   |
| 106     | 木子      | 女   | 2000-12-16 00:00:00 | 二班   |
+---------+-----------+------+---------------------+--------+
5 rows in set (0.37 sec)

36)

  • 查询student表中每个学生的姓名和年龄
mysql> select sname,year(now())-year(sbirthday) as old from student;
+-----------+------+
| sname     | old  |
+-----------+------+
| 张三      |   20 |
| 李四      |   20 |
| 王二      |   20 |
| 王尼玛    |   31 |
| 张全蛋    |   19 |
| 赵铁柱    |   36 |
| 木子      |   19 |
+-----------+------+
7 rows in set (0.00 sec)

分析：年龄=当前年份 - 出生年份，当前年份可以用 year（ now（））来体现，再加上别名

37)

  • 查询student表中最大最小sbirthday的日期值
mysql> select sbirthday from student order by sbirthday;
+---------------------+
| sbirthday           |
+---------------------+
| 1983-04-05 00:00:00 |
| 1988-01-11 00:00:00 |
| 1999-02-11 00:00:00 |
| 1999-09-01 00:00:00 |
| 1999-09-23 00:00:00 |
| 2000-09-03 00:00:00 |
| 2000-12-16 00:00:00 |
+---------------------+
7 rows in set (0.01 sec)

这里可以使用max（）、min（）函数

mysql> select max(sbirthday) as max,min(sbirthday) as min
       from student order by sbirthday;
+---------------------+---------------------+
| max                 | min                 |
+---------------------+---------------------+
| 2000-12-16 00:00:00 | 1983-04-05 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

38)

  • 以班级和年龄从大到小的顺序查询student表中的记录
mysql> select * from student order by class desc,sbirthday;
+---------+-----------+------+---------------------+--------+
| snumber | sname     | ssex | sbirthday           | class  |
+---------+-----------+------+---------------------+--------+
| 105     | 赵铁柱    | 男   | 1983-04-05 00:00:00 | 二班   |
| 106     | 木子      | 女   | 2000-12-16 00:00:00 | 二班   |
| 103     | 王尼玛    | 男   | 1988-01-11 00:00:00 | 一班   |
| 101     | 李四      | 男   | 1999-02-11 00:00:00 | 一班   |
| 100     | 张三      | 男   | 1999-09-01 00:00:00 | 一班   |
| 102     | 王二      | 女   | 1999-09-23 00:00:00 | 一班   |
| 104     | 张全蛋    | 男   | 2000-09-03 00:00:00 | 一班   |
+---------+-----------+------+---------------------+--------+
7 rows in set (0.00 sec)

分析：这里的年龄要从大到小，因为年份排序是根据年的大小来排序的（2000年>1988年），所以不需要指定降序，默认的升序对年龄来说就是从大到小。

注意：这里order by是先按照第一进行排列，第一个相同再按照第二个进行排列

39)

  • 查询男教师以及所上的课程
mysql> select * from teacher where tsex='男';
+---------+--------+------+---------------------+-----------+-----------------+
| tnumber | tname  | tsex | tbirthday           | prof      | depart          |
+---------+--------+------+---------------------+-----------+-----------------+
| 112     | 王     | 男   | 2000-09-03 00:00:00 | 副教授    | 计算机系        |
| 114     | 刘邦   | 男   | 1978-12-03 00:00:00 | 助教      | 通信工程系      |
+---------+--------+------+---------------------+-----------+-----------------+
2 rows in set (0.00 sec)

mysql> select * from course 
       where tnumber in ( select tnumber from teacher where tsex='男');
+---------+--------------+---------+
| cnumber | cname        | tnumber |
+---------+--------------+---------+
| 3-105   | 数据结构     | 112     |
| 9-888   | 数字电路     | 114     |
+---------+--------------+---------+
2 rows in set (0.00 sec)

分析：可以先查男教师，然后再作为条件来用

40)

  • 查询最高分同学的信息
mysql> select * from student where
    -> snumber=(select snumber from score where
       degree=( select max(degree) from score) );
+---------+--------+------+---------------------+--------+
| snumber | sname  | ssex | sbirthday           | class  |
+---------+--------+------+---------------------+--------+
| 101     | 李四   | 男   | 1999-02-11 00:00:00 | 一班   |
+---------+--------+------+---------------------+--------+
1 row in set (0.00 sec)

分析：由最高分找学号，再由学号找信息

41)

  • 查询和王尼玛同性别的同学名字
mysql> select sname from student 
       where ssex=(select ssex from student where sname='王尼玛');
+-----------+
| sname     |
+-----------+
| 张三      |
| 李四      |
| 王尼玛    |
| 张全蛋    |
| 赵铁柱    |
+-----------+
5 rows in set (0.00 sec)

42)

  • 查询和王尼玛同性别且同班的同学名字
mysql> select sname from student 
       where ssex=(select ssex from student where sname='王尼玛')    
    -> and class=(select class from student where sname='王尼玛');
+-----------+
| sname     |
+-----------+
| 张三      |
| 李四      |
| 王尼玛    |
| 张全蛋    |
+-----------+
4 rows in set (0.00 sec)

43)

  • 查询所有选修‘人工智能’课程的男同学的成绩

步骤：

  • 先从mysql> select cnumber from course where cname='人工智能';找到cnumber
  • 再从mysql> select snumber from student where ssex='男';找到snumber
  • 然后进行子查询：
mysql> select degree from score
    -> where cnumber=(select cnumber from course where cname='人工智能')
    -> and snumber in (select snumber from student where ssex='男');
+--------+
| degree |
+--------+
|     60 |
+--------+
1 row in set (0.00 sec)

注意：这里snumber有多个要用 in ，只有一个用 =

44)

  • 使用如下命令建立一个grade表

==》第一步：建立一个等级表

mysql> create table grade(
    -> low int(3),
    -> upp int(3),
    -> grade char(1)
    -> );
Query OK, 0 rows affected (1.58 sec)

 insert into grade values(90,100,'A');
 insert into grade values(80,89,'B');
 insert into grade values(70,79,'C');
 insert into grade values(60,69,'D');
 insert into grade values(0,59,'E');
 
 mysql> select * from grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+
5 rows in set (0.00 sec)

==》第二步：查询所有同学的snumber、cnumber、和grade列

mysql> select snumber,cnumber,grade from score,grade
    -> where degree between low and upp order by grade;  #再排个序
+---------+---------+-------+
| snumber | cnumber | grade |
+---------+---------+-------+
| 106     | 6-166   | A     |
| 101     | 3-245   | A     |
| 100     | 3-245   | B     |
| 102     | 3-105   | B     |
| 103     | 3-105   | B     |
| 105     | 6-166   | D     |
| 104     | 3-245   | D     |
+---------+---------+-------+
7 rows in set (0.00 sec)
posted @ 2019-09-28 20:20  fxyadela  阅读(651)  评论(0)  编辑  收藏  举报