SQL 详解之 DQL

DQL 概述

DQL（数据查询语言）：查询数据库中的数据，核心是 SELECT 语句。

基本语法

SELECT 列1, 列2, ...
  FROM 表名
 WHERE 条件
 ORDER BY 列 ASC/DESC
 LIMIT n OFFSET m;

基础查询

投影与别名

-- 查询所有列
SELECT * FROM tb_student;

-- 查询指定列并起别名
SELECT stu_id AS 学号,
       stu_name AS 姓名,
       stu_addr AS 籍贯
  FROM tb_student;

条件过滤（WHERE）

-- 单条件
SELECT * FROM tb_student WHERE stu_sex = 0;  -- 女学生

-- 多条件 AND/OR
SELECT * FROM tb_student
 WHERE stu_sex = 0 AND stu_addr = '四川成都';

-- 范围 BETWEEN
SELECT * FROM tb_student
 WHERE stu_birth BETWEEN '1980-1-1' AND '1989-12-31';

-- IN 列表
SELECT * FROM tb_course WHERE cou_credit IN (2, 4);

-- 空值判断
SELECT * FROM tb_student WHERE stu_addr IS NULL;
SELECT * FROM tb_student WHERE stu_addr IS NOT NULL;

模糊匹配

-- LIKE：% 任意字符，_ 单个字符
SELECT * FROM tb_student WHERE stu_name LIKE '杨%';    -- 姓杨
SELECT * FROM tb_student WHERE stu_name LIKE '杨_';    -- 姓杨+1个字
SELECT * FROM tb_student WHERE stu_name LIKE '%不%';    -- 名字含"不"

-- REGEXP：正则表达式
SELECT * FROM tb_student
 WHERE stu_name REGEXP '[林杨][\\u4e00-\\u9fa5]{2}';

排序与去重

-- 排序（默认升序）
SELECT * FROM tb_student
 WHERE stu_sex = 1
 ORDER BY stu_birth ASC;           -- ASC升序/DESC降序

-- 去重
SELECT DISTINCT stu_addr FROM tb_student;

聚合函数

-- 统计
SELECT COUNT(*) AS 人数,
       AVG(score) AS 平均分,
       MAX(score) AS 最高分,
       MIN(score) AS 最低分
  FROM tb_record WHERE stu_id = 1001;

分组（GROUP BY）

-- 按课程分组统计
SELECT cou_id,
       COUNT(*) AS 选课人数,
       AVG(score) AS 平均分
  FROM tb_record
 GROUP BY cou_id
HAVING AVG(score) >= 60;  -- 分组后过滤

注意：WHERE 在分组前过滤，HAVING 在分组后过滤

多表查询

连接（JOIN）

-- 内连接：只保留两边都匹配的行
SELECT s.stu_name, c.cou_name, r.score
  FROM tb_record r
 INNER JOIN tb_student s ON r.stu_id = s.stu_id
 INNER JOIN tb_course c ON r.cou_id = c.cou_id;

-- 左连接：保留左表全部行
SELECT s.stu_name, r.score
  FROM tb_student s
  LEFT JOIN tb_record r ON s.stu_id = r.stu_id;

-- 右连接：保留右表全部行
-- 右表全保留，左表无匹配则填 NULL

子查询

-- 标量子查询（返回单一值）
SELECT * FROM tb_student
 WHERE stu_birth = (SELECT MIN(stu_birth) FROM tb_student);

-- 表子查询（返回多行多列）
SELECT * FROM tb_student
 WHERE stu_id IN (
     SELECT stu_id FROM tb_record WHERE score >= 90
 );

集合运算

-- 并集（自动去重）
SELECT ... UNION SELECT ...

-- 并集（保留重复）
SELECT ... UNION ALL SELECT ...

-- 交集（MySQL不支持，可用 IN）

常用函数

-- 日期函数
SELECT CURDATE();                    -- 当前日期
SELECT DATEDIFF('2024-1-1', '2023-1-1');  -- 日期差
SELECT YEAR(stu_birth), MONTH(stu_birth) FROM tb_student;

-- 字符串函数
SELECT CONCAT(stu_name, '-', stu_addr) FROM tb_student;
SELECT SUBSTRING(stu_name, 1, 2) FROM tb_student;
SELECT TRIM(stu_addr) FROM tb_student;

-- 条件表达式
SELECT stu_name,
       CASE stu_sex WHEN 1 THEN '男' ELSE '女' END AS 性别
  FROM tb_student;

总结

• SELECT：投影（列）、WHERE（行过滤）、ORDER BY（排序）
• GROUP BY + 聚合函数：COUNT/SUM/AVG/MAX/MIN
• HAVING 过滤分组，WHERE 过滤行
• JOIN 多表连接：INNER/LEFT/RIGHT
• 子查询：嵌套 SELECT，可用于 WHERE、FROM