oracle函数+虚表dual+order by +having

一、单行函数（对一行进行操作）

- 字符函数
concat(m,n)：
拼接m,n并返回

initcap(n):
将n的第一个字母转换为大写

instr(x,y,m,n)
在x中查询y的位置. m代表从第几个位置开始查找，n代表字符串y出现的次数；

length(n):求字符串n的长度

lower(n)全部转为小写

lpad(x,n，y)：在x的左边补齐字符y
x:被操作的字符串
n:总的长度
y:补齐的字符,不写y,默认空格

rpad(x,n，y)：在x的右边补齐字符y

trim([leading| trailing | both[trim_character] ] from x )
leading:截取x左边指定的字符
trailing ：截取x右边指定的字符
both：截取x两边边指定的字符
x:被操作的字符串

trim(n):去除n的首尾空格

ltrim(x,y) :在x左边截取字符y

rtrim(x,y) :在x右边截取字符y

nvl(x,y)：如果x为空，返回y，否则返回x

nvl2(x,y,z)：如果x为空，返回z，否则返回y

substr(x,m,n)：字符串x从指定位置m开始获取n个长度的字符串

nanvl(x,y):如果x不是数字，返回y,否则返回x

replace(x,y,z) 在字符串x中，将y字符串替换成z

- 数字函数
abs(x):求x绝对值
ceil(x):大于或等于x的最小整数
floor(x):小于或等于x的最大整数

round(x,y)对x的第y位小数进行四舍五入（y分正负,对应四舍五入方向），
保留第y为小数.

trunc(x,y)对x的第y位小数进行截断（y分正负,对应截断方向）

mod(x,y) ：x/y的余数
power(x,y):x的y次方
sqrt(x)：x的平方根
- 转换函数(一种 类型到另外一种类型)
to_date(x[,format]) 将x转换为date

to_number(x[,format]) 将x转换为number

to_char(x[,format]) 将x转换为varchar2

cast(x as type) 将x 转换为type所指定的兼容数据类型
convert(x，source_char_set,dest_char_set):将x重源字符集source_char_set转换为结果字符集dest_char_set
decode(x，y,m，n):如果x==y返回m，否则返回n
bin_to_num(x):将二进制x转换为number类型
to_timestamp(x)将字符串x转换为一个timestamp类型

- 日期函数
add_months(x,y) 返回x+y后的月份
last_day(x):该月最后一天
months_between(x,y)返回x,y间隔多少个月
next_day(x,day):从x所在周开始，从周日开始计算，本周的第day的日期
sysdate():获取系统当前的日期值
current_timestamp():获取当前的日期和时间
greatest(data1,data2,….)从日期列表中获取最早的日期

round(x，y) 对x的y进行取整
下图年份的分割线在月份

trunc(x,y)对x的y截断
下图对年份截断，都返回1月1号

- 正则表达式函数

^ 匹配输入字符串的开始位置
$ 匹配输入字符串的结束位置
* 匹配前面的子表达式任意次 。 zo*等价于zo{0,}
+ 匹配前面的子表达式一次或多次。 zo+等价于zo{1,}
? 匹配前面的子表达式零次或一次。zo?等价于zo{0,1}
{n} 匹配确定的n次。o{2}等价于oo
\d 匹配一个数字字符。等价于[0-9]。
\D 匹配一个非数字字符。等价于[^0-9]。
\w 字母字符
\W 非字母字符
\s 空白字符
\S 非空白字符
{n,m} 匹配n次到m次。o{2,5}表示有2个到5个的o
. 匹配除null之外的任何单个字符
x|y 匹配x或y “z|food”能匹配”z”或”food”或”zood”
[a-z] 匹配指定范围内的任意字符
??匹配前面的元素0次或1次
{n,} 至少匹配n次。o{2,}表示2个o,或者大于2个o
\n :对前一次捕获的一个反向引用，其中n是正整数，\1 重复一次上次匹配的内容
\A 只匹配字符串的开头位置
\Z 只匹配字符串的结束位置
*？匹配前面元素0次或者多次
+？：匹配前面的元素1次或者多次

regexp_like(String,pattern【match_option】)
从String中搜索pattern参数定义的正则表达式.
String:被搜索的字符串
pattern：正则表达式
match_option
————–i:匹配不区分大小写，
————–c:匹配区分大小写，默认值，
————–n:允许使用可以匹配任意字符的操作符
————–m:将String作为一个包含多行的字符串，以^字符为字符串的开始，$为字符串的结束

regexp_instr(String,pattern
【,start
【,occurrence
【,return_option
【,match_option
】】】】)
从String中搜索pattern，并返回pattern所在的位置

start：搜索的开始位置，默认为1
occurrence：返回第几次出现的pattern的位置，默认位置1
return_option：说明应该返回什么整数，如果才参数为0，表示要返回的整数是String的第一个字符的位置，
如果该参数是非0的整数，说明要返回的整数位String中出现在pattern之后的字符的位置
match_option：参数为c，i, n，m参照上面的

regexp_replace(String,pattern
【,replace_str
【,start
【,occurrence
【,match_option
】】】】)

在String查找pattern，并将其替换为replace_str，参数的含义参照上面

regexp_substr(String,pattern
【,start
【,occurrence
【,match_option
】】】】)
返回String中可以匹配pattern的一个子字符串，起开始位置由stat决定，参数的含义参照上面

regexp_count(String,pattern
【,start
【,match_option
】】)
在String中查找pattern,并返回pattern在string中出现的次数.

测试数据
create table student2(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
cno varchar2(2)
);

insert into student2 values(‘1’,’lili’,18,’1’);
insert into student2 values(‘2’,’lili’,18,’1’);
insert into student2 values(‘3’,’lili’,18,’1’);
insert into student2 values(‘4’,’lili’,20,’1’);
insert into student2 values(‘5’,’lili’,21,’1’);

select sno,sname ,regexp_instr(sname,’i’)regexp_instr,regexp_replace(sname,’l’,’k’) regexp_replace
from student2
where regexp_like(sname,’^li’,’c’);

二、聚合函数（对多行进行操作）

count:返回非空的记录数
注意：count(name),如果name有10行，其中一行是null,返回的就是9,null不计入返回值中。
min:返回列的最小值
max:返回列的最大值
sum:对列求和
avg:返回列的平均值
media(x)：返回x的中间值，x为数字列.或计算列

group by;
将行分组为具有相同列值的的多个部分,多与聚合函数一起使用
流程是先查询，按列分组，再聚合

流程是先查询，按列分组，再聚合
获取姓李的平均年龄

先按查询条件获取数据，再按李分组，最后求每组的平均值

having
对聚合函数进行限制
或者说分组后的表进行数据筛选