Oracle进阶sql语句
一. 系统函数
dual:Oracle 数据库提供的一个临时的系统表
1. 字符类函数:主要用来操作字符串
(1)ascii():将字符转换成ASCII码值
例子:select ascii('A') from dual
(2)chr():将ASCII码值转换成字符
例子:select chr(65) from dual
(3)concat(s1,s2):字符串拼接
例子:select concat('hello','world') from dual
(4)initcap():将字符串首字母变成大写,其他字母变成小写
例子:select initcap('hello WORld')
(5)instr(s,s1,[i,j]):返回字符串s1在字符串s中第j次出现的位置,i代表从哪个位置开始查找。
i不设置代表从最开始查找,j不设置代表第一次出现的位置
(6)length(s):返回字符串的长度
(7)lower(s):将字符串全部转换成小写
(8)upper(s):将字符串全部转换成大写
(9)trim(s from s1):去除字符串s1两边的字符s
例子:select trim(' ' from ' sysys ') from dual
(9.1)ltrim():去除字符串s1左边的字符s
例子:select trim(' ' from ' sysys ') from dual
(9.1)rtrim():去除字符串s1右边的字符s
(10)replace(s1,s2,s3):将字符串s1中的字符s2替换成s3,如果s3省略,就代表去除s1中的s2
例子:select ('hello world','l','a') from dual
select ('hello world','l') from dual
(11)substr(s,i[,j]):截取字符串,j省略时,截取字符从i到结尾的字符串,j不省略时,截取字符从i开始长度为j的字符串
2. 算数类函数:
(1)round(n,m):m不写时四舍五入到整数,m写时表示保留的小数位数
(2)trunc(n,m):直接截取,不会四舍五入
(3)ceil(n):向上取整,返回比n大的最小整数
(4)floor(n):向下取整,返回比n小的最大整数
(5)sqrt(n):返回n的平方根,只返回正数
(6)mod(n,m):取n除m的余数
(7)power(x,y):计算x的y次幂
3. 时间类函数:
(1)sysdate:返回当前的系统时间
(2)last_day(d):返回指定时间当月的最后一天
(3)add_months(d,n):在指定时间的月份增加n个月
注意:如果要加多少天的话直接使用+/-运算符进行操作
(4)months_between(d1,d2):返回两个时间之间相差的月份
注意:如果要计算相差多少天的话直接使用-运算符进行操作
(5)trunc(d,['mm','q','day','iw','ww']):mm,将时间定位到d的月份的第一天,
q,将时间定位到d当前季度的第一天,
day,定位到d当周的第一天(第一天为周日)
iw,定位到d当周周一
ww,定位到d上周周六
(6)extract(year/month/day from d):截取时间d的年份,月份,天
注意:如果需要截取时、分、秒,则需要将时间转换成时间戳再截取
语法:extract(hour/minute/second from cast(d as timestamp))
4. 转换类函数:
(1)to_date(s,'格式'):将一个时间字符串转换成时间对象
(2)to_char(d,[格式']'):将一个时间对象或数据转换成字符串
注意:时间转换成字符串时如果需要自定义格式则要将字符串用" "包起来
例子:select to_char(sysdate,' yyyy"年"mm"月"dd"日" ') from dual
(3)to_timestamp(s,'格式'):将一个时间字符串转换成时间戳对象
(4)cast(数据 as 类型):将数据转换成指定类型
5. 分支类函数:
(1)case...when:
简单函数:
语法:case 字段 when 值 then 值
when 值 then 值
...
else 值 end
搜索函数:
语法:case when 条件1 then 语句1
when 条件2 then 语句2
...
else 语句n
end
(2)nvl(字段值,值1):如果字段值为null则返回值1,否则返回字段的值
nvl2(字段值,值1,值2):如果字段值不为空则返回值1,否则返回值2
(3)decode(字段,值1,值2,值3):如果字段的值等于值1则返回值2,否则返回值3
decode(字段,值1,语句1,值2,语句2,值3,语句3):如果字段的值等于值1,则返回语句1;如果字段的值等与值2,则语句2;否则返回语句3,执行过程与case的简单函数类似
二. 表集合操作
1. 并集操作:union [all]
将两个查询结果合并在一起
语法:select 语句 union [all] select 语句
注意:合并的表的列数必须一样且对应的数据类型也要一致,列名不要求相同
2. 交集操作:intersect
显示两张表结果中重复的数据
语法:select 语句 intersect select 语句
3.差集操作:minus
显示第一张表去除与第二张表重复的数据后的结果
语法:select 语句 minus select 语句
三. 开窗函数
语法:函数名() over(partition by 分组字段 order by 排序字段 [desc|asc] [定位框架])
1.函数名支持的函数有:
(1)聚合函数
row_number():按照连续的数字进行排序,不关注相关名次
rank():不会进行连续排名,如果遇到相同的名称则会跳过后续的名次,可以保证总名次等于总人数
dense_rank():会进行连续排序,如果遇到相同的值会显示相同的名次,但是不能保证总名次等于总人数
(2)移动函数
lag(字段,n):将指定的字段的值下移n行
lead(字段,n):将指定的字段的值上移n行
2. over():窗体函数,主要就是将表中的数据进行分组排序
group by 分组之后会将原始数据按照指定的分组进行汇总统计成一行数据,并不会显示每一组的详细信息
但是使用over分组之后,只是在结束后面新增几列来显示汇总后的数据不会影响原来的数据
partition by:开窗函数中的分组
order by:进行排序,如果要对某一列的值进行累加则需要对它进行排序
3. 开窗函数的定位框架
在使用了order by的时候可以设置定位框架
语法:order by 排序字段 [range|rows] between 边界规则1 and 边界规则2
边界规则:unbounded preceding:向上一直到第一行
n preceding:向上n行
unbounded following:向下一直到最后一行
n following:向下n行
current row :当前行
注意:如果不设置边界定位框架,则默认从第一行到当前行,并且按照range进行累计操作
range是按照值的范围进行累加,rows按照行进行累加
四. 行列转换
1. listagg():将指定的列中的数据拼接成一行数据
注意:必须要配置group by分组使用
语法:listagg(字段,'分隔符') within group (order by 排序字段)
行转列:
sname course score
小明 语文 78
小明 数学 90
小白 英语 90
小红 语文 88
行专列之后:
sname 语文 数学 英语
小明 78 90 0
小白 0 0 90
小红 88 0 0
2. pivot():行转列
语法:select 列名,列名,列名 from 表名 pivot(sum(需要计算的列名) for 需要转换的列 in('值1' [as] 列名1,'值2' [as] 列名2,'值3' [as] 列名3))
行转列:
(1)使用 case when 进行行转列
select sname,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 stu_cou_t group by sname;
(2)使用 decode 进行行转列
select sname,sum(decode(course,'语文' ,score,0)) 语文,
sum(decode(course,'数学' ,score,0)) 数学,
sum(decode(course,'英语' ,score,0)) 英语 from stu_cou_t group by sname;
(3)使用 pivot() 函数进行行转列
select sno,course,score from stu_cou_t pivot(sum(score) for course in('语文' 语文,'数学' 数学,'英语' 英语))
3.unpivot():列转行
(1)使用union
select * from (
select sname,'语文' course, 语文 score from stu_cou_t1
union
select sname,'数学' course, 数学 score from stu_cou_t1
union
select sname,'英语' course, 英语 score from stu_cou_t1) a where a.score!=0;
(2)使用 unpivot() 函数
语法:select * from 表名 unpivot(自定义列名 from 自定义列名 in('列名','列名','列名'))
select * from stu_cou_t1 unpivot(score for course in(语文,数学,英语))
五. 分页查询
rownum:用来显示表中的数据的行号,通常用来进行分页查询数据
分页查询的语法:
select a.* from (select b.*,rownum rn from 表名 b [order by ]) a where a.rn between 开始位置 and 结束位置
六. 给表中的数据去重
rowid:伪列类型,代表表中每一行数据的物理存储地址,该值是唯一的,在添加一条数据的时候系统就会生成一个rowid保存起来
rownum 与 rowid 的区别
rownum 是根据数据库的列而显示的行号,rowid 是数据存储的物理地址,是在添加数据的时候生成的,是唯一的
将表中重复的数据删除
delete from 表名 where rowid not in(select min(rowid) from 表名 group by 列名[,列名,列名])