欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

数据库技术：MySQL 多表，外键约束，数据库设计，索引，视图，存储过程，触发器，数据控制，数据备份与恢复

程序员文章站 2022-06-03 23:13:53
...

MySQL: Multi-Table, Foreign Key and Database Design

Multi-Table Database

In the development environment, a project usually consists of multiple tables. For Example, in a Online Shopping Mall project, it has user table, category table, product table, order table, et cetera. Also, there are some relationships among these tables.

Multi-table solves the redundancy problem of Single table in Database Design.

-- 单表：有冗余问题，同一个字段中出现大量的重复数据
# dep_name 和 dep_location 数据会重复出现
CREATE TABLE emp(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT ,
    dep_name VARCHAR(20),
    dep_location VARCHAR(20)
);

-- 删除emp表, 重新创建两张表
DROP TABLE emp;
# 主表，部门表。字段 id 为主键
CREATE TABLE department(
     id INT PRIMARY KEY AUTO_INCREMENT,   
     dep_name VARCHAR(30),  
     dep_location VARCHAR(30)
);
# 从表，员工表。外键 dept_id 与部门表中的主键对应
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT
);

-- 多表设计上的问题：
# 在员工表的 dept_id 里面输入不存在的部门 id，
# 数据依然可以添加，显然这是不合理的

-- 解决方法：
# 使用外键约束，约束 dept_id 为部门表中存在的 id

Foreign Key Constraint

What is Foreign Key?

外键指的是在“从表”中与“主表”的主键对应的那个字段，比如员工表的 dept_id，就是外键。使用外键约束可以让两张表之间产生一个对应关系，从而保证主从表的引用的完整性。

主表：主键 id 所在的表，约束别人的表；从表：外键所在的表，被约束的表。

Create Foreign Key Constraint

添加外键约束，就会产生强制性的外键数据检查，从而保证了数据的完整性和一致性。

-- 已有表添加外键约束
# 可以不写 CONSTRAINT emp_dept_fk
ALTER TABLE employee 
ADD CONSTRAINT emp_dept_fk 
	FOREIGN KEY (dept_id) 
	REFERENCES department(id);

-- 重新创建表，添加外键约束
# 先删除 employee表
DROP TABLE employee;
# 重新创建 employee 表，添加外键约束
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT,
    CONSTRAINT emp_dept_fk 
    	FOREIGN KEY(dept_id) 
    	REFERENCES department(id)
);
Delete Foreign Key Constraint
-- 删除 employee 表中的外键约束
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- 再将外键约束添加回来
# 省略外键约束名称, 系统会自动生成一个约束名称
ALTER TABLE employee 
ADD FOREIGN KEY (dept_id) 
	REFERENCES department (id);
Notes for Using Foreign Key Constraint

从表外键类型必须与主表主键类型一致，否则外键约束创建失败。（Error: Cannot add foreign key constraint）

添加数据时，应该先添加主表中的数据。

# 添加一个新的部门
INSERT INTO department(dep_name,dep_location) VALUES('市场部','广州');
# 添加一个属于市场部的员工
INSERT INTO employee(ename,age,dept_id) VALUES('张人大',24,1);

删除数据时，应该先删除从表中的数据。（Error: Cannot delete or update a parent row: a foreign key constraint fails）

-- 错误删除：直接删除主表数据
# 不能删除主表的这条数据，因为在从表中有对这条数据的引用
# DELETE FROM department WHERE id = 1;

-- 正确删除
# 先删除从表的所有关联数据
DELETE FROM employee WHERE dept_id = 1;
# 再删除主表的数据
DELETE FROM department WHERE id = 1;
Cascade Deletion

级联删除操作：实现删除主表数据的同时，也删除掉从表数据。

# 重新创建
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT,
    CONSTRAINT emp_dept_fk 
    	FOREIGN KEY(dept_id) 
    	REFERENCES department(id) 
    	ON DELETE CASCADE # 添加级联删除
);
# 添加数据
...
# 删除部门编号为 1 的记录，
# 同时，员工表中外键值是 1 的记录也自动删除了
DELETE FROM department WHERE id = 1;

Creating Multiple Tables and Table Relationships

一对多关系

最常见的关系，班级对学生，部门对员工，客户对订单，商品对分类。

实现方式：主表（一方）的主键为从表（多方）的外键。在多的一方建立外键，指向一的一方的主键。

-- 省和市表：一个省包含多个市
# 创建省表。主表，要添加主键约束
CREATE TABLE province(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    description VARCHAR(20)
);
# 创建市表。从表，外键类型要与主表主键一致
CREATE TABLE city(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    description VARCHAR(20),
    pid INT,
    CONSTRAINT pro_city_fk 
    	FOREIGN KEY (pid) 
    	REFERENCES province(id)
);
多对多关系

学生对课程，学生对老师，用户对角色。

实现方式：需要借助一张中间表，中间表中至少保存两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

-- 演员与角色表：多演员对多角色
# 创建演员表
CREATE TABLE actor(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
# 创建角色表
CREATE TABLE role(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
# 创建中间表
CREATE TABLE actor_role(
    # 中间表自己的主键
    id INT PRIMARY KEY AUTO_INCREMENT,
    # 指向 actor 表的外键
    aid INT,
    # 指向 role 表的外键
    rid INT
);
# 添加外键约束指向演员表的主键
ALTER TABLE actor_role 
ADD FOREIGN KEY(aid) REFERENCES actor(id);
# 添加外键约束指向角色表的主键
ALTER TABLE actor_role 
ADD FOREIGN KEY(rid) REFERENCES role(id);
一对一关系

在实际的开发中使用较少，因为一对一关系可以合成为一张表。

实现方式：可以在任意一方添加一个外键，指向另一方的主键。给外键设置唯一约束。

Multiple Tables Query

What is multiple tables query?

查询多张表，获取到需要的数据。例如，要查询家电分类下都有哪些商品，那么我们就需要查询分类表与商品表。

-- 创建 db1 数据库，指定编码
CREATE DATABASE db1 CHARACTER SET utf8;
use db1;

-- 创建分类表与商品表
# 分类表：一方，主表
CREATE TABLE category (
    cid VARCHAR(32) PRIMARY KEY ,
    cname VARCHAR(50)
);
# 商品表：多方，从表
CREATE TABLE products(
    pid VARCHAR(32) PRIMARY KEY,
    pname VARCHAR(50),
    price INT,
	# 是否上架标记
    flag VARCHAR(2),
    category_id VARCHAR(32),
    FOREIGN KEY (category_id) REFERENCES category (cid)
);

-- 插入数据
# 分类数据
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','鞋服');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','汽车');
# 商品数据
INSERT INTO products(pid,pname,price,flag,category_id) 
VALUES('p001','小米电视',5000,'1','c001');
...
INSERT INTO products(pid,pname,price,flag,category_id) 
VALUES('p004','篮球',800,'1','c002');
...
INSERT INTO products(pid,pname,price,flag,category_id) 
VALUES('p009','饮料',200,'1','c003');
Cartesian Product

假设集合 A = {a, b}，集合 B = {0, 1, 2}，则两个集合的笛卡尔积为 {(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

交叉连接查询，因为会产生笛卡尔积，所以基本不会使用。

-- 使用交叉连接查询商品表与分类表
# 观察查询结果，产生了笛卡尔积
SELECT * FROM category, products;
Classification of Multiple Tables Query
Inner Join

内连接：通过指定的条件去匹配两张表中的数据，匹配上就显示，匹配不上就不显示。比如，通过 从表的外键 = 主表的主键 的方式去匹配。

Implicit Inner Join

隐式内连接：在 from 子句后面直接写多个表名，并使用 where 子句指定连接条件来过滤无用的数据。

-- 隐式内连接查询所有商品和对应的分类信息
SELECT * 
FROM products, category 
WHERE category_id = cid;

-- 隐式内连接通过给表起别名的方式查询
SELECT 
    p.`pname`,
    p.`price`,
    c.`cname`
FROM products p, category c 
WHERE p.`category_id` = c.`cid`;

-- 查询小米电视是属于哪一分类下的商品
SELECT p.`pname`,c.`cname` 
FROM products p , category c 
WHERE p.`category_id` = c.`cid` 
	AND p.`cname` = '小米电视';
Explicit Inner Join

显式内连接：使用 Inner Join ... On 这种方式，Inner 可以省略。

-- 显式内连接查询所有商品信息和对应的分类信息
SELECT * FROM products p 
INNER JOIN category c 
	ON p.category_id = c.cid;

-- 查询鞋服分类下，价格大于 500 的商品名称和价格
SELECT
    p.pname,
    p.price
FROM products p 
INNER JOIN category c 
	ON p.category_id = c.cid
WHERE p.price > 500 
	AND cname = '鞋服';
Left Outer Join

左外连接：使用 Left Outer Join , Outer 可以省略。以左表为基准，匹配右边表中的数据；如果匹配的上，就展示匹配到的数据；如果匹配不到，左表中的数据正常展示，右边的展示为 null。

-- 左外连接查询所有分类下的商品信息
# 若分类下没有商品信息，数据显示为空
SELECT * 
FROM category c 
LEFT JOIN products p 
	ON c.`cid`= p.`category_id`;

-- 左外连接查询每个分类下的商品个数
SELECT 
    c.`cname` AS '分类名称',
    COUNT(p.`pid`) AS '商品个数' 
FROM category c 
LEFT JOIN products p 
	ON c.`cid` = p.`category_id`
GROUP BY c.`cname`;
Right Outer Join

右外连接：使用 Right Outer Join，Outer 可以省略。以右表为基准，匹配左边表中的数据；如果能匹配到，展示匹配到的数据；如果匹配不到，右表中的数据正常展示，左边展示为 null。

-- 右外连接查询所有分类下的商品信息
# 若分类下没有商品信息，数据显示为空
SELECT * 
FROM products p 
RIGHT JOIN category c 
	ON p.`category_id` = c.`cid`;
Conclusion
  • 内连接：只获取两张表中交集部分的数据。
  • 左外连接：以左表为基准，查询左表的所有数据，以及与右表有交集的部分。
  • 右外连接：以右表为基准，查询右表的所有数据，以及与左表有交集的部分。

Subquery

What is Subquery?

子查询概念：一条 select 查询语句的结果，作为另一条 select 语句的一部分。

子查询的特点：子查询必须放在小括号中；子查询一般作为父查询的查询条件使用。

Classification of Subquery
where 型子查询

将子查询的结果作为父查询的筛选条件。

-- 通过子查询的方式，查询价格最高的商品信息
# 先查询出最高价格
SELECT MAX(price) 
FROM products;
# 将最高价格作为条件，获取商品信息
SELECT * 
FROM products 
WHERE 
	price = (
    	SELECT MAX(price) 
    	FROM products
    );

-- 查询化妆品分类下的商品名称和商品价格
# 先查出化妆品分类的 id
SELECT cid 
FROM category 
WHERE cname = '化妆品';
# 根据分类 id，查询商品表对应的商品信息
SELECT 
    p.`pname`,
    p.`price`
FROM products p 
WHERE 
	p.`category_id` = (
        SELECT cid 
        FROM category 
        WHERE cname = '化妆品'
    );


-- 查询小于平均价格的商品信息
# 查询平均价格
SELECT AVG(price) 
FROM products;
# 查询小于平均价格的商品
SELECT * 
FROM products 
WHERE 
	price < (
        SELECT AVG(price) 
        FROM products
    );
from 型子查询

将子查询的结果作为一张表提供给父层查询使用。另外需要给这张表起别名，否则无法访问表中的字段。

-- 查询商品中，价格大于 500 的商品信息
# 先查询分类表的数据
SELECT * 
FROM category;
# 将上面的查询语句作为一张表使用
SELECT 
    p.`pname`,
    p.`price`,
    c.cname
FROM products p 
INNER JOIN 
	(
        SELECT * 
        FROM category
    ) c 
	ON p.`category_id` = c.cid 
WHERE p.`price` > 500;
exists 型子查询

如果子查询的结果是单列多行类似一个数组，那么父层查询可以使用 IN 函数来包含子查询的结果。

-- 查询价格小于两千的商品，来自于哪些分类（名称）
# 先查询价格小于 2000 的商品的分类 ID
SELECT DISTINCT category_id 
FROM products 
WHERE price < 2000;
# 基于以上的数据进行子查询
SELECT * 
FROM category 
WHERE cid IN (
    SELECT DISTINCT category_id 
    FROM products 
    WHERE price < 2000
);

-- 查询家电类与鞋服类下面的全部商品信息
# 先查询出家电与鞋服类的分类 ID
SELECT cid 
FROM category 
WHERE cname IN ('家电','鞋服');
# 根据 cid 查询分类下的商品信息
SELECT * 
FROM products 
WHERE category_id IN (
    SELECT cid 
    FROM category 
    WHERE cname IN ('家电','鞋服')
);
Conclusion
  • 子查询如果查出的是一个字段（单列），那就在 where 后面作为条件使用。
  • 子查询如果查询出的是多个字段（多列），就当做一张表使用（要起别名）。

Database Design

Database Normalization

范式是设计数据库的规则。

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

满足最低要求的范式是第一范式（1 NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2 NF）， 其余范式以此类推。一般说来，数据库只需满足第三范式（3 NF）就行了。

First Normal Form

第一范式：原子性，做到列不可拆分，是最基本的范式。数据库表里面字段都是单一属性的，不可再分。如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。

比如，如果把国家和城市都放到一个字段中，那么这个字段就不符合第一范式，所以需要被拆分为两个字段才行。

Second Normal Form

第二范式：在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。 一张表只能描述一件事。

比如，如果把学员的信息和课程信息放在一张表中，会导致数据的冗余，如果删除学员信息，课程的信息也被删除了，所以需要拆分为学员和课程两张表。

Third Normal Form

第三范式：消除传递依赖。如果能够推导表的信息出来，就不应该单独的设计一个字段来存放。空间最省原则。

比如，一个表中有三个字段，分别为数量、单价、总价格，因为总价格=数量*单价，所以这里的字段之间存在依赖关系，可以通过数量和单价推导出总价格，这时候就可以省略总价格这个字段。

Database De-normalization

数据库反范式化：通过增加冗余或重复的数据来提高数据库的读性能，以空间换时间。

Redundant Field

冗余字段：设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表，且完全等同于它在其本来所属表的意义表示。

Example

设计”用户表“和”订单表“：”用户表“中有”名字“字段，而”订单表“中也存在”名字“字段；当需要查询“订单表”所有数据并且只需要“用户表”的”名字“字段时，此时如果没有冗余字段，就需要去内连接”订单表“和”用户表“，进一步假设表中数据量非常的大，那这次内连接查询就会消耗巨大的系统性能；这种情况下，冗余的字段就可以派上用场了，如果有了冗余字段，那么我们只查询”订单表“就可以了。

Conclusion
  • 尽量遵循范式理论的规约，尽可能减少冗余字段。
  • 某些情况下，可以合理的加入冗余字段以减少表与表的连接操作，从而让数据库的执行效率更高。

MySQL: Index, View, Stored Procedures, Triggers, DCL, and Database Backup & Recovery

Index

What is Index?

在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令 MySQL 的查询和运行更加高效。

MySQL 将一个表的索引都保存在同一个索引文件中，如果对其中的数据进行增删改操作，MySQL 都会自动的更新索引。

Classification of Index
Primary Key – 主键索引

主键是一种唯一性索引，每个表只能有一个主键，用于标识数据表中的每一条记录。

一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含 NULL。

-- 为 demo1 表添加主键索引
# 创建 demo01 表
CREATE TABLE demo01(
    did INT,
    dname VARCHAR(20),
    hobby VARCHAR(30)
);
ALTER TABLE demo01 ADD PRIMARY KEY (did);

-- 删除表
DROP TABLE demo01;

-- 创建表的时候直接添加主键索引 (最常用)
CREATE TABLE demo01(
    did INT PRIMARY KEY,
    dname VARCHAR(20),
    hobby VARCHAR(30)
);
Unique – 唯一索引

唯一索引指的是索引列的所有值都只能出现一次，必须唯一。

唯一索引可以保证数据记录的唯一性，而且索引的效率也提升了。事实上，创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

-- 使用 create 语句添加唯一索引
CREATE UNIQUE INDEX index_hobby ON demo01(hobby)

-- 创建表的时候直接添加主键索引
CREATE TABLE demo02(  
    did INT PRIMARY KEY,
    dname VARCHAR(20),
    hobby VARCHAR(30),
    UNIQUE index_hobby (hobby)
);

-- 通过表结构删除索引
ALTER TABLE demo02 
DROP INDEX index_hobby;

-- 通过表结构添加索引
# 索引名字可省略，会自动生成名字
ALTER TABLE demo02 
ADD UNIQUE (hobby);

-- 向表中插入数据
INSERT INTO demo01 VALUES(1,'张人大','运动');
# 报错：Duplicate entry 'DBJ' for key 'hobby'
INSERT INTO demo01 VALUES(2,'布莱尔','运动');
index – 普通索引

最常见的索引，作用就是加快对数据的访问速度。

只为那些最经常出现在查询条件或排序条件中的数据列创建普通索引。

-- 使用 create 语句添加唯一索引
CREATE INDEX dname_index ON demo01(dname);

-- 通过表结构删除索引
ALTER TABLE demo01 
DROP INDEX dname_index;

-- 通过表结构添加索引
ALTER TABLE demo01 
ADD INDEX dname_index(dname);
Conclusion
  • 添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。
  • 优点：1. 大大的提高查询速度；2. 可以显著的减少查询中分组和排序的时间。
  • 缺点：1. 创建索引和维护索引需要时间，而且数据量越大时间越长。2. 当对表中的数据进行增加、修改、删除的时候，索引也要同时进行维护，降低了数据的维护速度。

View

What is View in MySQL?

视图是一种虚拟表。

视图建立在已有表的基础上，视图赖以建立的这些表称为基表。

向视图提供数据内容的语句为 SELECT 语句，可以将视图理解为存储起来的 SELECT 语句。

视图向用户提供基表数据的另一种表现形式。

Function of Database View

视图可以在权限控制时使用。比如，某几个列可以运行用户查询，其他列不允许，可以开通视图查询特定的列，起到权限控制的作用。

视图可以简化复杂的多表查询。视图本身就是一条查询 SQL，可以将一次复杂的查询构建成一张视图，用户只要查询视图就可以获取想要得到的信息（不需要再编写复杂的 SQL）。

Using the View
Create View
-- 创建视图
# 先编写查询语句
# 查询所有商品和商品的对应分类信息
SELECT * 
FROM 
	products p 
	LEFT JOIN 
	category c 
	ON 
	p.`category_id` = c.`cid`;
# 基于上面的查询语句，创建视图
CREATE VIEW products_category_view 
AS 
	SELECT * 
	FROM 
		products p 
		LEFT JOIN 
		category c 
		ON 
		p.`category_id` = c.`cid`;

-- 查询视图，当做一张只读的表来操作
SELECT * FROM products_category_view;
Query by View
-- 查询各个分类下的商品平均价格
# 通过多表查询
SELECT 
    cname AS '分类名称',
    AVG(p.`price`) AS '平均价格'
FROM 
	products p 
	LEFT JOIN 
	category c 
	ON 
	p.`category_id` = c.`cid`
GROUP BY c.`cname`;
# 通过视图查询省略了连接表的操作
SELECT 
    cname AS '分类名称',
    AVG(price) AS '平均价格'
FROM products_category_view 
GROUP BY cname;

-- 查询鞋服分类下最贵的商品的全部信息
# 通过连表查询
SELECT * 
FROM 
	products p 
	LEFT JOIN 
	category c 
	ON 
	p.`category_id` = c.`cid`
WHERE 
	c.`cname` = '鞋服' 
	AND 
	p.`price` = (
        SELECT 
        	MAX(price) AS maxPrice
        FROM 
        	products p 
        	LEFT JOIN 
        	category c 
        	ON 
        	p.`category_id` = c.`cid`
        WHERE c.`cname` = '鞋服');
# 通过视图查询
SELECT * 
FROM products_category_view pcv 
WHERE 
	pcv.`cname` = '鞋服'
	AND 
	pcv.`price` = (
        SELECT MAX(price) 
        FROM products_category_view 
        WHERE cname = '鞋服'
    );
Comparison Between View and Table

视图是建立在表的基础上，表存储数据库中的数据，而视图只是做一个数据的展示。

通过视图不能改变表中数据（一般情况下视图中的数据都是表中的列经过计算得到的结果，不允许更新）。

删除视图，表不受影响，而删除表，视图不再起作用。

Stored Procedure

What is Stored Procedure in MySQL?

MySQL 5.0 版本开始支持存储过程。

存储过程是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的 SQL 语句集，经编译创建并保存在数据库中，用户在需要时可通过指定存储过程的名字并给定参数来调用执行。

存储过程其实就是一堆 SQL 语句的合并，中间加入了一些逻辑控制。

Pros and Cons of Stored Procedure
Pros

存储过程一旦调试完成后，就可以稳定运行。前提是业务需求要相对稳定没有变化。

存储过程减少业务系统与数据库的交互，降低耦合，使数据库交互更加快捷，适合应用服务器与
数据库服务器不在同一个地区的情况。

Cons

互联网行业需求变化较快，MySQL 的存储过程与 Oracle 相比较弱，所以较少使用。

存储过程可以在简单的逻辑中使用，但是移植十分困难。特别是在数据库集群环境，要保证各个库之间存储过程变更一致也十分困难。阿里巴巴的代码规范里也提出了禁止使用存储过程，因为存储过程维护起来的确麻烦。

Create Procedure
-- 创建商品表与订单表
# 商品表，num 为库存
CREATE TABLE goods(
    gid INT,
    NAME VARCHAR(20),
    num INT
);
# 订单表，price 为订单价格
CREATE TABLE orders(
    oid INT,
    gid INT,
    price INT
);
# 向商品表中添加3条数据
INSERT INTO goods VALUES(1,'奶茶',20);
INSERT INTO goods VALUES(2,'绿茶',100);
INSERT INTO goods VALUES(3,'花茶',25);

-- 编写简单存储过程
# 查询所有商品数据
# 一般使用 $$ 为语句结束符
DELIMITER $$   
CREATE 
	PROCEDURE 
	goods_proc()  
BEGIN   
    select * from goods; 
END $$

-- 调用存储过程查询 goods 表数据
call goods_proc;

-- 创建接收参数的存储过程
# 接收一个商品 id，根据 id 删除数据
DELIMITER $$  
CREATE 
	PROCEDURE 
	goods_proc02(
        IN goods_id INT
    )  
BEGIN 
    DELETE FROM goods 
    WHERE gid = goods_id;
END $$

-- 调用存储过程传递参数，删除对应的商品
CALL goods_proc02(2);

-- 使用输出参数创建存储过程
# 接收参数插入数据, 并返回受影响的行数
DELIMITER $$
CREATE 
	PROCEDURE 
	orders_proc(
        IN o_oid INT,
        IN o_gid INT,
        IN o_price INT,
        OUT out_num INT
    )
BEGIN
    # 执行插入操作
    INSERT INTO orders 
   	VALUES(o_oid,o_gid,o_price);
    # 设置 num 的值为 1
    SET @out_num = 1;
    # 返回 out_num的值
    SELECT @out_num;
END $$

-- 调用存储过程插入数据，获取返回值
CALL orders_proc(1,2,30,@out_num);

Trigger

What is Trigger in MySQL?

触发器是 MySQL 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行增删改操作时就会**它执行。

可以把触发器理解为：当执行一条 SQL 语句的时候，这条 SQL 语句的执行会自动去触发执行其他的 SQL 语句。

Four Primary Key of Trigger
  • 监视地点 – table
  • 监视事件 – insert/update/delete
  • 触发时间 – before/after
  • 触发事件 – insert/update/delete
Create Trigger

在一个数据库中触发器名是唯一的。

-- 向商品中添加一条数据
INSERT INTO goods VALUES(1,'书本',40);

-- 编写触发器
# 卖出商品之后减少库存量
# 修改结束标识，避免执行出现错误
DELIMITER $
# 创建触发器 t1
CREATE TRIGGER t1
# 指定触发的时机，和要监听的表 
AFTER INSERT ON orders 
# 行触发器 固定写法
FOR EACH ROW
# 触发后具体要执行的事件
BEGIN
    # 订单+1，库存-1
    UPDATE goods 
    SET num = num - 1 
    WHERE gid = 1;
END$

-- 向订单表中添加一条数据
# 添加后，触发器执行，库存自动减一
INSERT INTO orders VALUES(1,1,25);

Data Control Language – DCL

MySQL 默认使用的都是 root 用户，超级管理员，拥有全部的权限。除了 root 用户以外，我们还可以通过 DCL 来定义一些权限较小的用户，分配不同的权限来管理和维护数据库。

-- 创建 admin1 用户
# 只能在 localhost 这个服务器登录 mySQL 服务器，密码为 123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';

-- 创建 admin2 用户
# 可以在任何电脑上登录 mySQL 服务器，密码为 123456
CREATE USER 'admin2'@'%' IDENTIFIED BY '123456';

-- 给 admin1 用户分配对 db4 数据库中 products 表的查询权限
GRANT SELECT ON db4.products TO 'admin1'@'localhost';

-- 给 admin2 用户分配所有权限和操作所有数据库的所有表
GRANT ALL ON *.* TO 'admin2'@'%'; 

-- 查看 root 用户的权限
# GRANT ALL PRIVILEGES 是表示所有权限
SHOW GRANTS FOR 'root'@'localhost';

-- 删除 admin1 用户
DROP USER 'admin1'@'localhost';

-- 查询所有用户
SELECT * FROM USER;

Database Backup & Recovery

备份的应用场景：在服务器进行数据传输、数据存储、数据交换时，就有可能产生数据故障。比如，发生意外停机或存储介质损坏。 此时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

SQLyog Graphic Interface
Data Backup

首先，选中要备份的数据库，右键选择“备份/导出”，选择“备份数据库”；然后指定文件位置，选择导出即可。

Data Recovery

首先，导入备份的 SQL 文件，选中用户名，右键选择“执行 SQL 脚本”；然后选择文件位置，点击执行即可。

Command Line
Data Backup

执行备份，备份 db1 中的数据到 D 盘的 db1.sql 文件中：

mysqldump -uroot -proot db1 > D:/db1.sql
Data Recovery

还原 db1 数据库中的数据（注意：还原的时候需要先创建一个 db1 数据库 ）：

use db1;
source 文件地址
相关标签： 软件开发 mysql sql 数据库

上一篇： mysql数据库从删库到跑路之mysql:视图、触发器、事务、存储过程、函数

下一篇： JAVA-20-数据约束、数据库设计三大范式、多表查询、存储过程、触发器、数据库权限

推荐阅读