详解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 的表，结构如下：

查询示例：

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 条记录。

查询结果：

二、 多表查询

1. 等值连接查询 (Equi-Join)

语法：

SELECT 列名
FROM 表1 AS 别名1
JOIN 表2 AS 别名2
ON 表1.列名 = 表2.列名;

示例：

假设有两个表：

表 employees：

表 departments：

查询员工姓名及其所在部门名称：

SELECT e.name, d.dept_name
FROM employees AS e
JOIN departments AS d
ON e.dept_id = d.dept_id;

结果：

分析：

• 等值连接查询通过 ON 子句将两个表的公共列用等值条件连接。
• 查询的结果集只包含满足连接条件的记录。

2. 非等值连接查询 (Non-Equi Join)

语法：

SELECT 列名
FROM 表1
JOIN 表2
ON 表1.列名 <> 表2.列名;

示例：
假设有一个表 grades 定义了工资的等级范围：

表 salaries：

表 grades：

查询每个员工的工资等级：

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;

结果：

分析：

• 非等值连接使用条件运算符（如 <, >, <=, >=, BETWEEN 等）连接表。
• 非等值连接通常用于范围查询。

3. 自然连接查询 (Natural Join)

语法：

SELECT 列名
FROM 表1
NATURAL JOIN 表2;

示例：
假设表 employees 和 departments 的公共列名为 dept_id。

查询员工姓名及其所在部门名称：

SELECT name, dept_name
FROM employees
NATURAL JOIN departments;

结果：

分析：

• 自然连接自动基于两个表中的同名列进行等值连接。
• 如果同名列的值不匹配，该行不会出现在结果中。

4. 复合条件连接查询

语法：

SELECT 列名
FROM 表1
JOIN 表2
ON 条件1 AND 条件2 OR 条件3;

示例：
假设有三个表：

表 students：

表 courses：

表 teachers：

查询学生姓名、所学课程名称及授课老师：

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;

结果：

分析：

• 复合条件连接可以通过多个 AND 或 OR 子句实现复杂的多条件连接。
• 查询需要逻辑上关联的多张表。

5. 自身连接查询 (Self Join)

语法：

SELECT a.列名1, b.列名2
FROM 表名 AS a
JOIN 表名 AS b
ON a.列名 = b.列名;

示例：
假设有一个表 employees，表示员工及其上级的关系：

表 employees：

查询每个员工及其上级姓名：

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;

结果：

分析：

• 自身连接在一张表中使用别名，作为两张表进行连接。
• 常用于层级结构的查询。

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：

表 departments：

查询所有员工及其所在部门（即使部门未匹配）：

SELECT e.name, d.dept_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.dept_id = d.dept_id;

结果：

分析：

• 外连接用于获取一个表中所有记录，即使另一表中没有匹配记录。
• 左外连接：保留左表的所有记录。
• 右外连接：保留右表的所有记录。
• 全外连接：保留两表的所有记录。

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')
);

分析：

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 salary
    FROM employees
    WHERE department_id = 3
);

分析：

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

示例 2：ALL 谓语

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

SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE 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 1
    FROM projects p
    WHERE p.employee_id = e.id
);

分析：

1. 子查询检查每个员工是否存在于 projects 表的 employee_id 中。
2. 如果子查询返回结果，则 EXISTS 谓语为真，主查询返回该员工的姓名。

特点：

• 子查询的返回值并不重要，只需确认是否存在符合条件的记录。
• 通常用来检查关联关系是否存在。

总结

四、集合查询

1. UNION

UNION 用于将两个查询的结果合并成一个结果集，同时去除重复的行。

语法：

SELECT column_list FROM table1
UNION
SELECT column_list FROM table2;

示例：
假设有两个表：

employees 表：

managers 表：

查询所有员工和经理的姓名（去重）：

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。

总结