前言
本文为在霍格沃兹测试开发学社的学习经历分享，希望有志同道合的小伙伴可以一起交流技术，一起进步~
多表查询
概念
-
多表就是在数据库设计中使用多张表格来实现数据存储的要求
-
在实际的项目开发中，数据量大而且复杂，需要分库分表
-
分表：按照一定的规则，对源有的数据库和表进行拆分
-
表与表之间可以通过外键建立连接
多表的优点：
-
简化数据
-
提高复用性
-
方便权限控制
-
提高系统的稳定性和负载能力
-- eid员工ID（自增主键）
-- ename 员工姓名
-- age 年龄
-- gender 性别
-- dept_name 所在部门
-- dept_id 部门ID
-- dept_manager 部门主管
-- dept_location 所在地点
create table emp_part(
eid int primary key auto_increment,
ename varchar(20),
age int,
gender varchar(5),
dept_id int
);
insert into emp_part values(1,'张三',20,'男',1);
insert into emp_part(ename,age,gender,dept_id) values('李四',30,'女',1);
insert into emp_part(ename,age,gender,dept_id) values('王五',28,'女',1);
insert into emp_part(ename,age,gender,dept_id) values('赵六',25,'男',2);
create table emp_dept(
dept_id int primary key auto_increment,
dept_name varchar(10),
dept_manager varchar(10),
dept_location varchar(10)
);
insert into emp_dept values(1,'研发部','郭靖','北京');
insert into emp_dept values(2,'人事部','黄蓉','四川');
外键约束
-
主键：可以唯一标识一条记录的列
-
外键：从表中与主表的主键对应的字段
-
主表：外键所指向的表，约束其他表的表
-
从表：外键所在的表，被约束的表
-
价值：建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性
示例：部门表里的部门id如果不存在，那在员工表就不应该出现不存在的部门id
主表就是部门表，从表就是员工表
建立外键约束
删除外键约束
注意：
-
建立外键连接时，从表外键数据类型要与主表的主键保持一致
-
删除数据时，需要先删除从表数据再删除主表的数据
-
添加数据时，先添加主表数据，再添加从表数据
级联删除
例如：删除主键的部门信息以后，从表中对应部门的员工信息也一并删除
如果对员工信息进行删除，工作量会很大，所以可以用级联删除
-- 建立外键约束
alter table emp_part add CONSTRAINT emp_dept foreign key(dept_id) references emp_dept(dept_id)
-- 建立外键约束并指定可以级联删除
alter table emp_part add CONSTRAINT emp_dept foreign key(dept_id) references emp_dept(dept_id) on delete cascade;
-- 删除外键约束
alter table emp_part drop foreign key emp_dept;
-- 级联删除
delete from emp_dept where dept_id=1;
注意：如果已经有了外键约束但没指定级联删除，需要先把外键约束删掉，再重新添加带级联删除的外键约束
多表关系
一对多
-
定义：主表的一条记录可以对应从表的多条记录
-
例子：部门表、员工表
-
建表原则：在一对多的关系中，多的表定位从表，设置外键指向主表
多对多
-
定义：主表的多条记录可以对应从表的多条记录
-
例子：商品信息表，客户表，订单表
-
建表原则：需要建立第三张表作为中间表，中间表需要包含两张表的主键
一对一
-
定义：从表的一条记录对应主表的一条记录
-
例子：员工信息表与身份证表，联系方式
-
建表原则：这种对应关系的数据，通常放在单表中
内连接查询
-
内连接（INNER JOIN）：使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。匹配上显示，匹配不上则不显示
-
例子：比如使用外键=主键这个条件过滤掉无效的数据
-
按语法结构分为：隐示内连接和显示内连接
例子:
SELECT
*
FROM
emp_part,
emp_dept
WHERE
emp_dept.dept_id = emp_part.dept_id
AND emp_dept.dept_id = 2;
隐示内连接
区分：没有明显出现内连接关键字 inner join 就是隐示内连接
下面的id字段是员工信息表的外键，上面叫dept_id，但和部门表冲突，所以更改为id。如果字段名称相同时想要查找的话，需要表名.列名去分别指定。
显示内连接
SELECT
dept_id,
dept_location,
ename
FROM
emp_dept
INNER JOIN emp_part ON dept_id=id
AND dept_id = 1;
外连接查询
-
概念：查询多个表中相关联的行，有时候需要包含没有关联的行中数据，即返回查询结果集合中不仅包含符合连接条件的行，还包括左表（左连接）、右表（右连接）中的所有数据行
-
左外连接：使用LEFT OUTER JOIN ，OUTER 可以忽略
-
右外连接：使用RIGHT OUTER JOIN ，OUTER 可以忽略
左连接时，想让哪个表的数据都显示出来，就让哪个放在左表中
- 使用左连接的查询方式查询出所有部门员工的员工号，姓名，性别以及他们所在的部门名称和城市
-- 使用左连接的查询方式查询出所有部门员工的员工号，姓名，性别以及他们所在的部门名称和城市
SELECT
eid,
ename,
gender,
dept_name,
dept_location
FROM
emp_dept
LEFT JOIN emp_part ON id = dept_id;
右连接时，想让哪个表的数据都显示出来，就让哪个放在右表中
-- 右连接的方式查询所有员工信息以及他们所在部门的名称和城市
SELECT
eid,
ename,
age,
gender,
dept_name,
dept_location
FROM
emp_dept
right JOIN emp_part ON id = dept_id;
总结：
推荐阅读文章链接：接口测试经典面试题：Session、cookie、token有什么区别？_霍格沃兹测试开发学社的博客-CSDN博客
原文地址：http://www.cnblogs.com/joker108/p/16818224.html
