OCP 047 SQL例子1
--047 SQL汇总/*
MINUSE,UNION,INTERSECT的用法
*/
DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1 (A VARCHAR(2),B INT);
CREATE TABLE T2 (A VARCHAR(2),B INT);
INSERT INTO T1 VALUES('A',1);
INSERT INTO T1 VALUES('B',2);
INSERT INTO T1 VALUES('C',3);
INSERT INTO T2 VALUES('A',1);
INSERT INTO T2 VALUES('B',2);
COMMIT;
SELECT * FROM T1;
A B
-- ----------
A 1
B 2
C 3
SELECT * FROM T2;
A B
-- ----------
A 1
B 2
SELECT * FROM T1 MINUS SELECT * FROM T2;
A B
-- ----------
C 3
--查询在T1而不在T2中的数据
SELECT * FROM T1 INTERSECT SELECT * FROM T2;
A B
-- ----------
A 1
B 2
--查询T1与T2表的交集
SELECT * FROM T1 UNION SELECT * FROM T2;
A B
-- ----------
A 1
B 2
C 3
--查询T1表与T2表的合并集合（出去掉重复）
SELECT * FROM T1 UNION ALL SELECT * FROM T2;
A B
-- ----------
A 1
B 2
C 3
A 1
B 2
--查询T1表与T2表的合并集合(无出去重复操作)
----------------------------------------------------------------------------------------------------------------------
--ROLLUP用法
SELECT OWNER, STATUS, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS WHERE OWNER IN('BI','GOSALES')
GROUP BY OWNER, STATUS, OBJECT_TYPE ORDER BY OWNER,STATUS;
OWNER STATUSOBJECT_TYPE COUNT(*)
------------------------------ ------- ------------------- ----------
ADMIN INVALID PROCEDURE 1
ADMIN VALID INDEX 15
ADMIN VALID LOB 19
ADMIN VALID PACKAGE 1
ADMIN VALID PROCEDURE 1
ADMIN VALID SEQUENCE 2
ADMIN VALID TABLE 37
ADMIN VALID TABLE PARTITION 23
ADMIN VALID TABLE SUBPARTITION 26
ADMIN VALID TRIGGER 3
ADMIN VALID TYPE 5
GOSALES VALID INDEX 45
GOSALES VALID PROCEDURE 1
GOSALES VALID TABLE 24
SELECT OWNER, STATUS, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS WHERE OWNER IN('ADMIN','GOSALES')
GROUP BY ROLLUP(OWNER, STATUS, OBJECT_TYPE) ORDER BY OWNER,STATUS;
OWNER STATUSOBJECT_TYPE COUNT(*)
------------------------------ ------- ------------------- ----------
ADMIN INVALID PROCEDURE 1
ADMIN INVALID 1
ADMIN VALID INDEX 15
ADMIN VALID LOB 19
ADMIN VALID PACKAGE 1
ADMIN VALID PROCEDURE 1
ADMIN VALID SEQUENCE 2
ADMIN VALID TABLE 37
ADMIN VALID TABLE PARTITION 23
ADMIN VALID TABLE SUBPARTITION 26
ADMIN VALID TRIGGER 3
ADMIN VALID TYPE 5
ADMIN VALID 132
ADMIN 133
GOSALES VALID INDEX 45
GOSALES VALID PROCEDURE 1
GOSALES VALID TABLE 24
GOSALES VALID 70
GOSALES 70
203
--从结果可以看出ROLLUP的效果：按照统计括号里面的维度顺序进行一级级向上小计。(注意”顺序“两个字)
--等同于增加了GROUP BY OWNER,STATUS和 GROUP BY OWNER和 全部的COUNT(*)
SELECT OWNER, STATUS, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS WHERE OWNER IN('ADMIN','GOSALES')
GROUP BY OWNER,ROLLUP(STATUS, OBJECT_TYPE) ORDER BY OWNER,STATUS;
OWNER STATUSOBJECT_TYPE COUNT(*)
------------------------------ ------- ------------------- ----------
ADMIN INVALID PROCEDURE 1
ADMIN INVALID 1
ADMIN VALID INDEX 15
ADMIN VALID LOB 19
ADMIN VALID PACKAGE 1
ADMIN VALID PROCEDURE 1
ADMIN VALID SEQUENCE 2
ADMIN VALID TABLE 37
ADMIN VALID TABLE PARTITION 23
ADMIN VALID TABLE SUBPARTITION 26
ADMIN VALID TRIGGER 3
ADMIN VALID TYPE 5
ADMIN VALID 132
ADMIN 133
GOSALES VALID INDEX 45
GOSALES VALID PROCEDURE 1
GOSALES VALID TABLE 24
GOSALES VALID 70
GOSALES 70
--这个结果不太好理解
--我的理解是是对括号内STATUS,OBJECT_TYPE进行计算小计，那么就是统计STATUS的小计，但还是要包含到rollup外的维度。
--统计的最高层次是owner
SELECT OWNER, STATUS, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS WHERE OWNER IN('ADMIN','GOSALES')
GROUP BY OWNER,STATUS,ROLLUP(OBJECT_TYPE) ORDER BY OWNER,STATUS;
OWNER STATUSOBJECT_TYPE COUNT(*)
------------------------------ ------- ------------------- ----------
ADMIN INVALID PROCEDURE 1
ADMIN INVALID 1
ADMIN VALID INDEX 15
ADMIN VALID LOB 19
ADMIN VALID PACKAGE 1
ADMIN VALID PROCEDURE 1
ADMIN VALID SEQUENCE 2
ADMIN VALID TABLE 37
ADMIN VALID TABLE PARTITION 23
ADMIN VALID TABLE SUBPARTITION 26
ADMIN VALID TRIGGER 3
ADMIN VALID TYPE 5
ADMIN VALID 132
GOSALES VALID INDEX 45
GOSALES VALID PROCEDURE 1
GOSALES VALID TABLE 24
GOSALES VALID 70
--维度层次向上汇总，但汇总的最高层次OWNER,STATUS
--GROUPING 的用法
SELECT GROUPING(STATUS), OWNER, STATUS, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS WHERE OWNER IN('ADMIN','GOSALES')
GROUP BY ROLLUP(OWNER,STATUS,OBJECT_TYPE) ORDER BY OWNER,STATUS;
GROUPING(STATUS) OWNER STATUSOBJECT_TYPE COUNT(*)
---------------- ------------------------------ ------- ------------------- ----------
0 ADMIN INVALID PROCEDURE 1
0 ADMIN INVALID 1
0 ADMIN VALID INDEX 15
0 ADMIN VALID LOB 19
0 ADMIN VALID PACKAGE 1
0 ADMIN VALID PROCEDURE 1
0 ADMIN VALID SEQUENCE 2
0 ADMIN VALID TABLE 37
0 ADMIN VALID TABLE PARTITION 23
0 ADMIN VALID TABLE SUBPARTITION 26
0 ADMIN VALID TRIGGER 3
0 ADMIN VALID TYPE 5
0 ADMIN VALID 132
1 ADMIN 133
0 GOSALES VALID INDEX 45
0 GOSALES VALID PROCEDURE 1
0 GOSALES VALID TABLE 24
0 GOSALES VALID 70
1 GOSALES 70
1 203
--GROUPING 表示对STATUS的小计的记录标示
--STATUS为空的记录都是STATUS的小计记录
--------------------------------------------------------------------------------------------------------------------
--CUBE的使用
--CUBE是立方体的意思，是对各个维度，各个层次的汇总。
SELECT GROUPING(STATUS),OWNER, STATUS, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS WHERE OWNER IN('ADMIN','GOSALES')
GROUP BY CUBE(OWNER,STATUS,OBJECT_TYPE) ORDER BY OWNER,STATUS;
GROUPING(STATUS) OWNER STATUSOBJECT_TYPE COUNT(*)
---------------- ------------------------------ ------- ------------------- ----------
0 ADMIN INVALID PROCEDURE 1
0 ADMIN INVALID 1
0 ADMIN VALID INDEX 15
0 ADMIN VALID LOB 19
0 ADMIN VALID PACKAGE 1
0 ADMIN VALID PROCEDURE 1
0 ADMIN VALID SEQUENCE 2
0 ADMIN VALID TABLE 37
0 ADMIN VALID TABLE PARTITION 23
0 ADMIN VALID TABLE SUBPARTITION 26
0 ADMIN VALID TRIGGER 3
0 ADMIN VALID TYPE 5
0 ADMIN VALID 132
1 ADMIN INDEX 15
1 ADMIN LOB 19
1 ADMIN PACKAGE 1
1 ADMIN PROCEDURE 2
1 ADMIN SEQUENCE 2
1 ADMIN TABLE 37
1 ADMIN TABLE PARTITION 23
1 ADMIN TABLE SUBPARTITION 26
1 ADMIN TRIGGER 3
1 ADMIN TYPE 5
1 ADMIN 133
0 GOSALES VALID INDEX 45
0 GOSALES VALID PROCEDURE 1
0 GOSALES VALID TABLE 24
0 GOSALES VALID 70
1 GOSALES INDEX 45
1 GOSALES PROCEDURE 1
1 GOSALES TABLE 24
1 GOSALES 70
0 INVALID PROCEDURE 1
0 INVALID 1
0 VALID INDEX 60
0 VALID LOB 19
0 VALID PACKAGE 1
0 VALID PROCEDURE 2
0 VALID SEQUENCE 2
0 VALID TABLE 61
0 VALID TABLE PARTITION 23
0 VALID TABLE SUBPARTITION 26
0 VALID TRIGGER 3
0 VALID TYPE 5
0 VALID 202
1 INDEX 60
1 LOB 19
1 PACKAGE 1
1 PROCEDURE 3
1 SEQUENCE 2
1 TABLE 61
1 TABLE PARTITION 23
1 TABLE SUBPARTITION 26
1 TRIGGER 3
1 TYPE 5
1 203
---从结果可以看出，CUBE是对三个维度OWNER,STATUS,OBJECT_TYPE,多个层次的汇总。
--cube(OWNER,STATUS,OBJECT_TYPE)等价于group by (owner,status)+group by (owner,object_type),group by (status,object_type)
--+group by (owner)+group by (status)+group by (object_type)
--GROUPING(STATS)为1 代表去除掉STATUS字段后所有的汇总，从STATUS为空，结果为1的记录可以看出。
-----------------------------------------------------------------------------------------------------------------------------------
--GROUPING SETS语法，只有10.1.0以上的版本才支持，下面的数据是DUMMY的，帮助理解
SELECT WINDOW,ROOM_STYPE,ROUND(SUM(SQ_FT),2) SUM_SQ_FT
FROM SHIP_CABINS
WHERE SHIP_ID = 1
GROUP BY GROUPING SETS((WINDOW,ROOM_STYLE),(ROOM_TYPE),NULL)
ORDER BY WINDOW,ROOM_STYLE,ROOM_TYPE;
WINDOW ROOM_STYPE ROOM_TYPE SUM_SQ_FT
---------------- ------------------------------ -------------------
NONE STATEROOM 616
NONE SUITE 2261
OCEAN STATEROOM 365
OCEAN SUITE 4524
LARGE 436
PRESIDENTIAL 1142
ROYAL 3269
SKYLOFT 722
STANDARD 2197
7766
----------------------------------------------------------------------------------------------------------------------------------
--INSERT MUTIPLE TABLES
admin@ORCL> DESC T_TAB
名称 是否为空? 类型
----------------------------------------------------- -------- -------------
TABLE_NAME VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
OBJECT_TYPE VARCHAR2(30)
admin@ORCL> DESC T_IDX
名称 是否为空? 类型
----------------------------------------------------- -------- -------------
TABLE_NAME VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
OBJECT_TYPE VARCHAR2(30)
admin@ORCL> DESC T_OTHERS;
名称 是否为空? 类型
----------------------------------------------------- -------- -------------
OBJECT_NAME VARCHAR2(30)
OBJECT_TYPE VARCHAR2(30)
--数据同时插入两个表
INSERT ALL
INTO T_TAB(TABLE_NAME,TABLESPACE_NAME) VALUES(TABLE_NAME,TABLESPACE_NAME)
INTO T_IDX(TABLE_NAME,TABLESPACE_NAME) VALUES(TABLE_NAME,TABLESPACE_NAME)
SELECT TABLE_NAME,TABLESPACE_NAMEFROM DBA_TABLES;
SELECT COUNT(*) FROM T_TAB;
COUNT(*)
----------
1755
SELECT COUNT(*) FROM T_IDX;
COUNT(*)
----------
1755
--数据根据不同条件插入
INSERT ALL
WHEN (OBJECT_TYPE = 'TABLE') THEN
INTO T_TAB(TABLE_NAME,OBJECT_TYPE) VALUES(OBJECT_NAME,OBJECT_TYPE)
WHEN (OBJECT_TYPE = 'INDEX') THEN
INTO T_IDX(INDEX_NAME,OBJECT_TYPE) VALUES(OBJECT_NAME,OBJECT_TYPE)
ELSE
INTO T_OTHERS(OBJECT_NAME,OBJECT_TYPE) VALUES(OBJECT_NAME,OBJECT_TYPE)
SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS;
SELECT COUNT(*) FROM DBA_OBJECTS;
COUNT(*)
---------
50834
SELECT COUNT(*) FROM T_TAB;
COUNT(*)
---------
1811
SELECT COUNT(*) FROM T_IDX;
COUNT(*)
----------
1955
SELECT COUNT(*) FROM T_OTHERS;
COUNT(*)
----------
47068
--ALL 与 FIRST 的区别
--ALL与判断所有的条件，而FRIST满足一个条件就会返回。
TRUNCATE TABLE T_TAB;
TRUNCATE TABLE T_IDX;
TRUNCATE TABLE T_OTHERS;
INSERT ALL
WHEN (OBJECT_TYPE = 'TABLE') THEN
INTO T_TAB(TABLE_NAME,OBJECT_TYPE) VALUES(OBJECT_NAME,OBJECT_TYPE)
WHEN (OBJECT_TYPE = 'TABLE') THEN
INTO T_IDX(INDEX_NAME,OBJECT_TYPE) VALUES(OBJECT_NAME,OBJECT_TYPE)
ELSE
INTO T_OTHERS(OBJECT_NAME,OBJECT_TYPE) VALUES(OBJECT_NAME,OBJECT_TYPE)
SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS;
SELECT COUNT(*) FROM T_TAB;
COUNT(*)
----------
1811
SELECT COUNT(*) FROM T_IDX;
COUNT(*)
----------
1811
---修改为FIRST
TRUNCATE TABLE T_TAB;
TRUNCATE TABLE T_IDX;
TRUNCATE TABLE T_OTHERS;
INSERT FIRST
WHEN (OBJECT_TYPE = 'TABLE') THEN
INTO T_TAB(TABLE_NAME,OBJECT_TYPE) VALUES(OBJECT_NAME,OBJECT_TYPE)
WHEN (OBJECT_TYPE = 'TABLE') THEN
INTO T_IDX(INDEX_NAME,OBJECT_TYPE) VALUES(OBJECT_NAME,OBJECT_TYPE)
ELSE
INTO T_OTHERS(OBJECT_NAME,OBJECT_TYPE) VALUES(OBJECT_NAME,OBJECT_TYPE)
SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS;
SELECT COUNT(*) FROM T_TAB;
COUNT(*)
----------
1810
SELECT COUNT(*) FROM T_IDX;
COUNT(*)
----------
0
页:
[1]