数据库范式练习

设W（C,P,S,G,T,R）其中C为课程，P为教师，S为学生，G为成绩，T为时间，R为教室。存在有如下函数依赖集{ (S,C)->G,(T,R)->C,(T,P)->R,(T,S)->R }关系模式W的候选码(  1  )，W的规范化程度最高达到(  2  ) 。


PST；1NF


现有函数依赖集F = {A→BC，B→AC，C→A}，试求其最小函数依赖集。


F= {A→B , B →C , C →A }


R<U,F>,U={A,B,C,D,E},F={AB->C,C->D,D->E}，其一个分解ρ={R1(ABC),R2(CD),R3(DE)}，判断该分解是否具有无损连接性。





R<U,F>,U={A,B,C,D},F={A->B,B->C,C->D,D->A}，判定分解ρ={AB,BC,CD}是否具有函数依赖保持性。


R(AB) F = {A->B}

R(BC) F = {B->C}

R(CD) F = {C->D}

设关系集G={A->B,B->C,C->D}

对于D->A，求CD关于G的闭包，即，可见，CD关于G的闭包不包含A，因此没有保持该函数依赖

因此ρ不具有函数依赖保持性



设有R(A,B,C,D) ,F={A->C,C->A,B->AC,D->AC}
（1）R的候选码
（2）将R分解成3NF且无损保持函数依赖
（3）将R分解成BCNF且保持无损连接



（1）BD
（2）先将F分解，得到最小函数依赖集F={A->C, C->A,B->A,D->C}

按左侧属性相同分组，得到R(AC), R(BA), R(DC)

此时没有保持函数依赖，添加R(BD)

因此可以分解为P={AC,BA,CD,BD}，满足3NF
（3）选择A->C，于是得到R1(AC)

R2(ABD)，F2={B->A,D->C}，此时R2不满足BCNF，因此继续分解，选择      B->A

得到R21(BA)，R22(BD)

因此，可以分解为P={AC, AB, BD}，满足BCNF



设有R(A,B,C,D) ,F={A->C,C->A,B->AC,D->AC}
1.计算（AD）+
2.求F的最小等价依赖集Fm



（1） $(AD)^+ = \{A,C,D\}$
（2）F={A->C, C->A, B->A, B->C, D->A , D->C}
因此最小等价依赖集Fm={A->C, C->A, B->A, D->A }



设关系模式R(A,B,C,D,E,P)，其中F={A->B,C->P,E->A,CE->D}
（1）求R的候选码。
（2）R的规范化程度。



（1）CE
（2）关系E->A部分依赖码CE，因此属于1NF



设R(A,B,C,D,E),F={A->C,C->D,B->C,DE->C,CE->A}
（1）求R的候选码及R的规范化程度；
（2）ρ={AD，AB，BC，CDE，AE}是否无损连接；
（3）将R分解BCNF且无损。



（1）R的候选码为BE，R的规范化程度为1NF
（2）
（3）选择A->C，得到R1(AC)，和R2(ABDE)，F2={BE->A, A->D, B->D}
​    选择A->D，得到R21(AD)，和R22(ABE)，F22={BE->A}
因此，R分解为(AC, AD, ABE)



设R(A,B,C,D,E),F={A->BD, B->E,C->A,CD->E}
（1）求(AD)+；
（2）求最小函数依赖集Fm；
（3）将其分解为3NF保持函数依赖；
（4）判断R的一个分解ρ={AB，ACE，BCD}是否保持函数依赖性，并说明原因。



（1）（AD)+ = {A, B, D, E }
（2）Fm={A->B, A->D, B->E, C->A}
（3）P={ABD, BE, CA}
（4）对于R1(AB)，F1={A->B}

R2(ACE) F2= {C->A, A->E}

R3(BCD) F3= {C->B, C->D}

F1 U F2 U F3 = G

G无法退出关系A->D

因此不保持函数依赖性



设有关系模式R（A,B,C,D,E），其上的函数依赖集：
F={A→D，CD→A，E→D，BC→D，D→B}
（1）求R的候选码；
（2）判断R属于第几范式，并说明原因；
（3）判断ρ={AD，CDE，BCD}是否为无损连接分解；
（4）将R分解使得其满足BCNF，并具有无损连接性。



（1）候选码为CE
（2）关系E->D，部分依赖码CE，因此是1NF
（3）
（4）选择A->D，得到R1(AD), R2(ABCE) F2m={ CE->A , A->B}
​     选择A->B，得到R21(AB)，R22(ACE)
​     因此可以将R分解为{AD, AB, ACE}

1、请简述满足1NF、2NF和3NF的基本条件。并完成下题：某信息一览表
如下，其是否满足3NF，若不满足请将其化为符合3NF的关系。(本小题
第一范式的关系应满足的基本条件是元组中的每一个分量都必须是不可
分割的数据项。
第二范式，指的是这种关系不仅满足第一范式，而且所有非主属性完全
依赖于其主码。
第三范式，指的是这种关系不仅满足第二范式，而且它的任何一个非主属性都不传递依赖于任何主关键字。
考生情况(考生编号，姓名，性别，考生学校)
考场情况(考场号，考场地点)
考场分配(考生编号，考场号)
成绩(考生编号，考试成绩，学分)
2、某信息一览表如下，其是否满足3NF，若不满足请将其化为符合3NF的关
配件关系：(配件编号，配件名称，型号规格)
供应商关系(供应商名称，供应商地址)
配件库存关系(配件编号，供应商名称，单价，库存量)
3、简述满足1NF、2NF和3NF的基本条件。并完成下题：已知教学关系，
教学(学号，姓名，年龄，性别，系名，系主任，课程名，成绩)，试问该关系的主键是什么，属于第几范式，为什么？如果它不属于3NF，请把它规范到3NF。
4、请确定下列关系的关键字、范式等级；若不属于3NF，则将其化为3NF 。
例1.仓库(仓库号，面积，电话号码，零件号，零件名称，规格，库存数量)例1答案：
仓库号+零件号；1NF；
仓库(仓库号，面积，电话号码)
零件(零件号，零件名称，规格)
可复制、编制，期待你的好评与关注！

    

    
目录
    

        
一、数据库的三大设计范式
1. 第一范式：数据中所有字段都是不可分割的原子值
        
2. 第二范式：必须满足第一范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖于主键
        
3. 第三范式：必须满足第二范式，除主键列的其他列之间不能有传递依赖关系
        
        
二、查询练习
1. 准备
        
2. 导入数据
        
3. 练习
        
        

一、数据库的三大设计范式
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)
对于这个复合语句进行拆分：
找到最高分
select max(degree) from score
找到最高分的学号和课程号
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)

转载于:https://www.cnblogs.com/fxyadela/p/11604688.html

本篇主要整理几道关于数据库范式的例题，同时练习使用一下数学符号的应用，Markdown编辑器最爱的仍然是Typora，强烈安利！
MarkDown-Unicode-LaTeX使用（以数据依赖关系中使用的符号为例）
Unicode Arrows：
← → ↑ ↓ ↚ ↛ ↮

X → Y； X ↛ Y
Unicode Math Symbols :
⊂ ⊃ ⊄ ⊅ ⊆ ⊇ ⊈ ⊉

X ⊂ Y； X ⊄ Y
LaTeX：
效果展示：



实现代码：
$X \leftarrow Y$ $X \rightarrow Y$ 
$X \nleftarrow Y$ $X \nrightarrow Y$
$X \not\leftarrow Y$ $X  \not\rightarrow Y$
$X \overset F \rightarrow Y$ $X \overset F \longrightarrow Y$
$X \overset P \rightarrow Y$  $X \overset P \longrightarrow Y$
$X \overset {传递} \rightarrow Y$ $X \overset {传递} \longrightarrow Y$
$(Sno,Cno) \overset F \rightarrow Grade$
$X \stackrel {F} {\longrightarrow} Y$

效果展示：



实现代码：
$X \subset Y$ $X \supset Y$ $X \not\subset Y$ $X \not\supset Y$
$X \subseteq Y$ $X \supseteq Y$ $X \nsubseteq Y$ $X \nsupseteq Y$

关于这一部分的内容，老师还推荐了网站，这里也一并附上。

链接1

链接2

链接3
数据库范式例题
解题过程：使用笨方法进行分析，后边的题也是一样的做法，就不再赘述了，这里只以第一题说明做题的思路和技巧。
一 .

Y(X1,X2,X3,X4)

(X1,X2)→X3

X2→X4
候选码？
属于第几范式？
一共有四个属性，每一个都考虑，这样不容易漏情况，当然这也是相对耗费时间的一种做法。

①考虑1个

X1不能推出X2，X3，X4，不是候选码

X2不能推出X1，不是候选码

X3不能推出X1, X2，不是候选码

X4不能推出X2，不是候选码

②考虑2个

（X1, X2）可以推出X3, X2单独又可以推出X4，全部属性都包含了，是候选码。√

（X1, X3）不能推出X2，直接排除，不是候选码

（X1, X4）不能推出X2，直接排除，不是候选码

（X2, X3）不能推出X1，直接排除，不是候选码

（X2, X4）不能推出X1，直接排除，不是候选码

（X3, X4）不能推出X1，X2，排除，不是候选码

考虑2个属性的已经得到候选码，直接结束，候选码是最小的超码！。所以候选码就是（X1, X2），对应的非主属性为X3, X4

因为(X1,X2)→X4, X2→X4，存在非主属性X4对候选码（X1,X2）的部分函数依赖；所以不属于2NF。

结论：候选码（X1,X2），属于第一范式。
小技巧，节省做题时间：

可以发现，在箭头右边的，一定不是候选码，因为他需要由其他属性推出；如果一个属性在左边，而要判断是否为候选码的属性组中没有该属性，则不是候选码；左边属性个数>=2，则任意单独一个不可以作为候选码。
二．

R（A，B，C，D）

F={AB→D，AC→BD，B→C}
候选码？
最高属于第几范式？
易得A, B, C, D不是候选码。（A, B）可以推出D，B单独又可推出C，所以为候选码。（A, C）可以推出B，也可推出D，所以为候选码。

所以非主属性为D，因为D没有部分依赖或传递依赖于码，所以为第三范式，即3NF。不是BCNF，因为B->C，但决定因素B不是码。
三．

R（X，Y，Z，W）

F={Y←→W，XY→Z}
候选码？？
最高属于第几范式？
XY→X,XY→Y,XY→Z,因为Y→W,所以XY→W  所以 （X,Y）是候选码

XW→X,XW→W, 因为W→Y,XW→XY,因为XY→Z,XW→Z,XW→W,W→Y,XW→Y 所以（X,W）是候选码

非主属性为Z

是2NF，因为Z完全依赖于码

是3NF，因为Z没有传递依赖于码

不是BCNF 因为W→Y，决定者W不包含码

所以最高为3NF
四.

R（A,B,C,D,E） F={A→B,CE→A,E→D}
1 求候选码
CE→A，A→B，E→D，所以CE为候选码，即（C,E）
2 最高属于第几范式，为什么？
最高位第一范式。

不是2NF，因为非主属性D部分依赖于（C, E），所以也不可能是3NF
3 分解到3NF
首先分解到2NF

R1（A，B，C，E），R2（D，E）

因为 CE→A，A∕→CE，A→B ，存在非主属性对码的传递函数依赖，所以不属于3NF

分解为：R1（A，C，E），R2（A，B），R3（D，E）
五.

R(商店编号，商品编号，数量，部门编号，负责人)

每个商店的每种商品只在一个部门销售，

每个商店的每个部门只有一个负责人

每个商店的每种商品只有一个库存数量
1 求候选码
按照题意，设R（A,B,C,D,E），且有F={AB→D,AD→E,AB→C}

所以候选码为（A,B），因为AB→C, AB→D,因为AD→E，而D可由AB导出，所以AB→E。
2 R已达第几范式？为什么？
R已为2NF，非主属性为C D E，每一个非主属性都完全依赖于码，不可由码中的任意单独一个主属性蕴涵得出。

R不为3NF，因为AB→AD, AD→E（且E为非主属性），AD↛ AB，存在非主属性对码的传递依赖关系。
3 若不属于3NF，分解成3NF
R1(A，B，C，D)

R2(A，D，E)
六．

R(A,B,C,D,E,F) F={A→C,AB→D,C→E,D→BF}
1 写出关键字
候选码为（A,B）和（A,D）。

（A,B）：因为A→C,所以AB→C，又因为C→E，所以AB→E；AB→D。所以AB为码

（A,D）：因为A→C,所以AD→C，又因为C→E，所以AD→E；D→B，所以AD→B。所以AD为码
2 分解到2NF
非主属性为C E

AB→A，A→C 所以 AB→C，A→C 部分函数依赖

AD→F，D→F也是部分函数依赖

因此，根据第二范式的定义可分解为：R1（A，B，D），R2（A，C，E），R3（D，F）
3 分解到3NF
在2NF的基础上继续分解。

A→C，C\→A，C→E 传递依赖

所以将2NF中的R2（A，C，E）分为 （A，C），（C，E）

因此最终分解为R1（A，B，D），R2（A，C），R3（C，E），R4（D，F）
4 分解到4NF
这个老师还没有讲，自己看了一下书，不过还是有点蒙的，尤其是多值函数依赖那里。。

D→B，但D不包含码

R1 分解成 （A，D），（B，D），这样（A，D）和（B，D）中就都有主属性了。

因此最终分解为 R1（A，B），R2（B，D），R3（A，C），R4（C，E），R5（D，F）