SQLPLUS中幾個常用命令———摘自網路

SQLPLUS中幾個常用命令
上一篇 / 下一篇  2010-08-27 16:10:13
檢視( 450 ) / 評論( 3 ) / 評分( 10 / 5 )
SQL> set echo on—————————————————設定執行命令是是否顯示語句
SQL> set feedback on———————————————-設定顯示“已選擇XX行”
SQL> set colsep | —————————————————設定列與列之間的分割符號
SQL> set pagesize 10———————————————–設定每一頁的行數
SQL> SET SERVEROUTPUT ON——————————-設定允許顯示輸出類似dbms_output.putline
SQL> set heading on————————————————設定顯示列名
SQL> set timing on————————————————–設定顯示“已用時間：XXXX”
SQL> set time on—————————————————–設定顯示當前時間
SQL> set autotrace on———————————————–設定允許對執行的sql進行分析
sqlplus sys/sys@DODO as sysdba
sqlplus /as sysdba
sqlplus /nolog
SQL>conn sys/sys@DODO as sysdba
    1、執行一個SQL指令碼

    SQL> start D:\a.sql
    SQL> @ D:\a.sql
    注：若需要在一個指令碼中呼叫另一個指令碼，則使用 @@D:\a.sql

    2、重新執行上一次命令：

    SQL> /              --也可使用 run/r 來替代執行

    3、編輯指令碼：

    SQL> edit            --編輯當前輸入的文字(前1次執行的部分)
    SQL> edit D:\a.sql  --編輯制定文件
    注：在txt中編輯完成後儲存，關閉即修改完成，使用“/”後執行

    4、儲存指令碼：

    SQL> save a          --自動存檔為a.sql 儲存在C:\Documents and Settings\wangxiaoqi
    SQL> save D:\a      --儲存到D:\a.sql

    5、匯入指令碼：

    SQL> get D:\a

    6、顯示一個表結構：

    SQL> desc tab        --如果tab不是表，則只顯示其型別和名字

    7、儲存所有輸入：

    SQL> spool D:\xxx    --建立一個xxx.LST檔案
    SQL> spool            --顯示當前spool狀態
    SQL> select * from dual;
    SQL> spool off      --結束錄入
    
    8、執行過程
    SQL> execute  a;

    9、增加頁首和頁尾：

    SQL> TTITLE abc      --新增頁首“abc”
    SQL> BTITLE def      --新增頁尾“def”
    SQL> TTITLE OFF      --取消頁首顯示

三、COLUMN[COL]命令修改欄位屬性：

    可以修改的屬性有：
    ALI[AS] alias
    CLE[AR]
    FOLD_A[FTER]
    FOLD_B[EFORE]
    FOR[MAT] format
    HEA[DING] text
    JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
    LIKE { expr|alias}
    NEWL[INE]
    NEW_V[ALUE] variable
    NOPRI[NT]|PRI[NT]
    NUL[L] text
    OLD_V[ALUE] variable
    ON|OFF
    WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
    注：若不同表的同列名，會一起改掉！

    1、HEADING--改變欄位名

    SQL> COLUMN id HEADING 'XX|YY'  --使用'|'可將列名顯示為兩行

    2、FORMAT--改變字元長度 & 格式化數值

    SQL> COLUMN id FORMAT a20      --此欄位長20個字元，只能針對字元，若為數字則無法正常顯示
    SQL> COLUMN id FORMAT $999.00  --字首$，小數點前3位，小數點後2位四捨五入

    3、JUSTIFY--改變欄位名顯示位置

    SQL> COLUMN id JUSTIFY center  --居中顯示，預設的都是靠右(r )顯示

    4、NOPRINT--不顯示

    SQL> COLUMN id NOPRINT          --不顯示出來，可用PRINT設定回來

    5、NULL--設定NULL值顯示

    SQL> COLUMN COMM NULL 0.00      --將NULL值設定位0.00  注意必須加上COMM  注：不起作用。 

    6、WRAPPED--設定迴繞方式

    SQL> COLUMN id FORMAT a5        --設定長度為5
    SQL> COLUMN id WRAPPED        --表示直接按長度迴繞
    SQL> COLUMN id WORD_WRAPPED  --按單詞迴繞
    SQL> COLUMN id TRUNCATED      --直接按長度截斷

    7、COLUMN--顯示當前列所有屬性

    SQL> COLUMN id                  --後面不加列名時顯示所有的COLUMN

    8、OFF|ON--設定某一欄位關閉

    SQL> COLUMN id OFF

    9、CLEAR--清空所有欄位屬性

    SQL> CLEAR COLUMNS



四、SQL*Plus的系統引數：

    show all        --顯示所有系統引數的當前值
    show [引數]      --顯示某個系統引數值
    set  [引數][值]  --設定系統引數值

    appinfo is OFF and set to "SQL*Plus"
    arraysize 15
    SQL*Plus一次從oracle獲取的行數，設定越大可提高效率，但是對記憶體有要求，1000以上效果不大
    autocommit OFF
    針對session的自動提交 SQL> SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}  --有IMM[EDIATE]則無需再ON
    autoprint OFF
    autorecovery OFF
    autotrace OFF
    設定可以對執行的SQL進行分析 SQL> SET AUTOTRACE ON
    blockterminator "." (hex 2e)
    btitle OFF and is the first few characters of the next SELECT statement
    cmdsep OFF
    colsep " "
    設定列與列之間的分割符號 SQL> SET COLSEP '|'
    compatibility version NATIVE
    資料庫版本，NATIVE為預設，可制定V6,V7,V8
    concat "." (hex 2e)
    copycommit 0
    COPYTYPECHECK is ON
    define "&" (hex 26)
    替換變數時所使用的字元
    describe DEPTH 1 LINENUM OFF INDENT ON
    echo OFF
    是否顯示執行程式碼 SQL> SET ECHO {ON|OFF}
    editfile "afiedt.buf"
    embedded OFF
    escape OFF
    FEEDBACK ON for 6 or more rows
    查詢、修改時所影響的行數 SQL> SET FEED[BACK] {6|n|ON|OFF}
    flagger OFF
    flush ON
    heading ON
    是否顯示列標題 SQL> SET HEA[DING] {ON|OFF}
    headsep "|" (hex 7c)
    instance "local"
    linesize 100
    設定一行可容納的字元數 SQL> SET LIN[ESIZE] {80|n}
    lno 8
    loboffset 1
    logsource ""
    long 5000
    longchunksize 80
    markup HTML OFF ...
    newpage 1
    頁與頁之間的分隔符 SQL> SET NEWP[AGE] {1|n|NONE}
              當set newpage 0 時，會在每頁的開頭有一個小的黑方框。
              當set newpage n 時，會在頁和頁之間隔著n個空行。
              當set newpage none 時，會在頁和頁之間沒有任何間隔。
    null ""
    設定null值  SQL> SET NULL 0.00
                    但是此值顯示出來後必為字元型
    numformat ""
    numwidth 10
    設定number型別的長度限制 SQL> SET NUM[WIDTH] 16
    pagesize 9999
    一頁顯示多少行 SQL> SET PAGES[IZE] {24|n}
                    當設定為0時，則所有行顯示在一頁中，並且不顯示標題行
    PAUSE is OFF
    pno 1
    recsep WRAP
    recsepchar " " (hex 20)
    release 1002000100
    repfooter OFF and is NULL
    repheader OFF and is NULL
    serveroutput ON SIZE 1000000 FORMAT WORD_WRAPPED
    輸出DBMS.OUTPUT時是否顯示 SQL> SET SERVEROUT[PUT] {ON|OFF} [size n]
    shiftinout INVISIBLE
    showmode OFF
    spool OFF
    sqlblanklines OFF
    sqlcase MIXED
    sqlcode 0
    sqlcontinue "> "
    sqlnumber ON
    sqlpluscompatibility 10.2.0
    sqlprefix "#" (hex 23)
    sqlprompt " wangxiaoqi@DODO > "
    sqlterminator ";" (hex 3b)
    suffix "sql"
    tab ON
    termout ON
    是否顯示輸出內容，例如spool輸出  SQL> SET TERM[OUT] {ON|OFF}
    timing OFF
    SQL語句執行花費時間顯示  set TIMING  {ON|OFF}
    trimout ON
    標準輸出中每行最後的空格是否去掉 SQL> SET TRIMS[OUT] {ON|OFF}
    trimspool ON
    spool標準輸出中每行最後的空格是否去掉 SQL> SET TRIMS[OUT] {ON|OFF}
    ttitle OFF and is the first few characters of the next SELECT statement
    underline "-" (hex 2d)
    USER is "WANGXIAOQI"
    verify ON
    wrap : lines will be wrapped
    當長度超過時是否迴繞  SQL> SET WRA[P] {ON|OFF}  --基本上都要ON



五、一些常用的小操作：

    show user                      --檢視當前登入使用者
    show errors                    --顯示錯誤資訊
    show rel[ease]                --顯示版本
    show SGA                      --顯示SGA
    set time on                    --前端始終顯示時間
    select name from v$database;  --檢視當前所在資料庫
    select * from v$instance;      --檢視所有資料庫例項(似乎沒什麼用)
    select * from V_$PWFILE_USERS; --檢視那些使用者有SYSDBA/SYSOPER許可權
show和set命令是兩條用於維護SQL*Plus系統變數的命令
    SQL> show all --檢視所有68個系統變數值
    SQL> show user --顯示當前連線使用者
    SQL> show error　　　　　　　　　　　　　　　 --顯示錯誤
    SQL> set heading off --禁止輸出列標題，預設值為ON
    SQL> set feedback off --禁止顯示最後一行的計數反饋資訊，預設值為"對6個或更多的記錄，回送ON"
    SQL> set timing on --預設為OFF，設定查詢耗時，可用來估計SQL語句的執行時間，測試效能
    SQL> set sqlprompt "SQL> " --設定預設提示符，預設值就是"SQL> "
    SQL> set linesize 1000 --設定螢幕顯示行寬，預設100
    SQL> set autocommit ON --設定是否自動提交，預設為OFF
    SQL> set pause on --預設為OFF，設定暫停，會使螢幕顯示停止，等待按下ENTER鍵，再顯示下一頁
    SQL> set arraysize 1 --預設為15
    SQL> set long 1000 --預設為80
    說明：
    long值預設為80，設定1000是為了顯示更多的內容，因為很多資料字典檢視中用到了long資料型別，如：
SQL> desc user_views
列名                          可空值否  型別
------------------------------- -------- ----
VIEW_NAME                      NOT NULL VARCHAR2(30)
TEXT_LENGTH                              NUMBER
TEXT                                    LONG

命令列表：
假設當前執行命令為：select * from tab;
(a)ppend　　　　 新增文字到緩衝區當前行尾　　　　a  order by tname　結果：select * from tab order by tname;
　　（注：a後面跟2個空格）
(c)hange/old/new 在當前行用新的文字替換舊的文字　c/*/tname　　　　　結果：select tname from tab;
(c)hange/text　　從當前行刪除文字　　　　　　　　c/tab　　　　　　　結果：select tname from ;
del　　　　　　　刪除當前行
del n　　　　　　刪除第n行
(i)nput 文字　　 在當前行之後新增一行
(l)ist　　　　　 顯示緩衝區中所有行
(l)ist n　　　　 顯示緩衝區中第 n 行
(l)ist m n　　　 顯示緩衝區中 m 到 n 行
run　　　　　　　執行當前緩衝區的命令
/　　　　　　　　執行當前緩衝區的命令
r　　　　　　　　執行當前緩衝區的命令
@檔名　　　　　執行調入記憶體的sql檔案，如：
SQL> edit s
如果當前目錄下不存在s.sql檔案，則系統自動生成s.sql檔案，
在其中輸入“select * from tab;”，存檔退出。
SQL> @s
系統會自動查詢當前使用者下的所有表、檢視、同義詞。
@@檔名　　　　 在.sql檔案中呼叫令一個.sql檔案時使用
save 檔名　　　將緩衝區的命令以檔案方式存檔，預設副檔名為.sql
get 檔名　　　 調入存檔的sql檔案
start 檔名　　 執行調入記憶體的sql檔案
spool 檔名　　 把這之後的各種操作及執行結果“假離線”即存檔到磁碟檔案上，預設副檔名為.lst
spool　　　　　　顯示當前的“假離線”狀態
spool off　　　　停止輸出
例：
SQL> spool a
SQL> spool
正假離線到 A.LST
SQL> spool off
SQL> spool
當前無假離線

exit　　　　　　 退出SQL*PLUS
desc 表名　　　　顯示錶的結構
show user　　　　顯示當前連線使用者
show error　　　 顯示錯誤
show all　　　　 顯示所有68個系統變數值
edit　　　　　　 開啟預設編輯器，Windows系統中預設是notepad.exe，把緩衝區中最後一條SQL語句調入afiedt.buf檔案中進行編輯
edit 檔名　　　把當前目錄中指定的.sql檔案調入編輯器進行編輯
clear screen　　 清空當前螢幕顯示
-22-----------------------------------------------------------------------------------------------
第三章附：上機練習
內容：1.建立一資料庫，啟動例項並裝配它。
  2.通過訪問資料字典瞭解資料庫的結構及例項結構。
步驟：
  一．建立資料庫
用Netterm或Ptelnet，以Oracle8帳號登入uibm主機(IP:210.34.0.23)。
進入 /oracle/目錄，ls檢視其結構。
          -------- clt1  ---- oradata  -- ora
          |              |--- testdata -- test
          |
      |------- ctl2  ----- oradata  -- ora
      |              |---  testdata -- test
      |
      |------- ctl3    …  … 
以上目錄為各資料庫中資料檔案存放目錄。
效仿以上的目錄結構，在ctl1、ctl2、clt3目錄下再建一個tstdata目錄，並在各tstdata
目錄下建立一個tst目錄
cd /oracle/app/oracle/admin
此目錄為Oracle各資料庫的管理目錄。
cd test
進入test目錄瞭解Oracle目錄組織結構(OFA結構)，結合ls命令。
      ora  ------- bdump  後臺儲存檔案目錄(BACKGROUP_DUMP_DEST的值)
      |------ udump  使用者轉儲檔案目錄(USER_DUMP_DEST的值)
      |------ cdump  核心檔案
      |------ pfile  init.ora和任何其它資料庫初始化引數
      |------ create 用於建立初始化資料和資料庫物件的指令碼
      |------ SQL    資料庫管理SQL檔案
     
進入create目錄閱讀crdbtest.sql及crdb2test.sql這兩個資料庫ORA的建立指令碼，進而知
道建立資料庫的過程命令。
效防test下的目錄結構，建立一名為tst的目錄，拷貝test/pfile及test/create下的所有
檔案到對應目錄。
          Cd /oracle/app/oracle/admin
          Mkdir tst
          Cd tst
          Mkdir bdump
          Mkdir udump
          Mkdir cdump
          Mkdir pfile
          Mkdir create
          Mkdir sql
          Cp ../test/pfile/* pfile
          Cp ../test/create/* create
          進入tst/pfile目錄，啟動vi編輯器編輯configora.ora檔案：
      將所有與原來test目錄有關的目錄全以tst替換test.
        如：原來的control_files引數中
                把/oracle/ctl1/oradata/test/control01.ctl,改為
                  /oracle/ctl1/oradata/tst/control01.ctl,
            需要改的地方還有：core_dump_dest
                              user_dump_dest 
                              db_name 引數行
                 
        把檔案configtest.ora改名為configtst.ora
              inittest.ora  改名為inittst.ora
              inittest_0.ora 改名為inittst_0.ora
        編輯inittst.ora及inittst_0.ora檔案，把它們中的前面的ifile指
      定檔案原來為:
        ifile = /oracle/app/oracle/admin/test/pfile/configtest.ora   
      改為：
        ifile  = /oracle/app/oracle/admin/tst/pfile/configtst.ora   

進入tst/create目錄
把crdbtest.sql及crdb2test.sql改名為crdbtst.sql及crdb2tst.sql
編輯crdbtst.sql檔案，修改以下行（將test改為tst）:
  spool /oracle/app/oracle/admin/test/create/crdbtest.lst
        startup nomount pfile= …
        create database "test" 改為create database “tst”
      將create database語句的用的character set改為ZHS16CGB231280，
      原先為US7ASCII。ZHS16CGB231280為Oracle中支援中文國標的字符集名。
     
        編輯crdb2tst.sql檔案，將其做類似以上的修改(將建立命令中所用到的有關原來
test目錄改為tst目錄，並可以適當調節你所要建立資料庫的相關資料檔案大小。
修改ORACLE_SID環境變數值為新的SID名，此SID告訴oracle欲啟動的例項名。
    ORACLE_SID=tst;exprot ORACLE_SID
  進入tst/create目錄，啟動伺服器管理器(svrmgrl)執行crdbtst.sql指令碼：
cd /oracle/app/oracle/admin/tst/create
    svrmgrl  @crdbtst.sql
    執行後在svrmgrl狀態下再執行crdb2tst.sql指令碼。
    Svrmgrl> start crdb2tst.sql
    建立過程需要數分鐘，請耐心等待 … 
        等上以指令碼執行完畢，新的資料庫已建立。此時，可以退出svrmgrl。
        拷貝tst/pfile/inittst.ora檔案至$ORACLE_HOME/dbs目錄，省得每次啟動svrmg
rl還要指定init.ora檔案位置。在啟動svrmgrl時，若沒有特別指定init.ora的檔案，ORA
CLE將在$ORACLE_HOME/dbs找init.ora作為其啟動的初始化引數檔案,SID為ORACLE_S
ID環境變數值。
再次啟動svrmgrl(注意：ORACLE_SID值必須已改為新的SID值。)
  svrmgrl
svrmgrl>connect internal;
svrmgrl>startup            (啟動資料庫)
svrmgrl>start $ORACLE_HOME\dbs\catproc.sql 
 
catproc.sql指令碼安裝Procedural Option所必需的指令碼或PL/SQL物件及其支援的資料庫結
構。
至此，我們已成功建立了一個新的資料庫tst，並且我們也用例項tst來裝配啟動它。
為了能夠讓使用者從遠端訪問此資料庫，我們還必須配置Oracle的TNS（Transparent Netwo
rk Service），配置這一服務只要改變一下其配置檔案listener.ora即可，最簡單的辦法
是拷貝一個副本備份，然後直接編輯它，把原來的例項名更換為新的例項名即可。更名後
，重新啟動tnslistener程式即生效。
$lsnrctl stop    (在作業系統狀態下執行)
$lsnrctl start

更改SYS及SYSTEM使用者的默讓密碼。
  grant connect to sys(或system) identified by
或 alter user sys identified by  
    以上命令可以在伺服器管理器狀態下執行，也可以在Sqlplus下執行。
 

二、考察資料庫及例項結構
啟動sqlplus用SYS或SYSTEM使用者連線。
或啟動svrmgrl，connect internal
1. 查詢例項啟動時間。
    select to_char(a.value,’J’)+b.value/86400,
            ’HH24:MI:SS DD-MON-RR’) start_time 
            from v$instance a,v$instance b
            where a.key=’STARTUP TIME –JULIAN’ AND
                  b.key=’STARTUP TIME –SECONDS’;
         
觀看例項儲存器分配資訊
SELECT name,bytes from v$sgastat
      Where name in (‘free memory’,’fixed_sga’,’db_block_buffers’,
                    ‘log_buffer’,’dictionary cache’,’library cache’,
                    ‘sql area’);
檢視程式例項程式
select spid,name from v$process, v$bgprocess where addr =paddr; 
檢視資料庫使用者
select username from dba_users;
檢視活動的控制檔案
select * from v$controlfile
檢視回滾段資訊
select a.segment_name,b.bytes,b.extents,a.tablespace_name, 
      c.shrinks,c.extends,c.hwmsize
  from dba_rollback_segs a,dba_segments b,v$rollstat c
  where a.segment_id=c.usn and a.segment_name=b.segment_name;
       
檢視重做日誌資訊
select  member,bytes,members,a.status 
  from  v$log, V$logfile b
  where a.group# = b.group#
  order by member;
檢視資料庫連結
select spid,mame from v$sysstatprocess,v$sysstatbgprocess
    where paddr(+)=addr;
檢視多執行緒伺服器程式
  select * from v$dispatcher;
  select * from v$shared_server;


第四章：SQL
                  (本次課在機房，結合上機講授)
本章介紹SQL的基礎知識。理解了SQL就理解了關聯式資料庫。Oracle與資料庫的所有互動都
使用SQL(Structured Query Language)。SQL*Plus是基於SQL但又具有Oracle特定功能的一
種工具，它可用來生成報表、控制螢幕顯示和列印輸出格式。
術語
下面介紹本章使用的一引技術術語：
■ DDL(Data Definition Language)  資料定義語言是SQL中定義資料庫中資料的結構的
語言。定義資料時，將在Oracle的資料字典中生成資料項。常見的DDL關鍵字是create、r
evoke、grant和alter
DML(Data Manipulation Language) 資料操縱語言為SQL結構，用來操縱資料庫中資料（而
非定義資料，定義資料由DDL完成）。常見的DML關鍵字為select、insert、update和dele
te。
在Oracle中，我們使用commit(提交)語句表示已經將修改後的資料儲存到資料庫。每次用
戶儲存結果時，Oracle將引用使用者的提交操作。
約束(constraint) 是一種保證一個Oracle表的資料間關係或兩不同表中資料間的一致性的
機制。
Oracle8資料庫中一個物件(object)是一個有意義的事物，可在其內部存放資訊。我們常談
的物件型別——表和檢視是兩種最常見的。
利用如SQL*Plus這樣的程式將資訊從Oracle資料庫中提取出來的操作稱為查詢(query)。
■ 回滾（Rollback）為當某個對話更改了資料庫中的資料後，由於某種原因不想提交些更
改時Oracle所採取的操作。這是一個把資訊恢復到使用者update前狀態的操作。
  SQL語句有兩大類：DDL和DML。下面我們進一步來看看二者的差異：
  二．DDL
DDL資料定義語言是一組SQL命令，用於建立和定義資料庫物件，並且將其儲存在資料字典
中。
資料定義語言使使用者能完成下列任務：
建立(create)資料庫物件
刪除(drop)資料庫物件
更改(alter)資料庫物件
為資料庫物件授權(grant)
回收已授給資料庫物件的許可權(revoke)
當釋出一條DDL SQL語句時，在每一條DDL語句執行前後，Oracle都將提交當前的事務，理
解這一上點很重要。因此如果使用者插入（insert）記錄到資料庫中並且釋出了一條DDL語句
，如create table，此時來自insert命令的資料將提交到資料庫。
 
屬於DDL的語句是自動提交的，這意味著當Oracle8通知使用者比如“Revoke succeeded”，
此時命令已完成不能回滾了。
  DDL語句部分列表：
  alter procedure          重編譯存貯過程
  alter table              增加表列、修改表列、更改存貯分配
  analyze                收集資料庫物件的效能統計值並送入代價的優化器
  alter table add constraint  在已有的表上增加約束
  create table              建立表
  create index              建立索引
  drop index              刪除索引
  drop table              刪除表
  grant                  將許可權或角色授予使用者或其它角色
  truncate                刪除表中所有行
  revoke                  從使用者或資料庫角色回收許可權
  三．DML
DML(資料操縱語言)允許使用者對資料庫中的資料進行insert、update、delete和select等操
作。正如名字所示，DML處理資料庫中的資料內容。最常見的DML語句是insert、update、
delete和select。
Insert
Delete
Update
Select
Commit work          把當前事務所作的更改永久化（寫入磁碟）
Rollback              作廢上次提交以來的所有更改
   
    在學習了兩種主要型別的SQL語句後，下面作進一步的介紹。首先登入進SQL*Plus，
然後試一些最常見的DDL和DML語句。
   
 
  四.SQL*Plus入門
學習SQL最簡單的辦法就是使用SQL*Plus。因此先登入到SQL*Plus。Oracle安裝後有一使用者
名scott,口令為tiger。我們可以用這個帳號登入試用。
有兩種方式進入SQL*Plus:
1.使用客戶端的的SQL* Plus 8.0。
  此程式項在啟動選單欄的Oracle For Windows95組中。啟動它，在connect對話方塊中按提
示輸入使用者名稱、口令及主機字串。如果單機已裝了Personal Oracle的，主機字串可以
不填，否則填上SQL * Net已配置的service name，所連線的資料庫例項在service裡已定
義，這些定義可以使用Oracle Net8 Easy Config進行配置。
其結果儲存在\ORAWIN95\NET80\ADMIN\TNSNAMES.ORA檔案中。
   
使用Unix上的SQL*Plus
登入到UNIX主機上，
打入命令 sqlplus scott/tiger  或sqlplus然後再按提示輸入使用者名稱及口令
SQL*Plus: Release 8.0.4.0.0 - Production on Sat Jul 3 0:31:55 1999
(c) Copyright 1997 Oracle Corporation.  All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.4.0.0 - Production
    PL/SQL Release 8.0.4.0.0 - Production
    SQL>
 
  在進入SQL*Plus後，會看到SQL*Plus提示符SQL>
這時您就可以鍵入想試驗的SQL語句。
  下面我們講一下與SQL緩衝器一起使用的SQL*Plus命令,這些可以幫助我們高效地輸入命
令。
命令  縮寫 動作
APPEND text A text 在行尾增加text
CHANGE  old/new C old/new 在一行中將old文字改為new文字
CLEAR BUFFER CL BUFF  刪除所有行
DEL 刪除緩衝器中所有行
INPUT I  將一行或多行增加到緩衝器
INPUT text I text 增加一由text組成的行
LIST L 列出SQL*Plus緩衝器內容
LIST n Ln或n 列出行n
LIST * L * 列出當前行
LIST m n  L m n 列出行m至行n
LIST LAST  L LAST 列出緩衝器中的最後一行
RUN 執行緩衝器中的命令
SQL*Plus中的命令列以分號(;)結束。
下面我們給出一些SQL語句，大家可以在自己的機器上試驗一下：
select table_name from user_tables
  此命令用資料字典user_tables中列出用登入使用者所擁有的表。
若用scott登入，列出的結果如下：
TABLE_NAME
------------------------------
BONUS
DEPT
DUMMY
EMP
HELP
  SALGRADE
  這些表是在資料庫安裝時建立的讓使用者試驗學習的表，檢視這些表的結構可以用descr
ibe命令，例如：
  SQL>describe emp             
  檢視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)
  SQL>select empno,ename from emp
  檢視錶內容。
  ２.create語句
  在任何資料庫總是以DDL語句開始，因為建立資料庫物件的工作是由DDL語句來完成的。
首先，我們將建立四個表：Customer、State、X和Y：
SQL>create table customer (
last_name  varchar2 (30) not null,
state_cd  varchar(2),
sales      number)
tablespace  users
storage (initial 25k next 25k minextents 1);
Table created.
SQL>create table state (
  2  state_cd    varchar(2) not null,
  3  state_name  varchar2(30);
Table created.
SQL>create table x(
  2  col      varchar2(30);
Table created.
SQL>create table y(
col varchar2(30));
Table created.
▲Null與Not Null
    在建立customer表時，last_name表列後跟一個限定符“not null”，這表示資料庫不
接受沒有表列資料行到customer表中。換句話說，not null表列是強制性欄位，在表cust
omer和state中，這意味著要在表中插入一行，last_name和state_cd欄位必須含有值。

什麼是空值(null value)
空(null)是不包括資料的表列。可以將null理解為長度為0的字串。很多時候若不知道某
表列的型別可以給它賦一空值。但人們最容易犯的一個錯誤就是將空值載入到一個數值型
表列中，而問題在於“1+null=null”！因此，如果使用者偶然將空值載入至數值域中，那麼
產生的統計報表肯定不正確。
３．Insert
  現在我們已經建立了一些表，讓我們用不用DML語句，在我們建立的表上插入一些資料
，這些資料也將作為我們試驗命令的資料。
SQL>insert into customer values (‘Teplow’,’MA’,23445.67);
SQL>insert into customer values (‘Abbev’,’CA’,6969.96);
每次成功地完成一條insert語句後，均返回建立資訊，
1 row created.
該資訊通知使用者建立的行數。
SQL>insert into customer values (‘Porter’,’CA’,6989.99);
SQL>insert into customer values (‘Martin’,’CA’,2345.45);
SQL>insert into customer values (‘Laursen’,’CA’,34.34);
SQL>insert into customer values (‘Bambi’,’CA’,1234.55);
SQL>insert into customer values (‘McGraw’,’NJ’,123.45);
現在我們用稍加變化的insert命令的資料插入state表。我們將指定資料要插入的表列名。
這在處理大表時很有用，因為使用者可能沒有表中每一列的資料。例如：在一個預算系統中
，只有在月末才有實際的花銷數。
SQL>insert into state (state_name,state_cd)
values (‘Massachusetts’,’MA’);
SQL>insert into state (state_name,state_cd)
values (‘California’,’CA’);
 
  最後我們再插入一些資料到表X和表Y中。
SQL>insert into x values (‘1’);
SQL>insert into x values (‘2’);
SQL>insert into x values (‘3’);
SQL>insert into y values (‘3’);
SQL>insert into y values (‘4’);
SQL>insert into y values (‘5’);
４．Select 
select命令用於從Oracle資料庫中檢索資料，select是使用者最常用的ＳＱＬ語句，select
命令由四個基本部分構成：
1).select後跟使用者要檢索的資訊（表或檢視中的列名），這是select命令不可少的部分，
可用*號代表全部列。
2).from後跟檢索物件(如存放資料的一個或多個表或檢視的名稱)，from部分也是必不可少
的。
3).where後跟檢索條件，可選的。
4).order by後跟分類準則，可選的。
現在我們來檢視我們剛才插入的資料：
SQL>select * from customer;
SQL>select state_name from state;
SQL>select * from x;
SQL>select * from y;
  下面我們來看一下條件及範圍檢索：
select last_name,state_cd,sales from customer where state_cd=’MA’;
檢視state_cd值為MA的所有客戶。
select * from customer where state_cd=’CA’ and sales>6000
  select * from customer where state_cd=’CA’ or sales>6000
  select * from customer where state_cd!=’MA’;
帶檢索表
select * from customer where state_cd in (‘NJ’,’CA’);
帶匹配條件
Select * from customer where last_name like ‘M%’;
Select * from customer where last_name like ‘%tin%;
  總結：
    =
!=  不等於
^=  不等於
^=  不等於
<>  不等於
    <
    >
    <=
    >=
in  (    )                等於括號內任一成員
not in  (  )            不等於括號內任一成員
between A and B          大於等於A與小於等於B
not between A and B      不大於等於A與小於等於B
like ‘%tin%’              包括給定子串(即‘tin’)

Order by:
  Select * from customer order by last_name desc;
  Select * from customer order by last_name;
  在order by子句中未指定升序或降序時，Oracle按升序排序。
5.Update、Delete和Alter
Update修改表中的資料
  SQL>Update customer set sales=23890.66 where state_cd=’MA’;
  若沒有用where指定修改的條件行，將修改表中全部行。
Delete刪除行資料
  Delete from customer，將刪除customer表的所有記錄；delete from customer where
state_cd=’CA’，將刪除state_cd為CA的客戶記錄。
Alter table修改表結構
  此語句有如Foxpro中的Modify stru語句。在建立表後，使用者可能想要增加表列。這時就
要用到alter table命令了。
  Alter table customer add (sale_date date)；
  將成功地把表列sale_date加到表customer中。
  Alter table x modify(col date)，改變一個已存在表列的資料型別。
6．連線兩個表
現實中，使用者需要的大量資料往往存放在多個表中。很多情況下需要處理多個表。例如，
customer表中只存放州程式碼(state_code)，然而使用者還想知道州名，這時需要將表custom
er與表state連線。這就要用到表的連線。通過定義，Oracle一類的關聯式資料庫允許使用者基
於公共域連線兩個或更多表。這些公共域通常稱為鍵域(key field)。
有兩種型別的鍵：主鍵（primary）和外部鍵(foreign)。主鍵使表中的資料行保持唯一。
在表state中，state_cd就是主鍵。表customer中也包含有state_cd,此時的state_cd就是
外部鍵。一個表的外部鍵用於從其他（foreign）表中獲取資訊。
SQL>select * right.col,left.col from x right,y left 
where right.col=left.col;

五．內部函式
數值型函式
函  數    返回值    樣  例 顯示
Abs(n) N的絕對值 Select abs(-321) from dual; 321
Ceil(n) 大於等於數值n的最大整數 Select ceil(10.6) from dual; 11
Floor(n) 小於等於數值n的最大整數 Select floor(10.6) from dual; 10
Mod(m,n) M除以n的餘數，若n=0返回n Select mod(7,5) from dual; 2
Power(m,n) M的n次方 Select power(3,2) from dual; 9
Round(n,m) 將n四捨五入，保留小數點後m位 Select round(1234.5678,2) from dual 12
34.57
Sign(n) N=0,返回0;n>0,返回1;n<0,返回-1 Select sign(12) from dual; 1
Sqrt(n) N的平方根 Select sqrt(25) from dual; 5
  Dual表擁有者為SYS，在句法正確，而資料庫中沒有其他表可用於該語句時，可使用dua
l表。
  2.字串函式
函  數  返回值 樣例 顯示
initcap(char)  把每個字串的第一個字元換成大寫 Select initcap(‘mr.telpow’)
from dual; Mr.Telplow
Lower(char) 整個字串換成小寫 Select lower(‘Mr.Frank Townson’) from dual; m
r.frank townson
Replace(char,str1,str2) 字串中所有str1換成str2 Select replace(‘Scott’,  ’
S’,’Boy’) from dual; Boycott
Soundex(char) 字串的語音表示，查詢發音相似拼寫不同的字串 Select last_name
from employee where soundex (last_name) = soundex(‘SMYTHE’); SMITH
Substr(char,m,n) 取出從m字元開始的n個字元的子串 Select substr(‘ABCDEF’,2,1)
from dual; B
Length(char) 求字串的長度 Select length(‘Anderson’) From dual; 8
 
  ||  並置運算子。
  Select ‘Dear’||’John’||’:’ from customer 
  將返回 ‘DearJohn:’
  3.日期型函式
  函式 返回值 樣例 顯示
Sysdate 當前日期和時間 Select sysdate from dual;
Last_day 本月最後一天 Select last_day(sysdate) From dual
Add_month(d,n) 當前日期d後推n個月 Select add_months(sysdate,2) from dual;
Months_between (f,s) 日期f和s間相差月數 Select months_between(sysdate,’12-MAR
-99’) from dual;
Next_day(d,day) D後第一週指定day的日期 Select next_day(sysdate,’Monday’) fro
m dual;
Oracle預設的日期格式為DD-MON-YY。為保證進入21世紀不出問題，請儘可能用四位數字的
年份。Oracle提供了一種特殊的世紀日期格式標記為DD-MON-RR。
常用日期格式
格式 返回值 樣例
Y、YY或YYY 年的最後一位，兩位或三位 Select to_char(sysdate,’YYY’) from dual;
SYEAR或YEAR 年，SYEAR公元前的年前加一負號 Select to_char(sysdate,’SYEAR’) fr
om dual;
Q 季度，1到3月為第一季度 Select to_char(sysdate,’Q’) from dual;
MM 月份數
Month 用9個字元長度表示月分(英文)
WW 當年第幾周
W 本月第幾周
D 周內第幾天
DD 當月第幾天
DY 周內第幾天縮寫(如：SUN)
HH 12進位制小時數
HH24 24進位制小時數
MI 分鐘數
SS 秒數
型別轉換
  to_char  將任意型別的資料轉換成字串
  to_number
  to_date

六．格式化輸出
在SQL*Plus中，有許多引數可以控制SQL*Plus的輸出顯示格式，利用SQL*Plus命令show a
ll使用者能知道顯示格式的當前設定。
SQL>show all;
appinfo is ON and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
shiftinout INVISIBLE
blockterminator "." (hex 2e)
btitle OFF and is the 1st few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
echo OFF
editfile "afiedt.buf"
  embedded OFF
    …  ...
1).行和頁的大小
  set linesize
  set pagesize
例：
  SQL>set linesize 80;
  SQL>set pagesize 25;
2) 頁頭標、頁尾標
  ttitle