Mysql总结

Mysql总结

版本:8.0.20

2021-11-28>初稿

2021-12-10>二次整理-剩余视图、索引、事务

2021-12-15>整理完所有的

中英对照

eg:例如

数据库的三大范式
1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割

2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分

3、第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖

一、数据库操作

1.查看数据库

shou databases [like wild]

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_web             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

2.创建数据库

create database [if not exists] 数据库名称;

eg: create database 数据库名称 [default charset=utf8mb4];

查看创建数据库：show create database <数据库名>;

3.删除

drop database [if exists] 数据库名;

4.切换

use 数据库名;

5.其他

查看当前连接的数据库: SELECT DATABASE();
查看数据库版本: SELECT VERSION();
查看当前用户: SELECT USER();
查看所有用户: SELECT User,Host FROM mysql.user;

6.数据库对象命名规则

1. 必须以字母开头

2. 可包括数字和三个特殊字符（# _ $）

3. 不要使用MySQL的保留字

4. 同一Schema下的对象不能同名

二、表的基本操作

1创建表

 CREATE TABLE 表名
 (column datatype[DEFAULT expr] ,
 …
 ) ENGINE = 存储机制
 
简单语法：
CREATE TABLE 表名（
 列名 列类型 comment `注释`，
 列名 列类型 comment `注释`
 ）;

eg: -> create table t_test (
    -> id 	int,
    -> name varchar(10)
    -> );

2查看表

show tables [from 数据库名][like wild];

mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| t_test            |
+-------------------+

查看表中各列信息

{DESCRIBE|DESC} 表名[列名];
# or
show columns from 表名称; 

查看更全面的表定义信息
show create table 表名 \G;  # \G 是使显示结果旋转90°
mysql> describe t_test;
mysql> desc t_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


mysql> show create table t_test \G;
*************************** 1. row ***************************
       Table: t_test
create Table: CREATE TABLE `t_test` (
  `id` int DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

4删除表

drop table [if exists] 表名;

5修改表的结构

修改列类型
alter table 表名 modify 列名 列类型;

增加列
alter table 表名 add 列名 列类型;

删除列
alter table 表名 drop 列名;

列改名
alter table 表名 change 旧列名 新列名 列类型;

更改表名
alter table 表名 rename 新表名;
rename table 表名 to 新表名;

6复制表的结构

复制一个表结构的实现方法有两种
方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下
create table 新表名 like 源表;

方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表
记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中
create table 新表名 select * from 源表;

方法三：如果已经存在一张机构一致的表，复制数据
insert into 表 select * from 原表;

7表的约束

为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束

约束条件	说明
PRIMARY KEY	主键约束用于唯一标识对应的记录
FOREIGN KEY	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
DEFAULT	默认值约束，用于设置字段的默认值

PRIMARY KEY		主键
FOREIGN KEY		外键
NOT NULL		非空
UNIQUE Key		唯一键
CHECK			检查
DEFAULT			默认值约束

---

约束的方法

约束作为数据库对象，存放在系统表中，也有自己的名字

---

创建约束的时机

1. 建表的同时创建
2. 建表后创建(修改表可定义列级或表级约束)

---

有单列约束和多列约束

定义约束的语法

列级约束：在定义列的同时定义约束
	语法：列定义 约束类型

表级约束：在定义了所有列之后定义的约束
    语法：
    列定义
    [CONSTRAINT 约束名] 约束类型(列名)

约束名的取名规则
	推荐采用：表名_列名_约束类型简介
约束可以在创建表时就定义，也可以在创建完后再添加

语法：
alter table 表名 add constraint 约束名 约束类型(要约束的列名)

7.1主键约束

主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号，并且这个身份证号是唯一的。

eg:字段名 数据类型 primary key;

create table t_pr(
    id int primary key auto_increment,
    name varchar(10));

mysql> desc t_pr;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

7.2外键约束

外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录

外键是构建于一个表的两个字段或者两个表的两个字段之间的关系
外键确保了相关的两个字段的两个关系：
子(从)表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。
当主表的记录被子表参照时，主表记录不允许被删除。

==!==外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。


# 1.在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
# 2.将创建数据表创号后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

1.创建时候添加
create table dept(
	id int primary key,
	name varchar(10)
);

create table emp(
    id int primary key ,
    name varchar(10),
    dept_id int,
    constraint emp_dept_id foreign key(dept_id) references dept(id)
);
# constraint 外键约束的别名
	
2.创建之后添加
create table emp (
	id int,
	name varchar(10),
	deptId int);
alter table emp add constraint emp_dept_id foreign key (dept_id) references dept(id);

# 查看创建表的结构
mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `dept_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_dept_id` (`dept_id`),
  CONSTRAINT `emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

7.3非空约束

非空约束即 NOT NULL指的是字段的值不能为空

eg:字段名 数据类型 NOT NULL;

create table t_not(
    id int not null,
    name char(20));
    
mysql> desc t_not;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

7.4唯一约束

唯一性约束条件确保所在的字段或者字段组合不出现重复值
唯一性约束条件的字段允许出现多个NULL
同一张表内可建多个唯一约束
唯一约束可由多列组合而成
建唯一约束时MySQL会为之建立对应的索引。
如果不给唯一约束起名，该唯一约束默认与列名相同。

create table t_un(
	id int unique);

mysql> desc t_un;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  | UNI | NULL    |       |
+-------+------+------+-----+---------+-------+

7.5检查约束

# 注意检查约束在8.0之前，MySQL默认但不会强制的遵循check约束（写不报错，但是不生效，需要通触发器完成）
# 8之后就开始正式支持这个约束了

create table user (
    id int primary key  auto_increment,
    age int ,
    gender char(1) check ( gender in ('m','f') ),
    constraint check1888 check ( age > 18 )
);

7.6默认值约束

可以使用default关键字设置每一个字段的默认值。
-- 创建一张user表
create table user(
    id int primary key auto_increment,
    age int default 99
);

7.7删除约束

删除NOT NULL约束
alter table 表名 modify 列名 类型;

删除UNIQUE约束
alter table 表名 drop index 惟一约束名;

删除PRIMARY KEY约束
alter table 表名 drop primary key;

删除FOREIGN KEY约束 
alter table 表名 drop foreign key 外键名;

7.8自动增长和默认值

auto_increment : 自动增长
    为新的行产生唯一的标识
    一个表只能拥有一个 auto_increment ,且该属性必须为主键的一部分
    auto_increment的属性可以是任何整数类型
    
default : 默认值

三、数据类型

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型

1.整数类型

根据数值取值范围的不同MySQL 中的整数类型可分为5种，分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。下图列举了 MySQL不同整数类型所对应的字节大小和取值范围而最常用的为INT类型的

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32768
MEDIUMINT	3	0~16777215	-8388608~8388608
INT	4	0~4294967295	-2147483648~ 2147483648
BIGINT	8	0~18446744073709551615	-9223372036854775808~9223372036854775808

2.浮点数类型和定点数类型

在MySQL数据库中使用浮点数和定点数来存储小数。浮点数的类型有两种：单精度浮点数类型（FLOAT)和双精度浮点数类型（DOUBLE)。而定点数类型只有一种即DECIMAL类型。下图列举了 MySQL中浮点数和定点数类型所对应的字节大小及其取值范围：

数据类型	字节数	有符号的取值范围	无符号的取值范围
FLOAT	4	-3.402823466E+38~-1.175494351E-38	0和1.175494351E-38~3.402823466E+38
DOUBLE	8	-1.7976931348623157E+308~2.2250738585072014E-308	0和2.2250738585072014E-308~1.7976931348623157E+308
DECIMAL（M,D）	M+2	-1.7976931348623157E+308~2.2250738585072014E-308	0和2.2250738585072014E-308~1.7976931348623157E+308

从上图中可以看出：DECIMAL类型的取值范围与DOUBLE类型相同。但是，请注意：DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长 度，D表示的是小数点后的长度。比如，将数据类型为DECIMAL(6,2)的数据6.5243 插人数据库后显示的结果为6.52

3.字符串类型

在MySQL中常用CHAR 和 VARCHAR 表示字符串。两者不同的是：VARCHAR存储可变长度的字符串。
当数据为CHAR(M)类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节；而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1

插入值	CHAR(3)	存储需求	VARCHAR(3)	存储需求
‘’	‘’	3个字节	‘’	1个字节
‘a’	‘a’	3个字节	‘a’	2个字节
‘ab’	‘ab’	3个字节	‘ab’	3个字节
‘abc’	‘ab’	3个字节	‘abc’	4个字节
‘abcd’	‘ab’	3个字节	‘abc’	4字节

4.字符串类型

文本类型用于表示大文本数据，例如，文章内容、评论、详情等，它的类型分为如下4种：

数据类型	储存范围
TINYTEXT	0~255字节
TEXT	0~65535字节
MEDIUMTEXT	0~16777215字节
LONGTEXT	0~4294967295字节

5.日期与时间类型

MySQL提供的表示日期和时间的数据类型分别是 ：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP。下图列举了日期和时间数据类型所对应的字节数、取值范围、日期格式以及零值：

数据类型	字节数	取值范围	日期格式	零值
YEAR	1	1901~2155	YYYY	0000
DATE	4	1000-01-01~9999-12-31	YYYY-MM-DD	0000-00-00
TIME	3	-838：59：59~ 838：59：59	HH:MM:SS	00:00:00
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	0000-00-00 00:00:00

5.1 YEAR类型

YEAR类型用于表示年份，在MySQL中，可以使用以下三种格式指定YEAR类型 的值。
1、使用4位字符串或数字表示，范围为’1901’—'2155’或1901—2155。例如，输人 ‘2019’或2019插人到数据库中的值均为2019。
2、使用两位字符串表示，范围为’00’—‘99’。其中，‘00’—'69’范围的值会被转换为 2000—2069范围的YEAR值，‘70’—'99’范围的值会被转换为1970—1999范围的YEAR 值。例如，输人’19’插人到数据库中的值为2019。
3、使用两位数字表示，范围为1—99。其中，1—69范围的值会被转换为2001— 2069范围的YEAR值，70—99范围的值会被转换为1970—1999范围的YEAR值。例 如，输人19插入到数据库中的值为2019。
请注意：当使用YEAR类型时，一定要区分’0’和0。因为字符串格式的’0’表示的YEAR值是2000而数字格式的0表示的YEAR值是0000。

5.2 TIME类型

TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS，其中，HH表示小时， MM表示分,SS表示秒。在MySQL中，可以使用以下3种格式指定TIME类型的值。
1、以’D HH:MM:SS’字符串格式表示。其中，D表示日可取0—34之间的值, 插人数据时，小时的值等于(DX24+HH)。例如，输入’2 11:30:50’插人数据库中的日期为59:30:50。
2、以’HHMMSS’字符串格式或者HHMMSS数字格式表示。 例如，输人’115454’或115454,插入数据库中的日期为11:54:54
3、使用CURRENT_TIME或NOW()输人当前系统时间。

5.3 DATETIME类型

DATETIME类型用于表示日期和时间，它的显示形式为’YYYY-MM-DD HH: MM:SS’，其中，YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS 表示秒。在MySQL中，可以使用以下4种格式指定DATETIME类型的值。
以’YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’字符串格式表示的日期和时间，取值范围为’1000-01-01 00:00:00’—‘9999-12-3 23:59:59’。例如，输人’2019-01-22 09:01:23’或 ‘20140122_0_90123’插人数据库中的 DATETIME 值都为 2019-01-22 09:01:23。
1、以’YY-MM-DD HH:MM:SS’或者’YYMMDDHHMMSS’字符串格式表示的日期和时间，其中YY表示年，取值范围为’00’—‘99’。与DATE类型中的YY相同，‘00’— '69’范围的值会被转换为2000—2069范围的值，‘70’—'99’范围的值会被转换为1970—1999范围的值。
2、以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期 和时间。例如，插入20190122090123或者190122090123,插人数据库中的DATETIME值都 为 2019-01-22 09:01:23。
3、使用NOW来输人当前系统的日期和时间。

5.4 TIMESTAMP类型

TIMESTAMP类型用于表示日期和时间，它的显示形式与DATETIME相同但取值范围比DATETIME小。在此，介绍几种TIMESTAMP类型与DATATIME类型不同的形式：
1、使用CURRENT_TIMESTAMP输人系统当前日期和时间。
2、输人NULL时系统会输人系统当前日期和时间。
3、无任何输人时系统会输入系统当前日期和时间。

6.二进制类型

在MySQL中常用BLOB存储二进制类型的数据，例如：图片、PDF文档等。BLOB类型分为如下四种：

数据类型	储存范围
TINYBLOB	0~255字节
BLOB	0~65535字节
MEDIUMBLOB	0~16777215字节
LONGBLOB	0~4294967295字节

四、用户授权

MySQL中授权(grant)和撤销授权(revoke）。

方法1：create和grant结合
    help CREATE USER;
    命令：CREATE USER <'用户名'@'地址'> IDENTIFIED BY ‘密码’;
    查看用户权限： help SHOW GRANTS;
    命令：show grants for '用户名'@'地址'；
    授权：help GRANT;
方法2：直接grant
    收回权限：REVOKE
    删除用户：DROP USER username
    
==========
生产环境授权用户建议：
    1、博客，CMS等产品的数据库授权
        select,insert,update,delete,create
        库生成后收回create权限
    2、生产环境主库用户授权
        select,insert,update,delete
    3、生产环境从库授权
        select
==========        
创建用户方法（推荐使用方法三）:
方法一：CREATE USER语句创建
    CREATE USER user1@’localhost’ IDENTIFIED BY ‘123456’;
	
方法二： INSERT语句创建
    INSERT INTO mysql.user(user,host, authentication_string,ssl_cipher,
                                x509_issuer,x509_subject)
    VALUES('user2','localhost',password('ABCabc123!'),'','','');
    FLUSH PRIVILEGES;
    
方法三： GRANT语句创建
    GRANT SELECT ON *.* TO user3@’localhost’ IDENTIFIED BY ‘123456’;
    FLUSH PRIVILEGES;
    
语法格式：
    grant 权限列表 on 库名.表名 to 用户名@'客户端主机'
    [identified by '密码' with option参数];
如：
    grant select on testdb.* to common_user@'%'
    grant insert on testdb.* to common_user@'%'
    grant update on testdb.* to common_user@'%'
    grant delete on testdb.* to common_user@'%'
    grant select, insert, update, delete on testdb.* to common_user@'%'
    grant create on testdb.* to developer@'192.168.0.%';
    grant alter on testdb.* to developer@'192.168.0.%';
    grant drop on testdb.* to developer@'192.168.0.%';
    grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库
    show grants; -- 查看当前用户（自己）权限
    show grants for dba@localhost;
    grant all on *.* to dba@localhost;
    # 移除权限
    # revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可
    revoke all on *.* from dba@localhost;

with_option参数
    GRANT OPTION： 授权选项
    MAX_QUERIES_PER_HOUR： 定义每小时允许执行的查询数
    MAX_UPDATES_PER_HOUR： 定义每小时允许执行的更新数
    MAX_CONNECTIONS_PER_HOUR： 定义每小时可以建立的连接数
    MAX_USER_CONNECTIONS： 定义单个用户同时可以建立的连接数

五、有关数据表的DML操作

DML(Data Manipulation Language): 数据操作语言，定义对数据库记录的操作。INSERT、DELETE、UPDATE、SELECT等

1.insert

插入数据

1.1为表中所有字段插入

INSERT INTO 表名 VALUES (值 1,值 2,...);

1.2为表中指定字段插入

INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);

1.3插入多条数据

INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...)，(值 1,值 2,...)，(值 1,值 2,...)……;

---

2.replace

使用replace语句向表插入新记录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录先被删除（注意：已有记录删除时也不能违背外键约束条件），然后再插入新记录

将delete 和 insert 合二为一

语法格式1：
replace into 表名 [（字段列表）] values （值列表）
语法格式2：
replace [into] 目标表名[(字段列表1) select (字段列表2) from 源表 where 条件表达式
语法格式3：
replace [into] 表名 set 字段1=值1, 字段2=值2

REPLACE与INSERT语句区别

​ replace语句的功能与insert语句的功能基本相同，不同之处在于：使用replace语句向表插入新记录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录先被删除（注意：已有记录删除时也不能违背外键约束条件），然后再插入新记录。

​ 使用replace的最大好处就是可以将delete和insert合二为一，形成一个原子操作，这样就无需将delete操作与insert操作置于事务中了

---

3.update

更新数据

update 表名
set 列名 = value [, column = value]
[where 限制条件];

eg:
    update user
    set age = 18;

修改可以一次修改多行数据，修改的数据可用where子句限定，where子句里是一个条件表达式，只有符合该条件的行才会被修改。

没有where子句意味着where字句的表达式值为true。也可以同时修改多列，多列的修改中间采用逗号(,)隔开

---

4.delete

删除数据

删除单条

delete from student where name='alexsb';

删除多条

delete from student;

---

5.truncate

DDL语句

完全清空一个表

truncate table 表名

DROP、TRUNCATE、DELETE的区别：

• delete：删除数据，保留表结构，可以回滚，如果数据量大，很慢
• truncate： 删除所有数据，保留表结构，不可以回滚，一次全部删除所有数据，速度相对很快
• drop： 删除数据和表结构,删除速度最快

---

6.select

====

注意:开发环境中不能用 *

6.1 简单的SELECT语句:

select {*, column [alias],...}
from table;

说明：
–SELECT 列名列表 *表示所有列。
–FROM 提供数据源(表名/视图名)
–默认选择所有行

6.2 SELECT语句中的算术表达式：

对数值型数据列、变量、常量可以使用算数操作符创建表达式（+ - * /）
对日期型数据列、变量、常量可以使用部分算数操作符创建表达式（+ -）
运算符不仅可以在列和常量之间进行运算，也可以在多列之间进行运算。

SELECT last_name, salary, salary*12 FROM employees;

补充：+说明
-- MySQL的+默认只有一个功能：运算符
SELECT 100+80; 		# 结果为180
SELECT '123'+80; 	# 只要其中一个为数值，则试图将字符型转换成数值，转换成功做预算，结果为203
SELECT 'abc'+80; 	# 转换不成功，则字符型数值为0，结果为80
SELECT 'This'+'is'; # 转换不成功，结果为0
SELECT NULL+80; 	# 只要其中一个为NULL，则结果为NULL

运算符的优先级：

乘法和除法的优先级高于加法和减法
同级运算的顺序是从左到右
表达式中使用括号可强行改变优先级的运算顺序
  SELECT last_name, salary, salary*12+100
  FROM employees;
 
  SELECT last_name, salary, salary*(12+100)
  FROM employees;

NULL值的使用：

空值是指不可用、未分配的值
空值不等于零或空格
任意类型都可以支持空值
包括空值的任何算术表达式都等于空
字符串和null进行连接运算，得到也是null.

补充说明:

安全等于<=>
1.可作为普通运算符的=
2.也可以用于判断是否是NULL 如：where salary is NULL/(is not NULL) ->where salary<=>NULL

示例1：查询emp表奖金为空的员工信息。
select * from emp where comm <=> NULL;

示例2：查询emp表奖金为50000的员工信息
select * from emp where comm <=> 50000;

6.3定义字段的别名：

改变列的标题头
用于表示计算结果的含义
作为列的别名
如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都可以通过为别名添加加双引号实现。
  SELECT last_name as “姓名”, salary “薪水”
  FROM employees;
 
  SELECT last_name, salary*12 “年薪”
  FROM employees;

6.4 重复记录

distinct 去重

缺省情况下查询显示所有行,包括重复行
  SELECT department_id
  FROM employees;
 
使用DISTINCT关键字可从查询结果中清除重复行
  SELECT DISTINCT department_id
  FROM employees;
 
DISTINCT的作用范围是后面所有字段的组合
  SELECT DISTINCT department_id , job_id
  FROM employees;

6.5 限制所选择的记录

1.使用WHERE子句限定返回的记录

WHERE子句在FROM 子句后

SELECT[DISTINCT] {*, column [alias], ...}
FROM table–[WHERE condition(s)];

WHERE中的字符串和日期值
	字符串和日期要用单引号扩起来
	字符串是大小写敏感的,日期值是格式敏感的
  SELECT last_name, job_id, department_id
  FROM employees
  WHERE last_name = "king";

WHERE中比较运算符:
  SELECT last_name, salary, commission_pct
  FROM employees
  WHERE salary<=1500;  

2.其他比较运算符

使用BETWEEN运算符显示某一值域范围的记录
 SELECTlast_name, salary
  FROM employees
  WHERE salary BETWEEN 1000 AND 1500;
 
(1)使用IN运算符
使用IN运算符获得匹配列表值的记录
 SELECTemployee_id, last_name, salary, manager_id
  FROM employees
  WHERE manager_id IN (7902, 7566, 7788);
 
(2)使用LIKE运算符
  使用LIKE运算符执行模糊查询
  查询条件可包含文字字符或数字
  (%) 可表示零或多个字符
  ( _ ) 可表示一个字符
    SELECT last_name
    FROM employees
    WHERE last_name LIKE '_A%';
   
(3)使用IS NULL运算符
  查询包含空值的记录
   SELECT last_name, manager_id
   FROM employees
   WHERE manager_id IS NULL;
   逻辑运算符
   
(4)使用AND运算符
    AND需要所有条件都是满足T.
   
    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary>=1100–4 AND job_id='CLERK';
(5)使用OR运算符
    OR只要两个条件满足一个就可以
 
    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary>=1100 OR job_id='CLERK';
(6)使用NOT运算符
    NOT是取反的意思
    SELECT last_name, job_id
    FROM employees
    WHERE job_id NOT IN ('CLERK','MANAGER','ANALYST');

3.使用正则表达式

REGEXP

<列名> regexp '正则表达式'
select * from product where product_name regexp '^2018';

4.数据分组

GROUP BY

(1)GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。
 分组的含义是：把该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录。
 分组函数忽略空值, 结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句。
 
     SELECT column, group_function
     FROM table
     [WHERE condition]
     [GROUP BY group_by_expression]
     [ORDER BY column];
 
#每个部门的平均工资
#每个部门每个职位的平均工资

分组函数重要规则
如果使用了分组函数，或者使用GROUP BY 的查询：出现在SELECT列表中的字段，要么出现在组合函数里，
要么出现在GROUP BY 子句中。
GROUP BY 子句的字段可以不出现在SELECT列表当中。
使用集合函数可以不使用GROUP BY子句，此时所有的查询结果作为一组。
 
 ==================================================
(2)数据分组-限定组的结果:HAVING子句
HAVING子句用来对分组后的结果再进行条件过滤。

    SELECT column, group_function
    FROM table
    [WHERE condition]
    [GROUP BY group_by_expression]
    [HAVING group_condition]
    [ORDER BYcolumn];

HAVING子句用来对分组后的结果再进行条件过滤。
#查询部门平均工资大于2000的
#分组后加条件 使用having
#where和having都是用来做条件限定的,但是having只能用在group by之后

 
==================================================
(3)HAVING 与 WHERE 的区别
WHERE是在分组前进行条件过滤, HAVING子句是在分组后进行条件过滤, WHERE子句中不能使用聚合函数,HAVING子句可以使用聚合函数。

组函数的错误用法
	不能在WHERE 子句中限制组.
	限制组必须使用HAVING 子句.
	不能在WHERE 子句中使用组函数
 
==================================================
(4)补充：MySQL 多行数据合并 GROUP_CONCAT
Syntax: GROUP_CONCAT(expr)

示例:fruits表按s_id,将供应水果名称合并为一行数据

select s_id,group_concat(f_name)
from fruits
group by s_id;

 +------+-------------------------+
 | s_id | group_concat(f_name)  |
 +------+-------------------------+
 |  101 | apple,blackberry,cherry |
 |  102 | orange,banana,grape   |
 |  103 | apricot,coconut     |
 |  104 | berry,lemon       |
 |  105 | melon,xbabay,xxtt    |
 |  106 | mango          |
 |  107 | xxxx,xbababa      |
 +------+-------------------------+
  7 rows in set (0.00 sec)
  
 注意：使用 GROUP_CONCAT() 函数必须对源数据进行分组，否则所有数据会被合并成一行
  
  
对结果集排序
  查询语句执行的查询结果，数据是按插入顺序排列
  实际上需要按某列的值大小排序排列
  按某列排序采用order by 列名[desc],列名…
  设定排序列的时候可采用列名、列序号和列别名
  如果按多列排序，每列的asc，desc必须单独设定  
 
  
==================================================
(5)联合查询
-- 中国或美国城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION   去重复
UNION ALL 不去重复
 
==================================================
(6)查询结果限定
在SELECT语句最后可以用LIMLT来限定查询结果返回的起始记录和总数量。MySQL特有。
SELECT … LIMIT offset_start,row_count;
offset_start:第一个返回记录行的偏移量。默认为0.
row_count:要返回记录行的最大数目。
例子：
SELECT * FROM TB_EMP LIMIT 5;/*检索前5个记录*/
SELECT * FROM TB_EMP LIMIT 5,10;/*检索记录行6-15*/

MySQL中的通配符：
MySQL中的常用统配符有三个：
   %：用来表示任意多个字符，包含0个字符
   _ : 用来表示任意单个字符
   escape：用来转义特定字符

7.多表关联查询

1. inner join：代表选择的是两个表的交差部分。
  内连接就是表间的主键与外键相连，只取得键值一致的，可以获取双方表中的数据连接方式。语法如下：
  SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
  
  
2. left join：代表选择的是前面一个表的全部。
  左连接是以左表为标准，只查询在左边表中存在的数据，当然需要两个表中的键值一致。语法如下：
  SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
  
  
3. right join：代表选择的是后面一个表的全部
同理，右连接将会以右边作为基准，进行检索。语法如下：
  SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;


4.自连接
  自连接顾名思义就是自己跟自己连接，参与连接的表都是同一张表。(通过给表取别名虚拟出)


5.交叉连接：不适用任何匹配条件。生成笛卡尔积
  联合查询
  说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
  UNION 去重复
  UNION ALL 不去重复
  -- 中国或美国城市信息
  SELECT * FROM city
  WHERE countrycode IN ('CHN' ,'USA');
  SELECT * FROM city WHERE countrycode='CHN'
  UNION ALL
  SELECT * FROM city WHERE countrycode='USA'

六、SQL函数

1.聚合函数

聚合函数对一组值进行运算，并返回单个值。也叫组合函数。
COUNT(*|列名) 		统计行数
AVG(数值类型列名) 	 平均值
SUM (数值类型列名) 	 求和
MAX(列名)	 			最大值
MIN(列名) 			最小值

除了COUNT()以外，聚合函数都会忽略NULL值。

2.数值型函数

ABS 	求绝对值
SQRT 	求平方根
POW 和 POWER 两个函数的功能相同，返回参数的幂次方
MOD 	求余数
CEIL 和 CEILING 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR 	向下取整，返回值转化为一个BIGINT
RAND 	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND 	对所传参数进行四舍五入
SIGN 	返回参数的符号

3.字符串函数

LENGTH 			计算字符串长度函数，返回字符串的字节长度
CHAR_LENGTH 	计算字符串长度函数，返回字符串的字节长度，注意两者的区别
CONCAT			合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT(str,pos,len,newstr) 替换字符串函数
LOWER 			将字符串中的字母转换为小写
UPPER 			将字符串中的字母转换为大写
LEFT(str,len) 	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT 			从右侧字截取符串，返回字符串右边的若干个字符
TRIM 			删除字符串左右两侧的空格
REPLACE(s，s1，s2) 	字符串替换函数，返回替换后的新字符串
SUBSTRING(s，n，len) 	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE 		字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
STRCMP(expr1,expr2)	  比较两个表达式的顺序。若expr1 小于 expr2 ，则返回 -1，0相等，1则相反
LOCATE(substr,str [,pos]) 返回第一次出现子串的位置
INSTR(str,substr) 	返回第一次出现子串的位置

4.日期和时间函数

SELECT NOW();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');

5.流程控制函数

IF(expr,v1,v2) 	判断，流程控制，当expr = true时返回 v1，当expr = false、null 时返回v2
IFNULL(v1,v2) 	判断是否为空，如果 v1 不为 NULL，则 IFNULL 函数返回 v1，否则返回 v2
CASE 			搜索语句

七、视图

可以将视图理解为预先定义好的sql语句

所以对视图进行某些操作(更新某个数据)时会影响到表

1.视图定义

• 视图通过以定制的方式显示来自一个或多个表的数据

• 视图是一种数据库对象，用户可以像查询普通表一样查询视图

• 视图内其实没有存储任何数据，它只是对表的一个查询

• 视图的定义保存在数据字典内，创建视图所基于对表称为“基表”

2.视图的好处

优点：

• 简单

• 安全

• 数据独立

使用视图的大部分情况是为了保障数据安全性，提高查询效率。

3.视图的基本操作

使用规则

1. 视图必须有唯一命名

2. 在mysql中视图的数量没有限制

3. 创建视图必须从管理员那里获得必要的权限

4. 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图

5. 在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY

6. 视图不能索引，也不能关联触发器或默认值

7. 视图可以和表同时使

3.1创建视图

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

=====参数说明=====
ALGORITHM：可选项，表示视图选择的算法。
视图名：表示要创建的视图名称。
属性清单：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
SELECT语句：表示一个完整的查询语句，将查询记录导入视图中。
WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图的权限范围之内。

示例:

create view v_emp_dept as
select emp.*, dept.name deptName, dept.intro deptIntro
from emp,
     dept
where emp.dept_id = dept.id;

3.2修改视图

1.可以直接使用 create or replace view 进行修改视图

create or replace view v_emp_dept as
select emp.*, dept.name deptNameGai
from emp,
     dept
where emp.dept_id = dept.id;

2.使用alter view 语句修改 v_emp_dept 视图,同时为每列指定列名

alter view v_emp_dept (a,b,c,d)
as select emp.*, dept.name deptName
from emp,
     dept
where emp.dept_id = dept.id;

3.3删除视图

drop view 视图名

3.4查看视图权限

创建视图需要具有CREATE VIEW的权限，同时应该具有查询涉及的列的SELECT权限

# 查看某个用户是否有权限
SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='用户名';

# 查看所用户及他们对应的权限
SELECT user,Select_priv,Create_view_priv FROM mysql.user;

3.5更新视图

update viewName set column=newValue  

# 更新视图
update dept_emp_allName set empName='小白龙' where empName='李四';

视图更新对表也有影响

八、索引

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

索引是提高数据库性能的重要方式,MySQL中，所有的数据类型都可以被索引

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）

MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

**注意:**一般数据库默认都会为主键生成索引

1.什么是索引

模式(schema)中的一个数据库对象
在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
与表独立存放，但不能独立存在，必须属于某个表
由数据库自动维护，表被删除时，该表上的索引自动被删除。
索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。

索引的原理

就是把无序的数据变成有序的查询

1. 把创建的索引的列的内容进行排序

2. 对排序结果生成倒排表

3. 在倒排表内容上拼上数据地址链

4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

---

2.索引优缺点

优点:

• 可以提高检索的效率，降低数据库的IO成本，类似于输的目录

• 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

  • 被索引的列会自动排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些
  • 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多

劣势:

• 索引会占据磁盘空间

• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件

索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了

---

3.创建索引

1.创建表时创建索引

=====eg1=====
create table t (
    id int primary key ,
    name varchar(10) unique
);

=====eg2=====
create table t (
    id int  ,
    name varchar(10) ,
    primary key(id),
    unique index (name)
);

2.alter指令更新索引

ALTER TABLE table_name ADD INDEX index_name  (column(length))

alter table t add index index_name (name(12));

3.create

create index 索引名称 on table(column(length)) 

create index i_emp_age on emp(age)

---

4.索引的设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的
索引

1. 选择惟一性索引

2. 为经常需要排序、分组和联合操作的字段建立索引

3. 为常作为查询条件的字段建立索引

4. 限制索引的数目

5. 尽量使用数据量少的索引

6. 尽量使用前缀来索引

7. 删除不再使用或者很少使用的索引

---

5.索引分类

5.1普通索引

# 直接创建索引
create index t_name on t(name);

# 创建表的时候同时创建索引


# 修改表结构的方式添加索引
alter table t add index index_name (name(12));

5.2唯一索引

create table t
(
    id   int,
    name varchar(10),
    unique index (name)
);

5.3全文索引

# 3 创建全文索引（FULLTEXT）
# MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；
# 他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，
# 或是随后使用ALTER TABLE 或CREATE INDEX被添加。
# 对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，
# 然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。
# 不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
只能创建在char,varchar或text类型的字段上。
create table index3(
    Id int,
    Info varchar(20),
    Fulltext index index3_info(info)
);

5.4单列索引

create table index4(
    Id int,
    Subject varchar(30),
    Index index4_st(subject(10))
);

5.5多列索引

使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引

如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。

也就是说多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

create table index5(
    Id int,
    Name varchar(20),
    Sex char(4),
    Index index5_ns(name,sex)
);

5.6空间索引

create table index6(
    Id int,
    Space geometry not null,
    Spatial index index6_sp(space)
)engine=myisam;

建空间索引时，表的存储引擎必须是myisam类型，而且索引字段必须有非空约束。空间数据类型包括geometry,point,linestring和polygon类型等。平时很少用到。

5.7总结

✨CREATE INDEX

首先保证已经存在表，才能使用这个命令创建索引

在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下：help create index
CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (属性名 [ (长度) ] [ ASC | DESC] );

1．创建普通索引
CREATE INDEX index_name ON table(column(length))

2．创建惟一性索引
CREATE UNIQUE INDEX indexName ON table(column(length))

3．创建全文索引
CREATE FULLTEXT INDEX index_content ON article(content)

4．创建单列索引
CREATE INDEX index3_name on index3 (name(10));

5．创建多列索引
6．创建空间索引

---

✨ALTER TABLE
用ALTER TABLE语句来创建索引，也是存在表的情况下

在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形式如下：
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名（属性名 [ (长度) ] [ ASC | DESC]）;

1．创建普通索引
ALTER TABLE table_name ADD INDEX index_name (column(length))

2．创建惟一性索引
ALTER TABLE table_name ADD UNIQUE indexName (column(length))

3．创建全文索引
ALTER TABLE index3 add fulltext index index3_name(name);

4．创建单列索引
ALTER TABLE index3 add index index3_name(name(10));

5．创建多列索引
6．创建空间索引

6.删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。
对于这样的索引，应该将其删除。本节将详细讲解删除索引的方法。

对应已经存在的索引，可以通过DROP语句来删除索引。基本形式如下：

DROP INDEX 索引名 ON 表名 ;

7.其它

7.1查询创建表的结构

Show create table emp \G;

=====创建表的结构=====
mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `dept_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ind_emp_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

7.2查询某张表中索引情况

show index from emp;

7.3使用计划查询SQL使用索引情况

=====命令行窗口中=====
Explain select * from emp where id=1 \G;

=====navicat=====
Explain select * from emp where id=1;

=====参数解释=====
explain select * from table where id=1;

EXPLAIN分析结果的含义:
table：这是表的名字。
type：连接操作的类型，ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能
从差到好）
possible_keys：可能可以利用的索引的名字
Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
key_len：索引中被使用部分的长度，以字节计。
ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行
rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1
Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响

九、事务

1.事务简介

1.1事务

数据库中的事务是指对数据库执行一批操作，这些操作最终要么全部执行成功，要么全部失败

比如银行转账，A账号转给B账号1000元，其中包括两个操作A-1000、B+1000，要么这两个操作全部完成，要么全部不做，决不允许

只执行其中一个操作

1.2事务回滚

事务回滚，当一个事务执行过程中发生了异常、错误，则重新回到最先未开始执行的过程。比如上面那个银行转账过程，假设A-1000操作已经完成，但是在执行B+1000操作时，系统发生位置错误，这时需要回到未执行该转账操作之前的状态，即A、B原来多少钱还是多少钱，一分不能少

1.3事务提交

事务提交，当一个事务执行过程没有发生任何异常、错误，这时我们要保存这个事务的修改。比如上面的银行转账过程，假设A-1000、B+1000操作全部完成，没有出现任何异常、错误，这时需要保存事务执行状态修改（A减少了1000元，B增加了1000元），即事务提交

2.事务的特性(ACID)

1. 原子性(Atomicity)：整体 【原子性是指事务包含的所有操作要么全部成功，要么全部失败】
2. 一致性(Consistency)：数据 【事务提交后的状态合集称为一致，也就是数据库只包含事务提交的状态】
3. 隔离性(Isolation)：并发 【对于任意两个并发的事务A和B，在事务A看来，B要么在A开始之前就已经结束，要么在A结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。】
4. 持久性(Durability)：结果 【持久性是指一个事务一旦提交了，就保存到硬盘上，对数据库中的数据的改变就是永久性的】

3.使用事务

MySQL默认开启自动提交，也就是其实每一条sql语句就是一个事务

MySQL开启事务、回滚事务、提交事务命令

begin
说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令

commit：提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了

rollback ：回滚事务
将内存中，已执行过的操作，回滚回去

---

自动提交策略：

MySQL默认已经开启自动提交，我们可以通过对应的设置来开启或者关闭自动提交

show variables like 'autocommit'; /*ON为开启立即提交*/
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

select @@autocommit; /* 1为开启立即提交*/
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

=====设置关闭=====
set autocommit=0;
set global autocommit=0;

注意：
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能

set设置关闭自动提交，仅在当前窗口有效

---

隐式提交:

=====用于隐式提交的 SQL 语句
begin
a
b
begin
SET AUTOCOMMIT = 1

导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

---

开始事务流程:

=====1、检查autocommit是否为关闭状态
select @@autocommit;
或者：
show variables like 'autocommit';

=====2、开启事务,并结束事务
begin
    delete from student where name='alexsb';
    update student set name='alexsb' where name='alex';
rollback; 
 
begin
    delete from student where name='alexsb';
    update student set name='alexsb' where name='alex';
commit;

4.事务的隔离级别

4.1读未提交读

读未提交读(Read Uncommitted)，也叫脏读

事务B读看到事务A没有提交的内容，比如对A、B账户的修改

---

4.2不可重复读

不可重复读(Read Committed)

一个事务读到另一个事务已提交的内容（主要是数据更新）

此级别解决了读未提交读的问题，即其它事务没有提交的内容对本事务不可见

但是事务B能读到事务A提交的数据更新内容

---

4.3可重复读

可重复读(Repeatable Read)

虚读/幻读，一个事务读到另一个事务已提交的内容（主要是数据插入）

此级别解决了读未提交读、不可重复读的问题

事务B能读到事务A提交的数据插入内容

---

4.4可串行化

可串行化(Serializable)：最高的隔离级别，通过强制事务排序，使之不可能相互冲突，从而解决幻读问题

---

4.5事务隔离级别

数据库共定义了四种隔离级别：

• Serializable：可避免脏读、不可重复读、虚读情况的发生（串行化）

• Repeatable read：可避免脏读、不可重复读情况的发（可重复读）

• Read committed：可避免脏读情况发生（读已提交）

• Read uncommitted：最低级别，以上情况均无法保证（读未提交）

📒可以通过命令 set transaction命令设置事务隔离级别：

set transaction isolation level 设置事务隔离级别

📒查询事务隔离级别

select @@tx_isolation 查询当前事务隔离级别
=====MySql8=====
select @@transaction_isolation 查询当前事务隔离级别

=====eg:=====
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

5.事务锁

为了解决事务隔离性问题，引入锁的概念，只 有 拿 到 锁 的 事 务 才 可 对 数 据 库 进 行 读 写 操 作。

✨事务有两种锁，并且有相应的权限：

1. 读锁，也称为共享锁。某个事务A拿到该锁时，事务A只能进行读操作，此时其他事务也可以拿到这把锁（共享）。

2. 写锁，也称为排它锁。某个事务A拿到该锁时，事务A能进行读、写操作，此时其他事务不能拿到这把锁（排它）。

📒如果某个事务A拿到了读锁，其它事务可以拿到读锁（共享），但是无法获取写锁。

📒如果某个事务A拿到了写锁，其他事务既不能拿到写锁，也拿不到读锁！

✨锁的粒度：

所谓锁的粒度，就是锁的范围，比如如果锁的范围是一张表，则事务A获取写锁后，只能事务A进行读、写，其他事务全部要靠边站。
如果锁的粒度是事务A需要操作的某几行记录，其它记录如果其他事务拿到锁仍然可以读、写。

一般情况下，锁的粒度越小（锁的范围小），则并发问题解决越好（事务都是并发执行），但是效率越低，因为需要大量的资源来确保各个事务的锁的粒度没有交集、冲突

锁的粒度越大（锁的范围大），则并发问题解决越差（其他事务都在等待），但是效率较高，因为不要资源来控制各个事务的锁粒度交集问题

✨运用锁解决隔离性问题