Oracle SQL精妙SQL语句讲解

 新葡亰编程     |      2020-05-05

核心提示: --行列转换 行转列DROP TABLE t_change_lc;CREATE TABLE t_change_lc (card_code VARCHAR2

为方便测试,创建表emp和表dept。

--行列转换 行转列 DROP TABLE t_change_lc; CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);

图片 1

INSERT INTO t_change_lc SELECT 001 card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM = 4 UNION SELECT 002 card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM = 4;

表emp

SELECT * FROM t_change_lc;

图片 2

SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0)) q1, SUM(decode(a.q, 2, a.bal, 0)) q2, SUM(decode(a.q, 3, a.bal, 0)) q3, SUM(decode(a.q, 4, a.bal, 0)) q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

表dept

--行列转换 列转行 DROP TABLE t_change_cl; CREATE TABLE t_change_cl AS SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0)) q1, SUM(decode(a.q, 2, a.bal, 0)) q2, SUM(decode(a.q, 3, a.bal, 0)) q3, SUM(decode(a.q, 4, a.bal, 0)) q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

子查询

SELECT * FROM t_change_cl;

子查询是一条查询语句,它是嵌套在其他SQL语句中的,目的是将查询的结果提供给外层的SQL语句。

SELECT t.card_code, t.rn q, decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal FROM (SELECT a.*, b.rn FROM t_change_cl a, (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM = 4) b) t ORDER BY 1, 2;

查看谁的工资高于CLARK的工资?

--行列转换 行转列 合并 DROP TABLE t_change_lc_comma; CREATE TABLE t_change_lc_comma AS SELECT card_code,quarter_||q AS q FROM t_change_lc;

SELECT ename,sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='CLARK')

SELECT * FROM t_change_lc_comma;

查看与CLARK同部门的员工?

SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ;)), 2) q FROM (SELECT a.card_code, a.q, row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn FROM t_change_lc_comma a) t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn GROUP BY t1.card_code;

SELECT ename,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='CLARK') AND ename<>'CLARK'

--行列转换 列转行 分割 DROP TABLE t_change_cl_comma; CREATE TABLE t_change_cl_comma AS SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ;)), 2) q FROM (SELECT a.card_code, a.q, row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn FROM t_change_lc_comma a) t1 START WITH t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn - 1 = PRIOR t1.rn GROUP BY t1.card_code;

查看高于公司平均工资的员工?

SELECT * FROM t_change_cl_comma;

SELECT ename,sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp)

SELECT t.card_code, substr(t.q, instr(; || t.q, ;, 1, rn), instr(t.q || ;, ;, 1, rn) - instr(; || t.q, ;, 1, rn)) q FROM (SELECT a.card_code, a.q, b.rn FROM t_change_cl_comma a, (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM = 100) b WHERE instr(; || a.q, ;, 1, rn) 0) t ORDER BY 1, 2;

在DDL中使用子查询,可以将一个子查询的结果集当做表快速创建出来。

-- 实现一条记录根据条件多表插入 DROP TABLE t_ia_src; CREATE TABLE t_ia_src AS SELECT a||ROWNUM c1, b||ROWNUM c2 FROM dual CONNECT BY ROWNUM=5; DROP TABLE t_ia_dest_1; CREATE TABLE t_ia_dest_1(flag VARCHAR2(10) , c VARCHAR2(10)); DROP TABLE t_ia_dest_2; CREATE TABLE t_ia_dest_2(flag VARCHAR2(10) , c VARCHAR2(10)); DROP TABLE t_ia_dest_3; CREATE TABLE t_ia_dest_3(flag VARCHAR2(10) , c VARCHAR2(10));

CREATE TABLE employee AS 

SELECT * FROM t_ia_src; SELECT * FROM t_ia_dest_1; SELECT * FROM t_ia_dest_2; SELECT * FROM t_ia_dest_3;

SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno(+)

INSERT ALL WHEN (c1 IN (a1,a3)) THEN INTO t_ia_dest_1(flag,c) VALUES(flag1,c2) WHEN (c1 IN (a2,a4)) THEN INTO t_ia_dest_2(flag,c) VALUES(flag2,c2) ELSE INTO t_ia_dest_3(flag,c) VALUES(flag1||flag2,c1||c2) SELECT c1,c2, f1 flag1, f2 flag2 FROM t_ia_src;

在使用子查询创建表的时候,查询多少个字段则创建的表就有多少个字段,字段名与查询的字段

-- 如果存在就更新,不存在就插入用一个语句实现 DROP TABLE t_mg; CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));

名字一致(不含有表别名),类型,长度也一致。

SELECT * FROM t_mg;

但是需要注意的是,若查询的字段是函数或者表达式,那么这个字段必须指定别名,并且生成

MERGE INTO t_mg a USING (SELECT the code code, the name NAME FROM dual) b ON (a.code = b.code) WHEN MATCHED THEN UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN INSERT (code, NAME) VALUES (b.code, b.NAME);

的表对应的该字段名就是这个别名。

-- 抽取/删除重复记录 DROP TABLE t_dup; CREATE TABLE t_dup AS SELECT code_||ROWNUM code, dbms_random.string(z,5) NAME FROM dual CONNECT BY ROWNUM=10; INSERT INTO t_dup SELECT code_||ROWNUM code, dbms_random.string(z,5) NAME FROM dual CONNECT BY ROWNUM=2;

DML中使用子查询

SELECT * FROM t_dup;

将CLARK所在部门的员工工资上浮10%

SELECT * FROM t_dup a WHERE a.ROWID (SELECT MIN(b.ROWID) FROM t_dup b WHERE a.code=b.code);

UPDATE emp SET sal=sal*1.1 WHERE deptno=(SELECT deptno FROM emp WHERE ename='CLARK')

SELECT b.code, b.NAME FROM (SELECT a.code, a.NAME, row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rn FROM t_dup a) b WHERE b.rn 1;

子查询根据查询结果分为:

-- IN/EXISTS的不同适用环境 -- t_orders.customer_id有索引 SELECT a.* FROM t_employees a WHERE a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);

单行单列,多行单列,多行多列子查询

SELECT a.* FROM t_employees a WHERE EXISTS (SELECT 1 FROM t_orders b WHERE b.customer_id = 12 AND a.employee_id = b.sales_rep_id);

其中单列子查询常用在过滤条件中多列子查询常被当做表使用。

-- t_employees.department_id有索引 SELECT a.* FROM t_employees a WHERE a.department_id = 10 AND EXISTS (SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);

对于多行单列子查询在进行过滤判断时,需要配合IN,ANY,ALL使用。

SELECT a.* FROM t_employees a WHERE a.department_id = 10 AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b);

查看与职位是SALESMAN同部门的员工?

-- FBI DROP TABLE t_fbi; CREATE TABLE t_fbi AS SELECT ROWNUM rn, dbms_random.STRING(z,10) NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual CONNECT BY ROWNUM =10;

SELECT ename,job,deptno FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE job='SALESMAN') AND job<>'SALESMAN'

CREATE INDEX idx_nonfbi ON t_fbi(dt);

查看比职位是SALESMAN和CLERK工资都高的员工?

DROP INDEX idx_fbi_1; CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));

SELECT ename,sal,job FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE job IN('SALESMAN','CLERK'))

SELECT * FROM t_fbi WHERE trunc(dt) = to_date(2006-09-21,yyyy-mm-dd) ;

EXISTS关键字

-- 不建议使用 SELECT * FROM t_fbi WHERE to_char(dt, yyyy-mm-dd) = 2006-09-21;

EXISTS用在WHERE中进行过滤,其后跟一个子查询,只要该子查询至少可以查询出一条记录,那么则该条件为true.

-- LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE; create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;

查看有下属的员工?

SELECT * FROM t_loop;

SELECT m.ename,m.job,m.sal FROM emp m WHERE EXISTS(SELECT * FROM emp e WHERE e.mgr = m.empno)

-- 逐行提交 DECLARE BEGIN FOR cur IN (SELECT * FROM user_objects) LOOP INSERT INTO t_loop VALUES cur; COMMIT; END LOOP; END;

HAVING中使用子查询:

-- 模拟批量提交 DECLARE v_count NUMBER; BEGIN FOR cur IN (SELECT * FROM user_objects) LOOP INSERT INTO t_loop VALUES cur; v_count := v_count + 1; IF v_count = 100 THEN COMMIT; END IF; END LOOP; COMMIT; END;

查看每个部门的最低薪水,前提是要高于30号部门的最低薪水?

-- 真正的批量提交 DECLARE CURSOR cur IS SELECT * FROM user_objects; TYPE rec IS TABLE OF user_objects%ROWTYPE; recs rec; BEGIN OPEN cur; WHILE (TRUE) LOOP FETCH cur BULK COLLECT INTO recs LIMIT 100; -- forall 实现批量 FORALL i IN 1 .. recs.COUNT INSERT INTO t_loop VALUES recs (i); COMMIT; EXIT WHEN cur%NOTFOUND; END LOOP; CLOSE cur; END;

SELECT MIN(sal),deptno FROM emp GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE deptno=30)

-- 悲观锁定/乐观锁定 DROP TABLE t_lock PURGE; CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

在FROM中使用子查询:

SELECT * FROM t_lock;

通常多列子查询在FROM子句中出现,作用是当做一张表使用。

-- 常见的实现逻辑,隐含bug DECLARE v_cnt NUMBER; BEGIN -- 这里有并发性的bug SELECT MAX(ID) INTO v_cnt FROM t_lock;

查看高于自己所在部门平均工资的员工?

-- here for other operation v_cnt := v_cnt + 1; INSERT INTO t_lock (ID) VALUES (v_cnt); COMMIT; END;

SELECT e.ename,e.sal,e.deptno FROM emp e,(SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno) x WHERE e.deptno=x.deptno AND e.sal>x.avg_sal

-- 高并发环境下,安全的实现逻辑 DECLARE v_cnt NUMBER; BEGIN -- 对指定的行取得lock SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE; -- 在有lock的情况下继续下面的操作 SELECT MAX(ID) INTO v_cnt FROM t_lock;

分页查询

-- here for other operation v_cnt := v_cnt + 1; INSERT INTO t_lock (ID) VALUES (v_cnt); COMMIT; --提交并且释放lock END;

分页查询就是将表中要查询出来的数据分批分段查询出来。这样做的好处是当查询的数据量过大时可以减少内存开销,提高系统响应速度,减少网络传输。

-- 硬解析/软解析 DROP TABLE t_hard PURGE; CREATE TABLE t_hard (ID INT);

分页是方言,不同数据库都提供了分页查询的机制,但是语法定义各不相同。

SELECT * FROM t_hard;

ORACLE是依靠提供了一个伪列:ROWNUM

DECLARE sql_1 VARCHAR2(200); BEGIN -- hard parse -- java中的同等语句是 Statement.execute() FOR i IN 1 .. 1000 LOOP sql_1 := insert into t_hard(id) values( || i || ); EXECUTE IMMEDIATE sql_1; END LOOP; COMMIT;

ROWNUM不存在于任何一张表中,但是每张表都可以查询该字段,该字段在结果集中的值就是每条记录的行号。而行号的生成是伴随查询过程进行的,只要可以从表中查询出一条记录,ROWNUM字段就是为该条记录在结果集中产生对应的行号,从1开始递增。

-- soft parse --java中的同等语句是 PreparedStatement.execute() sql_1 := insert into t_hard(id) values(:id); FOR i IN 1 .. 1000 LOOP EXECUTE IMMEDIATE sql_1 USING i; END LOOP; COMMIT; END;

需要注意,在使用ROWNUM对结果集编行号的过程中,不要使用ROWNUM做大于1以上数字

-- 正确的分页算法 SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM t_employees ORDER BY first_name) a WHERE ROWNUM = 500) WHERE rn 480 ;

的判断,否则得不到结果。

-- 分页算法(why not this one) SELECT a.*, ROWNUM rn FROM (SELECT * FROM t_employees ORDER BY first_name) a WHERE ROWNUM = 500 AND ROWNUM 480;

SELECT ROWNUM,ename,job,sal FROM emp WHERE ROWNUM >1----无查询结果

-- 分页算法(why not this one) SELECT b.* FROM (SELECT a.*, ROWNUM rn FROM t_employees a WHERE ROWNUM = 500 ORDER BY first_name) b WHERE b.rn 480;

SELECT * FROM(SELECT ROWNUM rn,ename,job,sal FROM emp) WHERE rn BETWEEN 6 AND 10----无查询结果

-- OLAP -- 小计合计 SELECT CASE WHEN a.deptno IS NULL THEN 合计 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN 小计 ELSE || a.deptno END deptno, a.empno, a.ename, SUM(a.sal) total_sal FROM scott.emp a GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

查看公司工资排名的第6-10名

-- 分组排序 SELECT a.deptno, a.empno, a.ename, a.sal, -- 可跳跃的rank rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r1, -- 密集型rank dense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r2, -- 不分组排序 rank() over(ORDER BY sal DESC) r3 FROM scott.emp a ORDER BY a.deptno,a.sal DESC;

SELECT * FROM(SELECT ROWNUM rn,t.*

-- 当前行数据和前/后n行的数据比较 SELECT a.empno, a.ename, a.sal, -- 上面一行 lag(a.sal) over(ORDER BY a.sal DESC) lag_1, -- 下面三行 lead(a.sal, 3) over(ORDER BY a.sal DESC) lead_3 FROM scott.emp a ORDER BY a.sal DESC;

                             FROM(SELECT ename,job,sal

                                         FROM emp ORDER BY sal DESC) t) WHERE rn BETWEEN 6 AND 10

SELECT * FROM(SELECT ROWNUM rn,t.*

                             FROM(SELECT ename,job,sal

                                        FROM emp ORDER BY sal DESC) t WHERE ROWNUM <=10)WHERE rn >= 6

换算范围的公式:

PageSize:每页可以显示的条目数

Page:页数

start:(Page-1)*PageSize + 1

end:PageSize*Page

DECODE函数

DECODE函数可以实现分支结构

SELECT ename, job, sal,DECODE(job,'MANAGER', sal * 1.2,'ANALYST', sal * 1.1,'SALESMAN', sal * 1.05,sal) bonus FROM emp;

DECODE在GROUP BY中可以实现将字段不同值的记录分为一组。

上一篇:更改数据库中表的所属用户的两个方法 下一篇:没有了