Oracle(SQL语言基础)

DDL语句

DDL（Data Definition Language，数据定义语言）是SQL语言的一个子集，用于定义和管理数据库对象的结构。在Oracle数据库中，DDL语句主要包括创建、修改和删除数据库对象的命令。

1. CREATE语句

用于创建数据库对象：

-- 创建表
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(25) UNIQUE,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(8,2),
    department_id NUMBER(4)
);

-- 创建索引
CREATE INDEX emp_last_name_idx ON employees(last_name);

-- 创建视图
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e JOIN departments d 
ON e.department_id = d.department_id;

-- 创建序列
CREATE SEQUENCE emp_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;

-- 创建同义词
CREATE SYNONYM emp FOR employees;

2. ALTER语句

用于修改数据库对象的结构：

-- 添加列
ALTER TABLE employees ADD (middle_name VARCHAR2(20));

-- 修改列
ALTER TABLE employees MODIFY (email VARCHAR2(30));

-- 删除列
ALTER TABLE employees DROP COLUMN middle_name;

-- 重命名列
ALTER TABLE employees RENAME COLUMN email TO email_address;

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT emp_salary_min 
CHECK (salary > 0);

-- 禁用约束
ALTER TABLE employees DISABLE CONSTRAINT emp_salary_min;

-- 重命名表
ALTER TABLE employees RENAME TO staff;

3. DROP语句

用于删除数据库对象：

-- 删除表
DROP TABLE employees;

-- 删除表(带约束)
DROP TABLE employees CASCADE CONSTRAINTS;

-- 删除视图
DROP VIEW emp_dept_view;

-- 删除索引
DROP INDEX emp_last_name_idx;

-- 删除序列
DROP SEQUENCE emp_seq;

4. TRUNCATE语句

用于快速删除表中的所有数据，但保留表结构：

TRUNCATE TABLE employees;

5. RENAME语句

用于重命名数据库对象：

RENAME employees TO staff;

DML语句

DML（Data Manipulation Language，数据操纵语言）是SQL中用于操作数据库中数据的语言子集，主要包括数据的插入、更新、删除和查询操作。

1. INSERT语句（数据插入）

基本插入：

INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
VALUES (1001, 'Smith', 'jsmith@example.com', SYSDATE, 'IT_PROG');

多行插入：

INSERT ALL
  INTO employees VALUES (1002, 'John', 'Doe', 'jdoe@example.com', SYSDATE, 'SA_REP', 6000, NULL, 145, 80)
  INTO employees VALUES (1003, 'Jane', 'Smith', 'jsmith@example.com', SYSDATE, 'IT_PROG', 7000, NULL, 103, 60)
SELECT 1 FROM dual;

从查询结果插入：

INSERT INTO employee_archive
SELECT * FROM employees
WHERE hire_date < TO_DATE('01-JAN-2000', 'DD-MON-YYYY');

2. UPDATE语句（数据更新）

基本更新：

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20;

多列更新：

UPDATE employees
SET salary = salary * 1.05,
    commission_pct = 0.1
WHERE job_id = 'SA_REP';

使用子查询更新：

UPDATE employees e
SET salary = (SELECT AVG(salary) FROM employees)
WHERE e.salary < (SELECT AVG(salary) FROM employees);

3. DELETE语句（数据删除）

基本删除：

DELETE FROM employees
WHERE employee_id = 1001;

使用子查询删除：

DELETE FROM employees
WHERE department_id IN 
  (SELECT department_id FROM departments WHERE location_id = 1700);

全表数据删除：

DELETE FROM employee_backup;

4. MERGE语句（合并操作，Oracle特有）

MERGE INTO employees_target t
USING employees_source s
ON (t.employee_id = s.employee_id)
WHEN MATCHED THEN
  UPDATE SET t.salary = s.salary, t.department_id = s.department_id
WHEN NOT MATCHED THEN
  INSERT (employee_id, last_name, salary, department_id)
  VALUES (s.employee_id, s.last_name, s.salary, s.department_id);

Oracle DML特性

*事务控制：

COMMIT：提交事务

ROLLBACK：回滚事务

SAVEPOINT：设置保存点

*锁定机制：

SELECT ... FOR UPDATE：锁定查询结果行

*闪回查询：

SELECT * FROM employees
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '30' MINUTE;

*批量绑定：

FORALL i IN 1..emp_ids.COUNT
  UPDATE employees SET salary = salaries(i)
  WHERE employee_id = emp_ids(i);

高级DML操作

1. 层次查询

SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

2. 分析函数

SELECT department_id, last_name, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
FROM employees;

3. 物化视图

CREATE MATERIALIZED VIEW emp_summary_mv

DQL语句

SELECT在数据库中是使用较为频繁的访问语句，虽然它只有一条命令，但是由于其丰富多样的灵动性，以及它附有子查询的强大功能，还可以组成各式各样的复杂查询语句，能够查询并统计出各种错综复杂的数据。
SELECT语句可以根据不同用户的需求查询数据库中的数据，并且SELECT可以实行简单的计算和统计。在数据库中执行最简化的SELECT语句只有一个FROM子句，代码格式如下：

SELECT 表达式 FROM 表名;
;是在设计完每个SQL语言后都必须添加的符号，对于新手学习非常重要

在SELECT语句查询时，可以增添一个或多个列名或者带入多个不同型式的表达式，用来指定需要查询出的列，或者对某样数据的计算。在FROM子句中也可以指定一个或多个表名，用来指定此次要查询所涉及到的表。查询的结果也是可以返回一行或多行数据，每行由一个或多个列的列值组成。
完整的SELECT语句包括WHERE、ORDER、GROUP等子句。具体格式如下：

SELECT 表达式
FROM 表名
WHERE 条件
GROUP BY 列名
HAVING 条件
ORDER BY 表达式;

SELECT语句中最灵活且使用次数最多的就是WHERE子句中的查询条件，这个条件用来指定查询什么样的数据。接下来分别介绍SELECT‌多元化组成部分

1、查询语句的基本用法

如果需要查询某个表中一个或多个列的数据，需要在SELECT命令后指定列的名称，并且在FROM子句后面添加所涉及的表名。格式如下：

SELECT 列1,列2....
FROM 表名;

SQL查询出来的结果，是从表中将指定列的数据显示出来。如果要查询DEPT表中的DEPTNO和SAL列，对应的SELECT查询语句如下：

SELECT DEPTNO,SAL FROM EMP;

查询这样的SQL语句可以在ORACLE自带的工具SQL*PLUS执行，也可以在其他SQL软件或应用程序中执行。在本文中详细介绍的是SQL语言是在SQL*PLUS中执行，因此所以;符号是必不可少的基本用法。在类似Linux或windows操作系统中的终端窗口，输入其命令sqlplus，并且指定其用户名和密码，即可登录数据库服务器。例如Linux服务器中shell提示符：

$SQLPLUS SCOTT/TIGER

在SQL语句中除了字符串外，各个部分是大小写不敏感的。SQL语句可以在一行中书写，也可以分行书写。命令的执行结果为：

DEPTNO LOC
10 NEW YORK
20 DALLAS
30 CHICAGO
40 BOSTON

如果要查询出表中所有列的数据，可以使用*符号代替其所有的列名。命令如下：

select * from emp;

如果不清楚表的结构，可以在SQL*PLUS中执行命令DESCRIBE可以简写为DESC，查看具体表的结构。这个命令的参数是表名，或者其他对象名。注意，这条命令不是SQL命令，而是SQL*PLUS中的命令。执行命令如下：

SELECT语句中可以给列定义别名，在执行语句显示结果时，列的标题就是定义的别名名称，在整个SQL语句里都可以使用该别名，使用别名的格式为：

SELECT 列1 AS 别名1，列2 AS 别名2 ……

也可以直接忽略掉AS关键字，直接定义其别名。例如：

SELECT empno AS 员工编号,sal 工资 FROM emp;

在查询结果中如果显示有多行重复值，可以考虑使用DISTINCT关键字去掉多余的重复值。重复行是指在SELECT语句中涉及的所有列的列值完全相同的行，例如，要查询员工所分布的部门编号，可以用DISTINCT关键字去掉其中的重复行：

SELECT DISTINCT deptno AS部门编号 FROM emp;

命令执行的结果为：
部门编号
-------
10
20
30

在SELECT语句中还可以对查询的列进行简单的计算,也可以在两个列之间进行计算，或者将某个列与其他表达式，或者两个表达式进行计算。表中列出了在SELECT语句可以使用的运算符。

例如，查询出emp表中员工的工资和奖金之和，查询的语句为：

SELECT sal+comm AS总收入 FROM emp;

这条语句的执行结果为：
总收入
----------
2100
1950
....

在emp表中共有12名员工，每名员工都有工资。如果奖金为空，对应的计算结果就为空。空值与0或者空格是不同的。空值就是没有数据，而0或者空格是实实在在的数据，就像考试没有成绩和得了0分是不一样的。为了解决空值的计算问题，SQL提供了一个函数，这个函数是NVL，它的功能是把空值转换为其他可以参加运算的数据。这个函数的调用格式是：NVL（表达式，替代值）
当表达式的结果为空时，这个函数就把表达式的值用指定的值代替。有了这个函数，我们就可以在奖金为空时把它用0或者其他数据代替。改进后的查询工资和奖金之和的语句为：
SELECT sal+nvl（comm，0）AS总收入FROM emp;

总收入
----------
600
2900
1650
1975
……

在Oracle数据库中提供了一个特殊的表为dual，这个表的结构为：DESC dual，通过查询这个表，发现表中只有一行数据，可见，dual表只有一个列，而且中有一行数据。所以，在进行与具体的表无关的运算时，可以在FROM子句中指定dual表，这样可以保证计算的结果只显示一次。例如：

SELECT*FROM dual;

DUMMY
-----
X

2、查询语句中的条件

在SELECT语句中由于没有限制特定的条件规则，所以查询出的结果返回的都是所有行显示，如果只是想查询出某一部分行的数据，可以通过WHERE子句来指定条件，WHERE子句中指定的条件是一个关系表达式，如果表达式结果则为真，条件成立，否则条件不成立。例如：查询出emp表中部门编号为10的员工名称和工资。

SELECT ename, sal FROM emp WHERE deptno=10;

=运算符只能对字符串进行精确比对，通常需要模糊匹配查询我们可以用LIKE运算符表达。在LIKE关系表达式中可以使用两个通配符：%和_，其中%可以代替多个字符，注意%也包括空字符，_只能代替一个字符。例如：要查询出员工姓名内包含ME的字符串，语句为：

SELECT ename FROM emp WHERE ename LIKE'%ME%';

又例如要查询员工姓名内，第一个字符是任意字符，第二个字符是B，然后是若干任意字符，语句为：

SELECT ename FROM emp WHERE ename LIKE'_B%';

IN运算符用来表达一个集合的元素进行比较，表达式与集合中的元素一一比较，要与其中一个相等，则条件成立。如果没有任何一个元素与表达式的值相等，则条件不成立。例如：查询emp表中姓名包含如下名称的员工：

SELECT ename FROM emp WHERE ename IN ('HALO'，'FED'，'HEL');

BETWEEN运算符用于将表达式的值与两个指定数据进行比较，如果表达式的值在这两个数据之间，则条件成立。例如：查询emp表中工资500到2000之间的员工：

SELECT ename,sal FROM emp WHERE sal BETWEEN 500 AND 2000;

如果使用“>”或“<”等运算符，来编写上述例子条件，语句为：

SELECT ename,sal FROM emp WHERE sal >=500 AND sal <=2000;

需要体现出多个反应的条件，在复杂的查询语句条件中可以通过AND或OR运算符表达，当多个表达式连接起来后，就可以构成一个逻辑表达式，表达式里的结果要么为true要么为false。
NOT运算符表达方式是对值取反，它的用法是在关系表达式上加上它。例如：要查询工资不大于2000的员工姓名，语句为：

SELECT ename FROM emp WHERE NOT sal＜2000;

语句等价于为：

SELECT ename FROM emp WHERE sal>=2000;

对两个条件同时取反以后的SELECT语句为：

SELECT ename FROM emp WHERE NOT (sal＞500 AND sal＜2000);

WHERE语句中要注意空值的运算。值与任何数据进行赋值运算、四则运算以及关系运算时，结果都为空值。例如，要查询姓名不为空的所有员工的工资，查询的结果应该是所有员工的工资，但是结果却为空。

SELECT sal FROM emp WHERE ename！= NULL;

判断某个表达式是否为空值的运算符是“IS NULL”，判断是否不为空值的运算符是“IS NOT NULL”。例如，用运算符“IS NOT NULL”重新构造上述SELECT语句，将得到我们希望的结果。语句为：

SELECT sal FROM emp WHERE ename IS NOT NULL;

3、查询语句中的单行函数

3.1、字符串处理函数：

1、LENGTH - 返回字符串长度

SELECT LENGTH('Oracle') FROM dual; -- 返回 6

2、LOWER/UPPER/INITCAP - 大小写转换

SELECT LOWER('ORACLE'), UPPER('oracle'), INITCAP('oracle database') 
FROM dual;
-- 返回: oracle, ORACLE, Oracle Database

3、SUBSTR - 截取子字符串

SELECT SUBSTR('Oracle Database', 8, 8) FROM dual; -- 返回 'Database'

4、INSTR - 查找子字符串位置

SELECT INSTR('Oracle Database', 'Data') FROM dual; -- 返回 8

5、CONCAT - 连接字符串(或使用 `||` 运算符)

SELECT CONCAT('Oracle', ' Database') FROM dual; -- 返回 'Oracle Database'
SELECT 'Oracle' || ' Database' FROM dual; -- 同上

6、REPLACE - 替换字符串

SELECT REPLACE('Oracle Database', 'Database', 'DB') FROM dual; -- 返回 'Oracle DB'

7、TRANSLATE - 字符级替换

SELECT TRANSLATE('12345', '123', 'ABC') FROM dual; -- 返回 'ABC45'

8、LPAD/RPAD - 字符串填充

SELECT LPAD('Oracle', 10, '*'), RPAD('Oracle', 10, '*') FROM dual;
-- 返回: '****Oracle', 'Oracle****'

9、TRIM/LTRIM/RTRIM - 去除空格或指定字符

SELECT TRIM('  Oracle  '), LTRIM('xxOracle', 'x'), RTRIM('Oraclexx', 'x') 
FROM dual;
-- 返回: 'Oracle', 'Oracle', 'Oracle'

10、ASCII/CHR - ASCII码转换

SELECT ASCII('A'), CHR(65) FROM dual; -- 返回: 65, 'A'

11、REGEXP_SUBSTR/REGEXP_REPLACE/REGEXP_INSTR/REGEXP_LIKE - 正则表达式函数

SELECT REGEXP_SUBSTR('100 Oracle Parkway', '[0-9]+') FROM dual; -- 返回 '100'
SELECT REGEXP_REPLACE('500 Oracle Parkway', '[0-9]+', '***') FROM dual; -- 返回 '*** Oracle Parkway'

12、NLS_INITCAP/NLS_LOWER/NLS_UPPER - 支持NLS的字符串转换

SELECT NLS_INITCAP('straße', 'NLS_SORT=XGERMAN') FROM dual; -- 返回 'Straße'

13、TO_CHAR - 数字/日期转为字符串

SELECT TO_CHAR(1234.56, '$9,999.99') FROM dual; -- 返回 '$1,234.56'

14、DECODE/CASE - 条件字符串处理

SELECT DECODE('A', 'A', 'Alpha', 'B', 'Beta', 'Other') FROM dual; -- 返回 'Alpha'

15、LISTAGG (11gR2+) - 字符串聚合

SELECT LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) 
FROM emp;

3.2、数学函数：

1、ABS - 绝对值

SELECT ABS(-15) FROM dual;  -- 返回 15

2、SIGN - 符号函数

SELECT SIGN(-23), SIGN(0), SIGN(45) FROM dual;  -- 返回 -1, 0, 1

3、MOD - 取模运算

SELECT MOD(11, 4) FROM dual;  -- 返回 3 (11除以4的余数)

4、POWER - 幂运算

SELECT POWER(3, 2) FROM dual;  -- 返回 9 (3的2次方)

5、SQRT - 平方根

SELECT SQRT(25) FROM dual;  -- 返回 5

6、ROUND - 四舍五入

SELECT ROUND(15.79), ROUND(15.79, 1), ROUND(15.79, -1) FROM dual;
-- 返回: 16, 15.8, 20

7、TRUNC - 截断数字

SELECT TRUNC(15.79), TRUNC(15.79, 1), TRUNC(15.79, -1) FROM dual;
-- 返回: 15, 15.7, 10

8、CEIL/FLOOR - 向上/向下取整

SELECT CEIL(15.2), FLOOR(15.9) FROM dual;  -- 返回: 16, 15

9、SIN/COS/TAN - 正弦/余弦/正切

SELECT SIN(30 * 3.14159265359/180) FROM dual;  -- 30度的正弦值

10、ASIN/ACOS/ATAN - 反正弦/反余弦/反正切

SELECT ASIN(0.5) * 180/3.14159265359 FROM dual;  -- 返回30(度)

11、ATAN2 - 两个参数的反正切

SELECT ATAN2(1, 1) * 180/3.14159265359 FROM dual;  -- 返回45(度)

12、EXP - e的幂

SELECT EXP(1) FROM dual;  -- 返回e的值≈2.71828

13、LN/LOG - 自然对数/指定底数的对数

SELECT LN(10), LOG(10, 100) FROM dual;  -- 返回≈2.302585, 2

14、GREATEST/LEAST - 最大/最小值

SELECT GREATEST(10, 20, 5), LEAST(10, 20, 5) FROM dual;  -- 返回20, 5

15、WIDTH_BUCKET - 等宽分桶

SELECT WIDTH_BUCKET(75, 0, 100, 4) FROM dual;  -- 返回3(将0-100分为4个桶)

16、BITAND - 位与运算

SELECT BITAND(6, 3) FROM dual;  -- 返回2 (0110 AND 0011 = 0010)

17、DBMS_RANDOM - 随机数生成(需要包)

SELECT DBMS_RANDOM.VALUE FROM dual;  -- 返回0-1之间的随机数
SELECT DBMS_RANDOM.VALUE(10, 20) FROM dual;  -- 返回10-20之间的随机数

18、TO_NUMBER - 字符串转数字

SELECT TO_NUMBER('$1,234.56', '$9,999.99') FROM dual;  -- 返回1234.56

19、NANVL - 处理NaN值(非数字)

SELECT NANVL(0/0, 0) FROM dual;  -- 当第一个参数是NaN时返回0

REMAINDER - 计算余数(与MOD类似但使用ROUND而非FLOOR)

SELECT REMAINDER(11, 4) FROM dual;  -- 返回-1 (不同于MOD(11,4)=3)

3.3、日期函数：

1、SYSDATE - 当前系统日期和时间

SELECT SYSDATE FROM dual;  -- 返回当前数据库服务器日期和时间

2、SYSTIMESTAMP - 当前系统时间戳(带时区)

SELECT SYSTIMESTAMP FROM dual;  -- 返回更精确的时间戳，包含小数秒和时区

3、CURRENT_DATE - 当前会话日期(考虑会话时区)

SELECT CURRENT_DATE FROM dual;  -- 返回会话时区的当前日期

4、CURRENT_TIMESTAMP - 当前会话时间戳(带时区)

SELECT CURRENT_TIMESTAMP FROM dual;  -- 返回会话时区的时间戳

5、ADD_MONTHS - 添加月份

SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;  -- 当前日期加3个月

6、MONTHS_BETWEEN - 计算月份差

SELECT MONTHS_BETWEEN('15-JUL-2023', '15-MAR-2023') FROM dual;  -- 返回4

7、NEXT_DAY - 下一个指定星期几的日期

SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM dual;  -- 返回下一个周五的日期

8、LAST_DAY - 月份的最后一天

SELECT LAST_DAY(SYSDATE) FROM dual;  -- 返回当前月份的最后一天

9、EXTRACT - 提取日期部分

SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;  -- 返回当前年份
SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual;  -- 返回当前月份
SELECT EXTRACT(DAY FROM SYSDATE) FROM dual;  -- 返回当前日

10、TO_CHAR (日期格式化) - 日期转字符串

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
-- 返回格式如: 2023-07-15 14:30:45

11、TO_DATE - 字符串转日期

SELECT TO_DATE('2023-07-15', 'YYYY-MM-DD') FROM dual;
-- 将字符串转换为日期类型

12、ROUND (日期) - 日期舍入

SELECT ROUND(SYSDATE, 'MONTH') FROM dual;  -- 舍入到最近月份的第一天
SELECT ROUND(SYSDATE, 'YEAR') FROM dual;   -- 舍入到最近年份的第一天

13、TRUNC (日期) - 日期截断

SELECT TRUNC(SYSDATE, 'MONTH') FROM dual;  -- 截断到当月第一天
SELECT TRUNC(SYSDATE, 'YEAR') FROM dual;   -- 截断到当年第一天

3.4、类型转换函数：

1、TO_NUMBER - 将字符串转换为数字

SELECT TO_NUMBER('1234.56') FROM dual;
SELECT TO_NUMBER('$1,234.56', '$9,999.99') FROM dual; -- 使用格式模型

2、TO_CHAR (数字转换) - 将数字转换为字符串

SELECT TO_CHAR(1234.56, '$9,999.99') FROM dual; -- 返回 '$1,234.56'
SELECT TO_CHAR(1234.56, 'L9,999.99') FROM dual; -- 返回本地货币格式

3、TO_CHAR (日期转换) - 将日期转换为字符串

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT TO_CHAR(SYSDATE, 'Day, Month DD, YYYY') FROM dual;

4、TO_DATE - 将字符串转换为日期

SELECT TO_DATE('2023-07-15', 'YYYY-MM-DD') FROM dual;
SELECT TO_DATE('15-July-2023', 'DD-Month-YYYY') FROM dual;

5、TO_TIMESTAMP - 将字符串转换为时间戳

SELECT TO_TIMESTAMP('2023-07-15 14:30:45.123', 'YYYY-MM-DD HH24:MI:SS.FF') FROM dual;

6、TO_TIMESTAMP_TZ - 将字符串转换为带时区的时间戳

SELECT TO_TIMESTAMP_TZ('2023-07-15 14:30:45.123 +08:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') FROM dual;

4、分组函数与分组统计

4.1、常用聚合函数

COUNT - 计数

SELECT COUNT(*) FROM employees; -- 计算所有行数
SELECT COUNT(DISTINCT department_id) FROM employees; -- 计算不重复的部门数

SUM - 求和

SELECT SUM(salary) FROM employees; -- 计算薪资总和

AVG - 平均值

SELECT AVG(salary) FROM employees; -- 计算平均薪资

MIN/MAX - 最小/最大值

SELECT MIN(salary), MAX(salary) FROM employees;

MEDIAN - 中位数

SELECT MEDIAN(salary) FROM employees;

4.2、统计函数

STDDEV - 标准差

SELECT STDDEV(salary) FROM employees;

VARIANCE - 方差

SELECT VARIANCE(salary) FROM employees;

4.3、GROUP BY 子句

GROUP BY 用于将结果集按一个或多个列分组：

SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id;

多列分组

SELECT department_id, job_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id, job_id;

4.4、HAVING 子句

HAVING 用于过滤分组后的结果（类似于WHERE，但用于分组后）：

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

5、数据排序

5.1、基本排序

单列排序

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary; -- 默认升序(ASC)

指定排序方向

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC; -- 降序排列

多列排序

SELECT department_id, last_name, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

6、多表查询

6.1、基本排序

等值连接：

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

自然连接 (NATURAL JOIN)：

SELECT employee_id, last_name, department_name
FROM employees NATURAL JOIN departments;

左外连接 (LEFT JOIN)：

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

右外连接 (RIGHT JOIN)：

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
-- 包含右表所有记录，左表无匹配则为NULL

全外连接 (FULL JOIN)：

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.department_id;
-- 包含两表所有记录，无匹配则为NULL

交叉连接 (CROSS JOIN)

SELECT e.last_name, d.department_name
FROM employees e
CROSS JOIN departments d;
-- 返回笛卡尔积

7、子查询

7.1、子查询基本概念：子查询是嵌套在 SELECT、INSERT、UPDATE、DELETE 或另一个子查询中的查询语句。

子查询分类

单行子查询：返回单行单列结果

多行子查询：返回多行单列结果

多列子查询：返回多列结果

关联子查询：引用外部查询列的子查询

标量子查询：返回单值且可出现在任何表达式位置的子查询

7.2、WHERE 子句中的子查询

1. 单行子查询

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

2. 多行子查询（使用 IN）

SELECT employee_id, last_name, department_id
FROM employees
WHERE department_id IN 
      (SELECT department_id FROM departments WHERE location_id = 1700);

3. 多行子查询（使用 ANY/SOME/ALL）

-- 大于任意一个(ANY/SOME)
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 60);

-- 大于所有(ALL)
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 60);

DCL语句

DCL（Data Control Language，数据控制语言）是SQL语言中用于控制数据库访问权限和安全性的语句子集，主要包括权限授予和回收操作。

1. GRANT语句（权限授予）

系统权限授予：

-- 授予用户创建会话的权限
GRANT CREATE SESSION TO scott;

-- 授予用户创建表的权限
GRANT CREATE TABLE TO scott;

-- 授予用户无限表空间配额
GRANT UNLIMITED TABLESPACE TO scott;

对象权限授予：

-- 授予对employees表的查询权限
GRANT SELECT ON employees TO scott;

-- 授予对employees表的插入、更新权限
GRANT INSERT, UPDATE ON employees TO scott;

-- 授予对employees表所有操作权限
GRANT ALL ON employees TO scott;

-- 授予权限并允许接收者继续授权
GRANT SELECT ON departments TO scott WITH GRANT OPTION;

角色授予：

-- 将CONNECT角色授予用户
GRANT CONNECT TO scott;

-- 将RESOURCE角色授予用户
GRANT RESOURCE TO scott;

-- 将自定义角色授予用户
GRANT hr_manager TO scott;

2. REVOKE语句（权限回收）

系统权限回收：

-- 回收创建表的权限
REVOKE CREATE TABLE FROM scott;

对象权限回收：

-- 回收对employees表的更新权限
REVOKE UPDATE ON employees FROM scott;

-- 回收所有对象权限
REVOKE ALL ON employees FROM scott;

角色回收：

-- 回收RESOURCE角色
REVOKE RESOURCE FROM scott;

3. CREATE ROLE语句（角色创建）

-- 创建角色
CREATE ROLE hr_clerk;

-- 为角色授予权限
GRANT SELECT, INSERT ON employees TO hr_clerk;
GRANT SELECT ON departments TO hr_clerk;

-- 将角色授予用户
GRANT hr_clerk TO scott, smith;

4. ALTER ROLE语句（角色修改）

-- 为角色设置密码保护
ALTER ROLE hr_manager IDENTIFIED BY secure123;

5. SET ROLE语句（角色激活）

-- 激活角色(需要密码)
SET ROLE hr_manager IDENTIFIED BY secure123;

-- 激活多个角色
SET ROLE hr_clerk, hr_manager IDENTIFIED BY secure123;

-- 禁用所有角色
SET ROLE NONE;

-- 激活除指定角色外的所有已授予角色
SET ROLE ALL EXCEPT hr_manager;

TCL语句

TCL（Transaction Control Language，事务控制语言）是SQL语言中用于管理数据库事务的语句子集，主要包括事务的提交、回滚和保存点操作。

1. COMMIT（提交事务）

基本语法：

COMMIT;

功能说明：

将当前事务中的所有DML操作永久保存到数据库
释放事务占用的所有锁
结束当前事务

使用示例：

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 202;
COMMIT; -- 确认转账操作

可选子句：

COMMIT WORK; -- WORK关键字可选，与COMMIT等效
COMMIT COMMENT '批量更新用户数据'; -- 添加注释
COMMIT WRITE IMMEDIATE NOWAIT; -- 控制重做日志写入行为

2. ROLLBACK（回滚事务）

基本语法：

ROLLBACK;

功能说明：

撤销当前事务中的所有DML操作
释放事务占用的所有锁
结束当前事务

使用示例：

BEGIN
  INSERT INTO orders VALUES (1001, SYSDATE, 203);
  INSERT INTO order_items VALUES (1001, 1, 10, 5);
  -- 发现错误
  ROLLBACK; -- 撤销整个事务
END;

回滚到保存点：

SAVEPOINT sp1;
UPDATE products SET stock = stock - 5 WHERE product_id = 101;
-- 发现错误
ROLLBACK TO sp1; -- 仅回滚到保存点sp1

3. SAVEPOINT（设置保存点）

基本语法：

SAVEPOINT savepoint_name;

功能说明：

在事务中创建标记点
允许部分回滚到指定保存点
保存点在事务提交或回滚后自动清除

使用示例：

BEGIN
  UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
  SAVEPOINT after_withdrawal;
  
  UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;
  -- 检查发现收款账户错误
  ROLLBACK TO after_withdrawal;
  
  UPDATE accounts SET balance = balance + 500 WHERE account_id = 203);
  COMMIT;
END;

Oracle事务特性

1. ACID特性

原子性(Atomicity)：事务是不可分割的工作单位
一致性(Consistency)：事务执行前后数据库保持一致状态
隔离性(Isolation)：并发事务之间互不干扰
持久性(Durability)：事务提交后改变永久有效

2. 事务隔离级别

Oracle支持以下隔离级别：

-- 设置隔离级别(需在事务开始时设置)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 默认级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY; -- 只读事务

3. 自治事务

允许在独立事务中执行特定操作：

CREATE OR REPLACE PROCEDURE log_error (p_msg VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log VALUES (SYSDATE, p_msg);
  COMMIT; -- 独立提交
END;

事务管理实践

1. 长事务处理

-- 定期提交大批量操作
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO big_table VALUES (...);
    IF MOD(i, 1000) = 0 THEN
      COMMIT; -- 每1000条提交一次
    END IF;
  END LOOP;
  COMMIT;
END;

2. 异常处理中的事务控制

BEGIN
  SAVEPOINT before_transfer;
  
  BEGIN
    UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101;
    UPDATE accounts SET balance = balance + 1000 WHERE account_id = 202);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK TO before_transfer;
      DBMS_OUTPUT.PUT_LINE('转账失败: ' || SQLERRM);
  END;
END;

3. 分布式事务

-- 跨数据库事务
BEGIN
  UPDATE accounts@remote_db SET balance = balance - 500 WHERE account_id = 101;
  UPDATE local_accounts SET balance = balance + 500 WHERE account_id = 202;
  COMMIT; -- 两阶段提交
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;

事务监控

1. 查看活动事务

SELECT s.sid, s.serial#, s.username, t.start_time, t.status
FROM v$session s JOIN v$transaction t ON s.saddr = t.ses_addr;

2. 查看锁等待

SELECT blocking_session, sid, serial#, wait_class, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

3. 查看长时间运行事务

SELECT s.sid, s.username, t.used_ublk, t.start_time
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr
ORDER BY t.used_ublk DESC;

注意事项

DDL语句会自动提交当前事务
断开连接时未提交的事务会自动回滚
长时间未提交的事务会导致undo表空间膨胀
分布式事务失败可能需要DBA干预
自治事务中的COMMIT/ROLLBACK不影响主事务

通过合理使用TCL语句，可以确保Oracle数据库事务的完整性和一致性，特别是在复杂的业务操作中，事务控制是保证数据正确性的关键机制。

如果觉得文章对你有用，请随意赞赏

Oracle(SQL语言基础)

http://101.43.96.52:51827/archives/854ab78d-297b-45b1-a003-0cc16760c30d

作者

陆沉夜

发布于

2025-06-16

更新于

2025-06-17

许可协议

CC BY 4.0

DDL语句

1. CREATE语句

2. ALTER语句

3. DROP语句

4. TRUNCATE语句

5. RENAME语句

DML语句

1. INSERT语句（数据插入）

2. UPDATE语句（数据更新）

3. DELETE语句（数据删除）

4. MERGE语句（合并操作，Oracle特有）

Oracle DML特性

*事务控制：

*锁定机制：

*闪回查询：

*批量绑定：

1. 层次查询

2. 分析函数

3. 物化视图

DQL语句

1、查询语句的基本用法

2、查询语句中的条件

3、查询语句中的单行函数

3.1、字符串处理函数：

1、LENGTH - 返回字符串长度

2、LOWER/UPPER/INITCAP - 大小写转换

3、SUBSTR - 截取子字符串

4、INSTR - 查找子字符串位置

5、CONCAT - 连接字符串(或使用 || 运算符)

6、REPLACE - 替换字符串

7、TRANSLATE - 字符级替换

8、LPAD/RPAD - 字符串填充

9、TRIM/LTRIM/RTRIM - 去除空格或指定字符

10、ASCII/CHR - ASCII码转换

11、REGEXP_SUBSTR/REGEXP_REPLACE/REGEXP_INSTR/REGEXP_LIKE - 正则表达式函数

12、NLS_INITCAP/NLS_LOWER/NLS_UPPER - 支持NLS的字符串转换

13、TO_CHAR - 数字/日期转为字符串

14、DECODE/CASE - 条件字符串处理

15、LISTAGG (11gR2+) - 字符串聚合

3.2、数学函数：

1、ABS - 绝对值

2、SIGN - 符号函数

3、MOD - 取模运算

4、POWER - 幂运算

5、SQRT - 平方根

6、ROUND - 四舍五入

7、TRUNC - 截断数字

8、CEIL/FLOOR - 向上/向下取整

9、SIN/COS/TAN - 正弦/余弦/正切

10、ASIN/ACOS/ATAN - 反正弦/反余弦/反正切

11、ATAN2 - 两个参数的反正切

12、EXP - e的幂

13、LN/LOG - 自然对数/指定底数的对数

14、GREATEST/LEAST - 最大/最小值

15、WIDTH_BUCKET - 等宽分桶

16、BITAND - 位与运算

17、DBMS_RANDOM - 随机数生成(需要包)

18、TO_NUMBER - 字符串转数字

19、NANVL - 处理NaN值(非数字)

REMAINDER - 计算余数(与MOD类似但使用ROUND而非FLOOR)

3.3、日期函数：

1、SYSDATE - 当前系统日期和时间

2、SYSTIMESTAMP - 当前系统时间戳(带时区)

3、CURRENT_DATE - 当前会话日期(考虑会话时区)

4、CURRENT_TIMESTAMP - 当前会话时间戳(带时区)

5、ADD_MONTHS - 添加月份

6、MONTHS_BETWEEN - 计算月份差

7、NEXT_DAY - 下一个指定星期几的日期

8、LAST_DAY - 月份的最后一天

9、EXTRACT - 提取日期部分

10、TO_CHAR (日期格式化) - 日期转字符串

11、TO_DATE - 字符串转日期

12、ROUND (日期) - 日期舍入

13、TRUNC (日期) - 日期截断

3.4、类型转换函数：

1、TO_NUMBER - 将字符串转换为数字

2、TO_CHAR (数字转换) - 将数字转换为字符串

3、TO_CHAR (日期转换) - 将日期转换为字符串

4、TO_DATE - 将字符串转换为日期

5、CONCAT - 连接字符串(或使用 `||` 运算符)