数据库指的是以一定方式储存在一起、能为多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。它具有整体性、共享性。数据库软件有着整体性和共享性的特点。

原文链接：JavaWeb基础1——MySQL

概念

数据库（DataBase，DB）：存储数据的仓库，数据是有组织的进行存储。
数据库管理系统（DataBase Management System, DBMS）：管理数据库的大型软件。
SQL（Structured Query Language）：结构化查询语言。操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

关系型数据库

关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的二维表组成的数据库。优点：

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

MySQL的数据库在mysql的data目录下

常用关系型数据库管理系统

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

MySQL下载安装配置

官网下载安装：MySQL :: Download MySQL Community Server (Archived Versions)
配置my.ini：解压后在mysql安装路径下创建一个my.ini文件，更改my.ini文件里面的前两行安装目录，第二行加上\data。注意my.ini文件不能多一个符号或者少一个符号，第二行第三行改成自己的MySQL路径

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
# basedir=D:\\mysql\\mysql-8.0.25-winx64
basedir=D:\\DataBase\\mysql-8.0.25-winx64 # 切记此处要么双斜杠\\，要么单斜杠/，单斜杠\会出错
# 设置mysql数据库的数据的存放目录，MySQL 8+ 可以不需要以下配置，系统自己生成即可，否则有可能报错
datadir=D:\\DataBase\\mysql-8.0.25-winx64\\data # 此处同上
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

系统环境变量配置：在path中新增mysql的bin路径（D:\DataBase\mysql-8.0.25-winx64\bin），即mysql安装路径\bin。
初始化：进入命令指示符（在bin目录下运行cmd）

# 1.输入下面命令初始化数据库，并设置默认root为空，初始化完成后，在mysql根目录中会自动生成data文件
# 设置数据目录和创建系统数据库和表。
# initialize-insecure指示 MySQL 初始化数据目录，但不会设置 root 用户的密码。这意味着初始化后的数据库实例将没有 root 密码，用户可以直接连接到 MySQL 服务器并设置密码。
mysqld --initialize-insecure --user=mysql

# 2.再输入mysqld -install,为windows安装mysql服务，默认服务名为mysql
mysqld -install
# 出现service successfully installed.表示配置完成

启动数据库：

1	net start mysql

解决发生系统错误 2。系统找不到指定的文件

如果报错“发生系统错误 2。系统找不到指定的文件。”，则以下方法解决：

Win+R输入regedit打开注册表

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL 在该路径下找到MySQL映像文件ImagePath。

查看ImagePath里面本地安装MySQL的路径是否有误，如果有误修改相应的路径即可。

初始化密码：输入mysql -u root -p进行登录。

命令解读：

mysql是安装目录bin下的mysql.exe与服务mysql间进行通信

-u后跟账户名root

-p后先不设置密码

1	mysql -u root -p

不用输入密码直接回车

1	alter user user() identified by "你要设置的密码，例如1234";

mysql退出 mysql>quit;

exit;

其他命令（慎用）：
1
2
3
4
# 关闭数据库
net stop mysql
# 卸载服务
mysqld -remove mysql
最后删除目录和环境变量

获取当前数据库版本

使用 select version() 获取当前 MySQL 数据库版本。

SQL语句

概述

英文：Structured Query Language，简称 SQL
结构化查询语言，一门操作关系型数据库的编程语言
定义操作所有关系型数据库的统一标准
对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”

SQL 语句可以单行或多行书写，以分号结尾。
MySQL 数据库的 SQL语句不区分大小写，关键字建议使用大写。
注释
1. 单行注释：– 注释内容或 #注释内容(MySQL 特有)
2. 多行注释：/*注释 */

SQL语句分类

数据定义语言DDL（Data Definition Language）CREATE，DROP，ALTER
- 对逻辑结构等有操作的，其中包括表结构，视图和索引。
数据查询语言DQL（Data Query Language）SELEC
- 即查询操作，以select关键字。各种简单查询，连接查询等都属于DQL。
数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE
- 对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。
数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK
- 对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

数据定义语言DDL

mysql自带数据库

information_schema 是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。
mysql核心数据库，存储MySQL数据库里最核心的信息，例如权限、安全。
**sys**：系统数据库。
**performance_schema**主要用于收集数据库服务器性能参数（研究性能调优要用到）

数据库的增删查、使用

查询

1	SHOW DATABASES;

创建

-- 创建数据库
CREATE DATABASE 数据库名称;
-- 创建数据库(判断，如果不存在则创建)
CREATE DATABASE IF NOT EXISTS 数据库名称;

删除

-- 删除数据库面
DROP DATABASE 数据库名称;
-- 删除数据库(判断，如果存在则删除)
DROP DATABASE IF EXISTS 数据库名称;

使用数据库

-- 查看当前使用的数据库
SELECT DATABASE();
-- 使用数据库
USE 数据库名称;

表的增删查

-- 查询当前数据库下所有表名称
SHOW TABLES;
-- 查询表结构
DESC 表名称;  # desc是describe缩写，译为描述
-- 创建表
CREATE TABLE 表名 (
    字段名1  数据类型1,
    字段名2  数据类型2,
    …
    字段名n  数据类型n
); # 注意：字段名是列名。最后一行末尾，不能加逗号
# 删除表
-- 删除表
DROP TABLE 表名;
-- 删除表时判断表是否存在
DROP TABLE IF EXISTS 表名;
# 改表
-- 关键字rename，add，modify，change，drop
-- 修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
-- 将表名student修改为stu
alter table student rename to stu;
-- 添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
-- 给stu表添加一列address，该字段类型是varchar(50)
alter table stu add address varchar(50);
-- 修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
-- 将stu表中的address字段的类型改为 char(50)
alter table stu modify address char(50);
-- 修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
-- 将stu表中的address字段名改为 addr，类型改为varchar(50)
alter table stu change address addr varchar(50);
-- 删除列
ALTER TABLE 表名 DROP 列名;
-- 将stu表中的addr字段 删除
alter table stu drop addr;
-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

数据类型：数值、日期、字符串

数值

tinyint : 小整数型，占一个字节
int ： 大整数类型，占四个字节
    eg ： age int
double ： 浮点类型
    使用格式： 字段名 double(总长度,小数点后保留的位数)
    eg ： score double(5,2)

日期

date ： 日期值要带引号。只包含年月日
    eg ：birthday date 
time :  时间值或持续时间
year :  年分值
datetime ： 混合日期和时间值。包含年月日时分秒

字符串，要带引号

char ： 定长字符串。
    优点：存储性能高
    缺点：浪费空间
    eg ： name char(10)  如果存储的数据字符个数不足10个，也会占10个的空间，汉字占1个字符
varchar ： 变长字符串。
    优点：节约空间
    缺点：存储性能底
    eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间

char 和 varchar 的区别

char(n) ：固定长度类型，如 char(10)，当输入”abc”三个字符时，它们占的空间还是 10 个字节，其他 7 个是空字节。

优点：效率高；
缺点：占用空间；
适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

从空间上考虑 varcahr 比较合适；
从效率上考虑 char 比较合适，二者使用需要权衡。

float 和 double 的区别

float 最多可以存储 8 位的十进制数，并在内存中占 4 字节。
double 最可可以存储 16 位的十进制数，并在内存中占 8 字节。

int(20)中20的涵义

是指显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；
不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示

数据操作语言DML

DML添加数据

-- 给指定列添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);
-- 给全部列添加数据
INSERT INTO 表名 VALUES(值1,值2,…);
-- 批量添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;

示例

-- 给指定列添加数据
INSERT INTO stu (id, NAME) VALUES (1, '张三');
-- 给所有列添加数据，列名的列表可以省略的
INSERT INTO stu (id,NAME,sex,birthday,score,email,tel,STATUS) VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
 
INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
 
-- 批量添加数据
INSERT INTO stu VALUES 
    (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
    (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
    (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);

DML修改数据

UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;
# 注意：修改语句中如果不加条件，则将所有记录都修改！中括号，表示在写sql语句中可以省略这部分
# 示例
update stu set sex = '女' where name = '张三';

DML删除数据

DELETE FROM 表名 [WHERE 条件] ;
# 注意：删除语句中如果不加条件，所有记录都将被删除，慎重！中括号，表示在写sql语句中可以省略的部分
# 示例
-- 删除张三记录
delete from stu where name = '张三';
-- 删除stu表中所有的数据
delete from stu;

drop、delete与truncate的区别

在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

	delete	truncate	drop
类型	DML	DDL	DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行，不释放空间，不重置自增列	表结构还在，删除表方全部数据，重置自增列，释放空间	删除表结构、数据、索引、约束等全部内容，释放空间，表不复存在

数据查询语言DQL

查询的完整语法

SELECT 字段列表
FROM 表名列表 
WHERE 条件列表
GROUP BY 分组字段
HAVING 分组后条件
ORDER BY 排序字段
LIMIT 分页限定

ORDER BY

ASC ： 升序排列 （默认值）ascending /əˈsendɪŋ/
DESC ： 降序排列，descending /dɪˈsendɪŋ/

注意：

null不能和等号运算，要 IS NULL或 IS NOT NULL，而不是=null

SQL语句没有==，相等是=，没有赋值的概念。

查询字段并去除重复记录

1	SELECT DISTINCT 字段列表 FROM 表名;

带条件的聚合函数：count(name=’abcd’ or null)

聚合函数列表详见聚合函数

统计所有名字为‘abcd’的学生：

SELECT count(name='abcd' or null) FROM student;                        # 使用count带条件统计数量必须or null
SELECT count(date  between '2019-01-01' and '2019-03-31' or null)     # 使用count带条件统计数量必须or null
SELECT count(distinct name) FROM student;                            # 注意distinct不能or null
 
# 使用sum带条件统计数量不用or null。尽量别用sum，因为sum主要用来取和，如果这个name字段是数字型，则会是取和。
SELECT sum(name='abcd') FROM student;

注意：

使用count带条件统计数量必须or null，否则是统计总数量（条件是distinct除外）

使用sum带条件统计数量不用or null

分组查询

1	SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

注意：

分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

在分组的情况下，查询字段为聚合函数时，这个聚合函数统计的将是每组的信息

where 和 having 区别：

执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。

可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。执行顺序where>聚合函数>having，不可能判断后面执行的条件。

分页查询

1	SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数;

约束

概念

约束是作用于表中列上的规则，用于限制加入表的数据。例如：可以给id列加约束，让其值不能重复，不能为null值。
添加约束可以在添加数据的时候就限制不正确的数据。例如把年龄是3000，数学成绩是-5分这样无效的数据限制掉，继而保障数据的完整性。

常用约束

约束名	约束关键字	说明
主键	primary key	唯一，非空。控件字段内容不能重复，但它在一个表只允许出现一个。
唯一	unique	不能重复，最多只有一个非空记录。控件字段内容不能重复，一个表允许有多个 Unique 约束。
默认	default	没有输入值，使用默认值
非空	not null	必须输入
外键	foreign key … references	外键在从表主表：1方从表：多方。用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向表中的值之一
自增	auto_increment	从1开始自增，只有唯一和主键约束能用
检查(mysql不支持)	check	保证列中的值满足某一条件。用于控制字段的值范围。

常用命令

# 查询表中所有约束
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    TABLE_SCHEMA = '表名'
    AND TABLE_NAME = '表名';

# 删除约束（DROP CONSTRAINT）:
ALTER TABLE table_name
DROP PRIMARY KEY; 
-- 或 DROP FOREIGN KEY, DROP UNIQUE, 等等

增删约束

# 1.添加约束
-- 创建表时添加非空约束
CREATE TABLE 表名(
    列名 数据类型 NOT NULL,
    ...
)
-- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL
# 2.删除约束
ALTER TABLE 表名 MODIFY 字段名 数据类型

外键约束

-- 创建表时添加外键约束
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [外键取名名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
); 
 
-- 创建表时添加外键约束，constraint译作限制，束缚；references译作关联，参考，提及
create table 表名(
   列名 数据类型,
   …
   [constraint] [外键取名名称] foreign key(外键列名) references 主表(主表列名) 
); 

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
 
-- 建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);

-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

数据库设计

概念

软件研发步骤
数据库设计概念
- 设计方向：有哪些表？表里有哪些字段？表和表之间有什么关系？
- 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
- 建立数据库中的表结构以及表与表之间的关联关系的过程。
数据库设计的步骤
- 需求分析（数据是什么? 数据具有哪些属性? 数据与属性的特点是什么）
- 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
  
  如下图就是ER(Entity/Relation)图：
物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
维护设计（1.对新的需求进行建表；2.表优化）

表关系

一对多

如部门和员工。一个部门对应多个员工，一个员工对应一个部门。

实现方式：在多的一方建立外键，指向一的一方的主键
多对多

如：商品和订单。一个商品对应多个订单，一个订单包含多个商品。

实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。
一对一

如：用户和用户详情。一对一关系多用于表拆分，将一个实体中常用字段放一张表，不常用字段放另一张表，用于提升查询性能。

实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

数据库的三范式

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
第三范式：任何非主属性不依赖于其它非主属性。

多表查询

连接查询

概念

内连接查询：相当于查询AB交集数据
外连接查询
- 左外连接查询：相当于查询A表所有数据和交集部门数据
- 右外连接查询：相当于查询B表所有数据和交集部分数据

关联查询结果行数：假设a表x行，b表y行；

a左连接b：x行~x*y行
a右连接b：y行~y*x行
内连接：0行~min(x,y)行

内连接查询

相当于查询AB交集数据。

隐式连接好理解好书写，语法简单，担心的点较少。

但是显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显

-- 隐式内连接。没有JOIN关键字，条件使用WHERE指定。书写简单，多表时效率低
SELECT 字段列表 FROM 表1,表2… WHERE 条件;
-- 显示内连接。使用INNER JOIN ... ON语句, 可以省略INNER。书写复杂，多表时效率高
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

自连接

自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

注意：自连接查询的列名必须是“表名.*”，而不是直接写“*”

案例：要求检索出学号为20210的学生的同班同学的信息

1
2
3

SELECT stu.*        #一定注意是stu.*，不是*
FROM stu JOIN stu AS stu1 ON stu.grade = stu1.grade
WHERE stu1.id = '20210'

递归查询

recurslve译为递归。

with：在mysql中被称为公共表达式，可以作为一个临时表然后在其他结构中调用。如果是自身调用那么就是后面讲的递归。

cte_name：公共表达式的名称，可以理解为表名，用来表示as后面跟着的子查询

col_name ：公共表达式包含的列名，可以写也可以不写

# with语法
WITH [RECURSIVE]
    cte_name [(col_name [, col_name] ...)] AS (subquery)
    [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

例子：使用MySQL临时表遍历1~5

with RECURSIVE t1  AS                    # 这里t1函数名，也是临时表的表名
(
  SELECT 1 as n                            # n是列的别名，1是初始记录
  UNION ALL                				# 把递归结果（2,3,4,5）合并到t1表中
  SELECT n + 1 FROM t1 WHERE n < 5        # n+1是参数，t1是函数名，n<5是遍历终止条件
)
SELECT * FROM t1;                        # 正常查询t1这个临时表，相当于调用这个函数。

说明：

t1 相当于一个表名

select 1 相当于这个表的初始值，这里使用UNION ALL 不断将每次递归得到的数据加入到表中。

n<5为递归执行的条件，当n>=5时结束递归调用。

案例，递归查询课程多级分类：

with recursive t1 as (                                # t1是函数名、临时表名
    select * from  course_category where  id= '1'   # 初始记录，也就是根节点
    union all                                         # 把递归结果合并到t1表中
    select t2.* from course_category as t2 inner join t1 on t1.id = t2.parentid    
                                                    # 递归，用分类表t和临时表t1内连接查询
)
select *  from t1 order by t1.id, t1.orderby        # 查t1表，相当于调用这个函数。

mysql递归特点，对比Java递归的优势

mysql递归次数限制：

mysql为了避免无限递归默认递归次数为1000，可以通过设置cte_max_recursion_depth参数增加递归深度，还可以通过max_execution_time限制执行时间，超过此时间也会终止递归操作。

对比Java递归的优势：

mysql递归相当于在存储过程中执行若干次sql语句，java程序仅与数据库建立一次链接执行递归操作。相比之下，Java递归性能就很差，每次递归都会建立一次数据库连接。

外连接查询

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

一般都用左外连接，因为右外连接可用左外连接实现，可读性更好。

MySQL 的内连接、左连接、右连接的区别

内连接：inner join；左连接：left join；右连接：right join。
内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。

子查询

查询中嵌套查询，称嵌套查询为子查询。

注意：子语句没有分号。

子查询根据查询结果不同，作用不同，可分为：

子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断

1	SELECT 字段列表FROM 表WHERE 字段名 = (子查询);

1 2	-- 查询比猪八戒薪水高的员工 SELECT * FROM emp WHERE salary >(SELECT salary FROM emp WHERE name='猪八戒');

子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断

1	SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);

1 2	-- 查询 '财务部' 和 '市场部' 所有的员工信息 SELECT * FROM emp WHERE dep_id in (SELECT did FROM dept WHERE dname IN ('财务部','市场部'));

子查询语句结果是多行多列，子查询语句作为虚拟表

1	SELECT 字段列表 FROM(子査询)WHERE 条件:

1 2	-- 查询入职日期是 '2011-11-11' 之后的员工信息和部门信息 select * from (select * from emp where join_date > '2011-11-11' ) AS t1, dept where t1.dep_id = dept.did;

事务

概念

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。

事务是一个不可分割的工作逻辑单元。

语法

# 开启事务
START TRANSACTION;        --transaction译为事务，业务，交易
或者  
BEGIN;
# 提交事务
commit;

示例

-- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500
SELECT ... 
-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';
出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

上面sql中的执行成功进选择执行提交事务，而出现问题则执行回滚事务的语句。以后我们肯定不可能这样操作，而是在java中进行操作，在java中可以抓取异常，没出现异常提交事务，出现异常回滚事务。

事务的四大特征 ACID

原子性（Atomicity）：一个事务（transaction）中的所有操作，或全部完成，或全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即事务不可分割、不可约简。

一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。事务完成时，必须使所有的数据都保持一致状态。

隔离性（Isolation）：多个事务之间，操作的可见性。数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

说明：

mysql中事务是自动提交的。

也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

可以通过下面语句查询默认提交方式：
1
SELECT @@autocommit;
查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式
1
set @@autocommit = 0;

ACID-原子性：MySQL通过使用日志文件来实现事务的原子性

日志刷盘
当一个事务开始时，InnoDB会为该事务分配一个唯一事务ID，并开始记录重做日志和回滚日志。随着事务的执行，所有的数据修改都会先写入到重做日志缓冲区中，然后在适当时机，这些修改会被刷新到磁盘上的重做日志文件中。如果系统崩溃，重做日志将被用来重做事务，确保事务的修改被应用到数据库中。
Redo Log
事务执行过程中发生错误或系统故障时，如果数据修改已经写入重做日志缓冲区中，在适当的时机（系统恢复时），这些修改会被刷新到磁盘上的重做日志文件中。
Undo Log
在事务执行过程中发生错误或系统故障，且无法通过**Redo Log** 保证数据一致性和完整性，MySQL将利用日志来回滚事务中的所有操作，保证数据库的原子性不被破坏。

ACID-一致性：MySQL通过几种机制来保证数据的一致性

约束（Constraints）：MySQL允许定义各种约束（如主键约束、外键约束、唯一约束等），这些约束直接作用于数据表上，确保数据的完整性和一致性。
事务隔离级别（Transaction Isolation Levels）：通过合理选择事务隔离级别，可以在并发环境下有效地维护数据的一致性。读未提交（Read Uncommitted）、读提交（Read Committed）、可重复读（Repeatable Read）、串行化（Serializable）
锁定机制（Locking Mechanisms）：MySQL使用锁定机制来控制对数据的并发访问，包括行级锁（Row-Level Locks）、表级锁（Table Locks）等，以确保在并发事务中数据的一致性和完整性。
原子性操作（Atomic Operations）：MySQL通过原子性操作确保事务中的所有操作要么全部成功，要么全部失败，从而保持数据的一致性。

ACID-持久性：MySQL中保证数据持久性的技术

日志文件
- 重做日志（Redo Log）：InnoDB存储引擎使用重做日志来保证事务的持久性。事务提交时，事务所做的所有修改都会被记录到重做日志中。即使数据库发生崩溃，重做日志也可以在重启后被用来重放这些修改，确保数据的持久性。
- 二进制日志（Binary Log）：MySQL服务器使用二进制日志记录所有修改数据库数据的操作，如INSERT、UPDATE、DELETE等。二进制日志不仅对数据恢复至关重要，也是复制和增量备份的基础。
备份策略
- 全量备份：定期对整个数据库进行完整备份，以确保数据的持久性和可恢复性。
- 增量备份：仅备份自上次备份以来发生变化的数据。与全量备份结合使用，可以有效减少数据恢复时间和存储需求。
- 点对点恢复（Point-in-Time Recovery, PITR）：使用二进制日志实现，可以将数据库恢复到特定时间点的状态，非常适合处理操作错误或数据损坏的情况。

数据库的事务隔离

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：
transaction-isolation = REPEATABLE-READ
可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

READ-UNCOMMITTED：读未提交，事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
READ-COMMITTED：读提交，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

脏读：表示一个事务能够读取另一个事务中还未提交的数据。
不可重复读：是指在一个事务内，多次读同一数据。
幻读：指同一个事务内多次查询返回的结果集不一样。发生幻读的原因是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所以数据行的记录就变多或者变少了。

MySQL默认使用的是“可重复读”隔离级别，SQL标准的默认隔离级别是“读提交”。
MySQL在“可重复读”级别下通过多版本并发控制（MVCC）机制避免幻读的问题。

MySQL的 binlog 的几种录入格式及区别

有三种格式，statement，row和mixed。

statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

函数

数值型函数

函数名称	作用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入。例如round(3.1415926,3)是四舍五入保留三位小数
SIGN	返回参数的符号
POW 和 POWER	两个函数的功能相同，都是所传参数的次方的结果值
SIN	求正弦值
ASIN	求反正弦值，与函数 SIN 互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数 COS 互为反函数
TAN	求正切值
ATAN	求反正切值，与函数 TAN 互为反函数
COT	求余切值

字符串函数

函数名称	作用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

日期和时间函数

函数名称	作用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR	获取年份，返回值范围是 1970〜2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

聚合函数

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

注意：null 值不参与所有聚合函数运算

流程控制函数

函数名称	作用
IF(expr1,expr2,expr3)	判断，流程控制。expr1 的值为 TRUE，则返回值为 expr2 。否则返回 expr3
IFNULL（expr1,expr2）	判断是否为空。例如select ifnull(age,0) from stu where id=0;如果这个学生年龄是null则返回0
CASE	搜索语句

索引

MySQL 索引是怎么实现的？

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。
具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

创建索引的原则有哪些？

最左前缀匹配原则，组合索引非常重要的原则，mysql会一直**向右匹配直到遇到范围查询(>、<、between、like)就停止匹配**，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
较频繁作为查询条件的字段才去创建索引
更新频繁字段不适合创建索引
不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
定义有外键的数据列一定要建立索引。
对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
对于定义为text、image和bit的数据类型的列不要建立索引。

怎么验证 MySQL 的索引是否满足需求

使用 explain 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。
explain 语法：explain select * from table where type=1。

业务相关

导入数据后数据库数据重复原因及解决

表象原因：如网络延迟、用户连点、并发等
本质原因：在数据库设计、及代码逻辑的严谨性出了问题。

前端解决方案（不可靠，推荐）
用户点击按钮后，让按钮点击失效或者禁用，待后端响应完成后，按钮可用
数据库解决方案(可靠，推荐，适合高并发场景)

幂等主要手段就是通过表中的唯一约束实现。
添加表的唯一约束或唯一索引进行约束处理，是最有效的防治重复提交的方法
MySql 避免”重复插入记录”的方法：INSERT ignore into，Replace into，ON DUPLICATE KEY UPDATE
使用 ignore 关键字，若用主键 primary或唯一索引 unique 区分了记录的唯一性，避免重复插入记录可以使用

悲观锁解决方案(可靠，推荐，适合高并发场景)

悲观锁：以一种预防的姿态在修改数据之前把数据锁住，再对数据进行读写，释放锁之前任何人都不能对其数据进行操作，直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁，才可以对数据进行操作，一般数据库本身锁的机制都基于悲观锁;
特点：可以完全保证数据的独占性和正确性，因为每次请求都会先对数据进行加锁，然后进行数据操作，最后再解锁，而加锁释放锁的过程会造成消耗，所以性能不高;

幂等：其任意多次执行所产生的影响均与一次执行的影响相同
需要幂等的场景:

核心业务数据新增，如订单系统中新增订单、用户信息系统中新增用户；
审批流系统多人审批同一数据时，可能导致审批多次。
定时任务触发数据生成时可能重复触发，导致数据生成多次。

MySQL数据中有很多换行符和回车符怎么办

如何去掉换行符、回车符。如果条件允许，建议直接通过语句清除掉换行符和回车符，如下：

// 去除"hero title"列中所有换行符 (通过以下两种方式都可以)
update 'LOL' set hero_title = REPLACE(hero_title, '\n', '');
update 'LOL' set hero_title = REPLACE(hero_title, char(10), '');

// 去除"hero title"列中所有回车符
update 'LOL' set hero_title = REPLACE(hero_title, '\r', '');
update 'LOL' set hero_title = REPLACE(hero_title, char(13),'');

// 忽略掉换行符查询
SELECT * from 'LOL' where REPLACE(hero_title, char(10), '') = '换行符换行符换行符’; 
// 忽略掉回车符查询
SELECT * from 'LOL' where REPLACE(hero_title, char(13), '') ='回车符回车符回车符’;
// 忽略掉换行符 & 回车符查询
SELECT * from 'LOL' where REPLACE(REPLACE(hero_title, char(13), ''), char(18),'') = '回车符回车符回车符’;