DML: SELECT, INSERT, UPDATE, DELETE, MERGE ,
EXPLAIN PALN, LOCK TABLE
DDL: CREATE, ALTER, DROP, RENAME, TRUNCATE, GRANT, REVOKE, AUDIT, NOAUDIT, COMMENT
Transaction Control: COMMIT, ROLLBACK, SAVEPOINT,
SET TRANSACTION
Session Control：ALTER SESSION, SET ROLE
System Control: ALTER SYSTEM
ORACLE DATATYPES
CHAR: 固定長度的字串，用空格填充，長度為1~2000bytes，預設長度為1byte
VARCHAR2: 儲存長度為4000bytes，沒有預設長度值。
The default size of a CHAR datatype is 1. For a VARCHAR2 datatype, you must always specify the size.
對空格鍵的比較：
CHAR datatype： ‘Yo’=’ Yo ’
VARCHAR2 datatype: ‘Yo’ < ’Yo ’
NUMBER(P,S):
The precision can be between 1 and 38, and the scale has a range between –84 and 127. If the precision and scale are omitted,Oracle assumes the maximum of the range for both values.
例子：
123.2564 NUMBER 123.2564 ：Range and precision are set to the maximum, so the datatype can store any value.
1234.9876 NUMBER(6,2) 1234.99 ： Since scale is only 2, the decimal part of the value is rounded to two digits.
12345.12345 NUMBER(6,2) Error
123456 NUMBER(6,2) Error
1234.9876 NUMBER(6) 1235 ： Decimal part rounded to the next
integer.
12345.345 NUMBER(5,-2) 12300 ：Negative scale rounds the number <s> digits left to the decimal point. –2 rounds to hundreds.
1234567 NUMBER(5,-2) 1234600： Rounded to the nearest hundred.
12345678 NUMBER(5,-2) Error
123456789 NUMBER(5,-4) 123460000 ： Rounded to nearest 10000.
1234567890 NUMBER(5,-4) Error ：
12345.58 NUMBER(*, 1) 12345.6 ：Use of * in precision specifies the
default limit (38).
0.1 Number(4,5) Error : Requires a zero after the decimal
point(5-4=1)
0.01234567 NUMBER(4,5) 0.01235 :
0.09999 NUMBER(4,5) 0.09999 :
0.099996 NUMBER(4,5) Error : Rounding this value to four digits after the decimal and zero results in 0.1, which is outside the range.
DATE: The default date format is specified using the initialization
parameter NLS_DATE_FORMAT.
Set Operators:
UNION Returns all rows from either queries; no duplicate rows
UNION ALL Returns all rows from either query, including duplicates
INTERSECT Returns distinct rows that are returned by both queries
MINUS Returns distinct rows that are returned by the first query but
not returne by the second.
Literals(常量)：
Text：例子:'The Quick Brown Fox'
Integer: 例子：24
Number： 例子 ：–345.65
Interval： 例子：INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND.
表的keyword ,column names,table names是大小寫不敏感的，只有雙引號括起來的才是敏感的。
Column Alias Names：用空格或者AS 關鍵字 來作為alias names，當使用雙引號括起來的時候是大小寫敏感的，否則得話，都是以大寫處理。
The DISTINCT keyword：只能放在column的前面，若放在2個column的前面，則是對2個column作唯一判斷。
WHERE ：不能在where字句中使用alias name。
Not in （null）： 此時無返回值
Between ：between and（包括等於號）
Like：如果要找AC_MEG ，則like ‘AC_%’
Order by :如果distinct中使用的column，order by中也應該有。
Sorting Nulls：在順序order 排序中，空值在結果的底部。在逆序排序中，空值在結果的最上面。可以使用nulls first 和nulls last來改變。
The CASE Expression：
Case
When THEN
[else ]
End
例子： select country_name, region_id,
Case region_id when 1 then ‘Europe’
When 2 then ‘America’
When 3 then ‘Asia’
Else ‘Other’ END Continent
From countries
Where country_name like ‘I%’
COUNTRY_NAME REGION_ID CONTINE
Israel 4 Other
India 3 Asia
CASE
WHEN THEN value>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11997930/viewspace-1027227/，如需轉載，請註明出處，否則將追究法律責任。