MySQL核心概念与SQL语句实操教程

数据库与表的基本管理

在开始数据操作前，需明确数据库与表的生命周期管理。创建数据库时应规范字符集以支持中文等多字节字符；创建表时需定义字段类型及属性。

第1步：创建并配置数据库

目的是初始化一个具备正确编码规则的数据容器。默认使用 utf8 或 utf8mb4 以防止乱码。

-- 查看当前所有数据库
SHOW DATABASES;

-- 创建指定字符集的数据库
CREATE DATABASE day15 DEFAULT CHARACTER SET utf8;

-- 查看已创建的数据库结构及字符集信息
SHOW CREATE DATABASE day15;

第2步：修改与删除数据库

注意：删除操作不可逆，且会影响该库下的所有表和数据。

-- 修改数据库默认字符集
ALTER DATABASE day15 DEFAULT CHARACTER SET gbk;

-- 慎重操作：彻底删除数据库及其包含的所有对象
DROP DATABASE day15;

第3步：进入数据库与创建表

操作前需切换至目标数据库上下文。建表时需注意字段名称的唯一性及数据类型合理性。

-- 切换并使用目标数据库
USE day15;

-- 查看所有表验证环境
SHOW TABLES;

-- 创建学生表
CREATE TABLE student(
 id INT,
 NAME VARCHAR(20),
 gender VARCHAR(2),
 age INT
);

-- 查看表结构与字段详情
DESC student;

-- 删除指定表
DROP TABLE student;

数据维护：增删改查(CRUD)

数据维护是日常开发最高频的操作。建议始终带上条件执行更新或删除，防止误操作全表数据。

第4步：插入数据(Insert)

插入时需保证值的顺序与数量与定义一致。若未指定所有字段，MySQL 会为未指定字段赋予默认值或 NULL。

-- 方式一：插入所有字段值（必须按顺序）
INSERT INTO student VALUES(1,'张三','男',20);

-- 方式二：仅插入指定字段（推荐，更安全）
INSERT INTO student(id,NAME) VALUES(2,'李四');

第5步：修改数据(Update)

不带 WHERE 子句的更新会覆盖表中所有记录，生产环境中严禁使用。修改多个字段时使用逗号分隔。

-- 【危险】无条件修改所有行的性别（极少使用）
UPDATE student SET gender='女';

-- 推荐：带精确条件的修改
UPDATE student SET gender='男' WHERE id=1;

-- 批量修改多个字段
UPDATE student SET gender='男',age=30 WHERE id=2;

第6步：删除数据(Delete vs Truncate)

理解 DELETE 与 TRUNCATE 的区别至关重要：DELETE 支持条件过滤且可回滚（属于DML操作）；TRUNCATE 用于清空全表且重置自增ID（属于DDL操作），速度更快但无法恢复。

-- 方式一：条件删除（安全，支持回滚）
DELETE FROM student WHERE id=2;

-- 方式二：清空全表并重置标识（谨慎使用）
TRUNCATE TABLE student;

第7步：查询数据(Select)

查询是灵活度最高的部分，涉及别名、常量列、去重、条件过滤及聚合统计。

-- 基础查询：指定列与别名
SELECT id AS '编号',NAME AS '姓名' FROM student;

-- 合并数值列计算（如总成绩）
SELECT id,(servlet+jsp) AS '总成绩' FROM student;

-- 去除重复记录
SELECT DISTINCT gender FROM student;

-- 常见聚合函数：求和、平均、最大/最小、计数
SELECT SUM(servlet) AS '总分', AVG(jsp) AS '平均分', COUNT(*) AS '人数'
FROM student;
-- 注意：COUNT(field) 会忽略 NULL 值，统计行数推荐使用 COUNT(*) 或非空主键

第8步：高级筛选与排序

利用 BETWEEN、LIKE 进行范围与模糊匹配；注意区分 NULL 与空字符串的处理逻辑。

-- 范围查询：包前包后
SELECT * FROM student WHERE jsp BETWEEN 75 AND 90;

-- 判空处理：NULL 用 IS NULL，空字符串用 =' '
SELECT * FROM student WHERE address IS NULL OR address='';

-- 模糊查询：%代表任意字符，_代表单个字符
SELECT * FROM student WHERE NAME LIKE '张%';

-- 多条件排序：支持正序(ASC)与倒序(DESC)
SELECT * FROM student ORDER BY servlet ASC, jsp DESC;

第9步：分页查询(Limit)

分页公式为 LIMIT (当前页-1)*每页条数, 每页条数。注意第一页的索引从0开始。

-- 第1页（显示第1、2条数据）
SELECT * FROM student LIMIT 0,2;
-- 第2页（显示第3、4条数据）
SELECT * FROM student LIMIT 2,2;

数据完整性与高级特性

合理的数据约束能保障业务逻辑的严谨性，而存储过程和触发器则用于封装复杂的数据库端逻辑。

第10步：实施数据约束

在建表阶段通过关键字设定字段行为，防止脏数据录入。

-- 允许为空，但有默认值
address VARCHAR(20) DEFAULT '广州天河'
-- 强制非空
gender VARCHAR(2) NOT NULL
-- 唯一约束
id INT UNIQUE
-- 主键约束（唯一+非空）
id INT PRIMARY KEY AUTO_INCREMENT -- 配合自增长使用

第11步：外键关联(Foreign Key)

外键用于维护多表间的一致性。操作顺序需严格遵循：先操作从表（子表），再操作主表（父表）。可结合 CASCADE 实现级联更新或删除。

-- 声明外键约束
CONSTRAINT employee_dept_fk 
FOREIGN KEY(deptId) REFERENCES dept(id) 
ON UPDATE CASCADE ON DELETE CASCADE;

第12步：多表联合查询

内连接只返回匹配成功的行；左连接保留左表所有行，右连接保留右表所有行。

-- 内连接写法
SELECT e.empName, d.deptName
FROM employee e INNER JOIN dept d
ON e.deptId = d.id;

-- 左外连接：即使部门下没有员工也会显示null
SELECT d.deptName, e.empName
FROM dept d LEFT OUTER JOIN employee e
ON d.id = e.deptId;

第13步：存储过程与变量

使用 DELIMITER 改变结束符以避免冲突。变量分为全局变量、会话变量和局部变量，注意生命周期差异。

-- 声明结束符并创建简单存储过程
DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
 SELECT * FROM employee;
END $
CALL pro_test();
DELIMITER ;

第14步：触发器自动日志记录

触发器随表数据的增、删、改自动执行，常用于审计或状态同步。

-- 每次插入新记录时，自动向日志表写入时间戳
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee 
FOR EACH ROW 
INSERT INTO test_log(content) VALUES('新增了一条员工记录');