ORACLE函数大全
SQL中的单记录函数
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵A
3.CONCAT
连接两个字符串;
SQL> select concat('010-','88888888')||'转23' 高乾竞电话from dual;
高乾竞电话
----------------
010-********转23
4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap('smith') upp from dual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2) instring from dual; INSTRING
---------
9
6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
高乾竞 3 北京市海锭区 6 9999.99 7
7.LOWER
返回字符串,并将所有的字符小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper('AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD
9.RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;
LPAD(RPAD('GAO',1
-----------------
*******gao*******
不够字符则用*来填满
10.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;
LTRIM(RTRIM('
-------------
gao qian jing
11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('130********',3,8) from dual;
SUBSTR('
--------
08888888
12.REPLACE('string','s1','s2')
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace('he love you','he','i') from dual;
REPLACE('H
----------
i love you
13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');
SQL> select xm from table1 where soundex(xm)=soundex('weather');
XM
--------
weather
wether
14.TRIM('s' from 'string')
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符
15.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
--------- ---------
100 100
给出反余弦的值
SQL> select acos(-1) from dual;
ACOS(-1)
---------
3.1415927
17.ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878
18.A TAN
返回一个数字的反正切值
SQL> select atan(1) from dual;
A TAN(1)
---------
.78539816
19.CEIL
返回大于或等于给出数字的最小整数SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
4
20.COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
-1
21.COSH
返回一个数字反余弦值
SQL> select cosh(20) from dual;
---------
242582598
22.EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)
--------- ---------
7.3890561 2.7182818
23.FLOOR
对给定的数字取整数
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
24.LN
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)
--------- --------- -------------
0 .69314718 .99999999
25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)
--------- ---------
0 2
26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55
29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)
--------- ---------- ---------
1 -1 0
30.SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
1
31.SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
--------- ---------
.91294525 242582598
32.SQRT
返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
--------- ---------
8 3.1622777
33.TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)
--------- ---------
2.2371609 .64836083
34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;
TANH(20) TAN(20)
--------- ---------
1 2.2371609
35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16
36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;
TO_CHA
------
199910
https://www.doczj.com/doc/cd3770418.html,ST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5月-04
38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
MON_BETWEEN
-----------
9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('
2005.05.20','yyyy.mm.dd')) mon_betw from dual;
MON_BETW
---------
-60
39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time 2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32 2004.05.09 18:05:32
40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;
NEXT_DAY
----------
25-5月-01
41.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;
TO_CHAR(SYSDATE,'
-----------------
09-05-2004 星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;
HH HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00
42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
ROWID ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ---------- AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC W ARD AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES
43.CONVERT(c,dset,sset)
将源字符串sset从一个语言字符集转换到另一个目的dset字符集SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual; conver
------
strutz
44.HEXTORAW
将一个十六进制构成的字符串转换为二进制
45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制
46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型
47.TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2004/05/09 21:14:41
48.TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL> select to_multi_byte('高') from dual;
TO
--
高
50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;
YEAR
---------
1999
51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
52.CONVERT('x','desc','source')
将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
2 0,'none',
3 2,'insert',
4 3,
5 'select',
6 6,'update',
7 7,'delete',
8 8,'drop',
9 'other') cmd from v$session where type!='background';
SID SERIAL# USERNAME CMD
--------- --------- ------------------------------ ------
1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个V ARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D 54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;
GR
--
AC
SQL> select greatest('啊','安','天') from dual;
GR
--
天
56.LEAST
返回一组表达式中的最小值
SQL> select least('啊','安','天') from dual;
LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"GAO"
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME USER_ID
------------------------------ ---------
GAO 25
https://www.doczj.com/doc/cd3770418.html,ER
返回当前用户的名字
SQL> select user from dual;
USER
------------------------------
GAO
https://www.doczj.com/doc/cd3770418.html,EREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSI ZE
ISDBA 查看当前用户是否是DBA如果是则返回true
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SQL> select userenv('isdba') from dual; USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv('sessionid') from dual; USERENV('SESSIONID')
--------------------
152
ENTRYID
返回会话人口标志
SQL> select userenv('entryid') from dual; USERENV('ENTRYID')
------------------
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual; USERENV('INSTANCE')
-------------------
1
LANGUAGE
返回当前环境变量
SQL> select userenv('language') from dual; USERENV('LANGUAGE')
---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual; USERENV('LANG')
---------------------------------------------------- ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual; USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
6 SYSTEM
60.A VG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('zhu',5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3;
A VG(DISTINCTSAL)
----------------
3333.33
SQL> select avg(all sal) from gao.table3;
A VG(ALLSAL)
-----------
2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;
MAX(DISTINCTSAL)
----------------
5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from gao.table3;
MIN(ALLSAL)
-----------
1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.5032
SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
1229.951
64.V ARIANCE(DISTINCT|ALL)
求协方差
SQL> select variance(sal) from scott.emp;
V ARIANCE(SAL)
-------------
1398313.9
65.GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400
66.HA VING
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
67.ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 W ARD 1250
30 MARTIN 1250
30 JAMES 950
oracle的字符函数
--------------------------------------------------------------------
字符函数主要用于修改字符列。这些函数接受字符输入，返回字符或数字值。Oracle 提供的一些字符函数如下。
1. CONCAT (char1, char2)
返回连接“char2”的“char1”。
示例
2. INITCAP(string)
将“string”的字符转成大写。
示例
3. LOWER (string)
将“string”转成小写。
示例
4. LPAD(char1,n [,char2])
返回“char1”，左起由“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长，则函数返回“char1”的前“n”个字符。
示例
5. LTRIM(string,trim_set)
从左边删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set”是我们要去掉的字符的集合。
示例
6. REPLACE(string, if, then)
用 0 或其他字符代替字符串中的字符。“if”是字符或字符串，对于每个出现在“string”中的“if”，都用“then”的内容代替。
示例
7. RPAD(char1, n [,char2])
返回“char1”，右侧用“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长，则函数返回“char1”的前“n”个字符。
示例
8. RTRIM(string,trim_set)
从右侧删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set”是我们要去掉的字符的集合。
示例
9. SOUNDEX(char)
返回包含“char”的表意字符的字符串。它允许比较英语中拼写不同而发音类似的字。
示例
10. SUBSTR(string, start [,count])
返回“string”中截取的一部分。该命令截取“string”的一个子集，从“start”位置开始，持续“count”个字符。如果我们不指定“count”，则从“start”开始截取到“string”的尾部。
示例
11. TRANSLATE(string, if, then)
“if”中字符的位置，并检查“then”的相同位置，然后用该位置的字符替换“string”中的字符。
示例
12. UPPER(string)
返回大写的“string”。
示例
13. ASCII(string)
该命令是“American Standard Code for Information Interchange”的缩写。它是使用数字表示可打印字符的基本规则。该函数返回“string”中第一个（最左边）字符的 ASCII 值。
示例
14. INSTR (string, set[, start[, occurrence] ] )
该命令“string”中从“start”位置开始查找字符集合的位置，再查找“set”出现的第一次、第二次等等的“occurrence”（次数）。“start”的
值也可以是负数，代表从字符串结尾开始向反方向搜索。该函数也用于数字和日期数据类型。
示例
15. LENGTH(string)
返回“string”的长度值。
SQLServer(多语句表值函数代码) 代码如下： set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE FUNCTION [dbo].[ufnGetContactInformation](@ContactID int) RETURNS @retContactInformation TABLE ( -- Columns returned by the function [ContactID] int PRIMARY KEY NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [JobTitle] [nvarchar](50) NULL, [ContactType] [nvarchar](50) NULL ) AS -- Returns the first name, last name, job title and contact type for the specified contact. BEGIN
DECLARE @FirstName [nvarchar](50), @LastName [nvarchar](50), @JobTitle [nvarchar](50), @ContactType [nvarchar](50); -- Get common contact information SELECT @ContactID = ContactID, @FirstName = FirstName, @LastName = LastName FROM [Person].[Contact] WHERE [ContactID] = @ContactID; SET @JobTitle = CASE -- Check for employee WHEN EXISTS(SELECT * FROM [HumanResources].[Employee] e WHERE e.[ContactID] = @ContactID) THEN (SELECT [Title] FROM [HumanResources].[Employee] WHERE [ContactID] = @ContactID) -- Check for vendor
Oracle中分析函数用法小结 一.分析函数适用场景: ○1需要对同样的数据进行不同级别的聚合操作 ○2需要在表内将多条数据和同一条数据进行多次的比较 ○3需要在排序完的结果集上进行额外的过滤操作 二.分析函数语法: FUNCTION_NAME(
SQLSERVER数据库操作 ******操作前，请确定SQL的服务已经开启******** 一：登录进入sql数据库 1、开始---所有程序---Microsoft SQL Server 2005---SQL Server Management Studio Express 2、此时出现“连接到服务器”的对话框， “服务器名称”设置为SQL数据库所在机器的IP地址 “身份验证”设置为SQL Server身份验证或者Windows 身份验证 填写登录名和密码后，点击“连接”按钮，即可进入到SQL数据库操作界面。 二：新建数据库 登录进去后，右击“数据库”，选择—“新建数据库” 设置数据库名称，在下面的选项卡中还可以设置数据库的初始大小，自动增长，路径。 点击确定，一个数据库就建好了。 三：如何备份的数据库文件。 登录进入后，右击相应的需要备份数据库----选择“任务” 目标下的备份到，点击“添加”按钮可以设置备份数据库保存的路径。 四：如何还原备份的数据库文件。（以本地机器为例子） 1、设置服务器名称，点击右边的下拉框的三角，选择“浏览更多…”。 此时出现查找服务器对话框，选择“本地服务器”---点开“数据库引擎”前面 的三角---选中出现的服务器名称—确定。 （注：可以在“网络服务器”选项卡中设置网络服务器） 2、设置身份验证，选择为“windows身份验证” 3、点击连接按钮，进入数据库管理页面 4、右击“数据库”，选择“还原数据库”，出现还原数据库的对话框 还原的目标----目标数据库，这里设置数据库的名字 还原的源----选择“源设备”，在弹出的对话框中点击“添加”按钮，找到所备 份的数据库文件，确定。 5、此时，在还原数据库对话框中会出现所还原的数据库的信息。在前面选中所需还 原的数据库。确定。 6、为刚刚还原的数据库设置相应的用户。 a点开“安全性”---右击“登录名”---新建登录名 b 设置登录名（假如为admin），并设置为SQL Server身份验证，输入密码，去除 “强制实施密码策略”前的勾。 C 找到导入的数据库，右击此数据库----选择“属性”，在选择页中，点击“文件” 设置所有者，点击右边的按钮，选择“浏览”，找到相应的用户（如admin）。确 定。。 7、此时重新以admin的身份进入，就可操作相应的数据库。
oracle中常用函数大全 1、数值型常用函数 函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual;
Oracle 分析函数(Analytic Functions)说明一. Analytic Functions 说明 分析函数是oracle 8中引入的一个概念,为我们分析数据提供了一种简单高效的处理方式. 官方对分析函数的说明如下： Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The groupof rows is called a window and is defined bythe analytic_clause. For each row, a sliding window of rows is defined.The window determines the range of rows used to perform the calculations forthe current row. Window sizes can be based on either a physical number of rowsor a logical interval such as time. Analytic functions are the last set of operations performed in a query except for thefinal ORDER BY clause. All joins and all WHERE, GROUP BY,and HAVING clauses are completed before the analytic functions areprocessed. Therefore, analytic functions can appear only in the select listor ORDER BY clause. Analytic functions are commonly used to compute cumulative, moving, centered, andreporting aggregates. From：Analytic Functions https://www.doczj.com/doc/cd3770418.html,/cd/E11882_01/server.112/e26088/functions004.htm#S QLRF06174 分析函数是对一组查询结果进行运算，然后获得结果，从这个意义上，分析函数非常类似于聚合函数（Aggregate Function）。区别是在调用分析函数时，后面加上了开窗子句over()。 聚合函数是对一个查询结果中的每个分组进行运算，并且对每个分组产生一个运算结果。分析函数也是对一个查询结果中的每个分组进行运算，但每个分组对应的结果可以有多个。产生这个不同的原因是分析函数中有一个窗口的概念，一个窗口对应于一个分组中的若干行，分析函数每次对一个窗口进行运算。运算时窗口在查询结果或分组中从顶到底移动，对每一行数据生成一个窗口。 Oracle 聚合函数（Aggregate Functions）说明 https://www.doczj.com/doc/cd3770418.html,/tianlesoftware/article/details/7057249
oracle比较decode/case when 、4种去重和去重中的统计函数 一：decode 和case when decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default) decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。 select username,decode(lock_date,null,'unlocked','locked') status from t; ----------------如果lock_date是null就返回unlocked 如果不是null就返回locked select username,decode(lock_date,null,'unlocked') status from t; ----------------如果lock_date是null就返回unlocked 否则是空(因为没有定义) 例如有个学生表...... 行转列------- create table score2( name varchar2(10), Language number(3), Math number(3), English number(3)); insert into score2 values('Zhang',80,67,95); insert into score2 values('Li',79,84,62); insert into score2(name,Language) values('Chen',88); commit; Select * from score2; 显示成报表的格式....... select name, sum(decode(subject,'Language', grade,0)) "Language", sum(decode(subject,'Math', grade,0)) "Math", sum(decode(subject,'English', grade,0)) "English" from score group by name; NAME Language Math English -------------------- ---------- ---------- ---------- Zhang 80 92 76 Wang 73 0 0 Li 81 95 50 第二列，如果subject='Language',那么就显示成绩,否则显示为0 第三列，如果subject='Math',那么就显示成绩,否则显示为0 第四列，如果subject='English',那么就显示成绩，否则显示为0 Case when Case [selector] ---selector可以不设置 When id=1 THEN ‘id是1’; ELSE ‘没有id值’; END CASE; ---->如果ID=1 就显示id是1 否则显示没有id值 ~~~~~上面用case when显示................. select https://www.doczj.com/doc/cd3770418.html,, sum( case when t.subject = 'Language' then t.grade else 0 end ) dd, sum( case when t.subject = 'Math' then t.grade else 0 end ) Math,
sql server日期时间函数 Sql Server中的日期与时间函数 1. 当前系统日期、时间 select getdate() 2. dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值 例如：向日期加上2天 select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000 3. datediff 返回跨两个指定日期的日期和时间边界数。 select datediff(day,'2004-09-01','2004-09-18') --返回：17 4. datepart 返回代表指定日期的指定日期部分的整数。 SELECT DATEPART(month, '2004-10-15') --返回 10 5. datename 返回代表指定日期的指定日期部分的字符串 SELECT datename(weekday, '2004-10-15') --返回：星期五 6. day(), month(),year() --可以与datepart对照一下 select 当前日期=convert(varchar(10),getdate(),120) ,当前时间=convert(varchar(8),getdate(),114) select datename(dw,'2004-10-15') select 本年第多少周=datename(week,'2004-10-15') ,今天是周几=datename(weekday,'2004-10-15') 函数参数/功能 GetDate( ) 返回系统目前的日期与时间 DateDiff (interval,date1,date2) 以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1 DateAdd (interval,number,date) 以interval指定的方式，加上number之后的日期 DatePart (interval,date) 返回日期date中，interval指定部分所对应的整数值
SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; A A ZERO SPACE --------- --------- --------- --------- 65 97 48 32 2.CHR 给出整数,返回对应的字符; SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C -- - 赵 A 3.CONCAT 连接两个字符串; SQL> select concat('010-','')||'转23' 高乾竞电话from dual; 高乾竞电话 ---------------- 010-转23 4.INITCAP 返回字符串并将字符串的第一个字母变为大写; SQL> select initcap('smith') upp from dual; UPP ----- Smith 5.INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置; C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置,默认为1 J 出现的位置,默认为1 SQL> select instr('oracle traning','ra',1,2) instring from dual; INSTRING --------- 9 6.LENGTH 返回字符串的长度; SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst; NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL
--已知：两种排名方式(分区和不分区):使用和不使用partition --两种计算方式(连续,不连续),对应函数：dense_rank,rank 语法： rank()over(order by排序字段顺序) rank()over(partition by分组字段order by排序字段顺序) 1.顺序：asc|desc名次与业务相关： 示例：找求优秀学员：成绩：降序迟到次数：升序 2.分区字段：根据什么字段进行分区。 问题：分区与分组有什么区别? ·分区只是将原始数据进行名次排列(记录数不变), ·分组是对原始数据进行聚合统计(记录数变少,每组返回一条),注意：聚合。rank()与dense_rank()：非连续排名与连续排名(都是简单排名) ·查询原始数据：学号,姓名,科目名,成绩 select*from t_score; S_ID S_NAME SUB_NAME SCORE 1张三语文80.00 2李四数学80.00 1张三数学0.00 2李四语文50.00 3张三丰语文10.00 3张三丰数学 3张三丰体育120.00 4杨过JAVA90.00 5mike c++80.00 3张三丰Oracle0.00 4杨过Oracle77.00 2李四Oracle77.00 ·查询各学生科目为Oracle排名(简单排名) select sc.s_id,sc.s_name,sub_name,sc.score,rank()over(order by score desc)名次from t_score sc where sub_name='Oracle'; S_ID S_NAME SUB_NAME SCORE名次 4杨过Oracle77.001 2李四Oracle77.001 3张三丰Oracle0.003 ·对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名) S_ID S_NAME SUB_NAME SCORE名次
分析函数sum over() 介绍 报送单位：审核人： 类型：业务应用 关键字：分析函数 1、引言 运维中，常常需要通过SQL语句对单行数据进行查询，同时又需要对结果集进行汇总，通常的方法是通过两个SQL语句分别进行查询汇总，这样效率低下。 2、现象描述 本节介绍一种ORACLE提供的全新的函数sum over()，该类函数称为分析函数，这类函数功能强大，可以通过一个SQL语句对数据进行遍历的同时又进行汇总，而且一张表只进行一次扫描，极大地提高SQL的执行效率。 3、处理过程 语法： FUNCTION_NAME(
执行计划： 下图说明分析函数只对表进行一次扫描 4、举例说明 下面分别举例来说明分析函数的使用，原始数据如下表
示例1：查询单行数据同时对所有数据工资进行汇总求和 示例2：查询所有数据，同时对第各部门工资进行汇总，汇总范围取值为第一行所在部门至当前部门所有数据。
示例3：查询所有数据，并且每一行汇总值按ENAME排序后取第一行至当前行ENAME所在行。 示例4：查询所有数据，并且每一行汇总值按EMPNO排序后取第一行至当前行EMPNO所在行
示例5：查询所有数据，同时每行对各部门分别进行按EMPNO排序后从各组第一行至当前行汇总求和。 示例6：查询所有数据，同时每行对从当前上两行数据范围的工资进行汇总求和。 示例7：查询所有数据，同时每行对从当前向前一行数据向后两行数据范围的工资进行汇总求和。
Sql Server 常用函数 1，统计函数avg, count, max, min, sum 2， 3，多数聚会不统计值为null的行。可以与distinct一起使用去掉重复的行。可以与group by 来分组4， 5， 2，数学函数 6， 7， SQRT 8， ceiling（n) 返回大于或者等于n的最小整数 9， floor(n), 返回小于或者是等于n的最大整数 10，round(m,n), 四舍五入,n是保留小数的位数 11，abs(n) 12，sign(n), 当n>0, 返回1，n=0,返回0，n<0, 返回-1 13，PI(), 3.1415.... 14，rand(),rand(n), 返回0-1之间的一个随机数 15，3，字符串函数 16， 17，ascii(), 将字符转换为ASCII码, ASCII('abc') = 97 18，char(), ASCII 码转换为字符 19，low()，upper() 20，str(a,b,c)转换数字为字符串。a,是要转换的字符串。b是转换以后的长度，c是小数位数。 str(123.456,8,2) = 123.46 21，ltrim(), rtrim() 去空格 22，left(n), right(n), substring(str, start,length) 截取字符串 23，charindex(子串，母串），查找是否包含。返回第一次出现的位置，没有返回0 24，patindex('%pattern%', expression) 功能同上，可是使用通配符 25，replicate('char', rep_time), 重复字符串 26，reverse(char),颠倒字符串 27，replace(str, strold, strnew) 替换字符串 28，space(n), 产生n个空行 29，stuff(), SELECT STUFF('abcdef', 2, 3, 'ijklmn') ='aijklmnef', 2是开始位置，3是要从原来串中删除的字符长度，ijlmn是要插入的字符串。 30，3，类型转换函数: 31， 32，cast, cast( expression as data_type), Example: 33，SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales FROM titles WHERE CAST(ytd_sales AS char(20)) LIKE '3%' 34，convert(data_type, expression) 35，4,日期函数 36， 37，day(), month(), year() 38，dateadd(datepart, number, date), datapart指定对那一部分加，number知道加多少，date指定在谁的基础上加。datepart的取值包括，
附录Ⅱ Oracle11g SQL函数 函数名 返回 类型 说明 字符串函数 ASCII(s) 数值 返回s首位字母的ASCII码 CHR(i) 字符 返回数值i的ASCII字符 CONCAT(s1,s2) 字符 将s2连接到字符串s1的后面 INITCAP(s) 字符 将每个单词首位字母大写其它字母小写 INSTR(s1,s2[,i[,j]]) 数值 返回s2在s1中第i位开始第j次出现的位置 INSTRB(s1,s2[,i[,j]]) 数值 与INSTR(s)函数相同，但按字节计算 LENGTH(s) 数值 返回s的长度。 LENGTHb(s) 数值 与LENGTH(s)相同，但按字节计算。 lower(s) 字符 返回s的小写字符 LPAD(s1,i[,s2]) 字符 在s1的左侧用s2字符串补足到总长度i LTRIM(s1,s2) 字符 循环去掉在s2中存在的s1左边字符 RPAD(s1,i[,s2]) 字符 在s1的右侧用s2字符串补足到总长度i RTRIM(s1,s2) 字符 循环去掉在s2中存在的s1右边字符 REPLACE(s1,s2[,s3]) 字符 用s3替换出现在s1中的s2 REVERSE(s) 字符 返回s倒排的字符串 SUBSTR(s,i[,j]) 字符 从s的第i位开始截得长度j的子字符串 SUBSTRB(s,i[,j]) 字符 与SUBSTR相同，但i,j按字节计算。 SOUNDEX(s) 返回与s发音相似的词 TRANSLATE(s1,s2,s3) 字符 将s1中与s2相同的字符以s3代替 TRIM(s) 字符 删除s的首部和尾部空格 UPPER(s) 字符 返回s的大写 正则表达式函数 REGEXP_LIKE() 布尔 功能与LIKE的功能相似 REGEXP_INSTR() 数值 功能与INSTR的功能相似 REGEXP_SUBSTR() 字符 功能与SUBSTR的功能相似 REGEXP_REPLACE() 字符 功能与REPLACE的功能相似 数字函数 ABS(i) 数值 返回i的绝对值 ACOS(i) 数值 反余玄函数，返回-1到1之间的数 ASIN(i) 数值 反正玄函数，返回-1到1之间的数 ATAN(i) 数值 反正切函数，返回i的反正切值
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是 对于每个组返回多行，而聚合函数对于每个组只返回一行。 一、开窗函数 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。 例如： 1)over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数 2)over（partition by department_id）按照部门分区 3)over（order by salary range between 50 preceding and 150 following） 每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150 4)over（order by salary rows between 50 preceding and 150 following） 每行对应的数据窗口是之前50行，之后150行 5)over（order by salary rows between unbounded preceding and unbounded following） 每行对应的数据窗口是从第一行到最后一行，等效： 6) over（order by salary range between unbounded preceding and unbounded following） 其中： 第一行是unbounded preceding 当前行是current row 最后一行是unbounded following 二、分析函数的概念 分析函数是在整个SQL查询结束后(SQL语句中的ORDER BY的执行比较特殊)再进行的操作, 也就是说 SQL语句中的ORDER BY也会影响分析函数的执行结果。 分析函数中包含三个分析子句:分组(Partition By), 排序(Order By), 窗口(Window) 当省略窗口子句时: 1) 如果存在Order By则默认的窗口是unbounded preceding and current row 2) 如果同时省略Order By则默认的窗口是unbounded preceding and unbounded following 如果省略分组,则把全部记录当成一个组 a) 如果SQL语句中的Order By满足分析函数分析时要求的排序,那么SQL语句中的排序将先执行,分析 函数分析时就不必再排序 b) 如果SQL语句中的Order By不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分 析函数分析结束后执行排序 三、分析函数 1）AVG
SQL SERVER函数大全 SQL SERVER命令大全 SQLServer和Oracle的常用函数对比 1.绝对值 S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual 3.取整（小） S:select floor(-1.001) value O:select floor(-1.001) value from dual 4.取整（截取） S:select cast(-1.002 as int) value O:select trunc(-1.002) value from dual 5.四舍五入 S:select round(1.23456,4) value 1.23460 O:select round(1.23456,4) value from dual 1.2346 6.e为底的幂 S:select Exp(1) value 2.7182818284590451 O:select Exp(1) value from dual 2.71828182 7.取e为底的对数 S:select log(2.7182818284590451) value 1 O:select ln(2.7182818284590451) value from dual; 1 8.取10为底对数 S:select log10(10) value 1 O:select log(10,10) value from dual; 1 9.取平方 S:select SQUARE(4) value 16 O:select power(4,2) value from dual 16
PL/SQL单行函数和组函数详解 函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类: 单行函数; 组函数 本文将讨论如何利用单行函数以及使用规则。SQL中的单行函数 SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。 SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename) 单行函数也可以在其他语句中使用，如update的SET子句，INSERT的V ALUES子句，DELET的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。 NULL和单行函数 在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCA T,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数:NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。 下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿 column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2 不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子: update empset salary=(salary+bonus)*1.1 这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。 所以正确的语句是: update empset salary=(salary+nvl(bonus,0)*1.1 单行字符串函数 单行字符串函数用于操作字符串数据，他们大多数有一个或多个参数，其中绝大多数返回字符串 ASCII()
自定义聚合函数，分析函数 --from GTA Aaron 最近做一数据项目要用到连乘的功能，而Oracle数据库里没有这样的预定义聚合函数，当然利用数据库已有的函数进行数学运算也可以达到这个功能，如： selectexp(sum(ln(field_name))) from table_name; 不过今天的重点不是讲这个数学公式，而是如何自己创建聚合函数，实现自己想要的功能。很幸运Oracle 允许用户自定义聚合函数，提供了相关接口，LZ研究了下，留贴共享。 首先介绍聚合函数接口： 用户可以通过实现Oracle的Extensibility Framework中的ODCIAggregate interface 来创建自定义聚合函数，而且自定义的聚合函数跟内建的聚合函数用法上没有差别。 通过实现ODCIAggregaterountines来创建自定义的聚合函数。可以通过定义一个对象类型（Object Type），然后在这个类型内部实现ODCIAggregate 接口函数(routines)，可以用任何一种Oracle支持的语言来实现这些接口函数，比如C/C++, JAVA, PL/SQL等。在这个Object Type定义之后，相应的接口函数也都在该Object Type Body内部实现之后，就可以通过CREATE FUNCTION语句来创建自定义的聚合函数了。 每个自定义的聚合函数需要实现4个ODCIAggregate 接口函数，这些函数定义了任何一个聚合函数内部需要实现的操作： 1. 自定义聚合函数初始化操作,从这儿开始一个聚合函数。初始化的聚合环境(aggregation context)会以对象实例(object type instance)传回给oracle. static function ODCIAggregateInitialize(varIN OUTagg_type ) return number 2. 自定义聚合函数,最主要的步骤,这个函数定义我们的聚合函数具体做什么操作，self 为 当前聚合函数的指针,用来与前面的计算结果进行关联。这个函数用来遍历需要处理的
在SQL Server在线图书或者在线帮助系统中，函数的可选参数用方括号表示。在下列的CONVERT()函数例子中，数据类型的length和style参数是可选的: CONVERT (data-type [(length)], expression[,style]) 可将它简化为如下形式，因为现在不讨论如何使用数据类型： CONVERT(date_type, expression[,style]) 根据上面的定义，CONVERT()函数可接受2个或3个参数。因此，下列两个例子都是正确的： SELECT CONVERT(Varchar(20),GETDATE()) SELECT CONVERT(Varchar(20),GETDATE(), 101) 这个函数的第一个参数是数据类型Varchar(20)，第2个参数是另一个函数GETDATE()。GETDATE()函数用datetime数据类型将返回当前的系统日期和时间。第2条语句中的第3个参数决定了日期的样式。这个例子中的101指以mm/dd/yyyy格式返回日期。本章后面将详细介绍GETDATE()函数。即使函数不带参数或者不需要参数，调用这个函数时也需要写上一对括号，例如GETDATE()函数。注意在书中使用函数名引用函数时，一定要包含括号，因为这是一种标准形式。 确定性函数 由于数据库引擎的内部工作机制，SQL Server必须根据所谓的确定性，将函数分成两个不同的组。这不是一种新时代的信仰，只和能否根据其输入参数或执行对函数输出结果进行预测有关。如果函数的输出只与输入参数的值相关，而与其他外部因素无关，这个函数就是确定性函数。如果函数的输出基于环境条件，或者产生随机或者依赖结果的算法，这个函数就是非确定性的。例如，GETDATE()函数是非确定性函数，因为它不会两次返回相同的值。为什么要把看起来简单的事弄得如此复杂呢？主要原因是非确定性函数与全局变量不能在一些数据库编程对象中使用(如用户自定义函数)。部分原因是SQL Server缓存与预编译可执行对象的方式。例如，即席查询可以使用任何函数，不过如果打算构建先进的、可重用的编程对象，理解这种区别很重要。 以下这些函数是确定性的： ●?AVG()(所有的聚合函数都是确定性的) ●?CAST() ●?CONVERT() ●?DATEADD() ●?DATEDIFF() ●?ASCII() ●?CHAR() ●?SUBSTRING() 以下这些函数与变量是非确定性的： ●?GETDATE()
数值函数
日期函数 SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual; SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual; 其他函数
5.高级查询（多表联合查询） 例子： SELECT job 职务 , SUM(sal) 工资总和 FROM emp WHERE job != 'PRESIDENT' GROUP BY job HAVING SUM(sal)>4500 ORDER BY SUM(sal); 分析函数 以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始 ROW_NUMBER返回连续的排位，不论值是否相等 RANK具有相等值的行排位相同，序数随后跳跃 DENSE_RANK 具有相等值的行排位相同，序号是连续的 例子： SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) RANK FROM emp; SELECT ename, job, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS SAL_RANK FROM SCOTT.EMP; SELECT d.dname, e.ename, e.sal, DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) AS DENRANK FROM emp e, dept d WHERE e.deptno = d.deptno;