MySQL之建表操作_mysql建表怎么建

表操作

创建表

简单语法

create table 表名(
    列名 列数据类型,
    列名 列数据类型
    );
1
2
3
4

mysql> create table stu(
    -> name char(10),
    -> age int(3)
    -> );
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> show tables;  #显示当前数据库中所有的表
+-----------------+
| Tables_in_huazi |
+-----------------+
| stu             |
+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

数据类型

在MySQL中，有三种主要的类型：文本，数值，日期/时间类型

文本类型

数值类型

注：这些数值类型拥有额外的选项 unsigned。通常，整数可以是负数或正数。如果添加 unsigned属性，那么范围将从 0 开始，而不是某个负数。

日期/时间类型

Bit数据类型

常见数据类型

MySQL存储引擎

• MySQL中的数据用各种不同的技术存储在文件(或者内存）中， 每种技术都使用不同的存储机制、 索引技巧、 锁定水平。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型）。
• 数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
• 现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。
• InnoDB 事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。应用于对事务的完整性要求高,在并发条件下要求数据的一致性的情况。 MySQL 5.5.5 之后，InnoDB 作为默认存储引擎。
• MyISAM 是基于 ISAM 的存储引擎，并对其进行扩展，是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。应用于以读写操作为主， 很少更新 、 删除 ， 并对事务的完整性、 并发性要求不高的情况。
• MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他数据提供快速访问。
• MEMORY：表的数据存放在内存中，访问效率高 ，但一旦服务关闭，表中的数据全部丢失。
• MERGE: 是一组MyISAM表的组合。 可以突破对单个MyISAM表大小的限制， 并提高访问效率。

注：默认情况下， 创建表不指定表的存储引擎， 则会使用配置文件my.cnf中 default-storage-engine=InnoDB指定的存储引擎。

创建表的三个操作

• 1.指定字段以及类型
• 2.设定存储引擎
• 3.设置字符集和校对规则

创建表时指定存储引擎，字符集，校对规则，行格式

• InnoDB 存储引擎支持四种行格式：redundant，compact，dynamic，compressed

mysql> create table stu(
    -> name varchar(10) comment '姓名',
    -> age tinyint(3) comment '年龄',
    -> gender enum('男','女')
    -> )engine=innodb default charset=gbk row_format=dynamic;
Query OK, 0 rows affected, 1 warning (0.02 sec)


mysql> show create table stu\G;
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `age` tinyint DEFAULT NULL COMMENT '年龄',
  `gender` enum('男','女') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)


mysql> drop table stu;    # 删除表
Query OK, 0 rows affected (0.03 sec)

mysql> create table stu(
    -> name varchar(10) comment '姓名',
    -> age tinyint(3) comment '年龄',
    -> gender enum('男','女')
    -> )engine=innodb default charset=gbk collate=gbk_chinese_ci row_format=dynamic;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show create table stu\G;
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `age` tinyint DEFAULT NULL COMMENT '年龄',
  `gender` enum('男','女') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)


mysql> create table teacher( 
	-> name varchar(10) comment '姓名', 
	-> age tinyint(3) comment '年龄', 
	-> gender enum('男','女') 
	-> )engine=innodb charset=gbk collate=gbk_chinese_ci row_format=dynamic;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show create table teacher;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
  `name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `age` tinyint DEFAULT NULL COMMENT '年龄',
  `gender` enum('男','女') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC       |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table worker( 
	-> name varchar(10) comment '姓名', 
	-> age tinyint(3) comment '年龄', 
	-> gender enum('男','女') 
	-> )engine=innodb;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show create table worker;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                            |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| worker | CREATE TABLE `worker` (
  `name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `age` tinyint DEFAULT NULL COMMENT '年龄',
  `gender` enum('男','女') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci       |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table body( 
	-> name varchar(10) comment '姓名', 
	-> age tinyint(3) comment '年龄', 
	-> gender enum('男','女') 
	-> )engine=innodb default;  # 会报错
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1


mysql> create table body( 
	-> name varchar(10) comment '姓名', 
	-> age tinyint(3) comment '年龄', 
	-> gender enum('男','女') 
	-> )engine=innodb;
Query OK, 0 rows affected, 1 warning (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

总结：在定义表时，写不写default都不影响。
在定义表名和列名时，本质是使用反引号包裹

查看表

显示数据库中所有表

语法：

mysql> show tables [from 数据库名] [like wild];
1

mysql> show tables;
+-----------------+
| Tables_in_huazi |
+-----------------+
| stu             |
+-----------------+
1 row in set (0.00 sec)


mysql> show tables from huazi like "%u";
+----------------------+
| Tables_in_huazi (%u) |
+----------------------+
| stu                  |
+----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

显示数据库中表的信息（表结构）

语法：

mysql> desc 表名 [列名];
或
mysql> describe 表名 [列名];
或
mysql> show columns from 表名;
1
2
3
4
5

mysql> desc stu;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)


mysql> desc stu name;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)


mysql> show columns from stu;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

显示表中各列的详细信息

语法：

mysql> show create table 表名\G;
(\G表示向mysql服务器发送命令，垂直显示结果)
1
2

mysql> show create table stu\G;
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `name` char(10) DEFAULT NULL,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified
1
2
3
4
5
6
7
8
9
10
11

定义表中的列名和表名，本质上是使用反引号包裹

删除表

语法

mysql> drop table [if exists] 表名;
1

mysql> create table stu(
    -> `姓名` varchar(10),
    -> `年龄` int(3),
    -> `学号` int
    -> )engine=innodb default charset=utf8 row_format=dynamic;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> show create table stu\G;
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `姓名` varchar(10) DEFAULT NULL,
  `年龄` int DEFAULT NULL,
  `学号` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

mysql> drop table if exists stu;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
Empty set (0.00 sec)
1
2
3
4
5

修改表结构（（alter table 表名）开头）

修改表名（rename）

语法

mysql> alter table 表名 rename 新表名;
或
mysql> rename table 表名 to 新表名;
1
2
3

修改列名（change）

语法

mysql> alter table 表名 change 原列名 新列名 数据类型;
1

修改列的数据类型（modify）

语法

mysql> alter table 表名 modify 列表 数据类型;
或
mysql> alter table 表名 modify 列表 数据类型 约束类型;  #修改数据类型时添加约束
1
2
3

删除列（drop）

语法

mysql> alter table 表名 drop 列名;
1

增加列（add）

语法

mysql> alter table 表名 add 列名 数据类型;
mysql> alter table 表名 add 列名 数据类型 after 列名;   (在指定列名之后添加字段)
mysql> alter table worker add 性别 char(1) not null;  (添加列时加约束)
1
2
3

举例

mysql> create table stu(
    -> name char(10) comment '姓名',
    -> age int(3) comment '年龄'
    -> )engine=innodb default charset=gbk row_format=dynamic;

mysql> alter table stu rename stu_info; # 修改表名
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-----------------+
| Tables_in_huazi |
+-----------------+
| stu_info        |
+-----------------+
1 row in set (0.03 sec)


mysql> alter table stu_info change `name` `姓名` char(10); # 修改列名
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu_info;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名  | char(10) | YES  |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> alter table stu_info modify `age` tinyint(3); # 修改数据类型
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc stu_info;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名  | char(10) | YES  |     | NULL    |       |
| age   | tinyint  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> alter table stu_info drop `age`; # 删除列
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu_info;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)


mysql> alter table stu_info add `id` int after `姓名`; # 添加列
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu_info;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名  | char(10) | YES  |     | NULL    |       |
| id    | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

复制表结构（2种方法）

方法1

在create table语法的末尾添加like子句，可以将原表的表结构复制到新表中。
语法：

mysql> create table 新表名 like 原表名;
1

mysql> show create table stu_info\G;
*************************** 1. row ***************************
       Table: stu_info
Create Table: CREATE TABLE `stu_info` (
  `姓名` char(10) DEFAULT NULL,
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)


mysql> create table stu like stu_info; # 复制表结构
Query OK, 0 rows affected (0.02 sec)

mysql> show create table stu\G;
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `姓名` char(10) DEFAULT NULL,
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)


mysql> desc stu_info;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名  | char(10) | YES  |     | NULL    |       |
| id    | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> desc stu;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| 姓名  | char(10) | YES  |     | NULL    |       |
| id    | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

方法2

在create table语句的末尾添加一个select语法，不仅可以实现表结构的复制，还可以将原表中的数据拷贝到新表中。
语法：

mysql> create table 新表名 select * from 原表名;
1

复制表数据

方法：如果已经存在一张结构一致的表，复制数据
语法：

mysql> insert into 表名 select * from 原表名;
1

表数据字典

由information_schema数据库负责维护：

tables－存放数据库里所有的数据表、以及每个表所在数据库。
schemata－存放数据库里所有的数据库信息
views－存放数据库里所有的视图信息。
columns－存放数据库里所有的列信息。
triggers－存放数据库里所有的触发器。
routines－存放数据库里所有存储过程和函数。
key_column_usage－存放数据库所有的主外键
table_constraints－存放各个表的约束。
statistics－存放了数据表的索引。
1
2
3
4
5
6
7
8
9

表的约束条件

• 约束是在表上强制执行的数据校验规则。约束主要用于保证数据库的完整性。当表中数据有相互依赖性时，可以保护相关的数据不被删除。
• 可以在创建表时规定约束（通过create table语句），或者在表创建之后通过alter table语句规定约束。
• 根据约束数据列的限制，约束可分为：
  • 单列约束：每个约束只约束一列。
  • 多列约束：每个约束可约束多列数据。
• 根据约束的作用范围，约束可分为：
  • 列级约束：只能作用在一个列上，跟在列的定义后面，语法：列定义 约束类型
  • 表级约束：可以作用在多个列上，不与列一起，而是单独定义
  • 表级约束类型有4种：主键，外键，唯一，检查

mysql> [constraint 约束名] 约束类型   
约束名的取名规则：表名_列名_约束类型    (推荐)
1
2

mysql> alter table 表名 add constraint 约束名 约束类型;
1

• 根据约束起的作用，约束可以分为以下6种约束类型：
  • not null：非空约束，规定某个字段不能为空
  • unique：唯一约束，规定某个字段在整个表中是唯一的
  • primary key：主键约束，非空且唯一
  • foreign key：外键约束
  • check：检查约束
  • default：默认值约束

创建约束的时机

1.在建表的同时创建

列级约束

只能作用在一个列上，跟在列的定义后面，语法：列定义 约束类型

mysql> create table stu(
    -> id int primary key auto_increment comment '学号',
    -> name varchar(20) not null comment '姓名',
    -> age tinyint(3) check(age>=18) comment '年龄不小于18',
    -> gender char(1) default 'M' check(gender in ('M','F')) comment '性别',
    -> address varchar(50) unique
    -> )auto_increment=1001;
Query OK, 0 rows affected, 1 warning (0.06 sec)


mysql> show create table stu\G;
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `age` tinyint DEFAULT NULL COMMENT '年龄不小于18',
  `gender` char(1) DEFAULT 'M' COMMENT '性别',
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `address` (`address`),
  CONSTRAINT `stu_chk_1` CHECK ((`age` >= 18)),
  CONSTRAINT `stu_chk_2` CHECK ((`gender` in (_gbk'M',_gbk'F')))
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.03 sec)


mysql> desc stu;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | NO   |     | NULL    |                |
| age     | tinyint     | YES  |     | NULL    |                |
| gender  | char(1)     | YES  |     | M       |                |
| address | varchar(50) | YES  | UNI | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

总结：

• 当使用列级约束进行check约束时，系统会自动起一个check约束名
• 一个表中primary key和auto_increment只能有一个
• 每一行可以定义多个约束

表级约束

可以作用在多个列上，不与列一起，而是单独定义

• 约束名的取名规则，推荐采用：表名_列名_约束类型，也可以随便起
• 表级约束类型有四种：主键、外键、唯一、检查
• 当出现外键约束时，最好使用表级约束

mysql> create table class(
    -> class_id int primary key comment '班号'
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table stu(
    -> id int auto_increment comment '学号',
    -> name varchar(20) not null comment '姓名',
    -> age tinyint(3) not null comment '年龄不小于18',
    -> gender char(1) default 'M' not null comment '性别',
    -> address varchar(50) not null comment '住址',
    -> classid int not null comment '班号',
    -> constraint stu_id_primary primary key(id),  # 主键约束
    -> constraint stu_age_check check(age>=18),  # 检查约束
    -> constraint stu_gender_check check(gender in ('M','F')),  # 检查约束
    -> constraint stu_address_unique unique(address), # 唯一约束
    -> constraint stu_class_foreign foreign key(classid) references class(class_id)  # 外键约束
    -> )auto_increment=1001;
Query OK, 0 rows affected, 1 warning (0.06 sec)


mysql> show create table stu\G;
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(20) NOT NULL COMMENT '姓名',
  `age` tinyint NOT NULL COMMENT '年龄不小于18',
  `gender` char(1) NOT NULL DEFAULT 'M' COMMENT '性别',
  `address` varchar(50) NOT NULL COMMENT '住址',
  `classid` int NOT NULL COMMENT '班号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stu_address_unique` (`address`),
  KEY `stu_class_foreign` (`classid`),
  CONSTRAINT `stu_class_foreign` FOREIGN KEY (`classid`) REFERENCES `class` (`class_id`),
  CONSTRAINT `stu_age_check` CHECK ((`age` >= 18)),
  CONSTRAINT `stu_gender_check` CHECK ((`gender` in (_gbk'M',_gbk'F')))
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

总结

• class表是主表，stu表是子表
• constraint所在行可以有主键，外键，唯一，检查
• 普通行可以有非空，默认值，自动增加
• 写外键约束之前，必须存在主表
• 外键参照的只能是主表的主键约束或唯一约束
• 当主表的记录被子表参照时，主表数据不允许被删除。
• 外键是构建于一个表的两个字段或者两个表的两个字段（两个表中各一个） 之间的关系，外键确保了相关的两个字段的两个关系。
• 子表（从表）外键参照的的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。
• 外键约束必须单独定义，不能在普通行中定义。

注：
1.列级约束和表级约束可以联合写（最推荐的写法）
2.当使用列级约束进行check约束时，系统会自动起一个check约束名
3.一个表中primary key和auto_increment只能有一个

2.在建表之后创建

mysql> create table class(
    -> class_id int primary key comment '班号'
    -> );
Query OK, 0 rows affected (0.05 sec)


mysql> create table stu(
    -> id int comment '学号',
    -> name varchar(30),
    -> age tinyint(3),
    -> gender char(1),
    -> address varchar(50),
    -> classid int
    -> );
Query OK, 0 rows affected, 1 warning (0.04 sec)


mysql> alter table stu add constraint stu_id_primary primary key(id);
mysql> alter table stu add constraint stu_age_check check(age>=18);
mysql> alter table stu add constraint stu_gender_check check(gender in ('M','F'));
mysql> alter table stu add constraint stu_address_unique unique(address);
mysql> alter table stu add constraint stu_classid_foreign foreign key(classid) references class(class_id);
mysql> alter table stu modify name varchar(30) not null;
mysql> alter table stu modify gender char(1) default 'M';
mysql> alter table stu modify id tinyint not null auto_increment;


mysql> show create table stu\G;
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `age` tinyint DEFAULT NULL,
  `gender` char(1) DEFAULT 'M',
  `address` varchar(50) DEFAULT NULL,
  `classid` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stu_address_unique` (`address`),
  KEY `stu_classid_foreign` (`classid`),
  CONSTRAINT `stu_classid_foreign` FOREIGN KEY (`classid`) REFERENCES `class` (`class_id`),
  CONSTRAINT `stu_age_check` CHECK ((`age` >= 18)),
  CONSTRAINT `stu_gender_check` CHECK ((`gender` in (_utf8mb4'M',_utf8mb4'F')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

总结

• 表级约束使用add
• 普通行约束使用modify

外键约束

总结

• class表是主表，stu表是子表
• constraint所在行可以有主键，外键，唯一，检查
• 普通行可以有非空，默认值，自动增加
• 写外键约束之前，必须存在主表
• 外键参照的只能是主表的主键约束或唯一约束
• 当主表的记录被子表参照时，主表数据不允许被删除。
• 外键是构建于一个表的两个字段或者两个表的两个字段（两个表中各一个） 之间的关系，外键确保了相关的两个字段的两个关系。
• 子表（从表）外键参照的的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。
• 外键约束必须在constraint行中单独定义，不能在普通行中定义。
• 有外键约束时，当子表中存在主表的数据值，该主表中的数据值不能被删除；当主表中某数据值不在子表中时，主表中该数据值可以被删除

主键约束

• 主键从功能上看相当于非空且唯一，一个表中只允许一个主键，主键是表中唯一确定一行数据的字段，主键字段可以是单字段或者是多字段的组合。
• 当建立主键约束时，MySQL为主键创建对应的索引。

检查约束

• 检查约束在8.0之前，MySQL默认但不会强制的遵循check约束（写不报错，但是不生效，需要通过触发器完成），8之后就开始正式支持这个约束了。

唯一约束

• 唯一约束条件确保所在的字段或者字段组合不出现重复值。
• 唯一性约束条件的字段允许出现多个NULL。
• 同一张表内可建多个唯一约束。
• 唯一约束可由多列组合而成。
• 建唯一约束时MySQL会为之建立对应的索引。
• 如果不给唯一约束起名，该唯一约束默认与列名相同。

自动增长auto_increment

• 自动增长,为新的行产生唯一的标识，一个表只能有一个auto_increment，且该属性必须为主键的一部分。
• auto_increment的属性可以是任何整数类型。（自动排序）
• 允许插入null，会自动添加值

非空约束

• 列级约束，只能使用列级约束语法定义。确保字段值不允许为空。

默认值约束

• 可以使用default关键字设置每一个字段的默认值。

总结：只要有唯一性的存在，MySQL都会为之建立对应的索引。

删除约束

mysql> alter table stu drop foreign key 外键约束的约束名;（删除外键约束）
mysql> alter table stu drop primary key [主键约束的约束名];（删除主键约束）
mysql> alter table stu drop index 唯一约束的约束名;(删除唯一约束)
mysql> alter table stu drop check 检查约束的约束名;(删除检查约束)
mysql> alter table stu modify id tinyint;（删除default和not null和auto_increment）
1
2
3
4
5

总结

• 表级约束的删除和它们自己的约束名有关
• 普通行约束的删除直接使用modify置空即可

完整的建表操作

create table 表名 (
	列名 列类型 [primary key AUTO_INCREMENT] [默认值] [列级约束]
	...
	[表级约束]
) [ENGINE=存储引擎类型] [auto_increment=初始值] [DEFAULT] [CHARSET=字符集] [collate=校对规则] [row_format=行格式];
1
2
3
4
5

表物理存储结构

此部分内容，等待博主下次揭晓！！！