数据库 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 条件;