Oracle 语言分类 数据类型 数据类型转换 常用函数 集合操作 子查询

 SQL分类

SQL(Structure Query Language)语言是数据库的核心语言。

SQL语言共分为四大类：数据定义语言DDL，数据操纵语言DML，数据查询语言DQL，数据控制语言DCL。

1. 数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
DDL操作是隐性提交的！不能rollback 

2 .数据操纵语言DML
数据操纵语言DML主要有三种形式：
1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE

3. 数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

4. 数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
1) GRANT：授权。

 数据类型

-- 1、字符串类型
-- 定长类型：指输入的字段值小于该字段的限制长度，但是实际存储数据时，会先自动向右补足空格
-- 优点：存储/查询效率高
-- 代表：CHAR类型 CHAR(size [BYTE | CHAR])，默认按字节存储，如果指定按字符存储就按字符存储
-- 举例：CHAR_OLD默认，CHAR_NEW指定为字符存储
CREATE TABLE CHAR_TEST(
       CHAR_OLD CHAR(10),
       CHAR_NEW CHAR(10 CHAR)
);
-- 插入
INSERT INTO CHAR_TEST(CHAR_OLD,CHAR_NEW) SELECT 'ABCDEFGHIJ','我要五个字我要五个字' FROM DUAL;
COMMIT;
SELECT * FROM CHAR_TEST;
-- 反过来再试试？
INSERT INTO CHAR_TEST(CHAR_OLD,CHAR_NEW) SELECT '我要五个字我要五个字','ABCDEFGHIJ' FROM DUAL;
-- CHAR_OLD的值太大（实际值：31，最大值10），因为在oracle中，如果NLS_CHARACTERSET为AL32UTF8，一个汉字占用三个字节

-- 变长类型：指输入的字段值小于该字段的限制长度，不会自动向右补足空格至最大长度
-- 优点：节省数据块空间
-- 代表：VARCHAR和VARCHAR2，两者没有太大区别，VARCHAR2为ORACLE特有的数据类型，一般建议使用VARCHAR2
-- 举例：CHAR_OLD默认，CHAR_NEW指定为字符存储

-- 2、数字类型
-- NUMBER(P,S)：P精度，即长度，S为小数位数
-- INTEGER：NUMBER的子类型，相当于NUMBER(38,0),用于存储整数，如果插入小数，会四舍五入
-- 举例
create table integer_test(
       integer_col integer,
       number_col number(38,2)
);
insert into integer_test(integer_col,number_col) select 11,11 from dual;
insert into integer_test(integer_col,number_col) select 11.11,11.11 from dual;
insert into integer_test(integer_col,number_col) select 11.51,11.51 from dual;
commit;
select * from integer_test;
--------------------- 
-- FLOAT：NUMBER的子类型，浮点型
-- FLOAT(n)：n表示精度,但是他的长度不好控制，建议用NUMBER或者DOUBLE
alter table integer_test add float_col float(7);

insert into integer_test(integer_col,number_col,float_col) select 11.11,11.11,11.11 from dual;
insert into integer_test(integer_col,number_col,float_col) select 11.51,11.51,11.51 from dual;
select * from integer_test;
--------------------- 
-- 3、日期类型
-- DATE：日期数据类型可以存储日期和时间信息
-- TIMESTAMP：时间戳可以包含小数秒，带小数秒的时间戳在小数点右边最多可以保留9位
-- DATE类型可以加一个数字,这个数字代表的是天数，表示多少天后的日期
-- 举例
select sysdate + 30 as "30天后的日期" from dual;
-- DATE类型可以相减，表示两个时间差
-- 举例
select trunc((sysdate - hiredate)/365, 2) as "工作年限" from emp;
select to_char((sysdate - hiredate)/365, '9999.99') as "工作年限" from emp;
-- PS：trunc函数，保留几位小数，但是如果最后是0，则省略，如果要求格式工整，可用to_char(char,'99.99')
--------------------- 
-- 四、LOB类型
-- CLOB：用于存储单字节和多字节字符数据
-- BLOB：用于存储二进制数据

-- 五、RAW和LONG RAW类型
-- 1、LONG：它存储变长字符串，最多达2G的字符数据（2GB是指2千兆字节， 而不是2千兆字符）
-- 与VARCHAR2 或CHAR 类型一样，存储在LONG 类型中的文本要进行字符集转换
-- ORACLE建议开发中使用CLOB替代LONG类型。支持LONG 列只是为了保证向后兼容性。CLOB类型比LONG类型的限制要少得多。
-- 官方给出的具体限制如下
--    一个表中只有一列可以为LONG型。
--    LONG列不能定义为主键或唯一约束，
--    不能建立索引
--    LONG数据不能指定正则表达式。
--    函数或存储过程不能接受LONG数据类型的参数。
--    LONG列不能出现在WHERE子句或完整性约束（除了可能会出现NULL和NOT NULL约束）

-- 2、RAW：用于存储二进制或字符类型数据，变长二进制数据类型

-- 六、ROWID和UROWID
--------------------- 

数据类型转换

三大类型转换
oracle中三大类型与隐式数据类型转换 
（1）varchar2变长/char定长–>number，例如：’123’->123 
（2）varchar2/char–>date，例如：’25-4月-15’->’25-4月-15’ 
（3）number—->varchar2/char，例如：123->’123’ 
（4）date——>varchar2/char，例如：’25-4月-15’->’25-4月-15’

oracle如何隐式转换： 
1）=号二边的类型是否相同 
2）如果=号二边的类型不同，尝试的去做转换 
3）在转换时，要确保合法合理，否则转换会失败，例如：12月不会有32天，一年中不会有13月
--------------------- 

1.varchar转为number，用 to_number（列名）： 
select to_number(t.create_user) from 表名 t; 
2.number转为varchar，用 to_char（列名）： 
select to_char(t.user_role_id) from 表名 t; 
3.date转为varchar类型，用 to_char（列名,想要的日期格式）： 
select to_char(t.create_date,’yyyy-mm-dd’) 重点内容from 表名 t 
4.varchar类型转date,用 to_date（列名,想要的日期格式）： 
insert into 表名 values(1,’lili’,to_date(‘2012-11-11’,’yyyy-mm-dd’)); 
5.CAST——类型转换 
select cast(” as number) as a, ‘01’ as b from dual union 
select 2 as a, ‘02’ as b from dual;
--------------------- 
--1、查询1980年12月17日入职的员工（方式一：日期隐示式转换）
select * from emp where hiredate = '17-12月-80';

--2、使用to_char(日期，'格"常量"式')函数将日期转成字符串，显示如下格式：2018 年 02 月 26 日 星期二
select to_char(sysdate,'yyyy" 年 "mm" 月 "dd" 日 "day') from dual;

--3、使用to_char(日期，'格式')函数将日期转成字符串，显示如格式：2018-02-06今天是星期二 15:59:15
select to_char(sysdate,'yyyy-mm-dd"今天是"day hh24:mi:ss') from dual;
--或  显示如格式：2018-02-06今天是星期二 3:59:55 下午
select to_char(sysdate,'yyyy-mm-dd"今天是"day HH12:MI:SS AM') from dual;

--4、使用to_char(数值，'格式')函数将数值转成字符串，显示如下格式：$1,234
select to_char(1234,'$9,999') from dual;

--使用to_char(数值，'格式')函数将数值转成字符串，显示如下格式：￥1,234
select to_char(1234,'L9,999') from dual;

--5、使用to_date('字符串','格式')函数，查询1980年12月17日入职的员工（方式二：日期显式转换）
select * from emp where hiredate = to_date('1980年12月17日','yyyy"年"mm"月"dd"日"');
--或
select * from emp where hiredate = to_date('1980#12#17','yyyy"#"mm"#"dd');
--或
select * from emp where hiredate = to_date('1980-12-17','yyyy-mm-dd');

--6、使用to_number('字符串')函数将字符串‘123’转成数字123
select to_number('123') from dual;


--注意：
select '123' + 123 from dual;  --246
select '123' || 123 from dual; --123123
--------------------- 
SYSDATE	2009-6-16 15:25:10	　
TRUNC(SYSDATE)	2009-6-16	　
TO_CHAR(SYSDATE,'YYYYMMDD')	20090616	到日
TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS')	20090616 15:25:10	到秒
TO_CHAR(SYSTIMESTAMP,'YYYYMMDD HH24:MI:SS.FF3')	20090616 15:25:10.848	到毫秒
TO_CHAR(SYSDATE,'AD')	公元	　
TO_CHAR(SYSDATE,'AM')	下午	　
TO_CHAR(SYSDATE,'BC')	公元	　
TO_CHAR(SYSDATE,'CC')	21	　
TO_CHAR(SYSDATE,'D')	3	老外的星期几
TO_CHAR(SYSDATE,'DAY')	星期二	星期几
TO_CHAR(SYSDATE,'DD')	16	　
TO_CHAR(SYSDATE,'DDD')	167	　
TO_CHAR(SYSDATE,'DL')	2009年6月16日 星期二	　
TO_CHAR(SYSDATE,'DS')	2009-06-16	　
TO_CHAR(SYSDATE,'DY')	星期二	　
TO_CHAR(SYSTIMESTAMP,'SS.FF3')	10.848	毫秒
TO_CHAR(SYSDATE,'FM')	　	　
TO_CHAR(SYSDATE,'FX')	　	　
TO_CHAR(SYSDATE,'HH')	03	　
TO_CHAR(SYSDATE,'HH24')	15	　
TO_CHAR(SYSDATE,'IW')	25	第几周
TO_CHAR(SYSDATE,'IYY')	009	　
TO_CHAR(SYSDATE,'IY')	09	　
TO_CHAR(SYSDATE,'J')	2454999	　
TO_CHAR(SYSDATE,'MI')	25	　
TO_CHAR(SYSDATE,'MM')	06	　
TO_CHAR(SYSDATE,'MON')	6月 	　
TO_CHAR(SYSDATE,'MONTH')	6月 	　
TO_CHAR(SYSTIMESTAMP,'PM')	下午	　
TO_CHAR(SYSDATE,'Q')	2	第几季度
TO_CHAR(SYSDATE,'RM')	VI  	　
TO_CHAR(SYSDATE,'RR')	09	　
TO_CHAR(SYSDATE,'RRRR')	2009	　
TO_CHAR(SYSDATE,'SS')	10	　
TO_CHAR(SYSDATE,'SSSSS')	55510	　
TO_CHAR(SYSDATE,'TS')	下午 3:25:10	　
TO_CHAR(SYSDATE,'WW')	24	　
TO_CHAR(SYSTIMESTAMP,'W')	3	　
TO_CHAR(SYSDATE,'YEAR')	TWO THOUSAND NINE	　
TO_CHAR(SYSDATE,'YYYY')	2009	　
TO_CHAR(SYSTIMESTAMP,'YYY')	009	　
TO_CHAR(SYSTIMESTAMP,'YY')	09

TO_CHAR 是把日期或数字转换为字符串
TO_DATE 是把字符串转换为数据库中得日期类型转换函数
TO_NUMBER 将字符转化为数字

 TO_CHAR 
使用TO_CHAR函数处理数字 
TO_CHAR(number, '格式') 
TO_CHAR(salary,’$99,999.99’); 
使用TO_CHAR函数处理日期 
TO_CHAR(date,’格式’); 

 TO_NUMBER 
使用TO_NUMBER函数将字符转换为数字 
TO_NUMBER(char, '格式') 

 TO_DATE 
使用TO_DATE函数将字符转换为日期 
TO_DATE(char, '格式') 

 数字格式格式 
9 代表一个数字 
0 强制显示0 
$ 放置一个$符 
L 放置一个浮动本地货币符 
. 显示小数点 
, 显示千位指示符 

 日期格式 
格式控制 描述 
YYYY、YYY、YY 分别代表4位、3位、2位的数字年 
YEAR 年的拼写 
MM 数字月 
MONTH 月的全拼 
MON 月的缩写 
DD 数字日 
DAY 星期的全拼 
DY 星期的缩写 
AM 表示上午或者下午 
HH24、HH12 12小时制或24小时制 
MI 分钟 
SS 秒钟 
SP 数字的拼写 
TH 数字的序数词 

“特殊字符” 假如特殊字符 
HH24:MI:SS AM 15:43:20 PM 

日期例子：
SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL
SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL
SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL
SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL
SELECT TO_DATE('2006', 'YYYY') FROM DUAL

日期说明：
当省略HH、MI和SS对应的输入参数时，Oracle使用0作为DEFAULT值。如果输入的日期数据
忽略时间部分，Oracle会将时、分、秒部分都置为0，也就是说会取整到日。

同样，忽略了DD参数，Oracle会采用1作为日的默认值，也就是说会取整到月。

但是，不要被这种“惯性”所迷惑，如果忽略MM参数，Oracle并不会取整到年，取整到当前月。

注意：
1.在使用Oracle的to_date函数来做日期转换时，可能会直觉地采用“yyyy-MM-dd HH:mm:ss”的格式
作为格式进行转换，但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。
如：select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual;原因是SQL
中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。select 
to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
2.另要以24小时的形式显示出来要用HH24
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;//mi是分钟
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm会显示月份

常用函数 

1、数值型常用函数



　函数　　返回值　　　　　　　　　　　　样例　　　　　　　　　　　显示
ceil(n) 大于或等于数值n的最小整数　　select ceil(10.6) from dual; 11

floor(n) 小于等于数值n的最大整数　 select floor(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;

last_day 　本月最后一天 select last_day(sysdate) from dual;

add_months(d,n)　当前日期d后推n个月 select add_months(sysdate,2) from dual;

months_between(d,n) 日期d和n相差月数 
select months_between(sysdate,to_date('20020812','YYYYMMDD')) from dual;

next_day(d,day) d后第一周指定day的日期 select next_day(sysdate,'Monday') from dual;

day 格式　　有　　'Monday' 星期一　　'Tuesday' 星期二

'wednesday' 　星期三   'Thursday' 星期四    'Friday' 星期五

'Saturday' 星期六   'Sunday' 星期日

4、特殊格式的日期型函数

Y或YY或YYY 年的最后一位，两位，三位 select to_char(sysdate,'YYY') from dual;

Q 季度,1-3月为第一季度　　　　select to_char(sysdate,'Q') from dual;

MM 　月份数　　　　　　　　　　　select to_char(sysdate,'MM') from dual;

RM 月份的罗马表示 select to_char(sysdate,'RM') from dual; IV

month 用9个字符表示的月份名 select to_char(sysdate,'month') from dual;

ww 当年第几周　　　　　　　　　select to_char(sysdate,'ww') from dual;

w 本月第几周　　　　　　　　　select to_char(sysdate,'w') from dual;

DDD 当年第几天,一月一日为001 ,二月一日032 select to_char(sysdate,'DDD') from dual;

DD 当月第几天 select to_char(sysdate,'DD') from dual;

D 周内第几天 select to_char(sysdate,'D') from dual; 如　sunday

DY 周内第几天缩写　　　　　　　select to_char(sysdate,'DY') from dual; 如　sun

hh12 12小时制小时数　　　　　　　select to_char(sysdate,'hh12') from dual;

hh24 24小时制小时数　　　　　　　select to_char(sysdate,'hh24') from dual;

Mi 分钟数　　　　　　　　　　　 select to_char(sysdate,'Mi') from dual;

ss 秒数　　　　　　　　　　　　 select to_char(sysdate,'ss') from dual;

select to_char(sysdate,'YYYY-MM-DD HH:24:mi:ss') from dua;

to_number() 将合法的数字字符串 select to_number('88877') from dual; 88877

to_char() 将数字转换为字符串　 select to_char(88877) from dual; '88877'

set serveroupt on;

dbms_output.put_line('hello world')

set heading off 由于正在创建数据文件，不需要表头

set pagesize 0 不需要分页

set linesize 80 设置行的最大尺寸

set echo off 告诉sql plus 在执行语句时，不要回显语句

set feedback off 禁止sql plus 显示有多少满足查询的行被检索到

col sales format 999,999,999

append 添加文本到当前行尾

change/old/new/ 在当前行用新的文本代替旧的文本

　change/text 从当前行删除ｗｅｎｂ

　del 删除当前行

　input text 　在当前行之后添加一行

　list 显示缓冲区中的所有行

　list n 显示缓冲区中的第n行

list m n 显示m到n

5、字符函数


--------------------------------------------------------------------------------

字符函数主要用于修改字符列。这些函数接受字符输入，返回字符或数字值。Oracle 提供的一些字符函数如下。

1. CONCAT (char1, char2)

返回连接“char2”的“char1”。

示例  SELECT CONCAT( CONCAT(ename, ' is a '), job) FROM emp;

2. INITCAP(string)

将“string”的字符转成大写。

示例 Select INITCAP(ename) from emp;

3. LOWER (string)

将“string”转成小写。

示例 Select LOWER(ENAME) from emp;

4. LPAD(char1,n ,char2)

返回“char1”，左起由“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长，则函数返回“char1”的前“n”个字符。

示例 SELECT LPAD(ename,15,'*') FROM emp;

5. LTRIM(string,trim_set)

从左边删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set” 是我们要去掉的字符的集合。

示例 SELECT LTRIM('abcdab','a') FROM DUAL;

6. REPLACE(string, if, then)

用 0 或其他字符代替字符串中的字符。“if”是字符或字符串，对于每个出现在“string”中的“if”，都用“then”的内容代替。

示例 SELECT REPLACE('JACK and JUE','J','BL') FROM DUAL;

7. RPAD(char1, n ,char2)

返回“char1”，右侧用“char2”中的字符补充到“n”个字符长。如果 “char1”比“n” 长，则函数返回“char1”的前“n”个字符。

示例 SELECT RPAD(ename,15,'*') FROM emp;

8. RTRIM(string,trim_set)

从右侧删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set” 是我们要去掉的字符的集合。

示例 SELECT RTRIM('abcdef', 'f') FROM DUAL;

9. SOUNDEX(char)

返回包含“char”的表意字符的字符串。它允许比较英语中拼写不同而发音类似的字。

示例 SELECT ename FROM emp

WHERE SOUNDEX(ename) = SoUNDEX('SMYTHE');

10. SUBSTR(string, start ,count)

返回“string”中截取的一部分。该命令截取“string”的一个子集，从“start”位置开始，持续“count”个
字符。如果我们不指定“count”，则从“start”开始截取到“string”的尾部。

示例 SELECT SUBSTR('ABCDEFGIJKLM',3,4) FROM DUAL;

11. TRANSLATE(string, if, then)

“if”中字符的位置，并检查“then”的相同位置，然后用该位置的字符替换 “string”中的字符。

示例 SELECT TRANSLATE(ename,'AEIOU', 'XXXXX') FROM emp;

12. UPPER(string)

返回大写的“string”。

示例 SELECT UPPER('aptech computer education') FROM dual;

13. ASCII(string)

该命令是“American Standard Code for Information Interchange”的缩写。它是使用数字表示
可打印字符的基本规则。该函数返回 “string”中第一个（最左边）字符的 ASCII 值。

示例 SELECT ASCII('APTECH') from dual;

14. INSTR (string, set, start, occurrence)

该命令“string”中从“start”位置开始查找字符集合的位置，再查找“set”出现的第一次、第二次
等等的“occurrence”（次数）。“start”的值也可以是负数，代表从字符串结尾开始向反方向搜索。
该函数也用于数字和日期数据类型。

示例 SELECT INSTR('aptech is aptech','ap',1,2) FROM DUAL;

15. LENGTH(string)

返回“string”的长度值。

示例 SELECT ename, LENGTH(ename) FROM emp 

WHERE empno = 7698;
--------------------- 
1.模糊查询like
%表示零或多个字符
_表示一个字符
对于特殊符号可以使用ESCAPE标识符来查找
select * from emp where ename like '%*_%'escape'*';
上面的escape表示*后面的字符不被当作特殊字符处理，就是普通的'_'符。

2.字符函数的使用
Upper 将字符串转为大写
	select Upper('aabbcc') from dual;
	select * from emp where ename=UPPER('smith');
	
	Lower 将字符串转为小写
	select Lower('AABBCC') from dual;
	select * from emp where ename=LOWER('SMITH');
	
	initcap 返回字符串并将首字母转为大写
	select initcap(ename) from emp;
	
	Concat 将后一个字符串连接到前一个字符串的尾部
	select concat('a','b') from dual;
	select 'a' || 'b' from dual;
	
	Substr 返回截取的字符串
	Substr(字符串,截取开始位置,截取长度)
	substr('Hello World',0,1) --返回结果为 'H'  *从字符串第一个字符开始截取长度为1的字符串
	substr('Hello World',1,1) --返回结果为 'H'  *0和1都是表示截取的开始位置为第一个字符
	substr('Hello World',2,4) --返回结果为 'ello'
	substr('Hello World',-3,3) --返回结果为 'rld' *用负数则从右边开始数第几个位置，截取
仍然是从左往右截取
	测试：
	select substr('Hello World',-3,3) value from dual;
	
	length 返回字符串的长度
	select length(ename) from emp;
	
	replace 替换字段中指定的字符串
	replace(原字段，“原字段旧内容“,“原字段新内容“,)
	select replace(ename,'a','A') from emp;
	
	instr 在一个字符串中查找指定的字符,返回被查找到的指定的字符的位置。 
	instr(sourceString,destString,start,appearPosition) 
	其中sourceString代表源字符串； 
	destString代表要从源字符串中查找的子串； 
	start代表查找的开始位置，这个参数可选的，默认为1； 
	appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 
    默认为1 	如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计算。 
	返回值为：查找到的字符串的位置。 
	select instr('Hello World','or') from dual; --返回结果:8 indexOf
	
	Lpad 
	lpad( string, padded_length, pad_string )
	string
　　准备被填充的字符串；
　　padded_length
　　填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，
lpad函数将会把字符串截取成从左到右的n个字符;
　　pad_string
　　填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数
将会在string的左边粘贴空格。
	lpad('Smith',10,'*')  --返回结果:*****Smith
	
	Rpad
	rpad(string,padded_length,pad_string)
	string
　　被填充的字符串
　　padded_length
　　字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串
截取成从左到右的n个字符;
　　pad_string
　　是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的
右边粘贴空格。
	Rpad('Smith',10,'*')  --返回结果为 Smith*****
	
	Trim 过滤掉首尾空格
	select trim(' Mr Smith ') from dual;--返回结果:Mr Smith

3.数值函数
Round
	将参数n按照n2指定的小数位进行四舍五入.不指定n2时默认n2为0,即近似到个位.如果第2个参数为
负数时将对小数点左边的数作四舍五入,回到左面的|n|+1位.
	select round(345.678,2) from dual; --四舍五入到百分位(小数点后第2位)
	select round(412,-2) from dual; --返回结果:400
	select round(412.313,-2) from dual; --返回结果:400
	
	Mod(n,n2)
	返回参数n除以参数n2时的余数.
	select mod(10,2) from dual; -- 返回结果:0
	
	TRUNC(n,n2)	函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数
前或后的部分做相应舍入选择处理，而统统截去。
	第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。与取整类似，比如
参数为1即取整到十分位，如果是-1，则是取整到十位，以此类推。 
	TRUNC（89.985，2）=89.98
　　TRUNC（89.985）=89
　　TRUNC（89.985，-1）=80

4.日期函数
MONTHS_BETWEEN(DATE1,DATE2) 即MONTHS_BETWEEN(日期1，日期2)	函数返回两个日期之间的月份数。
如果两个日期月份内天数相同，或者都是某个月的最后一天，返回一个整数.
	否则，返回数值带小数，以每天1/31月来计算月中剩余天数。如果日期1比日期2小 ，返回值为负数。
	例：months_between(to_date('1999.11.29','yyyy.mm.dd'), to_date('1998.11.29','yyyy.mm.dd')) 
    返回  12
	注：两个参数均为同样月份的29号，所以返回一整数。
	months_between(to_date('1999.11.29','yyyy.mm.dd'), to_date('1998.12.24','yyyy.mm.dd'))
    返回   13.16129
	注：两个参数表示的日期不是同一天，所以返回带小数的值。
	
	add_months(x,y)或者add_months(times,months)函数
	这个函数用于计算在时间x之上机上Y个月后的时间值，要是Y的值为负数的话就是在这个时间点之间
的时间值（这个时间-Y个月）。
	如 select add_months(sysdate,-6) from dual; 
	该查询的结果是当前时间半年前的时间
	select add_months(sysdate,6) from dual; 
	该查询的结果是当前时间半年后的时间
	
	NEXT_DAY(date,char) 
	date参数为日期型， 
	char:为1~7或Monday/Mon~Sunday/  1-星期天 2-星期一 依次类推
	select next_day(sysdate,'星期日') from dual;  --返回结果：2016.12.4 16:01:52
	select next_day(sysdate,1) from dual;  --和上面相同结果
	
	LAST_DAY 函数返回指定日期对应月份的最后一天。
	select last_day(to_date('2016.11.29','yyyy.mm.dd')) from dual;
	返回结果：2016.11.30
	select last_day(to_date('2017.2.2','yyyy.mm.dd')) from dual;
	返回结果：2017.2.28

5.转换函数
to_char(type, text)
	type:可以为timestamp、int、float、numeric格式
	text：转换的模板。如：'yyyy-mm-dd hh24:mi:ss'
	
	to_date(type, text)
	用法同上相反
	select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual; 
	注：很多Java程序员也许会直接的采用“yyyy-MM-dd HH:mm:ss”的格式作为格式进行转换，
但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。
	原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。
	
	to_number 将字符串转换为数字数据类型
	select to_number('12')+to_number('13') from dual;
	返回结果:25

6.通用函数
sign(n)
	取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
 
	NVL(expr1,expr2)
	含义是：如果oracle第一个参数为空(null)那么显示第二个参数的值，如果第一个参数的值不为空，
则显示第一个参数本来的值。
	注：Oracle会把''转化为null。但是'' 不等于' '。
	
	nvl2(expr1,expr2,expr3)
	如果 expr1 不是 null 值，则 nvl 函数返回 expr2 ，否则就返回 expr3 。参数可以返回
任何数据类型的值，但是 expr2 和 expr3 不能是 LONG 型的数据类型。
	说明：若 expr2 和 expr3 的数据类型不同：
   	 (1)若 expr2 是字符数据，则Oracle数据库在比较之前就会把 expr3 转换成 expr2 的数据类型
除非 expr3 是null。在 这种情况下，隐式数据转换是不必要的。Oracle数据库返回 VARCHAR2 数据类型
到 expr2 的字符集。
    	(2) 若 expr2 是数值型，则Oracle数据库决定哪个参数具有最高数值优先级，并把另一个参数的
数据类型隐式转换成这种数据类型，并返回这种数据类型的数据。
	
	NULLIF（表达式1，表达式2）
	如果表达式1和表达式2相等则返回空值，如果表达式1和表达式2不相等则返回表达式1的结果。
	注意：表达式1和表达式2应该是相同数据类型或能隐含转换成相同数据类型，表达式1不能用
字符null,但''不报错。
	
	COALESCE(表达式1,表达式2,...,表达式n)
	n>=2,此表达式的功能为返回第一个不为空的表达式，如果都为空则返回空值。
	注意：所有表达式必须为同一类型或者能转换成同一类型。
	
	case 表达式和java中的switch 类似
	select empno,ename,sal,case deptno 
									when 10 then '财务部'
									when 20 then '开发部'
									when 30 then '行政部'
							else '未知部门'
							end dept_name
	from emp;	
 
	DECODE(value,if1,then1,if2,then2,if3,then3,...,else)
	表示如果value等于if1时，DECODE函数的结果返then1,...,如果不等于任何一个if值，则返回else。
	可以用函数或表达式来替代value，if，then，else从而作出一些更有用的比较。

集合操作 

--Oracle中的复合查询
复合查询:包含集合运算(操作)的查询
常见的集合操作有:
union：    两个查询的并集(无重复行、按第一个查询的第一列升序排序)
union all：两个查询的并集(有重复行)
intersect：两个查询的交集(无重复行、按第一个查询的第一列升序排序)
minus：    两个查询的差集(无重复行、按第一个查询的第一列升序排序),取第一张表有而第二张表没有的所有记录

由于union、intersect、minus存在排序，故而对sql性能的影响很大，建议少用。

--测试
create table t1 (id number,name varchar2(20));
create table t2 (id number,name varchar2(20));
insert into t1 values(1,'表1');
insert into t1 values(2,'表1');
insert into t1 values(3,'表1');
insert into t1 values(4,'表1');
insert into t1 values(5,'表1');
insert into t2 values(3,'表2');
insert into t2 values(4,'表2');
insert into t2 values(5,'表2');
insert into t2 values(6,'表2');
insert into t2 values(7,'表2');

1、UION操作 
select empno, ename, job, deptno from emp where deptno=10 
union 
select empno, ename, job, deptno from emp; 
将两个查询结果合并在了一起，相同的结果不重复显示。

2、UION ALL操作 
select empno, ename, job, deptno from emp where deptno=10 
union all 
select empno, ename, job, deptno from emp; 
将两个查询结果合并在了一起，所有的重复数据都会进行显示。

3、INTERSECT操作 
select empno, ename, job, deptno from emp where deptno=10 
intersect 
select empno, ename, job, deptno from emp; 
返回相同的部分，属于交集操作。

4、MINUS操作 
select empno, ename, job, deptno from emp 
minus 
select empno, ename, job, deptno from emp where deptno=10; 
返回了两个集合的差集，用第一个查询结果减去第二个查询结果。

注：进行多表查询的数据集合操作时，多个查询结果返回的结构必须相同。
--------------------- 
Oracle查询语句

select * from scott.emp ;



1.--dense_rank()分析函数（查找每个部门工资最高前三名员工信息）

select * from (select deptno,ename,sal,dense_rank() over(partition by deptno 
order by 
sal desc) a from scott.emp) where a<=3 order by deptno asc,sal desc ;

结果：



--rank()分析函数(运行结果与上语句相同)

select * from (select deptno,ename,sal,rank() over(partition by deptno 
order by sal 
desc) a from scott.emp ) where a<=3 order by deptno asc,sal desc ;

结果：



--row_number()分析函数（运行结果与上相同）

select * from(select deptno,ename,sal,row_number() over(partition by deptno 
order by 
sal desc) a from scott.emp) where a<=3 order by deptno asc,sal desc ;

--rows unbounded preceding 分析函数（显示各部门的积累工资总和）

select deptno,sal,sum(sal) over(order by deptno asc rows unbounded preceding) 积累工资
总和 from scott.emp ;

结果：



--rows 整数值 preceding(显示每最后4条记录的汇总值)

select deptno,sal,sum(sal) over(order by deptno rows 3 preceding) 每4汇总值 
from scott.emp ;

结果：



--rows between 1 preceding and 1 following（统计3条记录的汇总值【当前记录居中】）

select deptno,ename,sal,sum(sal) over(order by deptno rows between 1 preceding 
and 1 following) 汇总值 from scott.emp ;

结果：



--ratio_to_report(显示员工工资及占该部门总工资的比例)

select deptno,sal,ratio_to_report(sal) over(partition by deptno) 比例 from scott.emp ;

结果：



--查看所有用户

select * from dba_users ;

select count(*) from dba_users ;

select * from all_users ;

select * from user_users ;

select * from dba_roles ;

--查看用户系统权限

select * from dba_sys_privs ;

select * from user_users ;

--查看用户对象或角色权限

select * from dba_tab_privs ;

select * from all_tab_privs ;

select * from user_tab_privs ;

--查看用户或角色所拥有的角色

select * from dba_role_privs ;

select * from user_role_privs ;

-- rownum:查询10至12信息

select * from scott.emp a where rownum<=13 and a.empno not in(select b.empno 
from scott.emp b where rownum<=9);

结果：



--not exists；查询emp表在dept表中没有的数据

select * from scott.emp a where not exists(select * from scott.dept b where a.empno=b.deptno) ;

结果：



--rowid;查询重复数据信息

select * from scott.emp a where a.rowid>(select min(x.rowid) from scott.emp x 
where x.empno=a.empno);

--根据rowid来分页(一万条数据，查询10000至9980时间大概在0.03秒左右)

select * from scott.emp where rowid in(select rid from(select rownum rn,rid from
(select rowid rid,empno from scott.emp order by empno desc) where rownum<10)
where rn>=1)order by empno desc ;

结果：



--根据分析函数分页(一万条数据，查询10000至9980时间大概在1.01秒左右)

select * from(select a.*,row_number() over(order by empno desc) rk 
from scott.emp a ) where rk<10 and rk>=1;

结果：



--rownum分页(一万条数据，查询10000至9980时间大概在0.01秒左右)

select * from(select t.*,rownum rn from(select * from scott.emp order by empno desc)
t where rownum<10) where rn>=1;

select * from(select a.*,rownum rn from (select * from scott.emp) a 
where rownum<=10) where rn>=5 ;

--left outer join:左连接

select a.*,b.* from scott.emp a left outer join scott.dept b on a.deptno=b.deptno ;

--right outer join:右连接

select a.*,b.* from scott.emp a right outer join scott.dept b on a.deptno=b.deptno ;

--inner join

select a.*,b.* from scott.emp a inner  join scott.dept b on a.deptno=b.deptno ;

--full join

select a.*,b.* from scott.emp a full join scott.dept b on a.deptno=b.deptno ;

select a.*,b.* from scott.emp a,scott.dept b where a.deptno(+)=b.deptno ;

select distinct ename,sal from scott.emp a group by sal having ;

select * from scott.dept ;

select * from scott.emp ;

--case when then end (交叉报表)

select ename,sal,case deptno when 10 then '会计部' when 20 then '研究部' when 30 
then '销售部' else '其他部门' end 部门 from scott.emp ;

结果：



select ename,sal,case when sal>0 and sal<1500 then '一级工资' when sal>=1500 and 
sal<3000 then '二级工资' when sal>=3000 and sal<4500 then '三级工资' 
else '四级工资' end 工资等级 from scott.emp order by sal desc ;

结果：



--交叉报表是使用分组函数与case结构一起实现

select 姓名,sum(case 课程 when '数学' then 分数 end)数学,
sum(case 课程 when '历史' then 分数 end)历史 from 学生 
group by 姓名 ;

--decode 函数

select 姓名,sum(decode(课程,'数学',分数,null))数学,
sum(decode(课程,'语文',分数,null)) 语文,sum(decode(课程,'历史','分数',null))历史 
from 学生 group by 姓名 ;

--level。。。。connect by(层次查询)

select level,emp.* from scott.emp connect by prior empno = mgr order by level ;

结果：



--sys_connect_by_path函数

select ename,sys_connect_by_path(ename,'/') from scott.emp start with mgr 
is null connect by prior empno=mgr ;

结果：



--start with connect by prior 语法

select lpad(ename,3*(level),'')姓名,lpad(ename,3*(level),'')姓名 
from scott.emp where job<>'CLERK' start with mgr is null connect by 
prior mgr = empno ;

--level与prior关键字

select level,emp.* from scott.emp start with ename='SCOTT' connect by prior empno=mgr;

select level,emp.* from scott.emp start with ename='SCOTT' connect by empno = prior mgr ;

结果：



--等值连接

select empno,ename,job,sal,dname from scott.emp a,scott.dept b 
where a.deptno=b.deptno and (a.deptno=10 or sal>2500);

结果：



--非等值连接

select a.ename,a.sal,b.grade from scott.emp a,scott.salgrade b 
where a.sal between b.losal and b.hisal ;

结果：



--自连接

select a.ename,a.sal,b.ename from scott.emp a,scott.emp b where a.mgr=b.empno ;

结果：



--左外连接

select a.ename,a.sal,b.ename from scott.emp a,scott.emp b where a.mgr=b.empno(+);

结果：



--多表连接

select * from scott.emp ,scott.dept,scott.salgrade 
where scott.emp.deptno=scott.dept.deptno 
and scott.emp.sal between scott.salgrade.losal and scott.salgrade.hisal ;

结果：



select * from scott.emp a join scott.dept b on a.deptno=b.deptno 
join scott.salgrade s on a.sal between s.losal and s.hisal 
where a.sal>1000;

select * from(select * from scott.emp a join scott.dept b on a.deptno=b.deptno 
where a.sal>1000) c join scott.salgrade s on c.sal between s.losal and s.hisal ;

--单行子查询

select * from scott.emp a where a.deptno=(
select deptno from scott.dept where loc='NEW YORK');

select * from scott.emp a where a.deptno in (
select deptno from scott.dept where loc='NEW YORK');

结果：



--单行子查询在 from 后

select scott.emp.*,(select deptno from scott.dept where loc='NEW YORK') 
a from scott.emp ;

--使用 in ,all,any 多行子查询

--in：表示等于查询出来的对应数据

select ename,job,sal,deptno from scott.emp where job in
(select distinct job from scott.emp where deptno=10);

--all:表示大于所有括号中查询出来的对应的数据信息

select ename,sal,deptno from scott.emp where sal>all
(select sal from scott.emp where deptno=30);

--any:表示大于括号查询出来的其中任意一个即可（只随机一个）

select ename,sal,deptno from scott.emp where sal>any(
select sal from scott.emp where deptno=30);

--多列子查询

select ename,job,sal,deptno from scott.emp where(deptno,job)=(
select deptno,job 
from scott.emp where ename='SCOTT');

select ename,job,sal,deptno from scott.emp where(sal,nvl(comm,-1)) 
in(select sal,nvl(comm,-1) from scott.emp where deptno=30);

--非成对比较

select ename,job,sal,deptno from scott.emp where sal in(select sal 
from scott.emp where deptno=30) and nvl(comm,-1) in(select nvl(comm,-1) 
from scott.emp where deptno=30);

--其他子查询

select ename,job,sal,deptno from scott.emp where exists(
select null from scott.dept where scott.dept.deptno=scott.emp.deptno 
and scott.dept.loc='NEW YORK');

select ename,job,sal from scott.emp join(select deptno,avg(sal) avgsal,null 
from scott.emp group by deptno) dept on emp.deptno=dept.deptno 
where sal>dept.avgsal ;

create table scott.test(

       ename varchar(20),

       job varchar(20)

);

--drop table test ;

select * from scott.test ;

--Insert与子查询(表间数据的拷贝)

insert into scott.test(ename,job) select ename,job from scott.emp ;

--Update与子查询

update scott.test set(ename,job)=(select ename,job from scott.emp where ename='SCOTT' 
and deptno ='10');

--创建表时，还可以指定列名

create table scott.test_1(ename,job) as select ename,job from scott.emp ;

select * from scott.test_1 ;

--delete与子查询

delete from scott.test where ename in('');

--合并查询

--union语法（合并且去除重复行，且排序）

select ename,sal,deptno from scott.emp where deptno>10 union select ename,sal,deptno 
from scott.emp where deptno<30 ;

select a.deptno from scott.emp a union select b.deptno from scott.dept b ;

--union all(直接将两个结果集合并，不排序)

select ename,sal,deptno from scott.emp where deptno>10 union all select ename,sal,
deptno from scott.emp where deptno<30 ;

select a.deptno from scott.emp a union all select b.deptno from scott.dept b ;

--intersect:取交集

select ename,sal,deptno from scott.emp where deptno>10 intersect select ename,sal,
deptno from scott.emp where deptno<30;

--显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和

select dname as 部门,sum(sal) as 工资总和 from scott.emp a,scott.dept b 
where a.deptno=b.deptno group by dname having sum(sal)>(
select sum(sal)/3 from scott.emp c,scott.dept d where c.deptno=d.deptno);

结果：



--使用with得到以上同样的结果

with test as (select dname ,sum(sal) sumsal  from scott.emp ,scott.dept 
where scott.emp.deptno=scott.dept.deptno group by dname) 
select dname as 部门,sumsal as 工资总和 from scott.test where sumsal>(
select sum(sumsal)/3 from scott.test);

结果：



--分析函数

select ename,sal,sum(sal) over(partition by deptno order by sal desc) from scott.emp ;

--rows n preceding(窗口子句一)

select deptno,sal,sum(sal) over(order by sal rows 5 preceding) from scott.emp ;

结果：



--rum(..) over(..)..

select sal,sum(1) over(order by sal) aa from scott.emp  ;

select deptno,ename,sal,sum(sal) over(order by ename) 连续求和,sum(sal) over() 
总和,100*round(sal/sum(sal) over(),4) as 份额 from scott.emp;

结果：



select deptno,ename,sal,sum(sal) over(partition by deptno order by ename) 部门
连续求和,sum(sal) over(partition by deptno) 部门总和,100*round(sal/sum(sal) over(),4) 
as 总份额 from scott.emp;

结果：



select deptno,sal,rank() over (partition by deptno order by sal),
dense_rank() over(partition by deptno order by sal) from scott.emp 
order by deptno ;

结果；



select * from (select rank() over(partition by 课程 order by 分数 desc) 
rk,分析函数_rank.* from 分析函数_rank) where rk<=3 ;

--dense_rank():有重复的数字不跳着排列

--row_number()

select deptno,sal,row_number() over(partition by deptno order by sal) rm 
from scott.emp ;

结果：



--lag()和lead()

select deptno,sal,lag(sal) over(partition by deptno order by sal) 上一个,
lead(sal) over(partition by deptno order by sal) from scott.emp ;

结果：



--max(),min(),avg()

select deptno,sal,max(sal) over(partition by deptno order by sal)最大,
min(sal) over(partition by deptno order by sal)最小,avg(sal) 
over(partition by deptno order by sal)平均 from scott.emp ;

结果：



--first_value(),last_value()

select deptno,sal,first_value(sal) over(partition by deptno)最前,
last_value(sal) over(partition by deptno )最后 from scott.emp ;

结果：



--分组补充 group by grouping sets

select deptno ,sal,sum(sal) from scott.emp group by grouping sets(deptno,sal);

select null,sal,sum(sal) from scott.emp group by sal union all 
select deptno,null,sum(sal) from scott.emp group by deptno ;

结果：



--rollup

select deptno,job,avg(sal) from scott.emp group by rollup(deptno,job) ;

--理解rollup等价于

select deptno,job,avg(sal) from scott.emp group by deptno,job union 
select deptno ,null,avg(sal) from scott.emp group by deptno union 
select null,null,avg(sal) from scott.emp ;

结果：



select deptno,job,avg(sal) a from scott.emp group by cube(deptno,job) ;

--理解CUBE

select deptno,job,avg(sal) from scott.emp group by cube(deptno,job) ;

--等价于

select deptno,job,avg(sal) from scott.emp group by grouping 
sets((deptno,job),(deptno),(job),());

结果：



--查询工资不在1500至2850之间的所有雇员名及工资

select ename,sal from scott.emp where sal not in(select sal from scott.emp 
where sal between 1500 and 2850 );

--部门10和30中的工资超过1500的雇员名及工资

select deptno,ename,sal from scott.emp a where a.deptno in(10,30) and a.sal>1500 
order by sal desc ;

结果：



--在1981年2月1日至1981年5月1日之间雇佣的雇员名，岗位及雇佣日期，并以雇佣日期先后顺序排序

select ename as 姓名,job as 岗位,hiredate as 雇佣日期 from scott.emp a 
where a.hiredate between to_date('1981-02-01','yyyy-mm-dd') 
and to_date('1981-05-01','yyyy-mm-dd') order by a.hiredate asc ;

结果：



select * from scott.emp where hiredate >to_date('1981-02-01','yyyy-MM-dd');

--查询获得补助的所有雇佣名，工资及补助额，并以工资和补助的降序排序

select ename,sal,comm from scott.emp a where a.comm > all(0) order by comm desc;

--工资低于1500的员工增加10%的工资，工资在1500及以上的增加5%的工资并按工资高低排序（降序）

select ename as 员工姓名,sal as 补发前的工资,case when sal<1500 then (sal+sal*0.1) 
else (sal+sal*0.05) end 补助后的工资 from scott.emp order by sal desc ;

结果：



--查询公司每天，每月，每季度，每年的资金支出数额

select sum(sal/30) as 每天发的工资,sum(sal) as 每月发的工资,sum(sal)*3 as 每季度
发的工资,sum(sal)*12 as 每年发的工资 from scott.emp;

结果：



--查询所有员工的平均工资，总计工资，最高工资和最低工资

select avg(sal) as 平均工资,sum(sal) as 总计工资,max(sal) as 最高工资,min(sal) 
as 最低工资 from scott.emp;

结果：



--每种岗位的雇员总数和平均工资

select job as 岗位,count(job) as 岗位雇员总数,avg(sal) as 平均工资 from scott.emp 
group by job order by 平均工资 desc;

结果：



--雇员总数以及获得补助的雇员数

select count(*) as 公司雇员总数,count(comm) as 获得补助的雇员人数 from scott.emp ;

--管理者的总人数

--雇员工资的最大差额

select max(sal),min(sal),(max(sal) - min(sal)) as 员工工资最大差额 from scott.emp ;

--每个部门的平均工资

select deptno,avg(sal) from scott.emp a group by a.deptno;

结果：



--查询每个岗位人数超过2人的所有职员信息

select * from scott.emp a,(select c.job,count(c.job) as sl from scott.emp c 
group by c.job ) b where b.sl>2 and a.job=b.job;

结果：



select * from scott.emp a where a.empno in(select mgr from scott.emp ) and 
(select count(mgr) from scott.emp)>2 ;

结果：



--处理重复行数据信息（删除，查找，修改）

select * from a1 a where not exists (select b.rd from (select rowid rd,
row_number() over(partition by LOAN, BRANCH order by BEGIN_DATE desc) rn from a1) b 
where b.rn = 1 and a.rowid = b.rd);

--查询emp表数据信息重复问题

select * from scott.emp a where exists(select b.rd from(select rowid rd,
row_number() over(partition by ename,job,mgr,hiredate,sal,comm,deptno order by
 empno asc) rn from scott.emp) b where b.rn=1 and a.rowid=b.rd);

--initcap：返回字符串，字符串第一个字母大写

select initcap(ename) Upp from scott.emp ;

结果：



--ascii:返回与指定的字符对应的十进制数

select ascii(a.empno) as 编号,ascii(a.ename) as 姓名,ascii(a.job) as 岗位 
from scott.emp a ;

结果：



--chr:给出整数，返回对应的字符

select chr(ascii(ename)) as 姓名 from scott.emp ;

结果：



--concat:连接字符串

select concat(a.ename,a.job)|| a.empno as 字符连接 from scott.emp a;

结果：



--instr:在一个字符串中搜索指定的字符,返回发现指定的字符的位置

select instr(a.empno,a.mgr,1,1) from scott.emp a ;

--length:返回字符串的长度

select ename,length(a.ename) as 长度,a.job,length(a.job) as 长度 from scott.emp a ;

--lower：返回字符串，并将所返回的字符小写

select a.ename as 大写,lower(a.ename) as 小写 from scott.emp a ;

结果：



--upper：返回字符串，并将返回字符串都大写

select lower(a.ename) as 小写名字,upper(a.ename) as 大写名字 from scott.emp a ;

结果：



--rpad:在列的右边粘贴字符，lpad: 在列的左边粘贴字符(不够字符则用*来填满)

select lpad(rpad(a.ename,10,'*'),16,'*') as 粘贴 from scott.emp a ;

结果：



--like不同角度的使用

select * from scott.emp where ename like '%XXR%';

select * from scott.emp where ename like '%S';

select * from scott.emp where ename like 'J%';

select * from scott.emp where ename like 'S';

select * from scott.emp where ename like '%S_';

--每个部门的工资总和

select a.ename,sum(sal) from scott.emp a group by ename;

--每个部门的平均工资

select a.deptno,avg(sal) from scott.emp a group by deptno ;

--每个部门的最大工资

select a.deptno,max(sal) from scott.emp a group by deptno ;

--每个部门的最小工资

select a.deptno,min(sal) from scott.emp a group by deptno ;

--查询原工资占部门工资的比率

select deptno ,sal,ratio_to_report(sal) over(partition by deptno) sal_ratio from scott.emp ;

--查询成绩不及格的所有学生信息（提示：没有对应的表，只是意思意思。不及格人数大于等于三才能查）

select * from scott.emp where empno in(select distinct empno from scott.emp 
where 3<(select count(sal) from scott.emp where sal<3000) and empno in(select empno 
from scott.emp where sal<3000));

结果：



--查询每个部门的平均工资

select distinct deptno,avg(sal) from scott.emp group by deptno  order by deptno desc;

--union组合查出的结果，但要求查出来的数据类型必须相同

select sal from scott.emp where sal >=all(select sal from scott.emp ) union select 
sal from scott.emp ;

select * from scott.emp a where a.empno between 7227 and 7369 ;--只能从小到大

---------创建表空间  要用拥有create tablespace权限的用户，比如sys

create tablespace tbs_dat datafile 'c:\oradata\tbs_dat.dbf' size 2000M;

---------添加数据文件

alter tablespace tbs_dat add datafile 'c:\oradata\tbs_dat2.dbf' size 100M;

---------改变数据文件大小

alter database datafile 'c:\oradata\tbs_dat.dbf' resize 250M;

---------数据文件自动扩展大小

alter database datafile 'c:\oradata\tbs_dat.dbf' autoextend on next 1m maxsize 20m;

---------修改表空间名称

alter tablespace tbs_dat rename to tbs_dat1;

---------删除表空间  and datafiles 表示同时删除物理文件

drop tablespace tbs_dat including contents and datafiles;

--substr(s1,s2,s3)：截取s1字符串,从s2开始,结束s3

select substr(job,3,length(job)) from scott.emp ;

--replace：替换字符串

select replace(ename,'LL','aa') from scott.emp;

select * from scott.test;

insert into scott.test(ename,job) values('weather','好');

insert into scott.test(ename,job) values('wether','差');

--soundex：返回一个与给定的字符串读音相同的字符串

select ename from scott.test where soundex(ename)=soundex('wether');

--floor：取整数

select sal,floor(sal) as 整数 from scott.emp ;

--log（n,s）：返回一个以n为低，s的对数

select empno,log(empno,2) as 对数 from scott.emp ;

--mod(n1,n2)：返回一个n1除以n2的余数

select empno,mod(empno,2) as 余数 from scott.emp ;

结果：



--power(n1,n2)：返回n1的n2次方根

select empno,power(empno,2) as 方根 from scott.emp ;

--round和trunc：按照指定的精度进行舍入

select round(41.5),round(-41.8),trunc(41.6),trunc(-41.9) from scott.emp ;

--sign：取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

select sign(45),sign(-21),sign(0) from scott.emp ;

 子查询

1.单行子查询
select * from emp
where sal > (select sal from emp where empno > 7876);
 
2.子查询空值/多值问题
如果子查询未返回任何行，则主查询也不会返回任何结果。
(空值)select * from emp where sal >(select sal from emp where empno=6666);
 
如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符
(正常)select * from emp
where sal > (select sal from emp where empno > 7876);
 
如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符
(多值)select * from emp where sal > (select avg(sal) from emp group by deptno);--非法
 
3.多行子查询
select * from emp where sal > any(select avg(sal) from emp group by deptno);
注：any(collection) 函数.  只要大于collection中任意一个即满足>条件。与in用法类似
select * from emp where sal > all(select avg(sal) from emp group by deptno);
注:all(collection)函数，比子查询返回结果中的所有值都大才满足>条件。
select * from emp where job in (select job from emp where ename = 'Smith' or ename = 'Halen');
 
4.TopN查询
select * from emp where rownum = 1 or rownum = 2;
排序，列出所有记录或前N条记录
select * from (select * from emp order by sal desc) 
where rownum <= 5;--rownum只能是小于等于”<=”n.
 
排序，列出指定区间的记录(下面的查询语句执行后不会有任何结果。)
select 字段列表
from   (select 字段列表 from 表名 order by 排序字段)
where rownum>=11 and rownum<=15;
 
如果想实现大于等于，或者between,则如下代码:
select 字段列表 from
(select rownum num, 字段列表 from 表名 order by 排序字段)
where num >= 8 and num <=16;
 
原因分析：rownum是查询过后才按顺序排的，假如你的条件是rownum>1;那么返回数据的第一条（rownum是1）就不符合要求了，
然后第二条数据变成了现在的第一条，结果这一条rownum又变成1了又不符合要求了，以此类推 就没有返回结果。
 
5.分页查询
通过第4点对rownum的理解之后，就可以写出我们想要的分页语句了。
仍然不清楚的，可以先去了解一下Oracle伪列rownum,rowid
select 字段列表
from (select rownum no,t.* from table_name t)
where no > 2 and no < 5;
---------------------------------------------
1、查询部分分为主查询和子查询； 
2、根据返回值的记录多少分为单行子查询和多行子查询；单行子查询用单行比较符=连接；多行子查询用多行比较符in连接； 
3、子查询的内容可以放在FROM后面，也可以放在WHERE后面，也可以放在HAVING后面； 
4、完整的SELECT语句可以拥有GROUP BY，HAVING子句，也可以使用组函数；也可以从多个表中查询； 
5、子查询的内容必须用小括号来界定； 
6、例子1：子查询（子句）在FROM后面的：

SELECT first_name,last_name
FROM 
(SELECT first_name,last_name
FROM s_emp
WHERE 1=1
AND first_name='国藩'
)
WHERE 1=1
AND last_name='曾';
1
2
3
4
5
6
7
8
9
精简一下就是：

SELECT first_name,last_name
FROM 
(子查询)
WHERE 1=1
AND last_name='曾';
1
2
3
4
5
这里就像是一个定于从句，这里的（子查询）就替换成了另一个select语句；这里要注意，子查询语句里
没有表示结束的分号；并且主查询的范围必须小于子查询，否则会因查不到数据而报错；

7、例子2：子查询（子句）在WHERE后面的

SELECT first_name,last_name 
FROM s_emp
WHERE dept_id=
(
SELECT dept_id
FROM s_emp
WHERE last_name='曾'
);
1
2
3
4
5
6
7
8
这里查询的是姓是和‘曾’的所在的部门ID相同（只有一个姓曾）的所有人的姓名；

这里，有人问了，如果有好几个姓曾的人怎么办，我要把所有姓曾人所在部门的人的姓名都查出来怎么办，
这里就是多行子查询；把等号换成in即可；
---------------------