(目录)
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)