MySQL DQL基础查询全解析：从电商案例学SELECT技巧

1. MySQL DQL基础查询全解析作为一名从业多年的数据库开发工程师我深知数据查询语言(DQL)在实际工作中的重要性。今天我将通过一个完整的电商商品管理案例带大家系统掌握MySQL中的SELECT查询技巧。我们会从最基础的查询语法开始逐步深入到条件筛选、聚合计算等高级功能每个知识点都配有可直接运行的SQL示例。1.1 环境准备与测试数据我们先搭建一个模拟电商数据库环境包含商品表和分类表-- 创建数据库 DROP DATABASE IF EXISTS dql_db; CREATE DATABASE IF NOT EXISTS dql_db; USE dql_db; -- 商品表结构设计 CREATE TABLE IF NOT EXISTS products ( id INT PRIMARY KEY AUTO_INCREMENT, -- 自增主键 name VARCHAR(24) NOT NULL, -- 商品名称 price DECIMAL(10, 2) NOT NULL, -- 价格(总位数10小数位2) score DECIMAL(5, 2), -- 评分(允许NULL) is_self VARCHAR(8), -- 是否自营 category_id INT -- 分类ID(外键) ); -- 商品分类表 CREATE TABLE IF NOT EXISTS category ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(24) NOT NULL ); -- 插入分类数据 INSERT INTO category VALUES (1, 手机),(2, 电脑),(3, 美妆),(4, 家居); -- 插入商品数据 INSERT INTO products VALUES (1, 华为Mate50, 5499.00, 9.70, 自营, 1), (2, 荣耀80, 2399.00, 9.50, 自营, 1), /* 其他商品数据省略... */ (12, 迪奥996, 330.00, 9.70, 非自营, 3);提示在实际项目中建议使用专门的数据库迁移工具(如Flyway)来管理表结构变更而不是直接执行DDL语句。1.2 基础查询操作1.2.1 全字段查询与指定字段查询最基本的查询方式是检索整张表的所有字段-- 查询所有商品信息(开发中不推荐) SELECT * FROM products; -- 推荐写法明确指定需要查询的字段 SELECT id, name, price, score, is_self, category_id FROM products;为什么推荐显式指定字段主要有三个原因提高查询效率(减少不必要的数据传输)避免表结构变更导致的应用层解析错误代码可读性更好1.2.2 字段别名与表别名当需要自定义输出列名或简化表名引用时可以使用别名-- 字段别名(AS可省略) SELECT name AS 商品名称, price AS 价格 FROM products; SELECT name 商品名, price 售价 FROM products; -- 表别名(常用于多表查询) SELECT p.name, p.price FROM products p;注意一旦为表设置了别名就必须使用别名引用字段原表名将失效。1.2.3 去重查询统计商品涉及的所有分类ID(去除重复值)SELECT DISTINCT category_id FROM products;DISTINCT作用于所有选择的列如果要基于多列组合去重SELECT DISTINCT category_id, is_self FROM products;1.3 条件查询详解1.3.1 比较运算符常用的比较运算符包括, , , , , -- 查询自营商品 SELECT * FROM products WHERE is_self 自营; -- 查询评分大于9.5的商品 SELECT * FROM products WHERE score 9.5; -- 查询价格不等于999的商品(两种写法等效) SELECT * FROM products WHERE price 999; SELECT * FROM products WHERE price ! 999;1.3.2 逻辑运算符AND、OR、NOT用于组合多个条件-- 查询价格在2000-4000之间的商品(三种写法) SELECT * FROM products WHERE price 2000 AND price 4000; SELECT * FROM products WHERE price BETWEEN 2000 AND 4000; SELECT * FROM products WHERE price BETWEEN 2000 AND 4000;注意BETWEEN是闭区间包含边界值而使用和组合时需要注意是否包含边界。1.3.3 IN与NOT IN当需要匹配多个离散值时-- 查询价格为999,2199,4199的商品 SELECT * FROM products WHERE price IN(999, 2199, 4199); -- 反向查询 SELECT * FROM products WHERE price NOT IN(999, 2199, 4199);1.3.4 模糊查询LIKELIKE配合通配符实现模糊匹配%匹配任意多个字符_匹配单个字符-- 查询名称以华开头的商品 SELECT * FROM products WHERE name LIKE 华%; -- 查询名称包含兰的商品 SELECT * FROM products WHERE name LIKE %兰%; -- 查询名称第二个字是兰的商品 SELECT * FROM products WHERE name LIKE _兰%;1.3.5 NULL值处理NULL表示缺失或未知的值需要用IS NULL/IS NOT NULL判断-- 查询没有评分的商品 SELECT * FROM products WHERE score IS NULL; -- 查询有评分的商品 SELECT * FROM products WHERE score IS NOT NULL;重要区别score NULL是错误的写法NULL不能使用等号比较。1.4 排序查询ORDER BY子句控制结果集的排序方式-- 按价格降序(高到低) SELECT * FROM products ORDER BY price DESC; -- 按评分升序(低到高ASC可省略) SELECT * FROM products ORDER BY price ASC; SELECT * FROM products ORDER BY price; -- 多列排序先按是否自营再按价格 SELECT * FROM products ORDER BY is_self, price; -- 带条件的排序价格2000的商品按评分降序 SELECT * FROM products WHERE price 2000 ORDER BY score DESC;1.5 聚合函数与分组查询1.5.1 常用聚合函数COUNT()计数SUM()求和AVG()平均值MAX()/MIN()最大/最小值-- 商品评分统计 SELECT COUNT(*) AS 商品总数, MIN(score) AS 最低评分, MAX(score) AS 最高评分, AVG(score) AS 平均评分 FROM products;注意COUNT(字段名)会忽略NULL值而COUNT(*)会计所有行。1.5.2 分组统计GROUP BY将数据按指定字段分组后聚合-- 统计每个分类的商品数量 SELECT category_id, COUNT(*) FROM products GROUP BY category_id; -- 统计各分类自营/非自营商品数量 SELECT category_id, is_self, COUNT(*) FROM products GROUP BY category_id, is_self;1.5.3 HAVING筛选分组结果WHERE过滤行HAVING过滤分组-- 查询平均评分9.5的分类 SELECT category_id, AVG(score) FROM products GROUP BY category_id HAVING AVG(score) 9.5;经验HAVING条件应尽量使用聚合函数虽然它也可以包含普通字段条件但这通常意味着查询设计可能存在问题。1.6 分页查询LIMIT实现分页查询语法LIMIT offset, count-- 查询价格最高的3个商品 SELECT * FROM products ORDER BY price DESC LIMIT 3; -- 分页查询(每页4条) SELECT * FROM products LIMIT 0, 4; -- 第1页 SELECT * FROM products LIMIT 4, 4; -- 第2页 SELECT * FROM products LIMIT 8, 4; -- 第3页1.7 综合练习解析1.7.1 学生成绩查询-- 查询总分200的学生 SELECT name, (chineseenglishmath) AS total FROM student WHERE (chineseenglishmath) 200; -- 查询英语80或总分200的学生 SELECT name, english, (chineseenglishmath) total FROM student WHERE english80 OR (chineseenglishmath)200;1.7.2 员工信息查询-- 部门平均薪水2000的部门 SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) 2000; -- 姓名第二个字母不是A且薪水800的员工 SELECT * FROM emp WHERE ename NOT LIKE _A% AND sal800 ORDER BY sal*12 DESC;1.8 关键知识点总结查询执行顺序FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMITNULL处理要点使用IS NULL/IS NOT NULL判断聚合函数忽略NULL值NULL参与比较结果总是UNKNOWN性能优化建议避免SELECT *只查询需要的字段为WHERE和ORDER BY涉及的字段建立索引大数据量分页使用延迟关联技巧常见误区BETWEEN是闭区间[...]LIKE %xxx无法使用索引COUNT(*)与COUNT(字段)的区别在实际项目中合理运用这些DQL技巧可以显著提高查询效率。特别是在处理百万级以上数据时良好的查询设计能带来数量级的性能提升。建议初学者多练习复杂查询的编写并学会使用EXPLAIN分析查询执行计划。