文章目录

多表关系数据库设计的范式数据库的备份和还原多表查询多表查询的分类：内连接查询隐式内连接：使用where条件消除无用数据显式内连接 多表查询的分类：外连接查询左外连接右外连接满外连接 多表查询的分类：子查询 事务事务的四大特征和隔离级别 DCL（了解）

多表关系

多表之间的关系分类和实现及模型图：

一对一(了解)：

如：人和身份证

分析：一个人只有一个身份证，一个身份证只能对应一个人

实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。（但这样的话我们为什么不把信息建在一张表上呢，所以，我们只要了解，实现要随情况而定）

模型图

一对多(多对一)：

如：部门和员工

分析：一个部门有多个员工，一个员工只能对应一个部门

实现方式：在多的一方建立外键，指向一的一方的主键。（其实就是我们上一篇提到的外键的创建方式）

模型图

多对多：

如：学生和课程

分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

模型图

一对一我们就了解，想做的话就普通定义就行了，一对多就是我们上一篇定义的，我们讲一下多对多

如：我们访问了一个旅游网站，我们选择了一个风景游，然后我们可以看到不同风景的旅游，我们（用户）收藏了几个风景旅游的线路表，那么，这时候，就会出现一个用户收藏多个线路，一个线路被多个用户收藏这种情况，这时用户和路线就是多对多的情况，收藏作为中间表。

该模型图大概如下

然后就是我们着手建立了

步骤：

由上图可只，线路表和分类是多对一的关系，所以我们先定义出分类表和线路表，然后用线路表的cid作为外键与分类表的cid相连

创建旅游线路分类表 tab_category

-- 创建旅游线路分类表 tab_category-- cid 旅游线路分类主键，自动增长-- cname 旅游线路分类名称非空，唯一，字符串 100CREATE TABLE tab_category (cid INT PRIMARY KEY AUTO_INCREMENT,cname VARCHAR(100) NOT NULL UNIQUE)-- 添加旅游线路分类数据：INSERT INTO tab_category (cname) VALUES ('周边游'), ('出境游'), ('国内游'), ('港澳游');SELECT * FROM tab_category;

创建旅游线路表 tab_route

/*创建旅游线路表 tab_routerid 旅游线路主键，自动增长rname 旅游线路名称非空，唯一，字符串 100price 价格rdate 上架时间，日期类型cid 外键，所属分类*/CREATE TABLE tab_route(rid INT PRIMARY KEY AUTO_INCREMENT,rname VARCHAR(100) NOT NULL UNIQUE,price DOUBLE,rdate DATE,cid INT,FOREIGN KEY (cid) REFERENCES tab_category(cid))-- 添加旅游线路数据INSERT INTO tab_route VALUES(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499,'-01-27', 1),(NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '-02-22', 3),(NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州往返 特价团】', 1699, '-01-27', 2),(NULL, '【经典•狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '-12-23',2),(NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店暨会议中心标准房 1 晚住宿】', 799, '-04-10', 4);SELECT * FROM tab_route;

然后就是线路表和用户之间多对多的关系了，我们要把用户表定义出来。然后用中间表的rid和uid做外键分别与线路表的rid与用户的id相连

创建用户表 tab_user

/*创建用户表 tab_useruid 用户主键，自增长username 用户名长度 100，唯一，非空password 密码长度 30，非空name 真实姓名长度 100birthday 生日sex 性别，定长字符串 1telephone 手机号，字符串 11email 邮箱，字符串长度 100*/CREATE TABLE tab_user (uid INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(100) UNIQUE NOT NULL,PASSWORD VARCHAR(30) NOT NULL,NAME VARCHAR(100),birthday DATE,sex CHAR(1) DEFAULT '男',telephone VARCHAR(11),email VARCHAR(100));-- 添加用户数据INSERT INTO tab_user VALUES(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@'),(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@');SELECT * FROM tab_user;

创建收藏表 tab_favorite（即中间表）

/*创建收藏表 tab_favoriterid 旅游线路 id，外键date 收藏时间uid 用户 id，外键rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次*/CREATE TABLE tab_favorite (rid INT,DATE DATETIME,uid INT,-- 创建复合主键（联合主键）PRIMARY KEY(rid,uid),FOREIGN KEY (rid) REFERENCES tab_route(rid),FOREIGN KEY(uid) REFERENCES tab_user(uid));-- 增加收藏表数据INSERT INTO tab_favorite VALUES(1, '-01-01', 1), -- 老王选择厦门(2, '-02-11', 1), -- 老王选择桂林(3, '-03-21', 1), -- 老王选择泰国(2, '-04-21', 2), -- 小王选择桂林(3, '-05-08', 2), -- 小王选择泰国(5, '-06-02', 2); -- 小王选择迪士尼SELECT * FROM tab_favorite;

上述提到了联合主键

PRIMARY KEY(字段1,字段2);

和主建的功能差不多，但去重复是两个字段完全一样才去重

根据架构设计器各表之间的关系为

数据库设计的范式

概念：

设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

分类及各自存在的问题：

第一范式（1NF）：每一列都是不可分割的原子数据项

如下图：表头行的系被分割为系名和系主任，这就不符合第一范式

要符合第一范式的话要把系给去掉，系名和系主任各执掌一列，如下图

第一范式存在问题

存在非常严重的数据冗余(重复): 姓名、系名、系主任

数据添加存在问题: 添加新开设的系和系主任时，数据不合法

数据删除存在问题: 张无忌同学毕业了，删除数据，会将系的数据一起删除。

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）

我们要了解以上标黄的几个概念，用例子来理解

函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A

例如：学号–>姓名。 （学号，课程名称） --> 分数

完全函数依赖：A–>B， 如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值。

例如：（学号，课程名称） --> 分数

部分函数依赖：A–>B， 如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一些值即可。

例如：（学号，课程名称） --> 姓名

传递函数依赖：A–>B, B -->C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A

例如：学号–>系名，系名–>系主任

码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码

例如：下表选课表中码为：（学号，课程名称）

主属性：码属性组中的所有属性非主属性：除过码属性组的属性

由上述可知，第二范式就是分表（把总表分为选课表和学生表）

选课表和学生表都符合第一范式，选课表中：分数完全依赖于（学号和课程）这个属性组；学生表中：（姓名，系名，系主任）完全依赖于学号

第二范式存在问题

数据添加存在问题: 添加新开设的系和系主任时，数据不合法

数据删除存在问题: 张无忌同学毕业了，删除数据，会将系的数据一起删除。

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

比如：第二范式中非主属性系主任依赖于非主属性系名

所以我们要继续分表，如下

这样，以上的三个问题就都解决了

数据库的备份和还原

我们有两种方式

（1）命令行：

语法：

备份：

mysqldump -u用户名 -p密码 数据库名称 > 保存的路径

还原：

步骤

①登录数据库

②创建数据库

③使用数据库

④执行文件：source 文件路径

（2）图形化工具（SQLyog）：

①在数据库列表处选择要备份的数据库

②选择要备份到的路径

③然后删除我们备份过的数据库

④在数据库列表出右键，然后选择之星SQL脚本

⑤填写我们备份的路径后执行就OK了

多表查询

查询语法：

select

列名列表

from

表名列表

where…

先准备一些数据

-- 创建部门表CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');-- 创建员工表CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性别salary DOUBLE, -- 工资join_date DATE, -- 入职日期dept_id INT,FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键));INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'-02-24',1);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'-12-02',2);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'-08-08',2);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'-10-07',3);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'-03-14',1);

两张表如下：

部门表

员工表

我们进行多表查询就要知道笛卡尔积，这是我们在查询时会出现的一种情况

笛卡尔积：

有两个集合A,B ，取这两个集合的所有组成情况。要完成多表查询，需要消除无用的数据

笛卡尔积实例：

SELECT*FROM emp,dept;

我们会发现有许多数据是错误的，所以我们要实现有意义的多表查询就要进行条件判断

多表查询的分类：内连接查询

内连接分为隐式内连接和显式内连接。

隐式内连接：使用where条件消除无用数据

例如：

查询所有员工信息和对应的部门信息

SELECT*FROM emp,dept WHERE dept.`id`=emp.`dept_id`; -- 撇号可以不要

查询结果如下图

查询员工表的名称，性别，部门表的名称

SELECT emp.`NAME`,emp.`gender`,dept.`NAME` FROM emp,dept WHERE dept.`id`=emp.`dept_id`;

一般为了方便加注释和理解，我们通常每个关键字占一行，内容占一行，有些表名过长我们同时改别名

SELECT t1.`NAME`,-- 员工表的姓名t1.`gender`,-- 员工表的性别t2.`NAME` -- 部门的名称FROM emp t1,dept t2 WHERE t1.`dept_id`=t2.`id`

显式内连接

语法：

select 字段列表 from 表名1 inner join 表名2 on 条件（inner可省略）

例如：

查询员工表的名称，性别，部门表的名称

SELECTt1.`NAME`,t1.`gender`,t2.`NAME`FROMemp t1INNER JOINdept t2ONt1.`dept_id`=t2.`id`-- 不加innerSELECTt1.`NAME`,t1.`gender`,t2.`NAME`FROMemp t1JOINdept t2ONt1.`dept_id`=t2.`id`

内连接查询步骤：

①要知道从哪些表中查询数据

②要得到想要的结果的条件是什么

③要查询哪些字段

多表查询的分类：外连接查询

外连接查询分为左外连接和右外连接

左外连接

语法：

select 字段列表 from 表1 left [outer] join 表2 on 条件；

查询两张以上的表的语法：

select 字段列表 from 表1

left [outer] join 表2 on 条件1

left [outer] join 表3 on 条件2

…;

查询的是左表所有数据以及其交集部分。（如果我们向员工表里添加了一行数据，员工表又在左边，但并未给外键赋值（为null），这个时候新加的数据也会被查询出来，但部门表的信息为null）

【左表的判断为哪个表先写哪个是左表】

例子：

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门SELECT*FROM emp t1 LEFT JOIN dept t2 ONt1.`dept_id`=t2.`id`;

结果如下

右外连接

语法：

select 字段列表 from 表1 right [outer] join 表2 on 条件；

查询的是右表所有数据以及其交集部分。

例子：

SELECT*FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id`=t2.`id`;

结果如下

满外连接

注意：Oracle数据库里面有full join，可是在mysql数据库中对full join支持的不好。我们可以使用union来达到目的。

Oracle语法：

select *from A full outer join B on 条件

MySQL使用union语法

左外连接

UNION

右外连接

注意：左右两张表要一样，且顺序不能变

SELECT*FROM dept LEFT OUTER JOIN emp ON dept_id=deptnoUNION SELECT*FROM dept RIGHT OUTER JOIN emp ON dept_id=deptno;

注意：使用union可以起到去重的效果，使用union all是将两张表直接拼接。

多表查询的分类：子查询

概念：

查询中嵌套查询，称嵌套查询为子查询。（这个嵌套的查询要用括号括起来）

比如说：我们要查询员工最高工资的人的信息，以往查询是分两步

①查询最高的工资是多少

SELECT MAX(salary) FROM emp;

② 查询员工信息，并且工资等于9000的

SELECT * FROM emp WHERE emp.`salary` = 9000;

我们学习子查询后，一条SQL就完成这个操作

SELECT*FROM emp WHERE emp.`salary`=(SELECT MAX(salary) FROM emp);

子查询有不同情况

①子查询的结果是单行单列是：

子查询可以作为条件，使用运算符去判断。 运算符： >、>=、=、<=、<

如：查询员工工资小于平均工资的人

SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

②子查询的结果是多行单列的：

子查询可以作为条件，使用运算符in来判断

如：查询“财务部”和“市场部”所有的员工信息

我们以前是先查询部门与员工外键相连的id数据，然后根据id数据查询员工表的数据，得到的id数据一般是多行单列的

SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

用子查询的话，多行当列的数据就相当于in后（）中的数据

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

③子查询的结果是多行多列的：

子查询可以作为一张虚拟表参与查询

如：查询员工入职日期是-11-11日之后的员工信息和部门信息

用普通内连接查询是

SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` > '-11-11' -- 这个时间是可以直接比较的

用子查询

SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '-11-11') t2WHERE t1.id = t2.dept_id;

事务

事务的概念：

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

事物的操作：

开启事务： start transaction或begin;

回滚：rollback;

提交：commit;

如下面这个例子：zhangsan和lisi进行转账操作，初始化都为1000元，开启事务（start transaction）后张三给李四转账500元，那么zhangsan和lisi的账户分别为500和1500，但是这个过程可能会出现错误，比如zhangsan给lisi转完帐后，系统出现了异常，那么zhangsan的钱变为500，而lisi的钱并未增加，但此时的事务并未被commit提交，所以我们可以进行回滚操作（rollback）回到开启事务前的状态，这样就可以避免错误的发生

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. 张三账户 -500UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';-- 2. 李四账户 +500-- 出错了...UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';-- 发现执行没有问题，提交事务COMMIT;-- 发现出问题了，回滚事务ROLLBACK;

MySQL数据库中事务默认自动提交

事务提交的两种方式：

①自动提交：

MySQL就是自动提交的

一条DML(增删改)语句会自动提交一次事务

②手动提交：

Oracle 数据库默认是手动提交事务需要先开启事务，再提交

修改事务的默认提交方式：

查看事务的默认提交方式：

SELECT @@autocommit;-- 1 代表自动提交 ，0 代表手动提交

修改默认提交方式：

set @@autocommit = 0; -- set autocommit=0; 也可以set @@autocommit = 1; -- set autocommit=1; 也可以

事务的四大特征和隔离级别

四大特征：

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。

持久性：当事务提交或回滚后，数据库会持久化的保存数据。

隔离性：多个事务之间。相互独立。（与事务的隔离级别有关）

一致性：事务操作前后，数据总量不变

对该四大特征的解释可以看m旧裤子博主的这篇博客

链接: 事务四大特性详解

事务的隔离级别（了解）

概念：

多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

设置隔离级别会存在一些问题：

①脏读：一个事务，读取到另一个事务中没有提交的数据

②不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。

③幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

对这三种问题的解释可以看qq_41776884博主的这篇博客

链接: 数据库事务隔离级别（脏读、幻读、不可重复读）

四种隔离级别：

read uncommitted：读未提交 产生的问题：脏读、不可重复读、幻读read committed：读已提交 （Oracle） 产生的问题：不可重复读、幻读repeatable read：可重复读 （MySQL默认） 产生的问题：幻读serializable：串行化 可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低，所以我们需要选择适当的隔离级别，这样相对的既保证了安全性，效率还不低。

数据库查询隔离级别：

select @@tx_isolation;

数据库设置隔离级别：（数据库设置隔离级别后要断开连接重新打开才能生效）

语法：set global transaction isolation level 级别字符串;

DCL（了解）

前面我们提到SQL的分类：

DDL：操作数据库和表DML：增删改表中数据DQL：查询表中数据DCL：管理用户，授权

我只说了前三种，因为DCL是由 DBA（数据库管理员）操作的，我们只需要了解一下就行

DCL的作用为：管理用户和授权

管理用户包括：

添加用户：

语法： CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;

删除用户：

语法：DROP USER ‘用户名’@‘主机名’;

修改用户密码：

语法：UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;

如：

UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';

MySQL5.7 不能用update user set

解决方案可以看简书上似朝朝我心的文章：

链接: update user set Password = password(‘12345678’) where User = ‘root’; ERROR 1054 (42S22): Unknown …

语法：SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);//DCL特有方式

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

MySQL8修改密码:

语法：ALTER USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘新密码’;

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

如果mysql中忘记了root用户的密码，我们可以进行如下操作

cmd – > net stop mysql 停止mysql服务（需要管理员运行该cmd）

使用无验证方式启动mysql服务： mysqld --skip-grant-tables

打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功

use mysql;

update user set password = password(‘你的新密码’) where user = ‘root’;

关闭两个窗口

打开任务管理器，手动结束mysqld.exe 的进程

启动mysql服务

使用新密码登录。

mysql8.0版本下命令行mysqld –skip-grant-tables 失效，无法登陆

该问题的解决可以看博主Kante_616的这篇文章：

链接: mysql8.0版本下命令行mysqld –skip-grant-tables 失效，无法登陆的问题

查询用户

步骤：

①切换到mysql数据库

USE mysql;

② 查询user表

SELECT * FROM USER;

通配符： % 表示可以在任意主机使用用户登录数据库

权限管理：

查询权限：

语法：SHOW GRANTS FOR ‘用户名’@‘主机名’;

如：

SHOW GRANTS FOR 'lisi'@'%';

授予权限：

语法：grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;

如：

-- 给张三用户授予所有权限，在任意数据库任意表上GRANT ALL ON *.* TO 'zhangsan'@'localhost';

撤销权限：

语法：revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;

如：

REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';