数据查询语言(DQL)

数据查询语言(DQL)概述

DQL（Data Query Language）是SQL语言中专门用于从数据库中查询数据的部分，核心命令是SELECT。SELECT语句是SQL中最常用和最复杂的语句，可以实现各种复杂的数据检索需求。

DQL的重要性

在数据库应用中，查询操作占据了绝大部分的比例。掌握DQL不仅能够提高数据检索效率，还能优化数据库性能，减少系统资源消耗。

SELECT语句的基本结构

                        SQL语法
                    

                        SELECT [DISTINCT] column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1 [ASC|DESC], ...
LIMIT offset, count;
                    

基本SELECT语句

查询所有列

使用星号(*)可以查询表中的所有列。这种方法简单方便，但在生产环境中应谨慎使用，因为它会返回所有列数据，可能导致不必要的网络传输和资源消耗。

                        SQL语法
                    
                        SELECT * FROM table_name;

                        SQL示例
                    
                        -- 查询用户表的所有数据
SELECT * FROM users;

查询指定列

明确指定需要查询的列名可以提高查询效率和代码可读性。

                        SQL语法
                    
                        SELECT column1, column2, ... FROM table_name;

                        SQL示例
                    
                        -- 查询用户的用户名和邮箱
SELECT username, email FROM users;

使用列别名

列别名可以使查询结果更易读，特别是在处理复杂的表达式或需要特定显示格式时。

                        SQL语法
                    

                        SELECT 
    column_name AS alias_name,
    column_name alias_name  -- AS关键字可选
FROM table_name;
                    

                        SQL示例
                    

                        -- 使用列别名
SELECT 
    user_id AS id,
    username AS 用户名,
    email 邮箱,
    created_at '注册时间'
FROM users;
                    

WHERE子句

WHERE子句用于过滤记录，只返回满足条件的行。它是SQL查询中最常用的子句之一。

WHERE子句中的运算符

运算符	描述	示例
=	等于	age = 25
<> 或 !=	不等于	age <> 25
>	大于	age > 25
<	小于	age < 25
>=	大于等于	age >= 25
<=	小于等于	age <= 25
BETWEEN	在某个范围内	age BETWEEN 18 AND 65
LIKE	搜索某种模式	name LIKE '张%'
IN	指定多个可能的值	country IN ('中国', '美国')
IS NULL	为空值	phone IS NULL
IS NOT NULL	不为空值	phone IS NOT NULL

WHERE条件示例

                        SQL示例
                    

                        -- 等于条件
SELECT * FROM users WHERE username = 'john_doe';

-- 不等于条件
SELECT * FROM users WHERE age <> 18;

-- 比较条件
SELECT * FROM products WHERE price > 100;

-- BETWEEN条件
SELECT * FROM users WHERE age BETWEEN 18 AND 65;

-- IN条件
SELECT * FROM users WHERE country IN ('China', 'USA', 'Japan');

-- LIKE条件（模糊匹配）
SELECT * FROM users WHERE username LIKE 'j%';      -- 以j开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- 以@gmail.com结尾
SELECT * FROM users WHERE name LIKE '%son%';     -- 包含son

-- IS NULL条件
SELECT * FROM users WHERE phone_number IS NULL;

-- 组合条件
SELECT * FROM users 
WHERE (age > 18 AND country = 'China') 
   OR (age > 21 AND country = 'USA');
                    

ORDER BY子句

ORDER BY子句用于对结果集进行排序。可以按一个或多个列排序，每个列可以指定升序(ASC)或降序(DESC)。

基本ORDER BY语法

                        SQL语法
                    
                        SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

ORDER BY示例

                        SQL示例
                    

                        -- 单列排序
SELECT * FROM products ORDER BY price DESC;

-- 多列排序
SELECT * FROM users 
ORDER BY last_name ASC, first_name ASC;

-- 使用列位置排序
SELECT username, email, created_at 
FROM users 
ORDER BY 3 DESC;  -- 按第三列(created_at)降序排序

-- 使用表达式排序
SELECT product_name, price, stock_quantity 
FROM products 
ORDER BY price * stock_quantity DESC;
                    

LIMIT子句

LIMIT子句用于限制返回的记录数量，常用于分页查询。

基本LIMIT语法

                        SQL语法
                    

                        -- 限制返回记录数
SELECT column1, column2, ...
FROM table_name
LIMIT number;

-- 分页查询
SELECT column1, column2, ...
FROM table_name
LIMIT offset, number;
                    

LIMIT示例

                        SQL示例
                    

                        -- 返回前10条记录
SELECT * FROM users LIMIT 10;

-- 分页查询：第1页，每页10条
SELECT * FROM users LIMIT 0, 10;

-- 分页查询：第2页，每页10条
SELECT * FROM users LIMIT 10, 10;

-- 结合ORDER BY使用
SELECT * FROM products 
ORDER BY price DESC 
LIMIT 5;  -- 最贵的5个产品
                    

DISTINCT关键字

DISTINCT关键字用于返回唯一不同的值，消除重复记录。

                        SQL示例
                    
                        -- 返回不同的国家
SELECT DISTINCT country FROM users;

-- 返回不同的国家和城市组合
SELECT DISTINCT country, city FROM users;

-- 统计不同国家的数量
SELECT COUNT(DISTINCT country) FROM users;

聚合函数

聚合函数对一组值执行计算并返回单个值。它们通常与GROUP BY子句一起使用。

常用聚合函数

函数	描述	示例
COUNT()	返回行数	COUNT(*), COUNT(column)
SUM()	返回数值列的总和	SUM(price)
AVG()	返回数值列的平均值	AVG(price)
MAX()	返回最大值	MAX(price)
MIN()	返回最小值	MIN(price)
GROUP_CONCAT()	返回连接字符串	GROUP_CONCAT(name)

聚合函数示例

                        SQL示例
                    

                        -- 统计用户总数
SELECT COUNT(*) FROM users;

-- 统计有电话号码的用户数量
SELECT COUNT(phone_number) FROM users;

-- 计算产品平均价格
SELECT AVG(price) FROM products;

-- 计算所有产品的总价值
SELECT SUM(price * stock_quantity) FROM products;

-- 找到最贵和最便宜的产品
SELECT MAX(price), MIN(price) FROM products;
                    

GROUP BY子句

GROUP BY子句用于结合聚合函数，根据一个或多个列对结果集进行分组。

基本GROUP BY语法

                        SQL语法
                    

                        SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;
                    

GROUP BY示例

                        SQL示例
                    

                        -- 按国家统计用户数量
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country;

-- 按产品类别统计平均价格和库存总量
SELECT 
    category_id,
    AVG(price) AS avg_price,
    SUM(stock_quantity) AS total_stock
FROM products
GROUP BY category_id;

-- 多列分组
SELECT 
    country, 
    city, 
    COUNT(*) AS user_count
FROM users
GROUP BY country, city;
                    

HAVING子句

HAVING子句用于对分组后的结果进行过滤，类似于WHERE子句，但用于聚合函数。

基本HAVING语法

                        SQL语法
                    

                        SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
                    

HAVING示例

                        SQL示例
                    

                        -- 查找用户数量超过100的国家
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

-- 查找平均价格超过50的产品类别
SELECT category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id
HAVING AVG(price) > 50;

-- 结合WHERE和HAVING
SELECT 
    country, 
    AVG(age) AS avg_age,
    COUNT(*) AS user_count
FROM users
WHERE age > 18  -- 先过滤成年用户
GROUP BY country
HAVING COUNT(*) > 50;  -- 再过滤用户数超过50的国家
                    

WHERE vs HAVING：

WHERE在分组前过滤记录
HAVING在分组后过滤记录
WHERE不能使用聚合函数
HAVING可以使用聚合函数

公用表表达式(CTE)

CTE（Common Table Expression）是MySQL 8.0引入的功能，用于创建临时结果集，提高复杂查询的可读性。

                        SQL示例
                    

                        -- 基本CTE
WITH dept_stats AS (
    SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, ds.emp_count, ds.avg_salary
FROM departments d
JOIN dept_stats ds ON d.department_id = ds.department_id;

-- 递归CTE（遍历组织结构）
WITH RECURSIVE employee_hierarchy AS (
    -- 锚点成员：顶级经理
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归成员：下属员工
    SELECT e.employee_id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN employee_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM employee_hierarchy;
                    

窗口函数

窗口函数（MySQL 8.0+）可以在不分组的情况下对结果集的行进行计算，同时保留原始行的详细信息。

                        SQL示例
                    

                        -- 排名函数
SELECT 
    product_name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
    RANK() OVER (ORDER BY price DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank_num,
    NTILE(4) OVER (ORDER BY price DESC) AS quartile
FROM products;

-- 分区窗口函数
SELECT 
    department,
    name,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid
FROM employees;

-- LAG/LEAD 获取前后行数据
SELECT 
    sale_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_day_amount,
    LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_day_amount
FROM daily_sales;
                    

集合操作：INTERSECT 和 EXCEPT

MySQL 8.0.31 开始支持 INTERSECT（交集）和 EXCEPT（差集）操作符。

                        SQL示例
                    

                        -- 既是活跃用户又是VIP的用户
SELECT user_id FROM active_users
INTERSECT
SELECT user_id FROM vip_users;

-- 在旧版本中可以使用 EXISTS 或 IN 替代
SELECT user_id FROM active_users
WHERE user_id IN (SELECT user_id FROM vip_users);
                    

横向派生表 (LATERAL)

LATERAL 派生表可以引用同一 FROM 子句中前面表的列，对于每行执行一个子查询非常有用。

                        SQL示例
                    

                        -- 为每个用户获取最近的3个订单
SELECT u.user_id, u.username, recent_orders.*
FROM users u
LEFT JOIN LATERAL (
    SELECT order_id, order_date, total_amount
    FROM orders o
    WHERE o.user_id = u.user_id
    ORDER BY order_date DESC
    LIMIT 3
) AS recent_orders ON TRUE;
                    

完整SELECT语句结构

完整的SELECT语句包含以下子句，必须按照特定顺序书写：

                        SQL语法
                    

                        SELECT [DISTINCT] column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1 [ASC|DESC], ...
LIMIT offset, count;
                    

完整示例

                        SQL示例
                    

                        -- 复杂的查询示例
SELECT 
    country,
    AVG(age) AS average_age,
    COUNT(*) AS user_count
FROM users
WHERE age >= 18
    AND created_at >= '2023-01-01'
GROUP BY country
HAVING COUNT(*) > 10
ORDER BY user_count DESC
LIMIT 0, 20;
                    

UNION操作符

UNION操作符用于合并两个或多个SELECT语句的结果集。

                        SQL示例
                    

                        -- 合并活跃用户和VIP用户
SELECT user_id, username, 'active' AS user_type
FROM users 
WHERE is_active = 1
UNION
SELECT user_id, username, 'vip' AS user_type
FROM vip_users;
                    

UNION vs UNION ALL：

UNION会去除重复行
UNION ALL会保留所有行，包括重复行
UNION ALL性能更好，因为不需要去重

子查询

子查询是嵌套在其他SQL查询中的查询，可以用于WHERE、FROM或SELECT子句中。

子查询示例

                        SQL示例
                    

                        -- 在WHERE子句中使用子查询
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

-- 在FROM子句中使用子查询
SELECT * 
FROM (SELECT country, COUNT(*) AS user_count 
      FROM users 
      GROUP BY country) AS country_stats
WHERE user_count > 100;

-- 在SELECT子句中使用子查询
SELECT 
    product_name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price
FROM products;
                    

查询优化器提示 (Optimizer Hints)

可以使用优化器提示来影响查询计划的生成，例如强制使用或忽略某个索引。

                        SQL示例
                    
                        -- 强制使用某个索引
SELECT * FROM users USE INDEX (idx_username) WHERE username = 'john';

-- 忽略某个索引
SELECT * FROM users IGNORE INDEX (idx_email) WHERE email = 'john@example.com';

-- 强制使用索引（如果无法使用则报错）
SELECT * FROM users FORCE INDEX (idx_username) WHERE username = 'john';

EXPLAIN ANALYZE

从 MySQL 8.0.18 开始，EXPLAIN ANALYZE 可以显示查询的实际执行计划和执行统计信息（时间、行数等）。

                        SQL示例
                    
                        EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john';
-- 输出包含实际执行时间和循环次数，有助于发现性能瓶颈

性能优化建议

尽量避免使用SELECT *，明确指定需要的列
在WHERE子句中避免对列使用函数，这会使索引失效
合理使用索引，特别是在WHERE和JOIN条件中的列
对于大数据量的表，使用LIMIT限制返回结果
避免在WHERE子句中使用!=或<>操作符
使用EXISTS代替IN，特别是对于子查询
使用CTE提高复杂查询的可读性和维护性
窗口函数可以替代某些子查询，性能更好
定期使用 EXPLAIN ANALYZE 分析慢查询

下一步学习

掌握了数据查询语言后，您可以继续学习：

数据控制语言(DCL) - 学习权限管理和访问控制
约束 - 学习如何保证数据完整性
函数 - 学习MySQL内置函数的使用