数据库知识点 - (≧∇≦)你又回来了

GRANT 子句是 SQL 中用于给数据库用户或角色授予权限的命令。它是数据库权限管理的核心，控制着“谁能在什么对象上执行什么操作”。

简单来说，它的作用就是：开门发钥匙。

门：数据库里的各种对象（如表、视图、数据库、函数等）。
钥匙：对这些对象执行的操作权限（如查看、插入、删除等）。
发钥匙的人：数据库管理员或拥有授权权限的用户。

GRANT是标准SQL提供的授权语句，即通过把数据库对象的操作权限授予用户，用户具有对象上的操作权限才能进行相应的操作。视图是建立在基本表上的虚表，通过外模式/模式的映像，将视阁所提供的字段（外模式）指向基本表（模式）中的部分数据，用户通过视图所访问的数据只是对应基本表中的部分数据，而无需给用户提供基本表中的全部数据，则视图外的数据对用户是不可见的，即受到了保护。存储过程是数据库所提供的一种数据库对象，通过存储过程定义一段代码，提供给应用程序调用来执行。从安全性的角度考虑，更新数据时，通过提供存储过程让第三方凋用，将需要更新的数据传入存储过程，而在存储过程内部用代码分别对需要的多个表进行更新，从而避免了向第三方提供系统的表结构，保证了系统的数据安全。

主要用途
授予对数据库的访问权限：允许用户连接到一个数据库。
授予对数据对象的操作权限：允许用户对特定的表、视图等进行增删改查。
授予执行特定代码的权限：允许用户运行存储过程或函数。
授予授予权限的权限：允许用户将自己获得的权限再授予其他人（使用 WITH GRANT OPTION）。

GRANT 权限类型 ON 对象 TO 用户或角色 [WITH GRANT OPTION];

权限类型

SELECT：查询数据
INSERT：插入新数据
UPDATE：更新现有数据
DELETE：删除数据
ALL PRIVILEGES：所有权限
CREATE, DROP, ALTER：对数据库或表结构的操作权限
EXECUTE：执行存储过程或函数
USAGE：使用模式、序列等对象的权限

数据库对象权限
├── 数据访问权限
│   ├── SELECT (读取)
│   ├── INSERT (创建)
│   ├── UPDATE (修改)
│   └── DELETE (删除)
│
├── 结构修改权限
│   ├── ALTER (修改结构)
│   ├── INDEX (创建索引)
│   └── REFERENCES (外键约束)
│
├── 特殊操作权限
│   ├── EXECUTE (执行)
│   └── TRUNCATE (清空)
│
└── 管理权限
    ├── GRANT OPTION (转授)
    └── WITH ADMIN OPTION (管理)

对象
可以是具体的 database_name.table_name
可以是整个数据库 database_name.*
可以是所有数据库 .

用户或角色：
一个或多个数据库用户名。
角色名（现代数据库更推荐先授权给角色，再把角色分配给用户）。

授予用户 read_user 对 employees 表的只读权限：
GRANT SELECT ON company.employees TO read_user;

授予用户 app_user 对 orders 表的完整数据操作权限：
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.orders TO app_user;

授予用户 admin_user 对 reports 数据库的所有权限，并允许其授权他人：
GRANT ALL PRIVILEGES ON reports.* TO admin_user WITH GRANT OPTION;
--WITH GRANT OPTION 是 SQL 授权语句中的一个可选子句，它允许被授权者（grantee）将自己获得的权限再次授予其他用户或角色。

允许用户 user1 连接到名为 mydb 的数据库：
GRANT CONNECT ON DATABASE mydb TO user1; -- PostgreSQL 示例

创建一个只能查询特定视图的只读账户：
GRANT SELECT ON my_schema.sales_summary_view TO reporting_user;

与 GRANT 对应的是 REVOKE 子句，用于收回已授予的权限。

REVOKE INSERT, UPDATE ON shop.orders FROM app_user;

注意事项与实践
最小权限原则：只授予用户完成工作所必需的最小权限。不要随意使用 ALL PRIVILEGES。
使用角色管理：对于复杂的权限管理，先创建角色（如 analyst, developer），将权限授予角色，再将角色分配给用户。这样更易于维护。
定期审计：使用如 SHOW GRANTS FOR user_name;（MySQL）或 \dp（PostgreSQL）等命令检查现有权限。
WITH GRANT OPTION 要慎用：这会导致权限链难以追踪和管理。
权限生效：通常 GRANT 执行后权限立即生效，但有时用户需要重新登录才能激活。

触发器是一种特殊的存储过程，它在特定的事件（如 INSERT、UPDATE、DELETE）发生时自动执行。但并不是所有数据库操作都能激活触发器。（增删改）

COMMIT 是"确认保存"的命令，它将事务中的所有更改永久写入数据库。

要理解 COMMIT，必须先明白什么是事务：
事务是一组 SQL 操作，它们要么全部成功，要么全部失败，是一个不可分割的工作单元。

事务的经典例子：银行转账

-- 这是一个完整的事务，包含两个操作：
1. 从A账户扣款100元
2. 向B账户加款100元

-- 这两个操作必须同时成功或同时失败
-- 不能只扣款不加款，也不能只加款不扣款

COMMIT 的作用机制
事务的生命周期：
开始事务 → 执行操作 → (可选：回滚ROLLBACK) → 提交COMMIT

-- 1. 开始事务后，所有的修改都是临时的
BEGIN TRANSACTION;  -- 开始事务

UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 临时修改
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 临时修改

-- 此时：
-- - 你自己能看到修改后的数据
-- - 其他用户/会话看不到你的修改
-- - 如果系统崩溃，修改会丢失

-- 2. 使用COMMIT后，修改变成永久的
COMMIT;  -- 确认保存

-- 此时：
-- - 修改被永久写入磁盘
-- - 所有用户都能看到修改
-- - 即使系统崩溃，修改也不会丢失

为什么需要 COMMIT？
1.保证数据一致性（ACID原则）
COMMIT 是 ACID 中 D（持久性）的实现：
A（原子性）：事务的所有操作是一个整体
C（一致性）：事务前后数据库状态一致
I（隔离性）：事务之间互不干扰
D（持久性）：COMMIT 后数据永久保存

2.提供"撤销"机会

--在 COMMIT 之前，可以用 ROLLBACK 撤销所有操作：
BEGIN TRANSACTION;

-- 不小心删错了数据
DELETE FROM employees WHERE department = 'IT';

-- 发现错误！可以撤销
ROLLBACK;  -- 所有删除操作被撤销，数据恢复

-- 或者确认无误后提交
-- COMMIT;  -- 永久删除

3.批量操作的性能优化

-- 没有事务：每条INSERT都立即写入磁盘，性能差
INSERT INTO log_table VALUES (...);  -- 立即写磁盘
INSERT INTO log_table VALUES (...);  -- 立即写磁盘
-- ... 1000次

-- 使用事务：批量写入，性能大幅提升
BEGIN TRANSACTION;
INSERT INTO log_table VALUES (...);
INSERT INTO log_table VALUES (...);
-- ... 1000次
COMMIT;  -- 一次性写入磁盘，效率高

视图
视图是一个虚拟表，其内容由查询定义。它不存储数据本身，而是存储查询。当查询视图时，数据库引擎执行底层查询并返回结果。

与 NOT IN 等价的操作符是 NOT EXISTS，但二者在 NULL 值处理上有重要差异

假设有两个表：员工和已离职员工。

使用 NOT IN（注意 NULL 问题）：
```
SELECT * FROM 员工 
WHERE 员工ID NOT IN (SELECT 员工ID FROM 已离职员工);
```
如果已离职员工中有任意一行的员工ID 为 NULL，则查询返回空结果。

使用 NOT EXISTS（安全写法）：

SELECT * FROM 员工 e
WHERE NOT EXISTS (SELECT 1 FROM 已离职员工 d WHERE d.员工ID = e.员工ID);

即使已离职员工中有 NULL，也能正确查询。
<> ALL（与 NOT IN 同样存在 NULL 问题）：

SELECT * FROM 员工 
WHERE 员工ID <> ALL (SELECT 员工ID FROM 已离职员工);

聚合函数是对一组值进行计算并返回单个值的函数，通常与 GROUP BY 子句一起使用，对数据行进行分组统计。
常用聚合函数

COUNT() - 计数

-- 统计行数（包括NULL）
SELECT COUNT(*) FROM 表名;
-- 统计特定列非NULL值的数量
SELECT COUNT(列名) FROM 表名;
-- 统计唯一值数量
SELECT COUNT(DISTINCT 列名) FROM 表名;

SUM() - 求和

-- 对数值列求和
SELECT SUM(金额) FROM 订单表;
-- 忽略NULL值
SELECT SUM(单价 * 数量) FROM 明细表;

AVG() - 平均值

-- 计算平均值（忽略NULL）
SELECT AVG(成绩) FROM 学生成绩;
-- 包含NULL的处理
SELECT AVG(COALESCE(成绩, 0)) FROM 学生成绩;

MAX() / MIN() - 最大值/最小值

-- 最大值
SELECT MAX(价格) FROM 产品表;
-- 最小值
SELECT MIN(入职日期) FROM 员工表;

GROUP_CONCAT() (MySQL) / STRING_AGG() (PostgreSQL/SQL Server) / LISTAGG() (Oracle)

-- MySQL: 将多行值连接成字符串
SELECT 部门, GROUP_CONCAT(员工姓名 SEPARATOR ', ') 
FROM 员工表 
GROUP BY 部门;

-- SQL Server
SELECT 部门, STRING_AGG(员工姓名, ', ') 
FROM 员工表 
GROUP BY 部门;

STDDEV() / VARIANCE() - 标准差/方差

-- 标准差
SELECT STDDEV(分数) FROM 考试成绩;
-- 方差
SELECT VARIANCE(分数) FROM 考试成绩;

SELECT列表规则

-- 错误：非聚合列未包含在GROUP BY中
SELECT 部门, 员工姓名, AVG(工资)  -- 员工姓名不在GROUP BY中
FROM 员工
GROUP BY 部门;

-- 正确
SELECT 部门, 员工姓名, AVG(工资)
FROM 员工
GROUP BY 部门, 员工姓名;  -- 或者使用聚合函数处理员工姓名

当使用 GROUP BY 分组时，SELECT 列表中只能包含：
聚合函数（如 COUNT、SUM、AVG 等）
GROUP BY 子句中出现的列
常量或表达式（但必须基于上述1或2）

逻辑解释：
GROUP BY 将多行数据合并为一行，SELECT 列表必须确保每列在分组后只有一个确定的值。

-- 假设员工表数据：
员工ID | 姓名   | 部门   | 工资
1     | 张三   | 销售部 | 5000
2     | 李四   | 销售部 | 6000
3     | 王五   | 技术部 | 7000

-- 如果按部门分组：
销售部：有张三(5000)、李四(6000) 两行
技术部：有王五(7000) 一行

问题：销售部分组中有"张三"和"李四"两个姓名，数据库不知道返回哪个

SELECT包含未在GROUP BY中的列

SELECT 
    部门,
    姓名,        -- 错误！每个部门有多个姓名，应该显示哪个？
    AVG(工资) as 平均工资
FROM 员工表
GROUP BY 部门;

正确写法
将列添加到 GROUP BY

-- 同时按部门和姓名分组（这样每个组只有一个人）
SELECT 部门, 姓名, AVG(工资)
FROM 员工表
GROUP BY 部门, 姓名;
-- 结果：每人一行，失去了分组统计的意义

执行顺序

-- SQL执行顺序
1. FROM/JOIN     -- 确定数据源
2. WHERE         -- 行级筛选
3. GROUP BY      -- 分组
4. HAVING        -- 组级筛选
5. SELECT        -- 选择列
6. ORDER BY      -- 排序
7. LIMIT/OFFSET  -- 限制结果

权限管理

主体
指可以被授予权限的实体。
用户：登录数据库的单个账户。
角色：一组权限的集合。将权限授予角色，再将角色授予用户，是管理权限的最佳实践。
组（在某些数据库中如 SQL Server）：类似角色。

客体
指可以被操作的对象。
层次结构通常为：服务器/实例 -> 数据库 -> 模式 -> 对象。
对象包括：表、视图、存储过程、函数、序列等。

权限
定义了对某个客体可以执行的操作。
主要分为两类：
系统权限：针对数据库或服务器本身的操作权限（如创建数据库、修改用户、查看服务器状态）。
对象权限：针对特定数据库对象的操作权限（如对某张表的 SELECT, INSERT, UPDATE, DELETE, EXECUTE 等）。

SQL 中的主要集合操作符有以下几种：

注意事项：
列的数量和类型必须匹配：所有参与集合操作的 SELECT 语句必须有相同数量的列，且对应列的数据类型必须兼容。
排序：可以在最后使用一个 ORDER BY 子句对整个结果排序。

数据库支持：
UNION 广泛支持。
INTERSECT 和 EXCEPT 在 SQL Server、PostgreSQL、SQLite 中支持。
Oracle 使用 MINUS 代替 EXCEPT。
MySQL 5.7 及以下版本不支持 INTERSECT 和 EXCEPT，需用 JOIN 或子查询模拟。

断言（Assertion）是 SQL 标准中定义的一种完整性约束，用于声明数据库必须始终满足的全局条件（涉及一个或多个表）。如果任何数据修改操作（插入、更新、删除）导致断言条件为假，则该操作会被拒绝。

特点：
全局性：不依附于特定表，可跨多个表定义约束。
自动维护：数据库自动检查并强制执行。
复杂条件：可使用子查询、聚集函数等。

语法

CREATE ASSERTION <断言名称>
CHECK (<条件>);

通常使用以下方式替代断言：
触发器（Triggers）
触发器是一种特殊的存储过程，它与表相关联，当特定的数据修改事件发生在表上时，数据库会自动执行触发器中定义的SQL语句。触发器通常用于强制执行业务规则、维护数据完整性、审计数据变更
触发器通常被定义为一组（一个或多个）SQL语句的集合，这些语句在特定事件（如INSERT、UPDATE、DELETE）发生时自动执行。这种“集合”意味着触发器可以包含多个SQL语句，这些语句作为一个整体在触发器被触发时执行。

存储过程通过封装数据修改操作来检查约束。
应用层验证在应用程序代码中保证数据完整性。

与 CHECK 约束的区别

触发器是一种特殊的数据库对象，它在特定事件发生时自动执行，用于实现复杂的业务规则、数据完整性约束和审计跟踪。

触发器是什么？
一段与表关联的 PL/SQL 或 T-SQL 代码
在指定事件发生时自动触发执行
依附于特定的表或视图

触发器的三个核心要素：
触发事件：INSERT、UPDATE、DELETE
触发时机：BEFORE（之前）或 AFTER（之后）
触发频率：行级（每行）或语句级（每次语句）

触发器类型（按执行时机）
1. BEFORE 触发器
在数据修改操作之前执行，常用于：
数据验证
数据转换
防止非法操作
-- MySQL 示例：插入前检查工资
DELIMITER //
CREATE TRIGGER check_salary_before_insert
BEFORE INSERT ON Teachers
FOR EACH ROW
BEGIN
    IF NEW.salary < 3000 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '工资不能低于3000';
    END IF;
END //
DELIMITER ;

2. AFTER 触发器
在数据修改操作之后执行，常用于：
审计日志
同步更新
级联操作

-- SQL Server 示例：记录删除日志
CREATE TRIGGER log_teacher_delete
ON Teachers
AFTER DELETE
AS
BEGIN
    INSERT INTO TeacherAudit (teacher_id, action, audit_time)
    SELECT deleted.teacher_id, 'DELETE', GETDATE()
    FROM deleted;
END;

不同数据库的触发器语法
  MySQL
  CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

  Oracle
  CREATE OR REPLACE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
ON {table_name | view_name}
[FOR EACH ROW]
DECLARE
    -- 变量声明
BEGIN
    -- 触发器逻辑
END;

触发器本身并不拥有独立的事务，它只是在已有事务中执行的一组操作。
触发器是事务的一部分，而不是事务的控制者。
触发器操作会随着主事务一起提交或回滚，不能自己决定事务的边界。

-- 想象一个简单的UPDATE操作：
BEGIN TRANSACTION;  -- 事务开始
    UPDATE Teachers SET salary = 6000 WHERE id = 101;
    -- 触发AFTER UPDATE触发器
    -- 触发器中的操作在此执行
COMMIT;  -- 事务结束，提交所有更改

关键点：触发器在 UPDATE 之后、COMMIT 之前执行，但触发器本身不能改变事务的边界。
触发器不能包含事务控制语句的根本原因：
职责分离：触发器负责数据完整性，事务控制由应用程序或存储过程负责
事务一致性：保证所有操作要么全部成功，要么全部失败
避免死锁和竞态条件：如果多个触发器各自控制事务，容易导致复杂并发问题
可预测性：确保数据库行为一致，易于理解和维护
记住：触发器是"乘客"，存储过程/应用是"司机"
乘客可以提醒司机（抛出异常）
但不能抢方向盘（控制事务）
最终去哪里（提交/回滚）由司机决定

触发器不传递参数

对数据库的更新，应采用事务的方式，以对应现实中的业务。用户在现实业务过程中通过调用事务程序，将事务程序交由DBMS来执行，DBMS通过其并发调度机制完成事务的并行执行。存储过程正是在服务器端所提供的功能调用，适用于编写更新数据库的事务程序。触发器是由更新语句来触发执行的，适用于数据的联动操作和复杂约束的实现，无法供应用程序主动调用。

嵌入式SQL（Embedded SQL）是一种将SQL语句直接嵌入到宿主编程语言（如C、C++、Java、COBOL等）中的技术，允许在传统编程语言中直接操作数据库。

嵌入式SQL的语法元素
声明段（Declare Section）
连接数据库
游标操作游标是一种数据库对象，允许你逐行访问查询结果集，就像读书时使用书签一样，可以逐行“翻阅”数据结果。
动态SQL