MySQL8干货

1 基本概念

1 关系模型指的就是二维表格模型

2 MySQL中同一个数据库，不同的表格可以选择不同的存储引擎。

3 多对多

该关系中第一个表中的一个行可以与第二个表中的一个或多个行相关。第二个表中的一个行也可以与第一个表中的一个或多个行相关。通常两个表的多对多关系会借助第三张表，转换为两个一对多的关系。

4 删除数据库，表，都是用的drop

5 导入数据

mysql> source d:\练习脚本.sql

6 导出数据

在命令行客户端不登录mysql，使用mysqldump命令。

1 2	mysqldump -u用戶名 -p密码数据库名 > 脚本名.sql mysqldump -u用戶名 -p密码数据库名表名 > 脚本名.sql

2 SQL语句

1 SQL的分类

DDL语句：

数据定义语句，

DML语句：

数据操作语句

DQL语句：

查询语句

DCL语句：

数据控制语句

其他语句一般称为命令。

A：数据库的表中的数据是否区分大小写。这个的话要看表格的字段的数据类型、编码方式以及校对规则。

校对规则存在命名约定，以其相关的字符集名开始，中间包括一个语言名，并且以_ci、_cs或_bin结尾。其中_ci表示大小写不敏感、_cs表示大小写敏感、bin表示直接比较字符的二进制编码，即区分大小写。

B：sql中的关键字，比如：create,insert等，不区分大小写。但是大家习惯上把关键字都“大写”。

3 特殊符号

建议不要使用mysql的关键字等来作为表名、字段名、数据库名等，如果不小心使用，请在SQL语句中使用（飘号）引起来

单引号’’：

字符串和日期类型的数据值使用单引号''引起来，数值类型的不需要加标点符号。

双引号””：

列的别名有空格时，请加双引号。列的别名中没有空格时，双引号可以加也可以不加。
表的别名不能加双引号，表的别名中间不能包含空格。
as大小写都可以，as也完全可以省略。

去重

mysql可以在查询结果中使用distinct关键字去重。

select distinct 字段列表 from 表名称【where 条件】;

除

除：/ div（只保留整数部分）
div：两个数相除只保留整数部分

null

mysql中只要有null值参与运算和比较，结果就是null，底层就是0，表示条件不成立。

用于null判断：

is null ,is not null

“<=>”的意思为“安全等于”，能比较null

ifnull用法

实发工资 = 薪资 + 薪资 * 奖金比例
select ename as 姓名,
salary as 薪资,
commission_pct as 奖金比例,
salary + salary * ifnull(commission_pct,0) as 实发工资
from t_employee;

集合范围

xxxxxxxxxx 区间范围：between x and y [x,y]

4 系统预定义函数

字符串函数

函数	用法
`CONCAT(S1,S2,......,Sn)`	连接S1,S2,……,Sn为一个字符串
`CONCAT_WS(separator, S1,S2,......,Sn)`	连接S1一直到Sn，并且中间以separator作为分隔符
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

WITH ROLLUP 汇总

ROUND(AVG(salary),2 )

5 分组函数

count(字段/表达式)：只统计“字段/表达式”部分非NULL值的行数。

5.5 窗口

在“t_employee”表中查询每个部门中薪资排名前2的员工姓名、部门编号和薪资。
SELECT * FROM (
SELECT ename,did,salary,
DENSE_RANK() over (PARTITION BY did ORDER BY salary DESC) AS paiming
FROM t_employee) temp
WHERE temp.paiming <=2;

6 连接查询

合并查询，两个Select语句的列数必须是相同的。
（3）要实现A∪B的结果，那么必须是合并查询

7 大子句顺序

（1）from：

（2）on：

（3）where：

（4）group by：分组依据

（5）having：在统计结果中再次筛选（with rollup)

（6）order by：

（7）limit：

必须按照（1）-（7）的顺序编写子句。

8 GROUP BY

根据SQL的语法规则，在使用GROUP BY子句时，SELECT列表中的非聚合列（在这种情况下是eid, ename, did）必须在GROUP BY子句中列出或者是聚合函数的参数。

在启用了”only_full_group_by” SQL模式的情况下，MySQL要求SELECT列表中的每个非聚合列必须在GROUP BY子句中出现或者是聚合函数的参数。

1
2
3

SELECT did, job_id, gender, COUNT(*)
FROM t_employee 
GROUP BY did, job_id, gender;

1	SELECT eid,ename, did, COUNT(*) FROM t_employee;

#eid,ename, did此时和count(，不应该出现在select后面,因为COUNT()只返回一行而其他字段可能有多行。

1	SELECT did, COUNT(*) FROM t_employee GROUP BY did;

#分组统计时，select后面只写和分组统计有关的字段，其他无关字段不要出现，否则会引起歧义

9 having子句

where的条件是针对原表中的记录的筛选。where后面不能出现分组函数。
having子句是对统计结果（分组函数计算后）的筛选。having可以加分组函数。

10 limit子句

limit m,n
n：表示最多该页显示几行
m：表示从第几行开始取记录，第一个行的索引是0
m = (page-1)*n page表示第几页

11. 子查询

SELECT中的EXISTS型子查询

为了让查询正确执行，需要为子查询中的列使用别名。当使用子查询作为临时表时，子查询中的列需要被赋予别名，以便在外部查询中引用这些列。

12 MySQL支持的数据类型

整数类型

MySQL还支持在类型名称后面加小括号(M)，而小括号中的M表示显示宽度，M的取值范围是(0, 255)。int(M)这个M在字段的属性中指定了unsigned（无符号）和zerofill（零填充）的情况下才有意义。表示当整数值不够M位时，用0填充。`

如果整数值超过M位但是没有超过当前数据类型的范围时，就按照实际位数存储。当M宽度超过当前数据类型可存储数值范围的最大宽度时，也是以实际存储范围为准。那有什么用？？

bit类型

如果没有指定(M)，默认是1位。这个1位，那么表示只能存1位的二进制值。

显示二进制值，需要使用bin函数

小数类型

decimal(M,D)

字符串类型

存储引擎对于选择CHAR和VARCHAR是有影响的。

对于MyISAM存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
对于InnoDB存储引擎，使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际的长度存储的，比较节省空间，所以对磁盘I/O和数据存储总量比较好。

BLOB和TEXT类型

日期时间类型

TIMESTAMP类型的日期时间值在存储时会将当前时区的日期时间值转换为世界标准时间值，检索时再转换回当前时区的日期时间值，这会更友好。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

13 DDL

修改数据库编码

1 2	#修改数据库字符集和校对规则 ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称 COLLATE 字符集对应校对规则;

1	ALTER DATABASE this0_chapter3_two CHARACTER SET utf8 COLLATE utf8_general_ci;

注意，修改数据库编码只会影响之后创建的表的默认编码，之前创建的表不会受影响。

修改数据表编码

修改表的字符集和排序规则

1	ALTER TABLE 表名 CONVERT TO CHARACTER SET 字符集名称 COLLATE 排序规则;

– 示例：将表改为utf8mb4字符集和utf8mb4_unicode_ci排序规则

1	ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Latin1是ISO-8859-1的别名，有些环境下写作Latin-1。ISO-8859-1编码是单字节编码，不支持中文等多字节字符，但向下兼容ASCII。

MySQL中utf8字符集，它是utf8mb3的别称

使用SHOW语句查看数据库的默认编码。

1	SHOW VARIABLES LIKE 'character_set_database';

修改表结构：删除字段

1	alter table 表名称 drop column 字段名称;

1	alter table teacher drop column weight;

10.修改表结构：修改字段的数据类型

alter table 表名称 modify 【column】字段名称新的数据类型;

修改表结构：修改字段的名称

alter table 表名称 change 【column】旧字段名称新的字段名称新的数据类型;

14 DML

`truncate表和delete表的区别`

delete是一条一条删除记录的。如果在事务中，事务提交之前支持回滚。如果删除的是主键自增数据,再次添加不会从头编号,而是从最后一个id开始继续往下编号，再次添加会重新编号

truncate是把整个表drop，新建一张，效率更高。就算在事务中，也无法回滚。

复制表结构

仅仅是复制表结构，可以用create语句
CREATE TABLE department LIKE t_department;

同时复制表结构+数据
CREATE TABLE d_department AS (SELECT * FROM t_department);
如果select后面是部分字段，复制的新表就只有这一部分字段

15 约束

1 数据的完整性要从以下四个方面考虑

`实体完整性：例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性：例如：年龄范围0-120
`引用完整性：例如：员工所在部门，在部门表中要能找到这个部门
用户自定义完整性：例如：用户名唯一

2 约束类型

键约束：主键约束、外键约束、唯一键约束
Not NULL约束：非空约束
Check约束：检查约束
Default约束：默认值约束

3 所有的表级约束都可以在“information_schema.table_constraints”表中查看。

建约束会自动创建索引，提高查询效率。

约束是一个逻辑概念，它不会单独占用物理空间，

索引是一个物理概念，它是会占用物理空间。

列级约束和表级约束

6约束，mysql列级约束只支持4种默认，非空，主键，唯一

非空约束(列级约束)

在insert添加记录时，必须给所有没有提前指定默认值的非空约束字段赋值。

唯一键约束

索引名默认是字段名，复合唯一默认是第一个字段名。

主键约束

唯一键约束本身不带非空限制，如果需要非空，需要单独定义。
主键约束不用再定义NOT NULL，自身就带非空限制。

自增属性

并且自增字段只能是key字段，即定义了主键、唯一键等键约束的字段。一般都是给主键和唯一键加自增。

检查约束

如果省略或指定为ENFORCED，则会创建检查约束并强制执行约束，不满足约束的数据行不能插入成功。

如果写的是not ENFORCED，则不满足检查约束也没关系。

就算是定义在字段后面，虽然此时和其他字段没关系，也是当成表级约束对象

外键约束（了解）

外键约束会影响性能，效率，所以很多人不愿意加外键约束。

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

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

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

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

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

外键约束的删除，索引不会自动删除，如果要删除对应的索引，必须手动删除

#主键字段索引名是PRIMARY，删除主键时，会自动删除对应索引
#唯一键字段索引名是字段名，删除唯一键时，就是通过删除对应的索引方式来删除唯一键约束
#外键字段索引名是字段名。#说明，删除外键约束时，不会自动删除外键字段的索引，因为它们的命名不一样，如果需要删除对应的索引，需要单独删

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

foreign key (departmentid) references dept(did) on update cascade on delete set null

16. 事务

1 事务的开启、提交、回滚

MySQL默认情况下是自动提交事务。

开启手动提交事务模式

set autocommit = false; 或 set autocommit = 0;

恢复自动提交模式
set autocommit = true; 或 set autocommit = 1;

说明：DDL不支持事务

truncate不可以回滚，不支持事务

2 事务特性：ACID

原子性：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性：事务前后数据的完整性必须保持一致。
隔离性：事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰,正常情况下数据库是做不到这一点的,可以设置隔离级别,但是效率会非常低。
持久性：一个事务一旦被提交，它对数据库中数据的改变就是永久性的。

3 并发访问问题

如果不考虑隔离性，事务存在3种并发访问问题。

脏读：一个事务读到了另一个事务未提交的数据.
不可重复读：一个事务读到了另一个事务已经提交(update)的数据。引发另一个事务，在事务中的多次查询结果不一致。
虚读 /幻读：一个事务读到了另一个事务已经提交(insert)的数据。导致另一个事务，在事务中多次查询的结果不一致。

4 隔离级别：解决问题

数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。

read uncommitted 读未提交，一个事务读到另一个事务没有提交的数据。

a)存在：3个问题（脏读、不可重复读、虚读）。

b)解决：0个问题
read committed 读已提交，一个事务读到另一个事务已经提交的数据。

a)存在：2个问题（不可重复读、虚读）。

b)解决：1个问题（脏读）
repeatable read:可重复读，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交。

a)存在：1个问题（虚读）。

b)解决：2个问题（脏读、不可重复读）

4.serializable 串行化，同时只能执行一个事务，相当于事务中的单线程。

a)存在：0个问题。

b)解决：3个问题（脏读、不可重复读、虚读）

安全和性能对比
- 安全性：serializable > repeatable read > read committed > read uncommitted
- 性能： serializable < repeatable read < read committed < read uncommitted
常见数据库的默认隔离级别：
- MySql：repeatable read
- Oracle：read committed

设置数据库的隔离级别

set session transactionisolation level 级别字符串
级别字符串：readuncommitted、read committed、repeatable read、serializable
例如：set session transaction isolation level read uncommitted;

17. 用户管理

创建用户

CREATE USER ‘用户名‘@’主机名’ IDENTIFIED BY ‘密码’;

授权格式

1	GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';

撤销授权

REVOKE 权限1, 权限2… ON 数据库.表名 FROM ‘用户名‘@’主机名’;

查看权限

1	SHOW GRANTS FOR '用户名'@'主机名';

删除用户

1	DROP USER '用户名'@'主机名';

修改用户密码

修改管理员密码

1	mysqladmin -uroot -p password 新密码 -- 新密码不需要加上引号

修改普通用户密码

set password for ‘用户名‘@’主机名’ = password(‘新密码’);

18. MySQL8的部分新特性

从 MySQL 8.0 开始，mysql 系统表和数据字典表使用 InnoDB 存储引擎，存储在 MySQL 数据目录下的 mysql.ibd 表空间文件中。

在 MySQL 5.7 之前，这些系统表使用 MyISAM 存储引擎，存储在 mysql 数据库文件目录下各自的表空间文件中。

19. 数据库三范式

第一范式（1NF）是最基本的范式。确保每列具有原子性（不可再分），消除重复组。

第二范式: 在满足1NF的基础上，确保表中的每行都能唯一区分

第三范式:非主键字段不能相互依赖

mysql5.1版本出现的新特性，是通过表动态生成的数据

20 后续内容

视图

下次可以直接用试图

会对原始表产生影响，一般只给查询权限

具备以下特点的视图不允许更新：（4个）

视图和表的区别

存储过程和函数

流程控制结构