6：列属性完整性-MySQL-阿里云开发者社区

6.1 列属性问题

列属性要根据业务的要求来对数据的一些控制，例如是否能为空，是否是唯一的，等各种操作，就是我们每次desc表的时候的表头上的内容就是与类属性有关的东西：TypeNullKeyDafult

6.2 Primary key主键作用以及企业用途

主键作用：用来区分数据，用来联系各表

特点就是：不能为空且在表中的数据是唯一的

不能为空保证了数据的完整性，在表中唯一使得在数据查询中变得更加方便，加快了表的查询速度

主键的设计非常重要，那么如何定义主键呢？

注意：在非自增字段的主键，Default必须赋值，不能为NULL

mysql> create table t_8(
    -> id int(20) primary key,
    -> name varchar(30)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

将某数据修改成主键的方法

alter table 表名  add primary key (字段名)；

mysql> alter table pet_user add primary key (id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc pet_user;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(10) | NO   | PRI | NULL    |       |
| age   | int(9)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

6.3 删除主键、组合键、选择主键

1. 删除主键

alter table 表名 drop primary key；

mysql> alter table t_8 drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc t_8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | NO   |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2. 组合键

可以添加组合键（复合主键），但扩展性确实不行

一张表里可以有多个组合键，但是可能在非特殊的情况下实际的意义不大

添加复合主键要先把原主键删除

alter table 表名 add primary key （字段，字段...）;

mysql>  alter table t_8 add primary key (id,name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc t_8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql>

3. 选择主键

主键的选择很重要，选择有把握且绝对不会更改的数据，最好是数字

万一所给你的数据没有这种类型的数据的时候，我们需要自己创建‘id’

6.4 复合主键究竟有什么用？

组和键运用范围？

例如用B站和微博，我们的id是唯一的，昵称也是唯一的，

这个时候可以参考组合键的方式，但现在使用的是唯一键，

注意：组合键并不是一个表中有多个主键，而是这些字段组合成复合主键，所以主键在表中只能有一个

6.5 unique唯一键的作用以及使用

唯一键限定范围是在一张表中，它不会用作来关联其他的数据

它也可以为空，在这张表中一定是唯一的，用来保证在这个表中这个数据不重复

和添加主键一样，有两种方法：

在创建表的时候数据写上unique

mysql> create table t_9(
    -> id int primary key,
    -> phone varchar(20) unique
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| phone | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

alter table 表名 add unique （字段，字段...）;

mysql> create table t_10(
    -> id int(4),
    -> phone varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_10;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table t_10 add unique(phone);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc t_10;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| phone | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

6.6 唯一键扩展

删除唯一键

alter table 表名 drop index 字段名 字段名不能使用括号

mysql> create table t_11(
    -> id int,
    -> name varchar(20) unique,
    -> phone varchar(20) unique
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc t_11;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
| phone | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table t_11 drop index phone;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc t_11;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

6.7 主键和唯一键区别

主键 `primary key`	唯一键 `unique`
可以用来连接各个表	不使用来链接各个表
不可以为空	可以为空
如果没有设置唯一键则自增字段必须是主键	自增字段不为主键的时候要将这个字段设置成唯一键

一张表只能有一个或者一个组合

可以有多个且不需要组合

6.8 sql内注释代码注释

1. 普通注释

命令单行注释与多行注释

mysql> create table t_12 (
    -> id int(20)  primary key,# this is primary key!
    -> phone varchar(20) -- this is phone
    -> /*
   /*> abcdefg
   /*> hijklmn
   /*> */
    -> );
Query OK, 0 rows affected (0.03 sec)

2. sql内注释

就是comment注释

mysql> create table staff(
    -> id int auto_increment primary key comment'主键id',
    -> name varchar(30) not null,
    -> age int comment'年龄',
    -> salary int default '0' comment'薪水'
    ->  )engine=innodb;
mysql> show create table staff;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| staff | CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(30) NOT NULL,
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `salary` int(11) DEFAULT '0' COMMENT '薪水',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6.9 数据库完整性

考虑数据库的完整性
一个表一定要有一个主键来约束
保证数据类型一定要是对的
哪些字段可以为空哪些不行
Defalut的使用
可能需要对外部的引用

6.10 引用数据表的完整性问题，抛出外键的概念

外部引用问题，如何关联两个表，将公共的字段链接在一起呢？

我们创建表时一定要明确主表和从表

6.11 外键

创建外键

foreign key (数据) references 连接的表名(数据));

mysql> create table eatery(
    -> id int primary key,
    -> money decimal(10,4),
    -> stuId int(4),
    -> foreign key (stuId) references stu(stuId));
Query OK, 0 rows affected (0.03 sec)
mysql> desc eatery;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | NO   | PRI | NULL    |       |
| money | decimal(10,4) | YES  |     | NULL    |       |
| stuId | int(4)        | YES  | MUL | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

alter table 表名 add foreign key (数据) references 连接的表名(数据);

mysql> create table eatery_2(
    -> id int primary key,
    -> money decimal(10,4),
    -> stuId int (4)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc eatery_2;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | NO   | PRI | NULL    |       |
| money | decimal(10,4) | YES  |     | NULL    |       |
| stuId | int(4)        | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table eatery_2 add foreign key (stuId) references stu(stuId);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc eatery_2;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | NO   | PRI | NULL    |       |
| money | decimal(10,4) | YES  |     | NULL    |       |
| stuId | int(4)        | YES  | MUL | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

6.12 什么时候设计外键呢？

在设计表初时就要考虑到外键，一旦创建很少修改，后期维护可能会修改

6.13 更正上节课错误，删除外键

先要找到这个外键的名字再删除，查看外键名

show create table 表名

找到外键名在CONSTRAINT后

CONSTRAINT `xxxxxx` FOREIGN KEY (`xxxx`) REFERENCES `xxx (`xx`)

删除外键

alter table 表名 drop foreign key 外键名

mysql> show create table eatery;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table
                                                                       |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| eatery | CREATE TABLE `eatery` (
  `id` int(11) NOT NULL,
  `money` decimal(10,4) DEFAULT NULL,
  `stuId` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stuId` (`stuId`),
  CONSTRAINT `eatery_ibfk_1` FOREIGN KEY (`stuId`) REFERENCES `stu` (`stuId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table eatery drop foreign key eatery_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table eatery;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table
                                                                 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| eatery | CREATE TABLE `eatery` (
  `id` int(11) NOT NULL,
  `money` decimal(10,4) DEFAULT NULL,
  `stuId` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stuId` (`stuId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6.14 外键三种操作：严格、置空、级联的使用场景以及介绍

1. 严格性操作

也就是增删改查表的规范

2. 置空操作

链接外键的表中，原表删除的数据会成为NULL

3. 级联操作

链接外键的表中，原表删除的数据会全部删除

注意：留给外键进行删除数据的时候使用置空，更新使用级联

6.15 置空和级联演示

创建eatery表，stuId为外键，设置级联和置空操作

foreign key(数据) references 表名(数据) on delete set null on update cascade

mysql> create table stu(
    -> stuId int (4) primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> create table eatery(
    -> id int(20) primary key,
    -> money decimal(10,4),
    -> stuId int(4),
    -> foreign key(stuId) references stu(stuId) on delete set null on update cascade
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> show create table eatery;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table
                                   |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| eatery | CREATE TABLE `eatery` (
  `id` int(20) NOT NULL,
  `money` decimal(10,4) DEFAULT NULL,
  `stuId` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stuId` (`stuId`),
  CONSTRAINT `eatery_ibfk_1` FOREIGN KEY (`stuId`) REFERENCES `stu` (`stuId`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

插入数据

mysql> insert into stu values(1,'frank');
Query OK, 1 row affected (0.01 sec)
mysql> insert into stu values(2,'jerry');
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu;
+-------+-------+
| stuId | name  |
+-------+-------+
|     1 | frank |
|     2 | jerry |
+-------+-------+
2 rows in set (0.00 sec)
mysql> insert into eatery values(1, 20.5, 2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from eatery;
+----+---------+-------+
| id | money   | stuId |
+----+---------+-------+
|  1 | 20.5000 |     2 |
+----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into eatery values(2, 78.6, 1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into eatery values(3, 99.9, 2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into eatery values(4, 748.4, 1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into eatery values(5, 748.4, 2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  1 |  20.5000 |     2 |
|  2 |  78.6000 |     1 |
|  3 |  99.9000 |     2 |
|  4 | 748.4000 |     1 |
|  5 | 748.4000 |     2 |
+----+----------+-------+
5 rows in set (0.00 sec)

更新stu表中stuId时，eatery表外键的数据会相应更改，这里是级联操作

mysql> update stu set stuId='4' where name = 'frank';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  1 |  20.5000 |     2 |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |     2 |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |     2 |
+----+----------+-------+
5 rows in set (0.00 sec)

删除stu表中stuId时，eatery表外键的数据会变为NULL，这里是置空操作

mysql> delete from stu where stuId='2';
Query OK, 1 row affected (0.01 sec)
mysql> select * from eatery;
+----+----------+-------+
| id | money    | stuId |
+----+----------+-------+
|  1 |  20.5000 |  NULL |
|  2 |  78.6000 |     4 |
|  3 |  99.9000 |  NULL |
|  4 | 748.4000 |     4 |
|  5 | 748.4000 |  NULL |
+----+----------+-------+
5 rows in set (0.00 sec)

6：列属性完整性-MySQL