完整性约束

一.介绍

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

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

1.主要分为:

PRIMARY KEY(PK)    #标识该字段为该表的主键,可以唯一的标识记录
FOREIGN KEY(FK)    #标识该字段为该表的外键
NOT NULL    #标识该字段不能为空
UNIQUE KEY(UK)    #标识该字段的值是唯一的
AUTO_INCREMENT    #标识该字段的值自动增长(整数类型,而且为主键)
DEFAULT    #为该字段设置默认值

UNSIGNED    #无符号
ZEROFILL    #使用0填充

2.说明

#1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
#2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'

#必须为正值（无符号） 不允许为空 默认是20
age int unsigned NOT NULL default 20 
# 3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

二.not null与default

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

not null　　#不可空

null　　#可空

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

create table tb1(
    nid int not null defalut 2,
    num int not null
);

1.默认值可以为空

mysql> create table t1(id int);    #id字段默认可以为空
Query OK, 0 rows affected (0.28 sec)

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

mysql> insert into t1 values();    #给t1表插一个空的值
Query OK, 1 row affected (0.07 sec)

#查询结果
mysql> select * from t1;
+------+
| id   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

View Code

2.设置not null, 插入值时不能为空

mysql> create table t2(id int not null);    #设置字段id不为空
Query OK, 0 rows affected (0.25 sec)

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

mysql> insert into t2 values();    #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value

View Code

3.设置id字段有默认值后,则无论id字段是null还是not null,都可以插入空,插入空默认填入default指定的默认值

#第一种情况
mysql> create table t3(id int default 1);
Query OK, 0 rows affected (0.23 sec)

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

mysql> insert into t3 values();
Query OK, 1 row affected (0.07 sec)

mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


#第二种情况
mysql> create table t4(id int not null default 2);
Query OK, 0 rows affected (0.27 sec)

mysql> desc t4;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | 2       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select * from t4;
Empty set (0.00 sec)

mysql> insert into t4 values();
Query OK, 1 row affected (0.07 sec)

mysql> select * from t4;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

View Code

三.unique

1.中文翻译:不同的,在MySql中称为单列唯一

2.举例说明:创建公司部门表(每个公司都有唯一的一个部门)

mysql> create table department(
    -> id int,
    -> name char(10)
    -> );
Query OK, 0 rows affected (0.27 sec)

mysql> insert into department values(1,"IT"),(2,"IT");
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from department;
+------+------+
| id   | name |
+------+------+
|    1 | IT   |
|    2 | IT   |
+------+------+
2 rows in set (0.00 sec)

#发现:同时插入两个IT部门也是可以的,但这是不合理的,所以我们要设置name字段为unique,解决这种不合理的现象

View Code

3.使用约束条件unique,来对公司部门的字段进行设置

#第一种创建unique的方式
#例子1:
mysql> create table department(
    -> id int,
    -> name char(10) unique
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql> insert into department values(1,"IT"),(2,"IT");
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
mysql>

#例子2:
mysql> create table department1(
    -> id int unique,
    -> name char(10) unique
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> insert into department1 values(1,"IT"),(2,"sale");
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from department1;
+------+------+
| id   | name |
+------+------+
|    1 | IT   |
|    2 | sale |
+------+------+
2 rows in set (0.00 sec)


#第二种创建unique的方式
mysql> create table department2(
    -> id int,
    -> name char(10),
    -> unique(id),
    -> unique(name)
    -> );
Query OK, 0 rows affected (0.29 sec)

mysql> insert into department2 values(1,"IT"),(2,"sale");
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from department2;
+------+------+
| id   | name |
+------+------+
|    1 | IT   |
|    2 | sale |
+------+------+
2 rows in set (0.00 sec)

4.联合唯一

# 创建services表
mysql> create table services(
    -> id int,
    -> ip char(15),
    -> port int,
    -> unique(id),
    -> unique(ip,port)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> desc services;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | UNI | NULL    |       |
| ip    | char(15) | YES  | MUL | NULL    |       |
| port  | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)


#联合唯一，只要两列记录，有一列不同，既符合联合唯一的约束
mysql> insert into services values
    -> (1,'192,168,11,23',80),
    -> (2,'192,168,11,23',81),
    -> (3,'192,168,11,25',80);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from services;
+------+---------------+------+
| id   | ip            | port |
+------+---------------+------+
|    1 | 192,168,11,23 |   80 |
|    2 | 192,168,11,23 |   81 |
|    3 | 192,168,11,25 |   80 |
+------+---------------+------+
3 rows in set (0.00 sec)

mysql> insert into services values(4,'192,168,11,23',80);
ERROR 1062 (23000): Duplicate entry '192,168,11,23-80' for key 'ip'

四.primary key

1.一个表中可以:

　　单列做主键

　　多列做主键(复合主键)

2.约束:等价于 not null unique,字段的值不为空且唯一

3.存储引擎默认是(innodb): 对于innodb存储引擎来说,一张表必须有一个主键

4.单列主键

#创建t5表,为id字段设置主键,唯一不同的记录
mysql> create table t5(
    -> id int primary key,
    -> naem char(16)
    -> );
Query OK, 0 rows affected (0.27 sec)

mysql> insert into t5 values
    -> (1,"haha"),
    -> (2,"hehe");
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t5 values(2,"heihei");
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> select * from t5;
+----+------+
| id | naem |
+----+------+
|  1 | haha |
|  2 | hehe |
+----+------+
2 rows in set (0.00 sec)


#not null + unique的化学反应,相当于给id设置primary key
mysql> create table t6(
    -> id int not null unique,
    -> name char(16)
    -> );
Query OK, 0 rows affected (0.27 sec)

mysql> desc t6;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(16) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

5.复合主键

create table t16(
    ip char(15),
    port int,
    primary key(ip,port)
);

insert into t16 values
('1.1.1.2',80),
('1.1.1.2',81);

View Code

五.auto_increment

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

2.不指定id,则自动增长(重点)

#创建student
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum("male","female") default "male"
    -> );
Query OK, 0 rows affected (0.27 sec)

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    |                |
+-------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into student(name) values("老猫"),("小猫");
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
|  1 | 老猫   | male |
|  2 | 小猫   | male |
+----+--------+------+
2 rows in set (0.00 sec)

View Code

3.也可以指定id

mysql> insert into student values(4,"abc","female");
Query OK, 1 row affected (0.04 sec)

mysql> insert into student values(7,"ddd","female");
Query OK, 1 row affected (0.08 sec)

mysql> select * from student;
+----+--------+--------+
| id | name   | sex    |
+----+--------+--------+
|  1 | 老猫   | male   |
|  2 | 小猫   | male   |
|  4 | abc    | female |
|  7 | ddd    | female |
+----+--------+--------+
4 rows in set (0.00 sec)

#再次插入一天不指定id的记录,会在之前的最后一条记录继续增长
mysql> insert into student(name) values("小黑");
Query OK, 1 row affected (0.08 sec)

mysql> select * from student;
+----+--------+--------+
| id | name   | sex    |
+----+--------+--------+
|  1 | 老猫   | male   |
|  2 | 小猫   | male   |
|  4 | abc    | female |
|  7 | ddd    | female |
|  8 | 小黑   | male   |
+----+--------+--------+
5 rows in set (0.00 sec)

View Code

4.对于自增的字段,在用delete删除后,再插入值,该字段仍然按照删除之前的位置继续增长

mysql> delete from student;
Query OK, 5 rows affected (0.08 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values("yyyy");
Query OK, 1 row affected (0.08 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  9 | yyyy | male |
+----+------+------+
1 row in set (0.00 sec)


#应该用truncate清空表,比起delete一条一条的删除记录,truncate是直接清空表,在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.22 sec)

mysql> insert into student(name) values("xiaoxixi");
Query OK, 1 row affected (0.08 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | sex  |
+----+----------+------+
|  1 | xiaoxixi | male |
+----+----------+------+
1 row in set (0.00 sec)

View Code

5.了解

查看可用的 开头auto_inc的词
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
rows in set (0.02 sec)
# 步长auto_increment_increment,默认为1
# 起始的偏移量auto_increment_offset, 默认是1

 # 设置步长 为会话设置，只在本次连接中有效
 set session auto_increment_increment=5;

 #全局设置步长 都有效。
 set global auto_increment_increment=5;

 # 设置起始偏移量
 set global  auto_increment_offset=3;

#强调：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的值会被忽略 

# 设置完起始偏移量和步长之后，再次执行show variables like'auto_inc%';
发现跟之前一样，必须先exit,再登录才有效。

mysql> show variables like'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 3     |
+--------------------------+-------+
rows in set (0.00 sec)

#因为之前有一条记录id=1
mysql> select * from student;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | xiaobai | male |
+----+---------+------+
row in set (0.00 sec)
# 下次插入的时候，从起始位置3开始，每次插入记录id+5
mysql> insert into student(name) values('ma1'),('ma2'),('ma3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | xiaobai | male |
|  3 | ma1     | male |
|  8 | ma2     | male |
| 13 | ma3     | male |
+----+---------+------+

auto_increment_increment和 auto_increment_offset

View Code

6.清空表区分delete和truncate的区别：

delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。

truncate table t1;数据量大，删除速度比上一条快，且直接从零开始。

六.foreign key

1.快速理解foreign key

之前创建表的时候都是在一张表中添加记录,比如下表:

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费。

解决方法:让员工信息表关联该表，如何关联，即foreign key

我们可以将上表改为如下结构：

此时有两张表，一张是employee表,简称emp表（关联表，也就从表）。一张是department表，简称dep表（被关联表，也叫主表）。

2.创建两张表的操作:

#1.创建表时先创建被关联表，再创建关联表
# 先创建被关联表(dep表)
mysql> create table dep(
    -> id int primary key,
    -> name varchar(20) not null,
    -> descripe varchar(20) not null
    -> );
Query OK, 0 rows affected (0.26 sec)

#再创建关联表(emp表)
mysql> create table emp(
    -> id int primary key,
    -> name varchar(20) not null,
    -> age int not null,
    -> dep_id int,
    -> constraint fk_dep foreign key(dep_id) references dep(id)
    -> );
Query OK, 0 rows affected (0.32 sec)

#2.插入记录时,先往被关联表中插入记录,再往关联表中插入记录
mysql> insert into dep values (1,"IT","IT事业部"),(2,"销售","销售部"),(3,"财务部","花钱");
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into emp values (1,"wahaha",18,1),(2,"shuangwaiwai",19,1),(3,"ruwawa",20,2),(4,"binghongcha",40,3),(5,"suansuanru",18,2);
Query OK, 5 rows affected (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----+--------------+-----+--------+
| id | name         | age | dep_id |
+----+--------------+-----+--------+
|  1 | wahaha       |  18 |      1 |
|  2 | shuangwaiwai |  19 |      1 |
|  3 | ruwawa       |  20 |      2 |
|  4 | binghongcha  |  40 |      3 |
|  5 | suansuanru   |  18 |      2 |
+----+--------------+-----+--------+
5 rows in set (0.00 sec)

mysql> select * from dep;
+----+-----------+-------------+
| id | name      | descripe    |
+----+-----------+-------------+
|  1 | IT        | IT事业部    |
|  2 | 销售      | 销售部      |
|  3 | 财务部    | 花钱        |
+----+-----------+-------------+
3 rows in set (0.00 sec)

#3.删除表
#按道理来说,删除了部门表中的某个部门,员工表的有关联的记录相继删除
mysql> delete from dep where id = 3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db7`.`emp`, CONSTRAINT `fk_dep` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

#但是先删除员工表的记录之后,再删除当前部门就没有任何问题
mysql> delete from emp where dep_id =3;
Query OK, 1 row affected (0.08 sec)

mysql> select * from emp;
+----+--------------+-----+--------+
| id | name         | age | dep_id |
+----+--------------+-----+--------+
|  1 | wahaha       |  18 |      1 |
|  2 | shuangwaiwai |  19 |      1 |
|  3 | ruwawa       |  20 |      2 |
|  5 | suansuanru   |  18 |      2 |
+----+--------------+-----+--------+
4 rows in set (0.00 sec)

mysql> delete from dep where id = 3;
Query OK, 1 row affected (0.08 sec)

mysql> select * from dep;
+----+--------+-------------+
| id | name   | descripe    |
+----+--------+-------------+
|  1 | IT     | IT事业部    |
|  2 | 销售   | 销售部      |
+----+--------+------------

View Code

上面的删除表记录的操作比较繁琐，按道理讲，裁掉一个部门，该部门的员工也会被裁掉。其实呢，在建表的时候还有个很重要的内容，叫同步删除，同步更新

接下来将刚建好的两张表全部删除，先删除关联表（emp）,再删除被关联表（dep）

接下来：
重复上面的操作建表
注意：在关联表中加入
on delete cascade #同步删除
on update cascade #同步更新

3.修改emp表:

create table emp(
    id int primary key,
    name varchar(20) not null,
    age int not null,
    dep_id int,
    constraint fk_dep foreign key(dep_id) references dep(id) 
    on delete cascade #同步删除
    on update cascade #同步更新
);

接下来的操作，就复合我们正常的生活中的情况了。

#再去删被关联表(dep)的记录，关联表(emp)中的记录也跟着删除
mysql> delete from dep where id = 3;
Query OK, 1 row affected (0.07 sec)

mysql> select * from dep;
+----+--------+-------------+
| id | name   | descripe    |
+----+--------+-------------+
|  1 | IT     | IT事业部    |
|  2 | 销售   | 销售部      |
+----+--------+-------------+
2 rows in set (0.00 sec)

mysql> select * from emp;
+----+--------------+-----+--------+
| id | name         | age | dep_id |
+----+--------------+-----+--------+
|  1 | wahaha       |  18 |      1 |
|  2 | shuangwaiwai |  19 |      1 |
|  3 | ruwawa       |  20 |      2 |
|  5 | suansuanru   |  18 |      2 |
+----+--------------+-----+--------+
4 rows in set (0.00 sec)

#再去更改被关联表(dep)的记录,关联表(emp)中的记录也跟着更改
mysql> update dep set id = 222 where id = 2;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#紧接着去看一下两张表是否都被删除,都被更改了
mysql> select * from dep;
+-----+--------+-------------+
| id  | name   | descripe    |
+-----+--------+-------------+
|   1 | IT     | IT事业部    |
| 222 | 销售   | 销售部      |
+-----+--------+-------------+
2 rows in set (0.00 sec)

mysql> select * from emp;
+----+--------------+-----+--------+
| id | name         | age | dep_id |
+----+--------------+-----+--------+
|  1 | wahaha       |  18 |      1 |
|  2 | shuangwaiwai |  19 |      1 |
|  3 | ruwawa       |  20 |    222 |
|  5 | suansuanru   |  18 |    222 |
+----+--------------+-----+--------+
4 rows in set (0.00 sec)

posted @ 2018-10-17 16:14 骑驴老神仙阅读(341) 评论(0) 收藏举报

刷新页面返回顶部

骑驴老神仙

完整性约束

一.介绍

1.主要分为:

2.说明

二.not null与default

1.默认值可以为空

2.设置not null, 插入值时不能为空

3.设置id字段有默认值后,则无论id字段是null还是not null,都可以插入空,插入空默认填入default指定的默认值

三.unique

1.中文翻译:不同的,在MySql中称为单列唯一

2.举例说明:创建公司部门表(每个公司都有唯一的一个部门)

3.使用约束条件unique,来对公司部门的字段进行设置

4.联合唯一

四.primary key

1.一个表中可以:

2.约束:等价于 not null unique,字段的值不为空且唯一

3.存储引擎默认是(innodb): 对于innodb存储引擎来说,一张表必须有一个主键

4.单列主键

5.复合主键

五.auto_increment

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

2.不指定id,则自动增长(重点)

3.也可以指定id

4.对于自增的字段,在用delete删除后,再插入值,该字段仍然按照删除之前的位置继续增长

5.了解

6.清空表区分delete和truncate的区别：

六.foreign key

1.快速理解foreign key

2.创建两张表的操作:

3.修改emp表:

公告