MariaDB教程FG011-MariaDB DDL库表索引语法与官方设计规范实战

本文档风哥主要介绍MariaDB的DDL（数据定义语言）语法与索引设计规范，包括数据库、表、索引的创建、修改和删除，以及官方推荐的设计规范。风哥教程参考MariaDB官方文档DDL
Statements、Indexes内容，适合数据库管理员和开发人员学习。

Part01-基础概念与理论知识

1.1 DDL概述

DDL（Data Definition Language）是用于定义数据库结构的语言，包括：

CREATE：创建数据库、表、索引等
ALTER：修改数据库、表、索引等
DROP：删除数据库、表、索引等
TRUNCATE：清空表数据

1.2 索引基础

索引是提高查询性能的重要手段，包括：

主键索引
唯一索引
普通索引
复合索引
全文索引

1.3 设计规范

MariaDB官方推荐的设计规范：

数据库命名规范
表命名规范
字段命名规范
索引命名规范
数据类型选择规范

更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 数据库设计

风哥提示：生产环境数据库设计应考虑业务需求、性能要求、可扩展性等因素。

2.2 表设计

表设计建议：

选择合适的数据类型
设置合理的字段长度
建立适当的约束
考虑表的大小和增长趋势

2.3 索引设计

索引设计建议：

为常用查询字段创建索引
避免过度索引
合理设计复合索引
考虑索引的维护成本

学习交流加群风哥微信: itpux-com

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

3.1 DDL语法

更多学习教程公众号风哥教程itpux_com

# 创建数据库
CREATE DATABASE fgedudb
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
# 使用数据库
USE fgedudb;
# 创建表
CREATE TABLE fgedu_users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
# 修改表
ALTER TABLE fgedu_users
ADD COLUMN phone VARCHAR(20),
MODIFY COLUMN age INT UNSIGNED,
DROP COLUMN age;
# 删除表
DROP TABLE IF EXISTS fgedu_users;
# 创建临时表
CREATE TEMPORARY TABLE temp_users (
id INT,
name VARCHAR(50)
);

3.2 索引创建

# 创建主键索引
CREATE TABLE fgedu_orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
amount DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
# 创建唯一索引
CREATE UNIQUE INDEX idx_email ON fgedu_users(email);
# 创建普通索引
CREATE INDEX idx_name ON fgedu_users(name);
# 创建复合索引
CREATE INDEX idx_user_created ON fgedu_orders(user_id, created_at);
# 创建全文索引
CREATE FULLTEXT INDEX idx_content ON fgedu_articles(content);
# 删除索引
DROP INDEX idx_name ON fgedu_users;
# 查看索引
SHOW INDEX FROM fgedu_users;

3.3 设计实施

# 设计实施示例
# 1. 创建数据库
CREATE DATABASE fgedudb
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
# 2. 创建用户表
CREATE TABLE fgedu_users (
user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘用户ID’,
username VARCHAR(50) NOT NULL COMMENT ‘用户名’,
email VARCHAR(100) UNIQUE NOT NULL COMMENT ‘邮箱’,
password VARCHAR(100) NOT NULL COMMENT ‘密码’,
nickname VARCHAR(50) COMMENT ‘昵称’,
status TINYINT DEFAULT 1 COMMENT ‘状态：1-启用，0-禁用’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT ‘用户表’;
# 3. 创建订单表
CREATE TABLE fgedu_orders (
order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘订单ID’,
user_id INT NOT NULL COMMENT ‘用户ID’,
order_no VARCHAR(32) UNIQUE NOT NULL COMMENT ‘订单号’,
amount DECIMAL(10,2) NOT NULL COMMENT ‘订单金额’,
status TINYINT DEFAULT 0 COMMENT ‘订单状态：0-待付款，1-已付款，2-已发货，3-已完成，4-已取消’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
FOREIGN KEY (user_id) REFERENCES fgedu_users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT ‘订单表’;
# 4. 创建商品表
CREATE TABLE fgedu_products (
product_id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘商品ID’,
product_name VARCHAR(100) NOT NULL COMMENT ‘商品名称’,
price DECIMAL(10,2) NOT NULL COMMENT ‘商品价格’,
stock INT DEFAULT 0 COMMENT ‘库存’,
category_id INT COMMENT ‘分类ID’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT ‘商品表’;
# 5. 创建索引
CREATE INDEX idx_username ON fgedu_users(username);
CREATE INDEX idx_user_status ON fgedu_users(status);
CREATE INDEX idx_order_user ON fgedu_orders(user_id);
CREATE INDEX idx_order_status ON fgedu_orders(status);
CREATE INDEX idx_product_category ON fgedu_products(category_id);
CREATE INDEX idx_product_price ON fgedu_products(price);

学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 DDL语法实战

# 实战示例：创建电商数据库
# 1. 创建数据库
[root@fgedu.net.cn ~]# mysql -u root -p
Enter password:
MariaDB [(none)]> CREATE DATABASE fgedu_ecommerce
-> CHARACTER SET utf8mb4
-> COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE fgedu_ecommerce;
Database changed
# 2. 创建用户表
MariaDB [fgedu_ecommerce]> CREATE TABLE fgedu_users (
-> user_id INT PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(50) NOT NULL,
-> email VARCHAR(100) UNIQUE NOT NULL,
-> password VARCHAR(100) NOT NULL,
-> nickname VARCHAR(50),
-> status TINYINT DEFAULT 1,
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)
# 3. 修改表结构
MariaDB [fgedu_ecommerce]> ALTER TABLE fgedu_users
-> ADD COLUMN phone VARCHAR(20),
-> MODIFY COLUMN status TINYINT DEFAULT 1 COMMENT ‘状态：1-启用，0-禁用’;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 4. 创建订单表
MariaDB [fgedu_ecommerce]> CREATE TABLE fgedu_orders (
-> order_id INT PRIMARY KEY AUTO_INCREMENT,
-> user_id INT NOT NULL,
-> order_no VARCHAR(32) UNIQUE NOT NULL,
-> amount DECIMAL(10,2) NOT NULL,
-> status TINYINT DEFAULT 0,
-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> FOREIGN KEY (user_id) REFERENCES fgedu_users(user_id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)
# 5. 查看表结构
MariaDB [fgedu_ecommerce]> DESCRIBE fgedu_users;
+————+————–+——+—–+——————-+—————————–+
| Field | Type | Null | Key | Default | Extra |
+————+————–+——+—–+——————-+—————————–+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(50) | NO | | NULL | |
| email | varchar(100) | NO | UNI | NULL | |
| password | varchar(100) | NO | | NULL | |
| nickname | varchar(50) | YES | | NULL | |
| status | tinyint(4) | YES | | 1 | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| phone | varchar(20) | YES | | NULL | |
+————+————–+——+—–+——————-+—————————–+
9 rows in set (0.00 sec)

4.2 索引设计实战

4.3 设计规范实战

# 实战示例：设计规范实施
# 1. 数据库命名规范
# 使用小写字母、数字和下划线，避免使用保留字
CREATE DATABASE fgedu_ecommerce;
# 2. 表命名规范
# 使用”fgedu_”前缀，表明所属项目
CREATE TABLE fgedu_users (…);
CREATE TABLE fgedu_orders (…);
CREATE TABLE fgedu_products (…);
# 3. 字段命名规范
# 使用小写字母、数字和下划线，避免使用保留字
# 表意明确，避免使用缩写
CREATE TABLE fgedu_users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
created_at TIMESTAMP
);
# 4. 索引命名规范
# 主键索引：PRIMARY
# 唯一索引：idx_字段名
# 普通索引：idx_字段名
# 复合索引：idx_字段1_字段2
CREATE INDEX idx_username ON fgedu_users(username);
CREATE INDEX idx_user_status ON fgedu_users(user_id, status);
# 5. 数据类型选择规范
# 选择合适的数据类型，避免使用过大的类型
# 例如：使用INT而不是BIGINT，使用VARCHAR(50)而不是VARCHAR(255)
CREATE TABLE fgedu_users (
user_id INT PRIMARY KEY, # 适用于用户ID
age TINYINT, # 适用于年龄
amount DECIMAL(10,2), # 适用于金额
created_at TIMESTAMP # 适用于时间
);

风哥提示：安全开发是防止SQL注入的第一道防线

Part05-风哥经验总结与分享

5.1 常见问题与解决

索引过多：删除不必要的索引，避免过度索引
索引失效：检查SQL语句，避免使用函数、类型转换等
表结构不合理：定期审查表结构，根据业务需求调整
命名不规范：统一命名规范，提高代码可读性
数据类型选择不当：选择合适的数据类型，节省存储空间

5.2 最佳实践

风哥提示：生产环境应建立完善的数据库设计规范，包括命名规范、数据类型选择、索引设计等，确保数据库结构的合理性和可维护性。

5.3 设计检查清单

数据库命名是否符合规范
表命名是否符合规范
字段命名是否符合规范
数据类型选择是否合理
索引设计是否合理
约束设置是否完善
表结构是否符合业务需求
是否考虑了未来的扩展性

from MariaDB视频:www.itpux.com

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