mysql学习笔记

最新推荐文章于 2024-06-28 15:42:05 发布
最新推荐文章于 2024-06-28 15:42:05 发布
阅读量2k 收藏 8
点赞数 1
文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45027619/article/details/124536792
版权

mysql

1 mysql的环境配置

1.1 服务的启动与停止

1.1.1 方式1：使用图形界面工具

步骤1：打开windows服务
方式1：计算机（点击鼠标右键）→ 管理（点击）→ 服务和应用程序（点击）→ 服务（点
击）
方式2：控制面板（点击）→ 系统和安全（点击）→ 管理工具（点击）→ 服务（点击）
方式3：任务栏（点击鼠标右键）→ 启动任务管理器（点击）→ 服务（点击）
方式4：单击【开始】菜单，在搜索框中输入“services.msc”，按Enter键确认
步骤2：找到MySQL80（点击鼠标右键）→ 启动或停止（点击）
在这里插入图片描述

1.1.2 方式2 使用命令行指令开启

  1. 启动服务 net start MySQL服务名
  2. 停止服务 net stop MySQL服务名

注意：如果当你输入命令后，提示“拒绝服务”，请以 系统管理员身份 打开命令提示符界面重新尝试。

1.2 登录与退出

1.2.1 使用windows命令行

1.2.1.1 登录

格式：

mysql -h主机名 -P端口号 -u用户名 -p
Enter password:****

举例：

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

注意：
密码建议在下一行输入，保证安全；
如果是登录本机的mysql，-h主机名可省略；
如果端口号为默认的3306，-P端口号可省略；
P是大小，其余都是小写；
除了-p密码之间不能有空格，其余都可以有空格；
登录成功后，可以使用mysql --version来查看当前版本号；
登录成功后，输入指令前每行都会有mysql> ，表示是在操作mysql，如果退出cmd，也会退出登录；
在这里插入图片描述

1.2.1.1 退出
exit 或quit

在这里插入图片描述

1.2.2 MySQL图形化管理工具

1.2.2.1 SQLyog
1.2.2.1.1 可能出现连接问题

有些图形界面工具，特别是旧版本的图形界面工具，在连接MySQL8时出现“Authentication plugin ‘caching_sha2_password’ cannot be loaded”错误。
在这里插入图片描述
解决方案
出现这个原因是MySQL8之前的版本中加密规则是mysql_native_password，而在MySQL8之后，加密规则
是caching_sha2_password。解决问题方法有两种，第一种是升级图形界面工具版本，第二种是把MySQL8
用户登录密码加密规则还原成mysql_native_password。
第二种解决方案如下，用命令行登录MySQL数据库之后，执行如下命令修改用户密码加密规则并更新用
户密码，这里修改用户名为“root@localhost”的用户密码规则为“mysql_native_password”，密码值为
“123456”，如图所示。

指令：

#使用mysql数据库 
USE mysql;

 #修改'root'@'localhost'用户的密码规则和密码
 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123';
 
 #刷新权限 
 FLUSH PRIVILEGES;

具体操作
在这里插入图片描述

1.3 常见问题的解决

问题1：root用户密码忘记，重置的操作

1: 通过任务管理器或者服务管理，关掉mysqld(服务进程)

2: 通过命令行+特殊参数开启mysqld mysqld – defaults-file=“D:\ProgramFiles\mysql\MySQLServer5.7Data\my.ini” --skip-grant-tables

3: 此时，mysqld服务进程已经打开。并且不需要权限检查

4: mysql -uroot 无密码登陆服务器。另启动一个客户端进行

5: 修改权限表 （1） use mysql; （2）update user set authentication_string=password(‘新密码’) where user=‘root’ and Host=‘localhost’; （3）flush privileges;

6: 通过任务管理器，关掉mysqld服务进程。

7: 再次通过服务管理，打开mysql服务。

8: 即可用修改后的新密码登陆。

问题2：mysql命令报“不是内部或外部命令”

如果输入mysql命令报“不是内部或外部命令”，把mysql安装目录的bin目录配置到环境变量path中。

问题3： No database selected

解决方案一：就是使用“USE 数据库名;”语句，这样接下来的语句就默认针对这个数据库进行操作

解决方案二：就是所有的表对象前面都加上“数据库.”

问题4：命令行客户端的字符集问题

mysql> INSERT INTO t_stu VALUES(1,'张三','男');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1

查看所有字符集：SHOW VARIABLES LIKE 'character_set_%';

解决方案，设置当前连接的客户端字符集 SET NAMES GBK;

2 基本的SELECT语句

2.1 sql概述

  • SQL（Structured Query Language，结构化查询语言）是使用关系模型的数据库应用语言；
  • 不同的数据库生产厂商都支持SQL语句，但都有特有内容；
    在这里插入图片描述

2.2 sql分类

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

  • create 创建
  • drop 删除
  • truncate 先删除表结构再重新创建一张空表
  • alter 修改
  • rename 重命名

2.2.2 DML（Data Manipulation Language、数据操作语言）增删改查

  • insert 插入
  • delete 删除
  • update 更改
  • select 查询（重点）

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

  • GRANT
  • REVOKE
  • COMMIT 提交
  • ROLLBACK 回退
  • SAVEPOINT

2.2.4 细节

因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。还有单独将 COMMIT 、 ROLLBACK 取出来称为TCL （Transaction Control Language，事务控制语言）。

2.3 SQL语言的规则与规范

2.3.1 基本规则

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

2.3.2 SQL大小写规范 （建议遵守）

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

2.3.3 注释

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

2.3.4 命名规则（暂时了解）

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

2.3.5 数据导入指令

2.3.5.1 在命令行客户端登录mysql，使用source指令导入
mysql> source d:\mysqldb.sql
2.3.5.2 使用图形化界面导入

以SQLyog为例：
在这里插入图片描述
在这里插入图片描述

2.4 基本的SELECT语句

2.4.1 SELECT…

SELECT 1; #没有任何子句
SELECT 1 + 1,3 * 2
FROM DUAL; #dual：伪表

2.4.2 SELECT … FROM

  • 语法：
SELECT 标识选择哪些列
FROM 标识从哪个表中选择
  • 选择全部列：
SELECT * 
FROM departments;
  • 选择特定的列：
SELECT department_id, location_id
FROM departments;

2.4.3 列的别名

  • 重命名一个列， 便于计算
  • 紧跟列名，也可以在列名和别名之间加入关键字AS，AS 可以省略
  • 如果别名中包含空格或特殊的字符并区分大小写，别名需要使用双引号
  • 列的别名只能再order by中使用，不能在where中使用
SELECT last_name AS name, commission_pct comm 
FROM employees;

SELECT last_name "Name", salary*12 "Annual Salary"
FROM employees;

2.4.4 去除重复行

格式：
SELECT DISTINCT 字段名1,字段名2 FROM 表名;
注意：
默认情况下，查询的结果集不会自动去重；
DISTINCT 需要放到所有字段名前面；
DISTINCT 是对其后面所有字段的组合进行去重，就是说两条数据的所有字段都一样才会去重；

2.4.5 空值null参与运算

  1. 空值参与计算，结果也是空值
SELECT employee_id id, salary AS 月工资, salary * (1+commission_pct) * 12 总工资,commission_pct 
FROM employees;

在这里插入图片描述

  1. 实际问题的解决方案是引入IFNULL
    IFNULL:如果字段是null，则用第二个参数代替；否则仍使用字段值。
SELECT employee_id id, salary AS 月工资, salary * (1+IFNULL(commission_pct,0)) * 12 总工资,commission_pct 
FROM employees;

2.4.6 着重号

  1. 我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在
    SQL语句中使用一对``（着重号）引起来。
  2. 着重号不是单引号，而是数字键1左边的键位符号。
SELECT * FROM ORDER; #错误
SELECT * FROM `order`; #正确

2.4.7 查询常数

  1. 查询常数是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。
  2. 作用：SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。
# 增加常数列学校，字段值是ysu
SELECT 'ysu' AS 学校 ,employee_id id, salary AS 月工资
FROM employees;

在这里插入图片描述

2.5 显示表结构

  • 语法
显示表结构:显示表中字段的详细信息，两种方式结果相同。
DESCRIBE employees;
DESC employees;
  • 举例
    在这里插入图片描述

2.6 过滤数据

  • 语法
    • 使用WHERE 子句，将不满足条件的行过滤掉
    • WHERE子句紧随FROM子句
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件
  • 举例
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;

3 运算符

3.1 算数运算符

在这里插入图片描述

  • 对于算数运算符来说，任意一侧为null，结果都是null；

3.1.1 加减运算符

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

-- ab不能转为数值，所以看作0
SELECT 1 + 'ab' 
FROM DUAL; # 1

3.1.2 乘除运算符

  • 一个整数类型的值对整数进行乘法操作，结果仍是整数；
  • 一个数对整数进行除法操作，无论是否可以整除，结果都是浮点数，保留到小数点后4位；
  • 一个数对浮点数进行乘法或除法操作，结果是浮点数；
  • 在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。

3.1.3 取模运算符

  • 结果的符号与被模数的符号一致；

3.2 比较运算符

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

运算符名称作用示例
=等于运算符判断两个值、字符串或表达式是否相等SELECT C FROM TABLE WHERE A = B
<=>安全等于运算符安全地判断两个值、字符串或表达式是否相等, 可以对null判断SELECT C FROM TABLE WHERE A <=> B
<> 或 !=不等于运算符判断两个值、字符串或表达式是否不相等SELECT C FROM TABLE WHERE A <> B
<小于运算符判断前面的值、字符串或表达式是否小于后面的值、字符串或表达式SELECT C FROM TABLE WHERE A < B
<=小于等于运算符判断前面的值、字符串或表达式是否小于等于后面的值、字符串或表达式SELECT C FROM TABLE WHERE A <= B
>大于运算符判断前面的值、字符串或表达式是否大于后面的值、字符串或表达式SELECT C FROM TABLE WHERE A > B
>=大于等于运算符判断前面的值、字符串或表达式是否大于等于后面的值、字符串或表达式SELECT C FROM TABLE WHERE A >= B
运算符名称作用示例
IS NULL为空运算符判断值、字符串或表达式是否为空SELECT B FROM TABLE WHERE A IS NULL
IS NOTNULL不为空运算符判断值、字符串或表达式是否不为空SELECT B FROM TABLE WHERE A IS NOT NULL
LEAST最小值运算符在多个值中返回最小值SELECT D FROM TABLE WHERE C LEAST(A, B)
GREATEST最大值运算符在多个值中返回最大值SELECT D FROM TABLE WHERE C GREATEST(A, B)
BETWEEN … AND …两值之间的运算符判断一个值是否在两个值之间SELECT D FROM TABLE WHERE C BETWEEN A AND B
ISNULL为空运算符判断一个值、字符串或表达式是否为空SELECT B FROM TABLE WHERE ISNULL(A)
IN属于运算符判断一个值是否为列表中的任意一个值SELECT D FROM TABLE WHERE C IN(A, B)
NOT IN不属于运算符判断一个值是否不是一个列表中的任意一个值SELECT D FROM TABLE WHERE C NOT IN(A, B)
LIKE模糊匹配运算符判断一个值是否符合模糊匹配规则SELECT C FROM TABLE WHERE A LIKE B
REGEXP正则表达式运算符判断一个值是否符合正则表达式的规则SELECT C FROM TABLE WHERE A REGEXPB
RLIKE正则表达式运算符判断一个值是否符合正则表达式的规则SELECT C FROM TABLE WHERE A RLIKEB

3.2.1 等号运算符 =

  • 等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。
    • 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
    • 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
    • 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较，如果字符串不能转为整数，则转为0。
    • 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
SELECT 1 = '1',1 = 1, 'a' = 0, 'a' = 'a',NULL = NULL,0 = NULL
FROM DUAL;

在这里插入图片描述

3.2.2 安全等于 <=> (为NULL而生)

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

SELECT NULL <=> NULL,0 <=> NULL
FROM DUAL;

在这里插入图片描述

3.2.3 不等于运算符 <> 或 !=

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

3.2.4 空运算符 is null、isnull(字段)

判断一个值是否为NULL，如果为NULL则返回1，否则返回0。和A <=> null作用相同。

3.2.5 非空运算符 is not null

判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。

SELECT * 
FROM employees
WHERE commission_pct IS NOT NULL;
#或
SELECT * 
FROM employees
WHERE NOT commission_pct <=> NULL;

3.2.6 最小值运算符

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

  • 当参数是整数或者浮点数时，LEAST将返回其中最小的值；
  • 当参数为字符串时，从前往后逐个比较各字符，返回字母表中顺序最靠前的字符；
  • 当比较值列表中有NULL时，不能判断大小，返回值为NULL。

3.2.7 最大值运算符

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

  • 当参数中是整数或者浮点数时，GREATEST将返回其中最大的值；
  • 当参数为字符串时，返回字母表中顺序最靠后的字符；
  • 当比较值列表中有NULL时，不能判断大小，返回值为NULL。

3.2.8 BETWEEN AND运算符 （连续）

格式通常为SELECT D FROM TABLE WHERE C BETWEEN A AND B，此时，当C大于等于A，并且C小于等于B时，结果为1，否则结果为0。
如果想要表示不在某个范围内SELECT D FROM TABLE WHERE C NOT BETWEEN A AND B。

3.2.9 IN、NOT IN运算符 （离散）

IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。
NOT IN运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一个值，则返回1，否则返回0。

-- 查询部门号是20、30、40的员工
方式1：
SELECT * 
FROM employees
WHERE department_id IN (20,30,40);
方式2：
SELECT * 
FROM employees
WHERE department_id = 20 OR department_id = 30 OR department_id = 40;

-- 查询部门号不是20、30、40的员工
SELECT * 
FROM employees
WHERE department_id NOT IN (20,30,40);

3.2.10 LIKE运算符（模糊匹配）

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

3.2.10.1 通配符
“%”：匹配0个或多个字符。
 “_”：只能匹配一个字符。
 “\” :默认的转义字符，也可以自己指定（见下方例子）。
3.2.10.2 转义字符

ESCAPE

  • 回避特殊符号的：使用转义符。
  • 如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。
#练习：查询第2个字符是_且第3个字符是'a'的员工信息
#需要使用转义字符: \ 
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';
#或者  (了解)
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';
3.2.10.3 例子
-- 查询员工姓名中包含a的
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';

-- 查询员工姓名中第二个字符是a
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';

-- 查询员工姓名中即包含a也包含e的，两种方式都可
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

3.2.11 REGEXP运算符(正则表达式)

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

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

以e结尾
SELECT *
FROM employees 
WHERE first_name REGEXP 'e$';

3.3 逻辑运算符

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

运算符作用示例
NOT 或 !逻辑非SELECT NOT A
AND 或 &&逻辑与SELECT A AND B
OR 或 ||逻辑或SELECT A OR B
XOR逻辑异或SELECT A XOR B

3.3.1 逻辑非运算符 NOT或!

表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。

3.3.2 逻辑与运算符 AND或&&

当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。

3.3.3 逻辑或运算符 OR或 ||

当给定的值都不为NULL，并且任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL。

注意：OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

3.3.4 逻辑异或运算符 XOR

当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。(相同为0，不同为1)

3.4 位运算符

运算符作用示例
&按位与（位AND）SELECT A & B
|按位或（位OR）SELECT A | B
^按位异或（为XOR）SELECT A ^ B
~按位取反SELECT ~A
>>按位右移SELECT A >> 2
<<按位左移SELECT B << 2

按位与运算符

按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二进制位的数值都为1时，则该位返回1，否则返回0。

按位或运算符

按位或（|）运算符将给定的值对应的二进制数逐位进行逻辑或运算。当给定值对应的二进制位的数值有一个或两个为1时，则该位返回1，否则返回0。

按位取反运算符

按位取反（~）运算符将给定的值的二进制数逐位进行取反操作，即将1变为0，将0变为1。

按位右移运算符

按位右移（>>）运算符将给定的值的二进制数的所有位右移指定的位数。右移指定的位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐。

按位左移运算符

按位左移（<<）运算符将给定的值的二进制数的所有位左移指定的位数。左移指定的位数后，左边高位的数值被移出并丢弃，右边低位空出的位置用0补齐。

3.5 运算符的优先级

  • 一般来说，我们使用运算符的时候可以通过使用括号来更清楚的表示运算的顺序。
    在这里插入图片描述

4 排序与分页

4.1 ORDER BY 数据排序

4.1.1 排序规则

  • 使用order by子句排序
    • Asc表示升序；（不写asc和desc情况下默认为升序）
    • desc表示降序；
    • ORDER BY子句在SELECT语句的结尾；
    • ORDER BY后面的字段可以是别名；
    • 没有order by排序的情况下，默认是以插入的顺序排列；

4.1.2 单列排序

只对一个字段进行排序。

SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary;

4.1.3 多列排序

  • 对第一个字段排序后，此字段中相同的值仍然是按照插入的顺序进行排列的，所以对于相同的值，我们需要按照其余字段进行排序。
  • 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
先按department_id 进行排序，department_id 相同时，再按salary进行排序。
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;

4.2 LIMIT 分页

4.2.1 分页规则

  • 使用limit 子句进行分页
    • 格式：limit 偏移量，显示条数；
    • limit 在查询排序之后才会执行，所以LIMIT 子句必须放在整个SELECT语句的最后！
    • 分页显示公式：limit (当前页数 - 1) * 每页显示条数，每页显示条数；

4.2.2 举例

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

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

-- 只取1条记录
 SELECT * FROM 表名 LIMIT 1；

4.2.3 使用 LIMIT 的好处

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

5 多表查询

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

5.1 为什么需要拆分成多张表

  1. 减少数据冗余；
  2. 提高IO效率；
  3. 降低维护难度；
  4. 提高并发效率(一张表在操作过程中会被锁定，其余事务就无法操作)；

5.2 笛卡尔积（或交叉连接）的理解

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

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

#查询员工姓名和所在部门名称 
SELECT last_name,department_name FROM employees,departments; 
SELECT last_name,department_name FROM employees CROSS JOIN departments;
  • 笛卡尔积的错误会在下面条件下产生：
    • 省略多个表的连接条件（或关联条件）
    • 连接条件（或关联条件）无效
    • 所有表中的所有行互相连接
  • 为了避免笛卡尔积， 需要在WHERE或ON中加入有效的连接条件

5.3 相同列名 & 表的别名

  • 多个表中有相同列时，必须在列名之前加上表名前缀。
  • 从sql优化角度来看，多表查询时，查询语句中的每个字段建议都加上表名前缀。
  • 使用表的别名可以简化查询，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

5.4 连接条件的个数

如果有n张表实现了多表查询，那么至少需要n-1个连接条件。
在这里插入图片描述

5.5 多表查询分类

5.5.1 分类1：等值连接 vs 非等值连接

  • 等值连接就是where子句中使用等号连接；
  • 非等值连接是where子句中不是使用等号连接；
5.5.1.1 非等值连接例子
-- 查询员工的工资等级
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

5.5.2 分类2：自连接 vs 非自连接

  • 自连接：一张表进行连接；
  • 非自连接：多张表进行连接；
5.5.2.1 自连接例子

子连接实质上是将一张表当作两张表来使用，通过别名进行区分。

# 查询员工姓名、id及其管理者的姓名、id
SELECT e1.last_name,e1.employee_id,e2.`last_name`,e2.`employee_id`
FROM employees e1, employees e2
WHERE e1.`manager_id` = e2.`employee_id`;

5.5.3 分类3：内连接 vs 外连接

  • 内连接：多表连接返回的结果集中只包含匹配项，不包括非匹配项；
  • 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
    • 左外连接：连接条件中左边的表也称为 主表 ，右边的表称为 从表 ，结果集中包括左表中不满足条件的行，其对应右表中的字段都为null；
    • 右外连接：与左外连接正好相反。
    • 题目中有‘所有的’这种表达方式时，通常使用外连接。
    • 左外和右外连接一定要写连接条件ON。
5.5.3.1 sql92
  1. 内连接
    sql92中的内连接不需要额外的关键字。
-- sql92语法实现内连接	
-- 查询员工的工资等级
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
  1. 外连接
    mysql中不支持sql92中的外连接。
#左外连接
 SELECT last_name,department_name FROM employees ,departments WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name FROM employees ,departments WHERE employees.department_id(+) = departments.department_id;
5.5.3.2 sql99
  1. 内连接
    关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接。
SELECT table1.column, table2.column, table3.column
FROM table1 JOIN table2
ON table1 和 table2 的连接条件
JOIN table3
ON table2 和 table3 的连接条件
WHERE 等其他子句;
  1. 左外连接(LEFT [OUTER] JOIN … ON)
SELECT 字段列表
FROM A表 
LEFT JOIN B表 
ON 关联条件 
WHERE 等其他子句;
  1. 右外连接(RIGHT [OUTER] JOIN … ON)
SELECT 字段列表 
FROM A表 
RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;
  1. 满外连接(FULL OUTER JOIN)
  • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据，相当于左外连接和右外连接的并集。
  • SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
  • 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

5.6 UNION的使用

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

5.6.1 语法格式

SELECT 查询字段
FROM A
UNION [ALL]
SELECT 查询字段
FROM B；

5.6.2 UNION和UNION ALL的区别

UNION合并结果集后会把重复的记录去除，自带去重功能；
UNION ALL对于两个结果集的重复部分，不去重。

5.6.3 使用建议

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

5.7 7种SQL JOINS的实现

在这里插入图片描述

5.7.1 查询左外连接中不符合条件的数据

SELECT 查询字段
FROM tableA  a
LEFT JOIN tableB b
ON a.字段1 = b.字段1
WHERE b.字段1 IS NULL

where子句：左外连接中tableA的所有记录都会显示出来，对于tableA和tableB中不匹配的数据，tableB的字段都为null，所以在where子句中可以选取tableB中的任意字段来进行判断，但是tableB中的某些字段可能本身就为null，因此选取连接字段1来进行判断最为合适。
在这里插入图片描述

5.8 sql99新特性(了解)

5.8.1 自然连接 NATURAL JOIN

可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段 ，然后进行等值连接 。

sql92中
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
sql99中
# NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`。
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

5.8.2 USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。

下面两种写法结果相同

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d 
USING (department_id);

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

5.9 总结

5.9.1 表连接约束条件的三种方式

  • WHERE, ON, USING
    • WHERE：适用于所有关联查询
    • ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
    • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等(适用范围小，不推荐使用)

5.9.2 注意事项

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
来源：阿里巴巴《Java开发手册》

5.10 练习

在这里插入图片描述

  • 多表查询的一些技巧：
    • “所有” 用外连接；
    • on后边跟的是连接条件，一般是两表的相同字段。
# 1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e
LEFT JOIN departments d
#using(department_id)
ON e.`department_id` = d.`department_id`

# 2.查询90号部门员工的job_id和90号部门的location_id 
SELECT e.job_id,d.location_id
FROM employees e 
JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` = 90

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name , d.department_name , d.location_id , l.city
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.location_id
WHERE e.`commission_pct` IS NOT NULL;

# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 
#sql99
SELECT e.last_name , e.job_id , d.department_id , d.department_name 
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.location_id
WHERE l.`city` = 'Toronto' 

#sql92
SELECT e.last_name , e.job_id , d.department_id , d.department_name 
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.location_id
AND l.`city` = 'Toronto' 

# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’ 
SELECT d.department_name,l.`street_address`,e.last_name,e.job_id,e.salary
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_name` = 'Executive'

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式(略)
employees Emp# manager Mgr# kochhar 101 king 100
# 7.查询哪些部门没有员工 
SELECT d.*,e.*
FROM departments d
LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.department_id IS NULL

# 8. 查询哪个城市没有部门
SELECT city
FROM locations l
LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL;
# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.*
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
#where d.`department_name` = 'Sales' or d.`department_name` = 'IT'
WHERE d.`department_name` IN ('Sales','IT');

6 单行函数 函数汇总

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

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

6.1 流程控制函数case when then

SELECT last_name, CASE WHEN salary > 20000 THEN 'ssp'
		WHEN salary > 15000 THEN 'sp'
		WHEN salary > 10000 THEN  'p'
		ELSE 'baicai'
		END 'detail'
FROM employees;

7 聚合函数

  • 聚合函数对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。
  • mysql中聚合函数不能嵌套使用

7.1 常用的聚合函数

7.1.1 平均值 AVG() 求和 SUM()

  • 仅适用于数值类型的数据，且会忽略数值为null的行。
  • AVG(列名) = SUM(列名) / COUNT(列名) 总是成立。
    • SUM(列名)和 COUNT(列名)都会忽略值为null的行，AVG(列名) 也会忽略。
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary) FROM employees

7.1.2 最大值 MAX() 最小值 MIN()

适用于任意数据类型的数据。

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

7.1.4 COUNT()

  1. count(1),count(*) 都是返回表中的总记录数，会统计值为null的行；
  2. count(列名) 不会统计值为null的行；

7.2 GROUP BY

7.2.1 语法格式

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

7.2.2 多列分组

按照多个字段的组合进行分组，字段次序不同不影响结果。

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

例题：查询每个工种、每个部门的部门名、工种名和最低工资
每个工种、每个部门表示两个分组条件

 SELECT department_name,job_id,MIN(salary)
 FROM departments d LEFT JOIN employees e
 ON d.`department_id` = e.`department_id`
 GROUP BY department_name,job_id

7.2.3 GROUP BY中使用WITH ROLLUP

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

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

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

7.2.3.1 细节

在这里插入图片描述
统计记录数量所在行其余字段默认为null，可以使用函数IFNULL()来更改其值。

SELECT IFNULL(department_id,'统计记录数量') AS 'department_id',AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

在这里插入图片描述

7.2.4 注意事项

  1. select中出现的非聚合函数的字段必须声明在group by中，而group by中声明的字段可以不出现在select中。
  2. group by 子句的声明位置：from、where后，order by、limit之前。
  3. select查询字段中如果出现了聚合函数，一般都是需要分组。

7.3 HAVING

  • having不能单独使用，必须跟group by一起使用，一般用于分组后约束。
  • having声明在group by后边；

7.3.1 having和where的区别

  • having比where的使用范围更广，having后面可以添加聚合函数，也可以实现where所实现的功能；
  • where后面不能有聚合函数，因为聚合函数也叫分组函数，分组后才可以使用，而where是在分组前执行的，所以说从执行顺序上来说是不可以的；
  • 如果没有聚合函数，一般推荐使用where，执行效率更高，因为where是在分组前过滤数据，而having是在分组后过滤数据；
  • 如果通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接
    后筛选。 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。

7.3.2 例题

查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内。

SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;

7.4 SELECT的执行过程

7.4.1 sql语句完整结构

sql92

SELECT 查询字段,聚合函数
FROM table1,table2.....
WHERE 多表查询的连接条件 AND 不包含聚合函数的约束条件
GROUP BY .....
HAVING 包含聚合函数的约束条件
ORDER BY ..... (ASC / DESC)
LIMIT ....,.....;

sql99

SELECT 查询字段,聚合函数
FROM table1 (LEFT,RIGHT)JOIN table2 ON 多表查询的连接条件
(LEFT,RIGHT)JOIN table3 ON 多表查询的连接条件
WHERE 不包含聚合函数的约束条件
GROUP BY .....
HAVING 包含聚合函数的约束条件
ORDER BY ..... (ASC / DESC)
LIMIT ....,.....;

7.4.2 sql语句的执行顺序

在这里插入图片描述
21312

7.4.3 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 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。

8 子查询

8.1 基本概念

  • 子查询（内查询）在主查询之前一次执行完成。
  • 子查询的结果被主查询（外查询）使用 。
  • 注意事项
    • 子查询要包含在括号内
    • 将子查询放在比较条件的右侧
    • 单行操作符对应单行子查询，多行操作符对应多行子查询
  • 子查询的位置
    • 除了group by和limit，其余位置都可以使用子查询。

8.2 分类

  • 单行子查询和多行子查询
    • 单行：子查询返回一条记录
    • 多行：子查询返回多条记录
  • 相关子查询和非相关子查询
    • 不相关：子查询只执行了一次
    • 相关：子查询需要执行多次，子查询需要使用主查询的列

8.3 单行子查询

8.3.1 单行比较操作符

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

8.3.2 例题

8.3.2.1 题目1

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

SELECT last_name, job_id, salary 
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
);
8.3.2.2 HAVING中的子查询

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

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
);
8.3.2.3 CASE中的子查询

题目：显示员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。
case是单行函数，在select子句中使用

SELECT employee_id, last_name, (
    CASE department_id 
    WHEN (
        SELECT department_id
        FROM departments
        WHERE location_id = 1800
    ) THEN 'Canada' ELSE 'USA' END
) location
FROM employees;
8.3.2.4 子查询中的空值问题
mysql> SELECT last_name, job_id
    -> FROM employees
    -> WHERE job_id = (
    ->     SELECT job_id
    ->     FROM employees
    ->     WHERE last_name = 'Haas'
    -> );
Empty set (0.01 sec)

子查询不返回任何行

8.3.2.5 非法使用子查询
mysql> SELECT employee_id, last_name
    -> FROM employees
    -> WHERE salary = ( # 多行子查询使用单行比较符
    ->     SELECT MIN(salary)
    ->     FROM employees
    ->     GROUP BY department_id
    -> );
ERROR 1242 (21000): Subquery returns more than 1 row

8.4 多行子查询

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

8.4.1 多行比较操作符

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

8.4.2 例题

8.4.2.1 ANY的使用

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

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (SELECT salary
			FROM employees
			WHERE job_id = 'IT_PROG')
8.4.2.2 ALL的使用

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

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (SELECT salary
			FROM employees
			WHERE job_id = 'IT_PROG')
8.4.2.3 ALL的使用

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

SELECT department_id,AVG(salary) avgs
FROM employees
GROUP BY department_id
HAVING avgs <= ALL (SELECT AVG(salary) avgs
				FROM employees
				GROUP BY department_id);

8.4.3 空值问题

子查询的结果中如果存在null，最终的结果为空(没有一条记录)。
子查询中有空值
在这里插入图片描述

8.5 相关子查询

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

在这里插入图片描述

8.5.1 例题

8.5.1.1 from型的子查询

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

方式1：相关子查询

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

方式2：from型的子查询

SELECT e.last_name,e.salary,e.department_id
FROM employees e,(SELECT department_id,AVG(salary) avgs FROM employees GROUP BY department_id) t
WHERE e.salary > t.avgs 
AND e.department_id = t.department_id

from子查询相当于将子查询的结果当作一个虚拟表，一定要给from子查询取别名。

8.5.1.2 在ORDER BY 中使用子查询

查询员工的id,salary,按照department_name 排序.

order by排序的方式是查出一条记录后，用当前记录的排序字段与之前记录的排序字段进行比较，来确定当前记录的位置。

SELECT e.employee_id,e.salary
FROM employees e 
ORDER BY (SELECT department_name FROM departments d WHERE d.department_id = e.department_id);
8.5.1.3

若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id。

注意聚合函数的用法(复习)

SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
	SELECT COUNT(1)
	FROM job_history j
	WHERE j.employee_id = e.employee_id
);

8.5.2 EXISTS 与 NOT EXISTS关键字

  • EXISTS
    • 将主查询中的一条记录与子查询中的所有记录依次比较，如果满足条件，停止查找，返回true，并将本条记录加入到结果集中；如果子查询所有记录都不满足条件，返回false，跳过这条记录，继续从主查询中抽取记录进行比较。
  • NOT EXISTS
    • 与EXISTS相反。
  • EXISTS 与 NOT EXISTS后边的子查询中的select 的查询字段随便写，一般写*；因为我们只关注于是否满足条件，而不关注于子查询中的查询字段是什么。
  • 一般情况下，in和not in 可以改写成 EXISTS 与 NOT EXISTS。
8.5.2.1 例题1

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

方式1：EXISTS

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

方式2：子查询

SELECT *
FROM employees e1
WHERE e1.`employee_id` IN (
		SELECT DISTINCT manager_id
		FROM employees e2
)

方式3：自连接

SELECT DISTINCT e2.*
FROM employees e1,employees e2
WHERE e1.manager_id = e2.employee_id;
8.5.2.2 例题2

查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
		SELECT *
		FROM employees e
		WHERE e.department_id = d.department_id
		)

8.6 子查询 vs 自连接

一般情况建议使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。
可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表
进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

8.7 子查询的技巧

  1. 复杂子查询，从里往外写；（例如：查询平均工资最低的部门信息）
  2. 简单子查询，从外往里写；
  3. 相关子查询一般从外往里写，因为其结构通常只有两层；
  4. 一般情况下，in和not in 可以改写成 EXISTS 与 NOT EXISTS。
  5. 查找记录中的唯一的最大最小值，可以使用order by和limit; (见课后习题8.8.1方法2)

8.8 课后习题

8.8.1 查询平均工资最低的部门信息

方式1：ALL的使用

SELECT * 
FROM departments
WHERE department_id = 
		(SELECT department_id
		FROM employees
		GROUP BY department_id
		HAVING AVG(salary) <= ALL(
			SELECT AVG(salary) avgs
					FROM employees
					GROUP BY department_id
		)
)

方式2：查找记录中的最大最小值，使用order by和limit

tip:第二层存在的意义在于平均工资最低的部门可能不止一个。

SELECT * 
FROM departments
WHERE department_id IN 
		(SELECT department_id
		FROM employees
		GROUP BY department_id
		HAVING AVG(salary) = (
			SELECT AVG(salary) avgs
			FROM employees
			GROUP BY department_id
			ORDER BY avgs 
			LIMIT 1
		)
)

方式3：from中的子查询

SELECT d.*
FROM departments d,(SELECT department_id,AVG(salary) avgs
			FROM employees
			GROUP BY department_id
			ORDER BY avgs 
			LIMIT 1) dept
WHERE d.department_id = dept.department_id

8.8.2 查询平均工资最低的部门信息和该部门的平均工资

方式1：from中的子查询
tip：记录中的数据分别来自于聚合函数和表时，可以将聚合函数和另一个表中的连接字段查询出来，当作一张虚拟表，然后通过连接字段和另一张表进行多表连接，最后得到所有想要的字段。

SELECT d.*,avgs  
FROM departments d,(SELECT department_id,AVG(salary) avgs
			FROM employees
			GROUP BY department_id
			ORDER BY avgs 
			LIMIT 1) dept
WHERE d.department_id = dept.department_id

方式2：select子查询
tip：将聚合函数通过select子查询的方式添加到当前记录中，子查询的约束条件来自于主查询，构成了相关查询。

SELECT * ,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avgs
FROM departments d
WHERE department_id = 
		(SELECT department_id
		FROM employees
		GROUP BY department_id
		HAVING AVG(salary) <= ALL(
			SELECT AVG(salary) avgs
					FROM employees
					GROUP BY department_id
		)
)

8.8.3 各个部门中 最高工资中最低的那个部门的 最低工资是多少?

和8.8.1思路一致

SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING department_id IN (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING MAX(salary) = (
		SELECT MAX(salary) maxs
		FROM employees
		GROUP BY department_id
		ORDER BY maxs
		LIMIT 1
	)
)

8.8.4 查询平均工资最高的部门的 manager 的详细信息

SELECT last_name, department_id, email, salary
FROM employees e1, (SELECT manager_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1) e2
WHERE e1.`employee_id` = e2.manager_id;

8.8.5 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

方式1：not in

SELECT department_id
FROM departments
WHERE department_id NOT IN(
		SELECT DISTINCT department_id
		FROM employees
		WHERE job_id = 'ST_CLERK'
);

方式2：not exists
tip:将not in改写成not exists时，一般都是将主查询中的约束条件添加到子查询where中充当连接条件。

SELECT  department_id
FROM departments d
WHERE NOT EXISTS(
		SELECT DISTINCT department_id
		FROM employees e
		WHERE job_id = 'ST_CLERK'
		AND d.department_id = e.department_id
);

8.8.6 查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

 SELECT department_name
 FROM departments d
 WHERE 5 < (
	SELECT COUNT(1)
	FROM employees e
	WHERE d.department_id = e.`department_id`
 )

9 创建和管理表

9.1 标识符命名规则

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

9.2 创建和管理数据库

9.2.1 创建数据库

9.2.1.1 方式1
CREATE DATABASE 数据库名;
9.2.1.2 方式2：指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
9.2.1.3 方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）
CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET '字符集';

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

9.2.2 使用数据库

  • 查看当前所有的数据库
SHOW DATABASES; #有一个S，代表多个数据库
  • 查看当前正在使用的数据库
SELECT DATABASE(); #使用的一个 mysql 中的全局函数
  • 查看指定库下所有的表
SHOW TABLES FROM 数据库名;
  • 查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
或者： 
SHOW CREATE DATABASE 数据库名\G
  • 使用/切换数据库
USE 数据库名;

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

9.2.3 修改数据库

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

9.2.4 删除数据库

9.2.4.1 删除指定的数据库
drop database 数据库名;
9.2.4.2 删除指定的数据库（ 推荐 ）
DROP DATABASE IF EXISTS 数据库名;

9.3 创建表 CREATE

9.3.1 两种方式

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

必须指明：表名，列名(或字段名)，数据类型，长度；
可选：约束条件，默认值；

9.3.1.2 方式2:使用已知表来创建
  • 格式
    在这里插入图片描述
  • 要求
    • 指定的列和子查询中的列要一一对应；
    • 通过列名和默认值定义列。
  • 通过已知表来创建表，只会将表中的数据复制过来，并不会将约束复制过来。
9.3.1.3 练习
  • 复制完整表，并且包括全部的数据
CREATE TABLE IF NOT EXISTS test1
AS
SELECT * FROM employees
  • 复制完整表，不包括数据
CREATE TABLE IF NOT EXISTS test1
AS
SELECT * FROM employees
WHERE 1 = 2
  • 使用方式1创建表
CREATE TABLE IF NOT EXISTS emp01(
	id INT(7),
	first_name VARCHAR(25),
	last_name VARCHAR(25),
	dept_id INT(7)
);

9.3.2 查看数据表结构

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

desc 表名；
SHOW CREATE TABLE 表名\G；

9.4 修改表 alter table

9.4.1 添加一个字段

  • 语法格式：
ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST |(AFTER 字段名)】;

tip：新增的字段默认位于所有字段的后边，如果想要指定字段的位置，使用【FIRST | (AFTER 字段名)】。

9.4.2 修改一个字段:数据类型，长度、默认值和位置

  • 语法格式：
ALTER TABLE 表名 
MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名 2】;
  • 例子
ALTER TABLE dept80 MODIFY last_name VARCHAR(30);
ALTER TABLE dept80 MODIFY salary double(9,2) default 1000;
ALTER TABLE test1 MODIFY age DOUBLE(10,2) DEFAULT 30 FIRST;
  • 注意事项
    • 修改默认值，只会影响以后对表的修改；
    • 一般不建议修改数据类型，可以只修改数据长度；

9.4.3 重命名一个字段

  • 语法格式：
ALTER TABLE 表名 
CHANGE 【column】 列名 新列名 新数据类型;

9.4.1 删除一个字段

  • 语法格式：
ALTER TABLE 表名 
DROP 【COLUMN】字段名

9.5 重命名表 RENAME

9.5.1 方式1 (推荐)

RENAME TABLE 旧表名 TO 新表名;

9.5.2 方式2

ALTER table 旧表名 RENAME [TO] 新表名; -- [TO]可以省略

9.6 删除表 DROP

  • 在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。
  • 数据和结构都被删除；
  • 所有正在运行的相关事务被提交；
  • 所有相关索引被删除；
  • 不可回滚；
  • 语法格式：
DROP TABLE IF EXISTS 表名；

9.7 清空表 truncate

  • 语法格式：
truncate table 表名；
  • 表示清空表中的所有数据，但是表结构保留。

9.7.1 DCL 中 COMMIT 和 ROLLBACK

  • COMMIT :提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着数据不可以回滚。
  • ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

9.7.2 对比 TRUNCATE TABLE 和 DELETE FROM

  • 相同点：都可以实现对表中所有数据的删除，同时保留表结构。
  • 不同点：
    • TRUNCATE TABLE ：执行后不可回滚。
    • DELETE FROM ：执行后可以回滚。

9.7.3 DDL 和 DML 的说明

  • DDL的操作一旦执行，就不可回滚(truncate属于DDL)。指令SET autocommit = FALSE对DDL操作失效。(因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作不受SET autocommit = FALSE影响的。)
  • DML的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了 SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无
事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

9.8 拓展

9.8.1拓展1：阿里巴巴《Java开发手册》之MySQL字段命名

  • 【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
    • 正例：aliyun_admin，rdc_config，level3_name
    • 反例：AliyunAdmin，rdcConfig，level_3_name
  • 【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
  • 【强制】表必备三字段：id, gmt_create, gmt_modified。
    • 说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新
  • 【推荐】表的命名最好是遵循 “业务名称_表的作用”。
    • 正例：alipay_task 、 force_project、 trade_config
  • 【推荐】库名与应用名称尽量一致。
  • 【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

9.8.2 拓展2：如何理解清空表、删除表等操作需谨慎？！

表删除操作将把表的定义和表中的数据一起删除，并且MySQL在执行删除操作时，不会有任何的确认信息提示，因此执行删除操作时应当慎重。在删除表前，最好对表中的数据进行备份，这样当操作失误时可以对数据进行恢复，以免造成无法挽回的后果。

同样的，在使用ALTER TABLE进行表的基本修改操作时，在执行操作过程之前，也应该确保对数据进行完整的备份，因为数据库的改变是无法撤销的，如果添加了一个不需要的字段，可以将其删除；相同的，如果删除了一个需要的列，该列下面的所有数据都将会丢失。

9.8.3 拓展3：MySQL8新特性—DDL的原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即DDL操作要么成功要么回滚。DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到）中，用于回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。

10 DML之增删改

10.1 插入数据

10.1.1 方式1：VALUES的方式添加

使用这种语法一次只能向表中插入一条数据。

情况1：为表的所有字段按默认顺序插入数据

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

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

情况2：为表的指定字段插入数据（推荐）

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

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

情况3：同时插入多条记录（推荐）

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：

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

VALUES 也可以写成 VALUE ，但是VALUES是标准写法。
字符和日期型数据应包含在单引号中。

10.1.2 方式2：将查询结果插入到表中

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

基本语法格式如下：

INSERT INTO 目标表名 (tar_column1 [, tar_column2, …, tar_columnn]) 
SELECT (src_column1 [, src_column2, …, src_columnn]) 
FROM 源表名
[WHERE condition]
  • 在 INSERT 语句中加入子查询。
  • 不必书写VALUES子句。
  • 子查询中的值列表应与 INSERT 子句中的列名对应。

举例：

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

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

说明：emp2表中要添加数据的字段的长度不能低于employees表中查询的字段的长度。
如果emp2表中要添加数据的字段的长度低于employees表中查询的字段的长度的话，就有添加不成功的风险。

10.2 更新数据

10.2.1 语法格式

UPDATE table_name 
SET column1=value1, column2=value2, … , column=valuen 
[WHERE condition]

10.2.2 注意事项

  • 一次可以更新多条数据；
  • 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
  • 如果不加where约束，表中的所有数据都会被修改；

10.3 删除数据

10.3.1 语法格式

delete from table_name
[WHERE condition]

10.3.2 注意事项

  • 使用 WHERE 子句删除指定的记录;
  • 如果省略 WHERE 子句，则表中的全部数据将被删除;

10.4 总结

DML操作默认会自动提交，如果希望执行完后不自动提交，使用SET autocommit = false;

10.5 MySQL8新特性：计算列

有空再学；

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

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。

举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的值。 首先创建测试表tb1，语句如下：

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

插入演示数据，语句如下：

INSERT INTO tb1(a,b) VALUES (100,200);

查询数据表tb1中的数据，结果如下：

mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
| NULL |  100 |  200 |  300 |
+------+------+------+------+

更新数据中的数据，语句如下：

mysql> UPDATE tb1 SET a = 500;
mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
| NULL |  500 |  200 |  700 |
+------+------+------+------+

11 MySQL数据类型

11.1 MySQL中的数据类型

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

常见数据类型的属性，如下：

MySQL关键字含义
NULL数据列可包含NULL值
NOT NULL数据列不允许包含NULL值
DEFAULT默认值
PRIMARY KEY主键
AUTO_INCREMENT自动递增，适用于整数类型
UNSIGNED无符号
CHARACTER SET name指定一个字符集

11.2 指定字符集

11.2.1 指定数据库的字符集

CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET '字符集';

11.2.2 指定表的字符集

CREATE TABLE IF NOT EXISTS test01(
	id INT,
	NAME VARCHAR(50),
	age INT
) CHARACTER SET 'gbk';

11.2.3 指定字段的字符集

CREATE TABLE IF NOT EXISTS test02(
	id INT,
	NAME VARCHAR(50) CHARACTER SET 'utf8',
	age INT
) CHARACTER SET 'gbk';

11.3 整型

11.3.1 类型介绍

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

整数类型字节有符号数取值范围无符号数取值范围
TINYINT1-128~1270~255
SMALLINT2-32768~327670~65535
MEDIUMINT3-8388608~83886070~16777215
INT、INTEGER4-2147483648~21474836470~4294967295
BIGINT8-9223372036854775808~92233720368547758070~18446744073709551615

11.3.2 可选属性

整数类型的可选属性有三个：

11.3.2.1 M

M: 表示显示宽度，M的取值范围是(0, 255)。例如，int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ZEROFILL”使用，表示用“0”填满宽度，否则指定显示宽度无效。

如果设置了显示宽度，那么插入的数据宽度超过显示宽度限制，会不会截断或插入失败？

答案：不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即显示宽度与类型可以存储的值范围无关。从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。

11.3.2.2 UNSIGNED

UNSIGNED: 无符号类型（非负），所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。

int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)。因为负号占了一个数字位。

11.3.2.3 ZEROFILL

ZEROFILL: 0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。

11.3.3 适用场景

TINYINT：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。

SMALLINT：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。

MEDIUMINT：用于较大整数的计算，比如车站每日的客流量等。

INT、INTEGER：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。

BIGINT：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

11.3.4 如何选择？

在评估用哪种整数类型的时候，你需要考虑存储空间和可靠性的平衡问题：一方 面，用占用字节数少的整数类型可以节省存储空间；另一方面，要是为了节省存储空间，使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起系统错误，影响可靠性。

举个例子，商品编号采用的数据类型是 INT。原因就在于，客户门店中流通的商品种类较多，而且，每天都有旧商品下架，新商品上架，这样不断迭代，日积月累。

如果使用 SMALLINT 类型，虽然占用字节数比 INT 类型的整数少，但是却不能保证数据不会超出范围65535。相反，使用 INT，就能确保有足够大的取值范围，不用担心数据超出范围影响可靠性的问题。

你要注意的是，在实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，我建议你首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间。

11.4 浮点型

11.4.1 类型介绍

浮点数和定点数类型的特点是可以处理小数，你可以把整数看成小数的一个特例。

  • FLOAT 表示单精度浮点数；
  • DOUBLE 表示双精度浮点数；
    在这里插入图片描述
  • REAL默认就是 DOUBLE。如果你把 SQL 模式设定为启用“REAL_AS_FLOAT”，那 么，MySQL 就认为REAL 是 FLOAT。如果要启用“REAL_AS_FLOAT”，可以通过以下 SQL 语句实现：
SET sql_mode = “REAL_AS_FLOAT”;

**问题1：**FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？

FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范围也大。

问题2：为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分呢？

MySQL 存储浮点数的格式为：符号(S) 、 尾数(M) 和 阶码(E)。因此，无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。因此， 所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。

11.4.2 数据精度说明

对于浮点类型，在MySQL中单精度值使用4个字节，双精度值使用8个字节。

  • MySQL允许使用非标准语法（其他数据库未必支持，因此如果涉及到数据迁移，则最好不要这么用）：FLOAT(M,D)或DOUBLE(M,D)。这里，M称为精度，D称为标度。(M,D)中 M=整数位+小数位，D=小数位。 D<=M<=255，0<=D<=30。

    例如，定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错。

  • FLOAT和DOUBLE类型在不指定(M,D)时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示。

  • 说明：浮点类型，也可以加UNSIGNED，但是不会改变数据范围，例如：FLOAT(3,2) UNSIGNED仍然只能表示0-9.99的范围。

  • 不管是否显式设置了精度(M,D)，这里MySQL的处理方案如下：

    • 如果存储时，整数部分超出了范围，MySQL就会报错，不允许存这样的值
    • 如果存储时，小数点部分若超出范围，就分以下情况：
      • 若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。例如在FLOAT(5,2)列内插入999.009，近似结果是999.01。
      • 若四舍五入后，整数部分超出范围，则MySQL报错，并拒绝处理。如FLOAT(5,2)列内插入999.995和-999.995都会报错。

  • 从MySQL 8.0.17开始，FLOAT(M,D)和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用，将来可能被移除。另外，关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了，将来也可能被移除。

在编程中，如果用到浮点数，要特别注意误差问题，**因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。**同时，在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。那么，MySQL 有没有精准的数据类型呢？当然有，这就是定点数类型： DECIMAL。

11.5 定点数decimal

11.5.1 类型介绍

  • MySQL中的定点数类型只有 DECIMAL 一种类型。
数据类型字节数含义
DECIMAL(M,D),DEC,NUMERICM+2字节有效范围由M和D决定

使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65， 0<=D<=30，D<M。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。

  • DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。
  • 定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的。
  • 当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

11.5.2 浮点数 vs 定点数

  • 浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）
  • 定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景 （比如涉及金额计算的场景）

11.5.3 开发中经验

“由于 DECIMAL 数据类型的精准性，在我们的项目中，除了极少数（比如商品编号）用到整数类型外，其他的数值都用的是 DECIMAL，原因就是这个项目所处的零售行业，要求精准，一分钱也不能差。 ” ——来自某项目经理

11.6 位类型：BIT

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

二进制字符串类型长度长度范围占用空间
BIT(M)M1 <= M <= 64约为(M + 7)/8个字节

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

使用b+0查询数据时，可以直接查询出存储的十进制数据的值。

11.7 日期与时间类型

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

  • YEAR类型通常用来表示年
  • DATE类型通常用来表示年、月、日
  • TIME类型通常用来表示时、分、秒
  • DATETIME类型通常用来表示年、月、日、时、分、秒
  • TIMESTAMP类型通常用来表示带时区的年、月、日、时、分、秒
类型名称字节日期格式最小值最大值
YEAR年1YYYY或YY19012155
TIME时间3HH:MM:SS-838:59:59838:59:59
DATE日期3YYYY-MM-DD1000-01-019999-12-03
DATETIME日期时间8YYYY-MM-DD HH:MM:SS1000-01-01 00:00:009999-12-31 23:59:59
TIMESTAMP日期时间4YYYY-MM-DD HH:MM:SS1970-01-01 00:00:00 UTC2038-01-19 03:14:07UTC

11.7.1 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已经不推荐使用。

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

11.7.3 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()，会插入当前系统的时间。

11.7.4 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类型的字段插入系统的当前日期和时间。

举例：

CREATE TABLE test_datetime1( dt DATETIME );
INSERT INTO test_datetime1
VALUES ('20220510'),(NOW());

在这里插入图片描述

11.7.5 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和DATETIME的区别：

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

11.7.6 开发中经验

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

11.8 字符串类型

11.8.1 CHAR与VARCHAR类型

字符串(文本)类型特点长度长度范围占用的存储空间
CHAR(M)固定长度M0 <= M <= 255M个字节
VARCHAR(M)可变长度M0 <= M <= 65535/3(实际长度 + 1) 个字节

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字符，所以最大为65535/3 = 21845个字符。
  • 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

哪些情况使用 CHAR 或 VARCHAR 更好

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

情况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和数据存储总量比较好。

11.8.2 TEXT类型

文本字符串类型特点长度长度范围占用的存储空间
TINYTEXT小文本、可变长度L0 <= L <= 255L + 2 个字节
TEXT文本、可变长度L0 <= L <= 65535L + 2 个字节
MEDIUMTEXT中等文本、可变长度L0 <= L <= 16777215L + 3 个字节
LONGTEXT大文本、可变长度L0 <= L<= 4294967295（相当于4GB）L + 4 个字节

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

开发中经验：

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

11.9 ENUM

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

文本字符串类型长度长度范围占用的存储空间
ENUML1 <= L <= 655351或2个字节
  • 当ENUM类型包含1～255个成员时，需要1个字节的存储空间；
  • 当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。
  • ENUM类型的成员个数的上限为65535个。
CREATE TABLE test_enum( 
    season ENUM('春','夏','秋','冬','unknow')
);

INSERT INTO test_enum 
VALUES('春'),('秋');

# 允许按照角标的方式获取指定索引位置的枚举值，从1开始
INSERT INTO test_enum 
VALUES('1'),(3);

# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum
VALUES(NULL);

11.10 SET类型

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

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

成员个数范围（L表示实际成员个数）占用的存储空间
1 <= L <= 81个字节
9 <= L <= 162个字节
17 <= L <= 243个字节
25 <= L <= 324个字节
33 <= L <= 648个字节

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

CREATE TABLE test_set(
    s SET ('A', 'B', 'C')
);

INSERT INTO test_set (s)
VALUES ('A'), ('A,B');
#插入重复的SET类型成员时，MySQL会自动删除重复的成员 
INSERT INTO test_set (s) 
VALUES ('A,B,C,A');

11.11 二进制字符串类型

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

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

二进制字符串类型特点值的长度占用空间
BINARY(M)固定长度M （0 <= M <= 255）M个字节
VARBINARY(M)可变长度M（0 <= M <= 65535）M+1个字节

11.11.2 BLOB类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。

需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到MySQL中。

二进制字符串类型值的长度长度范围占用空间
TINYBLOBL0 <= L <= 255L + 1 个字节
BLOBL0 <= L <= 65535（相当于64KB）L + 2 个字节
MEDIUMBLOBL0 <= L <= 16777215 （相当于16MB）L + 3 个字节
LONGBLOBL0 <= L <= 4294967295（相当于4GB）L + 4 个字节

11.11.3 TEXT和BLOB的使用注意事项

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

① BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同。为了提高性能，建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理。

② 如果需要对大文本字段进行模糊查询，MySQL 提供了前缀索引。但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT * 查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。

③ 把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。

11.12 小结及选择建议

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

这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

关于字符串的选择，建议参考如下阿里巴巴的《Java开发手册》规范：

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

  • 任何字段如果为非负数，必须是 UNSIGNED

  • 【强制】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。

    • 说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并分开存储。

  • 【强制】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。

    【强制】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

12 约束

12.1 约束(constraint)概述

12.1.1 为什么需要约束

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

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

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

12.1.2 什么是约束

  • 约束是表级的强制规定，是对表中字段的一种限制。可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。
  • 约束是对表的增删改添加一定的限制。

12.1.3 约束的分类

  • 根据约束数据列的限制，约束可分为：
    • 单列约束：每个约束只约束一列
    • 多列约束：每个约束可约束多列数据
  • 根据约束的作用范围，约束可分为：
    • 列级约束：只能作用在一个列上，跟在列的定义后面
    • 表级约束：可以作用在多个列上，不与列一起，而是单独定义
  • 根据约束起的作用，约束可分为：
    • NOT NULL 非空约束，规定某个字段不能为空
    • UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
    • PRIMARY KEY 主键(非空且唯一)约束
    • FOREIGN KEY 外键约束
    • CHECK 检查约束（MySQL不支持check约束，但可以使用check约束，而没有任何效果）
    • DEFAULT 默认值约束，给表中字段设置默认值
  • 查看某个表已有的约束
#information_schema数据库名（系统库） 
#table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints 
WHERE table_name = '表名称';

12.1.4 添加约束、删除约束的位置

12.1.4.1 添加约束
  1. 创建表的时候；
  2. 通过Alter table 添加约束。
12.1.4.2 删除约束
  • 通过Alter table 删除约束。

12.2 非空约束

12.2.1 作用

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

12.2.2 关键字

NOT NULL

12.2.3 特点

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

12.2.4 添加非空约束

  • 建表时
CREATE TABLE 表名称( 
    字段名 数据类型, 
    字段名 数据类型 NOT NULL, 
    字段名 数据类型 NOT NULL 
);
  • 建表后
ALTER TABLE 表名称 
MODIFY 字段名 数据类型 NOT NULL;

12.2.5 删除非空约束

# 方式一：
ALTER TABLE 表名称 
MODIFY 字段名 数据类型 NULL; 
# 方式二：
ALTER TABLE 表名称 
MODIFY 字段名 数据类型;

12.3 唯一性约束

12.3.1 作用

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

12.3.2 关键字

UNIQUE

12.3.3 特点

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

12.3.4 添加（复合）唯一约束

（1）建表时

#列级约束
CREATE TABLE 表名称( 
    字段名 数据类型, 
    字段名 数据类型 UNIQUE [KEY], 
    字段名 数据类型 
);

#表级约束
CREATE TABLE 表名称( 
    字段名 数据类型, 
    字段名 数据类型, 
    字段名 数据类型, 
    [CONSTRAINT 约束名] UNIQUE [KEY](字段列表)  #多个字段之间用逗号隔开
);

（2）建表后指定唯一键约束

#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的
#方式1： 
ALTER TABLE 表名称 
ADD [CONSTRAINT 约束名] UNIQUE [KEY](字段列表); #多个字段之间用逗号隔开
#方式2： 
ALTER TABLE 表名称 
MODIFY 字段名 字段类型 UNIQUE [KEY];

可以向声明为unique的字段上添加null值。而且可以多次添加null

12.3.5 删除唯一约束

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

注意：可以通过show index from 表名称;查看表的索引

12.3.6 举例

#建表后添加unique约束
ALTER TABLE USER ADD UNIQUE(NAME,PASSWORD);
ALTER TABLE USER ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD); 
ALTER TABLE USER MODIFY NAME VARCHAR(20) UNIQUE;

# 删除唯一性索引uk_test_unique2
ALTER TABLE test_unique2
DROP INDEX uk_test_unique2

12.4 PRIMARY KEY 主键约束

12.4.1 作用

用来唯一标识表中的一行记录

12.4.2 关键字

primary key

12.4.3 特点

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

12.4.4 添加（复合）主键约束

（1）建表时指定主键约束

create table 表名称( 
    字段名 数据类型 primary key, #列级模式 
    字段名 数据类型, 
    字段名 数据类型 
);
create table 表名称( 
    字段名 数据类型, 
    字段名 数据类型, 
    字段名 数据类型, 
    [constraint 约束名] primary key(字段列表) #表级模式，多个字段之间用逗号隔开
);

# 列级约束
CREATE TABLE emp4( 
    id INT PRIMARY KEY AUTO_INCREMENT, # AUTO_INCREMENT 表示自增
    NAME VARCHAR(20) 
);
# 表级约束
CREATE TABLE emp5( 
    id INT NOT NULL AUTO_INCREMENT, 
    NAME VARCHAR(20), pwd VARCHAR(15), 
    CONSTRAINT emp5_id_pk PRIMARY KEY(id) #没有必要起名字
);

（2）建表后增加主键约束

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

12.4.5 删除主键约束

alter table 表名称 
drop primary key;

说明：删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。
在实际开发中，不会去删除表中的主键约束！

12.5 自增列：AUTO_INCREMENT

12.5.1 作用

某个字段的值自增

12.5.2 关键字

auto_increment

12.5.3 特点和要求

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

12.5.4 如何指定自增约束

（1）建表时

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）建表后

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

12.5.5 如何删除自增约束

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

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

12.5.6.1 MySQL 8.0之前

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。
底层原理：在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个 计数器 来决定的，而该计数器只在 内存中维护 ，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

12.5.6.2 MySQL 8.0

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

12.6 FOREIGN KEY 约束

12.6.1 作用

限定某个表的某个字段的引用完整性。

比如：员工表中员工所在部门(外键)的选择，必须在部门表能找到对应的部分。
在这里插入图片描述

12.6.2 关键字

FOREIGN KEY

12.6.3 主表和从表/父表和子表

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

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

12.6.4 特点

（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列，因为被依赖/被参考的值必须是唯一的。

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

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

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

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

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

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

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

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

12.6.5 添加外键约束

（1）建表时

create table 主表名称( 
    字段1 数据类型 primary key, 
    字段2 数据类型 
);
create table 从表名称( 
    字段1 数据类型 primary key, 
    字段2 数据类型, 
    [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段) 
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

（2）建表后

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

总结：约束关系是针对双方的

  • 添加了外键约束后，主表的修改和删除数据受约束
  • 添加了外键约束后，从表的添加和修改数据受约束
  • 在从表上建立外键，要求主表必须存在
  • 删除主表时，要求从表先删除，或将从表中外键引用该主表的关系先删除

12.6.6 约束等级

  • 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的方式(把修改操作设置为级联修改等级，把删除操作设置为Restrict等级 )。

create table emp( 
    eid int primary key, #员工编号 
    ename varchar(5), #员工姓名 
    deptid int, #员工所在的部门
    foreign key (deptid) references dept(did) ON UPDATE CASCADE ON DELETE RESTRICT #把修改操作设置为级联修改等级，把删除操作设置为Restrict等级 
);

12.6.7 删除外键约束

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

12.6.8 开发场景

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

答：不是的

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

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

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

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

答：没有

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

12.6.9 阿里开发规范

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

12.7 CHECK 约束

12.7.1 作用

检查某个字段的值是否符号xx要求，一般指的是值的范围，如果不符合要求，无法插入或更新数据。

12.7.2 关键字

CHECK

12.7.3 MySQL 5.7 不支持

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

CREATE TABLE temp( 
    id INT AUTO_INCREMENT, 
    NAME VARCHAR(20), 
    age INT CHECK(age > 20), 
    gender char CHECK ('男' OR '女'),
    PRIMARY KEY(id) 
);

12.8 DEFAULT约束

12.8.1 作用

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

12.8.2 关键字

DEFAULT

12.8.3 如何给字段加默认值

（1）建表时

create table 表名称(
    字段名 数据类型 default 默认值 , 
    字段名 数据类型 not null default 默认值, 
    字段名 数据类型 not null default 默认值, 
    primary key(字段名),
    unique key(字段名) 
);
# 说明：默认值约束一般不在唯一键和主键列上加

（2）建表后

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

如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了
同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了

12.8.4 如何删除默认值约束

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

12.9 面试题

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

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

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

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

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

12.9.3 面试3、带AUTO_INCREMENT约束的字段值是从1开始的吗？

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

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

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

13 视图

13.1 概述

13.1.1 为什么使用视图？

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查
询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的
价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他
人的查询视图中则不提供这个字段。

13.1.2 视图的理解

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

13.2 视图的创建

13.2.1 格式

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

13.2.2 针对于单表

13.2.2.1 视图中的字段与基表中的字段有对应关系
  1. 查询时指定视图中字段的名称，如果指定了别名，视图中的字段以别名作为名称。
CREATE VIEW vu_emp
AS
SELECT last_name name,department_id,salary
FROM employees
  1. 通过视图中的[(字段列表)] 确定视图的字段名称
// (字段列表)中的名称个数必须与查询语句中的字段个数相同
CREATE VIEW vu_emp1(`name`,dept_id,emp_salary)
AS
SELECT last_name,department_id,salary
FROM employees;
13.2.2.2 视图中的字段不存在于基表中

例如通过聚合函数求出的平均值、最大值等，在基表中不存在，但是可以保存到视图中。

CREATE VIEW vu_emp2(dept_id,avg_salary)
AS
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id;

13.2.3 针对于多表

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

13.2.4 通过视图来创建视图

CREATE VIEW vu_emp3
AS 
SELECT *
FROM vu_emp2;

13.3 查看视图

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

SHOW TABLES;

语法2：查看视图的结构

DESC / DESCRIBE 视图名称;

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

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

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

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

SHOW CREATE VIEW 视图名称;

13.4 更新视图

13.4.1 一般情况

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

13.4.2 不可更新的视图

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

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

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

13.5 修改、删除视图

13.5.1 修改视图

方式1：使用CREATE OR REPLACE VIEW 子句修改视图

方式2：ALTER VIEW

ALTER VIEW 视图名称 
AS
查询语句

13.5.2 删除视图

  • 删除视图只是删除视图的定义，并不会删除基表的数据。
DROP VIEW IF EXISTS 视图名称;
DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;

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

13.6 总结

13.6.1 优点

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

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

3. 数据安全
MySQL将用户对数据的访问限制在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。

同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。

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

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

13.6.2 视图不足

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

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

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

14 存储过程与函数

后边再学

以梦为马|不负韶华
关注
  • 1
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL超详细学习教程，2023年硬核学习路线
zhangxia_的博客
01-19 3万+
为什么学习MySQL？这是一篇 MySQL 通关一篇过硬经验学习路线，包括数据库相关知识，SQL语句的使用，数据库约束，设计等。未来是一个数字化的时代，数据是我们最为宝贵的资源，不论是哪个专业出身，未来或多或少都会卷入数据时代的浪潮之中。而那些前提性的工作基本都是依靠`数据库`来完成。
外卖返利系统|外卖返利系统小程序|外卖系统|返利系统|外卖cps返利系统
m0_46608046的博客
07-27 1406
外卖CPS小程序源码分享 外卖券外卖省省外卖探探美团饿了么外卖联盟优惠券小程序系统软件开发源码 美团/饿了么外卖CPS联盟返利公众号小程序裂变核心源码 饿了么、美团优惠开发(外卖cps,三级裂变源码) 源码下载/搭建 http://y.mybei.cn 截图 功能列表 特价电影票 饿了么外卖（3个红包） 美团外卖 美团优选（1分钱买菜） 购物返利（淘宝，拼多多，京东） 话费优惠充值（92折，慢充） 火车票优惠 滴滴打车优惠 小程序广告收益（流量主） 公众号绑定
MySQL基础入门教程（非常详细）从零基础入门到精通，看完这一篇就够了
Javachichi的博客
08-31 1万+
网络安全行业产业以来，随即新增加了几十个网络安全行业岗位︰网络安全专家、网络安全分析师、安全咨询师、网络安全工程师、安全架构师、安全运维工程师、渗透工程师、信息安全管理员、数据安全工程师、网络安全运营工程师、网络安全应急响应工程师、数据鉴定师、网络安全产品经理、网络安全服务工程师、网络安全培训师、网络安全审计员、威胁情报分析工程师、灾难恢复专业人员、实战攻防专业人员…网上虽然也有很多的学习资源，但基本上都残缺不全的，这是我自己录的网安视频教程，上面路线图的每一个知识点，我都有配套的视频讲解。
MySQL基础大全（看这一篇足够！！！）
最新发布
weixin_35856285的博客
06-28 5340
自己看MySQL的总结，一方面加深自己的记忆，一方面与君共享
MySQL学习教程（超全）
qq_51533426的博客
03-15 5894
mysql学习安装配置
mysql数据库
qq_39309096的博客
09-02 423
数据库：用于存储和管理数据的仓库。DataBase 简称 ： DB 特点： 持久化存储数据的。其实数据库就是一个文件系统 方便存储和管理数据 使用了统一的方式操作数据库 – SQL 1. MySQL数据库软件 1. 安装 * 略 2. 卸载 1. 去mysql的安装目录找到my.ini文件 * 复制 datadir="C:/ProgramData/MySQL/MySQL S...
MySQL学习笔记
12-19
### MySQL学习笔记知识点详解 #### 一、数据库概述 数据库是指按照特定的数据结构来组织、存储和管理数据的集合体。随着信息技术的进步和社会需求的增长，数据库已经从最初的简单存储发展成为用户所需的各种复杂...
MySQL学习笔记.zip
02-23
这份“MySQL学习笔记”将引导我们深入理解其核心概念和实用技能。 一、MySQL简介 MySQL是一个开源、免费的数据库系统，由瑞典的MySQL AB公司开发，后被Oracle公司收购。它的设计目标是速度、可移植性和简洁性，支持...
mysql数据库基础知识，mysql数据库简介（一看就懂，一学就会）
学Java，找哪吒
05-11 1万+
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。
一文学习MySQL-MySQL必知必会
wspppdy的博客
03-29 1163
MySQL是对于数据库理论的很好实践，通过学习其存储引擎、物理和逻辑结构、索引、日志、事务、锁等等必知必会知识，对后续继续学习其他关系数据库和面试都有重要帮助。
MySQL教程：MySQL数据库学习宝典(从入门到精通)
qq_33509937的博客
07-04 183
数据库，用来存储数据。由表组成，可以有0到n张表，表就像是Excel，它适合存放一些简单的文字信息——一个人的身份证号码电话号码姓名Excel不适合存放什么信息呢？照片电影小说这些单个大体量的文件对象，不会放在数据库里。比如说，淘宝上所有的商品，背后的商品信息、店铺信息、销量等数据，就躺在数据库中的表格中，但是店铺上的宣传视频、图片、音乐，不会放在数据库中。世界上有各种各样的数据库，但幸运的是，你只需要学会MySQL基本使用方法，就可以应对绝大多数的场景。MySQL是什么？
Mysql学习笔记（全）
m0_67402914的博客
06-06 1530
目录MyISAM和InnoDB性能下降SQL慢的原因：Mysql执行顺序SQLJoin索引索引的优劣索引分类mysql索引结构那些情况建索引哪些情况不要建索引性能分析Extra索引优化索引口诀索引案例Order By 索引优化exsitesMysql慢查询日志命令函数操作批量插入数据show Profile分析sql全局查询日志Mysql锁锁命令表锁：读锁表锁：写锁行锁事务行锁测试间隙锁行锁总结对比MyISAMInnoDB主外键不支持支持事务不支持支持行表锁表锁，操作时即使操作一条记录也会锁住一整张表，不适
学习MySQL这一篇就够了
热门推荐
轻松的小希
11-25 21万+
第一章 数据库概述 1.1、数据库的好处 将数据持久化到本地 提供结构化查询功能 1.2、数据库的常见概念 DB：数据库，存储数据的仓库 DBS：数据库管理系统，又称为数据库软件或者数据库产品，用于创建和管理DB，常见的有MySQL、Oracle、DB2、SQL Server SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言 1.3、数据库的存储特点 数据存放到表中，然后表再放到库中 一个库中可以有多张表，每张表具有唯一的表名用来标识
MySQL 连接出现 Authentication plugin ‘caching_sha2_password‘ cannot be loaded
qq_39788123的博客
09-06 4210
出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password, 解决问题方法：把mysql用户登录密码加密规则还原成mysql_native_password.ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';#更新一下用户的密码。进入mysql 命令：mysql -u root -p。
mysql my.ini 配置文件
I_jump的博客
07-15 1517
[mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 [mysqld] #设置3306端口 port = 3306 # 设置mysql的安装目录 basedir=K:/mysql-5.7.23 # 设置mysql数据库的数据的存放目录 datadir=K:/mysql-5.7.23/data # 允许最大连接数 max_connections=2...
mysql8.0新增用户及密码加密规则修改
lllllyyyy的博客
01-13 1441
. 用户创建 创建用户的操作已经不支持grant的同时创建用户的方式，需先创建用户再进行授权 mysql> grant all on *.* to 'admin'@'%' identified by 'admin123'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual tha...
MySQL 8 新特性问题
小胖_@的博客
09-01 169
MySQL 8 新特性问题 遇到的问题 Authentication plugin caching_sha2_password cannot be loaded 问题原因 使用Navicat Premium 12连接MySQL数据库。 mysql8 之前的版本中加密规则是mysql_native_password, 而在mysql8之后,加密规则是caching_sha2_password。 # 解决方法： 一种是升级navicat驱动 一种是把mysql用户登录密码加密规则还原成mysql_native
MySQL学习笔记：DQL查询语句详解
"本篇是关于MySQL学习笔记的第六部分，主要关注DQL（Data Query Language），即查询语句的使用。通过创建一个名为`student02`的表，包含了id、NAME、age、sex、address、math和english等字段，并插入一些学生数据，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值