标签：级联 name -- mysql sec user key MySQL id

ⅠMySQL约束条件

【一】什么是约束条件

约束条件：限制表中的数据，保证添加到数据表中的数据准确和可靠性！凡是不符合约束的数据，插入时就会失败！
约束条件在创建表时可以使用，也可以修改表的时候添加约束条件

【二】约束条件概览

null 和 not null 为空和不为空
限制整数类型必须大于等于0（unsigned）
唯一性约束，数据只能出现一次（unique）
默认用0填充至指定的数据长度（zerofill）
组合使用 not null 和 unique，相当于主键约束PK（primary key）
主键约束PK（primary key），增加查询效率
外键约束FK（foreign key），一张表和另一张表的字段之间的关联关系
级联更新与级联删除

ps:创建表结构时，约束条件直接跟在字段后面的为列级约束，若约束条件是用括号时为表级约束。

使用表级约束给多个字段联合添加约束，如：unique（name,email）名字和邮箱这两个字段不能同时重复。

Ⅱ 非空/空约束（not null/null）

【1】作用

-- not null ： 限制当前字段插入值的时候不能为空
not null约束的字段不能为 NULL 值，必须赋具体数据；

【2】示例

create table user(
  id int(10) ,
  name varchar(32) not null,
  email varchar(128)
);

# 创建一个表
mysql> create table user(
    ->   id int(10) ,
    ->   name varchar(32) not null,
    ->   email varchar(128)
    -> );
Query OK, 0 rows affected (0.32 sec)

# 查看表
mysql> desc user;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(32)  | NO   |     | NULL    |       |
| email | varchar(128) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 插入数据
mysql> insert into user(id,name,email) values(1,'silence',741);
Query OK, 1 row affected (0.12 sec)
 
mysql> select * from user;
+------+---------+-------+
| id   | name    | email |
+------+---------+-------+
|    1 | silence | 741   |
+------+---------+-------+
1 row in set (0.00 sec)

# not null约束的字段不能为 NULL 值，必须赋具体数据；不然会报错
mysql> insert into user(id) values(13);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

【一】唯一性约束（unique）

【1】作用

unique约束的字段具有唯一性，不可重复，但是可以为空（null）。

【2】示例

修改之前的user表结构，把email设置为唯一性

语法

alter table user modify email varchar(128) unique;

insert into user(id,name,email) values(3,'happy',741);

示例

# 修改之前的user表结构，把email设置为唯一性
mysql> alter table user modify email varchar(128) unique;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 查看表是否是已经设置为unique
mysql> desc user;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(32)  | NO   |     | NULL    |       |
| email | varchar(128) | YES  | UNI | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 查看原来表中email数据
mysql>  select * from user;
+------+---------+-------+
| id   | name    | email |
+------+---------+-------+
|    1 | silence | 741   |
+------+---------+-------+
1 row in set (0.00 sec)

# 插入数据 跟原来数据email数据相同  报错
mysql> insert into user(id,name,email) values(3,'happy',741);
ERROR 1062 (23000): Duplicate entry '741' for key 'email'

【二】组合使用not null + unique

【1】not null 和 unique 单独使用(表级约束）

使用表级约束给多个字段联合添加约束，如：unique（name,email）名字和邮箱这两个字段不能同时重复，但是名字和邮箱字段可以单独重复。
可以给表级约束起名字，这样可以便于操作这个约束（如，删除，修改等）

【2】not null 和unique同时使用(列级约束)

被 not null 和 unique 约束的字段，该字段即不能为 NULL 也不能重复；

-- not null + unique 不为空且唯一就构成了你的主键 primary key

（1）语法

# 先删除user表
drop table user;
# 再创建运用unique not null的表
create table user(
	id int(4) unique not null,
    name varchar(32) not null,
    gender enum('male',"female")
);

# 创建运用primary key的表
create table user_pri(
	id int(4)  primary key,
    name varchar(32) not null,
    gender enum('male',"female")
);

（2）示例

mysql> drop table user;
Query OK, 0 rows affected (0.23 sec)

mysql> create table user(
    -> id int(4) unique not null,
    ->     name varchar(32) not null,
    ->     gender enum('male',"female")
    -> );
Query OK, 0 rows affected (0.40 sec)

mysql> desc user;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id     | int(4)                | NO   | PRI | NULL    |       |
| name   | varchar(32)           | NO   |     | NULL    |       |
| gender | enum('male','female') | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table user_pri(
    -> id int(4)  primary key,
    ->     name varchar(32) not null,
    ->     gender enum('male',"female")
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> desc user_pri;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id     | int(4)                | NO   | PRI | NULL    |       |
| name   | varchar(32)           | NO   |     | NULL    |       |
| gender | enum('male','female') | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

【3】结论

not null + unique一起使用相当于primary key

-- not null + unique 不为空且唯一就构成了你的主键 primary key

Ⅲ 主键约束PK（primary key）

【一】主键涉及到的术语名词解释

-- 【1】主键约束
-- 就是你添加的哪个约束规则
-- 【2】主键字段
-- 添加了主键规则的字段就叫主键字段
-- 【3】主键值
-- 主键字段对应的值

【二】主键约束、主键字段、主键值三者之间关系

表中某个字段添加主键约束之后，该字段被称为主键字段
主键字段中出现的每一个数据都被称为主键值；

ps：主键值必须不能为空值，具有唯一性，会自动添加索引，且具有该行数据的唯一标识。一张表只能有一个主键约束。

【三】主键的作用

添加主键primary key的字段即不能重复也不能为空，效果与“not null nuique”相同，但本质是不同的，添加主键约束之后，主键不仅会有“not null unique”作用，而且主键字段还会自动添加“索引 — index”；
一张表应该有主键，若没有，表示这张表是无效的。“主键值”是当前行数据的唯一标识，“主键值”可以是当前行数据的身份证号；（即使表中两行数据完全相同，但是由于主键不同，我们也认为这是两行完全不同的数据）

【四】主键根据个数分类

单一主键、复合主键
给一个字段添加一个主键约束，被称为单一主键
给多个字段联合添加一个主键约束，被称为复合主键

ps:不论时单一主键还是复合主键，一张表只能有一个主键约束。

【1】单一主键：列级约束

-- 给一个字段添加一个主键约束，被称为单一主键

语法

drop table if exists user;

create table user(
	id int(10) primary key,
    name varchar(32)
);

示例

mysql> drop table if exists user;
Query OK, 0 rows affected (0.23 sec)

mysql> create table user(
    -> id int(10) primary key,
    ->     name varchar(32)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

【2】单一主键：表级约束

1.语法

drop table if exists user;

create table user(
	id int(10),
  name varchar(32),
  primary key(id)
);

示例

mysql> drop table if exists user;
Query OK, 0 rows affected (0.22 sec)

mysql> create table user(
    -> id int(10),
    ->   name varchar(32),
    ->   primary key(id)
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql>  desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

【3】给主键重命名

语法

create table user(
	id int(10) primary key,
    name varchar(32)
);

# 给主键重命名 
#  table_constraints 专门用来存储字段约束信息
create table user_id(
	id int(10),
  name varchar(32),
  constraint user_id_pk primary key(id)  # 原来主键是id现在主键变成 user_id_pk
);

示例

mysql> desc user_id;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

【4】复合主键：给整张表加约束

语法

drop table if exists user;

# 复合主键，表级约束，并且给其重命名
create table user(
  id int(10),
  name varchar(32),
  email varchar(32),
  constraint user_id_name_pk primary key(id,name)
);

示例

mysql> create table user(
    ->   id int(10),
    ->   name varchar(32),
    ->   email varchar(32),
    ->   constraint user_id_name_pk primary key(id,name)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | NO   | PRI | NULL    |       |
| email | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

【5】主键根据性质分类

自然主键：主键值若是一个自然数，并且这个自然数与业务没有任何关系，这种主键称为自然主键；
业务主键：主键值若和当前表中的业务紧密相关，那么这种主键值被业务主键；如果业务发生改变时，业务主键往往会受到影响，所以业务主键使用较少，大多情况使用自然主键。

【6】mysql内部自动生成主键

MySQL中自动生成主键值(使用自增字段auto_increment)
MySQL数据库管理系统中提供了一个自增数字，专门用来自动生成主键值，主键值不需要用户去维护，也不需要用户生成，MySQL会自动生成。
自增数字默认从1开始，以1递增：1、2、3、

【7】额外的约束条件 auto_increment

（1）展示

语法

-- primary key : 唯一不为空
-- 现在插入的这条数据id为空
-- auto_increment 自动帮你填充数据 仅限于数字类型

drop table if exists user;

create table user(
  id int(10) primary key auto_increment,
  name varchar(32)
);

create table user_new(
  id int(10) primary key,
  name varchar(32)
);

insert into user(name) values('silence');

insert into user_new(name) values('silence');

示例

# 建有额外的约束条件 auto_increment的表
mysql> create table user(
    ->   id int(10) primary key auto_increment,
    ->   name varchar(32)
    -> );
Query OK, 0 rows affected (0.29 sec)

# 插入数据
mysql> insert into user(name) values('silence');
Query OK, 1 row affected (0.13 sec)

# 查看表
mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

# 查看表中数据 ，自动生成主键值
mysql> select * from user;
+----+---------+
| id | name    |
+----+---------+
|  1 | silence |
+----+---------+
1 row in set (0.00 sec)

# 没有额外约束项的表
mysql> create table user_new(
    ->   id int(10) primary key,
    ->   name varchar(32)
    -> );
Query OK, 0 rows affected (0.31 sec)

# 插入数据  不能自动生成主键值  就报错
mysql> insert into user_new(name) values('silence');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

（2）插入指定的ID数据

插入指定的ID数据之后再插入数据会按照上一个排列+1
自动生成后，一个主键自然数只能出现一次，若删除该行纪录，重新递增纪录时，主键自然数会跳过直接+1

语法

insert into user(name) values('silence'); 

insert into user(id,name) values(999,'silence');

insert into user(name) values('silence'); 
-- 再插入新的数据是在上一个ID 的基础上进行 +1 操作构成新ID

-- 插入指定的ID数据
insert into user(id,name) values(998,'silence');

示例

mysql> insert into user(name) values('silence');
Query OK, 1 row affected (0.12 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from user;
+----+---------+
| id | name    |
+----+---------+
|  1 | silence |
|  2 | silence |
+----+---------+
2 rows in set (0.00 sec)

# 插入指定的ID数据
mysql> insert into user(id,name) values(999,'silence');
Query OK, 1 row affected (0.04 sec)

mysql>  select * from user;
+-----+---------+
| id  | name    |
+-----+---------+
|   1 | silence |
|   2 | silence |
| 999 | silence |
+-----+---------+
3 rows in set (0.00 sec)

# 如果插入的ID是在现有的ID之前就会自动按照顺序排序
-- 下一次插入的ID就会在最后一个ID之上插入
mysql> insert into user(name) values('silence');
Query OK, 1 row affected (0.17 sec)

mysql>  select * from user;
+------+---------+
| id   | name    |
+------+---------+
|    1 | silence |
|    2 | silence |
|  999 | silence |
| 1000 | silence |
+------+---------+
4 rows in set (0.00 sec)

-- 如果插入的ID是在现有的ID之前就会自动按照顺序排序
-- 下一次插入的ID就会在最后一个ID之上插入
mysql> insert into user(id,name) values(998,'silence');
Query OK, 1 row affected (0.12 sec)

mysql>  select * from user;
+------+---------+
| id   | name    |
+------+---------+
|    1 | silence |
|    2 | silence |
|  998 | silence |
|  999 | silence |
| 1000 | silence |
+------+---------+
5 rows in set (0.00 sec)

【8】重置主键起始位置(基于自增主键)

（1）方式一：清空表数据

将原本的表数据全部清空
该操作会将表重置为刚创建时的状态，不建议轻易使用

语法

-- truncate 表名
-- 会将表中的所有数据清空并重置

truncate 表名;

# 重新插入数据 
insert into user(name) values('silence');

示例

mysql> truncate user;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from user;
Empty set (0.00 sec)

mysql> insert into user(name) values('silence');
Query OK, 1 row affected (0.03 sec)

mysql>  select * from user;
+----+---------+
| id | name    |
+----+---------+
|  1 | silence |
+----+---------+
1 row in set (0.00 sec)
1 row in set (0.00 sec)

（2）方式二：指定主键起始位置

使用表修改语句将带有 AUTO_INCREMENT 的字段数字指定为自定义位置

语法

-- 指定主键起始位置
-- alter table 表名 AUTO_INCREMENT = 指定主键起始位置;
-- 只能修改从当前已有最后的ID之后的起始ID 的位置，不能修改前面的其实ID

alter table 表名 AUTO_INCREMENT = 指定主键起始位置;

alter table user AUTO_INCREMENT = 5;
insert into user(name) values('silence');

示例

mysql> alter table user AUTO_INCREMENT = 5;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into user(name) values('silence');
Query OK, 1 row affected (0.13 sec)

mysql>  select * from user;
+----+---------+
| id | name    |
+----+---------+
|  1 | silence |
|  5 | silence |
+----+---------+
2 rows in set (0.00 sec)

Ⅳ 外键约束FK（foreign key）

【一】引入

假设我们现在有一张员工表，员工表内有下述字段

-- 我有一张员工表和部门表
-- 员工和部门进行关联 ， 

id   name   age 
1    silence   18

id name 
1  维修部

-- 方案一：不分表直接合到一起
id   name   age  dep_name
1    silence   18    维修部
2    mark     18    维修部

-- 方案二：分表 
-- 分一个员工表
id   name   age  dep_id
1    silence   18    1
2    mark     18    2

-- 分一个部门表
id dep_name 
1  维修部
2  后勤部

【二】外键涉及到的术语名词解释

-- 外键约束
-- 给字段增加的外键约束的规则就叫外键约束
-- 外键字段
-- 给字段增加了外键约束的字段就叫外键字段
-- 外键值
-- 外键字段对应的值就叫外键值

【三】外键关系介绍

一对一：个人和个人信息之间
一对多：一个图书对应一个出版社出版社可以印多本图书一对多
多对多：一个作者可以写多本书，这本书可以有多个作者多对多
没有关系

【四】外键约束创建的语法

-- 先创建表1
create table 表名1(
	字段名1 字段类型1 约束条件1 comment 注释1,
	字段名2 字段类型2 约束条件2 comment 注释2,
);

-- 再创建表2
-- 在表2中给指定外键关系
create table 表名2(
	字段名1 字段类型1 约束条件1 comment 注释1,
	字段名2 字段类型2 约束条件2 comment 注释2,
    -- foreign key 自己的外键字段  references 被关联的表名 需要关联的外键字段 字段名1
    foreign key(在表名2中显示的字段名，字段名2) references 表名1(表明1中需要建立外键关系的字段名)
);

【五】一对多外键关系

【1】理论分析

以员工表与部门表为例
先站在员工表的角度
- 问:一个员工能否对应多个部门
- 答:不可以
再站在部门表的角度
- 问:一个部门能否对应多个员工
- 答:可以
结论:换位思考之后得出的答案是一个可以一个不可以

【2】一对多外键关系应该创建在哪一方？

所以关系是"一对多"
- 部门是'一'
- 员工是'多'
- 外键关系建立在多的一方
'''关系表达只能用一对多不能用多对一'''
一对多关系外键字段建在"多"的一方(员工表)

-- 员工表
id name  age dep_id
1  silence 18    1
1  mark   18    2

-- 部门表
id dep_name 
1    构建    
2    报价

【3】建表思路

如果表中有外键字段那么建议你先编写普通字段最后再考虑外键字段

（1）创建部门表

语法

-- 分清谁和谁关联 ----> 分清谁先被创建
-- 部门表先被创建
-- comment 给字段增加注解
create table dep(
	id int primary key auto_increment comment '编号',
    dep_name varchar(32) comment '部门名称',
    dep_desc varchar(32) comment '部门描述'
);

-- 插入数据
-- 向部门表中插入数据
insert into dep(dep_name,dep_desc) values("cook","后厨部门");
insert into dep(dep_name,dep_desc) values("hospital","医院部门");

示例

# 建表
mysql> create table dep(
    -> id int primary key auto_increment comment '编号',
    ->     dep_name varchar(32) comment '部门名称',
    ->     dep_desc varchar(32) comment '部门描述'
    -> );
Query OK, 0 rows affected (0.40 sec)

# 查看表
mysql> desc dep;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| dep_name | varchar(32) | YES  |     | NULL    |                |
| dep_desc | varchar(32) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.11 sec)

# 插入数据
mysql> insert into dep(dep_name,dep_desc) values("cook","后厨部门");
Query OK, 1 row affected (0.04 sec)

mysql> insert into dep(dep_name,dep_desc) values("hospital","医院部门");
Query OK, 1 row affected (0.05 sec)

# 查看表内数据
mysql> select * from dep;
+----+----------+--------------+
| id | dep_name | dep_desc     |
+----+----------+--------------+
|  1 | cook     | 后厨部门     |
|  2 | hospital | 医院部门     |
+----+----------+--------------+
2 rows in set (0.00 sec)

（2）创建员工表

语法

-- 有了部门表以后才能创建员工表并且员工和部门绑定
-- 在创建表字段的时候也可以给每个字段添加中文注释
-- comment 给字段增加注解
create table emp(
  id int primary key auto_increment comment '编号',
  name varchar(32) comment '姓名',
  age int comment '年龄',
  dep_id int comment '部门编号',
  -- foreign key(自己的字段) references 目标表名(目标表的关联字段)
  foreign key(dep_id) references dep(id)
);

-- 插入数据
-- 向员工表中插入数据,不带外键字段
insert into emp(name,age) values("silence",18);

-- 向员工表中插入数据，携带部门表中存在的部门ID
insert into emp(name,age,dep_id) values("mark",28,1);

-- 插入一条部门表中不存在的部门ID，部门ID数据不存在则会报错
insert into emp(name,age,dep_id) values("happy",38,3);

示例

mysql> create table emp(
    ->   id int primary key auto_increment comment '编号',
    ->   name varchar(32) comment '姓名',
    ->   age int comment '年龄',
    ->   dep_id int comment '部门编号',
    ->   -- foreign key(自己的字段) references 目标表名(目标表的关联字段)
    ->   foreign key(dep_id) references dep(id)
    -> );
Query OK, 0 rows affected (0.31 sec)

-- 向员工表中插入数据,不带外键字段
mysql> insert into emp(name,age) values("silence",18);
Query OK, 1 row affected (0.04 sec)

mysql> desc emp;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(32) | YES  |     | NULL    |                |
| age    | int(11)     | YES  |     | NULL    |                |
| dep_id | int(11)     | YES  | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from emp;
+----+---------+------+--------+
| id | name    | age  | dep_id |
+----+---------+------+--------+
|  1 | silence |   18 |   NULL |
+----+---------+------+--------+
1 row in set (0.01 sec)

-- 向员工表中插入数据，携带部门表中存在的部门ID
mysql> insert into emp(name,age,dep_id) values("mark",28,1);
Query OK, 1 row affected (0.05 sec)

mysql> desc emp;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(32) | YES  |     | NULL    |                |
| age    | int(11)     | YES  |     | NULL    |                |
| dep_id | int(11)     | YES  | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from emp;
+----+---------+------+--------+
| id | name    | age  | dep_id |
+----+---------+------+--------+
|  1 | silence |   18 |   NULL |
|  2 | mark    |   28 |      1 |
+----+---------+------+--------+
2 rows in set (0.00 sec)

mysql> insert into emp(name,age,dep_id) values("happy",38,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`study001`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

【六】多对多外键关系

【1】理论分析

以图书与作者表为例
- 先站在图书表的角度
  - 问:一本书籍能否对应多名作者
  - 答:可以
- 再站在作者表的角度
- 问:一名作者能否对应多本书籍
- 答:可以
结论:换位思考之后两边都可以那么就是"多对多"关系

【2】建表思路

-- 创建表的时候按照一对多的思路是创建没有关联外键字段的那张表
-- 因为多对多关系两张表都各自需要被关联

id  author_name book_id

id  book_name   author_id

-- 借助第三张表
-- 在第三张表中增加额外的关联关系

id  author_name 

id  book_name   

id book_id author_id
 1    1      1
 2    1      2
 3    2      1

【3】代码实现

-- 如果针对的是多对多关系 建议是开设第三张表来存储响应的数据
create table book(
  id int primary key auto_increment,
  title varchar(32),
  price float(10,2)
);

create table author(
  id int primary key auto_increment,
  name varchar(32),
  gender enum('male','female','others')
);

# 针对多对多关系 需要单独开设第三张表专门存储关系
create table book_author(
  id int primary key auto_increment,
  author_id int,
  book_id int,
  foreign key(author_id) references author(id) 
  on update cascade   # 级联更新
  on delete cascade,  # 级联删除
  foreign key(book_id) references book(id) 
  on update cascade  # 级联更新
  on delete cascade  # 级联删除
);

【4】小结

针对多对多表关系
两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除

【七】一对一外键关系

针对qq用户表其实里面的数据可以分成两类
- 热数据:经常需要使用的数据
- eg:qq号码座右铭个人简介爱好
- 冷数据:不怎么经常需要使用的数据
  - eg:邮箱电话学校 ...
为了节省资源并降低数据库压力会将表一分为二
用户表
- 存使用频率较高的数据字段
用户详情表
存使用频率较低的数据字段

【1】理论分析

先站在用户表的角度
- 问:一个用户数据能否对应多个用户详情数据
- 答:不可以
再站在用户详情表的角度
问:一个用户详情数据能否对应多个用户数据
答:不可以
结论:换位思考之后两边都不可以那么关系可能有两种
'没有关系'：用膝盖都能判断出来
'一对一关系'：针对'一对一关系'外键字段建在任意一方都可以,但是推荐建在查询频率较高的较好的一方

-- 理论分析
-- 个人和个人详细信息
-- 一个人只能有一个详细信息
-- 一个详细信息只能对应一个人

-- 外键关系建立在谁身上？
-- 如果是一对一关系，建立在任何一方都没有问题
-- 但是建议建立在使用频次较高的一方
-- 个人表使用频率较高，建议建立在个人表中

【2】建表语句

创建用户表

create table User(
	id int primary key auto_increment,
  name varchar(32),
  gender enum('male','female','others'),
  user_detail_id int unique,  # 好好体会为什么加unique
  foreign key(user_detail_id) references UserDetail(id) 
  on update cascade   # 级联更新
  on delete cascade,  # 级联删除
);

创建用户详细信息表

create table UserDetail(
  id int primary key auto_increment,
  phone bigint,
  age int
);

Ⅴ 级联更新和级联删除

添加级联更新和级联删除时需要在外键约束后面添加
在删除父表中的数据时，级联删除子表中的数据 on delete cascade
在更新父表中的数据时，级联更新子表中的数据 on update cascade
以上的级联更新和级联删除谨慎使用，因为级联操作会将数据改变或删除【数据无价】
在修改约束条件时，建议可以将原先的约束删除再重新添加约束条件

语法

create table dep1(
  id int primary key auto_increment comment '编号',
  dep_name varchar(32) comment '部门名称',
  dep_desc varchar(32) comment '部门描述'
);

create table emp1(
  id int primary key auto_increment comment '编号',
  name varchar(32) comment '姓名',
  age int comment '年龄',
  dep_id int comment '部门编号',
  foreign key(dep_id) references dep1(id) 
  on update cascade  # 级联更新
  on delete cascade  # 级联删除
);

-- 向部门表中插入数据
insert into dep1(dep_name,dep_desc) values("cook","后厨部门");
insert into dep1(dep_name,dep_desc) values("hospital","医院部门");

-- 向员工表中插入数据,不带外键字段
insert into emp1(name,age) values("silence",18);

-- 向员工表中插入数据，携带部门表中存在的部门ID
insert into emp1(name,age,dep_id) values("mark",28,1);

-- 级联更新，如果更新被关联的数据表中的数据，关联表中的数据随之修改
update dep1 set id=100 where id=1;

-- 如果删除被关联表中的数据，关联表中的数据也会随之删除
delete from dep1 where id=100;

2.示例

mysql> create table dep1(
    ->   id int primary key auto_increment comment '编号',
    ->   dep_name varchar(32) comment '部门名称',
    ->   dep_desc varchar(32) comment '部门描述'
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql>
mysql> create table emp1(
    ->   id int primary key auto_increment comment '编号',
    ->   name varchar(32) comment '姓名',
    ->   age int comment '年龄',
    ->   dep_id int comment '部门编号',
    ->   foreign key(dep_id) references dep1(id)
    ->   on update cascade  # 级联更新
    ->   on delete cascade  # 级联删除
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> insert into dep1(dep_name,dep_desc) values("cook","后厨部门");
Query OK, 1 row affected (0.04 sec)

mysql> insert into dep1(dep_name,dep_desc) values("hospital","医院部门");
Query OK, 1 row affected (0.04 sec)

mysql> insert into emp1(name,age) values("silence",18);
Query OK, 1 row affected (0.05 sec)

mysql> insert into emp1(name,age,dep_id) values("mark",28,1);
Query OK, 1 row affected (0.04 sec)

mysql> desc dep1;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| dep_name | varchar(32) | YES  |     | NULL    |                |
| dep_desc | varchar(32) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc emp1;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(32) | YES  |     | NULL    |                |
| age    | int(11)     | YES  |     | NULL    |                |
| dep_id | int(11)     | YES  | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from dep1;
+----+----------+--------------+
| id | dep_name | dep_desc     |
+----+----------+--------------+
|  1 | cook     | 后厨部门     |
|  2 | hospital | 医院部门     |
+----+----------+--------------+
2 rows in set (0.00 sec)

mysql> select * from emp1;
+----+---------+------+--------+
| id | name    | age  | dep_id |
+----+---------+------+--------+
|  1 | silence |   18 |   NULL |
|  2 | mark    |   28 |      1 |
+----+---------+------+--------+
2 rows in set (0.00 sec)

-- 级联更新，如果更新被关联的数据表中的数据，关联表中的数据随之修改
mysql> update dep1 set id=100 where id=1;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 部门id=1被改为100
mysql>  select * from dep1;
+-----+----------+--------------+
| id  | dep_name | dep_desc     |
+-----+----------+--------------+
|   2 | hospital | 医院部门     |
| 100 | cook     | 后厨部门     |
+-----+----------+--------------+
2 rows in set (0.00 sec)

# 被关联的数据表中的数据，关联表中的数据随之修改
mysql> select * from emp1;
+----+---------+------+--------+
| id | name    | age  | dep_id |
+----+---------+------+--------+
|  1 | silence |   18 |   NULL |
|  2 | mark    |   28 |    100 |
+----+---------+------+--------+
2 rows in set (0.00 sec)

-- 如果删除被关联表中的数据，关联表中的数据也会随之删除
mysql> delete from dep1 where id=100;
Query OK, 1 row affected (0.12 sec)


mysql> select * from emp1;
+----+---------+------+--------+
| id | name    | age  | dep_id |
+----+---------+------+--------+
|  1 | silence |   18 |   NULL |
+----+---------+------+--------+
1 row in set (0.00 sec)

Ⅵ 如何查看当前表的约束条件

【一】查看所有数据库

show databases;

【二】切换到表结构数据库

use information_schema;

【三】查看当前数据下的所有表

show tables;

【四】 table_constraints 专门用来存储字段约束信息

desc table_constraints;

【五】查看指定表的约束信息

select constraint_name from table_constraints where table_name='表名';

select constraint_name from table_constraints where table_name='user';

Ⅶ MySQL之过滤条件

【一】标准的查询语句语法

select */字段名 from */表名 where */字段名=字段值;

select * from user where id=1;

-- 执行顺序
from 起手 知道是那张表
where 根据过滤条件在表中过滤数据
select 再过滤出自己想要的数据

* 全部数据

【二】准备数据

语法

-- 创建数据库
drop table if exists emp_data;
create database emp_data;
-- 切换数据库
use emp_data;
-- 创建表
create table emp(
	id int not null unique auto_increment,
    name varchar(20) not null,
    sex enum("male","female") not null default "male",
    age int(3) unsigned not null default 28,
    hire_date date not null,
    post varchar(50),
    post_comment varchar(100),
    salary double(15,2),
    office int,
    depart_id int
);

-- 插入数据
insert into emp(name, sex, age, hire_date, post, salary, office, depart_id) values
("dream", "male", 78, '20220306', "陌夜痴梦久生情", 730.33, 401, 1), # 以下是教学部
("mengmeng", "female", 25, '20220102', "teacher", 12000.50, 401, 1),
("xiaomeng", "male", 35, '20190607', "teacher", 15000.99, 401, 1),
("xiaona", "female", 29, '20180906', "teacher", 11000.80, 401, 1),
("xiaoqi", "female", 27, '20220806', "teacher", 13000.70, 401, 1),
("suimeng", "male", 33, '20230306', "teacher", 14000.62, 401, 1), # 以下是销售部
("娜娜", "female", 69, '20100307', "sale", 300.13, 402, 2),
("芳芳", "male", 45, '20140518', "sale", 400.45, 402, 2),
("小明", "male", 34, '20160103', "sale", 350.80, 402, 2),
("亚洲", "female", 42, '20170227', "sale", 320.99, 402, 2),
("华华", "female", 55, '20180319', "sale", 380.75, 402, 2),
("田七", "male", 44, '20230808', "sale", 420.33, 402, 2), # 以下是运行部
("大古", "female", 66, '20180509', "operation", 630.33, 403, 3),
("张三", "male", 51, '20191001', "operation", 410.25, 403, 3),
("李四", "male", 47, '20200512', "operation", 330.62, 403, 3),
("王五", "female", 39, '20210203', "operation", 370.98, 403, 3),
("赵六", "female", 36, '20220724', "operation", 390.15, 403, 3);

# 格式化美化数据
select * from emp\G;

示例

# 判断库是否存在 若存在则删除
mysql> drop table if exists emp_data;
Query OK, 0 rows affected, 1 warning (0.11 sec)

# 建库
mysql> create database emp_data;
Query OK, 1 row affected (0.00 sec)

# 切换库
mysql> use emp_data;
Database changed

# 建表
mysql> create table emp(
    -> id int not null unique auto_increment,
    ->     name varchar(20) not null,
    ->     sex enum("male","female") not null default "male",
    ->     age int(3) unsigned not null default 28,
    ->     hire_date date not null,
    ->     post varchar(50),
    ->     post_comment varchar(100),
    ->     salary double(15,2),
    ->     office int,
    ->     depart_id int
    -> );
Query OK, 0 rows affected (0.37 sec)

# 插入数据
mysql> insert into emp(name, sex, age, hire_date, post, salary, office, depart_id) values
    -> ("dream", "male", 78, '20220306', "陌夜痴梦久生情", 730.33, 401, 1), # 以下是教学部
    -> ("mengmeng", "female", 25, '20220102', "teacher", 12000.50, 401, 1),
    -> ("xiaomeng", "male", 35, '20190607', "teacher", 15000.99, 401, 1),
    -> ("xiaona", "female", 29, '20180906', "teacher", 11000.80, 401, 1),
    -> ("xiaoqi", "female", 27, '20220806', "teacher", 13000.70, 401, 1),
    -> ("suimeng", "male", 33, '20230306', "teacher", 14000.62, 401, 1), # 以下是销售部
    -> ("娜娜", "female", 69, '20100307', "sale", 300.13, 402, 2),
    -> ("芳芳", "male", 45, '20140518', "sale", 400.45, 402, 2),
    -> ("小明", "male", 34, '20160103', "sale", 350.80, 402, 2),
    -> ("亚洲", "female", 42, '20170227', "sale", 320.99, 402, 2),
    -> ("华华", "female", 55, '20180319', "sale", 380.75, 402, 2),
    -> ("田七", "male", 44, '20230808', "sale", 420.33, 402, 2), # 以下是运行部
    -> ("大古", "female", 66, '20180509', "operation", 630.33, 403, 3),
    -> ("张三", "male", 51, '20191001', "operation", 410.25, 403, 3),
    -> ("李四", "male", 47, '20200512', "operation", 330.62, 403, 3),
    -> ("王五", "female", 39, '20210203', "operation", 370.98, 403, 3),
    -> ("赵六", "female", 36, '20220724', "operation", 390.15, 403, 3);
Query OK, 17 rows affected (0.17 sec)
Records: 17  Duplicates: 0  Warnings: 0

# 查看表
mysql> desc emp;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

# 查看表内数据
mysql> select * from emp;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
| id | name     | sex    | age | hire_date  | post                  | post_comment | salary   | office | depart_id |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
|  1 | dream    | male   |  78 | 2022-03-06 | 陌夜痴梦久生情        | NULL         |   730.33 |    401 |         1 |
|  2 | mengmeng | female |  25 | 2022-01-02 | teacher               | NULL         | 12000.50 |    401 |         1 |
|  3 | xiaomeng | male   |  35 | 2019-06-07 | teacher               | NULL         | 15000.99 |    401 |         1 |
|  4 | xiaona   | female |  29 | 2018-09-06 | teacher               | NULL         | 11000.80 |    401 |         1 |
|  5 | xiaoqi   | female |  27 | 2022-08-06 | teacher               | NULL         | 13000.70 |    401 |         1 |
|  6 | suimeng  | male   |  33 | 2023-03-06 | teacher               | NULL         | 14000.62 |    401 |         1 |
|  7 | 娜娜     | female |  69 | 2010-03-07 | sale                  | NULL         |   300.13 |    402 |         2 |
|  8 | 芳芳     | male   |  45 | 2014-05-18 | sale                  | NULL         |   400.45 |    402 |         2 |
|  9 | 小明     | male   |  34 | 2016-01-03 | sale                  | NULL         |   350.80 |    402 |         2 |
| 10 | 亚洲     | female |  42 | 2017-02-27 | sale                  | NULL         |   320.99 |    402 |         2 |
| 11 | 华华     | female |  55 | 2018-03-19 | sale                  | NULL         |   380.75 |    402 |         2 |
| 12 | 田七     | male   |  44 | 2023-08-08 | sale                  | NULL         |   420.33 |    402 |         2 |
| 13 | 大古     | female |  66 | 2018-05-09 | operation             | NULL         |   630.33 |    403 |         3 |
| 14 | 张三     | male   |  51 | 2019-10-01 | operation             | NULL         |   410.25 |    403 |         3 |
| 15 | 李四     | male   |  47 | 2020-05-12 | operation             | NULL         |   330.62 |    403 |         3 |
| 16 | 王五     | female |  39 | 2021-02-03 | operation             | NULL         |   370.98 |    403 |         3 |
| 17 | 赵六     | female |  36 | 2022-07-24 | operation             | NULL         |   390.15 |    403 |         3 |
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+
17 rows in set (0.00 sec)

# 格式化美化数据
select * from emp\G;

【三】关键字where

对整体的数据进行初步筛选

【1】查询 id >=3 并且 id <= 6的数据

-- 查询 id >=3 并且 id <= 6的数据
select * from * where *;

-- 3<=id<=6 这种方式不生效
select * from emp where 3<=id<=6;

（1）方式一：id >=3 and id <= 6

-- 方式一：id >=3 and id <= 6 可以使用
select * from emp where id >=3 and id <= 6;

（2）方式二 between ... and ...

-- 方式二 between ... and ... 可以使用
select * from emp where id between 3 and 6;

【2】查询薪资是1w2或者1w3或者7300 的数据

（1）方式一： or 连接多个或条件

-- 方式一： or 连接多个或条件
select * from emp where salary=12000.50 or salary=13000.70 or salary=730.33;

（2）方式二：成员判断 in

-- 方式二：成员判断 in
select * from emp where salary in (12000.50,13000.70 ,730.33);

【3】查询员工姓名中包含字母o的姓名和薪资

-- 模糊查询 like 
--- %代表任意字符
select name,salary from emp where name like "%o%";

【4】查询员工姓名是由六个字符组成的姓名和薪资

-- 方式一：因为 _ 代表一个字符 6 个_ 代表6个字符
select name,salary from emp where name like "______";

-- 在SQL语句中不支持直接运算(舍去)
select name,salary from emp where name like "_"*6;

-- 方式二：使用 char_length 内置函数对指定字段进行计数
select name,salary from emp where char_length(name) = 6;

【5】查询 id<3 或者 id>6 的数据

-- or 连接两个或条件
select * from emp where id < 3 or id > 6;

-- 反着来 先查在他们俩之间的数据 直接取反 not between and 
select * from emp where id not between 3 and 6;

-- 反着来 先查在他们元组之间的数据 直接取反 not not in (3,4,5,6);
select * from emp where id not in (3,4,5,6);

-- 不太符合规范(舍去)
select * from emp where not id < 3 and id > 6;

-- 不行，没有()提高优先级的操作（舍去）
select * from emp where not (id < 3 and id > 6);

-- 不行
select * from emp where post_comment=null;

-- 如果查某个字段为 null 就不能用 = 而是要用 is 
select * from emp where post_comment is null;

标签：级联,name,--,mysql,sec,user,key,MySQL,id
From： https://www.cnblogs.com/zyb123/p/18221335

MySQL约束条件,非空空约束（not nullnull）,主键约束PK（primary key）,外键约束FK（foreign key）,级联更新和级联删除,如何查看当前表的约束条件,MySQL之过

ⅠMySQL约束条件

【一】什么是约束条件

【二】约束条件概览

Ⅱ 非空/空约束（not null/null）

【1】作用

【2】示例

【一】唯一性约束（unique）

【1】作用

【2】示例

【二】组合使用not null + unique

【1】not null 和 unique 单独使用(表级约束）

【2】not null 和unique同时使用(列级约束)

（1）语法

（2）示例

【3】结论

Ⅲ 主键约束PK（primary key）

【一】主键涉及到的术语名词解释

【二】主键约束、主键字段、主键值三者之间关系

【三】主键的作用

【四】主键根据个数分类

【1】单一主键：列级约束

【2】单一主键：表级约束

【3】给主键重命名

【4】复合主键：给整张表加约束

【5】主键根据性质分类

【6】mysql内部自动生成主键

【7】额外的约束条件 auto_increment

（1）展示

（2）插入指定的ID数据

【8】重置主键起始位置(基于自增主键)

（1）方式一：清空表数据

（2）方式二：指定主键起始位置

Ⅳ 外键约束FK（foreign key）

【一】引入

【二】外键涉及到的术语名词解释

【三】外键关系介绍

【四】外键约束创建的语法

【五】一对多外键关系

【1】理论分析

【2】一对多外键关系应该创建在哪一方？

【3】建表思路

（1）创建部门表

（2）创建员工表

【六】多对多外键关系

【1】理论分析

【2】建表思路

【3】代码实现

【4】小结

【七】一对一外键关系

【1】理论分析

【2】建表语句

Ⅴ 级联更新和级联删除

Ⅵ 如何查看当前表的约束条件

【一】查看所有数据库

【二】切换到表结构数据库

【三】查看当前数据下的所有表

【四】 table_constraints 专门用来存储字段约束信息

【五】查看指定表的约束信息

Ⅶ MySQL之过滤条件

【一】标准的查询语句语法

【二】准备数据

【三】关键字where

【1】查询 id >=3 并且 id <= 6的数据

（1）方式一：id >=3 and id <= 6

（2）方式二 between ... and ...

【2】查询 薪资是1w2或者1w3或者7300 的数据

（1）方式一： or 连接多个或条件

（2）方式二：成员判断 in

【3】查询 员工姓名中包含字母o的姓名和薪资

【4】查询员工姓名是由六个字符组成的姓名和薪资

【5】查询 id<3 或者 id>6 的数据

相关文章

赞助商

阅读排行

【2】查询薪资是1w2或者1w3或者7300 的数据

【3】查询员工姓名中包含字母o的姓名和薪资