详解SQL数据查询功能

一、 单表查询

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;

分析：

1. 筛选出 age > 20 的学生。
2. 按 department 列分组。
3. 计算每个 department 的平均成绩 average_grade。
4. 按平均成绩降序排列。
5. 返回前 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 idFROM departmentsWHERE name IN ('HR', 'Sales')
);

分析：

1. 子查询首先从 departments 表中选出名称为 “HR” 和 “Sales” 的部门 id。
2. 主查询将这些 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
);

分析：

1. 子查询计算 employees 表中员工的平均工资。
2. 主查询筛选出工资高于该平均值的员工。

特点：

• 子查询返回单一值（如 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 salaryFROM employeesWHERE department_id = 3
);

分析：

1. 子查询返回部门 id = 3 的所有员工工资。
2. 主查询筛选出工资比部门 3 中至少一名员工工资高的员工。

示例 2：ALL 谓语

查询工资高于所有部门 3 员工的员工：

SELECT name, salary
FROM employees
WHERE salary > ALL (SELECT salaryFROM employeesWHERE department_id = 3
);

分析：

1. 子查询返回部门 id = 3 的所有员工工资。
2. 主查询筛选出工资比部门 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 1FROM projects pWHERE p.employee_id = e.id
);

分析：

1. 子查询检查每个员工是否存在于 projects 表的 employee_id 中。
2. 如果子查询返回结果，则 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

分析：

• 结果是从第一个查询中排除了与第二个查询重复的部分。
• 自动去重。

注意事项

1. 列数和数据类型必须一致：
   • 集合查询的两个或多个子查询中，SELECT 子句中的列数必须相同。
   • 每列的数据类型必须兼容（例如，VARCHAR 和 CHAR 是兼容的，但 VARCHAR 和 INT 不是）。
2. 排序：
   • 如果需要对结果集排序，可以在集合操作后使用 ORDER BY，例如：

     SELECT name FROM employees
     UNION
     SELECT name FROM managers
     ORDER BY name;
     

3. 性能：
   • UNION 和 INTERSECT 由于会去重，性能可能较差。
   • 如果可以接受重复值，推荐使用 UNION ALL。

总结

操作符	功能描述	是否去重
UNION	合并两个结果集	是
UNION ALL	合并两个结果集	否
INTERSECT	返回两个结果集的交集	是
EXCEPT	返回第一个结果集中独有的部分	是