ORACLE 第二步之SQL查询和SQL函数

--利用现有的表创建表

create table emp_copy as select * from emp;

--利用现有的表创建表,不插入emp表中记录。

create table emp_copy as select * from emp where 1=2;

--插入来自其它表中的记录

insert into emp_copy select * from scott.emp;

--INTERSECT 操作符只返回两个查询的公共行【交集】。

select * from emp INTERSECT select * from emp where deptno=10;

--MINUS 操作符返回从第一个查询结果中排除第二个查询中出现的行【差集】。

select * from emp minus select * from emp where deptno = 10;

--SQL 操作符的优先级从高到低的顺序是：

算术操作符>连接操作符>比较操作符>NOT 逻辑操作符>AND 逻辑操作符>OR 逻辑操作符

--日期函数包括：

1、ADD_MONTHS

2、MONTHS_BETWEEN
3、LAST_DAY
4、ROUND
5、NEXT_DAY
6、TRUNC
7、EXTRACT

--字符函数接受字符输入并返回字符或数值

函数	输入	输出
Initcap(char)	Select initcap(‘hello’) from dual;	Hello
Lower(char)	Select lower(‘FUN’) from dual;	fun
Upper(char)	Select upper(‘sun’) from dual;	SUN
Ltrim(char,set)	Select ltrim( ‘xyzadams’,'xyz’) from dual;	adams
Rtrim(char,set)	Select rtrim(‘xyzadams’,ams’) from dual;	xyzad
Translate(char, from, to)	Select translate(‘jack’,'j’ ,'b’) from dual;	back
Replace(char, searchstring,[rep string])	Select replace(‘jack and jue’ ,'j’,'bl’) from dual;	black and blue
Instr (char, m, n)	Select instr (‘worldwide’,’d’) from dual;	5
Substr (char, m, n)	Select substr(‘abcdefg’,3,2) from dual;	cd
Concat (expr1, expr2)	Select concat (‘Hello’,’ world’) from dual;	Hello world

--数字函数接受数字输入并返回数值结果

函数	输入	输出
Abs(n)	Select abs(-15) from dual;	15
Ceil(n)	Select ceil(44.778) from dual;	45
Cos(n)	Select cos(180) from dual;	-0.5984601
Cosh(n)	Select cosh(0) from dual;	1
Floor(n)	Select floor(100.2) from dual;	100
Power(m,n)	Select power(4,2) from dual;	16
Mod(m,n)	Select mod(10,3) from dual;	1
Round(m,n)	Select round(100.256,2) from dual;	100.26
Trunc(m,n)	Select trunc(100.256,2) from dual;	100.25
Sqrt(n)	Select sqrt(4) from dual;	2
Sign(n)	Select sign(-30) from dual;	-1

--常用的转换函数有：
1、TO_CHAR
2、TO_DATE    SELECT TO_DATE('2011-09-25', 'yyyy-mm-dd') FROM dual;
3、TO_NUMBER     SELECT TO_NUMBER('100') FROM dual;

--几个用来转换空值的函数：

1、NVL    select nvl(comm,0) from emp;

2、NVL2

3、NULLIF

--分组函数基于一组行来返回结果

1、AVG    select avg(sal) from emp t where t.deptno=10;

2、MIN    select min(sal) from emp t where t.deptno=10;

3、MAX   select max(sal) from emp t where t.deptno=10;

4、SUM   select sum(sal) from emp t;

5、COUNT   select count(*) from emp t;

--GROUP BY子句 用于将信息划分为更小的组，每一组行返回针对该组的单个结果。

select t.deptno from emp t group by t.deptno;

--HAVING子句 用于指定 GROUP BY 子句检索行的条件。

select t.deptno from emp t group by t.deptno having t.deptno<>10;

-- 显示员工工资排名, 不对 :
SELECT ENAME,SAL,ROWNUM AS SAL_ORDER FROM EMP ORDER BY SAL DESC;

-- 显示员工工资排名 :
SELECT ENAME, SAL, ROWNUM AS SAL_ORDER FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC);

-- 显示员工工资排名 :

SELECT ENAME, SAL, ROW_NUMBER() OVER(ORDER BY SAL DESC) AS SAL_ORDER
FROM EMP;

--按部门以员工工资进行排名
SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER
            (PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM EMP;
--按部门以员工工资进行排名工资相等的排名相同
SELECT DEPTNO, ENAME, SAL, RANK() OVER
            (PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM EMP;

-- 找到每个部门工资最高的人(只选一个)
SELECT DEPTNO, ENAME, SAL FROM
      (SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER
            (PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM EMP)
    WHERE SAL_ORDER < 2;

-- 找到每个部门工资最高的人(包括并列第一)
SELECT DEPTNO, ENAME, SAL FROM
      (SELECT DEPTNO, ENAME, SAL, RANK() OVER
            (PARTITION BY DEPTNO ORDER BY SAL DESC) AS SAL_ORDER FROM EMP)
    WHERE SAL_ORDER < 2;

SELECT CHR(67) FROM DUAL;      --RETURN C
SELECT LPAD('FUNCTION',9,'=') FROM DUAL;     --RETURN =FUNCTION
SELECT TRIM (LEADING 9 FROM 9987697899) FROM DUAL;     --RETURN 87697899

SELECT TRIM (TRAILING 9 FROM 9987697899) FROM DUAL;    --RETURN 99876978
SELECT TRIM (9 FROM 9987697899) FROM DUAL;    --RETURN 876978

--DECODE函数将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。

SELECT ENAME,DECODE(DEPTNO,'10','部门1','20','部门2','30','部门3') FROM EMP;

查找EMP表中10条以后的记录
SELECT * FROM (SELECT E.*, ROWNUM RN FROM EMP) WHERE RN > 10;

查找EMP表中薪水第5高的员工
SELECT * FROM
(SELECT EMPNO, ENAME, SAL, ROW_NUMBER() OVER(ORDER BY SAL DESC) RN FROM EMP) WHERE RN = 5;

统计各部门的薪水总和.
select deptno, sal, sum(sal) from emp group by rollup(deptno, sal);

------------------------------------BFILE--------------------------------------

--①如何在创建表时声明一个BFILE数据类型的列
create table my_book
(
file_descr varchar2(40),
book_file bfile
);
--②如何在BFILE数据类型的列中插入值
create directory book_text as 'c:\test';
insert into my_book values('test 1',BFILENAME('BOOK_TEXT', 'test1.txt'));
--③如何查看BFILE列中的数据结果
select * from my_book;

------------------------------------BLOB--------------------------------------

--①如何在创建表时声明一个BLOB数据类型的列
create table my_diagrams
(
chapter_descr varchar2(40),
diagram_no integer,
diagram blob
);

--②如何在BLOB数据类型的列中插入值
--==以SYSTEM用户登录,授权SCOTT用户==--
grant create any directory to scott;
create directory IMAGES as 'c:\images';
grant read on directory IMAGES to scott;
--===========================================================--
declare
l_bfile bfile;
l_blob blob;
begin
insert into my_diagrams(diagram)
values(EMPTY_BLOB())
return diagram into l_blob;
l_bfile:=BFILENAME('IMAGES', '\login.jpg');
dbms_lob.open(l_bfile,dbms_lob.file_readonly);
dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
dbms_lob.close(l_bfile);
commit;
end;

---------------------------------------CLOB------------------------------------

--①如何在创建表时声明一个CLOB数据类型的列
CREATE TABLE MY_BOOK_TEXT
(
CHAPTER_ID NUMBER(3),
CHAPTER_DESCR VARCHAR2(40),
CHAPTER_TEXT CLOB
);

--②如何在CLOB数据类型的列中插入值
INSERT INTO MY_BOOK_TEXT VALUES
(5,'CHAPTER 5 PL/SQL','ABCDEFGHIGKLMNOPQRSTVUWXYZ');

--③如何读取CLOB数据类型列中的值
SELECT * FROM MY_BOOK_TEXT;

DECLARE
CLOB_VAR CLOB;
AMOUNT INTEGER := 24;
OFFSET INTEGER := 1;
OUTPUT_VAR VARCHAR2(100);
BEGIN
SELECT CHAPTER_TEXT INTO CLOB_VAR
FROM MY_BOOK_TEXT WHERE CHAPTER_ID=5;
DBMS_LOB.READ(CLOB_VAR,AMOUNT,OFFSET,OUTPUT_VAR);
DBMS_OUTPUT.PUT_LINE(OUTPUT_VAR);
END;

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

分析函数根据一组行来计算聚合值，用于计算完成聚集的累计排名、移动平均数等，为每组记录返回多个行。

1):ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) AS ALIAS
//采用ORACLE分析函数ROW_NUMBER()
//ROW_NUMBER为有序组中的每一行返回一个唯一的排序值, 序号由ORDER BY子句指定, 从1开始.
SELECT ENAME, JOB, ROW_NUMBER() OVER(ORDER BY SAL) AS SAL_RANK FROM EMP;

2):RANK() OVER(PARTITION BY COLUMN ORDER BY COLUMN) AS ALIAS
//RANK()函数计算一个值在一组值中的排位, 排位是以1开头的连续整数. 具有相等值的行排位相同, 序数随后跳跃相应的数值, 即如果两行的序数为1, 则没有序2下一行的序号为3.
SELECT DEPTNO, ENAME, SAL, RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANK FROM EMP;

3):DENSE_RANK() OVER(PARTITION BY COLUMN ORDER BY COLUMN) AS ALIAS
//DENSE_RANK()计算一个行在一组有序行中的排位, 排位是以1开头的连续整数. 具有相等值的行排位相同, 并且排位是连续的(不会跳过排序数)
SELECT D.DEPTNO, E.ENAME, E.SAL,DENSE_RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) DENSERANK FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO;

4):ROWNUM
//对于一个查询返回的每一行, ROWNUM伪列返回一个数值代表行的次序.返回的第一行的ROWNUM值为1, 第二行的ROWNUM值为2, 依次类推.其ROWNUM值为该记录在原表中的原始位置值.
SELECT ROWNUM, ENAME, JOB FROM EMP ORDER BY SAL;

分组函数

ROLLUP:该关键字(函数)用于与GROUP BY一起使用, 用于在分组的小计行上再一次统计出总计行.
SELECT DEPTNO,EMPNO, SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, EMPNO);

CUBE：该关键字（函数）用于与GROUP BY一起使用, 用于在分组的小计行上再一次统计出总计行+原始数据交叉表报表。
SELECT DEPTNO, EMPNO, SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO, EMPNO);