sqlplus命令(oracle10g).ppt

1,大型关系数据库（oracle10g） 第一章 sql plus命令,计算机科学与工程学院 2013年3月,2,oracle 的三个主要用户,（1）SYS 用户是超级用户，具有最高权限，具有Sysdba角色，有create database 的权限，该用户默认密码是manager; （2） system用户是管理操作员，权限较大，具有sysoper角色，没有create database 权限，默认密码是change_on_install ； （3）scott用户：普通用户，密码是tiger; 注意：一般讲，对于数据库的维护，使用system用户登录即可，上述默认密码只适用于9i，在10g 和11g中需用户设置。,3,Oracle 和sql server 2000的区别,区别： （1） sql server用户登录以后可以看到很多数据库； （2） Oracle 启动一个数据库就是启动一个实例，不同用户登录后看到不同的数据对象； （3）Oracle的多用户性能比SQL Server强大； （4）Oracle的界面基本是基于Java的，大部分的工具是Dos界面的，SQL Server是跟VB一样，全图形界面，很少见到DOS窗口； （5）SQL Server 2000提供了可扩展标记语言核心支持以及Internet上和防火墙外进行查询的能力，具有可伸缩性、灵活性、扩充性好，而且价格便宜等特点，而Oracle则有着可靠安全性，速度比SQL Server快上百倍，但其价格在实施中却比SQL Server它高出了几百倍。,4,oracle 管理工具介绍,1 oracle 自带的工具软件，主要用于执行SQL语句 ，PL/sql 块 使用方法： （1）开始程序oracle application development sql plus； (2)在运行栏中输入：sqlplus即可； (3)开始程序oracleapplication development sqlplus worksheet .,5,oracle 管理工具介绍,2 sql*plus dos 下操作oracle的工具，功能和sql*plus 相似 （1）在运行栏中输入sqlplus ； （2）找到可执行文件sqlplus.exe; 位于主目录oracle10gbin sqlplus.exe双击即可。 3 oracle 企业管理器 （oem oracle enterprise manager ） 开始程序oracle oracle home10genterprise manager console 即可启动oracle 的企业管理器，是一个图形界面. 4 pl/sql developer 第三方软件，主要用于开发，测试优化oracle pl/sql的存储过程 比如：触发器，这需要单独安装。,6,Sql plus 常用命令,连接命令 1、 connect 该命令用户连接到数据库，使用该命令建立会话时，会自动断开先前会话。 用法：conn 用户名/密码 as sysdba/sysoper 当用特权用户身份连接时，须带上as sysdba(以超级用户登录) 或是as sysoper（ 以管理员用户登录） 例：conn system/yhf123;（特权用户详见用户管理）,7,Sql plus 常用命令,2、 discconnect:断开命令，用于断开与当前数据库的连接，但不会退出Sql*plus ,quit命令则是退出Sql*plus。 例：Sql disc 3、Password:修改用户的密码，若想修改其他用户的密码，需要用sys/system登录； 例：scott用户 想修改自己的密码,操作步骤如下： （1）conn scott/tiger ； （2） passw （3）旧口令：； （4）新口令： （5）重新键入新口令：； （6）口令已更改,8,Sql plus 常用命令,4 show user:显示当前用户名； 5 EXIT和QUIT：EXIT和QUIT不但会断开数据库连接，还会退出SQL*PLUS，而且在退出SQL*PLUS时还会隐含提交事务。,9,编辑命令,Scott用户下emp表结构： EMPNO：雇员编号 ENAME ： 雇员名字 JOB ：雇员职位 MGR：员工的上级ID号 HIREDATE：任职时间 SAL ：工资 COMM：奖金 DEPTNO：所在部门号 注意：desc emp 即可查看emp表结构.,10,编辑命令,当在sql plus中执行sql语句时，oracle会将sql语句暂时存放到sql缓冲区中。当执行新的sql语句时，会自动清除先前sql缓冲区中的内容，并将新语句放到sql缓冲区中。使用sql plus所提供的编辑命令可以显示、编辑和修改sql缓冲区的内容。 1、 l ist 用于列出sql缓冲区的内容，使用该命令可以列出sql缓冲区某行、某几行或所有行的内容，在显示结果中，数字为具体的行号，“*”表示当前行。,11,编辑命令,1、 l ist(续) 例：select * from emp; 列出缓冲区中的第1-2行代码. l 1 2 ; 例：显示缓冲区的所有行(最后一行前面有*，表示最后一行为当前行) l 例：列出缓冲区中的当前行代码 l *,12,编辑命令,例1： Select empno, ename ,sal from emp where deptno=10; (1)显示缓冲区中的所有行： sql l ; (2)显示缓冲区中的第2行：sql 2；或 sql l2； 2 . append 用于在sql缓冲区的当前行尾部添加内容,默认最后一行为当前行。如： sql a and job=clerk; 显示： 2* sal from emp where deptno=10 and job=CLERK; 注意： a 之前加空格，否则追加语句和原来语句之间无空格；,13,编辑命令,3 Change 用于修改缓冲区的内容，如果在写sql语句时，写错了某个词，可以应用该命令进行修改。 Sql Select * from temp where deptno=10; Sql c/temp/emp; 将temp 修改为emp 4 del 用于删除sql缓冲区的内容，使用它可以删除某行、某几行或所有行，默认情况执行del只删除当前行的内容。 Sql del sql L ;此时提示 “缓冲区中不存在内容”,14,编辑命令,4 del 如果要删除多行，则指定起始行号和终止行号，如： Sql del 3 5 ； Sql Select empno, ename , 2 sal from emp where deptno=10 3 and job=CLERK; Sql del 1 2 ; Sql l 1* and job=CLERK,15,编辑命令,5 i nput 用于在sql缓冲区中当前行后新增一行，如： Select empno, ename , sal from emp where deptno=10 Sql i and job=CLERK Sql l 1 Select empno, ename , 2 sal from emp where deptno=10 3* and job=CLERK 注：若要在首行前增加内容，则用“0 文本”。 例：在首行前增加 and sql 0 and ;,16,编辑命令,6. n 用于定位到第n行，即该行成为当前行: sql l -显示缓冲区中的所有行 1 Select empno, ename , 2 sal from emp where deptno=10 3* and job=CLERK; sql 2 -定位到第2行，即第2行成为当前行 2* sal from emp where deptno=10,17,编辑命令,7 edit 用于编辑sql缓冲区的内容。运行该命令时，自动启动“记事本”，以编辑sql缓冲区的内容。 Sqledit ; 例：编辑c盘下的a.Sql文件,该文件是已经存在的。 edit c:a.sql; 8 run 和/ 可以用于运行SQL缓冲区中的sql语句，使用run命令时，不仅运行缓冲区中的sql语句，还会列出sql缓冲区的内容。 sql select * from emp where deptno=10; sqlrun; sql / ;,18,文件操作命令,如果经常执行某些命令，则可以将这些命令保存到sql脚本文件中。 1. save 用于将当前Sql缓冲区的内容保存到sql脚本中，当执行命令时，默认选项为create,即建立新文件。 例：select * from dept where deptno=10; Sql save c:a.sql create; 如果脚本已经存在，使用replace 选项的作用为：用缓冲区中的内容替换已经存在的脚本；如果要将当前缓冲区中的内容追加到现有文件的后面，则使用append选项;,19,文件操作命令,1 save(续) 例：将 select * from emp 追加到 c:a.sql 。 save c:a.sql create ; -错误提示：SP2-0540: 文件 “c:a.sql“ 已经存在 . save c:a.sql append ;(正确) 注意：使用create 选项，表示创建a.sql文件，同时还将缓冲区中的内容存放在该文件中。,20,文件操作命令,2 get 将sql脚本中的所有内容装载到sql缓冲区中。 Sql get c:a.sql; 3 start 和 用于运行sql脚本，运行时应指明路径。 运行位于d盘下的aa.sql文件，该文件中有:select * from emp 语句。 Sql d:aa.sql; 4 edit 用于编辑脚本，方法如下： 如：edit d:aa.sql 执行该命令后，打开文件等待编辑,21,文件操作命令,5、spool ：建立假脱机文件，将sql*plus屏幕上的内容输出到指定文件中去，即将查询结果保存到文件中，从spool命令开始到spool off 或者 spool out命令之间的查询结果都将保存到文件中。 格式：spool file_name off|out P90 Off :停止将结果保存到文件。 Out:关闭该文件，并在系统默认的打印机上打印。 sql spool d:d.sql 建立文件 Sql select * from emp ;-查询 Sql spool off; -停止将结果保存到文件 Sql edit d:d.sql -查看保存的结果 注：如果spool后没任何选项，则列出当前的spooling状态。,22,格式化命令,在使用格式化命令时，应该遵循下面的一些规则： 格式化命令设置之后，将会一直起作用，直到该会话结束或下一个格式化命令的设置； 每一次报表结束时，重新设置SQL*Plus为默认值；,23,格式化命令,1、 column 用select 语句可以指定别名，用column也可以指定一个别名；详见p85 例：为emp表的sal列标题取一个别名为：工资 column SAL HEADING 工资，最后不需要分号，不然查询结果中也有分号。 执行查询语句后，SAL名称变为“工资” 例： column 工资 format $999,999.00 注意 ：执行select * from emp 发现工资列并没有被格式化。若执行column sal format $999,999.00 , 则查询时，发现sal列已经被格式化。 注意：可以通过on和off 设置某列的显示属性是否起作用，如： Column SAL OFF ；执行此语句后，再查询scott.emp表，则sal列的名称为SAL ,而不是“工资” Column SAL ON ；执行此语句后，再查询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 (续) 例：col sal heading sal format L99999.99; 这告诉oracle使用该列的数值代替掩码中的9。 例：col sal heading sal format L99999.00; 这告诉oracle使用该列的数值代替掩码中的9，对应0位置的数字值则不显示，如某行的”sal”值为2500.15 ，则使用格式掩码后，显示的数字值就是2500.00. 例：col sal heading sal format 0999.00; 执行该语句后，再查询 scott.emp 表 ，sal列的所有值前面都加了一个0.,26,格式化命令,2 、ttitle 和 btitle (p87) ttitle ：用于指定页标题（打印时，每页的顶部标题） ； btitle：用于指定页脚注（打印时，每页的顶部标题） ； 例： sql TTITLE center 工资高于500的员工信息 ; sql select * from emp where sal =500; 例：sql BTITLE center 工资高于500的员工信息; sql select * from emp where sal =500; 关闭页标题和脚注： TTITLE off BTITLE off,27,交互式命令,1 、 注意：在输入值后，不需要以分号结束，否则提示错误 。 例4：设置符号“?”为替换符。 Set define ？,28,交互式命令,2、define 用于定义类型为CHAR的替代变量，该变量只在当前sql plus环境中有用,可以在命令文件使用，也可以在标题中用。使用“define 变量名”可以检查变量是否已经定义。 define title ; 例： define title=CLERK; select * from emp where job= -查询job为CLERK的员工信息,29,交互式命令 （2013 年2月24日）,3、undefine：用于清除替代变量的定义 undefine title; define title ; -用于检查title变量是否被定义 提示 SP2-0135: 符号title是 UNDEFINED 4、 prompt 和pause prompt用于输入提示信息，pause用于暂停脚本执行。在SQL脚本中结合使用，可以控制SQL脚本的暂停和执行。若在c:a文件中包含如下SQL脚本： prompt 按enter键继续; pause ; select * from scott.emp; 运行SQL脚本时，会暂停执行，如下： SQL c:a 按enter键继续 敲enter键后，才执行选择语句,30,交互式命令,5、accept (p91) 用于定义char 、number和date类型的替代变量。此外还可以指定变量输入提示、变量输入格式、隐藏输入内容,格式如下： accept variable number | char default prompt hide number 和char：指定该变量的数据类型； prompt为变量在接受用户输入之前，显示的提示信息；hide为隐藏用户输入的变量值。 例： accept title prompt 请输入岗位; 请输入岗位 CLERK （若输入小写clerk则查询无结果） -上述两步相当于为为title 赋值：CLERK 注意：输入的岗位和提示之间不能有空格，否则提示未选定行 select * from emp where job= 执行该语句后，则显示job为CLERK的员工信息。,31,交互式命令,5 、accept (续) 例：sql accept title prompt 请输入岗位； sql select * from emp where job= 此时输入的密码时并不显示,32,交互式命令,5、accept(续） 隐藏用户输入： 例： sql accept a number prompt 请输入部门号; 提示输入部门号 sql select * from scott.emp where deptno= 则显示部门号为20的员工,33,显示和设置环境变量,用于控制输入的各种格式，set show 1、 linesize 在第四种工具中看不出效果 设置显示行的宽度，默认是80个字符，如果超过80个字符，在sql plus 中会转行显示数据结果， 例如：set linesize 120; 查看当前每行的所显示的字符宽度用命令show 例如：show linesize ; 2、 pagesize 设置每页显示的行数，默认是14，用法和linesize一样。 例如：set pagesize 5; Show pagesize ;显示当前所设置的每页显示的行数,34,显示和设置环境变量,注：实际查询结果中并没有包含5条记录，因为包括标题行，空行和列标题与数据之间的分隔线。 3 、timing 将其设置为on，每次执行完sql命令， sql*plus就会显示该命令消耗的系统时间，默认值为off，表示不会显示sql语句执行时间。 例如 ：set timing on ; select * from emp where job=CLERK;,35,显示和设置环境变量,4 autocommit 用于设定是否自动提交DML（Data Manipulation Language ）语句(如：insert , delete , update, select),默认为off（表示禁止自动提交）。当设置为on时，每次执行DML语句都会自动提交。如： sql show autocommit ; sql set autocommit on ; 5 colsep（column separate） 用于设置列之间的分隔符，默认分隔符为空格。如果要使用其他分隔符，则使用set命令进行设置。 例：set colsep | ; 用|作分隔符 select ename, sal from emp where empno=7788;,36,显示和设置环境变量,6 FEEDBACK 用于指定在返回几行时，显示select语句处理的行数，默认值为6.若需禁止显示行数，将feedback设置为off,设置为on表示显示行数。假设只要有查询结果就希望能返回行数，将其设置为1. 格式：set feedback n; 如果实际查询结果返回的行数小于n，则不显示查询结果包含多少行的提示，如，n=10,查询结果只有5行，则不显示“已选择5行” sql select * from emp where job=SALESMAN; sql Show feedback; sql Set feedback 1; select * from emp where