一 数据库范式
1 基础理论
什么是数据库范式?
我们在设计一张表的时候,需要遵循的一些数据库设计的规范。
为什么需要使用范式呢?
为了减少数据库的当中数据冗余。(减少重复同一个表当中,出现数据重复的情况)
数据范式的分类
(1) 第一范式 1NF (2) 第二范式 2NF (3) 第三范式 3NF (4) 巴斯科德范式 BCNF (5) 第四范式 4NF (6) 第五范式 5NF 解释说明: 如果想要满足第二范式,必须先满足第一范式。 如果想要满足第三范式,必须先满足第二范式。
普通的表结构
几个专业词汇说明
1. 函数依赖: 通过当前列,可以找到确定其他列的数据值。 分为: 完全函数依赖和传递函数依赖。 完全函数依赖: 分数,完全需要通过(学号和课程名称)才能确定下来。 传递函数依赖: 系主任: 需要先通过学号,去确定系名,在通过系名确定系主任。 2. 主属性和非主属性: 主属性: 通过哪些列,可以完全的确定表当中的信息。(例如: 学号和课程名称) 主属性当中的列,也叫作码 非主属性: 除了码之外的,其他的列
2、 第一范式
效果图
说明
在第一范式当中,不存在合并项。以前的 系名和系主任存在合并项 系
3、 第二范式
效果图
说明
拆分表,将重复的数据,冗余的数据,拆分出来,形成新的多张表结构 解决问题: 解决了重复数据,冗余数据的问题。 发现问题: 增删数据,数据不合法
4、 第三范式
效果图
说明
拆分表,将不是特别紧密的关联关系,拆分成为多张表。(学号和系主任) 传递依赖 例如: 学号需要找到系主任,先找到系名,再去找到系主任。 对于第三范式,主要作用:消除了传递依赖。
二 数据库事务
1、 基础理论
什么是事务呢?
事务: 在一组多条SQL语句当中,对于数据库操作的时候,要么同时成功,要么同时失败。 举例: 银行转账过程当中。 张三给李四转账汇款 1000 元。 UPDATE account SET money=money-1000 WHERE name='张三'; UPDATE account SET money=money+1000 WHERE name='李四';
事务的三个操作SQL
(1) 开启事务 SQL语句的写法: START TRANSACTION; 语句操作的时机: 当我们需要将多个SQL语句被事务管理的时候,则开启事务 (2) 回滚事务 SQL语句的写法: ROLLBACK; 语句操作的时机: 当我们失败的时候,则执行回滚 (3) 提交事务 SQL语句的写法: COMMIT; 语句操作的时机: 当我们成功的时候,则执行提交
事务的四大特性(面试常问问题) ACID
1. 原子性:多个SQL语句操作的过程当中, 是统一不可以分割。(原子在化学当中是最小单位,不可以在分割了) 2. 一致性:多个SQL语句操作的过程当中, 数据总和不会改变。 3. 持久性:当事务执行了 COMMIT提交或者 ROLLBACK回滚 之后,数据将会永久性存储到数据库当中。 4. 隔离性:多个事务之间,应该是尽可能独立的部分,相互之间,互不影响。
2、 快速入门
代码准备
-- 1. 创建数据库,创建数据表 DROP DATABASE IF EXISTS mydb07; CREATE DATABASE IF NOT EXISTS mydb07; USE mydb07; DROP TABLE IF EXISTS account; CREATE TABLE IF NOT EXISTS account( uid INT, uname VARCHAR(20), money INT ); -- 2. 插入数据 INSERT INTO account VALUES (1,'张三',10000),(2,'李四',10000); -- 3. 查询数据 SELECT * FROM account; -- 4. 修改数据 UPDATE account SET money = 10000;
演示案例1:正常和异常
-- 1. 转账的操作(张三给李四转账1000元) UPDATE account SET money = money - 1000 WHERE uname = '张三'; 出错啦,嘻嘻嘻 UPDATE account SET money = money + 1000 WHERE uname = '李四'; -- 2. 查看数据表 SELECT * FROM account;
演示案例2:事务管理
-- 1. 转账的操作(张三给李四转账1000元) -- 设置MySQL的事务提交方式为手动提交,mySQL默认是自动提交 SET AUTOCOMMIT = 0; -- 设置提交方式,1自动提交,0手动提交 SELECT @@AUTOCOMMIT; -- 查看事务的提交方式 -- 开始转账之前,就需要开启事务 START TRANSACTION; UPDATE account SET money = money - 1000 WHERE uname = '张三'; 出错啦,嘻嘻嘻 -- 如果出现错误,则进行事务的回滚 ROLLBACK; UPDATE account SET money = money + 1000 WHERE uname = '李四'; -- 当转账结束之后, 则需要进行事务的提交 COMMIT; -- 2. 查看数据表 SELECT * FROM account;
3、 隔离级别
内容介绍(隔离级别越高、安全性越高,效率越低)
序号 | 隔离级别 | 中文名称 | 脏读 | 不可重复读 | 幻读 | 数据库默认 |
---|---|---|---|---|---|---|
1 | READ UNCOMMITTED | 读未提交 | YES | YES | YES | |
2 | READ COMMITTED | 读已提交 | NO | YES | YES | Oracle 默认 |
3 | REPEATABLE READ | 可重复读 | NO | NO | YES | MySQL默认 |
4 | SERIALIZABLE | 串行化 | NO | NO | NO |
专业词汇介绍
1. 脏读: 在事务A处理的过程当中,读取到了事务B的数据。 读取到,没还没进行事务提交的数据,导致数据不正确。多次查询的结果,可能不一致。 2. 不可重复读: 在事务A处理的过程当中,读取到了事务B的数据。 读取到,事务已经提交的数据,导致数据不正确。多次查询的结果,可能不一致。 3. 幻读: 系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级, 但是系统管理员B就在这个时候插入了一条具体分数的记录, 当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。
相关SQL语句介绍
-- 1. 查看事务的隔离级别
SELECT @@TX_ISOLATION;
-- 2. 修改事务的隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别的字符串;
-- 举例说明:(修改了隔离级别之后,需要重启 SQLyog才能生效)
-- 读未提交 READ UNCOMMITTED
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 读已提交 READ COMMITTED
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 可重复读 REPEATABLE READ
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 串行化 SERIALIZABLE
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
演示事务隔离级别
-- 1. 事务的三个操作(开启、提交、回滚)
START TRANSACTION;
COMMIT;
ROLLBACK;
-- 2. 查看数据表
SELECT * FROM account;
-- 3. 修改操作
UPDATE account SET money = money - 1000 WHERE uname = '张三';
出错啦,停电了,电脑爆炸了
UPDATE account SET money = money + 1000 WHERE uname = '李四';
-- 4. 回归到最初的状态
UPDATE account SET money = 10000;
SELECT * FROM account;
-- 5. 修改事务的提交方式,查看事务提交方式
SET AUTOCOMMIT = 0;
SELECT @@AUTOCOMMIT;
三 数据库视图
1、 基础理论
为什么存在视图?
我们前两天讲解多表查询的过程当中,说可以进行子查询。 子查询(一次查询的结果是下一次查询的条件)如果这样写SQL语句,语句显得比较长,比较多。 为了简化多表查询的操作,我们可以通过视图去完成。
数据准备
-- 创建表
DROP TABLE IF EXISTS country;
CREATE TABLE IF NOT EXISTS country(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO country VALUES (NULL,'中国'),(NULL,'美国'),(NULL,'岛国');
SELECT * FROM country;
-- 创建表
DROP TABLE IF EXISTS city;
CREATE TABLE IF NOT EXISTS city(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
cid INT
);
-- 建立外键
ALTER TABLE city ADD FOREIGN KEY (cid) REFERENCES country(id);
-- 添加数据
INSERT INTO city VALUES (NULL,'北京',1);
INSERT INTO city VALUES (NULL,'上海',1);
INSERT INTO city VALUES (NULL,'武汉',1);
INSERT INTO city VALUES (NULL,'华盛顿',2);
INSERT INTO city VALUES (NULL,'旧金山',2);
INSERT INTO city VALUES (NULL,'东京',3);
INSERT INTO city VALUES (NULL,'大阪',3);
SELECT * FROM city;
需求: 查询国家对应的城市信息
-- 内连接查询 SELECT t1.name '国家名称', t2.name '城市名称' FROM country t1, city t2 WHERE t1.id = t2.cid;
2、 创建视图
语法格式
-- 语法格式: CREATE VIEW 视图名称 (视图的列名1,视图的列名2,视图列名3) AS 以前的SQL语句;
举例说明
CREATE VIEW city_country (country_name,city_name) AS SELECT t1.name '国家名称', t2.name '城市名称' FROM country t1, city t2 WHERE t1.id = t2.cid;
3、修改视图
语法格式
-- 语法格式: (修改视图数据) UPDATE 视图名称 SET 视图的列名=值 WHERE 条件; -- 语法格式: (修改视图的结构) ALTER VIEW 视图名称 (列名1,列名2,列名3) AS 之前的查询SQL语句;
案例代码
-- 1. 修改视图的结构 ALTER VIEW city_country (country_name,city_name,city_id) AS SELECT t1.name,t2.name,t2.id FROM country t1,city t2 WHERE t1.id = t2.cid; -- 2. 查询视图 SELECT * FROM city_country; -- 3. 修改数据 -- 想要将岛国,city_id=7 的数据,大阪城市名称改名为 小坂 UPDATE city_country SET city_name = '小坂' WHERE city_id = 7; -- 4. 查询原始表 SELECT * FROM city; /** 注意:修改视图的内容,也会同步的去修改原始表的内容 **/
4、 删除视图
语法格式
-- 1. 删除视图的语法(简化格式) DROP VIEW 视图的名称; -- 2. 删除视图的语法(完整格式) DROP VIEW IF EXISTS 视图的名称;
案例代码
-- 1. 创建视图 CREATE VIEW city_country (country_name,city_name,city_id) AS SELECT t1.name,t2.name,t2.id FROM country t1,city t2 WHERE t1.id = t2.cid; -- 2. 查询视图 SELECT * FROM city_country; -- 3. 删除视图 DROP VIEW city_country; DROP VIEW IF EXISTS city_country;
5、 注意事项
/* 问题:删除了视图当中的 东京 city_id=6 这条记录 请问对原始表而言有影响吗? 没有影响,不能执行删除操作 */ -- 1. 查询视图 SELECT * FROM city_country; -- 2. 在视图当中,删除 city_id=6的记录 DELETE FROM city_country WHERE city_id=6; -- 3. 在视图当中,插入数据 INSERT INTO city_country VALUES ('英国','伦敦',8); /* 注意事项: 视图不可以执行删除的操作,也不可以做添加操作。 注意事项: 视图可以做 修改的操作,修改之后,会影响到原始表的数据。 注意事项: 视图最主要的作用,用于查询操作,简化多表查询。 */
小结:
视图可以做 查询和修改操作。
其中,如果执行了修改操作,则会影响到原始表的数据。
四 备份和还原
1、 代码的方式
备份
mysqldump -u root -p mydb05 > C:/db05.sql
案例代码(在小黑窗口当中实现,关闭掉 SQLyog)
还原
1、删除掉之前的数据库 2、创建一个空的数据库,数据库的名称和之前的名称相同。 3、使用创建的空数据库 4、执行指令 SOURCE C:/db05.sql
案例代码(启动小黑窗口去完成,关闭掉SQLyog)
2、 可视化界面
备份
还原
1、删除掉之前的数据库内容 2、创建一个空的数据库,名称和之前的名称相同 3、点击右键,选择导入
效果图