数据库_MySQL: mysql外键和事务

更过MySQL使用操作，点击查看专栏: MySQL数据库_命令详解

三.外键(foreigkey)

概念: 从表中的公共字段称为外键
作用: 外键约束用来保证数据的完整性

1.外键约束

外键特性:

主表没有的数据从表页不能插入

从表有的数据主表不能删除（先删从表，再删主表）

2.创建外键

• 创建(建表时就加上)

语法: foreig key（外键） references 主表（公共字段）

create table users (
id int primary key auto_increment,
name varchar(20) not null
)engine=innodb charset=utf8;

create table orders(
id int primary key auto_increment,
users_id int,
foreigkey (users_id) references users(id)
)engine=innodb charset=utf8;

注意: 存储引擎必须是innodb（公共字段名称可以不一样，但是数据类型必须一样）

• 创建(建表后添加)

语法：

alter database 数据名 charset=编码
alter view 视图名 as 新SQL语句
alter table 从表名 add foreigkey（外键） references 主表（公共字段）

create table users3 (
id int primary key auto_increment,
name varchar(20) not null
)engine=innodb charset=utf8;

create table orders3(
id int primary key auto_increment,
users_id int
)engine=innodb charset=utf8;

show create table orders3 \G
alter table orders3 add foreigkey (users_id) references users3(id);
show create table orders3 \G

mysql> show create table orders3 \G
*************************** 1. row ***************************
       Table: orders3
Create Table: CREATE TABLE `orders3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `users_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `users_id` (`users_id`),
  CONSTRAINT `orders3_ibfk_1` FOREIGKEY (`users_id`) REFERENCES `users3` (`id`) #约束名称
) ENGINE=InnoDB DEFAULT CHARSET=utf8

约束名称: orders3_ibfk_1

3.删除外键

语法：alter table 从表名 drop foreigkey 外键名（通过查看创建SQL语句）

mysql> alter table orders3 drop foreigkey orders3_ibfk_1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table orders3\G
*************************** 1. row ***************************
       Table: orders3
Create Table: CREATE TABLE `orders3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `users_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `users_id` (`users_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row iset (0.00 sec)

四.事务(transaction)

事务用于保护多个SQL语句的执行，只要有一个失败则全部失败，反之都成功。

1.语法

开启事务：start transactio或 begin

提交事务：commit

回滚事务：rollback

注意: 存储引擎必须是innodb

create table banks (
id int unsigned primary key auto_increment comment '编号',
name varchar(45) not null comment '姓名',
money decimal(10,2) not null default 0 comment '资金'
)engine=innodb charset=utf8;

insert into banks 
values
(null, '他爸', 100),
(null, '他妈', 100);

开启事物，验证提交事物

这里需要开2个mysql服务

• 他爸给他妈88块

start transaction;
update banks set money = money - 88 where name = '他妈';
update banks set money = money + 88 where name = '他爸';
#这时在第二个mysql服务中可以看到数字还是原来的100
#之后有两种选择,rollback或者commit. 
#rollback;之后第一个mysql中的数据会回复到原来的各100
#commit;之后第二个mysql中的数据会变为188和12.

rollback; #回滚
commit; #提交

2.事务的特性（ACID）

原子性（Atomicity）：整个事务中的所有操作要么全部提交成功，要么全部失败回滚

隔离性（Isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的

一致性（Consistency）：数据库数据从一个一致性的状态转换到另一个一致性的状态。

永久性（Durability）：提交事务后对数据的修改是永久性的