Oracle教程pdf版

1.SQL 简介

在第一学期的 SQL Server 学习中，已经知道，SQL 是结构化查询语言（Structured Query

Language），专门用于数据存取、数据更新及数据库管理等操作。并且已经学习了用 SQL 语句对数据库的表进行增删改查的操作。

在 Oracle 开发中，客户端把 SQL 语句发送给服务器，服务器对 SQL 语句进行编译、执行，把执行的结果返回给客户端。Oracle SQL 语句由如下命令组成：

Ø 数据定义语言（DDL），包括 CREATE（创建）命令、ALTER（修改）命令、DROP（删除）命令等。

Ø 数据操纵语言（DML），包括 INSERT（插入）命令、UPDATE（更新）命令、DELETE

（删除）命令、SELECT … FOR UPDATE（查询）等。

Ø 数据查询语言（DQL），包括基本查询语句、Order By 子句、Group By 子句等。

Ø 事务控制语言（TCL），包括 COMMIT（提交）命令、SAVEPOINT（保存点）命令、 ROLLBACK（回滚）命令。

Ø 数据控制语言（DCL），GRANT（授权）命令、REVOKE（撤销）命令。

目前主流的数据库产品（比如：SQL Server、Oracle）都支持标准的 SQL 语句。数据定义语言，表的增删改操作，数据的简单查询，事务的提交和回滚，权限的授权和撤销等，Oracle 与 SQL Server 在操作上基本一致。

2.Oracle 数据类型Oracle 数据库的核心是表，表中的列使用到的常见数据类型如下：

类型	含义
CHAR(length)	存储固定长度的字符串。参数length 指定了长度，如果存储的字符串长 度小于length，用空格填充。默认长度是 1，最长不超过 2000 字节。
VARCHAR2(length)	存储可变长度的字符串。length 指定了该字符串的最大长度。默认长度 是 1，最长不超过 4000 字符。
NUMBER(p，s)	既可以存储浮点数，也可以存储整数，p 表示数字的最大位数（如果是 小数包括整数部分和小数部分和小数点，p 默认是 38 为），s 是指小数位数。
DATE	存储日期和时间，存储纪元、4 位年、月、日、时、分、秒，存储时间

	从公元前 4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。
TIMESTAMP	不但存储日期的年月日，时分秒，以及秒后 6 位，同时包含时区。
CLOB	存储大的文本，比如存储非结构化的 XML 文档
BLOB	存储二进制对象，如图形、视频、声音等。

表 1 Oracle 的部分数据类型

对应 NUMBER 类型的示例：

格式	输入的数字	实际的存储
NUMBER	1234.567	1234.567
NUMBER（6，2）	123.4567	123.46
NUMBER（4，2）	12345.67	输入的数字超过了所指定的精度，数据库不能存储

表 2 Number 示例

对于日期类型，可以使用 sysdate 内置函数可以获取当前的系统日期和时间，返回 DATE

类型，用 systimestamp 函数可以返回当前日期、时间和时区。

3.创建表和约束

Oracle 创建表同 SQL Server 一样，使用 CREATE TABLE 命令来完成。创建约束则使用如下命令：

语法格式：ALTER TABLE 命令

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容。

不论创建表还是约束，与 SQL Server 基本相同，注意：在 Oracle 中 default 是一个值， 而 SQL Server 中 default 是一个约束，因此 Oracle 的 default 设置可以在建表的时候创建。

案例 1：创建一个学生信息（INFOS）表和约束

代码演示：Oracle 创建表和约束

CREATE TABLE INFOS (

STUID VARCHAR2(7) NOT NULL,–学号 学号=‘S’+班号+2位序号

STUNAME VARCHAR2(10) NOT NULL, –姓名GENDER VARCHAR2(2) NOT NULL,–性别AGE NUMBER(2) NOT NULL,–年龄SEAT NUMBER(2) NOT NULL,–座号ENROLLDATE DATE,–入学时间

STUADDRESS VARCHAR2(50) DEFAULT ‘地址不详’,–住址CLASSNO VARCHAR2(4) NOT NULL–班号 班号=学期序号+班级序号

)

/ ①

ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID) ②

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = ‘男’ OR GENDER = ‘女’) ③

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT CHECK(SEAT >=0 AND SEAT <=50) ④

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE CHECK(AGE >=0 AND AGE<=100) ⑤

/

ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO CHECK((CLASSNO >=’1001′ AND CLASSNO<=’1999′) OR

(CLASSNO >=’2001′ AND CLASSNO<=’2999′)) ⑥

/

ALTER TABLE INFOS ADD CONSTRAINTS UN_STUNAME⑦

/

代码解析：

① 在 Oracle 代码中，“/”执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过语句，由于每条语句没有用分号结尾，只是保存在缓冲区，因此每条语句后面都有单独一行“/”。

② 创建一个主键约束。

③ 与 ④ ⑤ ⑥ ⑦一起创建各种check 约束。其中⑦是唯一约束，表示该列值是唯一的，列中的值不能重复。

Oracle 中创建外键约束与 SQL Server 相同。比如：现有成绩表定义如下：

案例 2：创建一个成绩表（SCORES）表和约束代码演示：Oracle 创建表和约束

CREATE TABLE SCORES (

ID NUMBER ,–ID ①

TERM VARCHAR2(2),–学期 S1或S2

STUID VARCHAR2(7) NOT NULL,–学号

EXAMNO VARCHAR2(7) NOT NULL,–考号 E+班号+序号WRITTENSCORE NUMBER(4,1) NOT NULL,   –笔试成绩LABSCORE NUMBER(4,1) NOT NULL–机试成绩

)

ALTER TABLE SCORES

ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = ‘S1′ OR TERM =’S2’)

/

ALTER TABLE SCORES

ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID) ②

/

代码解析：

① SQL Server 中可以使用 identify 创建自动增长列，但是 Oracle 中的自动增长需要借助序列（Sequence）完成，在后面章节中讲解。

② Oracle 中的外键约束定义。

代码演示：

INSERT 向表中插入一个结果集

———————————————————–

SQL> INSERT INTO INFOS2 SELECT * FROM INFOS;

5 rows inserted

———————————————————–

INSERT 向表中插入一个常量结果集

———————————————————-

SQL> INSERT INTO INFOS

SELECT ‘s100106′,’卢俊义’,’男’,23,5,

TO_DATE(‘2009-8-9 08:00:10′,’YYYY-MM-DD HH24:MI:SS’),

‘青龙寺’,’1001′ FROM DUAL;

1 rows inserted SQL>COMMIT;

———————————————————–

代码演示：INSERT 向表中插入一个常量结果集

SQL> INSERT INTO INFOS

SELECT ‘s100106′,’卢俊义’,’男’,23,5,

TO_DATE(‘2009-8-9 08:00:10′,’YYYY-MM-DD HH24:MI:SS’),

‘青龙寺’,’1001′ FROM DUAL;

1 rows inserted SQL>COMMIT;

更新数据

Oracle 在表中更新数据的语法是：

语法结构：UPDATE 操作

UPDATE 表名 SET 列名 1=值，列名 2=值…… WHERE 条件

代码演示：UPDATE 操作

SQL> UPDATE INFOS SET CLASSNO=’1002′,STUADDRESS=’山东莱芜’ WHERE STUNAME=’阮小二’;

1 rows updated SQL> commit;

删除数据

Oracle 在表中删除数据的语法是：

语法结构：DELETE 操作

DELETE FROM 表名 WHERE 条件

代码演示：DELETE 操作

SQL> DELETE FROM INFOS WHERE STUID=’s100103′; 1 ROW DELETED

SQL> COMMIT;

TRUNCATE

在数据库操作中， TRUNCATE 命令（是一个 DDL 命令）可以把表中的所有数据一次性全部删除，语法是：

语法结构：TRUNCATE

TRUNCATE TABLE 表名

TRUNCATE 和 DELETE 都能把表中的数据全部删除，他们的区别是：

1.TRUNCATE 是 DDL 命令，删除的数据不能恢复；DELETE 命令是 DML 命令，删除后的数据可以通过日志文件恢复。

2.如果一个表中数据记录很多，TRUNCATE 相对 DELETE 速度快。

由于 TRUNCATE 命令比较危险，因此在实际开发中，TRUNCATE 命令慎用。

5.操作符

Oracle 开发中，依然存在算术运算，关系运算，和逻辑运算。

算术运算

Oracle 中的算术运算符，没有 C#中的算术运算符丰富，只有+、-、*、/四个，其中除号

(/)的结果是浮点数。求余运算只能借助函数：MOD(x,y)：返回 x 除以y 的余数。

案例 3：每名员工年终奖是 2000 元，请显示基本工资在 2000 元以上的员工的月工资， 年总工资。

该案例的表请参见本章练习的附表 1、附表 2、附表 3，这三张表是 ORACLE 10g 自带的。

代码演示：查询中的算术运算

SQL> SELECT ENAME,SAL,(SAL*12+2000) FROM EMP WHERE SAL>2000;

ENAMESAL(SAL*12+2000)

JONES297537700

BLAKE285036200

CLARK245031400

SCOTT300038000

KING500062000

FORD300038000

6 rows selected

关系运算和逻辑运算

Oracle 中 Where 子句经中经常见到关系运算和逻辑运算，常见的关系运算有：

运算符说明运算符说明

=等于>大于

<>或者!=不等于<=小于或者等于

<小于>=大于或者等于

表 3  Oracle 的关系运算符

逻辑运算符有三个：AND、OR、NOT

关系运算和逻辑运算与前面 SQL Server 学习过的一致。

字符串连接操作符（||）

在 Oracle 中，字符串的连接用双竖线（||）表示。比如，在 EMP 表中，查询工资在 2000

元以上的姓名以及工作。

代码演示：字符串连接

SQL> SELECT (ENAME || ‘is a ‘ || JOB) AS “Employee Details” ①

2FROM EMP

3WHERE SAL>2000; Employee Details

———————— JONESis a MANAGER BLAKEis a MANAGER CLARKis a MANAGER SCOTTis a ANALYST KINGis a PRESIDENT FORDis a ANALYST

6rows selected

代码解析：

① Oracle 中字符串可以用单引号，也可以用双引号，在别名中存在空格时，必须用双引号。在表名、列名时用双引号。

6.高级查询

在第一期学习过 SQL 的简单查询和连接查询。现在学习一些新的 SQL 操作符。

消除重复行

在 Oracle 查询中结果中，可能出现若干行相同的情况，那么可以使用 DISTINCT 消除重复行。具体的用法如示例：

代码演示：DISTINCT 消除重复行

SQL> SELECT DISTINCT DEPTNO FROM EMP; DEPTNO

——

30

20

10

NULL 操作

如果某条记录中有缺少的数据值，就是空值（NULL 值）。空值不等于 0 或者空格，空值是指未赋值、未知或不可用的值。任何数据类型的列都可以包括 NULL 值，除非该列被定义为非空或者主键。

代码演示：EMP 中的 NULL 值

SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL<2000;

ENAMEJOBSALCOMM

SMITHCLERK800

ALLENSALESMAN1600300

WARDSALESMAN1250500

MARTINSALESMAN12501400

TURNERSALESMAN15000

ADAMSCLERK1100

JAMESCLERK950

7rows selected

在查询条件中 NULL 值用 IS NULL 作条件，非 NULL 值用 NOT IS NULL 做条件。

案例 4：查询 EMP 表中没有发奖金的员工。

代码演示：NULL 值查询

SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP

2WHERE SAL<2000 AND COMM IS NULL;

ENAMEJOBSALCOMM

SMITHCLERK800

ADAMSCLERK1100

JAMESCLERK950

MILLERCLERK1300

IN 操 作

在 Where 子句中可以使用 IN 操作符来查询其列值在指定的列表中的行。比如：查询出工作职责是SALESMAN、PRESIDENT 或者 ANALYST 的员工。条件有两种表示方法：

1.WHERE job = ‘SALESMAN ‘ OR job = ‘PRESIDENT ‘ OR job = ‘ANALYST ‘

2.WHERE job IN (‘SALESMAN’, ‘PRESIDENT’, ‘ANALYST’)

代码演示：IN 操作

SQL> SELECT ENAME,JOB,SAL FROM EMP

2 WHERE job IN (‘SALESMAN’, ‘PRESIDENT’, ‘ANALYST’);

ENAMEJOBSAL

ALLENSALESMAN1600

WARDSALESMAN1250

MARTINSALESMAN1250

SCOTTANALYST3000

KINGPRESIDENT5000

TURNERSALESMAN1500

FORDANALYST3000

7 rows selected

对应 IN 操作的还有 NOT IN，用法一样，结果相反。

BETWEEN…AND…

在 WHERE 子句中，可以使用 BETWEEN 操作符来查询列值包含在指定区间内的行。比如，查询工资从 1000 到 2000 之间的员工。可以使用传统方法：

WHERE SAL>=1000 AND SAL<=2000

也可以使用：

WHERE SAL BETWEEN 1000 AND 2000

BWTWEEN 操作所指定的范围也包括边界。

代码演示：BETWEEN 操作

SQL> SELECT ename,job,sal FROM EMP WHERE sal BETWEEN 1000 AND 2000;

ENAMEJOBSAL

ALLENSALESMAN1600

WARDSALESMAN1250

MARTINSALESMAN1250

TURNERSALESMAN1500

ADAMSCLERK1100

MILLERCLERK1300

6 rows selected

LIKE 模糊查询

在一些查询时，可能把握不准需要查询的确切值，比如百度搜索时输入关键字即可查询出相关的结果，这种查询称为模糊查询。模糊查询使用 LIKE 关键字通过字符匹配检索出所需要的数据行。字符匹配操作可以使用通配符“%”和“_”:

%：表示零个或者多个任意字符。

_：代表一个任意字符。

语法是：LIKE ‘字符串'[ESCAPE ‘字符’]。匹配的字符串中，ESCAPE 后面的“字符”作为转义字符。与一期 SQLServer 中ESCAPE 用法相同。

通配符表达式说明

‘S%’以S 开头的字符串。

‘_S%’第二个字符时S 的字符串。

‘%30\%%’ escape ‘\’包含“30%”的字符串，“\”指转义字符，“\%”在字符串中

表示一个字符“%”。

表 4  通配符示例

案例 5：显示员工名称以 J 开头以 S 结尾的员工的姓名、工资和工资。

代码演示：LIKE 操作

SQL> SELECT ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE ‘J%S’;

ENAMEJOBSAL

—————————-

JONESMANAGER2975.00

JAMESCLERK950.00

集合运算

集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：

 INTERSECT(交集)，返回两个查询共有的记录。

UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。

UNION(并集)，返回各个查询的所有记录，不包括重复记录。

MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以不同。

案例 6：查询出 dept 表中哪个部门下没有员工。只需求出 dept 表中的部门号和emp

表中的部门号的补集即可。

代码演示：求补运算

SQL> SELECT DEPTNO FROM DEPT

2MINUS

3SELECT DEPTNO FROM EMP; DEPTNO

——

40

前面学习过可以通过 insert into …select 把一个结果集插入到另一张结构相同的表中，因此可以使用 union 把若干条记录一次性插入到一张表中。

代码演示：用 union 插入多条数据

SQL> INSERT INTO DEPT

2SELECT 50,’公关部’,’台湾’ FROM DUAL

3UNION

4SELECT 60,’研发部’,’西安’ FROM DUAL

5UNION

6SELECT 70,’培训部’,’西安’ FROM DUAL 7 /

3 rows inserted

连接查询

在 SQL Server 中已经学习过内联接(inner join)、外联接(outer join)，外联接又分为左外联接(left outer join)和右外联接(right outer join)。Oracle 中对两个表或者若干表之间的外联接用

（+）表示。

案例 7：请查询出工资大于 2000 元的，员工姓名，部门，工作，工资。由于部门名称在 dept 中，其他的信息在 emp 表中，需要内联接才能完成。

代码演示：内联接

SQL> SELECT e.ENAME,e.JOB,e.SAL,d.DNAME

2FROM emp e,dept d

3WHERE e.deptno=d.deptno 4 AND e.SAL>2000;

ENAMEJOBSALDNAME

JONESMANAGER2975RESEARCH

BLAKEMANAGER2850SALES

CLARKMANAGER2450ACCOUNTING

SCOTTANALYST3000RESEARCH

KINGPRESIDENT5000ACCOUNTING

FORDANALYST3000RESEARCH

6 rows selected

也可以使用 SQL/92 标准中的内联接：

代码演示：内联接

SELECT e.ENAME,e.JOB,e.SAL,d.DNAME

FROM EMP e INNER JOIN DEPT d ON e.DEPTNO=d.DEPTNO WHERE e.SAL>2000

这里 INNER JOIN 中，关键字 INNER 可以省略。

案例 8：请查询出每个部门下的员工姓名，工资。案例分析：

Emp 表用外键 deptno 引用 Dept 表中的 deptno，在 Dept 表中如果有某些部门没有员工，

那么用内联接，没有员工的部门将无法显示，因此必须以 Dept 表为基准的外联接。

代码演示：外联接

SQL> SELECT e.ENAME,e.JOB,e.SAL,d.DNAME

2FROM EMP e ,DEPT d

3WHERE e.DEPTNO(+)=d.DEPTNO ①

4 /

ENAMEJOBSALDNAME

SMITHCLERK800RESEARCH

ALLENSALESMAN1600SALES

WARDSALESMAN1250SALES

JONESMANAGER2975RESEARCH

MARTINSALESMAN1250SALES

BLAKEMANAGER2850SALES

CLARKMANAGER2450ACCOUNTING

SCOTTANALYST3000RESEARCH

KINGPRESIDENT5000ACCOUNTING

TURNERSALESMAN1500SALES

ADAMSCLERK1100RESEARCH

JAMESCLERK950SALES

FORDANALYST3000RESEARCH

MILLERCLERK1300ACCOUNTING

公关部

研发部

培训部

OPERATIONS

18 rows selected

代码解析：

①(+)：Oracle 专用的联接符，在条件中出现在左边指右外联接，出现在右边指左外联接。

也可以使用 SQL/92 标准的写法：

代码演示：外联接

SELECT e.ENAME,e.JOB,e.SAL,d.DNAME

FROM EMP e RIGHT OUTER JOIN DEPT d ON e.DEPTNO=d.DEPTNO

这里 RIGHT OUTER JOIN 中，关键字 OUTER 可以省略。

虽然Oracle 自身的联接查询语法比较好写，同时容易理解，但是为了程序便于移植， 推荐使用SQL/92 表中的联接查询。同时也可以与SQL Server 获得一致。

7.本章总结

Oracle  SQL  语句中有数据操纵语言（DML）、数据定义语言（DDL）、数据控制语言

（DCL）、事务控制语言（TCL）等等。

DML 语句包括增删改查语句，DDL 语句包括数据库对象创建、修改和删除语句，数据控制命令包括 GRANT、REVOKE 等，事务控制命令有 COMMIT、ROLLBACK 等。

数据库中建表常用的类型有：数字类型numbe（r

日期 date。

p，s），可变字符串varchar2（length），

Oracle 中 default 是一个值，在 Oracle 中不存在 default 约束。

Oracle 的增删改语句与 SQL Server 基本一致，都是使用 INSERT、UPDATE、DELETE

完成。

Oracle 高级查询中要注意：DISTINCT、NULL、IN、BETWEEN…AND…。

集合操作有：UNION、UNION ALL、INTESECT、MINUS。

联接查询有内联接和外联接。