SQL92语法

最新推荐文章于 2023-08-08 21:48:16 发布

basecn

最新推荐文章于 2023-08-08 21:48:16 发布

阅读量985

点赞数

分类专栏：数据库开发技术

本文链接：https://blog.csdn.net/basecng/article/details/10074907

版权

数据库开发技术专栏收录该内容

14 篇文章 0 订阅

订阅专栏

参考：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