6:列属性完整性-MySQL

时间:2022-12-17 20:57:34

(目录)

6.1 列属性问题

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

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)