131.【MySQL_基础篇】

MySQL_基础篇

• (一)、MySQL 介绍
• • 1.MySQL三大阶段
  • • • (1).基础篇
      • (2).进阶篇
      • (3).运维篇
  • 2.MySQL 概念
  • 3.数据模型
  • • • (1).关系型数据库(RDBMS)
  • 4.数据库三大范式
• (二)、SQL 编程语言
• • 1.SQL通用语法
  • 2.SQL 四大分类
  • 3.DDL (数据定义语言)
  • • • (1).数据库操作 ->(增删改查)
      • (2).表操作 -> (增删改查)
  • 4.DML (数据操纵语言)
  • • • (1).DML_添加数据
      • (2).DMl_修改数据
      • (3).DML_删除数据
  • 5.DQL (数据查询语言) -执行顺序在这 ⭐
  • • • (1).DQL语法
      • (2).DQL_基本查询
      • (3).DQL_条件查询
      • (4).聚合函数
      • (5).DQL_分组查询
      • (6).DQL_排序查询
      • (7).DQL_分页查询
      • (8).DQL_示列
  • 6. DCL (数据控制语言)
  • • • (1).DCL 介绍
      • (2).DCL 管理用户
      • (3).DCL_权限控制
• (三)、函数 - (非聚合函数)
• • 1. 字符串函数
  • • • (1).基础知识
      • (2).字符串语法
      • (3).示列
  • 2.数值函数
  • • • (1).基础知识
      • (2).示列
  • 3.日期函数
  • • • (1).基本知识
      • (2).示列
  • 4.流程函数
  • • • (1).基本知识
      • (2).示列
• (四)、约束
• • 1.约束概述
  • • • (1).约束的概念
      • (2).约束的目的
      • (3).约束的分类
  • 2.约束演示
  • 3.外键约束
  • • • (1).外键约束的概念
      • (2).SQL语句
      • (3).外键约束的语法
      • (4).有外键约束与外键约束情况
  • 4.外键删除更新行为
  • • • (1).删除/更新行为
      • (2).修改删除/更新行为语法
      • (3).级联删除/更新测试
      • (4). set null 删除/更新测试
• (五)、多表查询
• • 1.多表关系
  • • • (1).多表查询概述
      • (2).一对多 (多对一)关系
      • (3).多对多关系
      • (4)一对一关系
  • 2.多表查询概述
  • • • (1).笛卡尔积效应
      • (2).消除笛卡尔积效应
  • 3.多表查询分类
  • • • (1).连接查询
      • (2).子查询
  • 4.连接查询-内连接 (两张表交集部分) ⭐
  • • • (1).隐式内连接语法
      • (2).显示内连接语法
      • (3).隐式内连接和显示内联接测试
  • 5.连接查询-外连接 (查询某张表的全部数据和交集部分)
  • • • (1).外连接查询语法
      • (2).左外连接测试
      • (3).右外连接测试
  • 6.连接查询-自连接 (可交集、可单独) ⭐
  • • • (1).自连接查询语法
      • (2).自连接测试
  • 7.联合查询-union,union all
  • • • (1).联合查询的语法
      • (2).联合查询示列
  • 8.子查询 (嵌套查询) ⭐
  • • • (1).子查询的基本语法
      • (2).子查询的分类
  • 9.子查询-标量子查询 (返回结果是一个值)
  • • • (1).标量子查询
      • (2).标量子查询示列
  • 10.子查询-列子查询 (单列但可多行)
  • • • (1).列子查询
      • (2).列子查询示列
  • 11.子查询-行子查询 (单行但可多列)
  • • • (1).行子查询
      • (2).行子查询示列
  • 12.子查询-表子查询 （多行且多列）
  • • • (1).表子查询
      • (2).表子查询示列
• (六)、事务
• • 1.事务简介
  • 2.事务操作
  • • • (1).未出现异常下的事务
      • (2).出现异常下的事务
  • 3.事务控制 - (第一种方法)
  • • • (1).事务管理方法
      • (2).事务提交示列
      • (3).事务回滚示列
  • 4.事务控制 -(第二种方法)
  • • • (1).事务方法
      • (2).事务控制
  • 5.事务四大特性 (ACID)
  • • • (1).四大特性
  • 6.并发事务问题
  • • • (1).并发事务引起的三大问题
      • (2).三大问题详细介绍
  • 7.事务隔离级别
  • • • (1).事务隔离级别
      • (2).查看/操作事务隔离级别
      • (3).演示脏读
      • (4).演示不可重复读
      • (5).演示幻读

(一)、MySQL 介绍

1.MySQL三大阶段

(1).基础篇

• MySQL概述
• SQL
• 函数
• 约束
• 多表查询
• 事务

(2).进阶篇

• 存储引擎
• 索引
• SQL优化
• 视图/存储过程/触发器
• 锁
• InnoDB核心
• MySQL管理

(3).运维篇

• 日志
• 主从复制
• 分库分表
• 读写分离

2.MySQL 概念

1. 数据库(DB): 存储数据的仓库，数据是有组织的进行存储。
2. 数据库管理系统(DBMS): 操纵和管理数据库的大型软件。
3. SQL: 操纵关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。

3.数据模型

(1).关系型数据库(RDBMS)

概念: 建立在关系模型基础上，由多张相互链接的二维表组成的数据库。

特点:

1. 使用表存储数据，格式统一，便于维护。
2. 使用SQL语言操作，标准统一，使用方便。

我们的电脑上安装完MySQL之后，我们的电脑就会自动成为MySQL数据库的服务器，在服务器上会存储我们客户端通过SQL语句编译的数据。

4.数据库三大范式

1. 第一范式： 属性(字段)不能再分，也就是说字段不能是集合、数组。
2. 第二范式：首先满足第一范式，其次不能存在部分依赖。也就是说非主键字段必须完全依赖于主键。(也就是说只有一个主键)
3. 第三范式：首先满足第二范式，其次不能存在依赖传递。也就是说不能非主键字段依赖于另一个非主键字段。

(二)、SQL 编程语言

1.SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾。
2. SQL语句可以使用空格缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 注释:
   • 单行注释: - -注释内容 或 # 注释内容(MySQL特有)
   • 多行注释: / * 注释内容 * /

2.SQL 四大分类

1. Data Definition Language(DDL)：数据定义语言，用来定义数据库对象(数据库，表，字段)。
2. Data Manipulation Language(DML): 数据操纵语言，用来对数据库表中的数据进行增删改。
3. Data Query Labguage(DQL): 数据查询语言，用来查询数据库中表的记录。
4. Data Control Language(DCL): 数据控制语言，用来创建数据库用户、控制数据库的访问权限。

3.DDL (数据定义语言)

(1).数据库操作 ->(增删改查)

1.查询

show databases;  #查询所有的数据库

select database(); #查询当前数据库

2. 创建

create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]; #假如不存在这个数据库我们就进行创建

3.删除

drop database [if exists] 数据库名; # 假如存在这个数据库就删除

4. 使用

use 数据库名;  # 使用我们指定的数据库

(2).表操作 -> (增删改查)

1. DDL_表操作_查询

1. 查询当前数据库所有表

show tables; # 展示所有的表

2.查询表结果

DESC 表名;

3.查询指定表的建表语句

show create table 表名;

2. DDL_表操作_创建

1.表的创建

create table 表名(字段1 字段1类型 [comment '字段1注释'],字段2 字段1类型 [comment '字段2注释'],字段3 字段3类型 [comment '字段3注释'])[comment '表注释'];

 create table tb_user(   id int(4) comment '编号',   name varchar(20) comment '姓名',   age int(4) comment '年龄',   gender varchar(4) comment '性别' ) comment '用户表';

3. DDL_表操作_添加

1.给表中添加字段

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

4. DDL_表操作_修改

1.修改数据类型

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

2.修改字段名和字段类型

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

3.修改表名

alter table 表名 rename to 新表名;

5. DDL_表操作_删除

1.删除字段

alter table 表名 drop 字段名;

2.删除表，数据和表结构都删除

drop table[if exists] 表名;

3.删除指定表，并重新创建该表

truncate table 表名;

4.DML (数据操纵语言)

DML英文全称是 Data Manipulation language (数据操纵语言)，用来对数据库中的数据记录进行增删改操作。

• 添加操作 (insert)
• 修改操作(update)
• 删除操作(delete)

(1).DML_添加数据

1. 给指定字段添加数据

insert into (字段名1,字段名2...) values(值1，值2...);  # 给字段添加数据

2. 给全部字段添加数据

insert into 表名 values(值1，值2...);

3. 批量添加数据

insert into 表名(字段1，字段2...) values(值1，值2...),(值1,值2...)

insert into 表名 values(值1，值2...),(值1，值2...),(值1，值2...);

注意:

1. 插入数据时，指定的字段顺序需要与值得顺序是一一对应的。
2. 字符串和日期型数据应该包含在引号中。
3. 插入的数据大小，应该在字段的规定范围内。

(2).DMl_修改数据

1.多个字段数据修改

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

2.单个字段数据修改

update 表名 set 字段1=值1 [where 条件];

注意:

1. 如果不加where条件的话，默认会修改整个表的数据。
2. 如果加了where条件的话，默认只会修改单个字段的数据。

(3).DML_删除数据

delete from 表名 [where 条件]

注意:

1. delete语句的条件可以有，也可以没有，如果没有的话，则会删除整张表的所有数据。
2. delete 语句不能删除某一个字段的值(可以使用update)

5.DQL (数据查询语言) -执行顺序在这 ⭐

DQL 英文全称是Data Query Language(数据查询语言),数据查询语言，用来查询数据库中表的记录。

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for emp-- ----------------------------DROP TABLE IF EXISTS `emp`;CREATE TABLE `emp`  (  `ID` int(11) NULL DEFAULT NULL COMMENT '编号',  `WORKNO` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '工号',  `NAME` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '姓名',  `GENDER` char(1) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '性别',  `AGE` tinyint(3) UNSIGNED NULL DEFAULT NULL COMMENT '年龄',  `IDCARD` char(18) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '身份证号',  `WORKADDRESS` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '工作地址',  `ENTRYDATE` date NULL DEFAULT NULL COMMENT '入职时间') ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci COMMENT = '员工表' ROW_FORMAT = Compact;-- ------------------------------ Records of emp-- ----------------------------INSERT INTO `emp` VALUES (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01');INSERT INTO `emp` VALUES (2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');INSERT INTO `emp` VALUES (3, '3', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');INSERT INTO `emp` VALUES (4, '4', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');INSERT INTO `emp` VALUES (5, '5', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');INSERT INTO `emp` VALUES (6, '6', '杨道', '男', 28, '12345678931234567X', '北京', '2006-01-01');INSERT INTO `emp` VALUES (7, '7', '范骚', '男', 40, '123456789212345670', '北京', '2005-05-01');INSERT INTO `emp` VALUES (8, '8', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');INSERT INTO `emp` VALUES (9, '9', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');INSERT INTO `emp` VALUES (10, '10', '陈友凉', '男', 53, '123456789012345670', '上海', '2011-01-01');INSERT INTO `emp` VALUES (11, '11', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');INSERT INTO `emp` VALUES (12, '12', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');INSERT INTO `emp` VALUES (13, '13', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');INSERT INTO `emp` VALUES (14, '14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');INSERT INTO `emp` VALUES (15, '15', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');INSERT INTO `emp` VALUES (16, '16', '周芷若', '女', 18, NULL, '北京', '2012-06-01');SET FOREIGN_KEY_CHECKS = 1;

(1).DQL语法

1.SQL的Select 执行顺序如下:

(1) FROM (3) JOIN (2) ON (4) WHERE (5)GROUP BY (开始使用SELECT中的别名，后面的语句中都可以使用) - 也就是非聚合函数可以使用了(6) AVG,SUM.... (7)HAVING  (8) SELECT (9) DISTINCT (10) ORDER BY (11)LIMIT

2.常见的SQL语法

select 字段列表from表名列表where 条件列表group by分组字段列表having分组后排序列表order by排序字段列表limit分页参数

(2).DQL_基本查询

1.查询多个字段

select 字段1，字段2.. from 表名;

2.查询全部字段

select *from 表名;

3.设置别名

select 字段1 [as 别名1],字段2[as 别名2] ... from 表名;

4.去除重复记录

select distinct 字段列表 from 表名; 

(3).DQL_条件查询

1.基本语法

select 字段列表 from 表名 where 条件列表; 

2.条件

1. 比较运算符

> >=   大于< <=   小于=      等于<> !=  不等于between ... and ...  在某个范围之内包含本身in(...)   在in之后的列表中的值，任选一个即可like '占位符'  模糊匹配( 占位符是 _ 匹配单个字符, 占位符是 % 匹配任意个字符)is null   值为空is not null 值不为空

2. 逻辑运算符

and &&   且条件or  ||   或条件not !    非，不是

(4).聚合函数

聚合函数: 将一列数据作为一个整体，进行纵向计算。

1.常见的聚合函数

count()    #统计数量->统计的是条数不是求和max()      #最大值min()      #最小值avg()      #平均值sum()      #求和

2.聚合函数语法

select 聚合函数(字段列表) from 表名;

注意:

1. null值不参与聚合函数运算。

(5).DQL_分组查询

1.语法

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

2.where与having的区别

• 执行时机 : where是分组之前进行过滤,不满足where条件，不参与分组；而having是分组之后对结果进行过滤。 也就是说where的优先级高
• 判断条件不同 : where不能对聚合函数进行判断，而having可以。 也就是说having的优先级比较低

3. 示列

3.1根据性别分组，同级男性员工 和 女性员工的数量

SELECT gender,COUNT(*) FROM emp GROUP BY gender;

3.2根据性别分组，同级男性员工 和 女性员工的平均年龄

SELECT gender,avg(age) FROM emp GROUP BY gender;

3.3查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

SELECT WORKADDRESS,COUNT(*) FROM emp WHERE age<45 GROUP BY WORKADDRESS HAVING  COUNT(*)>=3;

注意:

1. 进行分组查询的时候，返回的字段通常是分组的条件，返回其他的没有意义。并不是说会报错只是没有意义。

(6).DQL_排序查询

1.支持多条件排序

先按照第一种字段进行排序，假如顺序相同那么就按照第二种字段进行排序。

select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2; #先按照第一种字段进行排序，假如顺序相同那么就按照第二种字段进行排序

2.排序方式

• ASC: 升序(默认值)
• DESC: 降序

3. 示列

3.1根据年龄对公司员工进行升序排序

SELECT * FROM emp ORDER BY age ASC;

3.2 根据入职时间对员工进行降序排序

SELECT * FROM emp ORDER BY ENTRYDATE DESC;

3.3 根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序

SELECT * FROM emp ORDER BY age ASC,ENTRYDATE DESC;

(7).DQL_分页查询

1.分页语法

select 字段列表 from 表名 limit 起始索引,查询记录数;

注意:

• 起始索引从0开始， 起始索引=(查询页码-1)*每页显示数
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是Limit
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

2. 示列

2.1 查询第一页员工数据，每页展示10条

SELECT * FROM emp LIMIT 10

2.2 查询第二页的十条数据

SELECT * FROM emp LIMIT 10,10

(8).DQL_示列

1.查询年龄为20，21，22，23岁的女性员工信息。

SELECT * FROM emp WHERE gender = '女' AND age in(20,21,22,23);

2.查询性别为男，并且年龄在 20-40 岁以内的姓名为三个字的员工。

SELECT * FROM emp WHERE gender = '男' AND name like'___' AND age BETWEEN 20 AND 40 

3.统计员工表中，年龄小于60岁的，男生员工和女性员工的人数

SELECT gender,count(*) FROM emp WHERE age<60 GROUP BY gender

4.查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按照入职降序排序

SELECT name,gender,age FROM emp WHERE age<=35 ORDER BY age ASC,entrydate DESC

5.查询性别为男，且年龄在20-40岁以内的前5个员工，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序

SELECT * FROM emp WHERE gender='男' AND age BETWEEN 10 and 70 ORDER BY age asc, entrydate desc LIMIT 5

6. DCL (数据控制语言)

(1).DCL 介绍

DCL英文全称是 Data Controller Language (数据控制语言)，用来管理数据库用户、控制数据库的访问权限。简单的说就是不同的用户具有不同的管理权限。

(2).DCL 管理用户

1. 查询用户

use mysql;select * from user;

2. 创建用户

create use '用户名'@'主机名' identified by '密码'

3. 修改用户

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'; 

4. 删除用户

drop user '用户名'@'主机名';

(3).DCL_权限控制

MySQL 中定义了很多中权限，但是常用的就以下几种。

1.查询权限

show grants for '用户名'@'主机名';

2.授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

3.撤销权限

revoke 权限列表 on 数据库名 from '用户名'@'主机名'

(三)、函数 - (非聚合函数)

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

1. 字符串函数

(1).基础知识

1. concat(s1,s2...sn),将s1 s2 字符串拼接成新的字符串。
2. lower(str), 将字符串str全部转化为小写。
3. upper(str), 将字符串str全部转化为大写。
4. lpad(str,n,pad), 用字符串pad对str左边进行填充，直到n个字符的长度。
5. rpad(str,n,pad), 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度。
6. trim(str),去掉字符串头部和尾部的空格。
7. substring(str,start,len),返回从字符串str的start位置起的len个长度的字符串。第一位的位置是1。

(2).字符串语法

select 函数;

(3).示列

1. contact

SELECT concat('hello','mysql');

2. lower 和 upper

select lower('Hello')

select upper('Hello')

3. lpad 和 rpad

select LPAD('李明',10,'_')

4. substring

select SUBSTRING('abcd',1,2)

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

update emp set WORKNO = LPAD(WORKNO,6,'0')

2.数值函数

(1).基础知识

常见的数值函数如下:

1. ceil(x), 向上取整。
2. floor(x),向下取整。
3. mod(x,y),返回x/y的模，也就是余数。
4. rand(),返回0~1内的随机数。
5. round(x,y),求参数x的四社五入的值，保留y位小数。

(2).示列

通过数据库函数，生成一个随机六位数的密码。

SELECT LPAD(substring(RAND()*1000000,1,6),6,'0')

3.日期函数

(1).基本知识

1. curdate(),返回当前日期。
2. curtime(),返回当前时间。
3. now(),返回当前日期和时间。
4. year(date),获取指定date的年份。
5. month(date),获取指定date的月份。
6. day(date),获取指定date的日期。
7. date_add(date,interval expr type)。返回一个日期/时间值间隔expr后的值。
8. datediff(date1(减数),date2(被减数)),返回起始时间date1和结束时间date之间的天数。

(2).示列

1.返回当前时间

now()

2.返回指定间隔后时间是多少

select date_add(now(), interval 70 day)

3.查询入职时间为多少天

select datediff( '2023-11-08 18:45:17',now())

4.查询所有员工的入职天数，并根据入职天数倒叙排序

SELECT `NAME`,DATEDIFF(NOW(),ENTRYDATE) FROM emp ORDER BY DATEDIFF(NOW(),ENTRYDATE) DESC

4.流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

(1).基本知识

if(value,t,f),如果value为true,则返回t,否则返回f。
2. ifnull(value1,value2),如果value1不为空，返回value1,否则返回value2。
3. case when [val1] then [res1] ... else [default] end, 如果val1为true,返回res1, …否则返回default默认值。
4. case [expr] when [val1] then [res1] ... else [default] en,如果expr的值等于val1,返回res1,否则返回default默认值。

(2).示列

1.查询emp表的员工姓名和工作地址(北京/上海 --->展示为一线城市,其他的为二线城市) 使用4

SELECT `NAME`,WORKADDRESS,( CASE WORKADDRESS ⭐WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市'  END ) FROM emp;

2.统计班级各个学员的成绩，展示的规则如下: >=85优秀，>=60及格,否则不及格。 使用3

SELECT `NAME`,AGE,CASE ⭐WHEN AGE>=70 THEN'优秀'WHEN AGE>=60 AND AGE<70 THEN  '及格'ELSE'不及格'END FROM emp;

#  也可以设置多条流程函数用于求单科的优秀还是不优秀。⭐SELECT `NAME`,AGE,(CASE WHEN AGE>=70 THEN '优秀' WHEN AGE>=60 AND AGE<70 THEN '及格' ELSE '不及格'END),(CASE WHEN AGE>=70 THEN '优秀' WHEN AGE>=60 AND AGE<70 THEN '及格' ELSE '不及格'END)FROM emp;

注意

1. when 和 then 可以写多个代表着 或 的意思。

(四)、约束

1.约束概述

(1).约束的概念

概念: 约束时作用于表中字段上的规则，用于限制存储在表中的数据。

(2).约束的目的

保证数据库中数据的正确、有效性和完整性。

(3).约束的分类

1. 非空约束(Not Null) 限制字段的数据不能为null
2. 唯一约束(unique) 保证该字段的所有数据都是唯一、不重复
3. 主键约束(primary key) 主键是一行数据的唯一标识，要求非空且唯一
4. 默认约束(Default) 保存数据时，如未指定字段则采用默认值
5. 检查约束(check_8.0.16版本支持) 保证字段值满足某一个条件
6. 外键约束(foreign key) 用来让两张表之间建立联系，保证数据的一致性和完整性。

注意: 约束是作用于表中字段上的，可以创建表/修改表的时候添加约束。

2.约束演示

# 会报错，因为的check约束是8.0.16版本以后才支持的，我们的数据库6.0.13版本的，所以会报错create table user(id int PRIMARY key auto_increment comment '主键',name varchar(20) not null unique comment '姓名',age int check(age>0 && age<=120) comment '年龄',  # 年龄在0~120岁之间statu char(1) default '1' comment '状态',gender char(1) comment '性别' );

3.外键约束

(1).外键约束的概念

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

(2).SQL语句

创建一个新的数据库: 数据库名字叫做itheima

create table dept(id int primary key auto_increment,name varchar(10) ); insert into dept values (null, '研发部'), (null, '市场部'), (null, '财务部'), (null, '销售部'), (null, '总经办'), (null, '人事部');create table emp( id int primary key auto_increment,name varchar(10),age int, job varchar(10), salary int, entrydate date,managerid int,dept_id int, constraint fk_dept foreign key (dept_id) references dept(id)); ⭐insert into emp values (null, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5), (null, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1), (null, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1), (null, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1), (null, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1), (null, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1), (null, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3), (null, '周芷若', 19, '会计', 4800, '2006-06-02', 7, 3), (null, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3), (null, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2), (null, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2), (null, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2), (null, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2), (null, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4), (null, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4), (null, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4), (null, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);create table salgrade( grade int, losal int, hisal int ); insert into salgrade values (1, 0, 3000), (2, 3001, 5000), (3, 5001, 8000), (4, 8001, 10000), (5, 10001, 15000), (6, 15001, 20000), (7, 20001, 25000), (8, 25001, 30000); 

(3).外键约束的语法

具有外键语法的表称为从表、不具有外键语法的表成为主表。

1. 语法

第一种添加外键- 创建表的时候

create table 表名(字段名 数据类型,...[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名))

第二种添加外键- 创建表之后

alter table 从表表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)

删除外键

alter table 从表表名 drop foreign key 外键名称。

(4).有外键约束与外键约束情况

1. 无外键的情况下

假如两张表数据是相互联系作用的，在没有绑定主外键的情况下，一张表数据的删除将不会影响到另一张表的删除。从而出现了数据不同步的情况。

2. 有外键的情况下

我们尝试删除主表中的数据，我们发现提示我们删除不了这个字段，因为在从表中有行使用我们这个字段。

4.外键删除更新行为

(1).删除/更新行为

1. not action : 在主表中删除/更新对应记录时，首先检查该记录是否对应外键，如果有则不允许删除/更新。
2. restrict : 当在主表中删除/更新记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
3. cascade(级联) : 当在主表中删除/更新对应记录时，首先检查记录是否对应外键，如果有，则也删除/更新外键在子表中的记录。
4. set null : 当在主表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null (这就要求前提是外键允许null)
5. set deffault 主表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)

注意: MySQL默认支持 no action 和 restrict 这两种行为机制。

(2).修改删除/更新行为语法

alter table 从表表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update xxxx on delete xxxx;

(3).级联删除/更新测试

# 先删除外键alter table emp drop foreign key fk_dept;# 再添加外键alter table emp add constraint fk_dept foreign key (dept_id) references dept(id) on update CASCADE on delete cascade;

1. 我们将主表的编码为2 更改为6.根据级联的特性，所以从表的字段内容也随着改变。
   
   

(4). set null 删除/更新测试

# 先删除外键alter table emp drop foreign key fk_dept;# 再添加外键alter table emp add constraint fk_dept foreign key (dept_id) references dept(id) on update set NULL on delete set null;

1.我们将我们的主键设置成6更改成8，发现从表数据修改为null

(五)、多表查询

1.多表关系

(1).多表查询概述

项目开发中，再进行数据表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为:

1. 一对一
2. 多对多
3. 一对多(多对一)

(2).一对多 (多对一)关系

这种关系最典型的列子就是 员工和部门的关系。员工为N 部门为1。

这种我们通常在从表(员工表)也就是员工表创建一个外键，与主表(部门表)进行联系。

(3).多对多关系

这种关系最典型的列子就是 学生与课程的关系。一个学生可以选择多门课程，一门课程可以供多各学生选择。

这种我们需要建立第三张表(中间表)并包括两个外键，第一个外键需要与学生表进行关联，另一张表需要和课程表进行关联。

建立 学生表+课程表+中间表

create table student(    id int auto_increment primary key comment '主键ID',    name varchar(10) comment '姓名',    no varchar(10) comment '学号') comment '学生表' charset=utf8;insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊','2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104') ;create table course(    id int auto_increment primary key comment '主键ID',    name varchar(10) comment '课程名称') comment '课程表' charset=utf8;insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') ,(null, 'Hadoop');create table student_course(    id int auto_increment comment '主键' primary key,    studentid int not null comment '学生ID',    courseid int not null comment '课程ID',    constraint fk_courseid foreign key (courseid) references course (id),    constraint fk_studentid foreign key (studentid) references student (id))comment '学生课程中间表' charset=utf8;insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3);

(4)一对一关系

这种关系最典型的列子就是 用户与用户详情的关系。

一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。

这种关系我们需要在 任意一方假如外键，关联另外一方的主键，并且设置外键为唯一的(unique)

create table tb_user(id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',age int comment '年龄',gender char(1) comment '1: 男 , 2: 女',phone char(11) comment '手机号') comment '用户基本信息表';create table tb_user_edu(id int auto_increment primary key comment '主键ID',degree varchar(20) comment '学历',major varchar(50) comment '专业',primaryschool varchar(50) comment '小学',middleschool varchar(50) comment '中学',university varchar(50) comment '大学',userid int unique comment '用户ID',constraint fk_userid foreign key (userid) references tb_user(id)) comment '用户教育信息表';insert into tb_user(id, name, age, gender, phone) values(null,'黄渤',45,'1','18800001111'),(null,'冰冰',35,'2','18800002222'),(null,'码云',55,'1','18800008888'),(null,'李彦宏',50,'1','18800009999');insert into tb_user_edu(id, degree, major, primaryschool, middleschool,university, userid) values(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

2.多表查询概述

(1).笛卡尔积效应

笛卡尔积时指在数学中，两个集合A集合和B集合的所有组合情况(排列组合)。(在多表查询时，需要消除无效的笛卡尔积)

-- 多表查询，会发现出现笛卡尔积效应select *from emp,dept;

(2).消除笛卡尔积效应

消除笛卡尔积效应的实质就是 消除掉不符合条件的排列组合数据。

-- 消除无效的笛卡尔积select *from emp,dept where emp.dept_id=dept.id;

3.多表查询分类

(1).连接查询

1. 内连接: 相当于查询A、B交集部分数据
2. 外连接:
   • 左外连接: 查询左表所有数据，以及两张表交集部分数据
   • 右外连接:查询右表所有数据，以及两张表交集部分数据
3. 自连接: 当前表与自身的连接查询，自连接必须使用表别名

(2).子查询

概念:SQL语句中嵌套Select语句，称为嵌套查询，又称子查询。

select *from 表1 where column1=(select column1 from 表2)

子查询外部的语句可以是 insert/update/delete/select的任何一个。

4.连接查询-内连接 (两张表交集部分) ⭐

(1).隐式内连接语法

selec 字段列表 from 表1，表2 where 条件...

(2).显示内连接语法

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

(3).隐式内连接和显示内联接测试

1. 隐式内连接 查询部门员工和对应部门

-- 隐式内连接使用where进行判断select emp.`name`,dept.name  from emp,dept on emp.dept_id=dept.id;-- 隐式内连接起别名,起玩别名之后只能用别名操作了select ep.`name`,dt.name  from emp as ep,dept as dt on ep.dept_id=dt.id;

2. 显示内连接查询部门员工

-- 显示内连接select emp.`name`,dept.name from emp inner join dept where emp.dept_id=dept.id;-- 显示内连接 可省略innerselect emp.`name`,dept.name from emp inner join dept where emp.dept_id=dept.id;

5.连接查询-外连接 (查询某张表的全部数据和交集部分)

相当于查询 left 或 right 左侧的第一个表的全部数据。

(1).外连接查询语法

1. 左外连接

这里相当于查询表1 的所有数据和表一与表二的交集

select 字段列表 from 表1 left [outer] join 表2 on 条件;

2. 右外连接

这里相当于查询表2 的所有数据和表一与表二的交集

select 字段列表 from 表1 right [outer] join 表2 on 条件;

(2).左外连接测试

关键字 left

-- 左外连接演示-- 1.查询emp表的所有数据，和对应的部门信息(左外连接)select * from emp left join dept on emp.dept_id=dept.id;-- 2.查询emp表的所有数据，和对应的部门信息(左外连接) 起别名select *from emp as ep left join dept as dt on ep.dept_id=dt.id; 

(3).右外连接测试

关键字 right

-- 1.查询dept表的所有数据，和对应的员工信息select dept.*,emp.`name` from emp right join dept on emp.dept_id=dept.id;

6.连接查询-自连接 (可交集、可单独) ⭐

子连接查询语法，一定要给表起别名。

(1).自连接查询语法

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

自连接查询，可以是内连接查询，也可以是外连接查询。

(2).自连接测试

1. 查询员工 及其 所属领导的名字

不管是什么连接都是经历了 排列组合 实现的数据查询

-- 自连接-- 1.查询员工 及其 所属领导的名字, 这里的ep_1相当于员工表，ep_2相当于领导表。 利用员工表的领导id 找 员工表的idselect ep_1.`name`,ep_2.`name` from emp ep_1 join emp ep_2 on ep_1.managerid=ep_2.id;

2. 查询所有员工 emp 及其 领导的名字emp,如果员工没有领导，也需要查询出来。

-- 查询所有员工 emp 及其 领导的名字emp,如果员工没有领导，也需要查询出来select ep_1.`name`,ep_2.`name` from emp ep_1 left join emp ep_2 on ep_1.managerid=ep_2.id;

7.联合查询-union,union all

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

(1).联合查询的语法

select 字段列表 from 表A...union [all]select 字段列表 from 表B...

(2).联合查询示列

1. 查询出所有满足的->不去重

-- 将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来select *from emp WHERE salary<5000 union all #⭐select *from emp WHERE age>50;

2. 查询出所有满足的->去重

-- 将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来  (去重)select *from emp WHERE salary<5000 union   # ⭐select *from emp WHERE age>50;

注意:

1. 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
2. union all会将全部的数据直接合并在一起，union会对合并之后的数据进行去重的操作。

8.子查询 (嵌套查询) ⭐

(1).子查询的基本语法

概念:SQL语句中嵌套Select语句，称为嵌套查询，又称子查询。

select *from 表1 where column1=(select column1 from 表2)

子查询外部的语句可以是 insert/update/delete/select的任何一个。

(2).子查询的分类

根据子查询结果不同，分为:

1. 标量子查询(子查询结果为单个值)
2. 列子查询(子查询结果为一列)
3. 行字查询(子查询结果为一行)
4. 表子查询(子查询结果为多行多列)

根据子查询位置，分为:where之后、from之后、select之后。

9.子查询-标量子查询 (返回结果是一个值)

(1).标量子查询

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

常用的操作符: <> = > >= < <=

(2).标量子查询示列

1..查询销售部的所有员工

-- 标量子查询-- 1.查询销售部的所有员工信息-- 1.1先查找销售部的idselect  id from dept where `name`='销售部'-- 1.2再查找这个数据select *from emp where emp.dept_id=4;-- 完整写法select *from emp where emp.dept_id= (select  id from dept where `name`='销售部');

2.查询在房东白入职之后的员工信息

-- 查询在方东白入职之后的员工信息select entrydate from emp where name ='方东白';select *from emp where entrydate>'2009-02-12';-- 完整写法select *from emp where entrydate>(select entrydate from emp where name ='方东白');

10.子查询-列子查询 (单列但可多行)

返回结构是一列数据 比如: 所有查询所有员工的id。 也就是一行数据里面的一个字段，但是可以是多行的同一字段。

(1).列子查询

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

常用的操作符: IN、NOT IN、ANY、SOME、ALL。

1. IN : 在指定的集合范围之内，多选一。
2. NOT IN: 不在指定的集合范围之内。
3. ANY: 子查询返回列表，有任意一个满足即可。
4. SOME: 与ANY等同，使用SOME的地方都可以使用ANY。
5. ALL:子查询返回列表的所有值都必须满足。

(2).列子查询示列

1.查询销售部和市场部的所有员工

-- 列子查询-- 1.查询销售部和市场部的所有员工select id from dept where dept.`name` in ('市场部','销售部');select *from emp where emp.dept_id in (2,4);select *from emp where emp.dept_id in (select id from dept where dept.`name` in ('市场部','销售部'));

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

-- 2.查询比财务部所有人工资都高的员工第一种：使用聚合函数-- 2.1 首先查询财务部的部门id select id from dept where dept.`name`='财务部'-- 2.2然后聚合函数查询财务部的最高工资select max(emp.salary) from emp where emp.dept_id=(select id from dept where dept.`name`='财务部')-- 2.3 查询信息select *from emp where emp.salary >= (select max(emp.salary) from emp where emp.dept_id=(select id from dept where dept.`name`='财务部')); 第二种：使用列子查询select *from emp where emp.salary  >= all(select emp.salary from emp where emp.dept_id=(select id from dept where dept.`name`='财务部')); 

3.查询比开发部任何一人工资低的员工信息

-- 3. 查询比研发部其中任意一人工资高的员工信息第一种: 使用聚合函数select dept.id from dept where dept.`name`='研发部';select min(emp.salary) from emp where emp.dept_id = (select dept.id from dept where dept.`name`='研发部');select *from emp where emp.salary >=(select min(emp.salary) from emp where emp.dept_id = (select dept.id from dept where dept.`name`='研发部'));第二种：使用列子查询 (some、any)select *from emp where emp.salary >= some(select emp.salary from emp where emp.dept_id = (select dept.id from dept where dept.`name`='研发部'));

11.子查询-行子查询 (单行但可多列)

返回的结果是一行，比如说查询 和 张无忌的薪资结构相同且领导相同的员工信息。薪资结构和领导都是张无忌一行的数据，一行数据多列字段。

(1).行子查询

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

(2).行子查询示列

1.查询与 张无忌的薪资结构 及 直属领导 相同的员工信息。查询的是张无忌一个

-- 行子查询-- 1.查询与 张无忌的薪资结构 及 直属领导 相同的员工信息select salary '薪资结构',managerid '管理者' from emp where emp.`name`='张无忌';select *from emp where (emp.salary,emp.managerid) =(12500,1);⭐select *from emp where (emp.salary,emp.managerid) =(select salary '薪资结构',managerid '管理者' from emp where emp.`name`='张无忌');

12.子查询-表子查询 （多行且多列）

比如: 两行分别是 鹿杖客 宋远桥 这两行，两列 分别是 薪资结构和职位。

(1).表子查询

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

(2).表子查询示列

1.1.查询与 鹿杖客 宋远桥 的职位和薪资相同的员工信息

-- 表子查询-- 1.查询与 鹿杖客 宋远桥 的职位和薪资相同的员工信息select emp.job,emp.salary from emp where emp.`name` in ('鹿杖客','宋远桥');-- 这里放in 表示要么一起满足这一行数据，要么一起满足下一行数据select *from emp where (emp.job,emp.salary) in (select emp.job,emp.salary from emp where emp.`name` in ('鹿杖客','宋远桥'));

2.查询入职日期是 2006-01-01 之后的员工信息，及其部门信息

-- 2. 查询入职日期是 2006-01-01 之后的员工信息，及其部门信息# 这个方法查询不完整，只查询到了交集,而要求我们查询一张表的全部信息select *from emp,dept where emp.entrydate > '2006-01-01' and emp.dept_id=dept.id;  select *from (select * from emp where emp.entrydate> '2006-01-01') as emp_a left join dept on emp_a.dept_id=dept.id;

注意： 我们新派生一张表的时候，我们要给这个新派生的表起别名，否则会报错。

起别名之后：

-- 1.查询员工的姓名，年龄、职位、部门信息 （隐式内连接）select emp.`name`,emp.age,emp.job,dept.id from emp,dept where emp.dept_id = dept.id;-- 2.查询年龄小于30岁的员工的姓名，年龄，职位，部门信息(显示内连接)#这里一定要使用where,如果使用and的话显示内连接会有效，但是外连接就会无效。select *from emp join dept on emp.dept_id=dept.id where emp.age>30;-- 3.查询拥有员工的部门ID、部门名称  (交集)select DISTINCT dept.id,dept.`name` from dept join emp on dept.id=emp.dept_id;--  4.查询所有年龄大于40岁的员工，及其归属的部门名称；如果没有分配部门，也需要展示出来 (左外连接)#这里千万不要使用and,如果使用and，外连接将会失效select emp.`name`,dept.`name` from emp left join dept on emp.dept_id = dept.id where emp.age>40;-- 5.查询所有员工的工资等级 : 员工表和薪资等级表是没有外键关联(直接在笛卡尔积中赛选了)  (隐式内连接)select  emp.`name`,emp.salary,salgrade.grade from emp,salgrade where emp.salary>=salgrade.losal and emp.salary<=salgrade.hisal;-- 6.查询 研发部 所有员工的信息及工资等级 (隐式内连接)select emp.`name`,emp.salary,dept.`name`,salgrade.grade from emp,salgrade join dept on dept.id=(select id from dept where dept.name='研发部') where emp.salary>=salgrade.losal and emp.salary<=salgrade.hisal;-- 7.查询研发部 员工的平均薪资 (隐式内连接)select avg(emp.salary) from emp  join dept on dept.id=emp.dept_id where dept.id=(select id  from dept where dept.name='研发部');-- 8. 查询工资比 灭绝 高的员工信息 (联表)select * from emp where  emp.salary >(select salary from emp where emp.`name`='灭绝');-- 9.查询比平均薪资高的员工信息 （联表）select *from emp where emp.salary>(select avg(emp.salary) from emp);-- 10.查询低于本部门平均工资的员工信息  (自连接)select * from emp e2 where e2.salary<(select avg(e1.salary) from emp e1 where e1.dept_id =e2.dept_id);-- 11.查询所有的部门信息，并统计部门的员工人数 (分组)select count(*),dept.`name` from emp,dept where dept.id=emp.dept_id GROUP BY emp.dept_id;-- 12.查询所有学生的选课情况，展示出学生名称、学号、课程名称。select *from student,course where (student.id,course.id) in (select student_course.studentid,student_course.courseid from student_course)

注意：

1. on后面的是联查条件，where是非联查条件。非联查条件不能用on;联查条件不能用where。
   

(六)、事务

1.事务简介

事务 是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即浙西操作要么同时成功，要么同时失败。

drop table if exists account;create table account(id int primary key AUTO_INCREMENT comment 'ID',name varchar(10) comment '姓名',money double(10,2) comment '余额') comment '账户表';insert into account(name, money) VALUES ('张三',2000), ('李四',2000);

注意：

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

2.事务操作

(1).未出现异常下的事务

张三转账给李四

-- 事务 (张三给李四转账1000)-- 1.查询张三账户余额select *from account where name='张三';-- 2.将张三账户余额-1000update account set account.money=account.money-1000 where account.`name`='张三';-- 3.将李四的余额+1000update account set account.money=account.money+1000 where account.`name`='李四';

(2).出现异常下的事务

当张三转完账单之后，李四账户还没来得及收时，有一个错误

-- 事务 (张三给李四转账1000)-- 1.查询张三账户余额select *from account where name='张三';-- 2.将张三账户余额-1000update account set account.money=account.money-1000 where account.`name`='张三';-- 3.将李四的余额+1000圣诞树上的  -- 制造错误update account set account.money=account.money+1000 where account.`name`='李四';

3.事务控制 - (第一种方法)

(1).事务管理方法

1.查看/设置事务提交方式

select @@autocommit;  #如果为1就是自动提交，如果为0就是不自动提交set @@autocommit=0; #设置事务不自动提交。

2.设置完手动提交后，我们要进行手动提交

commit;  #事务提交

3.假如提交后出现了异常，我们可以执行这个语句进行回滚事务。

rollback;  #事务回滚

(2).事务提交示列

1. 事务控制 - 手动提交(但是未提交)

-- 事务 (张三给李四转账1000)select @@autocommit;set @@autocommit=0;-- 1.查询张三账户余额select *from account where name='张三';-- 2.将张三账户余额-1000update account set account.money=account.money-1000 where account.`name`='张三';-- 3.将李四的余额+1000update account set account.money=account.money+1000 where account.`name`='李四';

2. 事务控制 - 手动提交(进行提交)

单独 执行commit之后，以前编写的数据才会开始同步。

commit;

(3).事务回滚示列

1. 执行有异常代码未回滚但已提交

-- 事务 (张三给李四转账1000)select @@autocommit;set @@autocommit=0;-- 1.查询张三账户余额select *from account where name='张三';-- 2.将张三账户余额-1000update account set account.money=account.money-1000 where account.`name`='张三';-- 3.将李四的余额+1000项目出现异常update account set account.money=account.money+1000 where account.`name`='李四';commit;

2. 执行有异常代码回滚且已提交

rollback;

在提交之后执行回滚的话，数据并不会回滚。假如进行回滚的话，那么就不要执行提交了；如果执行提交的话，就不用执行回滚了。

rollback;  #先回滚后提交

4.事务控制 -(第二种方法)

第二种方式我们不需要设置成手动提交。

(1).事务方法

1.开启事务

start transaction 或 begin;

2. 提交事务

commit;

3.回滚事务

rollback;

(2).事务控制

-- 2. 方法start transaction-- 1.查询张三账户余额select *from account where name='张三';-- 2.将张三账户余额-1000update account set account.money=account.money-1000 where account.`name`='张三';-- 3.将李四的余额+1000项目出现异常update account set account.money=account.money+1000 where account.`name`='李四';rollback;  #假如有错我们就进行回滚的操作 catchcommit;  #假如运行没有异常进行提交的操作

5.事务四大特性 (ACID)

(1).四大特性

• 原子性： 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性：事务完成时，必须使用所有的数据都保持一致状态。
• 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的。

6.并发事务问题

(1).并发事务引起的三大问题

1. 脏读： 一个事务读到另外一个事务还没有提交的数据。
2. 不可重复读： 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
3. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。

(2).三大问题详细介绍

1.脏读：(读取为提交的修改事务，且执行一次查询)

张三开启一个取钱的事务，已经从卡中取了1000，余额还剩1000，但是未关闭服务(也就是还未执行提交)。这个时候张三老婆同时也开启了一个查询的事务，此时张三老婆查看余额已经读取到了余额1000元。                        

2.不可重复读: (读取了提交的修改事务,且执行两次查询)

张三老婆正在查询银行卡余额发现有2000元，此时张三开启一个取钱的事务，从卡中取了1000 并提交了事务，张三老婆再次查找余额，发现余额突然剩下1000了，于是急忙地找工作人员进行处理这个问题。                        

3.幻读：(读取了提交的新增事务，且执行了一次查询和一次新增)

张三开启事务在办理一个员工的入职手续，由于工作习惯张三 先查询了一下这个员工是否办理过入职，发现没有便去上个厕所了，但此时另一个同事把这个员工添加了进去并提交了事务，张三上完厕所回来便添加这个员工发现提示这个员工已经存在，便再次查询了这个员工，发现依然没有。张三大叫见鬼了!!!!!

7.事务隔离级别

(1).事务隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	✅	✅	✅
Read committed	❌	✅	✅
Repeatable Read(默认)	❌	❌	✅
Serializable	❌	❌	❌

(2).查看/操作事务隔离级别

1.查看事务隔离级别

select @@transaction_isolation   #版本6.0+select @@tx_isolation   #版本6.0一下

2.设置事务隔离级别

# 假如是session只对当前窗口有效，假如设置的是global那么全部窗口都有效set [session|global] transaction isolation level {Read uncommitted | Read committed | Repeatable Read | Serializable }

3.版本6.0一下用 select @@transaction_isolation 报错

(3).演示脏读

# global 全局都设置，session 设置当前窗口(会话)set global transaction isolation level Read uncommitted;

select @@tx_isolation;

结果读取到了未提交的数据

(4).演示不可重复读

(5).演示幻读

来源地址：https://blog.csdn.net/qq_69683957/article/details/132544871