Oracle SQL Plus Tutorial 学习笔记

普通登录oracle的大体流程：

sqlplus  --> user name & passwd  --> host  回到操作系统环境 -->  echo %oracle_sid% #装在windows上的oracle，可以用该命令。 -->define #查看定义的oracle环境变量。 -->show parameters/user   #显示oracle系统参数/当前用户。

以管理员身份登录oracle的大体流程：
SQL>connect sys as sysdba   #也可在同一行输入用户名和密码不安全，linux中用ps可以看到，最好分开输入。 --> enter passwd

查看当前用户的所有的表名的命令：

SQL> select table_name from user_tables;

SQL> select * from v$version; 查看oracle数据库的版本。

打算用oracle自带的scott用户登陆做练习，结果提示ORA-28000: the account is locked。

解决办法，用命令解锁。以管理员身份登录，然后
SQL> alter user scott account unlock;
User altered.
SQL> commit;
Commit complete.
SQL> conn scott/tiger;
ERROR:
ORA-28001: the password has expired
Changing password for scott
New password: 
Retype new password: 
Password changed
Connected.
SQL> show user;
USER is "SCOTT"

若记scott的密码，可用以下方法修改密码。
SQL> conn /as sysdba
Connect.
SQL> show user;
USER is "SYS"
SQL> alter user scott identified by scott;
User altered.
SQL>commit;
Commit complete.
SQL>conn scott/scott;
Connected.
SQL> show user;
USER is "SCOTT"

注释：以下实验操作基于linux上安装的oracle 10g中默认的用户scott，默认密码tiger, 以及scott的emp表。


[oracle@redhat ~]$ sqlplus


SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 15 22:41:37 2014


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Enter user-name: scott
Enter password: 


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> show user
USER is "SCOTT"
SQL> select table_name from user_tables;


TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE


SQL> desc emp;   #desc代表describe
 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)


SQL> select empno, ename, job, mgr, hiredate
  2  from emp
  3  order by 1;  # 1是指按照第一列排序，数字由小到大。


     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- ---------
      7369 SMITH      CLERK           7902 17-DEC-80
      7499 ALLEN      SALESMAN        7698 20-FEB-81
      7521 WARD       SALESMAN        7698 22-FEB-81
      7566 JONES      MANAGER         7839 02-APR-81
      7654 MARTIN     SALESMAN        7698 28-SEP-81
      7698 BLAKE      MANAGER         7839 01-MAY-81
      7782 CLARK      MANAGER         7839 09-JUN-81
      7788 SCOTT      ANALYST         7566 19-APR-87
      7839 KING       PRESIDENT            17-NOV-81
      7844 TURNER     SALESMAN        7698 08-SEP-81
      7876 ADAMS      CLERK           7788 23-MAY-87


     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- ---------
      7900 JAMES      CLERK           7698 03-DEC-81
      7902 FORD       ANALYST         7566 03-DEC-81
      7934 MILLER     CLERK           7782 23-JAN-82


14 rows selected.


SQL> set pagesize 200   #设置页面的最大行数。
SQL> /


     EMPNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- ---------
      7369 SMITH      CLERK           7902 17-DEC-80
      7499 ALLEN      SALESMAN        7698 20-FEB-81
      7521 WARD       SALESMAN        7698 22-FEB-81
      7566 JONES      MANAGER         7839 02-APR-81
      7654 MARTIN     SALESMAN        7698 28-SEP-81
      7698 BLAKE      MANAGER         7839 01-MAY-81
      7782 CLARK      MANAGER         7839 09-JUN-81
      7788 SCOTT      ANALYST         7566 19-APR-87
      7839 KING       PRESIDENT            17-NOV-81
      7844 TURNER     SALESMAN        7698 08-SEP-81
      7876 ADAMS      CLERK           7788 23-MAY-87
      7900 JAMES      CLERK           7698 03-DEC-81
      7902 FORD       ANALYST         7566 03-DEC-81
      7934 MILLER     CLERK           7782 23-JAN-82


14 rows selected.




SQL> l  #显示buffer缓存。同下面的list。
  1  select empno, ename, job, mgr, hiredate
  2  from emp
  3* order by 1
SQL> list
  1  select empno, ename, job, mgr, hiredate
  2  from emp
  3* order by 1


SQL> 1  #阿拉伯数字一，输出缓存中的第一行。
  1* select empno, ename, job, mgr, hiredate
SQL> c/empno/deptno     #将缓存中的第一行中的empno换成deptno
  1* select deptno, ename, job, mgr, hiredate
SQL> l
  1  select deptno, ename, job, mgr, hiredate
  2  from emp
  3* order by 1


SQL 与SQL*Plus的区别
(1)定义
SQL(Structured Query Language)结构化查询语言，是所有关系型数据库通用的一种语言。
SQL*Plus是oracle的一种专门的工具，它是用户和oracle数据库的接口工具，它不是用户和oracle数据库交互的唯一工具。
(2)命令
SQL命令举例
select * from emp;
delete from emp where job = 'manager';
insert into emp(empno, ename, job) values(9999, 'William Wycherley', 'Sales Manager');
commit;  
rollback;   #this is the opposite of 'commit'
SQL*PLUS命令举例
set linesize 120
set pagesize 60
col ename format a15
col sal format $999,999.00
set pause on
set pause off
connect
ed
save 
spool
help index
(3)缓存的区别
SQL*PLUS 不会保存buffer，当你退出SQL*PLUS，重新登陆时，它会忘记你之前输入的命令。


SQL> /


    DEPTNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- ---------
        10 CLARK      MANAGER         7839 09-JUN-81
        10 KING       PRESIDENT            17-NOV-81
        10 MILLER     CLERK           7782 23-JAN-82
        20 JONES      MANAGER         7839 02-APR-81
        20 FORD       ANALYST         7566 03-DEC-81
        20 ADAMS      CLERK           7788 23-MAY-87
        20 SMITH      CLERK           7902 17-DEC-80
        20 SCOTT      ANALYST         7566 19-APR-87
        30 WARD       SALESMAN        7698 22-FEB-81
        30 TURNER     SALESMAN        7698 08-SEP-81
        30 ALLEN      SALESMAN        7698 20-FEB-81
        30 JAMES      CLERK           7698 03-DEC-81
        30 BLAKE      MANAGER         7839 01-MAY-81
        30 MARTIN     SALESMAN        7698 28-SEP-81


14 rows selected.


SQL> alter session set nls_date_format ='mm/dd/yyyy';


Session altered.


SQL> /


Session altered.


SQL> select deptno, ename, job, mgr, hiredate
  2  from emp
  3  order by 2;


    DEPTNO ENAME      JOB              MGR HIREDATE
---------- ---------- --------- ---------- ----------
        20 ADAMS      CLERK           7788 05/23/1987
        30 ALLEN      SALESMAN        7698 02/20/1981
        30 BLAKE      MANAGER         7839 05/01/1981
        10 CLARK      MANAGER         7839 06/09/1981
        20 FORD       ANALYST         7566 12/03/1981
        30 JAMES      CLERK           7698 12/03/1981
        20 JONES      MANAGER         7839 04/02/1981
        10 KING       PRESIDENT            11/17/1981
        30 MARTIN     SALESMAN        7698 09/28/1981
        10 MILLER     CLERK           7782 01/23/1982
        20 SCOTT      ANALYST         7566 04/19/1987
        20 SMITH      CLERK           7902 12/17/1980
        30 TURNER     SALESMAN        7698 09/08/1981
        30 WARD       SALESMAN        7698 02/22/1981


14 rows selected.


修改sal列的数字显示格式。 
SQL> select deptno, ename, job, mgr, hiredate, sal
  2  from emp
  3  order by 1;


    DEPTNO ENAME      JOB              MGR HIREDATE          SAL
---------- ---------- --------- ---------- ---------- ----------
        10 CLARK      MANAGER         7839 06/09/1981       2450
        10 KING       PRESIDENT            11/17/1981       5000
        10 MILLER     CLERK           7782 01/23/1982       1300
        20 JONES      MANAGER         7839 04/02/1981       2975
        20 FORD       ANALYST         7566 12/03/1981       3000
        20 ADAMS      CLERK           7788 05/23/1987       1100
        20 SMITH      CLERK           7902 12/17/1980        800
        20 SCOTT      ANALYST         7566 04/19/1987       3000
        30 WARD       SALESMAN        7698 02/22/1981       1250
        30 TURNER     SALESMAN        7698 09/08/1981       1500
        30 ALLEN      SALESMAN        7698 02/20/1981       1600
        30 JAMES      CLERK           7698 12/03/1981        950
        30 BLAKE      MANAGER         7839 05/01/1981       2850
        30 MARTIN     SALESMAN        7698 09/28/1981       1250


14 rows selected.


SQL> col sal format $999,999.00
SQL> /


    DEPTNO ENAME      JOB              MGR HIREDATE            SAL
---------- ---------- --------- ---------- ---------- ------------
        10 CLARK      MANAGER         7839 06/09/1981    $2,450.00
        10 KING       PRESIDENT            11/17/1981    $5,000.00
        10 MILLER     CLERK           7782 01/23/1982    $1,300.00
        20 JONES      MANAGER         7839 04/02/1981    $2,975.00
        20 FORD       ANALYST         7566 12/03/1981    $3,000.00
        20 ADAMS      CLERK           7788 05/23/1987    $1,100.00
        20 SMITH      CLERK           7902 12/17/1980      $800.00
        20 SCOTT      ANALYST         7566 04/19/1987    $3,000.00
        30 WARD       SALESMAN        7698 02/22/1981    $1,250.00
        30 TURNER     SALESMAN        7698 09/08/1981    $1,500.00
        30 ALLEN      SALESMAN        7698 02/20/1981    $1,600.00
        30 JAMES      CLERK           7698 12/03/1981      $950.00
        30 BLAKE      MANAGER         7839 05/01/1981    $2,850.00
        30 MARTIN     SALESMAN        7698 09/28/1981    $1,250.00


14 rows selected.


修改SAL列的列名。
SQL> col sal heading salary;
SQL> /


    DEPTNO ENAME      JOB              MGR HIREDATE         salary
---------- ---------- --------- ---------- ---------- ------------
        10 CLARK      MANAGER         7839 06/09/1981    $2,450.00
        10 KING       PRESIDENT            11/17/1981    $5,000.00
        10 MILLER     CLERK           7782 01/23/1982    $1,300.00
        20 JONES      MANAGER         7839 04/02/1981    $2,975.00
        20 FORD       ANALYST         7566 12/03/1981    $3,000.00
        20 ADAMS      CLERK           7788 05/23/1987    $1,100.00
        20 SMITH      CLERK           7902 12/17/1980      $800.00
        20 SCOTT      ANALYST         7566 04/19/1987    $3,000.00
        30 WARD       SALESMAN        7698 02/22/1981    $1,250.00
        30 TURNER     SALESMAN        7698 09/08/1981    $1,500.00
        30 ALLEN      SALESMAN        7698 02/20/1981    $1,600.00
        30 JAMES      CLERK           7698 12/03/1981      $950.00
        30 BLAKE      MANAGER         7839 05/01/1981    $2,850.00
        30 MARTIN     SALESMAN        7698 09/28/1981    $1,250.00


14 rows selected.


set命令
SQL> set underline '=';  
SQL> /


    DEPTNO ENAME      JOB              MGR HIREDATE         salary
========== ========== ========= ========== ========== ============
        10 CLARK      MANAGER         7839 06/09/1981    $2,450.00
        10 KING       PRESIDENT            11/17/1981    $5,000.00
        10 MILLER     CLERK           7782 01/23/1982    $1,300.00
        20 JONES      MANAGER         7839 04/02/1981    $2,975.00
        20 FORD       ANALYST         7566 12/03/1981    $3,000.00
        20 ADAMS      CLERK           7788 05/23/1987    $1,100.00
        20 SMITH      CLERK           7902 12/17/1980      $800.00
        20 SCOTT      ANALYST         7566 04/19/1987    $3,000.00
        30 WARD       SALESMAN        7698 02/22/1981    $1,250.00
        30 TURNER     SALESMAN        7698 09/08/1981    $1,500.00
        30 ALLEN      SALESMAN        7698 02/20/1981    $1,600.00
        30 JAMES      CLERK           7698 12/03/1981      $950.00
        30 BLAKE      MANAGER         7839 05/01/1981    $2,850.00
        30 MARTIN     SALESMAN        7698 09/28/1981    $1,250.00


14 rows selected.


增加一列
SQL> a  ,comm     #a代表add，后面两个空格，一个逗号，新加的列名。
  1* select deptno, ename, job, mgr, hiredate, sal ,comm
SQL> /


    DEPTNO ENAME      JOB              MGR HIREDATE         salary       COMM
========== ========== ========= ========== ========== ============ ==========
        10 CLARK      MANAGER         7839 06/09/1981    $2,450.00
        10 KING       PRESIDENT            11/17/1981    $5,000.00
        10 MILLER     CLERK           7782 01/23/1982    $1,300.00
        20 JONES      MANAGER         7839 04/02/1981    $2,975.00
        20 FORD       ANALYST         7566 12/03/1981    $3,000.00
        20 ADAMS      CLERK           7788 05/23/1987    $1,100.00
        20 SMITH      CLERK           7902 12/17/1980      $800.00
        20 SCOTT      ANALYST         7566 04/19/1987    $3,000.00
        30 WARD       SALESMAN        7698 02/22/1981    $1,250.00        500
        30 TURNER     SALESMAN        7698 09/08/1981    $1,500.00          0
        30 ALLEN      SALESMAN        7698 02/20/1981    $1,600.00        300
        30 JAMES      CLERK           7698 12/03/1981      $950.00
        30 BLAKE      MANAGER         7839 05/01/1981    $2,850.00
        30 MARTIN     SALESMAN        7698 09/28/1981    $1,250.00       1400


14 rows selected.


SQL> col comm like sal heading "Commission";   #虽然此处显示的列名是salary，但调用的的时候用原来的sal。
SQL> /


    DEPTNO ENAME      JOB              MGR HIREDATE         salary   Commission
========== ========== ========= ========== ========== ============ ============
        10 CLARK      MANAGER         7839 06/09/1981    $2,450.00
        10 KING       PRESIDENT            11/17/1981    $5,000.00
        10 MILLER     CLERK           7782 01/23/1982    $1,300.00
        20 JONES      MANAGER         7839 04/02/1981    $2,975.00
        20 FORD       ANALYST         7566 12/03/1981    $3,000.00
        20 ADAMS      CLERK           7788 05/23/1987    $1,100.00
        20 SMITH      CLERK           7902 12/17/1980      $800.00
        20 SCOTT      ANALYST         7566 04/19/1987    $3,000.00
        30 WARD       SALESMAN        7698 02/22/1981    $1,250.00      $500.00
        30 TURNER     SALESMAN        7698 09/08/1981    $1,500.00         $.00
        30 ALLEN      SALESMAN        7698 02/20/1981    $1,600.00      $300.00
        30 JAMES      CLERK           7698 12/03/1981      $950.00
        30 BLAKE      MANAGER         7839 05/01/1981    $2,850.00
        30 MARTIN     SALESMAN        7698 09/28/1981    $1,250.00    $1,400.00


14 rows selected.


SQL> col job format a10  #知道列的格式
SQL> /


    DEPTNO ENAME      JOB               MGR HIREDATE         salary   Commission
========== ========== ========== ========== ========== ============ ============
        10 CLARK      MANAGER          7839 06/09/1981    $2,450.00
        10 KING       PRESIDENT             11/17/1981    $5,000.00
        10 MILLER     CLERK            7782 01/23/1982    $1,300.00
        20 JONES      MANAGER          7839 04/02/1981    $2,975.00
        20 FORD       ANALYST          7566 12/03/1981    $3,000.00
        20 ADAMS      CLERK            7788 05/23/1987    $1,100.00
        20 SMITH      CLERK            7902 12/17/1980      $800.00
        20 SCOTT      ANALYST          7566 04/19/1987    $3,000.00
        30 WARD       SALESMAN         7698 02/22/1981    $1,250.00      $500.00
        30 TURNER     SALESMAN         7698 09/08/1981    $1,500.00         $.00
        30 ALLEN      SALESMAN         7698 02/20/1981    $1,600.00      $300.00
        30 JAMES      CLERK            7698 12/03/1981      $950.00
        30 BLAKE      MANAGER          7839 05/01/1981    $2,850.00
        30 MARTIN     SALESMAN         7698 09/28/1981    $1,250.00    $1,400.00


14 rows selected.




SQL> col comm clear   # 清除别名
SQL> /


    DEPTNO ENAME      JOB               MGR HIREDATE         salary       COMM
========== ========== ========== ========== ========== ============ ==========
        10 CLARK      MANAGER          7839 06/09/1981    $2,450.00
        10 KING       PRESIDENT             11/17/1981    $5,000.00
        10 MILLER     CLERK            7782 01/23/1982    $1,300.00
        20 JONES      MANAGER          7839 04/02/1981    $2,975.00
        20 FORD       ANALYST          7566 12/03/1981    $3,000.00
        20 ADAMS      CLERK            7788 05/23/1987    $1,100.00
        20 SMITH      CLERK            7902 12/17/1980      $800.00
        20 SCOTT      ANALYST          7566 04/19/1987    $3,000.00
        30 WARD       SALESMAN         7698 02/22/1981    $1,250.00        500
        30 TURNER     SALESMAN         7698 09/08/1981    $1,500.00          0
        30 ALLEN      SALESMAN         7698 02/20/1981    $1,600.00        300
        30 JAMES      CLERK            7698 12/03/1981      $950.00
        30 BLAKE      MANAGER          7839 05/01/1981    $2,850.00
        30 MARTIN     SALESMAN         7698 09/28/1981    $1,250.00       1400


14 rows selected.


SQL> clear columns;   #清楚所有列的格式，日期列除外。
columns cleared
SQL> /


    DEPTNO ENAME      JOB              MGR HIREDATE          SAL       COMM
========== ========== ========= ========== ========== ========== ==========
        10 CLARK      MANAGER         7839 06/09/1981       2450
        10 KING       PRESIDENT            11/17/1981       5000
        10 MILLER     CLERK           7782 01/23/1982       1300
        20 JONES      MANAGER         7839 04/02/1981       2975
        20 FORD       ANALYST         7566 12/03/1981       3000
        20 ADAMS      CLERK           7788 05/23/1987       1100
        20 SMITH      CLERK           7902 12/17/1980        800
        20 SCOTT      ANALYST         7566 04/19/1987       3000
        30 WARD       SALESMAN        7698 02/22/1981       1250        500
        30 TURNER     SALESMAN        7698 09/08/1981       1500          0
        30 ALLEN      SALESMAN        7698 02/20/1981       1600        300
        30 JAMES      CLERK           7698 12/03/1981        950
        30 BLAKE      MANAGER         7839 05/01/1981       2850
        30 MARTIN     SALESMAN        7698 09/28/1981       1250       1400


14 rows selected.


SQL> l
  1  select deptno, ename, job, mgr, hiredate, sal ,comm
  2  from emp
  3* order by 1
SQL> save all_emp.sql; #将缓存中的sql语句保存到all_emp.sql文件中，all_emp.sql将保存在启动SQL*PLUS的文件夹。save只保存最近的sql语句。
Created file all_emp.sql
SQL> @all_emp.sql;      #@all_emp来执行all_emp.sql文件。


    DEPTNO ENAME      JOB              MGR HIREDATE          SAL       COMM
========== ========== ========= ========== ========== ========== ==========
        10 CLARK      MANAGER         7839 06/09/1981       2450
        10 KING       PRESIDENT            11/17/1981       5000
        10 MILLER     CLERK           7782 01/23/1982       1300
        20 JONES      MANAGER         7839 04/02/1981       2975
        20 FORD       ANALYST         7566 12/03/1981       3000
        20 ADAMS      CLERK           7788 05/23/1987       1100
        20 SMITH      CLERK           7902 12/17/1980        800
        20 SCOTT      ANALYST         7566 04/19/1987       3000
        30 WARD       SALESMAN        7698 02/22/1981       1250        500
        30 TURNER     SALESMAN        7698 09/08/1981       1500          0
        30 ALLEN      SALESMAN        7698 02/20/1981       1600        300
        30 JAMES      CLERK           7698 12/03/1981        950
        30 BLAKE      MANAGER         7839 05/01/1981       2850
        30 MARTIN     SALESMAN        7698 09/28/1981       1250       1400


14 rows selected.


SQL> 3 where job = 'PRESIDENT'
SQL> l
  1  select deptno, ename, job, mgr, hiredate, sal ,comm
  2  from emp
  3* where job = 'PRESIDENT'
SQL> save /tmp/president.sql #将缓存中的sql语句保存到 /tmp目录下的 president.sql文件。 若该目录下已经存在同名文件，则用
SQL> save /tmp/president.sql replace
SQL> @/tmp/president.sql


    DEPTNO ENAME      JOB              MGR HIREDATE          SAL       COMM
========== ========== ========= ========== ========== ========== ==========
        10 KING       PRESIDENT            11/17/1981       5000