Oracle数据库

岚苼

已于 2023-07-12 09:40:56 修改

阅读量470

点赞数

文章标签： oracle 数据库

于 2023-07-09 14:19:08 首次发布

本文链接：https://blog.csdn.net/qq_56910237/article/details/131577326

版权

文章目录

1. 表的创建
2. 表的操作
3. 数据完整性和约束条件
4. 修改表结构
5. 分区表
- （1）分区的作用
- （2）分区的实例
6. 视图创建和操作
7. 数据查询
8. 数据操作

1. 表的创建

（1）创建表的语法

表的创建需要CREATE TABLE 系统权限，创建表的基本创建语法如下：

CREATE TABLE 表名
		(列名 数据类型(宽度)[DEFAULT 表达式][COLUMN CONSTRAINT],
		...
		[TABLE CONSTRAINT]
		[TABLE_PARTITION_CLAUSE]
		)；

表名最大长度为30个字符。在同一个用户下，表不能重名，但不同用户表的名称可以相重。另外，表的名称不能使用Oracle的保留字。在一张表中最多可以包含2000列。该语法中的其他部分根据需要添加，作用如下：
DEFAULT 表达式：用来定义列的默认值。
COLUMN CONSTRAINT：用来定义列级的约束条件。
TABLE CONSTRAINT：用来定义表级的约束条件。
TABLE_PARTITION_CLAUSE：定义表的分区子句。

举例1：创建出版社表。

	步骤1：创建出版社表，输入并执行以下命令：

CREATE TABLE 出版社(
		编号 VARCHAR2(2),
		出版社名称 VARCHAR2(30),
		地址 VARCHAR2(30),
		联系电话 VARCHAR2(20)
		);

举例2：创建图书表

CREATE TABLE 图书(
图书编号 VARCHAR2(5),
图书名称 VARCHAR2(30),
出版社编号 VARCHAR2(2),
作者 VARCHAR2(10),
出版日期 DATE,
数量 NUMBER(3),
单价 NUMBER(7,2)
);

（2）使用DESCRIBE（describe）显示图书表的结构

DESCRIBE 图书

	执行结果为：
	名称                                      是否为空? 类型
	 ----------------------------------------------------- --------------------- -------------------------
	图书编号                                           VARCHAR2(5)
	图书名称                                           VARCHAR2(30)
	 出版社编号                                         VARCHAR2(2)
	 作者                                               VARCHAR2(10)
	 出版日期                                           DATE
	 数量                                               NUMBER(3)
	 单价                                               NUMBER(7,2)

（3）通过子查询创建表

如果要创建一个同已有的表结构相同或部分相同的表，可以采用以下的语法：

CREATE TABLE 表名(列名...) AS SQL查询语句;

该语法既可以复制表的结构，也可以复制表的内容，并可以为新表命名新的列名。新的列名在表名后的括号中给出，如果省略将采用原来表的列名。复制的内容由查询语句的WHERE条件决定。

举例

通过子查询创建新的图书表。

步骤1：完全复制图书表到“图书1”，输入并执行以下命令：

CREATE TABLE 图书1 AS SELECT *　FROM 图书;

说明：“图书1”表的内容和结构同“图书”表完全一致，相当于表的复制。

步骤2：创建新的图书表“图书2”，只包含书名和单价，输入并执行以下命令：

CREATE TABLE 图书2(书名,单价) AS SELECT 图书名称,单价 FROM 图书;

图书2表只包含“图书”表的两列 “图书名称”和“单价”，并且对字段重新进行了命名

步骤3：创建新的图书表“图书3”，只包含书名和单价，不复制内容，输入并执行以下命令：

CREATE TABLE 图书3(书名,单价) AS SELECT 图书名称,单价 FROM 图书 WHERE 1=2;

“图书3”表同“图书2”表的结构一样，但表的内容为空。因为WHERE条件始终为假

（4）设置列的默认值 DEFAULT（default）

可以在创建表的同时指定列的默认值，这样在插入数据时，如果不插入相应的列，则该列取默认值，默认值由DEFAULT部分说明。

举例

创建表时，设置表的默认值。

CREATE TABLE 图书4(
		图书编号 VARCHAR2(5) DEFAULT NULL,
		图书名称 VARCHAR2(30) DEFAULT '未知',
		出版社编号 VARCHAR2(2) DEFAULT NULL,
		出版日期 DATE DEFAULT '01-1月-1900',
		作者 VARCHAR2(10) DEFAULT NULL,
		数量 NUMBER(3) DEFAULT 0,
		单价 NUMBER(7,2) DEFAULT NULL,
		借出数量 NUMBER(3) DEFAULT 0
		);

（5）删除已创建的表

删除表的语法如下：

DROP TABLE 表名[CASCADE CONSTRAINTS]；

表的删除者必须是表的创建者或具有DROP ANY TABLE权限。CASCADE CONSTRAINTS表示当要删除的表被其他表参照时，删除参照此表的约束条件。

解析CASCADE CONSTRAINTS（cascade constraints）

CASCADE CONSTRAINTS是在删除数据库中某个表时，当该表的某些列作为外键被其他表所引用时，级联删除参照该表的约束条件。下面是一个简单的例子：
假设有两张表，员工表和薪资表，员工表的主键是员工编号，薪资表中的员工编号是外键，参照员工表的主键。当我们想要删除员工表时，由于薪资表中的员工编号是外键，我们需要先删除薪资表中的数据，否则删除员工表时会出现错误。
使用CASCADE CONSTRAINTS关键字可以实现级联删除。具体操作如下：
sql

-- 创建员工表
CREATE TABLE t_employee (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR2(50) NOT NULL
);

-- 创建薪资表
CREATE TABLE t_salary (
  salary_id INT PRIMARY KEY,
  emp_id INT,
  salary NUMBER,
  FOREIGN KEY (emp_id) REFERENCES t_employee(emp_id)
);

-- 删除员工表时使用CASCADE CONSTRAINTS关键字
DROP TABLE t_employee CASCADE CONSTRAINTS;

在上述例子中，删除员工表时，CASCADE CONSTRAINTS关键字会自动删除参照该表的外键约束，即删除t_salary表中的emp_id外键约束。因此，我们不需要手动先删除薪资表中的数据，也不需要手动删除外键约束，就可以顺利删除员工表。
如果不加CASCADE CONSTRAINTS会怎样
如果在DROP TABLE语句中不使用CASCADE CONSTRAINTS选项，而该表又有其他表的外键关联到该表的主键上，那么将无法删除该表，系统会抛出ORA-02449错误。此时需要先手动删除其他表中关联到该表主键的外键，或者使用CASCADE CONSTRAINTS选项删除其他表中的数据和外键约束，才能成功删除该表。如果不解决外键约束问题，就无法删除该表。

2. 表的操作

（1）表的重命名 RENAME TO

语法如下：

RENAME 旧表名 TO 新表名;

只有表的拥有者，才能修改表名。
【训练1】修改“图书”表为“图书5”表：

RENAME 图书 TO 图书5;

（2）清空表TRUNCATE （truncate）

清空表的语法为：

TRUNCATE TABLE 表名；

清空表可删除表的全部数据并释放占用的存储空间。

（3）添加注释 COMMENT ON TABLE 表名 IS

1. 为表添加注释的语法为：

COMMENT ON TABLE 表名 IS '...';

该语法为表添加注释字符串。如IS后的字符串为空，则清除表注释。

 #为emp表添加注释：“公司雇员列表”。
COMMENT ON TABLE emp IS '公司雇员列表';

2. 为列添加注释的语法为：

COMMENT ON COLUMN 表名.列名 IS '...'

该语法为列添加注释字符串。如IS后的字符串为空，则清除列注释。

         #  为emp表的deptno列添加注释：“部门编号”。
		COMMENT ON COLUMN emp.deptno IS '部门编号';

（4）查看表DESCRIBE（describe）

使用以下语法可查看表的结构：

DESCRIBE 表名；

DESCRIBE可以简写为DESC。
可以通过对数据字典USER_OBJECTS的查询，显示当前模式用户的所有表。

举例

显示当前用户的所有表。

SELECT object_name FROM user_objects WHERE object_type='TABLE';

3. 数据完整性和约束条件

（1）概念：数据完整性约束

表的数据有一定的取值范围和联系，多表之间的数据有时也有一定的参照关系。在创建表和修改表时，可通过定义约束条件来保证数据的完整性和一致性。约束条件是一些规则，在对数据进行插入、删除和修改时要对这些规则进行验证，从而起到约束作用。
完整性包括数据完整性和参照完整性，数据完整性定义表数据的约束条件，参照完整性定义数据之间的约束条件。数据完整性由主键(PRIMARY KEY)、非空(NOT NULL)、惟一(UNIQUE)和检查(CHECK)约束条件定义，参照完整性由外键(FOREIGN KEY)约束条件定义。

（2）表的五种约束

表共有五种约束，它们是主键、非空、惟一、检查和外键。

1. 主键(PRIMARY KEY)

主键是表的主要完整性约束条件，主键惟一地标识表的每一行。
一般情况下表都要定义主键，而且一个表只能定义一个主键。
主键可以包含表的一列或多列，如果包含表的多列，则需要在表级定义。
主键包含了主键每一列的非空约束和主键所有列的惟一约束。
主键一旦成功定义，系统将自动生成一个B树惟一索引，用于快速访问主键列。比如图书表中 * 用“图书编号”列作主键，“图书编号”可以惟一地标识图书表的每一行。

主键约束的语法（表级，列级）如下：

[CONSTRANT 约束名] PRIMARY KEY					--列级

[CONSTRANT 约束名] PRIMARY KEY(列名1,列名2,...)	--表级

2. 非空(NOT NULL)

非空约束指定某列不能为空，它只能在列级定义。在默认情况下，Oracle允许列的内容为空值。比如“图书名称”列要求必须填写，可以为该列设置非空约束条件。
非空约束语法如下：

[CONSTRANT 约束名] NOT NULL					--列级

3．惟一(UNIQUE)

惟一约束条件要求表的一列或多列的组合内容必须惟一，即不相重，可以在列级或表级定义。但如果惟一约束包含表的多列，则必须在表级定义。比如出版社表的“联系电话”不应该重复，可以为其定义惟一约束。
惟一约束的语法如下：

[CONSTRANT 约束名] UNIQUE						--列级

[CONSTRANT 约束名] UNIQUE(列名1,列名2,...)		--表级

4．检查(CHECK)

检查约束条件是用来定义表的一列或多列的一个约束条件，使表的每一列的内容必须满足该条件(列的内容为空除外)。
在CHECK条件中，可以调用SYSDATE、USER等系统函数。
一个列上可以定义多个CHECK约束条件，一个CHECK约束可以包含一列或多列。
如果CHECK约束包含表的多列，则必须在表级定义。
比如图书表的“单价”的值必须大于零，就可以设置成CHECK约束条件。

检查约束的语法如下：

[CONSTRANT 约束名] CHECK(约束条件)  --列级，约束条件中只包含本列

[CONSTRANT 约束名] CHECK(约束条件)  --表级，约束条件中包含多列

CHECK详细举例
列级

-- 创建一个学生表
CREATE TABLE student (
  id INT PRIMARY KEY,
  name VARCHAR2(50),
  age INT,
  gender CHAR(1),
  grade FLOAT,
  CONSTRAINT age_check CHECK (age >= 18 AND age <= 30),
  CONSTRAINT gender_check CHECK (gender IN ('M', 'F')),
  CONSTRAINT grade_check CHECK (grade >= 0 AND grade <= 100)
);

表级

-- 创建一个员工表
CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR2(50) NOT NULL,
  emp_gender CHAR(1) NOT NULL,
  emp_age INT,
  emp_salary FLOAT,
  CONSTRAINT salary_check CHECK (emp_salary >= 0),
  CONSTRAINT age_gender_check CHECK ((emp_gender = 'M' AND emp_age >= 18 AND emp_age <= 65) OR (emp_gender = 'F' AND emp_age >= 18 AND emp_age <= 60))
);

5．外键 FOREIGN KEY（foreing key）

指定表的一列或多列的组合作为外键，外键参照指定的主键或惟一键。外键的值可以为NULL，如果不为NULL，就必须是指定主键或惟一键的值之一。外键通常用来约束两个表之间的数据关系，这两个表含有主键或惟一键的称为主表，定义外键的那张表称为子表。如果外键只包含一列，则可以在列级定义；如果包含多列，则必须在表级定义。
外键的列的个数、列的数据类型和长度，应该和参照的主键或惟一键一致。比如图书表的“出版社编号”列，可以定义成外键，参照出版社表的“编号”列，但“编号”列必须先定义成为主键或惟一键。如果外键定义成功，则出版社表称为主表，图书表称为子表。在表的创建过程中，应该先创建主表，后创建子表。

外键约束的语法如下：
oreign key (要设为外键的列名) references 表2-表名(与哪个表有关联) (表2中该列列名);
(references)

第一种语法，如果子记录存在，则不允许删除主记录：

[CONSTRANT 约束名] FOREIGN KEY(列名1,列名2,...)REFERENCES 表名(列名1,列名2,...)

第二种语法，如果子记录存在，则删除主记录时，级联删除子记录：

[CONSTRANT 约束名] FOREIGN KEY(列名1,列名2,...)REFERENCES 表名(列名1,列名2,...)on delete cascade

第三种语法，如果子记录存在，则删除主记录时，将子记录置成空：

[CONSTRANT 约束名] FOREIGN KEY(列名1,列名2,...)REFERENCES 表名(列名1,列名2,...)on delete set null
# 其中的表名为要参照的表名。

在以上5种约束的语法中，CONSTRANT关键字用来定义约束名，如果省略，则系统自动生成以SYS_开头的惟一约束名。约束名的作用是当发生违反约束条件的操作时，系统会显示违反的约束条件名称，这样用户就可以了解到发生错误的原因。

（3）约束条件的创建

在表的创建语法中可以定义约束条件：

CREATE TABLE 表名(列名 数据类型[DEFAULT 表达式][COLUMN CONSTRAINT],...
	[TABLE CONSTRAINT]
	)；

其中，COLUMN CONSTRAINT用来定义列级约束条件；TABLE CONSTRAINT用来定义表级约束条件。

创建带有约束条件的出版社表(如果已经存在，先删除)：

CREATE TABLE 出版社(
		编号 VARCHAR2(2) CONSTRAINT PK_1 PRIMARY KEY,
		出版社名称 VARCHAR2(30) NOT NULL ,
		地址 VARCHAR2(30) DEFAULT '未知',
		联系电话 VARCHAR2(20)
		);

创建带有约束条件(包括外键)的图书表(如果已经存在，先删除)：

		CREATE TABLE 图书(
		图书编号 VARCHAR2(5) CONSTRAINT PK_2 PRIMARY KEY,
		图书名称 VARCHAR2(30) NOT NULL,
		出版社编号 VARCHAR2(2) CHECK(LENGTH(出版社编号)=2) NOT NULL,
		作者 VARCHAR2(10) DEFAULT '未知',
		出版日期 DATE DEFAULT '01-1月-1900',
		数量 NUMBER(3) DEFAULT 1 CHECK(数量>0),
		单价 NUMBER(7,2),
		CONSTRAINT YS_1 UNIQUE(图书名称,作者),
		CONSTRAINT FK_1 FOREIGN KEY(出版社编号) REFERENCES 出版社表名(编号) ON DELETE CASCADE
		);

说明：

因为两个表同属于一个用户，故约束名不能相重，图书表的主键为“图书编号”列，主键名为PK_2。
其中，约束条件CHECK(LENGTH(出版社编号)=2)表示出版社编号的长度必须是2，约束条件UNIQUE(图书名称,作者)表示“图书名称”和“作者”两列的内容组合必须惟一。
FOREIGN KEY(出版社编号) REFERENCES 出版社(编号) 表示图书表的“出版社编号”列参照出版社的“编号”主键列。出版社表为主表，图书表为子表，出版社表必须先创建。
ON DELETE CASCADE表示当删除出版社表的记录时，图书表中的相关记录同时删除，比如删除清华大学出版社，则图书表中清华大学出版社的图书也会被删除。
如果同时出现DEFAULT和CHECK，则DEFAULT需要出现在CHECK约束条件之前。

测试

INSERT INTO 出版社 VALUES('01','电子科技大学出版社','西安','029-88201467');
		执行结果：
		ERROR 位于第1行:
		ORA-00001: 违反惟一约束条件 (SCOTT.PK_1)

第二个插入语句违反约束条件PK_1，即出版社表的主键约束，原因是主键的值必须是惟一的。

（4）提交插入的数据 COMMIT：

COMMIT;

注意：

在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在Oracle 数据库中，在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成时才能看见。
隐式提交的定义
又名自动提交，即无需显示执行commit语句，session中的操作被自动提交到数据库的过程。

隐式提交的方式

1、正常执行完ddl语句。包括create，alter，drop，truncate，rename。

2、正常执行完dcl语句。包括grant，revoke。

3、正常退出isql*plus，没有明确发出commit或者rollback。

隐式提交的注意事项

1、执行ddl语句时，前面的dml操作也会被提交到数据库中

因为是在一个session里，那执行ddl语句的时候前面的dml语句肯定也会“不可幸免”的被提交到库中。

2、即使ddl语句执行失败，前面的dml操作也会被提交到数据库中

这就有点儿让人奇怪了，ddl都执行失败了，怎么还会提交呢？这就需要探究一下隐式提交的本质了（下文有叙述）。

3、在前面1和2的基础上总结

为了避免隐式提交或者回滚，尽量保证一条或者几条DML操作完成后有显示的提交或者回滚，防止后续执行的DCL或者DDL自动提交前期的DML操作。

隐式提交的本质

1、一条ddl语句执行了两次commit

commit;

ddl statement;

commit;

第一个commit将当前session中未提交的事务隐式提交，以保证ddl语句失败时的回滚位置。

第二个commit将ddl

2、为什么需要隐式提交？

为了保证事务的一致性。我们在执行ddl语句的时候，oracle需要在它的系统表中进行元数据的记录操作（即：除了建表还会进行不少insert操作），如果它不隐式提交就无法保证一致性；从内部运行机制来看ddl语句和dml语句还是有很大区别的，dml会对每个语句的每条记录都做日志记录以便于回滚，而ddl往往没必要搞这么复杂，从功能和易用性上看隐式提交都是最好的选择。

显式提交

用COMMIT命令直接完成的提交为显式提交。其格式为：

SQL>COMMIT；

(2) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其语法为：

SQL>SET AUTOCOMMIT ON；

（5）恢复删除:

ROLLBACK；

回退已完成。

说明：参见训练2，外键约束FK_1带有ON DELETE CASCAD选项，删除清华大学出版社时，对应的图书也自动删除。其他两种情况用户可自行验证。

（6）查看约束条件

数据字典USER_CONSTRAINTS中包含了当前模式用户的约束条件信息。其中，CONSTRAINTS_TYPE 显示的约束类型为：
C：CHECK约束。
P：PRIMARY KEY约束。
U：UNIQUE约束。
R：FOREIGN KEY约束。
其他信息可根据需要进行查询显示，可用DESCRIBE命令查看USER_CONSTRAINTS的结构。

举例：检查表的约束信息：

SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION FROM USER_CONSTRAINTS
WHERE TABLE_NAME='图书';

执行结果：
CONSTRAINT_NAME C SEARCH_CONDITION
SYS_ C003111 C “图书名称” IS NOT NULL
SYS_C003112 C “出版社编号” IS NOT NULL
SYS_C003113 C LENGTH(出版社编号)=2
SYS_C003114 C 数量>0
PK_2 P
YS_1 U
FK_1 R

说明：图书表共有７个约束条件，一个PRIMARY KEY(P)约束PK_2，一个FOREIGN KEY(R)约束FK_1，一个UNIQUE®约束YS_1和4个CHECK©约束SYS_C003111、SYS_C003112、SYS_C003113和SYS_C003114，4个CHECK约束的名字是由系统命名的。

（7）使约束生效和失效

约束的作用是保护数据完整性，但有的时候约束的条件可能不再适用或没有必要，如果这个约束条件依然发生作用就会影响操作的效率，比如导出和导入数据时要暂时关闭约束条件，这时可以使用下面的命令关闭或打开约束条件。

使约束条件失效：DISABLE CONSTRANT

ALTER TABLE 表名 DISABLE CONSTRANT 约束名;

使约束条件生效：ENABLE CONSTRANT

ALTER TABLE 表名 ENABLE CONSTRANT 约束名;

1. 举例：
使图书表的数量检查失效。
步骤1：使约束条件SYS_C003114(数量>0)失效：

ALTER TABLE 图书 DISABLE CONSTRAINT SYS_C003114;

执行结果：
表已更改。
步骤2：修改数量为0：

UPDATE 图书 SET 数量=0 WHERE 图书编号='A0001';

执行结果：
已更新 1 行。

步骤3：使约束条件SYS_C003114生效：

ALTER TABLE 图书 ENABLE CONSTRAINT SYS_C003114;

执行结果：
ERROR 位于第 1 行:
ORA-02293: 无法验证 (SCOTT.SYS_C003114) - 违反检查约束条件
继续执行：

UPDATE 图书 SET 数量=5 WHERE 图书编号='A0001';

执行结果：
已更新 1 行。

继续执行：

ALTER TABLE 图书 ENABLE CONSTRAINT SYS_C003114;

执行结果：
表已更改。

说明：在步骤1中，先使名称为SYS_C003114 (数量>0)的检查条件暂时失效，所以步骤2修改第1条记录的数量为0才能成功。步骤3使该约束条件重新生效，但因为表中有数据不满足该约束条件，所以发生错误，通过修改第一条记录的数量为5，使约束条件重新生效。

4. 修改表结构

（1）增加新列

增加新列的语法如下：

ALTER TABLE 表名
ADD 列名 数据类型[DEFAULT 表达式][COLUMN CONSTRAINT];

如果要为表同时增加多列，可以按以下格式进行：

ALTER TABLE 表名
ADD (列名 数据类型[DEFAULT 表达式][COLUMN CONSTRAINT]...);

通过增加新列可以指定新列的数据类型、宽度、默认值和约束条件。增加的新列总是位于表的最后。假如新列定义了默认值，则新列的所有行自动填充默认值。对于有数据的表，新增加列的值为NULL，所以有数据的表，新增加列不能指定为NOT NULL约束条件。

举例：

#为“出版社”增加一列“电子邮件”：
		ALTER TABLE 出版社
		ADD 电子邮件 VARCHAR2(30) CHECK(电子邮件 LIKE '%@%');

说明：为出版社新增加了一列“电子邮件”，数据类型为VARCHAR2，宽度为30。CHECK(电子邮件 LIKE '%@%')表示电子邮件中必须包含字符“@”。可用DESCRIBE命令查看表的新结构。

（2）修改列

修改列的语法如下：

ALTER TABLE 表名
MODIFY 列名 数据类型 [DEFAULT 表达式][COLUMN CONSTRAINT]

如果要对表同时修改多列，可以按以下格式进行：

ALTER TABLE 表名
MODIFY (列名 数据类型[DEFAULT 表达式][COLUMN CONSTRAINT]...);

其中，列名是要修改的列的标识，不能修改。如果要改变列名，只能先删除该列，然后重新增加。其他部分都可以进行修改，如果没有给出新的定义，表示该部分属性不变。
修改列定义还有以下一些特点：
(1) 列的宽度可以增加或减小，在表的列没有数据或数据为NULL时才能减小宽度。
(2) 在表的列没有数据或数据为NULL时才能改变数据类型，CHAR和VARCHAR2之间可以随意转换。
(3) 只有当列的值非空时，才能增加约束条件NOT NULL。
(4) 修改列的默认值，只影响以后插入的数据。

举例： 修改“出版社”表“电子邮件”列的宽度为40。

ALTER TABLE 出版社
	MODIFY 电子邮件 VARCHAR2(40);

（3）删除列

删除列的语法如下：

ALTER TABLE 表名
DROP COLUMN 列名[CASCADE CONSTRAINTS]；

如果要同时删除多列，可以按以下格式进行：

ALTER TABLE 表名
DROP(COLUMN 列名 数据类型[DEFAULT 表达式][COLUMN CONSTRAINT]...)
[CASCADE CONSTRAINTS];

当删除列时，列上的索引和约束条件同时被删除。但如果列是多列约束的一部分，则必须指定CASCADE CONSTRAINTS才能删除约束条件。

举例：
删除“出版社”表的“电子邮件”列。

ALTER TABLE 出版社
DROP COLUMN 电子邮件;

（4）UNUSED状态（不会在表中显示出该列）

使用以下语法，可以将列置成UNUSED状态，这样就不会在表中显示出该列：

ALTER TABLE 表名 SET UNUSED COLUMN 列名 [CASCADE CONSTRAINTS];

以后可以重新使用或删除该列。通过数据字典可以查看标志成UNUSED的列。
删除标志成UNUSED的列：

ALTER TABLE 表名 DROP UNUSED COLUMNS;

举例：
将“图书”表的“出版日期”列置成UNUSED，并查看。

步骤1：设置“出版日期”列为UNUSED：

ALTER TABLE 图书 SET UNUSED COLUMN 出版日期;

步骤2：删除UNUSED列：

ALTER TABLE 图书 DROP UNUSED COLUMNS;

（5）约束条件的修改

可以为表增加或删除表级约束条件。

1．增加约束条件

增加约束条件的语法如下：

ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 表级约束条件;

【训练1】为emp表的mgr列增加外键约束：

ALTER TABLE emp ADD CONSTRAINT FK_3 FOREIGN KEY(mgr) REFERENCES emp(empno);

说明：本训练增加的外键为参照自身的外键，含义是mgr(经理编号)列的内容必须是empno(雇员编号)之一。

2．删除约束条件

删除约束条件的语法如下：

ALTER TABLE 表名
 DROP PRIMARY_KEY|UNIQUE(列名)|CONSTRAINT 约束名[CASCADE];

【训练2】删除为emp表的mgr列增加的外键约束：

 ALTER TABLE emp DROP CONSTRAINT FK_3;

5. 分区表

（1）分区的作用

在某些场合会使用非常大的表，比如人口信息统计表。如果一个表很大，就会降低查询的速度，并增加管理的难度。一旦发生磁盘损坏，可能整个表的数据就会丢失，恢复比较困难。根据这一情况，可以创建分区表，把一个大表分成几个区(小段)，对数据的操作和管理都可以针对分区进行，这样就可以提高数据库的运行效率。分区可以存在于不同的表空间上，提高了数据的可用性。

分区的依据可以是一列或多列的值，这一列或多列称为分区关键字或分区列。
所有分区的逻辑属性是一样的(列名、数据类型、约束条件等)，但每个分区可以有自己的物理属性(表空间、存储参数等)。
分区有三种：范围分区、哈斯分区和混合分区。
范围分区(RANGE PARTITIONING)：根据分区关键字值的范围建立分区。比如，根据省份为人口数据表建立分区。
哈斯分区(HASH PARTITIONING)：在分区列上使用HASH算法进行分区。
混合分区(COMPOSITE PARTITIONING)：混合以上两种方法，使用范围分区建立主分区，使用HASH算法建立子分区。

（2）分区的实例

由于分区用到了很多存储参数，故不在这里进行详细讨论，只给出一个范围分区的简单训练实例。
【训练1】创建和使用分区表。
步骤1：创建按成绩分区的考生表，共分为3个区：

CREATE TABLE 考生 (
考号 VARCHAR2(5),
姓名 VARCHAR2(30),
成绩 NUMBER(3)
)

PARTITION BY RANGE(成绩)
		(PARTITION A VALUES LESS THAN (300)
		TABLESPACE USERS,
		PARTITION B VALUES LESS THAN (500)
		TABLESPACE USERS,
		PARTITION C VALUES LESS THAN (MAXVALUE)
		TABLESPACE USERS
		);

INSERT INTO 考生 VALUES('10001','王明',280);
		INSERT INTO 考生 VALUES('10002','李亮',730);
		INSERT INTO 考生 VALUES('10003','赵成',550);
		INSERT INTO 考生 VALUES('10004','黄凯',490);
		INSERT INTO 考生 VALUES('10005','马新',360);
		INSERT INTO 考生 VALUES('10006','杨丽',670);

步骤3：检查A区中的考生：
SELECT * FROM 考生 PARTITION(A);
执行结果：
考号姓名成绩
-------- --------------------- ---------------------------------
10001 王明 280
步骤4：检查全部的考生：
SELECT * FROM 考生;
执行结果：
考号姓名成绩
-------- ------------------------ ------------------------------
10001 王明 280
10004 黄凯 490
10005 马新 360
10002 李亮 730
10003 赵成 550
10006 杨丽 670

说明：共创建A、B、C三个区，A区的分数范围为300分以下，B区的分数范围为300至500分，C区的分数范围为500分以上。共插入6名考生，插入时根据考生分数将自动插入不同的区。

6. 视图创建和操作

（1）视图的概念

视图是基于一张表或多张表或另外一个视图的逻辑表。视图不同于表，视图本身不包含任何数据。表是实际独立存在的实体，是用于存储数据的基本结构。而视图只是一种定义，对应一个查询语句。视图的数据都来自于某些表，这些表被称为基表。通过视图来查看表，就像是从不同的角度来观察一个(或多个)表。
视图有如下一些优点：

可以提高数据访问的安全性，通过视图往往只可以访问数据库中表的特定部分，限制了用户访问表的全部行和列。
简化了对数据的查询，隐藏了查询的复杂性。视图的数据来自一个复杂的查询，用户对视图的检索却很简单。
一个视图可以检索多张表的数据，因此用户通过访问一个视图，可完成对多个表的访问。
视图是相同数据的不同表示，通过为不同的用户创建同一个表的不同视图，使用户可分别访问同一个表的不同部分。
视图可以在表能够使用的任何地方使用，但在对视图的操作上同表相比有些限制，特别是插入和修改操作。对视图的操作将传递到基表，所以在表上定义的约束条件和触发器在视图上将同样起作用。

（2）视图的创建

创建视图需要CREAE VIEW系统权限，视图的创建语法如下：

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图名[(别名1[，别名2...])]
AS 子查询 
[WITH CHECK OPTION [CONSTRAINT 约束名]]
[WITH READ ONLY]

其中：
OR REPLACE 表示替代已经存在的视图。
FORCE表示不管基表是否存在，创建视图。
NOFORCE表示只有基表存在时，才创建视图，是默认值。

别名是为子查询中选中的列新定义的名字，替代查询表中原有的列名。
子查询是一个用于定义视图的SELECT查询语句，可以包含连接、分组及子查询。
WITH CHECK OPTION表示进行视图插入或修改时必须满足子查询的约束条件。后面的约束名是该约束条件的名字。
WITH READ ONLY 表示视图是只读的。

（3）删除视图

DROP VIEW 视图名;

删除视图者需要是视图的建立者或者拥有DROP ANY VIEW权限。视图的删除不影响基表，不会丢失数据。

创建简单视图
【训练1】创建图书作者视图。

步骤1：创建图书作者视图：

CREATE VIEW 图书作者(书名,作者) 
AS SELECT 图书名称,作者 FROM 图书;

（3）创建复杂视图

【训练3】修改作者视图，加入出版社名称。

步骤1：重建图书作者视图：

CREATE OR REPLACE VIEW 图书作者(书名,作者,出版社) 
AS SELECT 图书名称,作者,出版社名称 FROM 图书,出版社 
WHERE 图书.出版社编号=出版社.编号;

【训练4】创建一个统计视图。
步骤1：创建emp表的一个统计视图：

CREATE VIEW 统计表(部门名,最大工资,最小工资,平均工资)
AS SELECT DNAME,MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO GROUP BY DNAME;

（4）创建只读视图

创建只读视图要用WITH READ ONLY选项。

【训练5】创建只读视图。

步骤1：创建emp表的经理视图：

CREATE OR REPLACE VIEW manager 
AS SELECT * FROM emp WHERE job= 'MANAGER'
WITH READ ONLY;

步骤2：进行删除：

DELETE FROM manager;

执行结果：
ERROR 位于第 1 行:
ORA-01752: 不能从没有一个键值保存表的视图中删除
4．创建基表不存在的视图
正常情况下，不能创建错误的视图，特别是当基表还不存在时。但使用FORCE选项就可以在创建基表前先创建视图。创建的视图是无效视图，当访问无效视图时，Oracle将重新编译无效的视图。

【训练6】使用FORCE选项创建带有错误的视图：

CREATE FORCE VIEW 班干部 AS SELECT * FROM　班级 WHERE 职务 IS NOT NULL;

执行结果：
警告: 创建的视图带有编译错误。

（5）视图的操作

对视图经常进行的操作是查询操作，但也可以在一定条件下对视图进行插入、删除和修改操作。对视图的这些操作最终传递到基表。但是对视图的操作有很多限定。如果视图设置了只读，则对视图只能进行查询，不能进行修改操作。

视图的插入

【训练1】视图插入练习。

步骤1：创建清华大学出版社的图书视图：

CREATE OR REPLACE VIEW 清华图书 
AS SELECT * FROM 图书 WHERE 出版社编号= '01';

步骤2：插入新图书：

1．视图的插入

INSERT INTO 清华图书 VALUES('A0005','软件工程','01','冯娟',5,27.3);

执行结果：
图书图书名称出作者数量单价
-------- ---------------------------------------- ----------- -------- ------------------------ --------------
A0001 计算机原理 01 刘勇 5 25.3
A0005 软件工程 01 冯娟 5 27.3

说明：通过查看视图，可见新图书插入到了视图中。通过查看基表，看到该图书也出现在基表中，说明成功地进行了插入。新图书的出版社编号为“01”，仍然属于“清华大学出版社”。
但是有一个问题，就是如果在“清华图书”的视图中插入其他出版社的图书，结果会怎么样呢？结果是允许插入，但是在视图中看不见，在基表中可以看见，这显然是不合理的。

【训练2】使用WITH CHECK OPTION选项限制视图的插入。

2．使用WITH CHECK OPTION选项

	为了避免上述情况的发生，可以使用WITH CHECK OPTION选项。使用该选项，可以对视图的插入或更新进行限制，即该数据必须满足视图定义中的子查询中的WHERE条件，否则不允许插入或更新。比如“清华图书”视图的WHERE条件是出版社编号要等于“01”(01是清华大学出版社的编号)，所以如果设置了WITH CHECK OPTION选项，那么只有出版社编号为“01”的图书才能通过清华视图进行插入。

步骤1：重建清华大学出版社的图书视图，带WITH CHECK OPTION选项：

CREATE OR REPLACE VIEW 清华图书 
AS SELECT * FROM 图书 WHERE 出版社编号= '01'
WITH CHECK OPTION;

步骤2：插入新图书：

INSERT INTO 清华图书 VALUES('A0006','Oracle数据库','02','黄河',3,39.8);

执行结果：
ERROR 位于第 1 行:
ORA-01402: 视图 WITH CHECK OPTIDN 违反 where 子句

说明：可见通过设置了WITH CHECK OPTION选项，“02”出版社的图书插入受到了限制。如果修改已有图书的出版社编号情况会如何？答案是将同样受到限制。要是删除视图中已有图书，结果又将怎样呢？答案是可以，因为删除并不违反WHERE条件。

3．来自基表的限制

除了以上的限制，基表本身的限制和约束也必须要考虑。如果生成子查询的语句是一个分组查询，或查询中出现计算列，这时显然不能对表进行插入。另外，主键和NOT NULL列如果没有出现在视图的子查询中，也不能对视图进行插入。在视图中插入的数据，也必须满足基表的约束条件。

【训练3】基表本身限制视图的插入。

步骤1：重建图书价格视图：

CREATE OR REPLACE VIEW 图书价格 
AS SELECT 图书名称,单价 FROM 图书;

【训练3】基表本身限制视图的插入。

步骤1：重建图书价格视图：

CREATE OR REPLACE VIEW 图书价格 
		AS SELECT 图书名称,单价 FROM 图书;

步骤2：插入新图书：

INSERT INTO 图书价格 VALUES('Oracle数据库',39.8);

执行结果：
ERROR 位于第 1 行:
ORA-01400: 无法将 NULL 插入 (“SCOTT”.“图书”.“图书编号”)

说明：在视图中没有出现的基表的列，在对视图插入时，自动默认为NULL。该视图只有两列可以插入，其他列将默认为空。插入出错的原因是，在视图中不能插入图书编号，而图书编号是图书表的主键，是必须插入的列，不能为空，这就产生了矛盾。

4. 视图的查看

USER_VIEWS字典中包含了视图的定义。
USER_UPDATABLE_COLUMNS字典包含了哪些列可以更新、插入、删除。
USER_OBJECTS字典中包含了用户的对象。
可以通过DESCRIBE命令查看字典的其他列信息。在这里给出一个训练例子。

【训练1】查看清华图书视图的定义：

SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME='清华图书';

执行结果：TEXT
SELECT 图书名称,作者,单价 FROM 图书 WHERE 出版社编号=‘01’

【训练2】查看用户拥有的视图：

SELECT object_name FROM user_objects WHERE object_type='VIEW';

执行结果：
OBJECT_NAME
----------------------------------------------------------------------------------------------

【训练1】创建学生、系部、课程和成绩表，根据需要设置默认值、约束条件、主键和外键。
步骤1：创建系部表，编号为主键，系部名称非空，电话号码惟一：

CREATE TABLE 系部(
		编号 NUMBER(5) PRIMARY KEY,
		系部名 VARCHAR2(20) NOT NULL,
		地址 VARCHAR2(30),
		电话 VARCHAR2(15) UNIQUE,
		系主任 VARCHAR2(10)
		);

步骤2：创建学生表，学号为主键，姓名非空，性别只能是男或女，电子邮件包含@并且惟一，系部编号参照系部表的编号：

CREATE TABLE 学生 (
		学号 VARCHAR2(10) PRIMARY KEY,
		姓名 VARCHAR2(10) NOT NULL,
		性别 VARCHAR2(2) CHECK(性别='男' OR 性别='女'),
		生日 DATE,
		住址 VARCHAR2(30),
		电子邮件 VARCHAR2(20) CHECK(电子邮件 LIKE '%@%') UNIQUE,
		系部编号 NUMBER(5),
		CONSTRAINT FK_XBBH FOREIGN KEY(系部编号) REFERENCES 系部(编号)
		);

步骤3：创建课程表，编号为主键，课程名非空，学分为1到5：

CREATE TABLE 课程(
		编号 NUMBER(5) PRIMARY KEY,
		课程名 VARCHAR2(30) NOT NULL,
		学分 NUMBER(1) CHECK(学分>0 AND 学分<=5)
		);

步骤4：创建成绩表，学号和课程编号为主键，学号参照学生表的学号，课程编号参照课程表的编号：

CREATE TABLE 成绩(
		学号 VARCHAR2(10),
		课程编号 NUMBER(5),
		成绩 NUMBER (3),
		CONSTRAINT PK PRIMARY KEY(学号,课程编号),
CONSTRAINT FK_XH FOREIGN KEY(学号) REFERENCES 学生(学号),
		CONSTRAINT FK_KCBH FOREIGN KEY(课程编号) REFERENCES 课程(编号)
		);

说明：注意表之间的主从关系，对于系部和学生表，系部表为主表，学生表为子表。学生表的外键表示插入学生的系部编号必须是系部表的编号。对于成绩表，主键是学号和课程编号，表示如果学号相同课程编号必须不同，这样就可以惟一地标识记录。课程表有两个外键，分别参照学生表和课程表，表示成绩表的学号必须是学生表的学号，成绩表的课程编号必须是课程表的编号。

7. 数据查询

1 数据库查询语言SQL

SQL语言的特点和分类

（1）SQL语言有以下的主要特点：

SQL语言可以在Oracle数据库中创建、存储、更新、检索和维护数据，其中主要的功能是实现数据的查询和数据的插入、删除、修改等操作。
SQL语言在书写上类似于英文，简洁清晰，易于理解。它由关键字、表名、字段名，表达式等部分构成。
SQL语言属于非过程化的4GL(第四代语言)。
SQL语言按功能可分为DDL语言、DML语言、DCL语言和数据库事务处理语言四个类别。
SQL语言的主要关键字有：ALTER、DROP、REVOKE、AUDIT、GRANT、ROLLBACK、COMMIT、INSERT、SELECT、COMMENT、LOCK、UPDATE、CREATE、NOAUDIT、VALIDATE、DELETE、RENAME等。

（2）SQL语言的分类

在这里插入图片描述

2 基本查询和排序

（1）查询的基本用法

在Oracle数据库中，对象是属于模式的，每个账户对应一个模式，模式的名称就是账户名称。在表名前面要添加模式的名字，在表的模式名和表名之间用“.”分隔。我们以不同的账户登录数据库时，就进入了不同的模式。
比如登录到STUDENT 账户，就进入了STUDENT模式。而在STUDENT模式要查询属于SCOTT模式的表，就需要写成：SELECT * FROM SCOTT.EMP;
但如果登录用户访问属于用户模式本身的表，那么可以省略表名前面的模式名称。SELECT * FROM emp;

1．指定检索字段

下面的练习，只显示表的指定字段。
【训练1】显示DEPT表的指定字段的查询。
输入并执行查询：

SELECT deptno,dname FROM dept;

2．显示行号

每个表都有一个虚列ROWNUM，它用来显示结果中记录的行号。我们在查询中也可以显示这个列。
【训练2】显示EMP表的行号。
输入并执行查询：

SELECT rownum,ename FROM emp;

3．显示计算列

在查询语句中可以有算术表达式，它将形成一个新列，用于显示计算的结果，通常称为计算列。表达式中可以包含列名、算术运算符和括号。括号用来改变运算的优先次序。常用的算术运算符包括：
+：加法运算符。
-：减法运算符。
*：乘法运算符。
/：除法运算符。

以下训练在查询中使用了计算列。
【训练3】显示雇员工资上浮20%的结果。
输入并执行查询:

SELECT ename,sal,sal*(1+20/100) FROM emp;

4．使用别名

我们可以为表的列起一个别名，它的好处是，可以改变表头的显示。特别是对于计算列，可以为它起一个简单的列别名以代替计算表达式在表头的显示。

【训练4】在查询中使用列别名。

输入并执行：

SELECT ename AS 名称, sal 工资 FROM emp;

说明：表头显示的是列别名，转换为汉字显示。在列名和别名之间要用AS分隔，如ename和它的别名“名称”之间用AS隔开。AS也可以省略，如sal和它的别名“工资”之间用空格分割。
注意：如果用空格分割，要区别好列名和别名，前面为列名，后面是别名。别名如果含有空格或特殊字符或大小写敏感，需要使用双引号将它引起来。

【训练5】在列别名上使用双引号。
输入并执行查询：

SELECT ename AS "Name", sal*12+5000 AS "年度工资(加年终奖)" FROM emp;

说明：其中别名“Name”有大小写的区别，别名“年度工资(加年终奖) ”中出现括号，属于特殊符号，所以都需要使用双引号将别名引起。

5．连接运算符（||）

在前面，我们使用到了包含数值运算的计算列，显示结果也是数值型的。我们也可以使用字符型的计算列，方法是在查询中使用连接运算。连接运算符是双竖线“||”。通过连接运算可以将两个字符串连接在一起。

【训练6】在查询中使用连接运算。
输入并执行查询：

SELECT	ename||job AS "雇员和职务表" FROM emp;

输出结果为：
雇员和职务表
SMITHCLERK
ALLENSALESMAN

【训练7】在查询中使用字符串常量。
输入并执行查询：

SELECT	ename|| ' IS '||job AS "雇员和职务表" FROM emp;

输出结果为：
雇员和职务表
SMITH IS CLERK
ALLEN IS SALESMAN
说明：本练习中将雇员名称、字符串常量“ IS ”和雇员职务3个部分连接在一起。

6．消除重复行 DISTINCT

如果在显示结果中存在重复行，可以使用的关键字DISTINCT消除重复显示。

【训练8】使用DISTINCT消除重复行显示。
输入并执行查询：

SELECT DISTINCT job FROM emp;

说明：在本例中，如果不使用DISTINCT关键字，将重复显示雇员职务，DISTINCT关键字要紧跟在SELECT之后。请去掉DISTINCT关键字，重新执行，并观察显示结果的不同。

（2）查询结果的排序

如果要在查询的同时排序显示结果，可以使用如下的语句：

SELECT 字段列表 FROM 表名 WHERE 条件
ORDER BY 字段名1 [ASC|DESC][,字段名2 [ASC|DESC]...];

ORDER BY从句后跟要排序的列。ORDER BY 从句出现在SELECT语句的最后。
排序有升序和降序之分，ASC表示升序排序，DESC表示降序排序。
如果不指明排序顺序，默认的排序顺序为升序。如果要降序，必须书写DESC关键字。

1．升序排序

【训练1】查询雇员姓名和工资，并按工资从小到大排序。
输入并执行查询：

SELECT ename, sal FROM emp ORDER BY sal;

注意：若省略ASC和DESC，则默认为ASC，即升序排序。

2．降序排序

【训练2】查询雇员姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示。
输入并执行查询：

SELECT ename,hiredate FROM emp ORDER BY hiredate DESC;

注意： DESC表示降序排序，不能省略。

3．多列排序

可以按多列进行排序，先按第一列，然后按第二列、第三列…。
【训练3】查询雇员信息，先按部门从小到大排序，再按雇佣时间的先后排序。
输入并执行查询：

SELECT ename,deptno,hiredate FROM emp ORDER BY deptno,hiredate;

说明：该排序是先按部门升序排序，部门相同的情况下，再按雇佣时间升序排序。

4．在排序中使用别名

如果要对计算列排序，可以为计算列指定别名，然后按别名排序。
【训练4】按工资和工作月份的乘积排序。
输入并执行查询：

SELECT empno, ename, sal*Months_between(sysdate,hiredate) AS total 
FROM emp 
ORDER BY total;

说明：求得雇员工作月份的函数Months_between将在后面介绍。sysdate表示当前日期。

3 条件查询

（1）简单条件查询

要对显示的行进行限定，可在FROM从句后使用WHERE从句，在WHERE从句中给出限定的条件，因为限定条件是一个表达式，所以称为条件表达式。条件表达式中可以包含比较运算，表达式的值为真的记录将被显示。常用的比较运算符列于表2-2中。

1. 比较运算符

在这里插入图片描述
【训练1】显示职务为“SALESMAN”的雇员的姓名、职务和工资。
输入并执行查询：

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

注意：在本练习中，如果SALESMAN写成小写或大小写混合，将不会有查询结果输出。

【训练2】显示工资大于等于3000的雇员姓名、职务和工资。
输入并执行查询：

SELECT ename, job,sal FROM emp WHERE sal>=3000;

说明：结果只显示工资大于等于3000的雇员。缺省中文日期格式为DD-MM月-YY，如2003年1月10日应该表示为“10-1月-03”。

【训练3】显示1982年以后雇佣的雇员姓名和雇佣时间。
输入并执行查询：

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

说明：检查hiredate字段的内容，都在82年以后。

2.group by, having, order by语句的执行顺序

select CategoryName, count(*), AVG(Rating)
from BOOKSHELF
where Rating>1
group by CategoryName
having CategoryName like 'A%'
order by count(*) desc

我们现在知道，其执行顺序如下：
1.基于Where Rating>1 筛选出符合条件的行；
2.基于group by CategoryName 对筛选的结果进行分组；
3.为每个CategoryName组计算Count(*)
4. 基于having CategoryName like 'A%'留下符合条件的组
5. 根据order by 的条件对剩下的行组进行排序，SQL中的count(*)也是分组函数

（2）复合条件查询

可以用逻辑运算符构成复合的条件查询，即把两个或多个条件，用逻辑运算符连接成一个条件。有3个逻辑运算符，如表2-3所示。
在这里插入图片描述
运算的优先顺序是NOT，AND，OR。如果要改变优先顺序，可以使用括号。
下面是使用逻辑与运算的练习。

1．使用逻辑与

【训练1】显示工资在1000～2000之间(不包括1000和2000)的雇员信息。
输入并执行查询：

SELECT ename, job,sal FROM emp WHERE sal>1000 AND sal<2000;

说明：两个条件需要同时满足，所以必须使用AND运算。
注意：条件sal>1000 AND sal<2000不能写成sal>1000 AND <2000。

2．使用逻辑或

下面是使用逻辑或运算的练习。
【训练2】显示职务为CLERK或MANAGER的雇员信息。
输入并执行查询：

SELECT * FROM emp WHERE job='CLERK' OR job='MANAGER';

说明：检索职务为’CLERK’或’MANAGER’的雇员，需要使用OR运算，请自行察看结果。
注意：条件job=‘CLERK’ OR job='MANAGER’不能写成job=‘CLERK’ OR ‘MANAGER’。

3．使用逻辑非

下面是使用逻辑非运算的练习。
【训练3】显示部门10以外的其他部门的雇员。
输入并执行查询：

SELECT * FROM emp WHERE NOT deptno=10;

说明：执行结果包含部门编号不等于10的其他部门的雇员，请自行察看结果。

4．使用逻辑或和逻辑与

下面是同时使用逻辑或和逻辑与的复合练习。
【训练4】显示部门10和部门20中工资小于1500的雇员。
输入并执行查询

SELECT * FROM emp WHERE (deptno=10 OR deptno=20) AND sal<1500;

注意：该练习中的括号是不可省的。如果省略，意义有所不同。

（3）条件特殊表示法

使用如表2-4所示的特殊运算表示法，可使语句更为直观，易于理解。
在这里插入图片描述

1．BETWEEN的用法

对于数值型或日期型数据，表示范围时可以用以下的特殊运算表示方法：[NOT]BETWEEN...``AND...
【训练1】显示工资在1000～2000之间的雇员信息。
输入并执行查询：

SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000;

注意：下限在前，上限在后，不能颠倒。查询范围中包含上下限的值，因此在本例中，查询工资包含1000和2000在内。请自行执行并察看结果。

2．IN的用法

使用以下运算形式，可以显示值满足特定集合的结果：[NOT] IN (...)
【训练2】显示职务为“SALESMAN’，“CLERK”和“MANAGER”的雇员信息。
输入并执行查询：

SELECT * FROM emp WHERE job IN ('SALESMAN','CLERK','MANAGER');

注意：如果在IN前面增加NOT，将显示职务不在集合列表中的雇员。以上用法同样适用于数值型集合，请自行执行并察看结果。

3．LIKE的用法

使用LIKE操作符可完成按通配符查找字符串的查询操作，该操作符适合于对数据进行模糊查询。其语句法为：[NOT] LIKE 匹配模式
匹配模式中除了可以包含固定的字符之外，还可以包含以下的通配符：
%：代表0个或多个任意字符。
_ ：代表一个任意字符。
【训练3】显示姓名以“S”开头的雇员信息。
输入并执行查询：

SELECT * FROM emp WHERE ename LIKE 'S%';

说明：SMITH和SCOTT名字均以S开头，名字后边的字符和长度任意。
【训练4】显示姓名第二个字符为“A”的雇员信息。
执行查询：

SELECT * FROM emp WHERE ename LIKE '_A%';

说明：“_”代表第一个字符任意，第二个字符必须为“A”，“%”代表第二个字符后面的字符为任意字符，个数任意。

4．判断空值NULL

在表中，字段值可以是空，表示该字段没有内容。如果不填写，或设置为空则我们说该字段的内容为NULL。NULL没有数据类型，也没有具体的值，但是使用特定运算可以判断出来。这个运算就是：IS [NOT] NULL
【训练5】显示经理编号没有填写的雇员。
输入并执行查询：

SELECT  ename, mgr FROM emp WHERE mgr IS NULL;

注意：以下用法是错误的。SELECT ename, mgr FROM emp WHERE mgr=NULL;

4 函数

在这里插入图片描述

（1）数值型函数

【训练1】使用数值型函数练习。

步骤1：使用求绝对值函数abs。

SELECT abs(-5) FROM dual;

执行结果：

 ABS(-5)
   5

说明：求-5的绝对值，结果为5。

步骤2：使用求平方根函数sqrt。

SELECT sqrt(2) FROM dual;

执行结果：

SQRT(2)
1.41421356

说明：2的平方根为1.41421356。

步骤3：使用ceil函数。

SELECT ceil(2.35) FROM dual;

执行结果：

CEIL(2.35)
3

说明：该函数求得大于等于2.35的最小整数，结果为3。

步骤4：使用floor函数。

SELECT floor(2.35) FROM dual;

执行结果：

FLOOR(2.35)
2

说明：该函数求得小于等于2.35的最大整数，结果为2。

步骤5：使用四舍五入函数round。

SELECT round(45.923,2), round(45.923,0), round(45.923,-1) FROM dual;

执行结果：

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923, -1)
45.92              46                50

说明：该函数按照第二个参数指定的位置对第一个数进行四舍五入。2代表对小数点后第三位进行四舍五入，0 代表对小数位进行四舍五入，-1代表对个位进行四舍五入。

步骤6：使用截断函数trunc。

SELECT trunc(45.923,2), trunc(45.923),trunc(45.923, -1) FROM dual;

执行结果：

TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923, -1)
 45.92            45                40

说明：该函数按照第二个参数指定的位置对第一个数进行截断。2代表对小数点后第三位进行截断，0 代表对小数位进行截断，-1代表对个位进行截断。

步骤7：使用求余数函数mod。

SELECT mod(1600, 300) FROM dual;

执行结果：

MOD(1600,300)
100

说明：1600除以300的商为5，余数为100。

步骤8：使用cos函数。

SELECT cos(3.14159) FROM dual;

执行结果：

COS(3.14159)
-1

说明：cos函数的输入参数应为弧度，3.14159的cos值为-1。
函数可以嵌套使用，看如下例子。

【训练2】求|sin(230o)|的值，保留一位小数。

步骤1：执行查询。

SELECT sin(230*3.14159/180) FROM dual;

结果为：

SIN(230*3.14159/180)
-.76604226

说明：先将230o转换成为弧度，然后进行计算求值。

步骤2：求绝对值。

SELECT abs(sin(230*3.14159/180)) FROM dual;

结果为：

ABS(SIN(230*3.14159/180))
.766042264

说明：本步骤求绝对值。
步骤3：保留一位小数。

SELECT round(abs(sin(230*3.14159/180)),1) FROM dual;

结果为：

ROUND(ABS(SIN(230*3.14159/180)),1)
.8

说明：本步骤进行四舍五入，保留小数点后1位。

（2）字符型函数

字符型函数包括大小写转换和字符串操作函数。大小写转换函数有3个。常用的字符型函数如表2-6所示。
在这里插入图片描述

举例

【训练1】如果不知道表的字段内容是大写还是小写，可以转换后比较。
输入并执行查询：

SELECT  empno, ename, deptno	FROM emp
WHERE  lower(ename) ='blake';

结果为：

 EMPNO ENAME          DEPTNO
    --------------- ---------- ------------------------
 7698 BLAKE              30

说明：该查询将表中的雇员名转换成小写，与小写的blake进行比较。
【训练2】显示雇员名称和职务列表。
输入并执行查询：

SELECT   concat(rpad(ename,15,'.'),job) as 职务列表 FROM  emp;

结果为：

职务列表
    ---------------------------------------
SMITH...........CLERK
ALLEN..........SALESMAN
WARD...........SALESMAN

说明：rpad函数向字符串的右侧添加字符，以达到指定宽度。该例中雇员名称右侧连接若干个“.”，凑足15位，然后与雇员职务连接成列表。本例中使用了函数的嵌套。

【训练3】显示名称以“W”开头的雇员，并将名称转换成以大写开头。
输入并执行查询：

SELECT empno,initcap(ename),job FROM emp
WHERE substr(ename,1,1)='W';

结果为：

 EMPNO INITCAP(EN JOB
	--------------- ---------------- ------------------
 7521 	  Ward      SALESMAN

说明：本例在字段列表和查询条件中分别应用了函数initcap和substr。函数initcap将雇员名称转换成以大写开头。函数substr返回ename从第一个字符位置开始，长度为1的字符串，即第一个字符，然后同大写W比较。

【训练4】显示雇员名称中包含“S”的雇员名称及名称长度。
输入并执行查询：

SELECT empno,ename,length(ename) FROM emp
WHERE instr(ename, 'S', 1, 1)>0;

执行结果为：

EMPNO ENAME    LENGTH(ENAME)
	-------------- -------------- ---------------------------
 7369 SMITH         5
7566 JONES                	   5

【训练4】显示雇员名称中包含“S”的雇员名称及名称长度。
输入并执行查询：

SELECT empno,ename,length(ename) FROM emp
WHERE instr(ename, 'S', 1, 1)>0;

执行结果为：

EMPNO 	ENAME    	LENGTH(ENAME)
    -------------- 	-------------- 	---------------------------
7369 	 	SMITH                	  	 5
7566 		JONES                	 	  5

说明：本例在字段列表和查询条件中分别应用了函数length和instr。Length函数返回ename的长度。instr(ename,'S’1,1)函数返回ename中从第一个字符位置开始，字符串“S”第一次出现的位置。如果函数返回0，则说明ename中不包含字符串“S”；如果函数返回值大于0，则说明ename中包含字符串“S”。

（3）日期型函数

Oracle使用内部数字格式来保存时间和日期，包括世纪、年、月、日、小时、分、秒。缺省日期格式为 DD-MON-YY，如“08-05月-03”代表2003年5月8日。
SYSDATE是返回系统日期和时间的虚列函数。
使用日期的加减运算，可以实现如下功能：

对日期的值加减一个天数，得到新的日期。
对两个日期相减，得到相隔天数。
通过加小时来增加天数，24小时为一天，如12小时可以写成12/24(或0.5)。
还有如表2-7所示的日期函数可以使用。

【训练1】返回系统的当前日期。
输入并执行查询：

SELECT sysdate FROM dual;

返回结果为：

SYSDATE 
   ---------------
06-2月-03

说明：该查询返回执行该查询时的数据库服务器的系统当前时间，日期显示格式为默认格式，如“06-2月-03”表示03年2月6日。

【训练2】返回2003年2月的最后一天。
输入并执行查询：

SELECT last_day('08-2月-03') FROM dual;

返回结果为：

LAST_DAY('
    ---------------
28-2月-03

说明：该函数给定参数为某月份的任意一天，返回时间为该月份的最后一天。本例中，参数为03年2月8号，返回日期为03年2月28日，是该月的最后一天。

【训练3】假定当前的系统日期是2003年2月6日，求再过1000天的日期。
输入并执行查询：

SELECT sysdate+1000 AS "NEW DATE" FROM dual;

返回结果为：

NEW DATE
    ---------------
04-11月-05

说明：该查询使用到了日期的加法运算，求经过一定天数后的新日期。

【训练4】假定当前的系统日期是2003年2月6日，显示部门10雇员的雇佣天数。
输入并执行查询：

SELECT ename, round(sysdate-hiredate) DAYS
FROM   emp
WHERE  deptno = 10;

返回结果为：

ENAME            DAYS
    --------------- ---------------------
CLARK           	 7913
KING             	 7752
MILLER          	 7685

说明：该查询使用日期的减法运算求两个日期的相差天数。用round函数对天数进行四舍五入。

（4）转换函数

Oracle的类型转换分为自动类型转换和强制类型转换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBER，如表2-8所示。
在这里插入图片描述

1．自动类型转换

Oracle可以自动根据具体情况进行如下的转换：

字符串到数值。
字符串到日期。
数值到字符串。
日期到字符串。
以下是自动转换的训练。

【训练1】自动转换字符型数据到数值型。
输入并执行查询：

SELECT '12.5'+11 FROM dual;

执行结果为：

'12.5'+11
------------
23.5

说明：在本训练中，因为出现+运算符，说明进行的是算术运算，所以字符串’12.5’被自动转换成数值12.5，然后参加运算。
【训练2】自动转换数值型数据到字符型。
执行以下查询：

SELECT '12.5'||11 FROM dual;

结果为：

'12.5'
------
12.511

说明：在本训练中，因为出现||运算符，说明进行的是字符串连接运算，数值11被自动转换成字符串’11’，然后参加运算。

2．日期类型转换

将日期型转换成字符串时，可以按新的格式显示。如格式YYYY-MM-DD HH24:MI:SS表示“年-月-日小时:分钟:秒”。Oracle的日期类型是包含时间在内的。主要的日期格式字符的含义如表2-9所示。
在这里插入图片描述

【训练3】将日期转换成带时间和星期的字符串并显示。
执行以下查询：

SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual;

结果为：

TO_CHAR(SYSDATE,'YYYY-MM-DD HH24
		----------------------------------------------------------
2004-02-07 15:44:48 下午 星期六

在这里插入图片描述

说明：该语句中的第一个参数表示要转换的日期，第二个参数是格式字符串，表示转换后的格式，结果类型为字符串。“YYYY”为4位的年份，“MM”为两位的月份，“DD”为两位的日期，“HH24”表示显示24小时制的小时,“MI”表示显示分钟，“SS”表示显示秒，“AM”表示显示上午或下午(本例中为下午)，“DY”表示显示星期。“-”、“:”和空格原样显示，用于分割日期和时间。转换出来的系统时间为：2004年2月7日(星期六)下午15点44分48秒。
还可以按其他的格式显示。以下查询中插入中文的年月日，其中原样显示部分区别于外层的单引号，需要用双引号引起。
【训练4】将日期显示转换成中文的年月日。
输入并执行查询：

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;

执行结果为：

TO_CHAR(SYSDAT
		-------------------------
2003年11月18日

说明：双引号中的中文字“年”、“月”、“日”原样显示，单引号为字符串的界定标记，区别于双引号，不能混淆。
【训练5】将雇佣日期转换成字符串并按新格式显示。
输入并执行查询：

SELECT	ename, to_char(hiredate, 'DD Month YYYY') HIREDATE
FROM  	emp;

执行结果为：

ENAME      HIREDATE
		------------ -----------------------
SMITH      17 12月 1980
ALLEN      20 2月  1981

说明：Month表示月份的特殊格式，如“12月”。年度用4位显示。
对于数字型的日期格式，可以用数字或全拼格式显示，即在格式字符后面添加TH或SP。TH代表序列，SP代表全拼。
【训练6】以全拼和序列显示时间。
执行以下查询：

SELECT SYSDATE,to_char(SYSDATE,'yyyysp'),to_char(SYSDATE,'mmspth'),
to_char(SYSDATE,'ddth') FROM dual;

执行结果为：

SYSDATE   TO_CHAR(SYSDATE,'YYYYSP')           		TO_CHAR( TO_C
------------ -------------------------------------------------------------- --------------- --------
07-2月 -04  two thousand four          second     07th

在这里插入图片描述

说明：“two thousand four”为全拼表示的2004年；“second”为全拼序列表示的2月；“07th”为用序列表示的7号。
在格式字符中，前两个字符代表显示结果的大小写。如果格式中的前两个字符是大写，则输出结果的全拼也为大写。如果格式中的前两个字符是小写，则输出结果的全拼也为小写。如果格式中的前两个字符的第一个字符是大写，第二个字符是小写，则输出结果的全拼也为大写开头，后面为字符小写。
【训练7】时间显示的大小写。
步骤1：执行以下查询：

SELECT SYSDATE,to_char(SYSDATE,'yyyysp') FROM dual;

在这里插入图片描述

结果为：

SYSDATE   TO_CHAR(SYSDATE,'YYYYSP')
    ------------- ----------------------------------------------
07-2月 -04  two thousand four

步骤2：执行以下查询：

SELECT to_char(SYSDATE,'Yyyysp') FROM dual;

结果为：

SYSDATE    TO_CHAR(SYSDATE,'YYYYSP')
		-------------- -----------------------------------------------
Two Thousand Four

在这里插入图片描述

步骤3：执行以下查询：

SELECT SYSDATE,to_char(SYSDATE,'YYyysp') FROM dual;

结果为：

SYSDATE    TO_CHAR(SYSDATE,'YYYYSP')
		-------------- -----------------------------------------------
TWO THOUSAND FOUR

说明：步骤1输出全拼小写的年度，步骤2输出全拼的以大写开头的年度，步骤3输出全拼大写的年度。

（5）数字类型转换

将数字型转换成字符串时，也可以按新的格式显示。格式字符含义如表2-10所示。
在这里插入图片描述
【训练8】将数值转换成字符串并按新格式显示。
执行以下查询：

SELECT TO_CHAR(123.45,'0000.00'), TO_CHAR(12345,'L9.9EEEE') FROM dual;

结果为：

TO_CHAR( TO_CHAR(12345,'L9.9
------------ --------------------------------
0123.45          RMB1.2E+04

说明：格式字符串中“0”表示一位数字，转换结果中相应的位置上没有数字则添加0。“.”表示在相应的位置上显示小数点。“L”将以本地货币符号显示于数字前，在本例中本地货币符号为“RMB”。“EEEE”将显示转换为科学计数法。
【训练9】将数值转换成字符串并按新格式显示。
执行以下查询：

SELECT TO_CHAR(sal,'$99,999') SALARY FROM emp
WHERE ename = 'SCOTT';

结果为：

SALARY
------------
$4,000

说明：格式字符串中“$”表示转换结果前面添加$。“9”表示一位数字，“99，999”表示结果可以显示为5位的数字。“,”表示在相应的位置上添加逗号。如果实际数值位数不足5位，则只显示实际位数，如4000实际位数为4位，则只显示4位。如果实际位数超过5位，则会填充为#号。

（6）其他函数

Oracle还有一些函数，如decode和nvl，这些函数也很有用，归纳如表2-11所示。
在这里插入图片描述

1．空值的转换

如果对空值NULL不能很好的处理，就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是NULL。最典型的例子是，在查询雇员表时，将工资sal字段和津贴字段comm进行相加，如果津贴为空，则相加结果也为空，这样容易引起误解。
使用nvl函数，可以转换NULL为实际值。该函数判断字段的内容，如果不为空，返回原值；为空，则返回给定的值。
如下3个函数，分别用新内容代替字段的空值：
nvl(comm, 0)：用0代替空的Comm值。
nvl(hiredate, '01-1月-97')：用1997年1月1日代替空的雇佣日期。
nvl(job, '无')：用“无”代替空的职务。
【训练1】使用nvl函数转换空值。
执行以下查询：

SELECT	ename,nvl(job,'无'),nvl(hiredate,'01-1月-97'),nvl(comm,0) FROM	 emp;

结果为：

ENAME      NVL(JOB,'N NVL(HIREDA NVL(COMM,0)
---------------- ------------------- ------------------ -------------------
SMITH      CLERK        17-12月-80            0
ALLEN      SALESMAN    20-2月 -81          300

说明：本例中，空日期将显示为“01-1月-97”，空职务显示为“无”，空津贴将显示为0。

2．decode函数

decode函数可以通过比较进行内容的转换，完成的功能相当于分支语句。该函数的第一个参数为要进行转换的表达式，以后的参数成对出现，最后一个参数可以单独出现。如果第一个参数的值与第二个表达式的值相等，则返回第三个表达式的值；如果不等则继续比较，如果它的值与第四个表达式的值相等，则返回第五个表达式的值，以此类推。在参数的最后位置上可以存在单独的参数，如果以上比较过程没有找到匹配值，则返回该参数的值，如果不存在该参数，则返回NULL。
【训练2】将职务转换成中文显示。
执行以下查询：

SELECT	ename,decode(job, 'MANAGER', '经理', 
'CLERK','职员', 'SALESMAN','推销员', 'ANALYST','系统分析员','未知') FROM emp;

结果为：

ENAME      DECODE(JOB
-------------- ------------------------
SMITH     	职员
ALLEN      	推销员
WARD      	推销员
JONES      	经理
MARTIN     	推销员
BLAKE      	经理
CLARK      	经理
SCOTT      	系统分析员
KING       	未知
TURNER     	推销员
ADAMS     	职员
JAMES      	职员
FORD       	系统分析员
MILLER     	职员

已选择14行。
说明：在以上训练中，如果job字段的内容为“MANAGER”则返回“经理”，如果是“CLERK”则返回“职员”，以此类推。如果不是“MANAGER”、“CLERK”、“SALESMAN”和“ANALYST”之一，则返回“未知”，如KING的职务“PRESIDENT”不在上述范围，返回“未知”。

3．userenv函数

函数userenv返回用户环境信息字符串，该函数只有一个字符串类型的参数，参数的内容为如下之一的字符串，可以不区分大小写：

ISDBA：判断会话用户的角色是否为SYSDBA，是则返回TRUE。
INSTANCE：返回会话连接的INSTANCE标识符。
LANGUAGE：返回语言、地区、数据库字符集信息。
LANG：返回会话语言的ISO简称。
TERMINAL：返回正在会话的终端或计算机的标识符。

【训练3】返回用户终端或系统标识信息。
执行以下查询：

SELECT	userenv('TERMINAL') FROM dual;

结果为：

ORASERVER

说明：根据用户使用的机器不同返回的信息不同，在本例中机器标识符ORASERVER为主机的名称。
训练4】返回语言、地区、数据库字符集信息。
执行以下查询：

SELECT	userenv('LANGUAGE') FROM dual;

结果为：

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

说明：显示当前用户的语言为简体中文(SIMPLIFIED CHINESE)，地区为中国(CHINA)，字符集为ZHS16GBK。
【训练5】比较字符串的大小，返回最大值。
执行以下查询：

SELECT	greatest('ABC','ABD','abc', 'abd') FROM dual;

执行结果为：

GRE
------
abd

说明：在上述四个字符串中，大小关系为abd>abc>ABD>ABC。在ASCII码表中，排在后边的字符大，小写字母排在大写字母之后。字符串的比较原则是，先比较第一位，如果相同，则继续比较第二位，依此类推，直到出现大小关系。

5 高级查询

（1）多表联合查询

通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般是在WHERE子句中用比较运算符指明连接的条件。
忘记说明表的连接条件是常见的一种错误，这时查询将会产生表连接的笛卡尔积(即一个表中的每条记录与另一个表中的每条记录作连接产生的结果)。一般N个表进行连接，需要至少N-1个连接条件，才能够正确连接。两个表连接是最常见的情况，只需要说明一个连接条件。
两个以上的表也可以进行连接，在这里不做专门介绍。

两个表的连接有四种连接方式：

相等连接。
不等连接。
外连接。
自连接。

1．相等连接

通过两个表具有相同意义的列，可以建立相等连接条件。使用相等连接进行两个表的查询时，只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。
【训练1】显示雇员的名称和所在的部门的编号和名称。
执行以下查询：

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

执行结果如下：

ENAME         	  DEPTNO 	         DNAME
		------------- 	--------------	 ----------------------
		SMITH          	    20 		  RESEARCH
		ALLEN               30 	 	  SALES

说明：相等连接语句的格式要求是，在FROM从句中依次列出两个表的名称，在表的每个列前需要添加表名，用“.”分隔，表示列属于不同的表。在WHERE条件中要指明进行相等连接的列。以上训练中，不在两个表中同时出现的列，前面的表名前缀可以省略。所以以上例子可以简化为如下的表示：

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

【练习2】执行以下查询(省略表连接条件)并察看执行结果中共有多少记录产生。

SELECT ename,emp.deptno,dname FROM emp,dept

如果表名很长，可以为表起一个别名，进行简化，别名跟在表名之后，用空格分隔。
【训练2】使用表别名。
执行以下查询：

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

执行结果同上。
说明：emp表的别名为e，dept表的别名为d。
相等连接还可以附加其他的限定条件。

【训练3】显示工资大于3000的雇员的名称、工资和所在的部门名称。
执行以下查询：

SELECT ename,sal,dname FROM emp,dept 
WHERE emp.deptno=dept.deptno AND sal>3000;

显示结果为：

ENAME          SAL 	DNAME
-------------- ----------------- -------------------
KING             5000 	ACCOUNTING

说明：只显示工资大于3000的雇员的名称、工资和部门名称。在相等连接的条件下增加了工资大于3000的条件。增加的条件用AND连接。

2．外连接

在以上的例子中，相等连接有一个问题：如果某个雇员的部门还没有填写，即保留为空，那么该雇员在查询中就不会出现；或者某个部门还没有雇员，该部门在查询中也不会出现。
为了解决这个问题可以用外连接，即除了显示满足相等连接条件的记录外，还显示那些不满足连接条件的行，不满足连接条件的行将显示在最后。外连操作符为(+)，它可以出现在相等连接条件的左侧或右侧。出现在左侧或右侧的含义不同，这里用如下的例子予以说明。

【训练4】使用外连显示不满足相等条件的记录。
步骤1：显示雇员的名称、工资和所在的部门名称及没有任何雇员的部门。

Ⅰ 右外连接

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(emp)只会显示符合搜索条件的记录，而右表(dept )的记录将会全部表示出来。左表记录不足的地方均为NULL。

执行以下查询：

SELECT ename,sal,dname FROM emp,dept 
WHERE emp.deptno(+)=dept.deptno;

或者

SELECT ename,sal,dname FROM emp 
right outer join dept on emp.deptno = dept.deptno;

执行结果为：

ENAME            SAL          DNAME
	------------------- -------------- ------------------------
   CLARK           	2450 	 ACCOUNTING
	KING            5000 	 ACCOUNTING
	MILLER          1300	 ACCOUNTING
	SMITH           800		 RESEARCH
	ADAMS           1100     RESEARCH
	WARD            1250 	 SALES
                			 OPERATIONS

步骤2：显示雇员的名称、工资和所在的部门名称及没有属于任何部门的雇员。

Ⅱ 左外连接

说明：
left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
左(外)连接，左表(emp )的记录将会全部表示出来，而右表(dept)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
执行以下查询：

SELECT ename,sal,dname FROM emp,dept 
		WHERE emp.deptno=dept.deptno(+);

或者

SELECT ename,sal,dname FROM emp
 left outer join dept on emp.deptno = dept.deptno;

结果
在这里插入图片描述

说明：部门OPERATION没有任何雇员。查询结果通过外连显示出该部门。

3．不等连接

还可以进行不等的连接。以下是一个训练实例，其中用到的salgrade表的结构如下：

DESC salgrade

名称                              是否为空  类型
------------------------------------------- ------------------ ------------------
GRADE                              	  NUMBER
LOSAL                             	  NUMBER
HISAL                              	  NUMBER

Grade 表示工资等级，losal和hisal分别表示某等级工资的下限和上限。
表的内容为：

SELECT * FROM salgrade;

GRADE      LOSAL    HISAL
------------------- ------------------- -------------
1        700       	1200
2       1201       	1400
3       1401       	2000
4       2001       	3000
5       3001       	9999

【训练5】显示雇员名称，工资和所属工资等级。
执行以下查询：

SELECT e.ename, e.sal, s.grade FROM emp e,salgrade s
WHERE 	e.sal BETWEEN s.losal AND s.hisal；

执行结果为：

ENAME             SAL      GRADE
----------------- -------------------- -------------------
SMITH            	 800          1
ADAMS           	1100          1
JAMES            	 950          1
WARD            	1250          2
MARTIN          	1250          2
MILLER          	1300          2
ALLEN           	1600          3
TURNER          	1500          3
JONES            	2975          4
BLAKE            	2850          4
CLARK            	2450          4
SCOTT            	3000          4
FORD             	             3000          4
KING                   	 5000          5

说明：通过将雇员工资与不同的工资上下限范围相比较，取得工资的等级，并在查询结果中显示出雇员的工资等级。

4．自连接

最后是一个自连接的训练实例，自连接就是一个表，同本身进行连接。对于自连接可以想像存在两个相同的表(表和表的副本)，可以通过不同的别名区别两个相同的表。
【训练6】显示雇员名称和雇员的经理名称。
执行以下查询：
SELECT worker.ename||’ 的经理是 '||manager.ename AS 雇员经理
FROM emp worker, emp manager
WHERE worker.mgr = manager.empno;
执行结果为：

雇员		经理
-------------------------------------------
SMITH 的经理是 FORD
ALLEN 的经理是 BLAKE
WARD 的经理是 BLAKE

说明：为EMP表分别起了两个别名worker和manager，可以想像，第一个表是雇员表，第二个表是经理表，因为经理也是雇员。然后通过worker表的mgr(经理编号)字段同manager表的empno(雇员编号)字段建立连接，这样就可以显示雇员的经理名称了。
注意：经理编号mgr是雇员编号empno之一，所以经理编号可以同雇员编号建立连接。

（2）统计查询

通常需要对数据进行统计，汇总出数据库的统计信息。比如，我们可能想了解公司的总人数和总工资额，或各个部门的人数和工资额，这个功能可以由统计查询完成。
Oracle提供了一些函数来完成统计工作，这些函数称为组函数，组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于SELECT子句、HAVING子句或ORDER BY子句中。组函数也可以称为统计函数。
查询公司的总人数需要对整个表应用组函数；查询各个部门的人数，需要对数据进行分组，然后应用组函数进行运算。
分组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。
使用GROUP BY 从句可以对数据进行分组。所谓分组，就是按照列的相同内容，将记录划分成组，对组可以应用组函数。
如果不使用分组，将对整个表或满足条件的记录应用组函数。
在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT 表示对每一个非NULL值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。

1．统计查询

【训练1】求雇员总人数。
执行以下查询：

SELECT COUNT(*) FROM emp;

返回结果为：

COUNT(*)
------------------
14

说明：该实例中，因为没有WHERE条件，所以对emp表的全部记录应用组函数。使用组函数COUNT统计记录个数，即雇员人数，返回结果为14，代表有14个记录。
注意：*代表返回所有行数，否则返回非NULL行数。
【训练2】求有佣金的雇员人数。
执行以下查询：

SELECT COUNT(comm) FROM emp;

返回结果为：

COUNT(COMM)
---------------------
4

说明：在本例中，没有返回全部雇员，只返回佣金非空的雇员，只有4个人。
【训练3】求部门10的雇员的平均工资。
执行以下查询：

SELECT AVG(sal) FROM emp WHERE deptno=10;

返回结果为：

AVG(SAL)
-----------------
2916.66667

说明：增加了WHERE条件，WHERE条件先执行，结果只对部门10的雇员使用组函数AVG求平均工资。
最大值和最小值函数可以应用于日期型数据，以下是训练实例。
【训练4】求最晚和最早雇佣的雇员的雇佣日期。
执行以下查询：

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

返回结果为：

MAX(HIREDA MIN(HIREDA
------------------- -------------------
23-5月 	    -87 17-12月-80

说明：最晚雇员雇佣的时间为87年5月23日，最早雇员雇佣的时间为80年12月17日。
【训练5】求雇员表中不同职务的个数。
执行以下查询：

SELECT COUNT( DISTINCT job) FROM emp;

返回结果为：

COUNT(DISTINCT JOB)
-------------------------------
5

说明：该查询返回雇员表中不同职务的个数。如果不加DISTINCT，则返回的是职务非空的雇员个数。
【练习1】求部门10中工资大于1500的雇员人数。

2．分组统计

通过下面的训练，我们来了解分组的用法。
【训练6】按职务统计工资总和。
步骤1：执行以下查询：

SELECT SUM(sal) FROM emp GROUP BY job;

执行结果为：

  SUM(SAL)
-----------------
      6000
      4150
      8275
      5000
      5600

步骤2：执行以下查询：

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

执行结果为：

JOB         SUM(SAL)
----------------- -------------------
ANALYST       	6000
CLERK           4150
MANAGER        	8275
PRESIDENT       5000
SALESMAN       	5600

说明：步骤1按职务对雇员进行分组，有多少种职务就会返回多少行结果，相同职务的工资被汇总到一起，其中使用到了SUM函数对分组后的工资进行求和。以上查询结果没有显示分组后的职务。
分组查询允许在查询列表中包含分组列，对以上实例，因为是按职务job分组的，所以在查询列中可以包含job字段，使统计结果很清楚，如步骤2所示。
职务为ANALYST的雇员的总工资为6000，职务为CLERK的雇员的总工资为4150，依此类推。
注意：在查询列中，不能使用分组列以外的其他列，否则会产生错误信息。
【练习2】查看以下查询的显示结果，并解释原因。
SELECT ename,job,SUM(sal) FROM emp GROUP BY job;

3．多列分组统计

可以按多列进行分组，以下是按两列进行分组的例子。
【训练7】按部门和职务分组统计工资总和。
执行以下查询：

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

执行结果为：

   DEPTNO	 JOB         SUM(SAL)
------------------ --------- -----------------------
10  		CLERK         		130
		 10		 MANAGER      		245
		10		 PRESIDENT     		5000
        20 		ANALYST      		6000
        20 		CLERK         		1900
        20 		MANAGER       		2975
        30 		CLERK          		 950
        30		 MANAGER       		2850
        30 		SALESMAN      		5600

说明：该查询统计每个部门中每种职务的总工资。

4．分组统计结果限定

对分组查询的结果进行过滤，要使用HAVING从句。HAVING从句过滤分组后的结果，它只能出现在GROUP BY从句之后，而WHERE从句要出现在GROUP BY从句之前。
【训练8】统计各部门的最高工资，排除最高工资小于3000的部门。
执行以下查询：

SELECT   deptno, max(sal) FROM emp
GROUP BY deptno
HAVING   max(sal)>=3000;

执行结果为：

DEPTNO   MAX(SAL)
------------------ ------------------
10       5000
20       3000

说明：结果中排除了部门30，因部门30的总工资小于3000。
注意：HAVING从句的限定条件中要出现组函数。如果同时使用WHERE条件，则WHERE条件在分组之前执行，HAVING条件在分组后执行。
【练习3】统计人数小于4的部门的平均工资。

5．分组统计结果排序

可以使用ORDER BY从句对统计的结果进行排序，ORDER BY从句要出现在语句的最后。

【训练9】按职务统计工资总和并排序。
执行以下查询：

SELECT job 职务, SUM(sal) 工资总和 FROM emp
GROUP BY job
ORDER BY SUM(sal);

执行结果为：

职务           工资总和
---------------- ------------------
CLERK          	4150
PRESIDENT       5000
SALESMAN       	5600
ANALYST        	6000
MANAGER        	8275

注意：排序使用的是计算列SUM(sal)，也可以使用别名，写成：

SELECT job 职务, SUM(sal) 工资总和 FROM emp
GROUP BY job
ORDER BY 工资总和;

【练习4】统计各部门的人数，按平均工资排序。
6．组函数的嵌套使用
在如下训练中，使用了组函数的嵌套。

【训练10】求各部门平均工资的最高值。
执行以下查询：

SELECT max(avg(sal)) FROM emp GROUP BY deptno;

执行结果为：

MAX(AVG(SAL))
-----------------------
2916.66667

说明：该查询先统计各部门的平均工资，然后求得其中的最大值。
注意：虽然在查询中有分组列，但在查询字段中不能出现分组列。如下的查询是错误的：

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

因为各部门平均工资的最高值不应该属于某个部门。
【练习5】求每种职务总工资的最低值。

（3）子查询

我们可能会提出这样的问题，在雇员中谁的工资最高，或者谁的工资比SCOTT高。通过把一个查询的结果作为另一个查询的一部分，可以实现这样的查询功能。
具体的讲：要查询工资高于SCOTT的雇员的名字和工资，必须通过两个步骤来完成，第一步查询雇员SCOTT的工资，第二步查询工资高于SCOTT的雇员。第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中，这就是子查询。出现在其他查询中的查询称为子查询，包含其他查询的查询称为主查询。
子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询可以在SELECT、INSERT、UPDATE、DELETE等语句中使用。
子查询按照返回数据的类型可以分为单行子查询、多行子查询和多列子查询。

1．单行子查询

【训练1】查询比SCOTT工资高的雇员名字和工资。
执行以下查询：

SELECT ename,sal FROM emp
WHERE sal>(SELECT sal FROM emp WHERE empno=7788);

执行结果为：

ENAME             SAL
-------------- --------------------
KING              5000

说明：在该子查询中查询SCOTT的工资时使用的是他的雇员号，这是因为雇员号在表中是惟一的，而雇员的姓名有可能相重。SCOTT的雇员号为7788。
下面的训练实例包含两个子查询。
【训练2】查询和SCOTT同一部门且比他工资低的雇员名字和工资。
执行以下查询：

SELECT ename,sal FROM emp
WHERE sal<(SELECT sal FROM emp WHERE empno=7788)
AND deptno=(SELECT deptno FROM emp WHERE empno=7788);

执行结果为：

ENAME             SAL
------------- -----------------------
SMITH           	 800
JONES            	2975
ADAMS           	1100

说明：两个子查询出现在两个条件中，用AND连接表示需要同时满足。在子查询中也可以使用组函数。
【训练3】查询工资高于平均工资的雇员名字和工资。
执行以下查询：

SELECT ename,sal FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp);

执行结果为：

ENAME             SAL
------------- ----------------------
JONES           	2975
BLAKE           	2850
CLARK           	2450
SCOTT           	3000
KING             	5000
FORD            	3000

说明：在子查询中出现了组函数。由执行结果可知，在14个雇员中，大于平均工资的有6个。
【练习1】查询工资最高的雇员名字和工资。

2．多行子查询

如果子查询返回多行的结果，则我们称它为多行子查询。多行子查询要使用不同的比较运算符号，它们是IN、ANY和ALL。

【训练4】查询工资低于任何一个“CLERK”的工资的雇员信息。
执行以下查询：

SELECT  empno, ename, job,sal FROM emp
WHERE   sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK')
AND job <> 'CLERK';

执行结果为：

EMPNO ENAME      JOB              SAL
------------------- ------------ ----------------- ---------------------
7521 WARD       SALESMAN         1250
7654 MARTIN     SALESMAN         1250

说明：在emp表的雇员中有4个职务为“CLERK”，他们的工资分别是800、1100、950、1300。满足工资小于任何一个“CLERK”的工资的记录有2个，在这里使用了ANY运算符表示小于子查询中的任何一个工资。
注意：条件job <> 'CLERK'排除了职务是CLERK的雇员本身。

【训练5】查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
执行以下查询：

SELECT  empno, ename,sal FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE job= 'SALESMAN');

执行结果为：

EMPNO 	ENAME                 SAL
----------------     -------------     -----------------------
7566 		JONES           	   2975
7698		 BLAKE          	   2850
7782 		CLARK          	   2450
7788 		SCOTT          	   3000
7839 		KING           	   5000
7902		FORD           	   3000

说明：在emp表的雇员中有4个职务为“SALESMAN”，他们的工资分别是1600、1250、1250、1500。在这里使用了ALL运算符，表示大于查询中所有的工资。
【训练6】查询部门20中职务同部门10的雇员一样的雇员信息。
执行以下查询：

SELECT  empno, ename, job FROM emp
WHERE   job IN (SELECT job FROM emp WHERE deptno=10)
AND deptno =20;

执行结果为：

EMPNO 		ENAME              JOB
------------------   --------------    ----------------------
7369 		SMITH     	CLERK
7876 		ADAMS      	CLERK
7566 		JONES      	MANAGER

说明：在该训练中，使用IN运算符表示职务是子查询结果中的任何一个。部门10中有3种职务：MANAGER、PRESIDENT和CLERK，以上查询得到的是部门20中是这3种职务的雇员。
【训练7】查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息。
执行以下查询：

SELECT  empno, ename, job FROM emp
WHERE job =(SELECT job FROM emp WHERE empno=7788)
AND hiredate < (SELECT hiredate FROM emp WHERE empno=7788);

执行结果为：

EMPNO 	     ENAME      JOB
------------------ ------------- ----------------
7902 	     FORD       ANALYST

说明：在查询中用到了时间的比较。
【练习2】查询工资比SCOTT高或者雇佣时间比SCOTT早的雇员的编号和名字。

3．多列子查询

如果子查询返回多列，则对应的比较条件中也应该出现多列，这种查询称为多列子查询。以下是多列子查询的训练实例。
【训练8】查询职务和部门与SCOTT相同的雇员的信息。
执行以下查询：

SELECT  empno, ename, sal FROM emp
WHERE (job,deptno) =(SELECT job,deptno FROM emp WHERE empno=7788);

执行结果为：

EMPNO 	ENAME      JOB
------------------ --------------- ----------------
7902 	FORD       ANALYST

说明：在该例的子查询中返回两列，查询条件中也要出现两列，表示雇员的职务和部门应该和SCOTT的职务和部门相同。

4．在FROM从句中使用子查询

在FROM从句中也可以使用子查询，在原理上这与在WHERE条件中使用子查询类似。有的时候我们可能要求从雇员表中按照雇员出现的位置来检索雇员，很容易想到的是使用rownum虚列。比如我们要求显示雇员表中6～9位置上的雇员，可以用以下方法。
【训练9】查询雇员表中排在第6～9位置上的雇员。
执行以下查询：

SELECT ename,sal FROM (SELECT rownum as num,ename,sal FROM emp WHERE rownum<=9 )
WHERE num>=6;

执行结果为：

ENAME           	SAL
-------------- --------------------
BLAKE           	2850
CLARK           	2450
SCOTT           	3000
KING             	5000

说明：子查询出现在FROM从句中，检索出行号小于等于9的雇员，并生成num编号列。在主查询中检索行号大于等于6的雇员。

注意：以下用法不会有查询结果，请自行分析原因。

SELECT ename,sal FROM emp
WHERE rownum>=6 AND rownum<=9;

【练习3】查询雇员表中的第6个雇员。

（4）集合运算

多个查询语句的结果可以做集合运算，结果集的字段类型、数量和顺序应该一样。
Oracle共有4个集合操作，如表2-13所示。
在这里插入图片描述

1．使用集合的并运算

【训练1】查询部门10和部门20的所有职务。
执行以下查询：

SELECT  job FROM emp WHERE deptno=10
UNION
SELECT  job FROM emp WHERE deptno=20;

执行结果为：

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT

说明：部门10的职务有PRESIDENT、MANAGER、CLERK；部门20的职务有MANAGER、CLERK、ANALYST。所以两个部门的所有职务(相同职务只算一个)共有4个：ANALYST、CLERK、MANAGER和PRESIDENT。可以将UNION改为UNION ALL查看一下结果。

2．使用集合的交运算

【训练2】查询部门10和20中是否有相同的职务和工资。
执行以下查询：

SELECT  job,sal FROM emp WHERE deptno=10
INTERSECT
SELECT  job,sal FROM emp WHERE deptno=20;

执行结果为：
未选定行
说明：部门10的职务有PRESIDENT、MANAGER、CLERK；部门20的职务有MANAGER、CLERK、ANALYST。所以两个部门的相同职务为：CLERK和MANAGER。但是职务和工资都相同的雇员没有，所以没有结果。

3．使用集合的差运算（minus）

【训练3】查询只在部门表中出现，但没有在雇员表中出现的部门编号。
执行以下查询：

SELECT  deptno FROM dept
MINUS
SELECT  deptno FROM emp ;

执行结果为：

DEPTNO
------------------
40

说明：部门表中的部门编号有10、20、30和40。雇员表中的部门编号有10、20和30。差集的结果为40。
【练习1】查询具有职务CLERK和SALESMAN的所有部门编号。
【练习2】试求部门10和20中不相同的职务(即部门10中有、部门20中没有和部门20中有、部门10中没有的职务)。

8. 数据操作

1 数据库操作语句

在这里插入图片描述

（1）插入数据

可以使用INSERT命令，向已经存在的表插入数据，语法格式如下：

INSERT INTO 表名 [(字段列表)] {VALUES(表达式1, 表达式2,...)|QUERY语句}；

1．数据插入基本语法

最常见的插入操作可使用以下的语法(该形式一次只能插入一行数据)：

INSERT INTO 表名[(字段列表)] VALUES ( 表达式列表);

插入字段的值的类型要和字段的类型一一对应。字符串类型的字段值必须用单引号括起来，例如：‘CLERK’。字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验。字段列表如果省略则代表全部字段。
【训练1】表的部分字段插入练习。
步骤1：将新雇员插入到emp表：

INSERT INTO emp(empno,ename,job)
VALUES (1000, '小李', 'CLERK');

执行结果为：
已创建1行。
步骤2：显示插入结果

SELECT * FROM emp WHERE empno=1000;

执行结果：

EMPNO ENAME     JOB    MGR HIREDATE    SAL       COMM     DEPTNO
----------------- --------------- ----------- -------- ------------------- ---------- ------------------- ---------------
1000 小李       CLERK

说明：INSERT 语句的emp表名后的括号中为要插入的字段列表，VALUES后的括号中为要插入的字段值列表。要插入的字段是雇员编号empno、名称ename和职务job。其他没有插入的字段，系统会填写为表的默认值。如果在表的创建时没有说明默认值，则将插入NULL值。在本训练中，其他没有插入的字段值均为空值NULL。
日期类型的字段值也要用单引号括起来，如’10-1月-03’。日期型的数据默认格式为DD-MON-YY，默认的世纪为当前的世纪，默认的时间为午夜12点。如果指定的世纪不是本世纪或时间不是午夜12点，则必须使用TO_DATE系统函数对字符串进行转换。

【训练2】时间字段的插入练习。

步骤1：将新雇员插入到emp表：

INSERT INTO emp(empno,ename,job,hiredate)
VALUES (1001, '小马', 'CLERK', '10-1月-03');

执行结果为：
已创建 1 行。
说明：在本训练中，插入的雇员雇佣时间为2003年1月10日。
注意：时间的默认格式为DD-MON-YY。
如果要插入表的全部字段，则表名后的字段列表可以省略，如下面的训练。

【训练3】表的全部字段的插入练习。
执行以下的查询：

INSERT INTO dept VALUES (50, '培训部','深圳');

执行结果：
已创建 1 行。
说明：此种方式省略了字段名列表，要注意插入数据的顺序必须与表的字段默认顺序保持一致。如果不知道表的字段默认顺序，可以用DESCRIBE命令查看。
【训练4】插入空值练习。
执行以下的查询：

INSERT INTO emp(empno,ename,job,sal) VALUES(1005,'杨华', 'CLERK',null);

执行结果：
已创建 1 行。
说明：以上训练虽然指定了插入字段sal，但在插入的数值位置指定了NULL值，所以sal的插入值还是NULL。
【练习1】向雇员表插入全部字段的一条记录。

2．复制数据

另一种插入数据(相当于复制)方法的语法格式是：

INSERT INTO 表名(字段列表) SELECT(字段名1, 字段名2, ...) FROM 另外的表名;

该形式一次可以插入多行数据。
【训练5】通过其他表插入数据的练习。

步骤1：创建一个新表manager：

CREATE TABLE manager AS SELECT empno,ename,sal FROM emp WHERE job='MANAGER';

执行结果：
表已创建。

步骤2：从emp表拷贝数据到manager：

INSERT INTO manager
SELECT	empno, ename, sal
FROM   emp
WHERE	job = 'CLERK';

执行结果：
已创建 1 行。

步骤3：查询结果：

SELECT　* FROM MANAGER;

结果为：

EMPNO 	ENAME         	SAL
------------------ ---------------- --------------------
7566 	JONES            	2975
7698 	BLAKE           	2850
7782 	CLARK           	2450
1000 	小李

说明：CREATE命令用来根据已经存在的表创建新表。步骤1根据emp表创建一个新表manager，该表只有3个字段empno,ename和sal，创建的同时将emp表中职务为manager的雇员复制到其中。步骤2从emp表中把职务为clerk的雇员插入到manager表中。

3．使用序列

使用INSERT语句时，可以通过序列来填写某些数值型或字符型的列。序列是一个要预先定义的有序的数值序列, 应该先建立一个序列，然后在插入语句中使用，序列将在以后章节中介绍。

【训练6】插入数据中使用序列的练习。
步骤1：创建从2000起始，增量为1 的序列abc：

CREATE SEQUENCE abc INCREMENT BY 1  START  WITH  2000 
MAXVALUE  99999  CYCLE  NOCACHE;

执行结果：
序列已创建。

步骤2：在INSERT 语句使用序列，序列的名称为abc：

INSERT INTO manager VALUES(abc.nextval,'小王',2500);

执行结果：
已创建 1 行。

INSERT INTO manager VALUES(abc.nextval,'小赵',2800);

执行结果：
已创建 1 行。

步骤3：使用SELECT语句观察结果：

SELECT empno,ename,sal FROM emp;

执行结果：

EMPNO   	ENAME                SAL
-----------------   ---------------       ----------------		
7566 		JONES          	  2975
7698 		BLAKE          	  2850
7782 		CLARK          	  2450
2000		小王            	  2500
2001 		小赵           	  2800

说明：步骤1创建序列，步骤2在插入时使用序列来填充雇员编号，使用abc.nextval可获得序列中的下一个值。后边两个记录的雇员编号来自序列，并且是递增的。

（2）修改数据

修改数据的语句UPDATE对表中指定字段的数据进行修改，一般需要通过添加WHERE条件来限定要进行修改的行，如果不添加WHERE条件，将对所有的行进行修改。

(1) 修改数据的语句UPDATE的基本语法如下：

UPDATE 表名 SET 字段名1=表达式1, 字段名2=表达式2, ... WHERE 条件;

【训练1】修改小李(编号为1000)的工资为3000。
执行以下的查询：

UPDATE 	emp
SET    	sal = 3000
WHERE 	empno = 1000;

执行结果：
已更新 1 行。
说明：该操作将编号为1000的雇员的工资改为3000。
【训练2】将小李(编号为1000)的雇佣日期改成当前系统日期，部门编号改为50。
执行以下的查询：

UPDATE  	emp
SET 	hiredate=sysdate, deptno=50
WHERE   	empno = 1000;

执行结果：
已更新 1 行。
说明：该操作同时修改编号为1000的雇员的雇佣日期和部门编号两个字段的值。
如果修改的值没有赋值或定义，将把原来字段的内容清为NULL。若修改值的长度超过定义的长度，则会出错。
注意：本例中不能省略WHERE条件，否则将会修改表的所有行。
【练习1】将SCOTT的职务改为MANAGER，工资改为4000。

【训练3】为所有雇员增加100元工资。
执行以下的查询：

UPDATE  emp
SET  sal =sal+100;

执行结果：
已更新18行。
说明：若没有WHERE条件，将修改表的所有行。sal=sal+100的含义是：对于每条记录，取出原来sal字段的工资，加100后再赋给sal字段。
【练习2】将emp表的部门10的雇员工资增加10%。

(2) UPDATE语句的另外一种用法：

UPDATE 表名 SET(字段名1, 字段名2, ...)=SELECT (字段名1, 字段名2, ...) FROM 另外的表名WHERE条件;

【训练4】根据其他表修改数据。
执行以下的查询：

UPDATE  manager
SET (ename, sal) =(SELECT ename,sal FROM emp WHERE   empno = 7788)
WHERE   empno = 1000;

执行结果：
已更新 1 行。
说明：该操作将manager表中编号为1000的记录的雇员名字和工资修改成为emp表的编号为7788的雇员的名字和工资。

（3）删除数据

删除数据的基本语法如下：

DELETE FROM表名 WHERE 条件;

要从表中删除满足条件的记录，WHERE条件一般不能省略，如果省略就会删除表的全部数据。
【训练1】删除雇员编号为1000的新插入的雇员。
步骤1：删除编号为1000的雇员：

DELETE FROM emp WHERE empno=1000;

结果为：
已删除 1 行。
步骤2：显示删除结果：

SELECT * FROM emp WHERE empno=1000;

结果为：
未选定行。

说明：本例删除雇员编号为1000的雇员，它在WHERE中指定删除的记录。删除记录并不能释放Oracle中被占用的数据块表空间，它只是把那些被删除的数据块标成unused。

如果确实要删除一个大表里的全部记录，可以用TRUNCATE 命令，它可以释放占用的数据块表空间，语法为：

TRUNCATE TABLE 表名;

【训练2】彻底删除manager表的内容。
执行以下的命令：

TRUNCATE TABLE manager;

执行结果：
表已截掉。
说明：此命令和不带WHERE条件的DELETE语句功能类似，不同的是，DELETE命令进行的删除可以撤销，但此命令进行的删除不可撤销。
注意：TRUNCATE TABLE命令用来删除表的全部数据而不是删除表，表依旧存在。

2 数据库事务

（1）数据库事务的概念

事务是由相关操作构成的一个完整的操作单元。两次连续成功的COMMIT或ROLLBACK之间的操作，称为一个事务。在一个事务内，数据的修改一起提交或撤销，如果发生故障或系统错误，整个事务也会自动撤销。
比如，我们去银行转账，操作可以分为下面两个环节：
(1) 从第一个账户划出款项。
(2) 将款项存入第二个账户。
在这个过程中，两个环节是关联的。第一个账户划出款项必须保证正确的存入第二个账户，如果第二个环节没有完成，整个的过程都应该取消，否则就会发生丢失款项的问题。整个交易过程，可以看作是一个事物，成功则全部成功，失败则需要全部撤消，这样可以避免当操作的中间环节出现问题时，产生数据不一致的问题。
数据库事务是一个逻辑上的划分，有的时候并不是很明显，它可以是一个操作步骤，也可以是多个操作步骤。
我们可以这样理解数据库事务：对数据库所做的一系列修改，在修改过程中，暂时不写入数据库，而是缓存起来，用户在自己的终端可以预览变化，直到全部修改完成，并经过检查确认无误后，一次性提交并写入数据库，在提交之前，必要的话所做的修改都可以取消。提交之后，就不能撤销，提交成功后其他用户才可以通过查询浏览数据的变化。
以事务的方式对数据库进行访问，有如下的优点：
* 把逻辑相关的操作分成了一个组。
* 在数据永久改变前，可以预览数据变化。
* 能够保证数据的读一致性。

（2）数据库事务的应用

数据库事务处理可分为隐式和显式两种。显式事务操作通过命令实现，隐式事务由系统自动完成提交或撤销(回退)工作，无需用户的干预。
隐式提交的情况包括：当用户正常退出SQL*Plus或执行CREATE、DROP、GRANT、REVOKE等命令时会发生事务的自动提交。
还有一种情况，如果把系统的环境变量AUTOCOMMIT设置为ON(默认状态为OFF)，则每当执行一条INSERT、DELETE或UPDATE命令对数据进行修改后，就会马上自动提交。
设置命令格式如下：SET AUTOCOMMIT ON/OFF
隐式回退的情况包括：当异常结束SQL*Plus或系统故障发生时，会发生事务的自动回退。
显式事务处理的数据库事务操作语句有3条，如表3-2所示。
COMMIT操作把多个步骤对数据库的修改，一次性地永久写入数据库，代表数据库事务的成功执行。ROLLBACK操作在发生问题时，把对数据库已经作出的修改撤消，回退到修改前的状态。在操作过程中，一旦发生问题，如果还没有提交操作，则随时可以使用ROLLBACK来撤消前面的操作。
SAVEPOINT则用于在事务中间建立一些保存点，ROLLBACK可以使操作回退到这些点上边，而不必撤销全部的操作。一旦 COMMIT完成，就不能用ROLLBACK来取消已经提交的操作。一旦ROLLBACK完成，被撤消的操作要重做，必须重新执行相关操作语句。
如何开始一个新的事务呢？一般情况下，开始一个会话(即连接数据库)，执行第一条SQL语句将开始一个新的事务，或执行COMMIT提交或ROLLBACK撤销事务，也标志新的事务的开始。另外，执行DDL(如CREATE)或DCL命令也将自动提交前一个事务而开始一个新的事务。
数据在修改的时候会对记录进行锁定，其他会话不能对锁定的记录进行修改或加锁，只有当前会话提交或撤销后，记录的锁定才会释放。详细内容见下一节。
我们通过以下的训练来为雇员SCOTT增加工资，SCOTT的雇员号为7788。

【训练1】学习使用COMMIT和ROLLBACK。

步骤1：执行以下命令，提交尚未提交的操作：

COMMIT;

执行结果：
提交完成。
显示SCOTT的现有工资：

SELECT ename,sal FROM emp WHERE empno=7788;

执行结果：

ENAME            SAL
---------- ------------------------
SCOTT            3000

步骤2：修改雇员SCOTT的工资：

UPDATE emp SET sal=sal+100 WHERE empno=7788;

执行结果：
已更新1行。
显示修改后的SCOTT的工资：

SELECT ename,sal FROM emp WHERE empno=7788;

执行结果：

ENAME           	SAL
---------- ------------------------
SCOTT            	3100

步骤3：假定修改操作后发现增加的工资应该为1000而不是100，为了取消刚做的操作，可以执行以下命令：

ROLLBACK;

执行结果：
回退已完成。
显示回退后SCOTT的工资恢复为3000：

SELECT ename,sal FROM emp WHERE empno=7788;

执行结果：

ENAME           	SAL
---------- ------------------------
SCOTT           	3000

步骤4：重新修改雇员SCOTT的工资，工资在原有基础上增加1000：

UPDATE emp SET sal=sal+1000 WHERE empno=7788;

执行结果：
已更新 1 行。
显示修改后SCOTT的工资：

SELECT ename,sal FROM emp WHERE empno=7788;

执行结果：

ENAME           	SAL
---------- ------------------------
SCOTT            	4000

步骤5：经查看修改结果正确，提交所做的修改：

COMMIT;

执行结果：
提交完成。
说明：在执行COMMIT后，工资的修改被永久写入数据库。本训练的第1步，先使用COMMIT命令提交原来的操作，同时标志一个新的事务的开始。

注意：在事务执行过程中，随时可以预览数据的变化。对于比较大的事务，可以使用SAVEPOINT命令在事务中间划分一些断点，用来作为回退点。
【训练2】学习使用SAVEPOINT命令。
步骤1：插入一个雇员：

INSERT INTO emp(empno, ename, job)
VALUES (3000, '小马','STUDENT');

执行结果：
已创建 1 行。

步骤2：插入保存点，检查点的名称为PA：

 SAVEPOINT pa;

执行结果：
保存点已创建。

步骤3：插入另一个雇员：

 INSERT INTO emp(empno, ename, job)
 VALUES (3001, '小黄','STUDENT');

执行结果：
已创建 1 行。

步骤4：回退到保存点PA，则后插入的小黄被取消，而小马仍然保留。

 ROLLBACK TO　pa;

执行结果：
回退已完成。

步骤5: 提交所做的修改：

 COMMIT;

执行结果：
提交完成。
说明：第4步的回退，将回退到保存点PA，即第3步被撤销。所以最后的COMMIT只提交了对小马的插入。请自行检查插入的雇员。

在Oracle数据库中，有一个叫回滚段的特殊的存储区域。在提交一个事物之前，如果用户进行了数据的修改，在所谓的回滚段中将保存变化前的数据。有了回滚段才能在必要时使用ROLLBACK命令或自动地进行数据撤销。在提交事物之前，用户自己可以看到修改的数据，但因为修改还没有最终提交，其他用户看到的应该是原来的数据，也就是回滚段中的数据，这时用户自己看到的数据和其他用户看到的数据是不同的，只有提交发生后，变化的数据才会被写入数据库，此时用户自己看到的数据和其他用户看到的数据才是一致的，这叫做数据的读一致性。

【训练3】观察数据的读一致性。
步骤1：显示刚插入的雇员小马:

 SELECT empno,ename FROM emp WHERE empno=3000;

执行结果：

 EMPNO ENAME
 ------------------ -----------
 3000   小马

步骤2：删除雇员小马:

 DELETE FROM emp WHERE empno=3000;

执行结果：
已删除 1 行。

步骤3：再次显示该雇员，显示结果为该雇员不存在：

 SELECT empno,ename FROM emp WHERE empno=3000;

执行结果：
未选定行

步骤4：另外启动第2个SQL*Plus，并以SCOTT身份连接。执行以下命令，结果为该记录依旧存在。

 SELECT empno,ename FROM emp WHERE empno=3000;

执行结果：

 EMPNO ENAME
 ------------------ -----------
 3000   小马

步骤5：在第1个SQL*Plus中提交删除：

 COMMIT;

执行结果：
提交完成。
步骤6：在第2个SQL*Plus中再次显示该雇员，显示结果与步骤3的结果一致:

 SELECT empno,ename FROM emp WHERE empno=3000;

执行结果：
未选定行
说明：在以上训练中，当第1个SQLPlus会话删除小马后，第2个SQLPlus会话仍然可以看到该雇员，直到第1个SQL*Plus会话提交该删除操作后，两个会话看到的才是一致的数据。

3 表的锁定

1.锁的概念

锁出现在数据共享的场合，用来保证数据的一致性。当多个会话同时修改一个表时，需要对数据进行相应的锁定。
锁有“只读锁”、“排它锁”，“共享排它锁”等多种类型，而且每种类型又有“行级锁”(一次锁住一条记录)，“页级锁”(一次锁住一页，即数据库中存储记录的最小可分配单元)，“表级锁”(锁住整个表)。
若为“行级排它锁”，则除被锁住的行外，该表中其他行均可被其他的用户进行修改(Update)或删除(delete)。若为“表级排它锁”，则所有其他用户只能对该表进行查询(select)操作，而无法对其中的任何记录进行修改或删除。当程序对所做的修改进行提交(commit)或回滚(rollback)后，锁住的资源便会得到释放，从而允许其他用户进行操作。
有时，由于程序的原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，调出需要修改的数据后，未及时修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现“死机”，而服务器端却并未检测到，从而造成锁定的资源未被及时释放，影响到其他用户的操作。
如果两个事务，分别锁定一部分数据，而都在等待对方释放锁才能完成事务操作，这种情况下就会发生死锁。

2 隐式锁和显式锁

在Oracle数据库中，修改数据操作时需要一个隐式的独占锁，以锁定修改的行，直到修改被提交或撤销为止。如果一个会话锁定了数据，那么第二个会话要想对数据进行修改，只能等到第一个会话对修改使用COMMIT命令进行提交或使用ROLLBACK命令进行回滚撤销后，才开始执行。因此应养成一个良好的习惯：执行修改操作后，要尽早地提交或撤销，以免影响其他会话对数据的修改。

【训练1】对emp表的SCOTT雇员记录进行修改，测试隐式锁。

步骤1：启动第一个SQL*Plus，以SCOTT账户登录数据库(第一个会话)，修改SCOTT记录，隐式加锁。

 UPDATE emp SET sal=3500 where empno=7788;

执行结果：
已更新 1 行。
步骤2：启动第二个SQL*Plus，以SCOTT账户登录数据库(第二个会话)，进行记录修改操作。

 UPDATE emp SET sal=4000 where empno=7788;

执行结果，没有任何输出(处于等待解锁状态)。
步骤3：对第一个会话进行解锁操作：

 COMMIT;

步骤4：查看第二个会话，此时有输出结果：
已更新 1 行。
步骤5：提交第二个会话，防止长时间锁定。
说明：两个会话对同一表的同一条记录进行修改。步骤1修改SCOTT工资为3500，没有提交或回滚之前，SCOTT记录处于加锁状态。步骤2的第二个会话对SCOTT进行修改处于等待状态。
步骤3解锁之后(即第一个会话对SCOTT的修改已经完成)，第二个会话挂起的修改此时可以执行。最后结果为第二个会话的修改结果，即SCOTT的工资修改为4000。读者可以使用查询语句检查。
以上是隐式加锁，用户也可以使用如下两种方式主动锁定行或表，防止其他会话对数据的修改。表3-3是对行或表进行锁定的语句。

在这里插入图片描述

3.锁定行

【训练1】对emp表的部门10的雇员记录加显式锁，并测试。
步骤1：对部门10加显式锁：

 SELECT empno,ename,job,sal FROM emp WHERE deptno=10 FOR UPDATE;

结果为：

 EMPNO ENAME   	JOB              	SAL
 ------------------ -------------- ------------------ ------------------
 7782  CLARK    	MANAGER        	2450
 7839  KING      	PRESIDENT       	5000
 7934  MILLER    CLERK           	1300

步骤2：启动第二个SQL*Plus(第二个会话)，以SCOTT账户登录数据库，对部门10的雇员CLARK进行修改操作。

 UPDATE emp SET sal=sal+100 where empno=7782;

执行结果：
没有任何输出(处于等待解锁状态)。

步骤3：在第一个会话进行解锁操作：

 COMMIT;

步骤4：查看第二个会话，有输出结果：
已更新 1 行。
说明：步骤1对选定的部门10的雇员加锁，之后其他会话不能对部门10的雇员数据进行修改或删除。如果此时要进行修改或删除，则会处于等待状态。使用COMMIT语句进行解锁之后，如果有挂起的修改或删除操作，则等待的操作此时可以执行。

4.锁定表

LOCK语句用于对整张表进行锁定。语法如下：

 LOCK TABLE 表名 IN {SHARE|EXCLUSIVE} MODE

对表的锁定可以是共享(SHARE)或独占(EXCLUSIVE)模式。共享模式下，其他会话可以加共享锁，但不能加独占锁。在独占模式下，其他会话不能加共享或独占锁。
【训练1】对emp表添加独占锁。
步骤1：对emp表加独占锁：

 LOCK TABLE emp IN EXCLUSIVE MODE;

结果为：
表已锁定。
步骤2：对表进行解锁操作：

 COMMIT;

说明：当使用LOCK语句显式锁定一张表时，死锁的概率就会增加。同样地，使用COMMIT或ROLLBACK命令可以释放锁。
注意：必须没有其他会话对该表的任何记录加锁，此操作才能成功。

岚苼

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle数据库

修改“出版社”表“电子邮件”列的宽度为40。
复制链接

扫一扫

Oracle数据库

文章目录

1. 表的创建

（1）创建表的语法

举例1：创建出版社表。

举例2：创建图书表

（2）使用DESCRIBE（describe）显示图书表的结构

（3）通过子查询创建表

举例

（4）设置列的默认值 DEFAULT（default）

举例

（5）删除已创建的表

解析CASCADE CONSTRAINTS（cascade constraints）

2. 表的操作

（1）表的重命名 RENAME TO

（2）清空表TRUNCATE （truncate）

（3）添加注释 COMMENT ON TABLE 表名 IS

1. 为表添加注释的语法为：

2. 为列添加注释的语法为：

（4）查看表DESCRIBE（describe）

举例

3. 数据完整性和约束条件

（1）概念：数据完整性约束

（2） 表的五种约束

1. 主键(PRIMARY KEY)

主键约束的语法（表级，列级）如下：

2. 非空(NOT NULL)

3．惟一(UNIQUE)

4．检查(CHECK)

5．外键 FOREIGN KEY（foreing key）

（3）约束条件的创建

（4） 提交插入的数据 COMMIT：

隐式提交的方式

显式提交

（5）恢复删除:

（6）查看约束条件

（7）使约束生效和失效

使约束条件失效：DISABLE CONSTRANT

使约束条件生效：ENABLE CONSTRANT

4. 修改表结构

（1）增加新列

（2）修改列

（3）删除列

（4）UNUSED状态（不会在表中显示出该列）

（5）约束条件的修改

1．增加约束条件

2．删除约束条件

5. 分区表

（1）分区的作用

（2）分区的实例

6. 视图创建和操作

（1）视图的概念

（2） 视图的创建

（3）删除视图

（3）创建复杂视图

（4）创建只读视图

（5）视图的操作

视图的插入

1．视图的插入

2．使用WITH CHECK OPTION选项

3．来自基表的限制

4. 视图的查看

7. 数据查询

1 数据库查询语言SQL

（1）SQL语言有以下的主要特点：

（2）SQL语言的分类

2 基本查询和排序

（1）查询的基本用法

1．指定检索字段

2．显示行号

3．显示计算列

4．使用别名

5．连接运算符（||）

6．消除重复行 DISTINCT

（2）查询结果的排序

1．升序排序

2．降序排序

3．多列排序

4．在排序中使用别名

3 条件查询

（2）表的五种约束

（4）提交插入的数据 COMMIT：

（2）视图的创建

1. 比较运算符

【训练1】使用数值型函数练习。

【训练2】求|sin(230o)|的值，保留一位小数。