MySQL 多表操作

一.多表关系

1.一对一关系

一个学生只有一张身份证；一张身份证只能对应一个学生。

在任一表中添加外键，指向另一方主键，确保一对一关系。

一般一对一关系很少见，遇到一对一关系的表最好合并。

2.一对多/多对一关系

一个部门有多个员工，一个员工只能对应一个部门。

实现原则：在多的一方建立外键，指向一的一方的主键。

3.多对多关系

一个学生可以选择很多课程，一个课程也可以被很多学生选择。

实现原则：多对多关系实现需要借助第三章中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键。

二.外键约束（Foreign Key）

外键约束是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表，外键所在的表就是从表。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

定义一个外键时，需要遵循下列规则：

1.主表必须已经存在于数据库中，或是当前正在创建的表。

2.必须为主表定义主键

3.主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

4.外键中列的数目必须和主表中主键的列的数目相同且数据类型也要相同。

三.创建外键约束

1.创建表时设置外键约束

create database mydb3;
use mydb3;
create table if not exists dept(
deptno varchar(20) primary key,-- 部门号
name varchar(20)-- 部门名字
);
create table if not exists emp(
eid varchar(20) primary key, -- 员工编号
ename varchar(20),-- 员工名字
age int,-- 员工年龄
dept_id varchar(20), -- 员工所属部门
constraint emp_fk foreign key (dept_id) references dept(deptno) -- 外键约束
);

2.创建表后设置外键约束

alter table emp add constraint dept_fk foreign key(dept_id) references dept(deptno);

四.在外键约束下的数据操作

1.数据插入

必须先给主表添加数据。

给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列。

2.数据删除

主表的数据被从表依赖时，不能删除，否则可以删除。

从表的数据可以随便删除。

delete from emp where eid='7';

3.删除外键约束

外键一旦删除，就会解除主表和从表间的关系。

alter table emp drop foreign key dept_fk;

五.外键约束——多对多关系

增加一个中间表，来建立多对多关系。

先建立左侧主表和右侧主表，再建立中间表（从表）。

建立外键约束2次。

alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);

六.多表联合查询

1.交叉连接查询

交叉连接查询返回被连接的两个表所有数据行的笛卡尔积。

笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配。

假如A表有m行数据，B表有n行数据，则返回m*n行数据。

格式：select * from 表1，表2，……；

2.内连接查询

内连接查询求多张表的交集。

格式：

隐式内连接

select * from A,B where 条件；

显示内连接

select * from A inner join B on 条件；

3.外连接查询

外连接分为左外连接（left outer join）、右外连接（right outer join）、满外连接（full outer join）。

左外连接：select * from A left outer join B on 条件;

右外连接：select * from A right outer join B on 条件;

满外连接：select * from A full outer join B on 条件;

select * from dept3 left outer join emp3 on deptno=dept_id;
select * from dept3 left outer join emp3 on deptno=dept_id 
union select * from dept3 right outer join emp3 on deptno=dept_id;

MySQL 对于full outer join的支持不好，所以采用union。

union 去重 union all 没有去重

4.子查询

子查询就是指在一个完整的查询语句之中，嵌套若干个不同功能的小查询。

子查询返回的数据类型分为4种：

1.单行单列

2.单行多列

3.多行单列

4.多行多列

select * from emp3 where age=(select max(age) from emp3);

select * from dept3,emp3 where deptno=dept_id and name in ('研发部' ,'销售部');

select * from dept3 join emp3 on deptno=dept_id and (name='研发部' and age <20);

5.子查询关键字

1）all

格式：select ... from...where c>all（查询语句）

查询年龄大于1003部门所有年龄的员工信息

select * from emp3 where age>all(select age from emp3 where dept_id='1003');

 查询不属于任何一个部门的员工信息

select * from emp3 where dept_id!= all(select deptno from dept3);

2）any和some

some和any的作用一样

查询年龄大于‘1003’部门任意一个员工年龄的员工信息

select * from emp3 where age >any(select age from emp3 where dept_id='1003');

3）in

用于判断某个记录的值是否在指定的集合中。

在in前面加not可以将条件反过来。

查询研发部和销售部的员工信息

select * from emp3 where dept_id in (select deptno from dept3 where name in 
('研发部','销售部');

4）exists

exists后面的子查询不返回任何实际数据，只返回真或假。

exists运算效率比in高。

查询公司是否有大于60岁的员工，有则输出。

select * from emp3 a where exists(select * from emp3 where a.age >60);

一行一行查询

查询有所属部门的员工信息

select * from emp3 a where exists(select * from dept3 b where a.dept_id=b.deptno);

6.自关联查询

对表自身进行关联查询，一张表当作多张表使用。

自关联时必须要起别名。

select 字段列表 from 表1 a，表1 b where 条件；

select 字段列表 from 表1 a left join 表1 b on 条件；

七.总结