Mysql数据库学习(基础篇)

1.通用语法及分类

  • SQL通用语法

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

    • DDL(Data Defination Language):数据定义语言,用来定义数据库对象(数据库,表,字段)
    • DML(Data Manipulation Language):数据操作语言,用来对数据库中的数据进行增删改
    • DQL(Data Query Language):数据查询语言,用来查询数据库中表的记录。
    • DCL(Data Control Language):数据控制语言,用来创建数据库用户、控制数据库访问权限

1.1 DDL(数据定义语言)

数据库操作

  • 查询

查询所有数据库:

SHOW DATABASES;

查询当前数据库:

SELECT DATABASE();
  • 创建
CREATE DATABASE[IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
  • 删除
DROP DATABASE[IF EXISTS] 数据库名;
  • 使用
USE 数据库名;

表操作

  • 查询当前数据库所有表
SHOW TABLES;
  • 查询表结构
DESC 表名;
  • 查询指定表的建表语句
SHOW CREATE TABLE 表名;
  • 创建
CREATE TABLE 表名(
  字段名1 字段1类型 [COMMENT 字段1注释],
  字段名2 字段2类型 [COMMENT 字段2注释],
  字段名3 字段3类型 [COMMENT 字段3注释]
)[COMMENT 表注释];

例如:

create  table if not exists student(
    sid int,
    name varchar(20),
    gender varchar(20),
    age int,
    birth date, 
    address varchar(20),
    score double
);
  • 修改

    • 添加字段
    ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释][约束];

    例如给student表添加一个新的字段:dept,类型为varchar(20)

    ALTER TABLE student ADD 'dept' VARCHAR(20);
    • 修改数据类型

    修改数据类型

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

    修改字段名和字段类型

    ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
    • 删除字段
    ALTER TABLE 表名 DROP 字段名;
    • 修改表名
    ALTER TABLE 表名 RENAME TO 新表名;
  • 删除

    • 删除表
    DROP TABLE[IF EXISTS] 表名;
    • 删除指定表,并重新创建该表
    TRUNCATE TABLE 表名;

数值类型

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1byte (-128,127) (0,255) 小整数值
SMALLINT 2bytes (-32768,32767) (0,65535) 大整数值
MEDIUMINT 3bytes (-8388608,8388607) (0,16777215) 大整数值
INT或INTEGER 4bytes (-2147483648,2147483647) (0,4294967295) 大整数值
BIGINT 8bytes (-9223372036854775808,9223372036854775807) (0,18446744073709551615) 大整数值
FLOAT 4bytes (-3.402823466E+38,3.402823466351E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8bytes (-1.797 693 134 862 315 7 E+308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 依赖于M和D的值 依赖于M和D的值 小数值

字符串类型

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

日期类型

类型 大小(bytes) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值,时间戳

1.2 DML(数据操作语言)

用来对数据库中的数据记录进行增删改操作。

  • 添加数据(INSERT)
  • 修改数据(UPDATE)
  • 删除数据(DELETE)

添加数据

  1. 给指定字段添加数据

    INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...);
  2. 给全部字段添加数据

    INSERT INTO 表名 VALUES (值1,值2,...);
  3. 批量添加数据

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

字符串和日期型数据应该包含在引号中。

修改数据

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

删除数据

DELETE FROM 表名 [WHERE 条件];

1.3 DQL(数据查询语言)

查询关键字:SELECT

语法:

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

    1. 查询多个字段

      SELECT 字段1, 字段2, ... FROM 表名;
      SELECT * FROM 表名;
    2. 设置别名

      SELECT 字段1 [AS 别名1], 字段2[AS 别名2], ... FROM 表名;
    3. 去除重复记录

      SELECT DISTINCT 字段列表 FROM 表名;
  • 条件查询

    语法:

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

    条件:

    比较运算符 功能
    > 大于
    >= 大于等于
    < 小于
    <= 小于等于
    = 等于
    <> 或 != 不等于
    BETWEEN ... AND ... 在某个范围内(含最小、最大值)
    IN(...) 在in之后的列表中的值,多选一
    LIKE 占位符 模糊匹配(_匹配单个字符,%匹配任意个字符)
    IS NULL 是NULL
    逻辑运算符 功能
    AND 或 && 并且(多个条件同时成立)
    OR 或 || 或者(多个条件任意一个成立)
    NOT 或 ! 非,不是

    例子:

    -- 年龄等于30
    select * from employee where age = 30;
    -- 年龄小于30
    select * from employee where age < 30;
    -- 小于等于
    select * from employee where age <= 30;
    -- 没有身份证
    select * from employee where idcard is null or idcard = '';
    -- 有身份证
    select * from employee where idcard;
    select * from employee where idcard is not null;
    -- 不等于
    select * from employee where age != 30;
    -- 年龄在20到30之间
    select * from employee where age between 20 and 30;
    select * from employee where age >= 20 and age <= 30;
    -- 下面语句不报错,但查不到任何信息
    select * from employee where age between 30 and 20;
    -- 性别为女且年龄小于30
    select * from employee where age < 30 and gender = '女';
    -- 年龄等于25或30或35
    select * from employee where age = 25 or age = 30 or age = 35;
    select * from employee where age in (25, 30, 35);
    -- 姓名为两个字
    select * from employee where name like '__';
    -- 身份证最后为X
    select * from employee where idcard like '%X';
  • 聚合函数

    将一列数据作为一个整体,进行纵向计算。

    常见的聚合函数

    函数 功能
    count 统计数量
    max 最大值
    min 最小值
    avg 平均值
    sum 求和

    语法:

    SELECT 聚合函数(字段列表) FROM 表名;

    null值不参与聚合函数的运算

    例子:

    --1.统计员工数量
    select count(*) from emp;
    
    --2.统计该企业员工的平均年龄
    select avg(age) from emp;
    
    --3.统计该企业员工的最大年龄
    select max(age) from emp;
    
    --4.统计该企业员工的最小年龄
    select min(age) from emp;
    
    --5.统计西安地区员工的年龄之和
    select sum(age) from emp where workaddress='西安';
  • 分组查询

    语法:

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

    wherehaving的区别:

    1. 执行时机不同:where是在分组前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
    2. 判断条件不同:where不能对聚合函数进行判断,而having可以。

    例子:

    --1.根据性别分组,统计男性员工和女性员工的数量
    select gender, count(*) from emp group by gender;
    
    --2.根据性别分组,统计男性员工和女性员工的平均年龄
    select gender, 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。
    • 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
  • 排序查询

    语法:

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

    ASC 升序,DESC降序。

    例子:

    --1.根据年龄对公司员工进行升序排序
    select * from emp order by age asc;
    
    --2.根据入职时间对员工进行降序排序
    select * from emp order by entrydate desc;
    
    --3.根据年龄对员工进行升序排序,年龄相同,再按照入职时间进行降序排序
    select * from emp order by age asc, entrydate desc;
  • 分页查询

    语法:

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

    注意:

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

    例子:

    --1.查询第1页员工数据,每页展示10条记录
    select * from emp limit 0, 10;
    
    --2.查询第2页员工数据,每页展示10条记录
    select * from emp limit 10, 10;

DQL的执行顺序:FROM -> WHERE -> GROUP BY -> SELECT ->ORDER BY -> LIMIT.

1.4 DCL(数据控制语言)

  • 查询用户

    SELECT * FROM user;
  • 创建用户

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

    ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password or '新密码';
  • 删除用户

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

例子:

-- 创建用户test,只能在当前主机localhost访问
create user 'test'@'localhost' identified by '123456';
-- 创建用户test,能在任意主机访问
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密码
alter user 'test'@'localhost' identified with mysql_native_password by '1234';
-- 删除用户
drop user 'test'@'localhost';

权限控制

权限 说明
ALL, ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表
  • 查询权限

    SHOW GRANTS FOR '用户名'@'主机名';
  • 授予权限

    GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
  • 撤销权限

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

注意:

  • 多个权限用逗号分隔
  • 授权时,数据库名和表名可以用 * 进行通配,代表所有

2.函数

  • 字符串函数
  • 数值函数
  • 日期函数
  • 流程函数

2.1 字符串函数

函数 功能
CONCAT(s1,s2,...,sn) 字符串拼接
LOWER(str) 将字符串str全部转为小写
UPPER(str) 将字符串str全部转为大写
LPAD(str,n,pad) 左填充,用字符串pad对str左边进行填充,达到n个字符串长度
RPAD(str,n,pad) 右填充,用字符串pad对str右边进行填充,达到n个字符串长度
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str,start,len) 返回从字符串str从start位置起的len长度的字符串
REPLACE(column,source,replace) 替换字符串

例子:

-- 拼接
select concat('Hello', 'World');

--小写
select lower('Hello');

--大写
select upper('Hello');

--左填充
select lpad('01',5,'-'); # 结果:---01

--右填充
select rpad('01',5,'-'); # 结果:01---

--去除空格
select trim(' Hello World  '); # 结果:Hello World

--切片
select substring('Hello MySQL',1,5); # 结果:Hello

2.2 数值函数

函数 功能
CEIL(x) 向上取整
FLOOR(x) 向下取整
MOD(x,y) 返回x/y的模
RAND() 返回0-1内的随机数
ROUND(x,y) 求参数x四舍五入的值,保留y位小数

例子:

-- 向上取整
select ceil(1.1);

-- 向下取整
select floor(1.9);

-- 返回x/y的模
select mod(x,y);

-- 求随机数
select rand();

-- 四舍五入
select round(2.345, 2);

2.3 日期函数

函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定date的年份
MONTH(date) 获取指定date的月份
DAY(date) 获取指定date的日期
DATE_ADD(date, INTERVAL expr type) 返回一个日期/时间加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2) 返回起始时间date1和结束时间date2之间的天数
--DATE_ADD
select date_add(now(), INTERVAL 70 YEAR);

--DATEDIFF
select datediff('2021-10-01', '2021-12-01');

2.4 流程控制函数

函数 功能
IF(value, t, f) 如果value为true,则返回t,否则返回f
IFNULL(value1,value2) 如果value1不为空,返回value1,否则返回value2
CASE WHEN [val1] THEN [res1] ... ELSE [default] END 如果val1为true,返回res1 ... 否则返回default默认值
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END 如果expr的值等于val1,返回res1...否则返回default默认值
-- 需求:查询emp表的员工姓名和工作地址(北京/上海 --> 一线城市   其他 --> 二线城市)
select
    name,
    case when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end as '工作地址'
from emp;

3. 约束

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

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

分类:

约束 描述 关键字
非空约束 限制该字段的数据不能为null NOT NULL
唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE
主键约束 主键是一行数据的唯一标识,要求非空且唯一 PRIMARY KEY
默认约束 保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT
检查约束 保证字段值满足某一个条件 CHECK
外键约束 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 FOREIGN KEY

例子:

字段名 字段含义 字段类型 约束条件 约束关键字
id ID唯一标识 int 主键并且自动增长 PRIMARY KEY,AUTO_INCREMENT
name 姓名 varchar(10) 不为空,并且唯一 NOT NULL,UNIQUE
age 年龄 int 大于0,并且小于等于120 CHECK
status 状态 char(1) 如果没有指定该值,默认为1 DEFAULT
gender 性别 char(1)
create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age>0 && age<=120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表';

外键约束

  • 添加外键

    语法:

    -- 创建时添加外键
    CREATE TABLE 表名(
    字段名 数据类型,
    ...
    [CONTRAINT][外键名称] FORGEIN KEY (外键字段名) REFFERENCES 主表 (主表列名)
    )
    
    -- 创建后添加外键
    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFFERENCES 主表 (主表列名);

    例子:

    -- 创建部门表
    create table dept(
      id int auto_increment comment 'ID' primary key ,
      name varchar(50) not null comment '部门名称'
    )comment '部门表';
    
    -- 给员工表添加外键
    alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept (id);
  • 删除外键

    语法:

    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

    例子:

    alter table emp drop foreign key fk_emp_dept_id;
  • 删除/更新行为

    行为 说明
    NO ACTION 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与RESTRICT一致)
    RESTRICT 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新。(与NO ACTION一致)
    CASCADE 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则也删除/更新外键在子表中的记录。
    SET NULL 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许取null)
    SET DEFAULT 父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持)

    语法

    ALTER 表名 ADD CONTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCEDE;

4. 多表查询

4.1 多表关系

  • 一对多(多对一)
    • 案例:部门与员工之间的关系
    • 关系:一个部门对应多个员工,一个员工对应一个部门
    • 实现:在多的一方建立外键,指向一的一方的主键
  • 多对多
    • 案例:学生与课程之间的关系
    • 关系:一个学生可以选择多门课程,一门课程也可以供多个学生选择
    • 实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方的主键
  • 一对一
    • 用户与用户详情的关系
    • 关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升效率
    • 实现:在任意一方加入外键,关联另一方的主键,并设置外键为唯一的

4.2查询

合并查询(笛卡尔积)

select * from emp,dept;

消除无效笛卡尔积

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

4.3 内连接查询

查询两张表交集的部分

  • 隐式内连接:

    SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
  • 显式内连接

    SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

显式性能比隐式高

--查询每一个员工的姓名,及关联的部门(隐式内连接实现)
--表结构:emp, dept
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
--使用别名简化上面的sql语句
select e.name, d.name from emp e, dept d where e.dept_id = d.id;

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

4.3 外连接查询

  • 左外连接

    查询左表所有数据,以及两张表交际部分数据

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

    查询右表所有数据,以及两张表交际部分数据

    SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

例子:

-- 查询emp表的所有数据,和对应的部门信息(左外连接)
-- 表结构:emp,dept
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

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

4.4 自连接查询

语法:

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

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

例子:

-- 查询员工及其所属领导的名字
select a.name, b.name from employee a, employee b where a.managerid = b.id;
-- 没有领导的也查询出来
select a.name, b.name from employee a left join employee b on a.managerid = b.id;

4.5 联合查询-union,union all

把多次查询的结果合并起来,形成一个新的查询结果集。

语法:

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

例子:

-- 将薪资低于5000的员工和年龄大于50的员工全部查询出来
select * from emp where salary < 5000
union all
select * from emp where age > 50;
  • UNION ALL 会有重复结果,UNION 不会
  • 联合查询比使用or效率高,不会使索引失效

4.6 子查询

概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询

子查询的外部语句可以是INSERTUPDATEDELETESELECT中的任何一个。

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

  • 标量子查询(子查询的结果为单个值)
  • 列子查询(子查询的结果为一列)
  • 行子查询(子查询结果为一行)
  • 表子查询(子查询结果为多行)

根据子查询的位置,可分为:

  • WHERE之后
  • FROM之后
  • SELECT之后

4.6.1标量子查询

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

例子:

--1. 查询销售部的所有员工信息
--1.1 查询销售部的ID
select id from dept where name='销售部';
--1.2 根据销售部ID查询员工信息
select * from emp where dept_id = 4;
--1.3 合并
select * from emp where dept_id = (select id from dept where name = '销售部'); 

4.6.2 列子查询

子查询的返回结果是一列(可以是多行)。

常用操作符:IN, NOT IN, ANY, SOME, ALL

操作符 描述
IN 在指定的集合范围之内,多选一
NOT IN 不在指定的集合范围之内
ANY 子查询返回列表中,任意一个满足即可
SOME 与ANY等同,使用SOME的地方都可以使用ANY
ALL 子查询返回列表的所有值都必须满足

例子:

--1. 查询销售部和市场部的所有员工信息
select * from emp where dept_id in (select id from dept where name='销售部' or name = '市场部');

--2. 查询比财务部所有人工资都高的员工信息
select * from emp where salary > all(select salary from emp where dept_id = (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 = '研发部'));

4.6.3 行子查询

子查询的返回结果是一行(可以是多列)。

常用操作符:IN, NOT IN, =, <>

例子:

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

4.6.4 表子查询

子查询的返回结果是多行多列

常用操作符:IN

例子:

--查询与'鹿杖客'和'宋远桥'职位和薪资相同的员工信息
select * from emp where (job, salary) in (select job, salary from emp where name = '鹿杖客' or name = '宋远桥');

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

5. 事务

事务 是一组操作的集合,它是一个不可分割的工作单位,事务会被所有操作作为一个整体一起向系统提交或撤销操作请求,这些操作要么同时成功,要么同时失败

5.1 事务操作

-- 张三向李四转账1000
-- 1.查询张三账户余额
select * from account where name = '张三';
-- 2.将张三账户余额扣除1000
update account set money = money - 1000 where name = '张三';

模拟sql错误

--3. 将李四账户余额加1000
update account set money = money + 1000 where name = '李四';
# 上述语句会导致,张三余额扣除一千但是李四余额并没有增加一千

为了解决这个问题,需要将三个操作封装成一个事务。

操作方式一:

-- 查看事务的提交方式
select @@autocommit; # 查询提交方式,默认是1表示自动提交,我们需要将其修改成0
--设置事务提交方式,该设置仅对当前会话生效
set @@autocommit = 0;

-- 张三向李四转账1000
-- 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;

操作方式二:

--开启事务(也可以使用begin transaction)
start transaction;

-- 张三向李四转账1000
-- 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;

5.2 事务四大特性

  • 原子性(Atomicity):事务是不可分割的最小单元,要么全部成功,要么全部失败。
  • 一致性(Consistency):事务完成时,必须使所有的数据保持一致状态(例如不管转账是否成功张三和李四账户余额保持一致)。
  • 隔离性(Isolation):数据库提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行(事务的并发)。
  • 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。

5.3 并发事务问题

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

5.4 事务的隔离级别

隔离级别 脏读 不可重复读 幻读
Read uncommitted
Read committed ×
Repeatable Read(默认) × ×
Serializable × × ×
  • √表示在当前隔离级别下该问题会出现
  • Serializable 性能最低;Read uncommitted 性能最高,数据安全性最差
-- 查看事务的隔离级别
select @@transaction_isolation

-- 设置隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };
# session 表示当前会话,global表示全局会话

评论

  1. Piper1109
    3月前
    2024-2-22 22:03:35

    Modern Talking был немецким дуэтом, сформированным в 1984 году. Он стал одним из самых ярких представителей евродиско и популярен благодаря своему неповторимому звучанию. Лучшие песни включают “You’re My Heart, You’re My Soul”, “Brother Louie”, “Cheri, Cheri Lady” и “Geronimo’s Cadillac”. Их музыка оставила неизгладимый след в истории поп-музыки, захватывая слушателей своими заразительными мелодиями и запоминающимися текстами. Modern Talking продолжает быть популярным и в наши дни, оставаясь одним из символов эпохи диско. Музыка 2024 года слушать онлайн и скачать бесплатно mp3.

  2. Earl339
    3月前
    2024-2-12 13:27:28

    Мадонна, икона поп-музыки и культурного влияния, продолжает вдохновлять и поражать своей музыкой и стилем. Её карьера олицетворяет смелость, инновации и постоянное стремление к самовыражению. Среди её лучших песен можно выделить “Like a Prayer”, “Vogue”, “Material Girl”, “Into the Groove” и “Hung Up”. Эти треки не только доминировали на музыкальных чартах, но и оставили неизгладимый след в культурной и исторической панораме музыки. Мадонна не только певица, но и икона стиля, актриса и предприниматель, чье влияние простирается далеко за рамки музыкальной индустрии. Скачать mp3 музыку 2024 года и слушать онлайн бесплатно.

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇