MySql之多表

一、多表关系

1.1 多表关系概述

现实生活中，实体与实体之间肯定是有关系的，所以设计表时也应该体现出表与表之间的关系。

表与表之间的关系：

• 一对多，如一个部门对应多个员工
• 多对一，如多个订单对应一个用户
• 多对多，如一个老师对应多个学生，一个学生对应多个老师
• 一对一，如一个公民对应一张身份证

1.2 多表关系的实现

1.2.1 一对多&多对一

在一对多中,把一的一方称之为一表或者主表, 把多的一方称之为多表或者从表。

实现一对多的方式是在从表上添加外键。

外键：从表中有一列，该列的取值只能来源于主表的主键或者是null。

如商品和分类：

代码实现:

#一对多:  商品和分类  商品表product
CREATE TABLE product(
 pid INT PRIMARY KEY , 
 pname VARCHAR(32),
 cid INT 
);
#category 分类表
CREATE TABLE category(
 cid INT PRIMARY KEY , 
 cname VARCHAR(32)
);

#分类表
INSERT INTO category VALUES(1, '生活类');
INSERT INTO category VALUES(2, '科技类');
#商品表
INSERT INTO product VALUES(1, '牙膏' , 1);
INSERT INTO product VALUES(2, '牙刷' , 1);
INSERT INTO product VALUES(3, '手机' , 2);
INSERT INTO product VALUES(4, '电脑' , 2);
SELECT * FROM category;
SELECT * FROM product;

1.2.2 一对一

在开发中一对一不常见。

常见的解决方案：

1. 字段不多的话,两个实体可以放到一个表中
2. 唯一外键对应：从表中添加外键字段，给外键字段添加非空和唯一约束
3. 主键对应：把主键字段同时设置为外键

1.2.3 多对多

通过第三张中间表，中间表至少包含两列，这两列作为中间表的外键，分别关联两张表的主键。

多对多可以看成两个一对多，任何一方和中间表的关系都是一对多，如果没有中间表，则两张表没有关系。

代码实现:

#学生表  
CREATE TABLE student(
 sid INT PRIMARY KEY , 
 sname VARCHAR(32)
);
#课程表 class
CREATE TABLE class(
 cid INT PRIMARY KEY , 
 cname VARCHAR(32)
);

#中间表
CREATE TABLE student_class(
  sid INT , 
  cid INT
);


INSERT INTO student VALUES(1, 'jack');
INSERT INTO student VALUES(2, 'rose');
INSERT INTO class VALUES(1, 'java');
INSERT INTO class VALUES(2, 'ui');

INSERT INTO student_class VALUES(1,1);
INSERT INTO student_class VALUES(1,2);

INSERT INTO student_class VALUES(2,1);
INSERT INTO student_class VALUES(2,2);

1.3 外键约束

外键约束: 用来约束表中字段书写的内容

约束的作用: 规范数据的编写, 保证数据的有效性和完整性

加入外键约束的方式：

1. 通过修改表结构添加外键约束

alter table 多表名字 add foreign key(外键字段名) references 一表名字(主键名字);

alter table emp add foreign key(dept_id) references dept(id);

2. 在建表的时候在约束区域中添加外键约束（几乎不用）

create table 表名(
	字段名称 类型,
    字段名称 类型,
    constraint FK_ORDERS_USERID   foreign key (user_id)      references      user(id)
    关键字	   外键名称（自己起）   外键(关键字） 外键字段名称    关联（关键字）   主表名称     主表主键
);

-- 学生课程中间表
create table stu_course(
    sc_id int primary key auto_increment,
	s_id int,
    c_id int,
    constraint FK_STU_COURSEID foreign key (s_id) references stu(id),
    constraint FK_STU_COURSEID foreign key (c_id) references course(id)
);

外键约束的特点:

• 主表中不能删除已被子表引用的数据
• 从表中不能添加主表中不存在的数据
• 外键的值可以为null

二、多表操作

2.1 增操作&改操作

• 主表：单表操作，不考虑从表

• 从表：可以提供主表信息

如学生和班级，一个班级对应多个学生，一个学生对应一个班级；添加班级时不会考虑学生信息，但学生入学或转班需要添加班级信息。

2.2 删除操作

• 主表：

    有从表引用：
    
    	- 从表数据在主表删除之后还有保留的价值：先把从表中引用的数据解除引用，再删除主表
    	- 从表数据在主表删除之后没有保留的价值：先删除从表数据，再删除主表数据
    
     没有从表引用：单表操作
  

• 从表：单表操作

2.3 多表查询

多表查询：同时查询多张表，返回需要的数据

2.3.1 笛卡尔积

多表查询时左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔积，是多表查询的基础。

格式：
select * from 表1,表2

select * from emp,dept

结果：
dept表中有4条记录,emp表中有6条记录,笛卡尔积的结果是 4*6=24 条记录

2.3.2 内连接

内连接是在在笛卡尔积的基础上进行筛选，找到满足条件的数据。

显示内连接：

select * | 字段 
from a  [inner] join b 
on a和b关联条件 [inner] join c on c和上面结果(ab的结果)的关联条件
where 其他条件

-- 查询张三的 id，姓名，性别，工资和所在部门名称
SELECT emp.id,emp.name,emp.gender,emp.salary,dept.name
FROM emp
JOIN dept
ON emp.dept_id = dept.id
WHERE emp.name = '张三'	;

-- 为了方便写sql语句,可以给表起别名 :    表名 [as] 别名
SELECT e.id,e.name,gender,salary,d.name
FROM  emp e
JOIN dept d
ON e.dept_id = d.id
WHERE e.name = '张三';

隐式内连接：

select * | 字段
from a,b,c
where 关联条件和其他查询条件

SELECT e.id,e.name,gender,salary,d.name
FROM emp e,dept d
WHERE e.dept_id = d.id AND e.name = '张三';

2.3.3 外连接

外连接分为左外连接和右外连接。

左外连接：先展示左边表中的所有数据,然后根据关联条件去查询右表中的数据,右表中的数据如果满足条件就会展示,否则就会显示null。

select * | 字段
from a 
left [outer] join b
on a和b的关联条件
where 其他查询条件

-- 查询所有员工信息及对应的部门名称
SELECT e.*,d.name
FROM emp e
LEFT JOIN dept d
ON e.dept_id = d.id

右外连接 : 先展示右边表中的所有数据,然后根据关联条件去查询左表中的数据,左表中的数据如果满足条件就会展示,否则就会显示null。

select * | 字段
from a 
right [outer] join b
on a和b的关联条件
where 其他查询条件

-- 查询所有部门及对应的员工信息
SELECT d.*,e.*
FROM emp e
RIGHT JOIN dept d
ON e.dept_id = d.id

-- 右外转换成左外
SELECT d.*,e.*
FROM dept d
LEFT JOIN emp e
ON e.dept_id = d.id

2.3.4 外链接和内连接的区别

2.3.5 子查询

当一个查询需要依赖于另外一个查询的结果的时候,我们就把另外的这个查询称之为子查询。

即一个子查询的结果可以作为另一个查询语句的一部分。

子查询的查询结果:

• 单行单列的值:一般作为条件使用
• 多行单列的值:一般作为条件使用
• 多行多列的值:作为临时表使用

-- 1 查询工资最高的员工是谁？ 
#子查询:将一条sql语句的执行结果 作为了另一条sql语句的一部分(作为了条件)
#1.1 查询最高工资
INT money = SELECT MAX(salary) FROM emp;
#1.2 查询员工
SELECT * FROM emp WHERE salary = money;

SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

#作为表存在 
#查询出2011年以后入职的员工信息，包括部门名称
SELECT * FROM emp e 
INNER JOIN dept d ON e.dept_id = d.id
WHERE e.join_date > '2011-01-01';
	
	
SELECT * FROM (
	SELECT em.name,em.salary,em.join_date,em.dept_id 
		FROM emp em 
		WHERE em.join_date>'2011-01-01'
	) e  
INNER JOIN dept d ON e.dept_id = d.id;

2.3.6 自关联

关联查询的表还是本身,这种情况我们称之为自关联

查询当前员工和上级领导的名字：

#  emp e  普通员工   emp em  领导
SELECT e.id, e.ename , e.mgr , em.id , em.ename FROM emp e 
LEFT JOIN emp em ON e.mgr=em.id;

2.4 SQL执行顺序

select [distinct] 字段1,字段2 | [聚合函数]
from 表1
[join 表2 on 连接条件]
[join 表3 on 连接条件]
where 查询条件
[group by 分组字段 having[分组后条件筛选]]
[order by 排序字段 排序方式]
[limit m,n]

执行顺序:

1. 先执行from,确定从那些表中查询数据
2. join操作
3. 执行where筛选
4. group by 分组 ,分组函数执行,聚合函数就执行了
5. having 筛选
6. select 决定要显示那些字段
7. distinct操作
8. order by 排序操作
9. limit 分页