day037-2 mysql数据库完整性约束

本节内容：

1、介绍什么是约束条件
2、not null（不能为空）与default（默认值）
3、unique（唯一）
4、primary key（主键，不为空且唯一）
5、auto_incerment(自增id序号)
6、foreign key（外键，表之间的指向关系，关联关系）

一、介绍什么是约束条件

约束条件与数据类型的宽度一样，都是可选参数

　　作用：用于保证数据的完整性和一致性
　　主要分为：

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键

NOT NULL    标识该字段不能为空，即不可为null,但是可以为''，空字符并不是空

UNIQUE KEY (UK)    标识该字段的值是唯一的

AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）

DEFAULT    为该字段设置默认值,设置以后，当not null条件下，不输入，就为默认值

UNSIGNED 无符号
ZEROFILL 使用0填充

Mysql

Copy

一些说明

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
索引 (index,unique...)

Python

Copy

二、not null与default

1、not null（不可空）

是否可空，null表示空，非字符串，

　　not null – 不可空（指什么都没有输入），但是空字符可以，
　　null – 可空，不输入任何都可以

2、default（默认值）

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

3、输入相关命令的示例及结论

create table tb2(

　　　　id int not null default 2, # int类型的，不可为空且设置了默认值
　　　　num int not null # 不可为空，但没有设置默认值，默认值为null
　　);

# 1、非严格模式，如果int类型，不传值,因null不是int类型，该处的值将默认为0；
mysql> insert into tb2(id,num) values (1,); # 只插入一条数据也是可以的

# 注意：即便是你只给一个字段传值了，那么也是生成一整条记录，
这条记录的其他字段的值如果可以为空，那么他们就都是null空值，
如果不能为空，就会报错。

# 2、严格模式，传非int类型的值，该处的值也是0
mysql> insert into tb2(id,num) values (1,'疏影');

Mysql

Copy

综合练习

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','read','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','read','music') | YES  |     | play,music |       |
+-------+------------------------------------+------+-----+------------+-------+
mysql> insert into student(name) values('chao');
mysql> select * from student;
+------+-----+------+------------+
| name | age | sex  | hobby      |
+------+-----+------+------------+
| chao|  18 | male | play,music |
+------+-----+------+------------+

Python

Copy

三、unique（唯一性，一种key）

独一无二，唯一属性：id，身份证号等

　　是一种key，唯一键，是在数据类型之外的附加属性，
其实还有加速查询的作用，后面再讲这个。

1、unique创建

unique创建

============设置唯一约束 UNIQUE===============
方法一：
create table department1(
id int,
name varchar(20) unique,  # 设置唯一
comment varchar(100)
);


方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)  # 设置唯一
);

mysql> insert into department1 values(1,'IT','技术');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技术');  # 报错，唯一性，不可重复
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

Python

Copy

2、联合唯一（多值相同，方为重复）

就是多个值都相同，才是重复；
多个值，只要有一项不同，就不影响唯一性
例如：两个妹子的打扮，
联合唯一可以比喻成衣服和裤子同时一样才是重复，才跟唯一性冲突
裤子一样，衣服不同，这时并不是重复，并不影响唯一性

联合唯一

create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) # 联合唯一，这两个值都相同，才是重复，才是唯一
);

mysql> insert into service values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.30',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

Python

Copy

四、primary key(主键，设置后不为空且唯一）

从约束角度看primary key字段的值不为空且唯一，
那我们直接使用not null+unique不就可以了吗，要它干什么？

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

　　一个表中可以：
　　　单列做主键
　　　多列做主键（复合主键或者叫做联合主键）

1、关于主键的通俗解释和强调内容（重点*****）

unique key和primary key都是MySQL的特殊类型，
不仅仅是个字段约束条件，还称为索引，可以加快查询速度，
这个索引功能我们后面再讲，现在只讲一下这些key作为约束条件的效果。

关于主键的强调内容：
  1.一张表中必须有，并且只能由一个主键字段：innodb引擎下存储表数据的时候，
   会通过你的主键字段的数据来组织管理所有的数据，将数据做成一种树形结构的数据结构，
   帮你较少IO次数，提高获取定位数据、获取数据的速度，优化查询。

   解释：如果我们在一张表中没有设置primary key，那么mysql在创建表的时候，
   会按照顺序从上到下遍历你设置的字段，直到找到一个not null unique的字段，
   自动识别成主键pri，通过desc可以看到，这样是不是不好啊，所以我们在创建表的时候，
   要给他一个主键，让他优化的时候用，

   如果没有pri也没有not null unique字段，那么innodb引擎下的mysql被逼无奈，
   你没有设置主键字段，主键又有不为空且唯一的约束，又不能擅自给你的字段加上这些约束，
   那么没办法，它只能给你添加一个隐藏字段来帮你组织数据，
   如果是这样，你想想，主键是不是帮我们做优化查询用的啊，

   这个优化是我们可以通过主键来查询数据：
   例如：如果我们将id设置为主键，当我们查一个id为30的数据的时候，
   也就是select * from tb1 where id=30；这个查询语句的速度非常快，
   不需要遍历前面三十条数据，就好像我们使用的字典似的，找一个字，不需要一页一页的翻书，
   可以首先看目录，然后看在哪一节，然后看在哪一页，一步步的范围，
   然后很快就找到了，这就像我们说的mysql的索引(主键、唯一键)的工作方式，一步一步的缩小范围来查找，
   几步就搞定了，所以通过主键你能够快速的查询到你所需要的数据，
   所以，如果你的主键是mysql帮你加的隐藏的字段，你查询数据的时候，
   就不能将这个隐藏字段作为条件来查询数据了，就不能享受到优化后的查询速度了，对么

  2.一张表里面，通常都应该有一个id字段，而且通常把这个id字段作为主键，
   当然你非要让其他的字段作为主键也是可以的，看你自己的设计，
   创建表的时候，一般都会写create table t1(id int primary key);id int primary key这个东西在建表的时候直接就写上

Mysql

Copy

2、在没有设置主键的时候，not null+unique会被默认当成主键

在没有设置主键的时候，not null+unique会被默认当成主键

mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

Python

Copy

3、单列主键测试

单列主键测试

============单列做主键===============
#方法一：not null+unique
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    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

#方法二：在某一个字段后用primary key
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    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)

#方法三：在所有字段后单独定义primary key
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 | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

Python

Copy

4、联合主键解释

联合主键解释

联合主键
    和联合唯一是类似的，
    mysql> create table t10(
        ->id int,
        ->port int,
        ->primary key(id,port)
        -> );
    Query OK, 0 rows affected (0.45 sec)

    mysql> desc t10;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | id    | int(11) | NO   | PRI | 0       |       |
    | port  | int(11) | NO   | PRI | 0       |       |
    +-------+---------+------+-----+---------+-------+
    2 rows in set (0.10 sec)

    看key，两个都写的是pri，两个联合起来作为主键，他们两个作为一个主键，
    不能再有其他的主键了，也就是在创建表的时候，只能出现一次primary key方法。

    有同学说，老师，我不写primary key行不，只写一个not null unique字段，当然行，
    但是我们应该这样做吗，是不是不应该啊，所以以后设置主键的时候，就使用primary key来指定

Python

Copy

5、多列(联合)主键测试

多列(联合)主键测试

==================多列做主键================
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 | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

Python

Copy

五、auto_incretment(设置后就可以自增）

之前我们插入数据的时候，id也需要自己来写，是不是很麻烦啊，
我们是不是想，只要有一条记录就直接插入进去啊，
不需要考虑说，你现在存储到第多少条数据了，
对不对，所以出现了一个叫做auto_increment的属性

　　约束字段为自动增长，被约束的字段必须同时被key约束，
也就是说只能给约束成key的字段加自增属性，默认起始位置为1，步长也为1.

1、auto_increment测试

auto_increment测试

#不指定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)

Python

Copy

2、了解内容:步长：auto_increment_increment 起始偏移量：auto_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 '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 |
+----+-------+------+

Python

Copy

六、foreign key （外键，用来说明表之间的关系）

1、快速理解foreign key

（外键其实就是标明表和表之间的关系，
表和表之间如果有关系的话就三种：一对一，多对一，多对多，我们挨个看看~）

2、表的创建顺序

被指向的表，必须先创建，这样foreign key，才能有指向的表
foreign key 的表是指向表，后面创建

3、一对多的关系

示例：员工和部门之间的表的关系
如果我们没有做强制的约束关系，那么在员工表里面那个部门id可以随便写，即便是部门表里面没有这个id号，
它也是可以写的，但是这样写就错了，因为业务不允许，并且这个数据完全没用，根本就不存在这个部门，
哪里来的这个部门的员工呢，对不对，
所以要做一个硬性的关系，你员工里面的部门id一定要来自于部门表的id字段。

怎么来做这个硬性关系呢，通过外键foreign key，
怎么叫外键，就是跟外部的一个表进行关联，建立这种硬性的关系，就叫做外键，
就像我们上面这两个表似的，左边的员工表有一个字段(部门id字段)来自于右边的部门表，
那么我们就可以通过数据库在员工表的部门id字段加上一个foreign key，外键关联到右边部门表的id字段，
这样就建立了这种硬性的关系了，之前我们是看着两张表之间有关系，
但是没有做强制约束，还是两张普通的表，操作其中任何一个，另外一个也没问题，
但是加上了这种强制关系之后，他们两个的操作也就都关联起来了，具体操作看下面的代码：

fe:具体示例及创建顺序、说明

名字

创建表的顺序：
1、部门表是被关联的表，员工表是关联表，也就是员工表要关联部门表，
    对吧，如果我们先创建员工表，在创建员工表的时候加外键关系，就会报错，
2、表数据的输入顺序也是一样，先被关联表的
![](https://lj.fwit.win/wp-content/uploads/2018/12/af12080ce16254db2f62a2b0a261bf41.png)