SQL自学总结四 MySQL多表操作

目录

一 多表关系

1 一对一关系

 2 一对多/多对一关系

 3 多对多关系

4 外键约束 

在创建表时设置外键约束

在创建表后设置外键约束

多对多关系的外键约束

二 多表联查

1 交叉连接查询

2 内连接查询

3 外连接查询

4 子查询

5 子查询关键字

子查询关键字 all

子查询关键字 any和some

子查询关键字 in

子查询关键字 exists

6 自关联查询

---

一 多表关系

实际开发中，一个项目表通常需要多张表才能完成。例如一个商城项目就需要分类表，商品表，订单表等多张表，且这些表的数据之间存在一定关系。MySQL多表之间的关系可以概括为：一对一，一对多，多对多。

1 一对一关系

例如，一个学生只有一张身份证，一张身份证只能对应一个学生。可在任一表中添加唯一外键，指向另一方主键，确保一对一关系。一般一对一关系很少见，遇到一对一关系的表最好是合并表。

 2 一对多/多对一关系

例如，一个部门有多个员工，一个员工只能对应一个部门。在多的一方建立外键，指向一的一方的主键

 3 多对多关系

例如，一个学生可以选择很多门课程，一个课程可以被多个学生选择。多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的两张表的主键

4 外键约束 

MySQL外键约束（foreign key）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表，外键所在的表就是从表。外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

 定义一个外键时要遵守以下规则

• 主表必须已经存在于数据库中，或者是当前正在创建的表
• 必须为主表定义主键
• 主键不能包含空值，但允许外键中包含空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 在主表的表名后面指定列名或者列名的组合，这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表中主键列的数目相同。
• 外键中列的数据类型必须和主表主键中的对应列的数据类型相同。

操作-创建外键约束：在create table 语句中，通过foreign key关键字来指定外键

语法格式：[constraint <外键名>] foreign key 字段名 [, 字段名2, .....] references <主表名> 主键列 [, 主键列2, ......]

在创建表时设置外键约束

create database mydb3;
use mydb3;
-- 创建外键约束
-- 创建部门表 主表
create table if not exists dept(
deptno varchar(20) primary key,# 部门号
name varchar (20) # 部门名
);
-- 创建员工表 从表，并创建外键约束
-- [constraint <外键名>] foreign key 字段名 [,字段名2...] references <主表名> 主键列1 [,主键列2...] 
create table if not exists emp(
eid varchar(20) primary key,
ename varchar(20),
age int,
deptid varchar(20),#所属部门
constraint empfk foreign key(deptid) references dept(deptno)
);

在创建表后设置外键约束

外键约束可以在修改表时添加，添加外键约束的前提是：从表中的外键列数据必须与主表中主键列数据一致或者没有数据。

语法格式：alter table <数据表名> add constraint <外键名> foreign key (列名) references <主表名> (列名)

-- 创建部门表2  主表
create table if not exists dept2(
deptno varchar(20) primary key,# 部门号
name varchar (20) # 部门名
);
-- 创建员工表2 从表
create table if not exists emp2(
eid varchar(20) primary key,
ename varchar(20),
age int,
deptid varchar(20)#所属部门
);
alter table emp2 add constraint emp2fk foreign key(deptid) references dept2(deptno);

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

删除外键约束

alter table emp2 drop foreign key	emp2fk;

多对多关系的外键约束

-- 学生表和课程表(多对多)
  -- 1 创建学生表st