七天掌握SQL---＞第二天：SQL高级查询与数据库设计

SQL高级查询与数据库设计

一级目录

1. SQL高级查询技巧
   • 1.1 JOIN操作
   • 1.2 GROUP BY与聚合函数
   • 1.3 ORDER BY排序
   • 1.4 子查询
   • 1.5 窗口函数
   • 1.6 递归查询
2. 数据库设计原则
   • 2.1 实体关系模型（ER图）
   • 2.2 数据库范式理论
3. 实际案例与代码示例
   • 3.1 JOIN操作案例
   • 3.2 GROUP BY与聚合函数案例
   • 3.3 ORDER BY排序案例
   • 3.4 子查询案例
   • 3.5 窗口函数案例
   • 3.6 递归查询案例
4. 总结

SQL高级查询技巧

1.1 JOIN操作

JOIN操作是数据库查询中常见的性能瓶颈。尽量减少多表关联操作，尤其是关联大表。可以通过派生表、子查询、索引等方式优化JOIN性能。

SELECT a.*, b.* FROM table1 a JOIN table2 b ON a.id = b.foreign_id;

1.2 GROUP BY与聚合函数

GROUP BY用于将结果集按一列或多列的值进行分组，并对每组数据应用聚合函数进行计算。

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

1.3 ORDER BY排序

ORDER BY语句用于根据一个或多个列对结果集进行排序。

SELECT * FROM employees ORDER BY salary DESC;

1.4 子查询

子查询在SQL中有广泛的应用，但使用不当可能导致性能瓶颈。以下是一些子查询优化的技巧。

SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);

1.5 窗口函数

窗口函数是SQL中强大且灵活的工具，能够在不改变数据行数的情况下对数据进行计算。

SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS cumulative_sum,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

1.6 递归查询

递归查询在处理树状结构和分层数据时非常有用。

WITH RECURSIVE hierarchy AS (
    SELECT id, name, parent_id, 1 AS level
    FROM employees
    WHERE parent_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.parent_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.parent_id = h.id
)
SELECT * FROM hierarchy;

数据库设计原则

2.1 实体关系模型（ER图）

实体关系模型（ER图）是数据库设计中用于表示实体之间关系的图形化工具。

绘制ER图的原则：

• 确定实体和属性
• 确定实体之间的关系
• 使用图形化工具绘制ER图

2.2 数据库范式理论

数据库范式理论是一系列设计原则，用于减少数据冗余和提高数据完整性。

范式包括：

• 第一范式（1NF）：无重复的域
• 第二范式（2NF）：消除部分依赖
• 第三范式（3NF）：消除传递依赖
• BCNF：进一步消除依赖

示例： 为了符合3NF，需要创建一个单独的部门表，以消除传递依赖。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

实际案例与代码示例

3.1 JOIN操作案例

SELECT a.*, b.* FROM customers a JOIN orders b ON a.id = b.customer_id;

3.2 GROUP BY与聚合函数案例

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

3.3 ORDER BY排序案例

SELECT * FROM products ORDER BY price ASC;

3.4 子查询案例

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

3.5 窗口函数案例

SELECT
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

3.6 递归查询案例

WITH RECURSIVE employee_paths AS (
    SELECT id, name, manager_id, CAST(name AS VARCHAR(255)) AS path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, CONCAT(p.path, ' > ', e.name)
    FROM employees e
    JOIN employee_paths p ON e.manager_id = p.id
)
SELECT * FROM employee_paths;

总结

通过本教程的学习，我们掌握了SQL的高级查询技巧，包括JOIN、GROUP BY、ORDER BY、子查询、窗口函数和递归查询等。同时，我们也学习了数据库设计的原则，如实体关系模型（ER图）的绘制和数据库范式理论，包括1NF、2NF、3NF以及BCNF等。这些知识和技能将帮助我们在实际工作中更高效地进行复杂数据查询和数据库设计，提高数据处理的准确性和效率。