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.

-- 单表：有冗余问题，同一个字段中出现大量的重复数据 主表，部门表。字段 id 为主键CREATE TABLE department(     id INT PRIMARY KEY AUTO_INCREMENT，     dep_name VARCHAR(30)，      dep_location VARCHAR(30)); 在员工表的 dept_id 里面输入不存在的部门 id， 使用外键约束，约束 dept_id 为部门表中存在的 id

Foreign Key Constraint

What is Foreign Key?

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

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

Create Foreign Key Constraint

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

-- 已有表添加外键约束 先删除 employee表DROP TABLE employee; 省略外键约束名称，系统会自动生成一个约束名称ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department (id);

Notes for Using Foreign Key Constraint

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

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

39;市场部&39;广州& 添加一个属于市场部的员工INSERT INTO employee(ename，age，dept_id) VALUES(&39;，24，1);

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

-- 错误删除：直接删除主表数据 DELETE FROM department WHERE id = 1;-- 正确删除 再删除主表的数据DELETE FROM department WHERE id = 1;

Cascade Deletion

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

添加级联删除); 删除部门编号为 1 的记录，# 同时，员工表中外键值是 1 的记录也自动删除了DELETE FROM department WHERE id = 1;

Creating Multiple Tables and Table Relationships

一对多关系

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

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

-- 省和市表：一个省包含多个市 创建市表。从表，外键类型要与主表主键一致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 role(  id INT PRIMARY KEY AUTO_INCREMENT， NAME VARCHAR(20)); 中间表自己的主键  id INT PRIMARY KEY AUTO_INCREMENT，    指向 role 表的外键  rid INT); 添加外键约束指向角色表的主键ALTER TABLE actor_role ADD FOREIGN KEY(rid) REFERENCES role(id);

1、首先打开要建外键表的设计器，右击选择“关系”。2、然后弹出“外键关系”窗体，点击选择“添加”，3、然后点击“表和列规范”后面的小按钮，4、就会弹出另外一个窗体让我们选择主键表和列，选好之后点击确定。。

一对一关系

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

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

Multiple Tables Query

What is multiple tables query?

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

-- 创建 db1 数据库，指定编码CREATE DATABASE db1 CHARACTER SET utf8;use db1;-- 创建分类表与商品表 商品表：多方，从表CREATE TABLE products(  pid VARCHAR(32) PRIMARY KEY， pname VARCHAR(50)， price INT，分类数据INSERT INTO category(cid，cname) VALUES(&39;，&39;);INSERT INTO category(cid，cname) VALUES(&39;，&39;);INSERT INTO category(cid，cname) VALUES(&39;，&39;);INSERT INTO category(cid，cname) VALUES(&39;，&39;);39;p001&39;小米电视&39;1&39;c001&39;p004&39;篮球&39;1&39;c002&39;p009&39;饮料&39;1&39;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` = &39;;

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.priceFROM products p INNER JOIN category c ON p.category_id = c.cidWHERE p.price > 500 AND cname = &39;;

Left Outer Join

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

-- 左外连接查询所有分类下的商品信息39;分类名称&39;商品个数' FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`GROUP BY c.`cname`;

Right Outer Join

-- 右外连接查询所有分类下的商品信息# 若分类下没有商品信息，数据显示为空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 * FROM products WHERE price = (  SELECT MAX(price) FROM products  );-- 查询化妆品分类下的商品名称和商品价格39;化妆品& 根据分类 id，查询商品表对应的商品信息SELECT p.`pname`， p.`price`FROM products p WHERE p.`category_id` = (        SELECT cid       FROM category       WHERE cname = &39;  );-- 查询小于平均价格的商品信息 查询小于平均价格的商品SELECT * FROM products WHERE price < (        SELECT AVG(price)       FROM products  );

from 型子查询

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

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

exists 型子查询

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

-- 查询价格小于两千的商品，来自于哪些分类（名称） 基于以上的数据进行子查询SELECT * FROM category WHERE cid IN (    SELECT DISTINCT category_id   FROM products   WHERE price < 2000);-- 查询家电类与鞋服类下面的全部商品信息39;家电&39;鞋服& 根据 cid 查询分类下的商品信息SELECT * FROM products WHERE category_id IN (    SELECT cid   FROM category   WHERE cname IN (&39;，&39;));

1、第一步，创建一个主从表，如下图所示，然后进入下一步。2、其次，完成上述步骤后，选择主表，然后单击设计表进入表设计界面，如下图所示，然后进入下一步。3、接着，完成上述步骤后，单击外键进入外键的设置界面，如下。

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

尽量遵循范式理论的规约，尽可能减少冗余字段。

某些情况下，可以合理的加入冗余字段以减少表与表的连接操作，从而让数据库的执行效率更高。

Index

What is Index?

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

1、登录phpmyadmin，找到要删除外键的数据库的表。如果是本地的话一般是localhost/phpmyadmin。2、使用 SHOW CREATE TABLE at_article 查出表的结构，其中at_article是你要删除外键的表名。3、查来之后，是一个简单的。

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;-- 通过表结构添加索引39;张人大&39;运动& 报错：Duplicate entry &39; for key &39;INSERT INTO demo01 VALUES(2，&39;，&39;);

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?

sql server中建立外键约束有3中方式：enterprise manager中，tables,design table，设置table的properties,可以建立constraint, reference key;enterprise manager中，diagrams, new diagrams,建立两个表的关系；直接用transact sql语句。

视图是一种虚拟表。

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

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

方法如下：添加主键 ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY;添加外键 ALTER TABLE table_name ADD CONSTRAION pk_name FOREIGN KEY(列名) REFERENCES table_name(列名);这些都是基础语法，其他条件自行。

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

Function of Database View

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

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

Using the View

Create View

Comparison Between View and Table

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

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

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

Stored Procedure

What is Stored Procedure in MySQL?

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

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

1、首先新建一张表，设置主键字段，这个等下要关联另外表的外键，如图。2、然后在新建一张表，添加一个外键的字段，如图。3、点击外键功能，切换页面如图，这里就可以设置外键了。4、然后在弹出的对话框中，先取个外键的。

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

Pros and Cons of Stored Procedure

Pros

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

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

Cons

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

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

Create Procedure

-- 创建商品表与订单表 订单表，price 为订单价格CREATE TABLE orders(  oid INT， gid INT， price INT);39;奶茶&39;绿茶&39;花茶& 查询所有商品数据 接收一个商品 id，根据 id 删除数据DELIMITER $$  CREATE PROCEDURE goods_proc02(        IN goods_id INT  )  BEGIN   DELETE FROM goods   WHERE gid = goods_id;END $$-- 调用存储过程传递参数，删除对应的商品CALL goods_proc02(2);-- 使用输出参数创建存储过程 执行插入操作    INSERT INTO orders VALUES(o_oid，o_gid，o_price);     返回 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，&39;，40);-- 编写触发器 修改结束标识，避免执行出现错误DELIMITER $ 指定触发的时机，和要监听的表 AFTER INSERT ON orders触发后具体要执行的事件BEGIN     添加后，触发器执行，库存自动减一INSERT INTO orders VALUES(1，1，25);

Data Control Language -- DCL

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

-- 创建 admin1 用户39;admin1&39;localhost&39;123456& 可以在任何电脑上登录 mySQL 服务器，密码为 123456CREATE USER &39;@&39; IDENTIFIED BY &39;;-- 给 admin1 用户分配对 db4 数据库中 products 表的查询权限GRANT SELECT ON db4.products TO &39;@&39;;-- 给 admin2 用户分配所有权限和操作所有数据库的所有表GRANT ALL ON *.* TO &39;@&39;; -- 查看 root 用户的权限39;root&39;localhost&39;admin1&39;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 文件地址