MySQL教程FG075-MySQL DDL语句详解

内容简介：本文主要介绍MySQL相关知识，包括安装、配置、优化等内容。风哥教程参考MySQL官方文档MySQL SQL Syntax、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn 02 学习交流加群风哥微信: itpux-com

Part01-基础概念与理论知识

DDL（Data Definition Language，数据定义语言）是SQL语言的一个重要组成部分，用于定义和管理数据库对象的结构。在MySQL中，DDL语句主要用于创建、修改、删除数据库、表、索引、视图、存储过程等数据库对象。 03 学习交流加群风哥QQ113257174

1.1 DDL语句的特点

DDL语句执行后会自动提交，不需要手动COMMIT
DDL语句会隐式地对相关对象加锁，可能会影响数据库性能
DDL语句执行后会导致数据库对象的结构发生变化

1.2 常用DDL语句分类

类别	语句	作用
数据库操作	CREATE DATABASE, ALTER DATABASE, DROP DATABASE	创建、修改、删除数据库
表操作	CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE	创建、修改、删除、截断表
索引操作	CREATE INDEX, ALTER INDEX, DROP INDEX	创建、修改、删除索引
视图操作	CREATE VIEW, ALTER VIEW, DROP VIEW	创建、修改、删除视图
存储对象操作	CREATE PROCEDURE, CREATE FUNCTION, CREATE TRIGGER	创建存储过程、函数、触发器

Part02-生产环境规划与建议

2.1 创建数据库

-- 创建数据库
CREATE DATABASE test_db;

-- 创建数据库并指定字符集和排序规则
CREATE DATABASE test_db 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

2.2 修改数据库

-- 修改数据库字符集
ALTER DATABASE test_db 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

2.3 删除数据库

-- 删除数据库
DROP DATABASE test_db;

-- 安全删除数据库（如果存在）
DROP DATABASE IF EXISTS test_db;

Part03-生产环境项目实施方案

3.1 创建表

-- 创建基本表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    department VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10,2)
);

-- 创建带索引的表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    department VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10,2),
    INDEX idx_department (department),
    INDEX idx_hire_date (hire_date)
);

-- 创建带外键的表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    department_id INT,
    hire_date DATE,
    salary DECIMAL(10,2),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

3.2 修改表

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

-- 修改列类型
ALTER TABLE employees MODIFY COLUMN age TINYINT;

-- 修改列名
ALTER TABLE employees CHANGE COLUMN email employee_email VARCHAR(100);

-- 删除列
ALTER TABLE employees DROP COLUMN employee_email;

-- 添加主键
ALTER TABLE employees ADD PRIMARY KEY (id);

-- 添加索引
ALTER TABLE employees ADD INDEX idx_name (name);

-- 添加外键
ALTER TABLE employees ADD FOREIGN KEY (department_id) REFERENCES departments(id);

3.3 删除表

-- 删除表
DROP TABLE employees;

-- 安全删除表（如果存在）
DROP TABLE IF EXISTS employees;

-- 删除多个表
DROP TABLE IF EXISTS employees, departments;

3.4 截断表

-- 截断表（删除所有数据，但保留表结构）
TRUNCATE TABLE employees;

Part04-生产案例与实战讲解

4.1 创建索引

-- 创建普通索引
CREATE INDEX idx_name ON employees(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON employees(email);

-- 创建复合索引
CREATE INDEX idx_dept_hire ON employees(department, hire_date);

-- 创建全文索引
CREATE FULLTEXT INDEX idx_description ON products(description);

4.2 修改索引

-- 重命名索引（MySQL 5.7+）
ALTER TABLE employees RENAME INDEX idx_name TO idx_employee_name;

4.3 删除索引

-- 删除索引
DROP INDEX idx_name ON employees;

-- 或使用ALTER TABLE删除索引
ALTER TABLE employees DROP INDEX idx_name;

Part05-风哥经验总结与分享

5.1 创建视图

-- 创建基本视图
CREATE VIEW employee_view AS
SELECT id, name, department, salary
FROM employees
WHERE salary > 5000;

-- 创建带检查选项的视图
CREATE VIEW employee_view AS
SELECT id, name, department, salary
FROM employees
WHERE salary > 5000
WITH CHECK OPTION;

5.2 修改视图

-- 修改视图
ALTER VIEW employee_view AS
SELECT id, name, department, salary, hire_date
FROM employees
WHERE salary > 6000;

-- 或使用CREATE OR REPLACE修改视图
CREATE OR REPLACE VIEW employee_view AS
SELECT id, name, department, salary, hire_date
FROM employees
WHERE salary > 6000;

5.3 删除视图

-- 删除视图
DROP VIEW employee_view;

-- 安全删除视图（如果存在）
DROP VIEW IF EXISTS employee_view;

6. 存储对象DDL操作

6.1 创建存储过程

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE get_employee(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END //
DELIMITER ;

6.2 创建函数

-- 创建函数
DELIMITER //
CREATE FUNCTION calculate_bonus(salary DECIMAL(10,2)) RETURNS DECIMAL(10,2)
BEGIN
    RETURN salary * 0.1;
END //
DELIMITER ;

6.3 创建触发器

-- 创建触发器
DELIMITER //
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.hire_date = NOW();
END //
DELIMITER ;

7. DDL语句最佳实践

7.1 数据库设计最佳实践

在创建数据库时指定合适的字符集和排序规则
为数据库对象选择合适的命名规范
定期备份数据库结构

7.2 表设计最佳实践

为表选择合适的存储引擎（推荐InnoDB）
为表设置合适的字符集
合理设计表结构，避免过多列
为常用查询字段创建索引
使用合适的数据类型，避免使用过大的数据类型

7.3 索引设计最佳实践

只为常用查询的字段创建索引
避免在频繁更新的字段上创建索引
合理设计复合索引的顺序
定期维护和优化索引

7.4 DDL操作性能优化

在低峰期执行DDL操作
对于大表，使用在线DDL（MySQL 5.6+）
执行DDL操作前备份数据
使用pt-online-schema-change等工具执行大表的结构变更

8. 常见错误和解决方案

8.1 数据库创建错误

错误信息	原因	解决方案
ERROR 1007 (HY000): Can’t create database ‘test_db’; database exists	数据库已存在	使用CREATE DATABASE IF NOT EXISTS或先删除现有数据库
ERROR 1044 (42000): Access denied for user ‘user’@’localhost’ to database ‘test_db’	权限不足	确保用户有创建数据库的权限

8.2 表操作错误

错误信息	原因	解决方案
ERROR 1050 (42S01): Table ’employees’ already exists	表已存在	使用CREATE TABLE IF NOT EXISTS或先删除现有表
ERROR 1215 (HY000): Cannot add foreign key constraint	外键约束创建失败	确保父表存在，且字段类型匹配
ERROR 1067 (42000): Invalid default value for ‘hire_date’	默认值无效	为日期字段设置有效的默认值

8.3 索引操作错误

错误信息	原因	解决方案
ERROR 1061 (42000): Duplicate key name ‘idx_name’	索引名重复	使用不同的索引名
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes	索引键长度超过限制	缩短索引字段长度或使用前缀索引

9. 示例演示

9.1 完整的数据库和表创建示例

-- 创建数据库
CREATE DATABASE company_db 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE company_db;

-- 创建部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age TINYINT,
    department_id INT,
    position VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id),
    INDEX idx_department (department_id),
    INDEX idx_position (position),
    INDEX idx_salary (salary)
);

-- 创建视图
CREATE VIEW high_salary_employees AS
SELECT e.id, e.name, e.position, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 8000;

-- 插入测试数据
INSERT INTO departments (name, description) VALUES
('技术部', '负责公司技术开发'),
('市场部', '负责公司市场推广'),
('人力资源部', '负责人力资源管理');

INSERT INTO employees (name, age, department_id, position, salary, hire_date) VALUES
('风哥1号', 28, 1, '软件工程师', 10000, '2023-01-15'),
('风哥2号', 32, 1, '技术主管', 15000, '2022-06-10'),
('王五', 25, 2, '市场专员', 6000, '2023-03-20'),
('赵六', 35, 3, '人力资源经理', 9000, '2021-11-05');

-- 查询视图
SELECT * FROM high_salary_employees;

9.2 表结构修改示例

-- 添加新列
ALTER TABLE employees ADD COLUMN email VARCHAR(100);

-- 修改列类型
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);

-- 添加新索引
ALTER TABLE employees ADD INDEX idx_email (email);

-- 重命名表
ALTER TABLE employees RENAME TO company_employees;

-- 查看表结构
DESCRIBE company_employees;

10. 总结

DDL语句是MySQL数据库管理的重要组成部分，用于定义和管理数据库对象的结构。本文详细介绍了MySQL中常用的DDL语句，包括数据库操作、表操作、索引操作、视图操作和存储对象操作等。 04 风哥提示：

在使用DDL语句时，需要注意以下几点： 05更多学习教程公众号风哥教程itpux_com

DDL语句执行后会自动提交，无法回滚
对于大表的结构变更，应选择合适的时机并使用在线DDL
执行DDL操作前应备份数据，以防意外情况
遵循最佳实践，合理设计数据库结构和索引

通过掌握DDL语句的使用方法，可以有效地管理MySQL数据库结构，提高数据库的性能和可维护性。 06 from mysql视频:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html