DQL语句
主要是SELECT查询语句的使用。
SELECT语句
SELECT语句的基本语法:
SELECT 常量;
SELECT 表达式;
SELECT 函数;
例如:
SELECT 1;
SELECT 9/2;
SELECT NOW();
查询所有字段
SELECT * FROM employees;
查询指定字段
SELECT name, salary FROM employees;
带条件查询
SELECT name, salary
FROM employees
WHERE salary > 5000;
排序
SELECT name, salary
FROM employees
ORDER BY salary DESC;
分组统计
SELECT department_id, COUNT(*) AS total
FROM employees
GROUP BY department_id;
分组过滤
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 6000;
分页
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 0, 10; -- 查询前10条
常配合 WHERE
、GROUP BY
、HAVING
、ORDER BY
、LIMIT
使用
别名
- 给 表 或 字段 临时起一个名字(仅在当前 SQL 语句中生效)
- 使语句更简洁,更易读
- 在多表查询或函数计算结果中,避免列名混乱
别名分为 字段别名 和 表别名;
字段别名
SELECT 字段名 AS 别名
FROM 表名;
AS
可以省略,常用简写
SELECT 字段名 别名
FROM 表名;
示例
1、给字段起别名
SELECT name AS 姓名, salary AS 薪资
FROM employees;
2、计算字段并取别名
SELECT salary * 12 AS 年薪
FROM employees;
3、别名中包含空格时需要引号
SELECT name AS "员工 姓名", salary AS "月 薪"
FROM employees;
4、别名省略AS
SELECT name "员工 姓名", salary "月 薪"
FROM employees;
表别名
1、给表起别名
SELECT e.name, e.salary
FROM employees AS e;
2、多表查询时简化表名
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
3、自连接时必须用别名
SELECT e1.name AS 员工, e2.name AS 上级
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;
子查询结果别名
-- 假设有 exam 表
CREATE TABLE exam (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
subject VARCHAR(20),
score INT
);
INSERT INTO exam (name, subject, score) VALUES
('张三', '数学', 90),
('李四', '数学', 95),
('王五', '数学', 85),
('赵六', '语文', 88),
('钱七', '语文', 92),
('孙八', '语文', 92);
-- 子查询 + join
SELECT
e.name,
e.subject,
e.score
FROM exam e
JOIN (
SELECT subject, MAX(score) AS max_score
FROM exam
GROUP BY subject
) t
ON e.subject = t.subject
AND e.score = t.max_score;
1、t 就是子查询结果的别名,可以改成任何合法名字,比如 max_result。
2、在 ON 里就可以用 t.subject、t.max_score 来和外层表 e 关联。
3、这个查询的意义:查找每个科目的最高分对应的学生。
✨ 注意事项
AS
用于给字段或表起别名,可以省略;- 别名提高可读性,尤其在多表查询、计算字段、列名重复时非常有用;
- 别名可在
ORDER BY
、HAVING
使用,但不能在WHERE
使用;
AS建表
AS
作为结果创建表结构并插入数据。
示例1:创建新表(带数据)
CREATE TABLE top_math_students AS
SELECT name AS student_name, score AS math_score
FROM exam
WHERE subject = '数学' AND score >= 90;
📌 说明:
- 新表
top_math_students
会包含两列:student_name
、math_score
。 - 查询到的数据(数学成绩 ≥ 90 的学生)会直接插入到新表中。
示例2:创建新表(只建结构,不拷贝数据)
CREATE TABLE exam_copy AS
SELECT * FROM exam WHERE 1=0;
📌 说明:
WHERE 1=0
永远不成立 → 不会插入数据- 但表结构会被复制下来
示例3:临时表 + AS
如果只是想在一次会话里临时用,可以建临时表:
CREATE TEMPORARY TABLE tmp_result AS
SELECT name AS 姓名, subject AS 科目, score AS 分数
FROM exam;
📌 说明:
- 临时表只在当前会话有效,断开连接就会自动销毁。
结果去重
使用 关键字 distinct
关键字去重。
基本语法:
SELECT DISTINCT 字段1, 字段2, ...
FROM 表名
[WHERE 条件];
🔥 示例:
1、对单个字段去重
SELECT DISTINCT department_id
FROM employees;
2、对多个字段联合去重
SELECT DISTINCT department_id, job_id
FROM employees;
3、结合条件使用
SELECT DISTINCT name
FROM employees
WHERE salary > 5000;
4、和聚合函数一起用
SELECT COUNT(DISTINCT department_id)
FROM employees;
🔥 注意事项:
1、DISTINCT 作用范围是 整个字段组合,而不是单独某一个字段;
SELECT DISTINCT name, department_id
FROM employees;
2、DISTINCT 不能直接写在部分字段前面,必须作用于整组;
❌ 错误写法
SELECT name, DISTINCT department_id FROM employees;
✅ 正确写法
SELECT DISTINCT name, department_id FROM employees;
3、如果只是想去掉 重复行,用 DISTINCT;如果是对结果做更复杂的去重、排名,可以结合 ROW_NUMBER()、GROUP BY 等;
关联查询
📌 什么是关联查询
关联查询(Join Query)指的是从多个表中根据一定的关系条件(通常是主键和外键的对应关系)获取数据。 常用于数据分布在不同表,需要组合展示时。
📌 关联查询结果分为几种情况
隐式内连接
这种写法 也是关联查询,只是用的是 旧式的逗号分隔表 + WHERE 条件的写法,相当于 隐式内连接(Implicit INNER JOIN)
SELECT name, score
FROM student s, exam e
WHERE e.student_id = s.id;
student s, exam e
→ 两张表在 FROM
中用逗号列出
WHERE e.student_id = s.id
→ 定义关联条件
效果与下面的显式内连接等价:
SELECT name, score
FROM student s
INNER JOIN exam e ON e.student_id = s.id;
🚀 特点:
- 结果只包含 满足连接条件的记录 → 内连接(交集)
- 左表/右表的所有记录不保证保留,如果没有匹配则不会出现(与 LEFT JOIN 不同)
虽然逗号写法可用,但现代 SQL 更推荐 显式 JOIN 语法,可读性更强,也方便扩展多表关联:
SELECT s.name, e.score
FROM student s
JOIN exam e ON e.student_id = s.id;
内连接(INNER JOIN)
- 取两张表中符合连接条件的记录。
- 交集效果。
- 在 SQL 中,直接写
JOIN
或INNER JOIN
,默认是 内连接(Inner Join),既不是左连接也不是右连接。
-- 查询员工及其所在部门
select e.id, e.name, d.dept_name
from employee e
inner join department d
on e.dept_id = d.id;
左连接(LEFT JOIN)
- 返回左表的所有记录,即使右表没有匹配,也会显示(右表字段为
NULL
)。 - 左表为主。
-- 查询所有员工及部门(即便部门为空)
select e.id, e.name, d.dept_name
from employee e
left join department d
on e.dept_id = d.id;
右连接(RIGHT JOIN)
- 返回右表的所有记录,即使左表没有匹配,也会显示(左表字段为
NULL
)。 - 右表为主。
-- 查询所有部门及部门下员工(即便部门没人)
select e.id, e.name, d.dept_name
from employee e
right join department d
on e.dept_id = d.id;
UNION / UNION ALL(联合查询)
- UNION:对两个查询结果进行合并,并去重。
- UNION ALL:对两个查询结果进行合并,不去重,效率更高。
- 要求:字段数一致,字段类型兼容。
-- 查询所有员工编号(来自正式员工和实习生两张表)
select id, name from employee
union
select id, name from intern;
📌 关联查询的SQL有几种情况
常见情况:
- 内连接(inner join / join)
- 外连接(left join、right join、full join)
- 交叉连接(cross join,笛卡尔积)
- 联合查询(union / union all)
- 自连接(表与自身的关联查询)
📌 联合查询字段列表问题
使用 UNION
或 UNION ALL
时:
- 两个
select
的字段数量必须一致 - 对应字段的类型必须兼容(数值/字符串/日期)
- 字段名以第一个查询的字段名为准
select id, name from employee
union
select student_id, student_name from student;
-- 结果字段名是 id, name
自连接(SELF JOIN)
自连接:同一张表与自身进行关联,用于处理表内层级关系(树结构、上下级关系等)。
-- 查询员工及其上级领导姓名
select
e.id as emp_id, e.name as emp_name,
m.id as mgr_id, m.name as mgr_name
from employee e
left join employee m
on e.manager_id = m.id;
📌 总结
类型 | 作用 | 关键点 |
---|---|---|
INNER JOIN | 取两表交集 | 匹配的行 |
LEFT JOIN | 左表为主,右表可为空 | 左表全有 |
RIGHT JOIN | 右表为主,左表可为空 | 右表全有 |
FULL JOIN | 左右表并集(MySQL 不支持,Oracle/PG 支持) | 全部行 |
UNION | 合并结果并去重 | 字段数、类型需一致 |
UNION ALL | 合并结果不去重 | 性能更高 |
SELF JOIN | 自己和自己关联 | 处理树结构 |
七大子句
在 SQL 中,SELECT
语句常见 7大子句,它们的书写顺序与执行顺序不同:
SELECT ...
FROM ...
ON ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...
实际执行顺序
- FROM:确定数据来源表。
- ON:执行表连接时的连接条件。
- WHERE:筛选记录(行过滤)。
- GROUP BY:分组。
- HAVING:对分组后的结果进行条件过滤。
- SELECT:子查询(嵌套查询)。
- ORDER BY:结果排序。
- LIMIT:限制返回的行数。
where
WHERE
用于 过滤表中符合条件的行。- 通常在 SELECT、UPDATE、DELETE 语句中使用。
- 不能直接用于分组后的聚合条件(这时用
HAVING
)
基本语法:
SELECT 列名
FROM 表名
WHERE 条件
[ORDER BY 列名]
[LIMIT n];
条件类型
-- 比较运算符
=, <>, >, <, >=, <=
SELECT name, salary
FROM employee
WHERE salary > 5000;
-- 范围条件(BETWEEN ... AND ...)
-- 查询工资在 4000 到 8000 之间的员工
SELECT name, salary
FROM employee
WHERE salary BETWEEN 4000 AND 8000;
-- 集合条件(IN / NOT IN)
-- 查询 IT 和 HR 部门的员工
SELECT name, dept
FROM employee
WHERE dept IN ('IT', 'HR');
-- 模糊匹配(LIKE)
-- 查询名字以 'A' 开头的员工
SELECT name
FROM employee
WHERE name LIKE 'A%';
-- 空值判断(IS NULL / IS NOT NULL)
-- 查询没有部门的员工
SELECT name, dept_id
FROM employee
WHERE dept_id IS NULL;
-- 逻辑运算符(AND / OR / NOT)
-- 查询 IT 部门且工资大于 6000 的员工
SELECT name, dept, salary
FROM employee
WHERE dept = 'IT' AND salary > 6000;
ON
ON
用于 连接表时指定匹配条件(通常是主键与外键对应)。- 主要用于:
INNER JOIN
、LEFT JOIN
、RIGHT JOIN
、FULL JOIN
。 - 作用:定义连接的逻辑条件,决定哪些行可以匹配。
- 在 SQL 中,直接写
JOIN
或INNER JOIN
,默认是 内连接(Inner Join),既不是左连接也不是右连接。
与
WHERE
区别:
ON
控制表之间的连接逻辑。WHERE
控制结果集行的过滤。- 对外连接(LEFT/RIGHT JOIN)特别重要,因为
WHERE
可能会把原本 NULL 的行过滤掉。
基本语法
SELECT 表1.列名, 表2.列名, ...
FROM 表1
JOIN 表2
ON 表1.列名 = 表2.列名
[WHERE 条件]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];
使用示例
-- 内连接(INNER JOIN)
-- 只显示 有匹配部门的员工
-- 不匹配的行不会显示
SELECT e.name, d.dept_name
FROM employee e
INNER JOIN department d
ON e.dept_id = d.id;
-- 右连接(RIGHT JOIN)
-- 显示 所有部门
-- 如果部门没有员工,name 为 NULL
SELECT e.name, d.dept_name
FROM employee e
RIGHT JOIN department d
ON e.dept_id = d.id;
-- 多条件连接
-- 连接条件不仅要求 部门匹配,还要求员工工资大于 5000
SELECT e.name, d.dept_name
FROM employee e
JOIN department d
ON e.dept_id = d.id AND e.salary > 5000;
使用技巧
- 外连接时要用 ON 而不是 WHERE,避免把
NULL
行过滤掉 - 可以在 ON 中使用 多个条件(用 AND / OR)
- 配合别名使用更清晰:
SELECT e.name, d.dept_name
FROM employee AS e
JOIN department AS d
ON e.dept_id = d.id;
group By
GROUP BY
子句通常要 结合聚合函数 一起使用(比如COUNT()
、SUM()
、AVG()
、MAX()
、MIN()
)
表结构
create table employee (
id int primary key auto_increment,
name varchar(50),
dept varchar(50),
job varchar(50),
salary decimal(10,2)
);
CREATE TABLE department (
id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50)
);
-- 插入员工数据
insert into employee (name, dept, job, salary) values
('Alice', 'IT', 'Developer', 6000),
('Bob', 'IT', 'Developer', 7000),
('Cathy', 'IT', 'Manager', 12000),
('David', 'HR', 'Recruiter', 5000),
('Ella', 'HR', 'Manager', 9000),
('Frank', 'HR', 'Recruiter', 4000),
('Grace', 'Sales', 'Salesman', 3000),
('Helen', 'Sales', 'Salesman', 3500),
('Ivy', 'Sales', 'Manager', 10000),
('Jack', 'Finance', 'Accountant', 8000),
('Kate', 'Finance', 'Manager', 9500);
-- 插入部门数据
INSERT INTO department (dept_name) VALUES
('IT'),
('HR'),
('Sales'),
('Finance');
GROUP BY
使用示例:
-- 分组,通常结合聚合函数来使用
-- 统计每个部门的人数
select dept, count(*) as emp_count
from employee
group by dept;
-- 统计每个部门的平均工资
select dept, avg(salary) as avg_salary
from employee
group by dept;
-- 统计每个部门的最高工资
select dept, max(salary) as max_salary
from employee
group by dept;
-- 按职位统计总工资
select job, sum(salary) as total_salary
from employee
group by job;
-- 语法总结
select 分组字段, 聚合函数(列)
from 表
[where 条件]
group by 分组字段
[having 条件]; -- 对分组结果过滤
HAVING
HAVING
用于 分组后的结果集 进行过滤。- 通常和
GROUP BY
一起使用。 - 可以使用 聚合函数(如
COUNT()
、SUM()
、AVG()
等)进行条件判断。
语法:
SELECT 列名, 聚合函数(列名)
FROM 表名
[WHERE 条件] -- 可选:分组前过滤
GROUP BY 列名
HAVING 聚合函数条件 -- 对分组后的结果过滤
[ORDER BY 列名] -- 可选:排序
-- 按部门统计员工人数,并筛选人数大于 2 的部门
SELECT dept, COUNT(*) AS emp_count
FROM employee
GROUP BY dept
HAVING COUNT(*) > 2;
-- 统计部门平均工资,筛选平均工资 > 6000 的部门
SELECT dept, AVG(salary) AS avg_salary
FROM employee
GROUP BY dept
HAVING AVG(salary) > 6000;
-- WHERE + HAVING 结合使用
-- 只统计 IT 和 HR 部门员工,筛选人数 > 2
SELECT dept, COUNT(*) AS emp_count
FROM employee
WHERE dept IN ('IT', 'HR') -- 分组前过滤原始行
GROUP BY dept
HAVING COUNT(*) > 2; -- 分组后过滤结果
ORDER BY
ORDER BY
用于 对查询结果进行排序。- 可以按 一个或多个列 排序,默认升序(ASC)。
- 可以和
SELECT
、WHERE
、GROUP BY
、HAVING
配合使用。
基本语法
SELECT 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
[GROUP BY 列名]
[HAVING 条件]
ORDER BY 列名1 [ASC|DESC], 列名2 [ASC|DESC], ...;
使用示例
-- 按单列升序
-- 按工资升序排列
SELECT name, salary
FROM employee
ORDER BY salary ASC;
-- 按单列降序
-- 按工资降序排列
SELECT name, salary
FROM employee
ORDER BY salary DESC;
-- 按多列排序
-- 先按部门升序,再按工资降序
SELECT name, dept, salary
FROM employee
ORDER BY dept ASC, salary DESC;
-- 与 GROUP BY / HAVING 结合使用
-- 按部门统计员工人数,并按人数降序排列
SELECT dept, COUNT(*) AS emp_count
FROM employee
GROUP BY dept
HAVING COUNT(*) > 1
ORDER BY emp_count DESC;
-- 使用列索引排序
-- SELECT 的第 2 列按降序排列
SELECT name, salary
FROM employee
ORDER BY 2 DESC;
LIMIT
LIMIT
用于 限制查询返回的行数。- 常用于分页查询或只取前 N 条记录。
基本语法:
-- 只限制返回行数
SELECT 列名1, 列名2
FROM 表名
LIMIT 行数;
-- 指定偏移量 + 返回行数
SELECT 列名1, 列名2
FROM 表名
LIMIT 偏移量, 行数; -- MySQL 写法
使用示例
-- 取前 5 条员工记录
SELECT name, salary
FROM employee
ORDER BY salary DESC
LIMIT 5;
-- 取第 6 到第 10 条员工记录
SELECT name, salary
FROM employee
ORDER BY salary DESC
LIMIT 5, 5; -- 偏移 5 行,取 5 行
-- 与分页结合
-- 第 page 页,每页 page_size 条
SET @page = 2;
SET @page_size = 3;
SELECT name, dept, salary
FROM employee
ORDER BY salary DESC
LIMIT (@page-1)*@page_size, @page_size;
-- 与 GROUP BY / HAVING / ORDER BY 结合
-- 按部门统计员工人数,筛选人数>1,按人数降序,取前 3 个部门
SELECT dept, COUNT(*) AS emp_count
FROM employee
GROUP BY dept
HAVING COUNT(*) > 1
ORDER BY emp_count DESC
LIMIT 3;
使用技巧
- 必须配合 ORDER BY 才有确定性,否则返回的前 N 行可能是随机的。
- 分页查询常用
LIMIT 偏移量, 行数
或LIMIT 行数 OFFSET 偏移量
。 - 与
GROUP BY
/HAVING
/ORDER BY
结合,可以快速取分组统计结果的前几条。
子查询
子查询是指在一个 SQL 语句中嵌套另一个 SELECT
语句。嵌套中的SQL语句先执行。
表结构:
-- 表结构
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
sex CHAR(1),
age INT
);
-- 考试表
CREATE TABLE exam (
id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT,
subject VARCHAR(50),
score INT,
FOREIGN KEY (student_id) REFERENCES student(id)
);
-- 插入数据
INSERT INTO student (name, sex, age) VALUES
('张三', 'M', 20),
('李四', 'M', 22),
('王五', 'F', 21),
('赵六', 'F', 23);
-- 插入考试成绩:
INSERT INTO exam (student_id, subject, score) VALUES
(1, '数学', 85),
(1, '英语', 90),
(2, '数学', 76),
(2, '英语', 88),
(3, '数学', 92),
(3, '英语', 95),
(4, '数学', 70),
(4, '英语', 80);
📌 SELECT
子句中嵌套子查询
-- 标量子查询:查询每个学生 + 全部考试的最高分
SELECT
name,
(SELECT MAX(score) FROM exam) AS max_score
FROM student;
-- 相关子查询:查询每个学生的最高成绩,两表联查。
SELECT
s.name,
(SELECT MAX(e.score)
FROM exam e
WHERE e.student_id = s.id) AS max_score
FROM student s;
-- 表子查询:只查询最高分的学生
SELECT
s.name,
e.score
FROM student s
JOIN exam e ON s.id = e.student_id
WHERE e.score = (SELECT MAX(score) FROM exam);
-- 查询各科最高分的学生(子查询+GROUP BY+JOIN)
-- 子查询:按 subject 分组,取每门课的最高分。
-- 外层:再跟 exam + student 表 JOIN,查出具体是哪位学生。
SELECT
e.subject,
s.name,
e.score
FROM exam e
JOIN student s ON e.student_id = s.id
JOIN (
SELECT subject, MAX(score) AS max_score
FROM exam
GROUP BY subject
) t ON e.subject = t.subject AND e.score = t.max_score;
📌 WHERE / HAVING
中嵌套子查询
用于条件过滤,条件中的子查询先执行。
-- 查询比平均分高的学生
SELECT name, score
FROM exam
WHERE score > (SELECT AVG(score) FROM exam);
📌 EXISTS
型子查询
用于判断是否存在相关记录(布尔值):根据子查询的查询结果,如果存在则查询,否则则不查询。
-- 查询有成绩记录的学生
SELECT name
FROM student s
WHERE EXISTS (SELECT 1 FROM exam e WHERE e.student_id = s.id);
-- SELECT 1 代表的意思是 不返回表中的字段,而是固定输出数字 1,每匹配一行,就输出一行 1
📌 FROM
子句中嵌套子查询(派生表)
把子查询结果当作一张临时表:
-- 查询每个部门工资最高的员工
SELECT dept, name, salary
FROM (
SELECT dept, name, salary,
RANK() OVER(PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employee
) t
WHERE rnk = 1;
总结:
WHERE/HAVING
子查询 👉 用于筛选EXISTS
子查询 👉 用于存在性判断SELECT
子查询 👉 用于生成列值FROM
子查询 👉 临时表 / 派生表
面试问题
WHERE 和 HAVING 的区别
-
WHERE:对 原始数据行 进行过滤,不能用聚合函数。
-
HAVING:对 分组结果 进行过滤,可以用聚合函数。
一句话总结:
WHERE
是分组之前的过滤条件。HAVING
是分组之后的过滤条件。- 一般先用
WHERE
粗过滤,再用HAVING
精过滤。
示例:
表结构
create table employee (
id int primary key auto_increment,
name varchar(50),
dept varchar(50),
job varchar(50),
salary decimal(10,2)
);
CREATE TABLE department (
id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50)
);
-- 插入员工数据
insert into employee (name, dept, job, salary) values
('Alice', 'IT', 'Developer', 6000),
('Bob', 'IT', 'Developer', 7000),
('Cathy', 'IT', 'Manager', 12000),
('David', 'HR', 'Recruiter', 5000),
('Ella', 'HR', 'Manager', 9000),
('Frank', 'HR', 'Recruiter', 4000),
('Grace', 'Sales', 'Salesman', 3000),
('Helen', 'Sales', 'Salesman', 3500),
('Ivy', 'Sales', 'Manager', 10000),
('Jack', 'Finance', 'Accountant', 8000),
('Kate', 'Finance', 'Manager', 9500);
-- 插入部门数据
INSERT INTO department (dept_name) VALUES
('IT'),
('HR'),
('Sales'),
('Finance');
HAVING示例
-- 统计每个部门人数,筛选人数大于 2 的部门
select dept, count(*) as emp_count
from employee
group by dept
having count(*) > 2;