【掌握 DDL】：SQL 中的数据库与表管理

掌握 DDL：SQL 中的数据库与表管理

数据定义语言（DDL） 是 SQL 的一个子集，用于定义和管理数据库结构，包括数据库和表。本文详细介绍了用于创建、查看、修改和删除数据库及表的基本 DDL 命令，附带实用示例和最佳实践。

---

1. 数据库 DDL
   • 创建数据库
   • 查看数据库
   • 进入数据库
   • 删除数据库
   • 备份数据库
   • 查看数据库连接
   • 深入理解数据库创建与删除
   • 数据库字符集与校验规则
2. 表 DDL
   • 创建表
   • 查看表详情
   • 修改表
   • 删除表

---

数据库 DDL

创建数据库

使用 CREATE DATABASE 命令创建数据库：

CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset] [COLLATE collation];

• db_name：数据库名称。
• [IF NOT EXISTS]：可选，防止数据库已存在时出错。
• CHARACTER SET：指定字符编码（如 utf8）。
• COLLATE：定义字符串比较的校验规则（如 utf8_general_ci）。

示例：创建一个名为 db1 的数据库，字符集为 utf8，校验规则为 utf8_general_ci：

CREATE DATABASE IF NOT EXISTS db1 CHARACTER SET utf8 COLLATE utf8_general_ci;

查看数据库

查看所有数据库

SHOW DATABASES;

查看数据库创建语句

SHOW CREATE DATABASE db_name [\G];

• \G：MySQL 客户端选项，以垂直格式显示结果，便于阅读复杂输出。

进入数据库

操作表之前，需进入目标数据库：

USE db_name;

删除数据库

DROP DATABASE [IF EXISTS] db_name;

• [IF EXISTS]：可选，防止删除不存在的数据库时出错。

备份数据库

备份

在终端执行以下命令备份数据库：

mysqldump -P3306 -u root -p密码 -B db_name > backup_file_path

• -P3306：指定 MySQL 端口。
• -u root：用户名。
• -p密码：密码（无空格）。
• -B：备份整个数据库。
• >：将备份内容重定向到指定文件。

示例：备份 db1 到 /backup/db1.sql：

mysqldump -P3306 -u root -p123456 -B db1 > /backup/db1.sql

注意：

• 不指定文件路径，备份内容仅显示在屏幕上。
• 可备份特定表：mysqldump -P3306 -u root -p密码 db_name table1 table2 > backup_file_path
• 可备份多个数据库：mysqldump -P3306 -u root -p密码 -B db_name1 db_name2 > backup_file_path

备份的本质就是保存历史的sql命令：

恢复

在 MySQL 中执行以下命令恢复数据库：

SOURCE backup_file_path;

示例：恢复 /backup/db1.sql：

SOURCE /backup/db1.sql;

查看数据库连接

SHOW PROCESSLIST;

显示当前连接到 MySQL 服务器的所有线程（会话）状态。每个客户端连接对应一个线程。如果发现异常用户连接，可能表示数据库被入侵，需立即停止 MySQL 服务（mysqld）。

深入理解数据库创建与删除

1. 创建数据库：在 /var/lib/mysql 目录下创建以 db_name 命名的目录。
   

2. 删除数据库：从 /var/lib/mysql 目录删除以 db_name 命名的目录。
   

警告：不建议直接在 /var/lib/mysql 目录操作数据库目录，原因如下：

• 数据一致性：绕过存储引擎，可能导致数据不一致。
• 权限问题：破坏 MySQL 文件权限设置。
• 元数据问题：MySQL 未收到删除通知，可能引发错误。
• 备份风险：无法通过 MySQL 工具进行安全备份。
• 事务问题：未完成的事务无法正确处理。

推荐使用 MySQL 提供的 DDL 命令操作数据库，确保操作安全。

数据库字符集与校验规则

• 字符集（CHARACTER SET）：定义数据存储的编码格式（如 utf8）。
• 校验规则（COLLATE）：定义字符串比较和排序规则（如 utf8_general_ci）。

未指定时，数据库继承 MySQL 配置文件中的默认设置。

---

表 DLL

创建表

CREATE TABLE table_name (column_name1 datatype [constraints] [COMMENT 'description'],column_name2 datatype [constraints] [COMMENT 'description'],...[PRIMARY KEY (column_name)]
) [CHARACTER SET charset] [COLLATE collation] [ENGINE engine] [COMMENT 'table_description'];

• table_name：表名。
• column_name：列名。
• datatype：列数据类型（如 INT, VARCHAR）。
• constraints：约束（如 NOT NULL, UNIQUE）。
• CHARACTER SET / COLLATE：表级字符集和校验规则，未指定时继承数据库设置。
• ENGINE：存储引擎（如 InnoDB, MyISAM）。

示例：创建 students 表：

CREATE TABLE students (student_id INT AUTO_INCREMENT COMMENT '学生唯一标识符',first_name VARCHAR(50) NOT NULL COMMENT '学生的姓',last_name VARCHAR(50) NOT NULL COMMENT '学生的名',date_of_birth DATE COMMENT '出生日期',enrollment_date DATE COMMENT '入学日期',major VARCHAR(100) COMMENT '专业名称',email VARCHAR(100) UNIQUE COMMENT '电子邮件地址，必须唯一',PRIMARY KEY (student_id)
) COMMENT='存储学生信息的表格';

查看表详情

DESC table_name;

显示表结构，包括列名、数据类型、约束等。

查看表创建语句

SHOW CREATE TABLE table_name \G;

修改表

使用 ALTER TABLE 命令修改表结构：

ALTER TABLE table_name[operation1],[operation2],...;

1. 添加列

ALTER TABLE students
ADD COLUMN phone_number VARCHAR(15) COMMENT '学生联系电话';

2. 修改列

使用 MODIFY COLUMN 修改列定义：

ALTER TABLE students
MODIFY COLUMN email VARCHAR(120) COMMENT '更新后的电子邮件地址';

3. 删除列

ALTER TABLE students
DROP COLUMN phone_number;

4. 修改存储引擎

ALTER TABLE students
ENGINE = MyISAM;

5. 重命名表

ALTER TABLE students
RENAME TO stu;

注意：修改表结构时，建议先备份数据，特别是在生产环境中。

删除表

DROP TABLE [IF EXISTS] table_name [, table_name2, ...] [RESTRICT | CASCADE];

• [IF EXISTS]：防止删除不存在的表时出错。
• RESTRICT / CASCADE：控制外键约束行为（视存储引擎支持）。

---

结论

DDL 命令是数据库管理的核心工具，涵盖了数据库和表的创建、修改、查看和删除等操作。通过本文提供的语法和示例，您可以轻松掌握这些命令，并在实际项目中安全、高效地管理数据库结构。始终记得在操作前备份数据，并使用 MySQL 提供的命令而非直接操作文件系统，以确保数据一致性和安全性。