3 数据库 MySQL 约束条件关系

最新推荐文章于 2024-01-28 19:55:08 发布

Jianhao92

最新推荐文章于 2024-01-28 19:55:08 发布

阅读量175

点赞数

分类专栏： MySQL学习文章标签： mysql

本文链接：https://blog.csdn.net/qq_36565509/article/details/105934617

版权

MySQL学习专栏收录该内容

7 篇文章 0 订阅

订阅专栏

数据库 MySQL

1 约束条件

上一篇介绍了 not null 非空, zerofill 0填充, unsigned 无符号

1.1 default 默认值

create table t11(
    id int,
    name char(16),
    gender enum('male', 'female', 'others') default 'male'
);

desc t11;
+--------+--------------------------------+------+-----+---------+-------+
| Field  | Type                           | Null | Key | Default | Extra |
+--------+--------------------------------+------+-----+---------+-------+
| id     | int(11)                        | YES  |     | NULL    |       |
| name   | char(16)                       | YES  |     | NULL    |       |
| gender | enum('male','female','others') | YES  |     | male    |       |
+--------+--------------------------------+------+-----+---------+-------+

insert into t11(id, name) values(1, 'Dean');

select id, name, gender from t11;
+------+------------------+--------+
| id   | name             | gender |
+------+------------------+--------+
|    1 | Dean             | male   |
+------+------------------+--------+

1.2 unique 唯一

单列唯一
这个字段的值不能重复。
联合唯一
单个字段可以重复，多个字段的值总体不能重复。

 create table t12(
    id int unique,  # 单列唯一
    ip char(16),  
    port int,
    unique(ip, port)  # 联合唯一
);

desc t12;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | UNI | NULL    |       |
| ip    | char(16) | YES  | MUL | NULL    |       |
| port  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

insert into t12 values(1, '127.0.0.1', 8080), (2, '127.0.0.1', 8080);
# ERROR 1062 (23000): Duplicate entry '127.0.0.1       -8080' for key 'ip'

1.3 primary key 主键

primary key = not null + unique，即非空且唯一；
主键有助于提升查询效率；
主键是InnoDB存储引擎组织数据的依据，即InnoDB存储引擎在创建表时必须要有主键；
一张表中有且只能有一个主键，如果没有设置主键，则会从上向下检查字段，将遇到的第一个非空且唯一的字段设置为主键。如果没有符合条件的字段，InnoDB会在内部提供一个隐藏的字段作为主键，隐藏意味着无法使用这个字段，因此无法通过隐藏的主键提升查询速度；
一张表中通常将id字段作为主键；
联合主键，多个字段一起作为一个主键。

create table t13(id int primary key);

desc t13;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

create table t13_1(
    ip char(16),
    port int,
    primary key(ip, port)  # 联合主键
    );

desc t13_1;
+-------+----------+------+-----+------------------+-------+
| Field | Type     | Null | Key | Default          | Extra |
+-------+----------+------+-----+------------------+-------+
| ip    | char(16) | NO   | PRI |                  |       |
| port  | int(11)  | NO   | PRI | 0                |       |
+-------+----------+------+-----+------------------+-------+

1.4 auto_increment 自增

自增从1开始。
自增约束只能用于主键字段，不能用于普通字段，否则报错。

create table t14(id int primary key auto_increment);

desc t14;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+

insert into t14 values();
select * from t14;
+----+
| id |
+----+
|  1 |
+----+

总结：
创建表时一般需要id字段，id字段一般需要约束primary key和auto_increment。

1.5 补充 truncate

使用 delete from t14 删除表中数据，自增约束auto_increment的计数器不会清空。
使用truncate语句可以清空表中数据并重置主键自增的计数器。

truncate t14;

2 建立表与表之间的关系(约束)

员工表

id	emp_name	dep_id (外键)
1	谢大脚	1
2	马永强	1
3	刘翠英	2

部门表

id	dep_name
1	生产部
2	销售部

2.1 外键 foreign key

外键是用于建立表与表之间关系的特殊字段。

表与表之间关系主要有三种：一对一、一对多、多对多。

2.2 一对多关系

在判断表与表之间关系时，一定要换位思考，即分别站在两张表的角度考虑。
首先站在员工表的角度考虑，一个员工表中的员工数据只能对应部门表中的一个部门数据；
然后站在部门表的角度考虑，一个部门可以对应多个员工。
结论：员工表与部门表之间的关系是一对多。
SQL语句建立表关系
一对多关系，外键在"多"的一方；
创建表时，先建立"一"的一方的表，因为设置外键时需要指定被关联的表；
插入数据时，必须先插入"一"的一方的表数据。

create table dep(
    id int primary key auto_increment,
    dep_name char(16)
);

create table emp(
	id int primary key auto_increment,
	emp_name char(8),
	dep_id int,
	foreign key(dep_id) references dep(id) 
);

desc emp;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| emp_name | char(6) | YES  |     | NULL    |                |
| dep_id   | int(11) | YES  | MUL | NULL    |                |
+----------+---------+------+-----+---------+----------------+

此时不能直接修改/删除主表中与子表有关联的数据。
方法1：可以先删除子表中相应的数据，再修改/删除主表中的数据。
方法2：做到同步操作，使用级联更新和级联删除。

create table emp1(
	id int primary key auto_increment,
	emp_name char(8),
	dep_id int,
	foreign key(dep_id) references dep(id)  
	on update cascade 
	on delete cascade
);

desc emp1;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| emp_name | char(6) | YES  |     | NULL    |                |
| dep_id   | int(11) | YES  | MUL | NULL    |                |
+----------+---------+------+-----+---------+----------------+

2.3 多对多关系

图书表

id	book_name
1	《概率论与数理统计》
2	《高等数学》

作者表

id	emp_name
1	谢大脚
2	马永强
3	刘翠英

首先站在图书表的角度思考，一本书可以对应多位作者；
然后站在作者表的角度思考，一位作者可以对应多本书。
结论：图书表与作者表之间的关系是多对多。

处理多对多关系，需要使用中间表。

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

create table book(
	id int primary key auto_increment,
	title varchar(32)
);

create table author(
	id int primary key auto_increment,
	name varchar(8)
);

create table book2author(
	id int primary key auto_increment, 
	book_id int, 
	author_id int, 
	foreign key(book_id) references book(id) 
	on update cascade 
	on delete cascade, 
	foreign key(author_id) references author(id) 
	on update cascade 
	on delete cascade 
);

2.4 一对一关系

如果某个表的字段非常多，而且表中一部分字段不是每次检索都需要的，可以将这个表拆分成主表和详细表。

例如用户表和用户详细表
客户表

id	name	age	account_detail_id
1	喜大壮	25	1

客户详细表

id	job	company	level
1	战士	海绵宝宝突击队	上尉

一对一关系中，外键字段建在任意一方都可以，推荐建在查询频率较高的表中。

create table account_detail(
	id int primary key auto_increment,
	job varchar(32),
	company varchar(32),
	level varchar(32)
);

create table account(
	id int primary key auto_increment,
	name varchar(8),
	account_detail_id int unique,
	foreign key (account_detail_id) references account_detail(id) 
	on update cascade 
	on delete cascade 
);

2.5 总结

表关系的建立依赖于外键 foreign key
一对多：外键在多的一方；
多对多：外键在中间表中；
一对一：外键推荐在查询频率较高的一方。

补充：两种建立表与表之间关系的方式

使用外键建立固定的关系；
使用SQL语句在逻辑层面上建立关系。

delete from db1 where id = 1;
delete from db2 where id = 1;

创建外键会消耗额外的资源并且增加了表与表之间的耦合度。
在实际项目中，如果表特别多，可以不做外键处理，而是使用SQL语句建立逻辑层面上的关系。

3 修改表的完整语法

修改表名

alter table 旧表名 rename 新表名;

增加字段

# 默认在字段末尾追加
alter table 表名 add 新字段名 字段类型(宽度) 约束条件;

alter table 表名 add 新字段名 字段类型(宽度) 约束条件 first; 

alter table 表名 add 新字段名 字段类型(宽度) 约束条件 after 旧字段名;

删除字段

alter table 表名 drop 字段名;

修改字段

alter table 表名 modify 字段名 字段类型(宽度) 约束条件;

alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;

4 复制表

SQL语句查询的结果是一张内存中的虚拟表。

复制表，根据SQL语句查询到的虚拟表制作成一张新表。
不能复制主键、外键、索引等。

create table 新表名 as SQL语句;

create table 新表名 as select 字段名... from 旧表名 where 条件;