MySQL（四）完整性约束
一、介绍
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
说明：
1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
二、not null与default
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
default - 默认
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
mysql> create table t15(
-> id int,
-> name char(10),
-> sex enum('male','female')not null default 'male'
-> ) ;
Query OK, 0 rows affected (0.51 sec)
mysql>
mysql> insert into t15(id,name) values(1,'axiba');
Query OK, 1 row affected (0.07 sec)
mysql> select * from t15;
+------+-------+------+
| id | name | sex |
+------+-------+------+
| 1 | axiba | male |
+------+-------+------+
1 row in set (0.00 sec)
三、unique与primary key
unique:唯一
not null unique:不为空且唯一
primary key:非空且唯一
但一个表内只能有一个主键primary key
#只能有一个主建，但是可以有多个not null unique
create table t27(
id int,
name char(10),
primary key(id),
primary key(name)
);
create table t28(
id int not null unique,
name char(10) not null unique
);
#联合唯一:联合的那两个键合起来是唯一
create table t29(
id int,
ip char(15),
port int,
primary key(ip,port)
);
insert into t29 values
(1,'1.1.1.1',3306),
(2,'1.1.1.2',3306),
(3,'1.1.1.1',8080)
;
四、auto_increment
约束字段为自动增长，被约束的字段必须同时被key约束（只能有一个自增字段，并且该字段必须被约束成key）
#不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| 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 student(name) values
-> ('egon'),
-> ('alex')
-> ;
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | egon | male |
| 2 | alex | male |
+----+------+------+
#也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+------+--------+
| id | name | sex |
+----+------+--------+
| 1 | egon | male |
| 2 | alex | male |
| 4 | asb | female |
| 7 | wsb | female |
+----+------+--------+
#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 8 | ysb | male |
+----+------+------+
#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 1 | egon | male |
+----+------+------+
1 row in set (0.00 sec)
步长increment与起始偏移量offset: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');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 3 | egon | male |
+----+------+------+
row in set (0.00 sec)
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 '%incre%';
#基于会话级别
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 '%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
表之间的关系：
实现方式：foreign key
1、多对一：单向的foreign key
左表的多条记录对应右表的一条记录
右表的多条记录不能对应左表的一条记录
2、多对多：双向的foreign key
左表的多条记录对应右表的一条记录
右表的多条记录也能对应左表的一条记录
3、一对一：foreign key+unique
左表的一条记录唯一对应右表的一条记录
（1）多对一
有两张表，员工表和部门表，公司规定一个部门可以有多个员工，但每个员工不能身兼多职，员工工号从110开始
# 先创建被关联的表
mysql> create table dep(
-> id int primary key auto_increment,
-> dep_name char(20) not null unique,
-> dep_comment varchar(50))auto_increment=110;
Query OK, 0 rows affected (0.28 sec)
mysql> insert into dep(dep_name,dep_comment) values
-> ('IT','拿起键盘就是干'),
-> ('Sale','的把嘚吧嘚'),
-> ('Operation','不知道干啥的'),
-> ('HR','有没有学生啊');
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from dep;
+-----+-----------+----------------+
| id | dep_name | dep_comment |
+-----+-----------+----------------+
| 110 | IT | 拿起键盘就是干 |
| 111 | Sale | 的把嘚吧嘚 |
| 112 | Operation | 不知道干啥的 |
| 113 | HR | 有没有学生啊 |
+-----+-----------+----------------+
4 rows in set (0.00 sec)
# 再创表去关联上面的表
mysql> create table emp(
-> id int primary key auto_increment,
-> name char(6) not null,
-> sex enum('male','female') not null default 'male',
-> dep_id int,
-> foreign key(dep_id) references dep(id)
-> on delete cascade # 指定同步删除
-> on update cascade #指定同步更新
-> );
Query OK, 0 rows affected (0.57 sec)
mysql> insert into emp(name,sex,dep_id) values
-> ('egon','male',110),
-> ('alex','male',110),
-> ('yuan','male',113),
-> ('erha','female',112),
-> ('wusir','male',110);
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from emp;
+----+-------+--------+--------+
| id | name | sex | dep_id |
+----+-------+--------+--------+
| 1 | egon | male | 110 |
| 2 | alex | male | 110 |
| 3 | yuan | male | 113 |
| 4 | erha | female | 112 |
| 5 | wusir | male | 110 |
+----+-------+--------+--------+
5 rows in set (0.00 sec)
#解散一个部门
#未指定同步更新、同步删除的参数时，需要这么删除
delete from emp where dep_id=110;
delete from dep where id=110;
#指定后
mysql> delete from dep where id=110;
Query OK, 1 row affected (0.12 sec)
mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex | dep_id |
+----+------+--------+--------+
| 3 | yuan | male | 113 |
| 4 | erha | female | 112 |
+----+------+--------+--------+
2 rows in set (0.00 sec)
mysql> select * from dep;
+-----+-----------+--------------+
| id | dep_name | dep_comment |
+-----+-----------+--------------+
| 111 | Sale | 的把嘚吧嘚 |
| 112 | Operation | 不知道干啥的 |
| 113 | HR | 有没有学生啊 |
+-----+-----------+--------------+
3 rows in set (0.00 sec)
mysql> update dep set id=10086 where id=112;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dep;
+-------+-----------+--------------+
| id | dep_name | dep_comment |
+-------+-----------+--------------+
| 111 | Sale | 的把嘚吧嘚 |
| 113 | HR | 有没有学生啊 |
| 10086 | Operation | 不知道干啥的 |
+-------+-----------+--------------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex | dep_id |
+----+------+--------+--------+
| 3 | yuan | male | 113 |
| 4 | erha | female | 10086 |
+----+------+--------+--------+
2 rows in set (0.00 sec)
（2）多对多
两张表：一个用户可以有多个角色（权限），一个角色（权限）也可以有多个用户
#（1）role表
mysql> create table role(
-> id int primary key auto_increment,
-> name char(7),
-> permmison char(3),
-> comment varchar(30));
Query OK, 0 rows affected (0.23 sec)
mysql> insert into role(name,permmison,comment) values
-> ('admin','rwx','该你表演的时刻到了'),
-> ('会员','r','只要五十'),
-> ('黄 马','rw','一组1314钻戒'),
-> ('国王','rwx','飞机走起来，守护挂起来'),
-> ('游客','x','想空手套白狼');
Query OK, 5 rows affected (0.07 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from role;
+----+-------+-----------+------------------------+
| id | name | permmison | comment |
+----+-------+-----------+------------------------+
| 1 | admin | rwx | 该你表演的时刻到了 |
| 2 | 会员 | r | 只要五十 |
| 3 | 黄 马 | rw | 一组1314钻戒 |
| 4 | 国王 | rwx | 飞机走起来，守护挂起来 |
| 5 | 游客 | x | 想空手套白狼 |
+----+-------+-----------+------------------------+
5 rows in set (0.00 sec)
mysql> create table user(
-> id int primary key auto_increment,
-> name char(6),
-> password varchar(20));
Query OK, 0 rows affected (0.18 sec)
#（2）user表
mysql> insert into user(name,password)values
-> ('井上','bageyalu'),
-> ('龟岛','silasiladi'),
-> ('阿龙','5211314'),
-> ('叮咚','1111111'),
-> ('小度','2222222');
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from user;
+----+------+------------+
| id | name | password |
+----+------+------------+
| 1 | 井上 | bageyalu |
| 2 | 龟岛 | silasiladi |
| 3 | 阿龙 | 5211314 |
| 4 | 叮咚 | 1111111 |
| 5 | 小度 | 2222222 |
+----+------+------------+
5 rows in set (0.00 sec)
mysql> create table user2role(
-> id int primary key auto_increment,
-> user_id int,
-> role_id int,
-> foreign key(user_id) references user(id) on update cascade on delete cascade,
-> foreign key(role_id) references role(id) on update cascade on delete cascade);
Query OK, 0 rows affected (0.24 sec)
#（3）关系表
mysql> insert into user2role(user_id,role_id) values (1,5),(3,4),(3,1),(3,3),(2,5),(4,3),(5,2);
Query OK, 7 rows affected (0.05 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from user2role;
+----+---------+---------+
| id | user_id | role_id |
+----+---------+---------+
| 1 | 1 | 5 |
| 2 | 3 | 4 |
| 3 | 3 | 1 |
| 4 | 3 | 3 |
| 5 | 2 | 5 |
| 6 | 4 | 3 |
| 7 | 5 | 2 |
+----+---------+---------+
7 rows in set (0.00 sec)
（3）一对一
两张表：学生只能是顾客那张表里的
mysql> create table customer(
-> id int primary key auto_increment,
-> name char(10),
-> phone int,
-> qq int);
Query OK, 0 rows affected (0.24 sec)
insert into customer(name,phone,qq)values
-> ('egon',1343434434,66665555),
-> ('alex',1887878478,112223311),
('yuanhao',1323232332,33663365);
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from customer;
+----+---------+------------+-----------+
| id | name | phone | qq |
+----+---------+------------+-----------+
| 1 | egon | 1343434434 | 66665555 |
| 2 | alex | 1887878478 | 112223311 |
| 3 | yuanhao | 1323232332 | 33663365 |
+----+---------+------------+-----------+
3 rows in set (0.00 sec)
mysql> create table student(
-> id int primary key auto_increment,
-> name char(10),
-> class_name char(10),
-> c_id int unique,
-> foreign key(c_id) references customer(id) on delete cascade on update cascade);
Query OK, 0 rows affected (0.53 sec)
mysql> insert into student(name,class_name,c_id) values('yuan','s6',3),('alex','s1',2),('egon','s6',1);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+------+------------+------+
| id | name | class_name | c_id |
+----+------+------------+------+
| 1 | yuan | s6 | 3 |
| 2 | alex | s1 | 2 |
| 3 | egon | s6 | 1 |
+----+------+------------+------+
