YashanDB SQL语言（进阶篇）
YashanDB SQL语言（基础篇）回顾
SQL语言概述
- 什么是SQL语言
SQL（Structured Query Language）翻译为结构化查询语言。
SQL是一种是用于访问和管理数据库的标准计算机语言，最新标准为ISO/IEC 9075:2023。
SQL语言集数据定义、操纵、控制功能于一体。
- SQL语言的优点
SQL不是某个特定数据库供应商专有的语言，几乎所有关系型数据库都支持SQL。
SQL语言简单易学，功能强大，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作
数据操纵语言
- SQL分类
管理数据库对象（表、索引、视图等对象）
权限控制
- 权限的分类
系统权限：执行某种系统级操作的权限，可能作用于对象，也可能作用于数据库级别的操作。
对象权限：对象权限允许用户对指定的对象执行指定的操作。
- 角色的分类
内置角色：内置角色在数据库建库期间预置。
自定义角色：自定义角色需自行创建。
- 角色的维护
创建角色：CREATE ROLE role_name
删除角色：DROP ROLE role_name
向角色赋予权限：GRANT privilege_name TO role_name
从角色收回权限：REVOKE privilege_name FROM role_name
表连接
-
表连接的类型
-
自连接
SELECT table_al ias1. col_names , table_al ias2. col_names FROM table_name table_al ias1 JOIN table_name table_al ias2 ON table_al ias1.match_col = table_al ias2.match_col WHERE condition;
SQL高级语法
条件表达式
- 条件表达式概述
条件表达式的作用
条件表达式可以搜索指定表达式的值，并根据实际值，返回对应结果。如果没有匹配分支，则返回默认值。
如果默认值未指定，则返回NULL。
- 条件表达式有哪些
CASE 表达式（ANSI SQL标准）
DECODE函数（崖山专有函数，兼容Oracle）
- 条件表达式示例
SELECT decode(1, '1' , 'Male' , '2' , ' Female' , 'Unknown') FROM dual ;
SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' WHEN 3 THEN 'three' ELSE 'other' END FROM dual ;
SELECT name, gender, decode(gender, '1' , 'Male' , '2' , ' Female' , 'Unknown') FROM emp;
- CASE和DECODE的区别
CASE 表达式是ANSI SQL标准的设计；DECODE函数是崖山兼容Oracle的专有函数。
CASE表达式可以做条件匹配和等值匹配；DECODE只能做等值匹配。
CASE 表达式
- CASE表达式的语法。（左图为简单CASE表达式语法图；右图为搜索CASE表达式语法图）
CASE表达式示例
DECODE函数（只能用于等值连接）
DECODE函数示例与CASE表达式的对比。
SELECT entry_date, CASE
WHEN entry_date < to_date('2019-01-01' , 'yyyy-mm-dd') THEN ' in 2018'
WHEN entry_date < to_date('2020-01-01' , 'yyyy-mm-dd') THEN ' in 2019'
WHEN entry_date < to_date('2021-01-01' , 'yyyy-mm-dd') THEN ' in 2020'
WHEN entry_date < to_date('2022-01-01' , 'yyyy-mm-dd') THEN ' in 2021'
ELSE 'out of range'
END
FROM sales.employees;
SELECT entry_date, decode(trunc(entry_date, 'yyyy'),
TO_DATE('2018-01-01', 'yyyy-mm-dd'), 'in 2018'
, to_date('2019-01-01', 'yyyy-mm-dd'), 'in 2019'
, to_date('2020-01-01', 'yyyy-mm-dd'), 'in 2020'
, to_date('2021-01-01', 'yyyy-mm-dd'), 'in 2021'
, 'out of range') FROM sales.employees;
- 条件表达式
在SQL开发中，建议为所有的CASE表达式指定ELSE子句。
虽然，忽略ELSE子句不会导致语法报错。
指定ELSE子句可以提高复杂SQL的可维护性，以避免在多层嵌套中出现由NULL导致的语义错误。
子查询
- 子查询概述
子查询是嵌套在SELECT，INSERT，UPDATE或者DELETE内部的查询，另外一个子查询内部的查询也是子查询
SELECT语句中FROM子句下的子查询也称为内嵌视图
- 子查询示例
SELECT t1. year, t1.month, t1.quantity , t1.amount, t2.produc t _name
FROM (SELECT YEAR, MO NTH, produc t, quantity, amount FROM sales . sales _ info) t1 JOIN sales .produc t t2
ON t1.product = t2.product _no
-- 别名 t1 所指代的查询是示例SQL内部的子查询
- 子查询的改写
SELECT语句中FROM子句下的子查询可以使用视图进行改写，其本质是一致的。崖山数据库优化器可以使用查
询改写，谓词推入等功能进行查询优化。
子查询改写示例
CREATE VIEW sales . sales_info2 AS SELECT YEAR, MO NTH,produc t,quantity,amount FROM sales.sales_info;
SELECT t1. year, t1.month, t1.quantity , t1.amount, t2.produc t_name
FROM sales . sales_info2 t1 JOIN sales .product t2
ON t1.product = t2.product_no
标量子查询
- 标量子查询的含义
标量子查询是指返回结果只有一行且只有一列的子查询。
- 标量子查询的使用范围
DECODE 和CASE 的条件和表达式部分。
SELECT语句中除了GROUP BY之外的所有子句
UPDATE语句中的SET 子句和WHERE子句
-
标量子查询示例
-
SQL> SELECT sysdate, (SELECT 123 FROM DUAL ) FROM DUAL ; SYSDATE (SELECT123FROMDUAL ) - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 2023-07-06 123 -- 如果标量子查询的位置返回多行，崖山数据库会显示存在语法错误： -- YAS-04305 one column expec ted -- 应修改语句，确认只返回一行一列。相关子查询
- 相关子查询的含义
当子查询引用父语句所引用表的一列时，崖山数据库会执行相关子查询。
相关子查询会在父语句每处理一行而计算一次。
- 相关子查询的执行过程
由外部查询提取，获得一个候选行。
使用候选行的具体值执行内部查询。
使用内部查询的结果确定候选行是否符合要求。
重复执行操作，完成所有候选行的提取比较。
相关子查询示例
SQL高级语法-子查询
CREATE USER sales IDENTIFIED BY sales ;
CREATE TABLE sales.t3( c1 INT);
INSERT INTO sales .t3 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SELECT cmp_t.c1
FROM sales .t3 cmp_t
WHERE c1 = (SELECT trunc (10 * random()) res FROM dual WHERE cmp_t. c1 > 0);
-- 注意，每次执行的结果是不一样的，说明：比较每一行的时候都重新生产了随机值。
-- 如果不使用别名cmp_ t，将报错：YAS-04243 inval id identifier "SALES" . "T3" . "C1"
子查询与表连接的执行性能对比
l 假设某个饭店，它每天都推出一款特价菜，有一张表menu_1，记录了从今天到未来的特价菜列表，记录约2万行。
l 如何求出每天的时间序号，当天的特价菜菜名，以及和十天之后的特价菜菜名。
l 可以使用相关子查询或者表连接来实现。（两条SQL语句语义相同）
CREATE SEQUENCE seq_menu1;
CREATE TABLE menu_1(id number DEFAULT
seq_menu1.next val ,name varchar(200));
-- 插入数据（参考配套代码）
-- 通过示例数据，验证对应关系
SET timing ON
-- 使用相关子查询
SELECT id,name,(SELECT name FROM menu_1 WHERE menu_1.id = s1.id + 10) AS aft10d FROM menu_1 s1 ORDER BY id ASC;
-- 使用表连接
SELECT s1.id, s1.name, s2.name AS aft10d FROM menu_1 s1 LEFT JOIN menu_1 s2 ON s1.id = s2.id - 10 ORDER BY s1.id ASC;
- 子查询与表连接的执行性能对比
后者性能为前者性能的739倍至880倍。（使用表连接的方式比相关子查询的方式快很多）
且性能差异随表行数变大而扩大。
用例中仅构造了2万行测试数据，真实生产环境中的性能差异可能更大。
正则表达式函数
- 正则表达式函数概述
正则表达式是对字符串操作的一种逻辑公式，在数据库中，除了使用like做一些比较简单的匹配外，还可以使用正则表达式函数来完成更加复杂的匹配需求。
崖山数据库中，正则表达式函数有REGEXP_LIKE ，REGEXP _COUNT ， REGEXP_INSTR ， REGEXP_REPLACE ，REGEXP_SUBSTR
正则表达式函数的作用主要是，判断特定的字符串是否符合某种模式。
常见正则匹配
1，^匹配字符串的开始，$匹配字符串的结尾，. 匹配任意单个字符
2，*：匹配前面的模式零次或多次。
3，+：匹配前面的模式一次或多次。
4，[ ]：匹配括号内的任意一个字符。例如，[abc ] 匹配字符 “a”、“b” 或 " c "。
5，[^ ]：匹配除了括号内的字符以外的任意一个字符。例如，[^abc ] 匹配除了字符 “a”、“b” 或 " c " 以外的任意字符。
REGEXP_LIKE
REGEXP_LIKE为正则表达式函数，其功能与LIKE语法相似，但与LIKE只能使用’%‘和’_'通配符相比，REGEXP_LIKE函数的regexp参数可使用正则表达式，对expr表示的字符串进行正则匹配，匹配成功时返回TRUE，否则返回FALSE。
REGEXP_LIKE语法
regexp_like(expr,regexp[,match_para]) -- expr，传入进行匹配的串，如为NULL，则返回NULL
-- regexp，正则表达式规则
-- match ，匹配模式，取值范围见右图
‘i’ 大小写不敏感。 ‘c’ 大小写敏感，默认为此值。 ‘n’ 允许句点（.）匹配任何字符，包括换行符。如果省略此参数，则句点与换行符不匹配。 ‘m’ 将字符串视为多行，将^和$分别解释为字符串中任意行的开始和结束，而不仅仅是整个字符串的开始或结束。如果省略此参数，则将字符串视为一行。 ‘x’ 忽略空白和#注释。默认情况下，空白字符与其自身匹配
SELECT REGEXP_LIKE('aa\naa' , '^aa$' , 'i') reg1, REGEXP_LIKE('aa' , '^aa$' , 'i') reg2 FROM DUAL; -- false,true ----'^aa$' , 'i' 可以匹配Aa/aa/AA/aA
SELECT REGEXP_LIKE('AA' , 'A.A') reg1, REGEXP_LIKE('AA' , 'A.A' , 'n') reg2 FROM DUAL ; -- false,false
SELECT REGEXP_LIKE('AA' , 'A.A') reg1, REGEXP_LIKE('AbA' , 'A.A' , 'n') reg2 FROM DUAL ; -- false,true
SELECT REGEXP_LIKE('-614196584' , '^-[0-9]*[1-9][0-9]*$') reg1, REGEXP_LIKE('654196584' , '^[0-9]*[1-9][0-9]*$','m') reg2 FROM DUAL ;
-- true,true
REGEXP_COUNT
Ø REGEXP_COUNT为正则表达式函数，在expr表示的源字符串中按正则模式可以匹配到的次数，如果没有找到匹配项，则函数返回0。
Ø 匹配规则：在找到第一个正则匹配项后，继续从匹配项之后的第一个字符开始，查找第二个匹配项，直到expr的最后一个字符。
regexp_ count(expr,regexp[,pos it[,match_para]])
- - expr，传入进行匹配的串，如为NULL，则返回NULL
- - regexp，正则表达式规则
- - pos it ，匹配开始的位置，默认为1，从开始进行匹配
- - match_para，匹配模式，取值范围见REGEXP_ LIKE
SELECT REGEXP_COUNT('123123123123123' , '123' , 4, 'i') REGEXP_COUNT FROM DUAL ; --4 -- 从第4个字段开始，i表示忽略大小写
SELECT REGEXP_COUNT('A1B2C3' , '[A-Z][0-9]') REGEXP_COUNT FROM DUAL ; --3 -- 默认从第1个开始匹配，配置3个字母加数字的
REGEXP_INSTR
REGEXP_INSTR函数作用，按照正则规则在目标字符串中搜索能匹配到的子串，并返回子串位置相关信息。相当于是INSTR函数的正则版本。
REGEXP_INSTR语法
REGEXP_ INSTR ( expr , regexp [, pos ition[, oc currence[, return_opt[, match_para[, subexpr]]]]] )
- - expr，传入进行匹配的串，如为NUL L，则返回NUL L
- - regexp，正则表达式规则
- - position，匹配开始的位置，默认为1，从开始进行匹配
- - occurrence，匹配到多少次然后返回，默认为1
- - return_opt， 如果指定0，将返回匹配项的第一个字符的位置；如果指定1，将返回匹配项后第一个字符的位置。
- - match_para，匹配模式，取值范围见REGEXP_ LIKE
- - subexpr，指定使用第subexpr个子表达式
子表达式的顺序确定规则
以 0123(((abc )(de)f )ghi)45(678) 进行讲解。
如何找第一个子表达式？从左到右，先找正括号符号( ，然后找它的匹配的反括号，之间的部分就是它的第一个子表达式。
所以可以知道，abcdefghi 是它的第一个子表达式。
同理确认，第2到第5的子表达式分别为“abcdef”、“abc”、“de”和“678”
SELECT REGEXP_INSTR('1234567890' , '(123)(4(56)(78))' , 1, 1, 0, 'i' , 2) REGEXP_ISTR FROM DUAL ; --4 -- 从第2个子表达式开始（4(56)(78) --45678），从表达式的第1个字符开始，匹配到第1次时，进行返回，0表示从第一个返回，i表示忽略大小写。如果0改成1，返回的是9
SELECT REGEXP_INSTR('1234567890' , '(123)(4(56)(78))' , 1, 1, 0, 'i' , 3) REGEXP_ISTR FROM DUAL ; --5
SELECT REGEXP_INSTR('1234567890' , '[0-9]' , 1, 5)REGEXP_INSTR FROM DUAL ; --5 -- 从第1个字符开始，匹配到第5次，返回。
REGEXP_INSTR(expr, regexp [, position[, occurrence[, return_opt[,
match_para[, subexpr]]]]] )
– expr，传入进行匹配的串，如为NULL，则返回NULL
–regexp，正则表达式规则
–position，匹配开始的位置，默认为1，从开始进行匹配
– occurrence，匹配到多少次然后返回，默认为1
– return_opt， 如果指定0，将返回匹配项的第一个字符的位置；如果指定1，将返回匹配项后第一个字符的位置。
–match_para，匹配模式，取值范围见REGEXP_LIKE
–subexpr，指定使用第subexpr个子表达式
REGEXP_REPLACE
Ø REGEXP_REPLACE函数作用，按照正则规则在目标字符串中替换能匹配到的子串。
Ø REGEXP_REPLACE语法
regexp_ replace: := REGEXP_REPL ACE ( expr , regexp [, replace[, pos ition[, oc currence[, match_para]]]] )
- - expr，传入进行匹配的串，如为NUL L，则返回NUL L
- - regexp，正则表达式规则
- - replace，用于替代匹配上字符串的替代串
- - pos ition，匹配开始的位置，默认为1，从开始进行匹配
- - oc currence，匹配到多少次然后返回，默认为1
- - match_para，匹配模式，取值范围见REGEXP_ L IKE
SELECT REGEXP_REPLACE('1234567890' , '456' , '!' , 1, 1, 'i') REGEXP_REPLACE FROM DUAL ; --123!7890 --从第一个匹配上的开始，替换第1个字符
SELECT REGEXP_REPLACE('1234567456890' , '456' , '!' , 1, 2, 'i') REGEXP_REPLACE FROM DUAL ; --1234567!890 --从第一个匹配上的开始，替换第2个字符
regexp_replace::= REGEXP_REPLACE(expr, regexp [, replace[, position[, occurrence[, match_para]]]] )
–expr，传入进行匹配的串，如为NULL，则返回NULL
–regexp，正则表达式规则
–replace，用于替代匹配上字符串的替代串
–position，匹配开始的位置，默认为1，从开始进行匹配
–occurrence，匹配到多少次然后返回，默认为1
–match_para，匹配模式，取值范围见REGEXP_LIKE
SELECT REGEXP_REPLACE('1234567890' , '[0-9]' , '!' , 1, 5) REGEXP_REPLACE FROM DUAL ; --1234!67890
REGEXP_SUBSTR
REGEXP_SUBSTR函数作用，功能与参数定义均类似于REGEXP_INSTR，但它不返回子字符串的位置，而是返
回子字符串本身。如果需要匹配字符串的内容，但不需要其在源字符串中的位置，则此函数非常有用。
REGEXP_SUBSTR语法
regexp_ subs tr: := REGEXP_SUBSTR ( expr , regexp [, pos ition[, oc currence[, match_para[, subexpr]]]] )
- - expr，传入进行匹配的串，如为NULL，则返回NULL
- - regexp，正则表达式规则
- - pos ition，匹配开始的位置，默认为1，从开始进行匹配
- - occurrence，匹配到多少次然后返回，默认为1
- - match_para，匹配模式，取值范围见REGEXP_ LIKE
- - subexpr，指定使用第subexpr个子表达式
SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))' , 1, 1, 'i' , 2) REGEXP_SUBSTR FROM DUAL ; --45678 --从第2个字表达式开始匹配（45678），从第一个开始，匹配到第一个的时候，进行返回，i是忽略大小写。
SELECT REGEXP_SUBSTR('1234567890' , '[0-9]' , 1, 5)REGEXP_SUBSTR FROM DUAL ; --5
SQL高级语法-处理大型数据集
DEFAULT的使用
使用方法
插入某列时指定default，如果该列有默认值，则使用默认值，如果没有默认值则为null。注意：该列如果有非空约束，插入null将报错
CREATE TABLE ex_default( c1 int DEFAULT 1688);
INSERT INTO ex_default VALUES (default);
SELECT * FROM ex_default; --1688
使用SQL复制表数据
CTAS方法（CREATE TABLE t1 AS SELECT * FROM t2）
如果要创建某张表的备份表，可以使用CTAS方法创建备份，并可以通过指定列名和where子句备份指定数据。
CTAS方法不会复制源表上的约束，索引，触发器等对象。
如果需要复制一张表结构相同的空表，可以使用CREATE TABLE t1 AS SELECT * FROM t2 WHERE 1=2;
INSERT INTO SELECT 方法（INSERT INTO t1 SELECT * FROM t2）
可以使用INSERT INTO方法将备份数据插入已有数据表。
CREATE TABLE t1 AS SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t2;
多表INSERT语句
- 多表INSERT语句的类型
无条件insert all：对于子查询返回的每一行，在每一个目标表中插入一行。
条件insert all：对于子查询返回的每一行，在满足指定条件的每一个目标表中插入一行。
条件insert first：对于子查询返回的每一行，在满足条件的第一个目标表中插入一行。
- 多表INSERT的作用
在数据仓库环境下定期加载数据，执行数据转换。
- 无条件insert all语句
对于子查询返回的每一行，在每一个目标表中插入一行。
无条件insert all语句示例
INSERT
ALL
INTO t1 VALUES( c1, c2, c3)
INTO t2 VALUES( c1, c2, c3)
SELECT c1, c2, c3
FROM tz
WHERE cn < 100;
- - 从tz中，根据需要的条件（也可以省略条件），选择适当的列，并将这些数据分别全部插入到t1，t2表。
- - t1，t2表需要哪些数据列可以灵活定义或者根据需求进行函数转换。
- 条件insert all语句
对于子查询返回的每一行，根据满足条件，在目标表中进行插入。
条件insert all语句示例
-- 条件insert all (如果同时满足2020年前入职和等级非空，那么会同时插入t1，和t2)
INSERT
ALL
WHEN c2<'2020-01-01' THEN
INTO t1 VALUES( c1, c2, c3)
WHEN c5 IS NOT NULL THEN
INTO t2 VALUES( c1, c2, c3)
SELECT c1, c2, c3 FROM tz ;
- 条件insert first语句
对于子查询返回的每一行，根据设置条件，在目标表中进行插入。
条件insert first语句示例
-- 条件insert first (如果同时满足2020年前入职和等级非空，那么只会插入t1)
INSERT
FIRST
WHEN c2<'2020-01-01' THEN
INTO t1 VALUES( c1, c2, c3)
WHEN c5 IS NOT NULL THEN
INTO t2 VALUES( c1, c2, c3)
ELSE
INTO t3 VALUES( c1, c2, c3)
SELECT c1, c2, c3
FROM tz ;
- - 注意，条件insert all也可以使用else
MERGE语句
MERGE语句的作用
MERGE语句整合了多步的SELECT/UPDATE/INSERT/DELETE操作，使用一次该语句即可实现根据条件
（Condition）把源（Source）对象记录整合到目标（Target）对象的功能。
MERGE语句示例
CREATE TABLE hefei.emp_merge_target(id int,name varchar2(30),hiredate DATE,gender char(1), country varchar2(30));
CREATE TABLE hefei.emp_merge_source(id int,name varchar2(30),hiredate DATE,gender char(1), country varchar2(30));
ALTER TABLE hefei.emp_merge_target ADD (CONSTRAINT emp_pk PRIMARY KEY(id));
INSERT INTO hefei.emp_merge_target VALUES (1, 'Zhao' , to_date('2015-03-02' , 'yyyy-mm-dd'), 'F' , 'China');
INSERT INTO hefei.emp_merge_target VALUES (2, '张三' , to_date('2019-03-02' , 'yyyy-mm-dd'), 'F' , 'China');
INSERT INTO hefei.emp_merge_target VALUES (3, 'King' , to_date('2022-11-22' , 'yyyy-mm-dd'), '' , 'USA');
INSERT INTO hefei.emp_merge_source VALUES (2, '张三' , to_date('2018-03-02' , 'yyyy-mm-dd'), 'M' , 'China');
INSERT INTO hefei.emp_merge_source VALUES (3, 'King' , to_date('2021-11-22' , 'yyyy-mm-dd'), 'M' , 'China');
INSERT INTO hefei.emp_merge_source VALUES (4, 'Anna' , to_date('2019-05-02' , 'yyyy-mm-dd'), 'M' , 'USA');
MERGE语句示例
MERGE INTO hefei.emp_merge_target t
USING (SELECT * FROM hefei.emp_merge_source) s
ON ( s.id = t.id)
WHEN MATCHED THEN
UPDATE SET
t.name = s.name,
t.hiredate = s.hiredate,
t.gender = s.gender,
t. country = s.country
DELETE
WHERE (t.gender IS NULL ) WHEN NOT MATCHED THEN
INSERT VALUES ( s.id, s.name, s.hiredate, s.gender, s.country );
– 把表1的数据和表2的数据合并，删除了表1中的空值，然后插入表2中的差异数据。
WITH 子句
- WITH 子句介绍
在复杂查询中，如果一个查询块多次出现，那么可以使用WITH子句来定义该查询块来重复使用同一查询块。
崖山数据库会将这个查询块的结果保存在用户临时表空间中，这样可以提升查询性能
- 使用WITH 子句的优点
使得查询更加简洁，提升可读性
只执行查询块一次，并在需要时复用查询结果
可以明显提升大型查询性能
- WITH 子句示例
WITH a(c1) AS
(SELECT sysdate FROM dual),
b(c2) AS
(SELECT 123 FROM dual)
SELECT a.c1, b.c2
FROM a,b;
内置函数
函数概念
函数用来操作数据条目，并返回结果。
函数和运算符不同，函数可以接收更灵活的参数类型。
按照输出结果对函数进行分类
函数可以分为单行函数和多行函数。
单行函数每处理一行数据，返回一个结果。比如to_date()函数，可以将多个字符串对应转换为时间类型
多行函数接受一批数据，返回一个结果。比如SUM()函数，可以计算特定列的和。多行函数也称聚集函数。
按照功能还可以将函数主要分为以下类型（见下表）
转换函数
转换函数示例
SQL> SELECT LOWER('China'), UPPER('China') FROM DUAL ;
china CHINA
SQL> SELECT LENGTH('China'), BIT_LENGTH('China') FROM DUAL ;
5 40
-转换函数
SQL> SELECT CONCAT('China','Shenzhen'), CONCAT_WS('-' , 'China' , 'Shenzhen') FROM DUAL ;
ChinaShenzhen China-Shenzhen
SQL> SELECT * FROM t5;
1
2
3
SQL> SELECT STRING_AGG( c1, ' ') FROM t5; -- 列转行
123
SQL> SELECT LEFT ('12345' , 3), RIGHT ('12345' , 3) FROM dual ;
123 345
SQL> SELECT SPLIT('12345' , '3' , 2) FROM dual ;
45
聚集函数
SELECT AVG(fee_total),COUNT(fee_total),MAX(fee_total),MIN(fee_total),SUM(fee_total)FROM SALES.FINANCE_INFO;
SELECT WM_CONCAT(fee_total)FROM SALES.FINANCE_INFO;
窗口函数
CREATE TABLE employees (DEPARTMENT_ID int, LAST_NAME varchar2(30),SALARY int);
INSERT INTO employees VALUES (20, 'Lorentz' , 4300);
INSERT INTO employees VALUES (20, 'Neo' , 4400);
INSERT INTO employees VALUES (30, 'Aus tin' , 4900);
INSERT INTO employees VALUES (30, 'Pataballa' , 4900);
INSERT INTO employees VALUES (60, 'Ernst' , 6000);
INSERT INTO employees VALUES (60, 'Hunold' , 9000);
COMMIT;
SELECT
department_id,
LAST_name,
salary ,
ROW_NUMBER() OVER (PARTITION
BY department_id
ORDER BY
salary ) ROW_NUMBER
FROM
employees
SELECT
department_id,
LAST_name,
salary ,
ROW_NUMBER() OVER (PARTITION
BY department_id
ORDER BY
salary ) ROW_NUMBER
FROM
employees
ORDER BY ROW_NUMBER, last_name;
SELECT
department_id,
LAST_name,
salary ,
ROW_NUMBER() OVER (PARTITION
BY 1
ORDER BY
salary ) ROW_NUMBER
FROM
employees
ORDER BY ROW_NUMBER, last_name;
SELECT department_id, last_name, salary , RANK() OVER (PARTITION BY 1 ORDER BY salary ) R1 FROM employees ;
SELECT department_id, last_name, salary , DENSE_RANK() OVER (PARTITION BY 1 ORDER BY salary ) R2 FROM employees ;
SELECT department_id, last_name, salary , FIRST_VALUE( salary ) OVER (PARTITION BY DEPARTMENT_ID ORDER BY salary DESC) FIRST_VALUE FROM employees ;
SELECT department_id, last_name, salary , LAST_VALUE( salary ) OVER (PARTITION BY DEPARTMENT_ID ORDER BY salary DESC) LAST_VALUE FROM employees ;
SELECT
department_id,
last_name,
salary ,
LAST_VALUE(salary) OVER (PARTITION BY DEPARTMENT_ID
ORDER BY
salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LAST_VALUE
FROM
employees ;
除了普通的窗口函数，聚集函数也能作为窗口函数使用，通过指定OVER子句来进行识别，如SUM(),COUNT()。
l当聚集函数作为窗口函数使用时，它可以在窗口子句指定的窗口范围，起到聚合数据的作用。它可以根据位置的不同，选择不同的数据聚集范围；普通聚集函数不能做到这一点。
- - 从产品历史销量表中求出产品的历史累计销量
SELECT day_num, prod2, SUM(prod2) over(ORDER BY day_num ASC ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
FROM sales_amount_by_days limit 5;
窗口函数和子查询的性能对比
l现有一张每日销售量的记录表，它记录了一个销售门店在过去一万天的每天销售数据。
表中包含：天数的序号，产品一的当天销量，产品二的当天销量。
需要求出：这个销售门店过去每天的产品二的累计销量表。（我们生成前一千天和一万天的累计销量表进行性能对比）
我们可以使用窗口函数或者子查询来实现（两条SQL语句语义相同）。
CREATE TABLE sales_amount_by_days (day_num number,prod1 number,prod2 number);
DECLARE BEGIN FOR i IN 1..10000 loop INSERT INTO sales_amount_by_days VALUES(i,trunc(1000*random()),trunc(1000*random())); END loop; END;
-- 窗口函数
SELECT day_num, SUM(prod2) over(ORDER BY
day_num ASC ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW)
FROM sales_amount_by_days ;
-- 子查询
SELECT day_num, (SELECT SUM(prod2) FROM
sales_amount_by_days b WHERE b.day_num
<= a.day_num)
FROM sales_amount_by_days a;
窗口函数更快，性能为子查询的约50倍至500倍。
且性能差异随表行数变大而扩大。
用例中仅使用了1万行测试数据，真实生产环境中的性能差异可能更大。
表函数
SQL> SELECT SID, SQL_ID, THREAD_ID, STAGE_ID, TYPE FROM TABLE(PX_O BJ(131094)); --131094是会话id
131094 7x36y59y3gkwu 7404 -1 receiver
131094 7x36y59y3gkwu 7404 -1 receiver
131094 7x36y59y3gkwu 7391 0 sender
131094 7x36y59y3gkwu 7391 0 sender
SQL> SELECT SID, SQL_ID, STAGE_ID, TQ_ID, TYPE, IS_LOCAL FROM TABLE(PX_CHANNEL (131091));
131091 6w7vgtqwc018p -1 0 READER N
131091 6w7vgtqwc018p -1 0 READER N
131091 6w7vgtqwc018p -1 0 READER N
131091 6w7vgtqwc018p -1 0 READER N
字符函数
SQL> SELECT REPLACE('ShenZhen' , 'Zhen' , 'Yang') REPLACE FROM DUAL ;
ShenYang
SQL> SELECT POSITION('a' IN 'China') FROM DUAL ;
5
SQL> SELECT INITCAP('my work language is 中文') res FROM DUAL ; -- 首字母大写
My Work Language Is 中文
日期函数
SQL> SELECT ADD_MONTHS(to_date('2000-01-01' , ' yyyy-mm-dd'), 20), ADD_MO NTHS(to_date('2000-01-01' , ' yyyy-mm-dd'), -20) FROM DUAL ;
2001-09-01 1998-05-01
SQL> SELECT age(to_date('2000-01-01','yyyy-mm-dd')), age(to_date('2000-01-01','yyyy-mm-dd'), to_date('2010-01-01', 'yyyy-mm-dd')) FROM DUAL ;
+23-06 -10-00
SQL> SELECT CURRENT_TIMESTAMP() res FROM DUAL ; -- 查询当前时间戳
2023-07-07 07:36:57.066764
SQL> SELECT DATE ( 'January 15, 1989, 11:00 A.M.' , 'Month dd, YY, HH:MI A.M.' ), DATE ('1989/2/23' , 'YYYY/MM/DD HH24:MI:SS' ) res FROM DUAL ; -- 将字符串转换为时间类型
1989-01-15 1989-02-23
SQL> SELECT L AST_DAY(DATE '2022-7-26' + 20) res FROM DUAL ; -- 日期所在月份的最后一天
2022-08-31
SQL> SELECT DAYO FWEEK(DATE '2022-7-26') res FROM DUAL ; -- 返回日期是所在周的第几天（周日为第一天）
3
SQL> SELECT sysdate FROM DUAL ;
2023-07-07
其他函数
SQL> SELECT isnull(' '), isnull('a') FROM DUAL ; -- 是否为空，返回true或者fal se
true false
SQL> SELECT ifnull(' ' , 'a'), ifnull('a' , 'b'), ifnull(' ' , ' ') FROM DUAL ; -- 如果第一个参数为空，则返回第二个参数；否则返回第一个参数。
a a
SQL>SELECT GREATEST(2,3,5,1,7),LEAST(2,3,5,1,7)FROM DUAL; -- 返回最大的值，返回最小的值
7 1
SQL>SELECT nvl('a','b'),nvl('','b'),NVL('','')FROM DUAL; -- 返回首个非空值，都为空则返回null。
a b
SQL>SELECT nvl2('a','b','c'),NVL2('','b','c')FROM DUAL; -- 参数1非空，则返回参数2的值，如参数1为空，则返回参数3的值。
b c
课后作业
问答题：请编写一个SQL，它可以用来验证c1是否为2的非负整数次幂。
（2的非负整数次幂的序列为：1，2，4，8，16，32，64，128，256，512，1024，等）
测试环境构造：
drop table t1；
create table t1（c1 number）；
insert into t1 values（0.5），（1），（2），（3），（5），（8），（-16），（32），（-88.8），（128），（712），（1024）；
select c1 from t1；
期望的返回结果：
C1是（2的m次幂）
或者
C1不是（2的非负整数次幂）
SELECT c1,CASE WHEN log(2,c1)>=0 AND MOD(log(2,c1),1)=0 THEN '是' ELSE '不是' END AS RESULT FROM t1;
drop table emp1;
create table emp1(dept varchar(50), emp_name varchar(50), exam_name varchar(50), score number)
insert into emp1 values
('业务一部','King','English','99'),
('业务一部','Tom','English','50'),
('业务一部','Zhang','English','63'),
('业务一部','King','Math','46'),
('业务一部','Tom','Math','88'),
('业务一部','Zhang','Math','79'),
('业务二部','Wang','English','38'),
('业务二部','Lorentz','English','59'),
('业务二部','Neo','English','67'),
('业务二部','Wang','Math','96'),
('业务二部','Lorentz','Math','70'),
('业务二部','Neo','Math','89'),
('业务三部','Austin','English','97'),
('业务三部','Pataballa','English','64'),
('业务三部','Hunold','English','67'),
('业务三部','Austin','Math','70'),
('业务三部','Pataballa','Math','69'),
('业务三部','Hunold','Math','93');
请给出以下sql，
返回每个部门，总分最高的人员和他的总分。
with t1
as
(SELECT DISTINCT dept,emp_name,sum(score) OVER (PARTITION BY dept,emp_name) AS ss FROM emp1),
t2
AS
(SELECT dept,max(ss) ms FROM t1 GROUP BY dept )
SELECT t1.dept,t1.emp_name,t2.ms FROM t1,t2 WHERE
t1.dept=t2.dept AND t1.ss=t2.ms;
返回每个部门，数学分数比本部门数学平均分高的人员和他的分数。
方法一：
WITH t1
AS
(SELECT DISTINCT dept,avg(score) OVER (PARTITION BY dept) AS avgs FROM emp1 e WHERE exam_name = 'Math'),
t2
AS
(SELECT dept,emp_name,score FROM emp1 WHERE exam_name = 'Math')
SELECT t2.dept,t2.emp_name,t2.score FROM t1,t2
WHERE t1.dept=t2.dept AND t1.avgs<t2.score
方法二：
SELECT dept,emp_name,score FROM
(SELECT DISTINCT dept,emp_name,score,AVG(score) OVER (PARTITION BY dept) AS avgs FROM
emp1 e WHERE exam_name ='Math')
WHERE score>avgs;
方法三：
SELECT a.dept,a.emp_name,a.score FROM
(SELECT dept,emp_name,score FROM emp1 WHERE exam_name= 'Math') a ,
(SELECT dept,avg(score) AS avgs FROM emp1 e WHERE exam_name ='Math' GROUP BY dept) b
WHERE a.dept=b.dept AND a.score>b.avgs;
方法四：
SELECT dept,emp_name,score FROM emp1 e1 WHERE exam_name ='Math' AND score>
(SELECT avg(score) FROM emp1 e WHERE exam_name ='Math' AND e.dept=e1.dept)
返回每个部门，总分最高分的人员，以及他比总分第二高的人员高出了多少分数
WITH t1
AS
(SELECT DISTINCT dept,emp_name,SUM(score) OVER (PARTITION BY dept,emp_name) AS ss FROM emp1),
t2
AS
(SELECT dept,emp_name,RANK() OVER(PARTITION BY dept ORDER BY ss DESC) AS rank_num,ss,lead(ss,1,0) OVER (PARTITION by dept ORDER BY ss DESC ) AS next_score FROM t1)
SELECT t2.dept,t2.emp_name,t2.ss,t2.ss-t2.next_score AS score_diff FROM t2 WHERE t2.rank_num=1;
方法二：
SELECT t2.dept,t2.emp_name,t2.ss,t2.ss-t2.next_score AS score_diff FROM
(SELECT dept,emp_name,RANK() OVER(PARTITION BY dept ORDER BY ss DESC) AS rank_num,ss,lead(ss,1,0) OVER (PARTITION by dept ORDER BY ss DESC ) AS next_score FROM
(SELECT DISTINCT dept,emp_name,SUM(score) OVER (PARTITION BY dept,emp_name) AS ss FROM emp1 )) t2 WHERE t2.rank_num=1;
编写一个查询sql，返回所有的子节点，它的父节点，以及父节点对应的名称。
（子节点和父节点都是相对的概念。从表数据中可以看到，计算机技术分类的编号为1，它的子节点包含101，1
它的子节点包含101，102，103，104；而编程语言分类编号为104，它的子节点包含10401，10402，10403
，10401，10402，10403，10404。从表中也可以看到，对于某个节点的父节点，其实就是该行的parent id列的值）
create table book_catagories(
catagory_id number(20) not null, catagory_str varchar2(30) not NULL,parent_id number(20));
insert into book_catagories values (1,'计算机技术',null),(101,'数据库设计',1),(102,'SQL开发优化',1),(103,'NewSQL',1),(104,'编程语言',1),(10401,'PHP',104),(10402,'C语言',104),(10403,'Rust',104),(10404,'Java',104);
SELECT
t2.catagory_id AS child_id,
t1.catagory_id AS parent_id,
t1.catagory_str AS parent_name
FROM
BOOK_CATAGORIES t1
JOIN BOOK_CATAGORIES t2 ON
t1.catagory_id = t2.PARENT_ID ;
