Oracle基础篇.docx

Oracle基础篇.docx

《Oracle基础篇.docx》由会员分享，可在线阅读，更多相关《Oracle基础篇.docx（51页珍藏版）》请在冰豆网上搜索。

Oracle基础篇

CSK内部培训－ORACLESQL基础fORACLESQL基础（version1.0）CSKSYSTEM应铭军2005年4月1CSK内部培训－ORACLESQL基础1.1.简介1.1.1预备知识1、基表ORACLE是一种关系型数据库系统，它以关系的方式组织数据，这里所说的关系，我们称之为基表。

一个数据库可以由若干个表组成，用户对数据库的操作也就是对表的操作。

ORACLE用基表的形式存储信息，如表1.1所示。

表1.1EMP表EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO736974997521756676547698SMITHALLENWARDJONESMARTINBLAKECLERKSLESMANSALESMANMANAGERSALESMANMANAGER79027698769878397698783917-Dec-8020-Feb-8122-Feb-812-Apr-8128-Sep-811-May-81800160012502975125028501400300500203030203030每个基表都有一个表名，以便识别。

每个基表都包含一个或一个以上的列，每列包含一类信息，并都有一个列名，以描述保存在那一列中的信息的种类。

表名和列名构成了基表的框架，也就是基表的分类信息。

具体信息一行一行存放，表的每一行表示一组独立的数据，它由具有不同列值的各列组成。

2、基表之间的关系在数据库中，数据与数据之间是存在着联系的，我们可以将某个基表中的信息与其他基表中的信息建立关系，例如，在前面的EMP表中，每个雇员均有部门编号（DEPTNO），它对应于DEPT表中的部门编号（DEPTNO），见表1.2。

表1.2DEPT表DEPTNO403020DNAMEOPERATIONSSALSERSESARCHLOCBOSTONCHICAGODALLAS10ACCOUNTINGNEWYORK这样可便于信息的独立组织，因为我们可以在EMP表中存放雇员信息，而无需在基中存放DEPT表中有关部门的信息。

当然，我们还可以在多个基表之间建立联系。

3、数据字典数据字典是ORACLE关系数据库管理系统（RDBMS）最重要的成分之一，是数据库系统的宝贵资源。

数据库管理员（DBA）使用它来监视ORALCERDBMS的使用，并协助用户完成他们的工作。

数据字典是由ORALCE自动建立并更新的一组基表和视图，在这里记录了用户的姓名、基表和视图的定义、用户权限以及数据存储等方面的信息。

数据字典中包括下列信息的描述：

（1）ORACLE所有用户的标识及用户的存取权限。

（2）每个用户所拥有的数据库实体（表、空间定义、视图、索引、聚簇和同义词）的定义。

（3）为数据库实体分配的空间情况。

我们可以通过查询语句查看数据字典中的有关内容。

例如：

用户需要查看当前用户创建的所有基表、视图和同义词清单，可以使用下列命令：

SQL>SELECT*FROMCAT；CAT表中存放的就是有关用户创建的基表、视图和同义词情况的描述。

数据字典由几个基础表及派生出来的视图组成。

基础表是由ORACLERDBMS自动更新，用户基本上不访问基础表，ORACLERDBMS不断地更新数据字典的基础表，而视图总是引用基础表中的最新数据。

一般来说，用户不能直接更新ORACLE的数据字典，因为用户操作不当、删除或修改了某些数据字典的内容，就会造成数据库的破坏。

1.1.2.结构查询语言SQLSQL语言是一种使用方便灵活的语言，因为它是一种非过程化程度相当高的语言，使用这种语言编程，用户只需在程序中指出要干什么，至于如何干，用户不必在程序中指出，而由系统来决定完成。

SQL语言可以通过两种方式使用，一种是命令方式，另一种是程序方式。

采用命令方式时，用户通过交互来执行该命令。

SQL语言还可以嵌入C，COBOL和FORTRAN等高级语言中，组成一个完整的程序。

用户可以根据不同需要选择不同的使用方式，以满足不同的要求。

2CSK内部培训－ORACLESQL基础SQL语言还提供了事务控制（commit、rollback、savepoint（其他：

locktable、setconstraint（s）、settransaction）），以保证数据共享以及并发使用时不产生干扰，也便于对数据库的恢复。

此外，SQL语言还提供了授权控制（grant、revoke（授权、回收）、setrole），以保证数据的安全与保密，防止非法用户对数据库的使用与破坏。

另外ORACLE中的SQL命令还有审计控制（audit、noaudit），系统控制（altersystem），会话控制（altersession）其他语句：

comment（添加注释）、explainplan、analyze、validate、call正因为SQL语言的这些特点，使它受到人们的广泛重视与欢迎。

近几年来，SQL语言已成为标准的数据库语言，现在已有一百多个数据库管理产品支持SQL语言。

SQL*PLUS是ORACLE的交互查询工具，它允许用户使用SQL命令交互式地访问数据库，也允许用户使用SQL*PLUS命令与系统发生联系。

1.1.3启动SQL*PLUS启动SQL*PLUS的步骤如下：

（1）将ORACLE软件安装到机器上。

（2）启动机器进行操作系统登录，成功后用户将在屏幕上看到操作系统的提示符，说明进入操作系统已经成功。

（3）启动ORACLE数据库系统，将ORACLERDBMS装入内存中，命令执行后，就可以运行工具软件了。

（4）在操作系统提示符下执行SQL*PLUS：

$SQLPLUS[RETURN]SQL*PLUS将显示它的版本号、日期和版权信息，并提示你输入用户名。

因为ORACLE保护对它所有数据的访问，所以与它连接通常需要一个用户标识（user-name）和口令（password）。

Enteruser-name：

输入用户名后按[RETURN]，SQL*PLUS提示你输入口令：

Enterpassword：

如果口令输入正确，SQL*PLUS将宣布你已经与ORACLE连接上了，然后显示：

SQL>说明现在你已经进入SQL*PLUS中，并等待你的指示。

如果输入的用户名或口令不正确，在向ORACLE三次注册失败后，屏幕上将出现终止服务信息，并退出SQL*PLUS。

在进入SQL*PLUS时，也可以在命令行上直接输入用户名和口令，中间以“/”分隔，比如，你的用户名是SCOTT，口令是TIGER，那么你输入下列命令：

$SQLPLUSSCOTT/TIGER就可以进入SQL*PLUS中。

当你想停止工作并离开SQL*PLUS，则可以输入EXIT或QUIT命令。

SQL>EXITSQL*PLUS命令SQL*PLUS可接收两种类型的命令：

SQL命令和SQL*PLUS命令。

SQL命令主要用来对数据库进行操作，而SQL*PLUS命令主要用来设置查询结果的显示格式，设置一些环境选项。

1、SQL命令SQL命令主要用于ORACLE数据库中创建、存储、改变、检索和维护信息。

当输入SQL命令时，SQL*PLUS将其保存在内部缓冲区中，这个内部缓冲区称为SQL缓冲区。

SQL缓冲区只保存一条SQL命令，当输入新的SQL命令时，原来保存的SQL命令就被删除。

用户可以利用SQL缓冲区在不重新输入命令的情况下，编辑、运行上一次执行的SQL命令。

SQL命令可以分一行或多行输入，如果在一行输入，则是SQL>SELECTEMPNO,ENAME,JOB,SAL,FROMEMPWHERESAL<2500；在SQL命令输入过程中，如果按RETURN键，则表示命令分多行输入，在下一行的开始处将自动出现一个行号。

如：

SQL>SELECTEMPNO,ENAME,JOB,SAL2FROMEMP3WHERESAL<2500；SQL命令完成，可以用分号（；、斜线（/）或空行来结束命令的输入。

输入分号并按RETURN键，则结束SQL命）令的输入，并运行SQL缓冲区中的命令。

在单独的一行上键入斜线并按RETURN键，SQL*PLUS将运行SQL命令。

如果输入一个空行，即在命令的最后一行后按RETURN键，SQL*PLUS显示一个行号，此时再按RETURN键，SQL*PLUS结束SQL命令的输入，回到SQL*PLUS的提示符状态。

2、SQL*PLUS命令SQL*PLUS命令用于设置查询结果的输出格式，形成复杂的报表，编辑SQL命令，设置系统变量并可提供帮助信息。

SQL*PLUS的很多命令可简写成一个或几个字母，SQL*PLUS命令的输入与SQL命令不同，例如，我们想改变EMP表的SAL列的显示格式，可以键入：

SQL>COLUMNSALFORMAT$99,999HEADINGSALARYCOLUMN命令为SAL列的数据显示设置了“$”和“，，并给出了新的列标题，通过运行前面的SQL命令可以清楚”地看到SQL*PLUS命令的结果。

在输入SQL*PLUS命令过程中，按RETURN键表示输入完成，而不必输入分号（；。

）如果命令输入占满一行，光标会自动移到下一行，可以继续输入，直到按RETURN键结束输入。

1.1.43CSK内部培训－ORACLESQL基础1.1.5获取帮助1、随机帮助在实际使用SQL*PLUS时，有时需要能有一种快捷的方法，获得有关命令的语法、含义的说明。

SQL*PLUS提供了HELP命令，为使用者提供随机帮助信息。

如果要获取所有SQL和SQL*PLUS命令的信息，只需输入SQL>HELP如果希望获取某一个命令的信息，可以在HELP之后输入该命令的名字，例如：

SQL>HELPACCEPT2、DESCRIBE命令当我们需要对基表进行操作的时候，往往需要获取关于基表的结构信息，可以使用DESCRIBE命令查看基表的结构：

SQL>DESCRIBEDEP命令执行结果如下：

NAMEDEPTNODNAMENULL?

NOTNULLTYPENUMBER

（2）CHAR（14）LOCCHAR（13）基表的结构信息包括：

NAME列名NULL标识该列是否允许为空值，如缺省则表示允许为空值TYPE说明该列的数据类型，可以是字符型：

CHAR（w），VARCHAR2（w）；数字型：

NUMBER（w，d）日期型：

DATE原始数据型：

RAW以及LONG型等等。

其中w表示列的宽度，d表示小数点之后的位数还可以使用DESCRIBE命令获取有关函数、过程以及包括描述，例如：

要求显示有关函数AFUNC的信息：

SQL>DESCRIBEafunc结果是functionafunc（F1CHAR，F2NUMBER）returnNUMBER；1.1.6编辑SQL*PLUS命令的使用1、命令行编辑命令我们可以使用SQL*PLUS的一些行编辑命令编辑SQL缓冲区中的SQL命令，表1.3列出了编辑功能的SQL8PLUS命令。

表1.3命APPENDtextCHANGE/old/newCHANGE/textCLEARBUFFERDELINPUTINPUTtextLISTLISTnLIST*LISTLAST令AtextC/old/newC/textCLBUFF无IItextLLn或nL*LAST缩写text行尾增加在当前行中将old改为new从当前行中删除text删除SQL缓冲区的所有行删除当前行增加一行或多行增加一个由text组成的行显示SQL缓冲区的所有行显示一行显示当前行显示最后一行意义显示多行（从m到n）LISTmnLmn

（1）显示SQL缓冲区的命令使用LIST命令可以显示当前SQL缓冲区的内容。

如果LIST命令后面不带任何参数，将显示SQL缓冲区中的所有行；如果LIST后面带一个参数（如LISTn），则显示第n行的内容；如果LIST后面带有两个参数（LISTmn），那将显示多行，即从m行到n行。

例如：

SQL>LIST4CSK内部培训－ORACLESQL基础1SELECTEMPNO，ENAME，JOB，SAL2*FROMEMPWHERESQL<2500；行号2后面的星号表示第2行为当前行。

除LIST命令外，其他编辑命令都只对SQL缓冲区的当前行操作。

在SELECT命令尾部输入的分号（；）没有显示，因为SQL*PLUS并不把它保存在SQL缓冲区中，所以在缓冲区未尾增加新行时；不必从最后一行去掉分号，使编辑更为方便。

（2）修改SQL命令使用CHANGE命令可以从当前行中删除一部分内容，或者用新的内容替换原先的内容。

在使用CHANGE命令之前必须先用带一个参数的LIST命令选择当前行。

例如，下列命令将DEPTNO错输出DPTNO：

SQL>SELECTDPTNO，ENAME，SAL2FROMEMP3WHEREDEPTNO=10；系统给出出错信息：

SELECTDPTNO，ENAME，SAL*ERRORatLine1：

ORA-0904：

invalidcolumnname使用CHANGE命令进行修改：

SQL>CHANGE/DPTNO/DEPTNO改正后屏幕上显示：

1*SELECTDEPTNO，ENAME，SAL这时可用RUN命令重新运行修改后的SQL命令。

（3）增加新行使用INPUT命令可以在SQL缓冲区增加一个或多个新行。

如果只输入INPUT，则在当前行之后插入多个新行；如果输入INPUTtext，则在当前行之后插入由text组成的新行。

例如，输入INPUT命令：

SQL>INPUT4这时在INPUT命令之后出现一个行号，这个行号表明所要插入的行，此时可输入一个或多个新行。

输入一行内容后按RETURN键，SQL*PLUS给出一个新行号，如屏幕所示：

4ORDERBYSAL5再按RETURN键，表示插入结束，可用RUN命令重新运行来检验最终的查询结果。

（4）在一行后追加内容使用APPEND命令可以在SQL缓冲区中的当前行未尾追加内容。

首先用LIST命令将需要追加内容的行置为当前行，然后使用APPENDtext命令在当前行未尾追加内容。

例如，要在第四行未尾追加DESC选项，先将第四行置为当前行：

SQL>LIST44*ORDERBYSAL然后输入下列命令追加DESC：

SQL>APPENDDESC4*ORDERBYSALDESC最后用RUN命令重新运行。

（5）删除SQL缓冲区中的行使用DEL命令可以删除SQL缓冲区中的当前行，当前行以下各行的行号顺序减1，如果把第一行设为当前行，连续多次输入DEL后，可以删除SQL缓冲区中的所有行。

删除SQL缓冲区中所有行也可以使用CLEARBUFFER命令，它的作用是将SQL缓冲区清空。

2、EDIT命令EDIT命令用于调用一个操作系统提供的文本编辑器。

编辑SQL缓冲区里的内容或操作系统下的文本文件，并将要编辑的文件名传送给该编辑器。

如果文件扩展名（例如.ext）被省略，则系统自动追加.SQL的扩展名，如果文件名和扩展名均省略，则编辑器被调用，并将SQL*PLUS产生的文件名传给它，这个文件中包含着当前缓冲区的内容。

如果当前缓冲区为空，且EDIT被调用时没有给出文件名，则EDIT命令失败。

SQL>EDIT也可以是SQL>EDIT文件名可以使用EDFINE命令设置系统变量_EDITOR来改变文本编辑器的类型，一般在LOGIN.SQL文件中完成它的设置，当SQL*PLUS被调用时将读出这些信息。

例如，在UNIX平台上，定义Vi作为SQL*PLUS调用的外部编辑器：

SQL>DEFINE_EDITOR=Vi3、SAVE命令如果要把SQL命令存储到磁盘上，或者想使用自己的编辑程序，用户可以通过SAVE命令将内容写到一个文件上。

SQL>SAVEDEPTINFO5CSK内部培训－ORACLESQL基础这时SQL*PLUS响应：

CreatefileDEPTINFO文件名后缀缺省为.SQL。

这时用户需要保存的内容已经存入了DEPTINFO.SQL文件中，可以对文件进行编辑、运行。

如果想将修改过的内容保存到原有的命令文件中，则在SAVE命令后增加REPLACE选项：

SQL>SAVEDEPTINFOREPLACE如果想将SQL缓冲区的内容追加到已经存在的命令文件未尾，则在SAVE命令后增加APPEND选项：

SQL>SAVEfile—nameAPPEND4、GET命令GET命令把磁盘上的命令文件内容调入当前缓冲区，并显示在屏幕上，格式是SQL>GETfile—name文件后缀缺省为.SQL，文件被调入缓冲区后，可以对它进行编辑、运行。

5、运行命令文件START命令将指定文件调入缓冲区并执行文件内容：

SQL>STARTfile—name文件后缀缺省为.SQL。

也可以使用“@”命令运行文件：

SQL>@file—name1.2数据操纵语言数据操纵语言（DML：

DataManipulationLanguage）给用户或应用程序提供访问数据库系统的接口。

1.2.1数据库的基本查询SQL语言中最主要、最核心的部分是它的查询功能。

所谓查询就是从数据库中提取满足用户需要的数据，查询是由SELECT命令实现的。

在SQL语言中，许多操作都涉及到SELECT命令。

例如，半SELECT命令查询到的数据插入到另外一张基表中；使用SELECT命令用满足条件的数据创建一个视图等等。

因此SELECT命令也是SQL语言中最灵活、最复杂的命令。

我们将从简单的SELECT命令开始，逐步深入讨论。

通常，一个SELECT命令可以分解成三个部分：

查找什么数据，从哪里查找，查找条件是什么。

因此，SELECT命令可以分成以下几个子句：

SELECT子句和FROM子句（这两个子句是每条SELECT命令必须有的），WHERE子句（是可选的），另外带可以选择GROUPBY子句、HAVING子句和ORDERBY子句等。

它的基本语法是SELECT<查询内容>FORM<表名>WHERE<条件>GROUPBY<分组内容>HAVING<组内条件>ORDERBY<排序内容>1、SELECT子句SELECT子句的<查询内容>指出要查找的数据，FROM子句的<表名>指出查找过程中涉及到的基表，这两个子句必不可少。

例1、查找整个基表的内容。

SQL>SELECT*FROMEMP；EMPNO73697499752175667654769877827788783978447876790079027934ENAMESMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLERJOBCLERKSALESMANSALESMANMANAGERSALESMANMANAGERMANAGERANALYSTPRESIDENTSALESMANCLERK7788CLERK7698ANALYSTCLERK756677827698MGR7902769876987839769878397839766HIREDATE17-Dec-9420-Feb-9522-Feb-9502-Apr-9528-Sep-9501-May-9509-Jun-9509-Nov-9517-Nov-9508-Sep-9523-Sep-9503-Dec-9503-Dec-9423-Jan-94SAL80016001250297512502850245030005000150011009503000130001400300500COMMDEPTNO20303020303010201030203020106CSK内部培训－ORACLESQL基础14rowsselected.“*”号意味着查找表中所有列。

若SELECT子句中选择一个列名，则将得到这一列的所有数据。

例2、查看雇员名字。

SQL>SELECTENAMEFROMEMP；ENAMESMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER14rowsselected.若查找多列数据，则用逗号将各个列名分隔开。

例3、查看雇员编号、名字和工种。

SQL>SELECTEMPNO，ENAME，JOBFROMEMP；EMPNO73697499752175667654769877827788783978447876790079027934ENAMESMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMILLERJOBCLERKSALESMANSALESMANMANAGERSALESMANMANAGERMANAGERANALYSTPRESIDENTSALESMANCLERK7788CLERK7698ANALYSTCLERK14rowsselected.系统在显示查询结果时，各列的显示顺序由它们在SELECT子句中出现的顺序决定。

如例3中希望显示顺序是JOB，ENAME，EMPNO，则可以输入SQL>SELECTJOB，ENAME，EMPNOFROMEMP；JOBENAMEEMPNOCLERKSALESMANSMITHALLEN736974997CSK内部培训－ORACLESQL基础SALESMAN………………WARD752114rowsselected.在显示查询结果时，各列的标题就是基表中的列名，如果希望显示的列标题不同于列名，可在SELECT子句中定义列名的别名，方法是在列名后加一个空