DB sql

技能：SQL 操作掌握

名称： sql-operations-mastery

描述： 使用 SQL 进行数据库操作的全面指南，涵盖关系数据库系统中的数据定义、操作、查询和控制。

关键词： ["sql", "database", "relational database", "data manipulation", "data definition", "query", "mysql", "postgresql", "oracle"]

SQL 操作掌握

目标

让用户掌握使用标准 SQL（结构化查询语言）有效创建、管理和查询关系数据库所需的实用技能。

核心概念：数据的语言

SQL（结构化查询语言）是用于管理和操作关系数据库的标准编程语言。它是一种声明式语言，这意味着你只需指定*想要什么*数据，数据库管理系统（DBMS）就会自行找到最有效的检索方式。

• 关系数据库： 数据组织成表（关系），包含行（记录）和列（字段）。表可以通过键（主键和外键）相互关联。
• 通用应用： 尽管不同的数据库系统（如 MySQL、PostgreSQL、Oracle 和 SQL Server）拥有自己的扩展（例如 T-SQL），但核心 SQL 语法在各个平台上基本一致。

SQL 命令的四大支柱

SQL 命令根据其功能分为四种主要类型。

数据定义语言（DDL）

DDL 命令用于定义和管理数据库及其对象（如表和索引）的结构。

• CREATE： 用于创建新的数据库对象。

- 创建数据库：

CREATE DATABASE my_company;

• 创建表：

USE my_company;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

• ALTER： 用于修改现有对象的结构。

- 添加列：

ALTER TABLE employees ADD COLUMN department VARCHAR(50);

• DROP： 用于从数据库中删除整个对象。

- 删除表：

DROP TABLE employees;

数据操作语言（DML）

DML 命令用于插入、更新和删除表中的实际数据。

• INSERT： 向表中添加新数据行。

INSERT INTO employees (id, first_name, last_name, hire_date, salary, department)
VALUES (101, 'Jane', 'Doe', '2023-01-15', 75000.00, 'Engineering');

• UPDATE： 修改表中的现有数据。

UPDATE employees
SET salary = 80000.00
WHERE id = 101;

• DELETE： 从表中删除行。

DELETE FROM employees
WHERE id = 101;

数据查询语言（DQL）

DQL 主要用于从数据库中检索数据。SELECT 语句是 DQL 的核心。

• 基本查询： 检索表中的特定列。

SELECT first_name, last_name FROM employees;

• 使用 WHERE 过滤： 检索符合特定条件的数据。

SELECT * FROM employees WHERE department = 'Engineering';

• 使用 ORDER BY 排序： 按一个或多个列对结果集排序。

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

数据控制语言（DCL）

DCL 命令管理数据库的访问权限和许可。

• GRANT： 授予用户特定权限。

GRANT SELECT, INSERT ON employees TO 'analyst_user';

• REVOKE： 撤销用户的权限。

REVOKE INSERT ON employees FROM 'analyst_user';

高级查询技巧

要提取有意义的洞察，通常需要执行更复杂的查询。

聚合数据

聚合函数对一组值执行计算并返回单个值。常用函数包括 COUNT()、SUM()、AVG()、MIN() 和 MAX()。

• 示例： 查找 Engineering 部门的平均薪资。

SELECT AVG(salary) AS average_salary
FROM employees
WHERE department = 'Engineering';

• 使用 GROUP BY 分组： 与聚合函数一起使用，按一个或多个列对结果进行分组。

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

合并多个表的数据（JOIN）

JOIN 子句用于根据两个或多个表之间的相关列，将它们的行组合在一起。

• INNER JOIN： 返回两个表中具有匹配值的记录。

-- 假设有一个 'departments' 表，包含 dept_id 和 dept_name
SELECT e.first_name, e.last_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.department = d.dept_name;

子查询

子查询是嵌套在另一个查询内部的查询。通常用在 WHERE 子句中。

• 示例： 查找薪资高于公司平均薪资的员工。

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

安全高效操作的最佳实践

• UPDATE/DELETE 始终使用 WHERE： 在 UPDATE 或 DELETE 语句中忘记 WHERE 子句会将操作应用于表中的每一行。这是一个常见且往往会造成灾难性后果的错误。
• 使用事务保证数据完整性： 事务将一组 SQL 语句组合成一个工作单元。要么所有语句都成功，要么全部都不执行。这对于保持数据一致性至关重要。

BEGIN; -- 开始事务
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT; -- 保存所有更改
-- 如果发生错误，可以使用 ROLLBACK; 撤销所有更改

• 使用参数化查询：在编写与数据库交互的应用程序代码时，始终使用参数化查询，以防止 SQL 注入攻击（恶意 SQL 代码被插入到查询中）。
• 索引优化性能：在 WHERE 子句和 JOIN 条件中频繁使用的列上创建索引，可显著提升查询性能。

CREATE INDEX idx_employee_dept ON employees(department);