当前位置: 首页 > article >正文

详解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 表中查询 nameage 两列,结果只显示这两列数据。

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 表中查询 nameage 两列,并按 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 的表,结构如下:

idnameagedepartmentgrade
1Alice20Computer Sci90
2Bob22Math85
3Charlie19Computer Sci95
4David21Physics88
5Eva20Math92

查询示例:

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

查询结果:

departmentaverage_grade
Math85
Physics88

二、 多表查询

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

语法:

SELECT 列名
FROM1 AS 别名1
JOIN2 AS 别名2
ON1.列名 =2.列名;

示例:

假设有两个表:

employees

emp_idnamedept_id
1张三101
2李四102
3王五103

departments

dept_iddept_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;

结果:

namedept_name
张三财务部
李四人事部
王五技术部

分析:

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

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

语法:

SELECT 列名
FROM1
JOIN2
ON1.列名 <>2.列名;

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

salaries

emp_idsalary
15000
28000
312000

grades

grademin_salarymax_salary
A1000015000
B70009999
C40006999

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

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_idsalarygrade
15000C
28000B
312000A

分析:

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

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

语法:

SELECT 列名
FROM1
NATURAL JOIN2;

示例:
假设表 employeesdepartments 的公共列名为 dept_id

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

SELECT name, dept_name
FROM employees
NATURAL JOIN departments;

结果:

namedept_name
张三财务部
李四人事部
王五技术部

分析:

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

4. 复合条件连接查询

语法:

SELECT 列名
FROM1
JOIN2
ON 条件1 AND 条件2 OR 条件3;

示例:
假设有三个表:

students

student_idnamecourse_id
1张三101
2李四102
3王五103

courses

course_idcourse_nameteacher_id
101数学1
102英语2
103物理3

teachers

teacher_idteacher_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_namecourse_nameteacher_name
张三数学王老师
李四英语李老师
王五物理张老师

分析:

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

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

语法:

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

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

employees

emp_idnamemanager_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_namemanager_name
张三NULL
李四张三
王五张三

分析:

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

6. 外连接查询 (Outer Join)

语法:

-- 左外连接
SELECT 列名
FROM1
LEFT JOIN2
ON1.列名 =2.列名;

-- 右外连接
SELECT 列名
FROM1
RIGHT JOIN2
ON1.列名 =2.列名;

-- 全外连接
SELECT 列名
FROM1
FULL OUTER JOIN2
ON1.列名 =2.列名;

示例:
假设有两个表:

employees

emp_idnamedept_id
1张三101
2李四102

departments

dept_iddept_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;

结果:

namedept_name
张三财务部
李四NULL

分析:

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

7. 多表连接查询

语法:

SELECT 列名
FROM1
JOIN2
ON 条件1
JOIN3
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. 主查询将这些 idemployees 表中的 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 谓语为真,主查询返回该员工的姓名。

特点:

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

总结

谓语子查询返回值类型用途
IN一组值判断字段值是否存在于返回结果集中
比较运算符单一值用于比较字段值与子查询的结果
ANY一组值判断字段值是否与子查询结果中任意值匹配
ALL一组值判断字段值是否符合子查询结果中所有值
EXISTS判断是否存在记录(无具体值)检查子查询是否返回任何记录

四、集合查询

1. UNION

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

语法:

SELECT column_list FROM table1
UNION
SELECT column_list FROM table2;

示例:
假设有两个表:

employees 表:

idnamedepartment
1AliceHR
2BobSales
3CharlieIT

managers 表:

idnamedepartment
4DavidHR
5EmilyIT
6BobSales

查询所有员工和经理的姓名(去重):

SELECT name FROM employees
UNION
SELECT name FROM managers;

结果:

name
-----
Alice
Bob
Charlie
David
Emily

分析:

  • UNION 会自动去除重复的行,因此 Bob 只会出现一次。
  • 数据必须具有相同的列数和兼容的数据类型。

2. UNION ALL

UNION ALLUNION 类似,但它不去重,会包含所有重复的行。

语法:

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 子句中的列数必须相同。
    • 每列的数据类型必须兼容(例如,VARCHARCHAR 是兼容的,但 VARCHARINT 不是)。
  2. 排序
    • 如果需要对结果集排序,可以在集合操作后使用 ORDER BY,例如:
      SELECT name FROM employees
      UNION
      SELECT name FROM managers
      ORDER BY name;
      
  3. 性能
    • UNIONINTERSECT 由于会去重,性能可能较差。
    • 如果可以接受重复值,推荐使用 UNION ALL

总结

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

http://www.kler.cn/a/585423.html

相关文章:

  • 如何在Django中有效地使用Celery进行定时任务?
  • 查找特定的值(信息学奥赛一本通-1110)
  • 17.使用读写包操作Excel文件:pyxlsb 包
  • Interview preparation.md
  • 分享一个工具可以国内无限制访问GitHub(来源于GitHub开源项目)
  • Linux--操作系统/进程
  • golang算法二叉搜索树
  • 游戏引擎学习第158天
  • 图像分割技术深度解析:语义、实例与全景分割,及FCN、U-Net、Mask R-CNN、UPSNet的应用
  • Pycharm实用技巧
  • 泛目录技术:智能缓存提升网站速度与稳定性
  • 【Qt】QWidget属性介绍
  • 便捷开启 PDF 功能之旅,绿色软件随心用
  • CUDA编程之内存零拷贝技术
  • 保姆级离线TiDB V8+解释
  • C语言练习四(vscode)循环语句专练
  • linux中yum和wget指令的区别
  • 【 <一> 炼丹初探:JavaWeb 的起源与基础】之 Servlet 3.0 新特性:异步处理与注解配置
  • 上下左右移动的悬浮框/气泡
  • 基础知识《Redis解析》