前言

本文为在霍格沃兹测试开发学社的学习经历分享，希望有志同道合的小伙伴可以一起交流技术，一起进步~

多表查询

概念

多表就是在数据库设计中使用多张表格来实现数据存储的要求
在实际的项目开发中，数据量大而且复杂，需要分库分表
分表：按照一定的规则，对源有的数据库和表进行拆分
表与表之间可以通过外键建立连接

多表的优点：

简化数据
提高复用性
方便权限控制
提高系统的稳定性和负载能力

-- 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;

总结：

数据库学习笔记目录导航

标签：多表,--,连接,数据库,外键,查询,dept,emp,id
From： https://www.cnblogs.com/joker108/p/16818224.html

数据库（七）多表查询

前言