MySQL初级

本文是针对B站尚硅谷MySQL教程（基础篇）的笔记，视频链接https://www.bilibili.com/video/BV1iq4y1u7vj?p=2&spm_id_from=pageDriver&vd_source=276d55048634a5b508b1b53a1ecd56b3
2022/7/2 初学阶段暂时借助视频进行学习，之后看书阶段会将视频中没有涉及到的或者存在问题的地方进行补充，主要参考书籍《MySQL必知必会》《数据库系统概念》

2022/7/10 完成了初级阶段的学习，接下来准备参考上面提到的两本书进行进阶学习，最后高级学习再次回到B站视频；

一、概述

Q:数据库有什么用？

A:数据库作为一种持久化的存储介质，类似于doc、excel文件一样，可以将数据保存在可掉电式的存储设备中供之后使用（硬盘属于可掉电式设备，内存属于不可掉电式），doc、excel等只能存储相对简单的数据，对于一些结构比较复杂的数据，我们采用数据库进行保存；

1.数据库

数据库系统中常有以下术语容易让人混淆（下面三者共同组成数据库系统）

• 数据库:即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。我们可以简单认为数据库就是电脑磁盘上的一块区域，属于硬件。一般来说，数据库是什么名字则数据库管理系统使用相同的名字的管理系统；

• 数据库管理系统:数据库管理系统（Database Management System），是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。我们学习过程中需要安装的就是MySQL数据库管理系统（简称MySQL数据库），我们要学的也是DBMS而非数据库。

• SQL:结构化查询语言（Structured Query Language），是一种专门用来与数据库通信的语言。

简单来说，开发人员通过DBMS使用SQL语言与数据库进行通信，选取或操作数据库中的基本对象（表、视图…）

目前流行的大型关系型数据库有Oracle、MySQL、PostgreSQL和SQL Server，总体形式大致如下：

• Oracle数据库性能卓越，强大无比，在商用数据库中独占鳌头，无与争锋；

• MySQL数据库问市已有二十余年，是第一个免费开源的数据库，已被Oracle公司收购，不管Oracle是出于什么目的，MySQL数据库在架构的技术上将难有进步；

• PostgreSQL数据库也是免费开源的数据库，很多人认为它是未来免费开源数据库的主流；

• SQL Server数据库是微软公司的产品，微软依靠其强大的Windows生态圈成为市值最高的软件公司，但是，自Windows问市以来，用户对微软的抱怨从未停止。

在实际项目中，数据量和访问量比较少的系统普遍选择MySQL数据库，但是，在政府部门和企业里，重要的系统只能依赖Oracle数据库；

MySQL 由于其体积小、速度快、总体拥有成本低，可处理上千万条记录的大型数据库，尤其是开放源码这一特点，使得很多互联网公司、中小型网站选择了MySQL作为网站数据库；

1.1 RDB与非RDB

（1）关系型数据库

• 关系型数据库是最古老的数据库类型，关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）；
• 关系型数据库以行(row)和列(column)的形式存储数据，以便于用户理解。这一系列的行和列被称为表(table)，一组表组成了一个库(database)；
• 表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系型数据库，就是建立在关系模型基础上的数据库；
• SQL 就是关系型数据库的查询语言；

特点：

• 支持复杂查询，可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询；
• 事务支持，使得对于安全性能很高的数据访问要求得以实现；

（2）非关系型数据库

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

常见的非关系型数据库可分为以下几类：

• 键值型数据库通过 Key-Value 键值的方式来存储数据，其中 Key 和 Value 可以是简单的对象，也可以是复杂的对象。Key 作为唯一的标识符，优点是查找速度快，在这方面明显优于关系型数据库，缺点是无法像关系型数据库一样使用条件过滤（比如 WHERE），如果你不知道去哪里找数据，就要遍历所有的键，这就会消耗大量的计算；
• 文档型数据库可存放并获取文档，可以是XML、JSON等格式。在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录。文档数据库所存放的文档，就相当于键值数据库所存放的“值”。MongoDB 是最流行的文档型数据库。此外，还有CouchDB等；
• 搜索引擎数据库，虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎数据库是应用在搜索引擎领域的数据存储形式，由于搜索引擎会爬取大量的数据，并以特定的格式进行存储，这样在检索的时候才能保证性能最优。核心原理是“倒排索引”；
• 列式数据库是相对于行式存储的数据库，Oracle、MySQL、SQL Server 等数据库都是采用的行式存储（Row-based），而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的 I/O，适合于分布式文件系统，不足在于功能相对有限；
• 图形数据库，利用了图这种数据结构存储了实体（对象）之间的关系。图形数据库最典型的例子就是社交网络中人与人的关系，数据模型主要是以节点和边（关系）来实现，特点在于能高效地解决复杂的关系问题。关系型数据用于存储明确关系的数据，但对于复杂关系的数据存储却有些力不从心。如社交网络中人物之间的关系，如果用关系型数据库则非常复杂，用图形数据库将非常简单；

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

1.2 RDB概述

• 关系型数据库的典型数据结构就是数据表，这些数据表的组成都是结构化的（Structured）；
• 先将数据放到表中，再将表放到数据库中；
• 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己——表名具有唯一性；
• 表具有一些特性，这些特性定义了数据在表中如何存储；

（1）实体-联系模型

我们学习数据库需要知道数据库中常用的一种模型——E-R模型，常用于编程中数据库与编程语言（如JAVA、Python）的转换；

E-R（entity-relationship，实体-联系）模型中有三个主要概念是：实体集、属性、联系集

• 联系集：数据库中的一个表对应Python中的一个类；

• 实体集：表中的一行对应类的一个实体对象；

• 属性：表中的一列对应类中的一个字段或属性；

（2）表的关联

表与表之间的数据记录（也就是表的一行）存在关联关系(relationship)，主要有四种：一对一关联、一对多关联、多对多关联、自我引用

a)一对一关联

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

• 拆为两个表：两个表的记录是一一对应关系（比如在实际设计中如果一个表的字段太多了就可以将一个表拆分为多个表，也可以按照是否常用拆分，拆分的好处有很多，拆分是一种优化思想）

• 基础信息表（常用信息）：学号、姓名、手机号码、班级、系别

• 档案信息表（不常用信息）：学号、身份证号码、家庭住址、籍贯、紧急联系人、…

b)一对多关联

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

举例：一个部门有多个员工，一个员工只对应一个部门；

c)多对多关联

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

多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

举例：

学生信息表：一行代表一个学生的信息

课程信息表：一行代表一门课程的信息

选课信息表：充当联接表的角色，一个学生可以选择多门课程，一门课程可以被多名学生选择，这就是多对多关系

d)自我引用

举例：员工的主管也是公司中的员工，同样有员工编号

2.MySQL简介

根据教程我们成功安装了MySQL8.0版本以及图形管理工具Navicat，下面简单介绍一下基本操作（命令行和图形界面操作均有），在之后我们还会详细讲解这些操作的底层原理

安装过程几个注意点：

• MySQL自带一个root用户，在安装时要求我们输入root用户的密码，这个密码一定要记住，这将用于之后我们客户端登录；
• 用户与权限管理章节我们使用add user添加其他用户，所以前期使用root用户即可；
• mysql80这个Windows Service Name是再安装过程中需要我们指定的，当然你取一个自己喜欢的也可以，这将用于在服务器端启动MySQL服务；

2.1 启动并登录

MySQL安装完毕之后，需要启动服务器进程，不然客户端无法连接数据库。

我们这里只介绍使用命令行启动和关闭服务（使用cmd需要在管理员权限下，当然也可以在“服务”手动管理）

# 启动 MySQL 服务命令：
net start MySQL服务名#本机上是mysql80

# 停止 MySQL 服务命令：
net stop MySQL服务名

启动成功后界面如下

当MySQL服务启动完成后，便可以通过客户端（命令行或者Navicat）来登录并使用MySQL数据库。注意：在登陆之前要确认服务是开启的。

下面先介绍通过Window命令行登录

• 格式

mysql -h 主机名 -P 端口号 -u 用户名 -p密码

• 举例

mysql -h localhost -P 3306 -u root -p123456  # 假设此处设置的root用户的密码是123456

注意：

（1）-p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格。如：

mysql -hlocalhost -P3306 -uroot -pabc123

（2）密码建议在下一行输入，保证安全

mysql -h localhost -P 3306 -u root -p
Enter password:****

（3）客户端和服务器在同一台机器上，所以不用输入localhost或者IP地址127.0.0.1。同时，因为是连接本机，故-hlocalhost就可以省略，如果端口号没有修改：-P3306也可以省略

Shell命令可以简写成：

mysql -u root -p
Enter password:****

连接成功后，有关于MySQL Server服务版本的信息，还有第几次连接的id标识也可以在命令行通过以下方式获取MySQL Server服务版本的信息：

c:\> mysql --version

或登录成功后，通过以下方式查看当前版本信息：

MySQL语言需要在末尾添加分号表示结束该语句

mysql> select version();

退出登录

exit
或
quit

前面说过，我们安装有图形化工具Navicat，所以这里再介绍一下使用图形化界面工具登录的操作（使用Navicat的前提也是需要启动mysql80数据库服务器）

当我们创建了一个链接过后，以后还想使用这个链接，就不需要再新建链接了

对于我们常用的链接，可以选择导出，下次直接在对应位置寻找链接连接即可

2.2 查看所有数据库

show databases;

显示结果如下（初始状态没有人为添加数据库）

“information_schema”是 MySQL 系统自带的数据库，主要保存 MySQL 数据库服务器的系统信息，比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件 所在的文件夹和系统使用的文件夹，等等

“performance_schema”是 MySQL 系统自带的数据库，可以用来监控 MySQL 的各类性能指标。

“sys”数据库是 MySQL 系统自带的数据库，主要作用是以一种更容易被理解的方式展示 MySQL 数据库服务器的各类性能指标，帮助系统管理员和开发人员监控 MySQL 的技术性能。

“mysql”数据库保存了 MySQL 数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用的字符集、约束检查信息，等等

2.3 创建数据库

• 格式

create database 数据库名;

• 示例

#创建atguigudb数据库，该名称不能与已经存在的数据库重名。
create database atguigudb;

如果没有使用use语句，后面针对数据库的操作也没有加“数据库名”的限定，那么会报“ERROR 1046 (3D000): No database selected”（没有选择数据库）；

使用完use语句之后，如果接下来的SQL都是针对一个数据库操作的，那就不用重复use了，如果要针对另一个数据库操作，那么要重新use。

2.4 查看库中的表

• 格式

show tables; 			 #查看当前库中的表，要求前面有use语句

show tables from 数据库名; #查看指定数据库下的表

2.5 创建新表

• 格式

如果是最后一个字段，后面不用加逗号，因为逗号的作用是分割每个字段

create table 表名称(
	字段名  数据类型,
	字段名 数据类型
);

• 示例

#创建学生表
create table student(
	id int,
    name varchar(20)  #说名字最长不超过20个字符
);

2.6 查看表中的数据

• 格式

select * from 数据库表名称;

• 示例

#查看学生表的数据
select * from student;

2.7 添加数据

• 格式

insert into 表名称 values(值列表);

• 示例

#添加两条记录到student表中
insert into student values(1,'张三');
insert into student values(2,'李四');

2.8 查看创建信息

（1）查看表的创建信息

• 格式

show create table 表名称\G

• 示例

#查看student表的详细创建信息
show create table student\G

（2）查看数据库的创建信息

• 格式

show create database 数据库名\G

• 示例

#查看atguigudb数据库的详细创建信息
show create database atguigudb\G

3.SQL简介

不同的数据库生产厂商都支持SQL语句，但都额外有特有内容，可以用下面这幅图形象地展示其关系

SQL语言在功能上主要分为如下3大类（一般来说编程语言很少会分类，我们学习SQL也就是按照这三条主线来学习）：

• DDL（Data Definition Languages、数据定义语言）

  • 这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
  • 主要的语句关键字包括CREATE、DROP、ALTER等。

• DML（Data Manipulation Language、数据操作语言）——增删改查是SQL最重要的操作，而其中查询SELECT又是重中之重

  • 用于添加、删除、更新和查询数据库记录，并检查数据完整性。
  • 主要的语句关键字包括INSERT、DELETE、UPDATE、SELECT等。
  • SELECT是SQL语言的基础，最为重要。

• DCL（Data Control Language、数据控制语言）

  • 用于定义数据库、表、字段、用户的访问权限和安全级别。
  • 主要的语句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。

3.1 SQL语法规则

下面介绍的SQL语法规则同样适用于MySQL，但MySQL的某些特性可能并不适用于SQL

• SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
• 每条命令以;或\g或\G结束
• 关键字不能被缩写也不能分行
• 关于标点符号
  • 必须保证所有的()、单引号、双引号是成对结束的
  • 必须使用英文状态下的半角输入方式

3.2 SQL语法规范

• MySQL 在 Windows 环境下是大小写不敏感的
• MySQL 在 Linux 环境下是大小写敏感的
  • 数据库名、表名、表的别名、变量名是严格区分大小写的
  • 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
• 推荐采用统一的书写规范：
  • 数据库名、表名、表别名、字段名、字段别名等都小写
  • SQL关键字、函数名、绑定变量等都大写
• 关于标点符号
  • 字符串型和日期时间类型的数据尽量使用单引号（’ ‘）表示（使用””也不会报错）
  • 列的别名，尽量使用双引号（” “），而且不建议省略as（使用’’也不会报错）

3.3 SQL命名规则

• 数据库名、表名、字段名等对象名中间不要包含空格（实际上课程视频中举例使用了空格也没报错，但最好遵循规范）
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名

3.4 注释

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

3.5 导入数据

主要有两种方式导入现有的数据文件，一种是使用Shell命令，一种是使用图形化界面的按钮

mysql> source d:\mysqldb.sql

二、SQL查询语句

因为MySQL本质上就是在SQL的基础上增加了一些新的特性，所以我们完全可以先学习SQL的基本语句，将其运用在MySQL上是同样适用的；

这里我们先介绍的是SQL的数据操作语言中的查询语句，因为这属于整个SQL中最重要的概念，在本章之后我们会按照正常的顺序创建数据库、创建表、对表进行管理…来介绍；

1.SELECT语句

1.1 SELECT FROM

该语句是最基本的查询语句，作用是在指定的表中查找指定的字段

• 格式

SELECT 字段1，字段2 FROM 字段所在表

• 选择全部列

SELECT * FROM   departments;

• 选择特定列

SELECT department_id, location_id
FROM   departments;

1.2 SELECT AS FROM

该语句的作用就是给查询的列取别名，主要方式有三种

• 列名和别名之间空格分隔；
• 列名和别名之间加入AS；
• 别名使用””；

SELECT last_name AS name FROM   employees;
SELECT commission_pct comm commission_pct comm;
SELECT salary*12 "Annual Salary" FROM   employees;

1.3 SELECT DISTINCT FROM

该语句的作用是将查询结果中的重复行去除

SELECT DISTINCT department_id,salary 
FROM employees;

DISTINCT 需要放到所有列名的前面

DISTINCT 其实是对后面所有列名的组合进行去重

1.4 SELECT FROM WHERE

当我们需要将查询结果过滤，返回我们想要的查询结果时，可以使用WHERE子句将不满足条件的结果过滤

SELECT employee_id, last_name, job_id, department_id
FROM   employees
WHERE  department_id = 90 ;

1.5 注意事项

所有运算符或列值遇到null值，运算的结果都为null

在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。

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

mysql> SELECT * FROM `ORDER`;

当我们需要给查询结果额外添加一列时，可以在SELECT查询结果中增加一列固定的常数，用该常数列作为标记方便整合不同的数据源

2.DESCRIBE语句

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

DESCRIBE employees;
或
DESC employees;

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

3.运算符

3.1 算术运算符

算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算

++++++加法与减法运算符++++++

• 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
• 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
• 加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
• 在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）

++++++乘法与除法运算符++++++

• 一个数乘以整数1和除以整数1后仍得原数；
• 一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
• 一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
• 一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
• 乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同；
• 在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL；

3.2 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。

比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

符号型运算符

（1）等于运算符

等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。

在使用等号运算符时，遵循如下规则：

• 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等；
• 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小；
• 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较；
• 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL；

（2）安全等于运算符

安全等于运算符（<=>）与等于运算符（=）的作用是相似的，唯一的区别是‘<=>’可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。

（3）不等于运算符

不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。

非符号型运算符

（4）空运算符&非空运算符

空运算符（IS NULL或者ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回0。

非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。

（5）最小值运算符&最大值运算符

最小值运算符 语法格式为：LEAST(值1，值2，…，值n)。其中，“值n”表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值。

最大值运算符 语法格式为：GREATEST(值1，值2，…，值n)。其中，n表示参数列表中有n个值。当有两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。、、

（6）BETWEEN AND运算符

BETWEEN运算符使用的格式通常为

SELECT D FROM TABLE WHERE C BETWEEN A AND B

当C大于等于A，且C小于等于B时，结果为1，否则结果为0。

（7）LIKE运算符

LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。

LIKE运算符通常使用如下通配符：

“%”：匹配0个或多个字符。
“_”：只能匹配一个字符。

（8）REGEXP运算符

REGEXP运算符用来匹配字符串，语法格式为：expr REGEXP 匹配条件。如果expr满足匹配条件，返回1；如果不满足，则返回0。若expr或匹配条件任意一个为NULL，则结果为NULL。

REGEXP运算符在进行匹配时，常用的有下面几种通配符：

（1）‘^’匹配以该字符后面的字符开头的字符串。
（2）‘$’匹配以该字符前面的字符结尾的字符串。
（3）‘.’匹配任何一个单字符。
（4）“[…]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
（5）‘’匹配零个或多个在它前面的字符。例如，“x”匹配任何数量的‘x’字符，“[0-9]”匹配任何数量的数字，而“”匹配任何数量的任何字符。

3.3 逻辑运算符

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

3.4 位运算符

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。

3.5 运算符优先级

数字编号越大，优先级越高，优先级高的运算符先进行计算。可以看到，赋值运算符的优先级最低，使用“()”括起来的表达式的优先级最高。

4.排序与分页

4.1 排序

当我们需要对查询得到的结果进行排序时，可以使用ORDER BY子句

排序规则

• 使用 ORDER BY 子句排序
  • ASC（ascend）代表升序
  • DESC（descend）代表降序
• ORDER BY 子句在SELECT语句的结尾

（1）单列排序

• 示例

SELECT   last_name, job_id, department_id, hire_date
FROM     employees
ORDER BY hire_date DESC ;

（2）多列排序

• 示例

SELECT last_name, department_id, salary
FROM   employees
ORDER BY department_id, salary DESC;

4.2 分页

Q1:查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？

Q2:表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？

A:针对以上问题，我们使用分页显示，也就是将查询得到的结果按照一定条件分段显示

• 格式

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;

LIMIT 子句必须放在整个SELECT语句的最后！

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

5.*多表查询

（本小节相对较难，所以并没有特别关注课件之后的内容，如果有需要可以以后再学，这里只是介绍了基本的一些概念和语句的使用方法）

前面我们已经简单介绍过将一张表拆分为多张表的好处（节约空间、加载迅速、避免访问冲突），但是当我们想要根据某张表的信息查出另一张表的某个信息时就没有一张表方便，这将使用多表查询；

多表查询，也称为关联查询，指两个或更多个有关联关系的表一起完成查询操作；

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定存在关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

比如：员工表和部门表，这两个表依靠“部门编号”进行关联；

最简单的多表查询如下，我们假设数据库中有三张表，分别保存员工信息、部门信息、部门位置信息，我们想要知道员工Abel的部门位置，就需要使用下面三条语句分别进行查询

SELECT department_id
FROM employees
WHERE last_name='Abel';

SELECT location_id
FROM departments
WHERE department_id=80;

SELECT city
FROM locations
WHERE location_id=2500;

但是使用这种方式，需要经过多次交互才能达到目的，所以我们需要改进，是否能够将以上三个语句合并成为一个语句；

5.1 笛卡尔积

假如我们直接使用下面这种方式进行查询，得到的结果是错误的，这将是一个排列组合的问题，也称为笛卡尔积的错误；

SELECT last_name, department_name
FROM employees, departments

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

笛卡尔积的查询结果可以用下面这张图进行理解（效果就是所有表的所有行互相连接），这明显不是我们想要的查询结果

笛卡尔积的错误是如下原因导致的：

• 省略多个表的连接条件（或关联条件）
• 连接条件（或关联条件）无效

因此我们需要避免这些情况：

1.在查询语法中加入有效连接条件

SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

2.当表中有相同列时，在列名之前加上表名前缀（按照规范来说无论是否有相同列我们最好都指明列来自哪个表）

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

疑问：我们使用WHERE条件过滤，本质上是查询之后过滤结果再返回还是再查询之前就限定了查询的条件呢？

之所以会有这个疑问是因为我在想，加入WHERE连接条件能够实现正确的多表查询其原理究竟是在查询之前就按照WHERE的条件进行查询还是说是查询之后在笛卡尔结果中使用WHERE条件过滤正确的结果？

尽管这两个解释都是对的，但是在时间的效率上明显直接按照WHERE限定条件来进行查询是更高的；

5.2 多表查询拓展

我们在进行多表查询的时候有以下几种拓展方式

（1）多个连接条件用 AND 操作符连接

SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id and departments.location_id = locations.location_id;

（2）列名前使用表名前缀不仅可以区分不同表中的相同列名，还可以提高查询效率

• 举例

SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

（3）当表名过长时可以给表取别名，使用别名可以简化查询

• 举例

SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

（4）连接多个表需要连接条件的个数

总结：连接 n个表,至少需要n-1个连接条件。比如，连接3个表，至少需要2个连接条件。

（5）多表查询可以按照不同方式分为多种类别

• 按照连接条件分为等值连接和非等值连接；

• 自连接和非自连接；

• 内连接和外连接；

5.3 等值连接&非等值连接

等值连接就是我们前面使用的连接条件都是等于运算符；

非等值连接顾名思义就是连接条件不使用等于运算符，下面我们举一个例子说明非等值连接

下面的JOB_GRADES中的GRA工资等级是出于一个范围中的

我们需要对上面的两个表进行查询，使用如下的查询语句

SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

得到结果如下

5.4 自连接&非自连接

自连接就是我们前面介绍过的本表中的一行对象的某个属性指向本表中的另一行对象，非自连接反之；

我们前面介绍的都是非自连接，也就是不同的表之间实现的连接操作；

下面我们简单使用一个语句查询员工的id和姓名以及其上级的id和姓名，我们可以在逻辑上将这一个表看作两张表来书写连接条件

SELECT emp.employee_id,emp.last_name,mgr,employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.manager_id=mgr.employee_id

5.5 内连接&外连接

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行（简单来说就是连接条件相同的行才会合并，否则丢弃比如id为NULL的职工就被丢弃）；我们上面写的例子都是内连接；

• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

  • 如果是左外连接，则连接条件中左边的表（左表）也称为主表（即谁不满足条件谁就是主表），右边的表称为从表；

  • 如果是右外连接，则连接条件中右边的表（右表）也称为主表，左边的表称为从表；

  • 如果同时返回左/右表中满足条件的行，则称为满外连接；

连接条件左边的表为左表；

连接条件右边的表为右表；

长度较短的为从表；

长度较长的为主表；

上面我们仅介绍了内连接和外连接的概念，下面我们简单介绍一下其实现，题干中出现查询所有员工的信息，那么我们就需要使用外连接

SQL92实现多表查询

（1）内连接

方式同上

（2）外连接

• 在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表，简单理解就是给短的从表垫一截东西使之与主表长度相同；

• Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。

• 在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);

#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

SQL99语法实现多表查询

因为Mysql不支持SQL92语法的外连接（内连接是支持的），所以我们只能使用SQL99语法实现MySQL的外连接；

（1）内连接

• 内连接格式

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

SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。如果你采用 SQL92，可读性就会大打折扣。

• 可以使用 ON 子句指定额外的连接条件;
• 这个连接条件是与其它条件分开的;
• ON 子句使语句具有更高的易读性;
• 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接;

（2）外连接

• 左外连接格式

#实现查询结果A为主表，B为从表
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) ;

• 右外连接格式

#实现查询结果A为主表，B为从表
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) ;

LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，只能用 (+) 表示。

• 满外连接

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。

• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN代替。

5.6 Union的使用

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。

合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

• 格式

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

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

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

执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

6.函数

函数可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率，又提高了可维护性；

在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地提高用户对数据库的管理效率。

从函数定义的角度出发，我们可以将函数分成内置函数和自定义函数。在 SQL 语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的；

本章讲解的函数均为内置函数；

下面我们介绍的如GROUP BY这种没有用括号括参数的不是函数，是子句！！！

不同的DBMS支持的函数是不同的，一般不通用，所以我们在使用函数的过程中一定要注意其对应的DBMS是什么；

MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。

简单的可以将内置函数分为两类：单行函数（也就是除了聚合函数的其他函数）、聚合函数（或分组函数）；

单行函数

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

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

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

更多单行函数使用方法见网站SQL 函数 (w3school.com.cn)

下面我们简单介绍一下常用的聚合函数，单行函数因为使用简单、种类较多故略过不讲

6.1 常用的聚合函数

（1）AVG()和SUM()函数

这两个函数分别用于求数值型数据的平均值和总和

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

（2）MAX()和MIN()函数

这两个函数用于求任意数据类型数据的最大值和最小值

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

（3）COUNT()函数

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

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

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

（4）GROUP BY子句

可以使用GROUP BY子句将表中的数据分成若干组，分组依据为GROUP BY后面跟的字段

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

假如想在分多组的基础上按照某个字段再次分组，则直接在GROUP BY后面增加字段即可

SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id ;

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

注意：

当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的

（5）HAVING子句

HAVING子句的作用与WHERE相同，都是过滤查询结果；

当过滤条件中存在聚合函数时，只能使用HAVING子句，并且HAVING子句只能放在GROUP BY后面；

当过滤条件中不存在聚合函数时，既可以用HAVING子句，也可以用WHERE子句；

HAVING子句通常都是和GROUP BY一起出现，最好不要单独使用HAVING子句，因为单独使用也没有意义（不使用GROUP BY则聚合函数的输出结果就一个，此时再过滤就根本没意义了）；

为什么WHERE和HAVING功能相同并且HAVING的适用范围比WHERE广，但是WHERE还没有被淘汰呢？这就涉及下面我们要介绍的WHERE的效率问题；

WHERE和HAVING的对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；

HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。

这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。

区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

小结如下：

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

开发中的选择：

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

7.SELECT执行过程

下面介绍两种完整的使用SELECT进行查询的结构

#方式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 ...,...

#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

SELECT进行查询时遵循以下的原则

关键字的顺序不能颠倒：

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

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

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

最后我们给出SELECT的执行原理

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

1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 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 语句的时候，还要注意相应的关键字顺序；所谓底层运行的原理，就是我们刚才讲到的执行顺序

8.*子查询

子查询也可以称为嵌套查询，类似于C++中的嵌套for循环；

大多数时候通过一次的查询并不能得到我们想要的结果，所以引入子查询（其实使用原始的单层查询也可以解决某些问题，但是使用子查询可以简化语句并且提高查询效率）；

例如我们想要知道公司内谁的工资比Abel高，这时候我们就需要先得出Abel的工资是多少，再拿其他员工的工资与其比较；

#方式1：多次查询
#这种方式明显效率很低，需要在客户端和服务器之间来回交换多次数据信息
SELECT salary
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;

#方式2：自连接
#这种方式不适用于所有的查询请求，相对的子查询是通用方式
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`

#方式3：子查询
#这个书写的方式就是根据方式1的第二个查询语句的条件，组合第一个查询语句得到
SELECT last_name,salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'
		);

我们从上面一个案例引出了子查询的知识点，关于子查询，我们需要注意

• 子查询/内查询要包含在括号内；
• 将子查询放在比较条件的右侧（书写规范，不是硬性要求）；
• 单行操作符对应单行子查询，多行操作符对应多行子查询；
• 当内查询查询到空值时，整个子查询不返回任何行；

8.1 子查询的分类

（1）根据内查询的返回结果条数可分为单行子查询和多行子查询

单行子查询的内查询仅返回一条结果

多行子查询额内查询返回至少两条结果

（2）根据内查询和外查询的查询结果是否相关，分为相关子查询和非相关子查询

简单来说非相关子查询就是内查询和外查询的查询结果毫不相关，比如内查询的结果是一个常数，外查询的查询结果是根据这个常数作比较得出；相关子查询反之，即内查询的结果是一个随着外查询条件变化而变化；

8.2 非相关子查询

（1）单行子查询

单行比较操作符对应单行子查询

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

（2）多行子查询

多行比较操作符对应多行子查询

操作符	含义
IN	等于内查询返回列表中的任意一个值
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

下面我们举几个例子熟悉一下多行比较操作符的使用方法

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

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

8.3 相关子查询

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

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

（1）EXISTS和NOT EXISTS

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

三、DDL&DCL&DML

前面我们简单介绍了关于数据表的查询操作，也是数据库中最重要的概念；

接下来我们会按照创建和管理表（数据定义语言）、对表进行增删改查操作（数据操作语言）、权限控制（数据控制语言）的顺序讲解SQL

1.数据存储流程

一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据

2.数据类型

类型	类型举例
整数类型	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

3.创建和管理数据库(DDL)

前面的课程基本上都是在听，没有实际动手操作，这之后开始就会开始使用Navicat来实际操作了

Navicat虽然是图形化界面，但是并不是完全傻瓜式的鼠标点击，还是需要使用SQL语句来进行某些高效率工作的；

3.1 创建数据库

• 方式1：创建数据库

CREATE DATABASE 数据库名; 

• 方式2：创建数据库并指定使用的字符集

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

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

CREATE DATABASE IF NOT EXISTS 数据库名; 

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

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

Navicat比较特殊的一点是创建数据库之后还需要编辑链接手动选择新建的链接才会显示在目录下

注意：

编辑链接不要选择确定（编辑器会认为你想新建一个链接，这会导致提示链接已存在），直接关闭窗口选择最近打开过的链接进行重新连接即可

3.2 管理数据库

（1）查看数据库

• 查看当前所有的数据库

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

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

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

这一步和切换数据库非常重要，否则直接操作大概率会报错

• 查看指定库下所有的表

SHOW TABLES FROM 数据库名;

• 查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G

（2）修改数据库

• 使用/切换数据库

USE 数据库名;

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

• 更改数据库字符集（尽量不要这么干，很容易导致已经创建好的表崩溃）

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

（3）删除数据库

• 方式1：删除指定的数据库

DROP DATABASE 数据库名;

• 方式2：删除指定的数据库（推荐）

DROP DATABASE IF EXISTS 数据库名;

4.创建和管理表(DDL)

4.1 创建表

（1）直接创建表

创建表首先需要明确字段类型（也就是每一列存储数据的类型），我们在第二小节已经简单介绍过一次了，之后还会详细介绍；

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

注意：

笔记中的[]括起来的字段表示可选参数，注意在实际书写SQL语句的时候一定要去除[]；

约束条件、默认值我们都还没有介绍，所以默认不写；

举例：

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

（2）基于现有表

CREATE TABLE emp1 
AS 
SELECT * 
FROM employees 
WHERE 1=2; -- 创建的emp1是空表

4.2 管理表

（1）查询数据表结构

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

语法格式如下：

SHOW CREATE TABLE 表名\G

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

（2）修改表

a)添加一个字段

• 格式

ALTER TABLE 表名 
ADD 字段名 字段类型 [FIRST|AFTER 字段名];

默认添加字段到表的最后一个字段的后面；

可以选择FIRST|AFTER指定添加字段到整个表的最前面或者指定字段的后面；

• 举例

ALTER TABLE dept80 
ADD job_id varchar(15) FIRST;

b)修改一个字段

修改字段的数据类型、长度

• 格式

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

• 举例

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

c)重命名一个字段

• 格式

ALTER TABLE 表名 
CHANGE 列名 新列名 新数据类型;

• 举例

ALTER TABLE  dept80
CHANGE department_name dept_name varchar(15); 

d)删除一个字段

• 格式

ALTER TABLE 表名 
DROP COLUMN 字段名

• 举例

ALTER TABLE  dept80
DROP COLUMN  job_id; 

（3）重命名表

• 方式一：使用RENAME

RENAME TABLE emp
TO myemp;

• 方式二：

ALTER table dept
RENAME [TO] detail_dept;  -- [TO]可以省略

• 必须是对象的拥有者

（4）删除表

• 格式

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

• 举例

DROP TABLE dept80;

注意：

• 当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除；
• 所有正在运行的相关事务被提交，所有相关索引被删除；
• 删除表没有撤销操作；

（5）清空表

• 格式

TRUNCATE TABLE 数据表;

• 与删除表直接将表结构和数据全部删除不同，清空表仅仅清空表中所有的数据，保留下表结构（字段名、字段类型）
• 删除表中所有的数据
• 释放表的存储空间
• 使用TRUNCATE不能撤销；

• 举例

TRUNCATE TABLE detail_dept;

5.DCL

COMMIT：提交数据，将操作永久保存，一旦执行COMMIT之后就无法将COMMIT之前的数据操作进行撤销；

ROLLBACK:可以执行一次撤销操作，撤销到最近的一次COMMIT之后；

6.数据的增删改(DML)

• DDL的操作一旦执行就不可回滚（它的自动COMMIT是不受任何语句控制的）；

• DML的操作默认情况下一旦执行也是不可回滚的，如果在执行DML之前执行了语句 SET autocommit = FALSE（仅对DML有效），则执行的DML操作可以实现回滚；

6.1 插入数据

要求：

（1）逐条添加数据

• 格式1

#为表的所有字段按默认顺序插入数据
INSERT INTO 表名
VALUES (value1,value2,....);

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

• 举例1

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

• 格式2

#为表的指定字段插入数据
INSERT INTO 表名(column1 [, column2, …, columnn]) 
VALUES (value1 [,value2, …, valuen]);

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

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

• 举例2

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

• 格式3

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]);

同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开

• 举例3

INSERT INTO emp(emp_id,emp_name)
VALUES (1001,'shkstart'),
	  (1002,'atguigu'),
	  (1003,'Tom');

一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中效率更高。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

（2）将查询结果插入到表中

INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行

• 格式

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

• 在 INSERT 语句中加入查询语句；
• 不必书写 VALUES 子句；
• 查询中的值列表应与 INSERT 子句中的列名对；

• 举例

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

注意：

我们可能需要注意一点就是添加的字段的长度不能比查询的字段的长度短，可能导致原有数据长度冲突（这个具体以后遇到再讨论）

6.2 更新数据

要求：

• 格式

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]		#通常会添加过滤条件

• 可以一次更新多条数据；

• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

• 使用 WHERE 子句指定需要更新的数据，如果省略 WHERE 子句，则表中的所有数据都将被更新；

举例：

UPDATE employees
SET    department_id = 70
WHERE  employee_id = 113;

注意：

修改数据时可能不成功的，这大概率是由于约束存在（比如我们想要设置department_id = 70，但是在部门表中约束条件要求department_id不能等于70，则这将导致报错）

6.3 删除数据

要求：

• 格式

DELETE FROM table_name 
[WHERE <condition>];  #一般需要加上条件，否则会导致删除表中所有数据

• 举例

DELETE FROM departments
WHERE  department_name = 'Finance';

四、约束

本章是表对象的最后一小节，这之后将讲解数据库中的其他对象；

1.约束概述

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
• 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”（限制范围）
• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门（即符合实际情况）
• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，用户规定“本部门经理的工资不得高于本部门职工的平均工资的5倍”

约束是表级的强制规定——即对表中字段的限制；

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

1.1 约束的分类

• 根据约束数据列的限制，约束可分为：

  • 单列约束：每个约束只约束一列（id不为空）
  • 多列约束：每个约束可约束多列数据（id和name都不为空）

• 根据约束的作用范围，约束可分为：

  • 列级约束：只能作用在一个列上，跟在列的定义后面
  • 表级约束：可以作用在多个列上，不与列一起，而是单独定义

• 根据约束的功能，约束可分为（这也是下面讲解的约束）：

  • NOT NULL 非空约束，规定某个字段不能为空

  • UNIQUE 唯一约束，规定某个字段在整个表中是唯一的

  • PRIMARY KEY 主键(非空且唯一)约束

  • FOREIGN KEY 外键约束

  • CHECK 检查约束

  • DEFAULT 默认值约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果

1.2 查看约束

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

2.非空约束

作用：限定某个字段/某列的值不允许为空

关键字：NOT NULL

特点：

• 默认所有的类型的字段都可以是NULL，包括INT、FLOAT等数据类型

• 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空

• 一个表可以有很多列都分别限定了非空

• 空字符串’’不等于NULL，0也不等于NULL

2.1 添加约束

• 格式1：建表时添加约束

CREATE TABLE 表名称(
	字段名  数据类型,
    字段名  数据类型 NOT NULL,  
    字段名  数据类型 NOT NULL
);

• 举例

CREATE TABLE student(
	sid int,
    sname varchar(20) not null,#根据实际需要添加not null
    tel char(11) ,
    cardid char(18) not null
);

• 格式2：建表后添加约束

alter table 表名称 
modify 字段名 数据类型 not null;

• 举例

ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;

2.2 删除约束

• 格式

alter table 表名称 
modify 字段名 数据类型 NULL;#使用null替换not null，相当于修改某个非注解字段，该字段允许为空
#或 
alter table 表名称 
modify 字段名 数据类型;     #去掉not null

• 举例

ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;

3.唯一性约束

作用：用来限制某个字段/某列的值不能重复

关键字：UNIQUE

特点：

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

3.1 添加约束

• 格式1：创建表时

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

字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的

• 举例

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

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

• 格式2：建表后

#方式1：
alter table 表名称 
add unique key(字段列表); 

#方式2：
alter table 表名称 
modify 字段名 字段类型 unique;

• 举例

ALTER TABLE USER 
ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER 
MODIFY NAME VARCHAR(20) UNIQUE;

3.2 查看约束

• 格式

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

3.3 删除约束

• 添加唯一性约束的列上也会自动创建唯一索引（之后会介绍）

• 删除唯一约束只能通过删除唯一索引的方式删除

• 删除时需要指定唯一索引名，唯一索引名和唯一约束名一样

  • 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同，也可以自定义唯一性约束名

• 格式

ALTER TABLE USER 
DROP INDEX uk_name_pwd;

4.主键约束

作用：用来唯一标识表中的一行记录（也就是确保一个表中不会有完全相同的两个对象）

关键字：primary key

特点：

• 键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值

• 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建

• 主键约束对应着表中的一列或者多列（复合主键）

• 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复

• MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用

• 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）,如果删除主键约束了，主键约束对应的索引就自动删除了

• 需要注意的一点是，不要修改主键字段的值，因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性

4.1 添加约束

• 格式1：建表时指定主键约束

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

• 举例

CREATE TABLE emp4(
id INT PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20)
);

CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

• 格式2：建表后添加主键约束

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

• 举例

ALTER TABLE student ADD PRIMARY KEY (sid);

ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);

4.2 删除约束

• 格式

alter table 表名称 drop primary key;

• 举例

ALTER TABLE student DROP PRIMARY KEY;

注意：

• 删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空性质仍然存在；
• 实际开发过程中千万不要删除主键约束——这将导致主键索引被删除，整个B+树会被破坏；

5.自增约束

作用：令某个字段的值自增

关键字：auto_increment

特点：

（1）一个表最多只能有一个自增长列

（2）当需要产生唯一标识符或顺序值时，可设置自增长

（3）自增长列约束的列必须是键列（主键列，唯一键列）

（4）自增约束的列的数据类型必须是整数类型

（5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

5.1 添加约束

• 格式1：建表时

create table 表名称(
	字段名  数据类型  primary key auto_increment,
    字段名  数据类型 ,  
    字段名  数据类型 ,
    字段名  数据类型 ,
);
create table 表名称(
	字段名  数据类型 
    字段名  数据类型 unique key auto_increment,  
    字段名  数据类型 
    primary key(主键字段名)
);

• 举例

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

• 格式2：建表后

alter table 表名称 
modify 字段名 数据类型 auto_increment;

• 举例

#建表
create table employee(
	eid int primary key ,
    ename varchar(20)
);
#添加约束
alter table employee modify eid int auto_increment;

5.2 删除约束

• 格式

#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

• 举例

alter table employee modify eid int;

6.外键约束

作用：限定某个表的某个字段的引用完整性（员工表的员工所在部门的选择，必须在部门表能找到对应的部分）

关键字：FOREIGN KEY

特点：

（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列

​ 为什么？因为被依赖/被参考的值必须是唯一的

（2）在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。

（3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表

（4）删表时，先删从表（或先删除外键约束），再删除主表

（5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据

（6）在“从表”中指定外键约束，并且一个表可以建立多个外键约束

（7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。

​ 例如：都是表示部门编号，都是int类型。

（8）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）

（9）删除外键约束后，必须手动删除对应的索引

补充：

• 主表（父表）：被引用的表，被参考的表

• 从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表

约束关系是针对双方的

• 添加了外键约束后，主表的修改和删除数据受约束

• 添加了外键约束后，从表的添加和修改数据受约束

• 在从表上建立外键，要求主表必须存在

• 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

6.1 添加约束

• 格式1：建表时

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

• 格式2：建表后

一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

• 举例

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

create table emp(
	eid int primary key,  #员工编号
    ename varchar(5),     #员工姓名
    deptid int				#员工所在的部门
);
#这两个表创建时，没有指定外键的话，那么创建顺序是随意

alter table emp add foreign key (deptid) references dept(did);

6.2 删除约束

• 格式

(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名

ALTER TABLE 从表名 DROP INDEX 索引名;

• 举例

mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';

mysql> alter table emp drop foreign key emp_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

7.默认约束

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

关键字：DEFAULT

7.1 添加默认值

• 格式1：建表时

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 '' #默认是空字符串
);

• 格式2：建表后

alter table 表名称 modify 字段名 数据类型 default 默认值;

#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

举例

create table employee(
	eid int primary key,
    ename varchar(20),
    gender char,
    tel char(11) not null
);

7.2 删除默认值

• 格式：

alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束

alter table 表名称 modify 字段名 数据类型  not null; #删除默认值约束，保留非空约束

• 举例

alter table employee modify gender char; #删除gender字段默认值约束，如果有非空约束，也一并删除
alter table employee modify tel char(11)  not null;#删除tel字段默认值约束，保留非空约束

五、视图

这里开始我们就要讲解其他数据库对象了，常见数据库对象如下

对象	描述
表(TABLE)	表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录
数据字典	就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看
约束(CONSTRAINT)	执行数据校验的规则，用于保证数据完整性的规则
视图(VIEW)	一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
索引(INDEX)	用于提高查询性能，相当于书的目录
存储过程(PROCEDURE)	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数(FUNCTION)	用于完成一次特定的计算，具有一个返回值
触发器(TRIGGER)	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

1.概述

Q：为什么要使用视图？

A：视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图（根据权限给出不同视图）；

• 视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，它是 SQL 中的一个重要概念；

• 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表；

• 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之修改表也会影响视图；

• 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句

  • 在数据库中，视图不会保存数据，数据真正保存在数据表中；

• 视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便；

2.创建视图

2.1 单表视图

• 格式1：

#在 CREATE VIEW 语句中嵌入子查询
CREATE [OR REPLACE] 
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

• 格式2：

CREATE VIEW 视图名称 
AS 查询语句

• 举例

CREATE VIEW empvu80
AS 
SELECT  employee_id, last_name, salary
FROM    employees
WHERE   department_id = 80;

CREATE VIEW emp_year_salary (ename,year_salary)
AS 
SELECT ename,salary*12*(1+IFNULL(commission_pct,0))
FROM t_employee;

CREATE VIEW salvu50
AS 
SELECT  employee_id ID_NUMBER, last_name NAME,salary*12 ANN_SALARY
FROM    employees
WHERE   department_id = 50;

说明1：实际上视图就是我们在 SQL 查询语句的基础上封装了视图 VIEW，这样就会基于 SQL 语句的结果集形成一张虚拟表。

说明2：在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。

2.2 多表联合视图

• 举例

CREATE VIEW empview 
AS 
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

CREATE VIEW	dept_sum_vu
(name, minsal, maxsal, avgsal)
AS 
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id 
GROUP BY  d.department_name;

2.3 基于视图的视图

当我们创建好一张视图之后，还可以在它的基础上继续创建视图

举例：联合“emp_dept”视图和“emp_year_salary”视图查询员工姓名、部门名称、年薪信息创建 “emp_dept_ysalary”视图

CREATE VIEW emp_dept_ysalary
AS 
SELECT emp_dept.ename,dname,year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;

3.查看视图

语法1：查看数据库的表对象、视图对象

SHOW TABLES;

语法2：查看视图的结构

DESC / DESCRIBE 视图名称;

语法3：查看视图的属性信息

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G

执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。

语法4：查看视图的详细定义信息

SHOW CREATE VIEW 视图名称;

4.更新视图

4.1 可更新情况

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然

4.2 不可更新情况

要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系。另外当视图定义出现如下情况时，视图不支持更新操作：

• 在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
• 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
• 在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持INSERT和DELETE操作；
• 在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
• 在定义视图的SELECT语句后的字段列表中使用DISTINCT、聚合函数、GROUP BY、HAVING、UNION等，视图将不支持INSERT、UPDATE、DELETE；
• 在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE；
• 视图定义基于一个不可更新视图；
• 常量视图；

在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持更新操作;

虽然可以更新视图数据，但总的来说，视图作为虚拟表，主要用于方便查询，不建议更新视图的数据，对视图数据的更改，都是通过对实际数据表里数据的操作来完成的；

5.删除视图

• 删除视图只是删除视图的定义，并不会删除基表的数据。

• 删除视图的语法是：

  DROP VIEW IF EXISTS 视图名称;

  DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;

• 举例：

  DROP VIEW empvu80;

  说明：基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败，这样的视图c需要手动删除或修改，否则影响使用；

6.总结

6.1 视图的优点

1. 操作简单

将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。

2. 减少数据冗余

视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

3. 数据安全

MySQL将用户对数据的访问限制在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表，同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。

4. 适应灵活多变的需求
   当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。

5. 能够分解复杂的查询逻辑
   数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

6.2 视图的不足

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。

实际项目中，如果视图过多，会导致数据库维护成本的问题。

所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视图，使系统整体达到最优。

六、存储过程和存储函数

存储过程和函数能够将复杂的SQL逻辑封装在一起，应用程序无须关注存储过程和函数内部复杂的SQL逻辑，而只需要简单地调用存储过程和函数即可；

简单来说存储函数就是用户自定义的函数，用于封装复杂的SQL逻辑；

存储函数一定有返回值（因变量），存储过程可以没有返回值；

1.存储过程

1.1 存储过程简介

• 存储过程的英文是 Stored Procedure。它的思想很简单，就是一组经过预先编译的 SQL 语句的封装（这里与存储函数的思想非常类似）；
• 执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行；
• 一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的；

优点：

1、简化操作，提高了sql语句的重用性，减少了开发程序员的压力
2、减少操作过程中的失误，提高效率
3、减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
4、减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

1.2 存储过程的分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点存储过程可以分类如下：

1、没有参数（无参数无返回）
2、仅仅带 IN 类型（有参数无返回）
3、仅仅带 OUT 类型（无参数有返回）
4、既带 IN 又带 OUT（有参数有返回）
5、带 INOUT（有参数有返回）

注意：IN、OUT、INOUT 都可以在一个存储过程中存在多个

1.3 创建存储过程

• 格式

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体
END

1、参数前面的符号的意思

• IN：当前参数为输入参数，也就是表示入参；

  存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。

• OUT：当前参数为输出参数，也就是表示出参；

  执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。

• INOUT：当前参数既可以为输入参数，也可以为输出参数。

2、形参类型可以是 MySQL数据库中的任意类型。

3、characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
• [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。
  • CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
  • NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
  • READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
  • MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
  • 默认情况下，系统会指定为CONTAINS SQL。
• SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
  • DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
  • INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
  • 如果没有设置相关的值，则MySQL默认指定值为DEFINER。
• COMMENT 'string'：注释信息，可以用来描述存储过程。

4、存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。

1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

5、需要设置新的结束标记

DELIMITER 新的结束标记

因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

比如：“DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。

如果使用的是Navicat 工具，那么在编写存储过程的时候，Navicat 会自动设置 DELIMITER 为其他符号，我们不需要再进行 DELIMITER 的操作

1.4 调用存储过程

存储过程有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname

1、调用in模式的参数：

CALL sp1('值');

2、调用out模式的参数：

SET @name;
CALL sp1(@name);
SELECT @name;

3、调用inout模式的参数：

SET @name=值;
CALL sp1(@name);
SELECT @name;

2.存储函数

2.1 创建存储函数

• 格式

CREATE FUNCTION 函数名(参数名 参数类型,...) 
RETURNS 返回值类型
[characteristics ...]
BEGIN
	函数体   #函数体中肯定有 RETURN 语句
END

1、参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数；

2、RETURNS type 语句表示函数返回数据的类型；

RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句；

3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述；

4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END；

注意：若在创建存储函数中报错“you might want to use the less safe log_bin_trust_function_creators variable”，有两种处理方法：

• 方式1：加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”

• 方式2：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

4.4 对比存储函数和存储过程

2.2 调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

• 格式

SELECT 函数名(实参列表)

3.存储过程和存储函数

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	理解为有0个或多个	一般用于更新
存储函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时

• 存储函数可以放在查询语句中使用，存储过程不行；

• 存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的；

3.1 存储过程/函数的查删改

（1）查看

Q：创建完之后，怎么知道我们创建的存储过程、存储函数是否成功了呢？

A：MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看，也可直接从系统的information_schema数据库中查询。这里介绍3种方法。

1. 使用SHOW CREATE语句查看存储过程和函数的创建信息

基本语法结构如下：

SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

举例：

SHOW CREATE FUNCTION test_db.CountProc \G

2. 使用SHOW STATUS语句查看存储过程和函数的状态信息

基本语法结构如下：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期

[LIKE ‘pattern’]：匹配存储过程或函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程或函数的信息

举例

SHOW PROCEDURE STATUS LIKE 'SELECT%' \G 

3. 从information_schema.Routines表中查看存储过程和函数的信息

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

SELECT * 
FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' 
[AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

说明：如果在MySQL数据库中存在存储过程和函数名称相同的情况，最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。

举例：从Routines表中查询名称为CountProc的存储函数的信息，代码如下：

SELECT * 
FROM information_schema.Routines
WHERE ROUTINE_NAME='count_by_id'　
AND　ROUTINE_TYPE = 'FUNCTION' \G

（2）修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

• CONTAINS SQL，表示子程序包含SQL语句，但不包含读或写数据的语句。
• NO SQL，表示子程序中不包含SQL语句。
• READS SQL DATA，表示子程序中包含读数据的语句。
• MODIFIES SQL DATA，表示子程序中包含写数据的语句。
• SQL SECURITY { DEFINER | INVOKER }，指明谁有权限来执行。
  • DEFINER，表示只有定义者自己才能够执行。
  • INVOKER，表示调用者可以执行。
• COMMENT 'string'，表示注释信息。

修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。但是，这两个语句的结构是一样的，语句中的所有参数也是一样的。

（3）删除

删除存储过程和函数，可以使用DROP语句，其语法结构如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

IF EXISTS：如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。

举例：

DROP PROCEDURE CountProc;

DROP FUNCTION CountProc;

4.总结

优点：

1、存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。

2、可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。

3、存储过程的安全性强。我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。

4、可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。

5、良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

缺点：

1、可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。

2、调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。

3、存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。

4、不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

七、变量、流程控制与游标

1.变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据；

在 MySQL 数据库中，变量分为系统变量以及用户自定义变量；

1.1 系统变量

变量由系统定义，不是用户定义，属于服务器层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件（例如my.ini等）中的参数值；

系统变量分为全局系统变量（需要添加global 关键字）以及会话系统变量（需要添加 session 关键字），有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量；

静态变量（在 MySQL 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量；

• 每一个MySQL客户机成功连接MySQL服务器后，都会产生与之对应的会话。会话期间，MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制；
• 全局系统变量针对于所有会话（连接）有效，但不能跨重启；
• 会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值；
• 会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改；

• MySQL中有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；

• 有些系统变量作用域既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；

• 有些系统变量的作用域只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID；

（1）查看系统变量

#查看所有全局变量
SHOW GLOBAL VARIABLES;

#查看所有会话变量
SHOW SESSION VARIABLES;
或
SHOW VARIABLES;

#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';

#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';

作为 MySQL 编码规范，MySQL 中的系统变量以两个“@”开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量；

#查看指定的系统变量的值
SELECT @@global.变量名;

#查看指定的会话变量的值
SELECT @@session.变量名;

SELECT @@变量名;

（2）修改系统变量

有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、特征；

方式1：修改MySQL配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）；

方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

#为某个系统变量赋值
#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;

#为某个会话变量赋值
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;

1.2 用户变量

用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以一个“@”开头。根据作用范围不同，又分为会话用户变量和局部变量

• 会话用户变量：作用域和会话变量一样，只对当前连接会话有效；

• 局部变量：只在 BEGIN 和 END 语句块中有效，局部变量只能在存储过程和函数中使用；

（1）定义用户变量

定义会话用户变量

#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;

#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量  [FROM 等子句];

定义局部变量

#定义：可以使用`DECLARE`语句定义一个局部变量

#作用域：仅仅在定义它的 BEGIN ... END 中有效

#位置：只能放在 BEGIN ... END 中，而且只能放在第一句

BEGIN
	#声明局部变量
	DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
	DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];

	#为局部变量赋值
	SET 变量名1 = 值;
	SELECT 值 INTO 变量名2 [FROM 子句];

	#查看局部变量的值
	SELECT 变量1,变量2,变量3;
END

（2）查看用户变量

查看会话用户变量的值

SELECT @用户变量

SELECT 局部变量名;

（3）总结

			  作用域					定义位置				  语法
会话用户变量	  当前会话				   会话的任何地方				加@符号，不用指定类型
局部变量	   定义它的BEGIN END中 		BEGIN END的第一句话		  一般不用加@,需要指定类型

2.定义条件与处理程序

定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行（我们可以简单地认为就是异常处理函数，定义条件=捕获异常，处理程序=处理异常）

说明：定义条件和处理程序在存储过程、存储函数中都是支持的。

2.1 定义条件

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

定义条件使用DECLARE语句，语法格式如下：

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

错误码的说明：

• MySQL_error_code和sqlstate_value都可以表示MySQL的错误。
  • MySQL_error_code是数值类型错误代码。
  • sqlstate_value是长度为5的字符串类型错误代码。
• 例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，’HY000’是sqlstate_value。
• 例如，在ERROR 1142（42000）中，1142是MySQL_error_code，’42000’是sqlstate_value。

举例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。

#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

举例2：定义”ERROR 1148(42000)”错误，名称为command_not_allowed。

#使用MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;

#使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';

2.2 定义处理程序

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时，使用DECLARE语句的语法如下：

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

• 处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。
  • CONTINUE：表示遇到错误不处理，继续执行。
  • EXIT：表示遇到错误马上退出。
  • UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
• 错误类型（即条件）可以有如下取值：
  • SQLSTATE '字符串错误码'：表示长度为5的sqlstate_value类型的错误代码；
  • MySQL_error_code：匹配数值类型错误代码；
  • 错误名称：表示DECLARE … CONDITION定义的错误条件名称。
  • SQLWARNING：匹配所有以01开头的SQLSTATE错误代码；
  • NOT FOUND：匹配所有以02开头的SQLSTATE错误代码；
  • SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
• 处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“SET 变量 = 值”这样的简单语句，也可以是使用BEGIN ... END编写的复合语句。

定义处理程序的几种方式，代码如下：

#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';

#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

3.流程控制

解决复杂问题不可能通过一个 SQL 语句完成，我们需要执行多个 SQL 操作；

流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序，是我们完成复杂操作必不可少的一部分；

只要是执行的程序，流程就分为三大类（这与C++或者Python的流程控制是一样的）：

• 顺序结构：程序从上往下依次执行
• 分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
• 循环结构：程序满足一定条件下，重复执行一组语句

针对于MySQL 的流程控制语句主要有 3 类：

注意：只能用于存储程序；

• 条件判断语句：IF 语句和 CASE 语句
• 循环语句：LOOP、WHILE 和 REPEAT 语句
• 跳转语句：ITERATE 和 LEAVE 语句

3.1 IF语句

• 格式：

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF

根据表达式的结果为TRUE或FALSE执行相应的语句。这里“[]”中的内容是可选的；

3.2 CASE语句

• 格式

#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

#情况二：类似于多重if
CASE 
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

3.3 LOOP语句

LOOP循环语句用来重复执行某些语句。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程；

• 格式

[loop_label:] LOOP
	循环执行的语句
END LOOP [loop_label]

loop_label表示LOOP语句的标注名称，该参数可以省略

3.4 WHILE语句

WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环；

[while_label:] WHILE 循环条件  DO
	循环体
END WHILE [while_label];

hile_label为WHILE语句的标注名称；如果循环条件结果为真，WHILE语句内的语句或语句群被执行，直至循环条件为假，退出循环

3.5 REPEAT语句

REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止

[repeat_label:] REPEAT
　　　　循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句或语句群被重复，直至expr_condition为真

总结：

• 这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称
• LOOP：一般用于实现简单的”死”循环
• WHILE：先判断后执行
• REPEAT：先执行后判断，无条件至少执行一次

3.6 LEAVE语句

可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break

LEAVE 标记名

label参数表示循环的标志。LEAVE和BEGIN … END或循环一起被使用

3.7 ITERATE语句

只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue

ITERATE label

label参数表示循环的标志。ITERATE语句必须跟在循环标志前面

4.游标

我们可以通过筛选条件 WHERE 和 HAVING，或者是限定返回记录的关键字 LIMIT 返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录，并对记录的数据进行处理——这个时候，就可以用到游标。游标提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构；

游标让 SQL 这种面向集合的语言有了面向过程开发的能力；

SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作；

MySQL中游标可以在存储过程和函数中使用；

4.1 使用游标

游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明

（1）声明游标

在MySQL中，使用DECLARE关键字来声明游标

DECLARE cursor_name CURSOR FOR select_statement; 

• 举例

DECLARE cur_emp CURSOR FOR 
SELECT employee_id,salary FROM employees;

（2）打开游标

当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备

OPEN cursor_name

（3）使用游标

这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可

FETCH cursor_name INTO var_name [, var_name] ...

注意：

• var_name必须在声明游标之前就定义好

• 游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误

（4）关闭游标

有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标的操作，会释放游标占用的系统资源。

关闭游标之后，我们就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标。

CLOSE cursor_name

4.2 总结

游标是 MySQL 的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。

但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。

建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

八、触发器

在实际开发中，我们经常会遇到这样的情况：有 2 个或者多个相互关联的表，如商品信息和库存信息分别存放在 2 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。这样一来，我们就必须把这两个关联的操作步骤写到程序里面，而且要用事务（事物具体我们会在后面介绍）包裹起来，确保这两个操作成为一个原子操作，要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手动维护，这样就很容易忘记其中的一步，导致数据缺失。

你可以创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了 —— 触发器就是类似于网页设计中的事件和绑定的事件处理函数机制，它将触发事件和事件处理函数集合在一起进行定义；

1.触发器概述

触发器工作机制是由事件来触发程序某个操作，这些事件包括INSERT、UPDATE、DELETE事件。

所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发程序执行相应的操作。

当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现

2.创建触发器

• 格式

CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 
触发器执行的语句块;

说明：

• 表名：表示触发器监控的对象。

• BEFORE|AFTER：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。

• INSERT|UPDATE|DELETE：表示触发的事件。

• INSERT 表示插入记录时触发；

• UPDATE 表示更新记录时触发；

• DELETE 表示删除记录时触发。

• 触发器执行的语句块：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。

3.查看触发器

查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等

方式1：查看当前数据库的所有触发器的定义

SHOW TRIGGERS\G

方式2：查看当前数据库中某个触发器的定义

SHOW CREATE TRIGGER 触发器名

方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。

SELECT * FROM information_schema.TRIGGERS;

4.删除触发器

触发器也是数据库对象，删除触发器也用DROP语句

DROP TRIGGER  IF EXISTS 触发器名称;

5.总结

优点：

1、触发器可以确保数据的完整性；

2、触发器可以帮助我们记录操作日志；

利用触发器，可以具体记录什么时间发生了什么。比如，记录修改会员储值金额的触发器，就是一个很好的例子。这对我们还原操作执行时的具体场景，更好地定位问题原因很有帮助

3、触发器还可以用在操作数据前，对数据进行合法性检查；

比如，超市进货的时候，需要库管录入进货价格。但是，人为操作很容易犯错误，比如说在录入数量的时候，把条形码扫进去了；录入金额的时候，看串了行，录入的价格远超售价，导致账面上的巨亏……这些都可以通过触发器，在实际插入或者更新操作之前，对相应的数据进行检查，及时提示错误，防止错误数据进入系统

缺点：

1、触发器最大的一个问题就是可读性差；

因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能不受应用层的控制。这对系统维护是非常有挑战的

2、相关数据的变更，可能会导致触发器出错;

特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率