sqlplus命令(oracle10g).ppt

1,大型关系数据库（oracle10g）第一章sqlplus命令,计算机科学与工程学院2013年3月,2,oracle的三个主要用户,（1）SYS用户是超级用户，具有最高权限，具有Sysdba角色，有createdatabase的权限，该用户默认密码是manager;（2）system用户是管理操作员，权限较大，具有sysoper角色，没有createdatabase权限，默认密码是change_on_install；（3）scott用户：普通用户，密码是tiger;注意：一般讲，对于数据库的维护，使用system用户登录即可，上述默认密码只适用于9i，在10g和11g中需用户设置。,3,Oracle和sqlserver2000的区别,区别：（1）sqlserver用户登录以后可以看到很多数据库；（2）Oracle启动一个数据库就是启动一个实例，不同用户登录后看到不同的数据对象；（3）Oracle的多用户性能比SQLServer强大；（4）Oracle的界面基本是基于Java的，大部分的工具是Dos界面的，SQLServer是跟VB一样，全图形界面，很少见到DOS窗口；（5）SQLServer2000提供了可扩展标记语言核心支持以及Internet上和防火墙外进行查询的能力，具有可伸缩性、灵活性、扩充性好，而且价格便宜等特点，而Oracle则有着可靠安全性，速度比SQLServer快上百倍，但其价格在实施中却比SQLServer它高出了几百倍。,4,oracle管理工具介绍,1oracle自带的工具软件，主要用于执行SQL语句，PL/sql块使用方法：（1）开始程序oracleapplicationdevelopmentsqlplus；(2)在运行栏中输入：sqlplus即可；(3)开始程序oracleapplicationdevelopmentsqlplusworksheet.,5,oracle管理工具介绍,2sql*plusdos下操作oracle的工具，功能和sql*plus相似（1）在运行栏中输入sqlplus；（2）找到可执行文件sqlplus.exe;位于主目录oracle10gbinsqlplus.exe双击即可。3oracle企业管理器（oemoracleenterprisemanager）开始程序oracleoraclehome10genterprisemanagerconsole即可启动oracle的企业管理器，是一个图形界面.4pl/sqldeveloper第三方软件，主要用于开发，测试优化oraclepl/sql的存储过程比如：触发器，这需要单独安装。,6,Sqlplus常用命令,连接命令1、connect该命令用户连接到数据库，使用该命令建立会话时，会自动断开先前会话。用法：conn用户名/密码assysdba/sysoper当用特权用户身份连接时，须带上assysdba(以超级用户登录)或是assysoper（以管理员用户登录）例：connsystem/yhf123;（特权用户详见用户管理）,7,Sqlplus常用命令,2、discconnect:断开命令，用于断开与当前数据库的连接，但不会退出Sql*plus,quit命令则是退出Sql*plus。例：Sqldisc3、Password:修改用户的密码，若想修改其他用户的密码，需要用sys/system登录；例：scott用户想修改自己的密码,操作步骤如下：（1）connscott/tiger；（2）passw（3）旧口令：；（4）新口令：（5）重新键入新口令：；（6）口令已更改,8,Sqlplus常用命令,4showuser:显示当前用户名；5EXIT和QUIT：EXIT和QUIT不但会断开数据库连接，还会退出SQL*PLUS，而且在退出SQL*PLUS时还会隐含提交事务。,9,编辑命令,Scott用户下emp表结构：EMPNO：雇员编号ENAME：雇员名字JOB：雇员职位MGR：员工的上级ID号HIREDATE：任职时间SAL：工资COMM：奖金DEPTNO：所在部门号注意：descemp即可查看emp表结构.,10,编辑命令,当在sqlplus中执行sql语句时，oracle会将sql语句暂时存放到sql缓冲区中。当执行新的sql语句时，会自动清除先前sql缓冲区中的内容，并将新语句放到sql缓冲区中。使用sqlplus所提供的编辑命令可以显示、编辑和修改sql缓冲区的内容。1、list用于列出sql缓冲区的内容，使用该命令可以列出sql缓冲区某行、某几行或所有行的内容，在显示结果中，数字为具体的行号，“*”表示当前行。,11,编辑命令,1、list(续)例：select*fromemp;列出缓冲区中的第1-2行代码.l12;例：显示缓冲区的所有行(最后一行前面有*，表示最后一行为当前行)l例：列出缓冲区中的当前行代码l*,12,编辑命令,例1：Selectempno,ename,salfromempwheredeptno=10;(1)显示缓冲区中的所有行：sqll;(2)显示缓冲区中的第2行：sql2；或sqll2；2.append用于在sql缓冲区的当前行尾部添加内容,默认最后一行为当前行。如：sqlaandjob=clerk;显示：2*salfromempwheredeptno=10andjob=CLERK;注意：a之前加空格，否则追加语句和原来语句之间无空格；,13,编辑命令,3Change用于修改缓冲区的内容，如果在写sql语句时，写错了某个词，可以应用该命令进行修改。SqlSelect*fromtempwheredeptno=10;Sqlc/temp/emp;将temp修改为emp4del用于删除sql缓冲区的内容，使用它可以删除某行、某几行或所有行，默认情况执行del只删除当前行的内容。SqldelsqlL;此时提示“缓冲区中不存在内容”,14,编辑命令,4del如果要删除多行，则指定起始行号和终止行号，如：Sqldel35；SqlSelectempno,ename,2salfromempwheredeptno=103andjob=CLERK;Sqldel12;Sqll1*andjob=CLERK,15,编辑命令,5input用于在sql缓冲区中当前行后新增一行，如：Selectempno,ename,salfromempwheredeptno=10Sqliandjob=CLERKSqll1Selectempno,ename,2salfromempwheredeptno=103*andjob=CLERK注：若要在首行前增加内容，则用“0文本”。例：在首行前增加andsql0and;,16,编辑命令,6.n用于定位到第n行，即该行成为当前行:sqll-显示缓冲区中的所有行1Selectempno,ename,2salfromempwheredeptno=103*andjob=CLERK;sql2-定位到第2行，即第2行成为当前行2*salfromempwheredeptno=10,17,编辑命令,7edit用于编辑sql缓冲区的内容。运行该命令时，自动启动“记事本”，以编辑sql缓冲区的内容。Sqledit;例：编辑c盘下的a.Sql文件,该文件是已经存在的。editc:a.sql;8run和/可以用于运行SQL缓冲区中的sql语句，使用run命令时，不仅运行缓冲区中的sql语句，还会列出sql缓冲区的内容。sqlselect*fromempwheredeptno=10;sqlrun;sql/;,18,文件操作命令,如果经常执行某些命令，则可以将这些命令保存到sql脚本文件中。1.save用于将当前Sql缓冲区的内容保存到sql脚本中，当执行命令时，默认选项为create,即建立新文件。例：select*fromdeptwheredeptno=10;Sqlsavec:a.sqlcreate;如果脚本已经存在，使用replace选项的作用为：用缓冲区中的内容替换已经存在的脚本；如果要将当前缓冲区中的内容追加到现有文件的后面，则使用append选项;,19,文件操作命令,1save(续)例：将select*fromemp追加到c:a.sql。savec:a.sqlcreate;-错误提示：SP2-0540:文件c:a.sql已经存在.savec:a.sqlappend;(正确)注意：使用create选项，表示创建a.sql文件，同时还将缓冲区中的内容存放在该文件中。,20,文件操作命令,2get将sql脚本中的所有内容装载到sql缓冲区中。Sqlgetc:a.sql;3start和用于运行sql脚本，运行时应指明路径。运行位于d盘下的aa.sql文件，该文件中有:select*fromemp语句。Sqld:aa.sql;4edit用于编辑脚本，方法如下：如：editd:aa.sql执行该命令后，打开文件等待编辑,21,文件操作命令,5、spool：建立假脱机文件，将sql*plus屏幕上的内容输出到指定文件中去，即将查询结果保存到文件中，从spool命令开始到spooloff或者spoolout命令之间的查询结果都将保存到文件中。格式：spoolfile_nameoff|outP90Off:停止将结果保存到文件。Out:关闭该文件，并在系统默认的打印机上打印。sqlspoold:d.sql建立文件Sqlselect*fromemp;-查询Sqlspooloff;-停止将结果保存到文件Sqleditd:d.sql-查看保存的结果注：如果spool后没任何选项，则列出当前的spooling状态。,22,格式化命令,在使用格式化命令时，应该遵循下面的一些规则：格式化命令设置之后，将会一直起作用，直到该会话结束或下一个格式化命令的设置；每一次报表结束时，重新设置SQL*Plus为默认值；,23,格式化命令,1、column用select语句可以指定别名，用column也可以指定一个别名；详见p85例：为emp表的sal列标题取一个别名为：工资columnSALHEADING工资，最后不需要分号，不然查询结果中也有分号。执行查询语句后，SAL名称变为“工资”例：column工资format$999,999.00注意：执行select*fromemp发现工资列并没有被格式化。若执行columnsalformat$999,999.00,则查询时，发现sal列已经被格式化。注意：可以通过on和off设置某列的显示属性是否起作用，如：ColumnSALOFF；执行此语句后，再查询scott.emp表，则sal列的名称为SAL,而不是“工资”ColumnSALON；执行此语句后，再查询scott.emp表，则sal列的名称为“工资”,而不是SAL。,24,格式化命令,1、column(续)Format用于指定列格式。格式模型中包含一些元素：An：设置char,varchar2类型列的显示宽度（n代表数字）。9：在Number类型列上禁止显示前导0.0：在number类型列强制显示前导0。$:在number类型列前显示美元符号。L:在number类型列前显示本地货币符号。.：指定number类型列的小数点位置。，：指定number类型列的千分隔符。,25,格式化命令,1、column(续)例：colsalheadingsalformatL99999.99;这告诉oracle使用该列的数值代替掩码中的9。例：colsalheadingsalformatL99999.00;这告诉oracle使用该列的数值代替掩码中的9，对应0位置的数字值则不显示，如某行的”sal”值为2500.15，则使用格式掩码后，显示的数字值就是2500.00.例：colsalheadingsalformat0999.00;执行该语句后，再查询scott.emp表，sal列的所有值前面都加了一个0.,26,格式化命令,2、ttitle和btitle(p87)ttitle：用于指定页标题（打印时，每页的顶部标题）；btitle：用于指定页脚注（打印时，每页的顶部标题）；例：sqlTTITLEcenter工资高于500的员工信息;sqlselect*fromempwheresal=500;例：sqlBTITLEcenter工资高于500的员工信息;sqlselect*fromempwheresal=500;关闭页标题和脚注：TTITLEoffBTITLEoff,27,交互式命令,1、注意：在输入值后，不需要以分号结束，否则提示错误。例4：设置符号“?”为替换符。Setdefine？,28,交互式命令,2、define用于定义类型为CHAR的替代变量，该变量只在当前sqlplus环境中有用,可以在命令文件使用，也可以在标题中用。使用“define变量名”可以检查变量是否已经定义。definetitle;例：definetitle=CLERK;select*fromempwherejob=-查询job为CLERK的员工信息,29,交互式命令（2013年2月24日）,3、undefine：用于清除替代变量的定义undefinetitle;definetitle;-用于检查title变量是否被定义提示SP2-0135:符号title是UNDEFINED4、prompt和pauseprompt用于输入提示信息，pause用于暂停脚本执行。在SQL脚本中结合使用，可以控制SQL脚本的暂停和执行。若在c:a文件中包含如下SQL脚本：prompt按enter键继续;pause;select*fromscott.emp;运行SQL脚本时，会暂停执行，如下：SQLc:a按enter键继续敲enter键后，才执行选择语句,30,交互式命令,5、accept(p91)用于定义char、number和date类型的替代变量。此外还可以指定变量输入提示、变量输入格式、隐藏输入内容,格式如下：acceptvariablenumber|chardefaultprompthidenumber和char：指定该变量的数据类型；prompt为变量在接受用户输入之前，显示的提示信息；hide为隐藏用户输入的变量值。例：accepttitleprompt请输入岗位;请输入岗位CLERK（若输入小写clerk则查询无结果）-上述两步相当于为为title赋值：CLERK注意：输入的岗位和提示之间不能有空格，否则提示未选定行select*fromempwherejob=执行该语句后，则显示job为CLERK的员工信息。,31,交互式命令,5、accept(续)例：sqlaccepttitleprompt请输入岗位；sqlselect*fromempwherejob=此时输入的密码时并不显示,32,交互式命令,5、accept(续）隐藏用户输入：例：sqlacceptanumberprompt请输入部门号;提示输入部门号sqlselect*fromscott.empwheredeptno=则显示部门号为20的员工,33,显示和设置环境变量,用于控制输入的各种格式，setshow1、linesize在第四种工具中看不出效果设置显示行的宽度，默认是80个字符，如果超过80个字符，在sqlplus中会转行显示数据结果，例如：setlinesize120;查看当前每行的所显示的字符宽度用命令show例如：showlinesize;2、pagesize设置每页显示的行数，默认是14，用法和linesize一样。例如：setpagesize5;Showpagesize;显示当前所设置的每页显示的行数,34,显示和设置环境变量,注：实际查询结果中并没有包含5条记录，因为包括标题行，空行和列标题与数据之间的分隔线。3、timing将其设置为on，每次执行完sql命令，sql*plus就会显示该命令消耗的系统时间，默认值为off，表示不会显示sql语句执行时间。例如：settimingon;select*fromempwherejob=CLERK;,35,显示和设置环境变量,4autocommit用于设定是否自动提交DML（DataManipulationLanguage）语句(如：insert,delete,update,select),默认为off（表示禁止自动提交）。当设置为on时，每次执行DML语句都会自动提交。如：sqlshowautocommit;sqlsetautocommiton;5colsep（columnseparate）用于设置列之间的分隔符，默认分隔符为空格。如果要使用其他分隔符，则使用set命令进行设置。例：setcolsep|;用|作分隔符selectename,salfromempwhereempno=7788;,36,显示和设置环境变量,6FEEDBACK用于指定在返回几行时，显示select语句处理的行数，默认值为6.若需禁止显示行数，将feedback设置为off,设置为on表示显示行数。假设只要有查询结果就希望能返回行数，将其设置为1.格式：setfeedbackn;如果实际查询结果返回的行数小于n，则不显示查询结果包含多少行的提示，如，n=10,查询结果只有5行，则不显示“已选择5行”sqlselect*fromempwherejob=SALESMAN;sqlShowfeedback;sqlSetfeedback1;select*fromempwherejob=