MySQL(三) 完整性约束

一.介绍

　　约束条件与数据类型的宽度意义,都是可选参数.

　　作用:用于保证数据的完整性和一致性.

　　主要分为:

1PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
2FOREIGN KEY (FK)    标识该字段为该表的外键
3NOT NULL    标识该字段不能为空
4UNIQUE KEY (UK)    标识该字段的值是唯一的
5AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
6DEFAULT    为该字段设置默认值
7
8UNSIGNED 无符号
9ZEROFILL 使用0填充

二.not null与default

　　not null 指的时字段的值不可为空,null表示空

　　default 默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值

1==================not null====================
2mysql> create table t1(id int); #id字段默认可以插入空
3mysql> desc t1;
4+-------+---------+------+-----+---------+-------+
5| Field | Type    | Null | Key | Default | Extra |
6+-------+---------+------+-----+---------+-------+
7| id    | int(11) | YES  |     | NULL    |       |
8+-------+---------+------+-----+---------+-------+
9mysql> insert into t1 values(); #可以插入空
10
11
12mysql> create table t2(id int not null); #设置字段id不为空
13mysql> desc t2;
14+-------+---------+------+-----+---------+-------+
15| Field | Type    | Null | Key | Default | Extra |
16+-------+---------+------+-----+---------+-------+
17| id    | int(11) | NO   |     | NULL    |       |
18+-------+---------+------+-----+---------+-------+
19mysql> insert into t2 values(); #不能插入空
20ERROR 1364 (HY000): Field 'id' doesn't have a default value
21
22
23
24==================default====================
25#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
26mysql> create table t3(id int default 1);
27mysql> alter table t3 modify id int not null default 1;
28
29
30
31==================综合练习====================
32mysql> create table student(
33    -> name varchar(20) not null,
34    -> age int(3) unsigned not null default 18,
35    -> sex enum('male','female') default 'male',
36    -> hobby set('play','study','read','music') default 'play,music'
37    -> );
38mysql> desc student;
39+-------+------------------------------------+------+-----+------------+-------+
40| Field | Type                               | Null | Key | Default    | Extra |
41+-------+------------------------------------+------+-----+------------+-------+
42| name  | varchar(20)                        | NO   |     | NULL       |       |
43| age   | int(3) unsigned                    | NO   |     | 18         |       |
44| sex   | enum('male','female')              | YES  |     | male       |       |
45| hobby | set('play','study','read','music') | YES  |     | play,music |       |
46+-------+------------------------------------+------+-----+------------+-------+
47mysql> insert into student(name) values('egon');
48mysql> select * from student;
49+------+-----+------+------------+
50| name | age | sex  | hobby      |
51+------+-----+------+------------+
52| egon |  18 | male | play,music |
53+------+-----+------+------------+

View Code

三.unique

　　unique 唯一约束 指该字段的值不能重复

1============设置唯一约束 UNIQUE===============
2方法一：
3create table department1(
4id int,
5name varchar(20) unique,
6comment varchar(100)
7);
8
9
10方法二：
11create table department2(
12id int,
13name varchar(20),
14comment varchar(100),
15constraint uk_name unique(name)
16);
17
18
19mysql> insert into department1 values(1,'IT','技术');
20Query OK, 1 row affected (0.00 sec)
21mysql> insert into department1 values(1,'IT','技术');
22ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

使用方法

1create table service(
2id int primary key auto_increment,
3name varchar(20),
4host varchar(15) not null,
5port int not null,
6unique(host,port) #联合唯一
7);
8
9mysql> insert into service values
10    -> (1,'nginx','192.168.0.10',80),
11    -> (2,'haproxy','192.168.0.20',80),
12    -> (3,'mysql','192.168.0.30',3306)
13    -> ;
14Query OK, 3 rows affected (0.01 sec)
15Records: 3  Duplicates: 0  Warnings: 0
16
17mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
18ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

联合唯一

四.primary key

　　primary key称为主键约束,用于唯一标识表中的一条记录.

　　从约束角度看primary key字段的值不为空且唯一,那直接制用not null+unique不就可以了嘛,要它干什么?

　　主键primary key时innodb存储引擎组织数据的依据,innodb称为索引组织表,一张表中必须有且只有一个主键.

　　一个表可以有单列做主键和多列做主键

1============单列做主键===============
2#方法一：not null+unique
3create table department1(
4id int not null unique, #主键
5name varchar(20) not null unique,
6comment varchar(100)
7);
8
9mysql> desc department1;
10+---------+--------------+------+-----+---------+-------+
11| Field   | Type         | Null | Key | Default | Extra |
12+---------+--------------+------+-----+---------+-------+
13| id      | int(11)      | NO   | PRI | NULL    |       |
14| name    | varchar(20)  | NO   | UNI | NULL    |       |
15| comment | varchar(100) | YES  |     | NULL    |       |
16+---------+--------------+------+-----+---------+-------+
17rows in set (0.01 sec)
18
19#方法二：在某一个字段后用primary key
20create table department2(
21id int primary key, #主键
22name varchar(20),
23comment varchar(100)
24);
25
26mysql> desc department2;
27+---------+--------------+------+-----+---------+-------+
28| Field   | Type         | Null | Key | Default | Extra |
29+---------+--------------+------+-----+---------+-------+
30| id      | int(11)      | NO   | PRI | NULL    |       |
31| name    | varchar(20)  | YES  |     | NULL    |       |
32| comment | varchar(100) | YES  |     | NULL    |       |
33+---------+--------------+------+-----+---------+-------+
34rows in set (0.00 sec)
35
36#方法三：在所有字段后单独定义primary key
37create table department3(
38id int,
39name varchar(20),
40comment varchar(100),
41constraint pk_name primary key(id); #创建主键并为其命名pk_name
42
43mysql> desc department3;
44+---------+--------------+------+-----+---------+-------+
45| Field   | Type         | Null | Key | Default | Extra |
46+---------+--------------+------+-----+---------+-------+
47| id      | int(11)      | NO   | PRI | NULL    |       |
48| name    | varchar(20)  | YES  |     | NULL    |       |
49| comment | varchar(100) | YES  |     | NULL    |       |
50+---------+--------------+------+-----+---------+-------+
51rows in set (0.01 sec)

单列主键

1==================多列做主键================
2create table service(
3ip varchar(15),
4port char(5),
5service_name varchar(10) not null,
6primary key(ip,port)
7);
8
9
10mysql> desc service;
11+--------------+-------------+------+-----+---------+-------+
12| Field        | Type        | Null | Key | Default | Extra |
13+--------------+-------------+------+-----+---------+-------+
14| ip           | varchar(15) | NO   | PRI | NULL    |       |
15| port         | char(5)     | NO   | PRI | NULL    |       |
16| service_name | varchar(10) | NO   |     | NULL    |       |
17+--------------+-------------+------+-----+---------+-------+
18rows in set (0.00 sec)
19
20mysql> insert into service values
21    -> ('172.16.45.10','3306','mysqld'),
22    -> ('172.16.45.11','3306','mariadb')
23    -> ;
24Query OK, 2 rows affected (0.00 sec)
25Records: 2  Duplicates: 0  Warnings: 0
26
27mysql> insert into service values ('172.16.45.10','3306','nginx');
28ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

多列主键

五.auto_increment

　　约束字段为自动增长,被约束的字段必须同时被key约束

1#不指定id，则自动增长
2create table student(
3id int primary key auto_increment,
4name varchar(20),
5sex enum('male','female') default 'male'
6);
7
8mysql> desc student;
9+-------+-----------------------+------+-----+---------+----------------+
10| Field | Type                  | Null | Key | Default | Extra          |
11+-------+-----------------------+------+-----+---------+----------------+
12| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
13| name  | varchar(20)           | YES  |     | NULL    |                |
14| sex   | enum('male','female') | YES  |     | male    |                |
15+-------+-----------------------+------+-----+---------+----------------+
16mysql> insert into student(name) values
17    -> ('egon'),
18    -> ('alex')
19    -> ;
20
21mysql> select * from student;
22+----+------+------+
23| id | name | sex  |
24+----+------+------+
25|  1 | egon | male |
26|  2 | alex | male |
27+----+------+------+
28
29
30#也可以指定id
31mysql> insert into student values(4,'asb','female');
32Query OK, 1 row affected (0.00 sec)
33
34mysql> insert into student values(7,'wsb','female');
35Query OK, 1 row affected (0.00 sec)
36
37mysql> select * from student;
38+----+------+--------+
39| id | name | sex    |
40+----+------+--------+
41|  1 | egon | male   |
42|  2 | alex | male   |
43|  4 | asb  | female |
44|  7 | wsb  | female |
45+----+------+--------+
46
47
48#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
49mysql> delete from student;
50Query OK, 4 rows affected (0.00 sec)
51
52mysql> select * from student;
53Empty set (0.00 sec)
54
55mysql> insert into student(name) values('ysb');
56mysql> select * from student;
57+----+------+------+
58| id | name | sex  |
59+----+------+------+
60|  8 | ysb  | male |
61+----+------+------+
62
63#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
64mysql> truncate student;
65Query OK, 0 rows affected (0.01 sec)
66
67mysql> insert into student(name) values('egon');
68Query OK, 1 row affected (0.01 sec)
69
70mysql> select * from student;
71+----+------+------+
72| id | name | sex  |
73+----+------+------+
74|  1 | egon | male |
75+----+------+------+
76row in set (0.00 sec)

View Code

1#在创建完表后，修改自增字段的起始值
2mysql> create table student(
3    -> id int primary key auto_increment,
4    -> name varchar(20),
5    -> sex enum('male','female') default 'male'
6    -> );
7
8mysql> alter table student auto_increment=3;
9
10mysql> show create table student;
11.......
12ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
13
14mysql> insert into student(name) values('egon');
15Query OK, 1 row affected (0.01 sec)
16
17mysql> select * from student;
18+----+------+------+
19| id | name | sex  |
20+----+------+------+
21|  3 | egon | male |
22+----+------+------+
23row in set (0.00 sec)
24
25mysql> show create table student;
26.......
27ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
28
29
30#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
31create table student(
32id int primary key auto_increment,
33name varchar(20),
34sex enum('male','female') default 'male'
35)auto_increment=3;
36
37
38
39
40#设置步长
41sqlserver：自增步长
42    基于表级别
43    create table t1（
44        id int。。。
45    ）engine=innodb,auto_increment=2 步长=2 default charset=utf8
46
47mysql自增的步长：
48    show session variables like 'auto_inc%';
49    
50    #基于会话级别
51    set session auth_increment_increment=2 #修改会话级别的步长
52
53    #基于全局级别的
54    set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）
55
56
57#！！！注意了注意了注意了！！！
58If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 
59翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋
60比如：设置auto_increment_offset=3，auto_increment_increment=2
61
62
63
64
65mysql> set global auto_increment_increment=5;
66Query OK, 0 rows affected (0.00 sec)
67
68mysql> set global auto_increment_offset=3;
69Query OK, 0 rows affected (0.00 sec)
70
71mysql> show variables like 'auto_incre%'; #需要退出重新登录
72+--------------------------+-------+
73| Variable_name            | Value |
74+--------------------------+-------+
75| auto_increment_increment | 1     |
76| auto_increment_offset    | 1     |
77+--------------------------+-------+
78
79
80
81create table student(
82id int primary key auto_increment,
83name varchar(20),
84sex enum('male','female') default 'male'
85);
86
87mysql> insert into student(name) values('egon1'),('egon2'),('egon3');
88mysql> select * from student;
89+----+-------+------+
90| id | name  | sex  |
91+----+-------+------+
92|  3 | egon1 | male |
93|  8 | egon2 | male |
94| 13 | egon3 | male |
95+----+-------+------+

步长:auto_increment_increment,起始偏移量:auto_increment_offset

六.foreign key

　　如何找出两张表之间的关系

1分析步骤：
2#1、先站在左表的角度去找
3是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
4
5#2、再站在右表的角度去找
6是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
7
8#3、总结：
9#多对一：
10如果只有步骤1成立，则是左表多对一右表
11如果只有步骤2成立，则是右表多对一左表
12
13#多对多
14如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
15
16#一对一:
17如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

　　建立表之间的关系

1#一对多或称为多对一
2三张表：出版社，作者信息，书
3
4一对多（或多对一）：一个出版社可以出版多本书
5
6关联方式：foreign key

1=====================多对一=====================
2create table press(
3id int primary key auto_increment,
4name varchar(20)
5);
6
7create table book(
8id int primary key auto_increment,
9name varchar(20),
10press_id int not null,
11foreign key(press_id) references press(id)
12on delete cascade
13on update cascade
14);
15
16
17insert into press(name) values
18('北京工业地雷出版社'),
19('人民音乐不好听出版社'),
20('知识产权没有用出版社')
21;
22
23insert into book(name,press_id) values
24('九阳神功',1),
25('九阴真经',2),
26('九阴白骨爪',2),
27('独孤九剑',3),
28('降龙十巴掌',2),
29('葵花宝典',3)
30;

View Code

1#多对多
2三张表：出版社，作者信息，书
3
4多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
5　　
6关联方式：foreign key+一张新的表

1=====================多对多=====================
2create table author(
3id int primary key auto_increment,
4name varchar(20)
5);
6
7
8#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
9create table author2book(
10id int not null unique auto_increment,
11author_id int not null,
12book_id int not null,
13constraint fk_author foreign key(author_id) references author(id)
14on delete cascade
15on update cascade,
16constraint fk_book foreign key(book_id) references book(id)
17on delete cascade
18on update cascade,
19primary key(author_id,book_id)
20);
21
22
23#插入四个作者，id依次排开
24insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
25
26#每个作者与自己的代表作如下
27egon: 
28九阳神功
29九阴真经
30九阴白骨爪
31独孤九剑
32降龙十巴掌
33葵花宝典
34alex: 
35九阳神功
36葵花宝典
37yuanhao:
38独孤九剑
39降龙十巴掌
40葵花宝典
41wpq:
42九阳神功
43
44
45insert into author2book(author_id,book_id) values
46(1,1),
47(1,2),
48(1,3),
49(1,4),
50(1,5),
51(1,6),
52(2,1),
53(2,6),
54(3,4),
55(3,5),
56(3,6),
57(4,1)
58;

View Code

1#一对一
2两张表：学生表和客户表
3
4一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系
5
6关联方式：foreign key+unique

1#一定是student来foreign key表customer，这样就保证了：
2#1 学生一定是一个客户，
3#2 客户不一定是学生，但有可能成为一个学生
4
5
6create table customer(
7id int primary key auto_increment,
8name varchar(20) not null,
9qq varchar(10) not null,
10phone char(16) not null
11);
12
13
14create table student(
15id int primary key auto_increment,
16class_name varchar(20) not null,
17customer_id int unique, #该字段一定要是唯一的
18foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
19on delete cascade
20on update cascade
21);
22
23
24#增加客户
25insert into customer(name,qq,phone) values
26('李飞机','31811231',13811341220),
27('王大炮','123123123',15213146809),
28('守榴弹','283818181',1867141331),
29('吴坦克','283818181',1851143312),
30('赢火箭','888818181',1861243314),
31('战地雷','112312312',18811431230)
32;
33
34
35#增加学生
36insert into student(class_name,customer_id) values
37('脱产3班',3),
38('周末19期',4),
39('周末19期',5)
40;

View Code

级联操作

　　指的是就是同步更新和删除
　　语法:在创建外键时 在后面添加 on update cascade 同步更新
　　　　　　　　　　　　　　　　on delete cascade 同步删除

实例:

1create table class(id int primary key auto_increment,name char(10));
2
3create table student(
4id int primary key auto_increment,
5name char(10),
6c_id int,
7foreign key(c_id) references class(id)
8on update cascade
9on delete cascade
10);
11
12insert into class value(null,"python3期");
13insert into student value(null,"罗傲宇",1);

View Code

对主表的id进行更新
以及删除某条主表记录 来验证效果