Oracle数据库自学文档/笔记/技术文档！-零一物语

试读部分

基本SELECT语句

SELECT 内容 FROM 表、集合 WHERE 条件

*表示所有字段
多个字段之间可以用逗号隔开
*不能与其他字段混用，如果需要同时使用则需要表名.*的方式

虚列

Oracle数据库中存在虚列的概念

虚列本身不是表中的字段，但是可以当成字段来使用
表示某个集合结果中的行序
ROWNUM的初始值为0，每SELECT一行加1，然后进行WHERE匹配，若匹配成功则进入最终结果缓存，若匹配不成功则减1
ROWNUM只和当前执行的SELECT有关

SELECT ROWNUM,EMP.* FROM EMP WHERE ROWNUM = 1;
//上述sql语句会显示在表中第一行数据
//如果写成

SELECT ROWNUM,EMP.* FROM EMP WHERE ROWNUM = 2;
//上述语句是错误的，因为ROWNUM是从0开始累加的，当ROWNUM等于1时
//进行条件判断，不等于2，则根据规则减1，所以输出的结果为空

//如果想要显示第2条，可以使用
SELECT ROWNUM,EMP.* FROM EMP WHERE ROWNUM <= 2;
//上述语句会显示两条

计算列

每SELECT一次，计算一次
+-*/
NULL在参与算数预算时结果仍然为NULL
可以使用NVL(V1,V2)进行转换，如果V1为NULL则返回V2，否则返回V1

SELECT NVL(COMM,0)+2000 FROM EMP;
//这样如果COMM为NULL，也能正常进行加2000的操作了

字符串连接列

SELECT EMPNO||'=='||ENAME FROM EMP;
//实现使用==连接

符号使用单引号，如果想要使用的连接内容存在单引号则使用''两个单引号表示

给字段起别名

字段名 AS 别名
字段名别名
字段名 “别名”
字段名 AS “别名”

AS有与没有无本质区别

消除重复行（去重）

DISTINCT COL1,COL2

DISTINCT 一定要放在所有字段的最前端.
表示对由”COL1,COL2….”所组成的缓存结果进行消重. 如果结果集合中有完全相同的行,则只保留一行.

虚表

SELECT 365*225 FROM DUAL;

DUAL虚表，用于在Oracle中进行单独的算数运算

Oracle的sql语句必须存在FROM，所以无法直接进行算数运算，所以DUAL虚表应运而生，用于单独的算数运算。

当在sql语句中发现DUAL表时，则表示该语句仅进行算数运算。

在线练习：

https://livesql.oracle.com/

SELECT语句

基本SELECT语句

SELECT 内容 FROM 表、集合 WHERE 条件

1. *表示所有字段

1. 多个字段之间可以用逗号隔开

1. *不能与其他字段混用，如果需要同时使用则需要表名.*的方式

虚列

Oracle数据库中存在虚列的概念

1. 虚列本身不是表中的字段，但是可以当成字段来使用

1. 表示某个集合结果中的行序

1. ROWNUM的初始值为0，每SELECT一行加1，然后进行WHERE匹配，若匹配成功则进入最终结果缓存，若匹配不成功则减1

1. ROWNUM只和当前执行的SELECT有关

SELECT ROWNUM,EMP.* FROM EMP WHERE ROWNUM = 1;
//上述sql语句会显示在表中第一行数据
//如果写成

SELECT ROWNUM,EMP.* FROM EMP WHERE ROWNUM = 2;
//上述语句是错误的，因为ROWNUM是从0开始累加的，当ROWNUM等于1时
//进行条件判断，不等于2，则根据规则减1，所以输出的结果为空

//如果想要显示第2条，可以使用
SELECT ROWNUM,EMP.* FROM EMP WHERE ROWNUM <= 2;
//上述语句会显示两条

计算列

1. 每SELECT一次，计算一次

1. +-*/

1. NULL在参与算数预算时结果仍然为NULL

1. 可以使用NVL(V1,V2)进行转换，如果V1为NULL则返回V2，否则返回V1

SELECT NVL(COMM,0)+2000 FROM EMP;
//这样如果COMM为NULL，也能正常进行加2000的操作了

字符串连接列

SELECT EMPNO||'=='||ENAME FROM EMP;
//实现使用==连接

符号使用单引号，如果想要使用的连接内容存在单引号则使用''两个单引号表示

给字段起别名

1. 字段名 AS 别名

1. 字段名别名

1. 字段名 “别名”

1. 字段名 AS “别名”

AS有与没有无本质区别

消除重复行（去重）

DISTINCT COL1,COL2

1. DISTINCT 一定要放在所有字段的最前端.

1. 表示对由”COL1,COL2….”所组成的缓存结果进行消重. 如果结果集合中有完全相同的行,则只保留一行.

虚表

SELECT 365*225 FROM DUAL;

DUAL虚表，用于在Oracle中进行单独的算数运算

Oracle的sql语句必须存在FROM，所以无法直接进行算数运算，所以DUAL虚表应运而生，用于单独的算数运算。

当在sql语句中发现DUAL表时，则表示该语句仅进行算数运算。

排序

SELECT…FROM 表-集合 WHERE 条件

ORDER BY COL1 ASC|DESC,COL2 ASC|DESC,….

ORDER BY 在最后，默认升序排列

可以使用多列排序，逗号隔开,order by在最后执行

ORDER BY SAL DESC,EMPNO;

1. ASC升序（默认）

1. DESC降序

1) 单列排序:

ORDER BY COL1 ASC|DESC

缓存结果按COL1的值进行排序.

2) 多列排序:

ORDER BY COL1 ASC|DESC,COL2 ASC|DESC,….

1> 表示先按COL1的值进行排序, 然后,COL1值相同的部分再按COL2进行排序,依次类推.

2> 后边的排序是在前面排序的基础上进行的,类似与组内排序.

条件运算

SELECT…FROM…WHERE 条件

1. 简单条件

> , < , > = , < = , = , ! = , < > , ^ =

1> 数值直接比较大小.

2> 字符串按ASCII比较大小.

3> 日期类型按日期大小进行比较. 如果日期用字符串进行描述,则该字符串必须符合标准日期格式’DD-MON-YY’,如’8-8月-08’或者’8-8月-2008’或者’08-8月-2008′. 这样才能参与比较.

注意：标准日期格式只能是单引号，不能是双引号或者没有引号。在后面INSERT INTO 插入数据中同理。

注意：在oracle中使用类似于COMM != 0仍然会把NULL值排除，因为在比较过程中NULL值仍然会返回NULL而不是TRUE或者FALSE（不是一个数字），所以会被排除。

WHERE后逻辑条件

AND OR NOT

1. AND,OR的左右一定要是完整的条件. “条件1 AND 条件2”

1. NOT后必须是完整的条件. “NOT 条件”

1. 如果某个条件同时出现了AND,OR,NOT. 则按如下优先级从高到低运算:

NOT —> AND —> OR

WHERE后特殊条件

关键字

1. [NOT] BETWEEN…AND…

1. IS [NOT] NULL中的NOT不可以提前！！！！！

1. [NOT] LIKE

1. [NOT] IN(集合)

1. ANY-SOME(集合)

1. ALL(集合)

1. EXISTS(子查询)

1. [NOT] BETWEEN 下限 AND 上限

判断字段的值是否在上下限之间. 包括上下限.

下限一定要小于等于上限.

2. IS [NOT] NULL

判断某个字段的值是否为NULL.

1> NULL在参与简单比较运算的时候,结果仍然为NULL.

2> 因此,判定NULL值不能使用简单条件,否则没有结果.

只能使用IS NULL来进行判断.

3. [NOT] LIKE ‘匹配字符串’

判断某个字段的值是否与匹配字符串匹配.

匹配符号:

1> % : 代表任意多个字符.

2> _ : 代表一个字符,如一个中文字符或英文字符等均可.

4. [NOT] IN(集合)

判断某字段的值是否在集合之中.

1> 此处的集合为”多行单列”集合.

2> 可以表达为(V1,V2,V3,….),或是一个多行单列子查询.

注意: IN可以改写为OR, 如 “COL1 IN (V1,V2,….)” <==> “COL1=V1 OR COL1= V2 OR ….”

5. ANY(集合)

判断某字段的值是否和集合中的某一项匹配,只要有任意一项匹配,则成功.

1> 集合也是多行单列集合.

用子查询表示.

2> ANY前必须是简单比较符号: > ,< ,>=,=,<=…..

> ANY(…) <==> 大于最小值.

< ANY(…) <==> 小于最大值

6. ALL(集合)

判断某字段的值是否和集合中的所有项匹配,只有所有项匹配,才成功.

1> 集合也是多行单列集合.

用子查询表示.

2> ALL前必须是简单比较符号: > ,< ,>=,=,<=…..

> ALL(…) <==> 大于最大值.

< ALL(…) <==> 小于最小值

7. EXISTS(子查询)

如果子查询能够返回至少一行结果,则为真.

SELECT * FROM EMP WHERE EMPNO = 7839 AND EXISTS(SELECT * FROM EMP WHERE EMPNO = 7788 AND SAL > 2900);

数值函数

1. ABS 求绝对值函数 ABS(5) 可以使用数字和字符串ABS(‘-6’)也可行

1. SQRT 求平方根函数 SQRT(2) 1.41421356 可以使用数字和字符串SQRT(‘3’)也可行,不能是负数

1. POWER 求幂函数 POWER(2,3) 8 可以使用数字和字符串POWER(‘2′,’3’)也可行,两个参数均可以是负数

1. COS 求余弦三角函数 COS(3.14159) 可以使用数字和字符串COS(‘3.14159’)也可行,参数可以是负数

1. MOD 求除法余数 MOD(1600, 300) 100 可以使用数字和字符串MOD(‘4′,’3’)也可行,两个参数均可以是负数

1. CEIL 求大于等于某数的最小整数 CEIL(2.35) 3 可以使用数字和字符串CEIL(‘2.35’)也可行,参数可以是负数、

1. FLOOR 求小于等于某数的最大整数 FLOOR(2.35) 2 可以使用数字和字符串FLOOR(‘2.35’)可行,参数可以是负数

1. ROUND 按指定精度对十进制数四舍五入 ROUND(45.923, 1)

ROUND(-245.954,’-2′) = -200 可以使用数字和字符串,参数可以为负数

1. TRUNC 按指定精度截断十进制数 TRUNC(45.923, 1) 类似与ROUND（）

字符串函数

1. ASCII 获得字符的ASCII码 ASCII(‘A’) 65 如果写多个字符只输出第一个字符的ascii码，必须加单引号

1. CHR 返回与ASCII码相应的字符 CHR(65) A 参数可以加单引号，无影响

1. LOWER 将字符串转换成小写 LOWER (‘SQL Course’) sql course 必须加单引号

1. UPPER 将字符串转换成大写 UPPER(‘SQL Course’) SQL COURSE 必须加单引号

1. INITCAP 将字符串转换成每个单词以大写开头

INITCAP(‘SQL course’) Sql Course

1. CONCAT 连接两个字符串 CONCAT(‘SQL’, ‘ Course’) SQL Course 可以嵌套CONCAT(INITCAP(‘sql’),’w’)

1. SUBSTR 给出起始位置和长度，返回子字符串 SUBSTR(‘String’,1,3) Str 从1开始，第二个参数为长度不是下标

1. LENGTH 求字符串的长度 LENGTH(‘Wellcom’) 7

1. INSTR 给出起始位置和出现的次数，求子字符串在字符串中出现的位置

INSTR('String', 'r', 1, 1) //3

INSTR(string, substring, start_position, occurrence)
/*string: 要搜索的主字符串。
substring: 要查找的子字符串。
start_position: 从主字符串的哪个位置开始搜索（1表示从第一个字符开始）。
occurrence: 要查找的子字符串出现的次数（1表示第一次出现，2表示第二次出现，以此类推）。
*/

1. LPAD 用字符填充字符串左侧到指定长度 LPAD(‘Hi’,10,’-‘) ——–Hi 长度加不加引号无影响，第1 3个参数数字可以不加单引号，字符必须加

1. RPAD 用字符填充字符串右侧到指定长度 RPAD(‘Hi’,10,’-‘) Hi——–

1. TRIM 在一个字符串中去除另一个字符 TRIM(‘S’ FROM ‘SSMITH’) MITH 参数可以是字符和数字，但是只能去除在字符串或数字两端的字符，在中间的无法去除，并且截取集只能有一个字符

1. REPLACE 用一个字符串替换另一个字符串中的子字符串

REPLACE(‘ABC’, ‘B’, ‘D’) ADC 数字有无引号不影响

日期函数

1) 标准日期格式: DD-MON-YY

默认年份为1950~2049年之间,因此,60年表示的是1960的意思.

2) SYSDATE : 虚函数,表示当前服务器系统时间.

日期的计算:

1) 日期只能按天进行计算.

2) 日期与整数进行加减,表示对天进行计算.

3) 若希望对小时进行计算,则必须按24小时转换为天进行. 如12小时,则可以用0.5表示.

4) 两个日期相减,得到相隔的天数.

SELECT ENAME,SYSDATE-HIREDATE FROM EMP;

转换函数

隐式转换:

1) 字符串到数值。

当字符串参与算术运算的时候。

要求: 该字符串必须是由数字符号所组成。

2) 字符串到日期。

当字符串参与日期运算的时候. 要求该字符串由标准日期格式组成”DD-MON-YY”

3) 数值到字符串

4) 日期到字符串

SELECT '123'+1,'123'||1 FROM DUAL;

1) TO_CHAR 2) TO_NUMBER 3) TO_DATE

1）TO_CHAR(DATA, FORMAT) 函数

该函数用于将某个数据按指定的格式 FORMAT 转换成字符串。

a. 日期类型格式 (当 DATA 为日期类型时)

- 年:

- - - Y : 年
  - - YY : 两位数年份
  - - YYY : 三位数年份
  - - YYYY : 四位数年份

- 月:

- - - MM : 月份（1-12）
  - - MON : 月份简写
  - - MONTH : 完整月份名称

- 日:

- - - DD : 日（01-31）

- 星期:

- - - D : 数字表示的星期（周日为 1，周六为 7，范围 1-7）
  - - DY : 星期的简写
  - - DAY : 完整星期名称

- 时:

- - - HH : 12小时制
  - - HH12 : 12小时制
  - - HH24 : 24小时制（0-23）

- 分:

- - - MI : 分钟（0-59）

- 秒:

- - - SS : 秒（0-59）

- 上下午:

- - - AM : 上午
  - - PM : 下午

b. 特殊符号和文本

- 特殊符号: 可以使用的符号包括 -, ., :, ;, /

- 文本: 可以包含文本，格式为 "文本"

SELECT TO_CHAR(SYSDATE, '"现在是:"YYYY-MM-DD HH24:MI:SS/AM') FROM DUAL;
//现在是:2024-10-07 12:38:11/下午

2) TO_NUMBER(DATA,FORMAT) : 将某个数据按对应的格式FORMAT转换成数值.

若没有格式,则DATA必须是由数字符号组成的字符串.

- 9999 : 表示占位符，数字会填充到这个位置

- 0 : 强制显示数字（如果没有数字则显示为 0）

- . : 小数点

- , : 千位分隔符

SELECT TO_NUMBER('1,223.43','99,999.99') FROM DUAL;

3) TO_DATE(DATA,FORMAT) : 按某个格式转换为日期.

若没有格式,则DATA必须是有标准日期格式形成的字符串.

DD-MON-YY, DD-MON-YYYY

SELECT TO_DATE('10\7\24','MM\DD\YY') FROM DUAL;
\\2024/10/7

日期格式2:

1) 基数: SP

- 使用 SP 表示基数（如 “one”, “two”, “three” 等）。

序数: TH

- 使用 TH 表示序数（如 “first”, “second”, “third” 等）。

2) 大小写:

- 全小写:

- - - 当格式字符串以小写字母开头时，后续的字母也应为小写。
  - - 示例:

- - - - yyyy 表示四位数年份（如 2023）。
      - mm 表示月份（01-12）。
      - dd 表示日期（01-31）。

- 大写开头:

- - - 如果格式字符串以大写字母开头，后续字母的大小写跟随首字母的大小写。
  - - 示例:

- - - - Yyyy 表示四位数年份，首字母大写，后续小写。

- 其他情况全大写:

- - - 如果格式字符串不符合上述两种情况，则全为大写。
  - - 示例:

- - - - YYYY, MM, DD 等。

示例

- 日期格式示例:

- - - yyyy-mm-dd → 2023-10-07
  - - Yyyy/MM/DD → 2023/10/07
  - - MM-DD-YYYY → 10-07-2023

其他函数

USERENV, NVL, DECODE, GREATEST, LEAST

USERENV函数

1. USERENV(‘环境字符串’) 返回用户机对应的环境信息.

环境字符串

* ISDBA：判断会话用户的角色是否为SYSDBA，是则返回TRUE。

* INSTANCE：返回会话连接的INSTANCE标识符。

* LANGUAGE：返回语言、地区、数据库字符集信息。

* LANG：返回会话语言的ISO简称。

* TERMINAL：返回正在会话的终端或计算机的标识符

NVL函数

2. NVL(V1,V2) 空值转换函数.

当V1为NULL时,返回V2,否则返回V1.

注意:该方法在对数值进行计算或修改时非常重要!

DECODE函数

3. DECODE(CHOICE,K1,V1,K2,V2,…,Kn-1,Vn-1,Vn)

表示,用CHOICE与K1,K2,…Kn-1进行匹配,若有某个K值与CHOICE相等,则返回对应的V值.

若没有任何K值匹配,则返回Vn.

此时若没有Vn,则返回NULL.

注意: K1,K2,…必须是常数.并且,K1,K2,…的类型由K1的类型决定.

SELECT 
    employee_id,
    DECODE(department_id,
           10, 'HR',
           20, 'IT',
           30, 'Finance',
           'Unknown') AS department_name
FROM 
    employees;

GREATEST函数

4. GREATEST(K1,K2,K3,…) 选最大值.

注意: K1,K2,…的类型必须一致,并且类型由K1决定.

在字符串比较过程中字符的大小是按字典顺序（字母顺序）进行比较的，而不是按数值大小

LEAST函数

5. LEAST(K1,K2,K3,…) 选最小值.

注意: K1,K2,…的类型必须一致,并且类型由K1决定.

字符的大小是按字典顺序（字母顺序）进行比较的，而不是按数值大小

多表联合查询

SELECT 列名,… FROM 表1,表2,… [WHERE …] [ORDER BY]

-表可以起别名: 表名别名或表名 “别名”

如果表一旦起别名，接下来的查询中只要用到表名的地方全部使用表的别名

–列名: 如果表中有相同的字段,避免发生歧义，要使用表名.列名

SELECT EMPNO,ENAME,E.DEPTNO,DNAME FROM EMP E,DEPT

两个表连接，相当于进行笛卡尔积操作

存在如下两表

student:
sno  sname
01    zhangsan
02    lisi

sc:
sno  cno  grade
01   1     99
01   2     55
02   1     88

SELECT * FROM STUDENT,SC;

sno  sname  sno  cno grade
01   zhangsn 01   1   99
01   zhangsn 01   2   55
01   zhangsn 02   1   88
02   lisi    01   1   99
02   lisi    01   2   55
02   lisi    02   1   88

需要再笛卡尔积的基础上，进行条件筛选WHERE

1. 相等连接

表中有相同字段，做相等条件连接

SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

1. 不等连接

表中没有相同字段，但有关联，可以用不等条件进行连接

SELECT * FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL;

1. 自连接

表与表自身进行连接(表的副本,给表起别名)

查询员工及员工对应的经理信息

SELECT * FROM EMP E,EMP M WHERE E.EMPNO = M.MGR;

1. 外连接

1. 1. 1. 左外连接（右条件做严格的判断）

左外连接会返回左表（表1）中的所有行，以及右表（表2）中匹配的行。如果右表中没有匹配的行，结果中对应的右表字段会填充为 NULL。

SELECT ... FROM 表1，表2 WHERE 左条件=右条件(+)
SELECT ... FROM 表1 LEFT OUTER JOIN 表2 ON 左条件=右条件
SELECT * FROM EMP e,Emp m where e.mgr=m.empno(+);
SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO(+);

SELECT * FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO

显示员工及员工部门和工资级别信息

1. 1. 1. 右外连接

右外连接与左外连接相反，它会返回右表（表2）中的所有行，以及左表（表1）中匹配的行。如果左表中没有匹配的行，结果中对应的左表字段会填充为 NULL。

SELECT ... FROM 表1，表2 WHERE 左条件(+)=右条件
SELECT ... FROM 表1 RIGHT OUTER JOIN 表2 ON 左条件=右条件
SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

1. 1. 1. 全外连接

全外连接（FULL OUTER JOIN）是 SQL 中的一种连接方式，它会返回两个表中所有的记录。对于没有匹配的记录，结果中相应的字段会填充为 NULL。

SELECT ... FROM 表1 FULL OUTER JOIN 表2 ON 左条件=右条件
SELECT * FROM EMP FULL OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

INNER JOIN

INNER JOIN 用于连接两个或多个表，并返回满足连接条件的记录。只有在两个表中都有匹配的记录时，结果集才会包含这些记录。

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

在 JOIN 中，可以使用 WHERE 子句或在 ON 子句中直接添加条件。这里的条件可以是任何有效的 SQL 表达式。

INNER JOIN user_profile AS up ON up.device_id = qpd.device_id
AND up.university = '浙江大学'

统计查询

SELECT 内容 
FROM 表名
WHERE 条件
GROUP BY 分组字段
HAVING 条件
ORDER BY 排序字段

- 在统计查询中，内容只能是分组字段或统计函数。

- 如果省略GROUP BY，则把当前表看成是一个组，对整张表进行统计操作

统计函数

- 常用统计函数包括：

- - - SUM(列名): 计算总和
  - - AVG(列名): 计算平均值
  - - MAX(列名): 计算最大值
  - - MIN(列名): 计算最小值
  - - COUNT(): 统计记录个数

计数函数

- COUNT(*): 统计所有行（包括空值）。

- COUNT(列名): 统计指定列的非空值。

- COUNT(DISTINCT 列名): 统计指定列的非空且不重复值。

示例查询

统计员工表的工资总和、平均工资、最大工资和最小工资：

SELECT SUM(SAL), AVG(SAL), MAX(SAL), MIN(SAL) FROM EMP;

统计员工表的记录数

SELECT COUNT(*) FROM EMP;

统计不同部门的员工数量及工资统计：

SELECT COUNT(DISTINCT DEPTNO), SUM(SAL), AVG(SAL), MAX(SAL), MIN(SAL) FROM EMP;

单列分组

- 使用 GROUP BY 对单个列进行分组：

SELECT DEPTNO, SUM(SAL), AVG(SAL), MAX(SAL), MIN(SAL) 
FROM EMP
WHERE DEPTNO IS NOT NULL
GROUP BY DEPTNO;

多列分组

- 使用 GROUP BY 对多个列进行分组：

SELECT DEPTNO, JOB, SUM(SAL) 
FROM EMP
GROUP BY DEPTNO, JOB;

HAVING子句

- 用于对分组统计结果进行条件筛选：

SELECT DEPTNO, COUNT(*) AS a 
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) >= 5
ORDER BY a;

嵌套函数

- 统计平均工资最高的部门：

SELECT MAX(AVG(SAL)) 
FROM EMP
WHERE DEPTNO IS NOT NULL
GROUP BY DEPTNO;

执行顺序

- SQL 查询的执行顺序为：

1. 1. 1. FROM
  1. 1. WHERE
  1. 1. GROUP BY
  1. 1. HAVING
  1. 1. SELECT
  1. 1. ORDER BY

子查询

单行单列子查询

查询和7788员工相同职务的员工信息

select * from EMP 
where job=(SELECT JOB FROM EMP WHERE EMPNO=7788)

单行多列子查询

查询和7788员工相同职务和工资的员工信息

select * from EMP 
where (job,sal)=(SELECT JOB,sal FROM EMP WHERE EMPNO=7788)

多行单列子查询(IN,ANY,ALL)

select * from EMP 
where sal>all(SELECT SAL FROM EMP WHERE DEPTNO=20)

多行多列子查询

from后面,把子查询看成是一个表集合.

select * from(
select deptno,avg(sal) from emp
where deptno is not null
group by deptno
order by avg(sal)
)where rownum=1

输出平均薪资最高的部门号及平均薪资

select deptno, avg(sal) from emp
where deptno is not null
group by deptno
having avg(sal)=(
       select max(avg(sal)) from emp
       where deptno is not null
       group by deptno
);

统计平均工资最高部门对应的员工信息

select * from emp where
deptno =(
select deptno from emp
where deptno is not null
group by deptno
having avg(sal)=(
       select max(avg(sal)) from emp
       where deptno is not null
       group by deptno
)
)

表与表的集合操作

UNION:并集消重

UNION ALL：并集不消重

select job from emp where deptno =10
union all
select job from emp where deptno =20

INTERSECT：交集

select job from emp where deptno =10
intersect
select job from emp where deptno =20

MINUS:差集

select job from emp where deptno =10
minus
select job from emp where deptno =20

视图的定义与使用

视图的定义

视图是基于一个或多个表的虚拟表，他的内容由查询定义。视图可以简化复杂的查询、增强安全性。

创建视图的基本语法

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图名
  [(别名[, 别名]...)]
AS 子查询
[WITH CHECK OPTION [CONSTRAINT 约束名]
[WITH READ ONLY]

参数说明：

OR REPLACE如果视图已存在则替换它。

FORCE:即使基础表不存在也创建视图

NOFORCE仅在基础表存在的时候才创建视图（默认）

别名为查询结果中的列指定别名

子查询定义视图的基础查询语句

WITH CHECK OPTION确保通过视图插入或更新的数据符合视图的查询条件

WITH READ ONLY指定视图为只读，禁止更新

[CONSTRAINT 约束名]可选项，为这个约束起名方便后续管理

示例1创建带检查约束的视图

CREATE OR REPLACE VIEW DEPT10 
       AS SELECT * FROM EMP WHERE DEPTNO=10
          WITH CHECK OPTION CONSTRAINT DEPT1_CONS;

解释：

- WITH CHECK OPTION: 这个选项确保通过视图进行的任何插入或更新操作都必须符合视图定义的查询条件。在这个示例中，视图 DEPT10 只包含 DEPTNO=10 的员工记录。

- CONSTRAINT DEPT1_CONS: 这是为检查约束命名的，便于后续管理和识别。这个名称可以用于修改或删除约束。

作用：

- 数据完整性: 确保所有通过视图插入或更新的数据都符合视图的条件。例如，任何试图插入或更新 DEPT10 视图的操作，必须保证 DEPTNO 的值为 10。如果尝试插入或更新为其他部门号，将会导致操作失败。

- 防止不一致数据: 通过强制执行条件，避免了在视图中出现不符合逻辑的数据，从而保持数据的一致性和准确性。

如果：

你尝试进行下列操作

INSERT INTO DEPT10 (EMPNO, ENAME, DEPTNO) VALUES (7783, 'John', 20);

上述操作将会失败，因为DEPTNO不符合视图DEPT10的条件，能够有效的保护视图的完整性。

示例2查询视图

SELECT EMPNO,ENAME,DEPTNO FROM DEPT10 WHERE EMPNO=7782;

示例3创建另一个视图

CREATE OR REPLACE VIEW DEPT20
  AS SELECT * FROM EMP WHERE DEPTNO=20;

示例4尝试更新视图

UPDATE DEPT10 SET SAL = 3000 WHERE EMPNO = 7782;

将工号为7782的员工工资改为3000

注意：在涉及到表的更新时请注意遵循视图设置的约束条件

示例5强制创建视图

CREATE FORCE VIEW SUT1
  AS SELECT * FROM STUDENT WHERE CLASSNO = 1;

权限管理

首先切换到管理员用户，然后执行以下语句。

GRANT CREATE ANY VIEW TO SCOTT;

收回授权

REVOKE CREATE ANY VIEW FROM SCOTT;

视图的优点

1. 简化复杂查询：可以将复杂的SQL查询封装在视图中，方便使用

1. 增强安全性：可以限制用户访问特定数据，提供更安全的数据访问方式

1. 数据抽象：提供不同的数据视图，使用不同用户的需求

注意事项：

- 更新视图时需要注意约束条件，可能会导致更新失败

- 视图本身不存储数据，数据的变化会反映在基础表中

表操作语句（DDL)

建表基本语法

CREATE TABLE [模式名.]表名 
(
    字段名 数据类型[(长度)] [DEFAULT 默认值] [列级约束],
    ......
    [表级约束]
) 
[TABLESPACE 指定表空间]

说明：

- 模式名: 可选，指定表所属的模式（用户）。

- 表名: 要创建的表的名称。

- 字段名: 列的名称。

- 数据类型: 列的数据类型，如 CHAR, VARCHAR, VARCHAR2, NUMBER, INT, DATE 等。

- DEFAULT 默认值: 为字段指定默认值，当插入时未指定该字段时，将使用默认值。

- 列级约束: 针对单个列的约束（如 NOT NULL）。

- 表级约束: 针对整个表的约束（如主键、外键）。

- TABLESPACE: 可选，指定表的存储空间。

数据类型

CHAR:固定长度字符类型

VARCHAR/VARCHAR2可变长度字符类型

NUMBER数值类型

INT整数类型

DATE日期类型

- VARCHAR: 在不同的数据库管理系统（DBMS）中，VARCHAR 的行为可能有所不同。是一个通用的数据类型。

- VARCHAR2: 是 Oracle 数据库特有的类型，专门设计用于存储可变长度的字符串。它在 Oracle 中是推荐使用的字符串类型。

- VARCHAR: 在某些 DBMS 中，VARCHAR 可能会存储空字符串（''）和 NULL 值作为相同的内容，具体取决于实现。

- VARCHAR2: 在 Oracle 中，VARCHAR2 会将空字符串视为 NULL，这意味着如果存储空字符串，实际上存储的是 NULL。

- VARCHAR: 最大长度因 DBMS 而异，通常为 255 到 4000 字符，具体取决于实现。

- VARCHAR2: 在 Oracle 中，最大长度可以设置为 4000 字符（在某些情况下，使用 VARCHAR2 可以支持更大的长度，最高可达 32,767 字符，但需要使用 extended 语法）。

示例：创建学生表与操作

创建表

CREATE TABLE STUDENTS 
(
    SNO NUMBER(4) DEFAULT 1111,  -- 学生编号，默认值为 1111
    SNAME VARCHAR2(20),            -- 学生姓名
    SSEX CHAR(2) DEFAULT '男',     -- 性别，默认值为 '男'
    SAGE NUMBER(2) DEFAULT 20,     -- 年龄，默认值为 20
    BIRTHDATE DATE                 -- 出生日期
);

插入数据

INSERT INTO STUDENTS (SNAME, BIRTHDATE) VALUES ('TEST', '1-1月-2024');

- 说明: 在插入时，未指定 SNO, SSEX, 和 SAGE 字段，因此将使用定义的默认值。

查询数据

SELECT * FROM STUDENTS;

约束（DDL）

约束类型

列级约束与表级约束

- 列级约束: 约束某个指定字段的值，定义在对应字段之后，只能约束一个字段。

- 表级约束: 约束一个或多个字段，定义在表的最后部分，可以约束多个字段。

五大约束

1. 主键约束:

- - - 唯一标识一个实体，主键必须唯一且非空。
  - - 一个表只能有一个主键，建议由一个字段组成，最多不要超过 3 个字段。
  - - 列级定义: 字段名数据类型 [CONSTRAINT 约束名] PRIMARY KEY
  - - 表级定义: [CONSTRAINT 约束名] PRIMARY KEY (COL1, COL2, ...)

1. 非空约束:

- - - 约束某个字段的值必须非空。
  - - 只有列级约束。
  - - 定义: 字段名数据类型 [CONSTRAINT 约束名] NOT NULL

1. 唯一约束:

- - - 要求某个字段或字段的组合的值必须唯一，允许为空。
  - - 列级定义: 字段名数据类型 [CONSTRAINT 约束名] UNIQUE
  - - 表级定义: [CONSTRAINT 约束名] UNIQUE (COL1, COL2, ...)

1. 检查约束:

- - - 要求某字段的值必须满足特定条件。
  - - 列级定义: 字段名数据类型 [CONSTRAINT 约束名] CHECK (约束条件)
  - - 表级定义: [CONSTRAINT 约束名] CHECK (约束条件)

1. 参照完整性约束（外键约束）:

- - - 确保子表中的外键字段参照主表中的主键或唯一键。
  - - 定义: [CONSTRAINT 约束名] FOREIGN KEY (COL1, COL2, ...) REFERENCES 主表 (C1, C2, ...)
  - - 级联操作：

- - - - 级联删除: ON DELETE CASCADE，删除主表数据时，子表相应数据也被删除。
      - 级联置空: ON DELETE SET NULL，删除主表数据时，子表相应数据置为 NULL（外键允许为空）。

示例：

创建学生表

DROP TABLE STUDENTS;

CREATE TABLE STUDENTS 
(
    SNO NUMBER(4),
    SNAME VARCHAR2(20) NOT NULL,
    SSEX CHAR(2) DEFAULT '男',
    SAGE NUMBER(2) DEFAULT 20,
    BIRTHDATE DATE,
    PRIMARY KEY (SNO)
);

创建课程表

DROP TABLE COURSES;

CREATE TABLE COURSES 
(
    CNO NUMBER(4) PRIMARY KEY,
    CNAME VARCHAR2(50),
    CREDIT NUMBER(2) DEFAULT 2
);

创建选课表

DROP TABLE SC;

CREATE TABLE SC 
(
    SNO NUMBER(4),
    CNO NUMBER(4),
    GRADE NUMBER(2),
    PRIMARY KEY (SNO, CNO),
    FOREIGN KEY (SNO) REFERENCES STUDENTS(SNO) ON DELETE CASCADE,
    FOREIGN KEY (CNO) REFERENCES COURSES(CNO) ON DELETE SET NULL
);

注意事项

- 约束的命名: 约束名在模式中应唯一，若未显式定义，系统会自动分配。

- 数据完整性: 通过合理使用约束，确保数据库中的数据一致性和完整性。

- 性能考虑: 主键和唯一约束会影响插入和更新的性能，需合理设计。

表操作-删除表（DDL）

删除表

DROP TABLE 表名;

- 用于删除整个表及其所有数据和结构。

在mysql中存在drop table if exists `user_profile`;语句，表示如果表不存在则删除。oracle数据库中不存在，但是可以使用如下格式来实现。（扩展）

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE user_profile';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN  -- -942 表示表不存在
            RAISE;  -- 重新抛出其他异常
        END IF;
END;
/

修改表

添加字段

ALTER TABLE 表名
ADD (
    字段名 数据类型 DEFAULT 默认值 [列级约束],
    ...
);

修改字段

ALTER TABLE 表名
MODIFY (
    字段名 数据类型 DEFAULT 默认值,
    ...
);

没法改列名

删除字段

ALTER TABLE 表名
DROP (
    COLUMN 字段名,
    ...
);

- 用于删除一个或多个字段。

注意：

- 如果要删除的字段是多列约束的一部分，必须添加 CASCADE CONSTRAINTS。

ALTER TABLE 表名
DROP (
    COLUMN 字段名
) CASCADE CONSTRAINTS;

总结

- 使用 ALTER TABLE 命令可以灵活地对表结构进行修改，包括添加、修改和删除字段。

- 在进行结构修改时，需谨慎操作，确保数据完整性和约束条件的有效性。

数据操作（DML）

插入数据

单行插入

INSERT INTO 表名 [(COL1, COL2, ...)] VALUES (V1, V2, ...);

1. 将值 V1,V2,…分别插入到字段 COL1,COL2,… 中。

- - - 值和字段在类型、顺序、个数上必须一致。

1. 没有出现在 COL1,COL2,… 中的字段将插入默认值；若无默认值，则插入 NULL。

1. 若不指定字段名，则默认插入所有字段，值必须与表的原始字段列表一致。

示例：

INSERT INTO EMP (EMPNO, ENAME) VALUES (2, 'TEST2');
SELECT * FROM EMP;
ROLLBACK;  -- 撤销操作
COMMIT;    -- 提交操作

批量插入

INSERT INTO 表名 [(COL1, COL2, ...)] SELECT V1, V2, ... FROM ... WHERE ...;

1. 将子查询的结果插入到表中，V1,V2,… 必须与 COL1,COL2,… 在类型、顺序、个数上保持一致。

示例：

INSERT INTO BONUS (ENAME, JOB, SAL, COMM) SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE DEPTNO = 10;
SELECT * FROM BONUS;

修改数据

更新数据

UPDATE 表名 SET COL1 = V1, COL2 = V2, ... WHERE 条件;

1. 修改符合条件的数据，COL1的值修改为 V1，依此类推。

1. 正在修改的数据将被加锁，其他会话无法修改或删除，直到解锁。

1. 使用 COMMIT 或 ROLLBACK 解锁。

UPDATE BONUS SET COMM = 100 WHERE ENAME = 'KING';
SELECT * FROM BONUS;

使用子查询更新数据

UPDATE 表名 SET (COL1, COL2, ...) = (SELECT C1, C2, ... FROM ... WHERE ...) WHERE 条件;

1. 子查询必须是单行多列子查询。

1. 将 COL1,COL2,… 的值依次修改为 C1,C2,…的值。

删除数据

DELETE FROM 表名 WHERE 条件;

1. 删除符合条件的数据。

1. 删除的数据将被加锁，直到 COMMIT 或 ROLLBACK。

DELETE FROM BONUS WHERE ENAME = 'KING';

清空表

TRUNCATE TABLE 表名;

1. 删除表中所有数据。

1. 该语句自动 COMMIT，无法 ROLLBACK。

TRUNCATE TABLE MANAGER;

数据库事务

- 定义: 对数据库进行的完整操作，要么全部完成，要么全部失败。

- 特性: 一致性、原子性、持久性、独立性。

- 事务范围: 两个连续的 COMMIT 或 ROLLBACK 之间的部分称为一个事务。

事务操作

1. COMMIT: 提交事务，当前事务中的所有操作被提交到数据库。

1. ROLLBACK: 回滚事务，当前事务中的所有操作被撤销。

1. SAVEPOINT 保存点名称: 创建保存点。

1. ROLLBACK TO 保存点: 回退到指定的保存点。

序列、索引与同义词

序列

创建序列

CREATE SEQUENCE 序列名
    [INCREMENT BY n]
    [START WITH n]
    [{MAXVALUE n | NOMAXVALUE}]
    [{MINVALUE n | NOMINVALUE}]
    [{CYCLE | NOCYCLE}]
    [{CACHE n | NOCACHE}];

参数说明：

- INCREMENT BY n: 每次生成的值增加的步长。

- START WITH n: 序列的起始值。

- MAXVALUE n: 序列的最大值。

- MINVALUE n: 序列的最小值。

- CYCLE: 达到最大值后重新开始。

- NOCYCLE: 达到最大值后停止。

- CACHE n: 缓存的序列值数量。

- NOCACHE: 不缓存序列值。

CREATE SEQUENCE xulie
    INCREMENT BY 2
    START WITH 0
    MAXVALUE 100
    MINVALUE 0
    NOCYCLE
    NOCACHE;

SELECT xulie.NEXTVAL FROM DUAL;  -- 获取下一个序列值
SELECT xulie.CURRVAL FROM DUAL;   -- 获取当前序列值

CREATE SEQUENCE EMPNO_SEQUENCE 
    INCREMENT BY 1
    START WITH 8001
    MAXVALUE 8999
    MINVALUE 8001
    NOCYCLE
    NOCACHE;

伪列

- NEXTVAL: 返回序列的下一个值。

- CURRVAL: 返回序列的当前值。

修改序列

ALTER SEQUENCE 序列名
    [INCREMENT BY n]
    [START WITH n]
    [{MAXVALUE n | NOMAXVALUE}]
    [{MINVALUE n | NOMINVALUE}]
    [{CYCLE | NOCYCLE}]
    [{CACHE n | NOCACHE}];

ALTER SEQUENCE EMPNO_SEQUENCE 
    INCREMENT BY 2
    START WITH 8001
    MAXVALUE 8999
    MINVALUE 8001
    NOCYCLE
    NOCACHE;

删除序列

DROP SEQUENCE 序列名;

序列的使用

INSERT INTO EMP (EMPNO, ENAME) VALUES (EMPNO_SEQUENCE.NEXTVAL, 'TOM');
SELECT * FROM EMP;

系统索引使用

CREATE TABLE TEST (
    ID RAW(16) PRIMARY KEY,
    NAME CHAR(10)
);

INSERT INTO TEST VALUES (SYS_GUID(), 'zzz');
SELECT * FROM TEST;

SYS_GUID()系统生成在当前数据库中唯一的16位ID

索引

创建索引

CREATE INDEX 索引名
ON 表名 (列[, 列]...);

CREATE INDEX namexulie ON emp(ename);

删除索引

DROP INDEX 索引名;

同义词

创建同义词

CREATE [PUBLIC] SYNONYM 同义词名 FOR 对象;

删除同义词

DROP SYNONYM 同义词;

CREATE SYNONYM mingzi FOR emp.empno;
DROP SYNONYM mingzi;

补充

WHERE IN

现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据。

SELECT
    device_id,
    gender,
    age,
    university,
    gpa
FROM
    user_profile
WHERE
    university IN ('北京大学', '复旦大学', '山东大学');

ROUND/GROUP BY

现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

你的查询返回结果需要对性别和学校分组，结果保留1位小数，1位小数之后的四舍五入。

SELECT
    gender,
    university,
    count(gender) user_num,
    ROUND(avg(active_days_within_30), 1) avg_active_day,
    ROUND(avg(question_cnt), 1) avg_question_cnt
FROM
    user_profile
GROUP BY
    gender,
    university

CASE语句

case是一种多分支的函数，可以根据条件列表的值返回多个可能的结果

分类：

1. 简单CASE函数

1. 搜索CASE函数

简单CASE函数

CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

例如
查询班级表中的学生的班号、班名、系号和班主任号，并对系号作如下处理：
当系号为1时，显示 “计算机系”；
当系号为2时，显示 “软件工程系”；
当系号为3时，显示 “物联网系”。

SELECT 班号 ,班名,
CASE 系号
WHEN 1 THEN '软件工程系'
WHEN 2 THEN '计算机系'
WHEN 3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

搜索CASE函数

CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

例如：

SELECT 学号,课程号,
CASE
WHEN 成绩 >= 90 THEN '优'
WHEN 成绩 BETWEEN 80 AND 89 THEN '良'
WHEN 成绩 BETWEEN 70 AND 79 THEN '中'
WHEN 成绩 BETWEEN 60 AND 69 THEN '及格'
WHEN 成绩 <60 THEN '不及格'
END 成绩
FROM 成绩表
WHERE 课程号 = 'M01F011'

SELECT 班号 ,班名,
CASE
WHEN 系号=1 THEN '软件工程系'
WHEN 系号=2 THEN '计算机系'
WHEN 系号=3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

本博客所拥有的文章除特别声明外，均默认采用 CC BY 4.0 许可协议。
文章部分内容可能来源于公共网络，如有侵权，请联系博主在核实后进行修改或删除。

THE END

Oracle数据库文档
# 数据库 # Oracle