ORACLESQL语句学习教程解读.docx

ORACLESQL语句学习教程解读.docx

《ORACLESQL语句学习教程解读.docx》由会员分享，可在线阅读，更多相关《ORACLESQL语句学习教程解读.docx（69页珍藏版）》请在冰点文库上搜索。

ORACLESQL语句学习教程解读

目录

概述2

第一章SQL*PLUS工具软件的使用2

第二章基本查询2

第三章条件查询2

第四章单行函数2

第五章多表查询2

第六章组函数2

第七章子查询2

第八章运行期间指定变量2

第九章创建基表2

第十章ORACLE数据字典2

第十一章操纵数据2

第十二章修改基表及其约束2

第十三章创建序列2

第十四章创建视图2

第十五章创建索引2

概述

20世纪70年代初，E.F.Codd在计算机学会（AssociationofComputerMachinery,简写为ACM）期刊CommunicationsoftheACM（ACM通讯）发表了题为”ARelationalModelofDataforLargeSharedDataBanks”（大型共享数据库的数据关系模型）的论文，该论文提出的关系数据库模型成为今天最为权威的关系型数据库管理模型。

IBM公司首先使用该模型开发出了结构化英语查询语言SEQUEL（StructuredEnglishQueryLanguage），作为其关系数据库原型SystemR的操作语言，实现对关系数据库的信息检索。

SEQUEL后来简写为SQL，即StructuredQueryLanguage（结构化查询语言）的缩写。

ORACLE公司于1997年推出了第一个商业应用的SQL软件。

20世纪80年代初，美国国家标准化组织（ANSI）开始着手制订SQL标准，最早的ANSI标准于1986年颁布，它也被称为SQL-86。

标准的出台使SQL作为标准的关系数据库语言的地位得到加强。

SQL标准几经修改和完善，目前SQL语言方面新的ANSI标准是1992年制定的ANSIX3.135-1992，“DatabaseLanguageSQL”。

此标准也被国际电工委员会（InternationalElectrotechnicalCommission,即IEC）所属的国际标准化组织（InternationalStandardsOrganization,即ISO）所接受，并将它命名为ISO/IEC9075:

1992,“DatabaseLanguageSQL”。

这两个标准又被简称为SQL-92。

SQL是目前使用最广泛的数据库语言，就象SQL的名字一样，我们可以通过容易理解的查询语言，来和数据库打交道，从数据库中得到我们想要的数据。

对于SQL语言，由下列四个组成部分：

DML（DataManipulationLanguage）：

它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。

DDL（DataDefinitionLanguage）：

DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DCL（DataControlLanguage）：

包括GRANT，REVOKE，主要用于对用户权限的授权和回收。

DatabaseTransactions：

包括COMMIT,ROLLBACK,SAVEPOINT，主要用于对事务的提交，回收和设置保存点。

了解这四个部分的不同对于你今后使用时有很多方便，因为许多SQL语言中的约束也是根据这个分类来划分的，比如：

你在ORACLE中建立数据库链，那么只有DML类语言能对链接的数据库进行操作。

SQL作为一个ANSI标准，一直在不断改进，现在最新的标准是SQL99。

ORACLE不但对标准的SQL完全兼容，而且有自己更为方便的增强SQL，所以这里介绍的主要是ORACLE8i中使用的SQL语句。

ORACLE的今天

今天，座落在加利福尼亚RedwoodShores的ORACLE公司为电子信息管理提供软件产品和服务。

ORACLE是一个世界范围的软件提供者，1999年的收入将超过80亿美元。

ORACLE的业务遍及世界90多个国家，其软件在100多种不同的计算机上运行，在信息高速公路中扮演着一个重要的角色。

ORACLE是采用Internet计算模式的倡导者之一，在该模式下，组织者为分布式应用配置了万维网。

Internet是发布应用的理想基础设施。

在给定的组件为基础的模型中，Internet是将分散应用集成起来的理想场所，比较三层客户/服务器计算体系而言，Internet也是这些系统的理想场所。

构成此模型的三层为：

●客户层有时称为表达层，既显示信息给用户，同时接收输入的要处理的信息。

●应用层所有的处理都将按系统实现的逻辑在该层进行。

商业规则的加强、数据一致性的检查系统所需的复杂处理都在该层进行。

此层是三层模型中处理事务最多的一层。

●数据层通常称为backend，是为满足其它两层的需求而存储信息。

在很多情况下，在用户同系统交互时，关系数据库将优化从应用层接收到的以及返回给应用层的信息。

ORACLE集团不遗余力地将最新的技术提供给客户。

对未来技术发展趋势预见能力以及较早地拥有关键技术，不仅使得ORACLE得以生存，更使其在竞争中遥遥领先。

ORACLEServer是这一切的基石。

接下来看一下ORACLE8iServer以及是如何围绕它开发出一系列产品的。

为什么ORACLE能取得现在的地位

ORACLE的很多显著特征使得它走在日益发展的信息管理群体前列，信息技术不是一成不变的，接下来看一下其中的（但决非全部的）特性：

1.决策支持系统

决策支持系统即DSS，扮演着一个重要角色。

到处可以看到组织者发布以ORACLEServer为基础的数据仓库的种种信息。

对数据仓库的兴趣及其使用的与日俱增使得ORACLEServer及其相关的联机分析处理（OLAP）产品成为管理的首选。

随着Express及ORACLEDiscoverer等决策支持工具的开发，ORACLE已经不管在现在,还是在将来都确立了其在该领域的地位。

2.海量数据管理

数据库系统操作的硬件瞬息万变，去处理器或磁盘市场购买最快和功率最强的CPU，刚购买到，技术又进步了。

ORACLE一直对海量数据的管理非常重视，在版本7.3（circa1997）中为此采用了数据分区的办法。

采用数据分区后，海量数据分成很多可管理的块，当系统操作或用户会话处理查询时又能透明地将分块的数据组织起来。

3.保密机制

ORACLE的高级保密机制通过各种各样的特权，控制对敏感数据的存取。

用户根据连接到数据库的名称被赋予各种特权，如查看、修改和创建数据库等等。

用这些机制来保证某些用户能查看敏感数据，而有的用户被禁止。

4.备份与恢复

ORACLE提供了高级备份和恢复的子例程。

备份创建ORACLE数据的一个副本，恢复把备份的数据恢复出来。

ORACLE的备份和恢复把数据丢失的可能性降到最小，并使出现故障时的排错时间最少。

ORACLE的服务器也提供了备份和恢复的机制，允许每天、每周、每年不间断地访问数据。

5.空间管理

ORACLE提供了灵活的空间管理。

用户可以为存放数据分配所需磁盘空间，也可以通过指示ORACLE为以后的需求留下多少空间来控制后继的分配。

还有一系列为大型的数据库考虑而设计的特殊功能。

事实上，在ORACLE8和ORACLE7.3中许多功能都是为数据仓库的考虑而设计的。

从设计角度来说，数据仓库是典型的非常大的数据库。

6.开放式联接

ORACLE提供和其他软件联接的开放式接口。

使用ORACLEAccessManager，用户很容易就能将别的软件商开发的软件所运行的系统集成起来。

例如，使用IBM的AS/400平台的管理器，用户在应用中采用如COBOL和C的第三代、第四代语言就能透明地访问ORACLE数据，也支持PL/SQL，从用户的AS/400应用程序中可以调用远程的ORACLE存储过程。

使用AccessManager配之以ORACLE的透明网关，企业管理者就可以保护其已经在IBM软硬件上的投资。

AccessManager驻留在非ORACLE数据库的机器上，用户数据由所在的操作系统决定，工业标准SQL在下列方面支持ORACLE数据库：

●DDL即数据定义语言语法适用于对ORACLE数据库对象的定义（如CREATETABLE或CREATEINDEX）、修改一个或多个用户的特权（如GRANTSELECTON）或操纵支持ORACLE8i的基础组件（如ALTERTABLESPACE）。

●DML即数据操纵语言用于产生新的数据（如INSERTINTO）、处理已存在的数据（如UPDATE）、删除已存在的行（如DELETE）、或者是用非常熟悉的SELECT关键字简单地查看数据。

AccessManager能便捷地从ComputerAssociate的IDMS、Datacom、Ingres、Microsoft的SQLServer、Informix、Teradata的EDA/SQL、Sybase和IBM的CICS中访问数据。

7.开发工具

ORACLEServer通常指数据库引擎，支持一系列开发工具、终端用户查询工具、流行的应用以及办公范围内的信息管理工具。

ORACLEForm和ORACLEreport是ORACLE提供开发工具的核心，与Web相连进行发布并利用Internet计算的三层体系结构。

ORACLE企业开发套件中捆绑了一些组件，使得发布灵活、操作性强、易于维护，很容易开发出不同层次的应用。

套件中有如下四个主要组件：

●ORACLEDesigner用于定义系统元素（也就是数据源及其之间的关系），生成应用和定义数据库。

●ORACLEDeveloper是一个快速应用开发环境，用于建立交互应用、事务处理或联机事务处理为基础的系统。

●ORACLEDeveloperServer是一个强壮的为多层次提供的开发环境。

●ORACLEApplicationServer是一个公开的解决方案，它是为分布式事务应用处理而设计的。

ORACLE8i服务器

ORACLE8i服务器是一个完善的信息管理环境。

它是一个大量数据的储藏所，并给用户提供对这些数据的快速访问。

ORACLE8i服务器允许应用系统之间共享数据。

信息存放在一个地方并由许多应用系统来使用。

ORACLE8I服务器可运行在Sun系列以及WindowsNT上。

ORACLE8i服务器运行在很多不同的计算机上，支持下列配置：

●基于主机的配置用户直接连到存放数据库的同一计算机上。

●客户机/服务器结构用户通过网络从他们的个人计算机（客户机）上访问数据库，数据库

驻留在一个分离的计算机（服务器）上。

●分布式处理用户访问存放在不止一台计算机上的数据库。

数据库分散在不止一台机器

上，用户并不需要了解被存取数据的实际存放位置。

●Web计算（WebEnabledComputing）能从基于Internet的应用访问数据。

ORACLE服务器已经有助于使ORACLE公司成为成功的顶尖信息软件供应商，而ORACLE8i/9I将继续保持这一优势。

第一章SQL*PLUS工具软件的使用

SQL*PLUS是ORACLE数据库管理员和普通用户最常用的实用程序之一，它提供一个交互式SQL语句、PL/SQL语句块和SQL*PLUS命令的编辑、编译和执行环境。

普通用户使用它可以实现各种数据库操作，数据库管理员使用它除能够实现基本的数据库操作之外，还能够完成数据库启动、关闭和恢复等管理工作。

SQL、SQL*PLUS的工作流程

SQL*PLUS软件的主要功能包括：

●登录；

●描述表结构；

●执行SQL命令；

●编辑SQL命令；

●将SQL命令存入文件；

●执行文件中的SQL命令；

●将文件中的SQL命令调入缓存区；

●格式化输出结果。

登陆；

●图形方式：

点击SQL*PLUS图标，输入用户名，口令和主机字符串即可。

●命令行格式：

SQLPLUSusername/password@网络服务名

其中：

网络服务名包括：

主机名称（IP地址），网络访问协议，数据库名称。

SQLPLUSscott/tiger@ORACLE

●描述表结构

SELECT*FROMcat；显示用户可存取的所有基表清单。

DESCemp；描述基表EMP的结构。

●执行SQL命令

SELECT*FROMemp；显示基表EMP的所有数据记录。

●编辑SQL命令

EDIT；编辑存储在SQL*PLUS缓存区的内容，即最后一条SQL命令。

●将SQL命令存入文件

格式：

SAVEfilename

SAVEc:

\select_emp.sql;将存储在SQL*PLUS缓存区的内容，即最后一条SQL命令存储为磁盘文件。

●将文件中的SQL命令调入缓存区

格式：

EDITfilename

EDITc:

\select_emp.sql

●执行文件中的SQL命令

格式：

STARTfilename或@filename

STARTc:

\select_emp.sql

@c:

\select_emp.sql

●格式化输出结果。

设置列标题

COLenameHEADING‘Employee|Name’FORMATA10;

COLsalJUSTFYLEFTFORMAT$99,999.00;

COLhiredateFORMATA8NULL‘nothired’;

COLcommFORMAT$99,999.00NULL‘No’

字符和日期

An设置显示的宽度（n）.

数字

9Singlezero-suppressiondigit.

0强制为0.

$美元标识.

L当前的货币符号.

.小数点位.

千位分隔符.

●显示列的格式设置

COLename；

●清除列的格式设置

COLenameCLEAR；

●将输出结果存储为磁盘文件。

格式：

SPOOLfilename

SPOOLOFF

SPOOLc:

\emp.txt

SELECT*FROMempORDERBYdeptno;

SPOOLOFF

另外，ORACLE8I的SQL*PLUS工具含有帮助功能：

SQL〉HELP；

SQL〉HELPINDEX；显示所有SQL*PLUS可用命令。

SQL〉HELPSTARTUP；显示STARTUP的功能及用法。

SQL*PLUS系统常用命令介绍：

假设当前执行命令为：

SELECT*FROMtab;

●（A）PPEND　　　　添加文本到缓冲区当前行尾　　

SQL>Aorderbytname　

结果：

SELECT*FROMtabORDERBYtname;（注：

A后面跟2个空格）

●（C）HANGE/old/new在当前行用新的文本替换旧的文本

SQL>C/*/tname　

结果：

SELECTtnameFROMtab;

●（C）HANGE/text　　从当前行删除文本　

SQL>C/tab　　　　　　　结果：

SELECTtnameFROM;

●DEL　　　　　　　删除当前行

●DELn　　　　　　删除第n行

●（I）NPUT文本　　在当前行之后添加一行

●（L）IST　　　　　显示缓冲区中所有行

●（L）ISTn　　　　显示缓冲区中第n行

●（L）ISTmn　　　显示缓冲区中m到n行

●RUN　　　　　　　执行当前缓冲区的命令

●/　　　　　　　　执行当前缓冲区的命令

●R　　　　　　　　执行当前缓冲区的命令

●@文件名　　　　运行调入内存的sql文件，如：

●EDITs<回车>

如果当前目录下不存在s.sql文件，则系统自动生成s.sql文件，

在其中输入“select*fromtab;”，存盘退出。

●@s<回车>

系统会自动查询当前用户下的所有表、视图、同义词。

●@@文件名　　　　在.sql文件中调用另一个.sql文件时使用

●EXIT　　　　　　退出SQL*PLUS

●DESC表名　　　　显示表的结构

●CLEARSCREEN　清空当前屏幕显示

SQL*PLUS系统环境变量

SHOW和SET命令是两条用于维护SQL*PLUS系统变量的命令

SHOWALL

查看所有68个系统变量值

SHOWUSER

显示当前连接用户

SHOWERROR

显示错误信息

SETHEADINGOFF

禁止输出列标题，默认值为ON

SETFEEDBACKOFF

禁止显示最后一行的计数反馈信息，默认值为"对6个或更多的记录，回送ON"

SETTIMINGON

默认为OFF，设置查询耗时，可用来估计SQL语句的执行时间，测试性能

SETSQLPROMPT"SQL>"

设置默认提示符，默认值就是"SQL>"

SETLINESIZE1000

设置屏幕显示行宽，默认80

SETAUTOCOMMITON

设置是否自动提交，默认为OFF

SETPAUSEON

默认为OFF，设置暂停，会使屏幕显示停止，等待按下ENTER键再显示下一页

SETARRAYSIZE1

默认为15

SETLONG1000

默认为80

第二章基本查询

教学目标

●编写SELECT语句用于查询数据库数据；

●完成算术计算；

●处理NULL值；

●确定使用别名作为列的标题；

●连接列；

SELECT语法

SELECT[DISTINCT]{*,column[alias],…}

FROMtable;

●SELECT指明查询的列

●FROM指明查询的表

书写规则：

●命令可以写在一行或多行。

●可以使用缩进，提高可读性。

●关键字不能缩写或拆分。

●大小写不敏感。

●命令存入缓冲区。

最简单的查询语句包含如下两个子句：

●SELECT子句：

用*代表所有列

●FROM子句

例：

显示dept表的所有记录。

SELECT*FROMdept;

例：

显示emp表的所有记录。

SELECT*FROMemp;

查询指定列

●在SELECT子句中列出要查询的列，用，分隔。

查询结果中列的排列顺序由SELECT子句中位置而定。

SELECTempno,ename,sal,deptnoFROMemp;

SELECTdeptno,dnameFROMdept;

列标识的缺省对齐模式：

●日期、字符左对齐

●数字右对齐

●缺省的标识显示为大写.

算术表达式

●可以对数字、日期类型使用算术表达式。

●算术运算符包括加（+），减（-），乘（*），除（/）。

例：

显示每个雇员的年工资。

SELECTempno,ename,sal*12FROMemp;

操作符优先级：

●先乘除后加减。

●同级算符从左到右。

●（）改变优先次序。

SELECTename,12*sal+100FROMemp;

SELECTename,12*（sal+100）FROMemp;

使用别名改变列标识

●用于算术表达式。

●直接跟在列后面，可以在列后面使用关键字AS。

●若别名中由空格、特殊字符或大小写敏感，应使用“”。

SELECTempno,enamename,sal*12“AnnualSalary”FROMemp;

连接符（||）

●连接列或字符串。

●产生字符表达式。

SELECTename||’-----‘||sal||’-----‘||deptnoFROMemp;

空值处理

●NULL表示未定义、不可用且不占用存贮空间。

●NULL不是0或空格。

●包含null数学表达式也为NULL。

SELECTename,sal,comm,sal+commFROMemp;

NVL函数：

将NULL转换成其他值.

●可用于数字、日期、字符.

●数据类型必须匹配.

NVL（hiredate,'01-JAN-95'）

NVL（title,'NoTitleYet'）

NVL（salary,1000）

SELECTename,sal,comm,sal+NVL（comm,0）FROMemp;

去重（DISTINCT）

●缺省的查询结果显示所有记录（包括重复的值）；

SELECTdeptnoFROMemp;

●使用DISTINCT关键字去掉重复记录；

SELECTDISTINCTdeptnoFROMemp;

●DISTINCT可以用于SELECT列表中的所有的列。

●DISTINCT可以用于SELECT列表中的多个列,结果为列组合的去重。

例：

SELECTDISTINCTdeptno,jobFROMemp;

在数据库中，空值用来表示实际值未知或无意义的情况。

在一个表中，如果一行中的某列没有值，那么就称它为空值（NULL）。

任何数据类型的列,只要没有使用非空（NOTNULL）或主键（PRIMARYKEY）完整性限制，都可以出现空值。

在实际应用中，如果忽略空值的存在，将会造成造成不必要的麻烦。

例如，在雇员表（EMP）中，雇员名（ENAME）为KING的行，因为KING为最高官员（PRESIDENT），他没有主管（MGR），所以其MGR为空值。

因为不是所有的雇员都有手续费（COMM），所以列COMM允许有空值，除300、500、1400、0以外的其它各行COMM均为空值。

以EMP表为例，具体讨论一下空值在日常应用中所具有的一些特性。

空值的生成：

●如果一列没有非空（NOTNULL）完整性限制，那么其缺省的值为空值，即如果插入一行时未指定该列的值，则其值为空值。

●使用SQL语句INSERT插入行，凡未涉及到的列，其值为空值；涉及到的列，如果其值确实为空值，插入时可以用NULL来表示（对于字符型的列，也可以用''来表示）。

例：

插入一行，其EMPNO为1、ENAME为'JIA'、SAL为10000、job和comm为空值。

INSERTINTOemp（empno,ename,job,sal,comm）

VALUES（1,'JIA',NULL,1000,NULL）;

SELECT*FROMempWHEREempno=1;

可以看到新插入的一行，除job和comm为空值外，mgr、hiredate、deptno三列由于插入时未涉及，也为空值。

●使用SQL语句UPDATE来修改数据，空值可用NULL来表示（对于字符型的列，也可以用''来表示）。

UPDATEempsetename=NULL,sal=NULLWHEREempno=1;

空值的特点：

●等价于没有任何值。

●与0、空字符串或空格不同。

●在WHERE条件中,O