MySQL-4多表操作

MySQL-4多表操作

多表关系

一对一

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

一对多/多对一

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

• 部门和员工

  • 分析：
  • 一个部门有多个员工，
  • 一个员工只能对应一个部门

多对多

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

外键约束

介绍

• MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，
  • 经常与主键约束一起使用。
• 对于两个具有关联关系的表而言，
  • 相关联字段中主键所在的表就是主表（父表），
  • 外键所在的表就是从表（子表）。
• 外键用来建立主表与从表的关联关系，
  • 为两个表的数据建立连接，
  • 约束两个表中数据的一致性和完整性。
  • 比如：
    • 一个水果摊，只有苹果、桃子、李子、西瓜等 4 种水果，
    • 那么，你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜，
    • 其它的水果都是不能购买的。

特点

定义外键时，遵守下列规则

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

操作-创建外键约束

方式1-在创建表时设置外键约束

在 create table 语句中，通过 foreign key 关键字来指定外键，具体的语法格式如下：

[constraint <外键名>] foreign key 字段名 [，字段名2，…] references <主表名> 主键列1 [，主键列2，…]

实现

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 <数据表名> add constraint <外键名> foreign key(<列名>) references 
<主表名> (<列名>);

实现

-- 创建部门表
create table if not exists dept2(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp2(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
 
);

-- 创建外键约束
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);

数据插入

添加主表数据

 -- 1、添加主表数据
 -- 注意必须先给主表添加数据
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部');

添加从表数据