[Oracle]SQL*PLUS命令的使用大全

一、DataBase
  儲存資料，以表的形式表現資料
 
二、SQL
  Structur query language 
  結構化查詢語言 (SQL)，是操作關係型資料庫中的對象。  
  DDL（Data definition language 資料定義語言 (Data Definition Language)），用於建表或刪表操作，以及對錶約束進行修改
     create table , alter table , drop table 對錶結構的增刪操作。
  DML（Data manipulation language 資料操作語言），向表中插入紀錄，修改紀錄
     insert , update , delete , merge
  transaction ,事務控制語言，由DML語句組成的，commit; ,rollback;
  select 查詢語句
  dcl 授權語句 grant
 
三、Oracle
  DBMS 資料庫管理系統
  有Oracle提供，還提供AS，應用伺服器
  DBA 資料庫管理員

四、相關操作
1、sqlplus 訪問資料庫命令（本地訪問/遠端存取），和資料庫建立串連的命令，是資料庫操作的環境
  sqlplus 使用者名稱/密碼
2、show user 顯示目前使用者的使用者名稱
  改變身份可以直接connect 使用者名稱/密碼   --- 這個是sqlplus命令
  在sqlplus中可以使用 ! 可以在shell和sqlplus間切換，!shell命令 可以在sqlplus中使用shell命令。
  實際上是sqlplus開了子進程來執行shell命令。
3、Oracle資料庫中的表分兩類：使用者表（使用者使用操作的表），系統資料表（資料庫系統維護的表，也叫資料字典）
  對使用者表的DDL操作出發了對系統資料表的DML操作！

五、基本文法
1、select查詢語句
select table_name from user_tables;（查詢系統資料表）
以上的查詢語句就是查詢本使用者下所擁有的所有表的表名。

投影操作，只查看選擇的欄位的資訊。
選擇操作，查看欄位中的特定某些資訊。
聯結操作，多表查詢，通過表間串連，查尋出多表中的資訊

（1）select table_name from user_tables;（查詢系統資料表）
    以上的查詢語句就是查詢本使用者下所擁有的所有表的表名。
  
（2）sqlplus的buffer中會緩衝最後一條sql語句，可以使用"/"來執行這最後一條sql語句，也可以使用
    edit命令來編輯最後一條sql語句。
    l命令（list）（sqlplus命令）可以顯示buffer中最後一條命令。 
  
    sqlplus命令可以縮寫
  
（3）desc [表名]
    這是一條sqlplus命令，注意他不是sql語句，這條命令用於查看錶的結構。descript的縮寫
    [欄位名] [欄位的類型]，這是使用完desc命令後顯示的表結構。     
  
（4）select [表的欄位名1],[表的欄位名2], ... from 表名;
    select * from 表名; 查尋表中所有欄位的資訊   
  
（5）關鍵字不等拆分，sql語句，以及表名，欄位名是大小寫不敏感的。
    sql語句要以";"結尾，來表示sql語句結束，如果不加";"系統不會執行此條sql語句，並提示。   
    在Oracle中字元顯示是靠左對齊，數值靠右對齊。
  
（6）在select 語句中可以使用數學運算式。
    select [運算式（必須包含本表欄位名）],[...],.... from 表名；
    運算的優先順序的先乘除後加減，同級自左向右運算，括弧改變優先順序。

（7）別名
    select [欄位名或運算式] ["別名"]，[...] ["..."],.... from 表名；
    可以通過在欄位名或運算式後加空格"別名"，可以給列，或者運算式結果其別名。   
    表達別名必須加雙引號。
  
（8）字串拼接使用||符號
    select 目標欄位名||" "||目標欄位名 from 表名；

    注意：在Oracle中的字串要用'..'包含
    別名中需要使用空格，或是大小寫敏感時需要用".."包含。   
  
練習：
自己寫一條SQL語句，執行的結果是select * from ...;
其中...是每張系統資料表的表名
即在每張系統資料表的表名前加 “select * from”  ，後加“;”
select 'select * from '||table_name||';' from user_tables;

2、處理錯誤

（1）!oerr ora [錯誤號碼] ，系統可以顯示錯誤的原因和如何修改。如果命令錯誤輸入可以使用edit或ed來修改輸入錯誤。
    實際上是在編輯快取檔案中的最後一條sql語句。
    也可以使用 (change) c /錯誤欄位/正確欄位，來進行替換操作進行修改。
    只有在Linux平台使用
    ! 相當於 host ，沒有斷串連，只是切換了一下，執行shell命令
（2）edit命令來編輯最後一條sql語句。

3、sqlplus設定

set pause on 斷行符號響應，分屏顯示，只在本會話中有效
set pause off 關閉分屏顯示。
set pause "..."  設定分屏顯示的提示資訊。
set pause on 先輸出提示資訊，斷行符號響應，分屏顯示
set head off 提頭輸出關閉
set feed off 結尾輸出關閉
set echo off 回寫關閉
spool 檔案名稱.sql 寫入指定檔案
spool off 關閉寫入。  

4、sql指令碼

  也就是在檔案中寫有sql語句的檔案，可以在sqlplus中運行。
  引入sql指令碼
  sqlplus 使用者名稱/密碼 @sql指令碼 （注意：在使用者名稱密碼輸入結束後一定要加空格然後再寫@sql指令碼）
  在指令碼中最後一行寫上“exit”，則運行完指令碼以後，回到shell上

5、
Oracle中的空值 空值會當無窮大處理，其實空值根本就不會儲存，只是看作是無窮大。

Oracle中控制處理函數 NVL(欄位名，值)，這個欄位中的空值替換為指定值，如果不為空白，則會返回其原值。
例：select (salary*12)*(NVL(commission_pct,0)/100+1) salary,first_name from s_emp;

distinct關鍵字，去掉重複行（這個關鍵字會觸發排序操作）
例： select distinct dept_id,title from s_emp;
       dept_id與title的聯合不唯一
注意：distinct，關鍵字之後會對from之前的欄位進行排重操作。

6、column命令 --- sqlplus命令

   column命令 列格式的定義

   column 目標列名 查看這個類是否定義了格式

   column 目標列名 format a.. 設定列寬。
   column last_name heading 'Employee|Name'  formAT A15
   設定題頭
   這其中的'|'是分行符號

   column salary justify left format $99,990.00
   定義數字顯示格式
   注意：如果不滿足顯示的格式，就會把資料顯示為"#"
 
   column salary justify left format $00,000.00
   會出現$00,928.00 ,用0補齊

   column 列名 clear （清除列格式定義）

   注意：只有sqlplus命令才有簡寫，並且在使用sqlplus命令時結尾也不能加分號。

六、選擇操作

1、order by

  排序子句 ASC（預設，升序） DESC（降序）
  order by 目標列名（別名） 排序次序（不寫排序次序，會預設為升序排序）

  例：select first_name from s_emp order by first_name;
      select first_name from s_emp order by first_name desc;

  注意：升序空值在結果的末尾，降序空值在結果的最前面。
 
2、where 子句

where子句使用在 select ... from ... 後面，用來選擇所需（合格）的記錄

where後面跟的是運算式 也就是 XXX=XXX， XXX between X and X  ，XXX in（X，X，X）
like '...' 通配查詢

between ... and ... ,表示結果在這之間，between and是一個閉區間，
也就相當於... <= ... and ... >= ... 。
!=，<>，^=，這三個都標識不等於，& lt;=，>=，=，這些運算子都可以使用。
... in (va1,val2,...) 判斷結果是否在這個枚舉中存在
like '...' 字串通配查詢，'%'表示多個字元，'_'，表示一個字元。
注意：轉義的用法：like ‘S\_%’ escape ‘\’
... and ... 表示只有兩個條件同時滿足
... or ... 表示條件只要滿足其中只一就可以
all ... 是要求都滿足條件。
not .....，則是可以與以上的條件產生反效果。
空值會對not in造成影響，也就是不等於任何值，但是空值例外。
... is null 使用來判斷值是否為空白。
  
注意：Oracle中的字串是嚴格區分大小寫。

（1）注意資料類型，數字類型直接寫，字元用'......' ，預設格式的Date可以用'......'，只有別名
 才用" "包含。
（2）選擇合適的運算子

七、單行函數

1.字元函數

  字元是大小寫敏感的
  轉小寫 lower(欄位名)      ---  其中的參數可以是一個字串常量或是一個欄位名
  轉大寫 upper(欄位名)
  首字母大寫 initcap(欄位名)
  字串拼接 concat(欄位1, 欄位2)
  截取子串 substr(欄位名, 起始位置，取字元個數)
  dual表，是專門用於函數測試和運算的，他只有一條記錄
  字串拼接 concat(...,....)
  求指定子串 substr(...,起始位置，取字元個數)
  可以使用"-"表示從右向左取，取的時候可以從左往友取。
   例：select substr(first_name,-2,2) sub from s_emp;（取後兩個）
     select substr(first_name,2,2) sub from s_emp;（取前兩個）
   
2.數值函數

  四捨五入 round(資料,保留小數點後幾位)
  可以用負數表示小數點前，0，表示小數點後第一位，也就是保留個位，-1表示個位（保留到十位）。
  例：select round(15.36,1) from dual;
  截取數字函數 trunc(資料，保留的位元（小數點後位元）) 截取個位之後補0
  例：select trunc(123.456,1) from dual;
 
3.日期函數
  日期格式，
  全日期格式 世紀資訊，年月日，時分秒。
  預設日期格式，日-月-年 dd-mon-rr
  修改當前會話的日期格式，會按照指定的格式輸出日期
  alter session set nls_date_format='yyyy mm dd hh24:mi:ss';

  返回當前日期 sysdate
  例：select sysdate from dual;
      select sysdate+1 from dual;  獲得明天的日期，加1，單位是天
 
  日期是格式敏感的
  求兩個日期間相隔了多少個月 months_between(date1,date2)
  加減指定數量的月份 add_months(date,月數)，月數可以為負，負值就是減去相應的月數。
  從date日期開始的第一個星期五 next_day(date,FriDay)
  返回月末的日期 last_day(date)
  截取日期 trunc(date,'年或月或日或時分秒')
  例：select next_day(sysdate,2) from dual;
  例：select trunc(add_months(sysdate,1),'month') from dual; 
  ROUND('25-MAY-95','MONTH')01-JUN-95
  ROUND('25-MAY-95 ','YEAR')01-JAN-95
  TRUNC('25-MAY-95 ','MONTH')01-MAY-95
  TRUNC('25-MAY-95 ','YEAR')01-JAN-95
 
  練習：
  返回下個月的第一天的日期
  select round(last_day(sysdate),'MONTH') from dual;
  select add_months(trunc(sysdate,'MONTH'),1);
 
4. 不同資料類型間轉換函式

  將日期轉成字元 tochar(date,'日期格式')
  日期格式要用有效格式，格式大小寫敏感 'yyyy mm dd hh24:mi:ss',
  'year'(全拼的年),'mm'(數字表示的月) 'month'(全拼的月)，'day'(星期的全拼)，'ddspth' (日期的全拼) 'yy mm dd'
  例：select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;

  將字元轉換成數字 to_number('...')
 
  將數字轉字元to_char(number，'fmt') fmt是數字格式

  將字串轉成日期 to_date('...','日期格式')
  例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；