OracleSQL_071笔记_1（函数小结）

1、SQL基础

----查看数据库对象定义
SELECT DBMS_METADATA.GET_DDL(‘SEQUENCE’,‘EMP_SEQ’) FROM DUAL

SQL语句实例，查询各部门中工资大于5k的员工平均工资在6k以上的部门信息;

SELECT
DEPARTMENT_ID
,SUM(SALAR)
,AVG(SALAR)
FROM EMPLOYEES
WHERE SALARY>5000
AND DEPARTMENT=‘90’
GROUP BY EMPLOYEE_ID
HAVING AVG(SALAR) >6000
;

1.1、比较运算符

符号	注释
=	equal to
>	greater than
<	less than
>=	greate than or equal to
<=	less than or equal to
<>	not equal to
!=	not equal to
in (list)	match any of a list of values 匹配任意一个在列表中的字符
like	match a character pattern 匹配字符模
is null	is a null value
between … and …	是一个闭区间，等价于 <=… and >= …

1.2、行限制语句

用于在查询结果集中筛选出制定的数据，如排序后保留前或跳过前几行数据。
这类语句是在order by 之后对排序后的结果集进行操作的语句

SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; --排序后跳过前5个，从第6行开始，输出5个
FETCH FIRST 5 ROWS ONLY
; --输出结果集前五个，

1.3、定义变量

如果同一段代码需要替换不同的参数执行多次，可以使用定义变量的方式来简化；
单&符号表示的变量需要每次执行的时候重新赋值，如：&var1
双&符号表示的变量需要每次执行的时候重新赋值，如：&&var1
可以使用 define定义变量，定义的变量用，需要在变量名前用"&"来引用，如： define var3; select &var3 from dual;
可以使用 undefine 来删除变量，如：undefine var3;

定义变量并赋值

DEFINE DEP_ID=101;
UNDEFINE DEP_ID;
SELECT ‘&DEP_ID’ FROM DUAL; --使用时变量名前加上&

在sql中定义变量，每次执行时都要重新赋值

SELECT SALARY FROM EMPLOYEES WHERE SALARY<=&SAL;

定义一个持久化的变量，下次使用变量时不用赋值

SELECT SALARY FROM EMPLOYEES WHERE SALARY<=&&SAL;

2.单行函数

2.1、字符\数字函数

SELECT UPPER(‘abc’) from dual; -->ABC
SELECT lower(‘EFG’) from dual; -->efg
SELECT TRUNC(123.456) FROM DUAL ; -->123 直接截取
SELECT TRUNC(123.456,2) FROM DUAL ; -->123.45
SELECT ROUND(456.789) FROM DUAL; -->457 四舍五入
SELECT ROUND(-456.789) FROM DUAL; -->-457
SELECT FLOOR(12.3) FROM DUAL; -->12 寻找比自己小的整数
SELECT FLOOR(-12.3) FROM DUAL; --> -13
SELECT CEIL(45.937) FROM DUAL; --> 46 比自己大的整数
SELECT CEIL(-45.937) FROM DUAL; --> -45
SELECT MOD(94,9) FROM DUAL ; -->4

日期函数

SELECT SYSDATE FROM DUAL ; --中国 yyyy-mm-dd 美国： dd mm yy
–变更当前会话的日期格式
alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;
alter session set nls_date_format=‘yyyy-mm-dd’;

空值操作

聚合函数时null会被忽略
与null计算的数字结果为null
字符串与null拼接返回字符本身
NULL的相关函数包括：nvl,nvl2,nullif,coalesce,decode

SELECT 1+NULL FROM DUAL; --> NULL
SELECT ‘A’||NULL FROM DUAL; --> A
SELECT NVL(1,NULL) FROM DUAL ; -->1 :nvl如果第一个参数为空，则返回第二个参数，否则返回第一个参数
SELECT NVL(NULL,2) FROM DUAL ; -->2
SELECT NVL2(‘null’,‘p2’,‘p3’) FROM DUAL; -->p2 nvl2函数用法：判断第一个参数是否为空，否则返回参数2，是则返回参数3
SELECT NVL2(NULL,‘p2’,‘p3’) FROM DUAL ; -->p3

SELECT NULLIF(‘P1’,‘P1’) FROM DUAL; --结果：NULL ； nullif函数： 当两个参数相同则返回null，两个参数不同则返回第一个参数；
SELECT NULLIF(‘Y’,‘N’) FROM DUAL; --Y
SELECT COALESCE(‘A’,‘B’,‘C’) FROM DUAL ; —> A 返回第一个非空
SELECT COALESCE(NULL,‘B’,‘C’) FROM DUAL ; —> B

decode函数 case表达式

SELECT LAST_NAME,SALARY,
DECODE(TRUNC(SALARY/2000,0),0,0.00,1,0.09,0.45) TAX --返回第一匹配的值，否则返回最后一个
FROM EMPLOYEES WHERE DEPARTMENT_ID=‘80’;

case表达式
case expr1 when val1 then ‘1’ when val2 then ‘2’ else ‘3’ end ;
case when expr1=val1 then ‘1’ when expr1=val2 then ‘2’ else ‘3’ end ;

TRUNC和round的区别

trunc日期的话是截取时间，返回月初或年初，不会发生进位
round有可能返回明年初或下月初
round 过了7月1号(含)开始都是返回下一年，过来16号（含）开始返回下一月的 （无论大小月，无论大小年）

SELECT
SYSDATE --2021-03-11 23:40:36
,ROUND(SYSDATE,‘MM’) --2021-03-01 00:00:00
,ROUND(SYSDATE,‘YY’) --2021-01-01 00:00:00
,ROUND(TO_DATE(‘2021-10-31’,‘YYYY-MM-DD’) ,‘YY’) --2022-01-01 00:00:00
,ROUND(TO_DATE(‘2021-10-31’,‘YYYY-MM-DD’) ,‘MM’) --2021-11-01 00:00:00
,TRUNC(TO_DATE(‘2021-10-31’,‘YYYY-MM-DD’) ,‘YY’) --2021-01-01 00:00:00
,TRUNC(TO_DATE(‘2021-10-31’,‘YYYY-MM-DD’) ,‘MM’) --2021-10-01 00:00:00
FROM DUAL
;

转义语句

用q’[需转义的语句]’ 来转义字符串，如想返回一个单引号可以用四个单引号返回’’’’ ,也可以用 q’[’]’ 来实现；
select last_name,last_name ||q’[’]’||salary from employees ;

转换函数

在Oracle中数据类型转换分为隐式和显式转换两种。
隐式转换由系统自动完成，显式转换需要手动调整；
转换函数： to_char,to_date,to_number

to_date函数： 讲字符转成日期

select to_date(‘20210313’,‘yyyy-mm-dd’) from dual; --> 13-3月 -21
select to_date(20210313 ,‘yyyy-mm-dd’) from dual;