SQL语言-中文

计算机实用技术

Part I

Oracle SQL

Part II

Oracle 体系结构

谢金国朱敏

南京航空航天大学计算中心

2005年9月

目录

Part I ORACLE SQL

1. 什么是SQL？

SQL，翻译成中文就是“结构化查询语言”。其发音是“S-Q-L”，也可以读作“sequel”。

SQL是一种计算机语言，它是为了从存储在关系数据库中的数据中获得信息而设计的，即通过SQL可以从大量收集的数据中查找想要的信息。

SQL不同于其他计算机语言，我们使用SQL描述想要的信息类型，然后计算机会确定出获得它的最好过程，并运行这个过程。这就是所谓的“说明性”计算机语言，因为它注重的是结果：我们可以详细说明结果的外表特征。计算机允许使用任意数据处理方法，只要它获得正确结果。

其他计算机语言大部分是“过程性”的，如汇编、Pascal、C、Java等。我们使用这些语言描述应用于数据的过程，无需描述结果。结果是将过程应用到数据后所得到的东西。

这好比我们清晨去一家咖啡店。如果使用SQL支持的说明性方法，你只要说：“我想来一杯咖啡和一个炸面包圈。”而使用过程性方法，你不能那样说，你必须说出如何得到结果，并且给它一个特定的过程。也就是说，你必须说出如何制作咖啡以及炸面包圈。因此，对于咖啡，你必须说：“将一些烘烤好的咖啡豆磨成粉，添加开水，冲泡一会儿，再将它倒入一个茶杯，然后给我。”对于炸面包圈，你必须阅读菜谱。显然，说明性方法更贴近于我们通常说话的方式，并且它更容易于为大多数人所使用。

相对于其他计算机语言而言，SQL是易于使用的，实际上这也正是SQL如此流行和重要的主要原因。

信息自身并不强大，只有在需要的人使用它的时候，它才变得强大。SQL是传递信息的工具。

注释：关于SQL

SQL是从关系数据库中获得信息的说明性语言。

SQL会告诉获得什么信息，而不是告诉如何获得信息。

SQL的基本知识是易于掌握的。

SQL使得人们能够控制信息。

SQL允许人们使用新的方式来处理信息。

SQL通过向需要的人们提供信息来使信息变得强大。

2. 什么是关系数据库？为什么要使用它？

关系数据库是一种在计算机中用于组织数据的方法。

SQL是使人们将数据组织到关系数据库中的一个主要原因，使用SQL，你可以毫不费劲地从数据中获得信息，这是非常重要的。

另外一个原因是：许多人可以在同一时间使用关系数据库中的数据。有时，几百或者几千人可以一起共享数据库中的数据。所有人都可以看见数据，并且所有人都可以更改数据（如果他们有这样的权限的话）。从商业观点来看，这提供了一个协调所有员工的方法，并且让每一个人都使用相同的信息主体。

第三个原因是：关系数据库是为希望信息可以随时间进行更改而设计的。信息可能需要重新组织或者新的信息需要被添加，关系数据库的设计目标是为了使这种类型的更改变得容易。大多数计算机系统很难更改。因为它们假设你在开始构造之前就知道所有的请求。

从计算机语言的观点来看，关系数据库的灵活性和SQL的可用性使得开发新的计算机应用程序要比传统技术更为迅速。

开发关系数据库的想法出现于20世纪70年代早期，用来处理大量数据和数以百万计的纪录。最初，关系数据库被想象成后端处理器，它向使用过程性语言（如C语言或COBOL语言）编写的计算机应用程序提供信息。即使到现在，关系数据库还遗留着这些痕迹。

不过今天，这种想法变得如此成功，以致于整个信息系统常常被构造为关系数据库，并且无需很多过程代码（除了支持输入格式）。最初被开发为过程性代码做配角的关系数据库现在已经唱了主角，许多过程性代码已经不再需要。

在关系数据库中，所有数据都保存在表里，表是由列和行组成的二维结构。在使用了一段时间的表以后，你会发现表为处理数据提供了一个非常好的结构。它们易于更改，可以在同一时间与所有用户共享数据，并且可以在表数据上运行SQL。许多人开始从表的角度考虑他们的数据，表已经成为处理数据时的主要手段。

今天，人们使用小的个人数据库来保护地址簿、为音乐磁带做目录、组织藏书或者纪录他们的财务情况。商业上应用的数据库也被建立为关系数据库。许多人更愿意将他们的数据存入一个数据库中，即使其中只有少量的记录。

关系数据库的起源

关系数据库最初是在20世纪70年代开发的，开发目的是为了以一致并且相关的方式来组织大量信息。

关系数据库允许几千人在相同时间使用相同信息。

关系数据库总是保持信息的实时性和一致性。

关系数据库使单位中所有级别的人（从秘书到副总裁）都可以容易地获得信息。它们使用SQL、表单、标准化的报告和临时报告，及时地将信

息传递给人们。

关系数据库是作为信息服务器后端设计的。这意味着大部分人将不会直接使用数据库，而用另一层的软件。这个软件从数据库中获得信息，然

后传递给需要的人们。

关系数据库使人们能够在需要信息的时候获得当前信息。

今天——关系数据库任何改变

除了已经描述的大型数据库之外，现在还有许多处理较少信息量的小型数据库，它们可以被个人使用或几个人分享。

关系数据库非常成功，易于使用，使用它的应用程序要比原先设想的应用范围大许多。

现在许多人直接使用数据库，而不是通过另一个软件层来使用。

许多人更愿意将他们的数据保存在数据库中。他们觉得关系数据库为处理各种类型的数据提供了一个实用且高效的框架。

3. 为什么学习SQL？

SQL被运用于100多种软件产品中。一旦学会了SQL，你将能够使用所有这些产品。当然，你需要稍微了解一下每种产品的特性，很快你就会感觉到它们很熟悉并且知道如何使用。你可重复使用这些技巧。

主要SQL产品 其他SQL产品（和基于SQL的产品）

Oracle MYSQL

Microsoft Access SQLbase

IBM DB2 Cold Fusion

Microsoft SQL Server SAP

Informix Business Objects

SQL Windows ODBC

Sybase Ingres

SAS sql procedure Ocelot SQL

Foxpro OsloData

dBase PostgreSQL

Tandem SQL Rapid SQL

XDB

SQL/DS

Mini SQL

Empress

Interbase

Progress

Supra

SQL Report Writer

Paradox

Delphi

VAX SQL

Essbase

Beagle SQL

GNU SQL Server

Just Logic/SQL

PrimeBase

Altera SQL Server

DataScope

PowerBuilder

SQL被广泛使用的原因之一是：相对于其他许多计算机语言来说，SQL易于学习。另一个原因是：它打开了关系数据库的大门，而关系数据库提供了许多便利。一些人说：SQL是关系数据库最好的特性，并且SQL是关系数据库获得成功的原因。

而另一些人说：是关系数据库使得SQL成功。大部分人同意SQL和关系数据库是一个成功组合的观点。

SQL是最成功的说明性计算机语言——一种你可以对它说你想要什么，而不是告诉它如何得到你想要东西的语言。虽然也有其他一些说明性语言和报告生成工具，但它们大部分在功能上有更多的限制。SQL更强大，可以应用于更多的场合。

在SQL基础上易于构建最终用户程序，帮助不懂SQL语言的用户从数据库中获得信息。

4. SQL语句的分类

SQL使用一种很简单的语法，易于学习和使用。SQL语句可分为5类，简要概括如下：

● 查询语句 用于检索数据库表中存储的行。可以使用SQL的SELECT语句

编写查询语句。

● 数据操纵语言(Data Manipulation Language, DML)语句 用于修改表

的内容。DML语句有3种：

· INSERT 向表中添加行。

· UPDATE 修改行的内容。

· DELETE 删除行。

● 数据定义语言(Data Definition Language, DDL)语句 用于定义构成

数据库的数据结构，例如表。DDL语句有5种基本类型：

· CREATE 创建数据库结构。例如，CREATE TABLE 语句用于创建一个表；CREATE USER 用于创建一个数据库用户。

· ALTER 修改数据库结构。例如，ALTER TABLE 语句用于修改一个表。

· DROP 删除数据库结构。例如，DROP TABLE 语句用于删除表。

· RENAME 更改表名。

· TRUNCATE 删除表的全部内容。

● 事务控制(Transaction Control, TC)语句 用于将对行所作的修改永

久性地存储到表中，或者取消这些修改操作。TC语句有3种：

· COMMIT 永久性地保存对行所作的修改。

· ROLLBACK 取消对行所作的修改。

· SAVEBACK 设置一个“保存点”，可以将对行所作的修改回滚到此处。

● 数据控制语言(Data Control Language, DCL)语句 用于修改数据库结

构的操作权限。DCL 语句有2种：

· GRANT 授予用户对数据库结构（例如表）的访问权限。

· REVOKE 收回用户对数据库结构（例如表）的访问权限。

有很多方法都可以运行SQL语句，并从数据库中返回结果，其中包括使用Oracle Forms、Delphi、PowerBuilder等设计的程序；还可以通过JDBC在Java

程序中加入SQL语句。

Oracle还有一个名为SQL*Plus的工具，可以使用这个工具从键盘输入SQL语句，或者提供一个包含SQL语句的文件，并在SQL*Plus中运行这些语句。通过SQL*Plus，可以与数据库进行“对话”，因为可以输入SQL语句，并查看数据库所返回的结果。

5. 使用SQL*Plus

SQL*Plus有两个版本：一个是Windows版本，另外一个是命令行版本。可以在任何Oracle数据库的操作系统上使用命令行版本的SQL*Plus。

● 启动Windows版本的SQL*Plus

如果使用Windows操作系统，双击桌面的SQL*Plus图标，或单击 开始 菜单，并选择 程序 | Oracle | Application

Development | SQL*Plus 来启动SQL*Plus（如图 1 ）。

图1 从

程序菜单启动SQL*Plus

图 2 显示了在Windows操作系统上运行SQL*Plus时出现的Log On对话框。在用户名称(User Name)中输入：s30101，在口令(Password)中输入：student，主机字符串(Host String) 输入：oracle9i 用来告诉SQL*Plus数据库在哪里运行，然后单击 确定。

图 2 SQL*Plus登录窗口

成功登录到数据库中之后，就会看到SQL*Plus窗口，可以通过这个窗口与数据库进行交互。SQL*Plus窗口如图 3 所示。

图 3 SQL*Plus

● 启动命令行版本的SQL*Plus

要启动命令行的SQL*Plus，可以使用sqlplus命令。sqlplus命令的完整语法如下：

Sqlplus [user_name[/password[@host_sting]]]

其中：

· user_name 指定数据库的用户名。

· password 指定该数据库用户的密码。

· host_string 指定要连接的数据库。

下面是执行sqlplus命令的例子：

sqlplus s30101/student@oracle9i

● 使用SQL*Plus执行SELECT 语句

使用SQL*Plus登录到数据库之后，输入下面的SELECT语句，会返回数据库中的当前时间：

SELECT SYSDATE FROM dual;

SYSDATA 是一个内置Oracle 函数，它返回当前日期，dual 表是Oracle的一个内置表，该表只包含一行可以使用dual表来执行一些简单的查询，这些查询的结果并非从特定表中获得。

SQL> SELECT sysdate

2 FROM dual;

SYSDATE

---------

27-SEP-05

SQL>

通过输入EDIT命令，可以编辑SQL*Plus中的最后一条SQL语句。在输错SQL

语句或想修改SQL语句时，这种功能非常有用。在Windows系统中输入EDIT命令后，就会启动记事本，然后就可以使用记事本来编辑SQL语句。在退出记事本并保存SQL语句时，SQL语句就会被传递到SQL*Plus中，可以用/重新执行该条SQL语句。

6. 用于示例的表 (EMP和 DEPT)

后面课程中在介绍SQL语句时所引用的都是以下两个表。

EMP表(14条记录)，存放员工的基本信息。

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 790217-Dec-80100020 7499 ALLEN SALESMAN 769820-Feb-811800300 30 7521 WARD SALESMAN 769822-Feb-811450500 30 7566 JONES MANAGER 783902-Apr-81317520 7654 MARTIN SALESMAN 769828-Sep-8114501400 30 7698 BLAKE MANAGER 783901-May-81305030 7782 CLARK MANAGER 783909-Jun-81265010 7788 SCOTT ANALYST 756609-Dec-82320020 7839 KING PRESIDENT 17-Nov-81520010 7844 TURNER SALESMAN 769808-Sep-8117000 30 7876 ADAMS CLERK 778812-Jan-83130020 7900 JAMES CLERK 769803-Dec-81115030 7902 FORD ANALYST 756603-Dec-81320020 7934 MILLER CLERK 778223-Jan-82150010

DEPT表 (4条记录)，存放部门的信息

DEPTNO DNAME LOC

YORK

10 ACCOUNTING NEW

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

7. 退出SQL*Plus

可以使用EXIT命令退出SQL*Plus。

SQL> EXIT

8. 基本的SQL语句

SQL> SELECT job, SUM(sal) PAYROLL

2 FROM emp

3 WHERE job NOT LIKE 'SALES%'

4 GROUP BY job

5 HAVING SUM(sal)>5000

6 ORDER BY SUM(sal);

SQL语句的简单规则

?除非特别说明，SQL语句不区分大小写

?SQL语句可以在一行或多行输入

?关键词不能跨行分开或缩写

?为了方便阅读或编辑，语句通常被分为若干行

?Tab和缩排用来增加代码的可读性

?通常关键词用大写；其他的词，如表名和列名用小写

?在SQL*Plus里，在SQL提示后输入第一条SQL语句，而随后的行被编号。这叫做SQL buffer。

无论何时在buffer里当前的语句只有一条

?在语句的结尾用分号(;)。

排除重复的行

SQL> SELECT DISTINCT deptno, job

2 FROM emp;

DEPTNO JOB

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

10 CLERK

10 MANAGER

10 PRESIDENT

20 ANALYST

...

9 rows selected.

SQL和SQL*Plus的比较

SQL SQL*Plus

是一种语言，用来和服务器通信存取数据的识别SQL语句，并把它们发送给服务器

是Oracle为了执行SQL语句提供的接口

是建立在美国国家标准协会 (ANSI)的标准SQL

之上的

可以操纵定义在数据库里的数据和基表不允许操纵存储在数据库里的数据的值

可以输入一行或多行存储在SQL buffer里一次只输入一行；不存储在SQL buffer里

不能有连接符如果命令超过一行可以用破折号(-)作为连接符不能缩写可以缩写

在使用终止符后将立刻执行不需要终止符；命令会立刻执行

可以用函数来处理数据格式的工作用命令来改变数据的格式

9. Where, Order by子句

IS NULL操作

SQL> SELECT ename, mgr

2 FROM emp

3 WHERE mgr IS NULL;

ENAME MGR

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

KING

BETWEEN操作

SQL> SELECT ename, sal

2 FROM emp

3 WHERE sal BETWEEN 1000 AND 1500;

ENAME SAL

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

MARTIN 1250

TURNER 1500

WARD 1250

ADAMS 1100

MILLER 1300

IN操作

SQL> SELECT empno, ename, sal, mgr

2 FROM emp

3 WHERE mgr IN (7902, 7566, 7788);

EMPNO ENAME SAL MGR

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

7902 FORD 3000 7566 7369 SMITH 800 7902 7788 SCOTT 3000 7566 7876 ADAMS 1100 7788 LIKE操作

SQL> SELECT ename

2 FROM emp

3 WHERE ename LIKE '_A%';

ENAME

----------

JAMES

WARD

NOT操作

SQL> SELECT ename, job

2 FROM emp

3 WHERE job NOT IN ('CLERK','MANAGER','ANALYST');

ENAME JOB

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

KING PRESIDENT

MARTIN SALESMAN

ALLEN SALESMAN

TURNER SALESMAN

WARD SALESMAN

按多列进行排序

SQL> SELECT ename, deptno, sal

2 FROM emp

3 ORDER BY deptno, sal DESC;

ENAME DEPTNO SAL

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

KING 10 5000

CLARK 10 2450

MILLER 10 1300

FORD 20 3000

...

14 rows selected.

10. SQL函数

单行函数

?操纵数据项

?接受变量并返回一个值

?对每一行的返回值起作用

?每行返回一个结果

?改变数据类型

?能够被嵌套

函数结果

LOWER(‘Oracle Education’) oracle education

UPPER(‘Oracle Education’) ORACLE EDUCATION

INITCAP(‘ORACLE education’) Oracle Education

CONCAT(‘Certified’, ‘DBA’) CertifiedDBA

SUBSTR(‘Administrator’, 1, 5) Admin

LENGTH(‘Administrator’) 13

INSTR(‘Oracle’, ‘c’) 4

LPAD(sal, 10, ‘*’) ******5000

ROUND(78.926, 2) 78.93

TRUNC(78.926, 2) 78.92

MOD(2100, 500) 100

SQL> SELECT 'The job title for '||INITCAP(ename)||' is '

2 ||LOWER(job) AS "EMPLOYEE DETAILS"

3 FROM emp;

EMPLOYEE DETAILS

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

The job title for King is president

The job title for Blake is manager

The job title for Clark is manager

...

14 rows selected.

Date函数

函数结果说明

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') 19.6774194 计算两个日期之间的月数ADD_MONTHS ('11-JAN-94',6) '11-JUL-94' 计算指定日期后若干月的日期NEXT_DAY ('01-SEP-95','FRIDAY') '08-SEP-95' 计算指定日期后下一天的日期LAST_DAY('01-SEP-95') '30-SEP-95'

计算指定月份最后一天的日期ROUND('25-JUL-95','MONTH') 01-AUG-95 舍入日期

ROUND('25-JUL-95','YEAR') 01-JAN-96

TRUNC('25-JUL-95','MONTH') 01-JUL-95 截取日期

TRUNC('25-JUL-95','YEAR') 01-JAN-95

SQL> SELECT empno, hiredate,

2 MONTHS_BETWEEN(SYSDATE, hiredate) TENURE,

3 ADD_MONTHS(hiredate, 6) REVIEW,

4 NEXT_DAY(hiredate, 'FRIDAY'), LAST_DAY(hiredate)

5 FROM emp

6 WHERE MONTHS_BETWEEN (SYSDATE, hiredate)<200;

EMPNO HIREDATE TENURE REVIEW NEXT_DAY LAST_DAY

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

7839 17-NOV-81 192.24794 17-MAY-82 20-NOV-81 30-NOV-81

7698 01-MAY-81 198.76407 01-NOV-81 08-MAY-81 31-MAY-81

...

11 rows selected.

带日期的TO_CHAR函数

要素说明

SCC or CC 世纪；以S为前缀，用-表示公元前的日期

Years in dates YYYY or SYYYY 年；以S为前缀，用-表示公元前的日期

YYY or YY or Y 年的后3、2或1位数字

Y,YYY 在这个位置加逗号的年

IYYY, IYY, IY, I 以ISO标准为基础的 4, 3, 2, 或 1 位数字的年

SYEAR or YEAR 完整拼写的年；以S为前缀，用-表示公元前的日期

BC or AD 加上BC/AD指示

B.C. or A.D. 加上带句点的BC/AD指示

Q 四分之一年

MM 2位数字的月

MONTH 用9个字符长度（不够用空格填充）拼写的月

MON 以三个字母的缩写表示的月

RM 罗马数字表示的月

WW or W 年或月的星期

DDD or DD or D 年、月或星期的某日

DAY 用9个字符长度（不够用空格填充）拼写的日

DY 以三个字母的缩写表示的日

J 侏略日；从公元前4713年12月31日开始计算

AM or PM 加上上下午指示

A.M. or P.M. 加上带句点的上下午指示

HH or HH12 or HH24 时、时 (1–12)或时(0–23)

MI 分 (0–59)

SS 秒 (0–59)

SSSSS 午夜之后的秒数 (0–86399)

TH 序数(例如， DDTH for 4TH)

SP 完整拼写的数字(例如， DDSP for FOUR)

SPTH or THSP 完整拼写的序数(例如, DDSPTH for FOURTH)

fm 清除空格或打头的零

SQL> SELECT ename,

2 TO_CHAR(hiredate, 'fmDD Month YYYY') HIREDATE

3 FROM emp;

ENAME HIREDATE

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

KING 17 November 1981

BLAKE 1 May 1981

CLARK 9 June 1981

JONES 2 April 1981

MARTIN 28 September 1981

ALLEN 20 February 1981

...

14 rows selected.

SQL> SELECT ename,

2 TO_CHAR(hiredate, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM')

3 HIREDATE

4 FROM emp;

ENAME HIREDATE

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

KING Seventeenth of November 1981 12:00:00 AM

BLAKE First of May 1981 12:00:00 AM

...

14 rows selected.

带数字的TO_CHAR函数

要素说明例子结果

9 数字的位数 (9的数目决定显示的宽度) 999999 1234 0 首位加零显示099999 001234 $ 浮动的美元符号$999999 $1234 L 浮动的当地货币符号L999999 FF1234 . 小数点的位置999999.99 1234.00 , 逗号的位置999,999 1,234 MI 减号置于右边 (负数999999MI 1234-

PR 作为插入成分插入的负数999999PR <1234> EEEE 科学计数法 (格式必须是4个E) 99.999EEEE 1.234E+03 V 10的n次幂 (n = V后9的个数) 9999V99 123400 B 显示零值为空格，而不是0 B9999.99 1234.00

SQL> SELECT TO_CHAR(sal,'$99,999') SALARY

2 FROM emp

3 WHERE ename = 'SCOTT';

SALARY

--------

$3,000

NVL函数

?将空值转换为一个既定的值

?数据类型可以是日期、字符和数字

?数据类型一定要匹配

?NVL(comm,0)

?NVL(hiredate,'01-JAN-97')

?NVL(job,'No Job Yet')

SQL> SELECT ename, sal, comm, (sal*12)+comm

2 FROM emp;

ENAME JOB (SAL*12)+COMM

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

KING PRESIDENT

BLAKE MANAGER

CLARK MANAGER

JONES MANAGER

MARTIN SALESMAN 16400

...

14 rows selected.

2 FROM emp;

ENAME SAL COMM (SAL*12)+NVL(COMM,0) ---------- --------- --------- -------------------- KING 5000 60000 BLAKE 2850 34200 CLARK 2450 29400 JONES 2975 35700 MARTIN 1250 1400 16400 ALLEN 1600 300 19500 ...

14 rows selected.

DECODE函数

SQL> SELECT job, sal,

2 DECODE(job, 'ANALYST', SAL*1.1,

3 'CLERK', SAL*1.15,

4 'MANAGER', SAL*1.20,

5 SAL)

6 REVISED_SALARY

7 FROM emp;

JOB SAL REVISED_SALARY

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

PRESIDENT 5000 5000

MANAGER 2850 3420

MANAGER 2450 2940

...

14 rows selected.

Nesting函数

SQL> SELECT ename,

2 NVL(TO_CHAR(mgr),'No Manager')

3 FROM emp

4 WHERE mgr IS NULL;

ENAME NVL(TO_CHAR(MGR),'NOMANAGER')

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

KING No Manager

11. 表的联接

等值联接

SQL> SELECT emp.empno, emp.ename, emp.deptno,

2 dept.deptno, dept.loc

3 FROM emp, dept

4 WHERE emp.deptno=dept.deptno;

EMPNO ENAME DEPTNO DEPTNO LOC

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

7839 KING 10 10 NEW YORK

7698 BLAKE 30 30 CHICAGO

7782 CLARK 10 10 NEW YORK

7566 JONES 20 20 DALLAS

...

14 rows selected.

非等值联接

SALGRADE表

GRADE LOSAL HISAL

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

1200

1 700

1400

2 1201

2000

3 1401

4 2001

3000

9999

5 3001

2 FROM emp e, salgrade s

3 WHERE e.sal

4 BETWEEN s.losal AND s.hisal;

ENAME SAL GRADE

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

JAMES 950 1

SMITH 800 1

ADAMS 1100 1

...

14 rows selected.

外联接

SQL> SELECT e.ename, d.deptno, d.dname

2 FROM emp e, dept d

3 WHERE e.deptno(+) = d.deptno

4 ORDER BY e.deptno;

ENAME DEPTNO DNAME

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

KING 10 ACCOUNTING

CLARK 10 ACCOUNTING

...

40 OPERATIONS

15 rows selected.

自联接

SQL> SELECT worker.ename||' works for '||manager.ename

2 FROM emp worker, emp manager

3 WHERE worker.mgr = manager.empno;

WORKER.ENAME||'WORKSFOR'||MANAG

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

BLAKE works for KING

CLARK works for KING

JONES works for KING

MARTIN works for BLAKE

...

13 rows selected.

使用表的别名

SQL> SELECT e.empno, e.ename, e.deptno,

2 d.deptno, d.loc

3 FROM emp e, dept d

4 WHERE e.deptno=d.deptno;

12. 分组函数

分组函数的类型

函数说明AVG([DISTINCT|ALL]n) 计算n的平均值，忽略空值

COUNT({*|[DISTINCT|ALL]表达式}) 统计行数，如果有表达式，则统计空值以外的行数。用*将选择统计所有的行，包括重复的和空的行。

MAX([DISTINCT|ALL] 表达式) 取表达式的最大值，忽略空值MIN([DISTINCT|ALL] 表达式) 取表达式的最小值，忽略空值STDDEV([DISTINCT|ALL]x) 计算n的均方差，忽略空值

SUM([DISTINCT|ALL]n) 计算n的和，忽略空值

VARIANCE([DISTINCT|ALL]x) 计算n的方差，忽略空值SQL> SELECT AVG(sal), MAX(sal),

2 MIN(sal), SUM(sal)

3 FROM emp

4 WHERE job LIKE 'SALES%';

AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)

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

1400 1600 1250 5600

2 FROM emp

3 WHERE deptno = 30;

COUNT(*)

---------

6

GROUP BY子句

SQL> SELECT deptno, AVG(sal)

2 FROM emp

3 GROUP BY deptno;

DEPTNO AVG(SAL)

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

10 2916.6667

20 2175

30 1566.6667

2 FROM emp

3GROUP BY deptno, job;

DEPTNO JOB SUM(SAL)

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

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 ANALYST 6000

20 CLERK 1900

...

9 rows selected.

使用分组功能而导致的非法查询

SQL> SELECT deptno, COUNT(ename)

2 FROM emp;

SELECT deptno, COUNT(ename)

*

ERROR at line 1:

ORA-00937: not a single-group group function SQL> SELECT deptno, AVG(sal)

2 FROM emp

3 WHERE AVG(sal) > 2000

4 GROUP BY deptno;

WHERE AVG(sal) > 2000

*

ERROR at line 3:

ORA-00934: group function is not allowed here

HAVING子句

SQL> SELECT deptno, max(sal)

2 FROM emp

3 GROUP BY deptno

4 HAVING max(sal)>2900;

DEPTNO MAX(SAL)

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

10 5000

20 3000