数据库—MySQL02

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 表示自动增长(字段类型必须是整数类型)

      添加自增策略方式一（创建表的时候）：

create table st4(
id int primary key auto_increment,
name varchar(20),
age int
); 

       添加自增策略方式二（修改表的方式）:

create