SQL 基础知识

本文档整理了 SQL 的基础知识，包括命令分类、数据类型、常用函数和一些高级查询技巧。

1. SQL 语言分类

SQL (Structured Query Language) 主要分为以下四类：

数据操纵语言 (DML - Data Manipulation Language)

用于管理数据库中的数据。

• SELECT：从数据库中检索数据。
• INSERT：向表中插入数据。
• UPDATE：更新表中的现有数据。
• DELETE：从表中删除数据。
• MERGE：根据条件对数据进行插入或更新（UPSERT 操作）。

注意：在 Oracle 中，执行 DML 操作（SELECT 除外）会产生 undo 日志，并对相关资源施加锁（lock）。

数据库定义语言 (DDL - Data Definition Language)

用于定义和管理数据库的结构或模式。

• CREATE：创建数据库对象（如表、视图、索引）。
• ALTER：修改数据库对象的结构。
• DROP：删除数据库对象。
• TRUNCATE：快速删除表中的所有行，但保留表结构。

事务控制语言 (TCL - Transaction Control Language)

用于管理数据库中的事务。

• COMMIT：永久保存事务。
• ROLLBACK：撤销事务。
• SAVEPOINT：在事务内部设置一个保存点，以便后续可以回滚到该点。

数据控制语言 (DCL - Data Control Language)

用于控制对数据库的访问权限。

• GRANT：授予用户或角色权限。
• REVOKE：撤销用户或角色的权限。

2. 常用数据类型

字符类型

• VARCHAR2(size): 可变长度的字符串。
• CHAR(size): 固定长度的字符串。
• CLOB: 用于存储大量的单字节字符数据（文本数据）。

数值类型

• NUMBER(p, s): 精确数值，p 是总位数，s 是小数位数。
• INTEGER: 整数类型。

日期类型

• DATE: 存储日期和时间。
• TIMESTAMP: 存储日期、时间和时区信息，精度更高。

大对象 (LOB) 类型

• CLOB (Character Large Object): 存储大型文本数据。
• BLOB (Binary Large Object): 存储大型二进制数据，如图片、音频或视频文件。

3. 数据类型转换

• TO_CHAR(date, [format]): 将日期或数字类型转换为字符串类型。
• TO_DATE(string, [format]): 将字符串类型转换为日期类型。
• TO_TIMESTAMP(string, [format]): 将字符串类型转换为时间戳类型。

4. 常用函数

条件函数

DECODE

DECODE 函数是 Oracle 特有的，功能类似于 CASE 语句。

DECODE(value, if1, then1, if2, then2, ..., else)

示例：

SELECT
    product_name,
    DECODE(category_id, 1, 'Electronics', 2, 'Apparel', 'Other') as category_name
FROM products;

其他常用函数

• 字符串函数: SUBSTR, REPLACE, CONCAT
• 数值函数: ABS, ROUND, TRUNC
• 日期函数: TRUNC, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY
• 聚合函数: SUM, MIN, MAX, AVG, COUNT

5. SQL 高级技巧

复制表数据

CREATE TABLE ... AS SELECT ... (CTAS)

此方法会创建一个新表，并将查询结果插入到新表中。如果目标表已存在，则会报错。

-- 创建一个名为 new_employees 的新表，并复制 emp 表中部门号为 10 的员工数据
CREATE TABLE new_employees AS
SELECT * FROM emp WHERE deptno = 10;

INSERT INTO ... SELECT ...

此方法将查询结果插入到一个已存在的表中。

-- 将 emp 表中部门号为 20 的员工数据插入到已存在的 employees_archive 表中
INSERT INTO employees_archive (emp_id, emp_name, salary)
SELECT empno, ename, sal FROM emp WHERE deptno = 20;

MERGE 语句

MERGE 语句可以根据一个源表和一个目标表之间的连接条件，对目标表执行 UPDATE 或 INSERT 操作。

语法:

MERGE INTO target_table a
USING source_table b
ON (a.join_column = b.join_column)
WHEN MATCHED THEN
    UPDATE SET a.column1 = b.value1, a.column2 = b.value2
WHEN NOT MATCHED THEN
    INSERT (a.column1, a.column2) VALUES (b.value1, b.value2);

递归查询 (Hierarchical Queries)

在 Oracle 中，可以使用 START WITH ... CONNECT BY 语法进行递归查询，常用于处理树形结构的数据（如组织架构）。

语法:

SELECT *
FROM table_name
START WITH initial_condition -- 指定查询的起始节点
CONNECT BY PRIOR child_id = parent_id; -- 定义父子关系

• PRIOR child_id = parent_id: 从父节点到子节点进行向下查询。
• PRIOR parent_id = child_id: 从子节点到父节点进行向上查询。

示例：查询员工 7369 及其所有下属。

SELECT empno, ename, mgr
FROM emp
START WITH empno = 7369
CONNECT BY PRIOR empno = mgr;

窗口函数 (Window Functions)

窗口函数对一组与当前行相关的表行执行计算。常用的窗口函数包括 ROW_NUMBER(), RANK(), DENSE_RANK()。

ROW_NUMBER()

为结果集中的每一行分配一个唯一的连续整数，不考虑重复值。

示例：

SELECT
    ename,
    sal,
    ROW_NUMBER() OVER (ORDER BY sal DESC) as row_num
FROM emp;

如果两个员工薪资相同，他们的 row_num 也会是连续的（例如 3 和 4）。

DENSE_RANK()

为结果集中的每一行分配一个排名。如果存在相同的值，则分配相同的排名，且后续排名是连续的。

示例：

SELECT
    ename,
    sal,
    DENSE_RANK() OVER (ORDER BY sal DESC) as dense_rnk
FROM emp;

如果两个员工薪资相同，他们会获得相同的排名（例如 3），下一个排名将是 4。