MYSQL基础篇

1、常见面试题

2、学习内容

1、数据库概念

2、 常见的数据库管理系统

——SQL定义操作所有关系型数据库的统一标准，可以使用SQL操作所有的关系型数据库管理系统

• Oracle：收费的大型数据库，Oracle 公司的产品
• MySQL：开源免费的中小型数据库。后来 Sun公司收购了 MySQL，而 Sun 公司又被 Oracle 收购
• SQL Server： MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用
• PostgreSQL：开源免费中小型的数据库
• DB2：IBM 公司的大型收费数据库产品
• SQLite：嵌入式的微型数据库。如：作为 Android 内置数据库
• MariaDB：开源免费中小型的数据库

2、MySQL数据模型

• mysql属于Oracle(甲骨文)公司，是目前最主流的一种关系型数据库。

• 优点：

  • mysql是开源、免费的，可以根据需求修改源码开发自己的mysql系统

  • mysql支持大数据量存储，可以处理千万级数据。支持多系统运行，支持多种编程语言。

  • mysql使用标准sql数据语言形式

1、关系型数据库

• 建立在关系模型基础上的数据库（由多张能互相连接的二维表组成的数据库）
• 数据以表格形式存储，每行代表不同得记录(对象)，每列代表每行记录对应的数据项，许多行和列共同组成了一张表
• 常见关系型数据库：Mysql、Oracle、SqlServer、DB2

如下图，员工表 和 部门表 都是有行有列二维表，我们将这样的称为关系型数据库。

接下来看关系型数据库的优点：

• 都是使用表结构，格式一致，易于维护。
• 使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
• 数据存储在磁盘中，安全。

扩展：非关系型数据库

• 非关系数据库(NOSQL)：没有特定关系的数据库，没有表的概念，数据和数据直接没有必要逻辑关系，适合存储海量复杂的碎片化数据 ，读写速度非常快，一般是基于内存存储。
• 常见的nosql数据库：Redis、Mongodb

2、数据模型：

——我们在客户端可以通过数据库管理系统创建数据库，在数据库中创建表，在表中添加数据。创建的每一个数据库对应到磁盘上都是一个文件夹。

——上图中右边的 db.frm 是表文件（每张表对应到磁盘上一个 frm 文件），db.MYD 是数据文件（每张表的数据会被存储到磁盘中对应MYD文件中），通过这两个文件就可以查询到数据展示成二维表的效果。

扩展：数据库三范式

——设计数据库表时遵从一些规范和要求，才能设计合理的表结构，这些规范和要求就是范式。范式一种分层结构，主要包含六层，每一层都必须比上一层更加严格。如果需要设计下一层范式，前提时必须满足上一层范式。

——六层范式简称: 1NF 2NF 3NF… 6NF(最高层/最严格)，但是mysql是关系型数据库不仅仅考虑数据安全合理，也需要保证效率的问题，所以一般情况下只需要满足前三种范式即可。

• 第一范式(1NF)：要求设计数据库字段具有原子性，不可再分。（不能把类似于对象、集合、数组等不具有原子性数据当成字段）
• 第二范式(2NF)：在满足1NF前提下，要求每张表数据必须保证唯一，本质上就是强制要求表必须添加主键，防止数据冗余。
• 第三范式(3NF)：保证表中不包含其它表的非主键列，避免数据的冗余。
  • 例：员工表t_emp与部门表t_dept，员工表包含dept_id字段作为外键，而不包括部门表的非主键列。

三范式减少了数据冗余（避免在多张表中存储相同的非主键列以节省空间），提高了数据完整性（更精确、可靠）。

三范式不要求必须遵循，数据量体量特别大的地方不要求满足第三范式，以提高查询的效率。

范式化的优点是数据库更新起来更快、可以只修改更少的数据等，缺点是查询容易产生较多关联查询，且不能建立复合索引，所以查询性能会大大降低。

所以在设计数据库时，不一定强制要求使用标准范式化设计，可以进行一定的调整。一般的设计是混用范式化和反范式化，适度进行数据冗余，以提高查询性能。

扩展：数据库命名规范

表命名规范：

• 全部小写命名，禁止出现大写
• 禁止使用数据库关键字，如：name，time ，datetime，password等
• 用单数形式表示名称，多个单词用_分隔
• 表必须填写描述信息（使用SQL语句建表时）

字段命名规范：

• 全部小写命名，禁止出现大写
• 禁止使用数据库关键字，如：name，time ，datetime，password等
• 使用下划线进行分类，user_name，user_id，is_login。

索引命名规范：

• 主键索引名为pk_字段名

• 唯一索引名为uk__字段名_

• 普通索引名则为idx_字段名。

3、SQL

——Structured Query Language，结构化查询语言，一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准

3.1、通用语法

1). SQL语句可以单行或多行书写，以分号结尾。

2). SQL语句可以使用空格来增强语句的可读性。

3). MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

4). 注释：

单行注释：-- 注释内容 或 # 注释内容(mysql特有)

多行注释：/ 注释内容 /

3.2、 SQL分类

SQL语句分五类：DDL数据定义语言、DML数据操控语言、DQL数据操控语言、DCL数据库控制语言、TCL事务控制语言。

1、DDL（Data Definition Language）数据定义语言，用于操作表结构等，包括create创建、drop删除、alter修改、truncate截断；

• Create语句，可以创建数据库和数据库的一些对象；
• Drop语句，可以删除数据表、索引、触发程序、条件约束以及数据表的权限等；
• Alter语句，修改数据表定义及属性；
• Truncate语句，删除表中的所有记录,包括所有空间分配的记录被删除。但表及其约束、索引等保持不变。

2、DML（Data Manipulation Language）数据操控语言，用于操作表中数据，包括insert插入、delete删除、update更新；

• Insert语句，向数据表张插入一条记录；
• Delete语句，删除数据表中的一条或多条记录，也可以删除数据表中的所有记录，但是它的操作对象仍是记录；
• Update语句，用于修改已存在表中的记录的内容；

3、DQL（Data Query Language）数据查询语言，用于查询，包括select查询；

• Select语句，用于查询已存在表中的记录的内容。

4、DCL（Data Control Language）数据库控制语言，用于设定用户权限，包括grant授权、revoke撤销；

• Grant语句，允许对象的创建者给某用户或某组或所有用户（PUBLIC）某些特定的权限；
• Revoke语句，可以废除某用户或某组或所有用户访问权限；

5、TCL（Transaction Control Language）事务控制语言，用于控制事务，包括commit提交、rollback回滚。

• commit，提交事务；
• rollback，回滚事务；

3.3、Mysql数据类型

MySQL 支持多种类型，可以分为三类：

• 数值类型

——age tinyInt unsigned（用tinyInt节省空间，unsigned表示无符号）

——score double(4，1) （一个4位，小数位占1位）

• 字符串类型

• 日期类型

面试题：Mysql大字段

• MySQL大字段是指数据库中存储的数据类型中长度较长的一类数据，包括TEXT、BLOB、MEDIUMTEXT、MEDIUMBLOB、LONGTEXT、LONGBLOB等数据类型。这些数据类型可以存储大量的文本、二进制数据或图像等。

• MySQL大字段的优点

  • 能够存储大量的文本或二进制数据。
  • 可以提高数据库的灵活性和可扩展性。
  • 可以提高数据库的查询效率。

  MySQL大字段的缺点

  • 存储大量数据会占用较多的存储空间。

  • 对于大量数据的查询和更新操作会影响数据库的性能。

  • 对于大量数据的备份和恢复操作也会影响数据库的性能。

4、SQL详解

• sql语句规范全都大写，可用ctrl+shift+u切换。

• 字段引用时规范使用反引号+字段名。

• SQL语句执行顺序：from -> on ->join ->where -> group by ->聚合函数 ->having ->select ->distinct ->order by ->limit

扩展：SQL哪些操作支持事务

• 只有DML支持事务
• DML如何实现事务？
  • InnoDB存储引擎：提供一组用来记录事务活动的日志文件。
    • 在事务的执行过程中，每一天DML的操作都被记录在日志文件中，我们可以提交事务也可以回滚事务。

4.1、DDL：操作数据库

1、库操作

——建议设置字符集为utf8mb4；

create database/schema 数据库名 collate utf8_general_ci character set utf8;

2、表操作

——先指定数据库，再进行操作

2.1、表查询：

查询当前数据库所有表

show tables;

查询表结构

desc 表名;

查询指定表的建表语句

show creat table 表名;

2.2、表操作：

2.2.1、建表

2.2.2、修改表

添加字段

alter table 表名 add 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

修改数据类型

alter table 表名 modify 字段名 新数据类型 (长度);

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

删除字段

alter table 表名 drop 字段名; 

修改表名

alter table 表名 rename to 新表名;

2.2.3、删除表

删除表

drop table [ if exists ] 表名;

truncate table 表名;

4.2、DML：操作数据

——在数据库中每一行数据就代表一个对象。

——添加（Insert）、删除（Delete）、修改（Update）

4.2.1、添加数据

4.2.2、修改数据

update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ]; 

注意：修改语句的条件如果没有，则会修改整张表的所有数据。

4.2.3、删除数据

delete from 表名 [ WHERE 条件 ];

注意：删除语句的条件如果没有，则会删除整张表的所有数据。

​

4.3、DQL：查询数据（重难点）

——查询（Select）

4.3.1、基本查询

 select sname,ssex,sbirthday,'学生' 角色 from t_student union all  //此时'学生'可以当作内容，角色为它的别名
select tname,tsex,tbirthday,'教师' from t_teacher;

4.3.2、条件查询

4.3.3、聚合函数

——不加group by情况下默认整张表为一组。

使用count(*)的效率要高于count(1)，因为MySQL对count(*)做了特殊的优化。count(*)与count(1)都会计算null值。

4.3.4、分组查询

• 普通属性无法和聚合函数同时查询，除非普通属性出现在group by中（也就是分组字段）。
• group by 一般和聚合函数一起使用才有意义，having是对分组后的字段进行再次过滤筛查，一般是用聚合函数。

4.3.5、排序查询

select e.sal,e.comm from emp e order by e.sal desc ,e.comm desc ;

4.3.6、分页查询

例子：查询第二页数据，查询记录数为10，则起始索引=(2-1)*10=10

select * from student limit 10,10;

扩展：DQL执行顺序

——注意执行顺序，这是写语句的标准

4.4、用户管理

——开发人员使用少，了解即可

4.4.1、创建用户

4.4.2、权限管理

1、权限种类

——具体查看官方文档(https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html)

2、权限管理

5、函数

• 函数是指一段可以直接被另一段程序调用的程序或代码

• mysql内置了很多函数供我们使用，聚合函数就是其中一种，可搭配SQL语句使用，并且可以传递0-n个参数同时返回一个结果，直接调用即可

Mysql函数主要有两大分类:

聚合函数：对多条数据只会返回一个结果，比如：count(字段/*/1)、max(字段)、min(字段)

单行函数：对每行数据都会返回一个结果，比如：截取字符串…

5.1、字符串函数

案例：1、截取字符串

select substring('Hello MySQL',1,5);

2、由于业务需求变更，企业员工的工号统一为5位数，目前不足5位数的全部在前面补0。比如： 1号员工的工号应该为00001

update emp set workno = lpad(workno, 5, '0');

补充：1、REPLACE(str,from_str,to_str) 去除或替换字符串中的某个字符

——str为原字符串，from_str为原字符串中要替换的指定字符

例子：删除用户名称中的666

update emp set name=replace(name,'666','') where name='柳岩666';

5.2、数值函数

例：通过数据库的函数，生成一个六位数的随机验证码。

select Rpad(round(rand()*1000000 , 0), 6, '0');

5.3、日期函数

例：查询所有员工的入职天数，并根据入职天数倒序排序。

select name,datediff(curdate(),entrydate) 入职天数 from emp order by 入职天数 desc ;

扩展：

#日期时间
select now();

#日期转换为字符串
select date_format(now(),'%Y年/%m月/%d日  %H时:%i分:%s秒');
#日期字符串转时间
select str_to_date('2023年/08月/24日  14时:35分:18秒','%Y年/%m月/%d日  %H时:%i分:%s秒');

#日期计算(通过秒来计算)
select date_add(now(),INTERVAL 3600*24*30 second );

5.4、流程函数

例：1、统计班级各个学员的成绩，>=85展示优秀，>=60展示及格，否则展示不及格。

select
id,
name,
(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end )'数学',
(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格'end ) '英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格'end ) '语文'
from score;

2、查询emp表的员工姓名和工作地址 (北京/上海 工作地址设为 一线城市 , 其他 工作地址设为 二线城市)

select
name,
( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else
'二线城市' end ) as '工作地址'
from emp;

——注意多条件时怎么表示

3、面试题：将属性值作为字段显示

select username,
  max(if(kemu='语文',score,null)) 语文,
  max(if(kemu='数学',score,null)) 数学,
  max(if(kemu='英语',score,null)) 英语
 from score group by username;
或
select username,
  max(case when kemu='语文' then score end) 英语,
  max(case when kemu='数学' then score end )数学,
  max(case when kemu='英语' then score end) 英语
from chengji group by username;

6、约束

• Mysql约束限定表中的数据，用于保证表中数据是准确可靠的。凡是不符合约束的数据，插入或者修改时就会失败。添加约束时可以在创建表时添加，也可以表创建成功后单独对某一字段设置约束。

6.1、概述

——唯一键可以为空。

案例：

也可用工具直接创建：

6.2、主键约束特点

• 唯一性：主键是唯一的，不能有重复值。（一般id利用auto_increment设置为自增）
• 非空性：主键的值不允许为空。
• 不可更改性：主键的值在创建后不允许更改。
• 单一性：一个数据库表只能有一个主键。

扩展：联合主键

//将id与name字段联合作为主键
alter table emp add constraint foreign key (`id`,`name`);

• 注：主键一般来说一个表必须要添加，否则不符合规范。但是一个表有且只有一个主键，联合主键也是一个主键。

6.3、外键约束(重点)

——特别注意：外键是添加到子表中的，也就是说只有子表才有外键

——只要是两张表之间有关联，都要添加外键来保证数据的一致性和完整性

——特别注意：添加外键是给子表添加，上图表的外键字段就是dept_id

扩展：主表与子表是什么？

在MySQL中，主表与子表是指两个或多个数据表之间的关系。主表是指一个包含主键的数据表，子表则是指与主表关联的数据表。

在主表中，主键是唯一标识每一行数据的字段。 在子表中，外键是指与主表中主键相关联的字段。

6.3.1、相关语法

例子：为emp表的dept_id字段添加外键约束，关联dept表的主键id。

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

扩展： 逻辑外键和物理外键

物理外键：对某表的字段使用foreign key作为外键关联另外一张表、字段。（真实用语句建立外键）

逻辑外键：不使用foreign key，仅仅是在建表时上产生逻辑关联。

• 在实际应用中使用逻辑外键，因为没有约束，用户使用时程序员可以通过代码来限制用户操作。
• 不推荐物理外键是因为约束太多，当表很多时难以维护。

6.3.2、更新删除行为

——注意：默认是no action，效果与restrict相同，都是不允许对应外键的删除/更新

例子：1、设置 更新删除行为为CASCADE

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

——这个时候如果修改主表id=6，那么子表dept_id=1的也会相应变成dept_id=6。删除主表id=6的数据子表也会相应删除。

也可通过工具更改：

​

7、多表查询

7.1、多表关系

1、情况一

2、情况二

3、情况三

——外键字段设为唯一，以保证每一个userid对应唯一的id

7.2、分类

7.3、连接查询

数据准备

-- 创建dept表，并插入数据
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办'), (6, '人事部');


-- 创建emp表，并插入数据
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';

-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

7.3.1、内连接

例：查询每一个员工的姓名 及关联的部门的名称

表结构: emp , dept

连接条件: emp.dept_id = dept.id

1、隐式内连接
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;

-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

2、显示内连接
select emp.name, dept.name from emp join dept on emp.dept_id = dept.id;

——注意事项：一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

扩展：

• 自然连接：会自动以两张表中字段名相同的字段关联查询(nature join)
• 笛卡尔积：进行表连接操作时没有指定连接条件（ON）或者连接条件不正确，就有可能产生笛卡尔积。笛卡尔积是指两个表的所有可能组合的行数。（A表3行数据，B表10行数据，产生的笛卡尔积为30行数据）

7.3.2、外连接

例：查询emp表的所有数据, 和对应的部门信息

左连接
select emp.*, dept.name from emp left join dept on emp.dept_id = dept.id;

特别注意：主表的数据无论是否满足关联条件都会全部展示，副表只会显示满足关联条件的数据，不满足的数据显示null。

7.3.3、自连接

• 自连接不是一种连接的类型，只是一种连接的用法。本质上讲，就是一张表当成2张或者3表来处理，实现自己和自己关联。

——特别注意：自连接必须取别名

例：

1、查询员工及其所属领导的名字
select a.name,b.name 所属领导 from emp a ,emp b where a.managerid=b.id;

2、查询所有员工及其领导的名字, 如果员工没有领导, 也需要查询出来
select a.name 员工,b.name 所属领导 from emp a left join emp b on a.managerid=b.id;

7.4、联合查询

——mysql只要列数保持一致，不要求名称和类型一致。要求多个查询语句必须有相同个数查询字段，查询成功后显示字段名以第一个查询为准。

7.5、子查询

——概念：**SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。**子查询外部语句可以是INSERT/UPDATE/DELETE/SELECT中的任意一个。

SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2)

7.5.1、标量子查询

——子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

——常用的操作符：= 、 >、 >=、 <、 <=

例： 查询 “销售部” 的所有员工信息

第一步：查询"销售部"的id
select id from dept where name = '销售部';

第二步：根据"销售部"id查询的所有员工信息
select * from emp where dept_id = (select id from dept where name = '销售部'); 

7.5.2、列子查询

例：1、查询 “销售部” 和 “市场部” 的所有员工信息

第一步：查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name='销售部' or name='市场部';

第二步：根据部门ID, 查询员工信息
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');

2、查询比财务部所有人工资都高的员工信息

第一步：查询所有财务部人员工资
select id from dept where name='财务部';
select salary from emp where dept_id=(select id from dept where name='财务部');

第二步：比财务部所有人工资都高的员工信息
select * from emp where salary> all(select max(salary) from emp where dept_id=(select id from dept where name='财务部'));

7.5.3、行子查询

——子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

——常用的操作符：= 、<> 、IN 、NOT IN

例：查询与 “张无忌” 的薪资及直属领导相同的员工信息

第一步：查询 "张无忌" 的薪资及直属领导
select salary,managerid from emp where name='张无忌';
select id from emp where name='张无忌';

第二步：查询与 "张无忌" 的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid)= (select salary,managerid from emp where name='张无忌') and id!=(select id from emp where name='张无忌');

7.5.4、表子查询

——子查询返回的结果是多行多列，这种子查询称为表子查询

——常用的操作符：IN

例：查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息

第一步：查询入职日期是 "2006-01-01" 之后的员工信息
select * from emp where entrydate > '2006-01-01';

第二步：查询这部分员工, 对应的部门信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

面试题：exists和in的区别

exists ： 外表先进行循环查询，将查询结果放入exists的子查询中进行条件验证，确定外层查询数据是否保留。

• 简单来讲就是如果内层查询不存在，就不会执行外层查询。反之内层查询存在，才会执行外层查询。

• #语法
  select column1 from t1 where [conditions] and exists (select * from t2);
  
  #括号中的子查询并不会返回具体的查询到的数据，只是会返回true或者false，如果外层sql的字段在子查询中存在则返回true，不存在则返回false
  #即使子查询的查询结果是null，只要是对应的字段是存在的，子查询中则返回true
  

• #案例
  #查询部门id>2的所有员工
  #注意：使用exists一定要写连接条件，不然表中的数据都会查出来
  select * from emp e where exists(select id from dept d where id>2 and e.dept_id=d.id);
  -- 等价于
  for(){  -- 遍历员工信息 
     if(){ -- 判断员工部门大于2的员工是否存在 
     }
  }
  

in ： 先查询内表，将内表的查询结果当做条件提供给外表查询语句进行比较。

• #案例
  #查询部门id>2的所有员工
  select * from emp where dept_id in(select id from dept where id>2);
  -- 等价于双重for()
  for(){  -- 遍历员工信息  
  	for(){ -- 遍历表信息   
    }
  }
  

效率比较：

• 外层小表，内层大表（或者将sql从左到由来看：左面小表，右边大表）： exists 比 in 的效率高
• 外层大表，内层小表（或者将sql从左到由来看：左面大表，右边小表）： in 比 exists 的效率高

8、事务

——事务是一组操作（一组SQL语句）的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

——注意： 默认MySQL的事务是自动提交的，当执行完一条DML语句时，MySQL会立即隐式的提交事务。

经典案例：张三给李四转账1000

8.1、 事务操作

——注意：一旦开启事务，执行SQL语句对数据的修改都是临时性的，只有提交事务才会真正修改

方式1：将MYSQL自动提交改为手动提交

——这种方式是一直有效的，即使提交事务后，下一次仍然有效

方式2：开启事务

——这种方式一旦提交事务之后，下一次需要再次开启事务

注意事项：

• 之后做业务涉及到表的增删改(一般是多表)，一定要先开启事务，这样才能有回滚操作。

  begin;
  
  try(){
      操作SQL语句的方法;
  }catch(Exception e){   //SQL语句执行失败，抛出异常，执行rollback()操作。一旦执行回滚，事务结束，再执行commit()不会有任何效果。
      rollback();
  }
  commit();
  

• 无论是回滚还是提交，都会结束当前事务。回滚是将事务中的操作全部还原（无论操作成功与否），提交是将事务中成功执行的操作全部提交（修改是永久性的）。

8.2、四大特性(ACID)

• 一致性：无论是提交事务还是回滚事务，张三和李四的余额之和是不变的，保证数据的完整性。

• 隔离性：当多个用户并发操作数据库，比如操作同一张表，数据库为每一个用户开启的事务，不能被其他的事务所干扰或者影响，事务之间是彼此独立的。

• 持久性：一个事务一旦提交了，那么对数据库中数据的改变就是永久的，即使是在数据库发生故障时，也不会丢失事务提交的数据。

8.2.1、InnoDB是如何实现事务四大特性的

原子性：

• 通过undo Log日志实现
• 当事务对数据库进行修改时，InnoDB会生成与之对应的undo log。如果事务执行失败或者调用的rollback，导致事务需要回滚，InnoDB引擎会根据undo log中的记录，将数据回滚到之前的样子。

一致性：

• 在数据库层面，需要保证原子性，持久性，隔离性。同时需要确保字段的一致性。（如整型的字符不能传入其他格式，字符串的长度不能超过列的限制。）
• 在应用层面，需要开发者自己来保证的。（如从A转账给B一部分金额，需要保证从A将金额扣除多少，就要去给B增加多少金额）

隔离性：

• 事务间的写操作其实是靠MySQL的锁机制来实现隔离的，而事务间的写和读操作是靠MVCC机制来实现的。

持久性：

• MySQL操作通过I/O读写磁盘效率很慢，InnoDB为MySQL提供了缓冲池(Buffer Pool)，大大提高了效率

• 当从数据库读取数据时，会先从Buffer Pool中读取数据，如果Buffer Pool中没有，则从磁盘读取后放入到Buffer Pool中。

• 当向数据库写入数据时，会先写入到Buffer Pool中，Buffer Pool中更新的数据会定期刷新到磁盘中(此过程称为刷脏)。

问题提出：如果数据刚更新到Buffer Pool中还没来得及刷新到磁盘中时，MySQL突然宕机了，这就会导致数据丢失，造成事务的持久性无法保证了。

解决方法：通过redo log日志

• redo log采用的预写的方式记录日志，即先记录日志，再将数据更新Buffer Pool。这样即使MySQL宕机了，也能从redo log中恢复数据。

问题提出：MySQL中redo log和bin log的区别？

• 作用：

  • redo log是用来记录更新缓存的，为了保证MySQL就算宕机也不会影响事务的持久性。

  • binlog是用来记录什么时间操作了什么，主要有时间点，可以保证将数据恢复到某个时间点，也有用于主从同步数据的。

• 层次：

  • redo log是存储引擎InnoDB实现的，是独有的。
  • bin log是在MySQL服务器层面的，任何引擎都包括。

• 存储内容:

  • redo log是物理日志，包含了数据页的物理更改信息，例如哪个块的哪个字节被更改了。
  • binlog是逻辑日志，记录了数据库执行的SQL语句，以及数据更改的逻辑操作。

• 写入时机的区别：

  • redo log 在默认情况下是在事务提交时，进行刷盘的。可以通过参数：innodb_flush_log_at_trx_commit 来改变策略，可以不用等到事务提交时才进行刷盘。 如：可以设置成每秒提交一次。
  • binlog是在事务提交时写入。

8.2.2、InnoDB是如何实现事务的

Innodb通过 Buffer Pool ， LogBuffer ， Redo Log ， Undo Log 来实现事务

以⼀个update语句为例：

• Innodb在收到⼀个update语句后，会先根据条件找到数据所在的⻚，并将该⻚ 缓存在Buffer Pool 中
• 执⾏update语句，修改Buffer Pool中的数据，也就是内存中的数据
• 针对update语句⽣成⼀个RedoLog对象，并存⼊LogBuffe r中
• 针对update语句⽣成 undolog⽇志 ，⽤于事务回滚
• 如果事务提交，那么则把 RedoLog对象进⾏持久化 ，后续还有其他机制将Buffer Pool中所修改的数据⻚持久化到磁盘中
• 如果事务回滚，则利⽤undolog⽇志进⾏回滚

8.3、并发事务问题

——多个事务同时操作同一数据库时出现的问题

8.3.1、脏读

• 事务A正在访问数据，并且对该数据进行了修改，但是这种修改还没有提交到数据库中，这时候另外一个事务B也访问这个数据，然后使用了这个被A修改的数据，那么这个数据就是脏的，并不是数据库中真实的数据。这就被称作脏读。
• 解决办法:把数据库事务隔离级别调整到READ_COMMITTED
  • 让用户在更新时锁定数据库，阻止其他用户读取，直到更新全部完成才让你读取。

8.3.2、不可重复读

!

8.3.3、幻读

8.4、事务隔离级别

·

• Serializable (串行化)：可避免脏读、不可重复读、幻读的发生 > Repeatable read (可重复读)：可避免脏读、不可重复读的发生 > Read committed (读已提交)：可避免脏读的发生 > Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

• Serializable要求事务串行执行，也就是当前事务未提交，下一个事务会一直等待。这降低了性能。

9、Mysql深度分页问题

9.1、深度分页场景

分页语法：

select 字段列表 from 表名 limit offset(起始索引)，count(查询记录数);

select* from article LIMIT 1,3
select * from article LIMIT 3 OFFSET 1
//上面两种写法都表示取2,3,4三条条数据

执行语句：

select id,name,balance from account where update_time> '2020-09-19' limit 100000,10;

该查询分页需要扫描100000+10行数据，然后丢掉前面100000行记录，同时进行100000+10次回表，总计耗时7.5秒。

SQL变慢原因总结：

• limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。

  • offset表示偏移量

• limit 100000,10 扫描更多的行数，也意味着回表更多的次数。

• limit 深度分页问题本质原因：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉。这样就导致查询性能的下降。

9.2、优化方式

• 采用覆盖索引优化

  • 在需要查询的所有字段建立联合索引，也就是覆盖索引，避免回表操作，提升性能。

• 通过子查询优化

  • 优点：通用情况，利用子查询的聚簇索引查询100000数据处的id值，在查询之后的10条数据。

  select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) and a.update_time >= '2020-09-19' LIMIT 0,10;                 
  

• INNER JOIN 延迟关联

  • 跟子查询的优化思路其实是一样的，都是把条件转移到主键索引树，然后减少回表。不同点是，延迟关联使用了inner join代替子查询。

  • 在大数据量的情况下，inner join连接通常比子查询更快。

    原因在于JOIN连接可以利用数据库索引实现高效的查询，而子查询需要进行多次查询，从而导致效率降低。

  SELECT  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000, 1) AS  acct2 on acct1.id= acct2.id where acct1.update_time >= '2020-09-19' LIMIT 0,10;
  

• 标签记录法

  • 标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。
  • 缺点：每一次查询需要上一次查询到的id值，并且需要id连续自增。

  select  id,name,balance FROM account where id > 100000 order by id limit 10;
  

• between…and…

  • 很多时候，可以将limit查询转换为已知位置的查询，这样MySQL通过范围扫描between...and，就能获得到对应的结果。

  select id,name,balance FROM account where id between 100000 and 100010 order by id;
  

10、删除 MySQL 数据库中重复数据

• 使用 UNIQUE 约束，预防数据的重复

• 如果出现了重复数据

  • 查找重复记录

  SELECT column1, column2, COUNT(*) as count FROM table_name GROUP BY column1, column2 HAVING COUNT(*)> 1;
  //其中，table_name是需要查询的表名，column1和column2是组成重复记录的字段。这条SQL语句会返回每组重复记录中column1 和column2的值以及该组记录的重复次数count。
  例：select name,count(*) as count from user group by name having count(*)>1;
  

  • 找到重复记录后，我们需要将其删除，保留每组重复记录中的最小 id。

  DELETE t1 FROM table_name t1
  INNER JOIN (
  SELECT MIN(id) AS min_id, column1, column2 FROM table_name
  GROUP BY column1, column2 HAVING COUNT(*) > 1
  ) t2
  ON t1.column1 = t2.column1 AND t1.column2 = t2.column2
  WHERE t1.id > t2.min_id;
  
  例：
  delete t1 from user t1
  inner join( select min(id)，na'me as min_id,name from user group by name having count(*)>1 ) t2
  on t1.name=t2.name where t1.id>t2.min_id;
  

11、Mysql查询去重

• 使用DISTINCT关键字

  • 若存在两条完全相同的纪录，使用distinct关键字就可以去重。

    SELECT DISTINCT * FROM students;
    

  • 使用DISTINCT关键字来从结果集中去除重复行，只返回不同的值。例如，如果你有一个名为students的表，要查询不同的学生名字。

    SELECT DISTINCT name FROM students;
    

  • 使用DISTINCT会增加查询的性能开销，因为数据库系统需要比较和去重结果集中的行。在大型表上，这可能导致查询变得相当慢。

• 使用子查询过滤重复数据（只显示一条）

  Select * From students Where ID In (Select Max(ID) From students Group By name);
  

12、一条SQL语句的执行流程

实际上MySQL主要分为Server 层和存储引擎层，执行SQL语句时，主要就是通过这两层去实现的。

Server层主要包括：

• 连接器

  • 主要负责在用户登录数据库进行用户的身份认证，包括校验账户密码，权限等操作。
  • 当用户账户密码通过时，连接器会到权限表中查询该用户的所有权限，后续的所有操作都会根据你的权限去执行。
  • 后续只要不断开连接，即使管理员修改了该用户的权限，该用户也不会受到影响。

• 查询缓存(MySQL 8.0 版本后移除)

  • 连接建立后，执行查询语句的时候，会先查询缓存。MySQL会先校验这个sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。
  • 如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。
  • MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

• 分析器

  • MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析SQL语句是来干嘛的，分析器也会分为几步：
    • 词法分析：一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
    • 语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

• 优化器

  • 分析器完成分析后，通过优化器挑选出优化器认为最优的执行方案（执行计划）去执行SQL语句。（有时候可能也不是最优，比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。）

• 执行器

  • 当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息
  • 如果有权限，就会去调用存储引擎开放的的SQL接口，发送执行计划。
  • 然后存储引擎去磁盘上获取数据，并返回。如果开启了缓存，也会在缓存中存储一份。

13、timeStamp和dateTime区别

• 一个完整的日期格式如下：YYYY-MM-DD HH:MM:SS[.fraction]，它可分为两部分：date部分和time部分

  • date部分对应格式中的“YYYY-MM-DD”，time部分对应格式中的“HH:MM:SS[.fraction]”。
  • 对于date字段来说，它只支持date部分，如果插入了time部分的内容，它会丢弃掉该部分的内容，并提示一个warning。
  • 对于timeStamp和dateTime字段来说， 两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。

• 不同点：

  1、时区问题

  • timestamp会把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区返回。
  • datetime不会做任何改变，插入什么时间就是什么时间。

  2、存储范围与大小

  • TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。当然，对于跨时区的业务，TIMESTAMP更为合适。
  • timeStamp占用4个字节 ，datetime在mysql5.6占5个字节。

  3、存储null与now()

  • timeStamp会自动存储now()，dateTime存储null。
  • timeStamp可能与当前时间不同，因为会跟据UTC格式进行转换。datetime和当前时间一致。