SQL. , SQL. , SQL; , . ( BNF ):
.
SQL (<and>).
([and]).
(....) , .
(|) , , , .
({and}) , , (, ).
(::=) , , , .
, (.,..) , , . , , (* *) .
: , , ANSI. , .
, ANSI, , -. , <predicate> ANSI <predicate> <search condition>.
SQL
SQL. : .
; SQL , . <separator> ; <separator> . , SQL .
, , . , <separator>, SQL. , . , . . , <predicate>, , <predicate> . , <predicate> AND OR <predicate> .
|
|
<predicate> - . .
<separator> | <comment> | <space> | <newline> |
<comment> | --<string> <newline> |
<space> | |
<newline> | - |
<identifier> | <letter>[{<letter or digit> | <underscore>}... ] : ANSI, , <identifier> 18- . |
<underscore> | - |
<percent sign> | % |
<delimiter> | :, () < >.: = + " - | <> > = < = <string> |
<string> | [ ] : <string>, ('') ('). |
<SQL term> | , . (* *) |
SQL :
<query> | SELECT |
<subquery> | SELECT , , , - . |
<value expression> | <primary> | <primary> <operator> <primary> | <primary> <operator> <value expression> |
<operator> | : + - / * |
<primary> | <column name> | <literal> | <aggregate function> | <built-in constant> | <nonstandard function> |
<literal> | <string> | <mathematical expression> |
<built-in constant> | USER | <implementation-defined constant> |
<table name> | <identifier> |
<column spec> | [<table name> | <alias>]<column n ame> |
<grouping column> | <column spec> | <integer> |
<ordering column> | <column spec> | <integer> |
<colconstraint> | NOT NULL | UNIQUE | CHECK (<predicate>) | PRIMARY KEY | REFERENCES <table name>[(<column name>)] |
<tabconstraint> | UNIQUE (<column list>) | CHECK (<predicate>) | PRIMARY KEY (<column list>) | FOREIGN KEY (<column list>) REFERENCES <table name>[(<column list>)] |
<defvalue> | = <value expression> |
<data type> | (. B ANSI C .) |
<size> | <data type> (. B.) |
<cursor name> | <identifier> |
<index name> | <identifier> |
<synonym> | <identifier> (*nonstandard*) |
<owner> | <Authorization ID> |
<column list> | <column spec>.,.. |
<value list> | <value expression>.,.. |
<table reference> | { <table name> [<alias>] }.,.. |
|
|
<predicate> :
<predicate>::= [NOT]{ <comparison predicate> | <in predicate> | <null predicate> | <between predicate> | <like predicate> | <quantified predicate> | <exists predicate> } [AND | OR <predicate>]
<predicate> , , , <exists predicate> <null predicate>, .
, NULL . , NULL .
AND, OR NOT <predicate>. NOT = , NOT = , NOT = . AND OR , :
AND | |||
OR | |||
: , , , . AND, , () () , : AND = .
. . NOT , AND OR. <predicate> .
<comparison predicate> ( )
<value expresslon> <relational op> <value expresslon> | <subquery> <relatlonal op>::= = | < | > | < | >= | <>
<value expression> = NULL, <comparison predicate> = ; , .
<relational op> ; , . <value expression> . <subquery> , <value expression> SELECT, <value expression> <comparision predicate>, <subquery> .
<between predicate>
<value expression> [NOT] BETWEEN <value expression> AND <value expression>
<between predicate> A BETWEEN B AND C, <predicate> (A >= B AND <= C). <between predicate> A NOT BETWEEN B AND C, NOT (BETWEEN B AND C). <value expression> <subquery> (*nonstandard*).
<in prediicate>
<value expression> [NOT] IN <value list> | <subquery>
<value list> , <value expression> . <subquery>, <value expression> SELECT ( , ANSI). <subquery> , - , , <value list> . , <in predicate> <value expression> <value list>, NOT. A NOT IN (B, C) NOT (A IN (B, C)).
|
|
<like predicate>
<charvalue> [NOT] LIKE <pattern> [ESCAPE <escapechar>]
<charvalue> ** <value expression> - . <charvalue> , , <column spec>. <pattern> <string> <charvalue>. <escapechar> - . , :
* <underscore> <pattern> <escapechar>, <charvalue>.
* <percent sign> <pattern> <escapechar>, <charvalue>.
* <escapechar> <pattern> <escapechar>, <charvalue>.
* <pattern>, <charvalue>.
, <like predicate> , NOT. NOT LIKE '' NOT (A LIKE '').
<null predicate>
<column spec> IS [NOT] NULL
<column spec> = IS NULL, NULL . <null predicate> NULL. <column spec> IS NOT NULL , NOT(<column spec> IS NULL).
<quantified predicate>
<value expression> <relational op> <quantifier> <subquery> <quantifier>::= ANY | ALL | SOME
SELECT <subquery> <value expression>. <subquery> <result set>. <value expression> , <relational operator>, <result set>. :
<quantifier> = ALL, <result set> , <quantified predicate> .
<quantifier> = ANY, <result set>, , <quantified predicate> .
<result set> , <quantified predicate> , <quantifier> = ALL, .
<quantifier> = SOME, ANY.
<quantified predicate> , .
<exists predicate>
:
EXISTS (<subquery>)
|
|
<subquery> , <exists predicate> ; , .
SQL
SQL. , .
: , EXEC SQL, <SQL term> SQL.
BEGIN DECLARE SECTION
( )
EXEC SQL BEGIN DECLARE SECTION <SQL term>
<host-language variable declarations>
EXEC SQL END DECLARE SECTION<SQL term>
, SQL. SQLCODE .
CLOSE CURSOR
( )
EXEC SQL CLOSE CURSOR <cursor name> <SQL term>;
, .
COMMIT (WORK)
( ())
COMMIT WORK;
, , , , .
CREATE INDEX (*NONSTANDARD*)
( ) (**)
CREATE [UNIQUE] INDEX <Index name> ON <table name> (<column list>);
. UNIQUE , () .
CREATE SYNONYM (*NONSTANDARD*)
( ) (**)
CREATE IPUBLICl SYNONYM <synonym> FOR <owner>.<table name>;
() . , , . , ( ) . PUBLIC , SYSTEM .
CREATE TABLE
( )
CREATE TABLE <table name> ({<column name> <data type>[<size>] [<colconstralnt>...] [<defvalue>]}.,.. <tabconstraint>.,..);
. . .
<data type> . <data type> B; <data type>, C. <size> <data type>.
<colconstraint> <tabconstraint> , .
<defvalue> ( ) , . (. 17 CREATE TABLE 18 19 <defvalue>).
CREATE VIEW
( )
CREATE VIEW <table name> AS <query> [WITH CHECK OPTION];
SQL. <table name>, <query> , .
, , , <query>. WITH CHECK OPTION, <predicate> <query>.
DECLARE CURSOR
( )
EXEC SQL DECLARE <cursor name> CURSOR FOR <query><SQL term>
<cursor name>, <query>. (. OPEN CURSOR), <query> , ( FETCH) . , <query>, (. 25 ).
DELETE
()
|
|
DELETE FROM <table name> {[WHERE <predicate>];} | WHERE CURRENT OF <cursor name><SQL term>
WHERE , . WHERE <predicate>, , <predicate> . WHERE CURRENT OF () <cursor name>, <table name> <cursor name> . WHERE CURRENT SQL, .
EXEC SQL
( SQL)
EXEC SQL <embedded SQL command> <SQL term>
EXEC SQL , SQL, .
FETCH
()
EXEC SQL FETCH <cursorname> INTO <host-varlable llst><SQL term>
FETCH <query>, <host-variable list>, . <host-variable list> indicator (. 25.)
GRANT
( )
()
GRANT ALL [PRIVILEGES] | {SELECT | INSERT | DELETE | UPDATE [(<column llst>)] | REFERENCES [(<column llst>)l }.,.. ON <table name>.,.. TO PUBLIC | <Authorization ID>.,.. [WITH GRANT OPTION];
ALL (), PRIVILEGES (), . PUBLIC () .
. REFERENCES <column list> . , . UPDATE REFERENCES . GRANT OPTION .
()
GRANT DBA | RESOURCE | CONNECT.... TO <Authorization ID>.,.. [IDENTIFIED BY <password>]
CONNECT .
RESOURCE . DBA .
IDENTIFIED BY CONNECT, .
INSERT
()
INSERT INTO <table name> (<column llst>) VALUES (<value llst>) | <query>;
INSERT <table name>. VALUES, <table name>. <query> , <table name>. <column list> , <table name>, .
OPEN CURSOR
( )
EXEC SQL OPEN CURSOR <cursorname> <SQL term>
OPEN CURSOR , <cursor name>. FETCH.
REVOKE (*NONSTANDARD*)
( ) ()
REVOKE {ALL [PRIVILEGES] | <privilege>.,..} [ON <table name>] FROM { PUBLIC | <Authorization ID>.,..};
<privelege> GRANT. , REVOKE, , , GRANT. ON , .
ROLLBACK (WORK)
() ()
ROLLBACK WORK;
, . , , .
SELECT
()
SELECT { IDISTINCT | ALL] < value expression >.,.. } / *
[INTO <host variable list> (*embedded only*)]
FROM < table reference >.,..
[WHERE <predicate>]
[GROUP BY <grouping column>.,..]
[HAVING <predicate>]
[ORDER BY <ordering column> [ASC | DESC].,.. ];
(. 3 14). :
ALL, DISTINCT , ALL.
<value expression> <column spec>, <aggregate funct>, <nonstandard function>, <constant>, .
<table reference> , , , () . . , , . <separator>.
GROUP BY, <column spec> SELECT, <grouping column>, <aggregate funct>. <grouping column> <value expressions> SELECT. <grouping column>, .
HAVING , <predicate> GROUP BY, , .
ORDER BY , . <column identifer> <value expression> SELECT. <value expression> <column spec>, <column identifier> <column spec>. <column identifier> , <value expression> SELECT. <column identifier> , DESC . <column identifier>, ORDER BY .
SELECT - . - :
<table reference> , -.
<table reference> , . -.
- , <predicate> WHERE , , . GROUP BY , <value expression> -, , . GROUP BY , - , . <predicate> , <value expression> - . DISTINCT, () .
UNION
()
<query> {UNION [ALL] <query> }...;
<query> . <query> <value expression> SELECT 1..n , <data type> <size> 1..n .
UPDATE
()
UPDATE <table name>
SET { <column name> = <value expression> }.,..
{[ WHERE <predlcate>]; } | {[WHERE CURRENT OF <cursorname>] <SQL term>]}
UPDATE <column name> <value expression>. WHERE <predicate>, <predicate> , . WHERE CURRENT OF, <table name> <cursor name> . WHERE CURRENT OF SQL, . WHERE .
WHENEVER
( )
EXEC SQL WHENEVER <SQLcond> <actlon> <SQL term>
<SQLcond>::= SQLERROR | NOT FOUND | SQLWARNING
( )
<action>::= CONTINUE | GOTO <target> | GOTO <target>
<target>::=
E