Notes 多表操作

多表操作

 

常见的表关系

一对一、一对多（多对一）、多对多

外键约束

限定二张表有关系的数据，保证数据的正确性、有效性和完整性

语法

create table 表名(
    列名 数据类型 约束名,
    列名 数据类型 约束名,
    [constraint] [约束名] foreign key(外键列) references 主表(主键)
);  

已有表添加外键

alter table 表名 add [constraint] [约束名] foreign key(外键列) references 主表(主键);

删除外键约束

alter table 表名 drop foreign key 约束名;

笛卡尔积

a表中的每一条记录，都和b表中的每一条进行匹配连接。 会造在数据量级的增长

解决笛卡尔积现像，给足条件

注意事项

1） 主表不能删除从表已引用的数据
2） 从表不能添加主表未拥有的数据
3） 先添加主表数据再添加从表数据
4） 先删除从表数据再删除主表数据
5） 外键约束允许为空但不能是错的
​
== 主外键关系: 注意事项 ==
-- 前者：建立【实际的主外键关系】 和 后者：建立【逻辑主外键关系】 的区别？
-- 前者有约束，必须按照外键约束来写
-- 后者没有外键约束，但你不能写主键不存在
-- 前者删除受主外键约束，后者删除不再受主外键约束
-- 前者可能会造成级联删除， 后者不会造成级联删除
​
> 在实际开发中，一般不建立实际的外键关系，建立逻辑外键关系
> 在互联网公司中，不建立实际的外键关系，在实际传统行业中，需要建立实际的外键关系

多表关系案例

多表设计

步骤

略

多表查询

内链接查询

必须表与表之间的关系一一对应 ，不是一一对应的则不会显示出来

隐式内连接查询

select * from a,b where a.a_id = b.a_id;

显式内连接查询

select * from a inner join b on a.a_id = b.a_id

外链接

左外连接

用left关键字链接的两个表，以left左表为主表，左表查询的内容无论与left右表是否有对应关系都要全部显示出来，右表没有对应关系的则不显示

写法：select * from a left [outer] join b on a.a_id = b.b_id

右外连接

和left一样，只不过把左表换成了右表

写法：select * from a right [outer] join b on a.a_id = b.b_id

全外连接

会把要查的两侧的内容都显示出来

方式一

union 是把两个查询的结果进行合并。 去掉重复的合并

select * from teacher left join course on teacher.t_id = course.t_id union select * from teacher right join course on teacher.t_id = course.t_id;

方式二

union all 把两个结果集都显示出来，不会合并去重复

select * from teacher left join course on teacher.t_id = course.t_id union all select * from teacher right join course on teacher.t_id = course.t_id;

注意

写法： select * from a full join b on a.a_id = b.a_id 但是 mysql 不支持 full 关键字 全外连接。

关联子查询

关联子查询：将一个查询结果当作另一个查询的条件的一部分部分或者当成查询过程中的一个表

例子

==子查询结果为单行单列==

-- 【1】查询工资最高的员工是谁？ 
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

-- 【2】查询工资小于平均工资的员工有哪些？
-- 2.1 先求出平均工资
SELECT AVG(salary) FROM emp;
-- 2.2 查询低于平均工资的员工
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

==子查询结果为单列多行==

-- 【1】查询工资大于5000的员工，来自于哪些部门的名字  
-- 1.1 查询工资大于5000的员工
SELECT dept_id FROM emp WHERE salary >5000;
-- 1.2 来自于哪些部门的名字  
SELECT * FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000);

-- 【2】查询开发部与财务部所有的员工信息
-- 2.1 根据部门名称，查询部门主键
SELECT id FROM dept WHERE name IN('开发部','财务部');
-- 2.2 根据部门id查询员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name IN('开发部','财务部'));
​

==子查询结果为多列多行==

-- 【1】 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 1.1 根据 join_date 查询 2011-11-11 之后的员工信息
SELECT * FROM emp WHERE join_date > '2011-11-11';
​
-- 1.2 根据1步骤的结果，跟部门表关联
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;