SQL函数

常用的SQL函数：

运算符：

①between 值1 and 值2：查询数值在值1和值2之间的数据，值1要小于等于值2
②In（值列表）：查询数值在括号里面值中的数据
③like：模糊查询

另外：查询数据中带有%的数据：
SELECT * FROM 表 WHERE 列名 LIKE ‘%%%’ ESCAPE’’; escape可以定义转义字符
④四则运算

算数运算函数：

    select mod(10,3) from dual; 求余
    select abs(-1) from dual; 绝对值
    select dbms_random.random() from dual;产生随机数 
    Select dbms_random.value() from dual;产生0-1之间随机小数

日期函数：sysdate

一般配合to_char、trunc和其他日期处理函数

格式转换函数：

①to_char(日期函数等，格式化字符串) select to_char(sysdate,’yyyy-mm-dd’) from dual;

②to_date(字符串，转换格式)
③to_number(字符串)

截断函数truncate（trunc）

可对字符串进行截断:
①trunc(sysdate,‘year’)按年截断
②trunc(45.123，-1) —>40

字符长度计算函数length

length(字符串或列名)计算对应字符长度

字符串连接函数concat

concat(‘字符串A’,‘字符串B’) 等价于’字符串A’||‘字符串B’（Oracle独有）

字符串截取函数substr

substr('字符串’或列名，值1，值2) 值1表示从第几位开始截取，值2表示截取几位

空值转换函数nvl

nvl(参数1，参数2)表示当参数1的值为空值（null）时返回参数2

条件判断函数case when

Case命令：
when 条件1 then 返回值1
when 条件2 then 返回值2
…
else 返回值n

组函数

①Max:select max (sal) from emp;
②Min:select min (sal) from emp;
③Sum:select sum (sal) from emp:
④Avg:select avg (sal) from emp;
⑤Count:select count(*) from emp; 总记录数
select count（sal）from emp; sal列不为空的记录数

去重函数Distinct

去除重复记录 select distinct sal from emp；

伪列函数

rowid:同一个表里rowid取值是唯一的，代表某条数据的一个物理地址可以快速找到该记录。
rowid from 表名；

编号生成函数

rownum：给查询结果产生一个编号

别名函数

as ：给列起一个临时的别名：列名 as 别名
方便后期使用，特别是列名比较长的

Oracle独有的排名函数

rank() over(partition by 列名 order by 列名)

字符处理函数

LOWER 将字符串转换成小写
UPPER 将字符串变为大写
INITCAP 将字符串的第一个字母变为大写

日期处理函数

MONTHS_BETWEEN(date2,date1) 给出 date2 - date1的月数
ADD_MONTHS 增加或减去月份
NEXT_DAY ( date,’day) 给出日期date之后下一天的日期
LAST_DAY(date) 返回日期所在月的最后一天

数值处理函数（四舍五入）

Ceil 向上取整
Floor向下取整
Round四舍五入 ROUND (45.923, -1)----->50
Trunc去掉小数 TRUNC (45.923, -1)----->40

字符定位函数

Instr(a,b,c,d):判断字符串在现字符串中的位置：
A;现字符串
B:要查找的字符串
C:在现字符串的第几位开始查找
D:找到第D次出现要查找字符串的位置

字符替换函数

Replace：替换对应字符：
Replace(a,b,c):a表示目标字符串，b表示目标字符串中要替换的字符，c表示要替换成什么

填充函数

Pad:rpad/lpad:用法：pad(字符串/列名，数字，’填充符号’)
例如：select rpad(ename,10,’*’) from emp
显示以10个字符的方式显示ename，不足10位的用*号代替

删除空格函数

trim去除字符串中所有空格
ltrim(字符串/列名)：去除字符串左端的空格
Rtrim(字符串/列名)：去除字符串右端的空格

开窗函数

开窗over()一般不单独所有例如：
row_number():它必须和over()开窗函数联用，排序结果和伪列rownum相同
rank():它排序，重复的数据序号相同，序号不连续
dense_rank()：排序，重复数据序号相同 ，序号连续

select emp.*,rank()over(order by sal) from emp;

over(partition by 分组列 order by 排序列)：partition by根据列进行分组，这时的order by它表示组内排序

exists和in的转换

–IN语法：

SELECT SNO, SNAME
  FROM STUDENT
 WHERE SNO NOT IN (SELECT DISTINCT T1.SNO
                     FROM SC T1, COURSE T2, TEACHER T3
                    WHERE T1.CNO = T2.CNO
                      AND T2.TNO = T3.TNO
                      AND T3.TNAME = '谌燕');

–exists语法：

SELECT SNO, SNAME
  FROM STUDENT  T
 WHERE NOT EXISTS (SELECT DISTINCT T1.SNO
          FROM SC T1, COURSE T2, TEACHER T3
         WHERE T1.CNO = T2.CNO
           AND T2.TNO = T3.TNO
           AND T3.TNAME = '谌燕'	
           AND T.SNO = SNO);

decode函数

decode(列名,列或表达式的值,返回值,列或表达式的值,返回值,…,默认值);
例如：

select t.*,decode(ssex,'男',1,'女',0) from student t;

如果ssex列的值是“男”则返回值1，否则返回默认值0

select t.*,decode(ssex,'男',1,0) from student t;

Decode函数和case when函数的转换：

例如：
–查询员式信息如果sal<1000，显示低工资，如果sal>=1000 sal<2000 显示中等工资，如果sal>=2000 显示高工资

select emp.*,(case when sal<1000 then '低工资' when sal>=1000 and sal<2000 then '中等工资' else '高工资' end) 工资水平 from emp;

等同于：
将sal除以1000后向下取整如果其值为0时返回“低工资”其值为1时返回“中等工资”其他情况返回默认值“高工资”

select emp.*,decode(floor(sal/1000),0,'低工资',1,'中等工资','高工资') from emp;

行转列函数

方法1：

利用行转列函数unpivot:

select name, course, res score from score unpivot(res for course in(math, chinese, english));

//Unpivot函数中res列即score的值等于原表Math，Chinese，English列的相应值并且转换后
的course列取值必须存在于in后面括号中

方法2：

select 
name,								//转换后的name列来自原表name列可以理解为：'name' name
'math' course,					//转换后的course列值为‘math’
math								//转换后的第3列来自于原表的math列可以理解为：'math' math
from score
union all
select name,'chinese' course,chinese from score 
union all
select name,'english' course,english from score;

列转行函数

做法一：

select student,
       sum(case when course = '语文' then score else 0 end) 语文,
       sum(case when course = '英语' then score else 0 end) 英语,
       sum(case when course = '数学' then score else 0 end) 数学,
       sum(case when course = '物理' then score else 0 end) 物理
  from cc
 group by student;

做法二：

select student,
       sum(decode(course, '语文', score, 0)) 语文,
       sum(decode(course, '数学', score, 0)) 数学,
       sum(decode(course, '英语', score, 0)) 英语,
       sum(decode(course, '物理', score, 0)) 物理
  from cc
 group by student;

做法三：

利用列转行函数 pivot：

select * from 
(select * from cc
        pivot(sum(score)
           for course in('语文' 语文, '数学' 数学, '英语' 英语, '物理' 物理)));

字符串连接函数

wm_concat() 将查询结果以字符串连接的方式呈现

SELECT MAX(CNO) FROM 
(SELECT WM_CONCAT(CNO) OVER(PARTITION BY SNO ORDER BY CNO) CNO
          FROM SC WHERE SNO = 's001')

regexp_substr函数

regexp_substr(源字符串,正则表达式,开始位置,第几个);
该函数输出结果为字符串类型，实际使用中需要转换数据类型

select regexp_substr('&str','[^,]+',1,2) from dual;
--&为占位符

例如输入7369,S,orcl,7369,1999-09-09,4312,null,10则输出结果就是 S（字符串类型）

最后：此文档是以Oracle数据库为基础整理的可能其他数据库不适用。