第4章管理数据

陕西工院2023/2/4Oracle11g

数据库技术蔡苏亚主讲caisuya@126.com第4章管理数据4.1SQL*Plus4.2SQL语言基础4.1SQL*Plus4.1.1SQL*Plus简介4.1.2启动和退出SQL*Plus4.1.3交互式命令4.1.4编辑和运行语句4.1.5格式化输出4.1.6自定义SQL*PLUS环境4.1.1SQL*Plus简介

在Oracle系统中，SQL*Plus工具用于执行SQL或PL/SQL语言，是数据库管理员操作数据库中数据并执行各种数据库管理最直接和有效的工具，可以满足Oracle数据库管理员的大部分需求。SQL*Plus工具在服务器端和客户端都可以使用，在安装Oracle服务器端或客户端软件时都会自动安装SQL*Plus工具。SQL*Plus工具的主要功能有：1）启动关闭数据库实例。2）编辑、执行SQL或PL/SQL语句。3）创建、编辑和执行脚本文件。4）格式化输出结果。5）设置环境参数。4.1.2启动和退出SQL*Plus1以命令行方式启动SQL*Plus在DOS方式下输入“sqlplus”命令，接着再输入用户名和口令，这里可以输入默认的数据库用户system和相应的口令。当出现SQL>提示符后，就可以使用SQL*Plus工具

2以图形界面方式启动SQL*Plus在开始菜单中，选择程序→Oracle-OraDb10g_home1→应用程序开发→SQLPlus，输入对应的口令，在“主机字符串”一栏中输入服务名“orcl”，如果在本机登录，可以不输入内容保留空白，则会连接到默认的数据库实例。单击【确定】按钮，进入SQL*PLUS主界面。3退出SQL*Plus在SQL>命令提示符后面输入quit或exit命令，回车即可退出SQL*Plus工具。4.1.3交互式命令

SQL*Plus命令大致可以分为交互式命令、编辑命令和报表命令。4.1.3交互式命令下面介绍一些示例。SQL>CONNsystem/orcl@orcl;SQL>SHOWUSER;SQL>SELECTSYSDATEFROMDUAL;SQL>SELECT*FROMALL_USERS;SQL>SHOWSGA下面的语句用来创建一个假脱机文件。SQL>SPOOLD:\file.txt --开始假脱机输出SQL>SHOWSGASQL>SPOOLOFF --假脱机结束SQL>DISCONNECT4.1.4编辑和运行语句

SQL*PLus的常用编辑命令。4.1.4编辑和运行语句下面语句以HR用户的身份新建BOOK表，表结构见表。首先，如果当前Oracle数据库系统中的HR用户处于锁定状态，可以先执行下面的语句对该用户解锁。SQL>connsystem/orclassysdbaSQL>alteruserhraccountunlockSQL>connhr/hr已连接4.1.4编辑和运行语句接着以HR用户的身份登录，在SQL*Plus中输入并执行以下语句。SQL>CREATETABLEBOOK(NO CHAR(8)PRIMARYKEY,TITLE VARCHAR2(50)NOTNULL,AUTHOR VARCHAR2(20),PUBLISH VARCHAR2(20),PUB_DATE DATE,PRICE NUMBER(6,2));表已创建。如果用户在输入上面的语句时，少输了最后的右括号，执行时就会出错，如下面的形式：SQL>CREATETABLEBOOK(NO CHAR(8)PRIMARYKEY,4.1.4编辑和运行语句TITLE VARCHAR2(50)NOTNULL,AUTHOR VARCHAR2(20),PUBLISH VARCHAR2(20),PUB_DATE DATE,PRICE NUMBER(6,2);错误信息显示如下：PRICENUMBER(6,2)*第7行出现错误:ORA-00907:缺少右括号下面用SQL*Plus的编辑命令修改此错误。先用List命令列出发生错误的第7行。SQL>L77*PRICENUMBER(6,2)4.1.4编辑和运行语句再用Change命令修改发生错误的地方，命令格式是“C/<旧文本>/<新文本>”。SQL>C/)/))7*PRICENUMBER(6,2))接着再用List命令列出缓冲区的全部语句：SQL>L1CREATETABLEBOOK(2NO CHAR(8)PRIMARYKEY,3TITLE VARCHAR2(50)NOTNULL,4AUTHOR VARCHAR2(20),5PUBLISH VARCHAR2(20),6PUB_DATE DATE,7*PRICE NUMBER(6,2))4.1.4编辑和运行语句用Run命令重新执行如下：SQL>R1CREATETABLEBOOK(2NO CHAR(8)PRIMARYKEY,3TITLE VARCHAR2(50)NOTNULL,4AUTHOR VARCHAR2(20),5PUBLISH VARCHAR2(20),6PUB_DATE DATE,7*PRICE NUMBER(6,2))表已创建。接着，在BOOK表中插入数据。在SQL*Plus中输入下面的语句。4.1.4编辑和运行语句SQL>INSERTINTOBOOKVALUES('10000001','Oracle9i数据库系统管理','李代平等','冶金工业出版社',TO_DATE('2003/01/01','yyyy/mm/dd'),38.00);已创建1行。SQL>INSERTINTOBOOKVALUES('10000002','Oracle9i中文版入门和提高','赵松涛','人民邮电出版社',TO_DATE('2002/07/01','yyyy/mm/dd'),35.00);已创建1行。SQL>INSERTINTOBOOKVALUES('10000003','Oracle9i开发指南:PL/SQL程序设计','JoanCasteel','清华大学出版社',TO_DATE('2004/04/03','yyyy/mm/dd'),49.00);已创建1行。其中，TO_DATE是数据类型转换函数，用于将字符串转换为日期型。4.1.4编辑和运行语句下面的语句用来查询BOOK表中的所有记录，并且将查询语句保存到book.SQL脚本文件中，并调用脚本文件。SQL>SELECT*FROMBOOK;SQL>SAVEd:\book.SQLSQL>DEL清除缓冲区的内容SQL>R执行缓冲区的内容SQL>GETd:\book.SQLSQL>RSQL>@d:\book.SQL4.1.5格式化输出

SQL*PLUS可以格式化输出。1.COLUMN命令:可以修改列标题名称、显示宽度以及列标题的对齐格式等。COLUMN命令对列进行格式化后，其输出效果一直有效，直到重新设置该列的输出格式或者退出SQL*Plus。COLUMN命令的FORMAT子句用来定义列的宽带和显示格式，如果字段是字符型数据，则用An对其进行格式化。如果是数字型数据，可使用的格式化字符有“90$.，S”。4.1.5格式化输出其中，COLUMN命令的HEADING子句用来定义列标题。JUSTIFY子句表示对齐方式，可以选择的对齐方式有LEFT、CENTER和RIGHT。如果要还原列的默认输出格式，则可以使用COLUMN命令的CLEAR子句，如还原TITLE字段的输出格式命令为：SQL>COLUMNTITLECLEAR如果要还原所有列的默认输出格式，则使用命令:SQL>CLEARCOLUMN例：查询BOOK表并格式化输出。SQL>COLTITLEFORMATA30HEADING'书名'JUSTIFYRIGHT;SQL>COLAUTHORFORMATA20HEADING'作者'JUSTIFYLEFT;SQL>SELECTTITLE,AUTHORFROMBOOK;4.1.5格式化输出2.REPHEADER命令设置报表的标题，REPFOOTER命令设置报表的脚注。报表指一个完整的查询结果。下面的语句查询BOOK表的所有记录，设置报表的标题为“图书信息表”，报表的脚注为“制表人：孟欣”。SQL>REPHCENTER'图书信息表';SQL>REPFRIGHT'制表人：孟欣';3.TTITLE命令设置每页的页标题格式，BTITLE命令设置每页的页尾格式。每页所能显示的行数由“SETpagesizen”命令来指定。下面的语句查询HR.employees表的first_name，last_name字段，设置每页的标题为“雇员表信息”，每页的页尾为“每页页尾”。SQL>TTITLECENTER'雇员表信息';SQL>BTITLERIGHT'每页页尾';SQL>SELECTfirst_name,last_nameFROMhr.employees;4.1.5格式化输出SQL*Plus工具可以使用ON或OFF来打开或关闭报表和页的标题及脚注的设置，即分别执行下面的命令：SQL>TTITLEonSQL>TTITLEoffSQL>BTTILEonSQL>BTTILEoffSQL>REPHEADERonSQL>REPHEADERoffSQL>REPFOOTERonSQL>REPFOOTERoff4.1.6自定义SQL*PLUS环境SQL*Plus通过SET命令和菜单两种方式设置环境参数。1.SET命令：SET命令通过设置系统变量的值更改SQL*Plus的环境设置。1）ECHO2）PAGESIZE3）LINESIZE4）SERVEROUTPUT5）TRIMSPOOL2.环境对话框：用户可以直接单击SQL*Plus中的选项菜单中的环境菜单项，设置环境参数。4.2SQL语言基础

4.2.1概述4.2.2使用SELECT语句查询数据4.2.3使用INSERT语句插入数据4.2.4使用UPDATE语句更新数据4.2.5使用DELETE语句删除数据4.2.6事务控制命令4.2.1概述SQL（StructureQueryLanguage，结构化查询语言）于1975年由Boyce和Chamberlin提出，用来实现关系运算中查询、选择等操作，是一种综合的、功能极强的同时又简单易学的语言。作为关系数据库操作的标准语言，目前已被ANSI（美国国家标准化组织）正式批准为数据库的工业标准。SQL语言功能极强，可以完成数据库整个生命周期的全部活动。SQL是一种面向集合的操作语言，即操作对象和操作结果都是元组的集合。SQL语句可以在Oracle提供的SQL*Plus或SQL*Plus工作单工具中直接执行，并返回执行结果。SQL还可以嵌入到其他高级程序设计语言中，实现对后台数据库的访问。SQL语言按照功能可以分为3类：DDL、DML和DCL。4.2.2使用SELECT语句查询数据

SQL语言使用SELECT语句实现对数据表的任何查询，包括选择符合条件的行或列及其他操作等。常用的SELECT语法格式如下：SELECT字段1,字段2,……FROM表1[,表2]…WHERE查询条件GROUPBY分组字段1[,分组字段2]…HAVING分组条件ORDERBY列1[,列2]……4.2.2使用SELECT语句查询数据1.单表查询下面的语句查询DEPT表中的所有数据。SQL>SELECT*FROMSCOTT.DEPT;下面的语句查询EMP表的雇员编号、雇员姓名和工资信息。SQL>SELECTEMPNO,ENAME,SALFROMSCOTT.EMP;下面的语句查询EMP表中的工作种类（Job），去掉重复的记录。SQL>SELECTDISTINCTJOBFROMSCOTT.EMP;下面的语句查询雇员姓名（ENAME）为“BLAKE”的雇员编号、姓名、工作和雇用日期。SQL>SELECT*FROMSCOTT.EMPWHEREENAME='BLAKE';4.2.2使用SELECT语句查询数据查询条件运算符。4.2.2使用SELECT语句查询数据下面的语句查询EMP表雇员以“S”开头的雇员姓名。SQL>SELECTENAMEFROMSCOTT.EMPWHEREENAMELIKE'S％';下面的语句查询EMP表，对姓名按字母升序排列。SQL>SELECTENAMEFROMSCOTT.EMPORDERBYENAMEASC;下面的语句按照工作种类（JOB）分组统计EMP表中各部门的员工人数。SQL>SELECTJOB,COUNT(*)FROMSCOTT.EMPGROUPBYJOB;其中，COUNT函数用来统计符合条件的记录行数。GROUPBY语句还可以使用HAVING子句来检查分组的各组记录是否满足条件。相对于WHERE语句而言，HAVING只能配合GROUPBY语句使用。4.2.2使用SELECT语句查询数据2.多表查询指从多个有关联的表中查询数据。一般来说，多表查询的表要用等值连接联系起来，如果没有连接，则查询结果是这多个查询表的笛卡尔积。下面的语句查询雇员姓名和所在部门名称。SQL>SELECTENAME,DNAMEFROMSCOTT.EMPA,SCOTT.DEPTBWHEREA.DEPTNO=B.DEPTNO;下面的语句查询SALES部门的雇员姓名。SQL>SELECTENAMEFROMSCOTT.EMPA,SCOTT.DEPTBWHEREA.DEPTNO=B.DEPTNOANDB.DNAME='SALES';4.2.2使用SELECT语句查询数据3.嵌套查询指一个SELECT查询中包含一个以上的子查询，所谓子查询指嵌套在另一个SELECT、INSERT、UPDATE或DELETE语句中的SELECT查询语句。子查询的语法与SELECT语法类似，但有所限制，如子查询不能含有ORDERBY和INTO等关键字。下面的语句也可以查询“SALES”部门的雇员姓名。SQL>SELECTENAMEFROMSCOTT.EMPWHEREDEPTNOIN(SELECTDEPTNOFROMSCOTT.DEPTWHEREDNAME='SALES');下面的语句在EMP表中查询比平均工资高的雇员名单。SQL>SELECTENAMEFROMSCOTT.EMPWHERESAL>(SELECTAVG(SAL)FROMSCOTT.EMP);4.2.2使用SELECT语句查询数据4.使用函数的查询。可以使用的函数列表4.2.2使用SELECT语句查询数据可以使用的函数（续表）4.2.2使用SELECT语句查询数据下面的语句查询雇员总人数。SQL>SELECTCOUNT(*)FROMSCOTT.EMP;下面的语句把查询出来的部门名称以小写字符显示出来。SQL>SELECTLOWER(DNAME)AS"部门名称"FROMSCOTT.DEPT;下面的语句查询“2008/01/01”和“2006/01/01”两个日期之间的月份间隔。SQL>SELECTMONTHS_BETWEEN(TO_DATE('2008/01/01','yyyy/mm/dd'),TO_DATE('2006/01/01','yyyy/mm/dd'))AS"月份间隔"FROMDUAL;下面的语句使用TO_NUMBER函数用于求和的示例。SQL>SELECTTO_NUMBER('100')+TO_NUMBER('200')AS"求和"FROMDUAL;4.2.3使用INSERT语句插入数据

SQL语言用INSERT语句在数据表中插入数据。INSERT语句的使用语法一般有如下两种：INSERTINTO表名[字段1,字段2,……]VALUES(值1,值2,……)INSERTINTO表名[字段1,字段2,……]SELECT(字段1,字段2,……)FROM其他表名其中，第1条插入单条记录，第2条用于把从其他表中查询出来的数据插入到当前表中，用于多条记录的插入。无论是哪一种用法，都应该注意要插入的值与要插入的字段相互对应。1.单行记录的插入SQL>INSERTINTOSCOTT.EMPVALUES(7700,'John','ANALYS',7902,'08-9月-81',2500,'0',20);4.2.3使用INSERT语句插入数据SQL>INSERTINTOSCOTT.EMP(EMPNO,ENAME,JOB)VALUES(7100,'Mary','ANALYS')2多行记录的插入新建NEWEMP表，与EMP表相同结构，并将EMP表数据插入NEWEMP表。SQL>CREATETABLEscott.NEWEMP(EMPNONUMBER(5,0)NOTNULL,ENAMEVARCHAR2(10),JOBVARCHAR2(9),MGRNUMBER(5,0),HIREDATEDATE,SALNUMBER(7,2),COMMNUMBER(7,2),DEPTNONUMBER(2,0))SQL>INSERTINTOSCOTT.NEWEMPSELECT*FROMSCOTT.EMP;4.2.4使用UPDATE语句更新数据

UPDATE语句的一般语法如下：UPDATE表名SET字段1=值1[,字段2=值2]……WHERE条件表达式;其中，UPDATE后的表名指定要更新的表，SET指定要更新的字段及其相应的值，WHERE指定更新条件，如果没有指定更新条件，则对表中所有记录进行更新。下面的语句为雇员“BLAKE”加薪10％。SQL>UPDATESCOTT.EMPSETSAL=SAL*1.1WHEREENAME='BLAKE'下面的语句为EMP表中的所有雇员加薪10％。SQL>UPDATESCOTT.EMPSETSAL=SAL*1.1;4.2.5使用DELETE语句删除数据1使用DELETE语句删除记录DELETE语句删除数据表中的记录，语法格式如下：DELETEFROM表名[Where条件];其中，FROM后面的表名指定要删除数据表，WHERE子句指定要删除数据的条件。值得注意的是，使用DELETE语句删除表中数据时，并不能释放被占用的数据块空间，只是把那些被删除的数据块标记为无效（Unused），将来可以回退（Rollback）恢复过来。下面的语句删除NEWEMP表中的姓名为Mary的记录。SQL>DELETEFROMSCOTT.NEWEMPWHEREENAME='Mary'下面的语句删除NEWEMP表中的所有记录。SQL>DELETEFROMSCOTT.NEWEMP;4.2.5使用DELETE语句删除数据2使用TRUNCATE语句删除所有记录TRUNCATE语句全部清空表中的数据但保留表结构，释放掉那些占用的数据块，不能进行回退操作。因此进行此操作时，一定要慎重。语法格式如下：TRUNCATETABLE表名;下面的语句删除NEWEMP表中的所有记录。SQL>TRUNCATETABLESCOTT.NEWEMP;表已截掉。如再执行下面语句，看到表中的数据已全部清空。SQL>SELECT*FROMSCOTT.NEWEMP;4.2.6事务控制命令

1.事务概述事务（Transaction）是由一系列相关的SQL语句组成的最小逻辑工作单元。Oracle系统以事务为单位来处理数据，用以保证数据的一致性。对于事务中的每一个操作要么全部完成，要么全部不执行。事务当遇到以下情况时事务结束。1）执行COMMIT或ROLLBACK命令。2）执行DDL命令。3）LOGOFF。4）程序异常结束。如果一个程序没有遇到以上几种情况，则在程序正常结束时将整个程序作为一个事务提交。如在SQL*Plus中正常退出时，系统会自动提交最后一个事务。事务可以是一组SQL命令，也可以是一条SQL语句。这些SQL语句只能是DML语句，对于其他SQL语句，如CREATETABLE语句，一旦执行就立即提交给数据库，不可能回滚。4.2.6事务控制命令事务控制的命令有以下几种：提交事务（COMMIT）、设置保留点（SAVEPOINT）、回滚事务（ROLLBACK）和设置事务（SETTRANSACTION）等。在对数据库发出DML操作时，只有当事务提交后才确保操作完成。在事务提交前所作的修改只有操作者本人可以查看操作结果，其他用户只有在事务提交后才能够看到。2提交事务提交事务有以下3种类型。1）自动提交设置AUTOCOMMIT为ON时，自动提交事务SQL>SETAUTOCOMMITON/OFF因为Oracle系统维护自动提交所消耗的系统资源较多，建议取消自动提交功能。2）显式提交使用COMMIT命令显式提交事务。使用的语句如下：SQL>COMMIT;4.2.6事务控制命令3．设置保留点保留点是设置在事务中的标记，把一个较长的事务划分为若干个短事务。通过设置保留点，在事务需要回滚操作时，可以只回滚到某个保留点。设置保留点的语法如下：SAVEPOINT保留点名下面的语句设置两个保留点SP1和SP2。SQL>insertintoscott.emp(empno,ename,job)values(7878,'john','programee');SQL>savepointsp1;SQL>select*fromscott.emp;SQL>savepointsp2;SQL>deletefromscott.empwhereename='button';SQL>commit;4.2.6事务控制命令4．回滚事务有时用户在事务提交前取消所作的修改或由于系统故障等原因，Oracle系统将恢复到执行事务执行前的一致性状态，这称为回滚事务。Oracle系统允许回滚整个事务，也可以只回滚到某个保留点，但不能回滚已经被提交的事务。回滚到某个保留点的事务将撤销保留点之后的所有修改，而保留点之前的所有操作不受影响。同时，Oracle系统还删除该保留点之后的所有保留点，而该保留点还保留，以便多次回滚到同一保留点。如下面的语句将回滚整个事务：SQL>ROLLBACK;下面的语句将回滚到指定的保留点SP1：SQL>ROLLBACKTOSP1;4.2.6事务控制命令下面的语句回滚事务。SQL>insertintoscott.emp(empno,ename,job)values(7878,'john','programee');SQL>savepointtosp1;SQL>select*fromscott.emp;SQL>savepointtosp2;SQL>deletefromscott.empwhereename='button';SQL>rollbacktosp2;回滚到保留点SP2，则SP2保留点之后的所有DELETE操作将撤销。4.2.6事务控制命令5．设置事务设置事务用于指定事务是读写还是只读。一般情况下，读写事务可以包含任何SQL语句，如查询、修改和删除的DML语句。只读事务只能包含查询语句，可以使用的SQL命令如SELECT（不含forupdate子句）等。默认情况下的Oracle事务为读写事务。设置事务的语法如下：settransaction[readwrite|readonly][userollbacksegment回退段]其中，readwrite：读写，readonly：只读。userollbacksegment设置事务使