sql server命令怎样使用一个数据库 sql数据库怎么用sql语句

使用SQL语句

SQL是关系数据库的基本操作语言，它是应用程序与数据库进行交互操作的接口，SQL语言包括：

l         数据查询语言(SELECT)

l         数据操纵语言(INSERT，UPDATE，DELETE)

l         事务控制语言(COMMIT，ROLLBACK，SAVEPOINT)

l         数据定义语言(CREATE，ALTER，DROP)

l         数据控制语言(GRANT，REVOKE)

当编写PL/SQL应用程序时，只能直接嵌入SELECT语句、DML语句和事务控制语句。

 

一． 使用基本查询

SELECT语句用于检索数据库的数据。

 

1.1简单查询语句

基本语法：

SELECT <*,column [alias],…> FROM talbe;

l         SELECT关键字用于指定要检索的列

l         星号(*)表示检索所有列

l         column用于指定要索引的列或表达式

l         alias用于指定列或表达式的别名

l         FROM关键字用于指定要检索的表

l         table用于指定要索引的表名

 

(1)    确定表结构

如要要检索特定表列的数据，必须要清除表的结构。通过使用SQL*Plus的DESCRIBE命令(可以简写为DESC)，可以显示表结构。

 

示例：

DESC dept;

 

(2)    检索所有列

为了检索表的所有列数据，可以在SELECT关键字后指定星号(*)。

 

示例：

SELECT * FROM dept;

 

(3)    检索特定列

当检索表的特定列时，可以在SELECT关键字后指定列名。如果要检索多个列的数据，那么列之间使用逗号(,)隔开。

 

示例：

SELECT ename,sal,job,deptno FROM emp;

(4)    检索日期列

检索日期列与检索其他列没有任何区别。注意，日期数据的默认显示格式为“DD-MON-YY”，如果希望使用其他显示格式(YYYY-MM-DD)，那么必须使用TO_CHAR函数进行转换。另外，不同语言、地区的日期显示结构会有所不同。例如，如果语言为“SIMPLIFIED CHINESE”，则月名显示为中文格式(例如5月)；如果语言为“AMERICAN”，则月名显示为英文简写格式(例如MAY)。

使用默认日期显示格式显示雇员雇用日期

示例：

SELECT ename,hiredate FROM emp;

使用YYYY-MM-DD显示格式显示雇员雇用日期

示例：

SELECT ename,TO_CHAR(hiredate,'YYYY-MM-DD') FROM emp;

(5)    取消重复行

可以使用DISTINCT关键字来取消重复行。

显示所有部门号及岗位(保留重复行)

示例：

SELECT deptno,job FROM emp;

显示所有部门号及岗位(取消重复行)

示例：

SELECT DISTINCT deptno,job FROM emp;

(6)    使用算术表达式

当执行查询操作时，可以在数字列上使用算术表达式(+，-，*，/)，其中乘、除的优先级要高于加、减。如果要改变优先级，那么可以使用括号。

示例：

SELECT ename,sal*12 FROM emp;

(7)    使用列别名

默认情况下，列标题是大写格式的列名或表达式。通过使用列别名，可以改变列标题的显示样式。如果要使用列别名，那么列别名应在列或者表达式之后，在二者之间可以加AS关键字。注意，如果列别名有大小写之分，并包含特殊字符或空格，那么这样的别名必须要用双引号引住。

示例：

SELECT ename AS "姓名",sal*12 AS "年收入" FROM emp;

 

(8)    处理NULL

NULL表示未知值，它既不是空格也不是0。当给表插入数据时，如果没有给某列提供数据，并且该列没有默认值，那么其数据为NULL。注意，当算术表达式包含NULL时，其结果也是NULL。

示例：

SELECT ename,sal,comm,sal+comm FROM emp;

 

使用NVL函数处理NULL值

NVL函数用于将NULL转变为实际值，其语法格式为NVL(expr1,expr2)。如果expr1是NULL，则返回expr2；如果expr1不是NULL，则返回expr1。参数expr1和expr2可以是任意数据类型，但二者的数据类型必须要匹配。

示例：

SELECT ename,sal,comm,sal+nvl(comm,0) AS "月收入" FROM emp;

 

使用NVL2函数处理NULL值

NVL2是Oracle9i新增加的函数，该函数也用于处理NULL，其语法格式为NVL2(expr1,expr2,expr3)。如果expr1不是null，则返回expr2；如果expr1是null，则返回expr3。参数expr1可以是任意数据类型，而expr2和expr3可以是除LONG之外的任何数据类型。注意，expr3的数据类型必须要与expr1的数据类型匹配。

示例：

SELECT ename,NVL2(comm,sal+comm,sal) FROM emp;

 

(9)    连接字符串

可以使用“||”操作符来连接字符串。当连接字符串时，如果在字符串中要加入数字值，那么在“||”后可以直接指定数字；如果在字符串中要加入字符和日期值，则必须用单引号引住。

示例：

SELECT ename||' is a '||job AS "Employee Detail" FROM emp;

 

1.2使用WHERE 字句

WHERE字句用于指定限制条件。

语法：

SELECT <*,column [alias],…> FROM table [WHERE condition(s)];

l         WHERE关键字用于指定条件字句

l         condition用于指定具体的条件，如果条件字句返回值为TRUE，则会检索相应行的数据，如果条件为FALSE，则不会检索该行数据。

下表为条件字句所用到的比较操作符：

比较操作符	含义
=	等于
<>、!=	不等于
>=	大于等于
<=	小于等于
>	大于
<	小于
BETWEEN…AND…	在两值之间
IN(list)	匹配于列表值
LIKE	匹配于字符样式
IS NULL	测试NULL

 

(1)    在WHERE条件中使用数字值

当在WHERE条件中使用数字值时，既可以使用单引号引住数字值，也可以直接引用数字值。

示例：

SELECT ename,sal FROM emp WHERE sal > 2000;

(2)    在WHERE 条件中使用字符值

当在WHERE条件中使用字符值时，必须要用单引号引住。

示例：

SELECT job,sal FROM emp WHERE ename = 'SCOTT';

 

注意，因为字符值区分大小写，所以在引用字符值时必须要指定正确的大小写格式，否则不能正确输出信息。为了避免字符值的大小写问题，可以使用函数UPPER或LOWER转换大小写。

示例：

SELECT job,sal FROM emp WHERE LOWER(ename) = 'scott';

(3)    在WHERE条件中使用日期值

当在WHERE条件中使用日期值时，必须要用单引号引住，并且日期值必须要符合日期显示格式。如果日期值不符合默认日期显示格式，那么必须使用TO_DATE函数进行转换。

符合默认日期格式

示例：

SELECT ename,sal,hiredate FROM emp
WHERE hiredate > '01-1月-82';

不符合默认日期格式

示例：

SELECT ename,sal,hiredate FROM emp
WHERE hiredate > TO_DATE(' 
  
    1982-01-01 
  ','YYYY-MM-DD');

(4)    在WHERE条件中使用BETWEEN…AND操作符

BETWEEN…AND…操作符用于指定特定范围条件，在BETWEEN操作符后指定较小的一个值，在AND操作符后则指定较大的一个值。

示例：

SELECT ename,sal,hiredate,job FROM emp
WHERE sal BETWEEN 1100 AND 1600;

(5)    在WHERE条件中使用LIKE操作符

LIKE操作符用于执行模糊查询。当执行查询操作时，如果不能完全确定某些信息的查询条件，但这些信息又具有某些特征，那么可以使用模糊查询。当执行模糊查询时，需要使用通配符“%”和“_”，其中“%”(百分号)用于表示0个或多个字符，而“_”(下划线)则用于表示单个字符。如果要将通配符“%”和“_”作为字符值使用，那么需要在ESCAPE之后使用转义符。

显示首字符为S的所有雇员名及其工资

示例：

SELECT ename,sal FROM emp WHERE ename LIKE 'S%';

显示第三个字符为大写A的所有雇员名及其工资

示例：

SELECT ename,sal FROM emp WHERE ename LIKE '__A%';

显示雇员名包含“_”的雇员信息(其中ESCAPE后的字符a为转义符)

示例：

SELECT ename,sal FROM emp
WHERE ename LIKE '%a_%' ESCAPE 'a';

(6)    在WHERE条件中使用IN操作符

IN操作符用于执行列表匹配操作。当列或表达式结果匹配于列表中的任一个值时，条件字句返回TRUE。

示例：

SELECT ename,sal FROM emp WHERE sal IN (800,1250);

(7)    在WHERE条件中使用IS NULL操作符

IS NULL操作符用于检测列或表达式的结果是否为NULL。如果结果为NULL，则返回TRUE；否则返回FALSE。

示例：

SELECT ename,sal FROM emp WHERE mgr IS NULL;

当与NULL进行比较时，不要使用等于(=)、不等于(<>)操作符。尽管使用它们不会有任何语法错误，但条件字句返回总是FALSE。

 

(8)    在WHERE字句中使用逻辑操作符

操作符	含义
AND	如果条件都是TRUE，则返回TURE,否则返回FALSE
OR	如果任一个条件是TRUE，则返回TURE，否则返回FALSE
NOT	如果条件是FALSE，则返回TRUE；如果条件是TRUE，则返回FALSE

逻辑操作符AND、OR、NOT的优先级低于任一种比较操作符，在这三个操作符中，NOT优先级最高，AND其次，OR最低。如果要改变优先级，则需要使用括号。注意，NOT操作符主要与BETWEEN…AND，LIKE，IN以及IS NULL结合使用。

显示在部门20中岗位CLERK的所有雇员信息

示例：

SELECT ename,sal,job,deptno FROM emp
WHERE deptno = 20 AND job = 'CLERK';

显示工资高于2500或岗位为MANAGER的所有雇员信息

示例：

SELECT ename,sal,job,deptno FROM emp
WHERE sal > 2500 OR job = 'MANAGER';

显示补助非空的雇员信息

示例：

SELECT ename,sal,comm FROM emp
WHERE comm IS NOT NULL;

1.3使用ORDER BY字句

在执行查询操作时，默认情况下会按照行数据插入的先后顺序来显示行数据。但在实际应用中经常需要对数据进行排序，以显示更直观的数据，数据排序是使用ORDER BY字句来完成的，其语法如下：

SELECT <*,column [alias],…> FROM table [WHERE condition(s)] [ORDER BY expr {ASC|DESC}];

l         expr用于指定要排序的列或表达式

l         ASC用于指定进行升序排序(默认)

l         DESC用于指定进行降序排序

注意，当在SELECT语句中同时包含多个子句(WHERE ,GROUP BY,HAVING,ORDER BY等)时，ORDER BY必须是最后一条子句。

 

(1)    升序排序

默认情况下，当使用ORDER BY执行排序操作时，数据以升序方式排序。在执行升序排序时，也可以在排序列后指定ASC关键字。

示例：

SELECT ename,sal FROM emp WHERE deptno = 30
ORDER BY sal;

 

当执行升序排序时，如果被排序列包含NULL值，那么NULL会显示在最后面。

示例：

SELECT ename,sal,comm FROM emp WHERE deptno = 30
ORDER BY comm;

 

(2)    降序排序

当使用ORDER BY子句执行排序操作时，默认情况下会执行升序排序。为了执行降序排序，必须要指定DESC关键字。

示例：

SELECT ename,sal,comm FROM emp WHERE deptno = 30
ORDER BY sal DESC;

 

注意，当执行降序排序时，如果排序列存在NULL值，那么NULL会显示在最前面。

示例：

SELECT ename,sal,comm FROM emp WHERE deptno = 30
ORDER BY comm DESC;

(3)    使用多列排序

当使用ORDER BY字句执行排序操作时，不仅可以基于单个列或单个表达式进行排序，也可以基于多个列或多个表达式进行排序。当以多个列或多个表达式进行排序时，首先按照第一个列或表达式进行排序，当第一个列或表达式存在相同数据时，然后以第二个列或表达式进行排序。

示例：

SELECT ename,sal,comm FROM emp WHERE deptno = 30
ORDER BY sal ASC,comm DESC;

 

(4)    使用非选择列表列进行排序

当使用ORDER BY子句执行排序操作时，多数情况下选择列表都会包含被排序列。但在实际情况下，选择列表可以不包含排序列。

示例：

SELECT ename FROM emp ORDER BY sal DESC;

(5)    使用列别名排序

如果在WHERE子句中为列表或表达式定义了别名，那么当执行排序操作时，既可以使用列或表达式进行排序，也可以使用列别名进行排序。

示例：

SELECT ename,sal*12 AS "全年工资" FROM emp
WHERE deptno = 30
ORDER BY "全年工资" DESC;

(6)    使用列位置编号排序

当执行排序操作时，不仅可以指定列名、列别名进行排序，也可以按照列或表达式在选择列表中的位置进行排序。如果列名或表达式名称很长，那么使用列位置排序可以缩短排序语句的长度。另外当使用UNION，UNION ALL，INTERSECT，MINUS等集合操作符合并查询结果时，如果选择列表中的列名不同，并且希望进行排序，那么必须使用列位置。

示例：

SELECT ename,sal*12 "全年工资" FROM emp
WHERE deptno = 20 ORDER BY 2 DESC;

 

二． 使用DML语句

DML语句(INSERT，UPDATE，DELETE)用于操纵表和视图的数据。通过执行INSERT语句，可以给表增加数据；通过执行UPDATE语句，可以更新表的数据；通过执行DELETE语句，可以删除表的数据。

 

2.1插入数据

当要给表增加数据时，可以使用INSERT语句。使用INSERT语句既可以为表插入单行数据，也可以通过子查询将一张表的多行数据插入到另一张表中。从Oracle9i开始，Oracle还提供了多表插入功能，即使用一条INSERT语句同时为多张表插入数据。使用INSERT语句插入数据时，应注意：

l         如果为数字列插入数据，则可以直接提供数字值；如果为字符列或日期列插入数据，则必须使用单引号引住

l         当插入数据时，数据必须要满足约束规则，并且必须要为主键列和NOT NULL列提供数据

l         当插入数据时，数据必须要与列的个数和顺序保持一致

 

(1)    插入单行数据

插入单行数据是使用INSERT…VALUES语句来完成的，语法：

INSERT INTO<table> [(column[,column,…])] VALUES(value[,value,…])

l         Table用于指定表名或视图名

l         Column用于指定列名，如果要指定多个列，那么列之间要用逗号分开

l         Value用于提供列数据。当使用INSERT语句插入数据时，既可以指定列列表，也可以不指定列列表。如果不指定列列表，那么在VALUSE子句中必须要为每个列提供数据，并且数据顺序要与表列顺序完全一致。如果指定列的列表，则只需要为相应列提供数据。

不使用列的列表插入单行数据

当使用INSERT插入数据时，可以不指定列的列表。

注意，如果不指定列的列表，那么必须要为所有列提供数据，并且数据的顺序必须与列的顺序保持一致。

示例：

INSERT INTO dept VALUES(50,'TRAIN',' 
   
   
     BOSTON 
    
  ');

使用列的列表插入单行数据

当使用列的列表插入数据时，只需为相应列提供数据。而对于那些未出现在列的列表中的列，其数据为NULL。

示例：

INSERT INTO emp(empno,ename,job,hiredate)
VALUES(1234,'JOHN','CLERK','01-3月-86');

 

使用特定格式插入日期值

在使用INSERT语句为表插入数据时，默认情况下日期值必须要与日期格式、日期语言匹配，否则在插入数据时会显示错误信息。如果用户希望使用习惯方式插入日期数据，那么必须要使用TO_DATE函数进行转换。

示例：

INSERT INTO emp(empno,ename,job,hiredate)
VALUES(1356,'MARY','CLERK',TO_DATE(' 
  
    1983-10-20 
  ','YYYY-MM-DD'));

使用DEFAULT提供数据

从Oracle9i开始，当使用INSERT语句插入数据时，可以使用DEFAULT关键字提供数值。当指定DEFAULT时，如果列存在默认值，则会使用其默认值；如果列不存在默认值，则自动使用NULL。

示例：

INSERT INTO dept VALUES(60,'MARKET',DEFAULT);

(2)    使用子查询插入数据

当使用VALUES子句插入数据时，一次只能插入一行数据；当使用子查询插入数据时，可以将一张表的数据复制到另一张表中。当处理迁移、复制表数据或者装载外部表数据到数据库时，可以使用子查询插入数据，语法：

INSERT INTO <table> [(column[,column,…])] subQuery

l         subquery用于指定为目标表提供数据的子查询。当使用子查询插入数据时，INSERT列的数据类型和个数必须要与子查询列的数据类型和个数完全匹配

使用子查询插入数据

示例：

INSERT INTO employee(empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno FROM emp
WHERE deptno = 20;

使用子查询执行直接装载

示例：

INSERT /*+APPEND*/ INTO employee(empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno FROM emp
WHERE deptno = 20;

 

尽管以上两条语句的执行结果一样，但第二条语句使用？*+APPEND*/来表示采用直接装载方式。当要装载大批量数据时，采用第二种方法装载数据的速度要远远优于第一种方法。

 

(3)    使用多表插入数据

可以使用INSERT将某张表的数据同时插入到多张表中，语法：

INSERT ALL insert_into_clause [value_clause] subquery;
INSERT conditional_insert_clause subquery;

l         insert_into_clause用于指定INSERT子句

l         value_clause用于指定值子句

l         subquery用于指定提供数据的子查询

l         conditional_insert_clause用于指定INSERT条件子句

 

使用ALL操作符执行多表插入

当使用ALL操作符执行夺标插入时，在每个条件子句上都要执行INTO子句后的子查询。

示例：

INSERT ALL
  WHEN deptno = 10 THEN INTO dept10
  WHEN deptno = 20 THEN INTO dept20
  WHEN deptno = 30 THEN INTO dept30
  WHEN job = ‘CLERK’ THEN INTO clerk
  ELSE INTO other
  SELECT * FROM emp;

 

使用FIRST操作符执行夺标插入

当使用FIRST操作符执行多表插入时，如果数据已经满足了先前条件，并且已经被插入到某表，那么该行数据在后续插入中将不会被再次使用。

示例：

INSERT FIRST
  WHEN deptno = 10 THEN INTO dept10
  WHEN deptno = 20 THEN INTO dept20
  WHEN deptno = 30 THEN INTO dept30
  WHEN job = ‘CLERK’ THEN INTO clerk
  ELSE INTO other
  SELECT * FROM emp;

 

2.2更新数据

当要更新表中行的数据时，可以使用UPDATE语句。使用UPDATE语句时，既可以使用表达式更新列表，也可以使用子查询更新一列或多列的数据。使用UPDATE语句应注意以下事项：

l         如果要更新数自列，则可以直接提供数字值；如果要更新字符列或日期列，则数据必须用单引号引住

l         当更新数据时，数据必须要满足约束规则

l         当更新数据时，数据必须要与列的数据类型匹配

 

(1)    使用表达式更新数据

当要直接修改某行或某几行的数据时，可以在SET子句中直接指定列的新值或者指定表达式。

注意，当执行UPDATE语句时，如果未指定WHERE子句，则会修改表中的所有行的数据。

语法：

l         table用于指定要更新的表

l         view用于指定要更新的视图

l         column用于指定要更新的列

l         value用于指定更新后的列值

l         condition用于指定条件子句

 

更新单列数据

示例：

UPDATE emp SET sal = 2460 WHERE ename = 'SCOTT';

更新多列数据

当使用UPDATE语句修改表中行的数据时，既可以修改一列，也可以修改多列。当修改多列时，列之间用逗号分开。

示例：

UPDATE emp SET sal = sal*1.1,comm = sal*0.1
WHERE deptno = 20;

更新日期列数据

当更新日期列中的数据时，数据格式一定要与默认日期格式、日期语言相匹配，否则会显示错误信息。如果使用习惯方式指定日期值，就需要使用TO_DATE函数进行转换。

示例：

UPDATE emp SET hiredate = TO_DATE(' 
  
    1984/01/01 
  ','YYYY/MM/DD')
WHERE empno = 7788;

使用DEFAULT选项更新数据

从Oracle9i开始，当执行UPDATE语句更新列数据时，可以使用DEFAULT选项提供数据值。如果列存在默认值，则会使用默认值更新数据；如果列不存在默认值，则使用NULL。

示例：

UPDATE emp SET job = DEFAULT WHERE ename = 'SCOTT';

更新违反约束规则的数据

当使用UPDATE语句更新数据时，新数据必须满足约束规则，否则会显示错误信息。

示例：

UPDATE emp SET deptno = 55 WHERE empno = 7788;

输出：

UPDATE emp SET deptno = 55 WHERE empno = 7788 
  
*

第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 未找到父项关键字

 

因为在DEPT表中不存在部门55，所以当修改DEPTNO列为55时会违反参照完整性约束。

 

(2) 使用子查询更新数据

当使用UPDATE语句更新数据时，不仅可以使用表达式或数值直接更新数据，也可以使用子查询更新数据。某些情况下，使用子查询执行效率更好。另外，当使用触发器复制表之间的数据时，使用子查询可以更新相关表的数据。

 

更新关联数据

当更新关联数据时，使用子查询可以降低网络开销。

UPDATE emp SET(job,sal,comm)=(
SELECT job,sal,comm FROM emp WHERE ename = 'SMITH')
WHERE ename = 'SCOTT';

 

复制表数据

当使用触发器复制表数据时，如果表A数据被修改，那么表B数据也应该修改。通过使用子查询，可以基于一张表修改另一张表的数据。

示例：

UPDATE employee SET deptno = 
(SELECT deptno FROM emp WHERE empno = 7788)
WHERE job = (SELECT job FROM emp WHERE empno = 7788);

2.3删除数据

当要删除表中某行的数据时，可以使用DELETE语句。使用该语句既可以删除一行数据，也可以删除多行数据。

DELETE FROM <table|view> [WHERE <condition>];

l         table用于指定表名

l         view用于指定视图名

l         condition用于指定条件子句

当使用DELETE语句删除数据时，如果不指定WHERE条件子句，那么会删除表或视图的所有行。

 

删除满足条件的数据

当使用DELETE语句删除数据时，通过指定WHERE子句可以删除满足条件的数据。

示例：

DELETE FROM emp WHERE ename = 'SMITH';

删除表的所有数据

当使用DELETE语句删除表中的数据时，如果不指定WHERE子句，那么会删除表中的所有数据。

示例：

DELETE FROM emp;

使用TRUNCATE TABLE截断表

当使用DELETE语句删除表的所有数据时，不会释放表所占用的空间。如果用户确定要删除表的所有数据，那么使用“TRUNCATE TABLE”语句速度更快。

示例：

TRUNCATE TABLE emp;

注意，使用TRUNCATE TABLE语句不仅会删除表的所有数据，而且还会释放表段所占用的空间。DELETE语句的操作可以回退，但TRUNCATE TABLE语句的操作不会回退。

 

使用子查询删除数据

当使用DELETE语句删除数据时，可以直接在WHERE子句中指定值，并根据条件来删除数据。另外，也可以在WHERE子句中使用子查询作为条件。

示例：

DELETE FROM emp WHERE deptno = 
(SELECT deptno FROM dept WHERE dname = 'SALES');

删除主表数据的注意事项

当使用DELETE语句删除数据时应该注意：当删除主表数据时，必须要确保从表不存在相关记录，否则会显示错误信息。

示例：

DELETE FROM dept WHERE deptno = 10;

输出：

DELETE FROM dept WHERE deptno = 10

*

第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 已找到子记录

 

因为在DEPT表和EMP表之间有主从关系，所以当删除主表(DEPT)数据时，必须要确保从表(EMP)不存在相关子记录。

 

三． 使用事务控制语句

事务用于确保数据库数据的一致性，它由一组相关的DML语句组成。该组DML语句所执行的操作要么全部成功，要么全部取消。

数据库事务主要由INSERT，UPDATE，DELETE和SELECT…FOR UPDATE语句组成。当在应用程序中执行第一条SQL语句时，开始事务；当执行COMMIT或ROLLBACK语句时结束事务。

 

3.1事务和锁

当执行事务操作(DML语句)时，Oracle会在被作用表上加表锁，以防止其他用户改变表结构；同时会在被作用行上加行锁，以防止其他事务在相应行上执行DML操作。假定会话A更新EMP表行的数据，那么会在表EMP上加表锁；此时如果其他会话修改表结构，则会显示错误信息。

在Oracle数据库中，为了确保数据库数据的读一致性，不允许其他用户读取脏数据(为提交事务)。假定会话A将雇员SCOTT工资修改为2000(未提交)，那么其他会话将只能查询到原来的工资；只有在会话A提交了事务之后，其他会话才能查询到新工资。

 

3.2提交事务

使用COMMIT语句可以提交事务。当执行了COMMIT语句之后，会确认事务变化、结束事务、删除保存点、释放锁。当使用COMMIT语句结束事务之后，其他会话将可以查看到事务变化后的新数据。

注意，当出现以下情况时会自动提交事务：

l         当执行DDL语句时会自动提交事务，例如CREATE TABLE，ALTER TABLE，DROP TABLE等语句

l         当执行DCL语句(GRANT、REVOKE)时

l         当推出SQL*Plus时

 

3.3回退事务

保存点是事务中的一点，它用于取消部分事务。当结束事务时，会自动删除该事务所定义的所有保存点。在执行ROLLBACK命令时，通过指定保存点可以取消部分事务。

 

(1)    设置保存点

设置保存点是使用SQL命令SAVEPOINT来完成的。另外，开发人员在编写应用程序时，也可以使用包DBMS_TRANSACTION的过程SAVEPOINT来设置保存点。

示例：

sevepoint a;
或
exec dbms_transaction.savepoint(‘a);

(2)    取消部分事务

为了取消部分事务，用户可以回退到保存点。回退到保存点既可以使用ROLLBACK命令，也可以使用包DBMS_TRANSACTION的过程ROLLBACK_SAVEPOINT。

示例：

rollback to a;
或
exec dbms_transaction.rollback_savepoint(‘a);

(3)    取消全部事务

使用ROLLBACK命令可以取消全部事务，开发人员在编写应用程序时也可以使用包DBMS_TRANSACTION的过程ROLLBACK取消全部事务。

示例：

rollback;
或
exec dbms_transaction.rollback;

当使用ROLLBACK取消全部事务时，会取消所有事务变化、结束事务、删除所有保存点并释放锁。当出现系统灾难或应用程序地址例外时，会自动回退其事务变化。

 

3.4只读事务

只读事务是指只允许执行查询操作，而不允许执行任何DML操作的事务。当使用只读事务时，可以确保用户取得特定时间点的数据。假定企业需要在每天16点统计最近24小时的销售信息，而不统计当天16点之后的销售信息，那么用户可以使用只读事务。在设置了只读事务之后，尽管其他会话可能会提交新事务，但只读事务将不会取得新的数据变化，从而确保取得特定时间点的数据信息。

注意，当设置只读事务时，该语句必须是事务开始的第一条语句。另外在应用程序中，使用过程READ_ONLY也可以设置只读事务。

示例：

SET TRANSACTION READ_ONLY;
或
exec dbms_transaction.real_only;

3.5顺序事务

只读事务可以使得用户取得特定时间点的数据信息，但当设置了只读事务时，会话将不能执行INSERT/UPDATE/DELETE等DML操作。为了使得用户可以取得特定时间点的数据，并且允许执行DML操作，可以使用顺序事务。

假定会话A在时间点1设置了顺序事务，会话B在时间点2更新了SMITH的工资并执行了提交操作，会话A在时间点3查询SMITH工资时会取得时间点1的工资信息，而不会取得时间点2的新工资值。注意，当设置顺序事务时，该语句必须是事务开始的第一条语句。

示例：

SET TRANSACTION ISOLATION LEVEL,SERIALIZABLE;

四． 数据分组

在关系数据库中，数据分组是通过使用GROUP BY子句、分组函数以及HAVING子句共同实现的。

l GTOUP BY子句：用于指定要分组的列(例如DEPTNO)

l 分组函数：用于显示统计结果(如COUNT，AVG，SUM等)

l HAVING子句：用于限制分组显示结果。

 

4.1分组函数

分组函数用于统计表的数据。与单行函数不同，分组函数作用于多行，并返回一个结果，所以有时也被称为多行函数。一般情况下，分组函数要与GROUP BY子句结合使用。在使用分组函数时，如果忽略了GROUP BY子句，那么会汇总所有行，并产生一个结果。以下介绍最常用的七个分组函数：

l         MAX：该函数用于取得列或表达式的最大值，它适用于任何数据类型

l         MIN：该函数用于取得列或表达式的最小值，它适用于任何数据类型

l         AVG：该函数用于取得列或表达式的平均值，它只适用于数字类型

l         SUM：该函数用于取得列或表达式的总和，它只适用于数字类型

l         COUNT：该函数用于取得总计行数

l         VARIANCE：该函数用于取得列或表达式的方差，并且该函数只适用于数字类型。

l         STDDEV：该函数用于取得列或表达式的标准偏差，并且该函数只适用于数字类型。

当使用分组函数时，分组函数只能出现在选择列表、ORDER BY和HAVING子句中，而不能出现在WHERE和GROUP BY子句中。另外，使用分组函数还有以下一些注意事项：

l         当使用分组函数时，除了函数COUNT(*)之外，其他分组函数都会忽略NULL行

l         当执行SELECT语句时，如果选择列表同时包含列、表达式和分组函数，那么这些列和表达式必须出现在GROUP BY子句中

l         当使用分组函数时，在分组函数中可以指定ALL和DISTINCT选项。其中ALL是默认选项，该选项表示统计所有行数据(包括重复行)；如果指定DISTINCT，则只会统计不同行值

 

取得最大值和最小值

当执行SELECT语句时，分组函数MAX和MIN可以用于取得最大值和最小值。

示例：

SELECT MAX(sal),MIN(sal) FROM emp;

取得平均值和总和

当执行SELECT语句时，分组函数AVG和SUM可以用于取得平均值和总和。

示例：

SELECT AVG(sal),SUM(sal) FROM emp;

取得总计行数

当执行SELECT语句时，使用函数COUNT(*)可以取得总计行数。

示例：

SELECT COUNT(*) FROM emp;

在COUNT函数中还可以引用表达式。因为分组函数会忽略NULL行，所以使用COUNT(表达式)会显示NOT NULL的总计行数。

示例：

SELECT COUNT(comm) FROM emp;

取得方差和标准偏差

当执行SELECT语句时，函数VARIANCE和STDDEV可以用于取得方差和标准偏差。

示例：

SELECT VARIANCE(sal),STDDEV(sal) FROM emp;

取消重复值

当在SELECT语句中使用分组函数时，默认情况下会使用ALL选项显示所有数据的统计值(包括重复值)。为了在显示统计时取消重复值，需要使用DISTINCT选项。

示例：

SELECT COUNT(DISTINCT deptno) AS distinct_dept FROM emp;

4.2GROUP BY和HAVING

GROUP BY子句用于对查询结果进行分组统计，而HAVING子句则用于限制分组显示结果。注意，如果在选择列表中同时包含有列、表达式和分组函数，那么这些列和表达式必须出现在GROUP BY子句中。使用GROUP BY和HAVING子句的语法如下：

SELECT column,group_function FROM talbe
[WHERE condition] [GROUP BY group_by_expression]
[HAVING group_condtion];

l         column用于指定选择列表中的列或表达式

l         group_function用于指定分组函数

l         condition用于指定条件子句

l         group_by_expression用于指定分组表达式

l         group_condition用于指定排除分组结果的条件

 

使用GROUP BY进行单行分组

单行分组是指在GROUP BY子句中使用单个列生成分组统计结果。当进行单列分组时，会基于列的每个不同值生成一个数据统计结果。

示例：

SELECT deptno,AVG(sal),MAX(sal) FROM emp
GROUP BY deptno;

使用GROUP BY进行多列分组

多列分组是指在GROUP BY子句中使用两个或两个以上的列生成分组统计结果。当进行多列分组时，会基于多个列的不同值生成数据统计结果。

示例：

SELECT deptno,job,AVG(sal),MAX(sal) FROM emp
GROUP BY deptno,job;

使用HAVING子句限制分组显示结果

HAVING子句用于限制分组统计结果，并且HAVING子句必须跟在GROUP BY子句后面。

示例：

SELECT deptno,AVG(sal),MAX(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal) < 2500;

 

使用GROUP BY子句、WHERE子句和分组函数有以下一些注意事项：

l         分组函数只能出现在选择列表、HAVING子句和ORDER BY子句中

l         如果在SELECT语句中同时包含有GROUP BY，HAVING以及ORDER BY子句，则必须将ORDER BY子句放在最后。默认情况下，当使用GROUP BY子句统计数据时，会自动按照分组列的升序方式显示统计结果。通过使用ORDER BY子句，可以改变数据分组的排序方式

l         如果选择列表包含有列、表达式和分组函数，那么这些列和表达式必须出现在GROUP BY子句中，否则会显示错误信息

l         当限制分组显示结果时，必须要使用HAVING子句，而不能在WHERE子句中使用分组函数限制分组显示结果，否则会显示错误信息

 

4.3ROLLUP和CUBE

当直接使用GROUP BY执行数据统计时，只会生成列的相应数据统计。当使用ROLLUP操作符时，在生成原有统计结果的基础上，还会生成横向小计结果。当使用CUBE操作符时，在原有ROLLUP统计结果的基础上，还会生成纵向小计结果。

 

使用ROLLUP操作符

当直接使用GROUP BY子句进行多列分组时，只能生成简单的数据统计结果。为了生成数据统计以及横向小计统计，可以在GROUP BY子句中使用ROLLUP操作符。

示例：

SELECT deptno,job,avg(sal) FROM emp
GROUP BY ROLLUP(deptno,job);

使用CUBE操作符

当直接使用GROUP BY子句进行多行分组时，只能生成简单的数据统计结果。为了生成数据统计以及横向小计统计，可以在GROUP BY子句中使用ROLLUP操作符。为了生成数据统计、横向小计、纵向小计结果，可以使用CUBE操作符。

示例：

SELECT deptno,job,AVG(sal) FROM emp
GROUP BY CUBE(deptno,job);

 

使用GROUPING函数

GROUPING函数用于确定统计结果是否用到了特定列。如果函数返回0，则表示统计结果使用了该列；如果函数返回1，则表示统计结果未使用该列。

示例：

SELECT deptno,job,avg(sal),grouping(deptno),grouping(job)
FROM emp GROUP BY CUBE(deptno,job);

4.4GROUPING SETS

当使用GROUP BY子句执行数据分组统计时。默认情况下只会显示相应列的分组统计结果。可以使用GROUPING SETS操作符来合并多个分组的结果。

 

显示部分平均工资

示例：

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;

实现岗位平均工资

示例：

SELECT job,AVG(sal) FROM emp GROUP BY job;

显示部分平均工资和岗位平均工资

为了显示多个分组的统计结果，可以使用GROUPING SETS操作符合并统计结果。

示例：

SELECT deptno,job,AVG(sal) FROM emp
GROUP BY GROUPING SETS(deptno,job);

五． 连接查询

连接查询是指基于两个或两个以上表或视图的查询。

在使用连接查询时，应注意以下事项：

l         当使用连接查询时，必须在FROM子句后指定两个或两个以上的表

l         当使用连接查询时，应该在列名前加表名作为前缀。但是，如果不同表之间的列名不同，那么不需要在列名前加表名作为前缀；如果在不同表之间存在同名列，那么在列名之前要加表名作为前缀。否则会因为列的二义性而报错

l         当使用连接查询时，必须在WHERE子句中指定有效的连接条件(在不同表的列之间进行连接)。如果不指定连接条件，或者指定了无效的连接条件，那么会导致生成迪卡儿集(X*Y)。

当进行连接查询时，使用表别名可以简化连接查询语句。当指定表别名时，别名应该跟在表名后面。

 

5.1相等连接

相等连接是指使用相等比较符(=)指定连接条件的连接查询，该种连接查询主要用于检索主从表之间的相关数据。语法如下：

SELECT table1.column,table2.column FROM table1,table2 WHERE
table1.column1 = table2.column2;

当使用相等连接时，必须要使用等值比较符(=)指定连接条件。

 

使用相等连接执行主从查询

示例：

SELECT e.ename,e.sal,d.dname FROM emp e,dept d
WHERE e.deptno = d.deptno;

使用AND指定其他条件

当使用相等连接时，会显示满足连接条件的所有数据。为了在执行相等连接的同时指定其他连接条件，可以在WHERE子句中使用AND操作符。

示例：

SELECT d.dname,e.ename,e.sal FROM emp e,dept d
WHERE e.deptno = d.deptno AND d.deptno = 10;

5.2不等连接

不等连接是指在连接条件中使用除相等比较符外的其他比较操作符的连接查询，并且不等连接主要用于在不同表之间显示特定范围的信息。

示例：

SELECT a.ename,a.sal,b.grade FROM emp a,salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal;

5.3自连接

自连接是指在同一张表之间的连接查询，它主要用在子参照表上显示上下级关系或者层次关系。自参照表是指在不同列之间具有参照关系或主从关系的表。

因为子连接是在同一张表之间的连接，所以必须要定义表别名。

示例：

SELECT manager.ename FROM emp manager,emp worker
WHERE manager.empno = worker.mgr AND worker.ename = 'BLAKE';

5.4内连接和外连接

内连接用于返回满足连接条件的记录；而外连接则是内连接的扩展，它不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的记录。语法如下：

SELECT table1.column,table2.column
FROM talbe1[INNER|LEFT|RIGHT|FULL] JOIN table20N table1.column1 = table1.column2;

l         INNER JOIN表示内连接

l         LEFT JOIN表示左外连接

l         RIGHT JOIN表示右外连接

l         FULL JOIN表示完全外连接

l         ON子句用于指定连接条件。注意，如果使用FROM子句指定内、外连接，则必须要使用ON子句指定连接条件；如果使用(+)操作符指定外连接，则必须使用WHERE子句指定连接条件

 

(1)    内连接

内连接用于返回满足连接条件的所有记录。默认情况下，在执行连接查询时如果没有指定任何连接操作符，那么这些连接查询都属于内连接。

示例：

SELECT a.dname,b.ename FROM dept a,emp b
WHERE a.deptno = b.deptno AND a.deptno = 10;

另外，当执行连接查询时，通过在FROM子句中指定INNER JOIN选项，也可以指定内连接。

示例：

SELECT a.dname,b.ename FROM dept a INNER JOIN emp b 
ON a.deptno = b.deptno AND a.deptno = 10;

从Oracle9i开始，如果主表的主键列和从表的外部键列名称相同，那么还可以使用NATURAL JOIN关键字自动执行内连接操作。

示例：

SELECT dname,ename FROM dept NATURAL JOIN emp;

(2)    左外连接

左外连接是通过指定LEFT [OUTER] JOIN选项来实现的。当使用左外连接时，不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的连接操作符左别表的其他行。

示例：

SELECT a.dname,b.ename FROM dept a LEFT JOIN emp b
ON a.deptno = b.deptno AND a.deptno = 10;

(3)    右外连接

右外连接是通过指定RIGHT [OUTER] JOIN选项来实现的。当使用右外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的连接操作符右别表的其他行。

示例：

SELECT a.dname,b.ename FROM dept a RIGHT JOIN emp b
ON a.deptno = b.deptno AND a.deptno = 10;

(4)    完全外连接

完全外连接是通过指定FULL [OUTER] JOIN选项来实现的。当使用完全外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的所有其他行。

示例：

SELECT a.dname,b.ename FROM dept a FULL JOIN emp b
ON a.deptno = b.deptno AND a.deptno = 10;

(5)    使用(+)操作符

在Oracle9i之前，当执行外连接时，都是使用连接操作符(+)来完成的。尽管可以使用操作符(+)执行外连接操作，但Oracle9i开始Oracle建议使用OUTER JOIN执行外连接。使用(+)操作符执行外连接的语法如下：

SELECT table1.column,table2.column FROM table1,table2
WHERE table1.column1(+) = table2.column2;

当使用(+)操作符执行外连接时，应该将该操作符放在显示较少行(完全满足连接条件行)的一端。当使用(+)操作符时，必须要注意以下事项：

l         (+)操作符只能出向在WHERE子句中，并且不能与OUTER JOIN语句同时使用

l         当使用(+)操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含(+)操作符

l         (+)操作符只适用于列，而不能用在表达式上

l         (+)操作符不能与OR和IN操作符一起使用

l         (+)操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接

 

使用(+)操作符执行左外连接

当使用左外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的左别表的其他行。因为(+)操作符要放在行数较少的一端，所以在WHERE子句中应该将该操作符放在右别表的一端。

示例：

SELECT a.dname,b.ename FROM dept a,emp b
WHERE a.deptno = b.deptno(+) AND b.deptno(+) = 10;

 

使用(+)操作符执行右外连接

当使用右外连接时，不仅会返回满足连接条件的所有行，而且还会返回不满足连接条件的右别表的其他行。因为(+)操作符要放在行数较少的一端，所以在WHERE子句中应该将该操作符放在左别表的一端。

示例：

SELECT a.dname,b.ename FROM dept a,emp b
WHERE a.deptno(+) = b.deptno AND a.deptno(+) = 10
ORDER BY a.dname;

 

六． 子查询

子查询是指嵌入在其他SQL语句中的SELECT语句，也称为嵌套查询。注意，当在DDL语句中引用子查询时，可以带有ORDER BY子句；但是当在WHERE子句、SET子句中引用子查询时，不能带有ORDER BY子句。子查询具有以下一些作用：

l         通过在INSERT或CREATE TABLE语句中使用子查询，可以将源表数据插入到目标表中

l         通过在CREATE VIEW或CREATE MATERIALIZED VIEW中使用子查询，可以定义视图或实体化视图所对应的SELECT语句

l         通过在UPDATE语句中使用了子查询可以修改一列或多列数据

l         通过在WHERE，HAVING，START WITH子句中使用子查询，可以提供条件值

根据子查询返回结果的不同，子查询可被分为：

l         单行子查询

l         多行子查询

l         多列子查询

 

6.1单行子查询

单行子查询是指只返回一行数据的子查询语句。当在WHERE子句中引用单行子查询时，可以使用单行比较符(=，>，<，>=，<=，<>)。

示例：

SELECT ename,sal,deptno FROM emp WHERE deptno = 
(SELECT deptno FROM emp WHERE ename = 'SCOTT');

6.2多行子查询

多行子查询是指返回多行数据的子查询语句。当在WHERE子句中使用多行子查询时，必须要使用多行比较符(IN，ALL，ANY)，它们的作用如下：

运算符	含义
IN	匹配于子查询结果的任一个值即可
ALL	必须要符合子查询结果的所有值
ANY	只要符合子查询结果的任一个值即可

注意，ALL和ANY操作符不能单独使用，而只能与单行比较符(=，>，<，>=，<=，<>)结合使用。

 

(1)    在多行子查询中使用IN操作符

当在多行子查询中使用IN操作符时，会处理匹配于子查询任一个值的行。

示例：

SELECT ename,job,sal deptno FROM emp WHERE job IN
(SELECT DISTINCT job FROM emp WHERE deptno = 10);

(2)    在多行子查询中使用ALL操作符

ALL操作符必须与单行操作符结合使用，并且返回行必须要匹配于所有子查询结果。

示例：

SELECT ename,sal,deptno FROM emp WHERE sal > ALL
(SELECT sal FROM emp WHERE deptno = 30);

(3)    在多行子查询中使用ANY操作符

ANY操作符必须与单行操作符结合使用，并且返回行只需匹配于子查询的任一个结果即可。

示例：

SELECT ename,sal,deptno FROM emp WHERE sal > ANY
(SELECT sal FROM emp WHERE deptno = 30);

6.3多列子查询

单行子查询是指子查询只返回单列单行数据，多行子查询是指子查询返回单列多行数据，二者都是针对单列而言的。而多列子查询则是指返回多列数据的子查询语句。当多列子查询返回单行数据时，在WHERE子句中可以使用单行比较符；当多列子查询返回多行数据时，在WHERE子句中必须使用多行比较符(IN，ANY，ALL)。

示例：

SELECT ename,job,sal,deptno FROM emp WHERE (deptno,job) = 
(SELECT deptno,job FROM emp WHERE ename = 'SCOTT');

在使用子查询比较多个列的数据时，既可以使用成对比较，也可以使用非成对比较。其中，成对比较要求多个列的数据必须同时匹配，而非成对比较则不要求多个列的数据同时匹配。

 

(1)    成对比较示例

当执行成对比较时，因为要求多个列的数据必须同时匹配，所以必须要使用多列子查询实现。

示例：

SELECT ename,sal,comm,deptno FROM emp 
WHERE (sal,NVL(comm,-1)) IN 
(SELECT sal,NVL(comm,-1) FROM emp WHERE deptno = 30);

(2)    非成对比较示例

执行非成对比较时，应该要使用多个多行子查询来实现。

示例：

SELECT ename,sal,comm,deptno FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30)
AND NVL(comm,-1) IN
(SELECT NVL(comm,-1) FROM emp WHERE deptno = 30);

6.4其他子查询

在WHERE子句中除了可以使用单行子查询、多行子查询以及多列子查询外，还可以使用相关子查询。另外在FROM子句、DML语句、DLL语句中也可以使用子查询。

 

(1)    相关子查询

相关子查询是指需要引用主查询表列的子查询语句，相关子查询是通过EXISTS谓词来实现的。

示例：

SELECT ename,job,sal,deptno FROM emp WHERE EXISTS
(SELECT 1 FROM dept WHERE dept.deptno = emp.deptno
AND dept.loc = ' 
   
   
     NEW YORK 
    
  ');

 

当使用EXISTS谓词时，如果子查询存在返回结果，则条件为TRUE；如果子查询没有返回结果，则条件为FALSE。

 

(2)    在FROM子句中使用子查询

当在FROM子句中使用子查询时，该子查询会被作为视图对待，因此也被称为内嵌视图。注意，当在FROM子句中使用子查询时，必须要给子查询指定别名。

示例：

SELECT ename,job,sal FROM emp,
(SELECT deptno,AVG(sal) avgsal FROM emp
GROUP BY deptno) dept
WHERE emp.deptno = dept.deptno AND sal > dept.avgsal;

(3)    在DML语句中使用子查询

在INSERT语句中使用子查询

通过在INSERT语句中引用子查询，可以将一张表的数据装载到另一张表中。

示例：

INSERT INTO employee(id,name,title,salary)
SELECT empno,ename,job,sal FROM emp;

在UPDATE语句中使用子查询

当在UPDATE语句中使用子查询时，既可以在WHERE子句中引用子查询(返回未知条件值)，也可以在SET子句中使用子查询(修改列数据)。

示例：

UPDATE emp SET (sal,comm.) = 
(SELECT sal,comm. FROM emp WHERE ename = ‘SMITH’)
WHERE job = (SELECT job FROM emp WHERE ename = ‘SMITH’);

 

在DELETE语句中使用子查询

在DELETE语句中使用子查询时，可以在WHERE子句中引用子查询返回未知条件值。

示例：

DELETE FROM emp WHERE deptno = 
(SELECT deptno FROM dept WHERE dname = ‘SALES’);

(4)    在DDL语句中使用子查询

除了可以在SELECT，INSERT，UPDATE，DELETE语句中使用子查询外，也可以在DDL语句中使用子查询。注意，当在SELECT和DML语句中使用子查询时，WHERE子句和SET子句的子查询语句不能包括ORDER BY子句；但在DDL语句中使用子查询时，子查询可以包含ORDER BY子句。

 

在CREATE TABLE语句中使用子查询

通过在CREATE TABLE中使用子查询，可以在建立新表的同时复制表中的数据。

示例：

CREATE TABLE new_emp(id,name,sal,job,deptno) AS
SELECT empno,ename,sal,job,deptno FROM emp;

在CREATE VIEW语句中使用子查询

建立视图时，必须指定视图所对应的子查询语句。

示例：

CREATE OR REPLACE VIEW dept_10 AS
SELECT empno,ename,job,sal,deptno FROM emp
WHERE deptno = 10 ORDER BY ename;

在CREATE MATERIALIZED VIEW语句中使用子查询

建立实体化视图时，必须要指定实体化视图所对应的SQL语句，并且该SQL语句将来可以用于查询重写。

示例：

CREATE MATERIALIZED VIEW summary_emp AS
SELECT deptno,job,AVG(sal) avgsal,SUM(sal) sumsal 
FROM emp GROUP BY CUBE(deptno,job);

七．合并查询

为了合并多个SELECT语句的结果，可以使用集合操作符UNION，UNION ALL，INTERSECT和MINUS。语法如下：

SELECT 语句1 [UNION|UNION ALL|INTERSECT|MINUS] SELECT 语句2

…

这些集合操作符具有相同的优先级，当同时使用多个操作符时，会按照从左到右的方式引用这些集合操作符。当使用集合操作符时，必须确保不同查询的列个数和数据类型都要匹配。另外，使用集合操作符有以下一些限制：

l         对于LOB，VARRAY和嵌套表列来说，集合操作符是无效的

l         对于LONG列来说，UNION，INTERSECT，MINUS操作符是无效的

l         如果选择列表包含了表达式，则必须要为其指定列别名

 

(1)    UNION

UNION操作符用于获取两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行，并且会以第一列的结果进行排序。

示例：

SELECT ename,sal,job FROM emp WHERE sal > 2500
UNION
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';

(2)    UNION ALL

UNION ALL操作符用于获取两个结果集的并集。但与UNION操作符不同，该操作符不会取消重复值，而且也不会以任何列进行排序。

示例：

SELECT ename,sal,job FROM emp WHERE sal > 2500
UNION ALL
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';

(3)    INTERSECT

INTERSECT操作符用于获取两个结果集的交集。当使用该操作符时，只会显示同时存在于两个结果集中的数据，并且会以第一列进行排序。

示例：

SELECT ename,sal,job FROM emp WHERE sal > 2500
INTERSECT
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';

(4)    MINUS

MINUS操作符用于获取两个结果集的差集。当使用该操作符时，只会显示在第一个结果集中存在，在第二个结果集中不存在的数据，并且会以第一列进行排序。

示例：

SELECT ename,sal,job FROM emp WHERE sal > 2500
MINUS
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';

八．其他复杂查询

 

8.1层次查询

当表具有层次结构数据时，通过使用层次查询可以更直观的显示数据结果，并显示其数据之间的层次关系。

l         START WITH：用于指定层次查询的根行

l         CONNECT BY：用于指定父行和子行之间的关系。在condition表达式中，必须使用PRIOR引用父行。语法如下：

…PRIOR expr = expr 或 … expr = PRIOR expr

示例：

col ename format a15
col job format a15
SELECT LPAD(' ',3 * (LEVEL - 1)) || ename ename,
LPAD(' ',3 * (LEVEL - 1)) || job job FROM emp
WHERE job <> 'CLERK' START WITH mgr IS NULL
CONNECT BY mgr=PRIOR empno;

8.2使用CASE表达式

为了在SQL语句中使用IF…THEN…ELSE语法，可以使用CASE表达式。通过CASE表达式，可以避免调用过程来完成条件分支操作。

当使用CASE表达式时，使用WHEN子句可以指定条件语句。

SELECT ename,sal,CASE WHEN sal > 3000 THEN 3
WHEN sal > 200 THEN 2 ELSE 1 END grade
FROM emp WHERE deptno = 10;

8.3倒叙查询

默认情况下，当执行查询操作时，只能看到最近提交的数据。从Oracle9i开始，通过使用倒叙查询(Flashback Query)特征，可以查看到过去某时间点所提交的数据。注意，如果使用倒叙查询，那么要求数据库必须采用UNDO管理方式，并且初始化参数undo_retention限制了UNDO数据的保留时间。

(1)    查看当前数据

示例：

SELECT ename,sal FROM emp WHERE ename = ' 
  
    CLARK 
  ';

(2)    查看历史数据

执行倒叙查询时，通过在FROM子句后指定AS OF子句可以查看过去的历史数据。在AS OF子句中既可以指定时间，也可以指定SCN。注意，使用倒叙查询只能看到5分钟之前变化的数据，而不能查看到5分钟之内变化的数据。

示例：

SELECT ename,sal FROM emp AS OF TIMESTAMP to_timestamp(
'2003-05-18 19:59:00','YYYY-MM-DD HH24:MI:SS')
WHERE ename = ' 
  
    CLARK 
  ';

 

(3)    使用DBMS_FLASHBACK包获取特定SCN的数据

示例：

exec dbms_flashback.enable_at_system_change_number(717402);
SELECT sal FROM emp WHERE ename = ‘SCOTT’;
exec dbms_flashback.disable;
SELECT sal FROM emp WHERE ename = ‘SCOTT’;

 

8.4使用WITH子句重用子查询

对于多次使用相同子查询的复杂查询语句来说，用户可能会将查询语句分成两条语句执行。第一条语句将子查询结果存放到临时表，第二条查询语句使用临时表处理数据。从Oracle9i开始，通过WITH子句可以给子查询指定一个名称，并且使得在一条语句中可以完成所有任务，从而避免了使用临时表。

 

显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和(两次使用相同子查询)

示例：

SELECT dname,SUM(sal) AS dept_total FROM emp,dept
WHERE emp.deptno = dept.deptno GROUP BY dname
HAVING SUM(sal) >
(SELECT SUM(sal) * 1/3 FROM emp,dept
WHERE emp.deptno = dept.deptno);

显示部门工资总和高于雇员工资总和三分之一的部门名及工资总和(使用WITH子句重用子查询)

示例：

WITH summary AS(
SELECT dname,SUM(sal) AS dept_total FROM emp,dept
WHERE emp.deptno = dept.deptno GROUP BY dname
)
SELECT dname,dept_total FROM summary WHERE dept_total >
(SELECT SUM(dept_total) * 1/3 FROM summary);