SQL

分类

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

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

基本规则

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

大小写规范

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

注释要求

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

命名要求

  • 数据库、表名不得超过30个字符，变量名限制为29个
  • 必须只能包含 A–Z, a–z, 0–9, _共63个字符
  • 数据库名、表名、字段名等对象名中间不要包含空格
  • 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
  • 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
  • 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

数据导入指令

source d:\mysqldb.sql

DDL数据定义语言

数据库操作

  • 创建数据库
    创建数据库并设定字符集
    推荐使用IF NOT EXISTS：如果要创建的数据库已经存在，则创建不成功，但不会报错。 

    1. CREATE DATABASE mytest1;  # 创建的此数据库使用的是默认的字符集
    1. CREATE DATABASE mytest1 set CHARACTER set 'gbk';
    1. CREATE DATABASE IF NOT EXISTS mytest1 set CHARACTER set 'utf8';

  • 查询当前的数据库有哪些 

    1. SHOW DATABASE;

  • 查看数据库的创建信息 

    1. SHOW CREATE DATABASE 数据库名;

  • 切换数据库 

    1. USE atguigudb;

  • 查看当前数据库中的表
    查看指定数据库中保存的表 

    1. SHOW TABLES;
    1. SHOW TABLES FROM mysql;

  • 查看当前使用的数据库 

    1. SHOW DATABASE() FROM DUAL;

  • 更改数据库字符集 

    1. ALTER DATABASE mytest2 CHARACTER SET 'utf8';

  • 删除数据库
    推荐使用IF EXISTS： 如果要删除的数据库存在，则删除成功。如果不存在，则默默结束，不会报错。 

    1. DROP DATABASE mytest1;
    1. DROP DATABASE IF EXISTS mytest1;

表操作

创建表

  • 方式一：
    加上了IF NOT EXISTS关键字，则表示：如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。 

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

  • 方式二：使用 AS subquery 选项，将创建表和插入数据结合起来
    (会自动插入查询的数据和字段) 

    1. CREATE TABLE emp1 AS SELECT * FROM employees; 
    2. CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表
    3. CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80;

查询表结构

  1. SHOW CREATE TABLE 表名\G

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

查询表的字段

  1. DESC 表名;

修改表结构

使用ALTER TABLE 可以实现

  • 向已有的表中添加列 

    1. ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

  • 修改现有表中的列
    对默认值的修改只影响今后对表的修改，此外，还可以通过此种方式修改列的约束。 

    1. ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名 2】;

  • 删除现有表中的列 

    1. ALTER TABLE 表名 DROP 【COLUMN】字段名;

  • 重命名现有表中的列 

    1. ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

重命名表

  • 使用RENAME 

    1. RENAME TABLE emp TO myemp;

  • 使用ALTER 

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

删除表

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

语法如下：

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

IF EXISTS 的含义为：如果当前数据库中存在相应的数据表，则删除数据表；如果当前数据库中不存在相应的数据表，则忽略删除语句，不再执行删除数据表的操作。切注意：DROP TABLE 语句不能回滚

清空表

TRUNCATE TABLE语句：

  • 删除表中所有的数据
  • 释放表的存储空间

语法如下：

  1. TRUNCATE TABLE [表名]

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

DELETE FROM语句可以回滚。（推荐使用）

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

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

Mysql数据类型

MySQL基础篇 - 图1

Mysql数据类型属性值

MySQL基础篇 - 图2

整数类型

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。

MySQL基础篇 - 图3

浮点类型

定点数类型

位类型(BIT)

BIT类型中存储的是二进制值，类似010110。

MySQL基础篇 - 图4

BIT类型，如果没有指定(M)，默认是1位。这个1位，表示只能存1位的二进制值。这里(M)是表示二进制的位数，位数最小值为1，最大值为64。

注意：在向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内。

  • 使用SELECT命令查询位字段时，可以用 BIN() 或 HEX() 函数进行读取。
    MySQL基础篇 - 图5
  • 使用b+0查询数据时，可以直接查询出存储的十进制数据的值。
    MySQL基础篇 - 图6

日期与时间类型

日期与时间是重要的信息，在我们的系统中，几乎所有的数据表都用得到。原因是客户需要知道数据的时间标签，从而进行数据查询、统计和处理。

MySQL有多种表示日期和时间的数据类型，不同的版本可能有所差异，MySQL8.0版本支持的日期和时间类型主要有：YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。

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

MySQL基础篇 - 图7

可以看到，不同数据类型表示的时间内容不同、取值范围不同，而且占用的字节数也不一样，你要根据实际需要灵活选取。

为什么时间类型 TIME 的取值范围不是 -23:59:59～23:59:59 呢？原因是 MySQL 设计的 TIME 类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。

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() 函数，会插入当前系统的日期。

插入数据方式：

  1. INSERT INTO test_date1 VALUES ('2020-10-01'), ('20201001'),(20201001); 
  2. INSERT INTO test_date1 VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'), ('99-01-01'), ('990101');
  3. INSERT INTO test_date1 VALUES (000301), (690301), (700301), (990301); 
  4. INSERT INTO test_date1 VALUES (CURRENT_DATE()), (NOW());

TIME类型

TIME类型用来表示时间，不包含日期部分。在MySQL中，需要 3个字节 的存储空间来存储TIME类型的数据，可以使用“HH:MM:SS”格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒。在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式。

  • 可以使用带有冒号的字符串，比如’ 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。
  • 可以使用不带有冒号的字符串或者数字，格式为’ HHMMSS ‘或者 HHMMSS 。如果插入一个不合法的字符串或者数字，MySQL在存
    储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不12:10:00。
  • 使用 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格式。
  • 使用函数 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则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

文本字符串类型

MySQL中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、 LONGTEXT 、 ENUM 、 SET 等类型。

MySQL基础篇 - 图8

CHAR和VARCHAR

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

MySQL基础篇 - 图9

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个字节。

比较

MySQL基础篇 - 图10

  • 情况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类型

在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、 MEDIUMTEXT 和 LONGTEXT 类型。

在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储(在保存和查询数据时，不会删除TEXT类型的数据尾部的空格)，不需要预先定义长度。这一点和VARCHAR类型相同。

每种TEXT类型保存的数据长度和所占用的存储空间不同，如下：

MySQL基础篇 - 图11

由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用CHAR(M)，或者 VARCHAR(M)。

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

MENU类型

ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。

其所需要的存储空间由定义ENUM类型时指定的成员个数决定。

MySQL基础篇 - 图12

  • 当ENUM类型包含1～255个成员时，需要1个字节的存储空间；
  • 当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。
  • ENUM类型的成员个数的上限为65535个。
  1. # 创建表
  2. CREATE TABLE test_enum( season ENUM('春','夏','秋','冬','unknow') );
  3. INSERT INTO test_enum 
  4. VALUES('春'),('秋'); 
  6. # 忽略大小写 
  7. INSERT INTO test_enum 
  8. VALUES('UNKNOW'); 
  10. # 允许按照角标的方式获取指定索引位置的枚举值 
  11. INSERT INTO test_enum 
  12. VALUES('1'),(3); 
  14. # Data truncated for column 'season' at row 1 
  15. INSERT INTO test_enum 
  16. VALUES('ab'); 
  18. # 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的 
  19. INSERT INTO test_enum 
  20. VALUES(NULL);

SET类型

SET表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为 64 。设置字段值时，可以取取值范围内的 0 个或多个值。

当SET类型包含的成员个数不同时，其所占用的存储空间也是不同的，具体如下：

MySQL基础篇 - 图13

SET类型在存储数据时成员个数越多，其占用的存储空间越大。注意：SET类型在选取成员时，可以一次选择多个成员，这一点与ENUM类型不同。

  1. # 创建表
  2. CREATE TABLE temp_mul( 
  3. gender ENUM('男','女'), 
  4. hobby SET('吃饭','睡觉','打豆豆','写代码') 
  5. );
  7. INSERT INTO temp_mul VALUES('男','睡觉,打豆豆'); #成功 
  9. # Data truncated for column 'gender' at row 1 
  10. INSERT INTO temp_mul VALUES('男,女','睡觉,写代码'); #失败 
  12. # Data truncated for column 'gender' at row 1 
  13. INSERT INTO temp_mul VALUES('妖','睡觉,写代码');#失败 
  15. INSERT INTO temp_mul VALUES('男','睡觉,写代码,吃饭'); #成功

二进制字符串类型

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

MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB类型。

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) ，否则报错。

MySQL基础篇 - 图14

  1. CREATE TABLE test_binary1(
  2. f1 BINARY,
  3. f2 BINARY(3), 
  4. # f3 VARBINARY,
  5. f4 VARBINARY(10) 
  6. );

BLOB类型

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

MySQL基础篇 - 图15

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类型的数据更加方便和高效。

  1. CREATE TABLE test_json( 
  2. js json 
  3. );
  5. # 插入JSON数据
  6. INSERT INTO test_json (js) VALUES (
  7. '{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}'
  8. );
  10. # 当需要检索JSON类型的字段中数据的某个具体值时，可以使用“->”和“->>”符号。
  11. SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province' AS province, js -> '$.address.city' AS city
  12. -> FROM test_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,40 40)，点与点之间用逗号分隔，一个点中的经纬度用空格分隔，与POINT格式一致。
    • Polygon，多边形。可以是一个实心平面形，即没有内部边界，也可以有空洞，类似纽扣。最简单的就是只有一个外边界的情况，例如POLYGON((0 0,10 0,10 10, 0 10))。

阿里巴巴开发手册（类型使用）

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

约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。约束是表级的强制规定。可以在创建表时规定约束（通过CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。

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

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

分类

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

  • 单列约束：每个约束只约束一列
  • 多列约束：每个约束可约束多列数据

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

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

根据约束起的作用，约束可分为：

  • NOT NULL 非空约束，规定某个字段不能为空
  • UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
  • PRIMARY KEY 主键(非空且唯一)约束
  • FOREIGN KEY 外键约束
  • CHECK 检查约束 (MySQL不支持check约束，但可以使用check约束，而没有任何效果)
  • DEFAULT 默认值约束

查看约束

  1. # 方式一
  2. # information_schema数据库名（系统库） 
  3. # table_constraints表名称（专门存储各个表的约束） 
  4. SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称'; 
  6. # 方式二
  7. show index from 表名称;

非空约束

限定某个字段/某列的值不允许为空。 关键字：NOT NULL

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

添加非空约束

  • 创建表时添加 

    1. CREATE TABLE 表名称( 
    2. 字段名 数据类型, 
    3. 字段名 数据类型 NOT NULL, 
    4. 字段名 数据类型 NOT NULL 
    5. );

  • 建表后 

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

删除非空约束

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

唯一约束

用来限制某个字段/某列的值不能重复。 关键字：UNIQUE

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

添加唯一约束

  • 建表前
    ```sql create table 表名称( 字段名 数据类型, 字段名 数据类型 unique, 字段名 数据类型 unique key, 字段名 数据类型 );

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

例如

CREATE TABLE USER( id INT NOT NULL, NAME VARCHAR(25), PASSWORD VARCHAR(16),

使用表级约束语法

CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD) );

  2. -  建表后  
  3. ```sql
  4. #字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
  6. #方式1： 
  7. alter table 表名称 add unique key(字段列表);
  9. #方式2：
  10. alter table 表名称 modify 字段名 字段类型 unique;

复合约束

  1. # 创建表
  2. create table student_course( 
  3. id int, 
  4. sid int, 
  5. cid int, 
  6. score int, 
  7. unique key(sid,cid) #复合唯一 
  8. );
  10. # 插入数据
  11. insert into student_course values 
  12. (1, 1, 1001, 89), 
  13. (2, 1, 1002, 90), 
  14. (3, 2, 1001, 88), 
  15. (4, 2, 1002, 56);    #成功
  17. insert into student_course values 
  18. (1, 1, 1001, 89); 
  19. #  Duplicate entry '1-1001' for key 'student_course.sid' 违反sid-cid的复合唯一

删除唯一约束

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

MySQL基础篇 - 图16

  1. ALTER TABLE student_course DROP INDEX 索引名;

PRIMARY KEY 约束

用来唯一标识表中的一行记录。主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。

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

创建PRIMARY KEY约束

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

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

  2. -  建表后  
  3. ```sql
  4. ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); 
  5. #字段列表可以是一个字段，也可以是多个字段，如果是多 个字段的话，是复合主键

复合主键

  1. #选课表 
  2. create table student_course( 
  3. sid int, 
  4. cid int, 
  5. score int, 
  6. primary key(sid,cid) #复合主键 
  7. );
  9. insert into student_course values(1, 1001, 89),(1,1002,90),(2,1001,88),(2,1002,56); # 成功
  11. insert into student_course values(1, 1001, 100); 
  12. # ERROR 1062 (23000): Duplicate entry '1-1001' for key 'PRIMARY'

删除主键索引

  1. alter table 表名称 drop primary key;

说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

AUTO_INCREMENT 约束

某个字段的值自增。 关键字：AUTO_INCREMENT

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

创建自增约束

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

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

  2. -  建表后  
  3. ```sql
  4. alter table 表名称 modify 字段名 数据类型 auto_increment;

删除自增约束

  1. alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

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

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。 下面通过案例来对比不同的版本中自增变量是否持久化。

在MySQL 8.0版本中，测试步骤如下： 创建的数据表中包含自增主键的id字段，语句如下:

  1. # 创建测试表
  2. CREATE table test1(
  3. id INT PRIMARY key AUTO_INCREMENT
  4. )
  6. # 插入数据
  7. INSERT INTO test1 VALUES (0),(0),(0),(0);    # 此时id为 1，2，3，4
  8. SELECT * FROM test1;
  9. DELETE FROM test1 WHERE id = 4;
  10. INSERT INTO test1 VALUES (0);    # 此时id为 1，2，3，5
  12. # 关闭数据库后重新执行
  13. DELETE FROM test1 WHERE id = 5;
  14. INSERT INTO test1 VALUES (0);    # 此时id为 1，2，3，6

可以看出，MySQL 8.0将自增主键的计数器持久化到 重做日志 中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

但为在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个 计数器 来决定的，而该计数器只在 内存中维护 ，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

FOREIGN KEY 约束

限定某个表的某个字段的引用完整性。 关键字：FOREIGN KEY

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

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

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

例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

添加外键约束

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

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

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

(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样

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

  2. -  建表后<br />一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。  
  3. ```sql
  4. alter table emp add foreign key (deptid) references dept(did);

删除外键约束

  1. (1)第一步先查看约束名和删除外键约束 
  2. SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个 
  3. 表的约束名 
  4. ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
  6. （2）第二步查看索引名和删除索引。（注意，只能手动删除） 
  7. SHOW INDEX FROM 表名称; #查看某个表的索引名 
  8. ALTER TABLE 从表名 DROP INDEX 索引名;

开发常见

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

答：不是的

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

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

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

答：没有

外键约束规范

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

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

CHECK约束

检查某个字段的值是否符号xx要求，一般指的是值的范围。 关键字：CHECK

MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警

告，但是MySQL 8.0中可以使用check约束了。

  1. CREATE table test2(
  2. eid int primary key, 
  3. ename varchar(5),
  4. age INT CHECK(age > 20)
  5. )
  6. INSERT INTO test2 VALUES (1,'nihao',10);
  7. # Check constraint 'test2_chk_1' is violated.

DEAFULT约束

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

创建DEFAULT约束

  • 创表前
    ```sql 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(字段名) );

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

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

删除默认值约束

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

面试

  • 为什么建表时，加not null default ‘’ 或 default 0
    不想让表中出现null值。
  • 为什么不想要null的值？
    不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。 效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ‘’ 或 default 0
  • 带AUTO_INCREMENT约束的字段值是从1开始的吗？
    在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。
  • 并不是每个表都可以任意选择存储引擎？
    外键约束（FOREIGN KEY）不能跨引擎使用。MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。

查询

select语句

以employee表为例子，表结构

MySQL基础篇 - 图17

表的部分数据

MySQL基础篇 - 图18

DISTINCT去除重复行

DISTINCT 其实是对后面所有列名的组合进行去重，你能看到最后的结果是 74 条，因为这 74 个部门id不同，都有 salary 这个属性值。如果你想要看都有哪些不同的部门（department_id），只需要写 DISTINCT department_id 即可，后面不需要再加其他的列名了。

可以获取单列的不同数据。

MySQL基础篇 - 图19

空值参与运算

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

由于缺少字段annual_sal，和commission_pct字段为空，所以查询为空。

  1. SELECT employee_id,salary,commission_pct, 12 * salary * (1 + commission_pct) "annual_sal" FROM employees;

MySQL基础篇 - 图20

这里你一定要注意，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长

度是空。而且，在 MySQL 里面，空值是占用空间的。

着重号

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

使用二：可以增加常数字段，如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。

使用二举例：

MySQL基础篇 - 图21

ORDER BY排序

ASC 表示升序，DESC 表示降序。

  • 单列排序

MySQL基础篇 - 图22

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

MySQL基础篇 - 图23

MySQL中使用LIMIT 实现分页

公式：LIMIT [位置偏移量,] 行数

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

MySQL基础篇 - 图24

优化：

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

运算符

在查询过滤数据的时候，经常使用运算符。

算数运算符

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

MySQL基础篇 - 图25

比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

MySQL基础篇 - 图26

非符号型运算符

MySQL基础篇 - 图27

  • IS NOT NULL
    MySQL基础篇 - 图28
  • like运算符
    LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。
    LIKE运算符通常使用如下通配符：
    “%”：匹配0个或多个字符。
    “_”：只能匹配一个字符。
    MySQL基础篇 - 图29
    查询第二个字母为o
    MySQL基础篇 - 图30
  • IN运算符
    IN运算符 IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给
    定的值为NULL，或者IN列表中存在NULL，则结果为NULL。 MySQL基础篇 - 图31
  • ESCAPE
    回避特殊符号的：使用转义符。例如：将[%]转为[MySQL基础篇 - 图32]，然后再加上[ESCAPE‘$’]即可。
    MySQL基础篇 - 图33
  • REGEXP运算符

REGEXP运算符用来匹配字符串，语法格式为： expr REGEXP 匹配条件 。

（1）‘^’匹配以该字符后面的字符开头的字符串。

（2）‘$’匹配以该字符前面的字符结尾的字符串。

（3）‘.’匹配任何一个单字符。

（4）“[…]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

（5）‘’匹配零个或多个在它前面的字符。例如，“x”匹配任何数量的‘x’字符，“[0-9]”匹配任何数量的数字，而“”匹配任何数量的任何字符。

MySQL基础篇 - 图34

MySQL基础篇 - 图35

逻辑运算符

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

MySQL基础篇 - 图36

  • NOT运算符
  • AND 运算符
    MySQL基础篇 - 图37
  • OR 运算符
    MySQL基础篇 - 图38
  • XOR运算符
    逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。
    MySQL基础篇 - 图39

位运算符

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

MySQL基础篇 - 图40

  • &、|、^
    MySQL基础篇 - 图41

运算流程

MySQL基础篇 - 图42

  • << 和 >>
    MySQL基础篇 - 图43
    则是转化位2进制进行移位。

运算符的优先级

MySQL基础篇 - 图44

多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

笛卡尔积错误

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

例如：

两张表总共有数据2889条

MySQL基础篇 - 图45

使用多表查询出现交叉连接（一个员工在所有部门）的问题，此为笛卡尔积错误。

MySQL基础篇 - 图46

错误原因：

  • 省略多个表的连接条件（或关联条件）
  • 连接条件（或关联条件）无效
  • 所有表中的所有行互相连接

解决：

在表相同列加上WHERE子句，写入连接条件

MySQL基础篇 - 图47

等值连接和非等值连接

自连接和非自连接

自连接

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

  • 当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

MySQL基础篇 - 图48

非自连接

  • 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。

MySQL基础篇 - 图49

  1.     此时查出106条记录，有一个员工没有部门，所以不会被查出。
  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。（当题目有需要查询所有 XXX 时，注意一般是外连接）
    • 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
      此时查出107条，没有部门信息的员工也被查出。
      MySQL基础篇 - 图50
    • 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。
      右连接查询，没有员工的部门也被查出。
      MySQL基础篇 - 图51
    • 满外连接
      满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。
      UNION的使用：
      UNION 操作符返回两个查询的结果集的并集，去除重复记录。
      MySQL基础篇 - 图52
      UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
      注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
  • 七种 join 的实现
    MySQL基础篇 - 图53
    ```sql

    中图：内连接 A∩B

    SELECT employee_id,last_name,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

左上图：左外连接

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

右上图：右外连接

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

左中图：A - A∩B

SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id where e.department_id IS NULL;

右中图：B - A∩B

SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IS NULL;

左下图 #左上图 + 右中图

SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id UNION ALL SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IS NULL;

右下图 #左中图 + 右中图

SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IS NULL UNION ALL SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id where e.department_id IS NULL;

  2. <a name="98250245"></a>
  3. ## 子查询
  5. > 子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。
  8. -  语法结构：<br />![](https://gitee.com/ljlGitee001/pictures/raw/master/img/202203091450980.png#crop=0&crop=0&crop=1&crop=1&id=FCk4L&originHeight=262&originWidth=1301&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=) 
  9. -  子查询（内查询）在主查询之前一次执行完成。 
  10. -  子查询的结果被主查询（外查询）使用 。 
  11. -  **注意事项** 
  12.    -  子查询要包含在括号内 
  13.    -  将子查询放在比较条件的右侧 
  14.    -  单行操作符对应单行子查询，多行操作符对应多行子查询 
  16. <a name="d4a3c1ff"></a>
  17. #### 单行子查询
  19. 单行操作符比较
  21. ![](https://gitee.com/ljlGitee001/pictures/raw/master/img/202203091452232.png#crop=0&crop=0&crop=1&crop=1&id=muk2l&originHeight=438&originWidth=1115&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)
  23. -  返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资  
  24. ```sql
  25. SELECT last_name, job_id, salary 
  26. FROM employees 
  27. WHERE job_id = (
  28.     SELECT job_id 
  29.     FROM employees 
  30.     WHERE employee_id = 141
  31. ) AND salary > (
  32.     SELECT salary 
  33.         FROM employees 
  34. WHERE employee_id = 143
  35. );

HAVING中的子查询
  • 首先执行子查询。
  • 向主查询中的HAVING 子句返回结果。
    查询最低工资大于50号部门最低工资的部门id和其最低工资 
    1. SELECT department_id,MIN(salary)
    2. FROM employees
    3. GROUP BY department_id
    4. HAVING MIN(salary) > (
    5.  SELECT MIN(salary)
    6.  FROM employees
    7.  WHERE department_id = 50
    8. );

CASE中的子查询
  • 显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。 
    1. SELECT employee_id, last_name,(
    2.  CASE department_id
    3.  WHEN ( SELECT department_id 
    4.               FROM departments
    5.               WHERE location_id = 1800)
    6.  THEN  'Canada' ELSE 'USA' END) locations
    7. FROM employees;

非法使用子查询

  • 多行子查询使用单行比较符 
    1. SELECT employee_id, last_name FROM employees 
    2. WHERE salary = (
    3. SELECT MIN(salary) 
    4. FROM employees 
    5. GROUP BY department_id
    6. );

多行子查询

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

多行比较操作符

MySQL基础篇 - 图54

ANYk和ALL的使用
  • 返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary
    MySQL基础篇 - 图55
  • 返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
    MySQL基础篇 - 图56
  • 查询平均工资最低的部门id 
    1. SELECT d.department_id
    2. FROM departments d JOIN (
    3. SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) <= ALL
    4. (SELECT AVG(salary) FROM employees GROUP BY department_id)
    5. ) e
    6. ON d.department_id = e.department_id;

相关子查询

相关子查询执行流程

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

  • 相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询
    MySQL基础篇 - 图57 

    1.  说明：**子查询中使用主查询中的列**

相关子查询的使用
  • 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
    方式一：
    MySQL基础篇 - 图58
    方式二：
    from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名， 把它当成一张“临时的虚拟表”来使用。 
    1. SELECT last_name,salary,e1.department_id FROM employees e1,(
    2. SELECT department_id,AVG(salary) dept_avg_sal 
    3. FROM employees 
    4. GROUP BY department_id
    5. ) e2
    6. WHERE e1.`department_id` = e2.department_id 
    7. AND e2.dept_avg_sal < e1.`salary`;

EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

  • 如果在子查询中不存在满足条件的行：
    • 条件返回 FALSE
    • 继续在子查询中查找
  • 如果在子查询中存在满足条件的行：
    • 不在子查询中继续查找
    • 条件返回 TRUE
  • NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。
  • 查询公司管理者的employee_id，last_name，job_id，department_id信息 
    1. SELECT employee_id, last_name, job_id, department_id 
    2. FROM employees e1 
    3. WHERE EXISTS (
    4.  SELECT * 
    5.  FROM employees e2 
    6.  WHERE e2.manager_id = e1.employee_id
    7. );

不相关子查询

子查询没有使用外部查询条件即为不相关子查询。

子查询练习

  1. # 1.查询和Zlotkey相同部门的员工姓名和工资
  2. SELECT e.last_name,e.salary
  3. FROM employees e 
  4. where e.department_id = (
  5. SELECT department_id
  6. FROM employees
  7. WHERE last_name = 'Zlotkey'
  8. )
  10. # 2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
  11. SELECT employee_id,last_name,salary
  12. FROM employees 
  13. WHERE salary > (
  14.     SELECT AVG(salary)
  15.     FROM employees
  16. )
  18. #3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的
  19. #last_name, job_id, salary
  20. SELECT last_name, job_id, salary 
  21. FROM employees
  22. WHERE salary > ALL(
  23. SELECT salary
  24. FROM employees
  25. WHERE job_id = 'SA_MAN'
  26. )
  28. #4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
  29. SELECT employee_id, last_name 
  30. FROM employees 
  31. WHERE department_id = ANY( 
  32. SELECT DISTINCT department_id 
  33. FROM employees 
  34. WHERE last_name LIKE '%u%' 
  35. );
  38. #5.查询在部门的location_id为1700的部门工作的员工的员工号
  39. SELECT employee_id
  40. FROM employees
  41. WHERE department_id IN (
  42. SELECT DISTINCT department_id
  43. FROM departments
  44. WHERE location_id = 1700
  45. );
  47. #6.查询管理者是King的员工姓名和工资
  48. SELECT last_name,salary
  49. FROM employees 
  50. WHERE manager_id IN (
  51. SELECT employee_id
  52. FROM employees 
  53. WHERE last_name = 'King'
  54. );
  56. #7. 查询工资最低的员工信息: last_name, salary
  57. SELECT last_name,salary
  58. FROM employees
  59. WHERE salary = ALL(
  60. SELECT MIN(salary)
  61. FROM employees
  62. );
  64. #8. 查询平均工资最低的部门信息
  65. SELECT d.*
  66. FROM departments d JOIN (
  67. SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) <= ALL
  68. (SELECT AVG(salary) FROM employees GROUP BY department_id)
  69. ) e
  70. ON d.department_id = e.department_id;
  72. #9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
  73. SELECT  d.*,(SELECT AVG(salary) FROM employees 
  74. WHERE d.department_id = employees.department_id) avg
  75. FROM departments d
  76. WHERE d.department_id = (
  77.     SELECT department_id 
  78.     FROM employees
  79.     GROUP BY department_id
  80.     HAVING AVG(salary) <= ALL(
  81.         SELECT AVG(salary) 
  82.         FROM employees
  83.         GROUP BY department_id
  84. )
  85. )
  87. #10.查询平均工资最高的 job 信息
  88. SELECT j.*
  89. FROM jobs j,(
  90. SELECT job_id FROM employees 
  91.     WHERE salary >= ALL(
  92.     SELECT AVG(salary) 
  93.     FROM employees 
  94.     GROUP BY job_id
  95. )
  96. ) e
  97. WHERE j.job_id = e.job_id;
  99. #11. 查询平均工资高于公司平均工资的部门有哪些?
  100. SELECT department_id
  101. FROM employees
  102. WHERE department_id IS NOT NULL
  103. GROUP BY department_id
  104. HAVING AVG(salary) >= ALL(
  105.     SELECT AVG(salary) 
  106.     FROM employees
  107. );
  109. #12. 查询出公司中所有 manager 的详细信息
  110. SELECT m.*
  111. FROM employees m,(SELECT manager_id FROM employees GROUP BY manager_id) e
  112. WHERE m.employee_id = e.manager_id;
  114. # 方式一：使用IN
  115. SELECT m.*
  116. FROM employees m
  117. WHERE m.employee_id IN (
  118.     SELECT DISTINCT manager_id 
  119.     FROM employees
  120. );
  123. # 方式二：使用EXISTS
  124. SELECT m.*
  125. FROM employees m
  126. WHERE EXISTS (
  127.     SELECT *  
  128.     FROM employees e 
  129.     WHERE e.manager_id = m.employee_id
  130. );
  132. # 方式三：自连接
  133. SELECT DISTINCT m.* 
  134. FROM employees e JOIN employees m 
  135. ON e.manager_id = m.employee_id;
  137. #13. 各个部门中 最高工资中最低的那个部门的 最低工资是多少?
  138. # 方式一：多层子查询
  139. SELECT MIN(salary) 
  140. FROM employees
  141. WHERE department_id = (
  142.         SELECT department_id
  143.         FROM employees
  144.         GROUP BY department_id
  145.         HAVING MIN(salary) = (
  146.                     SELECT MIN(max_sal) 
  147.                     FROM (
  148.                             SELECT MAX(salary) max_sal
  149.                             FROM employees
  150.                             GROUP BY department_id
  151.                             ) t_dept_max_sal
  152.                     )
  153. )
  154. # 方式二：使用ALL
  155. SELECT MIN(salary) 
  156. FROM employees
  157. WHERE department_id = (
  158.         SELECT department_id
  159.         FROM employees
  160.         GROUP BY department_id
  161.         HAVING MAX(salary) <= ALL( 
  162.                             SELECT MAX(salary) 
  163.                             FROM employees
  164.                             GROUP BY department_id
  165.                     )
  166.     )
  167. # 方式三：使用分页
  168. SELECT MIN(salary) 
  169. FROM employees
  170. WHERE department_id = (
  171.         SELECT department_id
  172.         FROM employees
  173.         GROUP BY department_id
  174.         HAVING MAX(salary) = ( 
  175.                             SELECT MAX(salary) max_sal
  176.                             FROM employees
  177.                             GROUP BY department_id
  178.                             ORDER BY max_sal ASC
  179.                             LIMIT 0,1
  180.                     )
  181.     )
  182. # 方式四：虚构一张表
  183. SELECT MIN(salary)
  184. FROM employees e JOIN (
  185.         SELECT department_id
  186.         FROM employees 
  187.         GROUP BY department_id
  188.         HAVING MAX(salary) = ( 
  189.                             SELECT MAX(salary) max_sal
  190.                             FROM employees
  191.                             GROUP BY department_id
  192.                             ORDER BY max_sal ASC
  193.                             LIMIT 0,1
  194.                     )
  195.     ) t_dept_max_sal
  196. ON e.department_id = t_dept_max_sal.department_id
  198. #14. 查询平均工资最高的部门的 manager 的详细信息: 
  199. #last_name, department_id, email, salary
  200. SELECT m.last_name, m.department_id, m.email, m.salary
  201. FROM employees m WHERE
  202. m.employee_id IN 
  203.  (
  204.     SELECT DISTINCT manager_id
  205.     FROM employees
  206.     WHERE department_id = (
  207.         SELECT department_id
  208.         FROM employees 
  209.         GROUP BY  department_id 
  210.         HAVING     AVG(salary) >= ALL(
  211.             SELECT AVG(salary) 
  212.             FROM employees
  213.             GROUP BY  department_id 
  214.         )
  215.     ) 
  216. )
  218. #15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
  219. # 方式一：
  220. SELECT department_id
  221. FROM departments
  222. WHERE department_id NOT IN (
  223.     SELECT DISTINCT department_id
  224.     FROM employees
  225.     WHERE job_id IN ('ST_CLERK')
  226. )
  227. # 方式二：使用NOT EXISTS
  228. SELECT department_id
  229. FROM departments d
  230. WHERE NOT EXISTS (
  231.     SELECT *
  232.     FROM employees e
  233.     WHERE d.department_id = e.department_id
  234.     AND job_id IN ('ST_CLERK')
  235. )
  237. #16. 选择所有没有管理者的员工的last_name
  238. # 方式一：
  239. SELECT last_name 
  240. FROM employees
  241. WHERE employee_id  IN (
  242.     SELECT employee_id 
  243.     FROM employees 
  244.     WHERE manager_id IS NULL
  245. )
  246. # 方式二：使用NOT EXISTS
  247. SELECT last_name 
  248. FROM employees e1 
  249. WHERE NOT EXISTS ( 
  250.     SELECT * FROM employees e2 
  251.     WHERE e1.manager_id = e2.employee_id 
  252. );
  254. #17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
  255. # 方式一：
  256. SELECT employee_id, last_name, hire_date, salary
  257. FROM employees 
  258. WHERE manager_id IN (
  259.         SELECT employee_id
  260.         FROM employees 
  261.         WHERE last_name = 'De Haan'
  262. )
  263. # 方式二：使用EXISTS
  264. SELECT employee_id, last_name, hire_date, salary
  265. FROM employees e1 
  266. WHERE  EXISTS ( 
  267.     SELECT * FROM employees e2 
  268.     WHERE e1.manager_id = e2.employee_id  
  269.     AND e2.last_name = 'De Haan'
  270. );
  273. #18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(难)
  274. # 方式一：相关子查询
  275. SELECT employee_id,last_name,salary
  276. FROM employees e1
  277. WHERE salary > (
  278.     SELECT AVG(salary) 
  279.     FROM employees e2
  280.     GROUP BY department_id
  281.     HAVING e1.department_id = e2.department_id
  282. ) 
  283. # 方式二：
  284. SELECT employee_id,last_name,salary
  285. FROM employees e1, (
  286.     SELECT AVG(salary) avg_sal,department_id
  287.     FROM employees 
  288.     GROUP BY department_id
  289. )  dept_avg_sal
  290. WHERE e1.department_id = dept_avg_sal.department_id
  291. AND e1.salary > dept_avg_sal.avg_sal;
  293. #19.查询每个部门下的部门人数大于 5 的部门名称
  294. SELECT department_name
  295. FROM departments d
  296. WHERE 5 < (
  297.     SELECT count(*) 
  298.     FROM employees e
  299.     WHERE e.department_id = d.department_id
  300.     )
  302. #20.查询每个国家下的部门个数大于 2 的国家编号
  303. SELECT country_id
  304. FROM locations l
  305. WHERE 2 < (
  306.     SELECT count(*) 
  307.     FROM departments d
  308.     WHERE l.location_id = d.location_id
  309. )

函数

单行函数

MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类： 单行函数 、 聚合函数（或分组函数） 。

MySQL基础篇 - 图59

单行函数

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

数值函数

基本函数

MySQL基础篇 - 图60

转化弧度函数

MySQL基础篇 - 图61

三角函数

MySQL基础篇 - 图62

指数和对数

MySQL基础篇 - 图63

进制之间的转换

MySQL基础篇 - 图64

字符串函数

基本函数

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

MySQL基础篇 - 图65

MySQL基础篇 - 图66

日期和时间函数

获取日期和时间

MySQL基础篇 - 图67

日期和时间戳的转换

MySQL基础篇 - 图68

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

MySQL基础篇 - 图69

返回日期的数据

MySQL基础篇 - 图70

type取值如下：

MySQL基础篇 - 图71

时间和秒钟转换的函数

MySQL基础篇 - 图72

计算日期和时间的函数

第一组：

MySQL基础篇 - 图73

type取值如下：

MySQL基础篇 - 图74

第二组：

MySQL基础篇 - 图75

日期时间的格式化

MySQL基础篇 - 图76

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

MySQL基础篇 - 图77

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

MySQL基础篇 - 图78

流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。

MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

MySQL基础篇 - 图79

加密与加密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。

MySQL基础篇 - 图80

可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。

MySQL信息函数

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

MySQL基础篇 - 图81

其他函数

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

MySQL基础篇 - 图82

聚合函数（多行函数）

聚合函数作用于一组数据，并对一组数据返回一个值。以下列出常用的聚合函数。

AVG和SUM函数

计算列的平均值和总和

MySQL基础篇 - 图83

注意：AVG / SUM ：只适用于数值类型的字段（或变量）

MIN和MAX函数

计算列的最小值和最大值

MySQL基础篇 - 图84

注意：MAX / MIN :适用于数值类型、字符串类型、日期时间类型的字段（或变量）

COUNT

  • COUNT()返回表中记录总数，适用于*任意数据类型
  • COUNT(expr) 返回expr不为空的记录总数。
    MySQL基础篇 - 图85
  • 注意COUNT（列名）统计的是非空的字段，需要根据需求编写sql，例如统计全国人名的平均工资，为空的也要算上，或者直接使用COUNT(*)
    ```sql

    需求：查询公司中平均奖金率

    错误的！

    SELECT AVG(commission_pct) FROM employees;

正确的：

SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)), AVG(IFNULL(commission_pct,0)) FROM employees;


**优化**：

-  用count(*)，count(1)，count(列名)谁好呢?<br />其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。 
-  能不能使用count(列名)替换count(*)?<br />不要使用 count(列名)来替代 count(_) ， count(_) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。<br />说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。 

<a name="9b46252c"></a>
#### GROUP BY

将查询的数据进行分组，逗号隔开可以使用多列分组

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

明确：WHERE一定放在FROM后面

  • 在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中
  • 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

GROUP BY中使用WITH ROLLUP

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

MySQL基础篇 - 图86

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

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

MySQL基础篇 - 图87

注意：非法使用聚合函数 ： 不能在 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 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

MySQL基础篇 - 图88

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

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执行顺序
    FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
    例如： 
    SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5 
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1 
WHERE height > 1.80 # 顺序 2 
GROUP BY player.team_id # 顺序 3 
HAVING num > 2 # 顺序 4 
ORDER BY num DESC # 顺序 6 
LIMIT 2 # 顺序 7

SQL执行原理

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 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。

增删改

插入数据

单行插入

  • 方式一： 

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

  • 方式二：指定列进行添加 

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

多行插入

  • 方式一： 

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

  • 方式三：根据查询返回的数据进行添加

    • 在 INSERT 语句中加入子查询。
    • 不必书写 VALUES 子句。
    • 子查询中的值列表应与 INSERT 子句中的列名对应。 
      INSERT INTO 目标表名 (
tar_column1 [, tar_column2, …, tar_columnn])
SELECT (src_column1 [, src_column2, …, src_columnn]) 
FROM 源表名 
[WHERE condition]
      举例： 
      INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct 
FROM employees 
WHERE job_id LIKE '%REP%';

Mysql8.0新增特性：计算列

简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

CREATE TABLE tb1( 
id INT, 
a INT, 
b INT, 
c INT GENERATED ALWAYS AS (a + b) VIRTUAL 
);

更新数据

  • 可以一次更新多条数据。
  • 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
UPDATE table_name 
SET column1=value1, column2=value2, … , column=valuen 
[WHERE condition]

注意使用WHERE来选择更新的数据，不使用WHERE则会全部更新。

删除数据

table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。

DELETE FROM table_name [WHERE <condition>];

视图

简介

常见的数据库对象

MySQL基础篇 - 图89

  • 视图是一种 虚拟表 ，本身是 不具有数据 的，占用很少的内存空间，它是 SQL 中的一个重要概念。
  • 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
  • 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和
  • 修改操作时，数据表中的数据会相应地发生变化，反之亦然。
  • 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句
    • 在数据库中，视图不会保存数据，数据真正保存在数据表中。当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化；反之亦然。
  • 视图，是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。

简而言之，视图相当于在已有的表上创建的一张虚拟表，不同于直接复制一张表，修改视图的信息在基表上也会发生修改，可以理解为存储起来的select语句，可以将经常查询的数据放在视图中，提升效率。

创建视图

  • 复杂版 

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

  • 精简版 

    CREATE VIEW 视图名 AS 查询语句

单表视图

可以根据select的别名创建视图

MySQL基础篇 - 图90

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

MySQL基础篇 - 图91

多表联合视图

CREATE VIEW emp_dep_view 
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;

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

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

MySQL基础篇 - 图92

基于视图创建视图

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

举例：联合“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;

查看视图

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

    SHOW TABLES;

  • 语法2：查看视图的结构 

    DESC/DESCRIBE 视图名;

  • 语法3：查看视图的属性信息
    执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。 

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

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

    SHOW CREATE VIEW 视图名称;

更新视图

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

不可更新视图

要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如

下情况时，视图不支持更新操作：

  • 在定义视图的时候指定了“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；
  • 视图定义基于一个 不可更新视图 ；
  • 常量视图。

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

修改删除视图

  • 修改视图
    ```sql

    方式一

    CREATE OR REPLACE VIEW 字句;

方式二

ALTER VIEW 视图名称 AS 查询语句;


-  删除视图只是删除视图的定义，并不会删除基表的数据 <br />说明：基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这样的视图c需要手动删除或修改，否则影响使用。 
```sql
DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;

视图的优缺点

优点

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

缺点

视图过多可读性差，维护成本高。

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

存储过程和函数

存储过程

概述

含义：存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过 预先编译 的 SQL 语句的封装。

执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

好处：

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

和视图、函数的对比：

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是 虚拟表 ，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以 直接操作底层数据表 ，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是 没有返回值 的

分类

根据参数类型分类：

  • 无参数
  • IN参数（有参数无返回）
  • OUT参数（无参数，有返回）
  • IN，OUT（有参数有返回）
  • 带 INOUT（有参数有返回）

创建存储过程

语法：

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的转义字符。

示例：

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

创建存储过程实例：

  • 创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名。 

    DELIMITER $
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20)) 
BEGIN
 SELECT salary FROM employees WHERE employee_id = empname;
END $
DELIMITER ;

  • 创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出 

    DELIMITER // 
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE) 
BEGIN
SELECT MIN(salary) INTO ms FROM emps; 
END // 
DELIMITER ;

  • 创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。 

    DELIMITER // 
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE) 
BEGIN
SELECT salary INTO empsalary FROM emps WHERE ename = empname; 
END // 
DELIMITER ;

  • 创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名。 

    DELIMITER // 
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20)) 
BEGIN
SELECT ename INTO empname FROM emps 
WHERE eid = (SELECT MID FROM emps WHERE ename=empname); 
END // 
DELIMITER ;

调用格式

  • 调用IN参数 

    CALL 存储过程名(实参列表)

  • 调用OUT参数 

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

  • 调用INOUT参数 

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

存储函数

前面学习了很多函数，使用这些函数可以对数据进行的各种处理操作，极大地提高用户对数据库的管理效率。MySQL支持自定义函数，定义好之后，调用方式与调用MySQL预定义的系统函数一样

创建存储函数

语法：

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。

创建存储函数实例：

  • 创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。
    调用： 

    DELIMITER $
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
LANGUAGE SQL 
NOT DETERMINISTIC 
READS SQL DATA 
SQL SECURITY DEFINER 
COMMENT '查询部门平均工资'
BEGIN
 RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END $
DELIMITER ;

    SET @dept_id = 50;
SELECT count_by_id(@dept_id);



调用存储函数

SELECT 函数名(实参列表)

对比

MySQL基础篇 - 图94此外，存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等)和事务操作，这些功能是存储函数不具备的。

存储过程和函数的查看、修改、删除

查看

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

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

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

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

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

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

修改

  • 修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。
    其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。
    CONTAINS SQL ，表示子程序包含SQL语句，但不包含读或写数据的语句。
    NO SQL ，表示子程序中不包含SQL语句。
    READS SQL DATA ，表示子程序中包含读数据的语句。
    MODIFIES SQL DATA ，表示子程序中包含写数据的语句。
    SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。
    DEFINER ，表示只有定义者自己才能够执行。
    INVOKER ，表示调用者可以执行。
    COMMENT ‘string’ ，表示注释信息。 
    ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

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

删除

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

优点

  • 存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。
  • 可以减少开发工作量。将代码 封装 成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以 重复使用 ，在减少开发工作量的同时，还能保证代码的结构清晰。
  • 存储过程的安全性强。我们在设定存储过程的时候可以 设置对用户的使用权限 ，这样就和视图一样具有较强的安全性。
  • 可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。
  • 良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要 连接一次即可 。

缺点

  • 可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
  • 调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
  • 存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
  • 它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护， 增加数据库的压力 ，显然就不适用了。

触发器

概述

触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会 自动 激发触发器执行相应的操作。当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

创建触发器

CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 
触发器执行的语句块;
  • 表名 ：表示触发器监控的对象。
  • BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
  • INSERT|UPDATE|DELETE ：表示触发的事件。
    • INSERT 表示插入记录时触发；
    • UPDATE 表示更新记录时触发；
    • DELETE 表示删除记录时触发。
  • 触发器执行的语句块 ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。

实例：

  • 创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向test_trigger_log数据表中插入before_insert的日志信息。
    MySQL基础篇 - 图95 

    DELIMITER $
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN 
 INSERT INTO test_trigger_log(t_log) VALUES('before_insert');
END $
DELIMITER ;

  • 定义触发器“salary_check_trigger”，基于员工表“employees”的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报sqlstate_value为’HY000’的错误，从而使得添加失败。
    上面触发器声明过程中的NEW关键字代表INSERT添加语句的新记录。 

    DELIMITER // 
CREATE TRIGGER salary_check_trigger 
BEFORE INSERT ON employees FOR EACH ROW 
BEGIN
DECLARE mgrsalary DOUBLE; 
SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id; 
IF NEW.salary > mgrsalary THEN 
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误'; 
END IF; 
END // 
DELIMITER ;

查看触发器

  • 查看当前数据库的所有触发器的定义 

    SHOW TRIGGERS;

  • 查看当前数据库中某个触发器的定义 

    SHOW CREATE TRIGGER 触发器名;

  • 从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息 

    SELECT * FROM information_schema.TRIGGERS;

删除触发器

DROP TRIGGER IF EXISTS 触发器名;

优点

  • 触发器可以确保数据的完整性
  • 触发器可以帮助我们记录操作日志
  • 触发器还可以用在操作数据前，对数据进行合法性检查

缺点

  • 触发器最大的一个问题就是可读性差
    因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能 不受应用层的控制 。这对系统维护是非常有挑战的。
    而且触发器是隐藏的，插入数据不规范可能也会报错，但触发器可读性差，耗费维护。
  • 相关数据的变更，可能会导致触发器出错
    特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。

注意

使用外键约束时，主表修改删除新增导致子表修改删除新增时，不会触发子表的触发器。