数据库与表的基本操作

数据库与表的基本操作（DDL）

DDL（Data Definition Language）用于定义数据库结构，包括创建、修改、删除数据库和表。下面介绍 MySQL 中常用的 DDL 语句。

一、数据库操作

1. 创建数据库

sql

CREATE DATABASE [IF NOT EXISTS] database_name
  [CHARACTER SET charset_name]
  [COLLATE collation_name];

IF NOT EXISTS：避免重复创建时报错。
字符集推荐 utf8mb4（支持完整的 Unicode，包括 emoji）。

示例：

sql

CREATE DATABASE IF NOT EXISTS school
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

2. 删除数据库

sql

DROP DATABASE [IF EXISTS] database_name;

示例：

sql

DROP DATABASE IF EXISTS test_db;

注意：删除数据库是不可逆操作，会删除其中所有表和数据。

3. 修改数据库字符集

sql

ALTER DATABASE database_name
  CHARACTER SET new_charset
  COLLATE new_collation;

示例（将数据库字符集改为 utf8mb4）：

sql

ALTER DATABASE school
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

二、表操作

1. 创建表

sql

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
) [ENGINE=engine] [CHARACTER SET charset];

示例：创建 students 表

sql

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age TINYINT UNSIGNED,
    grade DECIMAL(5,2),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

常见约束：

PRIMARY KEY：主键
AUTO_INCREMENT：自增（通常用于主键）
NOT NULL：不能为空
UNIQUE：值唯一
DEFAULT：默认值
CHECK：检查约束（MySQL 8.0+ 支持）

2. 删除表

sql

DROP TABLE [IF EXISTS] table_name;

示例：

sql

DROP TABLE IF EXISTS temp_table;

3. 修改表结构（ALTER TABLE）

增加列

sql

ALTER TABLE table_name ADD column_name datatype [constraints] [FIRST | AFTER column];

示例：在 students 表中增加 email 列

sql

ALTER TABLE students ADD email VARCHAR(100) NOT NULL;

删除列

sql

ALTER TABLE table_name DROP COLUMN column_name;

示例：

sql

ALTER TABLE students DROP COLUMN email;

修改列类型（MODIFY）

sql

ALTER TABLE table_name MODIFY column_name new_datatype [constraints];

示例：将 name 列的长度从 50 改为 100

sql

ALTER TABLE students MODIFY name VARCHAR(100) NOT NULL;

重命名列（CHANGE）

sql

ALTER TABLE table_name CHANGE old_name new_name new_datatype [constraints];

示例：将 grade 列重命名为 score，同时修改类型为 DECIMAL(6,2)

sql

ALTER TABLE students CHANGE grade score DECIMAL(6,2);

重命名表

sql

RENAME TABLE old_name TO new_name;

示例：

sql

RENAME TABLE students TO pupils;

也可以使用 ALTER TABLE：

sql

ALTER TABLE old_name RENAME TO new_name;

三、补充操作

查看表结构

sql

DESC table_name;          -- 简洁版
SHOW FULL COLUMNS FROM table_name;  -- 详细信息

查看创建表的语句

sql

SHOW CREATE TABLE table_name;

复制表结构（不复制数据）

sql

CREATE TABLE new_table LIKE original_table;

复制表结构和数据

sql

CREATE TABLE new_table AS SELECT * FROM original_table;

清空表（删除所有行，保留结构）

sql

TRUNCATE TABLE table_name;

TRUNCATE 比 DELETE FROM table_name 更快，但无法回退（事务性差），且会重置自增计数器。

四、注意事项

命名规范：数据库名、表名、列名建议使用小写字母 + 下划线（如 user_info），避免使用 SQL 保留字。
字符集：建库时指定 utf8mb4 可避免中文或 emoji 乱码。
InnoDB 引擎：推荐使用 InnoDB，支持事务、外键、行级锁。
IF EXISTS / IF NOT EXISTS：在自动化脚本中建议加上，防止因对象不存在或重复创建而报错。
修改列类型（MODIFY/CHANGE）：可能会导致数据丢失（如将 VARCHAR 改为 INT 且原数据非数字），谨慎操作。

五、综合示例

sql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS company
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE company;

-- 创建表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10,2) NOT NULL,
    hire_date DATE,
    status TINYINT DEFAULT 1
);

-- 增加列
ALTER TABLE employees ADD email VARCHAR(100);

-- 修改列类型
ALTER TABLE employees MODIFY email VARCHAR(200);

-- 重命名列
ALTER TABLE employees CHANGE emp_name full_name VARCHAR(50);

-- 重命名表
RENAME TABLE employees TO staff;

-- 删除表（谨慎）
DROP TABLE IF EXISTS staff;

通过以上 DDL 语句，可以灵活管理数据库和表结构，为后续的数据操作（DML）奠定基础。

参考文献

资料	说明
MySQL 8.0 手册	官方
数据存储导读	学习路径