Data retrieval
-Select

Data manipulation language (DML)
-INSERT,UPDATE,DELETE

Data definition language(DDL
-CREATE,ALTER,DROP,RENAME,TRUNCATE

Transacation control
-COMMIT, ROLLBACK,SAVEPOINT

Data control language(DCL)
-GRANT,REVOKE

使用SQL *Plus進行數據庫操作。

openlab.tarena.ca% sqlplus tarena/tarena@TARENADB

用戶名/密碼

注意這樣登錄是強烈不推薦的，因為這樣別人能看到你的用戶名和密碼。

SQL> !ps -ef|grep sqlplus
    lujl   966   955  0 08:43:17 pts/1    0:00 sqlplus tarena/tarena@TARENADB
    lujl   969   966  1 08:45:45 pts/1    0:00 /bin/csh -c ps -ef|grep sqlplus
    lujl   971   969  0 08:45:45 pts/1    0:00 grep sqlplus

由于本人已有很多編寫SQL語句的經驗，所以只記錄重點信息。

SQL中可以使用數學表達式

+
-
*
/

先乘除后加減。

SQL> select last_name,salary, 12*salary + 100
  2  from s_emp

注意每一個子句最好單獨占一行。這樣以便以后的修改，要想寫完就執行，最后添加  ‘；’

1。可以給列取別名 Aliase，
2。可以級聯兩列，作為整體，輸出結果。

SQL> select first_name||last_name "Employees"
  2  from s_emp;

Employees
------------------------------
benqu
hui
lgf
liganfeng
lgf
BenDumas
AntoinetteDumas


使用單引號，將字符括起來。

select first_name||' '||last_name
from s_emp;

NULL是一個不確定的值！

對空的任何操作，返回都是空。

使用nvl函數，處理空值，為空就使用默認值。

NVL(salary,1000)

select distinct name
from s_dept;



sqlplus有很多命令。


SQL> help index

Enter Help [topic] for help.

 @             COPY         PAUSE                    SHUTDOWN
 @@            DEFINE       PRINT                    SPOOL
 /             DEL          PROMPT                   SQLPLUS
 ACCEPT        DESCRIBE     QUIT                     START
 APPEND        DISCONNECT   RECOVER                  STARTUP
 ARCHIVE LOG   EDIT         REMARK                   STORE
 ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
 BREAK         EXIT         REPHEADER                TTITLE
 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
 CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
 CLEAR         HOST         RUN                      WHENEVER OSERROR
 COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
 COMPUTE       LIST         SET
 CONNECT       PASSWORD     SHOW


DESCRIBE s_dept

SQL> describe s_dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(7)
 NAME                                      NOT NULL VARCHAR2(25)
 REGION_ID 

NUMBER（p,s）p為整數位，s為小數位數
VARCHAR2（s)
DATE  （Oracle中只有一種日期格式！）
CHAR(s)

SQL Plus  提供了很強的編輯SQL語句的能力。

SQL> l 
  1  select first_name||last_name "Employees"
  2* from s_emp
SQL> 1
  1* select first_name||last_name "Employees"
SQL>

list(簡寫l)
列出緩存中的SQL語句。

1（數字）就是將當前編輯行

看前面有*的，為當前編輯行，再才使用行編輯命令，就需要確定是否是想編輯的行。

A[PPEND] text
C[HANGE] /old/new
CL[EAR]BUFF[ER]
DEL
I[NPUT] text
L[IST] n
n text

由于有時會查出很多記錄，這時，需要分頁停頓。

SET PAUSE {[OFF|ON] text}

SAVE file寫緩存到文件
GET file讀入緩存

START filename讀文件到緩存，并執行。
@filename = START filename
EDIT filename
SPOOL filename 開始記錄，并將執行的命令和結果都寫入相應文件。
SPOOL OFF，這時會寫入文件。EXIT，正常退出，也會寫入文件。

關鍵命令

COL[UNM] [{column|alias} [option]]

FOR[MAT] format

9
0
$
L
.
,

col last_name format a15   調整last_name為15字符長度，默認為last_name數據類型的長度。（VARCHAR2（35），那就是35），調整列寬。


HEA[DING] text
設置表頭

JUS[TIFY] {align}
left center right

column last_name HEADING 'Employee|Name' FORMAT A15
column salary JUSTIFY LEFT FORMAT $099,999,99    保證位數，$012,345,00
column start_date FORMAT A8 NULL 'Not hired'    等同于nvl函數

column last_name
顯示某列的格式

column last_name CLEAR





select last_name
from s_emp
order by last_name;
升序默認

desc降序

select last_name,salary*12
from s_emp
order by 2;

按照select子句中的第二項 排序

select last_name,dept_id,salary
from s_emp
order by dept_id，salary DESC;

先按部門排序，如果相同，再按salary降序排。

-------------------------------------------------
where 子句，last_name='Megee'，單引號！

日期格式默認為日日-月月月-年年  DD-MON-RR(RR不同于YY，它只取1950到2049年的年，是為解決千年蟲問題設定的)

邏輯比較符
= > >= < <=

SQL比較符
BETWEEN...AND...
IN（LIST）  IN(1,3) 1和3
LIKE
IS NULL

邏輯操作符
AND
OR
NOT

不等于!= <> ^=

NOT BETWEEN
NOT IN
NOTLIKE
ISNOTNULL

===============================

LIKE

select last_name from s_emp where last_name like '_M%'

'_' 代表有且只有一格字符

---------------------------

這時，老師發現緩存不夠，察看老師的幾條命令，應用性很強哦。記下來，以后自己用！

du -sk *|sort
當前目錄按照大小的數字排序，

df -k .
察看當前目錄的文件系統使用情況。

rm -rf *
不提示刪除文件

---------------------------

select table_name
from user_tables
where table_name like 'S\_%' ESCAPE '\';

以S_開頭的表名，escape定義了轉義字符\


IS NULL  

IS NOT NULL

不要 = null    不出錯，但是邏輯不對！

執行順序

比較操作符〉AND〉OR



==============================

函數

單行函數，輸入一個值，輸出一個值

多行函數，多輸入，單輸出。

LOWER('SQL Course')=sqlcourse

UPPER

INITCAP('SqL COurse')=SqL COurse

CONCAT級聯

SUBSTR('String',1,3)=Str

LENGTH

NVL


ROUND(45.923,2)=45.92
ROUND(45.923,-1)=50

TRUNC(45.923,2)=45.92
TRUNC(45.923,-1)=50

MOD(1600,300)=100  余數

===========================================

Oracle Date Format是重點

日期包括世紀、年、月、日、小時、分鐘、秒幾類數

Oracle時間可以保存10負9次方妙

默認顯示日期格式為DD-MON-RR

SYSDATE一個能返回當前系統時間的函數

select sysdate from dual;

dual是了保證select語句完整性，設置的，沒有實際意義。我認為是個測試函數的好幫手。

YY   05
YYYY   2005
RR   05
RRRR   2005
YEAR   two thousand and five
MM   12
MON   NOV
MONTH   NOVEMBER
DD   18
DDTH   18TH
DDSP   EIGHTEEN
DDSPTH   EIGHTEENTH
HH   03   （12小時）
HH24   15
MI   31   分鐘
SS   48   秒
AM   PM
D   5
DY   FRI
DAY   FRIDAY


SQL> select to_char(sysdate,'yyyy mm ddhh miss')
  2  from dual;

TO_CHAR(SYSDATE,'
-----------------
2005 11 1904 1946



時期+/-數字，可以得到一個新的日期。（加在日子上）
兩個日期相減，得到一個數字。
如果想加小時到日期上，必須先除以24，在與對應日期相加。

  1  select to_char(sysdate+0.25,'yyyy mm dd hh miss')
  2* from dual

當前時間+6小時的時間。

日期相關函數

MONTHS_BETWEEN('01-SEP-95','11-JAN-94')
19.774194

1  select ROUND(MONTHS_BETWEEN('01-SEP-95','11-JAN-94'))
  2* from dual
  3  ;

20

返回兩個日期之間的月差值。

ADD_MONTHS('11-JAN-94',6)

NEXT_DAY('01-SEP-95','FRIDAY')

注意：下一個星期幾，但不一定下一周的星期幾，找到第一個為準。

LAST_DAY('01-SEP-98')

'30-SEP-98'

ROUND('25-MAY-95','MONTH')
01-JUN-95

TRUNC('25-MAY-95','MONTH')
01-MAY-95




TO_CHAR(date,'fmt')

fm是一個去除前置0或者空格的開關，fm....fm....

fm作用域，是從當前到下一個fm之間的范圍。第二個fm后面表示不去除前置0，奇偶變。


TO_CHAR(number,'fmt')

9
0
$
L
.
,


TO_NUMBER(char)

char必須是一個‘數字’



TO_DATE(char[,'fmt'])
不給定日期轉換格式，就按照默認的dd-mon-rr，進行轉換。



函數可以嵌套

  1  select last_name,
  2  NVL(TO_CHAR(MANAGER_ID),'No Manager')
  3  from s_emp
  4* where manager_id IS NULL




Join方法

等值
非等值
外連接
自連接


必須有連接鍵，否則就是迪卡爾連接。

  1  select s.last_name,s.dept_id,d.id,d.name
  2  from s_emp s,s_dept d
  3* where s.dept_id=d.id

LAST_NAME          DEPT_ID         ID NAME
--------------- ---------- ---------- -------------------------
Dumas                   31         31 Sales
Dumas                   32         32 Sales
Dumas                   33         33 Sales
payn                      34         34 Sales

外連接是一種特殊的等值連接，用于有空值出現時，尋找到所有記錄。

  1  select s.last_name,s.id,c.sal,c.name
  2  from s_emp s,s_customer c
  3* where s.id(+) = c.sal

LAST_NAME        ID        SAL      NAME
--------------- ---------- ---------- -------------------------
Dumas                   12         12         athletes attic
Dumas                   12         12         great athletes
Dumas                   12         12         bj athletics
Dumas                   12         12         athletic for all
Dumas                   12         12         sports,inc
                                          14         athletics two
                                          14         athletics one
                                          14         shhes for sports
                                          14         athletic attire
那個+ 表示有一側可能沒有對應數據。

表示有部分客戶沒有銷售代表。

如果+ 在另外一側，表示查找所有銷售的客戶情況。