详解SQL数据查询功能
数据查询
- 一、 单表查询
- 1. 选择表中的若干列
- 2. 选择表中的若干元组
- 3. `ORDER BY` 子句
- 4. 聚合函数
- 5. `GROUP BY` 子句
- 6. `LIMIT` 子句
- 综合示例:
- 二、 多表查询
- 1. 等值连接查询 (Equi-Join)
- 2. 非等值连接查询 (Non-Equi Join)
- 3. 自然连接查询 (Natural Join)
- 4. 复合条件连接查询
- 5. 自身连接查询 (Self Join)
- 6. 外连接查询 (Outer Join)
- 7. 多表连接查询
- 总结
- 三、 嵌套查询
- 1. 带有 `IN` 谓语的子查询
- 2. 带有比较运算符的子查询
- 3. 带有 `ANY(SOME)` 或 `ALL` 谓语的子查询
- 示例 1:`ANY` 谓语
- 示例 2:`ALL` 谓语
- 4. 带有 `EXISTS` 谓语的子查询
- 总结
- 四、集合查询
- 1. UNION
- 2. UNION ALL
- 3. INTERSECT
- 4. EXCEPT
- 注意事项
- 总结
一、 单表查询
1. 选择表中的若干列
语法:
SELECT column1, column2, ...
FROM table_name;
column1, column2
: 表中需要查询的列。table_name
: 查询的表名。
示例:
SELECT name, age
FROM students;
分析:
从 students
表中查询 name
和 age
两列,结果只显示这两列数据。
2. 选择表中的若干元组
语法:
SELECT *
FROM table_name
WHERE condition;
*
: 表示选择所有列,或者可以指定具体列。WHERE condition
: 查询满足指定条件的元组。
示例:
SELECT *
FROM students
WHERE age > 18;
分析:
查询 students
表中所有 age
大于 18 的元组,并返回这些元组的所有列。
3. ORDER BY
子句
语法:
SELECT column1, column2
FROM table_name
ORDER BY column_name [ASC|DESC];
ORDER BY
: 用于指定排序列。ASC
: 升序(默认)。DESC
: 降序。
示例:
SELECT name, age
FROM students
ORDER BY age DESC;
分析:
从 students
表中查询 name
和 age
两列,并按 age
降序排列。
4. 聚合函数
语法:
SELECT aggregate_function(column_name)
FROM table_name;
- 常见聚合函数:
COUNT(column_name)
: 统计记录数。SUM(column_name)
: 计算总和。AVG(column_name)
: 计算平均值。MAX(column_name)
: 获取最大值。MIN(column_name)
: 获取最小值。
示例:
SELECT AVG(age) AS average_age
FROM students;
分析:
计算 students
表中 age
列的平均值,并将结果命名为 average_age
。
5. GROUP BY
子句
语法:
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;
GROUP BY
: 用于对查询结果按指定列分组。- 常与聚合函数配合使用。
示例:
SELECT department, COUNT(*) AS student_count
FROM students
GROUP BY department;
分析:
按 department
列对 students
表中的数据分组,并统计每个 department
的学生数量,结果命名为 student_count
。
6. LIMIT
子句
语法:
SELECT column1, column2
FROM table_name
LIMIT number_of_rows;
LIMIT
: 用于限制返回的记录数。
示例:
SELECT *
FROM students
LIMIT 5;
分析:
从 students
表中查询前 5 条记录。
综合示例:
假设有一张名为 students
的表,结构如下:
id | name | age | department | grade |
---|---|---|---|---|
1 | Alice | 20 | Computer Sci | 90 |
2 | Bob | 22 | Math | 85 |
3 | Charlie | 19 | Computer Sci | 95 |
4 | David | 21 | Physics | 88 |
5 | Eva | 20 | Math | 92 |
查询示例:
SELECT department, AVG(grade) AS average_grade
FROM students
WHERE age > 20
GROUP BY department
ORDER BY average_grade DESC
LIMIT 2;
分析:
- 筛选出
age > 20
的学生。 - 按
department
列分组。 - 计算每个
department
的平均成绩average_grade
。 - 按平均成绩降序排列。
- 返回前 2 条记录。
查询结果:
department | average_grade |
---|---|
Math | 85 |
Physics | 88 |
二、 多表查询
1. 等值连接查询 (Equi-Join)
语法:
SELECT 列名
FROM 表1 AS 别名1
JOIN 表2 AS 别名2
ON 表1.列名 = 表2.列名;
示例:
假设有两个表:
表 employees
:
emp_id | name | dept_id |
---|---|---|
1 | 张三 | 101 |
2 | 李四 | 102 |
3 | 王五 | 103 |
表 departments
:
dept_id | dept_name |
---|---|
101 | 财务部 |
102 | 人事部 |
103 | 技术部 |
查询员工姓名及其所在部门名称:
SELECT e.name, d.dept_name
FROM employees AS e
JOIN departments AS d
ON e.dept_id = d.dept_id;
结果:
name | dept_name |
---|---|
张三 | 财务部 |
李四 | 人事部 |
王五 | 技术部 |
分析:
- 等值连接查询通过
ON
子句将两个表的公共列用等值条件连接。 - 查询的结果集只包含满足连接条件的记录。
2. 非等值连接查询 (Non-Equi Join)
语法:
SELECT 列名
FROM 表1
JOIN 表2
ON 表1.列名 <> 表2.列名;
示例:
假设有一个表 grades
定义了工资的等级范围:
表 salaries
:
emp_id | salary |
---|---|
1 | 5000 |
2 | 8000 |
3 | 12000 |
表 grades
:
grade | min_salary | max_salary |
---|---|---|
A | 10000 | 15000 |
B | 7000 | 9999 |
C | 4000 | 6999 |
查询每个员工的工资等级:
SELECT s.emp_id, s.salary, g.grade
FROM salaries AS s
JOIN grades AS g
ON s.salary BETWEEN g.min_salary AND g.max_salary;
结果:
emp_id | salary | grade |
---|---|---|
1 | 5000 | C |
2 | 8000 | B |
3 | 12000 | A |
分析:
- 非等值连接使用条件运算符(如
<
,>
,<=
,>=
,BETWEEN
等)连接表。 - 非等值连接通常用于范围查询。
3. 自然连接查询 (Natural Join)
语法:
SELECT 列名
FROM 表1
NATURAL JOIN 表2;
示例:
假设表 employees
和 departments
的公共列名为 dept_id
。
查询员工姓名及其所在部门名称:
SELECT name, dept_name
FROM employees
NATURAL JOIN departments;
结果:
name | dept_name |
---|---|
张三 | 财务部 |
李四 | 人事部 |
王五 | 技术部 |
分析:
- 自然连接自动基于两个表中的同名列进行等值连接。
- 如果同名列的值不匹配,该行不会出现在结果中。
4. 复合条件连接查询
语法:
SELECT 列名
FROM 表1
JOIN 表2
ON 条件1 AND 条件2 OR 条件3;
示例:
假设有三个表:
表 students
:
student_id | name | course_id |
---|---|---|
1 | 张三 | 101 |
2 | 李四 | 102 |
3 | 王五 | 103 |
表 courses
:
course_id | course_name | teacher_id |
---|---|---|
101 | 数学 | 1 |
102 | 英语 | 2 |
103 | 物理 | 3 |
表 teachers
:
teacher_id | teacher_name |
---|---|
1 | 王老师 |
2 | 李老师 |
3 | 张老师 |
查询学生姓名、所学课程名称及授课老师:
SELECT s.name AS student_name, c.course_name, t.teacher_name
FROM students AS s
JOIN courses AS c
ON s.course_id = c.course_id
JOIN teachers AS t
ON c.teacher_id = t.teacher_id;
结果:
student_name | course_name | teacher_name |
---|---|---|
张三 | 数学 | 王老师 |
李四 | 英语 | 李老师 |
王五 | 物理 | 张老师 |
分析:
- 复合条件连接可以通过多个
AND
或OR
子句实现复杂的多条件连接。 - 查询需要逻辑上关联的多张表。
5. 自身连接查询 (Self Join)
语法:
SELECT a.列名1, b.列名2
FROM 表名 AS a
JOIN 表名 AS b
ON a.列名 = b.列名;
示例:
假设有一个表 employees
,表示员工及其上级的关系:
表 employees
:
emp_id | name | manager_id |
---|---|---|
1 | 张三 | NULL |
2 | 李四 | 1 |
3 | 王五 | 1 |
查询每个员工及其上级姓名:
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.emp_id;
结果:
employee_name | manager_name |
---|---|
张三 | NULL |
李四 | 张三 |
王五 | 张三 |
分析:
- 自身连接在一张表中使用别名,作为两张表进行连接。
- 常用于层级结构的查询。
6. 外连接查询 (Outer Join)
语法:
-- 左外连接
SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 表1.列名 = 表2.列名;
-- 右外连接
SELECT 列名
FROM 表1
RIGHT JOIN 表2
ON 表1.列名 = 表2.列名;
-- 全外连接
SELECT 列名
FROM 表1
FULL OUTER JOIN 表2
ON 表1.列名 = 表2.列名;
示例:
假设有两个表:
表 employees
:
emp_id | name | dept_id |
---|---|---|
1 | 张三 | 101 |
2 | 李四 | 102 |
表 departments
:
dept_id | dept_name |
---|---|
101 | 财务部 |
103 | 技术部 |
查询所有员工及其所在部门(即使部门未匹配):
SELECT e.name, d.dept_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.dept_id = d.dept_id;
结果:
name | dept_name |
---|---|
张三 | 财务部 |
李四 | NULL |
分析:
- 外连接用于获取一个表中所有记录,即使另一表中没有匹配记录。
- 左外连接:保留左表的所有记录。
- 右外连接:保留右表的所有记录。
- 全外连接:保留两表的所有记录。
7. 多表连接查询
语法:
SELECT 列名
FROM 表1
JOIN 表2
ON 条件1
JOIN 表3
ON 条件2;
示例:
参照 复合条件连接查询 中的三表连接示例。
分析:
- 多表连接可以任意组合等值连接、外连接、非等值连接等。
- 每次添加新表需要通过
ON
子句指定与其他表的连接条件。
总结
类型 | 关键特点 | 适用场景 |
---|---|---|
等值连接 | 使用 = 条件 | 查找两个表中匹配记录 |
非等值连接 | 使用范围条件 | 范围匹配 |
自然连接 | 基于公共列的同名和等值条件自动匹配 | 自动匹配同名列 |
复合条件连接 | 多个条件组合 | 多条件复杂查询 |
自身连接 | 表与自身的关联 | 层级关系 |
外连接 | 保留一张表中的所有记录 | 查询非匹配记录 |
多表连接 | 同时连接多张表 | 复杂查询 |
三、 嵌套查询
1. 带有 IN
谓语的子查询
语法:
SELECT 列名
FROM 表名
WHERE 列名 IN (子查询);
示例:
假设有两张表:
employees
:员工表,包含字段id
,name
,department_id
departments
:部门表,包含字段id
,name
查询属于某些特定部门的员工:
SELECT name
FROM employees
WHERE department_id IN (
SELECT id
FROM departments
WHERE name IN ('HR', 'Sales')
);
分析:
- 子查询首先从
departments
表中选出名称为 “HR” 和 “Sales” 的部门id
。 - 主查询将这些
id
与employees
表中的department_id
进行匹配,选出符合条件的员工姓名。
特点:
- 子查询结果是一组值,适用于
IN
谓语。 IN
用于判断字段值是否存在于子查询的返回结果集中。
2. 带有比较运算符的子查询
语法:
SELECT 列名
FROM 表名
WHERE 列名 运算符 (子查询);
示例:
查询工资大于公司平均工资的员工:
假设 employees
表中包含字段 id
, name
, salary
。
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
分析:
- 子查询计算
employees
表中员工的平均工资。 - 主查询筛选出工资高于该平均值的员工。
特点:
- 子查询返回单一值(如
SUM
,AVG
,MAX
等)。 - 运算符可以是
=
,>
,<
,>=
,<=
, 或<>
等。
3. 带有 ANY(SOME)
或 ALL
谓语的子查询
语法:
ANY(SOME)
:至少满足子查询结果中的某一个条件。
SELECT 列名
FROM 表名
WHERE 列名 运算符 ANY (子查询);
ALL
:必须满足子查询结果中的所有条件。
SELECT 列名
FROM 表名
WHERE 列名 运算符 ALL (子查询);
示例 1:ANY
谓语
查询工资大于某个部门中任意一个员工的工资的员工:
SELECT name, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department_id = 3
);
分析:
- 子查询返回部门
id = 3
的所有员工工资。 - 主查询筛选出工资比部门
3
中至少一名员工工资高的员工。
示例 2:ALL
谓语
查询工资高于所有部门 3
员工的员工:
SELECT name, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department_id = 3
);
分析:
- 子查询返回部门
id = 3
的所有员工工资。 - 主查询筛选出工资比部门
3
中所有员工工资都高的员工。
特点:
ANY
(或等价的SOME
)适合查找与子查询结果中任意值匹配的记录。ALL
用于查找符合子查询结果中所有值的记录。
4. 带有 EXISTS
谓语的子查询
语法:
SELECT 列名
FROM 表名
WHERE EXISTS (子查询);
示例:
查询至少参与了一个项目的员工:
假设有两张表:
employees
:员工表,包含字段id
,name
projects
:项目表,包含字段id
,employee_id
,project_name
SELECT name
FROM employees e
WHERE EXISTS (
SELECT 1
FROM projects p
WHERE p.employee_id = e.id
);
分析:
- 子查询检查每个员工是否存在于
projects
表的employee_id
中。 - 如果子查询返回结果,则
EXISTS
谓语为真,主查询返回该员工的姓名。
特点:
- 子查询的返回值并不重要,只需确认是否存在符合条件的记录。
- 通常用来检查关联关系是否存在。
总结
谓语 | 子查询返回值类型 | 用途 |
---|---|---|
IN | 一组值 | 判断字段值是否存在于返回结果集中 |
比较运算符 | 单一值 | 用于比较字段值与子查询的结果 |
ANY | 一组值 | 判断字段值是否与子查询结果中任意值匹配 |
ALL | 一组值 | 判断字段值是否符合子查询结果中所有值 |
EXISTS | 判断是否存在记录(无具体值) | 检查子查询是否返回任何记录 |
四、集合查询
1. UNION
UNION
用于将两个查询的结果合并成一个结果集,同时去除重复的行。
语法:
SELECT column_list FROM table1
UNION
SELECT column_list FROM table2;
示例:
假设有两个表:
employees
表:
id | name | department |
---|---|---|
1 | Alice | HR |
2 | Bob | Sales |
3 | Charlie | IT |
managers
表:
id | name | department |
---|---|---|
4 | David | HR |
5 | Emily | IT |
6 | Bob | Sales |
查询所有员工和经理的姓名(去重):
SELECT name FROM employees
UNION
SELECT name FROM managers;
结果:
name
-----
Alice
Bob
Charlie
David
Emily
分析:
UNION
会自动去除重复的行,因此Bob
只会出现一次。- 数据必须具有相同的列数和兼容的数据类型。
2. UNION ALL
UNION ALL
与 UNION
类似,但它不去重,会包含所有重复的行。
语法:
SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2;
示例:
查询所有员工和经理的姓名(包括重复):
SELECT name FROM employees
UNION ALL
SELECT name FROM managers;
结果:
name
-----
Alice
Bob
Charlie
David
Emily
Bob
分析:
UNION ALL
的性能优于UNION
,因为它省略了去重操作。- 当不需要去重时,推荐使用
UNION ALL
。
3. INTERSECT
INTERSECT
用于返回两个查询结果中公共的部分。
语法:
SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2;
示例:
查询同时是员工和经理的姓名:
SELECT name FROM employees
INTERSECT
SELECT name FROM managers;
结果:
name
-----
Bob
分析:
- 只有两个查询结果中都存在的行才会被返回。
- 结果集会自动去重。
4. EXCEPT
EXCEPT
用于返回第一个查询结果中有,但第二个查询结果中没有的行。
语法:
SELECT column_list FROM table1
EXCEPT
SELECT column_list FROM table2;
示例:
查询仅是员工但不是经理的姓名:
SELECT name FROM employees
EXCEPT
SELECT name FROM managers;
结果:
name
-----
Alice
Charlie
分析:
- 结果是从第一个查询中排除了与第二个查询重复的部分。
- 自动去重。
注意事项
- 列数和数据类型必须一致:
- 集合查询的两个或多个子查询中,
SELECT
子句中的列数必须相同。 - 每列的数据类型必须兼容(例如,
VARCHAR
和CHAR
是兼容的,但VARCHAR
和INT
不是)。
- 集合查询的两个或多个子查询中,
- 排序:
- 如果需要对结果集排序,可以在集合操作后使用
ORDER BY
,例如:SELECT name FROM employees UNION SELECT name FROM managers ORDER BY name;
- 如果需要对结果集排序,可以在集合操作后使用
- 性能:
UNION
和INTERSECT
由于会去重,性能可能较差。- 如果可以接受重复值,推荐使用
UNION ALL
。
总结
操作符 | 功能描述 | 是否去重 |
---|---|---|
UNION | 合并两个结果集 | 是 |
UNION ALL | 合并两个结果集 | 否 |
INTERSECT | 返回两个结果集的交集 | 是 |
EXCEPT | 返回第一个结果集中独有的部分 | 是 |