SELECT [/*+ optimizer_hints*/]
[ALL | DISTINCT] select_list
[FROM
{table_name | view_name |    select_statement}]
[WHERE clause]
[GROUP BY group_by_expression]
[HAVING search_condition]
[START WITH … CONNECT BY]
[{UNION | UNION ALL | INTERSECT |
MINUS} SELECT …]
[ORDER BY clause]
[FOR UPDATE]

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[ GROUP BY [ALL] group_by_expression [,…n]
 [ WITH { CUBE | ROLLUP } ]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]

In addition:

UNION Operator
COMPUTE Clause
FOR BROWSE Clause
OPTION Clause

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS
INTERSECT
SELECT C.CCODE, C.CNAME
FROM STUDENT_ADMIN.GRADE G,
DEPT_ADMIN.CLASS C
WHERE C.CCODE = G.CCODE

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS C
WHERE EXISTS
(SELECT 'X' FROM    STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE)

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASS
MINUS
SELECT C.CCODE, C.CNAME
FROM STUDENT_ADMIN.GRADE G,
DEPT_ADMIN.CLASS C
WHERE C.CCODE = G.CCODE

SELECT CCODE, CNAME
FROM DEPT_ADMIN.CLASSC
WHERE NOT EXISTS
(SELECT 'X' FROM    STUDENT_ADMIN.GRADE G
WHERE C.CCODE = G.CCODE)

INSERT INTO
{table_name | view_name | select_statement} [(column_list)]
{values_list | select_statement}

INSERT [INTO]
    {
     table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n])
     | view_name [ [AS] table_alias]
     | rowset_function_limited
    }

    {    [(column_list)]
 { VALUES ( {    DEFAULT
   |    NULL
   |    expression
   }[,…n]
     )
 | derived_table
 | execute_statement    
 }
    }
    | DEFAULT VALUES

INSERT INTO (SELECT SSN, CCODE,    GRADE FROM GRADE)
VALUES ('111111111', '1111',NULL)

INSERT INTO GRADE (SSN, CCODE,    GRADE)
VALUES ('111111111', '1111',NULL)

UPDATE
{table_name | view_name |    select_statement}
SET [column_name(s) = {constant_value |    expression | select_statement | column_list |
   variable_list}
{where_statement}

UPDATE
{
table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n])
view_name [ [AS] table_alias]
| rowset_function_limited
}
SET
{column_name = {expression | DEFAULT | NULL}
| @variable = expression
| @variable = column = expression } [,…n]

{{[FROM {<table_source>} [,…n] ]

[WHERE
<search_condition>] }
|
[WHERE CURRENT OF
{ { [GLOBAL] cursor_name } | cursor_variable_name}
] }
[OPTION (<query_hint> [,…n] )]

DECLARE
VAR1 NUMBER(10,2);
BEGIN
    VAR1 := 2500;
    UPDATE     STUDENT_ADMIN.STUDENT
    SET TUITION_TOTAL = VAR1;
END;

DECLARE
@VAR1 NUMERIC(10,2)
SELECT @VAR1 = 2500
UPDATE STUDENT_ADMIN.STUDENT
SET TUITION_TOTAL=@VAR1

UPDATE
   STUDENT_ADMIN.STUDENT S
SET TUITION_TOTAL = 1500
WHERE SSN IN (SELECT SSN
FROM GRADE G
WHERE G.SSN = S.SSN
AND G.CCODE = '1234')

Subquery:

UPDATE
   STUDENT_ADMIN.STUDENT S
SET TUITION_TOTAL = 1500
WHERE SSN IN (SELECT SSN
FROM GRADE G
WHERE G.SSN = S.SSN
AND G.CCODE = '1234')

FROM clause:

UPDATE
   STUDENT_ADMIN.STUDENT S
SET TUITION_TOTAL = 1500
   FROM GRADE G
      WHERE S.SSN = G.SSN
      AND G.CCODE = '1234'

DELETE [FROM]
{table_name | view_name |    select_statement}
[WHERE clause]

DELETE
[FROM ]
{
table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n])
| view_name [ [AS] table_alias]
| rowset_function_limited
}

[ FROM {<table_source>} [,…n] ]

[WHERE
{ <search_condition>
|    { [ CURRENT OF
{
{ [ GLOBAL ] cursor_name }
cursor_variable_name
}
]
}
]
[OPTION (<query_hint> [,…n])]

TRUNCATE TABLE table_name
[{DROP | REUSE} STORAGE]

TRUNCATE TABLE table_name

SET IDENTITY_INSERT table_name ON

Msg 272, Level 16, State 1 Can't update a TIMESTAMP column.

CROSS JOIN

这是两个表的交叉产物。如果在一个旧式的连接中没有指定WHERE子句，则返回同一行。在Oracle中，这种类型的连接叫做笛卡儿连接。

INNER

该连接指定返回所有的内部行。任何不匹配的行将被丢弃。该连接同一个标准的Oracle表连接是一样的。

LEFT[OUTER]

这种类型的连接指定返回右边表的所有外部行，即使没有发现任何匹配行。该操作符同Oracle外部连接（+）是一样的。

RIGHT[OUTER]

这种类型的连接指定返回左边表的所有外部行，即使没有发现任何匹配行。该操作符同Oracle外部连接（+）是一样的。

FULL [OUTER]

如果来自任一表的一行不匹配选择标准，指定该行被包括到结果集中，并且它的符合其它表的输出列被设定为NULL。这和把Oracle外部连接操作符放在“=”的两端是一样的（col1(+) = col2(+)），而在Oracle中，这是不允许的。

SELECT S.SSN AS SSN,
FNAME, LNAME
FROM STUDENT_ADMIN.STUDENT S,
DEPT_ADMIN.CLASS C,
STUDENT_ADMIN.GRADE G
WHERE S.SSN = G.SSN(+)
AND G.CCODE = C.CCODE(+)

SELECT S.SSN AS SSN,
FNAME, LNAME
FROM STUDENT_ADMIN.GRADE G
RIGHT OUTER JOIN
STUDENT_ADMIN.STUDENT S
ON G.SSN = S.SSN
LEFT OUTER JOIN
DEPT_ADMIN.CLASS C
ON G.CCODE = C.CCODE

SELECT SSN, LNAME, FNAME,
TUITION_PAID, SUM_PAID
FROM STUDENT_ADMIN.STUDENT,
(SELECT SUM(TUITION_PAID) SUM_PAID FROM    STUDENT_ADMIN.STUDENT)

SELECT SSN, LNAME, FNAME,
TUITION_PAID, SUM_PAID
FROM STUDENT_ADMIN.STUDENT,
(SELECT SUM(TUITION_PAID) SUM_PAID FROM    STUDENT_ADMIN.STUDENT) SUM_STUDENT

绝对值

ABS

ABS

Arc cosine

ACOS

ACOS

Arc sine

ASIN

ASIN

Arc tangent of n

ATAN

ATAN

Arc tangent of n and m

ATAN2

ATN2

Smallest integer >= value

CEIL

CEILING

Cosine

COS

COS

Hyperbolic cosine

COSH

COT

Exponential value

EXP

EXP

Largest integer <= value

FLOOR

FLOOR

Natural logarithm

LN

LOG

Logarithm, any base

LOG(N)

N/A

Logarithm, base 10

LOG(10)

LOG10

Modulus (remainder)

MOD

USE MODULO (%) OPERATOR

Power

POWER

POWER

Random number

N/A

RAND

Round

ROUND

ROUND

Sign of number

SIGN

SIGN

Sine

SIN

SIN

Hyperbolic sine

SINH

N/A

Square root

SQRT

SQRT

Tangent

TAN

TAN

Hyperbolic tangent

TANH

N/A

Truncate

TRUNC

N/A

Largest number in list

GREATEST

N/A

Smallest number in list

LEAST

N/A

Convert number if NULL

NVL

ISNULL

把字符转换为ASCII

ASCII

ASCII

字串连接

CONCAT

(expression + expression)

把ASCII转换为字符

CHR

CHAR

返回字符串中的开始字符（左起）

INSTR

CHARINDEX

把字符转换为小写

LOWER

LOWER

把字符转换为大写

UPPER

UPPER

填充字符串的左边

LPAD

N/A

清除开始的空白

LTRIM

LTRIM

清除尾部的空白

RTRIM

RTRIM

字符串中的起始模式（pattern）

INSTR

PATINDEX

多次重复字符串

RPAD

REPLICATE

字符串的语音表示

SOUNDEX

SOUNDEX

重复空格的字串

RPAD

SPACE

从数字数据转换为字符数据

TO_CHAR

STR

子串

SUBSTR

SUBSTRING

替换字符

REPLACE

STUFF

将字串中的每个词首字母大写

INITCAP

N/A

翻译字符串

TRANSLATE

N/A

字符串长度

LENGTH

DATELENGTH or LEN

列表中最大的字符串

GREATEST

N/A

列表中最小的字符串

LEAST

N/A

如果为NULL则转换字串

NVL

ISNULL

日期相加

(date column +/- value) or
ADD_MONTHS

DATEADD

两个日期的差

(date column +/- value) or
MONTHS_BETWEEN

DATEDIFF

当前日期和时间

SYSDATE

GETDATE()

一个月的最后一天

LAST_DAY

N/A

时区转换

NEW_TIME

N/A

日期后的第一个周日

NEXT_DAY

N/A

代表日期的字符串

TO_CHAR

DATENAME

代表日期的整数

TO_NUMBER
(TO_CHAR))

DATEPART

日期舍入

ROUND

CONVERT

日期截断

TRUNC

CONVERT

字符串转换为日期

TO_DATE

CONVERT

如果为NULL则转换日期

NVL

ISNULL

数字转换为字符

TO_CHAR

CONVERT

字符转换为数字

TO_NUMBER

CONVERT

日期转换为字符

TO_CHAR

CONVERT

字符转换为日期

TO_DATE

CONVERT

16进制转换为2进制

HEX_TO_RAW

CONVERT

2进制转换为16进制

RAW_TO_HEX

CONVERT