常用SQL查询方法与实例

目录

SELECT查询

INSERT查询

UPDATE查询

DELETE查询

JOIN查询

GROUP BY查询

HAVING查询

窗口函数

公共表表达式（CTEs）

递归查询

透视表

分析函数

解透视

条件聚合

日期函数

合并语句

 情况语句

常用SQL查询方法有以下几种：

1. SELECT：用于从数据库表中检索数据。
2. WHERE：用于过滤结果集，只返回满足指定条件的记录。
3. ORDER BY：用于对结果集进行排序。
4. GROUP BY：用于将结果集按照一个或多个列进行分组。
5. HAVING：用于过滤分组后的结果集，只返回满足指定条件的分组。
6. JOIN：用于连接两个或多个表，根据指定的条件返回匹配的记录。
7. UNION：用于合并两个或多个SELECT语句的结果集。
8. LIMIT：用于限制返回的结果集数量。
9. OFFSET：用于指定返回结果集的起始位置。

SELECT查询

用于从数据表中选择数据。

SELECT first_name, last_name  
FROM employees  
WHERE department = 'HR';

INSERT查询

用于向数据表中插入新数据。

INSERT INTO customers (first_name, last_name, email)  
VALUES ('John', 'Doe', 'john.doe@example.***');

UPDATE查询

用于更新数据表中的数据。

UPDATE employees  
SET salary = 5000  
WHERE employee_id = 1;

DELETE查询

用于从数据表中删除数据。

DELETE FROM orders  
WHERE order_id = 1001;

JOIN查询

用于从多个表中检索相关数据。

SELECT customers.first_name, orders.order_date  
FROM customers  
JOIN orders ON customers.customer_id = orders.customer_id  
WHERE order_date >= '2023-01-01';

GROUP BY查询

用于根据一个或多个列对结果集进行分组。

SELECT department, COUNT(*) as number_of_employees  
FROM employees  
GROUP BY department;

HAVING查询

用于对GROUP BY子句的结果进行过滤。

SELECT department, AVG(salary) as average_salary  
FROM employees  
GROUP BY department  
HAVING average_salary > 5000;

选择所有列

SELECT * FROM students;
这将从students表中选择所有记录的所有列。

选择特定列并重命名

SELECT name AS 姓名, age FROM students;
此查询仅选择name和age列，并将name列在结果集中重命名为姓名。

条件查询（等于）

SELECT * FROM students WHERE age = 20;
查询年龄为20岁的学生的所有信息。

条件查询（范围）

SELECT * FROM students WHERE age BETWEEN 20 AND 30;
查询年龄在20到30岁之间的所有学生信息。

模糊查询（LIKE关键字）

SELECT * FROM students WHERE name LIKE '李%';
查找所有名字以“李”开头的学生信息。

使用IN关键字限定多个值

SELECT * FROM students WHERE native IN ('湖南', '四川');
查找来自湖南或四川的学生的所有信息。

连接查询

SELECT s.id, s.name, c.course_name 
FROM students AS s 
LEFT JOIN courses AS c ON s.course_id = c.id;
连接students和courses表，显示每个学生的ID、姓名及其选修的课程名称。

空值判断

SELECT * FROM students WHERE gender IS NULL;
查找gender字段为空的所有学生记录。

排序查询

SELECT name, age FROM students ORDER BY age DESC;
查询所有学生的名字和年龄，并按年龄降序排列。

分组与聚合查询

SELECT gender, COUNT(*) as total_students 
FROM students 
GROUP BY gender;

窗口函数

窗口函数是指在SQL查询中对一组相关行进行聚合或运算操作的函数。窗口函数可以在不改变基本表的情况下，为查询结果添加额外的计算列。举个例子，使用SUM()函数与OVER()子句计算销售额的运行总和。

SELECT date, sales,
       SUM(sales) OVER (ORDER BY date) AS running_total
FROM sales_data;

公共表表达式（CTEs）

CTE（***mon Table Expressions，公共表表达式）是一种在SQL查询中创建临时结果集的方法，可以被多次引用，提高查询的可读性和可维护性。以下是如何使用CTE计算每个产品类别的总收入的示例。

WITH category_revenue AS (
    SELECT category, SUM(revenue) AS total_revenue
    FROM sales
    GROUP BY category
)
SELECT * FROM category_revenue;

递归查询

递归查询能够帮助分析师遍历层次化数据结构，如组织图或物料清单。假设这里有一个表示员工关系的表，想查找某个经理的所有下属：

WITH RECURSIVE subordinates AS (
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id = 'manager_id_of_interest'
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

透视表

透视表将行转换为列，以表格形式汇总数据。比如，有一个包含销售数据的表格，想通过数据透视来显示每个产品在不同月份的总销售额：

SELECT *
FROM (
    SELECT product, month, sales
    FROM sales_data
) AS source_table
PIVOT (
    SUM(sales)
    FOR month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May')
) AS pivot_table;

分析函数

分析函数根据一组记录计算汇总值。例如，可以使用 ROW_NUMBER() 函数为数据集中的每条记录分配唯一的行号。

SELECT customer_id, order_id,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_rank
FROM orders;

解透视

解透视是透视操作的反向操作，解透视是将一张表中的列转换为行，而透视是将行转换为列。比如，这里有一个按月汇总销售数据的表格，想取消透视以分析随时间变化的趋势。

SELECT product, month, sales
FROM (
    SELECT 'Jan' AS month, product, sales_jan AS sales FROM sales_data
    UNION ALL
    SELECT 'Feb' AS month, product, sales_feb AS sales FROM sales_data
    UNION ALL
    SELECT 'Mar' AS month, product, sales_mar AS sales FROM sales_data
) AS unpivoted_sales;

条件聚合

条件聚合是指根据指定条件应用条件聚合函数。例如，如果想计算老客户订单的平均销售额：

SELECT customer_id, 
       AVG(CASE WHEN order_count > 1 THEN order_total ELSE NULL END) AS avg_sales_repeat_customers
FROM (
    SELECT customer_id, COUNT(*) AS order_count, SUM(order_total) AS order_total
    FROM orders
    GROUP BY customer_id
) AS customer_orders;

日期函数

SQL中的日期函数支持操纵和提取与日期相关的信息。例如，可以使用DATE_TRUNC()函数按月对销售数据进行分组。

SELECT DATE_TRUNC('month', order_date) AS month, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY DATE_TRUNC('month', order_date);

合并语句

合并语句（也称为 UPSERT 或 ON DUPLICATE KEY UPDATE）可让分析师根据与源表的连接结果在目标表中插入、更新或删除记录。比如，要同步两个包含客户数据的表。

MERGE INTO target_table AS t
USING source_table AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN
    UPDATE SET t.name = s.name, t.email = s.email
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, email) VALUES (s.customer_id, s.name, s.email);

 情况语句

情况语句支持在SQL查询中应用条件逻辑。例如，使用情况语句根据客户的总购买金额对其进行分类。

SELECT customer_id,
       CASE
           WHEN total_purchase_amount >= 1000 THEN 'Platinum'
           WHEN total_purchase_amount >= 500 THEN 'Gold'
           ELSE 'Silver'
       END AS customer_category
FROM (
    SELECT customer_id, SUM(order_total) AS total_purchase_amount
    FROM orders
    GROUP BY customer_id
) AS customer_purchases;

其他代码：

JOIN：查询员工及其所属部门的信息
SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.id;

UNION：查询男性员工和女性员工的信息
SELECT name, gender FROM employees WHERE gender = 'M' UNION SELECT name, gender FROM employees WHERE gender = 'F';

LIMIT：查询年龄最大的前5名员工信息
SELECT * FROM employees ORDER BY age DESC LIMIT 5;

OFFSET：查询第6到第10名员工的信息
SELECT * FROM employees LIMIT 5 OFFSET 5;