Oracle 11g 学习2——SQLPlus命令

一、连接数据库

//连接/断开数据库
方法一：SQLplus ：输入用户名，输入密码
例如：请输入用户名：sys /as sysdba
      请输入密码：***

方法二：命令行：sqlplus sys/admin@xrisk

方法三：SQLplus: CONNECT 用户名/密码 AS 身份；
例如：system/admin as sysdba;

断开连接 ：DISCONNECT;

 

二、常用命令

1、HELP命令

作用：查看命令的使用方法

语法：HELP [topic]

说明：topic:标识需要查看的命令名称

例如：HELP DESC;

 

2、HOST命令

作用：使用该命令可以从SQL*Plus环境切换到操作系统环境，以便执行操作系统  命令,

语法：

         HOST:从SQL*Plus环境切换到操作系统环境，默认Oracle安装目录下的BIN目录；使用cmd登录，可以再次进入SQLplus环境。

         HOST [操作系统命令]： 执行操作系统命令

说明：

例如：HOST notepad.exe

 

3、CLEAR SCR[EEN] 命令

作用：清除屏幕内容

语法：CLEAR SCR[EEN] 

 

4、SHOW命令

作用：查看SQL*Plus的所有系统变量值信息、当前是哪个用户在使用SQL*Plus、显示SGA大小、查看错误信息、数据库版本信息、系统初始化参数信息

语法：SHOW [ALL| USER | SGA| ERRORS| REL[EASE] | PARAMETERS]

说明：

         ALL：标识所有系统变量值

         USER：标识当前用户

         SGA：标识SGA大小

         ERRORS：标识错误信息

         RELEASE：标识数据库版本信息

         PARAMETERS：标识系统初始化参数

例子：

         SHOW REL;

 

5、DESCRIBE命令

作用：使用DESCRIBE命令可以查看表的结构信息。

语法：DESC[RIBE]

说明：查看对象的结构，这里的对象可以是表、视图、存储过程、函数和包等。

例子：DESC dba_users;

      

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//编辑SQL语句的命令，常用的一些编辑SQL语句的命令，例如APPEND命令、CHANGE命令、DELETE命令、RUN命令等。编辑后的SQL语句将被保存到缓冲区中

6、APPEND命令

作用：附加文本到当前行之后

语法：A[PPEND] text

说明：text：标识要附加的文本

例子：

7、CHANGE命令

作用：替换当前行的内容

语法：C[HANGE] /old/new

说明：old:标识旧的被替换的文本

           new:标识新的待替换的文本

例子：

 

8、CLEAR BUFFER命令

作用：清除缓存区中的所有行

语法：CL[EAR] BUFF[ER]

说明：

 

9、INPUT命令

作用：插入指定的文本

语法：I[NPUT] text

说明：text:标识要插入的文本


10、LIST命令

作用：显示当前命令缓冲区的内容

语法：L[IST] n

说明：n:标识显示命令缓冲区的第n行

例如：LIST :显示当前命令缓冲区的所有内容

            LIST 2：显示当前命令缓冲区的第2行内容

11、DEL命令

作用：删除命令缓冲区的某些行

语法：DEL m n

说明：m, n标识要删除的行的范围

例如：


12、RUN或/ 命令

作用：显示缓冲区中保存的语句，并运行这些语句

语法：RUN 或 /

说明

13、n命令

作用：将第n行作为当前行

语法：n

            n text

说明：n标识作为当前行的缓冲区命令行号
            text：标识要替换第n行信息的文本

例子：2：将第二行作为当前行

            2  text：使用text文本替换第2行信息

            0  text：在第一行之前插入text文本
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

14、SAVE命令

作用：将当前缓冲区中的内容保存到文件中

语法：SAV[E] [FILE] file_name [CRE[ATE] | REP[LACE] | APP[END]]

说明：

         file_name:指定的文件名，注：默认保存路径Oracle安装目录BIN下。

         CREATE:创建文件，该选项是默认值

         APPEND:若文件存在，追加到文件中；若文件不存在，创建文件

         REPLACE:若文件存在，覆盖原来文件；若文件不存在，创建文件

例子：

        SAVE test.sql;
        SAVE 'C:\test.sql'
        SAVE 'C:\test.sql' replace
        SAVE 'C:\test.sql' append
 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

15、GET命令

作用：读取文件内容到缓冲区。
语法：
         GET [FILE] file_name[LIST | NOLIST ]
说明：
         file_name :表示一个指定文件，将该文件的内容读入SQL*Plus缓冲区中
         LIST :列出缓冲区中的语句
         NOLIST: 不列出缓冲区中的语句
例如：
         GET 'C:/my.sql' NOLIST; //将C:/my.sql文件读到缓冲区中，但不显示
         GET 'C:/my.sql' LIST;       //将C:/my.sql文件读到缓冲区中，并显示

 

16、START命令
作用：读取并运行文件内容
语法：
         STA[RT] {url| file_name }
说明：
         url:用来指定一个URL地址，例如http://host.domain/script.sql。
         file_name: 指定待读入的文件。
         等同于同时调用GET命令和RUN命令。
例如：

         START 'C:\test.sql';
         START C:test.sql;
         @'C:\test.sql';
         @C:\test.sql;

 

17、EDIT命令

作用：编辑缓冲区内容或文件内容。使用EDIT命令，可以将SQL*Plus缓冲区的内容复制到一个名为afiedit.buf的文件中，然后启动操作系统中默认的编辑器打开这个文件，并              且文件内容能够进行编辑。在Windows操作系统中，默认的编辑器是Notepad（记事本）
语法：
         ED[IT] [file_name]

说明：
         file_name默认为afiedt.buf，也可以指定一个其他文件。

         弹出的文本编辑文件中修改缓冲区内容，直接保存，缓冲区就会自动更新
例如：
        edit;
        

18、SPOOL命令

作用：复制输出结果到文件。使用SPOOL命令实现将SQL*Plus中的输出结果复制到一个指定的文件中，或者把查询结果发送到打印机中，直到使用SPOOL OFF命令为止。
语法：
         SPO[OL] [file_name][CRE[ATE]|REP[LACE] | APP[END]|OFF | OUT ]
说明：
         file_name:指定文件
         CREATE:创建一个指定的file_name文件
         REPLACE:如果指定的文件已经存在，则替换该文件
         APPEND:将内容附加到一个已经存在的文件中
         OFF :停止将SQL*Plus中的输出结果复制到file_name文件中，并关闭该文件。
         OUT :启动该功能，将SQL*Plus中的输出结果复制到file_name文件中

 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//定义变量的命令

19 、&命令

作用：定义一个临时变量，在SQL语句中，如果在某个变量前面使用了&符号，那么就表示该变量是一个临时变量。执行SQL语句时，系统会提示用户为该变量提供一个具体的             数据。

语法：
         & verify_name;

说明：

         若希望重新使用某个临时变量并且不希望重新提示输入值，可以使用&&符号来表示临时变量。

         SET VERIFY OFF: 不显示新值和原值信息，执行该语句之后，不再显示新值和原值信息。

         SET VERIFY ON:  显示新值和原值信息
例如：

         select StuName, StudId
         from tb_student
         where studId > &temp;

        select &name, StudId
        from tb_student
        where &name > &temp;

        select &&name, StudId
        from tb_student
        where &&name > &temp;
 

20、DEFINE命令

作用：定义变量

语法：
         DEF[INE]:显示所有的已定义变量
         DEF[INE] variable :显示指定变量的名称、值和其数据类型
         DEF[INE] variable = value:创建一个CHAR类型的用户变量，并且为该变量赋初始值。

说明：

         已定义变量是指具有明确定义的变量，该变量的值会一直保留到被显式地删除、重定义或退出SQL*Plus为止。在SQL语句中，可以在使用变量之前对变量进行定义，然后          在同一个SQL语句中可以多次使用这个变量。定义变量，可以使用DEFINE或ACCPET命令；删除变量，可以使用UNDEFINE命令。

例如：
         DEFINE temp = 7850
         DEFINE temp
         select StuName,Sex
         from tb_student
         where StudId >= &temp;
注：已存在变量temp会自定覆盖临时变量temp的值，不需要用户输入。

21、ACCPET命令
作用：定义变量，并且定制一个用户提示，用于提示用户输入指定变量的数据。在定义变量时，可以明确地指定该变量时NUMBER还是DATE等数据类型。为了安全性的原因，            还可以将用户输入的信息隐藏起来。

语法：
         ACC[EPT] variable [data_type][FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE] 

说明：
        variable :定义的变量名称
        data_type:变量的类型
        FORMAT:   关键字
        format:   变量的格式
        DEFAULT:  关键字
        default:  变量的默认值
        PROMPT:   关键字，标识显示提示文本
        text:     提示文本的内容
        NOPROMPT: 关键字，标识不显示提示文本
        HIDE:     关键字，标识输入变量时不显示变量的值
例如：
        SQL> ACCEPT test NUMBER FORMAT 9999 PROMPT '你好，请输入一个EMPNO值：' HIDE
        你好，请输入一个EMPNO值：
        SQL> DEFINE test
        DEFINE TEST   =   1111 <NUMBER>

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

//格式化输出命令

22、COLUMN命令

作用：可以对列的显示效果，包括列标题和列数据进行格式化。
语法：
         COL[UMN] [{column_name | ALI[AS] alias } [options]]
说明：
         column_name:指定列名
         ALIAS alias :指定要格式化的列的别名
         options:指定用于格式化列或别名的一个或者多个选项

options选项：
(1)、FOR[MAT] format :将列或列名的显示格式设置为由format字符串指定的格式，format可以使用的格式如下表：
         An:为[VAR]CHAR类型的列设置宽度。如果内容超过指定的宽度，则内容自定换行，例如A5
         9:设置NUMBER列的显示格式，例如：999 999
         $:浮动的货币符号，例如：$9999
         L:本地货币符号， 例如：L9999
         .:小数点位置， 例如：9999.99
        ，:千位分隔符，例如：9,999

(2)、HEA[DING] text:设置由text字符串指定的列标题
(3)、JUS[TIFY][{LEFT|CENTER|RIGHT }]:将列的输出信息设置为左对齐、居中对齐或右对齐
(4)、WRA[PPED]:在输出结果中将一个字符串的末尾换行显示。该选项可能导致单个单词跨越多行。
(5)、WOR[D_WRAPPED]:与WRAPPED选项类似，但是单个单词不会跨越多行
CLE[AR]:清除列的格式化
TRUNCATED:删除第一行的字符串
NULL text :指定列为空值时显示的内容
PRINT:显示列标题
NOPRINT:隐藏列标题


例如：
COLUMN username HEADING '名称' FORMAT A22 JUSTIFY CENTER
COLUMN account_status HEADING '状态' FORMAT A20 JUSTIFY CENTER
select username, account_status
from dba_users;


//清除列格式

CLEAR COLUMNS

 

23、PAFESIZE命令

作用：使用PAGESIZE命令，可以设置每一页的大小，从而控制每一页显示的数据量。
语法：
          SET PAGESIZE n
说明：
         n:表示每一页大小的正整数，最大值可以是50000， 默认值为14，是行数

注：页并不是仅仅有输出的数据行构成的，而是由SQL*Plus显示到屏幕上的所有输出结果构成，包括标题和空行等。


24、LINESIZE命令
作用：设置一行显示的字符数量，默认数量为80（字符）。
语法
         SET LINESIZE n
说明
         n：屏幕上一行数据可以容纳的字符数量，有效范围为1~32767。

 

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
//创建简单报表,主要是为报表设置页眉和页脚


25、TTITLE命令

作用：为报表设置页眉

语法：

         TTI[TLE][printspec [text | variable] ...] | [OFF | ON]

说明：
         其中printspec的可选值有LEFT、CENTER、RIGHT、BOLD、FORMAT text、COL n、S[KIP][n]和TAB n。

         ON选项表示启用设置；

         OFF选项表示取消设置。还可以带有用户变量（内容有系统来维护），如SQL.PNO页号、SQL.LNO行号等。
例子：

          TTITLE LEFT '日期：' _DATE CENTER '执行'

          TTITLE OFF   //关闭页眉

26、BTITLE命令

作用：指定出现在报表中每一个页面底端的页脚。
语法：

         BTI[TLE] [printspec [text | variable ]...] | [OFF | ON ]

说明：
例如：
         BTITLE OFF  //关闭页脚
 

//以下命令为如何为创建的简单报表实现小计，例如实现数据的求和、求平均值等。

//计算小计
在执行查询操作或创建报表时，可以使用BREAK和COMPUTE命令，用来对输出的结果进行统计计算。其中
使用BREAK命令，可以让SQL*Plus根据列值的范围分隔输出结果，可以使得重复的列值不进行显示；
COMPUTE子句可以让SQL*Plus计算一列的值，并在最后输出显示。

27、BREAK命令

作用：让SQL*Plus根据列值的范围分隔输出结果，可以使得重复的列值不进行显示

语法：
         BRE[AK] [ON column_name] SKIP n
说明：
         column_name :表示对哪一列执行操作
         SKIP n：表示在指定列的值变化之前插入n个空行

例子：

         BREAK ON deptno

 

28、COMPUTE命令

作用：让SQL*Plus计算一列的值，并在最后输出显示

语法：

         COMP[UTE] function LABEL label OF column_name ON break_column_name

说明：

         function:表示执行的操作，例如SUM(求和)、MAXIMUM（最大值）、MINIMUM（最小值）、AVG(平均值)、COUNT(非空值得列数)、NUMBER(行数)、VARIANCE(方差)以及STD(均方差)等。
         LABEL 指定显示结果时的文本信息。

例如：

           BREAK ON deptno
           COMPUTE SUM OF sal ON deptno
           select empno,ename, mgr, hiredate,sal,deptno
           from scott.emp ORDER BY deptno;