参考:http://owen.sj.ca.us/rkowen/howto/sql92F.html
SQL92语法
翻译整理:Wnhoo Mail:Wnhoo@Gmail.com
==================================================
SQL92 Syntax and common or <Oracle> additions (not = '!')
数据类型:
CHAR(n) | CHARACTER(n)
VARCHAR(n) | CHARACTER VARYING(n) | <VARCHAR2(n)>
INTEGER | INT | SMALLINT
DECIMAL(p,s) | DEC(p,s) | NUMERIC(p,s)
FLOAT(p) | REAL | DOUBLE PRECISION
DATE | TIME
INTERVAL year-month | INTERVAL day
BOOLEAN | BLOB
条件:
< > <= >= <> = AND OR NOT
IS [NOT] NULL [NOT] LIKE
[NOT] IN ( [,...] ) [NOT] BETWEEN x AND y
[conditional] ANY ( [,...] )
[conditional] ALL ( [,...] )
函数:
AVG | MAX | MIN | SUM | COUNT
GREATEST|LEAST(x,y,...)
<{ROUND|TRUNC<!ATE>}({x,places|date,format})>
POSITION( s1 IN s2)
EXTRACT( datetime FROM datetime_value)
CHAR_LENGTH( s1 ) <LENGTH( s1 )>
SUBSTRING(string FROM start [FOR length])|<SUBSTR(string,start,length)>
<INSTR(str,substr,start,mnth)>
{<INITCAP>|UPPER|LOWER}(string)
TRIM({BOTH|LEADING|TRAILING} char FROM string)|<{L|R}TRIM(str,chrset)>
{TRANSLATE|CONVERT}( char USING value) |<TRANSLATE(str,from,to)>
<{L|R}PAD(str,to_len,str2)>
<DECODE(expr,search1,result1,...[,default])>
<NVL(expr,replace)>
<Date Format - ROUND|TRUNC|TO_CHAR|TO_DATE(value,fmt)>:
SYYYY|YYYY|YEAR|SYEAR|YY|IYYY|RR|RRRR MONTH|MON|MM|RM
DDD|DD|J DAY|DY|D HH|HH12|HH24 MI SS|SSSSS
IW AM|PM BC Q WW(year week) W (month week)
Table 约束:
[CONSTRAINT cname] {{UNIQUE|PRIMARY KEY}(col,...)|
CHECK(condition)|FOREIGN KEY (col,...) REFERENCES table(col,...)}
Column 约束:
[CONSTRAINT cname] {[NOT] NULL|UNIQUE|PRIMARY KEY|
REFERENCES table(col,...) ON DELETE CASCADE|CHECK(condition)}
===================================================
数据库命令
ALTER TABLE
Description: 修改table结构
ALTER TABLE table [ * ]
ADD [<!COLUMN>] column type
ALTER TABLE table [ * ]
DROP [ COLUMN ] column
ALTER TABLE table [ * ]
MODIFY [<!COLUMN>] column { <!SET> DEFAULT value | DROP DEFAULT }
ALTER TABLE table [ * ]
MODIFY [<!COLUMN>] column column_constraint
ALTER TABLE table [ * ]
RENAME [<!COLUMN>] column TO newcolumn
ALTER TABLE table
RENAME TO newtable
ALTER TABLE table
ADD table_constraint
ALTER TABLE table
{ENABLE|DISABLE} {NO}VALIDATE CONSTRAINT constraint
ALTER USER
Description: 设置用户密码
ALTER USER username IDENTIFIED BY passwd
CREATE TABLE
Description: Creates a new table
CREATE <![TEMPORARY|TEMP]> TABLE table (
column type
[ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ]
[column_constraint_clause | PRIMARY KEY } [ ... ] ]
[, ... ]
[, PRIMARY KEY ( column [, ...] ) ]
[, CHECK ( condition ) ]
[, table constraint ]
)
<CREATE TABLE table AS select query>
(CREATE <OR REPLACE> TRIGGER) (非SQL92语法)
Description: Creates a new trigger
CREATE TRIGGER name { BEFORE | AFTER |INSTEAD OF}
{DELETE| INSERT | UPDATE [OF (col,...)] [OR ...] }
ON {table|view} FOR EACH { ROW | STATEMENT }
[ WHEN (condition) ]
[<!EXECUTE PROCEDURE func ( arguments )>| pl/sql block]
CREATE SEQUENCE (非SQL92语法)
Description: 创建一个序号生成器
CREATE SEQUENCE seqname [ INCREMENT BY increment ]
[ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
[ START start ] [ CACHE cache | <WITH> NOCACHE ] [ CYCLE ]
(use seqname.CURRVAL & seqname.NEXTVAL)
CREATE INDEX (非SQL92语法)
Description: Constructs a secondary index
CREATE [UNIQUE|<BITMAP>] INDEX index_name ON table
[<!USING acc_name>] ( column [ASC|DESC][,...]) [NOSORT|REVERSE]
CREATE [ UNIQUE ] INDEX index_name ON table
[ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )
(CREATE <OR REPLACE> VIEW)
Description: Constructs a virtual table
CREATE VIEW view AS select query
ALTER VIEW view COMPILE
CREATE SYNONYM (非SQL92语法)
Description: 创建一个对象的别名
CREATE SYNONYM synname FOR object
COMMENT (Oracle)
Description: Comment on objects and view in USER_{TAB|COL}_COMMENTS
COMMENT ON TABLE table IS 'string'
COMMENT ON COLUMN table.col IS 'string'
TRUNCATE TABLE (Oracle)
Description: Remove all table rows
TRUNCATE TABLE table
RENAME TABLE (Oracle)
Description: Rename the object
RENAME table TO newtable
DROP
Description: Removes existing objects from database
DROP TABLE name [,...] <CASCADE CONSTRAINTS>
DROP VIEW name
DROP SEQUENCE name [,...]
...
-------------------------------------------------------------------------------
INSERT
Description: Inserts new rows into a table
INSERT INTO table [ ( column [, ...] ) ]
{ VALUES ( expression [, ...] ) | SELECT query }
UPDATE
Description: Replaces values of columns in a table
UPDATE table SET col = expression [,...]
[ FROM fromlist ]
[ WHERE condition ]
DELETE
Description: Removes rows from a table
DELETE FROM table [ WHERE condition ]
SELECT query
Description: Retrieve rows from a table or view
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
expression [ <![AS]> name ] [,...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
[ FROM {table | (select query)} [ alias ] [,...] ]
[ {{LEFT | RIGHT} [OUTER] | NATURAL |[FULL] OUTER} JOIN table alias
{ON condition | USING(col1,col2,...)} ]
[ WHERE {condition | EXISTS (correlated subquery)} ]
[ GROUP BY column [,...] ]
[ HAVING condition [,...] ]
[ { UNION [ ALL ] | INTERSECT | EXCEPT | MINUS } select ]
[ ORDER BY {column | int} [ ASC | DESC | USING operator ] [,...] ]
[ FOR UPDATE [ OF class_name [,...] ] ]
LIMIT { count | ALL } [ { OFFSET | ,} start ]
DECLARE (Oracle)
Description: Defines a cursor for table access
DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ SCROLL ]
CURSOR FOR query
[ FOR { READ ONLY | UPDATE [ OF column [,...] ] ]
FETCH (Oracle)
Description: Gets rows using a cursor
FETCH [ selector ] [ count ] { IN | FROM } cursor
FETCH [ RELATIVE ] [{ [ # | ALL | NEXT | PRIOR ] }] FROM cursor
Command: CLOSE (Oracle)
Description: Close a cursor
CLOSE cursor
Last Modified: 2002/09/21 18:10:42
Brought to you by: R.K. Owen, Ph.D.
来源于 http://owen.sj.ca.us/rkowen/howto/sql92F.html