分页经典实现:
select t2.* from (select t1.*,rownum rn from ( select * from users ) t1 where rownum <= 600100 ) t2 where t2.rn >= 600000;
在100W+级别的表中,执行时间为1.077秒
合并查询:
Union:用于合并2个select查询语句的并集结果,会去重复
Union all:类似于union,但是不会去重复,不会排序
Intersect:用于取得2个select结果集中的交集
Minus:用于取得2个select结果集中的差集,只显示存在于第一个select语句并且不存在于第二个select语句中的结果集.
函数:
1、 Cube函数：立方体函数，根据Cube中的列分别进行分组
cube(deptno,job)：先按deptno分组,再按job分组, 再按(deptno,job)联合分组
显示每个部门的平均工资，每个岗位的平均工资，每个部门每个岗位的平均工资
select avg(sal),deptno,job from emp group by cube(deptno,job);
函数分类:
一、 单行函数
1. 字符函数
(1) ascii():返回与指定的字符相对应的十进制数:
select ascii('A') A,ascii('a') a,ascii('0') zero ,ascii(' ') space from dual;
A A ZERO SPACE
-- ---------- ---------- ----------
65 97 48 32
(2)char():给出整数,返回对应的字符.和ascii()函数具有相反的功效.
(3)concat:连接2个字符串,和’||’具有相同的功效
(4)initcap():返回字符串,并将字符串的第一个字母大写
Select initcap('helloworld') h from dual;
H
----------
Helloworld
(5)instr(C1,C2,I,J):在一个指定的字符串中搜索指定字符的位置,
C1:被搜索的字符串;
C2:希望搜索的字符;
I:搜索的开始位置,默认为1:
J:出现的第几次,默认为1.
例: select instr('oracle traning','ra',1,2) as indexs from dual;
INDEXS
----------
9
(6)length:返回字符串的长度
(7)lower()和upper():将字符串转化为小/大写
例: select lower('Aa') v1,upper('Aa') as v2 from dual;
V1 V2
-- --
aa AA
(8)rpad()和lpad():在列的右边/坐标粘贴字符
select rpad ('gao',10,'*') from dual;
RPAD('GAO',10,'*')
------------------
gao*******
select rpad ('gao',10,'*') from dual;
RPAD('GAO',10,'*')
------------------
gao*******
(9)ltrim()和rtrim()去掉左边和右边的空格
(10)substr(string,start,count):截取字符串,注意,start从1开始
(11)replace(string,s1,s2):替换一个字符串中的某些字符
Ps:在用replace替换select查询出的内容时,替换掉的仅仅是显示的数据,数据表中保存的数据并未被改变.
(12)soundex():返回一个与给定的字符串读音相同的字符串(并不常用)
(13)trim():剪掉字符串前后指定的字符,
如果不指定,默认剪掉空格(与字符语法略有不同)
select trim('*' from '**张三**' ) as name from dual;
NAME
----
张三
2.数字函数
(1)abs:返回指定值的绝对值
(2)acos():反余弦
…还有很多类似的三角函数的函数,这里不一一列出.
(3)ceil():返回大于或等于给出数字的最大整数.
ceil是天花板的意思,所以是向上取整.
select ceil(4.12),ceil(5) from dual;
CEIL(4.12) CEIL(5)
---------- ----------
5 5
(4)floor():返回小于或等于给出数字的最大整数.和ceil有类似的效果
(5)exp():返回一个数字e(2.7182818)的n次方根
(6)in(n)和log(n1,n2):返回一个数字的对数值和指数值
(7)mod(n1,n2):返回一个n1除以n2的余数
(8)power(n1,n2):返回n1的n2次方根.power(2,3)返回值为2的3次方8
(9)round():用于四舍五入
trunc():用于截取,可以指定截取到小数点后面几位小数
select trunc(456.123,2),trunc(456.123,-2),round(456.123) from dual;
TRUNC(456.123,2) TRUNC(456.123,-2) ROUND(456.123)
---------------- ----------------- --------------
456.12 400 456
(10)sign(n):取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
3.日期函数:
(1) add_months():增加或减去月份
例:显示最近3个月入职的员工
分析:入职时间加上3个月,如果时间大于当前日期,则是3个月内入职的
select * from emp where add_months(hiredate,3) > sysdate;
(2) last_day():返回日期的最后一天.比如查询这个月的最后一天是几月几号:
select last_day(sysdate) from dual;
(3) months_between():计算2个时间点之后有几个月.
比如:计算smith入职到现在又多少个月
select months_between(sysdate,t.hiredate) from emp t where t.empno = 7369;
MONTHS_BETWEEN(SYSDATE,T.HIRED
------------------------------
408.385160543608
(4) next_day(date,’day’):给出日期date和星期x之后下一个星期的日期
select next_day('18-5月-2001','星期五') from dual;
NEXT_DAY('18-5月-2001','星期五
------------------------------
2001/5/25
如上: 离 2001 年 5 月 18 日最近的星期五是 2001 年五月 25 日.
(5)sysdate:获取当前时间.
4.转换函数:
(1) chartorowid()/rowidtochar():将字符数据类型和rowid类型相互转换
(2) convert(c,dest,sset):将源字符串sset从一个字符集转到另一个dest字符集.(没搞明白是怎么用的)
(3)hextoraw():将一个十六进制的字符串转换成二进制
(4)rowtohex():将一个二进制的字符串转换成十六进制
(5) to_char(date,fmt):将日期date按照fmt格式转换成char格式
例:select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2014-12-28 22:12:26
Fmt的格式不仅仅限于时间,还有其他的格式:
yy:显示两位数字的年份: 9:表示数字,并忽略前面的0
yyyy:显示4位数字的年份 0:表示数字,如果位数不足,用0补齐
mm:两位数字的年份 .:在指定位置显示.
dd:2位数字的天 ,:在指定位置显示,
hh24: $:在数字前面加美元符号$
hh12 L:在数字前面加本地货币符号
mi,ss:显示分钟/秒 C:在数字前面加国际货币符号
day:星期几 G:在指定位置显示组分隔符
D:在指定位置显示小数点符号(.)
(6) to_date(string,'format'):将字符串转换成oracle中的日期.
Formate:是预定义的日期格式
(7) to_multi_byte():将单字节字符串转换成多字节字符
(8) to_number():将给出的字符串转换成数字
(9) bfilename(dir,file): 参数 dir 表示路径，参数 file 表示文件名。
5.系统函数:
(1) convert(‘x’,’desc’,’source’):将x变量的源source转换为desc
(2)dump(s,fmt,start,length):以fmt指定的内部数字格式返回一个varchar2类型的值
(3) empty_blob()/empty_clob():对大数据类型字段初始化
(4)greatest()/least():返回一组表示式中的最大/小值,比较的ascii码值
(5) uid/user:返回当前登录用户的uid/用户名
(6) sys_context('userenv',info);
select sys_context('userenv','db_name') from dual;
select sys_context('userenv',’host’) from dual;
info可以的选项:
terminal:当前会话客户想对应的终端的标识符
language:语言
db_name:当前数据库名称
nls_date_format:当前会话客户端所对应的日期格式
session_user: 当前会话客户端所对应的数据库用户名
current_schemal:当前会话客户端所对应的解决方案
host:数据库所在的主机名
二、多行函数
(1) Avg:
(2) Max():
(3) Min():
(4) Stddev():求标准差
(5) variance():求协方差
(6) group by:分组函数
(7) having:对group by的分组数据进行过滤
(8) order by:排序函数
(9) decode: 类似于 case 语句，或者说是数字电路中的译码器电路。
例: select decode(deptno,10,'这是 10 号部门',20,'这是 20 号部门',30,'这是 30 号部门') from emp;
DECODE(DEPTNO,10,'这是10号部门
------------------------------
这是 20 号部门
这是 30 号部门
这是 30 号部门
这是 20 号部门
这是 30 号部门
这是 30 号部门
这是 10 号部门
这是 20 号部门
这是 10 号部门
这是 30 号部门
这是 20 号部门
这是 30 号部门
这是 20 号部门
这是 10 号部门
浙公网安备 33010602011771号