MySQL 基础 CRUD 与查询操作

2.1 全列查询

SELECT * FROM 表名;

2.2 指定列查询

SELECT 指定列名称 FROM 表名;

2.3 查询字段为表达式

1. 常量表达式

SELECT 10 FROM 表名;

2. 把所有学生的语文成绩加 10 分

SELECT Chinese + 10 FROM 表名;

3. 计算所有学生语文、数学和英语成绩的总分

SELECT chinese + math + english FROM 表名;

2.4 为查询结果指定别名

SELECT 列名 1 + 列名 2 AS 别名 FROM 表名;

AS 可以省略，别名如果包含空格必须用单引号包裹。

2.5 结果去重查询

SELECT DISTINCT 列名 1, 列名 2 FROM 表名;

使用 DISTINCT 去重时，只有查询列表中所有列的值都相同才会判定为重复。

3. WHERE 条件查询

SELECT 列名 1, 列名 2 FROM 表名 WHERE 条件……;

比较运算符

逻辑运算符

3.1 基本查询

1. 查询英语不及格的同学及英语成绩 (< 60)

SELECT id, name, english FROM exam WHERE english < 60;

2. 查询语文成绩高于英语成绩的同学

SELECT id, name, chinese, math FROM exam WHERE chinese > math;

3. 总分在 200 分以下的同学

SELECT id, name, chinese + math + english FROM exam WHERE chinese + math + english < 200;

3.2 AND 和 OR

1. 查询语文成绩大于 80 分且英语成绩大于 80 分的同学

SELECT id, name FROM exam WHERE chinese > 80 AND english > 80;

2. 查询语文成绩大于 80 分或英语成绩大于 80 分的同学

SELECT * FROM exam WHERE chinese > 80 OR english > 80;

3.3 AND 和 OR 的优先级

SELECT * FROM exam WHERE chinese > 80 OR math > 70 AND english > 70;
SELECT * FROM exam WHERE (chinese > 80 OR math > 70) AND english > 70;

注意：AND 的优先级高于 OR，括号的优先级高于 AND 和 OR。

3.4 范围查询

1. 语文成绩在 [80, 90] 分的同学及语文成绩

SELECT name, chinese FROM exam WHERE chinese >= 80 AND chinese <= 90;
SELECT name, chinese FROM exam WHERE chinese BETWEEN 80 AND 90;

2. 数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩

SELECT name, math FROM exam WHERE math = 78 OR math = 79 OR math = 98 OR math = 99;
SELECT name, math FROM exam WHERE math IN (78, 79, 98, 99);

3.5 模糊查询

1. 查询所有姓孙的同学

SELECT * FROM exam WHERE name LIKE '孙%';

2. 查询姓孙且姓名共有两个字同学

SELECT * FROM exam WHERE name LIKE '孙_';

3.6 NULL 的查询

1. 查询英语成绩为 NULL 的记录

SELECT * FROM exam WHERE english IS NULL;

2. 查询英语成绩不为 NULL 的记录

SELECT * FROM exam WHERE english IS NOT NULL;

注意事项

1. WHERE 条件中可以使用表达式，但不能使用别名
2. AND 的优先级高于 OR，在同时使用时，建议使用小括号 () 包裹优先执行的部分
3. 过滤 NULL 时不要使用等于号 (=) 与不等于号 (!= , <>)
4. NULL 与任何值运算结果都为 NULL

3.7 ORDER BY 排序

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT 列名 1, 列名 2 FROM 表名 WHERE 条件 ORDER BY 列名 DESC/ASC;

1. 按数学成绩从低到高排序 (升序)

SELECT * FROM exam ORDER BY math ASC;

2. 查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

SELECT * FROM exam ORDER BY math DESC, english ASC, chinese ASC;

3. 查询同学及总分，由高到低排序，可以使用列的别名进行排序

SELECT math + english + chinese AS 总分 FROM exam ORDER BY 总分 DESC;

4. 所有英语成绩不为 NULL 的同学，按语文成绩从高到低排序

SELECT * FROM exam WHERE english IS NOT NULL ORDER BY chinese DESC;

注意事项

1. 查询中没有 ORDER BY 子句，返回的顺序是未定义的，永远不要依赖这个顺序
2. ORDER BY 子句中可以使用列的别名进行排序
3. NULL 进行排序时，视为比任何值都小，升序出现在最上面，降序出现在最下面

3.8 分页查询

-- 起始下标为 0
-- 从 0 开始，筛选 num 条结果
SELECT 列名 FROM 表名 ((WHERE 条件) (ORDER BY 列名 ASC/DESC) LIMIT num;
-- 从 start 开始，筛选 num 条结果
SELECT 列名 FROM 表名 (WHERE 条件) (ORDER BY 列名 ASC/DESC) LIMIT start, num;
-- 从 start 开始，筛选 num 条结果，比第二种用法更明确，建议使用
SELECT 列名 FROM 表名 (WHERE 条件) (ORDER BY 列名 ASC/DESC) LIMIT num OFFSET start;

Tips:

1. 总页数 = 数据的总条数 / 每页要展示的数量，如果有余数则 + 1.
2. start = （当前页号 - 1）* 每页要展示的数量

4. Update 修改

UPDATE 表名 SET 修改值 (WHERE 条件) (ORDER BY 列名 ASC/DESC) (LIMIT num);

对符合条件的结果进行列值更新

1. 将孙悟空同学的数学成绩变更为 80 分

UPDATE exam SET math = 80 WHERE name = '孙悟空';

2. 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

UPDATE exam SET math = 60, chinese = 70 WHERE name = '曹孟德';

3. 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

UPDATE exam SET math = math + 30 WHERE chinese + math + english IS NOT NULL ORDER BY chinese + math + english ASC LIMIT 0, 3;

4. 将所有同学的语文成绩更新为原来的 2 倍

UPDATE exam SET Chinese = Chinese * 2;

注意事项

1. 以原值的基础上做变更时，不能使用 math += 30 这样的语法
2. 不加 where 条件时，会导致全表数据被列新，谨慎操作

5. Delete 删除

5.1 普通 Delete 删除

DELETE FROM 表名 [WHERE 条件] [ORDER BY...] [LIMIT count];

• 删除孙悟空同学的考试成绩

DELETE FROM exam WHERE name = '孙悟空';

注意事项

执行 Delete 时不加条件会删除整张表的数据，谨慎操作

5.2 截断表

TRUNCATE TABLE 表名;

例如：

-- 准备测试表
CREATE TABLE t_truncate ( 
    id INT PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(20)
);
-- 插入新数据
INSERT INTO t_truncate (name) VALUES ('A'), ('B'), ('C');
-- 此时查看建表结构，注意 AUTO_INCREMENT=4
-- 截断表
TRUNCATE TABLE t_truncate;
-- 继续查看表结构，注意 AUTO_INCREMENT 已被重置为 0

TRUNCATE 的注意事项：

1. 只能对整表操作，不能像 DELETE 一样针对部分数据
2. 不对数据操作所以比 DELETE 更快，TRUNCATE 在删除数据的时候，不经过真正的事物
3. 会重置 AUTO_INCREMENT 项，而 DELETE 不会

5.3 插入查询结果

INSERT INTO 表名 SELECT...

例如：删除表中的重复记录，重复的数据只能有一份

实现思路：原始表中的数据一般不会主动删除，但是真正查询时不需要重复的数据，如果每次查询都使用 DISTINCT 进行去重操作，会严重效率。可以创建一张与 t_record 表结构相同的表，把去重的记录写入到新表中，以后查询都从新表中查，这样真实的数据不丢失，同时又能保证查询效率

-- 创建测试表，并构造数据
CREATE TABLE t_record (id int, name varchar(20));
INSERT INTO t_record VALUES (100, 'aaa'), (100, 'aaa'), (200, 'bbb'), (200, 'bbb'), (200, 'bbb'), (300, 'ccc');
-- 创建一张新表，表结构与 t_record 相同
CREATE TABLE t_record_new LIKE t_record;
-- 原表中的记录去重后写入到新表
INSERT INTO t_record_new SELECT DISTINCT * FROM t_record;

6. 聚合函数

常用聚合函数

7. Group by 分组查询

GROUP BY 子句的作用是通过一定的规则将一个数据集划分成若干个小的分组，然后针对若干个分组进行数据处理，比如使用聚合函数对分组进行统计。

SELECT 列名 FROM 表名 GROUP BY 列名 HAVING 条件;

1. 统计每个角色的人数

SELECT role, COUNT(*) FROM emp GROUP BY role;

7.1 HAVING 子句

使用 GROUP BY 对结果进行分组处理之后，对分组的结果进行过滤时，不能使用 WHERE 子句，而要使用 HAVING 子句

1. 显示平均工资低于 1500 的角色和它的平均工资

SELECT role, AVG(salary) FROM emp GROUP BY role HAVING AVG(salary) < 1500;

Having 与 Where 的区别

• Having 用于对分组结果的条件过滤
• Where 用于对表中真实数据的条件过滤

8. 内置函数

8.1 日期函数

8.2 字符串处理函数

8.3 数学函数

8.4 其他常用函数