oracle学习笔记--sqlplus基本命令

最新推荐文章于 2023-05-06 01:52:01 发布

polo_longsan

最新推荐文章于 2023-05-06 01:52:01 发布

阅读量605

点赞数

分类专栏： Oracle 文章标签： Oracle数据库

本文链接：https://blog.csdn.net/polo_longsan/article/details/50837189

版权

Oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

sqlplus基本命令如下：

1、查看表结构DESC，

SQL> DESC emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

2、修改SQLPLUS显示屏幕字符宽度，SET LINE[SIZE] {80|n}，80为默认值，n为自然数，该命令标识将显示屏的的显示输出设置为n个字符那么宽。

未修改前：

SQL> SELECT *
  2  FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM
---------- ---------- --------- ---------- ------------ ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80           800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500
        30

修改后：

SQL> SET line 100
SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20

3、输入错误的sql语句之后的修改。L(List)列出刚才输入的SQL语句，n text修改刚才出错的行，‘/’执行刚才的sql语句

输入sql语句执行报错，并显示报错行号

SQL> SELECT empno,ename,job,sal
  2  FROM dept
  3  WHERE sal>=1500
  4  ORDER BY job,sal DESC;
WHERE sal>=1500
      *
ERROR at line 3:
ORA-00904: "SAL": invalid identifier

修改报错的语句并显示：

SQL> 2 FROM emp
SQL> L
  1  SELECT empno,ename,job,sal
  2  FROM emp
  3  WHERE sal>=1500
  4* ORDER BY job,sal DESC

自行修改之后的语句：

SQL> /

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500

4、删除缓存中sql语句的一行。，del n

SQL> SELECT empno,ename,job,sal
  2  FROM emp
  3  WHERE sal>=1500
  4  ORDER BY job,sal DESC;


     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500


8 rows selected.


SQL> L
  1  SELECT empno,ename,job,sal
  2  FROM emp
  3  WHERE sal>=1500
  4* ORDER BY job,sal DESC

删除第四行：

SQL> DEL 4
SQL> l
  1  SELECT empno,ename,job,sal
  2  FROM emp
  3* WHERE sal>=1500
SQL> /

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7499 ALLEN      SALESMAN        1600
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7788 SCOTT      ANALYST         3000
      7839 KING       PRESIDENT       5000
      7844 TURNER     SALESMAN        1500
      7902 FORD       ANALYST         3000

5、修改缓存中sql文件字符，C(HANGE)

SQL> SELECT empno,ename,job,sal
  2  FROM dept
  3  WHERE sal>=1500
  4  ORDER BY job,sal DESC;
WHERRE sal>=1500
       *
ERROR at line 3:
ORA-00933: SQL command not properly ended

将表名dept修改成emp:

SQL> C /dept/emp
SP2-0023: String not found.

报错是因为单前行不是第二行，首先定位到第二行：

SQL> 2
  2* FROM dept
SQL> C dept/emp
SP2-0023: String not found.
SQL> C /dept/emp
  2* FROM emp

'L'查看当前sql，再运行‘/’:

SQL> L
  1  SELECT empno,ename,job,sal
  2  FROM emp
  3  WHERE sal>=1500
  4* ORDER BY job,sal DESC
SQL> /

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500

6、生成脚本文件，save命令把sql缓冲区的内容存入指定的文件。

SQL> SELECT empno,ename,job,sal
  2  FROM emp
  3  WHERE sal>=1500
  4  ORDER BY job,sal DESC;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500

8 rows selected.

SQL> SAVE D:/SQL/TEST2.sql
Created file D:/SQL/TEST2.sql

备注：需要首先建好SQL文件夹。sqlplus的命令不会写入脚本文件中去

7、将脚本文件读入缓冲区命令get

SQL> get d:\SQL\TEST2.sql
  1  SELECT empno,ename,job,sal
  2  FROM emp
  3  WHERE sal>=1500
  4* ORDER BY job,sal DESC
SQL> l
  1  SELECT empno,ename,job,sal
  2  FROM emp
  3  WHERE sal>=1500
  4* ORDER BY job,sal DESC
SQL>

8、使用ed命令对脚本文件进行编辑

SQL> ed d:\SQL\TEST2

9、直接运行脚本文件,@或者START命令是把脚本文件的内容装入缓冲区并运行

SQL> @D:\SQL\TEST2.sql

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500

8 rows selected.

10、把屏幕上内容输出到文件,SPOOL命令。SPOOL后面跟一个文件名，他会把命令下的内容输出到OUTPUT文件，并且只有输出SPOOL OFF是才能看到文件中的内容。
文件。

SQL> SPOOL D:\SQL\OUTPUT
SQL> SELECT empno,ename,job,sal
  2  FROM emp
  3  WHERE sal>=1500
  4  ORDER BY job,sal DESC;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500

8 rows selected.

SQL> SPOOL OFF;

polo_longsan

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle学习笔记--sqlplus基本命令

sqlplus基本命令如下： 1、查看表结构DESC，SQL> DESC emp; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO
复制链接

扫一扫