MySQL数据库 - 基础篇

概述

是一种 关系型数据库：建立在关系模型上，由多张互相连接的二维表组成的数据库。关系数据库 特点：1. 使用表存储数据，格式统一，便于维护2. 使用SQL语言操作，标准统一，使用方便
MySQL的数据模型从客户端到DBMS，再到数据库，最后到达数据库中的表

MySQL启动的命令

net start mysql80  # 这里的mysql80是注册在Windows中的服务名称
net stop mysql80

MySQL客户端连接MySQL自带的客户端命令行CMD执行命令：

mysql -h 127.0.0.1 -P 3306 -u root -p

SQLSQL通用语法1. SQL语句单行，多行均可，默认分号结尾2. 使用空格/缩进以增加可读性3. 不区分大小写，推荐关键字使用大写4. 注释：单行注释：– 通用，# MySQL独有多行注释：/**/
SQL分类：DDL: Data Definition Language 数据定义语言，用来定义数据库对象 （数据库，表，字段）DML: Data Manipulation Language 数据操作语言，用来对数据库表中的数据进行增删改DQL: Data Query Language 数据查询语言，用来查询数据库中表的记录DCL: Data Control Language 数据控制语言，用来创建数据库用户、控制数据库的访问权限
DDL-数据库操作:查询查询所有数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();

创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]

案例：

create database if not exists test; -- 如果不存在，创建test数据库
create database itheima default charset utf8mb4;  -- 创建utf8mb4字符集的itheima 数据库

删除

DROP DATABASE [IF EXISTS] 数据库名

使用数据库

USE 数据库名

查询当前数据库全部表

SHOW TABLES;

查询表结构

DESC 表名 或者 DESCRIBE 表名

查询指定表的建表语句

SHOW CREATE TABLE 表名

DDL-表操作-创建

CREATE TABLE 表名(
    字段1   字段1类型[COMMENT  字段1注释],
    字段2   字段2类型[COMMENT  字段2注释],
    ......
    字段n   字段n类型[COMMENT  字段n注释]
)[COMMENT 注释];

第二章讲解InnoDB底层原理

DDL-表操作-数据类型主要分三类：数值类型，字符串类型，日期时间类型数值类型

实例：

age TINYINT UNSIGNED  # 0~255的整数
score DOUBLE(4, 1)   #100.0最长是4，填4，80.5，70.5，最多一位小数，填1。

字符串类型

定长/变长字符串的区别

char(10) # 长度固定为10，没用的部分用空格补位  --> 性能好？
varchar(10) # 长度最长不超过10，小于10时体现实际长度  --> 性能较差

实例：用户名 username 用那个更好呢？答案：varchar(50)更好。因为用户的用户名不是定长的，使用varchar可以节约空间性别 gender 用哪个？答案：char(1)
日期类型

实例：

birthday date  # 生日用date类型更合适

案例：根据需求创建表（设计合理的数据类型、长度）

答案：

create table emp (
    id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    entrydate date comment '入职时间'
) comment '员工表';

DDL-表操作-修改添加字段

ALTER TABLE 表名 ADD 字段名 类型（长度） [COMMENT 注释] [约束];

案例：为emp表添加一个字段“昵称”为nickname，类型为varchar(20)答案：

ALTER TABLE emp add nickname varchar(20) comment '昵称';

修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名称 类型（长度） [COMMENT 注释] [约束];

案例：将emp表的nickname 修改为username，类型为varchar(30)答案：

ALTER TABLE emp change nickname username varchar(30) comment '用户名';

删除字段

ALTER TABLE 表名 DROP 字段名;

案例：删除emp中的username列答案：

ALTER TABLE emp drop username;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

案例：将emp表表名修改为employee

ALTER TABLE emp RENAME TO employee;

删除表

DROP TABLE [IF EXISTS] 表名;

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

TRUNCATE TABLE 表名;

MySQL图形化界面：
MySQL Workbench
DML-介绍DML的全称是Data Manipulation Lanagage (数据操作语言），用来对数据表中的数据记录进行增删改操作。添加数据 (INSERT)修改数据 (UPDATE)删除数据 (DELETE)
DML-添加数据1. 给指定字段添加数据

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

2. 给全部字段添加数据

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

3. 批量添加数据

INSERT INTO 表名 (字段名1，字段名2，...) VALUSE (值1，值2，...), VALUSE (值1，值2，...);
INSERT INTO 表名 VALUSE (值1，值2，...), VALUSE (值1，值2，...);

注意：

DML-修改数据UPDATE 表名 SET 字段名1=值1, 字段名2=值2, … [WHERE 条件];注意：如果修改的条件没有，则会修改整张表的数据。案例：

-- id = 1 的名字修改为it 黑马
update employee set name = 'kelly' where id = 1;

-- id = 1 的名字修改为Kelly, gender 修改为女
update employee set name = 'kelly', gender = 'W' where id = 1;

-- 将所有员工的入职时间设定为2008-01-01
update employee set entrydate = '2008-01-01';

DML-删除数据DELETE FROM 表名 [WHERE 条件];注意：DELETE语句的条件可有可无，没有的情况下会删除整张表的数据。DELETE语句不能删除某一个字段的值（可以使用UPDATE）。案例：

-- 删除gender为W的员工
DELETE FROM employee WHERE gender = 'W';

-- 删除所有员工
DELETE FROM employee;

DQL-介绍DQL是Data Query Language 的缩写，用来查询数据库中表的记录。查询关键字：SELECT
DQL-语法

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

基本查询条件查询 WHERE聚合函数 count(), max(), min(), avg(), sum()分组查询 GROUP BY排序查询 ORDER BY分页查询 LIMIT
DQL-基本查询1. 查询返回多个字段

SELECT 字段1，字段2，字段3... FROM 表名;
SELECT * FROM 表名;

2. 设置别名

SELECT 字段1 [AS 别名1], 字段2 [AS 别名2] ... FROM 表名;

其中AS可以省略3. 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

案例：数据准备：

-- 数据准备
CREATE TABLE emp(
  id INT,
    workno VARCHAR(10),
    name VARCHAR(10),
    gender CHAR(1),
    age TINYINT UNSIGNED,
    idcard CHAR(18),
    workaddress VARCHAR(50),
    entrydate DATE
) COMMENT '员工表';

DESC emp;

INSERT INTO emp VALUES
(1, '1', 'aa', 'W', 28, '123456789012345678', 'Beijing', '2000-01-01'),
(2, '2', 'ab', 'M', 18, '123456789012335678', 'Dalian', '2000-01-01'),
(3, '3', 'ac', 'M', 8, '123456789012345678', 'Beijing', '2006-01-01'),
(4, '4', 'ad', 'W', 28, '123456783412345678', 'Dalian', '2002-01-01'),
(5, '5', 'ae', 'W', 38, '123456783312345678', 'Beijing', '2000-01-01'),
(6, '6', 'af', 'M', 68, '123456786012345678', 'Dalian', '2000-01-06'),
(7, '7', 'ag', 'W', 28, '123456789012445678', 'Beijing', '2007-01-01'),
(8, '8', 'ah', 'M', 8, '123456789012235678', 'Shanghai', '2000-01-01'),
(9, '9', 'ai', 'W', 28, '123456789022345678', 'Beijing', '2011-01-02'),
(10, '10', 'aj', 'W', 98, '123456722212345678', 'Beijing', '2000-01-01'),
(11, '11', 'ak', 'M', 28, '123456555512345678', 'Shanghai', '2000-01-01'),
(12, '12', 'al', 'W', 78, '123456789512345678', 'Beijing', '2000-01-01'),
(13, '13', 'am', 'M', 16, '123456789712345678', 'Shanghai', '1990-01-01'),
(14, '14', 'an', 'W', 18, '123456789872345678', 'Beijing', '2000-01-01'),
(15, '15', 'ao', 'M', 2, '123456789012345678', 'Shanghai', '2005-01-01'),
(16, '16', 'aq', 'W', 22, '123456782362345678', 'Beijing', '2000-01-01');

--  基本查询

-- 1. 查询指定字段，name，workno，age返回
SELECT name, workno, age FROM emp;

-- 2. 查询所有字段返回
SELECT * FROM emp;  -- 尽量不要写*，因为效率低而且不安全，相比之下写出来所有字段更好
SELECT id, workno, name, gender, age, idcard, workaddress, entrydate FROM emp;

-- 3. 查询所有员工的工作地址
SELECT workaddress as '工作地址' FROM emp;
SELECT workaddress '工作地址' FROM emp;

-- 4. 查询员工的上班地址（不要重复）
SELECT DISTINCT workaddress '工作地址' FROM emp;

DQL-条件查询1. 语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

2. 条件比较运算符![]
逻辑运算符

案例：

-- 条件查询
-- 1. 查询年龄等于98的员工
SELECT * FROM emp WHERE age = 98;
-- 2. 查询年龄小于20 的员工
SELECT * FROM emp WHERE age < 20;
-- 3. 查询年龄小于等于20的员工信息
SELECT * FROM emp WHERE age <= 20;
-- 4. 查询没有身份证号的员工信息
SELECT * FROM emp WHERE idcard IS NULL;
-- 5. 查询有身份证号的员工
SELECT * FROM emp WHERE idcard IS NOT NULL;
-- 6. 查询年龄不是88的员工
SELECT * FROM emp WHERE age <> 88;
SELECT * FROM emp WHERE age != 88;
-- 7. 查询年龄在15~20岁的员工
SELECT * FROM emp WHERE age BETWEEN 15 AND 20;
SELECT * FROM emp WHERE age >= 15 AND age <= 20;
-- 8. 查询性别为女而且年龄小于25的员工信息
SELECT * FROM emp WHERE gender = 'W' AND age < 25;
-- 9. 查询年龄等于18或20或40的员工信息
SELECT * FROM emp WHERE age = 18 OR age = 20 OR age = 40;
SELECT * FROM emp WHERE age IN (18, 20, 40);
-- 10. 查询名字是两个字的员工的信息
SELECT * FROM emp WHERE name like '__';
-- 11. 查询身份证号最后一位是X的员工信息
SELECT * FROM emp WHERE idcard LIKE '%X';
SELECT * FROM emp WHERE idcard LIKE '_________________X';

DQL-分组查询语法：

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

WHERE和HAVING的区别- 执行时间不同，WHER是分组前过滤，HAVING是分组后过滤- 判断条件不同，WHERE不能对聚合函数进行判断，HAVING可以
案例：

-- 分组查询
-- 1. 根据性别分组，统计男性员工和女性员工的数量
SELECT gender, COUNT(*) total FROM emp GROUP BY gender;
-- 2. 根据性别分组，统计男性员工和女性员工的平均年龄
SELECT gender, AVG(age) avg_age FROM emp GROUP BY gender;
-- 3. 查询年龄小于45的员工，并根据工作地址进行分组，获取员工数量>=3的工作地址
SELECT workaddress, COUNT(*) address_count FROM emp WHERE age < 45 GROUP BY workaddress HAVING address_count >= 3;

注意：- 执行顺序：where > 聚合函数 > having- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义
DQL-排序查询语法：

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,  字段2 排序方式2;

排序方式：

ASC: 升序 （默认值）
DESC: 降序

案例：

-- 排序查询
-- 1 根据年龄对公司的员工进行升序排序
SELECT * FROM emp ORDER BY age ASC;  -- ASC可以省略；降序则改为DESC
-- 2 根据入职时间对员工进行降序排序
SELECT * FROM emp ORDER BY entrydate DESC;
-- 3 根据年龄对公司员工进行升序排序，如果年龄相同再按入职时间进行降序排序
SELECT * FROM emp ORDER BY age ASC, entrydate DESC;

DQL-分页查询语法：

SELECT 字段列表 FROM 表名 LIMIT 起始索, 查询返回记录数;

注意：- 起始索引从0开始，其实索引 = (查询页码 - 1) * 每页显示记录数- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL是LIMIT- 如果查询的是第一页的数据，起始索引可以省略，直接简写为LIMIT 10
案例：

-- 分页查询
-- 1. 查询第一页员工数据，每页显示10条记录
SELECT * FROM emp LIMIT 0, 10;
SELECT * FROM emp LIMIT 10;

-- 2. 查询第二页员工数据，每页显示10条记录
SELECT * FROM emp LIMIT 10, 10; -- (页码 - 1)*页展示记录数

DQL 案例课

-- 案例
-- 1. 查询年龄为20，21，22，23岁的女性员工信息
SELECT * FROM emp WHERE age IN (20, 21, 22, 23) AND gender = 'W';
-- 2. 查询性别为男并且年龄在20-40岁（含）且姓名为两个字的员工
SELECT * FROM emp WHERE gender = 'M' AND ( age BETWEEN 20 AND 40 ) AND name LIKE '__';
-- 3. 统计员工表中，年龄小于60岁的男性和女性员工的数量。SELECT gender, COUNT(*) age_count FROM emp WHERE age < 60 GROUP BY gender;
-- 4. 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按照年龄升序排序，年龄相同按照入职时间降序排序
SELECT name, age FROM emp WHERE age <= 35 ORDER BY age, entrydate DESC;
-- 5. 查询性别为W且年龄在20-40（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按照入职时间升序
SELECT * FROM emp WHERE gender = 'W' AND ( age BETWEEN 20 AND 40 ) ORDER BY age, entrydate LIMIT 5;

DQL - 执行顺序

SELECT        (4)
FROM          (1)
WHERE         (2)
GROUP BY      (3)
HAVING
ORDER BY      (5)
LIMIT         (6)


=>


FROM 
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

案例：

-- 查询年龄大于15的员工的姓名、年龄、并根据年龄进行升序排序
SELECT name, age FROM emp WHERE age > 15 ORDER BY age;
-- 顺序验证
SELECT name, age FROM emp E WHERE E.age > 15 ORDER BY age; 
-- FROM 第一
SELECT E.name, E.age FROM emp E WHERE E.age > 15 ORDER BY age; 
-- SELECT 在FROM和WHERE之后
SELECT E.name ename, E.age EAGE FROM emp E WHERE EAGE > 15 ORDER BY age; 
-- 失败：SELECT 在FROM和WHERE之后执行
SELECT E.name ename, E.age EAGE FROM emp E WHERE E.age > 15 ORDER BY EAGE; 
-- 成功：SELECT 在ORDER BY之前执行

DCL-介绍DCL 全称 Data Control Language （数据控制语言），用来管理数据库用户、控制数据库的访问权限
DCL-管理用户1. 查询用户

USE mysql;
SELECT * FROM user;

2. 创建用户

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

3. 修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

4. 删除用户

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

案例：

-- DCL
use mysql;
show tables;
select * from user;

-- 创建用户itcast，只能够在localhost 访问，密码123456
create user 'itcast'@'localhost' identified by '123456';

-- 创建用户heima，可以在任意机器访问该数据库，密码123456
create user 'heima'@'%' identified by '123456';

-- 修改用户heima的密码为1234
alter user 'heima'@'%' identified with mysql_native_password by '1234';

-- 删除itcast@localhost用户
drop user 'itcast'@'localhost';

注意：- 主机名可以用’%‘通配- 这类SQL开发人员使用较少，主要是DBA (Database Administrator 数据库管理员)使用
DCL-权限控制

1. 查询权限

SHOW GRANTS FOR '用户名'@‘主机名’;

2. 授予权限

GRANT 权限列表 ON 数据库.表名 TO '用户名'@‘主机名’;

3. 撤销权限

REMOVE 权限列表 ON 数据库名.表名 FROM '用户名'@‘主机名’;

案例：

-- 查询权限
show grants for 'heima'@'%';

-- 授予权限
grant all on itcast.* to 'heima'@'%';

-- 撤销权限
revoke all on itcast.* from 'heima'@'%';

注意：

总结：1. 用户管理

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
ALTER USER 'usernmae'@'host' IDENTIFIED WITH mysql_native_password BY 'password';
DROP USER 'username'@'host';

2. 权限控制

GRANT 权限列表 ON 数据库名.表名 TO ‘用户名'@'主机名';
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

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

• 字符串函数

• 数值函数

• 日期函数

• 流程函数

字符串函数

演示：

-- 函数演示
-- concat
select concat('Hello ', 'MySQL');

-- lower
select lower('Hello');

-- upper
select upper('Hello');

-- lpad
select lpad('01', 5, '-');

-- rpad
select rpad('01', 5, '-');

-- trim
select trim('  Hello MySQL    ');

-- substring
select substring('Hello MySQL', 1, 5);  # 这里字符串索引从1开始

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

-- 1. 员工号补0.
use itcast;
select * from emp;
update emp set workno = lpad(workno, 5, '0');

数值函数常见的数值函数如下：

演示

-- 数值函数
-- ceil
select ceil(1.1);
-- floor
select floor(1.5);
-- mod
select mod(3, 4);  # 3除以4取余
select mod(6, 4);
-- rand
select rand();  # 获得一个0-1的随机数
-- round
select round(2.345, 2);
select round(2.344, 2);

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

-- 2.通过数据库的函数，生成一个六位数的随机验证码
select
substring(rand(), 3, 6);  # 用substring截取
select lpad(round(rand() * 1000000, 0), 6, '0');  # 计算，取整，填充

日期函数常见的日期函数

演示：

-- 日期函数
-- curdate()
select curdate();
-- curtime()
select curtime();
-- now()
select now();

-- YEAR, MONTH, DAY
select year(now()), month(now()), day(now());

-- date_add(date, INTERVAL expr type)
select date_add(now(), INTERVAL 70 day);  # 70天之后，70天之前用负值即可

-- date_diff
select datediff(now(), '2022-12-01');  # 第一个日期减去第二个日期

案例：

-- 查询所有员工的入职天数，并根据入职天数倒序排序
select name, entrydate, datediff(now(), entrydate) 入职日期 from emp order by 入职日期 desc;  #可以用中文。

流程函数流程函数实现条件筛选，提高语句的效率：

IF(value, t, f)
IFNULL(value1, value2)
CASE WHEN [val1] THEN [res1] ... ELSE [default] END
CASE [expr] WHEN [val1] THEN [res1] .. ELSE [default] END

演示：

-- 流程函数
-- if
select if (true, 'ok', 'error');

-- ifnull
select ifnull ('ok', 'Default');  -- 第一个值为非null则返回第一个值，若为null则返回第二个值

select ifnull ('', 'Default');

select ifnull (null, 'Default');

-- case when then else end
-- 需求：查询emp员工姓名和工作地址（北京/上海 --->一线城市，其他--->二线城市）
select name, 
     case workaddress 
       when 'Beijing' then '一线城市' 
       when 'Shanghai' then '一线城市'
       else '二线城市' end
from emp;

案例：

-- 案例：统计班级各个学员的成绩，展示的规则如下：-- >= 85，展示优秀 
-- >= 60, 展示及格 
-- 否则， 展示不及格 
create table score(
  id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
) comment '学员成绩表';
insert into score
values (1, 'Tom', 67, 88, 95), (2, 'Rose', 23, 66, 90), (3, 'Jack', 56, 98, 76);

select * from score;

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

总结字符串函数

concat
lower
upper
lpad 左填充
rpad 右填充
trim 去除左右空格
substring  字符串截取

数值函数

ceil
floor
mod
rand
round 四舍五入，保留指定位小数

日期函数

curdate
curtime
now
year
month
day
date_add
datediff  第一个日期减去第二个日期

流程函数

if
ifnull
case [...] when.. then... else... end

约束概述：常见的约束有哪些

• 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据

• 目的：保证数据库中数据的正确，有效性和完整性

• 分类：

约束演示案例：根据需求，完成表的创建

id: primary key, auto_increment
name: not null, unique
age: check
status: default
gender: N/A
--  约束
create database itheima default charset utf8mb4;
use itheima;

create table user(
  id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check(age > 0 and age < 120) comment '年龄',   -- 用and或&&都可以 
    status char(1) default '1' comment '状态',
    gender char(1)
) comment '用户表';

select * from user;

insert into user(name, age, status, gender) values ('Tom1', 19, '1', '男'), ('Tom2', 25, '0', '男');
insert into user(name, age, status, gender) values ('Tom3', 19, '1', '男');

insert into user(name, age, status, gender) values (null, 19, '1', '男'); -- 出错，因为不可以出错
insert into user(name, age, status, gender) values ('Tom3', 19, '1', '男'); -- 不可重复执行，因为存在唯一约束 

insert into user(name, age, status, gender) values ('Tom4', 80, '1', '男');  -- id 是5，虽然刚才插入没有成功，但是id已经被申请过了 
insert into user(name, age, status, gender) values ('Tom5', -1, '1', '男');  -- 检查约束生效 
insert into user(name, age, status, gender) values ('Tom5', 121, '1', '男'); -- 检查约束生效 
insert into user(name, age, gender) values ('Tom6', 121, '男');

insert into user(name, age, gender) values ('Tom5', 120, '男');

拓展：约束的修改

-- 发现建表的时候约束设置的不正确,如何修改 
show create table user;  -- 查看现有约束的名称
alter table user drop constraint user_chk_1; -- 删除现有约束
delete from user where age = 121;  -- 如果有数据不满足约束,去除之 
alter table user add constraint user_chk_1 check (age > 0 and age <= 120);  -- 添加新的约束,操作结束

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

具有外键的表称为子表，被关联的表叫做父表注意：数据库层面，未建立外键关联，所以无法保证数据的一致性和完整性。演示：

-- 外键约束 
-- 准备数据
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, '总经办');

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 '员工表';

insert into emp (name, age, job, salary, entrydate, managerid, dept_id) values ('金庸', 66, '总裁', 20000, '2000-01-01', null, 5), ('张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
('杨逍', 33, '开发', 8400, '2000-11-03', 2, 1), ('韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
('常遇春', 43, '开发', 10500, '2004-09-07', 3, 1), ('小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

select * from emp;
select * from dept;

-- 以上,没有设置物理外键,无法保证数据之间的一致性和完整性

语法：添加外键

CREATE TABLE 表名(
    字段名 数据类型,
    ..
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
案例：
delete from dept where id = 1;  -- 成功删除 
insert into dept (id, name) values (1, '研发部');

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

delete from dept where id = 1; -- Cannot delete or update a parent row: a foreign key constraint fails

-- 删除外键 
alter table emp drop foreign key fk_emp_dept_id;

delete from dept where id = 1;  -- 删除成功

删除/更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段）
REFERENCES 主表 （主表字段名）
ON UPDATE CASCADE ON DELETE CASCADE;

案例：

-- 外键的删除和更新行为 
alter table emp add constraint fk_emp_dept_id
foreign key(dept_id) references dept(id)
on update cascade on delete cascade;  -- 级联更新/删除 

-- 级联更新操作 
select * from dept;
update dept set id = 6 where name = '研发部';
commit;
select * from emp;  -- 查看子表，的确更新为6了，这就是cascade的作用 
delete from dept where id = 6; -- 子表数据也被删除了 

-- SET NULL 操作 
drop table emp;
drop table 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, '总经办');

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 '员工表';

insert into emp (name, age, job, salary, entrydate, managerid, dept_id)
values ('金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
('张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
('杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
('韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
('常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
('小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

select * from dept;
select * from emp;

-- 添加外键 
alter table emp add constraint fk_emp_dept_id foreign key(dept_id)
references dept(id) on update set null on delete set null; 
-- set null更新/删除 

-- 删除id = 1的数据
delete from dept where id = 1;
select * from emp; 
-- 可见所有emp 原有dept_id = 1的数据现在 dept_id的值为 null

总结1. 非空约束 not null2. 唯一约束 unique3. 主键约束 primary key (自增: AUTO_INCREMENT， ORACLE没有这个，而是SEQUENCE)4. 默认约束 default5. 检查约束 check6. 外键约束 foreign key
多表查询多表关系各个表结构之间存在各种联系：一对多 （多对一）多对多一对一一对多（多对一）案例：部门 与 员工 的关系关系：一个部门对应多个员工，一个员工对应一个部门实现：再多的一方建立外键，只想一的一方的主键

多对多案例：学生 与 课程的关系关系：一个学生可以选择多门课程，一门课程也可以供多个学生选择实现：建立第三张中间表，中间层至少包含两个外键，分别关联两方主键

案例：

-- 多对多 
-- 学生表
create table student (
  id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';

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 '课程表';

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_student_id foreign key (studentid) references student(id)
) comment '学生课程中间表';

insert into student_course values (null, 1, 1), (null, 1, 2), (null, 1, 3), (null, 2, 2), (null, 2, 3), (null, 3, 4);

describe student_course;
select * from studenstudent_coursestudent_coursestudent_coursestudent_coursestudent_courset_course;

.一对一案例：用户 和 用户详情的关系关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率实现：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的（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 values
(null, '本科', '舞蹈', '青岛市第一小学', '青岛市第一中学', '北京电影学院', 1),
(null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '上海戏剧学院', 2),
(null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州示范大学', 3),
(null, '本科', '计算机', '阳泉第一小学', '阳泉第一中学', '清华大学', 4);

多表查询概述概述：指从多张表中查询数据笛卡尔积：指在数学中，两个集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）

案例:

-- 数据准备 
drop table emp;
drop table 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, '人事部');

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 '员工表';

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

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

-- 多表查询 
select * from emp, dept;  -- 查出来的结果好像比想象中要多 - 笛卡尔积 17 * 6 = 102 条

select * from emp, dept where emp.dept_id = dept.id;

多表查询分类- 连接查询    - 内连接: 相当于查询A.B交集部分的数据    

• 外连接:        
  左外连接: 查询左表中的所有数据，以及交集部分数据        
  右外连接：查询右表中的所有数据，以及交集部分数据   
• 自连接：当前表与自身的连接查询，自连接必须使用表别名\
• 子查询
  内连接内连接查询语法：    
  隐式内连接：

select 字段列表 from 表1, 表2 where 条件...;

    显式内连接：

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

案例：

-- 内连接演示
-- 1. 查询每一个员工的姓名,以及关联的部门的名称 (隐式内连接实现) 
select e.name, d.name from emp e, dept d where e.dept_id = d.id;  
-- 起别名之后不能直接用表名访问字段了 
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;

-- 2. 查询每一个员工的姓名以及关联的部门名称(显式内连接实现) 
select e.name, d.name from emp e 
inner join dept d
on e.dept_id = d.id;

外连接语法：    左外：

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

    右外：

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

案例：

-- 外连接演示 
-- 1. 查询emp表所有数据， 和对应的部门信息 （左连接）
select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

-- 2. 查询dept表中所有数据，和对应的员工信息 （右连接） 
select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

-- 等价于：select d.*, e.* from dept d left outer join emp e  on e.dept_id = d.id;

自连接语法：

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

自联结查询可以是内连接也可以是外连接案例：

-- 内连接演示 
-- 1. 查询员工及其所属的领导名字 
-- 表结构：emp表
select a.name employee, b.name manager from emp a, emp b
where a.managerid = b.id;

-- 2. 查询所有员工emp及其领导的名字emp，如果员工没有领导也需要查询出来 
-- 表结构：emp e1, emp e2
select e1.name employee, e2.name boss from emp e1
left join emp e2 on e1.managerid = e2.id; -- 左外

联合查询关键字：union, union all语法：

select 字段列表 表A ...
UNION [ALL]
select 字段列表 表B ... ;

案例：

-- union all, union
-- 1. 将薪资低于5000的员工，和年龄大于50的员工全部查询出来 
select * from emp where salary < 5000
union # union合并后去重，union all则没有去重 
select * from emp where age > 50;

# 如果第二个查询只查询name，则无法使用union因为列数量不同

小结：

• 联合查询多张表的列数必须保持一致，字段类型也必须一致

• union all 直接合并，union则会去重

子查询概念：SQL语句中嵌套SELECT语句，成为嵌套查询，又称子查询语法：

select * from t1 where column1 = (select column1 from t2);

注意 - 子查询外部语句可以是insert/update/delete/select的任何一个。
分类：子查询结果不同，可以分为：    标量子查询 （子查询结果为单个值）    列子查询 （子查询结果为一列）    行子查询 （子查询结果为一行）    表子查询 （子查询结果为多行多列）
根据子查询位置，分为：WHERE之后，FROM之后，SELECT之后。
标量子查询：概念：子查询返回结果为单个值（数字，字符串，日期等），最简单的形式，这种子查询称为标量子查询。常用的操作符：= <> > >= < <=
案例：

-- 标量子查询 
-- 1. 查询销售部所有员工信息 
-- a. 查询销售部部门id
select id from dept where name = '销售部';
-- b. 根据部门id查询员工信息 
select * from emp where dept_id = 4;
-- 使用子查询 
select * from emp where dept_id =
(select id from dept where name = '销售部');
-- 使用外连接 
select e.* from emp e join dept d on e.dept_id = d.id
where d.name = '销售部';

-- 2. 查询在“房东白”入职之后的员工信息 
-- a. 查询房东白的入职时间 
select entrydate from emp where emp.name = '房东白';
-- b. 查询比房东白入职晚的员工 
select * from emp where entrydate > '2009-10-12';

-- 使用子查询 
select * from emp where entrydate >
(select entrydate from emp where name = '房东白');

列子查询：概念：子查询返回的结果是一列（可以是多行），这种子查询称为列子查询常用的操作符：IN, NOT IN, ANY, SOME, ALL

案例：

-- 列子查询 
-- 1. 查询“销售部”和“市场部”的所有员工信息 
-- a. 找到销售部和市场部的部门id
select id from dept where name in ('销售部', '市场部');
-- b. 根据部门id，查询员工信息 

select * from emp where dept_id in
(select id from dept where name in ('销售部', '市场部'));

-- 2. 查询比财务部所有人工资都高的员工信息
-- 标量子查询 
select * from emp a where salary > ( 
select max(salary) from emp where dept_id in
(select id from dept where name = '财务部'));

-- 列子查询 
select * from emp where salary > all (
select salary from emp where dept_id in
(select id from dept where name = '财务部')
);

-- 3. 比研发部任意一人工资高的员工信息 
-- 列子查询 
select * from emp where salary > any (
select salary from emp where dept_id = (
select id from dept where name = '研发部'
));

-- 标量子查询
select * from emp where salary > (
select min(e.salary) from emp e join dept d on e.dept_id = d.id
where d.name = '研发部'
);

行子查询概念：子查询返回的结果是一行（多列的）常用操作符：=, <>, IN, NOT IN案例：

-- 行子查询 
-- 查询与张无忌薪资和直属领导相同的员工信息  
select * from emp where (salary, managerid) = (
select salary, managerid from emp where name = '张无忌');

表子查询概念：子查询返回的结果是多行多列，这种子查询叫做表子查询常用操作符：IN案例：

-- 表子查询 
-- 1. 查询与路障可，宋远桥的职位和薪资相同的员工 
select * from emp where (job, salary) in (
select job, salary from emp where name in ('路障可', '宋远桥')
);

-- 2. 查询入职日期是“2006-01-01”之后的员工信息及其部门信息 
select a.*, b.* from dept a 
right join
(select * from emp where entrydate > '2006-01-01') b
on a.id = b.dept_id;

多表查询案例：

-- 多表查询案例 
create table salgrade (
  grade int,
    losal int,
    hisal int
) comment '薪资等级表';

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);

-- 1. 查询员工的姓名，年龄，职位，部门信息（隐式内连接） 
select e.name, e.age, e.job, d.name from emp e, dept d
where e.dept_id = d.id;

-- 2. 查询年龄小于30岁的员工的姓名，年龄，职位，部门信息（显式内连接） 
select e.name, e.age, e.job, d.name
from emp e join dept d
on e.dept_id = d.id
where e.age < 30;

-- 3. 查询拥有员工的部门的id，部门名称 
select distinct d.id, d.name from dept d
inner join emp e
on e.dept_id = d.id;

-- 4. 查询年龄大于40岁的员工，及其归属的部门名称；如果员工没有部门，也要展示出来 
select e.*, d.name from 
emp e left join dept d
on e.dept_id = d.id
where e.age > 40;

-- 5. 查询所有员工的工资等级 
select e.*, sg.grade, sg.losal, sg.hisal from
emp e left join salgrade sg
on e.salary >= sg.losal and e.salary <= sg.hisal;

select e.*, sg.grade from
emp e, salgrade sg
where e.salary between sg.losal and sg.hisal;

-- 6. 查询研发部所有员工的信息及工资等级 
select d.name, e.*, sg.grade from
emp e join salgrade sg join dept d
on e.salary between sg.losal and sg.hisal
and e.dept_id = d.id
where d.name = '研发部';

SELECT 
    d.name, e.*, sg.grade
FROM
    emp e,
    dept d,
    salgrade sg
WHERE
    e.salary BETWEEN sg.losal AND sg.hisal
        AND e.dept_id = d.id
        AND d.name = '研发部';
-- ctrl + B 

-- 7. 查询研发部的员工的平均工资 
select avg(e.salary) '研发部平均工资' from emp e, dept d
where e.dept_id = d.id
and d.name = '研发部';

-- 8. 查询工资比灭绝高的员工信息 
-- 自连接 
select e2.* from emp e1, emp e2
where e1.name = '灭绝'
and e2.salary > e1.salary;

-- 子查询
select * from emp where salary > (
select salary from emp where name = '灭绝'
);

-- 9. 查询比平均薪资高的员工信息 
select * from emp where salary > (
select avg(salary) from emp
);

-- 10. 查询低于本部门平均薪资的员工 
select e.*, avgsal.sal from emp e join (
select dept_id, avg(salary) sal from emp group by dept_id) avgsal
on e.dept_id = avgsal.dept_id
where e.salary < avgsal.sal;

-- 可以用子查询，目测效率更低因为对于每一行子查询都需要重新计算一下本部门平均薪资 
-- 第一步：计算本部门平均薪资：select avg(salary) from emp where dept_id = 1;
select * from emp e2 where e2.salary <
(select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);

-- 11. 查询所有部门信息并统计部门的员工人数 
select count(e.dept_id), d.name from emp e right join dept d
on e.dept_id = d.id
group by d.id;

-- 子查询
-- 第一步，计算每个部门的人数 
select count(*) from emp e where e.dept_id = 1;

-- 第二步，得出结果 
select d.id, d.name, (select count(*) from emp e
where e.dept_id = d.id) '人数' from dept d;

-- 12. 查询所有学生的选课情况，展示出学生名称，学号，课程名称 
select s.name '姓名', s.no '学号', c.name '课程名称' from
student s, course c , student_course sc
where s.id = sc.studentid
and sc. courseid = c.id;

select * from student_course;
select * from student;
select * from course;

多表查询-总结1. 多表关系多表之间的关系分为三类：    一对多：在多的一方设置外键，关联一的一方的主键    多对多：建立中间表，中间表包含两个外键，关联两张表的主键    一对一：用于表结构拆分，在其中任何一方设置外键（UNIQUE)，关联另一方的主键

（一对一注：xx_order与xx_order_det就是这样的关系）

2. 多表查询内连接    隐式：

select * from 表A, 表B where 条件....;

    显式：

select * from 表A inner join 表B on 条件.....;

外连接    左外：

select ... from 表A left join 表B on 条件...;

    右外：

select ... from 表A right join 表B on 条件...;

自连接：

select * from 表A 别名1, 表B 别名2 where 条件...;

子查询：标量子查询，列子查询，行子查询，表子查询。
事务事务简介事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。注意：默认MySQL的事务是自动提交的，即当执行一条DML语句，MySQL会隐式的提交事务。
事务操作案例：

-- 事务操作 
-- 数据准备 
create table account (
  id int primary key auto_increment comment '主键ID',
    name varchar(10) comment '姓名',
    money int comment '余额'
) comment '账户表';

insert into account
values (null, '张三', 2000), (null, '李四', 2000);

-- 恢复数据 
update account set money = 2000;

-- 转账操作 
-- 1. 查询张三的余额 
select * from account where name = '张三';

-- 2. 将张三的账户余额减去1000 
update account set money = money - 1000 where name = '张三';

-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';

select * from account;

事务操作方式一：查看/设置事务的提交方式

select @@autocommit;
set @@autocommit = 0; -- 将提交方式改为手动

提交事务

commit;

回滚事务

rollback;

案例：

set @@autocommit = 0;
select @@autocommit;

-- 恢复数据 
update account set money = 2000;

-- 转账操作 
-- 1. 查询张三的余额 
select * from account where name = '张三';

-- 2. 将张三的账户余额减去1000 
update account set money = money - 1000 where name = '张三';

-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';
commit;
rollback;

select * from account;

事务操作方式二：开启事务

start transaction 或者 begin

提交事务

commit

回滚事务

rollback

案例：

-- 方式2
select @@autocommit;
set @@autocommit = 1;

begin;  -- 事务开始 

-- 1. 查询张三的余额 
select * from account where name = '张三';

-- 2. 将张三的账户余额减去1000 
update account set money = money - 1000 where name = '张三';

-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';

commit; -- 提交事务

rollback; -- 回滚事务

commit 之前：

commit 之后：

出错了就rollback
事务四大特性（ACID）原子性 A - atomicity 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。一致性 C - consistency 事务完成时，必须使所有数据都保持一致隔离性 I - isolation 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境运行持久性 D - durability 事务一旦提交或回滚，它对数据库中数据的修改就是永久的
并发事务问题

问题	描述
脏读	一个事务读取到另一个事务未提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读	一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时，发现这行数据已经存在了，好像出现了”幻影“。

事务隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	allow	allow	allow
Read commiited (Oracle 默认）	no	allow	allow
Repeatable Read (MySQL 默认）	no	no	allow
Serializable	no	no	no

查看事务隔离级别：

select @@transaction_isolation;

设置事务隔离级别：

set [session|global] transaction isolation level
{read uncommitted | read committed | repeatable read | serializable}

案例：

-- 设置事务隔离级别案例 
-- 演示脏读 
set session transaction isolation level read uncommitted;
begin;
select * from account;  -- id = 1, money = 2000;
-- 新开个session，执行
# start transaction
# update account set money = 2001 where id = 1;
select * from account;  -- 发现数据更新了 money = 2001
commit;
-- 演示不可重复读 
-- 在一个事务里修改隔离级别是不会起作用的 
set session transaction isolation level read committed;
begin;
select * from account;  -- 发现数据是没有更新的 money = 2000;
-- 新开的session里执行
# commit
select * from account;  -- 发现数据更新了 money = 2001;
commit;
-- 演示可重复读 
set session transaction isolation level repeatable read;
begin;
select * from account;  -- id = 1, money = 2001
-- 新开的session里执行 
# being
# update account set money = 2005 where id = 1;
# commit;
select * from account;  -- 发现数据没有更新 money = 2001
commit;
select * from account; -- 这时候在上一个事务之外，读到了 money = 2005
-- 演示幻读 
begin;
select * from account;  -- 这时候只读到两条数据 
-- 在新session里执行 
# begin;
# insert into account values (3, '王五', 2000);
# commit;
insert into account values (3, '王五', 2000);  
-- 执行失败，原因是新session读已提交 
select * from account;  -- 依然读不到 id为3的数据，称之为幻读 
commit;
-- 演示串行化 
delete from account where id = 3;
set session transaction isolation level serializable;
begin;
select * from account;  -- 这时候只读到两条数据 
-- 在新开session里执行 
# begin;
# insert into account values (3, '王五', 2000);
# 发现新session卡住了，不能动弹 
commit;
# 发现新session里的insert执行完成了。

注意：事务隔离级别越高，数据越安全，但是效率也越低。
总结：1. 事务简介事务是一组操作的集合，一荣俱荣，一损俱损2. 事务操作

start transaction; 或者 begin; -- 开启事务
commit/rollback; -- 提交/回滚

事务3. 事务四大特性原子性A、一致性C、隔离性I、持久性D4. 并发事务问题脏读、不可重复读、幻读5. 事务隔离级别read uncommitted, read committed, repeatable read, serializable
基础篇总结MySQL概述 - SQL -函数 - 约束 - 多表查询 - 事务

转载自本人公众号笔记：MySQL数据库 - 基础篇，归类为原创。

文章目录

1. 概述

原文链接: https://xqtony.github.io/2023/05/23/MySQL_DB_basic/

版权声明: 转载请注明出处.