《基础语法篇》空值、字符串、数值
空值相关
NVL()，返回非null值
--若a.name为空，则nvl返回空的
select nvl(a.name,'空的') as name from student a join school b on a.ID=b.ID
is null 或者 is not null，查询为空
select *
from a
where alex is null or is not null
字符串拼接
“||”
select '1'||'2' from dual --代表12
select 'ABC'||'EFG' from dual; --ABCEFG
concat()函数
select concat('A','B') from dual;
wm_concat()函数，拼接多行数据
wm_concat()函数是oracle中独有的,mysql中有一个group_concat()函数。
要把local_labour_name这个字段用逗号拼接成一条数据。
然后我们需要加上wm_concat()这个函数,但是我这边加上的时候出现了问题:
这并不是我想要的结果,最后我看了看哪里出现了问题,原来是字段类型没对上，我们这边需要to_char()一下;
这个时候我们就得到了我们想要的结果。如果我们的查询到的结果中有重复怎么办?我们可以再前面加上distinct来去除重复。
LISTAGG() WITHIN GROUP ()，拼接多行数据
查询部门为20的员工列表:
SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20';
使用 listagg() WITHIN GROUP () 将多行合并成一行：
SELECT
T .DEPTNO,
LISTAGG (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) NAMES
FROM SCOTT.EMP T
WHERE
T .DEPTNO = '20' GROUP BY T .DEPTNO
2.高级用法：over(partition by XXX)。在不使用Group by语句时候，也可以使用LISTAGG函数：
WITH TEMP AS(
SELECT 500 POPULATION, 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL
SELECT 1500 POPULATION, 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL
SELECT 500 POPULATION, 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL
SELECT 1000 POPULATION, 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL
SELECT 500 POPULATION, 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL
SELECT 500 POPULATION, 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL
)
SELECT POPULATION,
NATION,
CITY,
LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY) OVER (PARTITION BY NATION) RANK
FROM TEMP
字符串截取
trim，去空格处理
//trim
SQL> select trim(' 11 ') aa from dual;
AA
11
//trim_character
SQL> select trim('x' from 'xxxxWORLDxxxx') aaaaa from dual;
AAAAA
WORLD
//使用rtrim
SQL> select rtrim('ORxxxxWORLDxxxxOR','OR') aaaaa from dual;
AAAAA
ORxxxxWORLDxxxx
//使用ltrim
SQL> select ltrim('ORxxxxWORLDxxxxOR','OR') aaaaa from dual;
AAAAA
xxxxWORLDxxxxOR
substr()，字符串截取
substr()，字符串截取
语法：SUBSTR(string，start， [length])
string：表示源字符串，即要截取的字符串。
start：开始位置，从1开始查找。如果start是负数，则从string字符串末尾开始算起。
length：可选项，表示截取字符串长度。
SELECT SUBSTR('Hello SQL!', 1) FROM dual --截取所有字符串，返回'Hello SQL!'
SELECT SUBSTR('Hello SQL!', 2) FROM dual --从第2个字符开始，截取到末尾。返回'ello SQL!'
SELECT SUBSTR('Hello SQL!', -4) FROM dual --从倒数第4个字符开始，截取到末尾。返回'SQL!'
SELECT SUBSTR('Hello SQL!', 3, 6) FROM dual --从第3个字符开始，截取6个字符。返回'llo SQ'
SELECT SUBSTR('Hello SQL!', -4, 3) FROM dual --从倒数第4个字符开始，截取3个字符。返回'SQL'
字符串查询
instr()，返回子字符串在源字符串中的位置
instr()，返回子字符串在源字符串中的位置
语法：INSTR(string，child_string，[start]，[show_time])
string：表示源字符串。
child_string：子字符串，即要查找的字符串。
start：可选项，开始位置，默认从1开始。如果为负数，则从右向左检索。
show_time：可选项，表示子字符串第几次出现在源字符串当中，默认第1次，负数则报错。
--表示从源字符串'city_company_staff'中第1个字符开始查找子字符串'_'第1次出现的位置
SELECT INSTR('city_company_staff', '_') FROM dual --返回5
--表示从源字符串'city_company_staff'中第5个字符开始查找子字符串'_'第1次出现的位置
SELECT INSTR('city_company_staff', '_', 5) FROM dual --返回5
--表示从源字符串'city_company_staff'中第5个字符开始查找子字符串'_'第1次出现的位置
SELECT INSTR('city_company_staff', '_', 5, 1) FROM dual --返回5
--表示从源字符串'city_company_staff'中第3个字符开始查找子字符串'_'第2次出现的位置
SELECT INSTR('city_company_staff', '_', 3, 2) FROM dual --返回13
--start参数为-1，从右向左检索，查找'_'字符串在源字符串中第1次出现的位置
SELECT INSTR('city_company_staff', '_', -1, 1) FROM dual --返回13
--start参数为-6，从右向左检索，查找'_'字符串在源字符串中第2次出现的位置
SELECT INSTR('city_company_staff', '_', -6, 2) FROM dual --返回5
length()，查询字符串长度
（1）length
select length(字段名) from 表名；
（2）lengthb
--lengthb(string)计算string所占的字节长度：返回字符串的长度，单位是字节
--length(string)计算string所占的字符长度：返回字符串的长度，单位是字符
--对于单字节字符，lengthb()和length()是一样的
--可以用lengthb('string')=length('string') 判断字符串是否含有中文
--例如：
select length('：：') from dual; --结果是 2字符
select lengthb('：：') from dual; --结果是 6字节
数值处理
trunc()，用于截取时间或者数值，返回指定的值。
（1）处理日期：
--sysdate和trunc(sysdate)是不一样的 sysdate在当天的零时零分零秒等于trunc(sysdate)
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
to_char(trunc(sysdate), 'yyyy-mm-dd hh24:mi:ss')
from dual t;
--年
select trunc(sysdate, 'yy') "当年第一天" from dual;
select trunc(sysdate, 'yyyy') "当年第一天" from dual;
select trunc(sysdate, 'year') "当年第一天" from dual;
--季度
select trunc(sysdate, 'q') "当前季度的第一天" from dual;
select trunc(add_months(sysdate,3), 'Q') -1/24 "这个季度最后一天23点" from dual;
--月
select trunc(sysdate, 'mm') "当月第一天" from dual;
select trunc(sysdate, 'month') "当月第一天" from dual;
select trunc(last_day(sysdate)+1) "下个月第一天的0点" from dual;
--周
select trunc(sysdate, 'd') "本周的第一天，周日起" from dual;
select trunc(sysdate,'day') "本周的第一天，周日起" from dual;
select next_day(TRUNC(SYSDATE ), 'TUESDAY' ) + 12/24 "下个星期二中午12点" from dual;
select TRUNC(LEAST(NEXT_DAY(SYSDATE, 'SATURDAY'), NEXT_DAY(SYSDATE, 'SUNDAY'))) + （6*60+10）/（24*60） "下个周六日早上6点10分" from dual;
--天
select trunc(sysdate+1) "今晚12点" from dual;
select trunc(sysdate+1) + (8*60+30)/(24*60) "明天早上8点30分" from dual;
--时
select trunc(sysdate, 'hh')"当前小时" from dual;
select trunc(sysdate, 'hh24') "当前小时" from dual;
--分
select trunc(sysdate, 'mi') "当前分钟" from dual;
select trunc(sysdate,'mi') + 10/ (24*60) "10分钟后的时间"from dual;
--秒
select sysdate + 10/(24 * 60 * 60) "10秒钟后" from dual;--trunc没有精确到秒的精度
（2）处理数值：
--123.56,将小数点右边指定位数后面的截去;
select trunc(123.567,2) from dual;
--100,第二个参数可以为负数，表示将小数点左边指定位数后面的部分截去，即均以0记;
select trunc(123.567,-2) from dual;
--123,默认截去小数点后面的部分;
select trunc(123.567) from dual;
ROUND()，截取数字
格式如下：ROUND（number[,decimals]）
其中：number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分，并四舍五入。如果为负数则表示从小数点开始左边的位数，相应整数数字用0填充，小数被去掉。需要注意的是，和trunc函数不同，对截取的数字要四舍五入。
SQL> select round(1234.5678,4) from dual;
ROUND(1234.5678,4)
——————
1234.5678
SQL> select round(1234.5678,3) from dual;
ROUND(1234.5678,3)
——————
1234.568
SQL> select round(1234.5678,2) from dual;
ROUND(1234.5678,2)
——————
1234.57
SQL> select round(1234.5678,1) from dual;
ROUND(1234.5678,1)
——————
1234.6
SQL> select round(1234.5678,0) from dual;
ROUND(1234.5678,0)
——————
1235
SQL> select round(1234.5678,-1) from dual;
ROUND(1234.5678,-1)
——————-
1230
SQL> select round(1234.5678,-2) from dual;
ROUND(1234.5678,-2)
——————-
1200
SQL> select round(1234.5678,-3) from dual;
ROUND(1234.5678,-3)
——————-
1000
附加：
SQL> select round(45.923,-1) from dual;
ROUND(45.923,-1)
——————-
50
