1.事务
2.数据库约束
3.表关系
4.三范式
5.多表查询
6.数据库备份
一、事务
1.事务的应用场景说明
在实际的业务开发中,有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行,要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败,就进行事务的回滚,所有的SQL语句全部执行失败。
例如: 张三给李四转账,张三账号减钱,李四账号加钱
-- 创建数据表
create table account(
id int,
name varchar(10),
balance double
);
-- 添加数据
insert into account (id,name,balance) values (1,'张三',1000),(2,'李四',1000);
模拟张三给李四转500元钱,一个转账的业务操作最少要执行下面的2条语句:
张三账号-500;
李四账号+500;
-- 张三账号-500
update account set balance=balance-500 where id=1;
-- 李四账号+500
update account set balance=balance+500 where id=2;
假设当张三账号上-500元之后,服务器崩溃了。李四的账号并没有+500元,数据就出现问题了。我们需要保证当其中一条SQL语句出现问题时,整个转账就算失败。只有两条SQL都成功了转账才算成功。这个时候就需要用到事务
2.操作事务
MySQL中可以有两种方式进行事务的操作:1.手动提交事务,2.自动提交事务
(1)手动提交事务
事务有关的SQL语句:
SQL语句 |
描述 |
start transaction; |
开启事务 |
commit; |
提交事务 |
rollback; |
回滚事务 |
手动提交事务使用步骤: 第1种情况:开启事务 -> 执行SQL语句 -> 成功 -> 提交事务 第2种情况:开启事务 -> 执行SQL语句 -> 失败 -> 回滚事务
案例演示:模拟张三给李四转500元钱(成功) 目前数据库数据如下:
1、使用Navicat新建查询
2、执行以下SQL语句: 1.开启事务, 2.张三账号-500, 3.李四账号+500
-- 开启事务
start transaction;
-- 张三账号-500
update account set balance=balance-500 where id=1;
-- 李四账号+500
update account set balance=balance+500 where id=2;
3、使用Navicat查看数据库:发现数据并没有改变
4、执行commit提交任务:
--提交事务
commit;
5、使用Navicat查看数据库:发现数据改变
案例演示:模拟张三给李四转500元钱(失败) 目前数据库数据如下:
1、执行以下SQL语句:1.开启事务, 2.张三账号-500
-- 开启事务
start transaction;
-- 张三账号-500
update account set balance=balance-500 where id=1;
2、使用Navicat查看数据库:发现数据并没有改变
3、执行rollback回滚事务:
-- 执行回滚
rollback;
4、使用Navicat查看数据库:发现数据没有改变
总结:如果事务中SQL语句没有问题,commit提交事务,会对数据库的数据进行改变。如果事务中SQL语句有问题,rollback回滚到事务,会回退到事务开启事务时的状态。
案例演示:回滚到回滚点
1、执行以下SQL语句:1.开启事务, 2.张三账号-500,3.设置回滚点,4.李四账户+500,5.回滚到回滚点
-- 开启事务
start transaction;
-- 张三账户-500
update account set balance=balance-500 where id=1;
-- 设置回滚点
savepoint aaa;
-- 李四账户+500
update account set balance=balance+500 where id=2;
-- 回滚到回滚点
rollback to aaa;
3、使用Navicat查看数据库:发现数据并没有改变
4、执行commit提交任务:
-- 提交事务
commit;
5、使用Navicat查看数据库:发现张三的账户-500,李四的账户数据没有改变
(2)自动提交事务
MySQL的每一条DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,执行完毕自动提交事务,MySQL默认开始自动提交事务
3.事务原理
事务开启之后, 所有的操作都会临时保存到事务日志, 事务日志只有在得到commit命令才会同步到数据表中,其他任何情况都会清空事务日志(rollback,断开连接)
二、数据库约束
对表中的数据进行进一步的限制,保证数据的正确性、有效性和完整性。 约束种类:
- PRIMARY KEY: 主键
- UNIQUE: 唯一
- NOT NULL: 非空
- FOREIGN KEY: 外键
- 检查约束(用户自定义约束) MySQL不支持
1.主键
(1)主键的作用
用来唯一标识一条记录,每个表都应该有一个主键,并且每个表只能有一个主键。 有些记录的 name,age,score 字段的值都一样时,那么就没法区分这些数据,造成数据库的记录不唯一,这样就不方便管理数据。
哪个字段应该作为表的主键?
通常不用业务字段作为主键,单独给每张表设计一个id的字段,把id作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。
(2)创建主键
主键:PRIMARY KEY 主键的特点:
1、主键必须包含唯一的值
2、主键列不能包含NULL值
创建主键方式一(创建表的时候创建):
语法:字段名 字段类型 PRIMARY KEY
或 PRIMARY KEY(字段名)
create table st1(
id int primary key,-- id是主键
name varchar(20),
age int
);
create table st2(
id int,
name varchar(20),
age int,
primary key(id)-- id是主键
);
创建主键方式二(修改表的方式添加主键)
create table st3(
id int,
name varchar(20),
age int
);
alter table st3 add constraint st primary key(id);-- id是主键 st是起的别名,可以不写,如下句
-- alter table st3 add constraint primary key(id);-- id是主键
添加数据
insert into st1 (id,name) values (1,'唐伯虎');
insert into st1 (id,name) values (2,'周文斌');
insert into st1 (id,name) values (3,'祝枝山');
insert into st1 (id,name) values (4,'文征明');
插入重复的主键值
-- 主键是唯一的,不能重复
insert into st1 (id,name) values (1,'文征明2');
插入NULL的主键值
-- 主键是不能为空的
insert into st1 (id,name) values (null,'文征明3');
(3)删除主键
语法:ALTER TABLE 表名 DROP PRIMARY KEY;
-- 删除表st3的主键
alter table st3 drop primary key;
(4)主键自增(策略)
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
语法:AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
添加自增策略方式一