MySQL基础学习笔记

学习时间：2023年6月16日

学习来源：尚硅谷

1 数据库简介

1.1 数据库与数据库管理系统

DB：数据库（Database）
即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。
DBMS：数据库管理系统（Database Management System）
是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。
SQL：结构化查询语言（Structured Query Language）
专门用来与数据库通信的语言。

数据库管理系统可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体用户的数据。

数据库管理系统、数据库和表的关系如图所示：

1.2 关系型数据库

关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

这种类型的数据库是最古老的数据库类型，关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。

关系型数据库以行(row)和列(column)的形式存储数据，以便于用户理解。这一系列的行和列被称为表(table)，一组表组成了一个库(database)。
表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系型数据库，就是建立在关系模型基础上的数据库。
SQL 就是关系型数据库的查询语言。

1.3 非关系型数据库

非关系型数据库，可看成传统关系型数据库的功能阉割版本，基于键值对存储数据，不需要经过SQL层的解析，性能非常高。同时，通过减少不常用的功能，进一步提高性能。

1.3.1 分类

键值型数据库

键值型数据库通过 Key-Value 键值的方式来存储数据，其中 Key 和 Value 可以是简单的对象，也可以是复杂的对象。Key 作为唯一的标识符，优点是查找速度快，在这方面明显优于关系型数据库，缺点是无法像关系型数据库一样使用条件过滤（比如 WHERE），如果你不知道去哪里找数据，就要遍历所有的键，这就会消耗大量的计算。

键值型数据库典型的使用场景是作为内存缓存。Redis 是最流行的键值型数据库。

文档型数据库

此类数据库可存放并获取文档，可以是XML、JSON等格式。在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录。文档数据库所存放的文档，就相当于键值数据库所存放的“值”。MongoDB 是最流行的文档型数据库。此外，还有CouchDB等。

搜索引擎数据库

虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎数据库是应用在搜索引擎领域的数据存储形式，由于搜索引擎会爬取大量的数据，并以特定的格式进行存储，这样在检索的时候才能保证性能最优。核心原理是“倒排索引”。

典型产品：Solr、Elasticsearch、Splunk 等。

列式数据库

列式数据库是相对于行式存储的数据库，Oracle、MySQL、SQL Server 等数据库都是采用的行式存储（Row-based），而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的 I/O，适合于分布式文件系统，不足在于功能相对有限。典型产品：HBase等。

图形数据库

图形数据库，利用了图这种数据结构存储了实体（对象）之间的关系。图形数据库最典型的例子就是社交网络中人与人的关系，数据模型主要是以节点和边（关系）来实现，特点在于能高效地解决复杂的关系问题。

图形数据库顾名思义，就是一种存储图形关系的数据库。它利用了图这种数据结构存储了实体（对象）之间的关系。关系型数据用于存储明确关系的数据，但对于复杂关系的数据存储却有些力不从心。如社交网络中人物之间的关系，如果用关系型数据库则非常复杂，用图形数据库将非常简单。典型产品：Neo4J、InfoGrid等。

1.3.2 演变

由于 SQL 一直称霸 DBMS，因此许多人在思考是否有一种数据库技术能远离 SQL，于是 NoSQL 诞生了，但是随着发展却发现越来越离不开 SQL。到目前为止 NoSQL 阵营中的 DBMS 都会有实现类似 SQL 的功能。下面是“NoSQL”这个名词在不同时期的诠释，从这些释义的变化中可以看出 NoSQL功能的演变：

1970：NoSQL = We have no SQL

1980：NoSQL = Know SQL

2000：NoSQL = No SQL!

2005：NoSQL = Not only SQL

2013：NoSQL = No, SQL!

NoSQL 对 SQL 做出了很好的补充，比如实际开发中，有很多业务需求，其实并不需要完整的关系型数据库功能，非关系型数据库的功能就足够使用了。这种情况下，使用性能更高、成本更低的非关系型数据库当然是更明智的选择。比如：日志收集、排行榜、定时器等。

1.4 关系型数据库设计规则

1.4.1 表，记录和字段

E-R（entity-relationship，实体-联系）模型中有三个主要概念是：实体集、属性、联系集。
一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表中的一行（row），也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）。

ORM思想 (Object Relational Mapping)体现：
数据库中的一个表  <---> Java中的一个类
表中的一条数据  <---> 类中的一个对象（或实体）
表中的一个列  <----> 类中的一个字段、属性(field)

1.4.2 主键和外键

关系型数据库中的一条记录中有若干个属性，若其中某一个属性组能唯一标识一条记录，该属性组就可以成为一个主键。

例如：

1
2
3

学生表(学号，姓名，性别，班级) 
课程表(课程编号,课程名,学分) 
成绩表(学号,课程号,成绩)

每个学生的学号是唯一的，学号就是一个主键
课程编号是唯一的,课程编号就是一个主键
成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以学号和课程号的属性组是一个主键

成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键。同理，成绩表中的课程号是课程表的外键。

归纳如下：A表中的一个字段，是B表的主键，则该字段是A表的外键。

主键	外键	索引
定义	唯一标识一条记录，不能有重复的，不允许为空	表的外键是另一表的主键，外键可以有重复的, 可以是空值
作用	用来保证数据完整性	用来和其他表建立联系用的
个数	主键只能有一个	一个表可以有多个外键

1.4.3 表的关联关系

① 一对一

举例：设计学生表：学号、姓名、手机号码、班级、系别、身份证号码、家庭住址、籍贯、紧急联系人、…

拆为两个表：两个表的记录是一一对应关系。
基础信息表（常用信息）：学号、姓名、手机号码、班级、系别
档案信息表（不常用信息）：学号、身份证号码、家庭住址、籍贯、紧急联系人、…

两种建表原则：

外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一。
外键是主键：主表的主键和从表的主键，形成主外键关系。

下面是外键唯一的例子：

② 一对多

一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键。

下图中，主表课程编号为1时，可在从表中可以找到多条记录。

③ 多对多

要表示多对多关系，必须创建第三个表，该表通常称为联接表，它将多对多关系划分为两个一对多关系。将这两个表的主键都插入到第三个表中。

例如：

④ 自我引用

2 MySQL环境搭建

暂略

3 基本SQL语句

3.1 sakila数据集

sakila数据库包含23张表：

+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+

3.2 SQL概述

3.2.1 分类

SQL语言在功能上主要分为如下3大类：

DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
- 主要的语句关键字包括CREATE、DROP、ALTER等。
DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。
- 主要的语句关键字包括INSERT、DELETE、UPDATE、SELECT等。
- SELECT是SQL语言的基础，最为重要。
DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
- 主要的语句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。

3.2.2 基本规则

SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
每条命令以 ; 或 \g 或 \G 结束
关键字不能被缩写也不能分行
关于标点符号
- 必须保证所有的()、单引号、双引号是成对结束的
- 必须使用英文状态下的半角输入方式
- 字符串型和日期时间类型的数据可以使用单引号（' '）表示
- 列的别名，尽量使用双引号（" "），而且不建议省略as

3.2.3 大小写规范

MySQL 在 Windows 环境下是大小写不敏感的
MySQL 在 Linux 环境下是大小写敏感的
- 数据库名、表名、表的别名、变量名是严格区分大小写的
- 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
推荐采用统一的书写规范：
- 数据库名、表名、表别名、字段名、字段别名等都小写
- SQL 关键字、函数名、绑定变量等都大写

3.2.4 注释

1
2
3

单行注释：#注释文字 (MySQL特有的方式)
单行注释：-- 注释文字 (--后面必须包含一个空格。)
多行注释：/* 注释文字  */

3.3 基本的SELECT语句

只列出中常用的实例。

3.3.1 SELECT FROM

1	SELECT 列名 FROM 表名;

示例：

查找所有列：

1	SELECT * FROM actor;

查找特定列：

1	SELECT first_name, last_name FROM actor;

3.3.2 列的别名

使用关键字AS，可以省略。别名需要紧跟列名，别名建议使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。

1
2
3

SELECT first_name AS fname, last_name AS lname FROM actor;
#或者
SELECT first_name "fname", last_name "lname" FROM actor;

3.3.3 去除重复行

默认情况下，查询会返回全部行，包括重复行。

1	SELECT country_id FROM city; #城市表city，多个城市可能在同一个国家，即country_id相同

在SELECT语句中使用关键字DISTINCT去除重复行

1	SELECT DISTINCT country_id FROM city;

去重结果：

3.3.4 着重号

我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对着重号引起来。

例如：

1
2

mysql> SELECT * FROM order;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER' at line 1

order表与关键字ORDER重名。需要使用着重号：

1	mysql> SELECT * FROM `order`;

3.3.5 查询常数

在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。

比如说，我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段corporation，这个字段固定值为“尚硅谷”，可以这样写：

1	SELECT '尚硅谷' AS corporation, last_name FROM employees;

3.4 表的结构

使用 DESCRIBE 或 DESC 命令，显示表结构。

1 2	DESCRIBE 表名; DESC 表名;

例如：

mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id    | int(6)      | NO   | PRI | 0       |       |
| first_name     | varchar(20) | YES  |     | NULL    |       |
| last_name      | varchar(25) | NO   |     | NULL    |       |
| email          | varchar(25) | NO   | UNI | NULL    |       |
| phone_number   | varchar(20) | YES  |     | NULL    |       |
| hire_date      | date        | NO   |     | NULL    |       |
| job_id         | varchar(10) | NO   | MUL | NULL    |       |
| salary         | double(8,2) | YES  |     | NULL    |       |
| commission_pct | double(2,2) | YES  |     | NULL    |       |
| manager_id     | int(6)      | YES  | MUL | NULL    |       |
| department_id  | int(4)      | YES  | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

Field：表示字段名称。
Type：表示字段类型。
Null：表示该列是否可以存储NULL值。
Key：表示该列是否已编制索引。
- PRI表示该列是表主键的一部分；
- UNI表示该列是UNIQUE索引的一部分；
- MUL表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，如果有，那么值是多少。
Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

又如：

3.5 WHERE语句

语法格式：WHERE子句紧随 FROM子句

1
2
3

SELECT 字段1,字段2, ...
FROM 表名
WHERE 过滤条件;

示例：

1	SELECT city_id, city FROM city WHERE country_id = 80;

4 运算符

4.1 逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。

MySQL中支持4种逻辑运算符如下：

NOT，!
AND，&&
OR，||
XOR

4.2 非符号型运算符

常用的：

IS NULL：判断是否为空
IS NOT NULL：判断是否不为空
IN
NOT IN
LIKE

5 排序和分页

5.1 排序语法

使用 ORDER BY 子句排序，默认升序。ORDER BY 子句在 SELECT 语句的结尾。

ASC（ascend）: 升序
DESC（descend）:降序

5.2 单列排序

只按照某一个查找的字段进行排序。

1	SELECT first_name, last_name FROM actor ORDER BY first_name; #按照姓升序排列

1	SELECT first_name, last_name FROM actor ORDER BY first_name DESC; #按照姓降序排列

5.3 多列排序

可以使用不在SELECT列表中的列排序。
在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

1	SELECT first_name, last_name FROM actor ORDER BY first_name, last_name DESC; #先按照姓升序排列，再按照名降序

1	SELECT first_name, last_name FROM actor ORDER BY first_name DESC, last_name DESC; #先按照姓降序排列，再按照名降序

5.4 分页

所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。

MySQL中使用 LIMIT 实现分页，子句必须放在整个SELECT语句的最后。

1	LIMIT [位置偏移量,] 行数

位置偏移量参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定位置偏移量，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）
行数指示返回的记录条数。

示例：

-- 前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
-- 或者
SELECT * FROM 表名 LIMIT 10;

-- 第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;

-- 第21至30条记录： 
SELECT * FROM 表名 LIMIT 20,10;

分页显示公式：（当前页数-1）* 每页条数，每页条数

1 2	SELECT * FROM table LIMIT (PageNo - 1) * PageSize, PageSize;

约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有 1 条，就可以使用LIMIT 1，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

6 多表查询

6.1 引例

6.1.1 引例说明

从两个或多个表中查找数据：

1
2
3

# 案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;

查询结果：

+-----------+----------------------+
| last_name | department_name      |
+-----------+----------------------+
| King      | Administration       |
| King      | Marketing            |
| King      | Purchasing           |
| King      | Human Resources      |
| King      | Shipping             |
| King      | IT                   |
| King      | Public Relations     |
| King      | Sales                |
| King      | Executive            |
| King      | Finance              |
| King      | Accounting           |
| King      | Treasury             |
...
| Gietz     | IT Support           |
| Gietz     | NOC                  |
| Gietz     | IT Helpdesk          |
| Gietz     | Government Sales     |
| Gietz     | Retail Sales         |
| Gietz     | Recruiting           |
| Gietz     | Payroll              |
+-----------+----------------------+
2889 rows in set (0.01 sec)

6.1.2 笛卡尔积

笛卡尔乘积是一个数学运算。假设有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

SQL92中，笛卡尔积也称为交叉连接，英文是 CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：

#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

6.1.3 错误分析

笛卡尔积的错误会在下面条件下产生：
- 省略多个表的连接条件（或关联条件）
- 连接条件（或关联条件）无效
- 所有表中的所有行互相连接
为了避免笛卡尔积，可以在 WHERE 加入有效的连接条件。

加入连接条件后，查询语法：

1
2
3

SELECT	table1.column, table2.column
FROM	table1, table2
WHERE	table1.column1 = table2.column2;  #连接条件

在 WHERE子句中写入连接条件。

正确写法：

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

在表中有相同列时，在列名之前加上表名前缀，用.来连接。

6.2 连接分类

6.2.1 等值连接

暂略

6.2.2 自连接

自连接是指在一个表中连接该表本身的操作，可以看做是表a与表b进行等值连接，而表a和表b实际上是同一个表。

1
2
3

SELECT a.column_name, b.column_name...
FROM table_name a, table_name b
WHERE a.some_column = b.some_column;

MySQL的自连接语法是在一个表中连接该表本身的操作，可以理解为两个虚表的连接操作。首先，MySQL会将该表复制一份，生成该表的副本表，然后，通过副本表和原表中的某些列进行连接，以满足 WHERE 中的条件。

需要注意的是，自连接会增加查询的复杂性，也会增加数据库的负担，因此在实际使用中需要慎重考虑。

| id | name    | manager_id |
| 1  | Alice   | 2         |
| 2  | Bob     | NULL      |
| 3  | Charlie | 2         |

如果我们要查询所有员工和其直接领导的信息，可以使用自连接来实现：

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m 
ON e.manager_id = m.id;

查询结果如下：

| name    | manager |
| Alice   | Bob     |
| Bob     | NULL    |
| Charlie | Bob     |

6.2.3 内连接和外连接

内连接（inner join）：合并具有同一列的两个以上的表的行， 结果集中不包含一个表与另一个表不匹配的行。即表的交集。
外连接（outer join）：两个表在连接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右）外连接。没有匹配的行时，结果表中相应的列为空（NULL）。
- 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
- 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。

6.3 SQL99实现的多表查询

6.3.1 基本语法

SELECT table1.column, table2.column,table3.column
FROM table1
    JOIN table2 ON table1 和 table2 的连接条件
        JOIN table3 ON table2 和 table3 的连接条件

JOIN关键字的分类：

JOIN，INNER JOIN，CROSS JOIN都表示内连接
左外连接：LEFT OUTER JOIN
右外连接：RIGHT OUTER JOIN
满外连接（全连接）：FULL OUTER JOIN

6.3.2 内连接实现

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

查找俄罗斯的所有城市：

SELECT city.city 
FROM city INNER JOIN country 
ON city.country_id=country.country_id 
WHERE country.country="Russian Federation";

三表联查：

SELECT employee_id, city, department_name
FROM   employees e -- 取别名
JOIN   departments d -- 取别名
ON     d.department_id = e.department_id 
JOIN   locations l
ON     d.location_id = l.location_id;

6.3.3 外连接实现

① 左外连接

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

② 右外连接

SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;

③ 满外连接

MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

6.4 UNION

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

语法格式：

1
2
3

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

6.5 总结

7 单行函数

7.1 MySQL的内置函数

MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数）。

单行函数

操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以嵌套
参数可以是一列或一个值

7.2 数值函数

函数	用法
ABS(x)	返回x的绝对值
SIGN(X)	返回X的符号。正数返回1，负数返回-1，0返回0
PI()	返回圆周率的值
CEIL(x)，CEILING(x)	返回大于或等于某个值的最小整数
FLOOR(x)	返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…)	返回列表中的最小值
GREATEST(e1,e2,e3…)	返回列表中的最大值
MOD(x,y)	返回X除以Y后的余数
RAND()	返回0~1的随机值
RAND(x)	返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x)	返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根。当X的值为负数时，返回NULL

示例：其余略

1 2	SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1) FROM DUAL;

7.3 字符串函数

略

7.4 日期和时间函数

略

7.5 流程控制函数

函数	用法
IF(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 …. [ELSE resultn] END	相当于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 …. [ELSE 值n] END	相当于Java的switch…case…

SELECT CASE 1 
　　WHEN 1 THEN '我是1'
　　WHEN 2 THEN '我是2'
ELSE '你是谁';

SELECT oid,`status`, 
CASE `status` 
	WHEN 1 THEN '未付款' 
	WHEN 2 THEN '已付款' 
	WHEN 3 THEN '已发货'  
	WHEN 4 THEN '确认收货'  
	ELSE '无效订单' END 
FROM t_order;

7.6 MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。

函数	用法
VERSION()	返回当前MySQL的版本号
CONNECTION_ID()	返回当前MySQL服务器的连接数
DATABASE()，SCHEMA()	返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER()	返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHARSET(value)	返回字符串value自变量的字符集
COLLATION(value)	返回字符串value的比较规则

示例：

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER()         | CURRENT_USER() | SYSTEM_USER()  | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+

mysql> SELECT CHARSET('ABC');
+----------------+
| CHARSET('ABC') |
+----------------+
| utf8mb4        |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT COLLATION('ABC');
+--------------------+
| COLLATION('ABC')   |
+--------------------+
| utf8mb4_general_ci |
+--------------------+
1 row in set (0.00 sec)

8 聚合函数

聚合（或聚集、分组）函数是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

注意，WHERE中不能使用聚合函数。

8.1 基本聚合函数

8.1.1 简介

聚合函数作用于一组数据，并对一组数据返回一个值。

聚合函数类型
- AVG()
- SUM()
- MAX()
- MIN()
- **COUNT() **
聚合函数语法：出现在SELECT中

聚合函数不能嵌套调用。比如不能出现类似AVG(SUM(字段名称))形式的调用。

8.1.2 AVG和SUM

可以对数值型数据使用AVG 和 SUM 函数。

1
2
3

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

8.1.3 MIN和MAX

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

1 2	SELECT MIN(hire_date), MAX(hire_date) FROM employees;

8.1.4 COUNT

COUNT(*)返回表中记录总数，适用于任意数据类型。

1
2
3

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

COUNT(expr) 返回expr不为空的记录总数。

1
2
3

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;

含义：

COUNT(*)：统计所有的行数，包括为null的行（COUNT(*)不单会进行全表扫描，也会对表的每个字段进行扫描。而COUNT('x')或者COUNT(COLUMN)或者COUNT(0)等则只进行一个字段的全表扫描）。
COUNT(1)：计算一共有多少符合条件的行，不会忽略null值
COUNT(列名)：查询列名那一列的，字段为null则不统计。

执行效率：

表有多个字段情况下：
- 有主键，count(主键列名) 效率最高
- 没有主键，count(1)效率最高
表只有一个字段情况下：
- count(*)效率最高

8.2 GROUP BY

8.2.1 基本使用

可以使用GROUP BY子句将表中的数据分成若干组。

SELECT column, group_function(column)
FROM table
[WHERE	condition]
[GROUP BY	group_by_expression]
[ORDER BY	column];

1
2
3

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id ;

1
2
3

SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;

8.2.2 使用多个列分组

暂略

8.3 HAVING

8.3.1 使用

HAVING子句用于过滤分组：

行已经被分组。
使用了聚合函数。
满足HAVING 子句中条件的分组将被显示。
HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>10000;

8.3.2 WHERE和HAVING的对比

	优点	缺点
WHERE	先筛选数据再关联，执行效率高	不能使用分组中的计算函数进行筛选
HAVING	可以使用分组中的计算函数	在最后的结果集中进行筛选，执行效率较低

8.4 SELECT的执行过程

8.4.1 查询语法

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2：
SELECT ...,....,...
FROM ... JOIN ... 
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

8.4.2 执行顺序

1. 关键字的顺序是不能颠倒的：SF WG HOL

1	SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

1	FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

8.4.3 执行原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。

当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1，就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2。

然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4。

当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段。

首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2。

当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段，得到虚拟表 vt6。

最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表 vt7。

当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

9 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

9.1 引例

9.1.1 问题

现有解决方法：

#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;

#方式二：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`

子查询：

#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
  SELECT salary
  FROM employees
  WHERE last_name = 'Abel'
);

9.1.2 基本使用

SELECT ...
FROM ...
WHERE expr operator (
	SELECT ...
  FROM ...
);

子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用。
注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询

9.1.3 子查询分类

分类方式1：

我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。

单行子查询

多行子查询

分类方式2：

我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

9.2 单行子查询

9.2.1 单行比较操作符

操作符	含义
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
<>	not equal to

9.2.2 示例

题目：查询工资大于149号员工工资的员工的信息

SELECT last_name
FROM employees
WHERE salary > (
	SELECT salary
  FROM employees
  WHERE employee_id = 149
);

题目：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name, job_id, salary
FROM   employees
WHERE  salary = (
  SELECT MIN(salary)
  FROM   employees
);

9.2.3 HAVING中的子查询

首先执行子查询。
向主查询中的HAVING 子句返回结果。

题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) > (
  SELECT MIN(salary)
  FROM   employees
  WHERE  department_id = 50
);

9.3 多行子查询

也称为集合比较子查询
内查询返回多行
使用多行比较操作符

9.3.1 多行比较操作符

操作符	含义
IN	等于列表中的任意一个
NOT IN	判断表达式的值是否不存在于给出的列表中；如果不是，返回值为 1，否则返回值为 0。
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

9.3.2 示例

题目：返回其它job_id中比job_id为IT_PROG部门任一工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (
	SELECT salary
  FROM employees
  WHERE job_id = "IT_PROG"
)
AND job_id <> "IT_PROG";

题目：返回其它job_id中比job_id为IT_PROG部门所有工资都低的员工的员工号、姓名、job_id以及salary

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (
	SELECT salary
  FROM employees
  WHERE job_id = "IT_PROG"
)
AND job_id <> "IT_PROG";

题目：查询平均工资最低的部门id

#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
  SELECT MIN(avg_sal)
  FROM (
    SELECT AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
  ) dept_avg_sal
);

说明：第三层子查询用于查找出每个部门的平均工资；第二层子查询找出所有部门最低的平均工资，最外层查找和最低平均工资相同的部门。

#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
  SELECT AVG(salary) avg_sal
  FROM employees
  GROUP BY department_id
);

9.4 相关子查询

9.4.1 执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

语法格式：

SELECT column1, column2, ...
FROM table1 outer
WHERE column1 operator (
	SELECT column1, column2
  FROM table2
  WHERE expr1 = outer.expr2
);

9.4.2 示例

题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

SELECT last_name, salary, department_id
FROM employees outer
WHERE salary > (
	SELECT AVG(salary)
  FROM employees
  WHERE department_id=outer.department_id
);

9.4.3 EXISTS 与 NOT EXISTS

关联子查询通常也会和 EXISTS 操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
如果在子查询中不存在满足条件的行：
- 条件返回 FALSE
- 继续在子查询中查找
如果在子查询中存在满足条件的行：
- 不在子查询中继续查找
- 条件返回 TRUE
NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

题目：查询公司管理者的employee_id，last_name，job_id，department_id信息

SELECT employee_id, last_name, job_id, department_id
FROM   employees e1
WHERE  EXISTS ( 
  SELECT *
  FROM   employees e2
  WHERE  e2.manager_id = e1.employee_id
);

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
  SELECT DISTINCT manager_id
  FROM employees
);

1
2
3

SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM   employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;

题目中可以使用子查询，也可以使用自连接。一般情况建议使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。

可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

9.5 IN和EXISTS的区别

先说结论：sql时应当遵循“小表驱动大表“的原则

in适合子表比主表数据小的情况（因为in先查子表）
exists适合子表比主表数据大的情况（因为EXISTS先查主表）

9.5.1 IN的性能分析

select * from A
where id in (
  select id from B
);

sql会先执行括号内的子查询，再执行主查询，因此相当于以下过程：

1 2	select id from B; #1 select * from A where A.id = B.id; #2

分析：

in子查询是B表驱动A表
mysql先将B表的数据查出来存在内存中
遍历B表的数据，再去查A表（每次遍历都是一次连接交互，这里会耗资源）
假设B有100000条记录，A有10条记录，会交互100000次数据库；再假设B有10条记录，A有100000记录，只会发生10次交互。

9.5.2 EXISTS的性能分析

select a.* from A a
where exists (
  select * from B b where a.id=b.id
)

类似于以下过程：

1 2	select * from A; #1 select * from B where B.id = A.id; #2

分析：

exists查询是A表驱动B表
与in不同，exists将A的记录数查询到内存，因此A表的记录数决定了数据库的交互次数
假设A有10000条记录，B有10条记录，数据库交互次数为10000；假设A有10条，B有10000条，数据库交互次数为10。

10 表操作

10.1 MySQL数据类型

详见12大节。

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON；集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

其中，常用的几类类型介绍如下：

数据类型	描述
INT	从`-2^31`到`2^31-1`的整型数据。存储大小为 4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，占用4个字节，M=整数位+小数位，D=小数位。 `D<=M<=255,0<=D<=30`，默认M+D<=6
DOUBLE(M,D)	双精度，占用8个字节，D<=M<=255,0<=D<=30，默认M+D<=15
DECIMAL(M,D)	高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同。
DATE	日期型数据，格式`'YYYY-MM-DD'`
BLOB	二进制形式的长文本数据，最大可达4G
TEXT	长文本数据，最大可达4G

10.2 库操作

10.2.1 创建数据库

创建数据库

1	CREATE DATABASE 数据库名;

创建数据库并指定字符集

1	CREATE DATABASE 数据库名 CHARACTER SET 字符集;

判断数据库是否已经存在，不存在则创建数据库（推荐）

1	CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

10.2.2 使用数据库

查看当前所有的数据库

1	SHOW DATABASES; #有一个S，代表多个数据库

查看当前正在使用的数据库

1	SELECT DATABASE(); #使用的一个 mysql 中的全局函数

查看指定库下所有的表

1	SHOW TABLES FROM 数据库名;

查看数据库的创建信息

1	SHOW CREATE DATABASE 数据库名;

使用/切换数据库

USE 数据库名;

10.2.3 修改数据库

更改数据库字符集

1	ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等

10.2.4 删除数据库

删除指定的数据库

1	DROP DATABASE 数据库名;

删除指定的数据库（推荐）

1	DROP DATABASE IF EXISTS 数据库名;

10.3 创建表

10.3.1 语法

语法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);

-- 创建表
CREATE TABLE emp (
  -- int类型
  emp_id INT,
  -- 最多保存20个中英文字符
  emp_name VARCHAR(20),
  -- 总位数不超过15位
  salary DOUBLE,
  -- 日期类型
  birthday DATE
);

MySQL在执行建表语句时，将id字段的类型设置为int(11)，这里的11实际上是int类型指定的显示宽度，默认的显示宽度为11。也可以在创建数据表的时候指定数据的显示宽度。

CREATE TABLE dept(
  -- int类型，自增
  deptno INT(2) AUTO_INCREMENT,
  dname VARCHAR(14),
  loc VARCHAR(13),
  -- 主键
  PRIMARY KEY (deptno)
);

10.3.2 查看表的结构

在MySQL中创建好数据表之后，可以查看数据表的结构。MySQL支持使用DESCRIBE/DESC语句查看数据表结构，也支持使用SHOW CREATE TABLE语句查看数据表结构。

语法格式如下：

1	SHOW CREATE TABLE 表名;

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码。

10.4 修改表

使用ALTER TABLE语句。

10.4.1 追加一个列

语法格式如下：

1 2	ALTER TABLE 表名 ADD [COLUMN] 字段名字段类型 [FIRST\|AFTER 字段名];

举例：

1 2	ALTER TABLE dept80 ADD job_id varchar(15);

10.4.2 修改一个列

可以修改列的数据类型，长度、默认值和位置
修改字段数据类型、长度、默认值、位置的语法格式如下：

1 2	ALTER TABLE 表名 MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值][FIRST\|AFTER 字段名2];

举例：

1 2	ALTER TABLE dept80 MODIFY last_name VARCHAR(30);

1 2	ALTER TABLE dept80 MODIFY salary double(9,2) default 1000;

对默认值的修改只影响今后对表的修改

10.4.3 重命名一个列

使用 CHANGE old_column new_column dataType子句重命名列。语法格式如下：

1 2	ALTER TABLE 表名 CHANGE [COLUMN] 列名新列名新数据类型;

举例：

1 2	ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);

10.4.4 删除一个列

删除表中某个字段的语法格式如下：

1 2	ALTER TABLE 表名 DROP [COLUMN] 字段名;

举例：

1 2	ALTER TABLE dept80 DROP COLUMN job_id;

10.5 重命名表

方式一：使用RENAME

1 2	RENAME TABLE emp TO myemp;

方式二：使用ALTER TABLE

1 2	ALTER TABLE dept RENAME [TO] detail_dept; -- [TO]可以省略

10.6 删除表

在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除

语法格式：

1	DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

举例：

1	DROP TABLE dept80;

注意：DROP TABLE 语句不能回滚

10.7 清空表

TRUNCATE TABLE语句：
- 删除表中所有的数据
- 释放表的存储空间
举例：

1	TRUNCATE TABLE detail_dept;

注意：TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

11 CRUD

crud是指在做计算处理时的增加(Create)、读取(Read)、更新(Update)和删除(Delete)几个单词的首字母简写。crud主要被用在描述软件系统中数据库或者持久层的基本操作功能。

11.1 插入

11.1.1 VALUES

1 2	INSERT INTO 表名 VALUES (value1,value2,....);

值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

例如：

1 2	INSERT INTO departments VALUES (70, 'Pub', 100, 1700);

1 2	INSERT INTO 表名(column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]);

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,....valuen需要与column1,...columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

例如：

1 2	INSERT INTO departments(department_id, department_name) VALUES (80, 'IT');

INSERT INTO table_name 
VALUES 
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

或者插入指定列：

INSERT INTO table_name(column1 [, column2, …, columnn]) 
VALUES 
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

示例：

mysql> INSERT INTO emp(emp_id,emp_name)
    -> VALUES (1001,'shkstart'),
    -> (1002,'atguigu'),
    -> (1003,'Tom');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：

Records：表明插入的记录条数。
Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
Warnings：表明有问题的数据值，例如发生数据类型转换。

注意：字符和日期型数据应包含在单引号中。

11.1.2 将查询结果插入表中

基本语法格式如下：

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition];

示例：

INSERT INTO emp2 
SELECT * 
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM   employees
WHERE  job_id LIKE '%REP%';

11.2 更新

语法格式：

1
2
3

UPDATE 表名
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition];

可以一次更新多条数据。

示例：

使用 WHERE 子句指定需要更新的数据。

1
2
3

UPDATE employees
SET    department_id = 70
WHERE  employee_id = 113;

如果省略 WHERE 子句，则表中的所有数据都将被更新。

1 2	UPDATE copy_emp SET department_id = 110;

11.3 删除

语法格式：

1 2	DELETE FROM 表名 [WHERE <condition>];

12 MySQL数据类型

12.1 整数类型

整数类型	字节	有符号数取值范围	无符号数取值范围
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT、INTEGER	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

TINYINT：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
SMALLINT：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
MEDIUMINT：用于较大整数的计算，比如车站每日的客流量等。
INT、INTEGER：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
BIGINT：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

12.2 定点数类型

MySQL中的定点数类型只有 DECIMAL 一种类型。

数据类型	字节数	含义
DECIMAL(M,D),DEC,NUMERIC	M+2字节	有效范围由M和D决定

使用 DECIMAL(M,D) 的方式表示高精度小数，其中M被称为精度，D被称为标度。

定点数在MySQL内部是以字符串的形式进行存储，这就决定了它一定是精准的。

12.3 日期与时间类型

类型	名称	字节	日期格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

用得最多的日期时间类型，就是 DATETIME。

此外，一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用时间戳，因为DATETIME虽然直观，但不便于计算。

12.4 文本字符串类型

12.4.1 CHAR与VARCHAR

CHAR和VARCHAR类型都可以存储比较短的字符串。

字符串(文本)类型	特点	长度	长度范围	占用的存储空间
CHAR(M)	固定长度	M，默认为1	0 <= M <= 255	M个字节
VARCHAR(M)	可变长度	M，必须手动指定	0 <= M <= 65535	(实际长度 + 1) 个字节

类型	特点	空间上	时间上	适用场景
CHAR(M)	固定长度	浪费存储空间	效率高	存储不大，速度要求高
VARCHAR(M)	可变长度	节省存储空间	效率低	非CHAR的情况

12.4.2 TEXT类型

TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来代替。还有TEXT类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表。

12.5 总结

在定义数据类型时：

如果确定是整数，就用 INT；
如果是小数，一定用定点数类型 DECIMAL(M,D)；
如果是日期与时间，就用 DATETIME。

阿里巴巴《Java开发手册》之MySQL数据库：

任何字段如果为非负数，必须是 UNSIGNED
【强制】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。
- 说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并分开存储。
【强制】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
【强制】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过5000。如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

13 约束

13.1 概述

13.1.1 什么是约束

约束（constraint）是表级的强制规定。

可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

13.1.2 约束分类

根据约束数据列的限制，约束可分为：
- 单列约束：每个约束只约束一列
- 多列约束：每个约束可约束多列数据
根据约束的作用范围，约束可分为：
- 列级约束：只能作用在一个列上，跟在列的定义后面
- 表级约束：可以作用在多个列上，不与列一起，而是单独定义
根据约束起的作用，约束可分为：
- NOT NULL 非空约束，规定某个字段不能为空
- UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
- PRIMARY KEY 主键(非空且唯一)约束
- FOREIGN KEY 外键约束
- CHECK 检查约束，MySQL不支持
- DEFAULT 默认值约束

查看某个表已有的约束

#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints 
WHERE table_name = '表名称';

13.2 非空约束

13.2.1 概述

非空约束（NOT NULL）限定某个字段/某列的值不允许为空。

特点：

默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
一个表可以有很多列都分别限定了非空
空字符串''不等于NULL，0也不等于NULL

13.2.2 操作

建表时添加非空约束

CREATE TABLE 表名称(
  字段名  数据类型,
  字段名  数据类型 NOT NULL,  -- 添加非空约束
  字段名  数据类型 NOT NULL
);

示例：

CREATE TABLE student(
  sid int,
  sname varchar(20) not null,
  tel char(11) ,
  cardid char(18) not null
);

insert into student values(1,'张三','13710011002','110222198912032545'); #成功

insert into student values(2,'李四','13710011002',null);#身份证号为空
ERROR 1048 (23000): Column 'cardid' cannot be null

insert into student values(2,'李四',null,'110222198912032546');#成功，tel允许为空

insert into student values(3,null,null,'110222198912032547');#失败
ERROR 1048 (23000): Column 'sname' cannot be null

建表后添加非空约束

1	ALTER TABLE 表名称 MODIFY 字段名数据类型 NOT NULL;

删除非空约束

1 2	# 去掉NOT NULL，相当于修改某个非注解字段，该字段允许为空 ALTER TABLE 表名称 MODIFY 字段名数据类型;

13.3 唯一性约束

13.3.1 概述

唯一性约束（UNIQUE）用来限制某个字段/某列的值不能重复。

特点：

同一个表可以有多个唯一约束。
唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
唯一性约束允许列值为空。
在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
MySQL会给唯一约束的列上默认创建一个唯一索引。

关键字：UNIQUE或UNIQUE KEY

13.3.2 添加唯一约束

建表时

CREATE TABLE 表名称(
  字段名  数据类型,
  字段名  数据类型  UNIQUE,  
  字段名  数据类型  UNIQUE KEY,
  字段名  数据类型
);

-- 指定约束名
CREATE TABLE 表名称(
  字段名  数据类型,
  字段名  数据类型,  
  字段名  数据类型,
  [CONSTRAINT 约束名] UNIQUE KEY(字段名)
);

示例：

CREATE TABLE t_course(
	cid INT UNIQUE,
	cname VARCHAR(100) UNIQUE,
	description VARCHAR(200)
);

CREATE TABLE users(
 id INT NOT NULL,
 NAME VARCHAR(25),
 PASSWORD VARCHAR(16),
 -- 使用表级约束语法
 CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD) -- 用户名和密码的组合不能重复
);

CREATE TABLE 表名称(
  字段名  数据类型,
  字段名  数据类型,  
  字段名  数据类型,
  UNIQUE KEY(字段列表) # 字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的
);

建表后

#方式1：
ALTER TABLE 表名称 ADD UNIQUE KEY(字段列表);

#方式2：
ALTER TABLE 表名称 MODIFY 字段名 字段类型 UNIQUE;

示例：

ALTER TABLE USER 
ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER 
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);

ALTER TABLE USER 
MODIFY NAME VARCHAR(20) UNIQUE;

13.3.3 删除唯一约束

添加唯一性约束的列上也会自动创建唯一索引。
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; #查看都有哪些约束

ALTER TABLE USER 
DROP INDEX uk_name_pwd;

13.4 主键约束

13.4.1 概述

主键约束（PRIMARY KEY）用来唯一标识表中的一行记录。

特点：

主键约束相当于唯一约束（UNIQUE）+非空约束（NOT NULL）的组合，主键约束列不允许重复，也不允许出现空值。

一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
主键约束对应着表中的一列或者多列（复合主键）
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

13.4.2 添加主键约束

建表时

create table 表名称(
  字段名  数据类型  primary key, #列级模式
  字段名  数据类型,  
  字段名  数据类型  
);

create table 表名称(
  字段名  数据类型,
  字段名  数据类型,  
  字段名  数据类型,
  [constraint 约束名] primary key(字段名) #表级模式
);

示例：

create table temp(
  id int primary key,
  name varchar(20)
);

建表后

1	ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

13.5 自增约束

13.5.1 概述

作用：某个字段的值自增（AUTO_INCREMENT）

特点和要求：

一个表最多只能有一个自增长列
当需要产生唯一标识符或顺序值时，可设置自增长
自增长列约束的列必须是键列（主键列或唯一键列）
自增约束的列的数据类型必须是整数类型
如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

13.5.2 指定自增约束

create table 表名称(
  字段名  数据类型  primary key auto_increment,
  字段名  数据类型  unique key not null,  
  字段名  数据类型  unique key,
  字段名  数据类型  not null default 默认值, 
);

create table 表名称(
  字段名  数据类型 default 默认值 ,
  字段名  数据类型 unique key auto_increment,  
  字段名  数据类型 not null default 默认值,,
  primary key(字段名)
);

示例：

create table employee(
  eid int primary key auto_increment,
  ename varchar(20)
);

13.6 外键约束

13.6.1 概述

作用：外键约束（FOREIGN KEY）限定某个表的某个字段的引用完整性。

比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

主表为Department，从表为Employees。

特点：

从表的外键列，必须引用主表的主键或唯一约束的列
在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1），也可以指定外键约束名。
当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
在从表中指定外键约束，并且一个表可以建立多个外键约束
从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。
当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）
删除外键约束后，必须手动删除对应的索引

13.6.2 添加外键约束

create table 主表名称(
  字段1  数据类型  primary key,
  字段2  数据类型
);

create table 从表名称(
  字段1  数据类型  primary key,
  字段2  数据类型,
  [CONSTRAINT <外键约束名称>] FOREIGN KEY (从表的某个字段) references 主表名(被参考字段)
);

#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样

-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

示例：

create table dept( #主表
  did int primary key,		#部门编号
  dname varchar(50)			#部门名称
);

create table emp(#从表
  eid int primary key,  #员工编号
  ename varchar(5),     #员工姓名
  deptid int,				#员工所在的部门
  foreign key (deptid) references dept(did)   #在从表中指定外键约束
  #emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);

说明：
（1）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。
（2）删除表时，先删除从表emp，再删除主表dept

13.6.3 开发场景

问题1：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？

答：不是的

问题2：建和不建外键约束有什么区别？

答：建外键约束，操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。

不建外键约束，操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的引用完整性，只能依靠程序员的自觉，或者是在Java程序中进行限定。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。

问题3：那么建和不建外键约束和查询有没有关系？

答：没有。

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以， MySQL 允许不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

阿里开发规范：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

13.7 默认值约束

13.7.1 概述

作用：默认值约束（DEFAULT）给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

13.7.2 给字段加默认值

create table 表名称(
  字段名  数据类型  primary key,
  字段名  数据类型  unique key not null,  
  字段名  数据类型  unique key,
  字段名  数据类型  not null default 默认值, 
);
create table 表名称(
  字段名  数据类型 default 默认值 ,
  字段名  数据类型 not null default 默认值,  
  字段名  数据类型 not null default 默认值,
  primary key(字段名),
  unique key(字段名)
);

说明：默认值约束一般不在唯一键和主键列上加

示例：

create table employee(
  eid int primary key,
  ename varchar(20) not null,
  gender char default '男',
  tel char(11) not null default '' #默认是空字符串
);

13.8 思考题

面试1、为什么建表时，加 not null default ‘’ 或 default 0

答：不想让表中出现null值。

面试2、为什么不想要 null 的值

答:（1）不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。

（2）效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ‘’ 或 default 0

面试3、带AUTO_INCREMENT约束的字段值是从1开始的吗？
在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。

面试4、并不是每个表都可以任意选择存储引擎？

外键约束（FOREIGN KEY）不能跨引擎使用。

MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。

14 视图

14.1 定义

MySQL 视图是一个虚拟的表，它由一个 SQL 查询定义，并且不存储实际的数据。视图的数据来自于查询执行的结果，并且可以像表一样被查询、更新和删除。视图提供了一种简化复杂查询的方法，并且可以用来限制用户对数据库中特定数据的访问。

创建视图所基于的表称为基表。

MySQL视图可以简化对基本表的复杂查询，同时保护基本表的数据，因此可以提高查询效率和重用性。
视图可以被视为存储在数据库中的预定义查询，可以方便地进行数据分析和报表生成。
视图还可以实现数据独立性，即使基本表的结构发生变化，视图的结构和查询语句也不需要改变。因此，使用视图可以使数据库的设计更加简单、安全和灵活。

14.2 操作

创建

1	create view 视图名称 as select 字段1，字段2... from 表；

注意：视图名称必须是唯一的

调用

1	select * from 视图名称;

15 存储过程与函数

存储过程就是一条或者多条SQL语句的集合，可以视为批文件。它可以定义批量插入的语句，也可以定义一个接收不同条件的SQL。

16 变量、流程控制与游标

暂略

17 触发器

暂略

18 范式

18.1 概述

计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式（Normal Form, NF），各种范式呈递次规范，越高的范式数据库冗余越小。

目前业界范式有：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)。

18.2 函数依赖

函数依赖简单点说就是：某个属性集决定另一个属性集时，称另一属性集依赖于该属性集。

以下面的表格为例：

学号	姓名	系名	系主任	科名	分数
001	张三	计算机系	李雷	高等数学	87
001	张三	计算机系	李雷	大学英语	88
001	张三	计算机系	李雷	数据库设计	89
002	李四	计算机系	李雷	高等数学	86
002	李四	计算机系	李雷	java程序设计	90
002	李四	计算机系	李雷	大学英语	98
003	王五	财务系	韩梅梅	高等数学	96
003	王五	财务系	韩梅梅	财务基础	95

18.2.1 完全函数依赖

设X，Y是关系R的两个属性集合，X'是X的真子集，存在X→Y，但对每一个X'都有X'!→Y，则称Y完全函数依赖于X。

比如通过(学号，课程) 推出分数，但是单独用学号推断不出来分数，那么就可以说：分数完全依赖于(学号，课程) 。

18.2.2 部分函数依赖

假如 Y 函数依赖于 X，但同时 Y 并不完全函数依赖于 X，那么我们就称 Y 部分函数依赖于 X。

比如通过(学号，课程) 推出姓名，因为其实直接可以通过学号推出姓名，所以：姓名部分依赖于 (学号，课程)。

18.2.3 传递函数依赖

设X，Y，Z是关系R中互不相同的属性集合，存在X→Y(Y!→X),Y→Z，则称Z传递函数依赖于X。

比如：学号推出系名，系名推出系主任，但是，系主任推不出学号，系主任主要依赖于系名。这种情况可以说：系主任传递依赖于学号。

18.3 1NF

第一范式1NF核心原则：属性必须不可切割。

学号	姓名	系名	系主任	课名	分数	学籍信息
001	张三	计算机系	李雷	高等数学	87	本科，大二
002	李四	计算机系	李雷	大学英语	88	研究生,研三

很明显上面表格设计是不符合第一范式的，学籍信息列中的数据不是原子数据项，是可以进行分割的，因此对表格进行修改，让表格符合第一范式的要求，修改结果如下图所示：

学号	姓名	系名	系主任	课名	分数	学历	所在年级
001	张三	计算机系	李雷	高等数学	87	本科	大二
002	李四	计算机系	李雷	大学英语	88	研究生	研三

18.4 2NF

第二范式2NF核心原则：不能存在部分函数依赖。

目的：消除非主属性对主码的部分函数依赖

学号	姓名	系名	系主任	课名	分数
001	张三	计算机系	李雷	高等数学	87
001	张三	计算机系	李雷	大学英语	88
001	张三	计算机系	李雷	数据库设计	89
002	李四	计算机系	李雷	高等数学	86
002	李四	计算机系	李雷	java程序设计	90
002	李四	计算机系	李雷	大学英语	98
003	王五	财务系	韩梅梅	高等数学	96
003	王五	财务系	韩梅梅	财务基础	95

以上表格明显存在部分依赖。比如，这张表的主键是 (学号，课名），分数确实完全依赖于(学号，课名），但是姓名并不完全依赖于(学号，课名），让表格符合第二范式的要求，修改结果如下图所示：

学号	姓名	系名	系主任
001	张三	计算机系	李雷
002	李四	计算机系	李雷
003	王五	财务系	韩梅梅

学号	课名	分数
001	高等数学	87
001	大学英语	88
001	数据库设计	89
002	高等数学	86
002	java程序设计	90
002	大学英语	98
003	高等数学	96
003	财务基础	95

18.5 3NF

第三范式核心原则：不能存在传递函数依赖。

学号	姓名	系名	系主任
001	张三	计算机系	李雷
002	李四	计算机系	李雷
003	王五	财务系	韩梅梅

在上面这张表中，存在传递函数依赖：学号->系名->系主任，但是系主任推不出学号。

上面表需要再次拆解：

学号	姓名	系名
001	张三	计算机系
002	李四	计算机系
003	王五	财务系

系名	系主任
计算机系	李雷
计算机系	李雷
财务系	韩梅梅