MySQL基础操作与查询优化

1. 概述

1.1 MySQL基础操作的重要性

MySQL基础操作是数据库管理的核心技能，涵盖了表的创建、修改、删除，字段的增删改查，约束的管理，以及复杂查询的实现。掌握这些基础操作对于数据库开发、运维和优化至关重要。

基础操作的价值：

数据管理：高效管理数据库结构和数据
性能优化：通过合理的表设计和查询优化提升性能
数据安全：通过约束保证数据完整性
运维效率：快速定位和解决数据库问题

1.2 本文内容结构

本文将从以下几个方面详细介绍MySQL基础操作：

表的操作：创建、修改、删除表
字段操作：字段的添加、修改、删除
约束管理：主键、唯一、外键等约束
数据操作：增删改查（CRUD）
查询优化：DQL查询语句和优化技巧
函数使用：数学、字符、日期、聚合函数
多表查询：内连接、外连接、子查询

2. 表的操作

2.1 创建表

2.1.1 基本语法

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
) [ENGINE=engine] [CHARSET=charset] [COMMENT='comment'];

2.1.2 创建学生表示例

-- 创建一张学生表
CREATE TABLE student (
    sid INT,
    sname VARCHAR(20)
);

说明：

sid：学生ID，整数类型
sname：学生姓名，可变字符串，最大长度20

2.1.3 创建表的最佳实践

-- 推荐的表创建方式
CREATE TABLE student (
    sid INT NOT NULL AUTO_INCREMENT COMMENT '学生ID',
    sname VARCHAR(20) NOT NULL COMMENT '学生姓名',
    sex TINYINT(1) DEFAULT 1 COMMENT '性别：1-男，0-女',
    birthday DATE COMMENT '生日',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (sid),
    INDEX idx_sname (sname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

最佳实践要点：

使用NOT NULL约束保证数据完整性
使用AUTO_INCREMENT实现主键自增
添加COMMENT注释说明字段含义
使用InnoDB引擎支持事务
使用utf8mb4字符集支持emoji
添加必要的索引提升查询性能
添加时间戳字段便于数据追踪

2.2 查看表结构

2.2.1 查看表结构

-- 查看表结构（推荐）
DESC student;

-- 或者
DESCRIBE student;

-- 查看建表语句
SHOW CREATE TABLE student;

DESC输出示例：

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sid      | int(11)     | NO   | PRI | NULL    |       |
| sname    | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

字段说明：

Field：字段名
Type：数据类型
Null：是否允许NULL
Key：索引类型（PRI-主键，UNI-唯一，MUL-普通索引）
Default：默认值
Extra：额外信息（AUTO_INCREMENT等）

2.2.2 查看所有表

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

-- 查看表状态
SHOW TABLE STATUS LIKE 'student';

2.3 修改表名

2.3.1 修改表名方法

-- 方法1：使用RENAME TABLE
RENAME TABLE student TO stu;

-- 方法2：使用ALTER TABLE
ALTER TABLE stu RENAME TO student;

注意事项：

修改表名需要相应权限
如果有视图或存储过程引用该表，需要同步修改
建议在业务低峰期操作

2.4 修改表注释

1 2	-- 修改表注释 ALTER TABLE student COMMENT '学生信息表';

2.5 清空表

2.5.1 TRUNCATE清空表

1 2	-- 清空表数据（推荐） TRUNCATE TABLE student;

TRUNCATE特点：

快速清空表数据
重置AUTO_INCREMENT计数器
不记录日志，无法回滚
释放表空间

2.5.2 DELETE清空表

1 2	-- 清空表数据（不推荐） DELETE FROM student;

DELETE特点：

逐行删除，速度较慢
记录日志，可以回滚
不重置AUTO_INCREMENT
不释放表空间

运维建议：

清空表数据优先使用TRUNCATE
需要回滚时使用DELETE
大表清空建议在业务低峰期操作

2.6 删除表

1 2	-- 删除表（谨慎操作） DROP TABLE student;

DROP TABLE特点：

删除表结构和数据
无法恢复
会删除相关索引、约束等

运维建议：

删除前先备份数据
确认无业务依赖
建议先重命名表，观察一段时间后再删除

3. 字段操作

3.1 添加字段

3.1.1 追加字段

1 2	-- 在表末尾追加字段 ALTER TABLE student ADD sex BIT(1);

3.1.2 在指定位置添加字段

-- 在最前面添加字段
ALTER TABLE student ADD email VARCHAR(20) FIRST;

-- 在指定字段后添加字段
ALTER TABLE student ADD birthday DATE AFTER sid;

运维建议：

添加字段建议在业务低峰期操作
大表添加字段可能锁表，考虑使用pt-online-schema-change
添加字段时指定默认值，避免影响现有数据

在线添加字段（大表）：

# 使用pt-online-schema-change工具
pt-online-schema-change \
    --alter "ADD COLUMN email VARCHAR(20) DEFAULT ''" \
    --execute \
    D=testdb,t=student

3.2 修改字段

3.2.1 修改字段名和类型

-- 修改字段名和类型（CHANGE）
ALTER TABLE student CHANGE email semail VARCHAR(20) AFTER sid;

-- 只修改字段类型（MODIFY）
ALTER TABLE student MODIFY semail VARCHAR(50);

CHANGE vs MODIFY：

CHANGE：可以修改字段名和类型
MODIFY：只能修改字段类型和属性

3.2.2 修改字段最佳实践

1
2
3

-- 修改字段类型和属性
ALTER TABLE student 
MODIFY semail VARCHAR(100) NOT NULL DEFAULT '' COMMENT '学生邮箱';

注意事项：

修改字段类型可能丢失数据，需要谨慎
扩大字段长度通常安全，缩小可能截断数据
修改字段属性建议在业务低峰期操作

3.3 删除字段

1 2	-- 删除字段 ALTER TABLE student DROP semail;

注意事项：

删除字段会永久删除数据，无法恢复
删除前确认无业务依赖
大表删除字段可能锁表

4. 约束管理

4.1 主键约束（PRIMARY KEY）

4.1.1 创建表时添加主键

方式1：字段级主键

CREATE TABLE student (
    sid INT PRIMARY KEY,
    sname VARCHAR(20),
    birthday DATE
);

方式2：表级主键

CREATE TABLE student (
    sid INT,
    sname VARCHAR(20),
    birthday DATE,
    PRIMARY KEY (sid)
);

4.1.2 联合主键

CREATE TABLE student (
    sid INT,
    sname VARCHAR(20),
    birthday DATE,
    PRIMARY KEY (sid, sname)
);

联合主键说明：

多个字段组合作为主键
所有主键字段的组合必须唯一
适用于多对多关系表

4.1.3 表已存在时添加主键

-- 添加单字段主键
ALTER TABLE student ADD CONSTRAINT PK_SID PRIMARY KEY (sid);

-- 添加联合主键
ALTER TABLE student ADD CONSTRAINT PK_SID_SNAME PRIMARY KEY (sid, sname);

注意事项：

添加主键前确保字段值唯一且非空
大表添加主键可能锁表，建议使用在线工具

4.1.4 删除主键

1 2	-- 删除主键 ALTER TABLE student DROP PRIMARY KEY;

注意事项：

删除主键前需要先删除自增属性
如果有外键引用，需要先删除外键

4.2 唯一约束（UNIQUE）

4.2.1 创建表时添加唯一约束

方式1：字段级唯一约束

CREATE TABLE student (
    sid INT PRIMARY KEY,
    sname VARCHAR(20) UNIQUE,
    birthday DATE
);

方式2：表级唯一约束

CREATE TABLE student (
    sid INT PRIMARY KEY,
    sname VARCHAR(20),
    birthday DATE,
    UNIQUE (sname)
);

4.2.2 表已存在时添加唯一约束

1 2	-- 添加唯一约束 ALTER TABLE student ADD CONSTRAINT UN_SNAME UNIQUE (sname);

4.2.3 删除唯一约束

1 2	-- 删除唯一约束 DROP INDEX UN_SNAME ON student;

说明：唯一约束会自动创建索引，删除时使用DROP INDEX

4.3 主键自增（AUTO_INCREMENT）

CREATE TABLE student (
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(20),
    birthday DATE,
    UNIQUE (sname)
);

AUTO_INCREMENT特点：

自动递增，无需手动指定值
通常与主键配合使用
删除记录后，自增值不会回退

运维建议：

使用AUTO_INCREMENT简化主键管理
注意自增值的上限（INT：2^31-1，BIGINT：2^63-1）
避免手动指定自增字段值，可能导致冲突

4.4 域完整性约束

4.4.1 非空约束（NOT NULL）

CREATE TABLE userinfo (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    sex BIT(1) DEFAULT 1
);

4.4.2 默认值（DEFAULT）

CREATE TABLE userinfo (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    sex BIT(1) DEFAULT 1,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

域完整性说明：

类型约束：通过数据类型限制
非空约束：保证字段必须有值
默认值：未指定值时使用默认值

4.5 外键约束（FOREIGN KEY）

4.5.1 创建外键约束

创建主表（班级表）：

CREATE TABLE classroom (
    cid INT NOT NULL,
    cname VARCHAR(25) NOT NULL,
    PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建从表（学生表）：

CREATE TABLE student (
    sid INT NOT NULL AUTO_INCREMENT,
    sname VARCHAR(20) DEFAULT NULL,
    cid INT DEFAULT NULL,
    PRIMARY KEY (sid),
    UNIQUE KEY sname (sname),
    KEY FK_CID (cid)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

添加外键约束：

-- 添加外键
ALTER TABLE student 
ADD CONSTRAINT FK_CID 
FOREIGN KEY (cid) REFERENCES classroom (cid);

4.5.2 外键约束选项

-- 添加外键，指定级联操作
ALTER TABLE student 
ADD CONSTRAINT FK_CID 
FOREIGN KEY (cid) REFERENCES classroom (cid)
ON DELETE CASCADE      -- 删除时级联
ON UPDATE CASCADE;     -- 更新时级联

外键选项：

ON DELETE CASCADE：删除主表记录时，自动删除从表相关记录
ON DELETE SET NULL：删除主表记录时，从表外键字段设为NULL
ON DELETE RESTRICT：禁止删除主表记录（默认）
ON UPDATE CASCADE：更新主表记录时，自动更新从表外键

4.5.3 删除外键

1 2	-- 删除外键 ALTER TABLE student DROP FOREIGN KEY FK_CID;

运维建议：

外键约束保证数据完整性，但影响性能
高并发场景建议在应用层保证数据完整性
删除外键前确认无业务依赖

5. 数据操作（增删改查）

5.1 查看建表语句

1 2	-- 查看建表语句 SHOW CREATE TABLE student;

输出示例：

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  UNIQUE KEY `sname` (`sname`),
  KEY `FK_CID` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

5.2 插入数据（INSERT）

5.2.1 插入所有字段

1 2	-- 插入所有字段（值的顺序和表的字段顺序一致） INSERT INTO userinfo VALUES (1, 'ls', 1);

5.2.2 插入部分字段

1 2	-- 插入部分字段（值顺序和前面字段顺序一致） INSERT INTO userinfo (username, sex) VALUES ('ww', 1);

5.2.3 批量插入

-- 批量插入多条记录
INSERT INTO userinfo (username, sex) 
VALUES 
    ('oo', 1),
    ('pp', 1),
    ('tt', 0);

批量插入优势：

减少网络往返次数
提高插入性能
建议批量插入1000-5000条记录

5.2.4 插入查询结果

1 2	-- 复制表数据 INSERT INTO user SELECT * FROM userinfo;

运维建议：

大批量插入建议使用LOAD DATA INFILE
插入前检查数据完整性
使用事务保证数据一致性

5.3 更新数据（UPDATE）

-- 更新单条记录
UPDATE userinfo SET sex = 0 WHERE username = 'ww';

-- 更新多条记录
UPDATE userinfo SET sex = 1 WHERE uid = 5;

UPDATE最佳实践：

-- 1. 更新前先查询确认
SELECT * FROM userinfo WHERE username = 'ww';

-- 2. 使用事务
START TRANSACTION;
UPDATE userinfo SET sex = 0 WHERE username = 'ww';
-- 确认无误后提交
COMMIT;

-- 3. 限制更新范围
UPDATE userinfo SET sex = 1 WHERE uid = 5 LIMIT 1;

注意事项：

更新前务必使用WHERE条件，避免误更新
大表更新建议分批进行
更新操作会加锁，注意对业务的影响

5.4 删除数据（DELETE）

-- 删除指定记录
DELETE FROM userinfo WHERE uid = 5;

-- 删除所有记录（不推荐）
DELETE FROM user;

DELETE最佳实践：

-- 1. 删除前先查询确认
SELECT * FROM userinfo WHERE uid = 5;

-- 2. 使用事务
START TRANSACTION;
DELETE FROM userinfo WHERE uid = 5;
-- 确认无误后提交
COMMIT;

-- 3. 限制删除范围
DELETE FROM userinfo WHERE uid = 5 LIMIT 1;

5.5 DROP、DELETE、TRUNCATE区别

操作	说明	特点	使用场景
DROP	删除表及数据	删除表结构和数据，无法恢复	不再需要的表
DELETE	逐行删除	记录日志，可以回滚，不重置自增	删除部分数据，需要回滚
TRUNCATE	清空表数据	快速清空，重置自增，不记录日志	清空表数据，不需要回滚

详细对比：

-- DROP：删除表及数据
DROP TABLE userinfo;

-- DELETE：逐行删除，不重置自增
DELETE FROM userinfo;  -- 清空表数据

-- TRUNCATE：清空表数据，重置自增
TRUNCATE TABLE userinfo;  -- 清空表数据

运维建议：

清空表数据优先使用TRUNCATE
需要回滚时使用DELETE
删除表使用DROP，需谨慎操作

6. 查询语句（DQL）

6.1 DQL语法结构

SELECT [DISTINCT] 字段列表
FROM 表名
[WHERE 条件]
[GROUP BY 分组字段]
[HAVING 分组后条件]
[ORDER BY 排序字段 [ASC|DESC]]
[LIMIT [offset,] rows];

子句说明：

SELECT：选择要查询的字段
FROM：指定数据来源
WHERE：过滤条件
GROUP BY：分组
HAVING：分组后过滤
ORDER BY：排序
LIMIT：限制结果数量

6.2 基础查询

6.2.1 查询所有字段

1 2	-- 查询所有字段 SELECT * FROM emp;

注意：生产环境不建议使用*，明确指定字段名

6.2.2 查询部分字段

1 2	-- 查询员工的编号和名称 SELECT empno, ename FROM emp;

6.3 条件查询（WHERE）

6.3.1 单一条件查询

1 2	-- 查询20号部门的员工信息 SELECT * FROM emp WHERE deptno = 20;

6.3.2 组合条件查询

-- 查询20号部门工资大于2000的员工信息
SELECT * FROM emp WHERE deptno = 20 AND sal > 2000;

-- 查询员工编号为7788，7521，7369的员工信息
SELECT * FROM emp 
WHERE empno = 7788 OR empno = 7521 OR empno = 7369;

6.3.3 范围查询

-- 查询员工工资在1000到2000之间的员工信息
SELECT * FROM emp WHERE sal >= 1000 AND sal <= 2000;

-- 使用BETWEEN（包含边界值）
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000;

BETWEEN说明：

BETWEEN包含边界值，等价于>= AND <=
适用于数值和日期类型

6.3.4 集合查询（IN）

1 2	-- 查询员工编号为7788，7521，7369的员工信息 SELECT * FROM emp WHERE empno IN (7788, 7521, 7369);

IN vs OR：

IN更简洁，性能通常更好
IN适用于值列表较多的情况

6.3.5 空值判断

-- 查询没有奖金的员工信息
SELECT * FROM emp WHERE comm IS NULL;

-- 查询有奖金的员工信息
SELECT * FROM emp WHERE comm IS NOT NULL;

注意事项：

不能使用= NULL或!= NULL判断空值
必须使用IS NULL或IS NOT NULL

6.4 别名（ALIAS）

-- 字段别名
SELECT ename AS 姓名, sal AS 工资 FROM emp;

-- 表达式别名
SELECT ename, sal * 1.05 AS 涨薪后工资 FROM emp;

-- 表别名
SELECT e.ename FROM emp e;

别名说明：

AS关键字可以省略
别名用于简化字段名或表达式
表别名用于多表查询

6.5 去重（DISTINCT）

-- 查询所有的职位（去重）
SELECT DISTINCT job FROM emp;

-- 多字段去重
SELECT DISTINCT deptno, job FROM emp;

DISTINCT说明：

去除重复记录
对NULL值也进行去重
多字段时，所有字段组合唯一才算重复

6.6 排序（ORDER BY）

-- 查询员工工资排序-升序（默认）
SELECT * FROM emp ORDER BY sal;

-- 查询员工工资排序-升序（显式）
SELECT * FROM emp ORDER BY sal ASC;

-- 查询员工工资排序-降序
SELECT * FROM emp ORDER BY sal DESC;

-- 多字段排序
SELECT * FROM emp ORDER BY sal DESC, empno DESC;

排序说明：

ASC：升序（默认）
DESC：降序
多字段排序时，先按第一个字段排序，相同值再按第二个字段排序

性能优化：

排序字段建议建立索引
避免对大结果集排序
使用LIMIT限制排序结果数量

6.7 模糊查询（LIKE）

-- 查询名字以S开头的员工信息
SELECT * FROM emp WHERE ename LIKE 'S%';

-- 查询名字以S结尾的员工信息
SELECT * FROM emp WHERE ename LIKE '%S';

-- 查询名字包含S的员工信息
SELECT * FROM emp WHERE ename LIKE '%S%';

-- 查询第二位为L的员工信息
SELECT * FROM emp WHERE ename LIKE '_L%';

通配符说明：

%：匹配0个或多个字符
_：匹配1个字符

性能优化：

避免%xxx%这种前后都有通配符的查询
尽量使用xxx%这种前缀匹配
大表模糊查询建议使用全文索引

6.8 限制结果（LIMIT）

-- 查询前5条记录
SELECT * FROM emp LIMIT 5;

-- 查询第2条到第6条记录（跳过1条，取5条）
SELECT * FROM emp LIMIT 1, 5;

-- 分页查询（第1页，每页10条）
SELECT * FROM emp LIMIT 0, 10;

-- 分页查询（第2页，每页10条）
SELECT * FROM emp LIMIT 10, 10;

LIMIT语法：

LIMIT n：取前n条记录
LIMIT offset, count：跳过offset条，取count条记录

分页优化：

避免大offset，使用索引字段定位
使用覆盖索引减少回表
考虑使用游标分页

7. 运算符

7.1 算术运算符

SELECT 1 + 1;      -- 2
SELECT 1 - 1;      -- 0
SELECT 1 * 1;      -- 1
SELECT 5 / 2;      -- 2.5000
SELECT 5 % 2;      -- 1（取余）
SELECT 3 / 0;      -- NULL（除零返回NULL）
SELECT 5 DIV 2;   -- 2（取整）

7.2 比较运算符

-- 结果：true --> 1，false --> 0
SELECT 1 = 1;      -- 1
SELECT 1 != 1;     -- 0
SELECT 1 <> 1;     -- 0
SELECT 1 > 1;      -- 0
SELECT 1 < 1;      -- 0
SELECT 1 >= 1;     -- 1
SELECT 1 <= 1;     -- 1

7.3 逻辑运算符

-- 结果：true --> 1，false --> 0
SELECT 1 > 2 AND 1 < 2;   -- 0
SELECT 1 > 2 OR 1 < 2;    -- 1
SELECT !(1 > 2);           -- 1
SELECT NOT (1 > 2);        -- 1

7.4 位运算符

-- 按位与：有一侧为0结果为0
SELECT 3 & 2;      -- 2

-- 按位或：有一侧为1结果为1
SELECT 3 | 2;      -- 3

-- 按位异或：两侧数据一样取0，不一样取1
SELECT 3 ^ 2;      -- 1

7.5 查看字符编码

1 2	-- 查看字符编码相关配置 SHOW VARIABLES LIKE '%char%';

输出示例：

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

字符集说明：

character_set_client：客户端字符集
character_set_connection：连接字符集
character_set_database：数据库字符集
character_set_results：结果字符集
character_set_server：服务器字符集

8. 函数

8.1 数学函数

-- 绝对值
SELECT ABS(-10);           -- 10

-- 向上取整
SELECT CEIL(10.5);         -- 11

-- 向下取整
SELECT FLOOR(10.5);        -- 10

-- 随机数（0-1之间）
SELECT RAND();             -- 0.123456

-- 四舍五入
SELECT ROUND(12.46);       -- 12
SELECT ROUND(12.46, 1);    -- 12.5

-- 幂运算
SELECT POW(2, 3);          -- 8

-- 平方根
SELECT SQRT(16);           -- 4

8.2 字符函数

-- 获取字符串长度（字节数）
SELECT LENGTH('abc');              -- 3
SELECT LENGTH('中文');             -- 6（UTF8）

-- 获取字符串长度（字符数）
SELECT CHAR_LENGTH('中文');        -- 2

-- 连接字符串
SELECT CONCAT('Hello', ' ', 'World');  -- 'Hello World'

-- 截取字符串
SELECT SUBSTRING('Hello World', 1, 5); -- 'Hello'

-- 转大写
SELECT UPPER('hello');             -- 'HELLO'

-- 转小写
SELECT LOWER('HELLO');             -- 'hello'

-- 去除空格
SELECT TRIM('  hello  ');          -- 'hello'
SELECT LTRIM('  hello');           -- 'hello'
SELECT RTRIM('hello  ');           -- 'hello'

-- 替换
SELECT REPLACE('Hello World', 'World', 'MySQL');  -- 'Hello MySQL'

8.3 日期函数

-- 获取当前日期时间
SELECT NOW();                      -- 2020-12-24 13:45:22

-- 获取当前日期
SELECT CURRENT_DATE();             -- 2020-12-24
SELECT CURDATE();                  -- 2020-12-24

-- 获取当前时间
SELECT CURRENT_TIME();             -- 13:47:05
SELECT CURTIME();                  -- 13:47:05

-- 获取当前时间戳
SELECT CURRENT_TIMESTAMP();        -- 2020-12-24 13:47:05

-- 获取年份
SELECT YEAR(NOW());                -- 2020
SELECT YEAR('2018-08-08');         -- 2018

-- 获取月份
SELECT MONTH(NOW());               -- 12

-- 获取日期
SELECT DAY(NOW());                 -- 24

-- 获取星期
SELECT DAYOFWEEK(NOW());           -- 1-7（周日=1）

-- 获取一年中的第几天
SELECT DAYOFYEAR(NOW());           -- 359

8.4 日期计算函数

-- 日期加减
SELECT DATE_ADD(NOW(), INTERVAL 2 WEEK);      -- 两周后
SELECT DATE_ADD(NOW(), INTERVAL -2 WEEK);    -- 两周前
SELECT DATE_ADD('2019-03-02', INTERVAL 5 DAY); -- 5天后

-- 日期减法
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);     -- 一个月前

-- 日期差值（天数）
SELECT DATEDIFF('2020-12-24', '2020-12-01'); -- 23

-- 获取月份最后一天
SELECT LAST_DAY('2016-02-03');                -- 2016-02-29

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- '2020-12-24 13:45:22'

日期格式化说明：

%Y：4位年份
%y：2位年份
%m：月份（01-12）
%d：日期（01-31）
%H：小时（00-23）
%i：分钟（00-59）
%s：秒（00-59）

8.5 聚合函数

-- 最大值
SELECT MAX(sal) FROM emp;

-- 最小值
SELECT MIN(sal) FROM emp;

-- 平均值
SELECT AVG(sal) FROM emp;

-- 求和
SELECT SUM(sal) FROM emp;

-- 计数
SELECT COUNT(*) FROM emp;          -- 总记录数
SELECT COUNT(1) FROM emp;          -- 总记录数（推荐）
SELECT COUNT(comm) FROM emp;      -- 非空记录数

COUNT说明：

COUNT(*)：统计所有记录数（包括NULL）
COUNT(1)：统计所有记录数（推荐，性能更好）
COUNT(字段)：统计非NULL记录数

8.6 分组函数（GROUP BY）

-- 每个部门的平均工资
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;

-- 平均工资大于2000的部门编号及平均工资
SELECT deptno, AVG(sal) AS avg_sal 
FROM emp 
GROUP BY deptno 
HAVING avg_sal >= 2000;

GROUP BY说明：

GROUP BY：根据字段值分组
HAVING：分组后过滤（类似WHERE，但用于聚合函数）
SELECT中只能出现分组字段和聚合函数

WHERE vs HAVING：

WHERE：分组前过滤，不能使用聚合函数
HAVING：分组后过滤，可以使用聚合函数

8.7 加密函数

-- MD5加密
SELECT MD5('root');                -- 63a9f0ea7bb98050796b649e85481845

-- SHA1加密
SELECT SHA1('root');               -- 更长的哈希值

-- SHA2加密
SELECT SHA2('root', 256);          -- SHA256加密

加密函数说明：

MD5：128位哈希值，已不安全
SHA1：160位哈希值，已不安全
SHA2：更安全的加密算法

8.8 流程函数

8.8.1 IF函数

1 2	-- 如果expr1是真，返回expr2，否则返回expr3 SELECT IF(1 > 2, 1, 0); -- 0

8.8.2 IFNULL函数

1 2	-- 如果expr1不是NULL，返回expr1，否则返回expr2 SELECT IFNULL(comm, 0) FROM emp;

8.8.3 CASE函数

方式1：CASE WHEN

-- 根据分数评级
SELECT sname,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score < 90 AND score >= 70 THEN 'B'
        ELSE 'C'
    END AS level
FROM student;

方式2：CASE表达式

-- 根据部门编号返回部门名称
SELECT ename,
    CASE deptno
        WHEN 10 THEN '财务部'
        WHEN 20 THEN '研发部'
        WHEN 30 THEN '销售部'
        ELSE '其他'
    END AS dept_name
FROM emp;

9. 多表查询

9.1 内连接（INNER JOIN）

9.1.1 查询所有员工的姓名及所在部门名称

方言写法：

1
2
3

SELECT ename, dname 
FROM emp, dept 
WHERE emp.deptno = dept.deptno;

标准写法：

1
2
3

SELECT ename, dname 
FROM emp 
INNER JOIN dept ON emp.deptno = dept.deptno;

USING写法（关联字段名相同）：

1
2
3

SELECT ename, dname 
FROM emp 
INNER JOIN dept USING(deptno);

内连接特点：

与连接顺序无关（没有主从表之分）
多张表都能匹配的数据才能出现在结果集中
只返回匹配的记录

9.2 外连接（OUTER JOIN）

9.2.1 左连接（LEFT JOIN）

-- 以emp表为主表，查询所有员工及其部门信息
SELECT * 
FROM emp a 
LEFT JOIN dept b ON a.deptno = b.deptno;

左连接特点：

以左表（主表）为基准
左表所有记录都会显示
右表无匹配时显示NULL

9.2.2 右连接（RIGHT JOIN）

-- 以dept表为主表，查询所有部门及其员工信息
SELECT * 
FROM dept a 
RIGHT JOIN emp b ON a.deptno = b.deptno;

外连接特点：

与连接顺序有关（有主从表之分）
以主表为基准，依次在主从表中寻找关联记录
如果匹配则关联并展示，否则以NULL填充

9.3 自连接

-- 查询员工及其领导的姓名
SELECT a.ename AS 员工, b.ename AS 领导 
FROM emp a, emp b 
WHERE a.mgr = b.empno;

自连接说明：

同一张表进行连接
通过别名区分不同的角色
适用于树形结构数据

9.4 嵌套查询（子查询）

9.4.1 单行子查询

-- 查询编号为7788的员工所在的部门名称
-- 方式1：连接查询
SELECT dname 
FROM emp, dept 
WHERE emp.deptno = dept.deptno AND empno = 7788;

-- 方式2：子查询（单行子查询：返回单行单列）
SELECT dname 
FROM dept 
WHERE deptno = (SELECT deptno FROM emp WHERE empno = 7788);

9.4.2 多行子查询

-- 薪水>2000的员工所在的部门的名称
SELECT dname 
FROM dept 
WHERE deptno IN (
    SELECT DISTINCT deptno 
    FROM emp 
    WHERE sal > 2000
);

多行子查询操作符：

IN：等于任意一个值
=ANY：相当于IN
>ANY：大于最小值
<ANY：小于最大值
>ALL：大于最大值
<ALL：小于最小值

9.4.3 相关子查询

-- 查询薪水超过所在部门平均工资的员工信息
SELECT * 
FROM emp a 
WHERE sal > (
    SELECT AVG(sal) 
    FROM emp b 
    WHERE b.deptno = a.deptno
);

相关子查询说明：

主查询传递deptno给子查询
子查询根据传递的deptno查询出所在部门的平均工资
主查询根据子查询返回的部门平均工资来处理后续

9.4.4 EXISTS子查询

-- 查询存在薪水>2000员工的部门名称
SELECT dname 
FROM dept 
WHERE EXISTS (
    SELECT * 
    FROM emp 
    WHERE sal > 2000 AND emp.deptno = dept.deptno
);

IN vs EXISTS：

IN：先执行子查询，再执行主查询
EXISTS：先执行主查询，再执行子查询
EXISTS子查询不返回具体结果，返回true/false
EXISTS考虑的是匹配问题，性能通常更好

9.5 联合查询（UNION）

-- 查询20号部门或者工资>2000的员工信息
-- 方式1：使用OR
SELECT * FROM emp WHERE deptno = 20 OR sal > 2000;

-- 方式2：使用UNION（去重）
SELECT * FROM emp WHERE deptno = 20
UNION
SELECT * FROM emp WHERE sal > 2000;

-- 方式3：使用UNION ALL（不去重）
SELECT * FROM emp WHERE deptno = 20
UNION ALL
SELECT * FROM emp WHERE sal > 2000;

UNION说明：

UNION：并集，去除重复记录
UNION ALL：并集，保留重复记录
UNION ALL性能更好，因为不需要去重

UNION要求：

两个查询的字段数量和类型必须一致
字段名可以不同，但类型必须兼容

10. 查询优化建议

10.1 索引优化

-- 为常用查询字段创建索引
CREATE INDEX idx_deptno ON emp(deptno);
CREATE INDEX idx_sal ON emp(sal);

-- 创建复合索引
CREATE INDEX idx_deptno_sal ON emp(deptno, sal);

索引使用原则：

WHERE子句中的字段建立索引
ORDER BY子句中的字段建立索引
JOIN关联字段建立索引
避免在索引字段上使用函数

10.2 查询优化

-- 1. 避免SELECT *
SELECT empno, ename FROM emp;  -- 推荐
SELECT * FROM emp;              -- 不推荐

-- 2. 使用LIMIT限制结果
SELECT * FROM emp LIMIT 10;

-- 3. 避免在WHERE子句中使用函数
SELECT * FROM emp WHERE YEAR(create_time) = 2020;  -- 不推荐
SELECT * FROM emp WHERE create_time >= '2020-01-01' AND create_time < '2021-01-01';  -- 推荐

-- 4. 使用EXISTS代替IN（大数据量）
SELECT * FROM dept WHERE EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno);

10.3 分页优化

-- 传统分页（大offset性能差）
SELECT * FROM emp LIMIT 10000, 10;

-- 优化分页（使用索引字段定位）
SELECT * FROM emp WHERE empno > 10000 ORDER BY empno LIMIT 10;

10.4 连接优化

-- 1. 使用INNER JOIN代替WHERE连接
SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;  -- 推荐

-- 2. 小表驱动大表
SELECT * FROM dept d INNER JOIN emp e ON d.deptno = e.deptno;  -- dept小表在前

-- 3. 关联字段建立索引
CREATE INDEX idx_deptno ON emp(deptno);

11. 运维最佳实践

11.1 表操作最佳实践

创建表：
- 使用InnoDB引擎
- 使用utf8mb4字符集
- 添加必要的注释
- 合理设计字段类型和长度
修改表：
- 大表修改使用在线工具（pt-online-schema-change）
- 在业务低峰期操作
- 修改前备份数据
删除表：
- 删除前确认无业务依赖
- 先重命名表，观察后再删除
- 保留备份数据

11.2 数据操作最佳实践

插入数据：
- 使用批量插入提高性能
- 大批量数据使用LOAD DATA INFILE
- 使用事务保证数据一致性
更新数据：
- 更新前先查询确认
- 使用WHERE条件限制范围
- 大表更新分批进行
删除数据：
- 删除前先查询确认
- 使用事务保证可回滚
- 定期清理历史数据

11.3 查询优化最佳实践

索引优化：
- 为常用查询字段建立索引
- 避免过多索引影响写入性能
- 定期分析索引使用情况
查询优化：
- 避免SELECT *
- 使用EXPLAIN分析查询计划
- 避免全表扫描
- 合理使用LIMIT
监控和调优：
- 监控慢查询日志
- 定期分析查询性能
- 根据业务变化调整索引

12. 总结

12.1 核心要点

表操作：创建、修改、删除表是数据库管理的基础
字段操作：灵活管理表结构，适应业务变化
约束管理：保证数据完整性和一致性
数据操作：高效的增删改查是业务核心
查询优化：合理的查询设计提升系统性能

12.2 架构师建议

设计阶段：
- 合理设计表结构
- 选择合适的字段类型
- 建立必要的索引和约束
开发阶段：
- 编写高效的SQL语句
- 避免全表扫描
- 使用事务保证数据一致性
运维阶段：
- 定期监控查询性能
- 优化慢查询
- 根据业务变化调整索引

12.3 学习路径

基础操作：掌握表的创建、修改、删除
数据操作：熟练使用增删改查
查询优化：理解查询原理，优化查询性能
高级特性：掌握多表查询、子查询等高级特性

相关文章：

第440集MySQL基础操作与查询优化

MySQL基础操作与查询优化

1. 概述

1.1 MySQL基础操作的重要性

1.2 本文内容结构

2. 表的操作

2.1 创建表

2.1.1 基本语法

2.1.2 创建学生表示例

2.1.3 创建表的最佳实践

2.2 查看表结构

2.2.1 查看表结构

2.2.2 查看所有表

2.3 修改表名

2.3.1 修改表名方法

2.4 修改表注释

2.5 清空表

2.5.1 TRUNCATE清空表

2.5.2 DELETE清空表

2.6 删除表

3. 字段操作

3.1 添加字段

3.1.1 追加字段

3.1.2 在指定位置添加字段

3.2 修改字段

3.2.1 修改字段名和类型

3.2.2 修改字段最佳实践

3.3 删除字段

4. 约束管理

4.1 主键约束（PRIMARY KEY）

4.1.1 创建表时添加主键

4.1.2 联合主键

4.1.3 表已存在时添加主键

4.1.4 删除主键

4.2 唯一约束（UNIQUE）

4.2.1 创建表时添加唯一约束

4.2.2 表已存在时添加唯一约束

4.2.3 删除唯一约束

4.3 主键自增（AUTO_INCREMENT）

4.4 域完整性约束

4.4.1 非空约束（NOT NULL）

4.4.2 默认值（DEFAULT）

4.5 外键约束（FOREIGN KEY）

4.5.1 创建外键约束

4.5.2 外键约束选项

4.5.3 删除外键

5. 数据操作（增删改查）

5.1 查看建表语句

5.2 插入数据（INSERT）

5.2.1 插入所有字段

5.2.2 插入部分字段

5.2.3 批量插入

5.2.4 插入查询结果

5.3 更新数据（UPDATE）

5.4 删除数据（DELETE）

5.5 DROP、DELETE、TRUNCATE区别

6. 查询语句（DQL）

6.1 DQL语法结构

6.2 基础查询

6.2.1 查询所有字段

6.2.2 查询部分字段

6.3 条件查询（WHERE）

6.3.1 单一条件查询

6.3.2 组合条件查询

6.3.3 范围查询

6.3.4 集合查询（IN）

6.3.5 空值判断

6.4 别名（ALIAS）

6.5 去重（DISTINCT）

6.6 排序（ORDER BY）

6.7 模糊查询（LIKE）

6.8 限制结果（LIMIT）

7. 运算符

7.1 算术运算符

7.2 比较运算符

7.3 逻辑运算符

7.4 位运算符

7.5 查看字符编码

8. 函数

8.1 数学函数