ORACLE SQL
包括字符串函数UPPER、LOWER、INITCAP、CONCAT、SUBSTR、LENGTH、TRIM、LTRIM、RTRIM、LPAD、RPAD、INSTR；数值类型及函数ROUND、TRUNC、MOD、CEIL、FLOOR；日期函数MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAT、LAST_DAY；转换函数TO_NUMBER、TO_CHAR、TO_DATE；聚合函数；DECODE函数；排序函数ROW_NUMBER、RANK、DENSE_RANK；高分组函数ROLLUP、CUBE、GROUPING SETS。
一、字符串函数
| 函数 | 作用 |
|---|---|
| LOWER | 小写函数 |
| UPPER | 大写函数 |
| INITCAP | 首字符大写函数 |
| CONCAT | 拼接函数 |
| SUBSTR | 求子串函数 |
| LENGTH | 字符串的长度 |
| LPAD | 右对齐函数，位数不够的，左边补指定字符 |
| RPAD | 左对齐函数，位数不够的，右边补指定字符 |
1、UPPER、LOWER和INITCAP
（1）、大小写转换函数，用来转换字符的大小写
（2）、UPPER(char)用来将字符转换为大写形式
（3）、LOWER(char)用来将字符转换为小写形式
（4）、INITCAP(cahr)用来将字符串中每个单词的首字符大写，其它字符小写，单词之间用空格分隔，分隔后每个单词首字母都会大写
（5）、如果输入的参数是NULL值，仍然返回NULL值
-- 伪表：dual
-- 当查询的内容不和任何表中数据有关系时，可以使用伪表，伪表只会查询出一条记录
SELECT UPPER('helloword'),LOWER('HELLOWORLD'),INITCAP('HELLO WORLD') FROM dual;
2、CONCAT
（1）、CONCAT()函数用来连接字符串
（2）、拼接运算符||：字符表达式意为字符串的拼接，||可以将某几列或某列与字符串拼接在一起
SELECT CONCAT(NAME,SALARY) FROM myemployee_liu;
SELECT CONCAT(CONCAT(NAME,','),SALARY) FROM myemployee_liu;
SELECT NAME||','||SALARY FROM myemployee_liu;
3、SUBSTR
（1）、SUBSTR(char, m[,n])用于获取字符串的子串，返回char中从m位开始取n个字符
（2）、如果m=0，则从首字符开始，如果m取负数，则从尾部开始
（3）、如果没有设置n，或者n的长度超过了char的长度，则取到字符串尾为止
（4）、字符串的首位计数从1开始（下标从1开始）
-- SUBSTR截取字符串
-- 数据库中的下标都是从1开始的
SELECT SUBSTR('thinking in java', 13, 4) FROM dual;
-- 第三个参数不指定则是截取到末尾，指定的长度若超过实际可以截取的内容也是截取到末尾
SELECT SUBSTR('thinking in java', 10) FROM dual;
-- 截取的位置可以是负数，若是则表示从倒数第几个字符开始截取
SELECT SUBSTR('thinking in java', -4,4) FROM dual;
4、LENGTH
（1）、LENGTH(char)用于返回字符串的长度
（2）、如果字符类型是VARCHAR2，返回字符的实际长度，如果字符类型是CHAR，长度还要包括后补的空格
SELECT NAME,LENGTH(NAME) FROM myemployee_liu;
5、TRIM、LTRIM、RTRIM
（1）、作用：截去子串
（2）、语法形式：
-
TRIM(c2 FROM c1)从c1的前后截去c2
-
LTRIM(c1[, c2])从c1的左边（Left）截去c2
-
RTRIM(c1[, c2])从c1的右边（Right）截去c2
-
如果没有c2，就去除空格
（3）、TRIM经常用来去掉字符串前后的空格
-- TRIM、LTRIM、RTRIM
-- 去除当前字符串中两边的指定重复字符，LTRIM仅去除左侧的，RTRIM则仅去除右侧的
SELECT TRIM('Q' FROM 'QQDFEWFEFQQQQ') FROM dual;
SELECT LTRIM('QMNQDFEWFEFQQNNMQMMNQ','MNQ') FROM dual;
6、LPAD、RPAD
补位函数，用于在字符串char1的左端用char2补足到n位，char2可重复多次
-
LPAD(char1, n, char2)左补位函数
-
RPAD(char1, n, char2)右补位函数
-- LPAD,RPAD补位函数
SELECT LPAD(SALARY,5,'$') FROM myemployee_liu;
**注：当字符串位数大于n时，只保留n位**
7、INSTR
（1）、INSTR(char1, char2[, n, m]])返回子串char2在源字符串char1中的位置
（2）、参数：
-
从n的位置开始搜索，没有指定n，从第1个字符开始搜索
-
m用于指定子串的第m次出现次数，如果不指定取值1
-
如果在char1中没有找到子串char2，返回0
-- INSTR(char1, char2[, n, m]])
-- 查找char2在char1中的位置
-- n为从第几个字符开始检索
-- m为第几次出现
-- n，m不写则默认都是1
SELECT INSTR('thinking in java', 'in', 3, 2) FROM dual;
注：如果找不到返回0，数据下标从1开始，0就代表没有
二、数值函数
1、NUMBER(X)表示整数
（1）、完整语法：NUMBER(precision, scale)
-
如果没有设置scale，则默认取值0，即NUMBER(X)表示整数
-
P表示数字的总位数，取值为1~38
（2）、用来表示存放如编码、年龄、次数等用整数记录的数据
2、NUMBER(P,S)表示浮点数
（1）、NUMBER(precision ,scale)
-
precsion：NUMBER可以存储的最大数字长度（不包括左右两边的0）
-
scale：在小数点右边的最大数字长度（包括左侧0）
（2）、指定了s但是没有指定p，则p默认为38，如：列名 NUMBER(*, s)
（3）、经常用来表示存放金额、成绩等有小数位的数据
（4）、NUMBER的变种数据类型：内部实现是NUMBER，可以将其理解为NUMBER的别名，目的是多种数据库及编程语言兼容
-
NUMBER(p,s)：完全映射至NUMBER(p,s)
-
DECIMAL(p,s)或DEC(p,s)：完全映射至NUMBER(p,s)
-
INTEGER或INT：完全映射至NUMBER(38)类型
-
SMALLINT：完全映射至NUMBER(38)类型
-
FLOAT(b)：映射至NUMBER类型
-
DOUBLE PRECISION：映射至NUMBER类型
-
REAL：映射至NUMBER类型
3、数值函数
ROUND
ROUND(n[, m])用于四舍五入
-
参数中的n可以是任何数字，值要被处理的数字
-
m必须是整数
-
m取正数则四舍五入到小数点后第m位
-
m取0值则四舍五入到整数位
-
m取负数，则四舍五入到小数点前m位
-
m缺省，默认值是0
-- ROUND(n,m)四舍五入
SELECT ROUND(45.923, 2) FROM dual;
SELECT ROUND(45.923, 0) FROM dual;
SELECT ROUND(45.923, -1) FROM dual;
| ROUND(45.923, 2) | 45.92 |
|---|---|
| ROUND(45.923, 0) | 46 |
| ROUND(45.923, -1) | 50 |
TRUNC
（1）、TRUNC(n[, m])用于截取
（2）、n和m的定义和ROUND(n[, m])相同，不同的是功能上按照截取的方式处理数字n
-- TRUNC(n,m)截取数字
SELECT TRUNC(45.923,2) FROM dual;
SELECT TRUNC(45.923) FROM dual;
SELECT TRUNC(45.923, -1) FROM dual;
| TRUNC(45.923, 2) | 45.92 |
|---|---|
| TRUNC(45.923, 0) | 45 |
| TRUNC(45.923, -1) | 40 |
MOD
MOD(m, n)返回m除以n以后的余数，n为0则直接返回m
-- MOD(m,n)求余数
SELECT name, salary, MOD(salary, 10) FROM myemployee_liu;
CEIL和FLOOR
（1）、CEIL(n)、FLOOR(n)这两个函数顾名思义，一个是天花板，就是取大于或等于n的最小整数值，一个是地板，就是取小于或等于n的最大整数值
（2）、比如数字n=4.5，那么它的CEIL是5，它的FLOOR是4
-- CEIL,FLOOR
-- 向上取整和向下取整
SELECT CEIL(45.654) FROM dual;
SELECT FLOOR(45.654) FROM dual;
三、日期函数
注：session语言为美语
1、MONTHS_BETWEEN
MONTHS_BETWEEN：两个日期之间相差多少个月
SELECT MONTHS_BETWEEN('01-SEP-95','11-JAN-94') FROM dual;
2、ADD_MONTHS
ADD_MONTHS：一个日期加减一个月
SELECT ADD_MONTHS('11-JAN-94',6) FROM dual;
3、NEXT_DAY
NEXT_DAY：根据参数，出现下一个的日期
SELECT ADD_MONTHS('11-JAN-94',6) FROM dual;
4、LAST_DAY
LAST_DAY：同一个月的最后一天
SELECT LAST_DAY('01-SEP-95') FROM dual;
四、转换函数
1、TO_NUMBER
TO_NUMBER将字符串转换成数值
2、TO_CHAR
SELECT TO_CHAR(colname, 'yyyy mm dd') FROM tabname;
格式说明：
-
第一个参数为要处理的日期，第二个参数为格式
-
格式必须用单引号括起来，并且大小写敏感
-
必须是有效的日期格式
-- 将myemployee_liu表中KING员工的birth以yyyy-mm-dd格式进行显示
SELECT TO_CHAR(birth,'yyyy-mm-dd') FROM myemployee_liu WHERE name='KING';
3、TO_CHAR（数值类型）
（1）、语法：
TO_CHAR(number,'$99,999.99')
（2）、格式说明：
-
9：代表数字位
-
0定义宽度大于实际值宽度时，0会被强制显示在前面，以补齐位数
-
$：美元的货币符号
-
L：本地货币符号
-
.：小数点
-
，：每千位显示一个逗号
SELECT TO_CHAR(salary,'$99,999.99') FROM myemployee_liu WHERE name='KING';
SELECT TO_CHAR(salary,'$00,000.99') FROM myemployee_liu WHERE name='KING';
SELECT TO_CHAR(salary,'L99,000.99') FROM myemployee_liu WHERE name='KING';
| 格式 | salary实际值为3400 |
|---|---|
| $99,999.99 | $3,400.00 |
| $00,000.99 | $03,400.00 |
| L99,000.99 | ￥3,400.00 |
注：如果显示位数不足（定义宽度小于实际值宽度），用#代替
4、TO_DATE
Session的概念
（1）、登录数据库：用client端程序与数据库建立连接，需要提供用户名和口令，需要有create session的权限
（2）、成功登录数据库的标志，通过client端程序获得一个session。session是逻辑上用户同服务器的通信交互，SQL语句的运行环境
（3）、从用户的直观上SQL是在session中运行
-- 改变session中的日期格式
ALTER SESSION SET NLS_DATE_FORMAT='2022 07 12 22:12:12';
-- 改变session中的语言；American美语、Simplified chinese简体中文
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
（4）、connection是物理上的客户机同服务器端的通信链路，即两个socket组成一个connection，是网络层面的描述
（5）、session是和connection同时建立的，两者是对同一件事情不同层次的描述，session是数据库层面的描述
（6）、两个socket分别对应client端应用进程和数据库服务器端的server process进程，从操作系统的角度看是进程，是真正的物理实现
（7）、session的物理实现：成功创建session的标志在Oracle server端创建一个server process进程。在session上运行的SQL语句由server process完成
（8）、用网络编程的方法实现数据中的session
日期格式
-
yyyy：用数字表达的四位年（2022年）
-
mm：用数字表达的二位月（07月）
-
dd：用数字表达的二位日（12日）
-
hh24：用水表达的24进行的小时（20点）
-
mi：用数字表达的分钟（30分）
-
ss：用数字表达的秒（30秒）
-
D：用数字表达的一周内的第几天（周日：1）
-
day：用全拼表达的星期几（sunday）
-
month：用全拼表达的月（march）
-
mon：用简拼表达的月（mar）
TO_DATE函数将字符串转换成一个日期值
--使用自定义日期格式插入记录
INSERT INTO myemployee_liu (id,name,job,birth) VALUES (2,'ersd','teacher',TO_DATE('2022-06-06','YYYY-MM-DD'));
五、聚合函数
（1）、查询时需要做一些数据统计，比如：查询员工表中各部门职员的平均薪水，各部门的员工人数
（2）、需要统计的数据并不能再员工表里直观列出，而是需要根据现有的数据计算得到结果
（3）、这种功能可以使用聚合函数来实现，即：将表里的全部数据划分为几组数据，每组数据统计出一个结果
（4）、因为是多行数据参与运算返回一行结果，也称为分组函数、多行函数、集合函数
（5）、聚合函数都忽略NULL值统计
-- MAX、MIN求给定字段的最大值与最小值
-- 查询公司的最高工资与最低工作是多少
SELECT MAX(salary),MIN(salary) FROM myemployee_liu;
-- AVG、SUM求平均值和总和
SELECT AVG(salary),SUM(salary) FROM myemployee_liu;
-- COUNT函数不是对给定的字段值进行统计，而是对给定字段不为NULL的记录数统计的
SELECT COUNT(salary) FROM myemployee_liu;
-- 通常查看表的记录数可以使用COUNT(*)
SELECT COUNT(*) FROM myemployee_liu;
-- 查看平均薪资(使用NVL将薪资为NULL的也算上)
SELECT AVG(NVL(salary,0)),SUM(salary) FROM myemployee_liu;
注：AVG和SUM只能针对NUMBER类型；COUNT和MAX和MIN可以针对任何类型
（6）、nvl函数：空值转换函数；参数的数据类型可以是数值number、字符character、日期date；参数的数据类型必须一致
六、DECODE函数
1、DECODE函数基本语法
DECODE(expr, search, result1[, search2, result2...][, default])
（1）、DECODE用于比较参数expr的值，如果匹配到哪一个sarch条件，就返回对应的result结果
（2）、可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值
（3）、default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL
-- 查询员工表，当职位分别为'MANAGER','ANALYST','SALESMAN'时，金额分别是薪水的1.2倍、1.1倍、1.05倍
SELECT name,job,DECODE(job,'MANAGER',salary*1.2,'ANALYST',salary*1.1,'SALESMAN',salary*1.05,salary) bonus FROM myemployee_liu;
（4）、和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作
-- CASE基本语法：
CASE WHEN condition THEN return_expr [WHEN condition THEN return_expr] ... ELSE else_expr END
（5）：CASE表达式：
-
寻找when的优先级：从上到下
-
再多的when，也只有一个出口，即其中有一个满足了expr就马上退出case
-
else_expr和return_expr的数据类型必须相同
-- 查询员工表，当职位分别为'MANAGER','ANALYST','SALESMAN'时，金额分别是薪水的1.2倍、1.1倍、1.05倍
SELECT name,job,salary,CASE job WHEN 'MANAGER' THEN salary*1.2 WHEN 'ANALYST' THEN salary*1.1 WHEN 'SALESMAN' THEN salary*1.05 ELSE salary END bonus FROM myemployee_liu;
2、DECODE函数在GROUP BY分组查询中的应用
按字段内容分组
场景：计算职位的人数，analyst/manager属于vip，其余普通员工operation，无法用GROUP BY简单实现
SELECT COUNT(*),DECODE(job,'MANAGER','VIP','ANALYST','VIP','OTHER') FROM myemployee_liu GROUP BY DECODE(job,'MANAGER','VIP','ANALYST','VIP','OTHER');
-- 排序，将‘OPERATIONS’排在第一位，‘ACCOUNTING’排在第二位，‘SALES’排在第四位
SELECT deptno,dname,loc FROM dept ORDER BY DECODE(dname,'OPERATIONS',1,'ACCOUNTING',2,'SALES',3);
七、排序函数
排序函数允许对结果集按照指定的字段分组，在组内再按照指定的字段排序，最终生成组内编号
1、ROW_NUMBER
ROW_NUMBER()函数生成组内连续且唯一的数字
-- 查看每个部门的工资排名
SELECT name,salary,deptno,ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY salary DESC) rank FROM myemployee_liu;
2、RANK
RANK函数，生成组内不连续也不唯一的数字，同组内排序字段值一样的记录，生成的数据也一样
-- 查看每个部门的工资排名
SELECT name,salary,deptno,RANK() OVER(PARTITION BY deptno ORDER BY salary DESC) rank FROM myemployee_liu;
3、DENSE_RANK
DENSE_RANK函数生成组内连续但不唯一的数字
-- 查看每个部门的工资排名
SELECT name,salary,deptno,DENSE_RANK() OVER(PARTITION BY deptno ORDER BY salary DESC) rank FROM myemployee_liu;
八、高级分组函数
ROLLUP、CUBE和GROUPING SETS运算符是GROUP BY子句的扩展，可以生产与使用UNION ALL来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询
-- 查看每天营业额
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY year_id,month_id,day_id ORDER BY year_id,month_id,day_id;
-- 查看每月营业额
SELECT year_id,month_id,SUM(sales_value) FROM sales_tab GROUP BY year_id,month_id ORDER BY year_id,month_id;
-- 查看每年营业额
SELECT year_id,SUM(sales_value) FROM sales_tab GROUP BY year_id ORDER BY year_id;
-- 查看总营业额
SELECT SUM(sales_value) FROM sales_tab;
-- 查看每天、每月、每年、总营业额
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY year_id,month_id,day_id UNION ALL SELECT year_id,month_id,NULL,SUM(sales_value) FROM sales_tab GROUP BY year_id,month_id UNION ALL SELECT year_id,SUM(sales_value) FROM sales_tab GROUP BY year_id UNION ALL SELECT NULL,NULL,NULL,SUM(sales_value) FROM sales_tab;
1、ROLLUP
ROLLUP()：分组原则，参数逐次递减，一直到所有参数都不要，每一种分组都统计一次结果并且并在一个结果集显示
GROUP BY ROLLUP(a,b,c)
等价于：
GROUP BY a,b,c
UNION ALL
GROUP BY a,b
UNION ALL
GROUP BY a
UNION ALL 全表
-- 查看每天、每月、每年、总营业额
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY ROLLUP(year_id,month_id,day_id);
2、CUBE
CUBE()：每种组合分一次组；分组次数：2的参数个数次方
GROUP BY CUBE(a,b,c)
等价于：
GROUP BY a,b,c
UNION ALL
GROUP BY a,b
UNION ALL
GROUP BY b,c
UNION ALL
GROUP BY a,c
UNION ALL
GROUP BY a
UNION ALL
GROUP BY b
UNION ALL
GROUP BY c
UNION ALL 全表
-- 查看每天、每月、每年、总营业额
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY CUBE(year_id,month_id,day_id) ORDER BY year_id,month_id,day_id;
3、GROUPING SETS
GROUPING SETS：每个参数是一种分组方式，然后将这些分组统计后并在一个结果集显示
-- 仅查看每天和每月的营业额
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY GROUPING SETS((year_id,month_id,day_id),(year_id,month_id));
注：括号中的看成一组
701
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
