MySQL基础及原理

MySQL

• 写在前面
• 一、 数据库概述
• • 为什么要使用数据库？
  • 为什么要使用MySQL？
  • Oracle和MySQL对比
  • RDBMS与非RDBMS
  • 关系型数据库设计规则
  • ORM思想
  • 表、记录、字段
  • 表的四种关联关系
• 二、SQL概述
• • SQL概述
  • SQL分类
  • 注释
  • 命名规则
  • 导入已有的数据表
  • SQL语句执行顺序
• 三、SQL规则与规范
• • SQL规则
  • SQL规范
• 四、SELECT基础查询语句
• • SELECT简单查询
  • 字段的别名
  • 数据去重
  • 空值null参与运算
  • 着重号``
  • 常数查询
  • 模糊查询
  • 显示字段详情（表结构）
  • 条件查询（过滤数据）
• 五、排序与分页
• • 单列排序(一条数据排序)
  • 多列排序(多条数据排序)
  • 分页
• 六、多表查询（关联查询）
• • 多表查询(关联查询)
  • 等值连接、非等值连接
  • 自连接、非自连接
  • 内连接、外连接
  • 外连接分类
  • • 左外连接
    • 右外连接
    • 满外连接
• 七、SQL99实现多表查询
• • SQL92、SQL99区别
  • SQL92实现外连接
  • SQL99实现多表查询
  • 内连接
  • 外连接
  • • 左外连接
    • 右外连接
    • 满外连接
  • 自然连接（新特性）
  • USING连接（新特性）
  • 多表连接约束条件总结
  • 笛卡尔积(交叉连接)错误
• 八、UNION关键字的使用
• • 合并查询结果
• 九、UNION实现7种多表查询
• • 内连接
  • 左外连接
  • 右外连接
  • 过滤右表
  • 过滤左表
  • 满外连接
  • 过滤交集
• 十、函数
• • 为什么需要函数？
  • 函数分类
  • 单行函数
  • • 数值函数
    • • 基本函数
      • 三角函数
      • 角度与弧度互换函数
      • 指数与对数
      • 进制间的转换
    • 字符串函数
    • 日期和时间函数
    • • 获取日期、时间
      • 日期与时间戳的转换
      • 获取月份、星期、星期数、天数等函数
      • 日期的操作函数
      • 时间和秒钟转换的函数
      • 计算日期和时间的函数
      • 日期的格式化与解析
    • 流程控制函数
    • 加密与解密函数
    • MySQL信息函数
    • 其他函数
  • 聚合函数（多行函数）
  • • AVG和SUM函数
    • MIN和MAX函数
    • COUNT函数
    • GROUP BY分组数据
    • HAVING过滤分组数据
    • HAVING和WHERE对比
• 十一、SQL执行顺序
• • SELECT语句的完整结构和执行顺序
• 十二、子查询
• • 子查询的基本使用
  • 子查询的分类
  • 单行子查询
  • • 单行比较操作符
  • 多行子查询
  • • 多行比较操作符
    • 相关（关联）子查询
    • EXISTS 与 NOT EXISTS关键字
• 十三、数据库的创建和管理
• • 创建数据库
  • 查看数据库
  • 修改、删除数据库
• 十四、表的创建与管理
• • 创建表
  • 查看表结构
  • 修改表
  • 重命名表
  • 删除表
  • 清空表
  • DCL中COMMIT和ROLLBACK
  • TRUNCATE TABLE 比 DELETE FROM对比
  • DDL和DML的回滚说明
  • MySQL8新特性—DDL的原子化
• 十五、数据的增、删、改
• • 插入数据
  • 更新数据
  • 删除数据
  • 计算列
• 十五、运算符
• • 算术运算符
  • 符号比较运算符
  • 非符号比较运算符
  • 逻辑运算符
  • 运算符优先级
  • 位运算符
• 十六、MySQL数据类型
• • 整数类型
  • • 可选属性M、ZEROFILL
    • 可选属性UNSIGNED无符号类型
    • 使用场景
    • 如何选择
  • 浮点类型
  • 定点数类型
  • 位类型
  • 日期时间类型
  • • YEAR类型
    • DATE类型
    • TIME类型
    • DATETIME类型
    • TIMESTAMP类型
    • TIMESTAMP和DATETIME的区别
    • 开发建议
  • 文本字符串类型
  • • CHAR与VARCHAR类型
    • CHAR和VARCHAR如何选择？
  • TEXT类型
  • ENUM类型
  • SET类型
  • 二进制字符串类型
  • • BINARY与VARBINARY类型
    • BLOB类型
    • TEXT和BLOB的使用注意事项
  • JSON类型
  • 空间类型
  • 数据类型使用总结
• 十七、约束
• • 约束概述
  • • 为什么需要约束？
    • 什么是约束？
    • 如何添加约束？
    • 约束分类
    • 查看某个表已有的约束
  • 非空约束
  • 唯一性约束
  • • 复合唯一约束
    • 删除唯一约束
  • 主键约束
  • • 添加主键约束
    • 复合主键
    • 删除主键约束
  • 自增约束
  • • 指定自增约束
    • 删除自增约束
    • MySQL 8.0新特性—自增变量的持久化
  • 外键约束
  • • 添加外键约束
    • 删除外键约束
    • 阿里开发规范
  • 约束等级
  • CHECK约束检查
  • DEFALUT默认值约束
  • • 添加默认值约束
    • 删除默认值约束
    • 关于默认值的面试题
  • 关于约束的开发建议
• 十八、视图
• • 常用的数据库对象
  • 什么是视图
  • 视图的创建
  • 视图别名
  • 创建多表联合视图
  • 利用视图对数据进行格式化
  • 查看视图
  • 操作视图的数据
  • 删除、修改视图
  • • 修改视图
    • 删除视图
  • 视图的优点
  • 视图的缺点
• 十九、函数与存储过程
• • 储存过程
  • • 什么是储存过程
    • 储存过程参数分类
    • 创建储存过程
    • 存储过程的调用
    • 存储过程的调试
  • 存储函数
  • • 创建函数
    • 函数调用
    • 函数和存储过程对比
  • 存储过程和函数的查看、修改、删除
  • • 查看
    • 修改
    • 删除
    • 关于存储过程和函数的使用争议
• 二十、变量
• • 系统变量
  • • 查看、修改系统变量
    • MySQL 8.0的新特性—全局变量的持久化
  • 用户变量
  • • 会话用户变量
    • 局部变量
    • 会话用户变量与局部变量对比
• 二十一、定义条件与处理程序
• • 定义条件
  • 定义处理程序
• 二十二、流程控制
• • 分支结构之 IF
  • 分支结构之 CASE
  • 循环结构之LOOP
  • 循环结构之WHILE
  • 循环结构之REPEAT
  • 跳转语句之LEAVE语句
  • 跳转语句之ITERATE语句
  • 三种循环结构对比
• 二十二、游标
• • 什么是游标
  • 游标的使用
  • 游标小结
• 二十三、触发器
• • 为什么需要触发器
  • 触发器概述
  • 创建触发器
  • 查看、删除触发器
  • • 查看触发器
    • 删除触发器
  • 触发器的优缺点
  • • 优点
    • 缺点
  • 注意点
• 二十四、 MySQL其他新特性
• • MySQL新特性
  • MySQL 8.0 移除的旧特性
  • 新特性1、窗口函数
  • • 序号函数
    • 分布函数
    • 前后函数
    • 首尾函数
    • 其他函数
  • 新特性2、共用表表达式
  • • 普通公用表表达式
    • 递归公用表表达式
• 二十五、关键字

写在前面

• 文章内容比较详细，大多常见问题和解决方案都会列举出来(尤其SQL语句部分)，建议仔细阅读！
• 若因文章排版导致阅读性差，望能评论反馈。收到后会第一时间修改排版风格。
• 文章的排序是按重要性故意调换顺序，并非排序混乱。

一、 数据库概述

为什么要使用数据库？

• 可持久化(persistence)：没有数据库的时候都是将数据保存在可掉电式存储设备中。即将数据以0、1的形式保存在内存中，一旦断电，数据就会丢失。
• 即持久化就是将内存中的数据保存到硬盘中加以“固化”，而该过程是通过各种关系型数据库实现的。
• 持久化的作用：将内存中的数据保存在关系型数据库、磁盘文件、XML数据文件中，即使断电数据也不会丢失，实现数据永久储存。
• 数据库存储数据量大，一张表就可以存储几千上万条数据。
• 查询速度快。

为什么要使用MySQL？

• MySQL式一个“开源的关系型数据库管理系统”，因为开源代码，所以使用成本低。
• 性能好、服务稳定。
• 软件体积小、使用简单、易于维护。
• 社区用户活跃、便于解决问题。
• 很多互联网大厂在用，更具可靠性。

Oracle和MySQL对比

• 为什么要对比Oracle和MySQL呢？ 因为Oracle常年具榜首，而MySQL常年居榜二，虽然现在都是Oracle公司旗下的。
• Oracle性能更好，安全性更高，同时也是收费的，适用于跨国大企业。
• MySQL体积小、速度快、成本低、尤其开源这一点，使得很多互联网公司、中小型网站都选择了MySQL作为网站数据库（Facebook、Twitter、YouTube、阿里巴巴、腾讯、美团、蚂蚁金服）。

RDBMS与非RDBMS

• RDBMS即关系型数据库管理系统（Oracle、MySQL、Microsoft SQL Server、IMB Db2、Access）
• 复杂查询： 可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
• 事务支持： 可以对安全性能很高的数据进行访问。
• 非RDBMS即非关系型数据库管理系统，也称NoSQL（MongoDB、Redis）
• 相对于RDBMS性能更好、查询速度更快。
• 键值型：用作内存缓存适用，如Redis。
• 文档型：是特殊的键值型数据库，其值为文档，如MongoDB。
• 搜索引擎：是应用在搜索引擎领域的数据储存形式，由于搜索数据会爬取大量的数据，并以特定的格式进行储存，这样在索引的时候才能保证性能最优。核心原理是”倒排索引“。如：ES、Solr
• 列式数据库：每一列是一条数据，可以只读取这条数据的几个字段，节省内存。如：HBase
• 关系型数据库都是行式数据库，每一行是一条数据。每读取一条数据，就会读取这条数据的所有字段，不能读取部分字段，若我只需要这条数据的某几个字段就会造成内存浪费。
• 图形数据库：利用图这种数据结构存储了实体（对象）之间的关系，能高效地解决复杂的关系问题，如社交网络中人与人的关系。如：InfoGird

关系型数据库设计规则

• 关系型数据库的典型数据结构就是数据表，这些表的组成都是结构化的（structured）。
• 一个数据库有很多张表，一个表有很多条数据，每个表都有自己的名字来标识自己的唯一性。

ORM思想

ORM思想（Object Relational Mapping）：对象关系映射，即表与面向对象语言的一一对应关系。 数据库中一个表 <-----> 一个类 表中一条数据 <-----> 一个对象（或实体） 表中的每一列 <-----> 一个属性、字段（filed）

表、记录、字段

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

表的四种关联关系

• 一对一关联：
• 即表A中的一条数据和表B中的一条数据是一一对应的。即两张表是同一个人的数据。
• 但这种在实际开发中用的不多，因为一对一的可以创建成一张表。
• 但如果某一条数据的字段太多，可以将常用字段和不常用字段拆分为两张表，后续在讲。
• 一对多关联：
• 即表A中的一条数据对应表B中的多条数据。如：部门表和员工表，部门表中的一个部门对应员工表中的多个员工。
• 多对多关联：
• 要表示多对多的关系就必须创建第三个表，该表通常称为联接表也称中间表，它将多对多的关系分为两个一对多的关系。将这两个表的主键都插入到第三个表中。
• 如学生选课，一个学生可以选择多门课程，而一门课程也对应多个学生。
• 如下图，中间表两边的表都存在一对多的关系。

• 自我引用： 即自己引用自己，如你的学号，老师可以通过学号查到你的身份，而学号就是你的一个引用。可以理解为引用地址吗？

二、SQL概述

SQL概述

• SQL（Structured Query Language，结构化查询语言）是使用关系模型的数据库应用语言，用于访问和操作数据库

SQL分类

• DDL（Data Definition Languages，数据定义语言）
• 定义不同数据库、表、视图、索引等数据库。
• 创建、删除、修改数据库和表的结构。
• CREATE：创建
• DROP：删除
• ALTER：修改
• RENAME：重命名
• TRUNCATE：清空
• DML（Data Manipulation Language，数据操作语言）
• 用于添加、删除、更新、查询数据库记录。
• 检查数据完整性。
• INSERT：新增
• DELETE：删除
• UPDATE：修改
• SELECT：查询
• 用于操作数据
• DCL（Data Control Language，数据控制语言）
• 定义数据库、表、字段、用户的访问权限和安全级别。
• GRANT：赋予权限
• REVOKE：删除权限
• COMMIT：提交
• ROLLBACK：回滚
• SAVEPOINT：恢复
• 用于在DML操作完数据后，对数据进行提交到数据库服务器，或回滚，赋予权限等操作

因为查询语句SELECT的使用非常频繁，所有有很多人将查询语句抽取出来作为一类：

• DQL（数据查询语言）

还有人将COMMIT、ROLLBACK取出来称为：

• TCL（事务控制语言）

注释

• 单行注释：# 注释文字
• 单行注释：-- 注释文字 – （-- 后边必须有空格）
• 多行注释：/* 注释文字 */

命名规则

• 数据库名、表名、字段名，中间不能有空格
• 数据库名、表名不得超过30个字符，变量名不超过29个字符。
• 命名只能包含大小写字母、数字、_下划线。
• 保证字段名和类型的一致性。 复合词用_分割，不能用驼峰命名
• 当字段名和MySQL保留字相同时，使用着重号包裹 着重号``

导入已有的数据表

• 在命令行中执行指令导入：soucer + 绝对路径（注意：路径不需要引号）
• 使用图形化工具导入

SQL语句执行顺序

SELECT 字段 From 表 WHERE 条件 其他子句；

• 先执行FROM语句，查询表；
• 再执行WHERE语句，按条件过滤字段；
• 再执行SELECT语句，取出需要的字段；
• 最后执行其他子句，按条件返回数据。

三、SQL规则与规范

规则与规范区别：规则是必须遵守的，规范可以不用遵守。

SQL规则

• SQL语句可以写成一行或多行。为了提高可读性，各子句分行写，必要时缩进。
• 每条指令以；或\g或\G结束。
• 字符串型和日期时间类型的数据可以使用单引号''表示。
• 列的别名，尽量使用双引号""表示，不建议省略as。

SQL规范

• MySQL在Windows环境下不区分大小写： 但字符串是遵守大小写的。
• MySQL在Linux环境下区分大小写:
• 数据库名、表名、表别名、变量名，严格区分大小写。
• 关键字、函数名、列名（字段名）、列的别名（字段的别名），不区分大小写。
• 推荐使用统一的书写规范：
• 数据库名、表名、表别名、字段名、字段别名，都小写。
• SQL关键字、函数名、绑定变量，都大写。

四、SELECT基础查询语句

SELECT简单查询

• SELECT 字段1，字段2... FROM 表名：查询指定字段。
• SELECT * FROM 表名：查询所有字段，* 代表所有字段（所有列）。
• SELECT 字段 FROM DUAL：从MySQL内置的公共表中输出结果集 DUAL关键字用于访问sys用户下的一张公共虚拟表（非实际存在的）。FROM DUAL也是可以省的，可省为SELECT 字段

字段的别名

• SELECT 字段 别名 FROM 表名 以空格分隔，即给字段重命名展示在结果集中。
• SELECT 字段 as 别名 FROM 表名 以as分隔，即给字段重命名展示在结果集中。
• SELECT 字段 "别名" FROM 表名 以""双引号包裹别名。双引号中可以为中文。
• 结果集：SELECT查询后的所有结果的集合。
• as：是alias（别名）的缩写。
• 别名只能在WHERE语句执行之前使用。详见: SQL语句执行顺序

数据去重

SELECT DISTINCT 字段1 FROM 表名

• 将字段值重复的数据合并为一条。
• 常用于查询单条数据。

空值null参与运算

• 空值null != 0，可意为未知的、不存在的。
• 任何数据与null作运算，结果为null。
• 解决方案： IFNULL(字段，0) 意为若字段存在则使用字段，若不存在则用0，0也可以时其他自定义数据。 也就是用自定义数据替换null。

着重号``

作用：

• 当字段名和MySQL保留字相同时，用着重号包裹即可。

常数查询

• SELECT '常数'，字段 FROM 表名 常数可以是字符串，可以是数字（数字不需要单引号）
• 作用：一个常数添加一列字段与值同名的数据 eg: SELECT '尚硅谷',123,employee_id,last_name FROM employess;

模糊查询

• SELECT 字段 FROM 表 WHERE 字段 LIKE '模糊匹配字符'

通配符：

• %：代表任何长度的字符串，字符串的长度可以为 0。 LIKE 'T%'：查询以T开头的数据。 LIKE '%T'：查询以T结尾的数据
• _：只能代表单个字符，字符的长度不能为 0。 LIKE '_T'：查询第2个字符为T的数据。 LIKE '__T'：查询第3个字符为T的数据。
• 精准匹配用REGEXP或RLIKE正则表达式匹配。

显示字段详情（表结构）

• DESCRIBE 表名或 DESC 表名 显示表中所有字段的详细信息，字段名、字段类型、是否允许为null、默认值…

条件查询（过滤数据）

• SELECT 字段 FROM表名 WHERE 查询条件
• 查询条件可以是任何运算符。
• 将字段中不满足条件的数据过滤掉。

五、排序与分页

• 若未指定排序方式，默认使用数据库中添加数据的顺序排序。

单列排序(一条数据排序)

SELECT 字段1，字段2 FROM 表 ORDER BY 排序字段 ASC/DESC;

• 若ORDER BY后边未显示的指定排序方式，默认是升序。
• ASC(ascend)：升序
• DESC(descend)：降序

多列排序(多条数据排序)

SELECT 字段1，字段2 FROM 表 ORDER BY 排序字段1 ASC/DESC，排序字段2 ASC/DESC;

• 若第一个排序条件中还存在相同的值，再按第二个条件排序，以此类推。
• 若排序后不存在相同的值，则不会执行后边的排序条件

分页

SELECT 字段 FROM 表名 LIMIT 位置偏移量，条目数量； SELECT 字段 FROM 表名 LIMIT 条目数量 OFFSEF 位置偏移量；(MySQL新特性8.0)

• 位置偏移量：从第几条数据开始
• 条目数量：返回多少条数据
• 如返回第1页数据，每页20条数据：SELECT 字段 FROM 表名 LIMIT 0，20； 意为：从第1条数据开始，返回20条数据(第一条数据索引为0)。偏移量为0时可不写偏移量。
• 如返回第2页数据，每页20条数据：SELECT 字段 FROM 表名 LIMIT 20，20； 意为：从第20条数据开始，返回20条数据。
• 分页公式：LIMIT (pageNum-1) * pageSize, pageSize
• 新特性分页公式：LIMIT pageSize OFFSET (pageNum-1) * pageSize, 意为：每页显示pageSize条数据，当前显示第pageNum页。
• 配合条件查询顺序：SELECT ... FROM ... WHERE ...ORDER BY ... LIMIT

六、多表查询（关联查询）

• 多表查询：也称关联查询，指两个或多个表一起完成查询操作。
• 前提条件：一起查询的表之间存在一对一、一对多的对应关系。
• 为什么不可以将多个表合并为一个表？ 1. 若将多个表合并为一个表，很多数据对应的字段可能为null，浪费空间。 2. 查询多个表时，每查询一张表就要进行一次IO，查询多个表时IO次数多，效率不佳（查询是将数据库中数据读取到内存中，每一次读写为一次IO）。 3. 表被查询时会被锁住，当我们查询该表时，其他人无法同时查询该表，影响效率。 4. 若多表合一，表中的数据越多,我们的表就越难去维护。
• 为什么需要多表查询？ 当我们要查询的数据存在于多个表时，使用多表查询

多表查询(关联查询)

• SELECT 字段1，字段2 FROM 表1，表2 WHERE 表1.id=表2.id 查询多表中同一主键id下的数据。
• SELECT 表1.字段1，表2.字段2 FROM 表1，表2，表3 WHERE 表1.id=表2.id AND 表1.id=表3.id 从SQL优化的角度来看，建议多表查询时，所有查询的字段都指定其所在表。
• 因为要指定字段所在的表，当表名太长时，可以在FROM语句中指定表的别名在SELECT和WHERE语句中使用表的别名。
• 查询两个表需要1个查询条件。
• 查询n个表至少需要n-1个查询条件，每个查询条件通过AND连接
• 一旦给表起了别名就不能用表的原名了，因为一旦指定了别名，服务器层的原名就会被别名覆盖，找不到原名。数据库中的表名不会变。

等值连接、非等值连接

• 等值连接：通过=号连接的WHERE条件语句。
• 非等值连接：通过=号以外的其他运算符连接的WHERE条件语句。

自连接、非自连接

• 自连接：表自己连接自己，即自我引用。 如：WHERE 表1.id1 = 表1.id2，从同一个表中查询id1和id2相同的数据。
• 非自连接：表与其他表连接。类比自连接示例便知。

内连接、外连接

• 内连接：结果集中只有满足WHERE条件语句条件的数据，没有不满足条件的数据，即为内连接。
• 外连接：结果集中除了有满足条件语句的数据，还查询到了不满足条件语句的数据，即为外连接。 通常查询所有某个id下的信息，都是外连接。如查询所有员工的姓名、年龄。

外连接分类

左外连接

结果集中除了有满足条件语句的数据，还查询到了左表中(运算符左边的表达式)不满足条件语句的数据，即为左外连接。

右外连接

结果集中除了有满足条件语句的数据，还查询到了右表中(运算符右边的表达式)不满足条件语句的数据，即为右外连接。

满外连接

结果集中除了有满足条件语句的数据，还查询到了左、右两表中(运算符两边的表达式)不满足条件语句的数据，即为满外连接。

七、SQL99实现多表查询

SQL92、SQL99区别

• 什么是SQL92、SLQ99？ SQL92、SQL99都是SQL标准的版本，也是SQL最重要的两个标准版本。 SQL92也称SQL-2标准。 SQL99也称SQL-3标准。
• 二则区别是什么？ SQL92的语法简单，因而语句更长，可读性差。 SQL99语法复杂，因而语句更短，可读性更强。 SQL99解决SQL92中诸多问题。

SQL92实现外连接

注意：
	1. MySQL不支持SQL92标准语法，但其他数据库管理系统支持。
	2. 内连接的实现方式和之前一样。

• 左外连接：在右表补充(+)。 示例：e.department_id = d.department_id(+);
• 右外连接：在左表补充(+)。 示例：e.department_id(+) = d.department_id;

SQL99实现多表查询

注意：SQL99语法是MySQL支持的。

内连接

• SELECT 字段1，字段2 FROM 表1 [INNER] JOIN 表2 ON 查询条件; 不再使用WHERE来控制查询条件， 取而代之的是JOIN…ON
• JOIN 表1 ON 查询条件 JOIN 表2 ON 查询条件 JOIN 表3 ON 查询条件；
• 两个以上的表即再后边追加JOIN...ON...即可，INNER关键字表示内连接，可省。
• 理解：从表1查询数据，发现数据还存在与表2，于是通过JOIN加入表2，用ON来加入查询条件；又发现数据还存在于表3，再通过JOIN加入表3，用ON控制查询条件，多表以此类推。
• 注意使用的哪两个表来做条件控制，建议统一用第1张表和新加表做条件控制；或者上一张表和新加表做条件控制，避免混乱。
• 示例: SELECT last_name,department_name,city FROM employees e JOIN departments d ON e.department_id=d.department_id JOIN locations l ON d.location_id=l.location_id;

外连接

• SELECT 字段 FROM 表1 [OUTER] JOIN 表2 ON 查询条件 OUTER关键字可省

左外连接

• SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 查询条件

右外连接

• SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 查询条件

满外连接

• SELECE 字段 FROM 表1 FULL OUTER JOIN 表2 ON 查询条件； 但MySQL不支持改语法

自然连接（新特性）

• 当要查询两表中所有相同字段的时候使用。
• 语法格式： SELECT 字段... FROM 表1 NATURAL JOIN 表2；
• 自动返回表1和表2中所有相同字段的数据，不需要在写条件语句。
• 由于自动查询所有相同字段，因为不够灵活，按需使用。

USING连接（新特性）

• 当要查询两个表中相同的字段的时候使用。
• 语法格式： SELECT 字段 FROM 表1 JOIN 表2 USING(表1和表2中相同的字段) 自动查询两表中的USING()中的参数字段。

多表连接约束条件总结

• WHERE：适用于所有关联查询。
• ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以合并到WHERE中和其他条件一起写，但分开写可读性更好。
• USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等。
• 注意： 我们要控制连接表的数量。多表连接就相当于嵌套for循环，非常消耗资源，会让SQL查询性能严重下降。超过3个表就不要使用join连接了。

笛卡尔积(交叉连接)错误

• 笛卡尔乘积时一种数学运算，因为这里的错误刚好复合这种运算规则，因而称之为笛卡尔积错误
• 如一个公司有多个部门，当我们查询一个员工所在部门时，返回的结果集中出现所有部门都有该员工的现象。即为笛卡尔积错误。
• 为什么会出现笛卡尔积错误？ 1. 因为缺少了多表的连接条件WHERE语句，导致字段匹配混乱。 2. 连接条件WHERE语句无效。 3. 所有表中的所有数据(行)连接了。
• 笛卡尔积错误语句示例： SELECT 字段1，字段2 FROM 表1，表2； SELECT 字段1，字段2 FROM 表1 CROSS JOIN 表2；

八、UNION关键字的使用

合并查询结果

• 利用UNION关键字，可以合并多条SELECT查询语句，并将所有SELECT查询语句的结果集合并为一个结果集。
• 合并时，两个表对应的字段数量(列数)、数据类型必须相同，并且相互对应。
• 每条SELECT语句之间使用UNION或UNION ALL关键字分割。
• 语法格式：SELECT 字段1,字段2 FROM 表1 UNION [ALL] SELECT 字段3,字段4 FROM 表2；
• UNION操作符：返回两个查询的结果集的并集，去除重复的记录。
• UNION ALL操作符：返回两个查询的结果集的并集，不去重。
• UNION ALL 比UNION的查询效率更高，若不需要去重建议使用UNION ALL。

九、UNION实现7种多表查询

内连接

如中图

• SELECT 字段1，字段2 FROM 表1 JOIN 表2 ON 查询条件；

左外连接

如左上图

• SELECT 字段... FROM 表1 LEFT JOIN 表2 ON 查询条件；

右外连接

如右上图

• SELECT 字段... FROM 表1 RIGHT JOIN 表2 ON 查询条件；

过滤右表

如左下图

• SELECT 字段... FROM 表1 LEFT JOIN 表2 ON 查询条件 WHERE 右表.字段 IS NULL；

过滤左表

如右下图

• SELECT 字段... FROM 表1 LEFT JOIN 表2 ON 查询条件 WHERE 左表.字段 IS NULL；

满外连接

如下左图

通过UNION ALL 关键字连合并查询条件即可。

• 方式一：左上图 + 右下图 SELECT 字段... FROM 表1 LEFT JOIN 表2 ON 查询条件 *UNION ALL* SELECT 字段... FROM 表1 LEFT JOIN 表2 ON 查询条件 WHERE 左表.字段 IS NULL；
• 方式二：右上图 + 左下图 SELECT 字段... FROM 表1 RIGHT JOIN 表2 ON 查询条件 **UNION ALL** SELECT 字段... FROM 表1 LEFT JOIN 表2 ON 查询条件 WHERE 右表.字段 IS NULL；

过滤交集

如下右图

通过UNION ALL 关键字连合并查询条件即可。

• 左下图 + 右下图 SELECT 字段... FROM 表1 LEFT JOIN 表2 ON 查询条件 WHERE 右表.字段 IS NULL **UNION ALL** SELECT 字段... FROM 表1 LEFT JOIN 表2 ON 查询条件 WHERE 左表.字段 IS NULL；

十、函数

为什么需要函数？

• 函数可以将我们经常使用的代码封装起来，需要的时候调用即可，
• 这样既可以提高代码效率，又提高了可维护性，
• 在SQL中，我们也可以使用函数将索引(查询)出来的数据进行函数操作，可以极大的提高用户对数据库的管理效率。

函数分类

MySQL的内置函数从实现功能角度可分为：

• 数值函数
• 字符串函数
• 日期和时间函数
• 流程控制函数
• 加密与解密函数
• 获取MySQL信息函数
• 聚合函数

这些内置函数又可以分为：

• 单行函数
• 聚合函数（或分组函数）

单行函数

什么是单行函数？

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

数值函数

基本函数

示例：

三角函数

举例：

• ATAN2(M,N)函数返回两个参数的反正切值。
• 与ATAN(X)函数相比，ATAN2(M,N)需要两个参数，例如有两个点point(x1,y1)和point(x2,y2)，
• 使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1))，
• 使用ATAN2(M,N)计算反正切值则为ATAN2(y2-y1,x2-x1).
• 由使用方式可以看出，当x2-x1等于0时，ATAN(X)函数会报错，而 ATAN2(M,N)函数则仍然可以计算。

ATAN2(M,N)函数的使用示例如下：

角度与弧度互换函数

示例：

SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90)) FROM DUAL;

指数与对数

进制间的转换

字符串函数

注意：MySQL中，字符串的位置是从1开始的。

日期和时间函数

获取日期、时间

日期与时间戳的转换

获取月份、星期、星期数、天数等函数

日期的操作函数

EXTRACT(type FROM date)函数中type的取值与含义：

时间和秒钟转换的函数

计算日期和时间的函数

第一组：

第二组：

日期的格式化与解析

上述 非GET_FORMAT 函数中fmt参数常用的格式符：

GET_FORMAT函数中date_type和format_type参数取值如下：

流程控制函数

• 流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。
• MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

示例：

加密与解密函数

• 加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。
• 这些函数在保证数据库安全时非常有用。

MySQL信息函数

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

其他函数

• MySQL中有些函数无法对其进行具体的分类，
• 但是这些函数在MySQL的开发和运维过程中也是不容忽视的。

聚合函数（多行函数）

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

AVG和SUM函数

• AVG：求数值类型平均值
• SUM：求数值类型总和

MIN和MAX函数

• MIN：求任意类型最小值
• MAX：求任意类型最大值

COUNT函数

• COUNT函数用于计算指定字段在表中出现的个数。
• 计算表中有多少条记录：
• 方式1：COUNT（*）
• 方式2：COUNT（数字）
• 方式3：COUNT（字段） 该方式不一定正确，因为不会计算值为NULL的数据。
• 三者效率对比：COUNT（*）= COUNT（数字）> COUNT（字段）
• 问题：用count( * )，count(数字)，count(字段)谁好呢? 其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。 Innodb引擎的表用count(*),count(数字)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(字段)。
• 问题：能不能使用count(字段)替换count( * )? 不要使用 count(字段)来替代 count( * ) ， count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
• 说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

GROUP BY分组数据

• GROUP BY： 将指定的字段，在结果集中分组展示。
• 语法格式：SELECT 字段1，字段2 FROM 表 GROUP BY 分组字段1，分组字段2;
• 注意：
• SELECT语句中有聚合函数的查询字段一定要显示的声明在GROUP BY中，反之不必。 若不显示的声明，所有的分组会展示在一列中，可读性差。
• 声明顺序：GROUP BY在FROM后边，在WHERE后边，在ORDER BY前边，在LIMIT前边。
• 没有聚合函数的一般查询语句写在WHERE中，有聚合函数的写在GROUP BY中。查询效率更佳。
• WITH ROLLUP关键字：用于在结果集末尾追加一条记录，显示查询的记录总数，即统计记录数量
• 语法格式：SELECT 字段 FROM 表 GROUP BY 分组字段 WITH ROLLUP; 注意：ROLLUP和ORDER BY是互相排斥的，二者不能同时使用。

HAVING过滤分组数据

• 作用：和WHERE一样，用于控制条件语句，但HAVING是控制分组的条件语句的。
• 语法格式：SELECT 字段 FROM 表 GROUP BY 分组字段 HAVING 有聚合函数的过滤条件；
• 要求：
• 当条件语句中 出现聚合函数 时，必须使用HAVING替换WHERE（出于查询效率考虑）。
• HAVING不能单独使用，必须和GROUP BY一起使用，因为他是过滤（处理）分组数据的。（单独使用也不会报错，但是没有意义）
• HAVING必须声明在GROUP BY 后边。 实质就是先将数据分组，在对数据进行求和等操作；若先对数据进行了求和等操作，再分组，则分组数据和需求不对应。
• HAVING和WHERE配合使用，将没有聚合函数的语句声明在WHERE中，有聚合函数的语句声明在HAVING中，不要全部声明在HAVING中，效率更高。 因为WHERE比HAVING先执行，将过滤条件声明在WHERE中，先过滤数据，后边的语句就会少操作很多数据；而聚合函数都是对数据进行求和等操作的，将其放在后边执行，也会减少操作的数据，提高执行效率。

HAVING和WHERE对比

总结：

• 从适用范围上看：HAVING适用范围更广。 因为HAVING可以控制有聚合函数的条件语句和一般条件语句；而WHERE只能控制一般条件语句。
• 条件语句中没有聚合函数时，WHERE的执行效率高于HAVING。 从SQL语句执行顺序可知，WHERE要比HAVING先执行，先通过WHERE对数据进行过滤之后，后边的数据就变少了，WHERE后边执行的语句处理的数据也就变少了，因而效率就更高了。

十一、SQL执行顺序

SELECT语句的完整结构和执行顺序

• SELECT语句完成结构

• 执行顺序

总结：

1. 首先通过FROM找到表，
2. 通过WHERE条件语句从表中将所需的数据过滤出来，
3. 通过GROUP BY对过滤的数据进行分组，
4. 通过HAVING的聚合函数对数据进行处理，
5. 返回处理后SELECT中查询的字段
6. 通过DISTINCT对数据进行去重，
7. 通过ORDER BY对去重后的数据进行排序，
8. 通过LIMIT限制返回的数据量，返回查询的结果集。

• 注意：SQL执行每一步都会产生一张虚拟表，然后将虚拟表传入下一个步骤作为输入。

十二、子查询

子查询的基本使用

• 语法格式示例：SELECT 字段 FROM 表 WHERE 字段 运算符 ( SELECT 字段 FROM 表 ) 通常是在一个查询语句的条件语句中嵌套另一个查询语句，用括号包裹表示一个整体。 子查询比主查询先执行。
• 若既可以使用子查询，也可以使用自连接，建议使用自连接，因为自连接效率高。
  • 可以这样理解，子查询实际上是通过未知表进行查询后的条件判断，
  • 而自连接时通过已知的自身数据表进行条件哦按段，
  • 因此在大部分DBMS中都对自连接处理进行了优化。

• 子查询：即嵌套查询，在一个查询语句中嵌套另一个查询语句。MySQL4.1引入的。
• 子查询的引入大大增强了SELECT查询的能力，因为很多时候需要从结果集中查询数据，或者从同一个表中先计算得出一个数据结果（可能是某个标量，也可能是某个集合），然后与这个数据结果进行比较。
• 称谓规范： 主查询（外查询）：括号外边的查询语句。 自查询（内查询）：括号内的查询语句。
• 注意事项：
• 子查询要包含在括号内。
  • 子查询声明在运算符右边，可读性好。
  • 单行操作符对应单行子查询，多好操作符对应多行子查询。
    • 单行子查询：子查询结果集只有一条数据（记录）。
    • 多行子查询：子查询结果集有多条数据（记录）。

子查询的分类

按子查询返回数据条目数分类：

• 单行子查询：子查询结果集只有一条数据（记录）。 注意：若子查询中出现null值，则子查询不返回任何数据；若子查询返回多条数据，会报错，因为子查询单行查询操作符不知道该使用哪条数据做比较。
• 多行子查询：子查询结果集有多条数据（记录）。

按子查询执行次数分类：

• 相关（关联）子查询：子查询的执行依赖于主查询，因为子查询会用到主查询中的表，并且进行了条件关联，因此每执行一次主查询，子查询都要重新执行一次。 过程：
  1. 先执行主查询，从子查询和主查询相同的那张表中取出子查询需要的字段（就是子查询需要查询的那个字段），
  2. 子查询根据这个字段查询，将查询结果返回给主查询，
  3. 用第1步，和第2步查询的结果作比较，返回满足条件的数据。
• 不相关（非关联）子查询：先从子查询开始执行，再执行主查询。 过程：
  1. 子查询遍历表返回满足条件的数据（只执行一次），将查询结果给主查询使用， （可理解为子查询每次的查询结果都是一样的）。
  2. 主查询遍历自己的每一条数据，与子查询返回的数据做比较，返回满足条件的数据。

单行子查询

• 子查询只返回一条数据
• 使用单行比较操作符
• 若子查询出现null值，则不反悔任何数据
• 示例：

单行比较操作符

多行子查询

• 也称为集合比较子查询
• 子查询返回多行数据
• 使用多行比较操作符
• 子查询出现null值，不会返回数据
• 示例：

多行比较操作符

相关（关联）子查询

• 相关（关联）子查询：子查询的执行依赖于主查询，因为子查询会用到主查询中的表，并且进行了条件关联，因此每执行一次主查询，子查询都要重新执行一次。也称为关联子查询。
• 过程：
  1. 先执行主查询，从子查询和主查询相同的那张表中取出子查询需要的字段（就是子查询需要查询的那个字段），
  2. 子查询根据这个字段查询，将查询结果返回给主查询，
  3. 用第1步，和第2步查询的结果作比较，返回满足条件的数据。

  

EXISTS 与 NOT EXISTS关键字

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

十三、数据库的创建和管理

创建数据库

• 方式1：直接创建。 CREWTE DATABASE 数据库名
• 方式2：创建时指定字符集。 CREATE DATABASE 数据库名 CHARACTER SET '字符集'
• 创建时，判断数据库是否已经存在。（推荐使用） CRESTE DATABASE IF NOT EXISTS 数据库名
• 注意：数据库名是不能修改的，图形化工具可以修改数据库名，是先新建库，再复制原数据库中的表到新数据库中，再删除原数据库实现的。

查看数据库

• 查看当前所有数据库 SHOW DATABASES;，注意有s
• 查看正在使用的数据库 SELECE DATABASE();，DATABASE()是MySQL中的全局函数。
• 查看指定库下所有的表 SHOW TABLES FROM 数据库名;
• 查看数据库的创建信息 SHOW CREATE DATABASE 数据库名; 或者 SHOW CREATE DATABASE 数据库名\G; 切换/使用数据库 SUE 数据库名; 注意：在操作表和数据之前必须先声明是对哪个数据库进行操作，否则就要对所有对象加上数据库名。

修改、删除数据库

• 修改数据库字符集 ALTER DATABASE 数据库名 CHARACTER SET '字符集';
• 删除指定数据库 DROP DATABASE 数据库名;
• 删除指定数据库（推荐） DROP DATABASE IF EXISTS 数据库名;

十四、表的创建与管理

创建表

• 方式1：直接创建

• 方式2：基于现有表创建，用子查询中查询出来的字段创建新表。

示例：

• 方式3：创建临时表 CREATE TEMPORARY TABLE 表 子语句

查看表结构

• 在MySQL中创建好数据表之后，可以查看数据表的结构。
• MySQL支持使用 DESCRIBE/DESC 语句查看数据表结构，也支持使用 SHOW CREATE TABLE 语句查看数据表结构。
• 三种查看语法格式：
  • SHOW CREATE TABLE 表名\G;
  • DESCRIBE 表名;
  • DESC 表明;

修改表

使用ALTER TABLE 语句可以实现：

• 向已有的表中添加列（字段） ALTER TABLE 表名 ADD 字段名 字段类型 [FIRST / AFTER 字段名];
• 修改现有表中的列（字段）：数据类型、长度、默认值… ALTER TABLE 表名 MODIFY 字段名1 字段类型 [DEFAULT 默认值] [FIRST / AFTER 字段名2];
• 删除现有表中的列（字段） ALTER TABLE 表名 DROP COLUMN 字段名;
• 重命名现有表中的列（字段） ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;

重命名表

• 方式1： RENAME TABLE 旧表名 TO 新表名;
• 方式2： ALTER TABLE 旧表名 RENAME TO 新表名;

删除表

• DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];
• 在MySQL中，当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
  • 表的数据和结构都被删除，释放表空间
  • 所有正在运行的相关事务被提交
  • 所有相关索引被删除
  • 删除后不能回滚

清空表

• 语法格式： TRUNCATE TABLE 表t; 或 DELETE FROM 表;（推荐使用，安全性高）
• 只删除表中所有的数据，但保留表结构
• 释放表的存储空间
• TRUNCATE语句不能回滚，而使用 DELETE FROM语句删除数据，可以回滚。

阿里开发规范：
【参考】TRUNCATE TABLE 比 DELETE FROM 速度快，且使用的系统和事务日志资源少，
但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

DCL中COMMIT和ROLLBACK

• COMMIT：提交数据。 一旦执行COMMIT，数据就会永久保存在数据库中，不可以回滚。
• ROLLBACK：回滚数据。 一旦执行ROLLBACK，则可以实现数据回滚，回滚到上一次COMMIT之后。

TRUNCATE TABLE 比 DELETE FROM对比

• 相同点： 都可以删除表中的所有数据，同时保留表结构。
• 不同点：
  • TRUNCATE TABLE：一旦执行，表中所有数据一定会被全部清除，不可回滚。
  • DELETE FROM：一旦执行，表中数据不一定被全部清除，可以回滚

DDL和DML的回滚说明

• DDL的操作一旦执行，旧不可以回滚。
• DML的操作一旦执行，默认情况下是不可以回滚的，但是，如果在DML执行之前，（声明）执行了SET autocommit = FALSE;，则可以回滚。
• 注意：默认情况下，所有操作执行后都会自动执行一次COMMIT，即自动提交。

MySQL8新特性—DDL的原子化

• 在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即DDL操作要么成功要么回滚 。
• DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到）中，用于回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。
• 如book1，book2两张表，book1是存在的，book2是不存在的：
  • 在mysql 5.7中，运行DROP TABLE book1,book2;删除book1和book2两张表，会报错book2不存在，但是book1会被删除。
  • 在mysql 8.0中执行DROP TABLE book1,book2;，也会报错，但是book1不会被删除。 先成功的删除book1，但在删除book2时报错了，删除book1的操作会被回滚，因为删除book1和book2是同一个事务。

十五、数据的增、删、改

插入数据

• 方式1：添加一条记录
  • 不指定添加的字段： INSERT INTO 表 VALUES（值1，值2...），若值为字符串需用''单引号包裹。
    • 注意： 该方式添加数据必须保证值的类型和字段类型一一对应，且顺序要对应。
  • 指定添加的字段： INSERT INTO 表（字段1，字段2...） VALUES（值1，值2...），没有赋值的字段，值为null。
• 方式2：同时添加多条记录 INSERT INTO 表（字段1，字段2...） VALUES（值1，值2...），（值1，值2...）
• 方式3：将查询结果添加到表中 INSERT INTO 表（字段1，字段2...) SELECT 字段1，字段2... FROM 表； 注意：
  • 查询的字段和添加的字段一一对应，
  • 添加的字段长度大于或等于查询的字段长度，否则可能添加不成功。

更新数据

• 方式1：修改指定字段下的所有记录 UPDATE 表 SET 字段1 = 值1,字段2 = 值2...;
• 方式2：修改指定字段下的部分记录 UPDATE 表 SET 字段1 = 值1,字段2 = 值2... WHERE 过滤条件;

删除数据

• 方式1：删除表中所有数据 DELETE FROM 表;
• 方式2：删除指定值的记录 DELETE FROM 表 WHERE 字段 = 值

计算列

• 计算列，即某一列的值时通过别的列计算的出来的。mysql 8.0新特性。
• 通过：GENERATED ALWAYS AS (计算表达式) VIRTUAL声明计算列
• 示例：CREATE TABLE 表 (a INT,b INT ,c INT GENERATED ALWAYS AS ( a + b ) VIRTUAL); 其中，字段c即为计算列。当b或c的值发生变化时，c的值也会随之改变。

十五、运算符

算术运算符

注意：

• null值参与运算，结果为null。
• MySQL中的+号只表示数值相加。
• 若遇到非数值类型，会自动转换为数值；若转换失败，就按0计算。 MySQL中字符串拼接要使用字符串函数CONCAT（）实现。

符号比较运算符

比较结果为真，返回1；结果为假，返回0；其他情况返回NUll，null参与计算的基本都返回null。

等于运算符：

• 数值和字符串比较，存在隐式转换，如：1= “1” 会将“1”转为1，而不是ACSII码，若转换失败则返回0。
• 字符串和字符串比较是转换为ASCII码进行比较。

安全等于：

• 即可以对null进行判断。当运算符两边的操作数都为null时，返回1；其中一个操作数为null时，返回0。而不是返回null。

非符号比较运算符

比较结果为真，返回1；结果为假，返回0；其他情况返回NUll，null参与计算的基本都返回null。

• 非符号运算符大多可以配合NOT使用，即：将比较结果取反。
• 模糊匹配：即字符串中包含匹配的字符即可，不用全等。
• 精准匹配：即正则表达式

逻辑运算符

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

注意：

• AND优先级高于OR
• 异或：相同为0，不同为1

运算符优先级

位运算符

位运算符会先将操作数转换成二进制数，然后按位进行运算，最后将结果转为十进制返回。

• 右移运算符相当于除法运算。
• 左移运算符相当于乘法运算。

十六、MySQL数据类型

常用类型介绍

整数类型

可选属性M、ZEROFILL

• M : 表示显示宽度(用多少位二进制类显示这个数)，M的取值范围是(0, 255)。 例如，int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。
• 该项功能需要配合“ ZEROFILL ”使用，表示当宽度不够时用“0”填满宽度，否则指定显示宽度无效。
• 如果设置了显示宽度，那么插入的数据宽度超过显示宽度限制，会不会截断或插入失败？ 答案：不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即 显示宽度与类型可以存储的值范围无关 。
• 从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。
• 整型数据类型可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。
• 语法格式：CREATE TABLE 表 （字段 类型[(M) ZEROFILL）];

可选属性UNSIGNED无符号类型

• UNSIGNED : 无符号类型（非负），所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无 符号整数类型的最小取值为0。
• 所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。
• 负数是有符号类型，因为有负号，正数是无符号类型。
• 语法格式：CREATE TABLE 表(字段 类型 [UNSIGNED]);

使用场景

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

如何选择

• 在评估用哪种整数类型的时候，你需要考虑 存储空间 和 可靠性 的平衡问题：一方 面，用占用字节数少的整数类型可以节省存储空间；另一方面，要是为了节省存储空间， 使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起 系统错误 ，影响可靠性。
• 举个例子，商品编号采用的数据类型是 INT。原因就在于，客户门店中流通的商品种类较多，而且，每天都有旧商品下架，新商品上架，这样不断迭代，日积月累。
• 如果使用 SMALLINT 类型，虽然占用字节数比 INT 类型的整数少，但是却不能保证数据不会超出范围65535。相反，使用 INT，就能确保有足够大的取值范围，不用担心数据超出范围影响可靠性的问题。
• 你要注意的是，在实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，我建议你首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间。

浮点类型

• 浮点数和定点数类型的特点是可以 处理小数 ，你可以把整数看成小数的一个特例。因此，浮点数和定点数的使用场景，比整数大多了。
• MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。
• 浮点数会四舍五入，数据不精准。

• REAL默认就是 DOUBLE。如果你把 SQL 模式设定为启用“ REAL_AS_FLOAT ”，那 么，MySQL 就认为REAL 是 FLOAT。
• 如果要启用“REAL_AS_FLOAT”，可以通过以下 SQL 语句实现： SET sql_mode = “REAL_AS_FLOAT”;
• 注意：
  • 在编程中，如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。
  • 同时，在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。那么，MySQL 有没有精准的数据类型呢？当然有，这就是定点数类型：DECIMAL 。

定点数类型

• 使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度(大小)，D被称为标度(小数位数)。0<=M<=65，0<=D<=30，D<M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。
• 定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的。
• 当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。
• 浮点数 vs 定点数：
  • 浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）
  • 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景 （比如涉及金额计算的场景）

位类型

• BIT类型中存储的是二进制值，类似010110。
• BIT类型，如果没有指定(M)，默认是1位。这个1位，表示只能存1位的二进制值。这里(M)是表示二进制的位数，位数最小值为1，最大值为64。
• 注意：在向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内。

日期时间类型

• YEAR 类型通常用来表示年
• DATE 类型通常用来表示年、月、日
• TIME 类型通常用来表示时、分、秒
• DATETIME 类型通常用来表示年、月、日、时、分、秒
• TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

YEAR类型

• YEAR类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要 1个字节 的存储空间。
• 在MySQL中，YEAR有以下几种存储格式：
• 以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。
• 以2位字符串格式表示YEAR类型，最小值为00，最大值为99。
  • 当取值为01到69时，表示2001到2069；
  • 当取值为70到99时，表示1970到1999；
  • 当取值整数的0或00添加的话，那么是0000年；
  • 当取值是日期/字符串的’0’添加的话，是2000年。
• 从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”，没必要写成YEAR(4)，
• 从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型。

DATE类型

DATE类型表示日期，没有时间部分，格式为 YYYY-MM-DD ，其中，YYYY表示年份，MM表示月份，DD表示 日期。需要 3个字节 的存储空间。在向DATE类型的字段插入数据时，同样需要满足一定的格式条件。

• 以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期，其最小取值为1000-01-01，最大取值为 9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
• 以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足 YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99 时，会被转化为1970到1999。
• 使用CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期。

TIME类型

• TIME类型用来表示时间，不包含日期部分。在MySQL中，需要 3个字节 的存储空间来存储TIME类型的数 据，可以使用“HH:MM:SS”格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒。
• 在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式。
  • （1）可以使用带有冒号的字符串，比如'D HH:MM:SS' 、'HH:MM:SS'、'HH:MM'、'D HH:MM'、'D HH'或'SS'格式，都能被正确地插入TIME类型的字段中。其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10。
  • （2）可以使用不带有冒号的字符串或者数字，格式为'HHMMSS'或者HHMMSS 。如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不是12:10:00。
  • （3）使用 CURRENT_TIME() 或者NOW()，会插入当前系统的时间。

DATETIME类型

• DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要 8 个字节的存储空间。在格式上 为DATE类型和TIME类型的组合，可以表示为 YYYY-MM-DD HH:MM:SS ，其中YYYY表示年份，MM表示月 份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。
• 在向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件。
  • 以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时， 最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
    • 以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式。
    • 以 YY-MM-DD HH:MM:SS 格式或者 YYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999。
    • 使用函数CURRENT_TIMESTAMP()和NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间。

TIMESTAMP类型

• TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是 YYYY-MM-DD,HH:MM:SS ，需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储 “1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫 作世界标准时间。
• 存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。
• 向TIMESTAMP类型的字段插入数据时，当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS 时，两位数值的年份同样符合YEAR类型的规则条件，只不过表示的时间范围要小很多。
• 如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围，则MySQL会抛出错误信息。

TIMESTAMP和DATETIME的区别

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
• TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

开发建议

• 用得最多的日期时间类型，就是 DATETIME 。虽然 MySQL 也支持 YEAR（年）、 TIME（时间）、DATE（日期），以及 TIMESTAMP 类型，但是在实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，很不容易记，而且查询的时候，SQL 语句也会更加复杂。
• 此外，一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用时间戳 ，因为DATETIME虽然直观，但不便于计算。

文本字符串类型

CHAR与VARCHAR类型

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

• CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
• 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在 右侧填充 空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
• 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。

VARCHAR类型：

• VARCHAR(M) 定义时， 必须指定 长度M，否则报错。
• MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节)；MySQL5.0版本以上，varchar(20)：指的是20字符。
• 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

CHAR和VARCHAR如何选择？

• 情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的，结果得不偿失。
• 情况2：固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。
• 情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。
• 情况4：具体存储引擎中的情况：
  • MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化，从而使 数据检索更快 ，用空间换时间。
  • MEMORY 存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。
  • InnoDB 存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好。

TEXT类型

• TEXT用来保存文本类型的字符串，
• 在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和VARCHAR类型相同。
• TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来代替。还有TEXT类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表
• 由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用CHAR(M)，或者 VARCHAR(M)。
• 每种TEXT类型保存的数据长度和所占用的存储空间不同，如下：

ENUM类型

• ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。
• 其所需要的存储空间由定义ENUM类型时指定的成员个数决定。
• 赋值时，值只能是ENUM中声明的值之中的一个，值只能是中ENUM声明的。

示例：

SET类型

• SET类型在存储数据时成员个数越多，其占用的存储空间越大。
• SET在赋值时，值可以时SET声明的值中的多个，这一点与ENUM类型不同，值只能是SET中声明的。

二进制字符串类型

• MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据。

BINARY与VARBINARY类型

• BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
• BINARY (M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定(M)，表示只能存储 1个字节 。例如BINARY (8)，表示最多能存储8个字节，如果字段值不足(M)个字节，将在右边填充’\0’以补齐指定长度。
• VARBINARY (M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数。VARBINARY类型 必须指定(M) ，否则报错。

示例：

BLOB类型

• BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。
• MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如 图片 、 音频 和 视频 等。
• 需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到 服务器的磁盘上 ，并将图片、音频和视频的访问路径存储到MySQL中。

示例：

TEXT和BLOB的使用注意事项

在使用text和blob字段类型时要注意以下几点，以便更好的发挥数据库的性能。

• ① BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的" 空洞 "，以后填入这些"空洞"的记录可能长度不同。为了提高性能，建议定期使用 OPTIMIZE TABLE 功能对这类表进行 碎片整理 。
• ② 如果需要对大文本字段进行模糊查询，MySQL 提供了 前缀索引 。但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT * 查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。
• ③ 把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会 减少主表中的碎片 ，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。

JSON类型

• JSON（JavaScript Object Notation）是一种轻量级的 数据交换格式 。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。
• 在MySQL 5.7中，就已经支持JSON数据类型。在MySQL 8.x版本中，JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构，使得在MySQL中存储和读取JSON类型的数据更加方便和高效。 创建数据表，表中包含一个JSON类型的字段 js 。
• 可以通过“->”和“->>”符号，从JSON字段中正确查询出指定的JSON数据的值。

空间类型

• MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东西，可以是一个实体，例如一座山；可以是空间，例如一座办公楼；也可以是一个可定义的位置，例如一个十字路口等等。MySQL中使用 Geometry（几何） 来表示所有地理特征。Geometry指一个点或点的集合，代表世界上任何具有位置的事物。
• MySQL的空间数据类型（Spatial Data Type）对应于OpenGIS类，包括单值类型：GEOMETRY、POINT、LINESTRING、POLYGON以及集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION 。
  • Geometry是所有空间集合类型的基类，其他类型如POINT、LINESTRING、POLYGON都是Geometry的子类。
    • Point，顾名思义就是点，有一个坐标值。例如POINT(121.213342 31.234532)，POINT(30 10)，坐标值支持DECIMAL类型，经度（longitude）在前，维度（latitude）在后，用空格分隔。
    • LineString，线，由一系列点连接而成。如果线从头至尾没有交叉，那就是简单的（simple）；如果起点和终点重叠，那就是封闭的（closed）。例如LINESTRING(30 10,10 30,4040)，点与点之间用逗号分隔，一个点中的经纬度用空格分隔，与POINT格式一致。

数据类型使用总结

• 在定义数据类型时，如果确定是 整数 ，就用 INT ； 如果是小数，一定用定点数类型DECIMAL(M,D) ； 如果是日期与时间，就用 DATETIME 。
• 这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如CHAR(M) 和 VARCHAR(M)。
• 关于字符串的选择，建议参考如下阿里巴巴的《Java开发手册》规范：

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

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

十七、约束

约束概述

为什么需要约束？

为了保证数据的完整性。

• 数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。
• 为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。从以下四个方面考虑：
  • 实体完整性（Entity Integrity） ：例如，同一个表中，不能存在两条完全相同无法区分的记录。
  • 域完整性（Domain Integrity） ：例如：年龄范围0-120，性别范围“男/女”。
  • 引用完整性（Referential Integrity） ：例如：员工所在部门，在部门表中要能找到这个部门。
  • 用户自定义完整性（User-defined Integrity） ：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

什么是约束？

• 约束是对表中字段值的限制，比如值的大小、类型、范围…。
• 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

如何添加约束？

• 在创建表时，添加约束： CREATE TABLE 表(字段1 数据类型1 约束1，字段2 数据类型2 约束2...);
• 在建表后，添加、删除约束： ALERT TABLE 表名称 MODIFY 字段名 数据类型 约束;

约束分类

• 根据约束数据列的限制，分为：
  • 单列约束：每个约束值约束一列（一个字段）。
  • 多列约束：每个约束可以约束多列数据（多个字段）。
• 根据约束的作用范围，分为
  • 列级约束：只能约束一个列，约束条件定义在字段后边。
  • 表级约束：能作用在多个列上，不能和字段定义在一起，需要单独定义。

  

• 根据约束的作用，分为：
  • NOT NULL：非空约束。规定某个字段不能位空。
  • UNIQUE：唯一约束。规定某个字段在整个表中是唯一的。
  • PRIMARY KEY：主键约束。规定某个字段是 非空且唯一 的。
  • FOREIGN KEY：外键约束。
  • CHECK：检查约束。
  • DEFAULT：默认值约束。

查看某个表已有的约束

• SELECT * FROM 表 WHERE 字段 = 表;

非空约束

• 作用：限定某个字段/某列的值不允许为空
• 关键字：NOT NULL
• 特点：
  • 默认情况下，所有类型的值都可以是NULL，包括INT、FLOAT等数据类型。
  • 非空约束只能用于列级约束，不能用于表级约束。（只能约束单个字段，不能约束字段组合）
  • 一个表可以有很多非空列。
  • 空字符串不等于NULL，0不等于NULL。
  • 当已存在的字段有NULL值时，不允许修改为NOT NULL
• 语法格式：
  • 建表时添加： CREATE TABLE 表名称(字段名 数据类型, 字段名 数据类型 NOT NULL, 字段名 数据类型 NOT NULL);
  • 建表后添加： alter table 表名称 modify 字段名 数据类型 not null;
  • 删除非空约束：
    1. alter table 表名称 modify 字段名 数据类型 NULL;， 修改为NULL，相当于修改某个非注解字段，该字段允许为空。
    2. alter table 表名称 modify 字段名 数据类型; 去掉not null，相当于修改某个非注解字段，该字段允许为空。

唯一性约束

• 作用：用来限制某个字段/某列的值不能重复。
• 关键字：UNIQUE
• 特点：
  • 唯一性约束可以用于列级，也可以用于表级。
  • 声明约束的时候，若不给约束命名，则默认和字段同名。
  • MySQL会给唯一约束的字段上默认创建一个唯一索引。
• 语法格式：
  • 建表时添加：
    • 约束名为可选项，但是删除约束时需要用到约束名。

    

  • 建表后添加：

  

复合唯一约束

• 复合唯一约束即一次将多个字段的值同时设置为唯一的，不可重复的。
• 语法格式：

删除唯一约束

• 添加了唯一性约束的列上都会自动创建唯一索引。
• 删除唯一约束只能通过删除唯一索引的方式来删除。
• 删除时需要指定唯一索引名，唯一索引名和约束名一样。
• 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，就默认和组合中的一个列名相同。也可以自定义唯一性约束名。
• 语法格式：ALERT TABLE 表 DROP INDEX 约束名; 可任意通过SHOW INDEX FROM 表;来查看表的索引。

主键约束

• 作用：用来唯一标识表中的一行记录。
• 关键字：PRIMARY KEY
• 特点：
  • 主键不允许重复，不允许出现空值。相当于唯一约束+非空约束的组合。
  • 一个表中，只能有一个主键约束。
  • 主键约束对应表中的一列或多列（复合主键）。
  • 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。 - MySQL的主键名固定为PRIMARY，就算自己命名了主键约束名也没用。
  • 当创建主键约束时，系统默认会在所在的列或组合上建立对应的主键索引(能够根据种族间查询的，就根据主键查询，效率更高)。如果删除了主键约束，主键约束对应的索引就自动删除了。
• 注意：
  • 不要修改主键字段的值，因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

添加主键约束

• 建表时添加

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

• 建表后添加

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

复合主键

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
primary key(字段名1,字段名2) #表示字段1和字段2的组合是唯一的，也可以有更多个字段
);

删除主键约束

• alter table 表名称 drop primary key;
• 删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存 在。

自增约束

• 自增列，即某个字段的值自增长。
• 关键字：AUTO_INCREMENT
• 特点：
  • 一个表最多只能有一个自增长列。
  • 自增长列约束的列必须时键列（主键列，唯一键列）。
  • 自增长约束的数据类型必须时整数类型。
  • 当需要产生唯一标识符或者顺序值时，可设置为自增长。
  • 如果自增长列指定了0和null，则会在当前最大值的基础上自增；如自增长列手动指定了具体值，则直接使用该值。
• 注意：
  • 开发中，一定我们指定了某个字段为自增长字段，就不要手动赋值了。

指定自增约束

建表时指定

列级：
create table 表名称(
字段名 数据类型 primary key auto_increment,#该字段为自增长字段，通常为主键id
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
表级：
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值,,
primary key(字段名)
);

建表后

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

删除自增约束

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

MySQL 8.0新特性—自增变量的持久化

• 比如一个自增id，值分别是1,2,3,4，当你删掉4，再插入一行记录，这条记录的id是5，而不是4，即1,2,3,5。
• 当你删掉5这条记录，重启服务器，再插入一行记录，这条记录的id是4，而不是6。
• 这是因为自增主键没有持久化。
• 再MySQL5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持节话到硬盘中，当重启数据库时，该计数器就会被初始化。
• 再MySQL 8.0中，将自增主键的计数器持久化到重做日志中，每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值，因而重启服务器不会出现第二点的情况。

外键约束

• 作用：限制某个表的某个字段的引用完整性。
• 关键字：FOREIGN KEY
• 主表（父表）：被引用/参考的表。
• 从表（子表）：引用/参考别人的表。
• 注意：
  • 从表的外键列，必须引用/参考主表的主键或唯一约束的列。（被参考/依赖的值必须时唯一的）
  • 在创建外键约束时，如果没给外键约束名，默认名不是列名，而是自动产生一个外键名。
  • 在CREAT创建表时就指定外键约束的话，先创建主表，再创建从表。
  • 删表时，先删从表（或外键约束），再删主表。
  • 当主表的记录被从表引用时，主表的记录不能直接被删除，需要先删除从表中依赖该记录的数据，再删出表的记录。
  • 外键约束时在从表中指定的，并且一个表可以建立多个外键约束。
  • 从表的外键列与主表被参考的列名可以不相同，但数据类型必须相同，逻辑意义必须一致。如果类型不一致，创建子表时就会报错。
• 总结：约束关系是针对双方的
  • 添加了外键约束后，主表的修改和删除数据受约束。
  • 添加了外键约束后，从表的添加和修改数据受约束。
  • 在从表上建立的外键，要求主表必须存在。
  • 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除。

添加外键约束

建表时添加

先创建主表：
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

建表后添加：

• 一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。
• ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

删除外键约束

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

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

阿里开发规范

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

约束等级

• Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录。
• Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null。
• No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作。
• Restrict方式 ：同no action， 都是立即检查外键约束。
• Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别。

如果没有指定等级，就相当于Restrict方式。

• 对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

建主表：
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) on update cascade on delete set null
#把修改操作设置为级联修改等级，把删除操作设置为set null等级
);

CHECK约束检查

• 检查某个字段的值，是否满足要求，一般是指值的范围。 某个字段在设置了CHECK约束后，在给这个字段添加值的时候，会检查这个值是否满足要求，若不满足，则会报错。
• 关键字：CHECK
• 注意：MySQL 5.7 中可以使用，但不支持该功能。在MySQL8 中才支持。
• 语法格式：CHECK(字段 运算符 值)

# 示例1
CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);
# 示例2
age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))
#示例3
CHECK(height>=0 AND height<3)

DEFALUT默认值约束

• 作用：给某个字段指定默认值，一旦设置默认值，若在插入数据时没有显示的给这个字段赋值，就会使用默认值。
• 关键字：DEFAULT
• 注意：默认值约束，一般不在主键和唯一键上使用。

添加默认值约束

创建时添加：

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

建表后添加：

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

删除默认值约束

• 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字段默认值约束，保留非空约束

关于默认值的面试题

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

关于约束的开发建议

• MySQL虽然提供了外键约束，但我们一般不使用，通常会在Java中进行约束，因为在sql中约束，当外键关联过多时，会对系统造成阻塞，影响系统性能。
• 问题1：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？ 答：不是的
• 问题2：建和不建外键约束有什么区别？ 答：建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。 不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的 引用完整性 ，只能依 靠程序员的自觉 ，或者是 在Java程序中进行限定 。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。
• 问题3：那么建和不建外键约束和查询有没有关系？ 答：没有

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

十八、视图

常用的数据库对象

什么是视图

• 视图是一种虚拟表，本事是不具有数据的，占用空间内存很少，他是SQL中的一个重要概念。
• 视图是建立在已有表的基础上的，这些已有表称为基表。
• 视图的本质就是一个存储SELECT语句的虚拟表。
• 其实视图就是一个代理基表的虚拟表，它可以选择展示基表中的某些字段；也可以代理对基表数据的操作，任何会视图的操作都会映射到基表中；视图是由SELECT语句构成的。
• 视图的应用场景：一般用于大型项目，对于只有几十张表的项目不推荐使用。
• 视图的优点：
  • 简化查询（封装了SELECT语句，我们可以直接调用视图，不用再写SELECT语句）
  • 控制数据的访问（可以选择展示基表中的哪些字段，或不展示哪些字段）

视图的创建

• 在 CREATE VIEW 语句中，嵌入子查询创建视图

精简版：
CREATE VIEW 视图名称
AS 查询语句

完整版：
CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

视图别名

• 在开发中，我们经常需要给字段重命名再展示。

方式1：字段别名
CREATE VIEW 视图
AS
SELECT 字段1 别名1，字段2 别名2
FROM 表

方式2：视图列表
# 视图列表的字段和SELECT查询的字段数量，顺序必须一致。
CREATE VIEW 视图（视图字段1，视图字段2...）# 视图列表
AS
SELECT 字段1，字段2
FROM 表

创建多表联合视图

• 在开发中，我们经常需要通过多张表来创建视图

CREATE VIEW 视图名
AS
SELECT 字段1，字段2
FROM 表1，表2

利用视图对数据进行格式化

• 我们经常需要输出某个格式的内容，比如我们想输出员工姓名和对应的部门名，对应格式为 emp_name(department_name)，就可以使用视图来完成数据格式化的操作：

CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id

查看视图

• 语法1：查看数据库所有的表、视图对象。 SHOW TABLES;
• 语法2：查看视图的结构 DESC / DESCRIBE 视图名称;
• 语法3：查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等） SHOW TABLE STATUS LIKE '视图名称'\G;某些终端可能无法识别“\G” 结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。
• 语法4：查看视图的详细定义信息 SHOW CREATE VIEW 视图名称;

操作视图的数据

• 对视图数据的操作，最终都会映射到基表中。（视图通常只用于查询数据，不用于操作数据）
• 对视图数据的操作和对表的数据的操作一样，只不过把表名换成视图而已。
• 同样可以增、删、改
• 若操作的视图的字段不存在于基表中，则操作失败

不可更新的情况：

• 要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如 下情况时，视图不支持更新操作：
  • 在定义视图的时候指定了“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；
  • 视图定义基于一个 不可更新视图 ；
  • 常量视图。

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

删除、修改视图

修改视图

• 当表中的视图字段被修改，视图中的字段也要做出相应的修改。

方式1：CREATE OR REPLACE VIEW
# 当字段存在的时候就修改字段，不存在就创建字段
CREATE OR REPLACE VIEW 视图(视图列表)
AS
SELECT 字段1，字段2...
FROM 表

方式2：ALTER VIEW
ALTER VIEW 视图
AS
SELECT语句

删除视图

• 删除视图不会影响基表，但是若有其他视图是基于被删视图创建的，则创建的这个视图也需要做出删除或修改。 DROP VIEW IF EXISTS 视图名称;

视图的优点

1. 操作简单 将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间 的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简 化了开发人员对数据库的操作。
2. 减少数据冗余 视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语 句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。
3. 数据安全 MySQL将用户对数据的 访问限制 在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用 户不必直接查询或操作数据表。这也可以理解为视图具有 隔离性 。视图相当于在用户和实际的数据表之 间加了一层虚拟表。 同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接 通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。
4. 适应灵活多变的需求 当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较 大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。
5. 能够分解复杂的查询逻辑 数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图 获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

视图的缺点

• 如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 可读性不好 ，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。
• 实际项目中，如果视图过多，会导致数据库维护成本的问题。
• 所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视图，使系统整体达到最优。

十九、函数与存储过程

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

储存过程

什么是储存过程

• 含义：存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过预先编译 的 SQL 语句的封装。 执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。
• 好处： 1、简化操作，提高了sql语句的重用性，减少了开发程序员的压力。 2、减少操作过程中的失误，提高效率。 3、减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）。 4、减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性。
• 和视图、函数的对比：
  • 它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表 ，通常不对底层数据表直接操作，而存储过程是程序化的SQL，可以直接操作底层数据表 ，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。
  • 一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是 没有返回值 的。

储存过程参数分类

• 按有无参数和返回值，可将参数分为5类：
  • 没有参数（无参数无返回值）
  • IN类型（有参无返回值）
  • OUT类型（无参有返回值）
  • INOUT（有参有返回值）
  • 即带IN也带OUT（有参有返回值）

创建储存过程

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。
• 4、存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END 编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。
  • 1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
  • 1. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
  • 1. SET：赋值语句，用于对变量进行赋值。
  • 1. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
• 4、需要设置新的结束标记 DELIMITER 新的结束标记
  • 因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用 DELIMITER改变存储过程的结束符。
  • 比如：“DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。存储过程定 义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。
  • 当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。

DELIMITER结束标记示例：

DELIMITER $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名1 参数类型1,...)
[characteristics ...]
BEGIN
sql语句1;
sql语句2;
END $

# 创建存储过程select_all_data()，查看 emps 表的所有数据
ELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM emps;
END $
# 最后再改回“；”分号，为了不影响后续使用。
DELIMITER ;

存储过程的调用

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

存储过程的调试

• 在 MySQL 中，存储过程不像普通的编程语言（比如 VC++、Java 等）那样有专门的集成开发环境。
• 因此，你可以通过 SELECT 语句，把程序执行的中间结果查询出来，来调试一个 SQL 语句的正确性。调试成功之后，把 SELECT 语句后移到下一个 SQL 语句之后，再调试下一个 SQL 语句。
• 这样 逐步推进 ，就可以完成对存储过程中所有操作的调试了。当然，你也可以把存储过程中的 SQL 语句复制出来，逐段单独调试。

存储函数

• 些函数可以对数据进行的各种处理操作，极大地提高用户对数据库的管理 效率。

创建函数

# SET GLOBAL log_bin_trust_function_creators = 1; 报错解决方案1
DELIMITER //
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...] 
# 报错解决方案2：
# LANGUAGE SQL
# NOT DETERMINISTIC
# READS SQL DATA
# SQL SECURITY DEFINER
# COMMENT '查询部门平均工资'
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END //
DELIMITER ;

说明：
1、参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。
2、RETURNS type 语句表示函数返回数据的类型；
RETURNS子句只能对FUNCTION做指定，对函数而言这是 强制 的。它用来指定函数的返回类型，而且函数体必须包含一个 RETURN value 语句。
3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。
4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。
5、若在创建存储函数中报错 `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：在创建函数前加上SET GLOBAL log_bin_trust_function_creators = 1;

函数调用

• 在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存 储函数与MySQL内部函数是一个性质的。区别在于，存储函数是 用户自己定义 的，而内部函数是MySQL 的 开发者定义 的 SELECT 函数名(实参列表);

函数和存储过程对比