DBA MySQL表相关操作
表的基本组成
每一张数据表可以粗略的理解为一个表格文件，在数据表中又分为表结构、表记录、表元信息:
表结构：字段、字段约束等信息
表记录：数据表中的每一行数据（不包含字段行）等信息
元信息：字符编码、校对规则、存储引擎等信息
| id | name | gender | age |
|---|---|---|---|
| 1 | YunYa | male | 18 |
| 2 | Jack | male | 17 |
| 3 | Baby | female | 16 |
数据表操作
创建数据表
语法介绍：
CREATE TABLE 表名(
字段名1 类型(宽度) 约束条件1，约束条件2... COMMENT "字段描述信息",
字段名2 类型(宽度) 约束条件1，约束条件2... COMMENT "字段描述信息"
) ENGINE 存储引擎 CHARSET 字符编码 COLLATE 校对规则;
注意事项：
- 在同一张表中，字段名是不能相同
- 宽度和约束条件可选
- 字段名和类型是必须的
- 表中最后一个字段不要加逗号
示例演示：
# 1. 必须进入数据库后才能建表
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "学号",
name CHAR(32) NOT NULL COMMENT "姓名",
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT "年龄",
gender ENUM ("male", "female", "unknown") NOT NULL DEFAULT "male" COMMENT "性别",
create_time DATETIME NOT NULL DEFAULT NOW() COMMENT "创建时间"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
# 2.不进入数据库建表
# 在第一句基础上加上库名，示例：CREATE TABLE db1.students(...);
# 此外，在下面介绍的命令中很多命令都有这两种形式，不再进行一一例举
建表规范：
- 表名为小写字母，不能使用数字开头
- 表名以及字段名不能是保留字符，请使用和业务有关的表名
- 为字段选择合适的数据类型及长度
- 尽量为每个字段设置 NOT NULL + DEFAULT ，对于数字类型请使用0进行填充，按照具体情况酌情应用
- 外键字段不设置NOT NULL，对于字符类的特殊字段如name等不设置DEFAULT，按照具体情况酌情应用
- 为每个字段设置描述信息
- 表必须设置存储引擎与字符集，可以不设置校对规则
- 主键字段尽量是无关列数字列，最好是自增长
- enum类型不要保存数字，只能是字符串类型
查看数据表
三个基本命令，格式及描述信息如下：
# 必须进入某个数据库后方可使用，查看该库下所有数据表
SHOW TABLES;
# 查看表的创建信息，能够获取到表结构及元信息
SHOW CREATE TABLE 表名;
# 查看表结构，仅能够获取到表结构
DESC 表名;
示例演示：
M > SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| students |
+---------------+
M > SHOW CREATE TABLE students\G;
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` char(32) NOT NULL COMMENT '姓名',
`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`gender` enum('male','female','unknown') NOT NULL DEFAULT 'male' COMMENT '性别',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
M > DESC students;
+-------------+---------------------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('male','female','unknown') | NO | | male | |
| create_time | datetime | NO | | CURRENT_TIMESTAMP | |
+-------------+---------------------------------+------+-----+-------------------+----------------+
修改表名字
使用以下命令格式修改表名：
ALTER TABLE 旧表名 RENAME 新表名;
示例演示：
M > ALTER TABLE students RENAME new_name_stu;
M > SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| new_name_stu |
+---------------+
M > ALTER TABLE new_name_stu RENAME students;
M > SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| students |
+---------------+
清空数据表
使用以下命令格式可将表中所有记录清空，并且对部分结构进行重置，如自增字段会恢复至初始值：
TRUNCATE 表名;
示例演示，注意观察自增id，又是从1开始的：
M > INSERT INTO
students(name, age, gender)
VALUES
("Jack", 19, "male"),
("Ken", 22, "male"),
("Angel", 21, "female");
M > SELECT * FROM students;
+----+-------+-----+--------+---------------------+
| id | name | age | gender | create_time |
+----+-------+-----+--------+---------------------+
| 1 | Jack | 19 | male | 2021-02-24 23:45:16 |
| 2 | Ken | 22 | male | 2021-02-24 23:45:16 |
| 3 | Angel | 21 | female | 2021-02-24 23:45:16 |
+----+-------+-----+--------+---------------------+
M > TRUNCATE students;
M > INSERT INTO
students(name, age, gender)
VALUES
("Tom", 21, "male");
M > SELECT * FROM students;
+----+------+-----+--------+---------------------+
| id | name | age | gender | create_time |
+----+------+-----+--------+---------------------+
| 1 | Tom | 21 | male | 2021-02-24 23:46:12 |
+----+------+-----+--------+---------------------+
删除数据表
使用以下命令格式删除某张数据表：
DROP TABLE 表名；
不再进行演示，生产中较少使用。
复制旧表创建新表
结构复制创建
使用以下命令格式只复制表的结构而不复制记录，用于创建新的一张表：
CREATE TABLE 新表名 LIKE 旧表名;
基础复制创建
使用以下命令格式可以复制基础表结构与所有记录，但不会复制主键、外键、索引，用于创建新的一张表：
CREATE TABLE 新表名 SELECT * FROM 旧表名;
选择复制创建
使用以下命令格式可以对一张表的某些记录与字段进行复制，用于创建新的一张表：
CREATE TABLE 新表名 SELECT 字段1,字段2 FROM 旧表名;
表字段操作
新增字段
新增字段的三种语法格式：
# 新增多字段
ALTER TABLE 表名
ADD 新字段名1 数据类型 约束条件1，约束条件2...,
ADD 新字段名2 数据类型 约束条件1，约束条件2...;
# 新增单字段，排在最前
ALTER TABLE 表名
ADD 新字段名 数据类型 约束条件1，约束条件2... FIRST;
# 新增单字段，排在某字段后
ALTER TABLE 表名
ADD 新字段名 数据类型 约束条件1，约束条件2... AFTER 旧字段名;
示例演示：
M > ALTER TABLE students
ADD classes CHAR(32) NOT NULL AFTER create_time;
修改字段
修改字段的两种语法格式：
# MODIFY仅能修改数据类型与完整性约束条件
ALTER TABLE 表名
MODIFY 被修改的字段名 数据类型 约束条件1，约束条件2...;
# CHANGE更加强大，相比于MODIFY还支持修改字段名
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 数据类型 约束条件1，约束条件2...;
示例演示：
M > ALTER TABLE students
CHANGE classes class VARCHAR(32) NOT NULL;
如果不修改名字只修改其原本的类型或完整性约束条件，可使用MODIFY进行操作。
删除字段
使用以下命令格式删除表的某一字段：
ALTER TABLE 表名 DROP 字段名;
不再进行演示，生产中较少使用。
表元信息操作
存储引擎
使用以下命令格式修改表的存储引擎：
ALTER TABLE 表名 ENGINE = 存储引擎;
字符集
使用以下命令格式修改表的字符集：
ALTER TABLE 表名 CHARSET = 字符集;
校对规则
使用以下命令格式修改表的校对规则：
ALTER TABLE 表名 COLLATE = 校对规则;
