[zT]sql培训内容
SQL定义：SQL是一种面向数据库的通用数据处理语言规范，能完成以下几类功能：提取查询数据，插入修改删除数据，生成修改和删除数据库对象，数据库安全控制，数据库完整性及数据保护控制。
SQL分类：
DDL—数据定义语言（CREATE，ALTER，DROP，DECLARE）
DML—数据操纵语言（SELECT，DELETE，UPDATE，INSERT）
DCL—数据控制语言（GRANT，REVOKE，COMMIT，ROLLBACK）
db2数据类型
CHAR()：定长字符串 最大长度为 254
VARCHAR()：变长字符 最大长度为 4000
SMALLINT：短整型数字 长度为 2 字节
INTEGER：整型数字 长度为 4 字节
REAL：单精度浮点 32 位近似值
DOUBLE：双精度浮点 64 位近似值
DECIMAL(m,n)：数字 精度为m小数位为n
DATE：日期时间
TIME：日期时间
TIMESTAMP：日期时间
DDL—数据库定义语言：直接提交的。
CREATE：用于创建数据库对象。
DECLARE：除了是创建只在过程中使用的临时表外，DECLARE语句和CREATE语句非常相似。唯一可以被声明的对象是表。并且必须放入用户临时表空间。
DROP：可以删除任何用CREATE（数据库对象）和DECLARE（表）创建的对象。
ALTER：允许修改某些数据库对象的信息。不能修改索引。
下面主要基于对象介绍基本的语法：
1、数据库：
创建数据库：CREATE DATABASE database-name [USING CODESET codeset TERRITORY territory]
注：代码页的问题。
删除数据库：drop database dbname
2、表：
创建新表：
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old
B：create table tab_new as select col1,col2… from tab_old definition only
修改表：
增加一个列:
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。添加主键：
Alter table tabname add primary key(col)
删除主键：
Alter table tabname drop primary key(col)
删除表：drop table tabname
3、表空间：
创建表空间：create tablespace tbsname pagesize 4k managed by database using (file ‘file’ size)
表空间加入容器：alter tablespace tablespace_name add(file 'filename' size)
注：该操作是不可逆的，加入容器后将不能将其删除，因此在加入的时候注意。
删除表空间：drop tablespace tbsname
4、索引：
创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
5、视图：
创建视图：create view viewname as select statement
删除视图：drop view viewname
注：视图唯一能修改的是引用类型列，改变列的范围。其他定义好了都不能修改。当视图基于的基表drop后，视图变为无效。
DML—数据库操纵语言，它不隐式地提交当前事务 ，是否提交视环境设定而定。
SELECT：从表中查询符合数据
注：条件中连接的问题，避免出现笛卡儿乘积
DELETE：删除已有表的数据
UPDATE：更新已有表的数据
INSERT：向已有表中插入数据
注：DELETE，UPDATE和INSERT是否直接提交取决与执行语句所在的环境。
在执行时注意事务日志满的情况。
1、select时，注意索引谓词和非索引谓词，尽量在有索引的列上使用索引谓词。
谓词类型 可索引 注 释
Col∝con Y ∝代表>,>=,=,<=,<,但是<>不是可索引的。
Col between con1 and con2 Y 在匹配系列中必须是最后的。
Col in list Y 仅对一个匹配列
Col is null Y
Col like ‘xyz%’ Y 模糊匹配%在后面。
Col like ‘%xyz’ N 模糊匹配%在前面。
Col1∝Col2 N Col1和col2来自同一个表
Col∝Expression N 例如：c1（c1+1）/2
Pred1 and Pred2 Y Pred1和Pred2都是可索引的，指相同索引的列
Pred1 or Pred2 N 除了（c1=a or c1=b）外，他可以被认为是c1 in（a，b）
Not Pred1 N 或者任何的等价形式：Not between,Not in,Not like等等。
使用索引的例子介绍：
<1>、单个表上索引查询的介绍 ：
A：select * from t1 where c1 = 10；
在c1列上无索引，如何检索
在c1列上有索引，如何检索
B：select * from t1 where c1 =10 and c2 between 5 and 10 and c3 like ‘A%’
仅在c1列上有索引
在c1,c2和c3列上有单独的索引
在c1，c2和c3列上有联合索引
此处可以填加索引匹配规则和高级规则，比较难理解！！
<2>、两个或多个表上索引查询的介绍：
(1)、使用循环嵌套法执行查询
A：select t1.c1,t1.c2,t2.c3,t2.c4 from t1,t2 where t1.c1=10 and t1.c2 =t2.c3
介绍查询的方法，看看哪列上最需要索引。
B：select t1.c1,t1.c2,t2.c3,t2.c4 from t1,t2 where t1.c1=10 and t2.c4 = 10 and t1.c2 =t2.c3
(2)、使用归并连接执行连接查询
例：select t1.c1,t1.c2,t2.c3,t2.c4 from t1,t2 where t1.c1=10 and t2.c4 = 10 and t1.c2 =t2.c3
(3)、三个以上的表进行连接查询采取的规则或者方法
（2）、GROUP BY：实现简单分组的功能，当用group by时，查询选择列中除了在group by中出现的和常量外，其他的列上要用分组函数。可以使用一些分组函数实现一些列不在group by中出现，min，max等。
（3）、HAVING的使用，对一些分组列进行条件判断。
（4）、ORDER BY子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值确定。
2、DELETE：从表中删除记录
语法格式：
DELETE FROM tablename WHERE (conditions)
3、INSERT：向表中插入记录
语法格式：
INSERT INTO tablename (col1,col2,…) VALUES (value1,value2,…);
INSERT INTO tablename (col1,col2,…) VALUES (value1, value2,…), (value1, value2,…),……
Insert不会等待任何程序，不会导致锁定。
4、UPDATE：
语法格式：
UPDATE tabname SET (col1=values1,col2=values2,…) WHERE (conditions);
注：update的速度比较慢，要在相应列上建立索引。
DCL—数据控制语言
GRANT—授予用户权限
REVOKE—撤消用户权限
COMMIT—提交事务，可以使数据库的修改永久化
ROLLBACK—回滚事务，消除上一个COMMIT命令后的所做的全部修改，使得数据库的内容恢复到上一个COMMIT执行后的状态.。
1、GRANT：所有着或者管理员把访问权限赋给其他用户
语法格式：
grant [all privileges|privileges,….] on tabname | viewname to [public|user,….]
2、REVOKE:取消某一用户的某中访问权限
语法格式：
Revoke [all privileges|privileges,….] on tabname | viewname from [public|user,….]
注：不能取消实例级别的用户的任何权限，他们不是通过grant授权的，是通过组实现的权限。
3、COMMIT:把事务中所做的修改永久化记录到数据库。
语法格式：
commit [work]
4、ROLLBACK:将上次提交以来所做的更改全部撤消。
语法格式：
Rollback [work]
高级sql简单介绍
一、查询间使用运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
二、外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
注：复合外连接按照从左到右的顺序执行连接，左边连接的结果集和右边连接
三、超级分组和移动函数
A：grouping sets：用来在单个sql中形成多级分组。
例：select company_id,node_id,count(customer_id) from customer group by grouping sets(company_id,node_id)
B：rollup：可以在单个数据库操作中形成多个分组。
例：select company_id,node_id,count(customer_id) from customer group by rollup(company_id,node_id)
注：rollup操作不是可交换的操作，指定用户组的顺序是很重要的。
C：cube： 生成分组表中分组的所有组合。
例：select company_id,node_id,count(customer_id) from customer group by cube(company_id,node_id)
D：over：移动函数可以帮助实现移动的数据分析
Select date,avg(qty) over(order by date rows between 1 preceding and 1 following) as values from sale
附录：常用函数介绍
1、类型转化函数：
转化为数字类型的：
decimal, double, Integer, smallint,real
Hex(arg):转化为参数的16进制表示。
转化为字符串类型的：
char, varchar
Digits(arg):返回arg的字符串表示法，arg必须为decimal。
转化为日期时间的：
date, time,timestamp
2、时间日期：
year, quarter, month, week, day, hour, minute ,second
dayofyear(arg):返回arg在年内的天值
Dayofweek(arg):返回arg在周内的天值
days(arg):返回日期的整数表示法，从0001-01-01来的天数。
midnight_seconds(arg):午夜和arg之间的秒数。
Monthname(arg):返回arg的月份名。
Dayname(arg):返回arg的星期。
3、字符串函数：
length,lcase, ucase, ltrim, rtrim
Coalesce(arg1,arg2….):返回参数集中第一个非null参数。
Concat (arg1,arg2):连接两个字符串arg1和arg2。
insert(arg1,pos,size,arg2):返回一个，将arg1从pos处删除size个字符，将arg2插入该位置。
left(arg,length):返回arg最左边的length个字符串。
locate(arg1,arg2,<pos>):在arg2中查找arg1第一次出现的位置，指定pos，则从arg2的pos处开始找arg1第一次出现的位置。
posstr(arg1,arg2):返回arg2第一次在arg1中出现的位置。
repeat(arg1 ,num_times):返回arg1被重复num_times次的字符串。
replace(arg1,arg2,arg3):将在arg1中的所有arg2替换成arg3。
right(arg,length):返回一个有arg左边length个字节组成的字符串。
space(arg):返回一个包含arg个空格的字符串。
substr(arg1,pos,<length>):返回arg1中pos位置开始的length个字符，如果没指定length，则返回剩余的字符。
4、数学函数：
Abs, count, max, min, sum
Ceil(arg):返回大于或等于arg的最小整数。
Floor(arg):返回小于或等于参数的最小整数。
Mod(arg1,arg2):返回arg1除以arg2的余数，符号与arg1相同。
Rand():返回1到1之间的随机数。
Power(arg1,arg2):返回arg1的arg2次方。
Round(arg1,arg2):四舍五入截断处理，arg2是位数，如果arg2为负，则对小数点前的数做四舍五入处理。
Sigh(arg):返回arg的符号指示符。-1,0,1表示。
truncate(arg1,arg2):截断arg1，arg2是位数，如果arg2是负数，则保留arg1小数点前的arg2位。
5、其他：
nullif(arg1,arg2)：如果2个参数相等，则返回null，否则，返回参数1
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-88888888转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('13088888888',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
16.ACOS
给出反余弦的值
SQL> Select Acos(-1) From Dual;
ACOS(-1)
---------
3.1415927
17.ASIN
给出反正弦的值
SQL> Select Asin(0.5) From Dual;
ASIN(0.5)
---------
.52359878
18.ATAN
返回一个数字的反正切值
SQL> Select Atan(1) From Dual;
ATAN(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;
COSH(20)
---------
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
27.POWER
返回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
37.LAST_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 WARD
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指定的内部数字格式返回一个VARCHAR2类型的值
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
58.USER
返回当前用户的名字
SQL> Select User From Dual;
USER
------------------------------
GAO
59.USEREVN
返回当前用户环境的信息,Opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
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')
------------------
0
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.AVG(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;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> Select Avg(All Sal) From Gao.Table3;
AVG(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.VARIANCE(DISTINCT|ALL)
求协方差
SQL> Select Variance(Sal) From Scott.Emp;
VARIANCE(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.HAVING
对分组统计再加限制条件
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 WARD 1250
30 MARTIN 1250
30 JAMES 950
1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。
2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。
3、高程序运行效率，优化应用程序，在SP编写Oracle存储过程中应该注意以下几点：
a) SQL的使用规范：
i. 尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。
ii. 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。
iii. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。
iv. 在Oracle存储过程开发中注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。
v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
vi. 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。
vii. 尽量使用“>=”，不要使用“>”。 viii. 注意一些or子句和union子句之间的替换
ix. 注意表之间连接的数据类型，避免不同类型数据之间的连接。
x. 注意Oracle存储过程中参数和数据类型的关系。
xi. 注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。
b) 索引的使用规范：
i. 索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。
ii. 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引
iii. 避免对大表查询时进行table scan，必要时考虑新建索引。
iv. 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。
v. 要注意索引的维护，周期性重建索引 ，重新编译Oracle存储过程。
c) tempdb的使用规范：
i. 尽量避免使用distinct、order by、group by、having、join、***pute，因为这些语句会加重tempdb的负担。
ii. 避免频繁创建和删除临时表，减少系统表资源的消耗。
iii. 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。
iv. 如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个Oracle存储过程的子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
v. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。
vi. 慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。
d) 合理的算法使用：
根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的Oracle存储过程方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplan on 等。
SQL定义：SQL是一种面向数据库的通用数据处理语言规范，能完成以下几类功能：提取查询数据，插入修改删除数据，生成修改和删除数据库对象，数据库安全控制，数据库完整性及数据保护控制。
SQL分类：
DDL—数据定义语言（CREATE，ALTER，DROP，DECLARE）
DML—数据操纵语言（SELECT，DELETE，UPDATE，INSERT）
DCL—数据控制语言（GRANT，REVOKE，COMMIT，ROLLBACK）
db2数据类型
CHAR()：定长字符串 最大长度为 254
VARCHAR()：变长字符 最大长度为 4000
SMALLINT：短整型数字 长度为 2 字节
INTEGER：整型数字 长度为 4 字节
REAL：单精度浮点 32 位近似值
DOUBLE：双精度浮点 64 位近似值
DECIMAL(m,n)：数字 精度为m小数位为n
DATE：日期时间
TIME：日期时间
TIMESTAMP：日期时间
DDL—数据库定义语言：直接提交的。
CREATE：用于创建数据库对象。
DECLARE：除了是创建只在过程中使用的临时表外，DECLARE语句和CREATE语句非常相似。唯一可以被声明的对象是表。并且必须放入用户临时表空间。
DROP：可以删除任何用CREATE（数据库对象）和DECLARE（表）创建的对象。
ALTER：允许修改某些数据库对象的信息。不能修改索引。
下面主要基于对象介绍基本的语法：
1、数据库：
创建数据库：CREATE DATABASE database-name [USING CODESET codeset TERRITORY territory]
注：代码页的问题。
删除数据库：drop database dbname
2、表：
创建新表：
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old
B：create table tab_new as select col1,col2… from tab_old definition only
修改表：
增加一个列:
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。添加主键：
Alter table tabname add primary key(col)
删除主键：
Alter table tabname drop primary key(col)
删除表：drop table tabname
3、表空间：
创建表空间：create tablespace tbsname pagesize 4k managed by database using (file ‘file’ size)
表空间加入容器：alter tablespace tablespace_name add(file 'filename' size)
注：该操作是不可逆的，加入容器后将不能将其删除，因此在加入的时候注意。
删除表空间：drop tablespace tbsname
4、索引：
创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
5、视图：
创建视图：create view viewname as select statement
删除视图：drop view viewname
注：视图唯一能修改的是引用类型列，改变列的范围。其他定义好了都不能修改。当视图基于的基表drop后，视图变为无效。
DML—数据库操纵语言，它不隐式地提交当前事务 ，是否提交视环境设定而定。
SELECT：从表中查询符合数据
注：条件中连接的问题，避免出现笛卡儿乘积
DELETE：删除已有表的数据
UPDATE：更新已有表的数据
INSERT：向已有表中插入数据
注：DELETE，UPDATE和INSERT是否直接提交取决与执行语句所在的环境。
在执行时注意事务日志满的情况。
1、select时，注意索引谓词和非索引谓词，尽量在有索引的列上使用索引谓词。
谓词类型 可索引 注 释
Col∝con Y ∝代表>,>=,=,<=,<,但是<>不是可索引的。
Col between con1 and con2 Y 在匹配系列中必须是最后的。
Col in list Y 仅对一个匹配列
Col is null Y
Col like ‘xyz%’ Y 模糊匹配%在后面。
Col like ‘%xyz’ N 模糊匹配%在前面。
Col1∝Col2 N Col1和col2来自同一个表
Col∝Expression N 例如：c1（c1+1）/2
Pred1 and Pred2 Y Pred1和Pred2都是可索引的，指相同索引的列
Pred1 or Pred2 N 除了（c1=a or c1=b）外，他可以被认为是c1 in（a，b）
Not Pred1 N 或者任何的等价形式：Not between,Not in,Not like等等。
使用索引的例子介绍：
<1>、单个表上索引查询的介绍 ：
A：select * from t1 where c1 = 10；
在c1列上无索引，如何检索
在c1列上有索引，如何检索
B：select * from t1 where c1 =10 and c2 between 5 and 10 and c3 like ‘A%’
仅在c1列上有索引
在c1,c2和c3列上有单独的索引
在c1，c2和c3列上有联合索引
此处可以填加索引匹配规则和高级规则，比较难理解！！
<2>、两个或多个表上索引查询的介绍：
(1)、使用循环嵌套法执行查询
A：select t1.c1,t1.c2,t2.c3,t2.c4 from t1,t2 where t1.c1=10 and t1.c2 =t2.c3
介绍查询的方法，看看哪列上最需要索引。
B：select t1.c1,t1.c2,t2.c3,t2.c4 from t1,t2 where t1.c1=10 and t2.c4 = 10 and t1.c2 =t2.c3
(2)、使用归并连接执行连接查询
例：select t1.c1,t1.c2,t2.c3,t2.c4 from t1,t2 where t1.c1=10 and t2.c4 = 10 and t1.c2 =t2.c3
(3)、三个以上的表进行连接查询采取的规则或者方法
（2）、GROUP BY：实现简单分组的功能，当用group by时，查询选择列中除了在group by中出现的和常量外，其他的列上要用分组函数。可以使用一些分组函数实现一些列不在group by中出现，min，max等。
（3）、HAVING的使用，对一些分组列进行条件判断。
（4）、ORDER BY子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值确定。
2、DELETE：从表中删除记录
语法格式：
DELETE FROM tablename WHERE (conditions)
3、INSERT：向表中插入记录
语法格式：
INSERT INTO tablename (col1,col2,…) VALUES (value1,value2,…);
INSERT INTO tablename (col1,col2,…) VALUES (value1, value2,…), (value1, value2,…),……
Insert不会等待任何程序，不会导致锁定。
4、UPDATE：
语法格式：
UPDATE tabname SET (col1=values1,col2=values2,…) WHERE (conditions);
注：update的速度比较慢，要在相应列上建立索引。
DCL—数据控制语言
GRANT—授予用户权限
REVOKE—撤消用户权限
COMMIT—提交事务，可以使数据库的修改永久化
ROLLBACK—回滚事务，消除上一个COMMIT命令后的所做的全部修改，使得数据库的内容恢复到上一个COMMIT执行后的状态.。
1、GRANT：所有着或者管理员把访问权限赋给其他用户
语法格式：
grant [all privileges|privileges,….] on tabname | viewname to [public|user,….]
2、REVOKE:取消某一用户的某中访问权限
语法格式：
Revoke [all privileges|privileges,….] on tabname | viewname from [public|user,….]
注：不能取消实例级别的用户的任何权限，他们不是通过grant授权的，是通过组实现的权限。
3、COMMIT:把事务中所做的修改永久化记录到数据库。
语法格式：
commit [work]
4、ROLLBACK:将上次提交以来所做的更改全部撤消。
语法格式：
Rollback [work]
高级sql简单介绍
一、查询间使用运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
二、外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
注：复合外连接按照从左到右的顺序执行连接，左边连接的结果集和右边连接
三、超级分组和移动函数
A：grouping sets：用来在单个sql中形成多级分组。
例：select company_id,node_id,count(customer_id) from customer group by grouping sets(company_id,node_id)
B：rollup：可以在单个数据库操作中形成多个分组。
例：select company_id,node_id,count(customer_id) from customer group by rollup(company_id,node_id)
注：rollup操作不是可交换的操作，指定用户组的顺序是很重要的。
C：cube： 生成分组表中分组的所有组合。
例：select company_id,node_id,count(customer_id) from customer group by cube(company_id,node_id)
D：over：移动函数可以帮助实现移动的数据分析
Select date,avg(qty) over(order by date rows between 1 preceding and 1 following) as values from sale
附录：常用函数介绍
1、类型转化函数：
转化为数字类型的：
decimal, double, Integer, smallint,real
Hex(arg):转化为参数的16进制表示。
转化为字符串类型的：
char, varchar
Digits(arg):返回arg的字符串表示法，arg必须为decimal。
转化为日期时间的：
date, time,timestamp
2、时间日期：
year, quarter, month, week, day, hour, minute ,second
dayofyear(arg):返回arg在年内的天值
Dayofweek(arg):返回arg在周内的天值
days(arg):返回日期的整数表示法，从0001-01-01来的天数。
midnight_seconds(arg):午夜和arg之间的秒数。
Monthname(arg):返回arg的月份名。
Dayname(arg):返回arg的星期。
3、字符串函数：
length,lcase, ucase, ltrim, rtrim
Coalesce(arg1,arg2….):返回参数集中第一个非null参数。
Concat (arg1,arg2):连接两个字符串arg1和arg2。
insert(arg1,pos,size,arg2):返回一个，将arg1从pos处删除size个字符，将arg2插入该位置。
left(arg,length):返回arg最左边的length个字符串。
locate(arg1,arg2,<pos>):在arg2中查找arg1第一次出现的位置，指定pos，则从arg2的pos处开始找arg1第一次出现的位置。
posstr(arg1,arg2):返回arg2第一次在arg1中出现的位置。
repeat(arg1 ,num_times):返回arg1被重复num_times次的字符串。
replace(arg1,arg2,arg3):将在arg1中的所有arg2替换成arg3。
right(arg,length):返回一个有arg左边length个字节组成的字符串。
space(arg):返回一个包含arg个空格的字符串。
substr(arg1,pos,<length>):返回arg1中pos位置开始的length个字符，如果没指定length，则返回剩余的字符。
4、数学函数：
Abs, count, max, min, sum
Ceil(arg):返回大于或等于arg的最小整数。
Floor(arg):返回小于或等于参数的最小整数。
Mod(arg1,arg2):返回arg1除以arg2的余数，符号与arg1相同。
Rand():返回1到1之间的随机数。
Power(arg1,arg2):返回arg1的arg2次方。
Round(arg1,arg2):四舍五入截断处理，arg2是位数，如果arg2为负，则对小数点前的数做四舍五入处理。
Sigh(arg):返回arg的符号指示符。-1,0,1表示。
truncate(arg1,arg2):截断arg1，arg2是位数，如果arg2是负数，则保留arg1小数点前的arg2位。
5、其他：
nullif(arg1,arg2)：如果2个参数相等，则返回null，否则，返回参数1
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-88888888转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('13088888888',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
16.ACOS
给出反余弦的值
SQL> Select Acos(-1) From Dual;
ACOS(-1)
---------
3.1415927
17.ASIN
给出反正弦的值
SQL> Select Asin(0.5) From Dual;
ASIN(0.5)
---------
.52359878
18.ATAN
返回一个数字的反正切值
SQL> Select Atan(1) From Dual;
ATAN(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;
COSH(20)
---------
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
27.POWER
返回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
37.LAST_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 WARD
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指定的内部数字格式返回一个VARCHAR2类型的值
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
58.USER
返回当前用户的名字
SQL> Select User From Dual;
USER
------------------------------
GAO
59.USEREVN
返回当前用户环境的信息,Opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
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')
------------------
0
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.AVG(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;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> Select Avg(All Sal) From Gao.Table3;
AVG(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.VARIANCE(DISTINCT|ALL)
求协方差
SQL> Select Variance(Sal) From Scott.Emp;
VARIANCE(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.HAVING
对分组统计再加限制条件
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 WARD 1250
30 MARTIN 1250
30 JAMES 950
1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。
2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。
3、高程序运行效率，优化应用程序，在SP编写Oracle存储过程中应该注意以下几点：
a) SQL的使用规范：
i. 尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。
ii. 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。
iii. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。
iv. 在Oracle存储过程开发中注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。
v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
vi. 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。
vii. 尽量使用“>=”，不要使用“>”。 viii. 注意一些or子句和union子句之间的替换
ix. 注意表之间连接的数据类型，避免不同类型数据之间的连接。
x. 注意Oracle存储过程中参数和数据类型的关系。
xi. 注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。
b) 索引的使用规范：
i. 索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。
ii. 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引
iii. 避免对大表查询时进行table scan，必要时考虑新建索引。
iv. 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。
v. 要注意索引的维护，周期性重建索引 ，重新编译Oracle存储过程。
c) tempdb的使用规范：
i. 尽量避免使用distinct、order by、group by、having、join、***pute，因为这些语句会加重tempdb的负担。
ii. 避免频繁创建和删除临时表，减少系统表资源的消耗。
iii. 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。
iv. 如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个Oracle存储过程的子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
v. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。
vi. 慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。
d) 合理的算法使用：
根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的Oracle存储过程方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplan on 等。
扫一扫
9068
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
