oracle11简单语句

----------------------8.查询sql语句等SQL语言概述 ----

1.1 merge 语句

使用merge语句，可以对指定的两个表执行合并操作，其语法如下： meger into table1_name

using table2_name on join_condition when matched then update set …

when not matched then insert … values … 语法说明如下： table1_name 表示需要合并的目标表。 table2_name 表示需要合并的源表。 join_condition 表示合并条件。

when matched then update 表示如果符合合并条件，则执行更新操作。

when not matched then insert 表示如果不符合合并条件，则执行插入操作。

1.1.2 UPDATE和INSERT

如果只是希望将源表中符合条件的数据合并到目标表中，可以只使用 update子句；如果只是希望将源表中不符合合并条件的数据合并到目标 表中，可以只使用insert子句。 在update子句和insert子句中，都可以使用where子句指定更新或 插入的条件。这时，对于合并操作来说，提供了两层过滤条件，第一层是 合并条件，由merge语句中的on子句指定；第二层是update或 insert子句中指定的where条件。从而使得合并操作更加灵活和精细

2. SQL语言概述

SQL（Structured Query Language，结构化查询语言）是目前最流行的关系查询语言，也是数据库的标准语言。用户可以使用SQL语言建立或删除数据库对象，插入、更新和删除数据库中的数据，并且可以对数据库执行各种管理。

SQL语言并不是Oracle数据库的专利，它是所有关系数据库管理系统的标准语言，也就是说，可以使用SQL语言对所有关系数据库进行操作。

2.1 SQL语言分类

SQL语言按照实现的功能不同，主要可以分为3类：数据操纵语言、数据定义语言和数据控制语言。

A .数据操纵语言（Data Manipulation Language，简称DML） DML语句 SELECT INSERT UPDATE DELETE CALL MERGE COMMIT ROLLBACK

功能说明 从表或视图中检索数据行 插入数据到表或视图 更新 删除 调用过程 合并（插入或修改） 将当前事务所做的更改永久化（写入数据库） 取消上次提交以来的所有更改 ? 数据定义语言（Data Definition Language，简称DDL）数据定义语言是一组SQL

命令，用于创建和定义数据库对象，并且将对这些对象的定义保存到数据字典中。通过DDL语句可以完成创建数据库对象、修改数据库对象和删除数据库对象

DDL语句 CREATE ALTER DROP RENAME 功能说明 创建数据库结构 修改数据库结构 删除数据库结构 更改数据库对象的名称 trucate 删除表的全部内容 ? 数据控制语言（Data Control Language，简称DCL） 数据控制语言用于修改数据库结构的操作权限 DCL语句 grant revoke 功能说明 授予其他用户对数据库结构的访问权限 收回用户访问数据库结构的权限 2.2 SQL语言规范 为了养成良好的编程习惯，编写SQL语句时需要遵循一定的规则：

? SQL关键字、对象名和列名不区分大小写，既可以使用大写格式，也可以使用小

写格式，或者混用大小写格式。

? 字符值和日期值区分大小写。当在SQL语句中引用字符值和日期值时，必须要给

出正确的大小写数据，否则不能返回正确信息。

? 在应用程序中编写SQL语句时，如果SQL语句文本很短，可以将语句文本放在一

行上；如果SQL语句文本很长，可以将语句文本分布到多行上，并且可以通过使用跳格和缩进提高代码的可读性。

? SQL*Plus中的SQL语句要以分号（;）结束。 3. SELECT语句

select [ all | distinct { * | expression | column1_name [ , column2_name ] [ , …] }

from { table1_name | ( subquery ) } [ alias ] [ , { table2_name | ( subquery ) } [ alias ] , … ] [ where condition ]

[ connect by condition [ start with condition ] ]

[ group by expression [ ,…] ] [ having condition [ ,…] ] [ { union | intersect | minus } ]

[ order by expression [ asc | desc ] [ , …] ] [ for update [ of [ schema. ] table_name | view ] column ] [ nowait ] ;

其中，[ ]表示可选项。语法说明如下：

? SELECT 必需的语句，查询语句的关键字。

? ALL 表示全部选取，而不管列的值是否重复。此选项为默认选项。 ? DISTINCT 当列的值相同时只取其中的一个，用于去掉重复值。 ? * 指定表的全部列。

? column1_name , column2_name ? 指定要查询的列的名称。可以指定一个或

? ? ? ? ? ? ? ? ? ? ? ? ?

多个列。

table1_name , table2_name? 指定要查询的对象（表或视图）名称。可以指定一个或多个对象。 subquery 子查询。

alias 在查询时对表指定的别名。

FROM 必需的语句，后面跟查询所选择的表或视图的名称。

WHERE 指定查询的条件，后面跟条件表达式。如果不需要指定条件，则不需要该关键字。

CONNECT BY 指定查询时搜索的连接描述。 START WITH 指定搜索的开始条件。

GROUP BY 指定分组查询子句，后面跟需要分组的列名。 HAVING 指定分组结果的筛选条件。

UNION | INTERSECT | MINUS 分别标识联合、交和差操作。 ORDER BY 指定对查询结果进行排序的条件。

ASC | DESC ASC表示升序排序（默认）；DESC表示降序排序。需要与ORDER BY子句联合使用。

FOR UPDATE ? NOWAIT 在查询表时将其锁住，不允许其他用户对该表进行DML操作，直到解锁为止。

4. WHERE子句

在执行简单查询语句时，如果没有指定任何限制条件，那么执行SELECT语句将会检索表的所有行。但是在实际应用中，用户往往只需要获得某些行的数据。例如检索雇员编号为7800的雇员信息，或者检索部门号为10的所有雇员信息等。在执行查询操作时，通过使用WHERE子句，可以指定查询条件，限制查询结果。

比较操作符 比较操作符 =、>、< <>、!= >= <= ANY ALL BETWEEN LIKE IN IS NULL 说明 等于、大于、小于 不等于 大于等于 小于等于 与一个列表中的任何值进行比较 与一个列表中的所有值进行比较 指定条件在两个值之间，包括边界值 匹配的字符样式，一般用于模糊查询 匹配的一个列表值 匹配空值 5. BETWEEN和IN操作符 在WHERE子句中可以使用BETWEEN操作符，用来检索列值包含在指定区间内的数据行。这个区间是闭区间，这就意味着包含区间的两个边界值。

【例】使用BETWEEN操作符，从scott用户的emp表中，检索empno列的值在7800和7900之间的记录。如下：

SELECT * FROM scott.emp WHERE empno BETWEEN 7800 AND 7900 ;

在WHERE子句中可以使用IN操作符，用来检索某列的值在某个列表中的数据行。

【例】对scott用户的emp表进行检索。在WHERE子句中使用IN操作符，检索empno列的值为7788、7800或7900的记录。如下：

SELECT * FROM scott.emp WHERE empno IN(7788,7800,7900); 6.LIKE操作符

在WHERE子句中可以使用LIKE操作符，用来查看某一列中的字符串是否匹配指定的模式。所匹配的模式可以使用普通字符和下面两个通配符的组合指定。

? 下划线字符（_） 匹配指定位置的一个字符。

? 百分号字符（%） 匹配从指定位置开始的任意个字符。

如果需要对一个字符串中的下划线和百分号字符进行文本匹配，可以使用escape选项标识这些字符。

escape后面指定一个字符，该字符用来告诉数据库，在字符串中该指定字符后面的字符表示要搜索的内容，从而区分要搜索的字符和通配符。

例如：‘%\\%%’ ESCAPE ‘\\‘ 。其中，在SCAPE后面指定反斜杠（\\）字符，那么在前面的字符串“‘%\\%%’”中，反斜杠后面的字符（也就是第二个%）表示要搜索的实际字符。第一个%是通配符，第3个%也是通配符，可以匹配任意个字符。所以，字符串“‘%\\%%’”用于匹配任意包含百分号（%）的字符串。

7.ORDER BY子句

在前面检索的数据中，数据的顺序是按照存储在表中的物理顺序显示的。这种物理顺序通常是比较混乱的。如果希望对显示的数据进行排序，可以使用ORDER BY子句。通过使用ORDER BY子句，可以强制一个查询结果按照升序或者降序进行排列。

在排序过程中，可以同时对多个列指定排序规则，多个列之间使用逗号（,）隔开。如果使用多个列进行排序，那么列之间的顺序非常重要，系统首先按照第一个列的值进行排序，当第一个列的值相同时，再按照第二个列的值进行排序，以此类推。

8.

在前面的操作中，都是对表中的每一行数据进行单独的操作。在有些情况下，需要把一个表中的行分为多个组，然后将这个组作为一个整体，获得该组的一些信息，例如获取各个部门的员工人数，或某个部门的员工的平均工资等。这时，就需要使用GROUP BY子句对表中的数据进行分组。使用GROUP BY子句，可以根据表中的某一列或某几列对表中的数据行进行分组，多个列之间使用逗号（,）隔开。如果根据多个列进行分组，Oracle会首先根据第一列进行分组，然后在分出来的组中再按照第二列进行分组，以此类推。 对数据分组后，主要是使用一些聚合函数对分组后的数据进行统计

GROUP BY子句

9.

HAVING子句通常与GROUP BY子句一起使用，在完成对分组结果的统计后，可以使用HAVING子句对分组的结果进行进一步的筛选。 一个HAVING子句最多可以包含40个表达式，HAVING子句的表达式之间使用关键字AND和OR分隔。 10.UPDATE语句

SQL语言使用UPDATE语句更新或修改满足条件的现有记录。一般情况下，UPDATE语句的 UPDATE table_name SET

{ column1_name = expression [ , column2_name = expression ] ? | ( column1_name[ ,column2_name ] ? ) = ( SELECT query ) } [ WHERE condition ] ;

语法说明如下： table_name 表示需要更新的表名。

? SET 用来设置需要更新的列以及列的新值。可以指定多个列，以便一次修改多

HAVING子句

个列的值。为需要更新的列分别指定一个表达式，表达式的值即为对应列的值。 ? SELECT query 与INSERT语句中的SELECT子查询句一样，在UPDATE语句中也可

以使用SELECT子语句获取相应的更新值。 ? WHERE 限定只对满足条件的行进行更新。

11.

当不再需要表中的某些数据时，应该及时删除该数据，以释放该数据所占用的空间。在Oracle系统中，删除表中的数据可以使用DELETE语句。 该语句的一般使用语法如下：

DELETE [ FROM ] [ schema. ] table_name [ WHERE condition ] ;

其中，DELETE FROM子句用来指定将要删除的数据所在的表；WHERE子句用来指定将要删除的数据所要满足的条件，可以是表达式或子查询。如果不指定WHERE子句，则将从指定的表中删除所有的行。

使用DELETE语句，只是从表中删除数据，不会删除表结构。如果要删除表结构，则应该使用DROP TABLE语句。

DELETE语句

12.TRUNCATE语句

使用TRUNCATE语句（DDL语言）可以删除表中的所有记录。使用TRUNCATE语句删除数据时，通常要比使用DELETE语句快得多，这是因为使用TRUNCATE语句删除数据时，不会产生回退信息，因此执行TRUNCATE操作也不能回退。

在TRUNCATE语句中还可以使用REUSE STORAGE关键字，表示删除记录后仍然保存记录占用的空间；与此相反，如果使用DROP STORAGE 关键字，则表示删除记录后立即回收记录占用的空间。默认使用DROP STORAGE关键字。 13. 算术运算符

在SELECT语句中，不但可以对表和视图执行查询操作，还可以执行数学运算（如+、-、*、/），也可以执行日期运算，也可以执行与列关联的运算。

在执行数学和日期运算时，经常使用系统提供的dual表。可以使用DESCRIBE命令查看该表的结构。该表只包含了一个字段，数据类型是VARCHAR2(1)，数据内容可以为空。

14.

实现多个表的简单连接时，如果仅仅通过SELECT子句和FROM子句连接多个表，那么查询的结果将是一个通过笛卡尔积所生成的表。所谓笛卡尔积所生成的表，就是一个基本表中每一行与另一个基本表的每一行连接在一起所生成的表，查询结果的行数是两个基本表的行数的积。

在笛卡尔积所生成的表中包含了大量的冗余信息。在检索数据时，为了避免冗余信息的出现，可以使用WHERE子句限定检索条件。在WHERE子句中使用等号（=）可以实现表的简单连接，表示第一个表中的列与第二个表中相应列匹配后才会在结果集中显示。 在多表查询时，如果多个表之间存在同名的列，则必须使用表名进行限定。例如使用scott.emp.deptno和scott.dept.deptno的方式，分别表示scott.emp表中的deptno列和scott.dept表中的deptno列。如果查询内容比较复杂，那么多次使用表名就会使语句变得繁琐，这时可以使用表别名的方式解决这个问题。

设置表的别名，只需要在FROM子句中引用该表时，将表别名跟在表的实际名称后面即可。表别名和表的实际名称之间使用空格进行分隔。

笛卡尔积

15.

在连接查询的FROM子句中，多个表之间可以使用英文逗号进行分隔。除了这种形式以外，SQL还支持使用关键字JOIN进行连接。在FROM子句中，使用JOIN连接的语法形式如下：

使用关键字JOIN进行连接

FROM join_table1 join_type join_table2 [ ON ( join_condition ) ] [ join_type ? ON join_condition , ? ] 语法说明如下：

? join_table1、join_table2 参与连接操作的表名。

? join_type 连接类型，连接类型有INNER JOIN（内连接）、OUTER JOIN（外连

接）和CROSS JOIN（交叉连接）。

? join_condition 连接条件，由被连接表中的列和比较运算符、逻辑运算 符等构成。可以使用多组join_type ? ON join_condition ?子句，实现多 个表的连接。

15.1 内连接

内连接是最常用的连接查询方式，使用INNER JOIN关键字进行指定。如果只使用JOIN关键字，默认表示内连接。内连接使用比较运算符，在连接表的某（些）列之间进行比较操作，并列出表中与连接条件相匹配的数据行。

根据使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接。

15.2 等值连接

所谓等值连接，是指在连接条件中使用等于（=）运算符比较被连接的值，也就是通过相等的列值连接起来的查询。

【例】使用INNER JOIN连接两个不同的表scott.emp和scott.dept，ON用来设置连接条件，使用WHERE子句限制查询范围。检索accounting部门的员工信息，如下： SQL> SELECT empno , ename , sal , d.deptno , dname

2 FROM scott.emp e INNER JOIN scott.dept d ON e.deptno = d.deptno 3 WHERE dname = 'ACCOUNTING';

15.3 不等连接

所谓不等连接，就是在连接条件中使用除等号（=）外的其他比较运算符，构成非等值连接查询。可以使用的比较运算符包括：>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）、!=（不等 于）、LIKE、IN和BETWEEN等。

【例】通过scott用户的emp表和salgrade表，查询员工的工资等级。使用BETWEEN运算符，建立不等连接，如下：

sql> select empno , ename , sal , grade

2 from scott.emp e inner join scott.salgrade s 3 on e.sal between s.losal and s.hisal; 15.4 自然连接

自然连接（NATURAL JOIN）是在两个表中寻找列名和数据类型都相同的字段，通过相同的字段将两个表连接在一起，并返回所有符合条件的结果。

使用自然连接，需要指定NATURAL JOIN连接关键字，但不需要指定连接条件。 【例】使用自然连接，重写【例8.22】中的语句，如下：

sql> select e.empno , e.ename , e.sal , deptno , d.dname 2 from scott.emp e natural join scott.dept d 3 where d.dname = ‘accounting’;

使用自然连接时，需要注意以下几点：

a.如果自然连接的两个表中有多个字段都满足名称和数据类型相同，那么它们都会被作为自然连接的条件。

b.如果自然连接的两个表中，仅仅是字段名称相同，而字段的数据类型不

同，那么使用该字段进行连接将会返回一个错误。

c.由于Oracle支持自然连接，那么在设计表时，应该尽量在不同的表中，将具有相同含义的字段使用相同的名字和数据类型。如果总是对主键和外键使用相同的名字，那么就可以满足自然连接。

15.5 外连接

使用内连接进行多表查询时，返回的查询结果集中仅包含符合查询条件和连接条件的行。内连接消除了与另一个表中的任何行不匹配的行，而外连接扩展了内连接的结果集，除了返回所有匹配的行外，还会返回一部分或全部不匹配的行，这就取决于外连接的类型。对于外连接，Oracle中可以使用加号（+）来表示，也可以使用LEFT RIGHT和FULL OUTER JOIN关键字。外连接可以分为下面这3类：

? 左外连接（LEFT OUTER JOIN或LEFT JOIN） ? 右外连接（RIGHT OUTER JOIN或RIGHT JOIN） ? 全外连接（FULL OUTER JOIN或FULL JOIN） 使用外连接，列出与连接条件相匹配的行，并且列出左表（左外连接时）、右表（右外连接时）或两个表（全外连接时）中，所有符合检索条件的数据行。 左外连接是在检索结果中除了显示满足连接条件的行外，还显示JOIN关键字左侧表中所有满足检索条件的行。

SQL> SELECT e.empno , e.ename , e.sal , d.grade

2 FROM scott.emp e LEFT OUTER JOIN scott.salgrade d 3 ON e.sal BETWEEN d.losal AND d.hisal; 右外连接是在结果中除了显示满足连接条件的行外，还显示JOIN右侧表中所有满足检索条件的行。

SQL> SELECT DISTINCT e.deptno , d.deptno

2 FROM scott.emp e RIGHT OUTER JOIN scott.dept d 3 ON e.deptno = d.deptno;

如果使用加号（+）实现右外连接，上述语句等价于下面的语句： SQL> select DISTINCT e.deptno , d.deptno 2 FROM scott.emp e , scott.dept d 3 WHERE e.deptno(+) = d.deptno;

全外连接是在结果中除了显示满足连接条件的行外，还显示JOIN两侧表中所有满足检索条件的行。

SQL> SELECT DISTINCT e.deptno , d.deptno

2 FROM scott.emp e FULL OUTER JOIN scott.dept d 3 ON e.deptno = d.deptno;

15.6 交叉连接 使用CROSS JOIN关键字，可以实现两个表的交叉连接，所得到的结果 将是这两个表中各行数据的所有组合，即这两个表所有数据行的笛卡尔积。交叉连接与简单连接操作非常相似，不同的是，使用交叉连接时，在FROM子句中多个表名之间不是用逗号，而是使用CROSS JOIN关键字隔开。另外，在交叉连接中不需要使用关键字ON限定连接条件，但是可以添加WHERE子句设置连接条件。

使用交叉连接，查询emp表和dept表中，部门编号为10的员工信息和部门信息，如下： SQL> SELECT empno , ename , sal , e.deptno , dname 2 FROM scott.emp e CROSS JOIN scott.dept d

3 WHERE e.deptno = 10 AND dname = 'ACCOUNTING';

15.7使用USING关键字

a.SQL/92标准可以使用USING关键字来简化连接查询，但是只有在查询 满足下面两个条件时，才能使用USING关键字进行简化：

? 查询必须是等值连接。

? 等值连接中的列必须具有相同的名称和数据类型。 【例】使用USING关键字，如下：

SQL> select empno , ename , sal , deptno , dname 2 from scott.emp e INNER JOIN scott.dept d 3 USING (deptno);

b.使用USING关键字简化连接时，需要注意以下几点：

? 使用emp表和dept表中的deptno列进行连接时，在USING子句和SELECT子句中，

都不能为deptno列指定表名或表别名。

? 如果在连接查询时使用了两个表中相同的多个列，那么就可以在USING子句中指

定多个列名，形式如下：

SELECT ? FROM table1 INNER JOIN table2 USING ( column1 , column2 ) 上述语句相当于下面的语句：

SELECT ? FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1 AND table1.column2 = table2.column2 ;

c.如果对多个表进行检索，就必须多次使用USING关键字进行指定，形式如下： SELECT ? FROM table1

INNER JOIN table2 USING ( column1 ) INNER JOIN table3 USING ( column2 ); 上述语句相当于下面的语句：

SELECT ? FROM table1 , table2 , table3 WHERE table1.column1 = table2.column1 AND table2.column2 = table3.column2 ;

15.8

----------------------7.建表、约束------------------

0.1使用SQL语句创建表----数据类型 数据类型 char [ ( length [ byte | char ] ) ] 说明 固定长度的字符数据。表示长度为length个字节或字符。BYTE表示按字节个数定义长度；CHAR表示按字符个数定义长度 Varchar2 ( length [ byte |char ] ) 可变长度的字符数据。表示长度最多可为length个字节或字符。最大长度可为4000字节 nchar [ ( length ) ] Nvarchar2 ( length ) number [ ( precision [ , scale] ) ]和 numeric [ ( precision [ , scale ] ) ] 固定长度的Unicode字符数据。最大长度为4000字节 可变长度的字符数据。表示长度为length个字符 可变长度的数字。precision是数字可用的最大位数（如果有小数点的话，是小数点前后位数之和），最大可为38；scale则表示小数点右边的最大位数。如果不指定precision和scale，则表示为小数点前后共38位的数字 int、integer和smallint binary_float binary_double date number的子类型。38位精度的整数 32位浮点数 64位浮点数 日期和时间。包括世纪、4位年份、月、日、时（24小时格式）、分和秒。可以存储公元前4712年1月1日和公元后4712年12月31日之间的日期和时间 timestamp [ ( seconds_precision ) ] clob nclob blob Bfile 日期和时间。包括世纪、4位年份、月、日、时（24小时格式）、分和秒 可变长度的单字节字符数据。最多存储128TB 可变长度的Unicode字符数据。最多存储128TB 可变长度的二进制数据。最多存储128TB 指向外部文件的指针。外部文件本身不存储在数据库中 0.2 创建表需要使用CREATE TABLE语句，其语法如下： CREATE TABLE [ schema. ] table_name(column_name data_type [ DEFAULT expression ] [ [ CONSTRAINT constraint_name ] constraint_def ][ , … ] )[ TABLESPACE tablespace_name ];

? schema 指定表所属的用户名，或者所属的用户模式名称。

? table_name 创建的表的名称。

? column_name 表中的列的名称，可以有多个列，多个列之间使用逗号（,）隔开。同一

个表中的列的名称必须惟一。 ? data_type 列的数据类型。

? DEFAULT expression 列的默认值。如果在向表中添加数据时，没有指定该列的数据，

则该列将使用默认值。

? CONSTRAINT constraint_name 为约束命名。如果不使用此子句，Oracle将自动为约束

建立默认的名称。如果是创建表级约束，则必须使用此子句为约束命名。 ? constraint_def 为列指定约束。例如非空约束、惟一约束等。

? TABLESPACE tablespace_name 可以为表指定存储表空间。如果不使用此子句，则使用

默认表空间存储新表。

1．1．0 NOT NULL约束 NOT NULL约束是指非空约束，用于要求向表中添加记录时必须为被约 束的列提供数据，否则将出现错误。NOT NULL约束只能在列级别上定 义，一个表中可以定义多个NOT NULL约束。

1.1.1添加NOT NULL约束在创建表时，为列添加NOT NULL约束，形式如下：

column_name data_type [ CONSTRAINT constraint_name ] NOT NULL 其中，CONSTRAINT constraint_name表示为约束指定名称。

也可以为已创建的表中的列添加NOT NULL约束，这时就需要使用ALTER TABLE ? MODIFY语

句，ALTER TABLE table_name MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL; 1.1.2删除NOT NULL约束

如果需要删除表中的列上的NOT NULL约束，依然是使用ALTER TABLE ? MODIFY语句，形式如下：

ALTER TABLE table_name MODIFY column_name NULL; 1.2.0 PRIMARY KEY约束

PRIMARY KEY约束是指主键约束，用于惟一标识一行记录。在一个表中只能定义一个PRIMARY KEY约束，该约束可以定义在单独的列上，也可以定义在多个列上（表级约束）。定义了PRIMARY KEY约束的列或列组合不能有重复值，也不能有NULL值。

1.2.1添加PRIMARY KEY约束

在创建表时，为列添加PRIMARY KEY约束，形式如下：

column_name data_type [ CONSTRAINT constraint_name ] PRIMARY KEY [ CONSTRAINT constraint_name ] PRIMARY KEY (column_name) [ , ? ] ) 也可以为已创建的表中的列添加PRIMARY KEY约束，形式如下 ALTER TABLE table_name ADD [ CONSTRAINT constraint_name ] PRIMARY KEY (column_name);

或 CREATE TABLE table_name (column_name data_type ,[ ? , ]

1.2.2删除PRIMARY KEY约束

删除列上的PRIMARY KEY约束，需要使用ALTER TABLE ? DROP语句，不过形式上只能采取指定约束名的方式，如下：

ALTER TABLE table_name DROP CONSTRAIN constraint_name;

如果在添加约束时使用CONSTRAINT子句为其指定了约束名，那么这里就可以直接使用该名

称而如果没有使用CONSTRAINT子句，则约束名由Oracle自动创建，此时就可以通过连接数据字典user_.cons_columns和user_constraints来查看约束名1.3 指定表空间

在Oracle数据库中，需要将表放在表空间中进行管理，所以在创建表时，可以使用TABLESPACE关键字指定该表存放于哪个表空间。在创建表时指定表空间的语法如下： TABLESPACE tablespace_name

1.4 管理表中的列

1.4.1 增加列 为表增加列的语法形式如下：

ALTER TABLE table_name ADD column_name data_type; 【例】为表person增加email列，如下：

SQL> ALTER TABLE person ADD email VARCHAR2(20);表已更改。 1．4.2 修改列的名称

修改表中的列的名称的语法如下：

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name; 【例】将person表中的birthday列的名称修改为age，如下：

SQL> ALTER TABLE person RENAME COLUMN birthday TO age;表已更改。 1.4.3修改列的数据类型

修改表中的列的数据类型的语法2如下：

ALTER TABLE table_name MODIFY column_name new_data_type; 【例】将person表中的age列的数据类型修改为NUMBER(4)，如下： SQL> ALTER TABLE person MODIFY age NUMBER(4);表已更改。 1.4.4 删除列

删除表中的列时可以分为一次删除一列和一次删除多列。一次删除一列的语法形式如下： ALTER TABLE table_name DROP COLUMN column_name; 一次删除多列的语法形式如下：

ALTER TABLE table_name DROP (column_name , ?);

对比两种语法可以看出，删除一列时需要使用COLUMN关键字，而删除多列时则不需要。

1.5 修改表

1.5.1 重命名

对于已存在的表，还可以修改其名称。重命名表有两种语法形式，一种是使用ALTER TABLE语句，语法如下： ALTER TABLE table_name RENAME TO new_table_name；

另一种是直接使用RENAME语句，语法如下：RENAME table_name TO new_table_name;

1.5.2 移动表

在创建表时可以为表指定存储空间，如果不指定，Oracle会将该表存储到默认表空间中。根据需要可以将表从一个表空间中移动到另一个表空间中。语法如下：

ALTER TABLE table_name MOVE TABLESPACE tablespace_name

1.5.3删除表 使用DROP TABLE语句可以删除表。删除表后，该表中的所有数据也将被删除。一般情况下，用户只能删除自己模式中的表；如果需要删除其他模式中的表，则该用户必须具有DROP ANY TABLE的系统权限。 删除表的语法如下：

DROP TABLE table_name [ CASCADE CONSTRAINTS ] [ PURGE ]; 语法说明如下：

? CASCADE CONSTRAINTS 指定删除表的同时，删除所有引用这个表的视图、约束、

索引和触发器等。

? PURGE 表示删除该表后，立即释放该表所占用的资源空间。 1.6 约束分类

数据库完整性（Database Integrity）是指数据库中数据的正确性和相容性，用来防止用户向数据库中添加不合语义的数据。数据库完整性是由各种各样的完整性约束来保证的，可以说，数据库完整性设计就是数据库完整性约束的设计。

按照不同的角度可以将表的完整性约束分成不同的类别。主要可以选取两个角度：约束的按照约束的作用域可以将表的完整性约束分为如下两大类： ? 表级约束 应用于表，对表中的多个列起作用。

作用域和约束的用途。

列级约束 应用于表中的一列，只对该列起作用。

约束 NOT NULL PRIMARY KEY UNIQUE CHECK FOREIGN KEY 简写 C P U C R 说明 非空约束。指定一列不允许存储空值。这实际就一种强制的CHECK约束 主键约束。指定表的主键。主键由一列或多列组成，惟一标识表中的一行 惟一约束。指定一列或一组列只能存储惟一的值 检查约束。指定一列或一组列的值必须满足的条件 外键约束。指定表的外键。外键引用另外一个表中 按照约束的用途可以将表的完整性约束分为5类

1.7 查看约束 通过查询数据字典视图USER_CONSTRAINTS，可以了解当前用户模式中所有约束的基本信息。 列 owner constraint_name constraint_type table_name status deferrable deferred 类型 VARACHAR2(30) VARACHAR2(30) VARACHAR2(1) VARACHAR2(30) VARACHAR2(8) VARACHAR2(14) VARACHAR2(9) 说明 约束的所有者 约束名 约束类型，值为P、R、C、U、V或O 约束定义所针对的表名 约束的状态。值为ENABLED或该约束是否为可延迟的。值为 约束是立即执行还是延迟执行。 通过查询数据字典视图USER_CONS_COLUMNS，可以了解定义约束的列。 列 owner constraint_name table_name column_name 类型 VARCHAR2(30) VARCHAR2(30) VARCHAR2(30) VARCHAR2(4000) 说明 约束的所有者 约束名 约束定义所针对的表名 约束定义所针对的列名 1.8指定级联操作类型 在添加FOREIGN KEY约束时，还可以指定级联操作的类型，主要用于

确定当删除（ON DELETE）父表中的一条记录时，如何处理子表中的外键 字段。有如下3种引用类型：

? CASCADE 此关键字用于表示当删除主表中被引用列的数据时，级联删除子表中

相应的数据行。

? SET NULL 此关键字用于表示当删除主表中被引用列的数据时，将子表中相应

引用列的值设置为NULL值。这种情况要求子表中的引用列支持NULL值。

? NO ACTION 此关键字用于表示当删除主表中被引用列的数据时，如果子表的引

用列中包含该值，则禁止该操作执行。默认为此选项。

-------------------6.日志操作---------------------------------

1.1创建日志文件组

语法如下：ALTER DATABASE database_name ADD LOGFILE [GROUP group_number] (file_name [, file_name [, ?]])[SIZE size] [REUSE]; 语法说明如下：

? GROUP group_number 为日志文件组指定组编号。 ? file_name 为该组创建日志文件成员。 ? SIZE number 指定日志文件成员的大小。

? REUSE 如果创建的日志文件成员已存在，可以使用REUSE关键字覆盖已存在的文件。但

是该文件不能已经属于其他日志文件组，否则无法替换。

创建日志文件，一般是指向日志文件组中添加日志成员，需要使用 ALTER DATABASE ? ADD LOGFILE MEMBER语句。 例如,向日志文件组GROUP 4中添加一个新的日志文件：

alter database add logfile member 'f:\\oraclefile\\logfile\\redo03.log'to group 4; 2.1切换日志文件组

日志文件组是循环使用的，当一组日志文件被写满时，Oracle系统自动切换到下一组日志文件。在需要的时候，数据库管理员也可以手动切换日志文件组。切换日志文件组需要使用 如下语句： ALTER SYSTEM SWITCH LOGFILE ; 2.2清空日志文件组

如果日志文件组中的日志文件受损，将导致数据库无法将受损的日志文件进行归档，这会最终导致数据库停止运行。此时，在不关闭数据库的情况下，可以选择清空日志文件组中的内容。 清空日志文件组语法如下： ALTER DATABASE CLEAR LOGFILE GROUP group_number ;

注意：被清空的日志文件组不能处于CURRENT状态，也就是说不能清空数据库当前正在使用的日志文件组。当数据库中只有两个日志文件组时，不能清空日志文件组 3.1删除日志文件 删除日志文件的语法如下：

ALTER DATABASE DROP LOGFILE MEMBER logfile_name ; 删除日志文件需要注意如下几点：

? 该日志文件所在的日志文件组不能处于CURRENT状态，需要执行一次手工日志切换，将

该日志文件组的状态修改为INACTIVE。

? 该日志文件所在的日志文件组中必须还包含有其他日志成员。

? 如果数据库运行在归档模式下，则应该在删除日志文件之前，确定它所在的日志文件组

已经被归档，否则会导致数据丢失。

3.2日志组的状态：

ACTIVE（活跃）、CURRENT（当前的）、INACTIVE（不活跃）、UNUSED（不用） 日志文件的状态：VALID（有效）、INVALID（无效）、STALE（陈旧）

3.3删除日志文件组的语法如下：

ALTER DATABASE DROP LOGFILE GROUP group_number ; 删除日志文件组需要注意如下几点： ? 一个数据库至少需要两个日志文件组。 ? 日志文件组不能处于CURRENT状态。

如果数据库运行在归档模式下，应该确定该日志文件组已经被归档 4.1归档日志

Oracle利用重做日志文件记录对数据库的操作，但是重做日志文件组是循环使用的，当所有的日志文件都被填满时，系统自动切换到第一组日志文件，当然数据库管理员也可以使用命令手工切换。而在循环使用日志文件时，日志文件中已经存在的日志内容将会被覆盖。为了完整地记录数据库的全部操作，Oracle提出了归档日志的概念。

如果是非归档日志模式，则切换日志文件时，日志文件中原有内容将被新的内容覆盖；如果是归档日志模式，则切换日志文件时，系统会首先对日志文件进行归档存储，之后才允许向文件中写入新的日志内容。

4.2 Oracle数据库有两种日志模式：

? 非归档日志模式（NOARCHIVELOG）

在非归档日志模式下，如果发生日志切换，则日志文件中原有内容将被新的内容覆盖； ? 归档日志模式（ARCHIVELOG）

在归档日志模式下，如果发生日志切换，则Oracle系统会将日志文件通过复制保存到指定的地方，这个过程叫“归档”，复制保存下来的日志文件叫“归档日志”，然后才允许向文件中写入新的日志内容。

在安装Oracle Database 11g时，默认设置数据库运行于非归档模式，这样可以避免对创建数据库的过程中生成的日志进行归档，从而缩短数据库的创建时间。在数据库成功运行后，数据库管理员可以根据需要修改数据库的运行模式。如果要修改数据库的运行模式，可以使用如下语句：

ALTER DATABASE ARCHIVELOG | NOARCHIVELOG ;

其中，ARCHIVELOG表示归档模式；NOARCHIVELOG表示非归档模

式。

4.3设置归档目标

归档目标就是指存放归档日志文件的目录。一个数据库可以有多个归档目标。在创建数据库时，默认设置了归档目标，可以通过db_recovery_file_dest参数查看。

设置归档目标的语法形式如下：

ALTER SYSTEM SET log_archive_dest_N = ' { LOCATION | SERVER } = directory ' ; 其中，directory表示磁盘目录；LOCATION表示归档目标为本地系统的目录；SERVER表示归

档目标为远程数据库的目录。

通过参数log_archive_format，可以设置归档日志名称格式。语法形式：

ALTER SYSTEM SET log_archive_format = ' fix_name%S_%R.%T ' SCOPE = scope_type ; 语法说明如下： ? fix_name%S_%R.%T

其中，fix_name是自定义的命名前缀；%S表示日志序列号；%R表示联机重做日志（RESETLOGS）log_archive_format参数的值必须包含%S、%R和%T匹配符。 ? SCOPE = scope_type

SCOPE有3个参数值：MEMORY、SPFILE和BOTH。其中，MEMORY 的ID值；%T表示归档线程编号。

表示只改变当前实例运行参数；SPFILE表示只改变服务器参数文件 SPFILE中的设置；BOTH则表示两者都改变。 5.1重新定义日志成员

重新定义日志成员，是指为日志成员组重新指定一个日志成员。

例如：GROUP 4文件组中包含一个redo01.log文件，现在移除该文件，改为包含redo01_new.log。

------------------5.表空间状态属性 --------------------

1. 表空间状态属性 表空间的状态属性主要有在线（ONLINE）、离线（OFFLINE）、只读

（READ ONLY）和读写（READ WRITE）这4种，其中只读与读写状态属于在线状态的特殊情况。通过设置表空间的状态属性，可以对表空间的使用进行管理。 1.1在线当表空间的状态为ONLINE时，才允许访问该表空间中的数据。

如果表空间不是ONLINE状态的，可以使用ALTER TABLESPACE语句将其状态修改为ONLINE，语句形式如下：ALTER TABLESPACE tablespace_name ONLINE; 1.2离线

当表空间的状态为OFFLINE时，不允许访问该表空间中的数据。例如向表空间中创建表或者读取表空间中的表的数据等操作都将无法进行。这时可以对表空间进行脱机备份；也可以对应用程序进行升级和维护等。如果表空间不是OFFLINE状态的，可以使用ALTER TABLESPACE语 句将其状态修改为OFFLINE，语句形式如下：

ALTER TABLESPACE tablespace_name OFFLINE parameter;

其中，parameter表示将表空间切换为OFFLINE状态时可以使用的参数。 ? NORMAL ? TEMPORARY ? IMMEDIATE ? FOR RECOVER 1.3只读

当表空间的状态为READ ONLY时，虽然可以访问表空间中的数据，但访问仅仅限于阅读，而不能进行任何更新或删除操作，目的是为了保证表空间的数据安全。如果表空间不是READ ONLY状态的，可以使用ALTER TABLESPACE语句将其状态修改为READ ONLY，语句形式如下： ALTER TABLESPACE tablespace_name READ ONLY;

不过，将表空间的状态修改为READ ONLY之前，需要注意如下事项：

? 表空间必须处于ONLINE状态 ? 表空间不能包含任何事务的回退段 ? 表空间不能正处于在线数据库备份期间 1.4读写

当表空间的状态为READ WRITE时，可以对表空间进行正常访问，包括对表空间中的数据进行查询、更新和删除等操作。如果表空间不是READ WRITE状态的，可以使用ALTER TABLESPACE 语句将其状态修改为READ WRITE，语句形式如下：

ALTER TABLESPACE tablespace_name READ WRITE;

修改表空间的状态为READ WRITE，也需要保证表空间处于ONLINE状态。 1.5 创建表空间

表空间是Oracle数据库中最大的逻辑存储结构，它与操作系统中的数据文件相对应，用于存储数据库中用户创建的所有内容。在Oracle中，表空间可以分为基本表空间、临时表空间、大文件表主要可以应用如下几个参数：

空间、非标准数据块表空间和撤销表空间等。

其中，基本表空间一般指用户使用的永久性表空间，用于存储用户的永久性数据；临时表空间用于存储排序或汇总过程中产生的临时数据；大文件表空间用于存储大型数据（例如LOB）；非标准数据块表空间用于在一个数据库实例中创建数据块大小不同的表空间；撤销表空间用于存储事务的撤销数据，在数据恢复时使用。

CREATE [ TEMPORARY | UNDO ] TABLESPACE tablespace_name [ DATAFILE | TEMPFILE 'file_name' SIZE size K | M [ REUSE ]

[ AUTOEXTEND OFF | ON

[ NEXT number K | M MAXSIZE UNLIMITED | number K | M ] ][ , ?]]

[ MININUM EXTENT number K | M ] [ BLOCKSIZE number K] [ ONLINE | OFFLINE ] [ LOGGING | NOLOGGING ] [ FORCE LOGGING ]

[ DEFAULT STORAGE storage ] [ COMPRESS | NOCOMPRESS ] [ PERMANENT | TEMPORARY ]

[ EXTENT MANAGEMENT DICTIONARY | LOCAL [ AUTOALLOCATE | UNIFORM SIZE number K | M ] ] [ SEGMENT SPACE MANAGEMENT AUTO | MANUAL ]; TEMPORARY | UNDO 指定表空间的类型。TEMPORARY表示创建临时表空间；UNDO表示创建撤销表空间；不指定类型，则表示创建的表空间为永久性表空间。 tablespace_name 指定新表空间的名称。

DATAFILE | TEMPFILE 'file_name‘ 指定与表空间相关联的数据文件。一般使用DATAFILE，如果是创建临时表空间，则需要使用TEMPFILE；file_name指定文件名与路径。可以为一个表空间指定多个数据文件。

SIZE size 指定数据文件的大小。

REUSE 如果指定的数据文件已经存在，则使用REUSE关键字可以清除并重新创建该数据文件。如果文件已存在，但是又没有指定REUSE关键字，则创建表空间时会报错。

AUTOEXTEND OFF | ON 指定数据文件是否自动扩展。OFF表示不自动扩展；ON表示自动扩展。默认情况下为OFF

NEXT number 如果指定数据文件为自动扩展，则NEXT子句用于指定数据文件每次扩展的大小。 MAXSIZE UNLIMITED | number 如果指定数据文件为自动扩展，则MAXSIZE子句用于指定数据文件的最大大小。如果指定UNLIMITED，则表示大小无限制，默认为此选项。 MININUM EXTENT number 表空间中的盘区可以分配到的最小的尺寸。

BLOCKSIZE number 如果创建的表空间需要另外设置其数据块大小，而不是采用初始化参数db_block_size指定的数据块大小，则可以使用此子句进行设置。此子句仅适用于永久性表空间。

ONLINE | OFFLINE 指定表空间的状态为在线（ONLINE）或离线（OFFLINE）。如果为ONLINE，则

表空间可以使用；如果为OFFLINE，则表空间不可使用。默认为ONLINE。

PERMANENT | TEMPORARY 指定表空间中数据对象的保存形式。PERMANENT表示持久保存；

TEMPORARY表示临时保存。

EXTENT MANAGEMENT DICTIONARY | LOCAL 指定表空间的管理

方式。DICTIONARY表示采用数据字典的形式管理；LOCAL表示采用本地化管理形式管理。默认为LOCAL。

AUTOALLOCATE | UNIFORM SIZE number 指定表空间中的盘区大小。AUTOALLOCATE表示盘区大小由Oracle自动分配，此时不能指定大小；UNIFORM SIZE number表示表空间中的所有盘区大小相同，都为指定值。默认为AUTOALLOCATE。

SEGMENT SPACE MANAGEMENT AUTO | MANUAL 指定表空间中段的管理方式。AUTO表示自动管理方式；MANUAL表示手动管理方式。默认为AUTO。 1.6创建和修改临时表空

临时表空间是一个磁盘空间，主要用于存储用户在执行ORDER BY等语句进行排序或汇总时产生的临时数据。默认情况下，所有用户都使用temp作为默认临时表空间。但是也允许使用其他临时表空间作为默认临时表空间，这需要在创建用户时进行指定。

创建临时表空间时需要使用TEMPORARY关键字，并且与临时表空间对应的是临时文件，由TEMPFILE关键字指定，也就是说，临时表空间中不再使用数据文件，而使用临时文件。

1．6.1修改临时表空间

创建临时表空间后，可以对该表空间进行修改。由于临时文件中不存储永久性的数据，只存储排序等操作过程中产生的临时数据，并且在用户操作结束后，临时文件中存储的数据由系统删除，所以一般情况下不需要调整临时表空间。但是当并发用户特别多，并且操作比较复杂时，可能会发生临时表空间不足。这时，数据库管理员可以增加临时文件来增大临时表空间。 如果需要增加临时文件，可以使用ADD TEMPFILE子句 。 如果需要修改临时文件的大小，可以使用RESIZE关键字。 可以修改临时文件的状态为OFFLINE或ONLINE。

1.7大文件表空间

大文件表空间是Oracle 10g引进的一个新表空间类型，主要用于解决存储文件大小不够的问题。与普通表空间不同的是，大文件表空间只能对应惟一一个数据文件或临时文件，而普通表空间则可以最多对应1022个数据文件或临时文件。

虽然大文件表空间只能对应一个数据文件或临时文件，但其对应的文件可达4G个数据块大小。而普通表空间对应的文件最大可达4M个数据块大小。

1.7.1创建大文件表空间

创建大文件表空间需要使用BIGFILE关键字，而且只能为其指定一个数据文件或临时文件。

与大文件表空间相对应，普通表空间一般使用SMALLFILE关键字表示，只不过默认情况下可以省略该关键字，这是因为默认创建的表空间类型就是SMALLFILE。可以通过查询数据字典database_properties了解当前数据库默认的表空间类型 。 1.8非标准数据块表空间

非标准(数据块)表空间，是指其数据块大小不基于标准数据块大小的表空间。在创建表空间时，可以使用BLOCKSIZE子句，该子句用来另外设置表空间中的数据块大小，如果不指定该子句，则默认的数据块大小由系统初始化参数db_block_size决定。db_block_size参数指定的数据块大小即标准数据块大小，在数据库创建

Oracle 11g允许用户创建非标准数据块表空间，使用BLOCKSIZE子句指定表空间中数据块的大小，但是必须有数据缓冲区参数db_nk_cache_size的值与BLOCKSIZE参数的值相匹配，。 BLOCKSIZE 2KB 4KB db_nk_cache_size db_2k_cache_size db_4k_cache_size 8KB 16KB 32KB db_8k_cache_size db_16k_cache_size db_32k_cache_size 例如，设置数据缓冲区参数DB_16K_CACHE_SIZE为16MB，如下： ALTER SYSTEM SET DB_16K_CACHE_SIZE = 16M;

设置缓冲区参数后，就可以创建数据块大小为16KB的非标准数据块表 空间：

SQL> create tablespace bigblockuse

2 datafile 'f:\\Oracle11g\%use\\bigblockuse0307.dbf' size 10m 3 autoextend on next 10m 4 blocksize 16k;

创建非标准表空间中，查询数据字典视图DBA_TABLESPACES的 BLOCK_SIZE字段，可以了解表空间的数据块大小，如下：

SELECT tablespace_name , block_size FROM dba_tablespaces; TABLESPACE_NAME BLOCK_SIZE --------------------------

------------------------- 8192

SYSTEM 已选择13行。

BIGBLOCKUSE 16384

1.9临时表空间组

在Oracle 11g中，用户可以创建临时表空间组，一个临时表空间组中可以包含一个或者多个临时表空间。 临时表空间组主要特征如下：

? 一个临时表空间组必须由至少一个临时表空间组成，并且无明确的最大数量限制。 ? 如果删除一个临时表空间组的所有成员，该组也自动被删除。 ? 临时表空间的名字不能与临时表空间组的名字相同。

? 在给用户分配一个临时表空间时，可以使用临时表空间组的名字代替实际的临时表空间

名；在给数据库分配默认临时表空间时，也可以使用临时表空间组的名字。

使用临时表空间组有如下优点：

? 由于SQL查询可以并发使用几个临时表空间进行排序操作，因此SQL查询很少会出现排序

空间超出，避免临时表空间不足所引起的磁盘排序问题。 ? 可以在数据库级指定多个默认临时表空间。

? 一个并行操作的并行服务器将有效地利用多个临时表空间。 ? 一个用户在不同会话中可以同时使用多个临时表空间。

1.20设置默认表空间

在Oracle中，用户的默认永久性表空间为system，默认临时表空间为temp。如果所有用户Oracle允许使用自定义的表空间作为默认永久性表空间，使用自定义临时表空间作为默认ALTER DATABASE DEFAULT [ TEMPORARY ] TABLESPACE tablespace_name;

如果使用TEMPORARY关键字，则表示设置默认临时表空间；如果不使用该关键字，则表示设都使用默认的表空间，无疑会增加system与temp表空间的竞争性。 临时表空间。设置默认表空间的语法如下：

置默认永久性表空间。

1.20.1 查询默认表空间

SQL> SELECT default_tablespace FROM user_users;

DEFAULT_TABLESPACE

------------------------------ SYSTEM

当前用户的默认表空间

SELECT property_name , property_value FROM database_properties WHERE property_name IN

('DEFAULT_PERMANENT_TABLESPACE' ,'DEFAULT_TEMP_TABLESPACE');

PROPERTY_NAME

PROPERTY_VALUE

------------------------------------------------------------------------------DEFAULT_TEMP_TABLESPACE TEMP 默认临时表空间

默认新用户的表空间

--------------

DEFAULT_PERMANENT_TABLESPACE USERS

1.21.1修改表空间(一)

1.21.2 增加表空间大小创建表空间时，必需在物理磁盘上为表空间创建数据文件。数据文件的 大小决定了创建的表空间的大小。构成表空间的数据文件可以位于不同的物理磁盘上，表空间的大小就等于不同物理磁盘上所有数据文件的大小之和。

如果在使用表空间的过程中，出现表空间不足的情况，那么就需要增加表空间的大小，具体实现时，可以采用增加数据文件的大小，也可以增加一个新的数据文件来增大表空间。 A．修改数据文件需要使用ALTER DATABASE语句，其语法如下：

ALTER DATABASE DATAFILE file_name RESIZE newsize K | M; 语法说明如下：

file_name 数据文件的名称与路径。RESIZE newsize 修改数据文件的大小为newsize b．选择为其增加新的数据文件。

增加新的数据文件需要使用ALTER TABLESPACE语句，其语法如下： ALTER TABLESPACE tablespace_name

ADD DATAFILE file_name SIZE number K | M [ AUTOEXTEND OFF | ON

[ NEXT number K | M MAXSIZE UNLIMITED | number K | M ] ][ , ?];

C．修改表空间中数据文件的自动扩展性

在创建表空间时，可以设置数据文件的自动扩展性。在为表空间增加新的数据文件时，也可以设置新数据文件的自动扩展性。而对于已创建的表空间中的已有数据文件，则可以使用ALTER DATABASE语句修改其自动扩展性，其语法如下：

ALTER DATABASE DATAFILE file_name AUTOEXTEND OFF | ON [ NEXT number K | M MAXSIZE UNLIMITED | number K | M ]

D．修改表空间中数据文件的状态

数据文件的状态主要有3种：ONLINE、OFFLINE和OFFLINE DROP。设置数据文件状态的语法如下：

ALTER DATABASE DATAFILE file_name ONLINE | OFFLINE | OFFLINE DROP

其中，ONLINE表示联机状态，此时数据文件可以使用；OFFLINE表示脱机状态，此时数据文

件不可使用，用于数据库运行在归档模式下的情况；OFFLINE DROP则会删除数据文件，与OFFLINE一样用于设置数据文件不可用，但它用于数据库运行在非归档模式下的情况。

将数据文件设置为OFFLINE状态时，不会影响到表空间的状态；但是将 表空间设置为OFFLINE状态时，属于该表空间的所有数据文件都被设置为 OFFLINE状态。

E．移动表空间中的数据文件

数据文件是存储于磁盘中的物理文件，它的大小受到磁盘大小的限制。如果数据文件所在的磁盘空间不够，则需要将该文件移动到新的磁盘中保存 。

【例】移动myspace表空间中数据文件myspace02.dbf的步骤如下： （1）修改myspace表空间的状态为OFFLINE，如下：

ALTER TABLESPACE myspace OFFLINE;

（2）在操作系统中，将磁盘中的myspace02.dbf文件移动到新的目录中，例如移动到F:\\oraclefile目录中。文件的名称也可以修改，例如修改为 myspace03.dbf。

（3）使用ALTER TABLESPACE语句，将myspace表空间中myspace02.dbf文件的原名称与路径修改为新名称与路径，如下：ALTER TABLESPACE myspace

RENAME DATAFILE 'E:\\app\\Administrator\\oradata\\orcl\\myspace02.dbf' TO 'F:\\oraclefile\\myspace03.dbf';

（4）修改myspace表空间的状态为ONLINE，如下：

ALTER TABLESPACE myspace ONLINE; F．重命名表空间

在需要的情况下，可以对表空间的名称进行修改。修改表空间的名称，不会影响到表空间中的数据。但不能修改系统表空间system与sysaux的名称。

重命名表空间的语法如下：

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name; 注意：如果表空间的状态为OFFLINE，则无法重命名该表空间

G．删除表空间

删除表空间需要使用DROP TABLESPACE语句，其语法如下：

DROP TABLESPACE tablespace_name

[ INCLUDING CONTENTS [ AND DATAFILES ] ] 语法说明如下： ? INCLUDING CONTENTS

表示删除表空间的同时，删除表空间中的所有数据库对象。如果表空间 ? AND DATAFILES

表示删除表空间的同时，删除表空间所对应的数据文件。如果不使用此 选项，则删除表空间实际上仅是从数据字典和控制文件中将该表空间的有 关信息删除，而不会删除操作系统中与该表空间对应的数据文件。 中有数据库对象，则必须使用此选项。

2.1 切换 撤销表空间

一个数据库中可以有多个撤销表空间，但数据库一次只能使用一个撤销表空间。默认情况下，数据库使用的是系统自动创建的undotbs1撤销表空间。如果要将数据库使用的撤销表空间切换成其他表空间，使用ALTER SYSTEM语句修改参数undo_tablespace的值即可。切换撤销表空间后，数据库中新事务的撤销数据将保存在新的撤销表空间中。

如：alter system set undo_tablespace = undotbs02; 2.1修改撤销记录保留的时间

在自动撤销记录管理方式中，可以指定撤销信息在提交之后需要保留的时间，以防止在长时间的查询过程中出现snapshot too old错误。在自动撤销管理方式下，DBA使用UNDO_RETENTION参数，指定撤销记录的保留时间。由于UNDO_RETENTION参数是一个动态参数，在

Oracle实例的运行中，可以通过ALTER SYSTEM SET UNDO_RETENTION语句，来修改撤销记录保留的时间。 撤销记录保留时间的单位是秒，默认值为900，即15分钟。

例如，将撤销记录的保留时间修改为10分钟，如下： SQL> alter system set undo_retention = 600; SQL> show parameter undo; NAME -------------------------

TYPE

VALUE ----------

---------

undo_management string AUTO undo_retention integer 600 undo_tablespace string UNDOTBS 2.2删除撤销表空间

删除撤销表空间同样需要使用DROP TABLESPACE语句，但删除的前提是该撤销表空间此时没有被数据库使用。如果需要删除正在被使用的撤销表空间，则应该先进行撤销表空间的切换操作。 如：drop tablespace undotbs02 including contents and datafiles;

3. 临时表空间组操作

3.1．创建临时表空间组

临时表空间组不需要特别创建，只需要在创建临时表空间时，使用 TABLESPACE GROUP语句为其指定一个组即可。 数据字典dba_tablespace_groups 使用ALTER TABLESPACE语句

3.2. 查看临时表空间组信息 3.3．移动临时表空间 3.4．删除临时表空间组

4.1 撤销表空间

为了实现对数据回退、恢复、事务回滚以及撤销等操作，Oracle数据库提供了一部分存储空间，专门保存撤销记录，将修改前的数据保存到该空间中，所以这部分空间被称为撤销表空间。多个撤销表空间可以存在于一个数据库中，但是在任何给定的时间内只有一个撤销表空间是可以获得的

4.2创建撤销表空间

创建撤销表空间需要使用CREATE UNDO TABLESPACE语句 。

例如，使用CREATE UNDO TABLESPACE语句，创建一个名称为UNDOTBS的撤销表空间，如下：SQL> create undo tablespace undotbs

2 datafile ‘e:\\app\\myspace\%undo01.dbf' size 20m 3 autoextend on; 表空间已创建。

4.3修改撤销表空间的数据文件

由于撤销表空间主要由Oracle系统自动管理，所以对撤销表空间的数据文件的修改也主要限于以下几种形式：

? 为撤销表空间添加新的数据文件 alter tablespace undotbs

add datafile ' e:\\app\\myspace\%undo02.dbf' size 10m;

? 修改撤销表空间的数据文件大小

alter database datafile ' e:\\app\\myspace\%undo02.dbf' resize 15m;

? 设置撤销表空间的数据文件的状态为ONLINE或OFFLINE alter tablespace undotbs offline;

--4.sql*plus（创建简单报表、计算小计、使用COLUMN命令、使用DESCRIBE命令、

使用EDIT命令、使用GET命令、使用LINESIZE命令、使用PAGESIZE命令、使用SAVE命令、使用SPOOL命令、使用START命令、使用临时变量、使用已定义变量）———

A.SQL*Plus语句

命令 A[PPEND] text C[HANGE] /old/new CL[EAR] BUFF[ER] I[NPUT] text DEL m n L[IST] n R[UN]或 / n n text 0 text 说明 将text附加到当前行之后 将当前行中的old替换为new 清除缓存区中的所有行 插入指定的文本text 删除从第m行到第n行之间的命令行 列出第n行 显示缓冲区中保存的语句，并运行这些语句 将第n行作为当前行 使用text文本替代第n行信息 在第一行之前插入text文本 A. 报表 所谓报表就是用表格、图表等格式来动态显示数据。计算机上的报表的 主要特点是数据动态化，格式多样化，并且实现报表数据和报表格式的完 全分离，用户可以只修改数据，或者只修改格式。 1. 为报表设置页眉和页脚 命令 说明 TTI[TLE] [ printspec 指定出现在报表中每一个页面顶端的页眉。其中printspec的可选值有 [ text | variable ] ...] | [ OFF | ON ] LEFT、CENTER、RIGHT、BOLD、FORMAT text、COL n、S[KIP] [n] 和TAB n。ON选项表示启用设置；OFF选项表示取消设置。还可以带有用户变量 （内容由系统来维护），如SQL.PNO页号、SQL.LNO行号等 BTI[TLE] [ printspec 指定出现在报表中每一个页面底 [ text | variable ] ...] | [ OFF | ON ] 端的页脚 C.BREAK命令和COMPUTE命令 在执行查询操作或创建报表时，可以使用BREAK和COMPUTE命令，用来对输出的结果进行统计计算。其中使用BREAK命令，可以让SQL*Plus根据列值的范围分隔输出结果，可以使得重复的列值不进行显示;COMPUTE子句可以让SQL*Plus计算一列的值，并在最后输出显示。用法如下：

BRE[AK] [ ON column_name ] SKIP n

COMP[UTE] function LABEL label OF column_name ON break_column_name 语法说明如下：

? column_name 表示对哪一列执行操作。

? SKIP n 表示在指定列的值变化之前插入n个空行。

? function 表示执行的操作，例如SUM（求和）、MAXIMUM（最大值）、MINIMUM（最小

值）、AVG（平均值）、COUNT（非空值的列数）、NUMBER（行数）、VARIANCE（方差）以及STD（均方差）等。

? LABEL 指定显示结果时的文本信息。

D.使用COLUMN命令

1.格式化列的显示效果

COLUMN命令可以对列的显示效果，包括列标题和列数据，进行格式化。该命令的语法如下：

COL[UMN] [ { column_name | ALI[AS] alias } [ options ] ] 语法说明如下：

? column_name 指定列名。

? ALIAS alias 指定要格式化的列的别名。

? options 指定用于格式化列或别名的一个或者多个选项。 2.OPTION选项 选项 FOR[MAT] format 说明 将列或列名的显示格式设置为由format字符串指定的格 式，format可以使用的格式如下表 HEA[DING] text JUS[TIFY] [ { LEFT | CENTER | RIGHT } ] WRA[PPED] 设置由text字符串指定的列标题 将列的输出信息设置为左对齐、居中对齐或右对齐 在输出结果中将一个字符串的末尾换行显示。该选项可 能导致单个单词跨越多行 WOR[D_WRAPPED] CLE[AR] TRUNCATED NULL text 与WRAPPED选项类似，但是单个单词不会跨越多行 清除列的格式化 删除第一行的字符串 指定列为空值时显示的内容 PRINT NOPRINT 显示列标题 隐藏列标题 2. format格式元素 元素 An 9 $ L . , 说明 为[VAR]CHAR类型的列内容设置宽度。如果内容超过指定的宽度，则内容自动换行 设置NUMBER列的显示格式 浮动的货币符号 本地货币符号 小数点位置 千位分隔符

举例 A5 999 999 $9999 L9999 9999.99 9,999 4.使用DESCRIBE命令 描述形容 命令 HELP [topic] HOST HOST 操作系统命令 CLEAR SCR[EEN] SHOW [ALL | USER | SGA | ERRORS | REL[EASE] | PARAMETERS ] DESC[RIBE] 说明 查看命令的使用方法，topic表示需要查看的命令名称。例如：HELP DESC 使用该命令可以从SQL*Plus环境切换到操作系统环境，以便执行操作系统命令 执行操作系统命令，例如：HOST notepad.exe 清除屏幕内容 查看SQL*Plus的所有系统变量值信息、当前是哪个用户 在使用SQL*Plus、显示SGA大小、查看错误信息、数据 库版本信息、系统初始化参数信息 查看对象的结构，这里的对象可以是表、视图、存储过程、函数和包等。例如：DESC dual DESCRIBE命令语法如下：

DESC[RIBE] { [ schema. ] object [ @connect_identifier ] } 语法说明如下：

? DESC[RIBE]：DESCRIBE可以简写为DESC。

? schema：指定对象所属的用户名，或者所属的用户模式名称。 ? object：表示对象的名称，如表名或视图名等。 ? @connect_identifier ： 表示数据库连接字符串。

5. EDIT命令 编辑

5.1使用EDIT命令编辑缓冲区内容

使用EDIT命令，可以将SQL*Plus缓冲区的内容复制到一个名为afiedt.buf的文件中，然后启动操作系统中默认的编辑器打开这个文件，并且文件内容能够进行编辑。在Windows操作系统中，默认的编辑器是Notepad（记事本）。

ED[IT] [ file_name ]其中file_name默认为afiedt.buf，也可以指定一个其他的文件。

6.SPOOL命令复制输出结果到文件

使用SPOOL命令实现将SQL*Plus中的输出结果复制到一个指定的文件中，或者把查询结果发送到打印机中，直到使用SPOOL OFF命令为止。 SPOOL命令的语法如下：

SPO[OL] [ file_name [ CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT ]

? ? ? ? ? ?

file_name 指定一个操作系统文件。 CREATE 创建一个指定的file_name文件。

REPLACE 如果指定的文件已经存在，则替换该文件。 APPEND 将内容附加到一个已经存在的文件中。

OFF 停止将SQL*Plus中的输出结果复制到file_name文件中，并关闭该文件。 OUT 启动该功能，将SQL*Plus中的输出结果复制到file_name文件。

7.使用GET命令

7.1使用GET命令读取文件内容到缓冲区

使用GET命令的语法如下：GET [ FILE ] file_name [ LIST | NOLIST ]

语法说明如下：file_name 表示一个指定文件，将该文件的内容读入SQL*Plus缓冲区中。

LIST 列出缓冲区中的语句。 NOLIST 不列出缓冲区中的语句

8.使用LINESIZE命令

8.1设置一行显示的字符数量

使用LINESIZE命令可以设置一行数据可以容纳的字符数量，默认数量为80。如果LINESIZE的值比较小，那么表中一行数据，有可能在屏幕上需要分多行显示；如果LINESIZE的值设置大一些，一行数据就可以在屏幕的一行中进行显示了。

LINESIZE命令的语法如下：SET LINESIZE n

其中，n表示屏幕上一行数据可以容纳的字符数量，有效范围是1 – 32767。

9. 使用PAGESIZE命令

设置一页显示多少行数据

使用PAGESIZE命令，可以设置每一页的大小，从而控制每一页显示的数据量。PAGESIZE命令的语法如下：SET PAGESIZE n

其中，参数n表示每一页大小的正整数，最大值可以为50,000，默认值为14。

页并不是仅仅由输出的数据行构成的，而是由SQL*Plus显示到屏幕上的所有输出结果构成，包括标题和空行等。

10.使用SAVE命令

使用SAVE命令将缓冲区内容保存到文件

使用SAVE命令可以将当前缓冲区的内容保存到文件中，这样，即使缓 冲区中的内容被覆盖，也保留有前面的执行语句。SAVE命令的语法如下：

SAV[E] [ FILE ] file_name [ CRE[ATE] | REP[LACE] | APP[END] ] 语法说明如下：

? file_name 表示将SQL*Plus缓冲区的内容保存到由file_name指定的文件中。 ? CREATE 表示创建一个file_name文件，并将缓冲区中的内容保存到该文件。该选项为

默认值。

? APPEND 如果file_name文件已经存在，则将缓冲区中的内容，追加到file_name文件的

FROM Goods WHERE t_ID='01' AND g_Price>

例-3 需要了解所有年龄在～25岁之间的会员的名称和年龄（用Nl表示，不是基本表中的字段，是计算出来的列

SELECT c_Name, TO_CHAR(SYSDATE,'YYYY') - TO_CHAR(c_BIRTH,'YYYY') NL FROM Customers

WHERE TO_CHAR(SYSDATE,'YYYY') - TO_CHAR(c_BIRTH,'YYYY') BETWEEN 20 AND 25 例-5 需要了解来自'湖南株洲'和'湖南长沙'两地会员的详细信息 SELECT c_ID,c_Name,c_Address FROM Customers

WHERE SUBSTR(c_Address,1,4) IN ('湖南株洲','湖南长沙')

例-6需要了解家庭地址不是'湖南株洲'和'湖南长沙'的商品的详细信息。 SELECT c_ID,c_Name,c_Address FROM Customers

WHERE SUBSTR(c_Address,1,4) NOT IN ('湖南株洲','湖南长沙')

3.选择行-条件查询-使用通配符

【例2-7】 需要了解所有商品中以“三星”两个字开头的商品的详细信息。 SELECT * FROM Goods WHERE g_Name LIKE '三星%‘

【例2-8】 需要了解姓“黄”且名字中只有两个汉字的会员的会员名、真实姓名、电话和电子邮箱。

SELECT c_Name, c_TrueName, c_Phone, c_E-mail FROM Customers

WHERE c_TrueName LIKE '黄_'

4.选择行-条件查询-空值判断

对于那些允许空值的列，可以使用IS NULL或IS NOT NULL来判断其值是否为空。对于使用IS NULL的查询表达式，如果返回值为逻辑真，则说明当前数据记录对应列的值为空，否则为非空。IS NOT NULL的含义与IS NULL恰好相反。

【例2-10】 查询暂时没有商品图片的商品信息 SELECT * FROM Goods WHERE g_Image IS NULL

5.选择行-使用DISTINCT

【例2-11】 需要了解在WebShop网站进行了购物并下了订单的会员编号。 SELECT g_ID FROM OrderDetails

SELECT DISTINCT g_ID FROM OrderDetails

6.选择行-使用ROWNUM

【例2-12】查询商品表中前5条商品的详细信息。

SELECT * FROM WHERE

SCOTT.GOODS ROWNUM <= 5;

在eBuy电子商城中，“新品推荐”功能中的商品就可以通过这种方式进行选择

7.选择行-查询结果排序

在输出查询数据记录集时，默认的数据记录显示顺序是按这些数据记录在原表中的逻辑排列顺序。如果需要定制查询数据记录集的输出顺序，则可以使用ORDER BY子句来实现。ORDER BY子句能够按照指定的一个或多个列（表达式）的升序或降序来重新排列查询数据记录集的输出顺序。 【例3-1】需要了解商品类别号为“01”的商品的商品号、商品名称和商品单价，并要求根据商品的价格进行降序（价格由高到低）排列。

SELECT g_ID, g_Name, g_Price FROM SCOTT.Goods WHERE t_ID='01' ORDER BY g_Price DESC

【例3-2】在“【例3-1】”中，如果商品的价格相同，要求根据商品名称进行升序排列。 SELECT g_ID, g_Name, g_Price FROM SCOTT.Goods WHERE t_ID='01' ORDER BY g_Price DESC,g_Name ASC

方法二.除了使用列排序外，还可以使用还对应编号排序 asc可以省略 SELECT g_ID, g_Name, g_Price FROM SCOTT.Goods WHERE t_ID='01' ORDER BY 3 DESC,2 ASC;

8.选择行-查询结果分组

有时候需要对查询数据记录集按列或表达式进行分组，以利于分析数据，此时可以通过使用GROUP BY子句来实现。如果需要在分组的基础上进行组的过滤，则可以结合GROUP BY子句再使用HAVING子句来实现。对于需要对查询数据集进行汇总以生成统计报表的情况，则需要使用COMPUTE子句。

【例4-1】查询所有商品的最高价、最低价、平均价和所有库存量之和。

SELECT MAX(g_Price) 最高价, MIN(g_Price) 最低价,AVG(g_Price) 平均价, SUM(g_Number) 总库存 FROM SCOTT.Goods; 【例4-2】 需要了解每一类别的商品总数。

SELECT t_ID 类别号, COUNT(t_ID) 商品数 FROM SCOTT.Goods GROUP BY t_ID;

执行该PL/SQL查询语句，将先按商品类别编号t_ID进行分组，相同商品类别编号的数据记录位于同一个组内，然后通过使用聚合函数进行统计，计算出每一组内具有的商品编号数目。 【例4-3】查询商品单价超过2000的商品类别编号和库存量。

对于该例，可以考虑分两步执行：第一步，使用WHERE条件过滤掉价格在2000元以下的所有商品记录，被过滤掉的数据记录不再参与后面的操作；第二步，按商品编号对过滤后的数据记录集进行分组，得到的分组数据记录集即为最终所需要的结果。

SELECT t_ID, SUM(g_Number) 库存量 FROM SCOTT.Goods WHERE

g_PRICE >=2000 GROUP BY t_ID;

如果需要使查询数据记录集生成数据统计，以及横向小计统计，则需要在GROUP BY子句中使用ROLLUP关键字。

【例4-4】显示每个商品类别的商品库存量及商品总库存量。 SELECT g_ID, t_ID, SUM(g_Number) 库存量 FROM GROUP BY ROLLUP(t_ID,g_ID); 如（长图）

如果需要对查询数据记录集生成数据统计、横向小计及纵向小计结果，则可以在GROUP BY子句中使用CUBE关键字 。

【例4-5】显示商品总库存量、每个种类商品的商品总量和每一商品的数量。 SELECT g_ID, t_ID, SUM(g_Number) 库存量 FROM SCOTT.Goods

GROUP BY CUBE(g_ID,t_ID); 如（短图）

SCOTT.Goods

在对查询数据记录集进行分组的基础上，再对每组数据记录集进行过滤时，不能使用WHERE子句来进行过滤，而应该使用HAVING子句进行过滤，因为WHERE子句在分组之前执行过滤，而HAVING则在分组之后执行过滤。

【例4-6】需要了解订单总额大于5000的订单信息，并按升序排列。 SELECT o_ID 订单编号, sum(d_Price*d_Number) 总金额 FROM SCOTT.OrderDetails GROUP BY o_ID

HAVING sum(d_Price*d_Number)>5000 ORDER BY sum(d_Price*d_Number)

9.连接查询

实际上，数据库实例中的各个表之间可能存在某些内在关联，通过这些关联，可以为应用程序提供一些涉及多个表的复杂信息，如主表和外表之间就存在主键和外键的关联。PL/SQL为这种多个表之间存在关联的查询提供了检索数据的方法，称为连接查询。

内连接是使用比较运算符作为连接条件的连接方式。内连接作为一种典型的默认连接方式，关键字INNER默认提供。使用内连接方式时，只有那些满足连接条件的数据记录被显示，不满足连接条件的数据记录将不被显示。

根据连接条件中的关系运算符是否使用“=”，内连接可以分为等值连接和非等值连接。若用于连接的两个表或视图来源于同一个表或视图，这样的内连接也被称之为自连接。

1.商品基本信息存放在Goods表中，商品分类信息存放在Types表，所以本查询实际上同时涉及Goods与Types两个表中的数据。这两个表之间的联系是通过两个表都具有的属性t_ID实现的。要查询商品及其类别名称，就必须将这两个表中商品号相同的记录连接起来，这是一个等值连接。 SELECT Goods.g_ID, Goods.t_ID, Types.t_Name, Goods.g_Name FROM SCOTT.Goods JOIN SCOTT.Types ON SCOTT.Goods.t_ID= SCOTT.Types.t_ID;

【例5-3】需要了解不低于“三星SGH-C178”价格的商品号、商品名称和商品单价，查询后的结果要求按商品单价升序排列。 （自连接）

SELECT G2.g_ID 商品号,G2.g_Name 商品名称,G2.g_Price 价格 FROM SCOTT.Goods G1 JOIN SCOTT.Goods G2

ON G1.g_Name='三星SGH-C178' AND G1.g_Price<=G2.g_Price ORDER By G2.g_Price;

10.外连接查询-左外连接

1.查询数据记录集包含来自一个表的所有数据记录和另一个表中的匹配数据记录的连接称为左外连接。对于左外连接，第一个表中的所有数据记录将被显示，第二个表（匹配表）如果找不到相匹配的数据记录，相应的列将显示为空值（NULL），否则显示匹配数据记录

【分析】将Types表和Goods表进行左外连接，Types为左表，Goods表为右表。完成语句如下所示。 SELECT Types.t_ID, t_Name, g_ID, g_Name, g_Price, g_Number

FROM SCOTT.Types LEFT OUTER JOIN SCOTT.Goods on Types.t_ID= Goods.t_ID;

2.查询数据记录集包含来自第二个表的所有数据记录和第一个表中的匹配数据记录的连接称为右外连接。对于右外连接

Types表和Goods表进行右外连接，Goods为左表，Types表为右表。完成语句如下所示。 SELECT Types.t_ID, t_Name, g_ID, g_Name, g_Price, g_Number

FROM SCOTT.Types RIGHT OUTER JOIN SCOTT.Goods on Types.t_ID= Goods.t_ID

3.查询数据记录集的两个连接表中所有行的连接操作称为完全外部连接。对于完全外部连接，两个连接表无论是否匹配，它们的数据记录都将被显示。

【分析】在Types表和Goods表之间的建立完整外部连接。完成语句如下所示。 SELECT Types.t_ID, t_Name, g_ID, g_Name, g_Price, g_Number

FROM SCOTT.Types FULL OUTER JOIN SCOTT.Goods on Types.t_ID= Goods.t_ID;

4. 交叉连接是使用CROSS关键字进行的连接，它的输出为笛卡儿积，即第一个表的每一条数据记录与第二个表的每一条数据记录进行连接。笛卡儿积的结果通常很大，其数据记录数目等于两个表的数据记录数目之积，数据记录的列数等于两个表的列数之和。

[语句一]

SELECT * FROM SCOTT.Types CROSS JOIN SCOTT.Goods [语句二]

SELECT Types.*, Goods.* FROM SCOTT.Types,SCOTT.Goods

11.子查询

将一个查询语句嵌套在另一个查询语句中的查询称为嵌套查询或子查询。被嵌入在其他查询语句中的查询语句称为子查询语句，子查询语句的载体查询语句称为父查询语句。子查询语句一般嵌入在另一个查询语句的WHERE子句或HAVING子句中，另外，子查询语句也可以嵌入在一个数据记录更新语句的WHERE子句中。

1. 对于使用IN的子查询的连接条件， 最常用的一种

其语法格式为： WHERE 表达式 [ NOT ] IN （子查询）

2．【例 6-1】需要了解和“摩托罗拉 W380”为同类商品的商品号、商品名称和类别号。 使用子查询的语句如下。

SELECT g_ID, g_Name ,t_ID FROM SCOTT.Goods

WHERE t_ID IN (SELECT t_ID FROM Goods WHERE g_Name='摩托罗拉 W380');

3.【例 6-2】需要了解购买了“红双喜牌兵乓球拍”的订单号、订单时间和订单总金额。 SELECT o_ID, o_Date, o_Sum FROM SCOTT.Orders WHERE o_ID IN (SELECT o_ID FROM OrderDetails WHERE g_ID IN

(SELECT g_ID FROM Goods WHERE g_Name='红双喜牌兵乓球拍'));

4.【例 6-3】需要了解购买了商品号为“060001”的会员e_ID（会员号）、e_Name（会员名称）和e_Address（籍贯）。

SELECT c_ID, c_Name, c_Address FROM SCOTT.Customers WHERE c_ID IN

(SELECT c_ID FROM Orders JOIN OrderDetails ON Orders.o_ID=OrderDetails.o_ID WHERE g_ID= '060001');

12.比较运算符子查询

子查询也可以使用比较运算符引入。此时，子查询结果为一个单行单列的值，并可以在父查询中通过比较运算符（“>”、“>=”、“<”、“<=”、“=”、“!=”或“<>”）连接子查询，如果子查询返回不止一个值，整个查询语句将会产生错误 。

1. 【例6-4】 需要了解购买了“红双喜牌兵乓球拍”的订单号、订单时间和订单总金额。（使用“=”完成“）

SELECT o_ID, o_Date, o_Sum FROM Orders WHERE o_ID IN (SELECT o_ID FROM OrderDetails WHERE g_ID =

(SELECT g_ID FROM Goods WHERE g_Name='红双喜牌兵乓球拍') );

2.【例6-5】需要了解比籍贯为“湖南长沙”任一会员年龄小的会员信息，查询结果按降序排列。 【分析】比任一会员的年龄小，即比最小的还要小。反过来，如果是大于ALL，则要比最大的还要大，完成语句如下所示。

SELECT c_ID, c_Name,TO_CHAR(SYSDATE)-TO_CHAR(c_Birth) Age, c_Address FROM Customers

WHERE SUBSTR(c_Address,1,4)< >'湖南长沙' AND c_Birth>ALL

(SELECT c_Birth FROM Customers WHERE SUBSTR(c_Address,1,4)='湖南长沙') ORDER BY Age DESC

【例6-6】 针对Employees表中的每一名员工，在Orders表中查找处理过订单并且送货模式为“邮

寄”的所有订单信息。 EXISTS

【分析】第一步要查找处理过订单的员工编号，第二步再根据员工处理订单的送货模式显示订单详细信息。

SELECT * FROM Orders WHERE o_Sendmode = '邮寄'

AND EXISTS (SELECT e_ID FROM Employees AS Emp WHERE Emp.e_ID = Orders.e_ID)

二 插入数据

INSERT INTO语句不但可以将一条数据记录插入到表中，也可以通过使用子查询的形式将一个数据记录集插入到表中，实现批量插入。

【例6-7】 求每一类商品的平均价格，并将结果保存到数据库中。

（1）在数据库中建立一个有两个属性列的新表，其中一列存放类别名，另一列存放相应类别的商品平均价格。

CREATE TABLE AvgGoods(t_ID CHAR(2),a_avg FLOAT) ； 其中t_ID代表商品类别号，a_avg代表平均价格。

（2）对数据库的商品表按商品号分组求平均价格，再把商品号和平均价格存入新表中。 INSERT INTO AvgGoods (t_ID, a_avg)

SELECT t_ID, AVG(g_Price) FROM Goods GROUP BY t_ID; （3）查看表AvgGoods表中的记录。 SELECT * FROM AvgGoods;

三，更新字句

在UPDATE语句中使用子查询，可以通过构造复杂的更新操作条件来实现更新数据记录的操作 。 【例6-8】将商品中类别名称为 “家用电器”的商品折扣修改为0.8。 UPDATE Goods SET g_Discount=0.8

WHERE '家用电器'= (SELECT t_Name FROM Types WHERE Goods.t_ID=Types.t_ID); 再使用查询语句：

SELECT g_ID,g_Name,t_ID,g_Discount FROM GOODS;

四．DELETE子查询

在DELETE FROM语句中使用子查询，可以通过构造复杂删除操作条件来实现删除数据记录的操作 。

【例6-10】 删除类别名称为“家用电器”的商品的基本信息。 DELETE FROM Goods

WHERE '家用电器'= (SELECT t_Name FROM Types WHERE Goods.t_ID=Types.t_ID)

五.联合查询

需要了解“三星”的商品以及价格不高于2000的商品。完成语句如下所示。 SELECT g_ID 商品号,g_Name 商品名称,g_Price 价格 FROM Goods WHERE SUBSTR(g_Name,1,2)='三星' UNION

SELECT g_ID 商品号,g_Name 商品名称,g_Price 价格 FROM Goods WHERE g_Price<2000；

*/

本文来源：https://www.bwwdw.com/article/miqr.html