ORACLE Sql语句大全

ORALCE Sql语句大全

1.ROUND(m, n) 将m四舍五入，保留小数点后n位
SELECT ROUND(1234.5678, 2) from dual

2.sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1
SELECT SIGN(1)from dual;

3.initcap(char) 把每个字符串的第一个字符换成大写
SELECT INITCAP(‘mr.ecop’)from dual;

4.lower(char) 整个字符串换成小写
SELECT LOWER(‘MR.ecop’) from dual;

5.replace(char,str1,str2) 字符串中所有str1换成str2
SELECT REPLACE(‘Scott’, ‘S’, ‘Boy’)from dual

6.substr(char,m,n) 取出从m字符开始的n个字符的子串
SELECT SUBSTR(‘ABCDEF’, 2, 2)from dual

7.length(char) 求字符串的长度
SELECT LENGTH(‘ABC’)FROM DUAL

时间型函数
8.sysdate 当前日期和时间
SELECT SYSDATE from dual;

9.last_day 　本月最后一天
SELECT LAST_DAY(SYSDATE) from dual;

10.add_months(d,n)　当前日期d后推n个月
SELECT ADD_MONTHS(SYSDATE, 2) from dual

11.months_between(d,n) 日期d和n相差月数
SELECT “MONTHS_BETWEEN”(SYSDATE, “TO_DATE”(‘20020812’, ‘YYYYMMDD’))from dual

12.next_day(d,day) d后第一周指定day的日期
SELECT “NEXT_DAY”(SYSDATE, ‘Monday’)from dual

特殊格式日期类型函数
13.Q 季度,1-3月为第一季度
SELECT to_char(SYSDATE, ‘Q’)from dual

14.MM 　月份数
SELECT “TO_CHAR”(SYSDATE,’MM’) from dual

15.ww 当年第几周
SELECT “TO_CHAR”(SYSDATE,’WW’) from dual

16.DDD 当年第几天
SELECT “TO_CHAR”(SYSDATE,’DDD’) from dual

17.to_number() 将合法的数字字符串
SELECT “TO_NUMBER”(‘888777’) from dual

字符函数
18.* CONCAT (char1, char2)返回连接“char2”的“char1”*
SELECT “CONCAT”(“CONCAT”(ename, ’ is a ‘), job) FROM EMP

19.LPAD(char1,n [,char2])返回“char1”，左起由“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长，则函数返回“char1”的前“n”个字符
SELECT LPAD(ename,15,’*’) FROM emp;

20.* REPLACE(string, if, then)用 0 或其他字符代替字符串中的字符。“if”是字符或字符串，对于每个出现在“string”中的“if”，都用“then”的内容代替*
SELECT “REPLACE”(‘JACK AND JUE’, ‘J’, ‘BL’)FROM DUAL

21.RPAD(char1, n [,char2])返回“char1”，右侧用“char2”中的字符补充到“n”个字符长。如果 “char1”比“n” 长，则函数返回“char1”的前“n”个字符
SELECT “RPAD”(ename, 15, ‘*’) from emp

22.SUBSTR(string, start [,count])返回“string”中截取的一部分。该命令截取“string”的一个子集，从“start”位置开始，持续“count”个字符。如果我们不指定“count”，则从“start”开始截取到“string”的尾部。
SELECT “SUBSTR”(‘ABCDEFGIJKLM’, 3, 4)from dual

23.INSTR (string, set[, start[, occurrence] ] )该命令“string”中从“start”位置开始查找字符集合的位置，再查找“set”出现的第一次、第二次等等的“occurrence”（次数）。“start”的值也可以是负数，代表从字符串结尾开始向反方向搜索。该函数也用于数字和日期数据类型。
SELECT INSTR(‘aptech is aptech’,’s’,1,1) FROM DUAL;

连表查询
24。join on 连接表进行查询
select * from emp e join DEPT d on(e.DEPTNO = d.DEPTNO and d.DEPTNO = 20)

25.A left outer join B on(…)以A表记录显示为主,B表记录为补充.当A表记录在B表找不到对等记录时,B以NULL方式补充
SELECT E .ename,E .sal,E .deptno,D .dname FROM emp E LEFT OUTER JOIN dept D ON (E .deptno = D .deptno);

Where和having的区别
Where和having子句都用来筛选数据，但是where是针对原数据进行筛选，而having子句针对汇总的结果进行筛选。

运算符
1.ANY 运算符表示小于子查询中的任何一个值。
SELECT empno, ename, job,sal FROM emp
WHERE sal < ANY (SELECT sal FROM emp WHERE job = ‘CLERK’)
AND job <> ‘CLERK’;

2.多列子查询（子查询返回多少列，比较条件中对应多少列）
SELECT empno, ename, sal from EMP where (JOB,deptno)=(SELECT job,deptno from emp where EMPNO=7788)

3.模糊查询：（%：代表0个或多个任意字符。 _ ：代表一个任意字符。）
SELECT * FROM emp WHERE ename LIKE ‘S%’;
SELECT * FROM emp WHERE ename LIKE ‘_A%’;

4.判断空值null
SELECT ename, mgr from emp where mgr is null

rollup使用
Rollup意思有”卷起,汇总”的意思,他可以在使得在其括号中的字段,
按从右到左的顺序分别group后显示,类似我们用多个group by 语句,然后union all起来
Select A,B,C,sum(n) from test group by A,B,C
union all
Select A,B,null,sum(n) from test group by A,B
union all
Select A,null,null,sum(n) from test group by A
union all
Select null,null,null,sum(n) from test
等同于
SELECT a, b , c ,”SUM”(n)total from testgroup GROUP BY rollup(a,b,c)
查看结果
正常查询结果

使用rollup后

Oracle 11g 行转列函数 pivot
聚合列取值。需要告诉pivot函数进行转列的过程中，聚合操作的函数和处理对象；
行转列标准。依据那个列进行行转列；
列转行取值。因为要将数据行取值转成列，我们需要告诉Oracle那些取值成列，并且这些取值成列的过程中，列顺序是如何的；
SELECT * from “TEST” pivot
(
sum(empno) –聚合操作函数
for STYPE –行转列标准
in (‘B’,’C’,’D’,’E’,’F’,’G’) –行转列列取值和顺序
)
查看结果
正常查询结果

使用pivot查询结果

wm_concat的使用
该函数可以把列值以”,”号分隔起来,
1）行转列
select wm_concat(name) from test;
结果：a,b,c,d,e
2) 把结果里的逗号替换成“|”
select replace(wm_concat(name), ‘,’ , ‘|’) from test;
结果：a|b|c|d|e
3) 按ID分组合并name
select id, wm_concat(name) name from test group by id;
结果：1 a,b,c
2 d,e
(注意：此函数在oracle 12c中被删除)

COALESCE函数
COALESCE(EXPR1,EXPR2,EXPR3…EXPRn)
从左往右数，遇到第一个非null值，则返回该非null值。COALESCE里的所有参数类型必须保持一致
SELECT COALESCE(COMM, 1) FROM EMP

decode函数 使用
1）decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
2）decode(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1、值2、值3也可以是表达式，这个函数使得某些sql语句简单了许多
<1> 比较大小
select decode (sign(变量1-变量2),-1,变量1,变量2)from dual;–取较小值

=： :=和变量绑定
‘:=’是赋值语句 如 l_name :=’sky’
‘:’是变量绑定
变量绑定 是指在sql语句的条件中使用变量而不是常量。

oracle通配符和运算符
用于where比较条件的有：
　　等于：=、<、<=、>、>=、<>
　　包含：in、not in exists、not exists
　　范围：between…and、not between….and
　　匹配测试：like、not like
　　Null测试：is null、is not null
　　布尔链接：and、or、not
通配符：
　　在where子句中，通配符可与like条件一起使用。在Oracle中：
　　%(百分号)： 用来表示任意数量的字符，或者可能根本没有字符。
　　_(下划线)： 表示确切的未知字符。
　　?(问号)： 用来表示确切的未知字符。
　　#(井号)： 用来表示确切的阿拉伯数字，0到9。
　　[a-d]：用来表示字符范围，在这里是从a到d。
　　
单引号(‘)：在Oracle中，应该只使用单引号将文本和字符和日期括起来，不能使用引号（包括单双引号）将数字括起来。
　　
双引号(“)：在Oracle中，单双引号含义不同。双引号被用来将包含特定字符或者空格的列别名括起来。双引号还被用来将文本放入日期格式。
　　
撇号(‘)：在Oracle中，撇号也可以写成彼此相邻的两个单引号。为了在供应商名字中间查找所有带撇号的供应商名字，可以这样编写代码：
select * from l_suppliers where supplier_name like ‘%”%’
　　
&符号：在Oracle中,&符号常用来指出一个变量。例如，&fox是一个变量，稍微有点不同的一种&& fox。每当&fox出现在Oracle脚本中时，都会要求您为它提供一个值。而使用&&fox，您只需要在& &fox第一次出现时为它提供变量值。如果想将&符号作为普通的符号使用，则应该关闭这个特性。要想关闭这个特性，可以运行以下的命令： set define off ，这是一个SQLplus命令，不是一个SQL命令。SQLplus设置了SQL在Oracle中运行的环境。
　　
双竖线（||）：Oracle使用双竖线表示字符串连接函数。
　　
星号(*)：select 意味着选择所有的列，count（*）意味着计算所有的行，表示通配符时，表示0个或任意多个字符。
　　
正斜杠(/)：在Oracle中，用来终止SQL语句。更准确的说，是表示了“运行现在位于缓冲区的SQL代码”。正斜杠也用作分隔项。
　　
不等于：有多种表达方式：!=、^=、<>、not xxx=yyy、not(xxx=yyy)

oracle 合并两表数据

select nvl(A.NAME,B.NAME), nvl (A.KEMU1,0),nvl(B.KEMU2,0)
from A full outer join B on A.NAME=B.NAME;