MySQL进阶(二)
MySQL进阶(二)
一、约束
1.外键约束
外键约束概念
- 让表和表之间产生关系,从而保证数据的准确性!
建表时添加外键约束
- 为什么要有外键约束
plaintext1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30-- 创建db2数据库
CREATE DATABASE db2;
-- 使用db2数据库
USE db2;
-- 创建user用户表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT, -- id
NAME VARCHAR(20) NOT NULL -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五');
-- 创建orderlist订单表
CREATE TABLE orderlist(
id INT PRIMARY KEY AUTO_INCREMENT, -- id
number VARCHAR(20) NOT NULL, -- 订单编号
uid INT -- 订单所属用户
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
(NULL,'hm003',2),(NULL,'hm004',2),
(NULL,'hm005',3),(NULL,'hm006',3);
-- 添加一个订单,但是没有所属用户。这合理吗?
INSERT INTO orderlist VALUES (NULL,'hm007',8);
-- 删除王五这个用户,但是订单表中王五还有很多个订单呢。这合理吗?
DELETE FROM USER WHERE NAME='王五';
-- 所以我们需要添加外键约束,让两张表产生关系- 外键约束格式
plaintext1
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
- 创建表添加外键约束
plaintext1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24-- 创建user用户表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT, -- id
NAME VARCHAR(20) NOT NULL -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五');
-- 创建orderlist订单表
CREATE TABLE orderlist(
id INT PRIMARY KEY AUTO_INCREMENT, -- id
number VARCHAR(20) NOT NULL, -- 订单编号
uid INT, -- 订单所属用户
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) -- 添加外键约束
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
(NULL,'hm003',2),(NULL,'hm004',2),
(NULL,'hm005',3),(NULL,'hm006',3);
-- 添加一个订单,但是没有所属用户。无法添加
INSERT INTO orderlist VALUES (NULL,'hm007',8);
-- 删除王五这个用户,但是订单表中王五还有很多个订单呢。无法删除
DELETE FROM USER WHERE NAME='王五';删除外键约束
plaintext
1 | -- 标准语法 |
- 建表后添加外键约束
plaintext
1 | -- 标准语法 |
2.外键的级联更新和级联删除(了解)
- 什么是级联更新和级联删除
- 当我想把user用户表中的某个用户删掉,我希望该用户所有的订单也随之被删除
- 当我想把user用户表中的某个用户id修改,我希望订单表中该用户所属的订单用户编号也随之修改
- 添加级联更新和级联删除
plaintext
1 | -- 添加外键约束,同时添加级联更新 标准语法 |
二、多表设计
1.一对一(了解)
- 分析
- 人和身份证。一个人只有一个身份证,一个身份证只能对应一个人!
- 实现原则
- 在任意一个表建立外键,去关联另外一个表的主键
- SQL演示
plaintext
1 | -- 创建db5数据库 |
- 图解
01
2.一对多
- 分析
- 用户和订单。一个用户可以有多个订单!
- 商品分类和商品。一个分类下可以有多个商品!
- 实现原则
- 在多的一方,建立外键约束,来关联一的一方主键
- SQL演示
plaintext
1 | /* |
- 图解
02
3.多对多
- 分析
- 学生和课程。一个学生可以选择多个课程,一个课程也可以被多个学生选择!
- 实现原则
- 需要借助第三张表中间表,中间表至少包含两个列,这两个列作为中间表的外键,分别关联两张表的主键
- SQL演示
plaintext
1 | -- 创建student表 |
- 图解
03
三、多表查询
1.多表查询-数据准备
- SQL语句
plaintext
1 | -- 创建db6数据库 |
- 架构器图解
04
2.多表查询-笛卡尔积查询(了解)
- 有两张表,获取这两个表的所有组合情况
- 要完成多表查询,需要消除这些没有用的数据
- 多表查询格式
plaintext
1 | SELECT |
- 笛卡尔积查询
plaintext
1 | -- 标准语法 |
3.多表查询-内连接查询
- 查询原理
- 内连接查询的是两张表有交集的部分数据(有主外键关联的数据)
- 显式内连接
plaintext
1 | -- 标准语法 |
- 隐式内连接
plaintext
1 | -- 标准语法 |
4.多表查询-外连接查询
左外连接
- 查询原理
- 查询左表的全部数据,和左右两张表有交集部分的数据
- 基本演示
plaintext1
2
3
4
5
6
7
8
9
10
11
12
13
14-- 标准语法
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
-- 查询所有用户信息,以及用户对应的订单信息
SELECT
u.`name`, -- 姓名
u.`age`, -- 年龄
o.`number` -- 订单编号
FROM
USER u -- 用户表
LEFT OUTER JOIN
orderlist o -- 订单表
ON
u.`id`=o.`uid`;- 查询原理
右外连接
- 查询原理
- 查询右表的全部数据,和左右两张表有交集部分的数据
- 基本演示
plaintext1
2
3
4
5
6
7
8
9
10
11
12
13
14-- 基本语法
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
-- 查询所有订单信息,以及订单所属的用户信息
SELECT
u.`name`, -- 姓名
u.`age`, -- 年龄
o.`number` -- 订单编号
FROM
USER u -- 用户表
RIGHT OUTER JOIN
orderlist o -- 订单表
ON
u.`id`=o.`uid`;- 查询原理
5.多表查询-子查询
子查询介绍
- 查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询!
子查询-结果是单行单列的
- 可以作为条件,使用运算符进行判断!
- 基本演示
plaintext1
2
3
4
5
6
7-- 标准语法
SELECT 列名 FROM 表名 WHERE 列名=(SELECT 聚合函数(列名) FROM 表名 [WHERE 条件]);
-- 查询年龄最高的用户姓名
SELECT MAX(age) FROM USER; -- 查询出最高年龄
SELECT NAME,age FROM USER WHERE age=26; -- 根据查询出来的最高年龄,查询姓名和年龄
SELECT NAME,age FROM USER WHERE age = (SELECT MAX(age) FROM USER);子查询-结果是多行单列的
- 可以作为条件,使用运算符in或not in进行判断!
- 基本演示
plaintext1
2
3
4
5
6
7-- 标准语法
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]);
-- 查询张三和李四的订单信息
SELECT id FROM USER WHERE NAME='张三' OR NAME='李四'; -- 查询张三和李四用户的id
SELECT number,uid FROM orderlist WHERE uid=1 OR uid=2; -- 根据id查询订单
SELECT number,uid FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME='张三' OR NAME='李四');子查询-结果是多行多列的
- 可以作为一张虚拟表参与查询!
- 基本演示
plaintext1
2
3
4
5-- 标准语法
SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM USER u,(SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;
6.多表查询练习
- 查询用户的编号、姓名、年龄。订单编号
plaintext
1 | /* |
- 查询所有的用户。用户的编号、姓名、年龄。订单编号
plaintext
1 | /* |
- 查询所有的订单。用户的编号、姓名、年龄。订单编号
plaintext
1 | /* |
- 查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号
plaintext
1 | /* |
- 查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号
plaintext
1 | /* |
- 查询商品分类的编号、分类名称。分类下的商品名称
plaintext
1 | /* |
- 查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称
plaintext
1 | /* |
- 查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称
plaintext
1 | /* |
- 查询所有的用户和所有的商品。显示用户的编号、姓名、年龄。商品名称
plaintext
1 | /* |
- 查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称
plaintext
1 | /* |
7.多表查询-自关联查询
- 自关联查询介绍
- 同一张表中有数据关联。可以多次查询这同一个表!
- 自关联查询演示
plaintext
1 | -- 创建员工表 |
四、视图
1.视图的概念
- 视图是一种虚拟存在的数据表
- 这个虚拟的表并不在数据库中实际存在
- 作用是将一些比较复杂的查询语句的结果,封装到一个虚拟表中。后期再有相同复杂查询时,直接查询这张虚拟表即可
- 说白了,视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中,所以我们在创建视图的时候,工作重心就要放在这条SELECT查询语句上
2.视图的好处
- 简单
- 对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集
- 安全
- 视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集
- 数据独立
- 一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响
3.视图数据准备
plaintext
1 | -- 创建db7数据库 |
4.视图的创建
- 创建视图语法
plaintext
1 | -- 标准语法 |
- 普通多表查询,查询城市和所属国家
plaintext
1 | -- 普通多表查询,查询城市和所属国家 |
- 创建视图基本演示
plaintext
1 | -- 创建一个视图。将查询出来的结果保存到这张虚拟表中 |
- 创建视图并指定列名基本演示
plaintext
1 | -- 创建一个视图,指定列名。将查询出来的结果保存到这张虚拟表中 |
5.视图的查询
- 查询视图语法
plaintext
1 | -- 标准语法 |
- 查询视图基本演示
plaintext
1 | -- 查询视图。查询这张虚拟表,就等效于查询城市和所属国家 |
- 查询视图创建语法
plaintext
1 | -- 标准语法 |
- 查询视图创建语句基本演示
plaintext
1 | SHOW CREATE VIEW city_country; |
6.视图的修改
- 修改视图表中的数据
plaintext
1 | -- 标准语法 |
- 修改视图表结构
plaintext
1 | -- 标准语法 |
7.视图的删除
- 删除视图
plaintext
1 | -- 标准语法 |
8.视图的总结
- 视图是一种虚拟存在的数据表
- 这个虚拟的表并不在数据库中实际存在
- 说白了,视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中,所以我们在创建视图的时候,工作重心就要放在这条SELECT查询语句上
- 视图的好处
- 简单
- 安全
- 数据独立
五、备份与还原
1.命令行方式
备份
- 使用SecureCRT工具连接到Linux系统,输入:mysqldump -u root -p 数据库名称 > 文件保存路径
12- 进入文件保存路径,查看文件是否存在
1314恢复
- 登录mysql数据库
15- 删除已经备份的数据库
16- 重新创建名称相同的数据库
17- 使用该数据库
18- 导入文件执行:source 备份文件路径;
19
2.图形化界面方式
- 备份
05
06
07
- 恢复
08
09
10
11
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 By_Xb!
评论