SG_007_CHAPTER ONE

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>

[else ]

End

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11997930/viewspace-1027227/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11997930/viewspace-1027227/