第5章 SQL语言介绍
本章介绍SQL语言的基础知识。SQL是Structure Query Language（结构化查询语言）的简称，是用户与数据库交流所需要的标准语言。因此，要操作数据库一定要掌握好SQL。本章着重介绍SQL语言的语法规范，该规范是所有关系数据库的共同语言规范。
本章主要内容如下：
◎ SQL语言概要
◎ 如何使用SELECT检索数据
◎ 如何使用基本函数
◎ 如何使用复杂SELECT语句
◎ 如何使用DDL语句
◎ 如何使用DML语句
◎ 如何使用描述语句
5.1 SQL语言概述
SQL（结构化查询语言）是一种在关系数据库中定义和操纵数据的标准语言。其基本格式类似于英语语法，它最早是由Boyce和Chamberlin于1974年提出的（当时称做SEQUEL语言），后来在1976年，由IBM公司的San Jose研究所在研制关系数据库管理系统System R时修改为SEQUEL2，也就是目前的SQL。
1986年，美国国家标准化组织ANSI推出了第一个SQL语言标准SQL-86，是当时数据库系统的工业标准。随着数据库技术的发展，SQL标准也在不断地进行扩展和修正，数据库标准委员会先后又推出了SQL-89，SQL-92及SQL-99标准。1979年，Relation Software公司（Oracle前身）首先向市场推出了SQL执行工具，Oracle完全遵从了ANSI的SQL标准，并且将最新的SQL-99标准集成到了Oracle 9i以后的数据库版本中。
SQL是关系数据库的基本操作语言，是应用程序与数据库进行交互操作的接口。它将数据查询（DQL）、数据操纵（DML）、数据定义（DDL）和数据控制（DCL）功能集于一身，从而使得应用开发人员、数据库管理员和最终用户都可以通过SQL语句访问数据库，并执行相应的操作。
5.1.1 SQL语言特点
SQL采用集合操作方式，对数据的处理是成组进行的，而不是一条一条处理。通过使用集合操作方式，可以加快数据的处理速度。
执行SQL语句时，每次只能发送并处理一条语句。如果要减少语句发送和处理次数，可以使用PL/SQL。
执行SQL语句时，用户只需要知道其逻辑含义，而不需要知道SQL语句的具体执行步骤。例如，在使用WHERE子句检索数据时，用户可以取得所需要的记录，而这些记录如何存储、如何检索不需要用户进行干预。Oracle会自动对SQL语句进行优化处理，确定最佳执行路线，然后执行SQL，并且最终返回用户结果。
使用SQL语句时，既可以采用交互方式（例如SQL*Plus），也可以将SQL语句嵌入到高级语言中执行（例如C、C++、Java等）。
5.1.2 SQL语言分类
SQL语言主要有以下几类。
◎ 数据查询语言（SELECT）语句：用于检索数据库数据。在SQL所有语句中，SELECT语句的功能和语法最复杂，同时也最灵活。
◎ 数据操纵语言（Data Manipulation Language，DML）：用于改变数据库数据，包括INSERT,UPDATE和DELETE三条语句。其中，INSERT语句用于将数据插入到数据库中，UPDATE语句用于更新已经存在的数据库数据，而DELETE语句则用于删除已经存在的数据库数据。
◎ 事务控制语言（Transactional Control Language，TCL）：用于维护数据的一致性，包括COMMIT，ROLLBACK和SAVEPOINT三条语句。其中，COMMIT语句用于确认已经进行的数据库改变，ROLLBACK语句用于取消已经进行的数据库改变，而SAVEPOINT语句则用于设置保存点，以取消部分数据库改变。
◎ 数据定义语言（Data Definition Language，DDL）：用于建立、修改和删除数据库对象。例如，使用CREATE TABLE可以创建表，使用ALTER TABLE语句则可以对表结构进行修改，而如果想删除某个表，则可以使用DROP TABLE语句。这里需要提醒读者注意的是，DDL语句会自动提交事务。
◎ 数据控制语言（Data Control Language，DCL）：用于执行权限授予和权限收回操作，包括GRANT和REVOKE两条命令，其中，GRANT命令用于给用户或者角色授予权限，而REVOKE命令则用于收回用户或角色所具有的权限。需要注意的是，DCL语句也是自动对事务进行提交的。
5.1.3 SQL语言的编写规则
SQL关键字不区分大小写，既可以使用大写格式，也可以使用小写格式，或者大小写格式混用。例如：
SQL> SELECT ename ,sal ,job ,deptno FROM emp; SQL> select ename ,sal ,job ,deptno from emp;
以上两个SQL语句是没有区别的。
对象名和列名不区分大小写，它们既可以使用大写格式，也可以使用小写格式，或者大小写格式混用，例如：
SQL> SELECT ename ,sal ,job ,deptno FROM emp; SQL> select ENAME ,SAL ,job ,deptno from EMP;
以上两个SQL语句也没有区别。
字符值和日期值区分大小写。当在SQL语句中引用字符值和日期值时，必须要给出正确的大小写数据，否则，不能得到正确的查询结果，例如：
SQL> SELECT ename ,sal ,job ,deptno FROM emp where ename='SCOTT'; SQL> SELECT ename ,sal ,job ,deptno FROM emp where ename='scott';
以上两个SQL语句的执行结果是不同的，因为其where子句中的条件不一致。读者可以运行这两个语句以便进行验证。
在应用程序中编写SQL语句时，如果SQL语句的文本很短，可以将语句文本放在一行上；如果SQL语句的文本很长，可以将语句文本分步到多行上，并且可以通过使用跳格和缩进提高可读性。另外，在SQL*Plus中，SQL语句要以分号结束。
例如：
单行语句文本：
Select ename,sal FROM emp;
多行语句文本：
SELECT a.dname ,b.ename, b.sal ,b.comm ,b.job FROM dept a RIGHT JOIN emp b ON a.deptno=b.deptno AND a.deptno=10;
5.2 使用SELECT检索数据
数据库的用户查询是一个限制从数据库检索的信息到那些适合条件的行的选择过程。SELECT语句用于选择过程，此语句的不同部分用来定位在数据库表中的数据，其完整句法描述如下。
SELECT [ALL | DISTINCT TOP n [PERCENT] WITH TIES select_list
[INTO [new_table_name]]
[FROM {table_name | view_name}[(optimizer_hints)]
[[,{table_name2 | view_name2}[(optimizer_hints)]
[…, table_name16 | view_name16][(optimizer_hints)]]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]
一个SELECT语句就像叠加在数据库表上的过滤器，利用SQL关键字，数据库缩小到定位在一个查询中检索到的列和，如图5-1所示。过滤器最宽的部分为检索数据选定了数据库的全部行和列，过滤器最窄的部分表示检索数据的最小横截面，即可从表中检索仅有一列的单一行。
图5-1 选择查询用于定位数据库特定的列和行
大多数SQL查询检索的行和列都比整个表的范围窄，在图5-1中由过滤器的底部表示，比单个行和列更宽的范围则由与漏斗底部相对的漏斗顶部的点表示。用户将需要检索比单个行和列多，但又比数据库所有行和列少的数据。
这就是更加复杂的SELECT语句的由来。正如后面要讲到的，用户对数据库表进行操作的数量和种类“范围”可以从简单到复杂，能提供用户所需精确信息的服务器端的查询。
5.2.1 使用FROM子句指定表
SELECT语句的不同部分常用来指定要从数据库返回的数据。SELECT语句使用FROM子句指定查询中包含的行和列所在的表。以下是FROM子句的语法。
[FROM {table_name | view_name}[(optimizer_hints)]
[[,{table_name2 | view_name2}[(optimizer_hints)]
[…, table_name16 | view_name16][(optimizer_hints)]]]
与创建表一样，登录SQL*Plus用到一个用户名。在查询其他角色对应的方案中的表时，需要指定这个方案的名称。例如，查询方案SH的COUNTRIES表中的所有行数据的SQL语句如下所示（这个方案和表在安装Oracle时就自动创建了）。
SELECT * FROM SH.COUNTRIES
可以在FROM子句中指定多个表，表与表之间用逗号（,）隔开，如下所示。
SELECT * FROM SH.COUNTRIES, SH.CHANELS
5.2.2 使用SELECT（必要元素）指定列
用户可以指定查询的表中的某些列而不是全部。这些列名紧跟在SELECT关键词后，与FROM子句一样，列名与列名之间用逗号（,）隔开，如下所示。
SELECT column_name_1, ……, column_name_n FROM table_name_1, ……, table_name_n
利用SELECT指定列可以改变创建表时列的顺序来显示查询的结果，并且可以通过在多个地方指定同一个列来多次显示同一个列。例如，在方案SH中的表COUNTRIES的列顺序为：COUNTRY_ID,COUNTRY_ NAME,COUNTRY_SUBREGION和COUNTRY_REGION。通过SELECT指定列，查询显示结果如下。
SQL> SELECT COUNTRY_REGION,COUNTRY_NAME FROM SH.COUNTRIES; COUNTRY_REGION COUNTRY_NAME ---------------------------------------- ---------------------------------------- Americas United States of America Europe Germany Europe United Kingdom Europe The Netherlands Europe Ireland Europe Denmark Europe France Europe Spain Europe Turkey Europe Poland Americas Brazil Americas Argentina Asia Malaysia Asia Japan Asia India Oceania Australia Oceania New Zealand Africa South Africa Middle East Saudi Arabia 已选择19行。
用户可以在SELECT子句中使用通配符（*）来指定所有的列。值得注意的是，在实际应用中应尽可能少地使用通配符（*），因为这样将检索所有行，效率非常低。用户总是应该查询符合某种条件的数据，而不是全部。
5.2.3 使用WHERE子句指定行
在前面的介绍中，SELECT语句只是指定表中的部分列，而全部的行都被检索。在很多情况下，用户仅需要有目的地检索部分行，这就需要用到WHERE子句。
关键词WHERE常用来在SELECT语句中构成一个指定被检索表中的行的子句，WHERE子句使用如下语法。
SELECT column_name_1, ……, column_name_n FROM table_name_1, ……, table_name_n WHERE column_name comparison_operator value
WHERE子句构成一个行选择表达式，这样就可以尽可能小范围地指定检索的行。含有WHERE子句的SELECT语句可以返回一个单独的行，甚至没有相匹配的行。如果没有行返回，并不返回错误，而是显示“未选定行”。
下面的例子用于检索SH.COUNTRIES表中属于欧洲国家的数据：
SQL> SELECT COUNTRY_ID,COUNTRY_NAME FROM SH.COUNTRIES 2 WHERE COUNTRY_REGION='Europe'; CO COUNTRY_NAME ---- ---------------------------------------- DE Germany UK United Kingdom NL The Netherlands IE Ireland DK Denmark FR France ES Spain TR Turkey PL Poland 已选择9行。
1．在WHERE子句中使用比较运算符
WHERE子句允许在表列名称（column_name）和列值（value）之间使用比较运算符（comparison_ operator）。上面的例子只用到等号（=），表5-1列出了可以在WHERE子句中使用的比较运算符。
表5-1 比较运算符
注意
LIKE比较运算符是一个比较特殊的比较运算符，下面会作专门的介绍。
示例1：使用“不等于”比较运算符可以检索表SH.COUNTRIES中所有非欧洲区域的国家的数据。
SQL> SELECT COUNTRY_NAME FROM SH.COUNTRIES 2 WHERE COUNTRY_REGION<>'Europe'; COUNTRY_NAME ---------------------------------------- United States of America Brazil Argentina Malaysia Japan India Australia New Zealand South Africa Saudi Arabia 已选择10行。
注意
对于CHAR或VARCHAR等字符型的数据类型也可以进行比较。Oracle首先将字符转换成二进制数值，然后进行比较。例如，字母A在Oracle中被认为比字母B小。因此，对于字符串数据，大小写是有区别的，例如，A在比较运算中就不等于a。
2．使用比较运算符LIKE
LIKE比较运算符是关键词。LIKE关键词后面跟一个值的部分而不是一个完整的列值。通配符如百分号（%）可以用来匹配任何长度的字符。例如，检索地区中包含英文字母A的任何国家名称。
SQL> SELECT COUNTRY_NAME FROM SH.COUNTRIES 2 WHERE COUNTRY_REGION LIKE 'A%'; COUNTRY_NAME ---------------------------------------- United States of America Brazil Argentina Malaysia Japan India South Africa 已选择7行。
因为Asia、Africa和Americas作为地区名称都包含字母A。
下划线（_）是可以用来指定值的数据部分的另一个通配符，每个下划线匹配一个字母。例如，LIKE 'A---'用来匹配任何包含一个用A开头并在后面跟着3个字母的值。
SQL> SELECT COUNTRY_NAME FROM SH.COUNTRIES 2 WHERE COUNTRY_REGION LIKE 'A---'; COUNTRY_NAME ---------------------------------------- Malaysia Japan India
在这个例子中，就只返回地区名称为Asia的数据，而不会返回Africa和Americas的数据，因为这两个地区名称尽管是A开头的，但是后面分别跟着5个和7个字母，而不是3个，这与设置的WHERE子句条件不符。
3．在WHERE子句中使用布尔操作和其他关键词
可以使用布尔操作来设置WHERE子句中的多个条件。使用布尔运算符可以组成多个检索条件，更加精确地控制被检索的行。
使用布尔操作的语法如下。
SELECT column_name_1, ……, column_name_n FROM table_name_1, ……, table_name_n WHERE column_name comparison_operator value Boolean_operator column_name comparison_operator value
可以对布尔操作（Boolean_operator）使用多个操作符，下面将分别进行介绍。
4．使用OR操作符
OR操作符是“或”的意思，即由OR操作符连接的两个条件只要任意满足一个即可获得逻辑真值。例如，查询欧洲或者美洲的国家，如下所示。
SQL> SELECT COUNTRY_NAME FROM SH.COUNTRIES 2 WHERE COUNTRY_REGION='Europe' 3 OR COUNTRY_REGION='Americas'; COUNTRY_NAME ---------------------------------------- United States of America Germany United Kingdom The Netherlands Ireland Denmark France Spain Turkey Poland Brazil Argentina 已选择12行。
5．使用AND操作符
AND操作符是“且”的意思，即由AND操作符连接的两个条件必须同时满足才能获得逻辑真值。例如，查询SALES部门的MANAGER（销售部门经理）的姓名，如下所示。
SQL> SELECT ENAME FROM SCOTT.EMP,SCOTT.DEPT 2 WHERE DNAME='SALES' 3 AND JOB='MANAGER' 4 AND EMP.DEPTNO=DEPT.DEPTNO; ENAME ---------- BLAKE 已选择1行
6．使用NOT操作符
NOT操作符是“非”的意思，即NOT操作符后面的条件不满足时获得逻辑真值。例如，查询非欧美国家，如下所示。
SQL> SELECT COUNTRY_NAME FROM SH.COUNTRIES 2 WHERE NOT COUNTRY_REGION='Europe' 3 AND NOT COUNTRY_REGION='Americas'; COUNTRY_NAME ---------------------------------------- Malaysia Japan India Australia New Zealand South Africa Saudi Arabia 已选择7行。
注意
上面的例子NOT COUNTRY_REGION='Europe'，等价于使用了不等于比较运算符（<>）“OUNTRY_REGION<>'Europe'”，并且该实例还展示了多个布尔操作联合使用的情况。
7．使用BETWEEN选择值的范围
使用BETWEEN和AND关键词来指定要检索的值的范围。例如，检索SCOTT.EMP和DEPT表中工资在3001~5000之间的雇员的姓名、职位和所在的部门，如下所示。
SQL> SELECT ENAME,JOB,DNAME FROM SCOTT.EMP,SCOTT.DEPT 2 WHERE SAL BETWEEN 3000 AND 5000 3 AND EMP.DEPTNO=DEPT.DEPTNO; ENAME JOB DNAME -------------------- ------------------- ------------------------ SCOTT ANALYST RESEARCH KING PRESIDENT ACCOUNTING FORD ANALYST RESEARCH 已选择3行。
8．使用IN来指定一条列值
BETWEEN和AND关键词组合用来指定某个范围内的所有值，而IN关键词用来指定几个特定的值。
SELECT column_name_1, ……, column_name_n FROM table_name_1, ……, table_name_n WHERE column_name IN(value_1, …, value_n)
实际上，使用IN等同于下面的使用OR操作符的SQL语句。
SELECT column_name_1, ……, column_name_n FROM table_name_1, ……, table_name_n WHERE column_name=value_1 OR … OR column_name=value_n
示例2：查询SCOTT.EMP和DEPT表中SALES（销售部）和RESEARCH（研究部）的雇员姓名。
SQL> SELECT ENAME,DNAME FROM SCOTT.EMP,SCOTT.DEPT
2 WHERE DNAME IN('SALES','RESEARCH')
3 AND EMP.DEPTNO=DEPT.DEPTNO;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
SCOTT RESEARCH
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
已选择11行。
5.2.4 使用ORDER BY子句
数据在数据库中的存储是无序的，使用SELECT语句可以指定检索和显示行的顺序，通过ORDER BY子句对SELECT语句检索得到的数据进行排序。包含ORDER BY子句的SELECT语句的语法如下。
SELECT column_name_1, …, column_name_n FROM table_name_1, …, table_name_n ORDER BY column_name_1, …, column_name_n
使用该语句，首先根据column_name_1列进行排序，如果出现该列中有多个相同的数据，则根据column_name_2列进行排序。
下面的例子，首先根据国家所在的区域进行排序，处于同一个区域时根据分区排序，如果还一致，则根据国家的名称排序。
SQL> SELECT COUNTRY_REGION, COUNTRY_SUBREGION, COUNTRY_NAME 2 FROM SH.COUNTRIES 3 ORDER BY COUNTRY_REGION,COUNTRY_SUBREGION,COUNTRY_NAME; COUNTRY_REGION COUNTRY_SUBREGION COUNTRY_NAME -------------- -------------- -------------- Africa Africa South Africa Americas Northern America United States of America Americas Southern America Argentina Americas Southern America Brazil Asia Asia India Asia Asia Japan Asia Asia Malaysia Europe Eastern Europe Poland Europe Western Europe Denmark Europe Western Europe France …… 已选择19行。
在默认情况下，根据指定的列进行升序排序。例如，上面的例子就是按照升序排列的，对字符串进行大小比较时，系统按照字母顺序对字符串赋值进行比较。这就像是在字典中进行的排序一样，字母A总是在最前，而字母Z则几乎在最后。
如果需要按降序排列，则可以在列名后加上关键词DESC（descend，下降的简写）。实际上，升序也有一个关键词ASC（ascend，上升的简写），只不过升序是默认的排序法，一般不必加关键词。例如，将欧洲国家根据国家的名称降序排列。
SQL> SELECT COUNTRY_NAME FROM SH.COUNTRIES 2 WHERE COUNTRY_REGION='Europe' 3 ORDER BY COUNTRY_NAME DESC; COUNTRY_NAME ---------------------------------------- United Kingdom Turkey The Netherlands Spain Poland Ireland Germany France Denmark 已选择9行。
5.2.5 使用DISTINCT检索唯一的表列值
由于数据在表中的存储往往是无序的甚至是无规则的，因此，表中就可能存储了重复数据的行。当然，用户在设计数据库的表时，可以通过定义限制，使用唯一的关键字禁止向表中存储重复的行。然而，某一列中的数据总是可能重复的。例如，使用上面用到的示例表SH.COUNTRIES，许多国家在同一个地区内。也就是说，对应的COUNTRY_REGION列的值可能在多行中是重复的。而用户往往想知道在某一列中到底存储了哪些不同的数据，例如，要查询SH.COUNTRIES表中存储的各个国家所在的地区，就可以使用DISTINCT来检索COUNTRY_REGION中的唯一值。
SQL> SELECT DISTINCT COUNTRY_REGION FROM SH.COUNTRIES; COUNTRY_REGION -------------------- Africa Americas Asia Europe Middle East Oceania 已选择6行。
下面的例子是不使用DISTINCT的情况。
SQL> SELECT COUNT(*) FROM SH.COUNTRIES;
COUNT(*)
----------
19
SQL> SELECT COUNTRY_REGION FROM SH.COUNTRIES;
COUNTRY_REGION
--------------------
Americas
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Americas
Americas
Asia
Asia
Asia
Oceania
Oceania
Africa
Middle East
已选择19行。
上面的COUNT（*）函数用于返回表中的行数。由此可见，表中存储的每行数据都对应有一个COUNTRY_REGION列值，不使用DISTINCT检索该列，则每行该列的值都作为结果显示，而不管其是否重复。
5.2.6 使用算术运算符
在SQL语句中还可以使用算术运算符。与日常使用算术表达式的方式一样，SQL语句中带有运算符的表达式同样是从左至右按照乘除优先加减的顺序进行运算。例如，显示SCOTT.EMP表中所有职位是CLERK的员工的工资提升10%的结果，如下所示。
SQL> SELECT ENAME,SAL*1.1 FROM SCOTT.EMP 2 WHERE JOB='CLERK'; ENAME SAL*1.1 ----------------- ---------------- SMITH 880 ADAMS 1210 JAMES 1045 MILLER 1430 已选择4行。
在SQL语句中，可以使用的算术运算符及其运算的优先顺序如表5-2所示。
表5-2 算术运算符及其优先顺序
5.3 基本函数的使用
Oracle的函数很多，可以分为基本函数和分析函数两大类。在联机事务处理系统中，基本函数使用的很多；而在以数据仓库为基础的经营分析系统中，除了基本函数外，也要用到分析函数。在本节中，我们仅仅为大家介绍基本函数的使用。
5.3.1 字符函数
字符函数是专门用于字符处理的函数，处理对象可以是字符串常数，也可以是字符类型的列。常用的字符函数如下所示。
1．ASCII(c1)
其中，c1表示一个字符串，该函数返回c1第一个字母的ASCII码，其逆函数是CHR()。例如：
SELECT ASCII('A') BIG_A , ASCII('z') BIG_z FROM emp
返回结果：
BIG_A BIG_z 65 122
2．CHR(i)
其中，i表示一个数字，该函数返回十进制表示的字符。
例如：
select CHR(65), CHR(122), CHR(223) FROM emp
返回结果：
CHR65 CHR122 CHR223 A z B
3．CONCAT(c1,c2)
其中，c1,c2均为字符串，该函数将c2连接到c1的后面，如果c1为null，将返回c2；如果c2为null，则返回c1；如果c1,c2都为null，则返回null。与使用操作符||返回的结果相同。例如：
select concat('slobo ','Svoboda') username from dual
返回结果：
Username slobo Syoboda
4．INITCAP(c1)
其中，c1为字符串。该函数将每个单词的第一个字母大写，其他字母小写返回。单词由空格、控制字符、标点符号限制。例如：
select INITCAP('veni,vedi,vici') Ceasar from dual
返回结果：
Ceasar Veni,Vedi,Vici
5．INSTR(c1,c2,[,i[,j]])
其中，c1,c2均为字符串，i,j为整数。该函数返回c2在c1中第j次出现的位置，搜索从c1的第i个字符开始。当没有发现所需要的字符时，则返回0；如果i为负数，那么搜索将从右到左进行，但是位置的计算还是从左到右，i和j的默认值为1。例如：
select INSTR('Mississippi','i',3,3) from dual
返回结果：
INSTR('MISSISSIPPI','I',3,3)
11
再例如：
select INSTR('Mississippi','i',-2,3) from dual
返回结果：
INSTR('MISSISSIPPI','I',3,3)
2
6．INSTRB(c1,c2,[,i[,j]])
与INSTR()函数一样，只是这里返回的是字节，对于单字节，INSTRB()的效果等于INSTR()。
7．LENGTH(c1)
如果c1为字符串，则返回c1的长度；如果c1为null，那么将返回null值。
例如：
select LENGTH('Ipso Facto') ergo from dual
返回结果：
Ergo 10
8．LENGTHB()
与LENGTH()一样，返回字节。
9．LOWER(c)
返回c的小写字符，经常出现在WHERE子串中。
例如：
select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE '%white%'
返回结果：
COLORNAME Winterwhite
10．LPAD(c1,i,c2)
其中，c1,c2均为字符串，i为整数。在c1的左侧用c2字符串补足长度i，可多次重复，如果i小于c1的长度，那么只返回长度为i的c1字符，其他的将被截去。c2的默认值为单空格，参见RPAD。
例如：
select LPAD(answer,7,'') padded,answer unpadded from question;
返回结果：
PADDED UNPADDED Yes YesNO NOMaybe maybe
11．LTRIM(c1,c2)
将c1中最左边的字符去掉，使其第一个字符不在c2中，如果没有c2，则c1就不会改变。
例如：
select LTRIM('Mississippi','Mis') from dual
返回结果：
LTR ppi
12．RPAD(c1,i ,c2)
在c1的右侧用c2字符串补足长度i，可多次重复，如果i小于c1的长度，那么只返回长度为i的c1字符，其他的将被截去。c2的默认值为单空格，其他的与LPAD相似。
13．RTRIM(c1[,c2])
将c1中最右边的字符去掉，使其最后一个字符不在c2中，如果没有c2，则c1就不会改变。
14．REPLACE(c1,[c2,c3])
c1,c2,c3都是字符串，此函数用c3代替出现在c1中的c2后返回。
select REPLACE('uptown','up','down') from dualREPLACEdowntown
15．STBSTR(c1,i[,j])
c1为一个字符串，i,j为整数，从c1的第i位开始返回长度为j的子字符串，如果j为空，则直到串的尾部。
select SUBSTR('Message',1,4) from dualSUBSMess
16．SUBSTRB(c1,i[,j])
与SUBSTR大致相同，只是i,j是以字节计算的。
17．SOUNDEX(c1)
返回与c1发音相似的词。
select SOUNDEX('dawes') Dawes SOUNDEX('daws') Daws, SOUNDEX('dawson') from dualDawes Daws DawsonD200 D200 D250
18．TRANSLATE(c1,c2,c3)
将c1中与c2相同的字符以c3代替。
select TRANSLATE('fumble','uf','ar') test from dualTEXTramble
19．TRIM(c1 from c2)
该函数用于从字符串的头部、尾部或两端截断特定字符，参数c1为要截去的字符，c2是源字符串。
select TRIM('A' from 'ABCDEF') from dual;
20．UPPER(c1)
返回c1的大写，常出现在WHERE子串中。
select name from dual where UPPER(name) LIKE 'KI%'NAMEKING
5.3.2 数字函数
数字函数操作数字数据，执行数学和算术运算。所有函数都有数字参数并返回数字值。所有三角函数的操作数和值都是弧度而不是角度，Oracle没有提供内建的弧度和角度的转换函数。
1．ABS(n)
返回n的绝对值。
2．ACOS(n)
反余弦函数，返回-1到1之间的数，n表示弧度。
select ACOS(-1) pi,ACOS(1) ZERO FROM dualPI ZERO3.14159265 0
3．ASIN(n)
反正弦函数，返回-1到1之间的数，n表示弧度。
4．ATAN(n)
反正切函数，返回n的反正切值，n表示弧度。
5．CEIL(n)
返回大于或等于n的最小整数。
6．COS(n)
返回n的余弦值，n为弧度。
7．COSH(n)
返回n的双曲余弦值，n为数字。
select COSH(<1.4>) FROM dualCOSH(1.4)2.15089847
8．EXP(n)
返回e的n次幂，e=2.71828183。
9．FLOOR(n)
返回小于等于n的最大整数。
10．LN(n)
返回n的自然对数，n必须大于0。
11．LOG(n1, n2)
返回以n1为底n2的对数。
12．MOD(n1, n2)
返回n1除以n2的余数。
13．POWER(n1, n2)
返回n1的n2次方。
14．ROUND(n1, n2)
返回舍入小数点右边n2位的n1的值，n2的默认值为0，返回与小数点最接近的整数，如果n2为负数就舍入到小数点左边相应的位上，n2必须是整数。
select ROUND(12345,-2),ROUND(12345.54321,2) FROM dualROUND(12345,-2) ROUND(12345.54321,2)12300 12345.54
15．SIGN(n)
如果n为负数，则返回-1；如果n为正数，则返回1；如果n =0，则返回0。
16．SIN(n)
返回n的正弦值，n表示弧度。
17．SINH(n)
返回n的双曲正弦值，n表示弧度。
18．SQRT(n)
返回n的平方根，n表示弧度。
19．TAN(n)
返回n的正切值，n表示弧度。
20．TANH(n)
返回n的双曲正切值，n表示弧度。
21．TRUNC(n1, n2)
返回截尾到n2位小数的n1的值，n2默认设置为0，当n2为默认设置时，会将n1截尾为整数；如果n2为负值，就截尾在小数点左边相应的位上。
5.3.3 日期函数
日期函数操作DATE数据类型，大多数都有DATE数据类型的参数，且大多数返回的也是DATE数据类型的值。
1．ADD_MONTHS(d,i)
返回日期d加上i个月后的结果，i可以是任意整数。如果i是一个小数，那么数据库将隐式地将其转换成整数，截去小数点后面的部分。
2．LAST_DAY(d)
此函数返回包含日期d月份的最后一天。
3．MONTHS_BETWEEN(d1,d2)
返回d1和d2之间月的数目，如果d1和d2的日期相同，或者都是该月的最后一天，那么将返回一个整数；否则，返回的结果将包含一个分数。
4．NEW_TIME(d1,tz1,tz2)
d1是日期数据类型，当时区tz1中的日期和时间是d时，返回时区tz2中的日期和时间。tz1和tz2是字符串。
5．NEXT_DAY(d,char)
该函数用于返回指定日期后的第一个工作日（由char指定）所对应的日期。
6．SYADATE
此函数没有参数，返回当前日期和时间。
7．TRUNC(d,[fmt])
返回由fmt指定的单位的日期d。
5.3.4 转换函数
转换函数用于操作多数据类型，在数据类型之间进行转换。
1．CHARTORWID(c)
c为一个字符串，函数将c转换为RWID数据类型。
SELECT test_id
from test_case
where rowid=CHARTORWID('AAAA0SAACAAAALiAAA')
2．CONVERT(c,dset,sset)
c为一个字符串，dset、sset是两个字符集，函数将字符串c由sset字符集转换为dset字符集，sset默认设置为数据库的字符集。
3．HEXTORAW(x)
x为十六进制的字符串，函数将十六进制的x转换为RAW数据类型。
4．RAWTOHEX(x)
x是RAW数据类型字符串，函数将RAW数据类型转换为十六进制的数据类型。
5．ROWIDTOCHAR(rowid)
函数将ROWID数据类型转换为CHAR数据类型。
6．TO_CHAR(x[,fmt[,nlsparam]])
x是DATE或NUMBER数据类型，函数将x转换成fmt指定格式的CHAR数据类型。如果x为日期，则nlsparam=NLS_DATE_LANGUAGE控制返回的月和日所使用的语言。如果x为数字，则nlsparam=NLS_NUMERIC_CHARACTERS用来指定小数位和千分位的分隔符以及货币符号。
NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"
7．TO_DATE([c,[fmt,nlsparam]])
c表示字符串，fmt表示一个特殊格式的字符串。返回按照fmt格式显示的c，nlsparam表示使用的语言。函数将字符串c转换成DATE数据类型。
8．TO_MULTI_BYTE(c)
c表示一个字符串，函数将c的单字节字符转换成多字节字符。
9．TO_NUMBER([c,[fmt[,nlsparam]])
c表示一个字符串，fmt表示一个特殊格式的字符串，函数返回值按照fmt指定的格式显示。nlsparam表示语言，函数将返回c代表的数字。
10．TO_SINGLE_BYTE(c)
将字符串c中的多字节字符转换成等价的单字节字符。该函数仅当数据库字符集同时包含单字节和多字节字符时才使用。
5.3.5 组函数
组函数也叫集合函数，返回基于多个行的单一结果，行的准确数量无法确定，除非查询被执行并且所有的结果都被包含在内。与单行函数不同的是，在解析时所有的行都是已知的。由于这种差别，使组函数与单行函数在要求和行为上有微小的差异。
Oracle提供了丰富的基于组的、多行的函数，这些函数可以在select或select的having子句中使用，当用于select子串时，常常都和GROUP BY一起使用。
1．AVG([{DISYINCT|ALL}])
返回数值的平均值，默认设置为ALL。
SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal)
FROM scott.empAVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)1877.94118 1877.94118 1916.071413
COUNT({*|DISTINCT|ALL})
返回查询中行的数目，默认设置是ALL，*表示返回所有的行。
2．MAX([{DISTINCT|ALL}])
返回选择列表项目的最大值，如果x是字符串数据类型，则返回一个VARCHAR2数据类型；如果x是一个DATE数据类型，则返回一个日期；如果x是NUMBER数据类型，则返回一个数字。注意：DISTINCT和ALL不起作用，因为最大值与这两种设置是相同的。
3．MIN([{DISTINCT|ALL}])
返回选择列表项目的最小值。
4．STDDEV([{DISTINCT|ALL}])
返回选择列表项目的标准差，所谓标准差是方差的平方根。
5．SUM([{DISTINCT|ALL}])
返回选择列表项目的数值的总和。
6．VARIANCE([{DISTINCT|ALL}])
返回选择列表项目的统计方差。
5.3.6 使用GROUP BY进行数据分组
组函数用于对数据进行分组或分类，当在SELECT语句的SELECT子句中使用组函数时，必须把分组或非常数列放置在GROUP BY子句中，如果没有使用GROUP BY进行专门的处理，那么默认的分类是将整个结果设为一类。
select stat,counter(*) zip_count from zip_codes GROUP BY state;ST ZIP_COUNT -- --------- AK 360AL 1212AR 1309AZ 768CA 3982
本例中，用state字段分类，如果我们要将结果按照zip_codes排序，可以使用ORDER BY子句，ORDER BY子句可以使用列或组函数。
select stat,counter(*) zip_count from zip_codes GROUP BY state ORDER BY COUNT(*) DESC;ST COUNT(*) -- -------- NY 4312PA 4297TX 4123CA 3982
GROUP BY子句将表按行分组，每组中的行对指定的列具有相同的值，每个不同值的重复值被放在相同的组中，分组使用户可以对行的组执行同样的函数。
在一条语句中可以用任意数量的列进行分组，在选择清单中的列必须是在GROUP BY子句中或有一个函数使用它。包含GROUP BY子句的SELECT语句的语法如下：
SELECT column_1, … , column n FROM tablename GROUP BY columnname 1, … , columnname n
GROUP BY在按列值升序排序后（默认），仅定位唯一的列值。GROUP BY不像ORDER BY子句，尽管也按升序对列值排序，但它不清除重复的列值。下面的例子根据COUNTRY_REGION进行分组，同一地区的国家被放在一个组里，函数COUNT(*)用来统计该组中（即同一地区中）国家的数量。
SQL> SELECT COUNTRY_REGION,COUNT(*) 2 FROM SH.COUNTRIES 3 GROUP BY COUNTRY_REGION; COUNTRY_REGION COUNT(*) ------------------------------ ------------------ Africa 1 Americas 3 Asia 3 Europe 9 Middle East 1 Oceania 2 已选择6行。
还可以使用HAVING子句选择特殊的组，HAVING子句将组的一些属性与常数值比较，如果满足HAVING子句中的逻辑表达式，那么，它就被包括在查询结果中，使用HAVING子句的SELECT语句的语法如下。
SELECT column_1, … , column n FROM tablename GROUP BY columnname HAVING expression
例如，查询包含三个国家的地区名称，如下所示。
SQL> SELECT COUNTRY_REGION FROM SH.COUNTRIES 2 GROUP BY COUNTRY_REGION 3 HAVING COUNT(*)=3; COUNTRY_REGION ---------------------------------- Americas Asia 已选择2行。
5.3.7 使用HAVING子句限制分组数据
现在已经知道了在查询的SELECT语句和ORDER BY子句中使用组函数，组函数只能用于两个子串中，组函数不能用于WHERE子串中。例如，下面的查询是错误的。
SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept='OUTSIDE' AND SUM(sale_amount)>10000 GROUP BY sales_clerk
该语句中数据库不知道SUM()是什么，当我们需要指示数据库进行分组，然后限制分组后行的输出时，正确的方法是使用HAVING语句。
SELECT sales_clerk,SUN(sale_amount) FROM gross_sales WHERE sales_dept='OUTSIDE' GROUP BY sales_clerkHAVING SUM(sale_amount)>10000;
5.3.8 Oracle递归函数的使用
在日常应用中，经常需要查询指定记录的所有上层或下层数据树形结构，不必发愁，Oracle已经为我们提供了专门的函数来实现这个需求。下面通过例子介绍其使用方法。
基本语法：
SELECT col2,col2,... FROM tablename
START WITH 条件1
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}
WHERE 条件3,条件4,…;
此时需要对应的表结构中至少存在能够上下递归的2个列，即需要存在有上下层级关系的2个列。下面通过例子进行进一步的说明。
SELECT
col1, //需要进行查询的任意列
col2, //需要进行查询的任意列
LEVEL //Oracle的特殊字段，查询出来的值默认从1开始递增
……
From TableName
// PRIOR运算符在一侧表示父结点，在另一侧表示子结点
CONNECT BY PRIOR col1 ID= col2 ID //col1和col2是存在父子层级关系的字段
//更换这2个字段的左右位置可实现向上或向下递归查询
START WITH col2 ID=指定值 //指定值决定递归的起点，可省略，此时默认从根结点开始
//可同时指定多个结点
WHERE 条件3,条件4,…;
5.4 复杂SELECT语句的使用
本节将介绍SELECT语句的一些高级用法，包括集合操作、子查询、表的连接、CASE语句及PL/SQL中功能强大的DECODE函数。
5.4.1 集合操作
集合操作主要包括并、交、差三种，为了合并多个SELECT语句的结果，可以使用集合操作符UNION，UNION ALL，INTERSECT和MINUS。其语法如下。
SELECT语句1 [UNION |UNION ALL |INTERSECT |MINUS ] SELECT语句2 …
1．UNION和UNION ALL操作
UNION操作符用于获取两个或多个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行。
UNION ALL操作符返回查询所检索出的所有行，包括重复的行。
UNION（ALL）子句的语法如下所示。
SELECT column_name_1, ……, column_name_n FROM table_name_1, ……, table_name_n WHERE column_name comparison_operator value [GROUP BY … ] [HAVING … ] UNION [ALL] SELECT column_name_1, ……, column_name_n FROM table_name_1, ……, table_name_n WHERE column_name comparison_operator value [GROUP BY … ] [HAVING … ] [ORDER BY … ]
需要注意的是，如果使用ORDER BY子句进行排序，该子句只出现在最后一个查询的后面。
下面的例子中，查询销售部门（SALES）所有雇员的姓名（ENAME）和职位（JOB），以及职位是经理（MANAGER）的所有雇员的姓名和所在的部门（DNAME）。由于涉及两个不同的查询，可以使用UNION语句。
SQL> SELECT ENAME,DNAME,JOB 2 FROM SCOTT.EMP E,SCOTT.DEPT D 3 WHERE E.DEPTNO=D.DEPTNO 4 AND DNAME='SALES' 5 UNION 6 SELECT ENAME,DNAME,JOB 7 FROM SCOTT.EMP E,SCOTT.DEPT D 8 WHERE E.DEPTNO=D.DEPTNO 9 AND JOB='MANAGER' 10 ORDER BY DNAME; ENAME DNAME JOB --------------- ---------------- -------------- CLARK ACCOUNTING MANAGER JONES RESEARCH MANAGER ALLEN SALES SALESMAN BLAKE SALES MANAGER JAMES SALES CLERK MARTIN SALES SALESMAN TURNER SALES SALESMAN WARD SALES SALESMAN 已选择8行。
在上面的例子中，名为“BLAKE”的雇员既是SALES部门的员工，又是部门的MANAGER，他符合两个查询的条件，既作为前一个查询的结果返回又作为后一个查询的结果返回。然而，由于UNION语句不显示两个查询结果中重复的行，因此，关于“BLAKE”的数据只显示一行。
如果使用UNION ALL语句，则会保留查询中的重复行，如下所示。
SQL> SELECT ENAME,DNAME,JOB 2 FROM SCOTT.EMP E,SCOTT.DEPT D 3 WHERE E.DEPTNO=D.DEPTNO 4 AND DNAME='SALES' 5 UNION ALL 6 SELECT ENAME,DNAME,JOB 7 FROM SCOTT.EMP E,SCOTT.DEPT D 8 WHERE E.DEPTNO=D.DEPTNO 9 AND JOB='MANAGER' 10 ORDER BY DNAME; ENAME DNAME JOB --------------- ------------------- -------------- CLARK ACCOUNTING MANAGER JONES RESEARCH MANAGER ALLEN SALES SALESMAN WARD SALES SALESMAN MARTIN SALES SALESMAN BLAKE SALES MANAGER BLAKE SALES MANAGER TURNER SALES SALESMAN JAMES SALES CLERK 已选择9行。
上面的例子，由于使用了UNION ALL语句保留重复的行，因此，“BLAKE”雇员的数据行出现了两次。请参照上述的两个例子，体会UNION语句和UNION ALL语句的区别。
可以利用UNION语句结合SELECT语句将查询得到的数据用INSERT INTO语句插入到一张新表中。
首先创建一张新的雇员表SCOTT.EMP_NEW，然后存储一些新雇员的信息。接着，使用UNION语句将查询到的原SCOTT.EMP表中SALES部门的雇员信息和新EMP_NEW表中的SALES部门的雇员信息插入到新建的另一个表SALES_EMP中。本例将综合用到上面介绍的各种SQL语句。
首先，创建新表EMP_NEW，存储新雇员信息。
SQL> CREATE TABLE SCOTT.EMP_NEW 2 (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), SAL NUMBER(7,2), DEPTNO NUMBER(2), 4 CONSTRAINT FK_DEPTNO2 FOREIGN KEY(DEPTNO) 5 REFERENCES SCOTT.DEPT(DEPTNO), 6 CONSTRAINT PK_EMP2 PRIMARY KEY(EMPNO) 7 USING INDEX 8 TABLESPACE SYSTEM); 表已创建。
然后，将新雇员的信息插入到表EMP_NEW中。
SQL> INSERT INTO SCOTT.EMP_NEW 2 VALUES (8001, 'FUGERSON', 'MANAGER', 3000, 30); 已创建 1 行。 SQL> INSERT INTO SCOTT.EMP_NEW 2 VALUES (8002, 'KEMP', 'SALESMAN', 4500, 30); 已创建 1 行。 SQL> INSERT INTO SCOTT.EMP_NEW 2 VALUES (8003, 'BECK', 'SALESMAN', 4750, 30); 已创建 1 行。 SQL> INSERT INTO SCOTT.EMP_NEW 2 VALUES (8004, 'DODGE', 'SALESMAN', 4330, 30); 已创建 1 行。 SQL> INSERT INTO SCOTT.EMP_NEW 2 VALUES (8005, 'GIGGS', 'CLERK', 2050, 30); 已创建 1 行。
该表中存储的信息如下所示。
SQL> select * from scott.emp_new;
EMPNO ENAME JOB SAL DEPTNO
--------------- ----------------- -------------- ------------- ----------------
8001 FUGERSON MANAGER 3000 30
8002 KEMP SALESMAN 4500 30
8003 BECK SALESMAN 4750 30
8004 DODGE SALESMAN 4330 30
8005 GIGGS CLERK 2050 30
接下来，创建另外一个新表SALES_EMP，用于存储属于SALES部门的所有新旧雇员的信息。
SQL> CREATE TABLE SCOTT.SALES_EMP 2 (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), SAL NUMBER(7,2), DEPTNO NUMBER(2), 4 CONSTRAINT FK_DEPTNO3 FOREIGN KEY(DEPTNO) 5 REFERENCES SCOTT.DEPT(DEPTNO), 6 CONSTRAINT PK_EMP3 PRIMARY KEY(EMPNO) 7 USING INDEX 8 TABLESPACE SYSTEM); 表已创建。
然后，将表SCOTT.EMP中的SALES部门中的雇员和表SCOTT.EMP_NEW中的雇员信息存储到表SCOTT.SALES_EMP中。
SQL> INSERT INTO SCOTT.SALES_EMP 2 SELECT EMPNO,ENAME,JOB,SAL,DEPTNO 3 FROM SCOTT.EMP 4 WHERE DEPTNO=30 5 UNION 6 SELECT * FROM SCOTT.EMP_NEW; 已创建11行。
最后，得到的表SALES_EMP如下所示（由于与表DEPT建立了关系，我们将部门的名称显示出来）。
SQL> SELECT EMPNO,ENAME,JOB,SAL,DNAME
2 FROM SCOTT.SALES_EMP S,SCOTT.DEPT D
3 WHERE S.DEPTNO=D.DEPTNO
4 ORDER BY SAL DESC;
EMPNO ENAME JOB SAL DNAME
-------------- --------------- ----------------- ------------ ------------
8003 BECK SALESMAN 4750 SALES
8002 KEMP SALESMAN 4500 SALES
8004 DODGE SALESMAN 4330 SALES
8001 FUGERSON MANAGER 3000 SALES
7698 BLAKE MANAGER 2850 SALES
8005 GIGGS CLERK 2050 SALES
7499 ALLEN SALESMAN 1600 SALES
7844 TURNER SALESMAN 1500 SALES
7521 WARD SALESMAN 1250 SALES
7654 MARTIN SALESMAN 1250 SALES
7900 JAMES CLERK 950 SALES
已选择11行。
2．交（INTERSECT）操作
INTERSET操作符用于获取两个结果集的交集。当使用该操作符时，只会显示同时存在于两个结果集中的数据。下面以显示工资高于2500并且岗位为“MANAGER”的雇员为例，说明使用该操作符的方法，举例如下。
SQL>SELECT ENAME,SAL,JOB FROM EMP WHERE SAL>2500 2 INTERSECT 3 SELECT ENAME,SAL,JOB FROM EMP WHERE JOB=’MANAGER’; ENAME SAL JOB ----------- -------- ---------- BLAKE 2850 MANAGER JONES 2975 MANAGER 已选择2行。
3．差（MINUS）操作
MINUS操作符用于获取两个结果集的差集。当使用该操作符时，只会显示第一个结果集中存在，第二个结果集中不存在的数据。下面以显示工资高于2500但岗位不是“MANAGER”的雇员为例，说明使用该操作符的方法，示例如下。
SQL>SELECT ENAME,SAL,JOB FROM EMP WHERE SAL>2500 2 MINUS 3 SELECT ENAME,SAL,JOB FROM EMP WHERE JOB=’MANAGER’; ENAME SAL JOB ----------- -------- ---------- FORD 3000 ANALYST KING 5000 PRESIDENT …
5.4.2 子查询
可以在一个SELECT语句中嵌入另一个完整的SELECT语句，则嵌入到SELECT语句中的SELECT语句称为子查询。子查询应用括号进行区分。例如，查询与Germany处于同一地区的国家的名称，如下所示。
SQL> SELECT COUNTRY_NAME FROM SH.COUNTRIES 2 WHERE COUNTRY_REGION= 3 ( 4 SELECT COUNTRY_REGION FROM SH.COUNTRIES 5 WHERE COUNTRY_NAME='Germany' 6 ) 7 ; COUNTRY_NAME ---------------------------------------- Germany United Kingdom The Netherlands Ireland Denmark France Spain Turkey Poland 已选择9行。
5.4.3 表连接
在前面SELECT语句的介绍中讲到，可以使用FROM指定多个表进行查询。一般地，指定的多个表一定会有某种关系，用户需要综合这些表来查询特定的信息。例如，在5.2.3节中，由于表在设计时，在雇员信息表EMP中仅仅存储了某个雇员所在部门的编号DEPTNO，而没有存储部门的名称，相应的，在部门信息表DEPT中则存储了部门编号对应的部门名称DNAME、部门所在地LOC等信息。为了查询雇员在哪个部门，就必须将这两张表连接起来进行查询。
在设计表格时，就要考虑查询的问题。因此，在表中相应创建了主关键字约束和外部关键字约束来使这两张表联系在一起。查询时，使用WHERE子句设置用一个表中的主关键字等于另一个表中的外部关键字的方法来实施一种关系连接，进行查询。这种关系连接称为等值连接或自然连接。
用户可能会想，为什么不能通过在WHERE子句中设置雇员信息表中的雇员姓名来查询部门信息表中的部门名称来进行查询呢？反映为SQL语句如下所示。
SQL> SELECT DNAME FROM SCOTT.EMP,SCOTT.DEPT 2 WHERE EMP.ENAME='SMITH';
执行该语句的结果如下所示。
DNAME -------------- ACCOUNTING RESEARCH SALES OPERATIONS
为什么会出现一个人同时在4个部门呢？这就需要理解实施了关系连接的两种表是如何连接的。
在FROM子句中指定两张表，则这两张表就会合并在一起进行查询。两张表的合并算法叫做笛卡儿乘积（类似于两个矩阵相乘）。笛卡儿乘积的算法是，将表A的第一行与表B的所有行分别合并，得到与表B的行数相等的一系列新行；然后将表A的第二行与表B的所有行分别合并，得到与表B的行数相等的一系列新行……依此类推，直至将表A的最后一行与表B的所有行分别合并，得到与表B的行数相等的一系列新行。
由此可知，在FROM子句中指定了两表EMP和DEPT后合并得到的结果是，存在一张逻辑上的大表（两个表的笛卡儿乘积的结果，但是物理上并不存在），其列数等于两张表中的各个列数相加。
SQL> SELECT * FROM SCOTT.EMP,SCOTT.DEPT 2 WHERE EMP.ENAME='SMITH'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- 7369 SMITH CLERK 7902 17-12月-80 800 20 10 ACCOUNTING NEW YORK 7369 SMITH CLERK 7902 17-12月-80 800 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 17-12月-80 800 20 30 SALES CHICAGO EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- 7369 SMITH CLERK 7902 17-12月-80 800 20 40 OPERATIONS BOSTON
其行数等于两张表的行数相乘（下面的COUNT(*)函数用于返回符合条件的所有行数）。
SQL> SELECT COUNT(*) FROM SCOTT.EMP;
COUNT(*)
----------------
14
SQL> SELECT COUNT(*) FROM SCOTT.DEPT;
COUNT(*)
----------------
4
SQL> SELECT COUNT(*) FROM SCOTT.EMP,SCOTT.DEPT;
COUNT(*)
----------------
56
理解了表的合并算法，就可以知道为什么不使用WHERE子句设置主关键字等于外部关键字的条件就不能得到正确结果的原因了，限定主关键字等于外部关键字，可以得到想要的信息。
SQL> SELECT DNAME FROM SCOTT.EMP,SCOTT.DEPT 2 WHERE ENAME='SMITH' AND EMP.DEPTNO=DEPT.DEPTNO; DNAME -------------- RESEARCH
如果两张表中有同名的列，那么，在选择时一定要指明是哪张表中的列，表名作前缀，用“.”分隔。如果不指明，将会产生错误。
SQL> SELECT DEPTNO FROM SCOTT.DEPT,SCOTT.EMP
2 WHERE ENAME='SMITH';
SELECT DEPTNO FROM SCOTT.DEPT,SCOTT.EMP
*
ERROR 位于第 1 行:
ORA-00918: 未明确定义列
当SELECT语句中有多个表被引用时，表的名称被用来避免混淆。实际上，加在列名之前的表名可以认为是一种区域变量。当然，由于表名往往具有确定的含义，可能很长。为了提高SQL语句输入的效率，用户可以在FROM子句中为表名指定一个相应的符号，该符号在其他需要使用表名的地方用来代替表名，这就是区域变量的定义功能。
在SELECT语句的FROM子句中，通过在表名后指定一个字符常量来定义区域变量，语法如下所示。
FROM table_name_1 range_name_1, … , table_name_n range_name_n
对于上面经常用到的两张表SCOTT.EMP和SCOTT.DEPT，我们可以为它们定义一个区域变量。
SQL> SELECT E.ENAME,D.DNAME 2 FROM SCOTT.EMP E,SCOTT.DEPT D 3 WHERE E.DEPTNO=D.DEPTNO; ENAME DNAME ---------------- ------------------ SMITH RESEARCH ALLEN SALES WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES ADAMS RESEARCH ENAME DNAME ---------------- ------------------ JAMES SALES FORD RESEARCH MILLER ACCOUNTING 已选择14行。
在上面的例子中，我们使用区域变量E和D来分别指代表SCOTT.EMP和表SCOTT.DEPT。在查询中，用到多个表时，最好使用区域变量来代替冗长的表名。
5.4.4 CASE语句的使用
CASE语句可以在SQL中实现if-the-else型的逻辑，而不必使用PL/SQL。
有两种类型的CASE语句。
◎ 简单CASE语句，使用表达式确定返回值。
◎ 搜索CASE语句，使用条件确定返回值。
1．使用简单CASE语句
简单CASE表达式使用表达式确定返回值，语法如下：
CASE search_expression WHEN expression1 THEN result1 WHEN expression2 THEN result2 ... WHEN expressionN THEN resultN ELSE default_result END
其中：
◎ search_expression是待求值的表达式。
◎ expression1, expression2, ..., expressionN是要与search_expression进行比较的表达式。
◎ result1, result2, ..., resultN是（每一个可能的表达式所对应的）返回值。如果expression1的值等于search_expression，则返回result1，依此类推。
◎ default_result是无法找到相匹配的表达式时的默认返回值。
下面的这个例子展示了如何使用简单CASE语句。
SELECT product_id, product_type_id,
CASE product_type_id
WHEN 1 THEN 'Book'
WHEN 2 THEN 'Video'
WHEN 3 THEN 'DVD'
WHEN 4 THEN 'CD'
ELSE 'Magazine'
END
FROM products;
PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
1 1 Book
2 1 Book
3 2 Video
4 2 Video
5 2 Video
6 2 Video
7 3 DVD
8 3 DVD
9 4 CD
10 4 CD
11 4 CD
12 Magazine
2．使用搜索CASE语句
搜索CASE语句使用相关的条件来确定返回值，其语法如下。
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE default_result END
其中：
◎ condition1, condition2, ..., conditionN是待求值的表达式
◎ result1, result2, ..., resultN是返回值（对应每个可能出现的条件）。如果condition1为真，则返回result1，依次类推。
◎ default_result是当没有为真的条件时返回的默认结果。
下面的例子展示了搜索CASE语句的用法。
SELECT product_id, product_type_id,
CASE
WHEN product_type_id = 1 THEN 'Book'
WHEN product_type_id = 2 THEN 'Video'
WHEN product_type_id = 3 THEN 'DVD'
WHEN product_type_id = 4 THEN 'CD'
ELSE 'Magazine'
END
FROM products;
PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
1 1 Book
2 1 Book
3 2 Video
4 2 Video
5 2 Video
6 2 Video
7 3 DVD
8 3 DVD
9 4 CD
10 4 CD
11 4 CD
12 Magazine
在搜索CASE表达式中可以使用操作符，如下例所示。
SELECT product_id, price,
CASE
WHEN price > 15 THEN 'Expensive'
ELSE 'Cheap'
END
FROM products;
PRODUCT_ID PRICE CASEWHENP
---------- ---------- ---------
1 19.95 Expensive
2 30 Expensive
3 25.99 Expensive
4 13.95 Cheap
5 49.99 Expensive
6 14.95 Cheap
7 13.49 Cheap
8 12.99 Cheap
9 10.99 Cheap
10 15.99 Expensive
11 14.99 Cheap
12 13.49 Cheap
5.4.5 强大的DECODE函数
DECODE（value, search_value, result, default_value）对value与search_value进行比较，如果这两个值相等，DECODE()返回result，否则，就返回default_value。DECODE()允许在SQL中执行if-then-else类型的逻辑处理，而不需要使用PL/SQL。（注：DECODE不是标准SQL中的，是Oracle特有的函数。）
下面的例子介绍了DECODE()的使用方法。
SELECT DECODE(1, 1, 2, 3)
FROM dual;
DECODE(1,1,2,3)
---------------
2
在上面的例子中，DECODE()返回值为2，因为对1与1进行比较，两者相等，因此返回2。
下面的例子使用DECODE()对1和2进行比较。由于两者不等，因此返回3，如下所示。
SELECT DECODE(1, 2, 1, 3)
FROM dual;
DECODE(1,2,1,3)
---------------
3
下面的例子对more_products表中的available列进行比较，如果available等于Y，返回字符串Product is available；否则，返回字符串Product is not available。
SELECT prd_id, available,
DECODE(available, 'Y', 'Product is available',
'Product is not available')
FROM more_products;
PRD_ID A DECODE(AVAILABLE,'Y','PR
---------- - ------------------------
1 Y Product is available
2 Y Product is available
3 N Product is not available
4 N Product is not available
5 Y Product is available
可以向DECODE()传递多个搜索和结果参数，如下例所示。
SELECT product_id, product_type_id,
DECODE(product_type_id,
1, 'Book',
2, 'Video',
3, 'DVD',
4, 'CD',
'Magazine')
FROM products;
PRODUCT_ID PRODUCT_TYPE_ID DECODE(P
---------- --------------- --------
1 1 Book
2 1 Book
3 2 Video
4 2 Video
5 2 Video
6 2 Video
7 3 DVD
8 3 DVD
9 4 CD
10 4 CD
11 4 CD
12 Magazine
注意
◎ product_type_id等于1，则返回Book。
◎ product_type_id等于2，则返回Video。
◎ product_type_id等于3，则返回DVD。
◎ product_type_id等于4，则返回CD。
◎ product_type_id等于任何其他值，则返回Magazine。
5.5 DDL语句的使用
SQL语句的DDL部分主要用于创建各种类型的对象，例如表、视图等。本节主要介绍利用DDL语句创建各种常见对象的方法，包括表、视图、索引、主键、外键、序列、同义词及数据库链接等。
5.5.1 常用的数据类型
Oracle 11g支持的数据类型有几十种之多，其中，常用的数据类型大体可以分为字符类型、数字类型、日期类型和布尔类型4种，此外，也包括一些大对象类型，参见表5-3。
表5-3 常用的数据类型
Oracle支持的数据类型存放在DBA_TYPES数据字典中，可以以DBA登录系统进行查询。
SELECT TYPE_NAME FROM DBA_TYPES WHERE OWNER IS NULL;
结果如下：
TYPE_NAME ------------------------------ VARYING ARRAY VARCHAR2 VARCHAR UROWID UNSIGNED BINARY INTEGER(8) UNSIGNED BINARY INTEGER(32) UNSIGNED BINARY INTEGER(16) TIMESTAMP WITH TZ TIMESTAMP WITH LOCAL TZ TIMESTAMP TIME WITH TZ TYPE_NAME ------------------------------ TIME TABLE SMALLINT SIGNED BINARY INTEGER(8) SIGNED BINARY INTEGER(32) SIGNED BINARY INTEGER(16) REF REAL RAW POINTER PL/SQL STRING TYPE_NAME ------------------------------ PL/SQL ROWID PL/SQL REF CURSOR PL/SQL RECORD PL/SQL POSITIVEN PL/SQL POSITIVE PL/SQL PLS INTEGER PL/SQL NATURALN PL/SQL NATURAL PL/SQL LONG RAW PL/SQL LONG PL/SQL COLLECTION TYPE_NAME ------------------------------ PL/SQL BOOLEAN PL/SQL BINARY INTEGER OID OCTET NUMBER NAMED OBJECT NAMED COLLECTION LOB POINTER INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND INTEGER TYPE_NAME ------------------------------ FLOAT DOUBLE PRECISION DECIMAL DATE CONTIGUOUS ARRAY CLOB CHAR CFILE CANONICAL BLOB BINARY_FLOAT TYPE_NAME ------------------------------ BINARY_DOUBLE BINARY ROWID BFILE
5.5.2 表
由于创建数据库对象的工作是由DDL语句来完成的，所以，建立数据库应用总是从DDL语句开始的。例如，可以使用CREATE TABLE语句创建表。
使用CREATE TABLE语句创建表的简单语法如下。
CREATE TABLE [ [database.]owner.] table_name (column_name datatype [not null | null] IDENTITY[seed, increment] [constraint] [,column_name datatype [not null | null] IDENTITY[seed, increment]]] [constraint] …) [ON file group]
上述CREATE TABLE语句中，有几项是必需的，用户不仅要给出表的名称，还必须列出表中所有的列。
例如，使用CREATE TABLE语句创建一个名为COUNTRIES的表。
SQL> CREATE TABLE "SCOTT"."COUNTRIES"
2 ("COUTRY_ID" CHAR(2 BYTE) NOT NULL,
3 "COUTRY_NAME" VARCHAR2(40 BYTE) NOT NULL,
4 "COUTRY_SUBREGION" VARCHAR2(30 BYTE),
5 "COUTRY_REGION" VARCHAR2(20 BYTE) NOT NULL)
6 ;
表已创建。
由于Oracle在安装过程中就已经建立了一系列方案，且这些方案与Oracle安装过程中建立的一系列角色一一对应。因此，在不同方案下的表的所有者就是这些与方案一一对应的角色。所以，在上面的SQL语句中，用CREATE TABLE创建表除了给出了表名还同时给出了这个表的所有者名称。这个所有者对应的角色并不是我们注册SQL*PLUS所用的用户名。在下面的其他SQL语句对表进行操作时同样存在这个问题。我们往往在输入表名前加入这个表的所有者名称，并用“.”连接。
继续该例，对创建的新表先插入一行数据，然后用SELECT语句查询该表。
SQL> INSERT INTO SCOTT.COUNTRIES
2 (COUNTRY_ID,COUNTRY_NAME,COUNTRY_SUBREGION,
3 COUNTRY_REGION)
4 VALUES('CN','CHINA PR','ESTERN ASIA','ASIA');
已创建 1 行。
SQL> SELECT * FROM SCOTT.COUNTRIES;
CO COUNTRY_NAME COUNTRY_SUBREGION COUNTRY_REGION
---- ------------------------- ------------------------ --------------------------------
CN CHINA PR ESTERN ASIA ASIA
5.5.3 主键
主键是用来约束表的一个列或者几个列的，其取值是唯一的并且非空。在关系数据库中，使用主键来实现数据的一致性和完整性，可以使用关键字Primary Key来建立主键。在Oracle系统中，一旦在表的一列或者几列上创建了主键约束，Oracle会自动为该表建立一个唯一索引。
Oracle数据库引擎强制主键约束规则，在创建主键时，首先，所有的主键值都是唯一的；其次，它们必须有一个值，作为主键的列不能为NULL。
主键约束可以设置为disable和enable两种状态，可以使用ALTER TABLE语句来完成。如果将约束的状态设置为disable，则将减少数据加载时间，这是因为此时索引不需要更新（not update）。
可以在向一个表中加载数据时将约束状态设置为disable，加载完成后，再改回enable状态。约束规则仅应用于新加载的数据，对于旧的数据，即使违反商业规则，也仍保留在数据表中，这个面向商业规则的强制性策略可以应用于数据仓库，但必须有可供分析的历史数据，该选项要求enable约束使用NOVALIDATE关键字。
1．在创建表时定义主键
示例1：创建表dept时，在deptno列上定义了主键（可以不指定主键名称，此时，Oracle会为该主键自动命名）。
CREATE TABLE dept (deptno number(2) ,loc varchar2(20) ,PRIMARY KEY (deptno) );
示例2：在创建表dept时声明主键deptno，同时将主键命名为pk_deptno。
CREATE TABLE dept (depno number(2) ,dname varchar2(20) ,loc varchar2(20) ,CONSTRAINT pk_deptno PRIMARY KEY (deptno) USING INDEX TABLESPACE indx STORATE (INITIAL 10M NEXT 5M PCTINCRESE 0) ) TABLESPACE user STORATE (INITIAL 10M NEXT 5M PCTINCREASE 0)
说明
在上面表的创建过程中，为主键约束定义了名称，由于Oracle会自动为主键创建索引，所以，我们同时声明将主键索引建立在索引表空间indx中，而将表的数据存放在users表空间中，同时也指明了它们的存储方式。
Oracle强烈建议，任何应用程序的库表至少需要创建两个表空间，一个用于存储表数据，而另一个用于存储表索引数据。如果将表数据和索引数据放在一起，那么，表数据的I/O操作和索引的I/O操作将产生影响系统性能的I/O竞争，降低系统的响应效率。而将表数据和索引数据存放在不同的表空间中（如一个为APP_DATA，另一个为APP_IDX），并在物理层面将这两个表空间的数据文件放在不同的物理磁盘上，就可以避免这种竞争。
2．使用ALTER TABLE语句定义主键
如果在创建表时没有定义主键，则可以使用ALTER TABLE … ADD CONSTRAINT PRIMARY KEY创建主键。语法如下所示。
ALTER TABLE [scheme.]tablename ADD CONSTRAINT CONSTRIANT_NAME PRIMARY KEY (COLUMN1,[,COLUMN2,…])
例如：
ALTER TABLE T_ORDER DROP CONSTRAINT PK_ORDER_ID; ALTER TABLE T_ORDER ADD CONSTRAINT PK_ORDER_ID PRIMARY KEY (ORDER_ID)
3．使用ALTER TABLE语句修改主键的状态
示例3：通过使用ALTER TABLE语句，可以将主键约束设置为无效。
ALTER TABLE T_ORDER DISABLE CONSTRAINT PK_ORDER_ID; ALTER TABLE T_ORDER ENABLE PRIMARY KEY;
注意
如果存在引用该主键的外键值，则不能将主键的状态设置为无效，否则，Oracle会报告ORA-02297错误，此时，必须首先删除主外键关系，才能使主键无效。
4．删除主键
如果要删除主键约束，可以使用带有DROP子句的ALTER TABLE语句，语法如下。
ALTER TABLE [schema.]tablename DROP CONSTRAINT constraint_name [CASCADE];
删除顺序如下（如果后面加CASCADE，系统自动删除时，也按照以下顺序操作）。
5．使依赖于该主键的外键无效，然后删除相关的外键
6．使该主键无效，然后删除该主键
示例4：删除主键。
ALTER TABLE T_ORDER DROP PRIMARY KEY;
或者
ALTER TABLE T_ORDER DROP CONSTRAINT PK_ORDER_ID;
5.5.4 外键
外键也称为外部键约束，是保证完整性约束的唯一方法，也是关系数据库的精髓所在。外部键约束可以保证该数据列与所引用的主键约束的数据列一致。创建外键前，先要完成引用主键的创建，然后才能声明外键和引用。
1．在创建表时定义外键
关键语法如下：
CREATE TABLE [schema.]table_name …… //省略创建表的部分 [CONSTRAINT constraint_name] FROEIGH KEY (column1 [,column2,….] ) REFERENCES [scheme.]table_name (column1 [,column2,…] ON DELETE [CASCADE | SET NULL] );
语法部分说明。
◎ CONSTRAINT：给外键命名，如果没有此关键字，则Oracle会自动给外键命名。
◎ FOREIGE KEY：要引用的列名，如果是多列，则根据主键的列的顺序来确定。
◎ REFERENCES：要引用的列名。
◎ ON DELETE：设定当主键的数据列被删除时，外键所对应的列是否自动被删除。如果后面跟CASCADE选项，则自动被删除，如果后面跟SET NULL选项，则会将该外键值设为NULL。
举例：
（1）创建表dept，定义主键，为外键的引用做准备。
CREATE TABLE dept ( deptno NUMBER(2) ,dname VARCHAR2(9) ,loc VARCHAR(10) ,CONSTRAINT pk_dept PRIMARY KEY (deptno) )
（2）创建emp表，声明deptno列为外部键，并且指定在主键删除时，外键所对应的列也级联删除。
CREATE TABLE emp (empno NUMBER(4) ,ename VARCHAR2(10) ,job VARCHAR2(10) ,mgr NUMBER(4) ,hiredate DATE ,sal number(7,2) ,comm number(7,2) ,deptno ,constraint fk_deptno FOREIGH KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE );
2．使用ALTER TABLE语句定义外键
关键语法如下：
ALTER TABLE [schema.]table_name
ADD ( CONSTRAINT constraint_name FROEIGH KEY (column1[,column2,…])
REFERENCES [schema.]table_name (column1[,column2,…]
[ON DELETE {CASECADE| SET NULL}]
);
示例5：如果在创建EMP表时没有定义外键，则可以用以下的语句进行创建。
ALTER TABLE emp Add constraint fk_deptno FOREIGH KEY(deptno) REFERENCES dept(deptno)ON DELETE CASCADE
3．修改外键的状态
Oracle不允许改变已经被定义为外键的列，也不允许改变外键的名字，所以只能用ALTER TABLE语句定义一个新的外键或者使用DISABLE子句将已经存在的外键设置为无效（然后可以进一步删除）。
示例6：将上面定义在emp表上的外键设置为无效。
ALTER TABLE emp DISABLE CONSTRAINT fk_deptno;
4．删除外键
要删除已经定义的外键，需要使用ALTER TABLE中的DROP关键字，语法如下。
ALTER TABLE [schema.]talbe_name DROP CONSTRAINT constraint_name;
示例：
ALTER TABLE emp DROP CONSTRAINT fk_deptno;
此处需要强调的是，主键和外键约束是关系数据库的核心，在进行数据库的结构设计时，往往采用主键和外键来定义那些有关系的表，这样可以保证应用系统数据的完整性和一致性。
5.5.5 约束
约束，也称为完整性约束，Oracle系统的完整性约束包括主键、外键及CHECK、NOT NULL、UNIQUE INDEX 5种类型。
完整性约束是一种规则，不占用任何数据库空间。完整性约束存在于数据字典中，在执行SQL或PL/SQL期间使用。用户可以指明约束启用还是禁用，当约束启用时，它增强了数据的完整性，否则，不进行约束条件的检查，但约束始终存在于数据字典中。
主键和外键的约束在前面一节中已经介绍过了，下面主要讲解其余的几种类型。
1．CHECK约束
CHECK约束用于增强表中数据内容规则的一致性。比如，员工表中，员工的工资不能为负数，性别的值应该只有“男”、“女”两种情况。
CHECK约束可以被创建或者增加为在某个列级别上的约束，也可以被创建或者增加为在一个表级别上的约束。如果CHECK约束保护了多个数据列，则必须使用表约束的语法。
此外，如果在创建约束时没有具体给出约束的名字，则Oracle将产生一个sys_Cnnn形式的唯一约束名。为了确保数据库结构的可读性，建议给应用系统的约束命名。
语法：
CONSTRAINT [constraint_name] CHECK (condition);
示例：
CREATE TABLE worker (empno number(4) primary key ,name varchar2(10) ,sex char(2) check (sex='男' or sex='女') ,age number(2) check ( age between 18 and 65) ,sal number(9,2) check (sal>0.0 and sal<9999999.99) ,lodging char(15) references LODGING(lodging) );
2．NOT NULL约束
NOT NULL约束应用在单一的数据列上，该数据列不能为空值。默认情况下，允许任何列都可以有NULL值，但应用系统中有时要求某些列不能为空值。
例如：
CREATE TABLE worker (empno number(4) primary key ,name varchar2(10) NOT NULL ,sex char(2) check (sex='男' or sex='女') ,age number(2) check ( age between 18 and 65) ,sal number(9,2) check (sal>0.0 and sal<9999999.99) ,lodging char(15) references LODGING(lodging) );
NOT NULL约束既可以在创建表时指定，也可以在表创建完成后指定，此时的语法与一般的约束语法有所不同，示例如下。
ALTER TABLE worker MODIFY name NOT NULL
3．UNIQUE（唯一性）约束
唯一性约束要求一列或一组列中的每一个值都是唯一的。如果唯一性约束应用于单一列，则此列只有唯一的值；如果唯一性约束应用于一组列，那么，这组列合起来具有唯一的值。唯一性约束允许空值，除非此列也应用了NOT NULL非空约束。
唯一性约束既可以在创建表时创建，也可以在表创建完成以后，使用ALTER TABLE语句创建。
Oracle系统将唯一性约束作为一般索引来对待，即唯一性约束是一个占用空间的对象，所以，在创建唯一性约束时，一般都要用到USING INDEX TABLESPACE子句来为唯一性约束指定表空间（建议建立专门的索引表空间来存储）和存储分配。
（1）在创建表时建立唯一性约束。
CREATE TABLE table_name …… Column_name data_type CONSTRAINT constraint_name UNIQUE USING INDEX TABLESPACE INDX
将唯一性约束产生的索引存放在专门存放索引数据的索引表空间中。
（2）在组合列上建立唯一性约束（此时，该约束要作为表级别的约束来进行创建）。
CREATE TABLE table_name …… ,CONSTRAINT constraint_name UNIQUE (column1,column2,…) USING INDEX TABLESPACE indx STORAGE <stored clause>
例如：
CRATE TABLE insured_autos (policy_id NUMBER constraint pk_policies PRIMARY KEY ,vin VARCHAR2(10) ,coverage_begin DATE ,coverage_term NUMBER ,CONSTRAINT unique_auto UNIQUE(policy_id,vin) USING INDEX TABLESPACE indx STORAGE(INITIAL 10M NEXT 5M PCTINCREASE 0 );
（3）使用ALTER TABLE DISABLE语句设置唯一性索引无效。
语法如下：
ALTER TABLE table_name DISABLE CONSTRAINT CONSTRAINT_NAME;
（4）使用ALTER TABLE DROP语句删除唯一性约束。
语法如下：
ALTER TABLE table_name DROP CONSTRAINT unique_indx;
注意
用户不能删除带有外键指向表的唯一性约束，这种情况下，需要首先禁用或者删除外键。
另外，禁用或者删除唯一性约束通常会删除相关联的唯一索引，从而导致数据库性能的下降。
（5）直接使用CREATE UNIQUE INDEX语句创建唯一性索引。
语法如下：
CREATE UNIQUE INDEX index_name on table_name(column,…) tablespace indx
5.5.6 索引
索引是关系数据库中用于存放表中每一条记录位置的一种对象，主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求很高的工作，一般在数据库设计阶段就要考虑到如何设计索引，应用系统的性能直接与索引的合理与否有关系。
1．创建索引
创建索引的语法如下：
CREATE [UNIQUE] INDEX [schema.]index ON [schema.]table (column [ ASC | DESC ] , column [ ASC | DESC ]…) [CLUSTER schema.cluster] [INITRANS n] [MAXTRANS n] [PCTFREE n] [STORAGE storage] [TABLESPACE tablespace] [NO SORT]
以下是几个关键选项的说明。
◎ CLUSTER：指定一个聚簇（Hash cluster不能创建索引）；
◎ INITRANS,MAXTRANS：指定初始和最大的事务入口数；
◎ TABLESPACE：索引存储的表空间；
◎ STORAGE：存储参数；
◎ PCTFREE：索引数据块空闲空间的百分比；
◎ NOSORT：不排序（存储时就已经按照升序进行排序，所以指出不再排序）。
示例7：创建商场的商品库表结构，为该表的商品代码good_id创建唯一索引，以便在前台POS收款时提高查询速度。
CREATE TABLE good (good_id number(8) /* 商品的条码 */ ,good_desc varchar2(40) /* 商品描述*/ ,unit_cost number(10.2) /* 单价 */ ,good_unit varchar2(6) /* 单位 */ ,unit_pric number(10,2) /* 零售价 */ ) tablespace user;
下面在good_id列上创建唯一索引。
CREATE UNIQUE INDEX uindx_good on good(good_id);
2．修改索引
修改索引的主要工作是修改索引的存储参数、重建索引、对无用的空间进行合并等，这些工作由管理员来完成的。
修改索引的语法如下：
ALTER [UNIQUE] INDEX [user.]index INITRANS n Maxtrans n REBUILD [STORAGE <storage>]
◎ INITRANS n：表示一个块内同时访问的初始事务的入口数，n为十进制整数。
◎ MAXTRANS n：表示一个块内同时访问的最大事务入口数，n为十进制整数。
◎ REBUILD：表示根据原来的索引结构重新建立索引，即重新对表进行全表扫描以后创建索引数据。
◎ STORAGE <storage> ：表示存储数据，与CREATE INDEX相同。
示例8：使用ALTER INDEX语句修改索引pk_deptno的参数。
ALTER INDEX pk_deptno REBUILD STORAGE( INITIAL 1M NEXT 512K );
可以使用ALTER INDEX语句将索引修改为逆键索引，例如：
ALTER INDEX emp_ix REBUILD REVERSE;
可以使用ALTER INDEX语句将索引空间合并，例如：
ALTER INDEX ename_idx COALESCE;
3．删除索引
可以使用DROP语句删除索引。
DROP INDEX schema.index
注意
如果表结构被删除，则与该表有关的索引也会随之删除。
5.5.7 视图
简而言之，视图可以认为是“存储的查询”或是“虚拟的表”。视图的最大特点是，在执行查询时与表似乎完全一致，实际上，它只包含映射到基表（这里的基表既可以是真正的表也可以是视图）的一组SQL语句，其本身并不包含数据。
采用视图的一个主要目的是简化查询所使用的语句，此外还可以起到安全和保密的作用，还可以在查询处理中完成复杂的操作。
1．创建视图
视图的创建非常简单，因为它仅是包含一组SQL语句的数据库对象，使用CREATE VIEW语句可以创建视图，概要语法如下所示。
CREATE [OR REPLACE] view_name AS SELECT …… （一组语句）
示例9：创建一个视图用来查询SCOTT.EMP和SCOTT.DEPT中的信息。（其中用到的SQL语句在上一章中已经反复提到。）
SQL> CREATE OR REPLACE VIEW SCOTT.V_EMP_DEPT AS 2 SELECT ENAME,DNAME,JOB,SAL 3 FROM SCOTT.EMP E,SCOTT.DEPT D 4 WHERE E.DEPTNO=D.DEPTNO; 视图已建立。
可以像使用表一样使用视图。
SQL> SELECT * FROM SCOTT.V_EMP_DEPT; ENAME DNAME JOB SAL ------------- -------------------- ------------------ ---------------- SMITH RESEARCH CLERK 800 ALLEN SALES SALESMAN 1600 WARD SALES SALESMAN 1250 JONES RESEARCH MANAGER 2975 MARTIN SALES SALESMAN 1250 BLAKE SALES MANAGER 2850 CLARK ACCOUNTING MANAGER 2450 SCOTT RESEARCH ANALYST 3000 KING ACCOUNTING PRESIDENT 5000 TURNER SALES SALESMAN 1500 ADAMS RESEARCH CLERK 1100 JAMES SALES CLERK 950 FORD RESEARCH ANALYST 3000 MILLER ACCOUNTING CLERK 1300 已选择14行。
2．删除视图
语法如下：
DROP VIEW view_name;
5.5.8 序列
作为Oracle数据库对象，利用序列可生成唯一的整数。一般使用序列自动地生成主码值。
一个序列的值是由特殊的Oracle程序自动生成的，因此，序列避免了在应用层实现序列而引起的性能瓶颈。
Oracle序列允许同时生成多个序列号，而每一个序列号是唯一的。当一个序列号生成时，序列是递增的，独立于事务的提交或回滚。允许设计默认序列，不需指定任何子句。该序列为上升序列，由1开始，增量为1，没有上限。
1．建立序列命令
CREATE SEQUENCE [user.]sequence_name [increment by n] [start with n] [maxvalue n | nomaxvalue] [minvalue n | nominvalue];
◎ INCREMENT BY：指定序列号之间的间隔，该值可为正或负整数，但不可为0，序列为升序。忽略该子句时，默认值为1。
◎ START WITH：指定生成的第一个序列号，在升序时，序列从比最小值大的值开始，默认值为序列的最小值。对于降序，序列由比最大值小的值开始，默认值为序列的最大值。
◎ MAXVALUE：指定序列可生成的最大值。
◎ NOMAXVALUE：为升序指定最大值为1027，为降序指定最大值为-1。
◎ MINVALUE：指定序列的最小值。
◎ NOMINVALUE：为升序指定最小值为1，为降序指定最小值为-1026。
示例10：创建一个序列号的语句。
CREATE SEQUENCE EXAM_NO_SEQ START WITH 1484 MAXVALUE 9999999999 MINVALUE 1 CYCLE CACHE 20 NOORDER;
2．更改序列命令
ALTER SEQUENCE [user.]sequence_name [INCREMENT BY n] [MAXVALUE n| NOMAXVALUE ] [MINVALUE n | NOMINVALUE]；
修改序列可以：
◎ 修改未来序列值的增量；
◎ 设置或撤销最小值或最大值；
◎ 改变缓冲序列的数目；
◎ 指定序列号是否有序。
3．删除序列命令
DROP SEQUENCE [user.]sequence_name；
5.5.9 同义词
同义词（SYNONYM）是数据库对象的别名，用户可以使用同义词为表起一个替换名。同义词可以使多个用户使用同一个对象而不用将模式（schema）作为前缀加在对象的前面，从而简化SQL查询与授权操作。
使用同义词可以保护数据库对象的隐私，可以用CREATE SYNONYM语句创建。例如：
CREATE PUBLIC SYNONYM PUBLIC_EMP FRO SCOTT.EMP;
使用DROP SYNONYM语句可以删除同义词。例如：
DROP SYNONYM PUBLIC_EMP;
5.6 DML语句的使用
DML是Data Manipulation Language的缩写，译为“数据操纵语言”。DML语句主要用于数据库表、视图操作。在一般的关系数据库系统中，DML是指SELECT,INSERT,UPDATE及DELETE语句。而在Oracle 11g当中，DML除了包括SELECT,INSERT,UPDATE,DELETE语句之外，还包括CALL,EXPLAIN PLAN,LOCK TABLE,MERGE语句。本节我们只对INSERT,UPDATE,DELETE, MERGE语句及TRUNCATE语句进行简单的介绍。
5.6.1 使用INSERT语句插入表数据
1．一般INSERT语句的使用
INSERT语句用于向表中插入数据，语法如下：
INSERT INTO [user.]table[@db_link][(column1[,column2]…)]] Values (express1[,express2]…)
◎ table：要插入的表名；
◎ db_link：数据库链接名；
◎ column1,column2：表的列名；
◎ express ,…：表达式；
◎ subquery：子查询语句，可以是任何合法的SELECT语句；
◎ values：给出要插入的值列表。
2．多表INSERT语句的使用
在以前的版本中，如果想把数据插入多个表中，需要使用多条INSERT语句，从Oracle 9i版本开始，可以用一条INSERT语句实现向多个表中插入数据。
INSERT [ALL] [conditional_insert_clause] [insert_into_clause values_clause] (subquery)
其中conditional_insert_clause如下：
[ALL] [FIRST] [WHEN condition THEN] [insert_into_clause values_clause] [ELSE] [insert_into_clause values_clause]
上面的语法说明包含两种情况。
◎ 无条件
◎ 有条件
所以，后面的conditional_insert_clause中出现的[ALL]和INSERT后面的[ALL]是分属两种情形下的举例就很容易理解了。
3．无条件的INSERT ALL
INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID,hire_date HIREDATE,salary SAL,manager_id MGR FROM employees WHERE employee_id>200;
因为没有附加限制于检索出的记录，所以所有检索返回数据将根据其列名插入相应的表中。
4．有条件的INSERT ALL
INSERT ALL WHEN SAL>10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR>200 THEN INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID,hire_date HIREDATE,salary SAL,manager_id MGR FROM employees WHERE employee_id>200;
这里将对返回值进行比较；如果SAL大于10000则插入SAL_HISTORY，同理，MGR大于200的记录将插入MGR_HISTORY表，其余都将丢弃。
5．有条件的FIRST INSERT
FIRST与ALL的区别在于，当遇到第一个求值为TRUE的子句之后停止对WHEN子句求值，而ALL不论是否求值都为TRUE。
INSERT FIRST
WHEN SAL>25000 THEN
INTO special_sal VALUES(DEPTID,SAL)
WHEN HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
ELSE
INTO hiredate_history VALUES(DEPTID,HIREDATE)
SELECT department_id DEPTID,SUM(salary) SAL,
MAX(hire_date) HIREDATE
FROM employees
GROUP BY department_id;
上例中如果第一个WHEN子句求值为TRUE，则其后的WHEN子句将不会被执行，反之，将直到遇到第一个满足条件的子句为止。
5.6.2 使用UPDATE语句更新表数据
在需要修改表中数据时，可以使用UPDATE命令，该命令由三部分组成。
◎ UPDATE后跟一个或多个要修改的表，这部分是必不可少的。
◎ SET后跟一个或多个要修改的表列，这也是必不可少的。
◎ WHERE后跟更新限定条件，这是可选的。
例如，用户准备把customer表中的所有sales值改为0，只要执行命令update customer setsales = 0;即可。Oracle将修改所有的数据行，并返回被修改的行数。如果只想把state_cd为MA的客户的sales改为0，那么，可以用SQL语句update customer set sales = 0 where state_cd=' MA ';，可见UPDATE命令的功能十分重要。
5.6.3 使用DELETE语句删除表数据
DELETE命令用来从表中删除一行或多行数据，该命令由两部分组成。
◎ 关键字DELETE FROM后跟准备从中删除数据的表名，这是必不可少的。
◎ 关键字WHERE后跟删除条件，可选｡
如果用户想删除customer表的所有记录可以用SQL语句delete from customer｡如果用户要删除state_cd为CA的客户记录，可用SQL语句delete from customer where state_cd = 'CA';。
5.6.4 使用MERGE语句修改表数据
使用MERGE语句能够在一个SQL语句中对表同时执行插入和更新操作。MERGE命令从一个或多个数据源中选择行来更新或插入一个或多个表。在Oracle 11g中MERGE具有以下特点。
◎ UPDATE和INSERT子句是可选的；
◎ UPDATE和INSERT子句可以加WHERE子句；
◎ 在ON条件中使用常量过滤谓词来INSERT所有的行到目标表中，不需要连接源表和目标表；
◎ UPDATE子句后面可以跟DELETE子句，去除一些不需要的行。
首先创建示例表。
create table PRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
commit;
create table NEWPRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
commit;
1．可省略的UPDATE或INSERT子句
在Oracle 11g中，可以省略UPDATE或INSERT子句中的一个。下面的例子根据表NEWPRODUCTS的PRODUCT_ID字段是否匹配来更新表PRODUCTS的信息。
SQL> MERGE INTO products p 2 USING newproducts np 3 ON (p.product_id = np.product_id) 4 WHEN MATCHED THEN 5 UPDATE 6 SET p.product_name = np.product_name, 7 p.category = np.category; 3 rows merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1502 OLYMPUS CAMERA ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER TOYS SQL> SQL> ROLLBACK; Rollback complete. SQL>
在上面的例子中，MERGE语句影响的是产品id为1502、1601和1666的行，其产品名字和种类被更新为表newproducts中的值。下面的例子省略UPDATE子句，把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中，对于在两个表中能够匹配上PRODUCT_ID的数据不作任何处理。从该例子可以看到PRODUCT_ID=1700的行被插入到表PRODUCTS中。
SQL> MERGE INTO products p 2 USING newproducts np 3 ON (p.product_id = np.product_id) 4 WHEN NOT MATCHED THEN 5 INSERT 6 VALUES (np.product_id, np.product_name, 7 np.category); 1 row merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1502 OLYMPUS IS50 ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER DVD 1700 WAIT INTERFACE BOOKS
2．带条件的UPDATE和INSERT子句
可以将WHERE子句添加到UPDATE或INSERT子句中，来跳过UPDATE或INSERT操作对某些行的处理。下面的例子根据表NEWPRODUCTS来更新表PRODUCTS数据，但字段CATEGORY也必须得同时匹配上。
SQL> MERGE INTO products p 2 USING newproducts np 3 ON (p.product_id = np.product_id) 4 WHEN MATCHED THEN 5 UPDATE 6 SET p.product_name = np.product_name 7 WHERE p.category = np.category; 2 rows merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1502 OLYMPUS CAMERA ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER DVD SQL> SQL> rollback;
在上面的例子中，产品ID为1502、1601和1666匹配ON条件但是1666的category不匹配。因此，MERGE命令只更新两行数据。下面的例子展示了在UPDATE和INSERT子句都使用WHERE子句。
SQL> MERGE INTO products p 2 USING newproducts np 3 ON (p.product_id = np.product_id) 4 WHEN MATCHED THEN 5 UPDATE 6 SET p.product_name = np.product_name, 7 p.category = np.category 8 WHERE p.category = 'DVD' 9 WHEN NOT MATCHED THEN 10 INSERT 11 VALUES (np.product_id, np.product_name, np.category) 12 WHERE np.category != 'BOOKS' SQL> / 1 row merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1502 OLYMPUS IS50 ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER TOYS SQL>
注意
由于有WHERE子句，INSERT没有将所有不匹配ON条件的行插入到表PRODUCTS中。
3．无条件的INSERT
可以不需要连接源表和目标表就把源表的数据插入到目标表中。Oracle 11g支持在ON条件中使用常量过滤谓词。下面的例子从源表插入行到表PRODUCTS，不检查这些行是否在表PRODUCTS中存在。
SQL> MERGE INTO products p 2 USING newproducts np 3 ON (1=0) 4 WHEN NOT MATCHED THEN 5 INSERT 6 VALUES (np.product_id, np.product_name, np.category) 7 WHERE np.category = 'BOOKS' SQL> / 1 row merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1502 OLYMPUS IS50 ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER DVD 1700 WAIT INTERFACE BOOKS 6 rows selected. SQL>
4．新增加的DELETE子句
Oracle 11g中的MERGE提供了在执行数据操作时清除行的选项。可以在WHEN MATCHED THEN UPDATE子句中包含DELETE子句。DELETE子句必须有WHERE条件来删除匹配某些条件的行，匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除。
下面的例子验证DELETE子句。我们从表NEWPRODUCTS中合并行到表PRODUCTS中，但删除category为ELECTRNCS的行。
SQL> MERGE INTO products p 2 USING newproducts np 3 ON (p.product_id = np.product_id) 4 WHEN MATCHED THEN 5 UPDATE 6 SET p.product_name = np.product_name, 7 p.category = np.category 8 DELETE WHERE (p.category = 'ELECTRNCS') 9 WHEN NOT MATCHED THEN 10 INSERT 11 VALUES (np.product_id, np.product_name, np.category) SQL> / 4 rows merged. SQL> SELECT * FROM products; PRODUCT_ID PRODUCT_NAME CATEGORY ---------- -------------------- ---------- 1501 VIVITAR 35MM ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTER TOYS 1700 WAIT INTERFACE BOOKS SQL>
产品ID为1502的行从表PRODUCTS中被删除，因为它同时匹配ON条件和DELETE WHERE条件。产品ID为1501的行匹配DELETE WHERE条件但不匹配ON条件，所以它没有被删除。产品ID为1700的行不匹配ON条件，所以被插入表PRODUCTS。产品ID为1601和1666的行匹配ON条件但不匹配DELETE WHERE条件，所以被更新为表NEWPRODUCTS中的值。
5.6.5 TRUNCATE语句的使用
若要删除表中的所有行，则TRUNCATE TABLE语句是一种快速、无日志记录的方法，TRUNCATE TABLE与不含有WHERE子句的DELETE语句在功能上相同。但是，TRUNCATE TABLE速度更快，并且使用更少的系统资源和事务日志资源。
与DELETE语句相比，TRUNCATE TABLE具有以下优点。
（1）所用的事务日志空间较少。
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE释放用于存储表数据的数据页来删除数据，并且在事务日志中只记录页释放。
当使用行锁执行DELETE语句时，将锁定表中各行以便删除。TRUNCATE TABLE始终锁定表和页，而不是锁定各行。
（2）如无例外，在表中不会留有任何页。
执行DELETE语句后，表仍会包含空页。例如，必须至少使用一个排他（LCK_M_X）表锁，才能释放堆中的空表。如果执行删除操作时没有使用表锁，表（堆）中将包含许多空页。对于索引，删除操作会留下一些空页，尽管这些页会通过后台清除进程迅速释放。
与DELETE语句相同，使用TRUNCATE TABLE清空的表的定义与其索引和其他关联对象一起保留在数据库中。
下面的示例为删除JobCandidate表中的所有数据。在TRUNCATE TABLE语句之前和之后使用SELECT语句来比较结果。
USE AdventureWorks; GO SELECT COUNT(*) AS BeforeTruncateCount FROM HumanResources.JobCandidate; GO TRUNCATE TABLE HumanResources.JobCandidate; GO SELECT COUNT(*) AS AfterTruncateCount FROM HumanResources.JobCandidate; GO
5.7 使用描述语句
Oracle对SQL最重要的改进之一就是增加了描述（DESCRIBE）命令。使用DESCRIBE命令，用户可以快速掌握表及其中所有表列的概要。例如，查看SCOTT.EMP表的信息，如下所示。
SQL> DESCRIBE SCOTT.EMP 名称是否为空? 类型 ------------------------------------- -------------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
使用DESCRIBE命令掌握表中列的信息。通过DESCRIBE命令，可以查看改变的结果。
5.8 本章小结
本章介绍了SQL语言基础知识和Oracle SQL语句的初级使用，重点介绍了SELECT、UPDATE、DELETE的语法及其使用方法。