/**PageBeginHtml Block Begin **/ /***自定义返回顶部小火箭***/ /*生成博客目录的JS 开始*/ /*生成博客目录的JS 结束*/

SQL*Plus命令

 

SQL*Plus命令

 

前言

image

一：SQL*Plus 与数据库的交互

image

 

二：设置SQL* Plus的运行环境

image

 

二     -   1 ：SET命令概述

image

image

image

image

 

二     -  2 ：使用SET命令设置运行环境

image

二     -  2 ____1：Pagesize 变量

image

  1 SYS@orcl> show pagesize
  2 pagesize 14
  3 SYS@orcl> set pagesize 18
  4 SYS@orcl> show pagesize
  5 pagesize 18
  6 SYS@orcl>
View Code

image

 

 

二     -  2 ____2：newpage 变量

image

  1 
  2 SYS@orcl> show newpage
  3 newpage 1
  4 SYS@orcl> set newpage 5
  5 SYS@orcl> show newpage
  6 newpage 5
  7 SYS@orcl>
View Code

image

 

二     -  2 ____3：linesize  变量

image

  1 SYS@orcl> show linesize
  2 linesize 80
  3 SYS@orcl> set linesize 3000
  4 SYS@orcl> show linesize
  5 linesize 3000
  6 SYS@orcl>
View Code

image

 

二     -  2 ____4：pause 变量

image

  1 SYS@orcl> show pause
  2 PAUSE is OFF
  3 SYS@orcl> set pause on
  4 SYS@orcl> show pause
  5 PAUSE is ON and set to ""
  6 SYS@orcl>
View Code

image

121

二     -  2 ____5：numformat 变量

image

image
image

  1 SYS@orcl> select ename,job,sal from scott.emp;
  2 
  3 
  4 
  5 
  6 
  7 
  8 ENAME      JOB              SAL
  9 ---------- --------- ----------
 10 SMITH      CLERK            800
 11 ALLEN      SALESMAN        1600
 12 WARD       SALESMAN        1250
 13 JONES      MANAGER         2975
 14 MARTIN     SALESMAN        1250
 15 BLAKE      MANAGER         2850
 16 CLARK      MANAGER         2450
 17 SCOTT      ANALYST         3000
 18 KING       PRESIDENT       5000
 19 TURNER     SALESMAN        1500
 20 ADAMS      CLERK           1100
 21 
 22 
 23 
 24 
 25 
 26 
 27 ENAME      JOB              SAL
 28 ---------- --------- ----------
 29 JAMES      CLERK            950
 30 FORD       ANALYST         3000
 31 MILLER     CLERK           1300
 32 
 33 14 rows selected.
 34 
 35 SYS@orcl> set pagesize 500
 36 SYS@orcl> show pagesize
 37 pagesize 500
 38 SYS@orcl> set numformat $999,999,999.00
 39 SYS@orcl> select ename,job,sal from scott.emp;
 40 
 41 
 42 
 43 
 44 
 45 
 46 ENAME      JOB                    SAL
 47 ---------- --------- ----------------
 48 SMITH      CLERK              $800.00
 49 ALLEN      SALESMAN         $1,600.00
 50 WARD       SALESMAN         $1,250.00
 51 JONES      MANAGER          $2,975.00
 52 MARTIN     SALESMAN         $1,250.00
 53 BLAKE      MANAGER          $2,850.00
 54 CLARK      MANAGER          $2,450.00
 55 SCOTT      ANALYST          $3,000.00
 56 KING       PRESIDENT        $5,000.00
 57 TURNER     SALESMAN         $1,500.00
 58 ADAMS      CLERK            $1,100.00
 59 JAMES      CLERK              $950.00
 60 FORD       ANALYST          $3,000.00
 61 MILLER     CLERK            $1,300.00
 62 
 63 14 rows selected.
 64
View Code

image

 

三：常用SQL*Plus 命令

image

 

三     -  1 ： hlep 命令

image

image

 

 

 

三     -  2 ： describe 命令

image

 

 

 

三     -  3 ：spool命令

image

 

三     -  4： define 命令

image

  1 SYS@orcl> define vjob='sajob'
  2 SYS@orcl> define vjob
  3 DEFINE VJOB            = "sajob" (CHAR)
  4 SYS@orcl>
View Code

image

 

三     -  5： show　命令

image

image

 

 

 

 

三     -  ６：　edit命令

image

image

 

 

三     -  7： save 命令

image

 

三     -  8： get命令

imageimage

 

 

三     - 9：  start 和 @ 命令

image

 

 

 

四：格式化查询结果

image

 

四     - 1：  column 命令

image

image

image

四     -  1 ____1：format 变量

image

 

四     -  1 ____2：heading 变量

image

  1 
  2 00:05:44 SYS@orcl> col empno heading 雇员编号
  3 00:06:07 SYS@orcl> col ename heading 雇员姓名
  4 00:06:17 SYS@orcl> col sal heading 雇员工资
  5 00:06:28 SYS@orcl> select empno,ename,sal from scott.emp;
  6 
  7 
  8 
  9 
 10 
 11 
 12         雇员编号 雇员姓名       雇员工资
 13 ---------------- ---------- ------------
 14        $7,369.00 SMITH             $8,00
 15        $7,499.00 ALLEN            $16,00
 16        $7,521.00 WARD             $12,50
 17        $7,566.00 JONES            $29,75
 18        $7,654.00 MARTIN           $12,50
 19        $7,698.00 BLAKE            $28,50
 20        $7,782.00 CLARK            $24,50
 21        $7,788.00 SCOTT            $30,00
 22        $7,839.00 KING             $50,00
 23        $7,844.00 TURNER           $15,00
 24        $7,876.00 ADAMS            $11,00
 25        $7,900.00 JAMES             $9,50
 26        $7,902.00 FORD             $30,00
 27        $7,934.00 MILLER           $13,00
 28 
 29 14 rows selected.
 30 
 31 00:06:47 SYS@orcl>
View Code

image

 

四     -  1 ____3：null 变量

image

  1 
  2 00:08:39 SYS@orcl> col comm null '空值'
  3 00:08:57 SYS@orcl> select empno,ename,comm from scott.emp where comm is null;
  4 
  5 
  6 
  7 
  8 
  9 
 10         雇员编号 雇员姓名               COMM
 11 ---------------- ---------- ----------------
 12        $7,369.00 SMITH      空值
 13        $7,566.00 JONES      空值
 14        $7,698.00 BLAKE      空值
 15        $7,782.00 CLARK      空值
 16        $7,788.00 SCOTT      空值
 17        $7,839.00 KING       空值
 18        $7,876.00 ADAMS      空值
 19        $7,900.00 JAMES      空值
 20        $7,902.00 FORD       空值
 21        $7,934.00 MILLER     空值
 22 
 23 10 rows selected.
 24 
 25 00:09:19 SYS@orcl>
View Code

image

 

 

四     -  1 ____4: ON|OFF选项

image

 

image

 

 

四     - 2：  tttle  和 btitle 命令

image

image

  1 00:16:38 SYS@orcl> set pagesize 8
  2 00:16:43 SYS@orcl> ttitle left '销售情况排行表'
  3 00:16:59 SYS@orcl> btitle left '打印日期：2017 /12/24 打印人： 一品堂'
  4 00:17:39 SYS@orcl> select * from scott.salgrade;
  5 
  6 
  7 
  8 
  9 
 10 
 11 销售情况排行表
 12            GRADE            LOSAL            HISAL
 13 ---------------- ---------------- ----------------
 14            $1.00          $700.00        $1,200.00
 15 ‘打印日期：201712/24 打印人：烈黄诽
 16
View Code

image

  1 
  2 00:19:18 SYS@orcl> set pagesize 8;
  3 00:19:24 SYS@orcl> define varT= '               雇员信息表'
  4 00:19:40 SYS@orcl> define var8=' 操作人：一品堂'
  5 00:20:02 SYS@orcl> ttitle  left varT;
  6 00:20:45 SYS@orcl> btitle  left var8;
  7 00:20:56 SYS@orcl> select empno,ename,job from scott.emp;
  8 
  9 
 10 
 11 
 12 
 13 
 14                雇员信息表
 15         雇员编号 雇员姓名   JOB
 16 ---------------- ---------- ---------
 17        $7,369.00 SMITH      CLERK
 18  操作人：一品堂‘
 19
View Code

image

 

 

 

 

 

 

 

 

————————————————————————————————————————————————————————————————

 

 

 

 

 

 

 

 

 

 

 

————————————————————————————————————————————————————————————————

posted @ 2017-12-23 22:59  一品堂.技术学习笔记  阅读(491)  评论(0)  编辑  收藏  举报