05、约束条件
一、介绍
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
# 分类
primary key 标识该字段为该表的主键，可以唯一的标识记录
foreign key 标识该字段为该表的外键
not null 标识该字段不能为空
unique key 标识该字段的值时唯一的
auto_increment 标识该字段的值自动增长（整数类型，而且为主键）
default 为该字段设置默认值
unsigned 无符号
zerofill 零填充
二、not null 与 default
1、介绍
not null - 不可空
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
2、验证
#=======================not null=========================#
mysql> create database db02; # id字段默认可以插入空
mysql> use db02;
mysql> create table t1(id int);
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values(); #可以插入空
mysql> select * from t1;
+------+
| id |
+------+
| NULL |
+------+
mysql> create table t2(id int not null); #设置字段id不为空
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> insert into t2 values(); #不能插入空
ERROR 1364 (HY000): Field 'id' doesn not have a default value
# =======================default=========================#
# 设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
mysql> create table t3(id int,name varchar(16) default '匿名');
mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | 匿名 | |
+-------+-------------+------+-----+---------+-------+
mysql> insert into t3(id) values(1);
mysql> insert into t3 values(2,'momo');
mysql> select * from t3;
+------+--------+
| id | name |
+------+--------+
| 1 | 匿名 |
| 2 | momo |
+------+--------+
# =======================综合练习=========================#
mysql> create table student(
-> name varchar(20) not null,
-> age int(3) unsigned not null default 18,
-> sex enum('male','female') default 'male',
-> hobby set('play','study','music') default 'play,music'
-> );
mysql> desc student;
+-------+----------------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------------+------+-----+------------+-------+
| name | varchar(20) | NO | | NULL | |
| age | int(3) unsigned | NO | | 18 | |
| sex | enum('male','female') | YES | | male | |
| hobby | set('play','study','music') | YES | | play,music | |
+-------+----------------------------+------+-----+------------+-------+
mysql> insert into student(name) values('momo');
mysql> select * from student;
+------+-----+------+------------+
| name | age | sex | hobby |
+------+-----+------+------------+
| momo | 18 | male | play,music |
+------+-----+------+------------+
三、unique
# 单列唯一
mysql> create table t4(
-> id int,
-> name varchar(16) unique
-> );
mysql> insert into t4 values(1,'momo');
mysql> select * from t4;
+------+------+
| id | name |
+------+------+
| 1 | momo |
+------+------+
mysql> insert into t4 values(2,'momo');
ERROR 1062 (23000): Duplicate entry 'momo' for key 'name'
# 联合唯一
mysql> create table t5(
-> id int,
-> host varchar(32),
-> port varchar(32),
-> unique(host,port)
-> );
mysql> insert into t5 values
-> (1,'192.168.0.10',80),
-> (2,'192.168.0.20',80);
mysql> select * from t5;
+------+--------------+------+
| id | host | port |
+------+--------------+------+
| 1 | 192.168.0.10 | 80 |
| 2 | 192.168.0.20 | 80 |
+------+--------------+------+
mysql> insert into t5 values(3,'192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
# not null + unique 联用
mysql> create table t1(id int not null unique);
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
四、primary key
1、介绍
主键：
1、从约束条件上而言主键相当于not null + unique(非空且唯一)
2、主键的功能目前简单的理解为能够加快数据的查询速度相当于书的目录
3、InnoDB存储引擎规定每张表都必须有且只有一个主键
1、表中没有任何的主键和其他约束条件
InnoDB默认会采用一个隐藏字段作为表的主键
2、表中没有主键但是有非空且唯一的字段
InnoDB会自动将该字段升级为主键
结论:每张表都必须要有一个id(sid nid uid)字段并且该字段就是主键
2、单列主键
# 方法一：not null+unique
mysql> create table department1(
-> id int not null unique, #主键
-> name varchar(20) not null unique,
-> comment varchar(100)
-> );
mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | UNI | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
# 方法二：在某一个字段后用primary key
mysql> create table department2(
-> id int primary key, #主键
-> name varchar(20),
-> comment varchar(100)
-> );
mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
# 方法三：在所有字段后单独定义primary key
mysql> create table department3(
-> id int,
-> name varchar(20),
-> comment varchar(100),
-> constraint pk_name primary key(id) # 创建主键并为其命名pk_name
-> );
mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(20) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3、多列主键
mysql> create table service(
-> ip varchar(15),
-> port char(5),
-> service_name varchar(10) not null,
-> primary key(ip,port)
-> );
mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip | varchar(15) | NO | PRI | | |
| port | char(5) | NO | PRI | | |
| service_name | varchar(10) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
mysql> insert into service values
-> ('172.168.45.10','3306','mysql'),
-> ('172.168.45.11','3306','mariadb')
-> ;
mysql> select * from service;
+---------------+-------+--------------+
| ip | port | service_name |
+---------------+-------+--------------+
| 172.168.45.10 | 3306 | mysql |
| 172.168.45.11 | 3306 | mariadb |
+---------------+-------+--------------+
mysql> insert into service values('172.168.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.168.45.10-3306 ' for key 'PRIMARY'
五、auto_increment
约束字段为自动增长，被约束的字段必须同时被key约束；专门配合主键一起使用的 让主键能够自增
1、验证
# 不指定id，则自动增长
ysql> create table stu_info(
-> id int primary key auto_increment,
-> name varchar(20),
-> sex enum('male','female') default 'male'
-> );
mysql> desc stu_info;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | male | |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into stu_info(name) values
-> ('momo'),
-> ('alex');
mysql> select * from stu_info;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | momo | male |
| 2 | alex | male |
+----+------+------+
# 也可以指定id
mysql> insert into stu_info values
-> (4,'lily','female'),
-> (7,'tom','male');
mysql> select * from stu_info;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | momo | male |
| 2 | alex | male |
| 4 | lily | female |
| 7 | tom | male |
+----+------+--------+
# 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from stu_info;
mysql> select * from stu_info;
Empty set (0.00 sec)
mysql> insert into stu_info(name) values('kevin');
mysql> select * from stu_info;
+----+-------+------+
| id | name | sex |
+----+-------+------+
| 8 | kevin | male |
+----+-------+------+
# 应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate stu_info;
mysql> insert into stu_info(name) values('momo');
mysql> select * from stu_info;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | momo | male |
+----+------+------+
2、总结
主键的自增是不会收到delete from删除操作的影响 truncate既可以清空表数据也会重置主键值
3、步长：auto_increment_increment，起始偏移量：auto_increment_offset（了解知识）
# 在创建完表后，修改自增字段的起始值
mysql> create table student(
-> id int primary key auto_increment,
-> name varchar(20),
-> sex enum('male','female') default 'male'
-> );
mysql> alter table student auto_increment=3;
mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
mysql> insert into student(name) values('egon');
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 3 | egon | male |
+----+------+------+
mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
# 也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;
# 设置步长
sqlserver：自增步长
基于表级别
create table t1（
id int。。。
）engine=innodb,auto_increment=2 步长=2 default charset=utf8
mysql自增的步长：
show session variables like 'auto_inc%';
#基于会话级别
set session auth_increment_increment=2 #修改会话级别的步长
#基于全局级别的
set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）
# ！！！注意了注意了注意了！！！
If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.
翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋
比如：设置auto_increment_offset=3，auto_increment_increment=2
mysql> set global auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)
mysql> set global auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_incre%'; # 需要退出重新登录
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
mysql> insert into student(name) values('egon1'),('egon2'),('egon3');
mysql> select * from student;
+----+-------+------+
| id | name | sex |
+----+-------+------+
| 3 | egon1 | male |
| 8 | egon2 | male |
| 13 | egon3 | male |
+----+-------+------+
六、foreign key
1、快速理解
1.定义一张员工表
id name age dep_name dep_desc
1 jason 18 外交部 搞外交
2 kevin 28 教学部 教书育人
3 tony 38 教学部 教书育人
4 oscar 48 安保部 保家卫国
5 jackson 58 财务部 发工资
"""
上述表不合理之处
1.表内部数据混乱(可忽略)
2.反复的录入重复数据(可忽略)
3.修改数据太过繁琐 浪费磁盘空间(可忽略)
4.极大地影响了操作数据的效率
"""
2.将上述表拆分成两张表
id name age
1 jason 18
2 kevin 28
3 tony 38
4 oscar 48
5 jackson 58
id dep_name dep_desc
1 外交部 搞外交
2 教学部 教书育人
3 安保部 保家卫国
4 财务部 发工资
"""拆分完之后解决了上述四个缺陷"""
外键
用来记录表与表之间的关系
2、如何找出两张表之间的关系
分析步骤： #1、先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id） #2、再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id） #3、总结： #多对一： 如果只有步骤1成立，则是左表多对一右表 如果只有步骤2成立，则是右表多对一左表 #多对多 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 #一对一: 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
3、多对一
"""查找表关系:换位思考"""
书籍表与出版社表
1.先站在书籍表
问:一本书能够对应多个出版社
答:不可以
2.再站在出版社表
问:一个出版社能否对应多本书
答:可以
结论:一个可以一个不可以 表关系为"多对一"
那么外键字段建在"多"的一方
# =========================== 多对一 ================================
mysql> create table publish(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
mysql> create table book(
-> id int primary key auto_increment,
-> name varchar(20),
-> publish_id int not null,
-> foreign key(publish_id) references publish(id)
-> on delete cascade
-> on update cascade
-> );
mysql> insert into publish(name) values
-> ('东方出版社'),
-> ('西方出版社'),
-> ('中央出版社');
mysql> insert into book(name,publish_id) values
-> ('九阳神功',1),
-> ('九阴真经',2),
-> ('九阴白骨爪',2),
-> ('独孤九剑',3),
-> ('降龙十八掌',2),
-> ('葵花宝典',3);
mysql> select * from publish;
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | 东方出版社 |
| 2 | 西方出版社 |
| 3 | 中央出版社 |
+----+-----------------+
mysql> select * from book;
+----+-----------------+------------+
| id | name | publish_id |
+----+-----------------+------------+
| 1 | 九阳神功 | 1 |
| 2 | 九阴真经 | 2 |
| 3 | 九阴白骨爪 | 2 |
| 4 | 独孤九剑 | 3 |
| 5 | 降龙十八掌 | 2 |
| 6 | 葵花宝典 | 3 |
+----+-----------------+------------+
# 总结
1.在创建表的时候需要先创建被关联表(没有外键的表)
2.在写入数据的时候也需要先写入被关联表(没有外键的表)
3.被关联表里面的数据无法直接删除和修改关联字段的操作，需要级联更新和删除
4、多对多
以书籍表与作者表为例
1.先站在书籍表的基础之上
问:一本书能否对应多个作者
答:可以
2.在站在作者表的基础之上
问:一个作者能否对应多本书
答:可以
结论:两个都可以那么表关系就是"多对多"
外键字段建在第三张关系表中
# =========================== 多对多 ================================
mysql> create table author(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
mysql> create table books(
-> id int primary key auto_increment,
-> name varchar(32)
-> );
mysql> create table books2author(
-> id int primary key auto_increment,
-> author_id int not null,
-> books_id int not null,
# -> constraint fk_author foreign key(author_id) references author(id)
-> foreign key(author_id) references author(id)
-> on delete cascade
-> on update cascade,
# -> constraint fk_book foreign key(book_id) references book(id)
-> foreign key(books_id) references books(id)
-> on delete cascade
-> on update cascade
# -> primary key(author_id,book_id) 要加主键的话，上一句要在后面加,
-> );
mysql> insert into author(name) values('momo'),('egon'),('lily');
mysql> insert into books(name) values
-> ('九阳神功'),
-> ('九阴真经'),
-> ('九阴白骨爪'),
-> ('独孤九剑'),
-> ('降龙十八掌'),
-> ('葵花宝典');
mysql> insert into books2author(author_id,books_id) values
-> (1,1),
-> (1,2),
-> (1,5),
-> (2,1),
-> (2,6),
-> (3,3),
-> (3,4);
mysql> select * from author;
+----+------+
| id | name |
+----+------+
| 1 | momo |
| 2 | egon |
| 3 | lily |
+----+------+
mysql> select * from books;
+----+-----------------+
| id | name |
+----+-----------------+
| 1 | 九阳神功 |
| 2 | 九阴真经 |
| 3 | 九阴白骨爪 |
| 4 | 独孤九剑 |
| 5 | 降龙十八掌 |
| 6 | 葵花宝典 |
+----+-----------------+
mysql> select * from books2author;
+----+-----------+----------+
| id | author_id | books_id |
+----+-----------+----------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 5 |
| 4 | 2 | 1 |
| 5 | 2 | 6 |
| 6 | 3 | 3 |
| 7 | 3 | 4 |
+----+-----------+----------+
注意：被关联字段必须时唯一的，被关联字段可以使用 unique key 或者 primary key
# 表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
mysql> create table department(
-> id int primary key,
-> name varchar(20) not null
-> )engine=innodb;
# dpt_id外键，关联父表（department主键id），同步更新，同步删除
mysql> create table employee(
-> id int primary key,
-> name varchar(20) not null,
-> dpt_id int,
-> constraint fk_name foreign key(dpt_id)
-> references department(id)
-> on delete cascade
-> on update cascade
-> )engine=innodb;
# 先往父表department中插入记录
mysql> insert into department values
-> (1,'事业部'),
-> (2,'资源部'),
-> (3,'销售部');
mysql> select * from department;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 事业部 |
| 2 | 资源部 |
| 3 | 销售部 |
+----+-----------+
# 再往子表employee中插入记录
mysql> insert into employee values
-> (1,'momo',1),
-> (2,'lily',3),
-> (3,'tom',2),
-> (4,'kevin',2),
-> (5,'jane',1);
mysql> select * from employee;
+----+-------+--------+
| id | name | dpt_id |
+----+-------+--------+
| 1 | momo | 1 |
| 2 | lily | 3 |
| 3 | tom | 2 |
| 4 | kevin | 2 |
| 5 | jane | 1 |
+----+-------+--------+
# 删父表department，子表employee中对应的记录跟着删
mysql> delete from department where id=3;
mysql> select * from employee;
+----+-------+--------+
| id | name | dpt_id |
+----+-------+--------+
| 1 | momo | 1 |
| 3 | tom | 2 |
| 4 | kevin | 2 |
| 5 | jane | 1 |
+----+-------+--------+
# 更新父表department，子表employee中对应的记录跟着改
mysql> update department set id=100 where id=2;
mysql> select * from employee;
+----+-------+--------+
| id | name | dpt_id |
+----+-------+--------+
| 1 | momo | 1 |
| 3 | tom | 100 |
| 4 | kevin | 100 |
| 5 | jane | 1 |
+----+-------+--------+
5、一对一
# 场景
客户表与学生表
QQ用户表
以用户表与用户详情表为例
1.先站在用户表的基础之上
问:一个用户能否对应多个用户详情
答:不可以
2.在站在用户详情表基础之上
问:一个用户详情能否对应多个用户
答:不可以
结论:换位思考之后两边都不可以 那么表关系有两种
1.没有关系(用膝盖都能判断出来)
2.一对一关系
外键字段建在哪里?
理论上建在任何一方都可以但是推荐建在查询频率较高的表中
# ============================= 一对一 ==================================
mysql> create table customer(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> phone char(16) not null
-> );
mysql> create table students(
-> id int primary key auto_increment,
-> class_name varchar(20) not null,
-> customer_id int unique,
-> foreign key(customer_id) references customer(id)
-> on delete cascade
-> on update cascade
-> );
mysql> insert into customer(name,phone) values
-> ('李飞机',13811341220),
-> ('王大炮',15213146809),
-> ('守榴弹',1867141331),
-> ('吴坦克',1851143312),
-> ('赢火箭',1861243314),
-> ('战地雷',18811431230);
mysql> insert into students(class_name,customer_id) values
-> ('脱产3班',3),
-> ('周末19期',4),
-> ('周末19期',5);
mysql> select * from customer;
+----+-----------+------------------+
| id | name | phone |
+----+-----------+------------------+
| 1 | 李飞机 | 13811341220 |
| 2 | 王大炮 | 15213146809 |
| 3 | 守榴弹 | 1867141331 |
| 4 | 吴坦克 | 1851143312 |
| 5 | 赢火箭 | 1861243314 |
| 6 | 战地雷 | 18811431230 |
+----+-----------+------------------+
mysql> select * from students;
+----+-------------+-------------+
| id | class_name | customer_id |
+----+-------------+-------------+
| 1 | 脱产3班 | 3 |
| 2 | 周末19期 | 4 |
| 3 | 周末19期 | 5 |
+----+-------------+-------------+
