DBA | MySQL 数据库与表字段操作管理指南

大家好，我是?WeiyiGeek，一名深耕安全运维开发（SecOpsDev）领域的技术从业者，致力于探索DevOps与安全的融合（DevSecOps），自动化运维工具开发与实践，企业网络安全防护，欢迎各位道友一起学习交流、一起进步 ，若此文对你有帮助，一定记得倒点个关注?与小红星??，收藏学习不迷路??。

前言简述

描述：上一篇文章，我们对 MySQL 数据库基础学习环境搭建，以及常用连接管理客户端进行了简单介绍，想必大家都已经实践部署了吧。

此外，在前面回顾 SQL 时已将数据库操作、查询进行了简单介绍以及分类。本章将学习实践 DDL 数据定义语言，包含 MySQL 8.x 数据库的操作（查看、创建、删除）、表、字段的操作（创建、查看、更新、删除）介绍与实践。

原文链接:?https://articles.zsxq.com/id_qo2jc7wkzvdp.html

不过在学习之前，我们先了解（复习）一下 SQL 的一些基础通用语法：

1.SQL 语句关键字不区分大小写，如?SELECT?与?select?使用时效果相同，但是数据库表名、列名和值是否区分，依赖于具体的 DBMS 以及配置, MySQL、MariaDB 数据库是区分的数据库名与表名大小写的。

2.SQL 语句（多行）必须以分号（;）分隔。

3.SQL 语句中关键字、表达式左右需以空格分隔，此外处理 SQL 语句时，所有空格都被忽略，即 SQL 语句可以写成一行，也可以分写为多行。

4.SQL 语句中，字符串数据需要用引号（单引号或双引号）括起来，不过为了兼容性建议搭建使用单引号。

5.SQL 语句中，注释有?--?和?/* */?两种方式，或者 MySQL 特有的?#?符号注释。

-- 注释：一行 SQL 语句
UPDATEuserSET?username='weiyigeek',?password='weiyigeek.top'WHERE?username =?'weiyigeek';

/*?
? 注释：?
? 多行 SQL 语句
*/
UPDATEuser
SET?username='weiyigeek',?password='weiyigeek.top'
WHERE?username =?'weiyigeek';

-- 注释：中间 SQL 语句，不影响执行。
selectid,?name,?password
fromuser
where
/*
? username like '%weiyigeek%'
AND?
*/
? country =?"CN";

DDL 数据定义语言

1.数据库操作

创建数据库

-- # 语法
CREATEDATABASE?[IFNOTEXISTS] 数据库名 [DEFAULTCHARSET?字符集] [COLLATE?排序规则] [ENCRYPTION 禁用启用数据库加密];

-- # 示例
-- 1.创建一个名为test的数据库,其它全部缺省。
mysql>?CREATEDATABASEtest;
Query OK, 1 row affected (0.12 sec)

mysql>?CREATEDATABASEtest; ??-- 若数据库存在，再次创建会报错
ERROR 1007 (HY000): Cant?createdatabase'test'; database exists

-- 2.创建一个名为test01的数据库，若存在则不创建, 不存在则创建,指定字符集为utf8mb4，排序规则为utf8mb4_general_ci。
mysql>?CREATEDATABASEIFNOTEXISTS?test01?DEFAULTCHARSET?utf8mb4?COLLATE?utf8mb4_general_ci;
Query OK, 1 row affected (0.18 sec)

温馨提示：若不指定字符集，MySQL 5.7 版本默认创建的数据库字符集为 latin1，而非 utf8mb4，其次MySQL 8.x校对规则为 utf8mb4_0900_ai_ci 排序长度为 0 ，填充规则为 No PAD。

切换数据库

-- # 语法
USE?数据库名;

-- # 示例
-- 切换到test数据库
USE?test;

查询数据库

-- # 查询当前数据库
SELECTDATABASE();
| DATABASE() |
+------------+
| test ? ? ? |

-- # 查询数据库创建语句
SHOWCREATEDATABASE'数据库名字'；
SHOWCREATEDATABASEtest;
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database |?CreateDatabase? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
|?test? ? ?|?CREATEDATABASE`test`/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci *//*!80016 DEFAULT ENCRYPTION='N' */?|
+----------+--------------------------------------------------------------------------------------------------------------------------------+
1rowinset?(0.00?sec)

mysql>?SHOWCREATEDATABASE?test01;
| Database |?CreateDatabase? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| test01 ? |?CREATEDATABASE`test01`/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci *//*!80016 DEFAULT ENCRYPTION='N' */?|

-- MySQL 5.7 版本，缺省创建的数据库字符集为 latin1，而非 utf8mb4，这一点非常需要注意。
|?Database?|?CreateDatabase? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|
+----------+-----------------------------------------------------------------+
|?test? ? ?|?CREATEDATABASE`test`/*!40100 DEFAULT CHARACTER SET latin1 */?|

-- # 查询所有数据库
SHOWDATABASES;
| Database ? ? ? ? ? |
+--------------------+
| information_schema |
| mysql ? ? ? ? ? ? ?|
| performance_schema |
| sys ? ? ? ? ? ? ? ?|
| test ? ? ? ? ? ? ? |
| test01 ? ? ? ? ? ? |
6 rows in?set?(0.00?sec)

知识扩展：MySQL 数据库安装后，默认创建的数据库如下：

information_schema：MySQL 数据字典，存储了所有其他数据库的信息，比如数据库名、有哪些表、哪些字段及类型，索引信息，访问权限等，其中表都是视图的形式，不能直接修改。

mysql：MySQL 核心数据库，是服务器运行时所需的一些系统表，比如用户权限表、授权表、及相关数据库配置表等，其类比于 MsSQL 中的 master 表。

performance_schema：MySQL 性能数据库，主要用于收集数据库服务器性能参数，如资源消耗、表级锁定、表I/O等情况。

sys: MySQL 系统视图，是基于 performance_schema 提供的一些更方便的查询接口，主要是将复杂度降低，让DBA可快速了解DB运行情况。

删除数据库

-- # 语法
DROPDATABASE?[IFEXISTS] 数据库名;

-- # 示例
-- 删除数据库 test01
mysql>?DROPDATABASE?test01;
Query OK, 0 rows affected (0.19 sec)

mysql>?DROPDATABASE?test01;
ERROR 1008 (HY000): Can't?dropdatabase'test01'; database doesn't exist

-- 若想删除存在的数据库，可使用 IF EXISTS 关键字。
mysql>?DROPDATABASEIFEXISTS?test01;
Query OK, 0 rows affected, 1 warning (0.05 sec)

至此，数据库操作就介绍到这里，接下来我们继续学习表和字段的操作。

2.表操作

创建表

表创建语句

-- # 语法
CREATETABLE?[IFNOTEXISTS] 表名 (
? 字段1?数据类型(长度) 约束 [COMMENT?字段1注释],
? 字段2?数据类型(长度) 约束 [COMMENT?字段2注释],
?...
? 字段N 数据类型(长度) 约束 [COMMENT?字段N注释] ? ?-- 注意：最后一个字段后面不加逗号
)?ENGINE=数据库引擎 AUTO_INCREMENT=自增序号?DEFAULTCHARSET=字符集?COLLATE=排序规则 [COMMENT?表注释] ;

-- # 示例
-- 1.创建一个名为 oa_user 的表，包含字段 id、username、password、department、start_date、end_date，并为字段添加注释。
USEtest; ?-- 切换到test数据库
CREATETABLEIFNOTEXISTS?oa_user (
idINT(11) AUTO_INCREMENT PRIMARY?KEYCOMMENT'用户id',?-- 主键自增，从1开始
? username?VARCHAR(255)?NOTNULLCOMMENT'用户名', ? ?-- 用户名，不可为空
passwordVARCHAR(255)?NOTNULLCOMMENT'用户密码', ? ?-- 密码，不可为空
? department?VARCHAR(255)?NOTNULLCOMMENT'部门', ? ? ??-- 部门，不可为空
? start_date?DATENOTNULLNOTNULLCOMMENT'入职时间', ? ?-- 开始日期，不可为空
? end_date?DATEdefaultNULLCOMMENT'离职时间', ? ? ? ? ? ??-- 结束日期，不可为空
Index?idx_username (username) ?-- 索引，基于用户名创建
)?ENGINE=InnoDB?AUTO_INCREMENT=1DEFAULTCHARSET=utf8mb4?COLLATE=utf8mb4_general_ci?COMMENT='用户表';

温馨提示：默认情况下，我们是不需要指定表的存储引擎，因为我们在创建数据库时，就已经指定了默认的存储引擎。如果要显式地指定存储引擎，可以使用 ENGINE=InnoDB 或者 ENGINE=MyISAM 等关键字。

温馨提示：默认情况下，MySQL 对标的数量是没有限制的，但是文件系统对文件数量是有所限制的。

查询表

查询数据库中表

SHOW tables;
+----------------+
| Tables_in_test |
+----------------+
| oa_user ? ? ? ?|
+----------------+
1 row?in?set?(0.00 sec)

查询表结构

-- # 语法
DESC 表名;
mysql> DESC oa_user;
+------------+--------------+------+-----+---------+----------------+
| Field ? ? ?| Type ? ? ? ? | Null | Key | Default | Extra ? ? ? ? ?|
+------------+--------------+------+-----+---------+----------------+
| id ? ? ? ? | int ? ? ? ? ?| NO ? | PRI | NULL ? ?| auto_increment |
| username ? | varchar(255) | NO ? | ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|
| password ? | varchar(255) | NO ? | ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|
| department | varchar(255) | NO ? | ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|
| start_date | date ? ? ? ? | NO ? | ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|
| end_date ? | date ? ? ? ? | YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|
+------------+--------------+------+-----+---------+----------------+
6 rows in?set?(0.01?sec)

温馨提示：使用 DESC 关键字可以查询表结构，但是并不能查看到表、字段的备注、数据库表引擎、以及字符集排序规则等，这就需要下面的语句方式查询。

查询建表语句

-- # 语法
SHOWCREATETABLE?表名;
mysql>?SHOWCREATETABLE?oa_user;
+---------+--------------+
| Table ? |?CreateTable? |
+---------+--------------+
| oa_user |?CREATETABLE`oa_user`?(
`id`intNOTNULL?AUTO_INCREMENT?COMMENT'用户id',
`username`varchar(255)?COLLATE?utf8mb4_general_ci?NOTNULLCOMMENT'用户名',
`password`varchar(255)?COLLATE?utf8mb4_general_ci?NOTNULLCOMMENT'用户密码',
`department`varchar(255)?COLLATE?utf8mb4_general_ci?NOTNULLCOMMENT'部门',
`start_date`dateNOTNULLCOMMENT'入职时间',
`end_date`dateDEFAULTNULLCOMMENT'离职时间',
? PRIMARY?KEY?(`id`)
KEY`idx_username`?(`username`)
)?ENGINE=InnoDBDEFAULTCHARSET=utf8mb4?COLLATE=utf8mb4_general_ci?COMMENT='用户表'?|

复制表

描述：复制表结构但不复制数据，若有复制数据的需求可使用?INSERT INTO 新表 SELECT * FROM 旧表;?的方式复制数据。

-- # 语法
CREATETABLE?新表名?LIKE?旧表名;

-- # 示例
-- 复制 oa_user 表结构到 new_oa_user 表中。
mysql>?CREATETABLE?new_oa_user?LIKE?oa_user;
Query OK, 0 rows affected (0.05 sec)

mysql>?SHOWtables;
+----------------+
| Tables_in_test |
+----------------+
| new_oa_user ? ?|
| oa_user ? ? ? ?|
+----------------+
2 rows in?set?(0.00?sec)

温馨提示：此语句是 MySQL 特有的语法，并不是 SQL 标准语法。

修改表

更新表名

-- # 语法
ALTERTABLE?表名?RENAMETO?新表名;

-- # 示例
-- 将 new_oa_user 表名修改为 users。
ALTERTABLE?new_oa_user?RENAMETOusers;
mysql>?showtables;
| Tables_in_test ? ?|
+-------------------+
| oa_user ? ? ? ? ? |
| users ? ? ? ? ? ? |

添加字段

-- # 语法
ALTERTABLE?表名?ADD?字段名 数据类型(长度) [约束] [COMMENT?注释] [AFTER?字段名];?-- 缺省将字段添加到表的后面,也可使用 AFTER 指定字段名。
ALTERTABLE?表名?ADD?字段名 数据类型(长度) [约束] [COMMENT?注释] ?FIRST; ?-- 将字段添加到表的最前面。

-- # 示例
-- 在 oa_user 表中添加一个字段 nickname，数据类型为 varchar(20)，位于 password 后面。
ALTERTABLE?oa_user?ADD?nickname?VARCHAR(20)?COMMENT'昵称'FIRSTAFTERpassword;
mysql> DESC oa_user;?-- 查看表结构，发现新增的字段已经出现在了表中
| Field ? ? ?| Type ? ? ? ? | Null | Key | Default | Extra ? ? ? ? ?|
+------------+--------------+------+-----+---------+----------------+
| id ? ? ? ? | int ? ? ? ? ?| NO ? | PRI | NULL ? ?| auto_increment |
| username ? | varchar(255) | NO ? | MUL | NULL ? ?| ? ? ? ? ? ? ? ?|
| password ? | varchar(255) | NO ? | ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|
| nickname ? | varchar(20) ?| YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|?-- 新增字段
| department | varchar(255) | NO ? | ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|
| start_date | date ? ? ? ? | NO ? | ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|
| end_date ? | date ? ? ? ? | YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|

修改字段

-- # 语法
-- 修改字段数据类型、长度、注释等
ALTERTABLE?表名?MODIFY?字段名 新数据类型(长度) [约束] [COMMENT?注释];

-- 修改字段名称及字段数据类型、长度等
ALTERTABLE?表名?CHANGE?旧字段名 新字段名 新数据类型(长度) ?[约束] [COMMENT?注释];

-- # 示例
-- 修改 oa_user 表中 nickname 字段的数据类型为 varchar(30)，注释改为 '用户别名'。
ALTERTABLE?oa_user?MODIFY?nickname?VARCHAR(30)?COMMENT'用户别名';
-- 查看指定字段的详细信息，会发现 nickname 字段的数据类型和注释已经发生了变化。
mysql>?SHOWFULLCOLUMNSFROM?oa_user?WHEREField?=?'nickname';
| Field ? ?| Type ? ? ? ?| Collation ? ? ? ? ?| Null | Key | Default | Extra | Privileges ? ? ? ? ? ? ? ? ? ? ?|?Comment? ? ? |
+----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+--------------+
| nickname |?varchar(30) | utf8mb4_general_ci | YES ?| ? ? |?NULL? ? | ? ? ? |?select,insert,update,references?| 用户别名 ? ? |

-- 修改 oa_user 表中 nickname 字段的名称改为 realname，数据类型为 varchar(10)，注释为 '真实姓名'。
ALTERTABLE?oa_user?CHANGE?nickname realname?VARCHAR(10)?COMMENT'真实姓名';
mysql>?SHOWFULLCOLUMNSFROM?oa_user?WHEREField?=?'realname';
| Field ? ?| Type ? ? ? ?| Collation ? ? ? ? ?| Null | Key | Default | Extra | Privileges ? ? ? ? ? ? ? ? ? ? ?|?Comment? ? ? |
+----------+-------------+--------------------+------+-----+---------+-------+---------------------------------+--------------+
| realname |?varchar(10) | utf8mb4_general_ci | YES ?| ? ? |?NULL? ? | ? ? ? |?select,insert,update,references?| 真实姓名 ? ? |

删除字段

-- # 语法
ALTER?TABLE?表名?DROP?字段名;

-- # 示例
-- 删除 oa_user 表中 realname 字段。
alter?table?oa_user?drop?realname;
Query OK, 0 rows affected (1.64 sec)
Records: 0 ?Duplicates: 0 ?Warnings: 0
mysql> DESC oa_user; ?-- 查看表结构，会发现 realname 字段已经从表中删除。

清空表

-- # 语法
TRUNCATE?TABLE?表名;

-- # 示例
-- 清空 oa_user 表中的所有数据，包括初始化自增值，类似与删除表又重新创建表。
TRUNCATE?TABLE?oa_user;

温馨提示：TRUNCATE 与 DELETE 在显示效果上类似，但底层实现不同。TRUNCATE 操作会重置自增值，而 DELETE 不会，并且 DELETE 操作可以可以在开启 BINLOGS 后回滚，而 TRUNCATE 不支持，正是因为如此 TRUNCATE 操作效率更高。

删除表

-- # 语法
DROP?TABLE?[IF?EXISTS] 表名;

-- # 示例
mysql>?DROP?TABLE?IF?EXISTS?new_oa_user;
Query OK, 0 rows affected (0.19 sec)

温馨提示：实际上在 MySQL DLL 数据定义语言操作中，还包含了索引、视图、约束等操作，为了大家在学习中不一子懵逼，作者将在 MySQL 进阶学习中再一一讲解。

知识总结

数据库操作相关的 DDL 语句主要包括：创建数据库、删除数据库、修改数据库字符集和校对规则等。

SHOW?DATABASES;
CREATE?DATABASE?IF?NOT?EXISTS?数据库名?CHARACTER?SET?utf8mb4?COLLATE?utf8mb4_general_ci;
USE?数据库名;
SELECT?DATABASE();
DROP?DATABASE?IF?EXISTS?数据库名;

数据库表操作相关的 DDL 语句主要包括：创建表、删除表、修改表结构、重命名等。

SHOW?TABLES;
CREATETABLEIFNOTEXISTS?表名 (字段名 类型(长度) [约束]?COMMENT'注释']...;)
DESC 表名;
SHOWCREATETABLE?表名;
ALTERTABLE?表名?RENAMETO?新表名;
ALTERTABLE?表名?ADD?字段名 类型(长度) [约束] [COMMENT'注释'] ;
ALTERTABLE?表名?MODIFY?字段名 新数据类型(长度) [约束] [COMMENT'注释'];
ALTERTABLE?表名?CHANGE?旧字段名 新字段名 新数据类型(长度) [约束] [COMMENT'注释'];
ALTERTABLE?表名?DROP?字段名;
TRUNCATETABLE?表名;
DROPTABLEIFEXISTS?表名;

参考来源:

MySQL 数据定义语言文档:?https://dev.mysql.com/doc/refman/8.4/en/sql-data-definition-statements.html

至此，我们已经掌握了 MySQL 中数据定义语言的基本操作。希望这些内容能够帮助你更好地理解和使用 MySQL 的 DDL 语句。

加入：作者【全栈工程师修炼指南】知识星球

『?全栈工程师修炼指南』星球，主要涉及全栈工程师（Full Stack Development）实践文章，包括但不限于企业SecDevOps和网络安全等保合规、安全渗透测试、编程开发、云原生（Cloud Native）、物联网工业控制（IOT）、人工智能Ai，从业书籍笔记，人生职场认识等方面资料或文章。