526互联

表相关操作2-完整约束、表之间关系

发布时间 2023-04-07 16:32:00作者: 星空看海

目录

六、表完整性约束

1.介绍

约束条件就是在数据类型的基础上在添加额外的限制

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

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

unsigned            无符号
zerofill            使用0填充

not null            标识该字段不能为空
default             为该字段设置默认值
unique              标识该字段的值是唯一的
primary key         标识该字段为该表的主键，可以唯一的标识记录
auto_increment      标识该字段的值自动增长（整数类型，而且为主键）
foreign key         标识该字段为该表的外键

说明

1. Null栏
是否允许为空，默认NULL(YES)，可设置NOT NULL(NO)，字段不允许为空，必须赋值

2. Default栏
字段是否有默认值，空缺的默认值是NULL。设置后，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20

3. Key栏
唯一 unique           (UNI)
主键 primary key      (PRI)
外键 foreign          (MUL)
索引 (index,unique...)

4. Extra栏
自增 auto_increment   (auto_increment)

2.unsigned、zerofill

无符号(unsigned):只能写非负数

零填充:当插入的数值比定义的属性长度小的时候,会在数值前面进行补值操作，用0来补充。

验证：
1.unsigned

mysql> create database db3;
Query OK, 1 row affected (0.01 sec)

mysql> use db3;
Database changed

mysql> create table t0(id int unsigned);
Query OK, 0 rows affected (0.04 sec)

mysql> desc t0;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.03 sec)

mysql> insert into t0 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t0 values(129);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t0;
+------+
| id   |
+------+
|    1 |
|  129 |
+------+
2 rows in set (0.00 sec)

mysql> insert into t0 values(-3);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

2.zerofill

3. not null

是否可空，null表示空

create table t1(
	id int,
 	name varchar(16)
);
insert into t1(id) values(1);
insert into t1(name) values('jason');
insert into t1(name,id) values('kevin',2);
insert into t1 values(null,null);  # 可以这样插入记录但是没有意义
ps:所有字段类型不加约束条件的情况下默认都可以为空
    
 
create table t2(
	id int,
 	name varchar(16) not null  # 不能为空，是一个约束条件，但是两个字母中间要加空格
);
insert into t2(id) values(1);  # 会报错
insert into t2(name) values('jason');  
insert into t2 values(1,''); 
insert into t2 values(2,null);  # 报错

'''在sql中，NULL和''(空字符)不一样'''

验证

mysql> create table t1(
    -> id int,
    -> name varchar(16)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1(id) values(1);  # 所有字段类型不加约束条件的情况下默认都可以为空
Query OK, 1 row affected (0.02 sec)

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

mysql> insert into t1(name)values('jason');
Query OK, 1 row affected (0.01 sec)

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

mysql> insert into t1(name,id) values('kevin',2);  # 可以换位置，值只要符合表括号后面的顺序就行
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | NULL  |
| NULL | jason |
|    2 | kevin |
+------+-------+
3 rows in set (0.00 sec)

mysql> insert into t1 values(null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | NULL  |
| NULL | jason |
|    2 | kevin |
| NULL | NULL  |
+------+-------+
4 rows in set (0.00 sec)

加个约束条件，非空

mysql> create table t2(
    -> id int,
    -> name varchar(16) not null  # 不能为空，是一个约束条件，但是两个字母中间要加空格
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | NO   |     | NULL    |       |  # Null栏发生了变化，变成了NO
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> insert into t2(id) values(1);  # 会报错，因为name设置了非空
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into t2(name) values('jason');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(1,'');  # 这个比较特殊，只要不是null，空字符也可以
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(2,null);  # null就会报错
ERROR 1048 (23000): Column 'name' cannot be null

4.default

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

默认值后面设置的值也要符合该字段结构。

create table t3(
	id int default 666,
	name varchar(16) default '匿名'
);
insert into t3(id) values(1);  # 没有添加name,就会用默认的值
insert into t3(name) values('jason');
insert into t3 values(2,'kevin');  # 两个值都添加，就直接用添加的值

默认值配合非空一起使用：

create table group1(
    id int not null default 666,
	name varchar(16) default '匿名' not null
);
insert into group1(id) values(1);
insert into group1(name) values('jason');
insert into group1 values(2, 'kevin');
insert into group1 values(3,null);  # 填入空会报错
insert into group1 values();

验证：

mysql> create table t3(
    -> id int default 666,
    -> name varchar(16) default '匿名'
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | 666     |       |  
| name  | varchar(16) | YES  |     | 匿名    |       |  # Default栏都设置了默认值
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

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

mysql> select * from t3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 匿名   |  # 只添加了id,没有添加对应name,name就会用默认的值
+------+--------+
1 row in set (0.00 sec)

mysql> insert into t3(name) values('jason');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 匿名   |
|  666 | jason  |
+------+--------+
2 rows in set (0.00 sec)

mysql> insert into t3 values(2, 'kevin');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 匿名   |
|  666 | jason  |
|    2 | kevin  |  # 两个值都添加，就直接用添加的值
+------+--------+
3 rows in set (0.00 sec)

默认值配合非空一起使用

mysql> create table group1(
    -> id int not null default 666,
    -> name varchar(16) default '匿名' not null
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc group1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | 666     |       |
| name  | varchar(16) | NO   |     | 匿名    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> insert into group1(id) values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from group1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 匿名   |
+----+--------+
1 row in set (0.00 sec)


mysql> insert into group1(name) values('jason');
Query OK, 1 row affected (0.00 sec)

mysql> insert into group1 values(2,'kevin');
Query OK, 1 row affected (0.01 sec)

mysql> insert into group1 values(3,null);  # 填入空会报错
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into group1 values();
Query OK, 1 row affected (0.01 sec)

mysql> select * from group1;
+-----+--------+
| id  | name   |
+-----+--------+
|   1 | 匿名   |
| 666 | jason  |
|   2 | kevin  |
| 666 | 匿名   |
+-----+--------+
4 rows in set (0.00 sec)

5.unique

唯一值，也叫唯一键。

1.单列唯一：对每个字段进行约束,单个不能重复
create table t4(
	id int,
	name varchar(32) unique
);
insert into t4 values(1,'jason'),(2,'jason');  # 会报错
insert into t4 values(1,'jason');
insert into t4 values(2,'tom'); 
insert into t4 values(1,'oscar');  # id没有设置唯一，id可以重复


2.联合唯一：几个字段组合(拼)在一起的结果必须是唯一的，单个字段可以重复
create table t5(
	id int,
 	ip varchar(32),
 	port int,
 	unique(ip,port)
);
desc t5;
show create table t5;  # 可以直接看到唯一的约束条件
insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);
insert into t5 values(4,'127.0.0.1',8080);  # ip-port一致了，报错

验证：
1.单列唯一：对每个字段进行约束,单个不能重复

mysql> create table t4(
    -> id int,
    -> name varchar(32) unique
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc t4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> insert into t4 values(1,'jason'),(2,'jason');  # name设置的是唯一，输入两个相同的name值就会报错
ERROR 1062 (23000): Duplicate entry 'jason' for key 'name'

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

mysql> insert into t4 values(2,'tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values(1,'oscar');  # id没有设置唯一条件，就可以重复
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4;
+------+-------+
| id   | name  |
+------+-------+
|    1 | jason |
|    2 | tom   |
|    1 | oscar |
+------+-------+
3 rows in set (0.00 sec)

2.联合唯一：几个字段组合(拼)在一起的结果必须是唯一的，单个字段可以重复

mysql> create table t5(
    -> id int,
    -> ip varchar(32),
    -> port int,
    -> unique(ip,port)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| ip    | varchar(32) | YES  | MUL | NULL    |       |
| port  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> show create table t5;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t5    | CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL,
  `ip` varchar(32) DEFAULT NULL,
  `port` int(11) DEFAULT NULL,
  UNIQUE KEY `ip` (`ip`,`port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);  # ip可以重复，port可以重复，只要ip-port不会一样就不会报错
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t5;
+------+-----------+------+
| id   | ip        | port |
+------+-----------+------+
|    1 | 127.0.0.1 | 8080 |
|    2 | 127.0.0.1 | 8081 |
|    3 | 127.0.0.2 | 8080 |
+------+-----------+------+
3 rows in set (0.00 sec)

mysql> insert into t5 values(4,'127.0.0.1',8080);  # ip-port一致了，报错
ERROR 1062 (23000): Duplicate entry '127.0.0.1-8080' for key 'ip'

6.primary key

1.单从约束层面上而言主键相当于not null + unique(非空且唯一)
主键可以加快查询速度!!!因为主键本质上也是一种索引!!!

	create table t6(
    	id int primary key,
    	name varchar(32)
    );
	insert into t6(name) values('jason');  # 会报错
 	insert into t6 values(1,'kevin'); 
 	insert into t6 values(1,'jerry');  # 报错

2.InnoDB存储引擎规定了所有的表都必须有且只有一个主键(主键是组织数据的重要条件并且主键可以加快数据的查询速度)
可以看成是新华字典的目录

  • 当表中没有主键也没有其他非空切唯一的字段的情况下

    InnoDB存储引擎内部有一个隐藏的字段，作为了主键，我们看不到，并且，这个隐藏的主键不能加快查询速度，就是因为InnoDB存储引擎建表就是有主键来构成的，加这个隐藏的字段作为主键，仅仅是为了把表创建成功。

    隐藏意味着无法使用，基于该表的数据查询只能一行行查找，速度很慢

  • 当表中没有主键但是有其他非空且唯一的字段，那么会从上往下将第一个该字段自动升级为主键

create table t7(
  id int,
  age int not null unique,  # age会自动变成主键
  phone bigint not null unique,
  birth int not null unique,
  height int not null unique
);
        
'''
  我们在创建表的时候应该有一个字段用来标识数据的唯一性，并且该字段通常情况下就是'id'(编号)字段
  字段名可以是：id nid sid pid gid uid
'''

如何给字段添加主键

create table userinfo(
    uid int primary key,  # 这个id字段就已经有了主键的特性
    name varchar(32)
);

验证：

约束层面,主键是：not null + unique(非空且唯一)

mysql> create table t6(
    -> id int primary key,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |  # Null是NO,Key栏变成PRI
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> insert into t6(name) values('jason');  # 没有输入id就会报错
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> insert into t6 values(1,'kevin');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t6 values(1,'jerry');  # id是唯一的，输入第二次就会报错
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | kevin |
+----+-------+
1 row in set (0.00 sec)

表中没有主键,将第一个该字段自动升级为主键

mysql> create table t7(
    -> id int,
    -> age int not null unique,
    -> birth int not null unique,
    -> height int not null unique
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t7;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | YES  |     | NULL    |       |
| age    | int(11) | NO   | PRI | NULL    |       |  # age会自动变成主键PRI
| birth  | int(11) | NO   | UNI | NULL    |       |
| height | int(11) | NO   | UNI | NULL    |       |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.03 sec)

7.auto_increment

自增，该约束条件不能单独出现，并且一张表中只能出现一次，主要就是配合主键一起用

1.自增含义：在原来的基础上，每次加1

create table t8(
	id int primary key,
 	name varchar(32)
); 
# 此时的编号录入要唯一，但是我们可能会忘记录入到了几号，所以就有了自增

create table t9(
	id int primary key auto_increment,  # 自增
 	name varchar(32)
);
isert into t9(name) values ('jason'),('kevin'),('tom'),('jack'),('tony')
select * from t9;  # 以后插入数据每次需要写上字段名

2.自增特性
自增不会因为数据的删除而回退，永远自增往前
如果自己设置了更大的数，则之后按照更大的往前自增

如果想重置某张表的主键值，可以使用
	delete t9; 删除表数据
	truncate t9;  清空表数据并重置主键

3.以后我们在创建id字段的时候，固定语法结构：

id int primary key auto_increment
# 主键字段在添加值的时候，就不用在单独添加了，而是自动生成.
'''一张表中，我们可以通过主键字段来唯一确定一条记录'''

验证：

mysql> create table t8(
    -> id int primary key,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> desc t8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

创建自增

mysql> create table t9(
    -> id int primary key auto_increment,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t9;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |  # Extra栏增加了自增
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> insert into t9(name) values('jason'),('kevin'),('tom'),('jack'),('tony');  # 编号就不需要写了，但是这样表名后的括号内就需要把剩下的字段都写上
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
|  4 | jack  |
|  5 | tony  |
+----+-------+
5 rows in set (0.00 sec)

mysql> insert into t9(name) values('jason'),('kevin'),('tom'),('jack'),('tony');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t9(name) values('jason'),('kevin'),('tom'),('jack'),('tony');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
|  4 | jack  |
|  5 | tony  |
|  6 | jason |
|  7 | kevin |
|  8 | tom   |
|  9 | jack  |
| 10 | tony  |
| 11 | jason |
| 12 | kevin |
| 13 | tom   |
| 14 | jack  |
| 15 | tony  |
+----+-------+
15 rows in set (0.00 sec)

删除记录后，编号还是在原来编号顺序往后加

mysql> delete from t9 where id>3;
Query OK, 12 rows affected (0.02 sec)

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
+----+-------+
3 rows in set (0.00 sec)

mysql> insert into t9(name) values('lili');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
| 16 | lili  |
+----+-------+
4 rows in set (0.00 sec)

mysql> insert into t9(id,name) values(4,'haha');  # 可以自己操作编号
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
|  4 | haha  |
| 16 | lili  |
+----+-------+
5 rows in set (0.00 sec)

mysql> insert into t9(name) values('tong');  # 只要自己没有操作编号，就是按照之前的编号顺序往后加
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
|  4 | haha  |
| 16 | lili  |
| 17 | tong  |
+----+-------+
6 rows in set (0.00 sec)

如果自己设置了更大的数，则之后按照更大的往前自增

mysql> insert into t9(id,name) values(2000,'乌拉拉');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | jason     |
|    2 | kevin     |
|    3 | tom       |
|    4 | haha      |
|   16 | lili      |
|   17 | tong      |
| 2000 | 乌拉拉    |
+------+-----------+
7 rows in set (0.00 sec)

mysql> insert into t9(name) values('程程程');   # 会从2000之后往后加
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | jason     |
|    2 | kevin     |
|    3 | tom       |
|    4 | haha      |
|   16 | lili      |
|   17 | tong      |
| 2000 | 乌拉拉    |
| 2001 | 程程程    |
+------+-----------+
8 rows in set (0.00 sec)

delete t9; 删除表数据

mysql> delete from t9;
Query OK, 8 rows affected (0.01 sec)

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

mysql> insert into t9(name) values('楚楚楚');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+------+-----------+
| id   | name      |
+------+-----------+
| 2002 | 楚楚楚    |  # 编号仍然是往后自增，delete删除数据没有用
+------+-----------+
1 row in set (0.00 sec)

truncate t9; 清空表数据并重置主键

mysql> truncate t9;  # 清空表数据并重置主键,相当于格式化操作
Query OK, 0 rows affected (0.04 sec)

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

mysql> insert into t9(name) values('呵呵呵');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t9(name) values('呵呵呵');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t9(name) values('呵呵呵');
Query OK, 1 row affected (0.01 sec)

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

8.foreign key

外键字段:其实就是用来标识表与表之间的数据关系的字段。简单的理解为该字段可以让你去到其他表中查找数据，是有约束的，只能出现被关联的字段的编号出现的值。

表与表之间建关系

关系总共有四种：

  • 一对多
  • 多对多
  • 一对一
  • 没有关系
    关系的判断可以采用'换位思考'原则。换位思考就是站在双方的角度考虑问题。

外键字段建立

# 建立
小技巧:先定义出含有普通字段的表,之后再考虑外键字段的添加

# 外键的约束
1.在创建表的时候,需要先创建被关联表(没有外键字段的表)
2.在插入新数据的时候,应该先确保被关联表中有数据
3.在插入新数据的时候,外键字段只能填写被关联表中已经存在的数据
4.在修改和删除被关联表中的数据的时候,无法直接操作

# 如果想要数据之间自动修改和删除需要添加额外的配置 >>> :级联更新,级联删除
on update cascade:修改时，关联的数据会自动修改.
on delete cascade:删除时，关联的数据会自动删除.


"""
由于外键有实质性的诸多约束，当表特别多的时候外键的增多反而会增加耦合程度
	所以在实际开发项目中 有时候并不会使用外键创建表关系
	而是通过SQL语句层面 建立逻辑意义上的表关系
		eg:操作员工表的sql执行完毕之后,立刻跟着执行操作部门的sql
"""

一对多关系

以员工表和部门表为例
	1.先站在员工表的角度
    	问:一名员工能否对应多个部门
         答:不可以
 	2.再站在部门表的角度
    	问:一个部门能否对应多名员工
     	答:可以	
	结论:一个可以一个不可以,那么关系就是'一对多'
        员工表是多 部门表是一
	针对'一对多'关系,外键字段建在'多'的一方
    # 表关系没有'多对一'一说 都是'一对多'

代码验证：

# 创建部门表
create table dep(
	id int primary key auto_increment,
 	dep_name varchar(32),
  	dep_desc varchar(64)
);

# 创建员工表
create table emp(
	id int primary key auto_increment,
 	name varchar(32),  # 括号内的数字尽量不要超过255，写16的倍数
 	age int,
 	dep_id int,
	foreign key(dep_id) references dep(id)  # 这个是外键字段，references是关联 表名(关联字段名)
);

# 展示两个表的结构
mysql> desc dep;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| dep_name | varchar(32) | YES  |     | NULL    |                |
| dep_desc | varchar(64) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc emp;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(32) | YES  |     | NULL    |                |
| age    | int(11)     | YES  |     | NULL    |                |
| dep_id | int(11)     | YES  | MUL | NULL    |                |  # Key栏显示MUL，是外键
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> insert into dep(dep_name,dep_desc) values('讲师部','教书育人'),('财务部','发放工资');  # 必须先添加部门表数据
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 讲师部    | 教书育人     |
|  2 | 财务部    | 发放工资     |
+----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> insert into emp(name,age,dep_id) values('jason',18,1),('kevin',20,2),('tom',22,2);  # 然后才可以添加员工表数据(有外键的表)
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----+-------+------+--------+
| id | name  | age  | dep_id |
+----+-------+------+--------+
|  1 | jason |   18 |      1 |
|  2 | kevin |   20 |      2 |
|  3 | tom   |   22 |      2 |
+----+-------+------+--------+
3 rows in set (0.00 sec)

mysql> insert into emp(name,age,dep_id) values('ocase',18,3);  # 被关联表中id不存在就会报错
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db3`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

修改数据的时候外键字段无法修改和删除

mysql> delete from dep where id=1;  # 不能删除，报错
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db3`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
mysql> update dep set id=200 where id=1;  # 不能修改，报错
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db3`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

级联更新,级联删除

on update cascade:修改时，关联的数据会自动修改.
on delete cascade:删除时，关联的数据会自动删除.

create table dep1(
	id int primary key auto_increment,
 	dep_name varchar(32),
  	dep_desc varchar(64)
);

create table emp1(
	 id int primary key auto_increment,
 	 name varchar(32),
 	 age int,
 	 dep_id int,
	 foreign key(dep_id) references dep1(id) 
    on update cascade  # 级联更新
    on delete cascade  # 级联删除 
);


# 查看两个表的结构
mysql> desc dep1;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| dep_name | varchar(32) | YES  |     | NULL    |                |
| dep_desc | varchar(64) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc emp1;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(32) | YES  |     | NULL    |                |
| age    | int(11)     | YES  |     | NULL    |                |
| dep_id | int(11)     | YES  | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)


mysql> insert into dep1(dep_name, dep_desc) values('讲师部','讲课'),('财务部','发放工资'),('安保','防护');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dep1;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 讲师部    | 讲课         |
|  2 | 财务部    | 发放工资     |
|  3 | 安保      | 防护         |
+----+-----------+--------------+
3 rows in set (0.00 sec)

mysql> insert into emp1(name,age,dep_id) values('jason',18,1),('kevin',20,2),('oscar',32,2),('jarry',35,3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from emp1;
+----+-------+------+--------+
| id | name  | age  | dep_id |
+----+-------+------+--------+
|  1 | jason |   18 |      1 |
|  2 | kevin |   20 |      2 |
|  3 | oscar |   32 |      2 |
|  4 | jarry |   35 |      3 |
+----+-------+------+--------+
4 rows in set (0.00 sec)

mysql> delete from dep1 where id=3;  # 此时就可以删除了
Query OK, 1 row affected (0.01 sec)

mysql> select * from dep1;  # 部门表第三行删除了
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 讲师部    | 讲课         |
|  2 | 财务部    | 发放工资     |
+----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> select * from emp1;  # dep_id为3的也随之删除了
+----+-------+------+--------+
| id | name  | age  | dep_id |
+----+-------+------+--------+
|  1 | jason |   18 |      1 |
|  2 | kevin |   20 |      2 |
|  3 | oscar |   32 |      2 |
+----+-------+------+--------+
3 rows in set (0.00 sec)

mysql> update dep1 set id=666 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dep1;
+-----+-----------+--------------+
| id  | dep_name  | dep_desc     |
+-----+-----------+--------------+
|   2 | 财务部    | 发放工资     |
| 666 | 讲师部    | 讲课         |
+-----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> select * from emp1;
+----+-------+------+--------+
| id | name  | age  | dep_id |
+----+-------+------+--------+
|  1 | jason |   18 |    666 |
|  2 | kevin |   20 |      2 |
|  3 | oscar |   32 |      2 |
+----+-------+------+--------+
3 rows in set (0.00 sec)

多对多的表关系

以书籍表与作者表为例
	1.先站在书籍表的角度
  		问:一本书能否对应多个作者
      	答:可以
 	2.再站在作者表的角度
    	问:一个作者能否对应多本书
      	答:可以
   结论:两个都可以，关系就是'多对多'	
 	针对'多对多'不能在表中直接创建，需要新建第三张关系表

代码操作：

create table book(  # 书籍表
	id int primary key auto_increment,
 	title varchar(32),
  	price float(7,2)
);
create table author(  # 作者表
	id int primary key auto_increment,
 	name varchar(32),
  	phone bigint
);
create table book2author(  # 第三张表，用这张表专门存储两张表中间的关系，把外键字段都写在这张表中
	id int primary key auto_increment,
   author_id int,  # 被关联表是作者表
 	foreign key(author_id) references author(id)
  	on update cascade
  	on delete cascade,
   book_id int,  # 被关联表是书表，book_id是几就表示书的id是几
  	foreign key(book_id) references book(id)
  	on update cascade
  	on delete cascade
);


# 展示第三张表的结构
mysql> desc book2author;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| author_id | int(11) | YES  | MUL | NULL    |                |
| book_id   | int(11) | YES  | MUL | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into book(title,price) values('aaa',11.11),('bbb',22.22),('ccc',33.33),('ddd',44.44),('eee',55.55),('fff',66.66);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into author(name,phone) values('jason',100),('kevin',200),('tom',300);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
            
mysql> select * from book;
+----+-------+-------+
| id | title | price |
+----+-------+-------+
|  1 | aaa   | 11.11 |
|  2 | bbb   | 22.22 |
|  3 | ccc   | 33.33 |
|  4 | ddd   | 44.44 |
|  5 | eee   | 55.55 |
|  6 | fff   | 66.66 |
+----+-------+-------+
6 rows in set (0.00 sec)

mysql> select * from author;
+----+-------+-------+
| id | name  | phone |
+----+-------+-------+
|  1 | jason |   100 |
|  2 | kevin |   200 |
|  3 | tom   |   300 |
+----+-------+-------+
3 rows in set (0.00 sec)

mysql> insert into book2author(author_id,book_id) values(1,1),(1,2),(1,3),(2,1),(2,4),(2,5),(3,5),(3,6);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0
            
mysql> select * from book2author;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         1 |       3 |
|  4 |         2 |       1 |
|  5 |         2 |       4 |
|  6 |         2 |       5 |
|  7 |         3 |       5 |
|  8 |         3 |       6 |
+----+-----------+---------+
8 rows in set (0.00 sec)

mysql> select * from book2author where author_id=1;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         1 |       3 |
+----+-----------+---------+
3 rows in set (0.01 sec)

mysql> select * from book2author where book_id=5;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  6 |         2 |       5 |
|  7 |         3 |       5 |
+----+-----------+---------+
2 rows in set (0.00 sec)

一对一关系

以用户表与用户详情表为例
	1.先站在用户表的角度
  		问:一个用户能否对应多个用户详情
      答:不可以
 	2.再站在用户详情表的角度
    	问:一个用户详情能否对应多个用户
      	答:不可以
   结论:两个都可以,关系就是'一对一'或者没有关系	
 	针对'一对一'外键字段建在任何一方都可以，但是推荐建在查询频率较高(热数据)的表中。冷数据：不经常使用的数据。冷热分离。

代码操作：

create table userdetail(
	id int primary key auto_increment,
  	phone bigint
);

create table user(
	id int primary key auto_increment,
  	name varchar(32),
 	detail_id int unique,  # 需要加个唯一，出现后，下面就不能使用
  	foreign key(detail_id) references userdetail(id)
  	on update cascade
  	on delete cascade
);

mysql> desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(32) | YES  |     | NULL    |                |
| detail_id | int(11)     | YES  | UNI | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc userdetail;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| phone | bigint(20) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into userdetail(phone) values(100),(200);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into user(name,detail_id) values('kevin',1),('jerry',2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+-------+-----------+
| id | name  | detail_id |
+----+-------+-----------+
|  1 | kevin |         1 |
|  2 | jerry |         2 |
+----+-------+-----------+
2 rows in set (0.00 sec)

mysql> select * from userdetail;
+----+-------+
| id | phone |
+----+-------+
|  1 |   100 |
|  2 |   200 |
+----+-------+
2 rows in set (0.00 sec)

总结：

一对多的外键字段------>多的一方
一对一的外键字段------> 两边都可以，一般建在查询频率较高的一方
多对多的外键字段------>第三张表中

七、修改表ALTER TABLE

1. 修改表名  
      ALTER TABLE 表名 
                          RENAME 新表名;
2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;  # 增加该字段到第一个位置
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名1;  # 增加该字段到字段名1 后面
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;
4. 修改字段  # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

示例：

1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;

mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                # 添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          # 添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);  # # modify只能改字段数据类型完整约束，不能改字段名
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    # 修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;