表的完整性约束、表与表的关系
文章目录
- 表的完整性约束、表与表的关系
- 一、表的完整性约束
- 1.1 not null和default
- 1.2 unique
- 1.3 primary key
- 1.4 auto_increment
- 二、表与表的关系
- 2.1一对多
- 2.2多对多
- 2.3一对一
一、表的完整性约束
表的完整性约束如下：
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
1.1 not null和default
对于大部分的数据类型而言默认是允许空值的，如果不想为空需要设置not null，也可以设置默认值default，这样插入值为空时会自动填充默认值。
create table t1(id int);
desc t1;+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+#插入空值
insert into t1 values();#t2的id不允许为空
create table t2(id int not null);
desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+#空值插入失败
insert into t2 values();ERROR 1364 (HY000): Field 'id' doesn't have a default value#设置默认值
create table t3(id int default 1);
#修改t3为不能空，默认值1
alter table t3 modify id int not null default 1;
1.2 unique
unique表示值唯一。
#使用方法一：
create table department1(id int,name varchar(20) unique);
#使用方法二：
create table department2(id int,name varchar(20),constraint uk_name unique(name));
联合唯一会将多个字段联合起来，插值时要保证联合的多个字段不能重复。
create table t1( id primary key,name varchar(10),age int,unique(name,age));
insert t1 values(1,'aa',12);
insert t1 values(2,'zz',12);
insert t1 values(3,'zz',14);+----+------+------+
| id | name | age |
+----+------+------+
| 2 | aa | 12 |
| 1 | zz | 12 |
| 3 | zz | 14 |
+----+------+------+
对于以及创建的表需要加入联合唯一约束可以使用alter table t1 add unique(name,age);
1.3 primary key
主键primary key是innodb存储引擎组织数据的依据，一张表中必须有且只有一个主键，并且主键唯一且不能为空。
对于主键而言单列可以做主键，多列也可以做主键（复合主键）。
#单列主键
#方式一
create table department2(id int primary key, name varchar(20));#方式二
create table department3(id int,name varchar(20),constraint pk_name primary key(id);
需要说明的是一张表如果不设置主键，mysql会自动寻找第一个不为空且唯一的字段作为主键。如果没有不为空且唯一的字段，mysql会自动创建隐藏字段作为主键，主键是mysql索引记录的重要字段，隐藏主键会对mysql的索引效率产生一定的影响。
create table t1(id int not null unique);+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
#多列主键(联合主键)
#联合主键的唯一性是指联合的多个字段整体不重复
create table service(ip varchar(15),port char(5),service_name varchar(10) not null,
primary key(ip,port));
desc service;+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip | varchar(15) | NO | PRI | NULL | |
| port | char(5) | NO | PRI | NULL | |
| service_name | varchar(10) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
1.4 auto_increment
auto_increment表示自增，只能给key使用并且默认值从1开始
create table student(id int primary key auto_increment,name varchar(20));
desc student;+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+#设置自增后当不传入值，字段值会从1开始自动增加
insert student(name) values('a'),('b');
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
+----+------+#自增字段使用delete清空以后依然会在之前的数值上增加
delete * from student;
insert student(name) values('c');+----+------+
| id | name |
+----+------+
| 3 | c |
+----+------+#需要使用truncate才能清除自增值
truncate student;
create table student(id int primary key auto_increment,name varchar(20)）#对于已经创建的表可以修改自增的起始值
#可以使用show create table student;查看下一次自增值的信息
alter table student auto_increment=3;#也可以建表时直接设置起始值
create table student(id int primary key auto_increment,name varchar(20))auto_increment=3;#设置步长
#mysql设置步长只能设置会话级和全局的步长(会话级值作用于当前数据库，全局指作用于这个mysql)
#mysql只能设置表级的初始值，而不能设置表级的步长#auto_increment_increment表示步长，auto_increment_offset表示起始值，如果auto_increment_offset
#大于auto_increment_increment的值，auto_increment_offset的值会被忽略
#global表示全局，如果想设置会话级变量可以改为set session auto_increment_increment=5;
#下面的设置必须重新登录以后才能生效
set global auto_increment_increment=5;
set global auto_increment_offset=3;#查看变量
show variables like 'auto_incre%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 3 |
+--------------------------+-------+create table t1(id int primary key auto_increment);
insert t1 values();
insert t1 values();+----+
| id |
+----+
| 3 |
| 8 |
+----+
二、表与表的关系
表与表之间有三种关系：一对一、一对多和多对多。
我们现在假设有三组表格：出版社和书、作者和书、用户和vip。
分析一下我们可以知道出版社和书之间属于是一对多的关系：一本书由一个出版社出版，而一个出版社可以出版多本书。作者和书之间属于多对多的关系：一个作者可以写多本书，同样一本书也可以有多个作者。用户和vip的关系是一对一：一个用户对应一条vip记录，但是并非所有用户都是vip。
在正式创建关联表之前需要强调一点：被关联字段必须是唯一的，也就是说被关联字段可以使unique key或者primary key。
2.1一对多
#一对多(多对一)
#外键需要放在多的表中(书)
#先创建父表(出版社)，再创建关联的表(书)
create table publish(id int primary key auto_increment,name varchar(20));
#publish_id用于存放publish表中关联记录的id，on delete cascade on update cascade表示同步删除同步更新
create table book(id int primary key auto_increment,name varchar(20),publish_id int,
foreign key(publish_id) references publish(id) on delete cascade on update cascade);#插入数据时要先插父表的，再插关联表的
insert publish values(1,'南京出版社');
#如果关联表中插入父表中不存在的id号时会报错
insert book values(1,'aa',1);
insert book values(2,'bb',1);+----+------+------------+
| id | name | publish_id |
+----+------+------------+
| 1 | aa | 1 |
| 2 | bb | 1 |
+----+------+------------++----+-----------------+
| id | name |
+----+-----------------+
| 1 | 南京出版社 |
+----+-----------------+#同样更改父表的id以后关联表中的id也会跟着改变
update publish set id=22222 where id=1;
+-------+-----------------+
| id | name |
+-------+-----------------+
| 22222 | 南京出版社 |
+-------+-----------------+
+----+------+------------+
| id | name | publish_id |
+----+------+------------+
| 1 | aa | 22222 |
| 2 | bb | 22222 |
+----+------+------------+#删除记录时也必须先删除父表，父表记录被删除以后关联表记录也会被删除
delete from publish where id=1;
#两个表都变为空值
Empty set (0.00 sec)#删除表格需要先删关联表，再删除父表
2.2多对多
#多对多
#需要令建一张表存放多对多的关联关系create table author(id int primary key auto_increment,name varchar(20));
create table book(id int primary key auto_increment,name varchar(20));
#author_book表用来存放author和book的关联关系
create table author_book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
foreign key(author_id) references author(id) on delete cascade on update cascade,
foreign key(book_id) references book(id) on delete cascade on update cascade,
#保证插入的author_id与book_id对应关系唯一
primary key(author_id,book_id)
);#插值要先插author和book，再插关联表
insert author(name) values('a'),('b');
insert book(name) values('红楼梦'),('三国无双'),('爸爸去哪里了');
insert author_book(author_id,book_id) values(1,2),(1,3),(2,1);#从下面的三张表中我们就可以清楚的看到哪些作者写了哪些书。而哪些书是被哪些作者写的。
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | 红楼梦 |
| 2 | 三国无双 |
| 3 | 爸爸去哪里了 |
+----+--------------------++----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
+----+------++----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 1 |
+----+-----------+---------+#更新、删除记录和删除表格与一对多的关系同理，这里不再介绍
2.3一对一
#一对一
#一对一的关联一般建在记录数较少的表中(vip表)并且要求表的关联字段不能重复
create table user(id int primary key auto_increment,name varchar(20) not null);
create table vip(id int primary key auto_increment,number int,user_id int unique,
foreign key(user_id) references user(id) on delete cascade on update cascade);insert user(name) values('a'),('b'),('c');
insert vip(number,user_id) values(1234,1),(1235,3);+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------++----+--------+---------+
| id | number | user_id |
+----+--------+---------+
| 1 | 1234 | 1 |
| 2 | 1235 | 3 |
+----+--------+---------+#更新、删除记录和删除表格与一对多的关系同理，这里不再介绍