Oracle数据库基础及应用第05章SQLPlus命令.pptx

第第5章章 SQLPlus命令命令5.1使用使用SQL*P1us在数据库系统中，可以使用两种方式 执行命令：图形化工具使用各种命令在Oracle 11g系统中，可以使用SQL语句进行访问，实现查询、插入、修改和删除等操作。SQL*Plus是一个通用的、在各种平台上几乎都完全一致的工具。它既能在Windows机器上使用它，也能在UNIX、Linux机器上使用它。直观、简单、容易记忆；灵活性较差直观、简单、容易记忆；灵活性较差非常灵活、加深对复杂命令的理解、非常灵活、加深对复杂命令的理解、完成图形化工具无法完成的工作；完成图形化工具无法完成的工作；需要记忆具体的命令和语法格式需要记忆具体的命令和语法格式5.1.1启动启动SQL*P1us在Windows下启动SQL*Plus的步骤是：(1)在开始菜单中，选择“开始”“所有程序”“Oracle-OraDblog-homel”“Application Development”“SQL Plus”命令,出现SQL*Plus的登录窗口(2)在该窗口中输入用户名称system、口令“admin”，这里用户口令采用的回显的方式，所以是看不见的。然后单击“Enter”按钮，则打开Oracle SQL*Plus窗口。在该窗口中显示SQL*Plus的命令提示符“SQL”，表明SQL*Plus已经连接到Oracle 11g，准备接受命令了。5.1.2登录数据库命令登录数据库命令CONNECT命令的作用是连接数据库，若是当前已经有用户连接了数据库，那么将会中断当前的连接，而使用该命令指定的用户进行建立新的连接。CONNECT命令的语法格式为：CONNECT/assysdba/sysoper其中：username指的是连接数据库的用户名，password指的是密码，如果不省略它们，则会直接登录到SQL*Plus中；如果省略，可以在启动SQL*Plus之后再输入连接数据库的用户名和密码。5.1.3退出退出SQL*P1us用户如果不需要再使用SQL*Plus命令时，想返回到操作系统，只需在SQL*Plus命令提示符下，输入exit或quit命令，按Enter键即可。在SQL命令后输入exit。如：SQLexit。如图5-5所示。或是使用菜单命令，选择“文件”“退出”命令，也可以退出SQL*Plus。5.2 SQL*Plus的运行环境设置的运行环境设置SQL*Plus运行环境是SQL*Plus的运行方式和查询语句执行结果显示方式的总称。设置合适的SQL*Plus运行环境，可以使SQL*Plus能够按照用户的要求运行和执行各种操作。5.2.1 SET命令选项命令选项在Oracle 11g系统中，用户可以使用SET命令来设置SQL*Plus的运行环境。SET命令是SQL*Plus内部命令中最重要、使用频率最高的命令。使用SET命令的语法格式为：set sysytem_option value选 项描 述set autocommit on|off|immediate设置事务处理方式，手动/自动提交set autoprinton|off设置是否自动打印变量值set autorecoveryon|off设置为on时，以默认的文件名重做记录，需要恢复时，使用recover automatic database语句恢复，否则只能用recover database语句恢复set autotrace on|off|traceonly exlainstatistics对正常执行完毕的SQL DML语句自动生成报表信息set blockterminatorO|on|off定义表示PL/SQL块结束的字符set colset_|text设置列和列之间的分隔字符，默认为空格set cmdset;|c|on|off定义命令行区分字符，默认为off。set linesize80|n设置一行中能够显示的总字符数，默认80set long80|n为long型数值设置最大显示宽度，默认80set newpage1|n|none设置每页打印标题前的空行数，默认为1Set 选项及说明选 项描 述set numformat format设置数字的默认显示格式set null text设置select语句返回null值时显示的字符串set pauseoff|on|text设置输出结果时是否滚动显示set pagesize14|n设置每页打印的行数set recset wrapped|each|off显示或打印记录分隔符set space1|n设置输出结果中列和列之间的空格数，默认值为10set sqlcasemixed|lower|upper设置在执行sql命令之前是否转换大小写set sqlcontinue|text设置命令提示符set timeoff|on设置当前时间的显示set timingoff|on用于启动和关闭显示sql语句执行时间set underline-|c|on|off设置是否在列标题下面添加分隔线set wrapon|off设置当一个数据项比当前行宽时，是否截断数据项的显示Set 选项及说明(续)5.2.2 设置运行环境设置运行环境在Oracle中怎么设置运行环境以及设置后的效果如何，在这里可以通过具体的示例来进行演示。设置运行环境中使用频率较高的操作如下所示。1.SET PAGESIZE n 选项选项当执行有返回结果的查询语句时，SQL*Plus首先会显示用户所选择数据的列名，然后在相应的列名下显示数据，列名之间的空间就是SQL*Plus的一页。SQL*Plus的一页多大，可以使用命令SHOW PAGESIZE显示SQL*Plus默认的一页的大小。可以通过使用PAGESIZE命令来改变这个默认值。例如，设置PAGESIZE为20后查询HR.EMPLOYEES表，命令如下：SQL show pagesizepagesize 14SQL set pagesize 20SQL select employee_id,first_name,last_name 2 from hr.employees;当PAGESIZE被设置为20后，SQL*Plus在一页内显示了17行数据。一页的内容不仅包含查询的数据结果，还包括表的表头、虚线和空白行等。2.SET PAUSE选项选项如果查询语句返回的结果很多时，以至于无法在SQL*Plus窗口中一次显示完，这时SQL*Plus输出窗口会快速滚动显示。这样就需要在窗体上对数据进行缓冲，以存储滚动到屏幕以外的数据，以便一页一页地查看查询结果。通过设置环境变量PAUSE为ON来控制SQL*Plus在显示完一页后暂停显示，直到按“Enter”键后才继续显示下一页数据。当设置PAUSE命令为ON时，需注意：当提交查询的时候，SQL*Plus会在显示第一页之前就暂停显示。只有按回车键后第一页的内容才会显示。PAUSE选项还可以设置暂停后显示的字符串，以便提示用户。默认情况下，是不启用此功能的。如果想启用它，也可以使用Text参数在该命令后面给出相应的提示信息。关闭PAUSE命令的形式如下：SQLset pause off3.SET LINESIZE n选项选项使用LINESIZE选项，可以设置SQL*Plus中一行数据可以容纳的字符数量。n表示每行能够显示的字符数，取值范围为132767，默认值为80。若是修改系统默认的每行打印80个字符。当SQL*Plus输出LINESIZE指定数量的字符后，随后的数据就会折叠到下一行显示，如果用户窗口特别宽，那么用户就可以设置更宽的LINSIZE，以避免折叠显示SQLshow linesizeLinesize 80SQLset linesize 110Linesize 1104.SET TIMINGON/OFF选项选项 该选项用于启动和关闭显示SQL语句执行时间的功能。在SQL*Plus中运行SQL命令时，不同的SQL命令消耗的系统时间是不同的。为了查看命令所消耗的系统时间，可以设置TIMING选项为ON，这时每当执行完SQL命令，SQL*Plus就会显示该命令所消耗的系统时间。该命令输出的时间单位是小时：分：秒：毫秒5.SET TIME ON/OFF选项选项 该选项用于在提示符前显示或不显示系统时间。在SQL*Plus中运行SQL命令时，为了查看系统时间，可以设置TIME选项为ON，这时每当执行SQL命令，命令符前就会显示系统时间。例如：SQLset time on该命令执行后，就会在命令提示符前显示当前系统的时间。再次输入命令SET TIME OFF就会退出该命令，提示符前就不会显示系统时间了。6.SET HEADING ON/OFF 选项选项 该选项用于设置是否显示表头信息，ON 表示为显示，OFF表示为不显示。默认值一般为ON。执行不显示表头信息命令，例如：SQLset heading offSQL select job_id,job_title2 from hr.jobs3 where rownum=8;该命令执行后，数据前的表头信息将不再显示出来。再次输入命令SET HEADING ON就会重新显示出表头等信息。5.3 SQL*Plus定制行为命令定制行为命令 在Oracle 11g数据库系统当中，SQL*Plus还提供了很多可以定制该工具行为的命令。这些命令包括：DESCRIBE、PROMRT、SPOOL等。5.3.1 DESCRIBE 命令命令在SQL*Plus的许多命令中，DESCRIBE命令可能是用户使用最为频繁的命令。作用：返回对数据库中所存储的对象的描述；列出其各个列的名称以及属性；输出PL/SQL块中的过程、函数和程序包的规范。DESCRIBE命令的语法形式如下：DESCRIBE object_name;其中，DESCRIBE可以缩写为DESC，object_name表示将要描述的对象名称。DESCRIBE命令不仅可以描述表、视图的结构，而且还可以描述PL/SQL对象，如下面通过DESCRIBE命令查看hr.employees表的结构，如图所示。在SQL*Plus中，如果输入了很长一段SQL语句后，发现忘记了该表中的列名，重新再输入会浪费时间。我们可以利用DESCRIBE命令来解决这个问题。只需要在另一行以“#”开头，就可在输入SQL语句过程中临时运行一个SQL*Plus命令。例如，在查询HR.EMPLOYEES表中的数据时，WHERE子句中忘记了某个列名，可利用上述办法解决，继续加载WHERE语句中剩余的内容。5.3.2 PROMPT命令命令使用PROMPT命令可以在显示屏幕上输出指定的数据和空行，这种输出方式非常有助于在脚本文件中向用户传递相应的信息。PROMPT命令的语法形式如下：PROMPTtext;其中，TEXT表示用于指定要在屏幕上显示的提示信息，省略TEST则会输出一行空行5.3.3 SPOOL命令命令SPOOL是SQL*Plus中主要完成以标准输出方式输出SQL*Plus的命令及执行结果，一般可把查询结果保存到文件中或者发送到打印机中。当查询语句的结果很多或是要生成一个报表时，通常会使用此命令以生成一些查询的脚本或者数据。该命令的语法格式如下：SPOOLFILE_NAME CREATE|REPLACE|APPEND|OFF;其中，FILE_NAME参数用于指定脱机文件的名称，默认的文件扩展名为.LST。使用CREATE关键字，表示创建一个新的脱机文件；使用REPLACR关键字，表示替代已经存在的脱机文件；使用APPEND关键字，表示把脱机内容附加到一个已经存在的脱机文件中。例 如，使用SPOOL命令生成employees.txt文件，并将查询hr.employees表的内容保存到文件夹中，命令如图所示。spool命令执行的结果为：从spool命令开始，一直到spool off或者spool out命令之间的查询结果都将保存到D:employees.txt文件中。注意，只有输入执行完spool off命令后，才能在文件中查看到保存的内容，如图所示。5.3.4 HELP命令命令SQL*Plus的命令很多，如果不知道某个具体命令的用法，就可以使用HELP INDEX命令来获取SQL*Plus内建帮助系统中的相关支持命令信息。HELP命令的语法形式如下：HELP topic;其中，topic参数表示将要查询的命令名称。使用help index命令，就会显示SQL*Plus中所有的命令列表，如图所示。5.4缓存区命令缓存区命令在Oracle中，通常所提到的SQL语句、PL/SQL语句块都是SQL*Plus语句的命令。这一类命令是发送到服务器端执行的命令，它们要求以分号或反斜线结尾以表示语句执行完毕。当它们执行完成后，在SQL*Plus的缓存区中用户就可以重新调用、编辑或运行那些最近输入的SQL语句。但需要注意的是，缓存区中只存储用户最近执行的命令语句。5.4.1 EDIT命令命令通过在缓冲区存储这些命令语句，用户可以使用EDIT命令，将缓存区中的内容传递到Windows记事本中进行编辑。编辑器只有在SQL缓冲区中有内容时才能启动。EDIT命令的语法形式如下：EDIT file_name;EDIT命令自动打开记事本，用来编辑缓存区中的内容，修改后直接关闭记事本，记事本中的内容就会存到缓存区。也可以在edit后面指定文件名，编辑完内容将文件保存，系统会自动将文件读入缓存区。在SQL*Plus命令行中，输入 内容如下：select employee_id from hr.employees where rownum=或!=其它(not)inis(not)betweenandandor单值函数nvl-空值替换函数abs-绝对值函数mod-求模函数power-求幂函数sqrt-平方根函数exp，ln，log-对数函数sin，cos等-三角函数唯一性查询l关键字：distinctselectdistinct列名from表名where条件；l模糊查询select列名1，列名2，列名3from表名where列名1like_%mmorderby列名1,列名2；l关键字：(not)likel通配符：%_联接查询select表名1.列名1，表名2.列名2，列名3from表名1，表名2where条件orderby表名1.列名1,表名2.列名2；子查询 单值子查询 多值子查询select列名1，列名2，列名3from表名1where列名1=（select列名1from表名2where条件）orderby列名1,列名2；select列名1，列名2，列名3from表名1where列名1in（select列名1from表名2where条件）orderby列名1,列名2；组值函数avg-平均值函数count-非空值数目max-最大值函数min-最小值函数sum-求和函数distinct-唯一性函数分组查询分组查询select列名1，列名2，sum(列名3)from表名where条件groupby列名1,列名2；l关键字：groupby改变数据操作lInsert插入操作insertinto表名1（列名1，列名2）values(值1，值2）；注意：值表中的数据用逗号间隔；字符型和日期型要用单引号括起来；缺省的日期格式为dd-man-yy。insertinto表名1usingselect列名1，列名2from表名2where条件；Update 修改操作可修改单个字段、修改多行、修改单个列。update表名set列名=值或表达式where条件；Delete删除ldeletefrom表名where条件；注意不能只删除行的一部分where子句决定哪一行将被删除。如里省略了where子句，则所有的行都将被删除。其它常用sql命令Createtable-创建表；例子Droptable-删除表，sql同时自动删除该表上的索引、特权，但并不删除相关的视图或同义词；Grant-授权Connect-连上Oracle,做最基本操作resource-具有程序开发最基本的权限dba-数据库管理员所有权限l-运行指定的命令文件，作用等价于start命令；l/-运行sql缓冲区的命令，与run命令等价；lConnect-退出当前账号，向指定的用户帐号登录；lDesc-显示指定表的结构；lExit-中止sql*plus，返回操作系统；lSpool-将屏幕显示内容输出到一个文件上；lSpooloff-输出文件结束；其它常用sql*plus命令四、SQL*NET的配置和使用 l1配置网络协议TCP/IPl2测试网络是否连通l3在Windows95上选择“开始”按钮，选定“程序”中的“OracleforWindows95”下的“SQL*NetEasyConfiguration”，在“SQL*NetEasyConfiguration”窗口中选择“AddDatabaseAlias”，在“ChooseDatabaseAlias”对话框中“DatabaseAlias”项中键入数据库别名，按“OK”按钮确认。在“ChooseTCP/IPHostNameandDatabase”对话框中的TCP/IPHostname项中键入SERVER的IP地址，在“DatabaseInstance”项中键入Oracle的实例名。l在“ConfirmAddingDatabaseAlias”对话框中可以看到你新增加的数据库别名。按“YES”按钮，按“OK”，退出“SQL*NetEasyConfiguration”。五、数据库备份(exp/imp)EXPORT将数据库中数据备份成一个二进制系统文件称为“导出转储文件”(EXPORTDUMPFILE)，并将重新生成数据库写入文件中。它有三种模式：a.用户模式：导出用户所有对象以及对象中的数据；b.表模式：导出用户所有表或者指定的表；c.整个数据库：导出数据库中所有对象。IMPORT导入：导入的过程是导出的逆过程，这个命令先读取导出来的导出转储二进制文件，并运行文件，恢复对象用户和数据。数据库导出过程exp80用户名/密码Enterarrayfetchbuffersize:4096exportfile:expdat.dmp(1)Entiredatabase(2)Users(3)Table:(2)UsersExportgrants:yesExporttabledata:yesConpressextents:yesUsertobeexportExportingtable、synonyms、view、grant等Exportterminatedsuccessfullywithoutwarning.导出选项l关键字内容lUSERID运行导出命令的帐号的用户名/口令。lBUFFEER用来取数据行的缓冲区的大小。lFILE导出转储文件的名字lCOMPRESS导出是否应该压缩有碎片的段成一个范围lGRANTS导出时否要导出数据库对象上的授权lINDEXES是否要导出表上的索引lROWS是否应导出行。如它为N，那么在导出文件中仅生成数据库对象的DDLlCONSSTRAINTS是否导出表旧的约定条件lFULL如设为Y，那么将执行一个整个数据库导出操作lOWNER要导出的一系列数据库帐号，然后执行这些帐号的USER导出操作lTABLES要导出的一系列表；执行这些表的TABLE导出操作导出选项l关键字内容lRECORDLENGTH转储导出文件日志的长度(以字节为单位)，通常都使用缺省，除非要在两个不同操作系统中传递导出文件lINCTYPE正在执行的导出操作的类型(有效值有”COMPLETE“(缺省值),CUMULATIVE”和“IVCREMENTAL”)lRECORD在增量导出类型中的一个标记，标明是否将日志存储到数据字典中日志导出的表中lPARFILE要传递给导出的参数文件的名字。这个文件将包含所有参数的入口项lANALYZE标明是否将移对象的统计信息写入导出转储文件中lCONSISTENT标明是否保留所有导出对象的读取一致复制。lLOG导出日志将要写入的文件的名字lMLS标明是否要导出MLS标号lMLS_LABEL_规定了MLS标号的格式数据库恢复Imp80用户名/密码Importfile:expdat.dmp导入选择项l关键字内容lUSERID运行导入命令的帐号的用户名/口令。lBUFFEER用来取数据行的缓冲区的大小。lFILE导入转储文件的名字lSHOW规定文件内容是否被显示，而不是被执行。lEGORE标明在执行CREATE命令时，是否忽略遇到的错误。如果正l在导入的对象已经存在时，应使用这个参数。lFULL如设为Y，那么将执行一个整个数据库导入操作lFROMUSER在FULL=N的情况下，才有参数。它是一系列的数据库帐号，其l对象应从导出转储文件中读取。lTOUSER一系列数据库帐号，导出转储文件扣的对象将要导入这些帐号。lGRANTS导入时否要导入数据库对象上的授权lINDEXES是否要导入表上的索引lROWS是否应导入行。如它为N，那么在导入文件中执行数据库对l象的DDL导入选择项l关键字内容lFULL如设为Y，那么将执行一个整个数据库导出操作lTABLES要导入的一系列表lRECORDLENGTH转储导入文件日志的长度(以字节为单位)，通常都使用缺省，除非要在两个不同操作系统中传递导入文件lINCTYPE正在执行的导入操作的类型(有效值有”COMPLETE“(缺省值)，lCUMULATIVE”和“IVCREMENTAL”)lPARFILE要传递给导入的参数文件的名字。这个文件将包含所有参数的入l口项lANALYZE标明是否将移对象的统计信息写入导入转储文件中lLOG导入日志将要写入的文件的名字lMLS标明是否要导入MLS标号六、其它常见问题l1.字符集的问题l在Windows95上选择“开始”按钮，启动“运行”图标，键入REGEDIT，进行注册表编辑，选取“我的电脑”中的“HKEY_LOCALMACHINE”下的“Software”Oracle选取Oracle下的NLS_LANG，其缺省值为：AMERICAN_AMERICA.us7ascii，双击进行编辑改为：AMERICAN_AMERICAl2.PB常用的描述器应用、数据库管理、表、l3.利用PB进行数据的导入l4.