526互联

SQL-92 具体内容（READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE）

发布时间 2023-06-02 08:17:45作者: zno2

http://www.ocelot.ca/commands.htm

ALTER

Change domain, schema, sequence, or table definition.
Example: ALTER TABLE TABLE_1 DROP COLUMN COLUMN_1 RESTRICT;

  • ALTER DOMAIN domain-name
    ... ADD constraint-definition
    ... DROP CONSTRAINT constraint-name { RESTRICT | CASCADE }
    ... DROP DEFAULT { RESTRICT | CASCADE }
    ... SET default-clause
  • ALTER SCHEMA schema-name
    ... DEFAULT CHARACTER SET character-set-name
  • ALTER SEQUENCE sequence-name
    ... AS data-type
    ... { CYCLE | NO CYCLE }
    ... INCREMENT BY n
    ... MAXVALUE n
    ... MINVALUE n
    ... RESTART WITH n
  • ALTER TABLE table-name
    ... ADD [COLUMN] column-name column-definition
    ... ADD constraint-definition
    ... ALTER [COLUMN] column-name SET
    ... ALTER [COLUMN] column-name DROP
    ... ALTER [COLUMN] column-name RESTART
    ... DROP COLUMN column-name
    ... DROP CONSTRAINT constraint-name
    ... DROP FOREIGN KEY foreign-key-name
    ... DROP PRIMARY KEY primary-key-name

 

BEGIN

Start a compound statement. Example: BEGIN INSERT INTO t VALUES (5); END;

  • BEGIN
    ... [ATOMIC] any series of SQL/PSM statements

 

CALL

Call an SQL-invoked routine. Example: CALL ROUTINE_X;

  • CALL
    ... routine-name

 

CLOSE

Close a cursor. This is only legal in a compound statement. Example: BEGIN ... OPEN ... CLOSE ... END

  • CLOSE
    ... cursor-name

 

COMMIT

Make permanent any changes to the database since the last commit. Example: COMMIT

  • COMMIT [WORK]

 

CONNECT

Tell the DBMS what cluster you want to access, who you are, and what name you'd like your new connection to have. Example: CONNECT TO 'OCELOT' AS 'CONNECTION_1' USER 'OCELOT';

  • CONNECT TO dsn [AS connection name] [USER user name]

CONNECT STRINGS. The string used for dsn or connection name or user name may contain additional or alternative information, with the form 'tag=value'. The possible tags are: 'dsn=' 'uid=' 'pwd=' 'net=' 'well_known_port=' 'encrypted=' 'remoteipaddress=' 'homeipaddress=' 'disable_autodial=' 'autocommit=' 'max_rows=' 'login_timeout=' 'txn_isolation=' 'quirks=' 'query_timeout=' 'current_catalog=' 'current_schema=' 'server=' 'database=' 'use_big_heaps=' 'max_dbcs=' 'max_stmts=' 'max_stmt_heaps=' 'resources_base_address='. Example: CONNECT TO 'ocelot;uid=peter;WELL_KNOWN_PORT=9999'

 

CREATE

CREATE. Make an object. For short list of common objects click here. Example: CREATE TABLE TABLE_1 (COLUMN_1 CHARACTER VARYING (20000));

  • CREATE ASSERTION assertion-name
  • CREATE CHARACTER SET character-set-name
    ... [AS] GET source [COLLATE name] [COLLATION FROM 'language']
  • CREATE COLLATION collation-name
    ... FOR character-set-spec FROM source [NO PAD | PAD SPACE ]
  • CREATE DOMAIN domain-name
    ... [AS] data type
  • CREATE FUNCTION function-name
    ... function definition
  • CREATE PROCEDURE procedure-name
    ... procedure definition
  • CREATE SCHEMA schema-name
    ... AUTHORIZATION auth-id
    ... DEFAULT CHARACTER SET character-set-name
    ... any series of CREATE or GRANT statements
  • CREATE SEQUENCE sequence-name
    ... AS data-type
    ... { CYCLE | NO CYCLE }
    ... INCREMENT BY n
    ... MAXVALUE n
    ... MINVALUE n
    ... RESTART WITH n
  • CREATE TABLE table-name
    ... (column definition, ...)
  • CREATE {LOCAL|GLOBAL} TEMPORARY TABLE table-name
    ... {column definition, ...} [ON COMMIT {PRESERVE|DELETE} ROWS]
  • CREATE ROLE role-name
    ... role definition
  • CREATE TRANSLATION translation-name
    ... FOR character-set TO character-set
  • CREATE TRIGGER trigger-name
    ... [BEFORE|AFTER] INSERT|UPDATE|DELETE ON table [FOR EACH ROW] routine body
  • CREATE TYPE type-name
    ... type definition
  • CREATE VIEW view-name
    ... view definition

 

DECLARE

Name and allocate space for a variable. Legal only in SQL/PSM. Example: BEGIN DECLARE v INT; SET v = 5; INSERT INTO t VALUES (v); END;

  • DECLARE variable-name data-type

 

DELETE

Get rid of rows in a table. Example: DELETE FROM TABLE_1 WHERE COLUMN_1 = 'X';

  • DELETE
    ... FROM table-name [WHERE clause]

 

DISCONNECT

The opposite of CONNECT. Like logging off. Example: DISCONNECT ALL;

  • DISCONNECT
    ... ALL
    ... connection name

 

DROP

The opposite of CREATE. Get rid of the definition. Example: DROP COLLATION C RESTRICT;

  • DROP ASSERTION assertion-name
  • DROP CHARACTER SET character-set-name
  • DROP COLLATION collation-name
  • DROP DOMAIN domain-name
  • DROP ROLE role-name
  • DROP SCHEMA schema-name
  • DROP SEQUENCE sequence-name
  • DROP TABLE table-name
  • DROP TRIGGER trigger-name
  • DROP VIEW view-name

 

END

Mark the termination of a compound statement that started with BEGIN. Example: BEGIN INSERT INTO T VALUES (5); END

  • END

 

FETCH

    • FETCH [NEXT] FROM cursor-name
      ... INTO variable,...

       

      GRANT

      Give privileges to users so they can access or change things. Example: GRANT USAGE ON TRANSLATION TRANSLATION_1 TO SAM;
      • GRANT USAGE ON CHARACTER SET character-set-name TO user-list
      • GRANT USAGE ON COLLATION collation-name TO user-list
      • GRANT EXECUTE ON SPECIFIC ROUTINE routine-name TO user-list
      • GRANT USAGE ON TRANSLATION translation-name TO user-list
      • GRANT DELETE INSERT|ALL PRIVILEGES|SELECT USAGE ON table-name TO user-list
      • GRANT DELETE UPDATE ON table-name [column list] TO user-list

       

      INSERT

      Put rows in a table. This is what you need to add new data. Example:INSERT INTO TABLE_1 VALUES ('X',55.5,1E+7);
      • INSERT INTO table-name
        ... VALUES (list of values)
        ... SELECT statement

       

      IF

      Conditional execution. This is only legal with SQL/PSM. Example: BEGIN DECLARE v1 INT; SET v1=9; ww: WHILE v1=9 DO IF v1=10 THEN ITERATE ww; ELSE LEAVE ww; END IF; END WHILE ww; END;
      • IF condition THEN statement ELSE statement END IF

       

      ITERATE

      Go back to loop start. This is only legal with SQL/PSM. Example: BEGIN DECLARE v1 INT; SET v1=9; ww: WHILE v1=9 DO IF v1=10 THEN ITERATE ww; ELSE LEAVE ww; END IF; END WHILE ww; END;
      • ITERATE label

       

      LEAVE

      Break out of a loop. This is only legal with SQL/PSM. Example: BEGIN DECLARE v1 INT; SET v1=9; ww: WHILE v1=9 DO IF v1=10 THEN ITERATE ww; ELSE LEAVE ww; END IF; END WHILE ww; END;
      • LEAVE label

       

      LOOP

      Loop start. This is only legal with SQL/PSM. Example: BEGIN DECLARE v1 INT; SET v1=5; ww: LOOP INSERT INTO tt VALUES (v1); SET v1=v1+1; IF v1 > 5 THEN LEAVE ww; END IF; END LOOP ww; END;
      • LOOP statements END LOOP

       

      OPEN

      Open a cursor. This is only legal with SQL/PSM. Example: BEGIN ... OPEN ... FETCH ... CLOSE ... END;
      • OPEN cursor-name

       

      REPEAT

      Loop start. This is only legal with SQL/PSM. Example: BEGIN DECLARE v1 INT; SET v1=7; REPEAT INSERT INTO tt VALUES (v1); SET v1=v1+1 UNTIL v1=8; END REPEAT; END;
      • REPEAT statements UNTIL condition END REPEAT

       

      REVOKE

      The opposite of GRANT. Take privileges away from users. Example: REVOKE USAGE ON TRANSLATION_1 FROM SAM;
      • REVOKE USAGE ON CHARACTER SET character-set-name FROM user-list
      • REVOKE USAGE ON COLLATION collation-name FROM user-list
      • REVOKE EXECUTE ON SPECIFIC ROUTINE routine-name FROM user-list
      • REVOKE USAGE ON TRANSLATION translation-name FROM user-list
      • REVOKE DELETE INSERT|ALL PRIVILEGES|SELECT USAGE FROM table-name TO user-list
      • REVOKE DELETE UPDATE ON table-name [column list] FROM user-list

       

      RETURN

      Return from a function|procedure. This is only legal with SQL/PSM. Example: CREATE FUNCTION f1 () RETURNS INT RETURN 7;
      • RETURN
      • RETURN value

       

      ROLLBACK

      Cancel the effects of all activity since the last COMMIT. So if you altered a table, now you can un-alter it. Example: ROLLBACK;
      • ROLLBACK [WORK]

       

      SAVEPOINT

      Establish a savepoint for partial rollbacks. Example: SAVEPOINT AFTER_UPDATE;
      • SAVEPOINT label

       

      SELECT

      Find data. Typically: search for rows that meet certain criteria in certain tables, so that the results can be displayed. Example: SELECT COLUMN_1 FROM TABLE_1 WHERE COLUMN_1 = 'X';
      • SELECT select-list FROM tables [WHERE conditions] [GROUP BY columns] [HAVING conditions] [ORDER BY columns]

       

      SET

      Change connection parameters, such as the user name or the time zone or the default schema. Or -- SQL/PSM only -- set value for variable. Example: SET CONSTRAINTS ALL IMMEDIATE;
      • SET CATALOG name
      • SET CONNECTION name
      • SET CONSTRAINTS { name | ALL } { DEFERRED | IMMEDIATE }
      • SET FLAGGER { ON | OFF }
      • SET NAMES name
      • SET ROLE name
      • SET SCHEMA name
      • SET SESSION AUTHORIZATION name
      • SET TIME ZONE interval
      • SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
      • SET variable = expression

       

      SIGNAL

      Make a diagnostic. This is only legal within SQL/PSM. Example: SIGNAL SQLSTATE = '55555';
      • SIGNAL SQLSTATE = value

       

      UPDATE

      Change information stored in existing rows. Example: UPDATE TABLE_1 SET COLUMN_1 = 'Y' WHERE COLUMN_1 = 'X';
    • UPDATE table-name SET set-clause [WHERE conditions]