【MySQL基础】MySQL表操作全面指南：从创建到管理的深度解析
MySQL学习：
https://blog.ZEEKLOG.net/2301_80220607/category_12971838.html?spm=1001.2014.3001.5482
前言：
在上一篇我们学习了库的创建和使用，表是其存储数据的核心结构。本文将全面讲解MySQL中关于表的各项操作，包括创建、修改、删除等，并深入探讨相关知识点和注意事项。
表的基本概念
在MySQL中，表是存储数据的主要对象，由行和列组成。理解表的基本结构对数据库设计至关重要。
表的主要组成部分
| 组成部分 | 描述 |
|---|---|
| 表名 | 表的唯一标识符，遵循命名规则 |
| 列(字段) | 表的垂直结构，定义数据的类型和约束 |
| 行(记录) | 表的水平结构，实际存储的数据 |
| 主键 | 唯一标识表中每一行的列或列组合 |
| 索引 | 提高查询性能的数据结构 |
| 约束 | 保证数据完整性的规则 |
创建表
基本语法
CREATE TABLE [IF NOT EXISTS] table_name ( column1 datatype [constraints] [COMMENT 'column_comment'], column2 datatype [constraints] [COMMENT 'column_comment'], ..., [table_constraints] ) [ENGINE=storage_engine] [CHARACTER SET charset] [COLLATE collation] [COMMENT 'table_comment'] [PARTITION BY partition_type (partition_expression)];语法各部分详细说明：
CREATE TABLE：创建表的核心关键字[IF NOT EXISTS]：可选子句，避免表已存在时报错table_name：要创建的表的名称（遵循命名规则）列定义部分：column1,column2：列名（字段名）datatype：列的数据类型（如INT, VARCHAR等）[constraints]：可选的列约束（如NOT NULL, UNIQUE等）[COMMENT]：可选的列注释table_constraints：表级约束（如PRIMARY KEY, FOREIGN KEY等）ENGINE：指定表的存储引擎（如InnoDB, MyISAM）CHARACTER SET：指定表的字符集（如utf8mb4）COLLATE：指定表的排序规则（如utf8mb4_general_ci）COMMENT：可选的表注释PARTITION BY：可选的分区定义
关键参数说明
| 参数 | 描述 | 示例 | 注意事项 |
|---|---|---|---|
| IF NOT EXISTS | 避免表已存在时报错 | CREATE TABLE IF NOT EXISTS users | 不会检查表结构是否相同 |
| 列定义 | 列名+数据类型+约束 | username VARCHAR(50) NOT NULL COMMENT '用户登录名' | 列名不能重复 |
| 数据类型 | 定义列存储的数据类型 | INT, VARCHAR(255), DECIMAL(10,2) | 选择最合适的数据类型 |
| 列约束 | 限制列中数据的规则 | NOT NULL, UNIQUE, DEFAULT 'value' | 约束越多，性能开销越大 |
| 表级约束 | 应用于整个表的约束 | PRIMARY KEY (id), FOREIGN KEY (dept_id) REFERENCES departments(id) | 复合主键必须在此定义 |
| 存储引擎 | 指定表的存储引擎 | ENGINE=InnoDB | MySQL 5.5+默认InnoDB |
| 字符集 | 指定表的默认字符集 | CHARACTER SET utf8mb4 | 推荐utf8mb4支持完整Unicode |
| 排序规则 | 指定字符比较规则 | COLLATE utf8mb4_general_ci | _ci表示不区分大小写 |
| 注释 | 为表或列添加描述 | COMMENT '员工基本信息表' | 有助于文档化 |
数据类型详解
MySQL支持多种数据类型，主要分为几大类：
数值类型：
- 整数：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
- 小数：FLOAT, DOUBLE, DECIMAL(精确小数)
字符串类型：
- 定长：CHAR(0-255)
- 变长：VARCHAR(0-65535)
- 文本：TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
- 二进制：BLOB系列
日期时间类型：
- DATE：YYYY-MM-DD
- TIME：HH:MM:SS
- DATETIME：YYYY-MM-DD HH:MM:SS
- TIMESTAMP：时间戳（1970-2038）
- YEAR：年份
其他类型：
- ENUM：枚举值
- SET：集合
- JSON：MySQL 5.7+支持
约束类型详解
| 约束类型 | 语法 | 描述 | 示例 |
|---|---|---|---|
| NOT NULL | col_name datatype NOT NULL | 列不能存储NULL值 | name VARCHAR(50) NOT NULL |
| UNIQUE | col_name datatype UNIQUE | 列中所有值必须不同 | email VARCHAR(100) UNIQUE |
| PRIMARY KEY | PRIMARY KEY (col1, col2) | 唯一标识表中每行 | id INT AUTO_INCREMENT PRIMARY KEY |
| FOREIGN KEY | FOREIGN KEY (col) REFERENCES table(col) | 强制引用完整性 | FOREIGN KEY (dept_id) REFERENCES departments(id) |
| CHECK | CHECK (condition) | 确保列值满足条件 | salary DECIMAL CHECK (salary > 0) |
| DEFAULT | col_name datatype DEFAULT value | 未指定值时使用默认值 | created_at TIMESTAM |
完整示例
建立一个学生表student
CREATE TABLE Student (Sno CHAR(8) PRIMARY KEY, /*列级完整性约束条件，Sno是主码*/ Sname VARCHAR(20) UNIQUE, /*sname取唯一值*/ Ssex CHAR(6), Sbirthdate Date, Smajor VARCHAR(40) )character set utf8 engine MyISAM;
建立一个员工表，这个比较复杂，涉及到约束问题，简单看一下了解一下就行，后面会针对约束问题专门开一篇
CREATE TABLE IF NOT EXISTS employees ( emp_id INT AUTO_INCREMENT COMMENT '员工ID，自动递增', first_name VARCHAR(50) NOT NULL COMMENT '名字', last_name VARCHAR(50) NOT NULL COMMENT '姓氏', email VARCHAR(100) UNIQUE COMMENT '邮箱地址，唯一', hire_date DATE NOT NULL COMMENT '入职日期', salary DECIMAL(10,2) CHECK (salary > 0) COMMENT '薪水，必须大于0', dept_id INT COMMENT '部门ID', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最后更新时间', -- 表级约束 PRIMARY KEY (emp_id), INDEX idx_name (last_name, first_name) COMMENT '姓名组合索引', CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='公司员工信息表';创建表时的注意事项
索引设计：
不要过度索引（影响写入性能）为常用查询条件创建索引考虑组合索引的顺序
关于索引和表的约束的问题，我们留在后面几篇细讲，下面出现的所有的关于索引和表的约束的问题都可以先忽略
字符集选择：
推荐utf8mb4而非utf8（完整支持Unicode包括emoji）数据库、表、列字符集保持一致
存储引擎选择：
InnoDB：支持事务、外键（MySQL 5.5+默认）MyISAM：全文本搜索，但不支持事务（MySQL 5.5前默认）MEMORY：数据存储在内存中
主键设计：
每表应有主键优先使用自增整数（简单高效）复合主键谨慎使用避免使用业务数据作为主键
数据类型选择：
选择能满足需求的最小数据类型（如用TINYINT存储0-100的值）考虑未来扩展性（如VARCHAR长度适当放大）对于字符串，VARCHAR比CHAR更节省空间（除非长度固定）金额等精确计算使用DECIMAL而非FLOAT/DOUBLE
命名规范：
使用有意义的名称（如employees而非tbl1）建议使用小写字母和下划线组合（如employee_salaries）避免使用MySQL保留字（如order, group等）保持一致性（如全部单数或复数形式）
查看表结构
show tables;通过这个我们可以查看我们当前所在的数据库中都有哪些表
desc 表名;而这个语句则能帮助我们直接查看指定的数据表的结构
修改表结构
随着需求变化，经常需要修改已有表的结构。
常用ALTER TABLE操作详解
| 操作类型 | 语法示例 | 说明 | 注意事项 | |
|---|---|---|---|---|
| 添加列 | ALTER TABLE table ADD COLUMN col_name datatype [constraints] [AFTER existing_col] | 添加新列 | 大表操作可能耗时 | |
| 修改列 | ALTER TABLE table MODIFY COLUMN col_name new_datatype [constraints] | 修改列定义 | 数据类型变更可能导致数据丢失 | |
| 重命名列 | ALTER TABLE table CHANGE COLUMN old_name new_name datatype [constraints] | 修改列名和定义 | 必须包含数据类型 | |
| 删除列 | ALTER TABLE table DROP COLUMN col_name | 删除现有列 | 不可逆操作 | |
| 添加约束 | ALTER TABLE table ADD CONSTRAINT constraint_name constraint_def | 添加主键、外键等 | 外键需确保数据一致 | |
| 删除约束 | ALTER TABLE table DROP CONSTRAINT constraint_name | 删除约束 | 主键约束名为PRIMARY | |
| 重命名表 | ALTER TABLE old_name RENAME TO new_name 或 RENAME TABLE old_name TO new_name | 修改表名 | 需更新相关视图、存储过程 | |
| 修改引擎 | ALTER TABLE table ENGINE=InnoDB | 更改存储引擎 | 可能锁表 | |
| 添加索引 | ALTER TABLE table ADD INDEX idx_name (col1, col2) | 添加普通索引 | 避免重复索引 | |
| 添加全文索引 | ALTER TABLE table ADD FULLTEXT idx_name (text_col) | 添加全文索引 | 仅MyISAM和InnoDB支持 | |
| 修改字符集 | ALTER TABLE table CONVERT TO CHARACTER SET charset COLLATE collation | 修改表字符集 | 将转换现有数据 |
修改表示例
-- 添加新列（指定位置） ALTER TABLE employees ADD COLUMN phone VARCHAR(20) NOT NULL AFTER email; -- 修改列数据类型和约束 ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2) CHECK (salary >= 0); -- 重命名列（必须指定数据类型） ALTER TABLE employees CHANGE COLUMN phone mobile_phone VARCHAR(20); -- 添加外键约束（命名约束） ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE SET NULL ON UPDATE CASCADE; -- 添加组合索引 ALTER TABLE employees ADD INDEX idx_name_department (last_name, dept_id); -- 修改表注释 ALTER TABLE employees COMMENT='公司员工基本信息表'; -- 重命名表（两种方式） ALTER TABLE employees RENAME TO staff; -- 或 RENAME TABLE employees TO staff; -- 修改存储引擎 ALTER TABLE employees ENGINE=InnoDB; -- 修改字符集（转换现有数据） ALTER TABLE employees CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;修改表时的注意事项
索引管理：
添加索引可能加快查询但减慢写入监控未使用索引组合索引顺序很重要（最左前缀原则）
测试环境验证：
所有结构变更先在测试环境验证备份重要数据（特别是生产环境）考虑使用事务（部分ALTER支持）
外键约束：
添加外键前确保引用数据已存在删除有外键引用的表需先删除约束考虑外键操作（ON DELETE/UPDATE）
数据类型变更风险：
缩小数据类型可能导致数据截断（如VARCHAR(100)改为VARCHAR(50)）某些类型转换可能不支持（如TEXT转INT）变更前检查数据兼容性
大表修改：
大表结构变更可能锁表很长时间（百万级以上）考虑使用在线DDL工具（pt-online-schema-change）在低峰期执行先创建新表再迁移数据（对于重大变更）
删除表
删除表是不可逆操作，需谨慎执行。
基本语法详解
DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name2...] [RESTRICT | CASCADE];语法各部分详细说明：
- DROP TABLE：删除表的核心关键字
- [TEMPORARY]：可选，仅删除临时表
- [IF EXISTS]：避免表不存在时报错
- table_name：要删除的表名（可多个，逗号分隔）
- [RESTRICT]：默认选项，如果有依赖对象则拒绝删除
- [CASCADE]：级联删除依赖对象（慎用）
删除表示例
-- 安全删除单个表 DROP TABLE IF EXISTS temp_users; -- 删除多个表 DROP TABLE temp_table1, temp_table2; -- 仅删除临时表 DROP TEMPORARY TABLE temp_session_data; -- 使用CASCADE强制删除（慎用） DROP TABLE departments CASCADE;
相关删除操作对比
| 操作 | 语法 | 描述 | 是否可恢复 |
|---|---|---|---|
| DROP TABLE | DROP TABLE table_name | 删除整个表结构和数据 | 不可恢复 |
| TRUNCATE TABLE | TRUNCATE TABLE table_name | 删除表中所有数据但保留结构 | 不可恢复 |
| DELETE | DELETE FROM table_name [WHERE] | 删除部分或全部数据 | 可回滚 |
删除表时的注意事项
- 备份数据：
- 删除前确认数据已备份
- 重要表考虑先重命名而非直接删除
- 使用
CREATE TABLE new_table SELECT * FROM old_table快速备份
- 依赖关系检查：
- 检查是否有视图、存储过程、触发器依赖该表
- 权限控制：
- 限制DROP TABLE权限
- 使用数据库角色管理权限
- 生产环境实施权限分离
- 替代方案：
- 考虑TRUNCATE TABLE清空数据但保留结构
- 临时表可使用CREATE TEMPORARY TABLE自动会话结束时删除
- 归档数据而非删除
- 事务考虑：
- DROP TABLE是隐式提交的操作（即使在使用事务中）
- 不能在事务中回滚DROP TABLE操作
检查外键约束
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '要删除的表名';表操作的最佳实践
设计阶段
文档化：
维护数据字典使用注释说明表和列的用途记录变更历史
命名约定：
规范化设计：
遵循适当的范式（通常3NF）平衡规范化和性能需求避免过度规范化导致的复杂连接
感谢各位大佬观看，创作不易，还望各位大佬点赞支持！！
