【MySQL基础】MySQL表操作全面指南：从创建到管理的深度解析_mysql 怎么移除列character

技术文档

MySQL学习：

https://blog.csdn.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_ciCOMMENT=\'公司员工信息表\';

创建表时的注意事项

命名规范：
- 使用有意义的名称（如employees而非tbl1）
- 建议使用小写字母和下划线组合（如employee_salaries）
- 避免使用MySQL保留字（如order, group等）
- 保持一致性（如全部单数或复数形式）
数据类型选择：
- 选择能满足需求的最小数据类型（如用TINYINT存储0-100的值）
- 考虑未来扩展性（如VARCHAR长度适当放大）
- 对于字符串，VARCHAR比CHAR更节省空间（除非长度固定）
- 金额等精确计算使用DECIMAL而非FLOAT/DOUBLE
主键设计：
- 每表应有主键
- 优先使用自增整数（简单高效）
- 复合主键谨慎使用
- 避免使用业务数据作为主键
存储引擎选择：
- InnoDB：支持事务、外键（MySQL 5.5+默认）
- MyISAM：全文本搜索，但不支持事务（MySQL 5.5前默认）
- MEMORY：数据存储在内存中
字符集选择：
- 推荐utf8mb4而非utf8（完整支持Unicode包括emoji）
- 数据库、表、列字符集保持一致
索引设计：
- 不要过度索引（影响写入性能）
- 为常用查询条件创建索引
- 考虑组合索引的顺序
关于索引和表的约束的问题，我们留在后面几篇细讲，下面出现的所有的关于索引和表的约束的问题都可以先忽略

查看表结构

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;

修改表时的注意事项

大表修改：
- 大表结构变更可能锁表很长时间（百万级以上）
- 考虑使用在线DDL工具（pt-online-schema-change）
- 在低峰期执行
- 先创建新表再迁移数据（对于重大变更）
数据类型变更风险：
- 缩小数据类型可能导致数据截断（如VARCHAR(100)改为VARCHAR(50)）
- 某些类型转换可能不支持（如TEXT转INT）
- 变更前检查数据兼容性
外键约束：
- 添加外键前确保引用数据已存在
- 删除有外键引用的表需先删除约束
- 考虑外键操作（ON DELETE/UPDATE）
测试环境验证：
- 所有结构变更先在测试环境验证
- 备份重要数据（特别是生产环境）
- 考虑使用事务（部分ALTER支持）
索引管理：
- 添加索引可能加快查询但减慢写入
- 监控未使用索引
- 组合索引顺序很重要（最左前缀原则）

删除表

删除表是不可逆操作，需谨慎执行。

基本语法详解

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;

删除表时的注意事项

备份数据：
- 删除前确认数据已备份
- 重要表考虑先重命名而非直接删除
- 使用CREATE TABLE new_table SELECT * FROM old_table快速备份
依赖关系检查：
- 检查是否有视图、存储过程、触发器依赖该表
- 检查外键约束
```
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAMEFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE REFERENCED_TABLE_NAME = \'要删除的表名\';
```
- 权限控制：
  - 限制DROP TABLE权限
  - 使用数据库角色管理权限
  - 生产环境实施权限分离
- 替代方案：
  - 考虑TRUNCATE TABLE清空数据但保留结构
  - 临时表可使用CREATE TEMPORARY TABLE自动会话结束时删除
  - 归档数据而非删除
- 事务考虑：
  - DROP TABLE是隐式提交的操作（即使在使用事务中）
  - 不能在事务中回滚DROP TABLE操作

表操作的最佳实践

设计阶段

规范化设计：
- 遵循适当的范式（通常3NF）
- 平衡规范化和性能需求
- 避免过度规范化导致的复杂连接
命名约定：
文档化：
- 维护数据字典
- 使用注释说明表和列的用途
- 记录变更历史

感谢各位大佬观看，创作不易，还望各位大佬点赞支持！！

【MySQL基础】MySQL表操作全面指南：从创建到管理的深度解析_mysql 怎么移除列character

表的基本概念

表的主要组成部分

创建表

基本语法

关键参数说明

数据类型详解

约束类型详解

完整示例

创建表时的注意事项

查看表结构

修改表结构

常用ALTER TABLE操作详解

修改表示例

修改表时的注意事项

删除表

基本语法详解

删除表示例

相关删除操作对比

删除表时的注意事项

表操作的最佳实践

设计阶段

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签

【MySQL基础】MySQL表操作全面指南：从创建到管理的深度解析_mysql 怎么移除列character

表的基本概念

表的主要组成部分

创建表

基本语法

关键参数说明

数据类型详解

约束类型详解

完整示例

创建表时的注意事项

查看表结构

修改表结构

常用ALTER TABLE操作详解

修改表示例

修改表时的注意事项

删除表

基本语法详解

删除表示例

相关删除操作对比

删除表时的注意事项

表操作的最佳实践

设计阶段

相关问题

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签