下面是小凰凰的简介，看下吧！
💗人生态度：珍惜时间，渴望学习，热爱音乐，把握命运，享受生活
💗学习技能：网络 -> 云计算运维 -> python全栈( 当前正在学习中)
💗您的点赞、收藏、关注是对博主创作的最大鼓励，在此谢过！
有相关技能问题可以写在下方评论区，我们一起学习，一起进步。
后期会不断更新python全栈学习笔记，秉着质量博文为原则，写好每一篇博文。

一、表完整性约束

1、大致介绍

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

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

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

UNSIGNED 无符号
ZEROFILL 使用0填充

说明：

1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 年龄必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

2、not null与default

（1）not null

==================not null====================
mysql> create table t1(id int); #id字段默认可以插入空
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values(); #可以插入空


mysql> create table t2(id int not null); #设置字段id不为空
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t2 values(); #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value

（2）default

==================default====================
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
mysql> create table t3(id int default 1);
mysql> alter table t3 modify id int not null default 1;



==================综合练习====================
mysql> create table student(
    -> name varchar(20) not null,
    -> age int(3) unsigned default 18, # not null一般不与default连用！这里可以添加not null，但是因为有default了因此就可以不加了
    -> sex enum('male','female') default 'male',
    -> hobby set('play','study','read','music') default 'play,music'
    -> );
mysql> desc student;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| name  | varchar(20)                        | NO   |     | NULL       |       |
| age   | int(3) unsigned                    | YES  |     | 18         |       |
| sex   | enum('male','female')              | YES  |     | male       |       |
| hobby | set('play','study','read','music') | YES  |     | play,music |       |
+-------+------------------------------------+------+-----+------------+-------+
mysql> insert into student(name) values('egon');
mysql> select * from student;
+------+-----+------+------------+
| name | age | sex  | hobby      |
+------+-----+------+------------+
| egon |  18 | male | play,music |
+------+-----+------+------------+

3、unique

（1）唯一约束

============设置唯一约束 UNIQUE===============
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);


mysql> insert into department1 values(1,'IT','技术');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技术');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

（2）联合唯一约束+非空（相当于复合主键）

======================== 设置联合唯一约束 ===========================
create table service(
ip varchar(15) not null,
port char(5) not null,
service_name varchar(10) not null,
unique(ip,port)
);
# 注意联合唯一约束加非空约束就相当于复合主键。注意是复合主键不是联合主键！

4、primary key

从约束角度看primary key字段的值不为空且唯一，即它的约束效果就等于not null+unique，那么它本身相比not null+unique肯定也有优势：

主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。主键的主要作用是索引！

（1）单列主键

注意：innodb存储引擎中一个表必须要有主键，如果一个表没有主键，那么会从上倒下依次查找一个not null且unique的字段为主键，如果没有这样一个字段，自动创建一个6字节的指针作为索引

============单列做主键============================================
# 方法一：not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

# 方法二：在某一个字段后用primary key（推荐）
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)

# 方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name

mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

（2）复合主键

这里要区别一下联合主键和复合主键：联合主键体现在多个表上，复合主键体现在一个表中的多个字段
复合主键应用场景：像ip+port，ip是可以重复的，因为端口可以不同。端口可以重复，因为ip可以不同。像这种情况下，主键需要两个字段才能唯一，不重复，这个就需要复合主键

======================== 多列做主键（复合主键）=========================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

5、auto_increment

（1）设置自增字段

约束字段为自动增长，被约束的字段必须同时被key约束

insert插入时，可以指定字段，insert into student(name) values ('huahua')

# 不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

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    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+


# 也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


# 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

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

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

# 要让自增计数器清零，应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

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

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

（2）指定自增的起始偏移量及步长

1. 表级指定 # 无论是否重新连接mysql，都只对该表生效
# 创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3; # 指定该表的自增初始值，这个位置是表选项的书写位置
注意：'表选项好像没有自增步长。因此表级无法指定自增步长！'

2. 会话级指定 # 对当前mysql连接的所有表生效，下次重新连接mysql，恢复原样
show session variables like 'auto_inc%';
set session auto_increment_offset=3; # 修改会话级别的自增初始值
set session auto_increment_increment=2; # 修改会话级别的自增步长

3. 全局指定 # 无论是否重新连接mysql。自增初始值和自增步长都是全局指定的这个大小。
show variables like 'auto_incre%';
set global auto_increment_offset=3; # 修改全局级别的自增初始值
set global auto_increment_increment=5; # 修改全局级别的自增步长

注意：
如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋

（3）常见的表选项

# 表选项就是，创建一个表的时候，对该表的整体设定，主要有如下几个：
charset = 要使用的字符编码，

engine = 要使用的存储引擎（也叫表类型），

auto_increment = 设定当前表的自增长字段的初始值，默认是1

comment = '该表的一些说明文字'

6、foreign key、级联更新及删除

（1）快速理解foreign key

员工信息表有三个字段：工号 姓名 部门id
还应该有个部门表：部门id，部门name，部门员工。
部门表和员工信息表之间怎么建立关系呢？就是通过部门id建立关系的。因此部门id就是员工信息表的外键

#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段
create table department( # 部门表
id int primary key,
name varchar(20) not null
)engine=innodb;

#dpt_id外键，关联父表（department主键id），同步更新，同步删除
create table employee( # 员工信息表
id int primary key,
name varchar(20) not null,
dpt_id int,
constraint fk_name foreign key(dpt_id) # 声明谁是外键 
references department(id) # 外键和哪个表的哪个字段关联。该字段可以是任何字段。
on delete cascade # 级联删除
on update cascade # 级联更新 
)engine=innodb;


#先往父表department中插入记录
insert into department values
(1,'欧德博爱技术有限事业部'),
(2,'艾利克斯人力资源部'),
(3,'销售部');


#再往子表employee中插入记录
insert into employee values
(1,'egon',1),
(2,'alex1',2),
(3,'alex2',2),
(4,'alex3',2),
(5,'李坦克',3),
(6,'刘飞机',3),
(7,'张火箭',3),
(8,'林子弹',3),
(9,'加特林',3)
;


#删父表department，子表employee中对应的记录跟着删
mysql> delete from department where id=3;
mysql> select * from employee;
+----+-------+--------+
| id | name  | dpt_id |
+----+-------+--------+
|  1 | egon  |      1 |
|  2 | alex1 |      2 |
|  3 | alex2 |      2 |
|  4 | alex3 |      2 |
+----+-------+--------+


#更新父表department，子表employee中对应的记录跟着改
mysql> update department set id=22222 where id=2;
mysql> select * from employee;
+----+-------+--------+
| id | name  | dpt_id |
+----+-------+--------+
|  1 | egon  |      1 |
|  3 | alex2 |  22222 |
|  4 | alex3 |  22222 |
|  5 | alex1 |  22222 |
+----+-------+--------+

注意：因为员工信息表中有部门id，因此部门表应该先create！不然会报错

（2）级联更新、级联删除

constraint fk_name foreign key(dpt_id) # 声明谁是外键 
references department(id) # 外键和那个表的那个字段关联，该字段可以不是那个表的主键，但必须是unique的
on delete cascade # 级联删除
on update cascade # 级联更新 

假如不要级联更新删除语句，那么因为外键约束，部门表中的部门无法删除、部门id无法修改，因为部门id在成员信息表中也有。你如果删除了部门，成员信息表中的部门id是不会删除的，就会信息不同步！因此需要采用级联更新与删除，部门id一变，成员信息表中相应成员的部门id也跟着变，部门删除，部门下的所有员工信息也从成员信息表中删除！保障有外键关联的两个表的内容的同步、准确！

二、建立表之间的关系

1、如何找出两张表之间的关系

#多对一：
记住：一对多和多对一都是一样的。
站在成员角度看：成员与部门是多对一
站在部门角度看：部门与成员是一对多
'建立关系方法：需要在多的一方的表中添加外键约束，设置级联更新、删除！'

#多对多
两个表是双向的多对一，即作者与图书的关系，作者可以著有多本图书，图书可有多个作者
'建立关系方法：需要定义一个这两张表的关系表来专门存放二者的关系'

#一对一:
两个表是双向的一对一，用户与博客的关系，一个用户只有一个自己的CSDN博客，自己的这个CSDN博客的用户也只有自己。注意这里是自己的csdn博客，而不是csdn博客平台！
'建立关系方法：选取用户表添加博客id外键字段（博客从属于用户，因此选择用户表添加外键字段），将用户表的博客id外键字段设置成unique，且博客表中的博客id字段也需要unique，这样才能保证一对一！'

2、建立表之间的关系

（1）多对一实操

#一对多或称为多对一
三张表：出版社，作者信息，书

一对多（或多对一）：一个出版社可以出版多本书

关联方式：foreign key
=====================多对一=====================
create table press(
id int primary key auto_increment,
name varchar(20)
);

create table book(
id int primary key auto_increment,
name varchar(20) unique, # 这里的name需要指定unique，因为如果可以重复，那么我就可以重复指定某本书，并指定不同的出版社！这就变成了多对多关系！
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);


insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;

insert into book(name,press_id) value # 因为书和出版社是多对一，因此一本书只能有一个出版社。所以插入数据时左侧书籍没有重复的，右侧出版社可以重复
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3)
;

（2）多对多实操

多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
　　
关联方式：foreign key+一张新的表
=====================多对多=====================
create table author(
id int primary key auto_increment,
name varchar(20)
);

'book表就省略不写了！它必须是要有的！这里只是懒得写'

#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);


#插入四个作者，id依次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

#每个作者与自己的代表作如下
1 egon: 
      1 九阳神功
      2 九阴真经
      3 九阴白骨爪
      4 独孤九剑
      5 降龙十巴掌
      6 葵花宝典


2 alex: 
      1 九阳神功
      6 葵花宝典

3 yuanhao:
      4 独孤九剑
      5 降龙十巴掌
      6 葵花宝典

4 wpq:
      1 九阳神功


insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;

（3）一对一实操

一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系

关联方式：foreign key+unique
#一定是student来foreign key表customer，这样就保证了：
#1 学生一定是一个客户，
#2 客户不一定是学生，但有可能成为一个学生


create table customer(
id int primary key auto_increment, # id必须唯一，primary key就是唯一的
name varchar(20) not null,
qq varchar(10) not null,
phone char(16) not null
);


create table student(
id int primary key auto_increment,
class_name varchar(20) not null,
customer_id int unique, # 该字段一定要是唯一的
foreign key(customer_id) references customer(id) # 外键的字段一定要保证unique，因此customer表中的id必须唯一，这样才能保证一对一！
on delete cascade
on update cascade
);


#增加客户
insert into customer(name,qq,phone) values
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230)
;


#增加学生
insert into student(class_name,customer_id) values
('脱产3班',3),
('周末19期',4),
('周末19期',5)
;

三、修改表

语法：
1. 修改表名
      ALTER TABLE 表名 RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]; # 默认添加都在最后
      
      ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  FIRST; # 在最前面添加一个字段
      
      ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名; # 在某个字段后，添加一个新的字段

3. 删除字段
      ALTER TABLE 表名 DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 CHANGE  字段名 新字段名 数据类型 [完整性约束条件…];

四、复制表

MariaDB [db1]> select * from service;
+----+------+--------------+
| ip | port | service_name |
+----+------+--------------+
|  2 | 55   |              |
|  4 | 77   |              |
|  6 | 99   |              |
+----+------+--------------+
如上所示select * from service;执行出来也是一个表的样子。'这其实是一个存在于内存中的虚拟表。因此我们可以基于此复制表。'

'复制表结构＋记录 （ 主键、外键和索引不会复制）'
mysql> create table new_service select * from service;

五、删除表

DROP TABLE 表名;