一、约束条件
CREATE TABLE 表名(字段1 类型[(宽度) 约束条件],字段2 类型[(宽度) 约束条件]);
1.1、条件列表
约束条件在上一节有提到过如NOT NULL、UNSIGNED，它跟宽度一样，是可选的；条件有很多种，下面是罗列：
| 约束条件 | 简称 | 作用 | 详情 |
|---|---|---|---|
| NOT NULL | 字段不能为NULL | 默认数据可为NULL，可标识该字段下的数据不能为NULL，必须赋值 | |
| DEFAULT | 为字段设置默认值 | 字段默认值为NULL，修改默认值后插入数据时不指定，则使用修改后的默认值 | |
| UNIQUE | UNI | 该字段值是唯一的 | |
| UNSIGNED | 取消数值符号 | ||
| ZEROFILL | 使用0填充 | 如char不足位数时，指定此值后则不足用0填充至指定位数 | |
| PRIMARY KEY | PRI | 非空且唯一 | 除了非空唯一，还是innodb组织数据的依据 |
| FOREIGN KEY | 外键 | 标识字段为外键 | |
| AUTO_INCREMENT | 自动增长 | 字段的值自动增长 |
1.2、补充知识点
插入数据的时候可以指定字段
MariaDB [study]> INSERT INTO copy(name,test) VALUES('li',6);
Query OK, 1 row affected (0.004 sec)
1.3、default（默认值）
跟Python默认位置参数一样，MySQL也可以对字段设置默认值！只需要在字段后面跟上default和其值即可！
MariaDB [study]> CREATE TABLE student(name varchar(16),gender ENUM('male','female') DEFAULT 'male');
Query OK, 0 rows affected (0.020 sec)
MariaDB [study]> INSERT INTO student(name) VALUES('sanxi');
Query OK, 1 row affected (0.004 sec)
MariaDB [study]> SELECT name,gender FROM student;
+-------+--------+
| name | gender |
+-------+--------+
| sanxi | male |
+-------+--------+
1 row in set (0.000 sec)
MariaDB [study]> DESC student; # 可以看到gender字段的默认值是male
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| gender | enum('male','female') | YES | | male | |
+--------+-----------------------+------+-----+---------+-------+
2 rows in set (0.021 sec)
1.4、unique唯一值
单列唯一
类型后面直接unique即可
MariaDB [study]> CREATE TABLE t2(id int UNIQUE,name varchar(16));
Query OK, 0 rows affected (0.022 sec)
MariaDB [study]> DESC t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.026 sec)
MariaDB [study]> INSERT INTO t2 VALUES(1,'sanxi'),(1,'chrystal');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
联合唯一
即单个字段可以重复，多个字段组合起来必须唯一！这就好比IP+PORT（一个公网IP下有多台设备）独一无二！
unique(字段1,字段2)，最好放在最后！
MariaDB [study]> CREATE TABLE t3(name varchar(16),host varchar(16),port int,unique(host,port));
Query OK, 0 rows affected (0.024 sec)
MariaDB [study]> INSERT INTO t3 VALUES('sanxi','192.168.0.5',8080),('chrystal','192.168.0.8',8080),('hi','192.168.0.5',8088);
Query OK, 3 rows affected (0.003 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [study]> INSERT INTO t3 VALUES('ha','192.168.0.5',8080); # 重复了
ERROR 1062 (23000): Duplicate entry '192.168.0.5-8080' for key 'host'
1.5、primary key(主键)
1.5.1、什么是主键？
单单从约束效果上来看primary key相当于not null + unique，即非空且唯一！但是它除了有约束效果之外，还是InnoDB存储引擎组织数据的依据，可以称之为索引组织表，InnoDB存储引擎在创建表的时候必须要有且唯一！
- 一张表中有且只能有一个主键，如不设置主键，InnoDB会从上到下搜索直到遇到一个非空且唯一的字段将它自动升级为主键。
- 如表中没有主键也没有其他任何的非空且唯一的字段，那么InnoDB会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它，就无法提升查询速度。
- 一张表中应该有一个主键字段，通常是ID字段，只要能标识序号就行！
1.5.2、单列主键
MariaDB [study]> CREATE TABLE t4(id int PRIMARY KEY,name varchar(16));
Query OK, 0 rows affected (0.032 sec)
MariaDB [study]> DESC t4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.045 sec)
1.5.3、联合主键
类似unique的联合唯一，主键还可以联合主键（多个字段联合起来作为表的主键，类似欧盟主席）。
MariaDB [study]> CREATE TABLE t5(name VARCHAR(16),host VARCHAR(16),port int,PRIMARY KEY(host,port));
Query OK, 0 rows affected (0.035 sec)
MariaDB [study]> DESC t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| host | varchar(16) | NO | PRI | NULL | |
| port | int(11) | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.042 sec)
1.5.4、auto_increment
自动增长，像前面添加ID字段序号时由人为维护比较麻烦，可以用这个参数实现插入数据序号自动增长；此参数仅为主键服务！！！
MariaDB [study]> CREATE TABLE t6(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(16));
Query OK, 0 rows affected (0.035 sec)
MariaDB [study]> INSERT INTO t6(name) VALUES('sanxi'),('chrystal');
Query OK, 2 rows affected (0.017 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [study]> SELECT id,name FROM t6;
+----+----------+
| id | name |
+----+----------+
| 1 | sanxi |
| 2 | chrystal |
+----+----------+
2 rows in set (0.000 sec)
MariaDB [study]> DESC t6;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.031 sec)
1.5.5、注意
以后在创建表ID（数据的唯一标识）字段时，应该使用primary key + auto_increment！且对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长！
MariaDB [study]> DELETE FROM t6 WHERE id >=1;
Query OK, 2 rows affected (0.010 sec)
MariaDB [study]> INSERT INTO t6(name) VALUES('hi');
Query OK, 1 row affected (0.003 sec)
MariaDB [study]> SELECT * FROM t6;
+----+------+
| id | name |
+----+------+
| 3 | hi |
+----+------+
1 row in set (0.000 sec)
1.5.6、truncate
清空表，比起delete一条一条地删除记录，truncate是直接清空表，因此连自增都能清掉，在删除大表时可以考虑用它！
MariaDB [study]> TRUNCATE t6;
Query OK, 0 rows affected (0.045 sec)
MariaDB [study]> INSERT INTO t6(name) VALUES('ha');
Query OK, 1 row affected (0.004 sec)
MariaDB [study]> SELECT * FROM t6;
+----+------+
| id | name |
+----+------+
| 1 | ha |
+----+------+
1 row in set (0.000 sec)
重点：foreign key(外键)
我们说过MySQL是关系型数据库，关系关系，就是数据之间彼此有紧密关联！而外键就是帮助InnoDB建立表与表之间关系的特殊字段！注意：是InnoDB专用！表与表之间的关系最多有三种：
- 一对多关系
- 多对多关系
- 一对一关系
一对多关系
想象一下：我们有一个学校，学生有好几万名，我们需要将学生信息录入数据库！用到的字段假设有：姓名、年龄、班级、籍贯，很容易看出班级、籍贯这两个字段的值有很多是重复的值：好多学生来自同一个班级、很多学生来自同一个省市，几万人存下来省得非常累赘，也浪费了磁盘空间等资源！
这时候我们可以将这张大表拆分开来，班级、籍贯等重复频率特别高的字段另起炉灶，用外键将它们之间关联起来！
一对多的表关系，外键字段在多的一方；且创建表时，一定要先建被关联表；在录入数据的时候，也必须先录入被关联表
先建表结构
MariaDB [study]> CREATE TABLE stu_info(id int PRIMARY KEY AUTO_INCREMENT,class VARCHAR(16));
Query OK, 0 rows affected (0.036 sec)
MariaDB [study]> CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(16) NOT NULL,
-> age INT,
-> class_id INT,
-> FOREIGN KEY(class_id) REFERENCES stu_info(id)
-> ON DELETE CASCADE # 同步更新
-> ON UPDATE CASCADE); # 同步删除
Query OK, 0 rows affected (0.039 sec)
先插入被关联表的数据
MariaDB [study]> INSERT INTO stu_info(class) VALUES('Python'),('Linux');
Query OK, 2 rows affected (0.017 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [study]> SELECT id,class FROM stu_info;
+----+--------+
| id | class |
+----+--------+
| 1 | Python |
| 2 | Linux |
+----+--------+
2 rows in set (0.001 sec)
插入主表数据
MariaDB [study]> INSERT INTO student(name,age,class_id) VALUES('sanxi',18,1),('Ben',20,2),('chrystal',18,1);
Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [study]> SELECT * FROM student;
+----+----------+------+----------+
| id | name | age | class_id |
+----+----------+------+----------+
| 1 | sanxi | 18 | 1 |
| 2 | Ben | 20 | 2 |
| 3 | chrystal | 18 | 1 |
+----+----------+------+----------+
3 rows in set (0.000 sec)
级联更新、级联删除
它可以让表之间真正做到数据之间有关系，系同步操作，即表之间有数据改动，立即同步操作到相关联的表！我们称为：级联更新、级联删除！用法是创建表结构时在最后添加如下两行：
ON DELETE CASCADE # 同步更新
ON UPDATE CASCADE); # 同步删除
MariaDB [study]> DELETE FROM stu_info WHERE id=2;
Query OK, 1 row affected (0.004 sec)
MariaDB [study]> SELECT * FROM student; # 在被关联表删除数据后，主表立即同步操作，有关数据立即被删除！
+----+----------+------+----------+
| id | name | age | class_id |
+----+----------+------+----------+
| 1 | sanxi | 18 | 1 |
| 3 | chrystal | 18 | 1 |
+----+----------+------+----------+
2 rows in set (0.001 sec)
多对多关系
想象一下：我们做一个兴趣爱好调研，一个人可以有多种爱好，而每一种爱好也可以被多个人所喜爱！那么这就不是上面的一对多关系了，而是多人对应多种爱好的关系了！
如果按照上面一对多那样建立外键会有一个严重问题，创建表时必须先创建被关联表，两者都是相互被关联，会引起死循环！针对多对多字段建立表关系，不能在两张原有的表中创建外键，需要单独创建一张表，专门用于存储两者之间的关系！
先建立表结构
MariaDB [study]> CREATE TABLE people(id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(16) NOT NULL,
-> age INT);
Query OK, 0 rows affected (0.021 sec)
MariaDB [study]> CREATE TABLE hobby(id INT PRIMARY KEY AUTO_INCREMENT,
-> hobby VARCHAR(16));
Query OK, 0 rows affected (0.021 sec)
创建关系表
MariaDB [study]> CREATE TABLE humanhobby(id INT PRIMARY KEY AUTO_INCREMENT,
-> people_id INT NOT NULL,
-> hobby_id INT NOT NULL,
-> FOREIGN KEY(people_id) REFERENCES people(id)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE,
-> FOREIGN KEY(hobby_id) REFERENCES hobby(id)
-> ON DELETE CASCADE
-> ON UPDATE CASCADE);
Query OK, 0 rows affected (0.022 sec)
插入主表数据
MariaDB [study]> INSERT INTO hobby(hobby) VALUES('playing'),('reading'),('running');
Query OK, 3 rows affected (0.013 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [study]> INSERT INTO people(name,age) VALUES('sanxi',18),('chrystal',18);
Query OK, 2 rows affected (0.010 sec)
Records: 2 Duplicates: 0 Warnings: 0
插入关系表数据
MariaDB [study]> INSERT INTO humanhobby(people_id,hobby_id) VALUES
-> (1,1),
-> (1,2),
-> (1,3),
-> (2,2),
-> (2,3);
Query OK, 5 rows affected (0.004 sec)
Records: 5 Duplicates: 0 Warnings: 0
验证
MariaDB [study]> SELECT * FROM humanhobby;
+----+-----------+----------+
| id | people_id | hobby_id |
+----+-----------+----------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
+----+-----------+----------+
5 rows in set (0.000 sec)
MariaDB [study]> DELETE FROM hobby WHERE id=1; # 删除主表数据后，这关联表数据同步删除
Query OK, 1 row affected (0.004 sec)
MariaDB [study]> SELECT id,people_id,hobby_id FROM humanhobby;
+----+-----------+----------+
| id | people_id | hobby_id |
+----+-----------+----------+
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
+----+-----------+----------+
4 rows in set (0.000 sec)
CREATE TABLE user(id int PRIMARY KEY AUTO_INCREMENT,
name varchar(16) NOT NULL,
age int,
gender enum('male','female') DEFAULT 'male',
details int UNIQUE,
FOREIGN KEY(details) REFERENCE user_details(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
一对一关系
在很多社交平台，每个人都有很多个人信息，当你点击别人头像查看信息时，多数只显示一些最常见的如年龄、性别、名称，更其它更详细的信息多数要点击查看详情之类的按钮才能看到！因为直接显示所有信息，一个是看起来杂乱不堪，二是对服务器压力大！
同理，如果一个表的字段很多，你首先得将所有数据读取出来再进行筛选；但每次查询又不是所有的字段都用得到，这时就可以将表一分二，读取较为频繁的数据放在一张表，低频率数据放在另一张表，还是用外键把它们关联起来！
外键字段在任意一方都可以，但是建议放在查询频率较高的表中
先创建被关联表结构
MariaDB [study]> CREATE TABLE user_details(user_id int PRIMARY KEY AUTO_INCREMENT,
-> address varchar(40),
-> signature varchar(40));
Query OK, 0 rows affected (0.033 sec)
创建主表结构
MariaDB [study]> CREATE TABLE user(id int PRIMARY KEY AUTO_INCREMENT,
-> name varchar(16) NOT NULL,
-> age int NOT NULL,
-> gender enum('male','female') DEFAULT 'male',
-> details int UNIQUE, # 关联到另一张表的该字段必须唯一
-> FOREIGN KEY(details) REFERENCES user_details(user_id) # 必须保证被关联的字段是唯一的
-> ON DELETE CASCADE
-> ON UPDATE CASCADE
-> );
Query OK, 0 rows affected (0.035 sec)
插入被关联表数据
MariaDB [study]> INSERT INTO user_details(user_id,address,signature) VALUES(1,'GD','这个人很懒，什么都没写！');
Query OK, 1 row affected (0.022 sec)
MariaDB [study]> INSERT INTO user_details(user_id,address,signature) VALUES(2,'BJ','我喜欢看书，每天到处乱跑！');
Query OK, 1 row affected (0.004 sec)
插入主表数据
MariaDB [study]> INSERT INTO user(name,age,details) VALUES('sanxi',18,1);
Query OK, 1 row affected (0.015 sec)
MariaDB [study]> INSERT INTO user(name,age,details) VALUES('zhangsan',30,2);
Query OK, 1 row affected (0.004 sec)
表关系总结：
- 表关系的建立需要用到foreign key。
- 一对多，外键字段建在多的一方。
- 多对多，自己开设额外的表来存储关系。
- 一对一，任意一方都行，建议是查询频率较高的一方。
二、补充：修改表的语法
MySQL对大小写不严格区分，因此上面的命令选项用大写小写都可以，只是规范建议用大写！
2.1、修改表名
alter table 表名 rename 新表名
MariaDB [study]> ALTER TABLE t3 rename ttt;
Query OK, 0 rows affected (0.013 sec)
2.2、增加字段
2.2.1、插入最后面
alter table 表名 add 字段名 字段类型(宽度) 约束条件
MariaDB [study]> ALTER TABLE ttt add detials varchar(16) NOT NULL;
Query OK, 0 rows affected (0.030 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [study]> DESC ttt;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| host | varchar(16) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
| detials | varchar(16) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.039 sec)
2.2.2、插入最前面
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first，即此字段放在表最前面
MariaDB [study]> ALTER TABLE ttt add user_id int NOT NULL first;
Query OK, 0 rows affected (0.064 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [study]> DESC ttt;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| user_id | int(11) | NO | | NULL | |
| name | varchar(16) | YES | | NULL | |
| host | varchar(16) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
| detials | varchar(16) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.039 sec)
2.2.3、指定插入位置
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名，即放在谁后面
MariaDB [study]> ALTER TABLE ttt add test int NOT NULL after port;
Query OK, 0 rows affected (0.069 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [study]> DESC ttt;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| user_id | int(11) | NO | | NULL | |
| name | varchar(16) | YES | | NULL | |
| host | varchar(16) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
| test | int(11) | NO | | NULL | |
| detials | varchar(16) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.029 sec)
2.3、删除字段
alter table 表名 drop 字段名
MariaDB [study]> ALTER TABLE ttt DROP user_id;
Query OK, 0 rows affected (0.071 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [study]> DESC ttt;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| host | varchar(16) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
| test | int(11) | NO | | NULL | |
| detials | varchar(16) | NO | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.036 sec)
2.4、修改字段
修改字段类型、条件
alter table 表名 modify 字段名 字段类型(宽度) 约束条件
MariaDB [study]> ALTER TABLE ttt modify detials varchar(8);
Query OK, 3 rows affected (0.068 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [study]> DESC ttt; # 长度变成了8，约束条件也没了
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| host | varchar(16) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
| test | int(11) | NO | | NULL | |
| detials | varchar(8) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.041 sec)
修改字段名
alter table 表名 change 旧字段名 新字段名 旧字段类型(宽度) 约束条件
MariaDB [study]> ALTER TABLE ttt change detials info varchar(8);
Query OK, 0 rows affected (0.023 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [study]> DESC ttt;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| host | varchar(16) | YES | MUL | NULL | |
| port | int(11) | YES | | NULL | |
| test | int(11) | NO | | NULL | |
| info | varchar(8) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.030 sec)
2.5、复制表
create table 新表名 select * from 旧表，此方法只能复制表结构和数据，不能复制主键、外键等等；当条件为假时，仅复制表结构！
MariaDB [study]> CREATE TABLE copy SELECT * FROM ttt;
Query OK, 3 rows affected (0.051 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [study]> SELECT * FROM copy;
+----------+-------------+------+------+------+
| name | host | port | test | info |
+----------+-------------+------+------+------+
| sanxi | 192.168.0.5 | 8080 | 0 | |
| chrystal | 192.168.0.8 | 8080 | 0 | |
| hi | 192.168.0.5 | 8088 | 0 | |
+----------+-------------+------+------+------+
3 rows in set (0.000 sec)
MariaDB [study]> DESC copy;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | YES | | NULL | |
| host | varchar(16) | YES | | NULL | |
| port | int(11) | YES | | NULL | |
| test | int(11) | NO | | NULL | |
| info | varchar(8) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.030 sec)
作业：如何判断表关系及如何建立表关系