MySQL — 数据库建库与建表

     MySQL数据库建库与建表是数据存储的核心操作。建库通过CREATE DATABASE语句实现，需指定库名及字符集（如utf8mb4）和排序规则（如utf8mb4_unicode_ci），确保兼容多语言数据。建库后使用USE命令选定当前数据库。建表需通过CREATE TABLE定义表结构，包含列名、数据类型（如INT、VARCHAR、DATE）、约束及索引。主键用PRIMARY KEY声明以唯一标识记录，NOT NULL强制字段非空，UNIQUE保证值唯一，外键通过FOREIGN KEY关联其他表。索引通过INDEX或KEY提升查询效率，存储引擎可选InnoDB（支持事务）或MyISAM。设计时需遵循命名规范，合理选择数据类型，优化索引，平衡范式与冗余，确保数据一致性和查询性能。规范的库表结构是系统高效运行的基础。

---

建库（Database）

MySQL建库通过CREATE DATABASE语句创建数据库，指定字符集（如utf8mb4）和排序规则。

创建数据库

   CREATE DATABASE database_name 
     CHARACTER SET utf8mb4 
     COLLATE utf8mb4_unicode_ci;

1. CHARACTER SET：推荐 utf8mb4（支持Emoji和更多字符）
2. COLLATE：推荐 utf8mb4_unicode_ci（大小写不敏感排序）

删除数据库

   DROP DATABASE IF EXISTS database_name;

切换数据库

  USE database_name;

---

 建表（Table）

MySQL建表使用CREATE TABLE语句，定义表名、字段、数据类型及约束（如主键、外键、非空等）。

 基础语法

CREATE TABLE table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description TEXT,
    UNIQUE KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

常用字段类型

类型	说明
INT	整数
VARCHAR(n)	可变长度字符串（最大n字符）
CHAR(n)	定长字符串
DATE	日期（YYYY-MM-DD）
DATETIME	日期时间（YYYY-MM-DD HH:MM:SS）
TIMESTAMP	时间戳（自动记录更新时间）
DECIMAL(m,n)	精确小数（总位数m，小数n位）
TEXT	长文本

常用约束和选项

1. 主键：PRIMARY KEY
2. 自增：AUTO_INCREMENT
3. 非空：NOT NULL
4. 默认值：DEFAULT value
5. 唯一约束：UNIQUE KEY
6. 外键约束：  

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

索引

-- 单列索引
CREATE INDEX idx_name ON table_name (column_name);

-- 联合索引
CREATE INDEX idx_name ON table_name (col1, col2);

---

修改表结构

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

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

-- 删除列
ALTER TABLE table_name DROP COLUMN description;

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

-- 删除表
DROP TABLE IF EXISTS table_name;

---

表设计规范

• 命名规范：使用小写字母和下划线（如user_info）
• 主键：建议使用自增整数或业务无关字段（如UUID）
• 避免保留字：如order需写成`order`
• 注释：使用COMMENT说明字段含义

   CREATE TABLE user (
       id INT COMMENT '用户ID'
   );

• 性能优化：  
  • 避免过度使用TEXT或BLOB
  • 合理设计索引（避免过多索引影响写入性能）

---

完整示例

创建数据库

CREATE DATABASE shop 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;
USE shop;

创建用户表

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password CHAR(60) NOT NULL, -- 存储加密后的密码（如BCrypt）
    email VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

创建商品表

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    is_deleted TINYINT DEFAULT 0 -- 逻辑删除标记
);

---

总结

     MySQL数据库建库与建表是数据库设计的核心环节。建库时需通过CREATE DATABASE语句定义数据库名称，并指定字符集（如utf8mb4）和排序规则（如utf8mb4_unicode_ci），以确保多语言兼容性和数据存储规范。使用IF NOT EXISTS可避免重复创建冲突。建表需使用CREATE TABLE语句，明确表名、字段名、数据类型及约束条件。字段设计需合理选择数据类型，例如整型（INT）、可变字符串（VARCHAR）、日期（DATE）等，同时设定长度限制。

     主键（PRIMARY KEY）用于唯一标识记录，外键（FOREIGN KEY）实现表间关联，索引（INDEX）可提升查询效率。约束条件如NOT NULL保证非空值，UNIQUE确保唯一性，DEFAULT设置默认值，AUTO_INCREMENT实现自增。表设计还需考虑存储引擎，InnoDB支持事务和行级锁，MyISAM适合读密集型场景。字符集需与库级设置一致，避免乱码。命名应简洁规范，避免使用保留字，表名建议小写加下划线分隔。合理规划表结构需遵循范式原则，平衡冗余与查询性能，同时预留扩展性。通过SHOW CREATE语句可验证建库建表语句的准确性。