MySQL数据库---表关系与多表查询

表关系

1. 一对一

• 不常用, 因为一对一的两张表可以合并成一张表.

2. 一对多

• 建表原则: 在多的一方创建外键, 指向一的一方的主键(从表创建外键指向主表的主键)
• 例如: 分类和商品, 用户和订单

3. 多对多

• 建表原则: 创建一张第三方表, 这张表至少有两个字段, 分别作为外键, 指向另外两张表的主键
• 例如: 老师和学生, 学生和课程

拆表之后

• 从表（商品种类数据来源于主表）

商品编号pid	商品名称pname	商品价格price	商品库存pcount	商品种类cid(外键)
1	华为P40	5688	500	1
2	小米12	3688	800	1
3	格力空调	2399	300	2
4	创维电视	2099	200	2

• 主表

cid(主键)	cname
1	手机数码
2	大型家电
3	鞋靴箱包

外键约束

主表(parent)和从表(child)

• 当表①中的数据是来源于另一张表②, 那我们就可以将表①称之为从表, 表②称之为主表
  • 能够单独存在的表就是主表
  • 不能单独存在(有一些数据不是到是什么)就是从表

外键和外键约束

• 外键: 从表中的某个字段是来源于主表的, 那么这个字段就称之为外键

• 外键约束: 设置成外键的字段, 一定是来源于主表的主键, 让这两个字段产生关联

• 添加外键约束的格式:

  constraint 外键约束名 foreign key(外键字段) reference 主表名(主键字段)
  

• 删除外键

  alter table ... drop foreign key 外键约束名;
  

• 添加外键

  alter table ... add constraint 外键约束名 foreign key(外键字段) reference 主表名(主键字段);
  

外键的操作

​ 修改主表中的外键字段, 从表跟着改变

• 直接将这两个代码, 添加到外键约束的后面

on delete cascade
on update cascade

外键约束, 添加级联操作

constraint fk_pro_cid_cat_cid foreign key(cid) references category(cid) on delete cascade on update cascade 

多表查询【重点】

• 多表查询的关键在于: 把多表关联起来, 关联的时候一定要有条件

1. 笛卡尔积

• 查询每个部门有哪些人

  • 由于部门和员工信息存在两张表中, 所以需要从两张表中查询

  select * from emp, dept;
  

  • 清除笛卡尔积(添加连接条件)

  select * from emp, dept where emp.dept_id = dept.id;
  

2. 内连接

• 隐式内连接

  格式

  select * from 表1, 表2 where 条件
  

• 显式内连接

  格式

  select * from 表1 inner join 表2 on 连接条件 where 其他过滤条件
  

隐式内链接和显式内连接查询的结果是一样, 只是格式不同

3. 外连接

• 查询效果: 查询一张表的全部数据, 以及另一张表的关联数据

3.1 左外连接

• 查询左表的所有数据, 以及右表相关联的数据

• **格式: **

  select ... from 左表 left outer join 右表 on 关联条件 where 其他过滤条件
  

谁写在左边(前面)就是左表, 谁写在右边(后面)就是右表

数据准备

-- 在部门表中添加一个销售部
INSERT INTO dept (NAME) VALUES ('销售部');

-- 左外连接
-- select * from 左表 left outer join 右表 on 连接条件
select * from dept d left outer join emp e on e.dept_id = d.id;

3.2 右外连接

• 查询右表的所有数据, 以及左表相关联的数据

• 格式

  select ... from 左表 right outer join 右表 on 关联条件 where 其他过滤条件
  

-- 右外连接
-- select * from 左表 right outer join 右表 on 连接条件
select * from emp e right outer join  dept d on e.dept_id = d.id;

左外连接和右外连接查询出的结果是一样的(改变表的顺序)

4. 子查询

• 子查询==(双层嵌套查询)==, 没有固定的语法, 只是一种查询技巧
• 特点
  1. 子查询必须放在()中
  2. 子查询的结果有3种情况
     • 子查询的结果是一个值
     • 子查询的结果是单列多行
     • 子查询的结果是多列多行(表)
  3. 子查询的结果是单列, 在where后面作为条件(一个值: >, <, =; 单列多行: in())
  4. 子查询的结果是多列, 在from后面作为表

4.1 子查询的结果是一个值

• 查询工资最高的员工是谁

-- 1. 查询出最高工资是多少(子查询)
select max(salary) from emp;     把它当成一个值-----最高工资(9000)
-- 2. 查询是最高工资的员工
select * from emp where salary = (select max(salary) from emp);

• 查询工资小于平均工资的员工有哪些

-- 1. 查询出平均工资
select avg(salary) from emp;
-- 2. 查询出小于平均工资的员工
select * from emp where salary < (select avg(salary) from emp);

4.2 子查询的结果是单列多行

• 查询工资大于5000的员工, 来自哪些部门

-- 查询工资大于5000的员工, 来自哪些部门
-- 1. 查询工资大于5000的员工所在的部门id
select dept_id from emp where salary > 5000;
-- 2. 在部门表中, 查询出对应id的部门名称
select name from dept where id in(select dept_id from emp where salary > 5000);

• 查询开发部和财务部所有员工的信息

-- 1. 查询开发部和财务部对应的部门id
select id from dept where name = '开发部' or name = '财务部'; -- (1, 3)
-- 2. 在员工表中, 查询出部门id是子查询结果的
select * from emp where dept_id in(select id from dept where name = '开发部' or name = '财务部');

4.3 子查询的结果是多行多列

• 查询出2011年以后入职的员工信息, 包括部门名称

-- 1. 查询出2011年以后入职的员工信息
select * from emp where join_date >= '2011-1-1'
-- 2. 虚拟表和部门表进行连接查询, 查询出部门名称
-- 沙僧的部门id是null, 内连接查询不到这条数据
select e.id, e.name, e.salary, d.name, e.join_date from dept d , (select * from emp where join_date >= '2011-1-1') e where e.dept_id = d.id;

-- 使用外连接查询出沙僧
select e.id, e.name, e.salary, d.name, e.join_date from dept d right join (select * from emp where join_date >= '2011-1-1') e on e.dept_id = d.id;