MySQL 数据库与表核心管理

MySQL 数据库与表核心管理指南
作为一名 MySQL 初学者，整理了数据库和数据表的核心管理操作，涵盖创建、修改、约束等核心知识点，适合入门学习和日常查阅。
# 本文核心：MySQL 数据库 / 表的创建、管理、数据类型、约束设置等基础操作，附 SQL 规范和实操案例

• 一、SQL 语句基础规范
  命令不区分大小写（如CREATE和create等效）
  表名 / 库名默认区分大小写（不同系统可能有差异）
  语句必须以;结束
  查看帮助：HELP CREATE TABLE（可替换为任意关键字）
• 二、数据库核心操作

1. 核心概念
   数据库：存放数据的仓库（对应系统目录）
   数据表：仓库中的 "货架"（对应系统文件，实际存储数据）
   操作 SQL 语句 说明
   查看所有数据库 SHOW DATABASES; 列出当前实例下所有数据库
   创建数据库 CREATE DATABASE [IF NOT EXISTS] db_name; IF NOT EXISTS避免重复创建报错
   创建并指定编码 CREATE DATABASE IF NOT EXISTS test4 DEFAULT CHARACTER SET UTF8; 推荐显式指定编码，避免乱码
   查看创建语句 SHOW CREATE DATABASE test4; 查看库的实际创建配置
   加\G可垂直显示 （SHOW CREATE DATABASE test4\G;）
   使用数据库 USE db_name; 切换到目标库，后续操作默认在此库执行
   查看库内表 SHOW TABLES; 需先执行USE切换数据库
   修改字符集 ALTER DATABASE test5 DEFAULT CHARACTER SET UTF8; 调整已有库的默认编码
   删除数据库 DROP DATABASE IF EXISTS test5; IF EXISTS避免删除不存在的库报错
2. 扩展
   SQL 脚本执行：可将 SQL 语句写入*.sql文件，通过客户端执行
   MySQL 不支持直接修改数据库名（需间接实现）

• 三、数据表核心管理

1. 核心概念
   列（字段）：表的纵向维度，定义数据类型和含义（如name列存字符串）
   行：表的横向维度，代表一条完整数据记录（需符合列的约束）
2. 数据类型
   （1）整型（按字节 / 范围划分）
   类型 字节 范围 常用属性
   TINYINT 1 -128~127 UNSIGNED（无符号：0~255）、ZEROFILL（0 填充）
   SMALLINT 2 -32768~32767 同上
   MEDIUMINT 3 更大范围 同上
   INT 4 常用整型 同上
   BIGINT 8 超大整数（如主键自增） 同上
   ****示例：
   CREATE TABLE test_int(
   age TINYINT UNSIGNED, -- 无符号，只能存0-255
   num1 INT(10) ZEROFILL, -- 总长度10，不足补0
   num2 INT(10) UNSIGNED ZEROFILL -- 无符号+0填充
   );
   （2）浮点型
   类型 字节 特点 格式说明
   FLOAT 4 单精度浮点数 FLOAT (总长度，小数位) 如 FLOAT (10,2)
   DOUBLE 8 双精度浮点数 精度更高，用法同上
   DECIMAL - 定点数（精准） 适合金额等高精度场景
   （3）字符串类型
   类型 特点 适用场景
   CHAR(N) 固定长度 手机号、身份证、学号（长度固定）
   VARCHAR(N) 可变长度 标题、昵称（长度不固定）
   TEXT 大文本存储 文章内容、备注
   JSON 轻量级数据交换 结构化文本数据
   核心区别：CHAR 占用固定空间，查询快；VARCHAR 按实际长度存储，节省空间。
   （4）日期时间类型
   类型 字节 格式 特点
   DATE 3 yyyy-MM-dd 仅日期
   TIME 3 HH:mm:ss 仅时间
   DATETIME 8 yyyy-MM-dd HH:mm:ss 与时区无关
   TIMESTAMP 4 yyyy-MM-dd HH:mm:ss 与时区相关，范围小
   YEAR 1 yyyy 仅年份
   （5）枚举 / 集合类型
   枚举（ENUM）：多选一（如性别：男 / 女）
   集合（SET）：多选多（如爱好：读书 / 运动 / 音乐）
3. 表的增删改查
   （1）创建表
   sql
   CREATE TABLE [IF NOT EXISTS] table_name(
   列名 数据类型 [约束],
   列名 数据类型 [约束]
   ) [DEFAULT CHARACTER SET 编码];
   （2）插入数据
   sql
   -- 插入单行（按列顺序）
   INSERT INTO test_int VALUES (1, 1, 1);
   -- 插入多行
   INSERT INTO test_int VALUES (1,1,1), (2,2,2);
   -- 指定字段插入（推荐）
   INSERT INTO test_int (age, num1) VALUES (10, 10);
   （3）查询数据
   sql
   -- 查询所有列
   SELECT * FROM test_int;
   -- 查询指定列
   SELECT age, num1 FROM test_int;
   （4）删除表
   sql
   DROP TABLE [IF EXISTS] table_name;
   （5）复制表
   sql
   -- 仅复制表结构
   CREATE TABLE new_classes LIKE classes;
   -- 复制结构+数据
   CREATE TABLE new_classes2 AS SELECT * FROM classes;
4. 修改表结构（ALTER TABLE）
   操作 SQL 语句 示例
   新增列 ALTER TABLE 表名 ADD 列名 类型 [FIRST/AFTER 列名]; ALTER TABLE classes ADD col3 int AFTER class_teacher;
   删除列 ALTER TABLE 表名 DROP COLUMN 列名; ALTER TABLE classes DROP COLUMN col1;
   修改列类型 ALTER TABLE 表名 MODIFY 列名 新类型; ALTER TABLE classes MODIFY col2 char(10);
   修改列名 ALTER TABLE 表名 CHANGE 旧列名 新列名 类型; ALTER TABLE classes CHANGE col3 col03 int;
   修改表名 ALTER TABLE 表名 RENAME TO 新表名; ALTER TABLE classes RENAME TO class;
   添加约束 ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型(列名); ALTER TABLE tb ADD CONSTRAINT pk_id PRIMARY KEY (id);
   四、约束（保证数据完整性）
5. 核心约束类型
   约束 关键字 作用 示例
   非空 NOT NULL 列值不能为 NULL name varchar(4) NOT NULL
   默认值 DEFAULT 未赋值时用默认值 status enum('disable','enable') DEFAULT 'enable'
   唯一 UNIQUE 列值唯一（允许 NULL） username varchar(5) UNIQUE
   主键 PRIMARY KEY 非空 + 唯一（表的唯一标识） id INT AUTO_INCREMENT PRIMARY KEY
   外键 FOREIGN KEY 表间关联（保证数据一致性） 见下文示例
6. 外键约束（表间关系）
   sql
   -- 先创建主表
   CREATE TABLE classes (
   class_id VARCHAR(10) PRIMARY KEY,
   class_teacher VARCHAR(5),
   class_room VARCHAR(20)
   );

-- 从表关联主表（外键）
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
class_id VARCHAR(10),
FOREIGN KEY (class_id) REFERENCES classes(class_id)
-- 可选：ON DELETE CASCADE（级联删除）/ SET NULL（置空）
);
外键优缺点：
优点：保证数据一致性、减少冗余、便于维护
缺点：降低性能（每次操作需检查约束）
企业实践：通常不在数据库层用外键，而是在代码层实现约束
五、实用技巧

1. MySQL 模式切换
   严格模式（默认）：不符合约束直接报错
   宽松模式：自动修正不符合约束的数据
   临时切换宽松模式：
   sql
   SET sql_mode = 'NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE';
2. 排错思路
   不要无视错误提示，重点看near后的位置（错误起始点）
   查看系统日志（MySQL 日志、操作系统日志）
   检查基础环境：
   bash
   运行
   # 临时关闭SELinux
   setenforce 0
   # 关闭防火墙（测试环境）
   systemctl stop firewalld
   systemctl disable firewalld
   总结
   MySQL 核心操作：库 / 表的创建、修改、删除，需遵循 SQL 规范，显式指定编码避免乱码；
   数据类型选择：根据业务场景选合适类型（整型 / 浮点 / 字符串 / 日期），兼顾性能和存储空间；
   约束使用：主键保证唯一性，外键保证表间一致性（企业中常用代码层替代外键），非空 / 唯一 / 默认值约束保证数据完整性。
   # 学习建议：实操是掌握 MySQL 的核心，遇到错误先分析提示信息，逐步培养独立排错能力 #
   六、思维导图