--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 STATUS OBJECT_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 STATUS OBJECT_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 STATUS OBJECT_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 STATUS OBJECT_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 STATUS OBJECT_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 STATUS OBJECT_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_NAME FROM 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
相关推荐
根据提供的文档信息，我们可以推断出这份文档主要讨论了Oracle 10g OCP认证考试中的部分题目解析，特别是关于SQL语句的应用场景和技术选择。接下来，我们将详细解析文档中提及的重要知识点，并对其进行深入探讨。 #...
根据提供的文件信息，我们可以深入探讨Oracle Database 11g OCP升级考试1Z0-050中的几个关键知识点。 ### 1. SQL计划基线（SQL Plan Baseline）加载来源 题目要求识别两个可以直接加载到SQL计划基线中的来源。选项...
Oracle 11g OCP认证是Oracle公司提供的数据库管理专家认证之一，其中051考试是针对SQL基础知识的测试。OCP全称为Oracle Certified Professional，是继Oracle Certified Associate（OCA）之后的进阶认证。通过11g OCP...
### Oracle Certified Professional (OCP) 11g: SQL Fundamentals I (1Z0-051) V9.02知识点解析 #### 一、考试概述与版本介绍 本资料为最新的Oracle Certified Professional (OCP) 11g: SQL Fundamentals I (1Z0-...
### OCP1Z0-052.188答案解析 #### 1. 查看展览并检查undo表空间的属性。 - **知识点**: 在Oracle数据库中，undo表空间是用于存储事务处理过程中未提交更改的数据。这部分内容讨论了如何查看undo表空间的属性，并...
- 在这个例子中，虽然`CREATE TABLE`命令中的`order_date`列与`SELECT`子句中的`time_id`列名称不一致，但Oracle允许在`SELECT`子句中使用不同的列名来创建新表。因此，选项C错误。 - **选项D**：“`NEW_SALES`表会...
根据提供的信息，我们可以详细解析与Oracle 11g OCP-051考试相关的知识点，主要聚焦于SQL命令以及数据库表结构的操作。 ### SQL命令详解 #### 1. CREATE TABLE命令 CREATE TABLE 命令用于创建一个新的表，并定义表...
通过以上对题目的分析，可以看出OCP认证考试涵盖了Oracle数据库中的关键概念和技术细节，如表结构、视图权限、SQL查询语法等。这些知识点对于准备参加OCP认证考试的专业人士来说至关重要，掌握这些知识点将有助于...
例如，在给定的例子中，正确的顺序是1,4,3,2（Mount数据库、恢复数据文件、恢复数据、打开数据库）。 ### 3. Managing Database Performance & Tuning #### 3.1 性能管理与调优 - **性能监控**：了解数据库性能...
1. OCP认证与Oracle Database SQL Fundamentals IV（1Z0-071）考试： - OCP是Oracle认证专家（Oracle Certified Professional）的缩写，是IT行业广泛认可的职业资格认证之一。 - Oracle Database SQL Fundamentals...
1. **存储过程和函数**：PL/SQL允许开发人员定义自己的函数和过程，这些可以在数据库中执行复杂的业务逻辑。例如，可能会有创建一个过程来批量更新数据，或者定义一个函数来计算特定的业务指标。 2. **游标**：在...
根据提供的标题、描述以及部分内容，可以看出这是一份关于Oracle Certified Professional (OCP) 11g 1Z0-051考试题目的答案解析文档。这份文档的主要目的是帮助考生理解考试中的具体问题，并通过详细解释来加深他们...
在《Oracle Database 11g OCP 认证全合一考试指南》这本书中，作者首先从数据库的基本概念入手，介绍了数据库的定义、SQL语言的基础以及Oracle 10g支持的数据库对象等内容。 ##### 1.1 数据库及其关系模型介绍 - *...
1. 表现层（Presentation Layer）：这是用户与应用交互的界面，负责接收用户的输入，显示处理结果。在.NET环境中，通常使用Windows Forms、ASP.NET或WPF等技术来构建。在这个例子中，ThreeTierTest可能包含了用于...
在这个例子中，由于 `USER1.EMP` 表中的 `DNO` 字段作为外键参照了 `USER1.DEPT` 表中的 `DNO` 字段，因此直接尝试删除 `USER1.DEPT` 表中的 `DNO` 列会导致违反引用完整性规则，从而导致操作失败。 ### 知识点二：...
这个例子是学习和实践设计模式的好机会，例如单一职责原则（SRP）、开闭原则（OCP）等，这些都可以提升代码质量。 总结，这个“asp.net三层架构例子”涵盖了ASP.NET开发中基础的三层架构设计，通过实际的注册功能...
本书共四篇59章：第一篇是Oracle SQL*Plus讲述Oracle应用系统设计的基本知识；第二篇是Oracle PL/SQL基础，讲述Oracle...总之，本书基本上包括当前Oracle 8i的最新内容，也覆盖了最新的Oracle 8i OCP培训大纲的内容。
这部分代码可能会包含与 SQL Server 数据库的连接和交互，如`SqlHelper`类或使用Entity Framework等ORM框架。 3. **Serialization**：由于业务对象可以在客户端和服务器之间传输，因此需要序列化和反序列化机制。...
这部分内容会涉及变量声明、流程控制、异常处理等基础知识，并通过实际例子来展示如何在Oracle环境中运用PL/SQL。 此外，本教程还涵盖了Oracle8i OCP（Oracle Certified Professional）认证考试的相关内容，适合于...