面试常问：事务的四大特性，隔离级别，以及数据库存储引擎以及锁算法
事务(重要) 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进行数据库文件导入导出
