MySQL的事务特性与约束【精通MySQL系列三】

最新推荐文章于 2024-05-29 11:38:21 发布

Disen-构架师

最新推荐文章于 2024-05-29 11:38:21 发布

阅读量337

点赞数 5

分类专栏：数据存储文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/ahhqdyh/article/details/105138096

版权

数据存储专栏收录该内容

8 篇文章 1 订阅

订阅专栏

一、银行案例练习

-- backstore 库名
create database backstore default character set utf8;

-- 打开库
use backstore;

-- 创建back 银行信息表
create table back(
   id integer primary key auto_increment,
   name varchar(50) unique,
   address varchar(200) not null
) default charset=utf8;

-- 增加10条记录
insert into back(name, address) values
('中国银行', ''),
('中国工商银行', ''),
('中国建设银行', ''),
('招商银行', ''),
('上海浦发银行', ''),
('兴业银行',''),
('北京银行',''),
('西安银行', ''),
('民生银行', ''),
('中国农业银行', '');

-- user 用户表
create table user(
   id integer primary key auto_increment,
   number varchar(18) unique comment '身份证号',
   name varchar(20) not null,
   phone varchar(11) comment '手机号'
) default charset=utf8;

insert into user(number, name, phone) values
('110299199301054568','罗鹏','17866542319'),
('180279199402184128','赵旭','15862542719'),
('231829199101103319','高超','19066542386'),
('120299199308254538','赵飞龙','19866543319'),
('119299199311054578','李成','18866542619'),
('224599199212121529','李晨西','17761543188');

-- card 银行卡
create table card(
   id integer primary key auto_increment,
   number varchar(20) unique comment '银行卡号',
   money  float default 0 comment '余额',
   passwd varchar(100) comment '支付密码',
   user_id integer comment '用户ID',
   back_id integer comment '银行ID'
) default charset=utf8;

insert into card(number, passwd, user_id, back_id) 
values
('6627 7818 1999 1087',password('000000'),1,1),
('6127 7918 2000 9011',password('000000'),1,2),
('9927 1118 0000 0186',password('000000'),2,3),
('1666 2111 8785 1123',password('000000'),3,1),
('9231 7214 3737 0124',password('000000'),4,5),
('8765 2134 4455 6719',password('000000'),5,2),
('2323 1900 2018 2295',password('000000'),5,4);

-- trade 交易记录
create table trade(
    id integer primary key auto_increment,
    card_id integer not null comment '银行卡ID',
    to_card_id integer comment '对方银行ID,用于转账',
    trade_time timestamp comment '交易时间',
    trade_money float comment '交易金额',
    trade_type int comment '0 查询 1 存款 2 取款 3 转账',
    note text comment '备注说明'
) default charset=utf8;

-- 查询交易记录
insert into trade(card_id, trade_type, trade_time) values
(1, 0, '2019-01-12'),
(2,0, '2019-01-15'),
(3, 0, '2019-03-12'),
(5, 0, '2019-03-12'),
(6, 0, '2019-03-12'),
(1, 0, '2019-03-12'),
(1, 0, '2019-04-12'),
(1, 0, '2019-05-12'),
(3, 0, '2019-06-18'),
(3, 0, '2019-07-17');

-- 存款交易记录
insert into trade(card_id, trade_type, trade_time, trade_money, note) values
(1, 1, '2019-01-11', 5000, '工资'),
(1, 1, '2019-01-12', 500, '生活费'),
(1, 1, '2019-01-12', 2500, '报销经费'),
(2, 1, '2019-01-12', 8000, '工资'),
(3, 1, '2019-01-11', 7000, '工资'),
(4, 1, '2019-01-13', 12000, '工资'),
(5, 1, '2019-01-12', 15000, '工资'),
(6, 1, '2019-01-14', 40000, '工资');

-- 同步更新银行卡的余额
update card set money=8000 where id=1;
update card set money=8000 where id=2;
update card set money=7000 where id=3;
update card set money=12000 where id=4;
update card set money=15000 where id=5;
update card set money=40000 where id=6;

查看MySQL系统的字符集

mysql>  show variables like '%character%';

二、事务

2.1 DTL（Database Transaction Language）数据库事务语言

begin 开启事务
- 在事务开启之后，所做的DML（Insert/Update/delete- 数据库操纵语言）操作都可以取消。
rollback 回滚事务
- 在事务开启之后使用
- 在提交事务之前，可以取消事务开启后的所有DML操作
commit 提交事务
- 在事务开启之后使用
- 当事务开启后，可以提交所有的DML操作
- 提交事务后，不能再回滚
- 如果事务开启后，执行DDL(create/drop/alter-数据库定义语言)操作时会自动提交事务

事务，指每个客户端连接操作相同数据时，锁定这条记录不让别的客户端连接去修改（操作）它，即在同一个时间只能由一个客户端操作这条数据。类似于Python中的多线程操作同一数据（list/dict）时加锁操作（Lock）。

在MySQL支持事务的数据库引擎是InnoDB。

2. 2 事务的隔离级别

事务的隔离级别指两个事务之间对修改的同一数据设置的间隔情况。

事务有四个特征(ACID):

原子性（Atomicity): 事务中所有的操作要么全部成功，要么全部失败。
一致性（Consistency）：事务开启和结束期间，不影响数据库中原有的数据。
隔离性(Isolation)：事务和事务之间是间隔的，互不影响。

事务的隔离性有四种级别
- 读未提交(READ UNCOMMITTED)
  
  即事务对数据库数据所做的修改，在事务未提交之前，可以被其他事务看到。
  
  A事务对数据库数据做的修改，在A事务未提交时，事务B可读到A事务对数据所做的修改。
  
  容易产生的问题：脏读、不可重复读、幻读
```
-- 设置当前会话的事务隔离级别
set session transaction isolation level read uncommitted;

-- 查看当前会话的事务隔离级别
select @@tx_isolation;
```
  在A客户端中打开事务，删除或修改一条记录，在B客户中直接查询可以查看A修改未提交的结果。
```
-- A 客户端
mysql> begin;
msyql> delete from C;
```
```
-- B 客户端
msyql> select * from C;  -- 数据就没有
```
- 读已提交(READ COMMITTED)
  
  两个客户端都开启事务，某一个客户端修改数据并提交事务，在另一个客户端中会查询到被修改的数据。
  
  会产生的问题：不可重复读、幻读
- 可重复读（repeatable read）
  
  两个客户端都开启事务，某一个客户端修改数据并提交事务，在另一个客户端多次查询的数据与开启事务之前的数据是一致的，但在修改数据之后，数据与另一个事务提交之后的数据保持同步。
  
  会产生的问题：幻读
- 串行化（Serializable）
  
  当某一个客户端开启事务后，并修改数据时，其它的客户端事务则无法修改(包含查看)某一表的任何数据。即对表进行加锁, 又称之为表锁。
  
  不会产生的问题：脏读、不可重复读、幻读
持久性(Durability)

事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失.

mysql的数据默认存储在 /var/lib/mysql目录中。

2.3 扩展备份数据库

切换root用户
```
$ su root
password:   
root@xx # 
```
进入mysql的数据文件目录
```
# cd /var/lib/mysql
```

备份或还原 back2 数据库

备份

# tar cvf back2.tar back2

还原

# tar xvf back2.tar

使用mysqldump命令备份与还原

将back2数据库备份成sql脚本

$ mysqldump -uroot -proot back2 > ~/back2.sql

还原数据库

mysql> create database back2;
mysql> use back2;
mysql> source ~/back2.sql

三、外键约束及管理

3.1 查看某一库下所有约束

-- 查看back数据库下所有表的所有约束名和约束类型
-- 目前约束类型： primary key, unique
select table_name, constraint_name,constraint_type
from information_schema.table_constraints
where table_schema='back';

如果只查看某一表的约束，还可以通过显示创建表的SQL查看。

show create table user;  -- 查看user表创建的sql语句
 
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` varchar(18) DEFAULT NULL COMMENT '身份证号',
  `name` varchar(20) NOT NULL,
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `number` (`number`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

AUTO_INCREMENT=7 表示下一次插入数据时，自增的字段为7。

3.2 增加约束

创建表时增加约束

约束一般是跟在字段类型的后面，如 field_name field_type[(length)] [约束]

也可以将约束放在最后一列的下方，最后一列需要逗号分隔，如下：

create table user_photo(
   photo_id varchar(32) comment '图片的编号，32位UUID值',
   user_id integer comment '用户的ID',
   url varchar(200) comment '头像的网址',
   primary key (photo_id)
);

photo_id值可以使用uuid()函数返回的字符串，但必须经过replace()将uuid()字符串’-‘替换成空字符串’ '，如：

insert into user_photo
values
(replace(uuid(), '-', ''), 1, 'http://www.aliyun.com/u1/1.png'),
(replace(uuid(), '-', ''), 1, 'http://www.aliyun.com/u1/2.png'),
(replace(uuid(), '-', ''), 1, 'http://www.aliyun.com/u1/3.png');

修改表的方式增加约束

语法
```
alter table 表名
add constraint [约束名] constraint_type (字段名);
```
注意：
- 如果当前表中的数据违反新增加的约束，则约束不会添加成功。
- 如果增加的约束类型是primary key ，约束名则无效。

增加外键约束

create table 表名(
    ....,
    外键字段 ..., 
    constraint [约束名] foreign key (外键字段) references 主表名(主表中的主键字段)
);

外键字段表示取值范围来源主表中的主键值。

alter table 表名
add constraint [约束名] foreign key (外键字段) references 主表名(主表中的主键字段);

练习：给user_photo表的user_id字段增加外键约束，user_id字段取值范围必须是user表的id字段值的范围。

alter table user_photo
add constraint user_id_fk foreign key (user_id) references user(id);

外键约束的级联操作

级联删除

on delete cascade
级联设置为NULL

on delete set null

alter table user_photo
add constraint user_id_fk foreign key (user_id) references user(id)
on delete cascade;

3.3 删除约束

alter table 表名
drop 约束类型 [约束名];

练习：将user_photo表的user_id_fk的外键约束删除。

alter table user_photo
drop foreign key user_id_fk;

练习题

将back库card、trade表，增加外键约束，并写出相关的测试sql语句

要求：

card表的back_id的外键约束设置为级联删除

card表的user_id的外键约束设置为级联置空
查询back库所有表的所有约束名称、约束类型，并将查询结果保存在table_cons表中。
总结事务的ACID及ISOLATION的四个隔离级别
总结SQL、DML、 DCL、DTL、 DDL、ACID等关键字的全称

四、连表查询

多表连接查询，一个完整的数据集是来源不同的表。表和表之间连接查询时，必须提供连接条件，假如有n张表连接查询，则需要提供n-1个连接条件。如果缺少连接条件，则会出现迪卡尔乘积，即m*n 的结果。

select id,back_id,number from user;
+----+---------+---------------------+
| id | back_id | number              |
+----+---------+---------------------+
|  1 |       1 | 6627 7818 1999 1087 |
|  2 |       2 | 6127 7918 2000 9011 |
|  3 |       3 | 9927 1118 0000 0186 |
|  4 |       1 | 1666 2111 8785 1123 |
|  5 |       5 | 9231 7214 3737 0124 |
|  6 |       2 | 8765 2134 4455 6719 |
|  7 |       4 | 2323 1900 2018 2295 |
+----+---------+---------------------+

mysql> select * from back;
+----+--------------------+---------+
| id | name               | address |
+----+--------------------+---------+
|  1 | 中国银行           |         |
|  2 | 中国工商银行       |         |
|  3 | 中国建设银行       |         |
|  4 | 招商银行           |         |
|  5 | 上海浦发银行       |         |
|  6 | 兴业银行           |         |
|  7 | 北京银行           |         |
|  8 | 西安银行           |         |
|  9 | 民生银行           |         |
| 10 | 中国农业银行       |         |
+----+--------------------+---------+

-- 将back表和card表进行连接查询,如果没有连接条件，则结果的行数为 7*10 = 70条
select back.id as back_id, back.name,card.number,card.id as card_id
from back,card
where back.id = card.back_id; -- 等值条件查询, 即是内连接

-- 以内连接方式实现等值条件查询 join on
select c.id as card_id, 
       number,
       b.id as back_id,
       name
from back b
join card c on (b.id = c.back_id);

表是可以设置表别名， select中的列名如果在连表查询中存在重复的列，则使用<表名或别名.列名 as 别名>。

Disen-构架师

关注

5
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
MySQL的事务特性与约束【精通MySQL系列三】

一、银行案例练习-- backstore 库名create database backstore default character set utf8;-- 打开库use backstore;-- 创建back 银行信息表create table back( id integer primary key auto_increment, name varchar(50) ...
复制链接

扫一扫