面试常问:事务的四大特性,隔离级别,以及数据库存储引擎以及锁算法
事务(重要)
1. 事务的基本介绍
1. 概念:
* 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
事务执行是一个整体,所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常,则所有的
SQL 语句都要回滚,整个业务执行失败。
2. 操作:
1. 开启事务: start transaction;
2. 回滚:rollback;
3. 提交:commit;
3. 例子:
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 张三给李四转账 500 元
-- 0. 开启事务
START TRANSACTION;
-- 1. 张三账户 -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2. 李四账户 +500
-- 出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
-- 发现执行没有问题,提交事务
COMMIT;
-- 发现出问题了,回滚事务
ROLLBACK;
4. MySQL数据库中事务默认自动提交
* 事务提交的两种方式:
* 自动提交:
* mysql就是自动提交的
* 一条DML(增删改)语句会自动提交一次事务。
* 手动提交:
* Oracle 数据库默认是手动提交事务
* 需要先开启事务,再提交
* 修改事务的默认提交方式:
* 查看事务的默认提交方式:SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
* 修改默认提交方式: set @@autocommit = 0;
2.事务的步骤:
1) 客户端连接数据库服务器,创建连接时创建此用户临时日志文件
2) 开启事务以后,所有的操作都会先写入到临时日志文件中
3) 所有的查询操作从表中查询,但会经过日志文件加工后才返回
4) 如果事务提交则将日志文件中的数据写到表中,否则清空日志文件。
3. 事务的四大特征:
1. 原子性:每个事务都是一个整体,不可再拆分,事是不可分割的最小操作单位,要么同时成功,要么同时失败。
2. 持久性:一旦事务执行成功,对数据库的修改是持久的。就算关机,也是保存下来的。当事务提交或回滚后,数据库会持久化的保存数据。
3. 隔离性:多个事务之间。相互独立。事务与事务之间不应该相互影响,执行时保持隔离的状态。
4. 一致性:事务操作前后,数据总量不变。事务在执行前数据库的状态与执行后数据库的状态保持一致。如:转账前2个人的
总金额是 2000,转账后 2 个人总金额也是 2000
4. 事务的隔离级别(了解)
* 概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
* 存在问题:
1. 脏读:一个事务,读取到另一个事务中没有提交的数据
2. 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
要求的是一个事务中多次读取时数据是一致的,这是事务 update 时引发的问题
3. 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
一个事务中两次读取的数据的数量不一致,要求在一个事务多次读取的数据的数量是一致的,这是 insert 或 delete 时引发的问题
* 隔离级别:
1. read uncommitted:读未提交
* 产生的问题:脏读、不可重复读、幻读
2. read committed:读已提交 (Oracle)
* 产生的问题:不可重复读、幻读
3. repeatable read:可重复读 (MySQL默认)
* 产生的问题:幻读
4. serializable:串行化
* 可以解决所有的问题
* 注意:
隔离级别从小到大安全性越来越高,但是效率越来越低
* 数据库查询事务的隔离级别:
* select @@tx_isolation;
* 数据库设置事务的隔离级别:
* set global transaction isolation level 级别字符串;
* 演示:
set global transaction isolation level read uncommitted;
start transaction;
-- 转账操作
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;
5.回滚点
概念:
在某些成功的操作完成之后,后续的操作有可能成功有可能失败,但是不管成功还是失败,前面操作都已经成
功,可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置,而不是返回所有操作,这个点称
之为回滚点。
语法:
设置回滚点:
savepoint 名字
回到回滚点:
rollback to 名字
具体操作:
1) 将数据还原到 1000
2) 开启事务
3) 让张三账号减 3 次钱,每次 10 块
4) 设置回滚点:savepoint three_times;
5) 让张三账号减 4 次钱,每次 10 块
6) 回到回滚点:rollback to three_times;
7) 分析执行过程
总结:
设置回滚点可以让我们在失败的时候回到回滚点,
而不是回到事务开启的时候。
数据库表的约束
概念或作用:
对表中的数据进行限定,保证数据的正确性、有效性和完整性。
一个表如果添加了约束,不正确的数据将无法插入到表中。
约束在创建表的时候添加比较合适。
约束分类:
1. 主键约束:primary key
2. 非空约束:not null
3. 唯一约束:unique
4. 外键约束:foreign key
1.主键约束:
关键字:
primary key
注意:
1. 含义:非空且唯一
2. 一张表只能有一个主键(单列或多列)
3. 主键就是表中记录的唯一标识
语法:
创建主键:
1.在创建表的时候给字段添加主键
字段名 字段类型 primary key
或
create table 表名(
字段1 数据类型,
字段2 数据类型,
...
primary key(设置为主键的字段名)
);
2.在已有表中添加主键
alter table 表名 add primary key(字段名);
3.设置主键自动增长(auto_increment,默认从1开始)
前提条件:主键列需要是数值类型
方式一:创建表之时
字段名 字段类型 primary key auto_increment
方式二:创建表之后
alter table 表名 modify 主键列名 数据类型 auto_increment;
4.修改主键自增后,默认初始值
创建表时指定初始值
create table 表名 (
字段名 数据类型 primary key auto_increment,
...
) auto_increment = 指定值;
创建表后修改初始值
alter table 表名 auto_increment=指定值;
删除主键:
alter table 表名 drop primary key;
操作:
mysql> create table tbtest01( --- 创建表时添加主键 方式1
-> id int primary key,
-> name varchar(11)
-> );
Query OK, 0 rows affected (0.23 sec)
mysql> create table tbtest01( --- 创建表时添加主键 方式2
-> id int,
-> name varchar(11),
-> primary key(id)
-> );
Query OK, 0 rows affected (0.24 sec)
mysql> alter table tbtest01 drop primary key; --- 删除主键
Query OK, 0 rows affected (0.88 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table tbtest01 add primary key(id); -- 创建表之后添加主键
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table tbtest01 modify id int auto_increment; --- 创建表之后设置主键自增
Query OK, 0 rows affected (0.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 验证主键自增是否设置成功
mysql> insert into tbtest01 values(null,"猪八戒"); --- 设置主键自增后,添加数据方式一
Query OK, 1 row affected (0.09 sec)
mysql> select * from tbtest01;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 猪八戒 |
+----+-----------+
1 row in set (0.00 sec)
mysql> insert into tbtest01(name) values("猪八戒"); --- 设置主键自增后,添加数据方式二
Query OK, 1 row affected (0.13 sec)
mysql> select * from tbtest01;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 猪八戒 |
| 2 | 猪八戒 |
+----+-----------+
2 rows in set (0.00 sec)
mysql> create table tbtest01( --- 创建表时设置主键自增时,指定自增初始值
-> id int primary key auto_increment,
-> name varchar(11)
-> )auto_increment=1001;
Query OK, 0 rows affected (0.29 sec)
mysql> insert into tbtest01 values(null,"Poison"); -- 验证是否设置初始值成功
Query OK, 1 row affected (0.42 sec)
mysql> select * from tbtest01;
+------+--------+
| id | name |
+------+--------+
| 1001 | Poison |
+------+--------+
1 row in set (0.00 sec)
mysql> alter table tbtest01 auto_increment=2001; -- 创建表后修改主键自增初始值
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into tbtest01(name) values("Poison");
Query OK, 1 row affected (0.10 sec)
mysql> select * from tbtest01;
+------+--------+
| id | name |
+------+--------+
| 2001 | Poison |
+------+--------+
1 row in set (0.00 sec)
2.唯一约束
概念:表中某一列不能出现重复的值
注意:
唯一约束可以有NULL值,但是只能有一条记录为null
语法:
添加唯一约束:
创建表时:
create table 表名 (
字段名 数据类型 unique,
...
);
创建表后:
alter table 表名 modify 字段名 数据类型 unique;
删除唯一约束:
alter table 表名 drop index 字段名(被设置了唯一的字段);
操作:
mysql> create table tbtest02( -- 创建表时添加字段唯一
-> id int,
-> name varchar(20) unique
-> );
Query OK, 0 rows affected (0.29 sec)
mysql> insert into tbtest02 values(1,"孙悟空");
Query OK, 1 row affected (0.09 sec)
mysql> insert into tbtest02 values(2,"孙悟空"); -- 验证唯一约束是否添加成功
ERROR 1062 (23000): Duplicate entry '孙悟空' for key 'name'
mysql> alter table tbtest02 drop index name; -- 删除唯一约束
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into tbtest02 values(1,"Poison"); -- 验证唯一约束是否删除
Query OK, 1 row affected (0.09 sec)
mysql> insert into tbtest02 values(2,"Poison");
Query OK, 1 row affected (0.09 sec)
mysql> create table tbtest02(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.60 sec)
mysql> alter table tbtest02 modify name varchar(20) unique; --- 建表之后,设置字段唯一
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.非空约束
概念:not null,某一列的值不能为null
语法:
建表时添加:
CREATE TABLE 表名(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
建表后添加:
alter table 表名 modify 字段名 数据类型 not null;
删除非空约束:
alter table 表名 modify 字段名 数据类型;
操作:
mysql> create table tb3( -- 建表时添加非空约束
-> id int,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.72 sec)
mysql> alter table tb3 modify id int not null; -- 建表后添加非空约束
Query OK, 0 rows affected (0.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.外键约束
概念或作用:
foreign key,让表与表产生关系,从而保证数据的正确性。
注意:
有外键关联的表,不能直接删除
要先删除副表,再删除主表,或取消副表的外键关联
语法:
建表时添加:
create table 表名(
字段定义列表
...
外键列
constraint 外键名称 foreign key(外键列名) references (关联表)主表名称(主键列);
);
建表后添加:
alter table 表名 add constraint 外键名称 foreign key(外键列名) references (关联表)主表(主键列);
删除外键:
alter table 表名 drop foreign key 外键名称;
添加级联操作:
alter table 表名 add constraint 外键名称
foreign key(外键列) references (关联表)主表(主键列)
[on update cascade(级联更新)|on delete cascade(级联删除)];
操作:
mysql> create table tb01(
-> id int,
-> name varchar(20) not null,
-> primary key(id)
-> );
Query OK, 0 rows affected (2.04 sec)
mysql> create table tb02( --- 创建表时,添加外键关联
-> id int,
-> sname varchar(20) not null,
-> t1_id int,
-> constraint t1_t2_fk foreign key(t1_id) references tb01(id)
-> );
Query OK, 0 rows affected (2.07 sec)
mysql> alter table tb02 drop foreign key t1_t2_fk; -- 删除外键
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table tb02 add constraint t1_t2_fk foreign key(t1_id) references tb01(id); -- 创建表之后添加外键
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 准备两张表
mysql> select * from tb01;
+---------+-----------+
| id | name |
+---------+-----------+
| 2019001 | 研发部 |
| 2019002 | 销售部 |
| 2019003 | 人事部 |
+---------+-----------+
3 rows in set (0.00 sec)
mysql> select * from tb02;
+----+--------------+---------+
| id | sname | t1_id |
+----+--------------+---------+
| 1 | 张无忌 | 2019001 |
| 2 | 努尔哈赤 | 2019001 |
| 3 | 赵敏 | 2019001 |
| 4 | 王二狗 | 2019003 |
| 5 | 张三丰 | 2019003 |
| 6 | 韦小宝 | 2019003 |
| 7 | 沐剑屏 | 2019003 |
+----+--------------+---------+
7 rows in set (0.00 sec)
--- 设置外键级联更新
mysql> alter table tb02 add constraint t1_t2_fk foreign key(t1_id) references tb01(id)
-> on update cascade;
Query OK, 7 rows affected (1.88 sec)
Records: 7 Duplicates: 0 Warnings: 0
-- 验证
mysql> update tb01 set id=1001 where id=2019001; --- 更新主表(父表)
Query OK, 1 row affected (1.92 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb01;
+---------+-----------+
| id | name |
+---------+-----------+
| 1001 | 研发部 |
| 2019002 | 销售部 |
| 2019003 | 人事部 |
+---------+-----------+
3 rows in set (0.00 sec)
mysql> select * from tb02; -- 子表(副表)随之更新,
+----+--------------+---------+
| id | sname | t1_id |
+----+--------------+---------+
| 1 | 张无忌 | 1001 |
| 2 | 努尔哈赤 | 1001 |
| 3 | 赵敏 | 1001 |
| 4 | 王二狗 | 2019003 |
| 5 | 张三丰 | 2019003 |
| 6 | 韦小宝 | 2019003 |
| 7 | 沐剑屏 | 2019003 |
+----+--------------+---------+
7 rows in set (0.00 sec)
-- 设置外键级联删除
mysql> alter table tb02 add constraint fk foreign key(t1_id) references tb01(id)
-> on delete cascade;
Query OK, 7 rows affected (0.65 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> delete from tb01 where id=1001; -- 删除主表信息
Query OK, 1 row affected (0.10 sec)
mysql> select * from tb01;
+---------+-----------+
| id | name |
+---------+-----------+
| 2019002 | 销售部 |
| 2019003 | 人事部 |
+---------+-----------+
2 rows in set (0.00 sec)
mysql> select * from tb02; -- 副表信息随之删除
+----+-----------+---------+
| id | sname | t1_id |
+----+-----------+---------+
| 4 | 王二狗 | 2019003 |
| 5 | 张三丰 | 2019003 |
| 6 | 韦小宝 | 2019003 |
| 7 | 沐剑屏 | 2019003 |
+----+-----------+---------+
4 rows in set (0.00 sec)
insert into tb02 values(1,"张无忌",1001);
insert into tb02 values(2,"努尔哈赤",1001);
insert into tb02 values(3,"赵敏",1001);
DCL管理用户权限
* SQL分类:
1. DDL:操作数据库和表
2. DML:增删改表中数据
3. DQL:查询表中数据
4. DCL:管理用户,授权
* DBA:数据库管理员
* DCL:管理用户,授权
1. 管理用户
1. 添加用户:
* 语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
操作:
mysql> create user 'rose'@'%' identified by '******';
Query OK, 0 rows affected (0.12 sec)
mysql> select Host,User from user;
+-----------+-----------+
| Host | User |
+-----------+-----------+
| % | rose |
| localhost | Poison |
| localhost | mysql.sys |
| localhost | root |
+-----------+-----------+
4 rows in set (0.00 sec)
2. 删除用户:
* 语法:DROP USER '用户名'@'主机名';
操作:
mysql> drop user 'rose'@'%';
Query OK, 0 rows affected (0.00 sec)
3. 修改用户密码:
注意:
mysql数据库的用户表中默认密码字段为authentication_string
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
操作:
mysql> update user set authentication_string=password('065864') where User='Poison';
Query OK, 0 rows affected, 1 warning (1.90 sec)
Rows matched: 1 Changed: 0 Warnings: 1
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
操作:
mysql> set password for 'Poison'@'localhost'=password('065864');
Query OK, 0 rows affected, 1 warning (0.00 sec)
* mysql中忘记了root用户的密码?
1. cmd -- > net stop mysql 停止mysql服务
* 需要管理员运行该cmd
2. 使用无验证方式启动mysql服务: mysqld --skip-grant-tables
3. 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功
4. use mysql;
5. update user set password = password('你的新密码') where user = 'root';
6. 关闭两个窗口
7. 打开任务管理器,手动结束mysqld.exe 的进程
8. 启动mysql服务
9. 使用新密码登录。
修改用户表密码字段:
alter table user change 原字段名 新字段名 数据类型;
alter table user change authentication_string password text;
4. 查询用户:
-- 1. 切换到mysql数据库
USE myql;
-- 2. 查询user表
SELECT * FROM USER;
* 通配符: % 表示可以在任意主机使用用户登录数据库
2. 权限管理:
1. 查询权限:
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';
操作:
mysql> show grants for 'Poison'@'localhost';
+--------------------------------------------+
| Grants for Poison@localhost |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'Poison'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)
2. 授予权限:
-- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
操作:
mysql> grant select,delete,update on school.student3 to 'Poison'@'localhost';
Query OK, 0 rows affected (0.00 sec)
-- 给张三用户授予所有权限,在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
3. 撤销权限:
-- 撤销权限:
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
操作:
mysql> revoke update on school.student3 from 'Poison'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'Poison'@'localhost';
+---------------------------------------------------------------------+
| Grants for Poison@localhost |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'Poison'@'localhost' |
| GRANT SELECT, DELETE ON `school`.`student3` TO 'Poison'@'localhost' |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
数据库设计
1. 多表之间的关系
1. 分类:
1. 一对一(了解):
* 如:人和身份证
* 分析:一个人只有一个身份证,一个身份证只能对应一个人
2. 一对多(多对一):
* 如:部门和员工,班级和学生,客户和订单,分类和商品
* 分析:一个部门有多个员工,一个员工只能对应一个部门
3. 多对多:
* 如:学生和课程
* 分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
2. 实现关系:
1. 一对多(多对一):
* 如:部门和员工,班级和学生,客户和订单,分类和商品
* 实现方式:在多的一方建立外键,指向一的一方的主键。
2. 多对多:
* 如:学生和课程,老师和学生,用户和角色
* 实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键
3. 一对一(了解):
* 如:人和身份证
* 实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。
2. 数据库设计的范式
* 概念:设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
* 分类:
1. 第一范式(1NF):每一列都是不可分割的原子数据项
2. 第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)
* 几个概念:
1. 函数依赖:A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A
例如:学号-->姓名。 (学号,课程名称) --> 分数
2. 完全函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值。
例如:(学号,课程名称) --> 分数
3. 部分函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可。
例如:(学号,课程名称) -- > 姓名
4. 传递函数依赖:A-->B, B -- >C . 如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称 C 传递函数依赖于A
例如:学号-->系名,系名-->系主任
5. 码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
例如:该表中码为:(学号,课程名称)
* 主属性:码属性组中的所有属性
* 非主属性:除过码属性组的属性
3. 第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
数据库备份与还原
命令行:
* 语法:
* 备份: mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
如:
mysqldump -uroot -p123456 db1 > d://test.sql
* 还原:
1. 登录数据库
mysql -u用户名 -p[回车]
2. 创建数据库
create database 数据库名 [default character set 字符集];
3. 使用数据库
use 数据库名
4. 执行文件。
source 备份的文件路径
如:
source d://test.sql
图形化工具:
使用Data Export或Data Import进行数据库文件导入导出