跳到主要内容

SQL高级查询

概述​

SQL（Structured Query Language）是关系型数据库的标准语言，用于管理和操作关系型数据库。基础的SQL查询包括SELECT、INSERT、UPDATE和DELETE等操作，而高级SQL查询则包括复杂的连接查询、子查询、窗口函数、分组查询等。本教程将介绍SQL高级查询的各种技巧和方法，帮助你更高效地查询和分析数据库中的数据。

高级连接查询​

连接查询是SQL中最常用的高级查询技术之一，用于将两个或多个表中的数据根据它们之间的关系连接起来。

内连接(INNER JOIN)​

内连接是最常用的连接类型，它返回两个表中匹配的行。

语法：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

示例：

-- 查询订单和对应的客户信息
SELECT o.order_id, o.order_date, c.customer_name, c.customer_email
FROM orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id;

左连接(LEFT JOIN)​

左连接返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则返回NULL。

语法：

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

示例：

-- 查询所有客户及其订单信息（如果有的话）
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;

右连接(RIGHT JOIN)​

右连接返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则返回NULL。

语法：

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

示例：

-- 查询所有订单及其对应的产品信息（如果有的话）
SELECT o.order_id, o.order_date, p.product_id, p.product_name
FROM orders o
RIGHT JOIN products p
ON o.product_id = p.product_id;

全连接(FULL JOIN)​

全连接返回左表和右表中的所有行。如果一侧没有匹配的行，则返回NULL。

注意： MySQL不支持FULL JOIN，但可以通过UNION结合LEFT JOIN和RIGHT JOIN来实现。

语法：

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

示例：

-- PostgreSQL示例：查询所有客户和所有订单，包括没有订单的客户和没有客户的订单
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
FULL JOIN orders o
ON c.customer_id = o.customer_id;

-- MySQL替代方案
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
UNION
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id;

交叉连接(CROSS JOIN)​

交叉连接返回两个表的笛卡尔积，即左表中的每一行与右表中的每一行组合。

语法：

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

示例：

-- 查询所有客户和所有产品的组合
SELECT c.customer_name, p.product_name
FROM customers c
CROSS JOIN products p;

自连接(SELF JOIN)​

自连接是指表与自身进行连接，用于查询表中具有层次关系或父子关系的数据。

语法：

SELECT a.column_name, b.column_name
FROM table1 a, table1 b
WHERE a.related_column = b.related_column;

示例：

-- 查询员工及其经理信息
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;

高级子查询​

子查询是指嵌套在其他SQL语句中的查询语句。子查询可以用于SELECT、INSERT、UPDATE和DELETE语句中，也可以用于WHERE、HAVING和FROM子句中。

WHERE子句中的子查询​

在WHERE子句中使用子查询，用于过滤主查询的结果。

示例：

-- 查询订单金额大于平均订单金额的订单
SELECT order_id, order_amount
FROM orders
WHERE order_amount > (
    SELECT AVG(order_amount)
    FROM orders
);

FROM子句中的子查询​

在FROM子句中使用子查询，将子查询的结果作为一个临时表。

示例：

-- 查询每个客户的订单数量和平均订单金额
SELECT c.customer_id, c.customer_name, o.order_count, o.avg_order_amount
FROM customers c
INNER JOIN (
    SELECT customer_id, COUNT(*) AS order_count, AVG(order_amount) AS avg_order_amount
    FROM orders
    GROUP BY customer_id
) o
ON c.customer_id = o.customer_id;

SELECT子句中的子查询​

在SELECT子句中使用子查询，用于返回单个值。

示例：

-- 查询每个产品的名称和它的库存状态
SELECT product_id, product_name, (
    SELECT CASE 
        WHEN stock_quantity > 100 THEN '充足'
        WHEN stock_quantity > 0 THEN '紧张'
        ELSE '缺货'
    END
    FROM inventory
    WHERE inventory.product_id = products.product_id
) AS stock_status
FROM products;

EXISTS子查询​

EXISTS子查询用于检查子查询是否返回任何行，如果返回至少一行，则EXISTS返回TRUE，否则返回FALSE。

示例：

-- 查询至少有一个订单的客户
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

NOT EXISTS子查询​

NOT EXISTS子查询用于检查子查询是否没有返回任何行，如果没有返回任何行，则NOT EXISTS返回TRUE，否则返回FALSE。

示例：

-- 查询没有任何订单的客户
SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

ANY/SOME子查询​

ANY/SOME子查询用于将主查询中的值与子查询返回的任何值进行比较，如果主查询中的值与子查询返回的任何值满足比较条件，则ANY/SOME返回TRUE。

示例：

-- 查询订单金额大于任何一个订单金额小于1000的订单
SELECT order_id, order_amount
FROM orders
WHERE order_amount > ANY (
    SELECT order_amount
    FROM orders
    WHERE order_amount < 1000
);

ALL子查询​

ALL子查询用于将主查询中的值与子查询返回的所有值进行比较，如果主查询中的值与子查询返回的所有值都满足比较条件，则ALL返回TRUE。

示例：

-- 查询订单金额大于所有订单金额小于1000的订单
SELECT order_id, order_amount
FROM orders
WHERE order_amount > ALL (
    SELECT order_amount
    FROM orders
    WHERE order_amount < 1000
);

窗口函数​

窗口函数是SQL中的高级功能，用于在一组行上进行计算，并为每行返回一个结果。窗口函数可以用于排名、聚合、移动计算等场景。

基本语法​

SELECT column_name(s),
       window_function(expression) OVER (
           [PARTITION BY partition_column]
           [ORDER BY order_column]
           [ROWS/RANGE frame_specification]
       ) AS result_column
FROM table_name;

排名窗口函数​

ROW_NUMBER()​

为分区中的每行分配一个唯一的序号，序号从1开始，不考虑重复值。

示例：

-- 为每个部门的员工按薪资排序并分配行号
SELECT department_id, employee_name, salary,
       ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) AS row_num
FROM employees;

RANK()​

为分区中的每行分配一个排名，排名可能会有间隔（如果有重复值）。

示例：

-- 为每个部门的员工按薪资排名
SELECT department_id, employee_name, salary,
       RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) AS rank_num
FROM employees;

DENSE_RANK()​

为分区中的每行分配一个排名，排名不会有间隔（如果有重复值）。

示例：

-- 为每个部门的员工按薪资密集排名
SELECT department_id, employee_name, salary,
       DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) AS dense_rank_num
FROM employees;

NTILE()​

将分区中的行分成指定数量的组，并为每行分配一个组号。

示例：

-- 将员工按薪资分成4个组
SELECT employee_name, salary,
       NTILE(4) OVER (
           ORDER BY salary DESC
       ) AS tile_num
FROM employees;

聚合窗口函数​

SUM() OVER()​

计算分区中某列的累计和。

示例：

-- 计算每个部门员工的累计薪资
SELECT department_id, employee_name, salary,
       SUM(salary) OVER (
           PARTITION BY department_id
           ORDER BY employee_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_salary
FROM employees;

AVG() OVER()​

计算分区中某列的平均值。

示例：

-- 计算每个部门员工的平均薪资
SELECT department_id, employee_name, salary,
       AVG(salary) OVER (
           PARTITION BY department_id
       ) AS avg_department_salary
FROM employees;

MAX() OVER()​

计算分区中某列的最大值。

示例：

-- 计算每个部门员工的最高薪资
SELECT department_id, employee_name, salary,
       MAX(salary) OVER (
           PARTITION BY department_id
       ) AS max_department_salary
FROM employees;

MIN() OVER()​

计算分区中某列的最小值。

示例：

-- 计算每个部门员工的最低薪资
SELECT department_id, employee_name, salary,
       MIN(salary) OVER (
           PARTITION BY department_id
       ) AS min_department_salary
FROM employees;

偏移窗口函数​

LAG()​

返回分区中当前行的前N行的值。

示例：

-- 查询每个员工及其前一个员工的薪资
SELECT employee_id, employee_name, salary,
       LAG(salary, 1) OVER (
           ORDER BY employee_id
       ) AS previous_salary
FROM employees;

LEAD()​

返回分区中当前行的后N行的值。

示例：

-- 查询每个员工及其后一个员工的薪资
SELECT employee_id, employee_name, salary,
       LEAD(salary, 1) OVER (
           ORDER BY employee_id
       ) AS next_salary
FROM employees;

FIRST_VALUE()​

返回分区中第一行的值。

示例：

-- 查询每个部门的第一个员工的薪资
SELECT department_id, employee_name, salary,
       FIRST_VALUE(salary) OVER (
           PARTITION BY department_id
           ORDER BY employee_id
       ) AS first_salary
FROM employees;

LAST_VALUE()​

返回分区中最后一行的值。

示例：

-- 查询每个部门的最后一个员工的薪资
SELECT department_id, employee_name, salary,
       LAST_VALUE(salary) OVER (
           PARTITION BY department_id
           ORDER BY employee_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_salary
FROM employees;

高级分组查询​

GROUP BY增强​

GROUP BY ROLLUP​

ROLLUP操作符用于生成分组的小计和总计。

示例：

-- 按部门和职位分组，计算薪资总和，并生成小计和总计
SELECT department_id, job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP (department_id, job_title);

GROUP BY CUBE​

CUBE操作符用于生成所有可能的分组组合的小计和总计。

示例：

-- 按部门和职位的所有可能组合分组，计算薪资总和
SELECT department_id, job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE (department_id, job_title);

GROUP BY GROUPING SETS​

GROUPING SETS操作符用于指定多个分组组合。

示例：

-- 按部门分组和按职位分组，计算薪资总和
SELECT department_id, job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (department_id, job_title);

HAVING子句高级用法​

HAVING子句用于过滤分组后的结果，类似于WHERE子句，但WHERE子句用于过滤行，而HAVING子句用于过滤分组。

示例：

-- 查询平均薪资大于5000的部门
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

-- 查询员工数量大于10且平均薪资大于5000的部门
SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10 AND AVG(salary) > 5000;

高级数据操作​

条件表达式​

CASE表达式​

CASE表达式用于在SQL语句中实现条件逻辑。

语法：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END;

示例：

-- 根据薪资等级对员工进行分类
SELECT employee_name, salary,
       CASE
           WHEN salary >= 10000 THEN '高级'
           WHEN salary >= 5000 THEN '中级'
           ELSE '初级'
       END AS salary_level
FROM employees;

-- 计算每个部门的不同等级员工数量
SELECT department_id,
       COUNT(CASE WHEN salary >= 10000 THEN 1 END) AS senior_count,
       COUNT(CASE WHEN salary >= 5000 AND salary < 10000 THEN 1 END) AS middle_count,
       COUNT(CASE WHEN salary < 5000 THEN 1 END) AS junior_count
FROM employees
GROUP BY department_id;

IF函数​

IF函数是MySQL中的特有函数，用于实现简单的条件逻辑。

语法：

IF(condition, value_if_true, value_if_false);

示例：

-- 判断员工薪资是否大于5000
SELECT employee_name, salary,
       IF(salary > 5000, '高薪资', '低薪资') AS salary_status
FROM employees;

COALESCE函数​

COALESCE函数用于返回参数列表中的第一个非NULL值。

语法：

COALESCE(expression1, expression2, ..., expressionN);

示例：

-- 使用COALESCE处理NULL值
SELECT employee_name, COALESCE(phone_number, '未提供') AS contact_phone
FROM employees;

字符串函数​

字符串拼接​

  • CONCAT函数：用于拼接字符串（所有主流数据库都支持）
  • ||操作符：用于拼接字符串（Oracle、PostgreSQL支持）

示例：

-- MySQL和SQL Server
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- Oracle和PostgreSQL
SELECT first_name || ' ' || last_name AS full_name
FROM employees;

字符串截取​

  • SUBSTRING函数：用于截取字符串的一部分
  • LEFT/RIGHT函数：用于截取字符串的左侧或右侧部分

示例：

-- 截取员工邮箱的域名部分
SELECT email, SUBSTRING(email, POSITION('@' IN email) + 1) AS email_domain
FROM employees;

-- 截取员工姓名的前3个字符
SELECT first_name, LEFT(first_name, 3) AS short_name
FROM employees;

字符串替换​

  • REPLACE函数：用于替换字符串中的指定部分

示例：

-- 将员工邮箱中的@example.com替换为@company.com
SELECT email, REPLACE(email, '@example.com', '@company.com') AS new_email
FROM employees;

字符串大小写转换​

  • UPPER/LOWER函数：用于将字符串转换为大写或小写

示例：

-- 将员工姓名转换为大写
SELECT UPPER(first_name) AS upper_first_name
FROM employees;

-- 将员工姓名转换为小写
SELECT LOWER(last_name) AS lower_last_name
FROM employees;

日期和时间函数​

获取当前日期和时间​

  • NOW函数：返回当前日期和时间
  • CURDATE/CURRENT_DATE函数：返回当前日期
  • CURTIME/CURRENT_TIME函数：返回当前时间

示例：

-- 获取当前日期和时间
SELECT NOW() AS current_datetime;

-- 获取当前日期
SELECT CURDATE() AS current_date;

-- 获取当前时间
SELECT CURTIME() AS current_time;

日期和时间的计算​

  • DATE_ADD/DATE_SUB函数：用于日期和时间的加减运算
  • DATEDIFF函数：用于计算两个日期之间的天数差

示例：

-- 计算3天后的日期
SELECT DATE_ADD(NOW(), INTERVAL 3 DAY) AS three_days_later;

-- 计算3个月前的日期
SELECT DATE_SUB(NOW(), INTERVAL 3 MONTH) AS three_months_ago;

-- 计算员工的入职天数
SELECT employee_name, DATEDIFF(NOW(), hire_date) AS days_employed
FROM employees;

日期和时间的格式化​

  • DATE_FORMAT函数（MySQL）：用于格式化日期和时间
  • TO_CHAR函数（Oracle、PostgreSQL）：用于格式化日期和时间

示例：

-- MySQL：格式化当前日期为'YYYY-MM-DD'格式
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS formatted_date;

-- PostgreSQL：格式化当前日期为'YYYY-MM-DD'格式
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD') AS formatted_date;

SQL优化技巧​

索引优化​

  • 为经常用于查询条件、排序、分组和连接的列创建索引
  • 避免为经常更新的列创建索引
  • 避免为基数低的列创建索引（如性别、状态等）
  • 考虑使用复合索引，遵循最左前缀原则
  • 定期维护和优化索引

查询优化​

  • 只选择需要的列，避免使用SELECT *
  • 使用WHERE子句过滤数据，减少结果集的大小
  • 使用LIMIT子句限制结果集的大小
  • 避免在WHERE子句中使用函数或表达式，这会导致索引失效
  • 避免在WHERE子句中使用!=、<>、NOT IN、NOT EXISTS等操作符，这会导致全表扫描
  • 对于复杂查询，考虑使用子查询、视图或存储过程

连接优化​

  • 优先使用INNER JOIN，避免使用OUTER JOIN
  • 对于大表，考虑使用临时表或表变量
  • 连接的表的顺序会影响查询性能，将小表放在前面
  • 使用表别名，简化SQL语句

事务优化​

  • 保持事务的简短，减少锁定时间
  • 避免在事务中执行不必要的操作
  • 使用合适的隔离级别，平衡并发性能和数据一致性
  • 避免长时间持有锁，导致死锁

总结​

SQL高级查询是数据库开发和管理中的重要技能，掌握SQL高级查询技术可以帮助你更高效地查询和分析数据库中的数据。本教程介绍了SQL高级查询的各种技巧和方法，包括高级连接查询、高级子查询、窗口函数、高级分组查询、高级数据操作和SQL优化技巧。

在实际的数据库开发和管理中，需要根据具体的业务需求和数据库系统的特性，灵活运用这些技巧和方法，以提高查询性能和数据处理效率。同时，也需要不断学习和掌握新的SQL特性和优化技术，以适应不断变化的业务需求和技术发展。