MySQL创建数据库和表的命令详解（附实例）

在现代Web开发和数据驱动型应用中，MySQL 作为最流行的开源关系型数据库管理系统之一，广泛应用于各类项目中。无论是搭建网站、开发App，还是进行数据分析，掌握 MySQL 的基本操作都是开发者不可或缺的技能。

其中，创建数据库和表是使用 MySQL 进行数据管理的第一步。数据库用于存储数据，而表则是组织和管理数据的基本结构。通过合理的数据库设计与表结构定义，可以有效提升系统的性能与可维护性。

本文将详细介绍如何使用 SQL 命令在 MySQL 中创建数据库和数据表，并结合实际示例帮助读者快速掌握这一基础但至关重要的技能。无论你是刚入门的新手，还是希望巩固基础的开发者，都能从中获得实用的知识。

一、数据库创建与管理核心命令

1. 数据库创建语法与实例

MySQL通过CREATE DATABASE语句实现数据库创建，核心语法如下：

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

• 参数说明：

• IF NOT EXISTS：避免重复创建同名数据库时产生错误。

• CHARACTER SET：指定字符集（如utf8mb4支持完整Unicode字符）。

• COLLATE：定义排序规则（如utf8mb4_unicode_ci实现不区分大小写的排序）。

实例1：创建支持中文的数据库

CREATE DATABASE ecommerce_platform 
 CHARACTER SET utf8mb4 
 COLLATE utf8mb4_unicode_ci;

此命令创建名为ecommerce_platform的数据库，采用UTF-8编码和Unicode排序规则，确保能正确存储中文商品名称和用户信息。

2. 数据库操作与权限管理

• 查看现有数据库：

  SHOW DATABASES; -- 列出所有数据库
  SHOW CREATE DATABASE ecommerce_platform; -- 查看数据库创建参数

• 使用数据库：

  USE ecommerce_platform; -- 切换到目标数据库
  SELECT DATABASE(); -- 确认当前数据库

• 权限分配（需管理员权限）：

  GRANT ALL PRIVILEGES ON ecommerce_platform.* 
  TO 'dev_user'@'localhost' IDENTIFIED BY 'SecurePass123!';

此命令授予本地用户dev_user对ecommerce_platform数据库的完全控制权，密码为SecurePass123!。

3. 数据库删除与安全实践

DROP DATABASE IF EXISTS test_db; -- 安全删除测试数据库

注意事项：

• 删除操作不可逆，需提前备份重要数据。

• 生产环境建议通过mysqldump导出数据后再删除：

  mysqldump -u root -p ecommerce_platform > backup.sql

二、数据表创建与结构定义

1. 基础表创建语法

CREATE TABLE [IF NOT EXISTS] table_name (
 column1 datatype [constraints],
 column2 datatype [constraints],
 ...
 [PRIMARY KEY (column_list)]
 [ENGINE=storage_engine]
) ENGINE=InnoDB;

• 关键参数：

• datatype：支持INT、VARCHAR(n)、DECIMAL(p,s)等类型。

• constraints：包括NOT NULL、UNIQUE、AUTO_INCREMENT等。

• ENGINE：指定存储引擎（InnoDB支持事务，MyISAM适合读密集型场景）。

2. 电商用户表示例

CREATE TABLE users (
 user_id INT AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50) NOT NULL UNIQUE,
 email VARCHAR(100) NOT NULL UNIQUE,
 password_hash CHAR(60) NOT NULL, -- 存储bcrypt哈希值
 registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 last_login DATETIME,
 INDEX idx_username (username), -- 添加索引加速查询
 CONSTRAINT chk_email CHECK (email LIKE '%@%.%') -- 简单格式验证
) ENGINE=InnoDB;

设计亮点：

• 使用AUTO_INCREMENT实现自增主键。

• 通过UNIQUE约束防止用户名和邮箱重复。

• 添加CHECK约束确保邮箱格式基本正确。

• 为高频查询字段username创建索引。

3. 订单表与外键关联

CREATE TABLE orders (
 order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
 user_id INT NOT NULL,
 order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 total_amount DECIMAL(10,2) NOT NULL,
 status ENUM('pending', 'paid', 'shipped', 'completed') DEFAULT 'pending',
 FOREIGN KEY (user_id) REFERENCES users(user_id) -- 外键关联
  ON DELETE CASCADE -- 级联删除
  ON UPDATE CASCADE, -- 级联更新
 INDEX idx_order_date (order_date)
) ENGINE=InnoDB;

关键设计：

• 使用ENUM类型限制订单状态值。

• 通过外键关联users表，确保数据完整性。

• 设置级联操作，当用户被删除时自动删除其订单。

4. 表结构修改与优化

• 添加字段：

  ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

• 修改字段类型：

  ALTER TABLE orders MODIFY COLUMN total_amount DECIMAL(12,2);

• 删除约束：

  ALTER TABLE users DROP INDEX idx_username;

• 表重命名：

  RENAME TABLE old_orders TO archive_orders;

三、数据操作与高级特性

1. 数据插入与批量操作

• 单条插入：

  INSERT INTO users (username, email, password_hash)
  VALUES ('john_doe', 'john@example.com', '$2a$10$xJwL5vZ3Y7k9mNqP8sTq...');

• 批量插入（提升性能）：

  INSERT INTO products (product_name, price, stock) VALUES
  ('Laptop', 999.99, 50),
  ('Smartphone', 699.99, 100),
  ('Tablet', 349.99, 75);

2. 复杂查询与性能优化

• 多表关联查询：

  SELECT u.username, o.order_id, o.total_amount
  FROM users u
  JOIN orders o ON u.user_id = o.user_id
  WHERE o.order_date > '2025-01-01'
  ORDER BY o.total_amount DESC
  LIMIT 10;

• 分页查询优化：

  -- 使用覆盖索引避免回表
  SELECT order_id, user_id, total_amount
  FROM orders FORCE INDEX (idx_order_date)
  WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'
  ORDER BY order_date LIMIT 10000, 20;

3. 事务处理与数据一致性

START TRANSACTION;
-- 扣减库存
UPDATE products SET stock = stock - 1 WHERE product_id = 1001 AND stock >= 1;
-- 创建订单（需检查受影响行数）
IF ROW_COUNT() > 0 THEN
 INSERT INTO orders (user_id, product_id, quantity) VALUES (101, 1001, 1);
 COMMIT;
ELSE
 ROLLBACK;
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
END IF;

关键点：

• 使用START TRANSACTION开启事务。

• 通过ROW_COUNT()检查操作结果。

• 失败时使用ROLLBACK回滚，并返回自定义错误信息。

四、生产环境最佳实践

1. 命名规范与标准化

• 数据库命名：使用小写字母和下划线（如ecommerce_platform）。

• 表命名：复数形式（如order_items）或单数形式（如order_item），保持团队统一。

• 字段命名：避免使用MySQL保留字（如order应改为order_id）。

2. 索引策略优化

• 高频查询字段：为WHERE、JOIN、ORDER BY中的字段创建索引。

• 复合索引设计：

  CREATE INDEX idx_user_status_date ON orders (user_id, status, order_date);

  遵循最左前缀原则，支持user_id、user_id+status等查询模式。

3. 数据类型选择指南

4. 安全与备份策略

• 定期备份：

  # 每日全量备份
  mysqldump -u root -p --single-transaction ecommerce_platform > daily_backup.sql
  
  # 二进制日志备份（支持时间点恢复）
  mysqlbinlog /var/lib/mysql/mysql-bin.000123 > binlog_backup.sql

• 敏感数据保护：

• 使用AES_ENCRYPT()加密存储信用卡号：

  INSERT INTO payments (user_id, card_number)
  VALUES (101, AES_ENCRYPT('4111111111111111', 'encryption_key'));

• 通过视图限制数据访问：

  CREATE VIEW customer_orders AS
  SELECT order_id, total_amount, order_date
  FROM orders
  WHERE user_id = CURRENT_USER_ID(); -- 需应用层实现

五、完整电商系统示例

1. 数据库初始化脚本

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

-- 使用数据库
USE ecommerce_platform;

-- 创建用户表
CREATE TABLE users (
 user_id INT AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50) NOT NULL UNIQUE,
 email VARCHAR(100) NOT NULL UNIQUE,
 password_hash CHAR(60) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_username (username)
) ENGINE=InnoDB;

-- 创建产品表
CREATE TABLE products (
 product_id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 description TEXT,
 price DECIMAL(10,2) NOT NULL,
 stock INT NOT NULL DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FULLTEXT INDEX ft_search (name, description) -- 全文索引
) ENGINE=InnoDB;

-- 创建订单表
CREATE TABLE orders (
 order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
 user_id INT NOT NULL,
 order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 total_amount DECIMAL(12,2) NOT NULL,
 status ENUM('pending', 'paid', 'shipped', 'completed') DEFAULT 'pending',
 FOREIGN KEY (user_id) REFERENCES users(user_id)
  ON DELETE RESTRICT -- 防止误删用户
  ON UPDATE CASCADE,
 INDEX idx_status_date (status, order_date)
) ENGINE=InnoDB;

2. 典型业务操作示例

• 用户注册：

  INSERT INTO users (username, email, password_hash)
  VALUES ('alice_smith', 'alice@example.com', 
      '$2a$10$N9qo8uLOickgx2ZMRZoMy...'); -- bcrypt哈希值

• 产品搜索：

  -- 使用全文索引搜索包含"laptop"或"gaming"的产品
  SELECT product_id, name, price
  FROM products
  WHERE MATCH(name, description) AGAINST('+laptop +gaming' IN BOOLEAN MODE)
  LIMIT 10;

• 订单状态更新：

  -- 使用事务确保数据一致性
  START TRANSACTION;
  UPDATE orders SET status = 'shipped', shipping_date = NOW()
  WHERE order_id = 10001 AND status = 'paid';
  
  IF ROW_COUNT() > 0 THEN
   INSERT INTO order_history (order_id, old_status, new_status)
   VALUES (10001, 'paid', 'shipped');
   COMMIT;
  ELSE
   ROLLBACK;
  END IF;

通过系统掌握上述命令和设计模式，开发者可高效构建可扩展、高性能的MySQL数据库应用，同时确保数据的安全性和一致性。实际开发中需结合具体业务需求调整表结构和索引策略，并定期进行性能监控与优化。