33.MySQL数据库【三】约束条件

约束条件

限制表中的数据，保证数据的准确性和可靠性而存在的限制规则

【一】非空约束（not null）

限制指定字段不能为空

# 建表
create table eg(
	name varchar(32) not null,
	hobby varchar(32)
);
# name字段不能为空

【二】唯一性约束（unique）

限制字段具有唯一性，不能重复，但能为空

# 建表
create table eg(
    id int(16) unique,
	name varchar(32) not null,
	hobby varchar(32)
);
# 每个id字段不能重复，但能空着

【三】组合使用

1）not null 和 unique 单独使用（表级约束）

# 建表
create table eg(
    id int(16) unique,
	name varchar(32) not null,
	hobby varchar(32)
);
# 每个id字段不能重复，但能空着
# name字段不能为空

2）not null 和 unique 同时使用（列级约束）

# 建表
create table eg_1(
    id int(16) unique not null,
	name varchar(32) not null,
	hobby varchar(32)
);
# id字段不能重复，也不能空
# name字段不能空

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

【四】主键约束PK（primary key）

1）相关术语

主键约束：添加的那个约束规则
主键字段：添加了主键规则的字段
主键值：主键字段对应的值

2）主键的作用

添加主键primary key的字段既不能重复也不能空，其不仅有not null、unique 的作用，还会自动添加”索引—index“

3）主键根据个数分类

1.单一主键

给一个字段添加一个主键约束

列级约束

# 建表
create table pk_1(
	id int(16) primary key,
	name varchar(32)
);
# 查看表
mysql> desc pk_1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

表级约束

# 建表
create table pk_2(
	id int(16),
	name varchar(32),
    constraint pk_2_id_pk primary key(id)
);
# 查看表
mysql> desc pk_2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.复合主键

给多个字段联合添加一个主键约束

# 创建
create table pk_3(
	id int(16),
	name varchar(32),
    constraint pk_3_id_name_pk primary key(id,name)
);
# 查看表
mysql> desc pk_3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(32) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

4）主键根据性质分类

自然主键：主键若是一个自然数，并且这个自然数与业务无任何关系，这种主键称之为自然主键
业务主键：主键值若和当前表中的业务紧密相关，这种主键值被称之为业务主键；若业务发送改变，业务主键往往也会受影响

5）自动生成主键

auto_increment：自增主键，在MySQL中自动生成主键值
默认从1开始递增

# 创建表
create table pk_4(
	id int primary key auto_increment,
	name varchar(32)
);
# 插入数据
insert into pk_4(name) values('a'),('b'),('c');
# 查看表数据
mysql> select * from pk_4;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

补：

手动插入不存在的id后，新数据id将在最后的id基础 +1 ，并作为该数据的id
若手动插入的id在现有id之前，表中数据会自动按顺序排列

6）重置主键起始位置

1.清空表数据

将原本的表数据全部清空
```
truncate 表名；
```

2.指定主键起始位置

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

只能从当前现有id的最后一位之后的id位置开始，不能从之前的id位置开始

alter table 表名 AUTO_INCREMENT=指定的位置;

# 指定位置
mysql> alter table pk_4 AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 插入
mysql> insert into pk_4(name) values('a'),('b'),('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
# 查看表数据
mysql> select * from pk_4;
+----+------+
| id | name |
+----+------+
| 10 | a    |
| 11 | b    |
| 12 | c    |
+----+------+
3 rows in set (0.00 sec)

【五】外键约束FK（foreign key）

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中需要建立外键关系的字段名)
);

3）按关系分类

0.级联更新和级联删除

添加级联更新和级联删除时需要在外键约束后面添加
级联更新：on update cascade
级联删除：on delete cascade

1.一对多关系

外键字段建在“多”的一方

# 先建部门表
create table fk_1_dep(
	id int primary key auto_increment comment '编号',
	dep_name varchar(32) comment '部门名称'
);
# 再建员工表
create table fk_1_emp(
	id int primary key auto_increment comment '编号',
	name varchar(32) comment '姓名',
    age int comment '年龄',
    dep_id int comment '部门编号',
    foreign key(dep_id) references fk_1_dep(id)
);

# 创建部门
insert into fk_1_dep(dep_name) values('语文'),('数学'),('英语');
# 创建员工(无外键)
insert into fk_1_emp(name,age) values('a',20);
# 创建员工(有外键)
insert into fk_1_emp(name,age,dep_id) values('b',25,2),('c',35,1);
# 查看表数据
mysql> select * from fk_1_emp;
+----+------+------+--------+
| id | name | age  | dep_id |
+----+------+------+--------+
|  1 | a    |   20 |   NULL |
|  2 | b    |   25 |      2 |
|  3 | c    |   35 |      1 |
+----+------+------+--------+
3 rows in set (0.00 sec)

2.多对多关系

需要单独开设第三张表专门存储关系

# 书名表
create table fk_2_book(
	id int primary key auto_increment comment '编号',
	title varchar(32) comment '书名',
    price float(10,2) comment '价格'
);
# 作者表
create table fk_2_author(
	id int primary key auto_increment comment '编号',
	name varchar(32) comment '作者名',
    sex enum('man','woman') comment '性别'
);
# 外键约束表
create table fk_2(
	id int primary key auto_increment comment '编号',
    book_id int,
    author_id int,
    foreign key(book_id) references fk_2_book(id)
    on update cascade   # 级联更新
    on delete cascade,  # 级联删除
    foreign key(author_id) references fk_2_author(id)
    on update cascade   # 级联更新
    on delete cascade  # 级联删除
);

# 创建书名
insert into fk_2_book(title,price) values('语文书',30.5),('数学书',30.5),('英语书',30.5);
# 创建作者
insert into fk_2_author(name,sex) values('a','man'),('b','man'),('c','woman'),('d','woman');
# 创建关联表
insert into fk_2(book_id,author_id) values(1,2),(2,1),(2,4),(3,3);
# 查看表数据
mysql> select * from fk_2_book;
+----+-----------+-------+
| id | title     | price |
+----+-----------+-------+
|  1 | 语文书    | 30.50 |
|  2 | 数学书    | 30.50 |
|  3 | 英语书    | 30.50 |
+----+-----------+-------+
3 rows in set (0.00 sec)

mysql> select * from fk_2_author;
+----+------+-------+
| id | name | sex   |
+----+------+-------+
|  1 | a    | man   |
|  2 | b    | man   |
|  3 | c    | woman |
|  4 | d    | woman |
+----+------+-------+
4 rows in set (0.00 sec)

mysql> select * from fk_2;
+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|  1 |       1 |         2 |
|  2 |       2 |         1 |
|  3 |       2 |         4 |
|  4 |       3 |         3 |
+----+---------+-----------+
4 rows in set (0.00 sec)

3.一对一关系

推荐建在查询频率较高的一方

# 用户信息
create table fk_3_userinfo(
    id int primary key auto_increment,
    age int comment '年龄',
    sex enum('man','woman') comment '性别',
    phone bigint comment '电话',
);
# 用户
create table fk_3_user(
    id int primary key auto_increment,
    user_id int unique,
    name varchar(32) comment '名称',
    foreign key(user_id) references fk_3_userinfo(id) 
    on update cascade   # 级联更新
  	on delete cascade  # 级联删除
);

# 创建用户信息
insert into fk_3_userinfo(age,sex,phone) values(20,'man',5568),(22,'woman',2225);
# 创建用户 关联
insert into fk_3_user(user_id,name) values(2,'a'),(1,'b');
# 查看表数据
mysql>  select * from fk_3_user;
+----+---------+------+
| id | user_id | name |
+----+---------+------+
|  1 |       2 | a    |
|  2 |       1 | b    |
+----+---------+------+
2 rows in set (0.00 sec)

mysql> select * from fk_3_userinfo;
+----+------+-------+-------+-------+
| id | age  | sex   | phone | hobby |
+----+------+-------+-------+-------+
|  1 |   20 | man   |  5568 | NULL  |
|  2 |   22 | woman |  2225 | NULL  |
+----+------+-------+-------+-------+
2 rows 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='表名';

posted on 2024-05-31 09:15 晓雾-Mist 阅读(31) 评论(0) 编辑收藏举报