Ïðèëîæåíèå 2

Îïåðàòîðû ÿçûêà SQL

1. Îïåðàòîðû îïèñàíèÿ

CREATE DATABASE database-name                           ñîçäàíèå áàçû äàííûõ

[WITH

{ [BUFFERED] LOG                                ñ (áóôôåðèçîâàííîé) æóðíàëèçàöèåé

| LOG MODE ANSI}]                                             â ñòàíäàðòå ANSI

 

CREATE SCHEMA schema-name                           ñîçäàíèå ñõåìû áàçû äàííûõ

CREATE [TEMP] TABLE table-name          ñîçäàíèå òàáëèöû

{ c èìåíåì table-name

{ column-name column-type                                     èìÿ è òèï ñòîëáöà

| column-name {BYTE | TEXT}                                    òèïà BYTE | TEXT

[IN {TABLE | blobspace-name}]                         ãäå ñîçäàâàòü

[NOT NULL]                                                       îòñóòñòâèå NULL-çíà÷åíèé

[UNIQUE [(unique col-list)]                                  óíèêàëüíîñòü

[CONSTRAINT constraint-name]                           íàëîæåíî îãðàíè÷åíèå

[, ...])

[WITH NO LOG]                                                         áåç æóðíàëèçàöèè

[IN dbspace-name]                                              ãäå ñîçäàâàòü

[LOCK MODE ({PAGE | ROW})]                        óðîâåíü áëîêèðîâàíèÿ

 

CREATE [UNIQUE][CLUSTER]                            ñîçäàíèå èíäåêñà

INDEX index-name ON table-name                            äëÿ êàêîé òàáëèöû

(column-name [ASC | DESC] [,...])                             ïî êàêîìó ñòîëáöó

è â êàêîì ïîðÿäêå

 

CREATE SYNONYM                                      ñîçäàíèå ñèíîíèìà èìåíè

synonym-name FOR table-name                   óêàçàííîé òàáëèöû

 

CREATE VIEW view-name                                      ñîçäàíèå ïðåäñòàâëåíèÿ

[(column-list)] AS SELECT-statement

 

ALTER TABLE table-name                             èçìåíåíèå ñòðóêòóðû òàáëèöû

{

ADD ( newcol-name                                               åñëè íåîáõîäèìî äîáàâèòü

newcol-type [NOT NULL][UNIQUE                    ñòîëáåö â òàáëèöó

[CONSTRAINT constraint-name]]                          íàëîæåíî îãðàíè÷åíèå

[, ...])

[BEFORE oldcol-name]                                     ïåðåä êàêèì ñòîëáöîì âñòàâëÿòü

| DROP( oldcol-name [, ...] )                                     óäàëèòü ñòîëáåö(öû)

| MODIFY (oldcol-name newcol-type

NOT NULL] [, ...]

| ADD CONSTRAINT UNIQUE ( oldcol-name [, ...] )

[CONSTRAINT constraint-name]

| DROP CONSTRAINT (constraint-name [, ...] )

}

 

CLOSE DATABASE                                      çàêðûòèå òåêóùåé áàçû äàííûõ

 

DATABASE database-name                                      àêòèâèçàöèÿ áàçû äàííûõ

[EXCLUSIVE]                                                 [â ìîíîïîëüíîì ðåæèìå]

 

CONNECT TO database-name                            àêòèâèçàöèÿ áàçû äàííûõ

USER user-name USING parol                            èìÿ ïîëüçîâàòåëÿ è ïàðîëü

 

DROP DATABASE                                                óäàëåíèå áàçû äàííûõ

{database-name | char-variable }             ïî åå èìåíè ëèáî ïî ïåðåìåííîé

 

DROP INDEX index-name                                      óäàëåíèå èíäåêñà

 

DROP TABLE table-name                   óäàëåíèå òàáëèöû èç áàçû äàííûõ

 

DROP SYNONYM                                                óäàëåíèå ñèíîíèìà

 

DROP VIEW view-name                                               óäàëåíèå ïðåäñòàâëåíèÿ

 

RENAME TABLE oldname TO newname             ïåðåèìåíîâàíèå òàáëèöû

 

RENAME COLUMN                                        ïåðåèìåíîâàíèå ñòîëáöà

oldcol-name TO newcol-name

2. Îïåðàòîðû ìàíèïóëÿöèè äàííûìè

DELETE FROM table-name                             óäàëåíèå ñòðîê èç òàáëèöû

[WHERE }]

{condition                                                           ïî óêàçàííîìó óñëîâèþ

 

INSERT                                                              âñòàâêà ñòðîê â òàáëèöó

INTO table-name [(column-list)]                     èìåíà òàáëèö è ñòîëáöîâ

{VALUES( value-list)                                         ñïèñîê çíà÷åíèé ïîëåé

| SELECT-statement}                               âñòàâêà ðåçóëüòàòà âûïîëíåíèÿ

îïåðàòîðà SELECT

 

SELECT                                                             âûáîðêà äàííûõ èç òàáëèö

[ALL | [DISTINCT | UNIQUE]] select-list              ÷òî âûáèðàåòñÿ

[INTO variable-list]                                         êóäà âûáèðàåòñÿ (ESQL/Ñ)

FROM                                                                îòêóäà âûáèðàåòñÿ

{ table-name [table-alias]                                      èç óêàçàííûõ òàáëèö

| OUTER table-name [table-alias]                             äëÿ ñîçäàíèÿ

}[,....]                                                                 âíåøíåãî ñîåäèíåíèÿ

[WHERE condition]                                        óñëîâèå âûáîðà ñòðîê

[GROUP BY column-list]                               ãðóïïèðîâàíèå

[HAVING condition]                                        óñëîâèå â ãðóïïàõ

[ORDER BY column-list [ASC | DESC][,...]]          ñîðòèðîâêà

[INTO TEMP table-name]                                      êóäà ïîìåñòèòü ðåçóëüòàò

 

UPDATE table-name SET                              ìîäèôèêàöèÿ ñòðîêè òàáëèöû

{column-name=expr[, ...] }                            èìåíà ñòîëáöîâ

[WHERE {condition                                        óñëîâèå èçìåíåíèÿ

|CURRENT OF cursor-name} ]                                  èìÿ êóðñîðà

 

LOAD FROM "pathname"                   çàãðóçêà áàçû äàííûõ èç ôàéëà

[DELIMITER "char"]                                         ðàçäåëèòåëü ïîëåé

{INSERT INTO

table-name[(column-name [, ...])] | insert-statement }      èìÿ òàáëèöû è ñòîëáöû

 

UNLOAD                                                           âûãðóçêà áàçû äàííûõ â ôàéëà

SELECT â ASCII-ôàéë

TO "pathname" [DELIMITER "char"]          ïóòü ê ôàéëó è ðàçäåëèòåëü ïîëåé

SELECT-statement                                         îïåðàòîð SELECT

3. Îïåðàòîðû îïðåäåëåíèÿ òðàíçàêöèé

BEGIN WORK                                             îïðåäåëåíèå íà÷àëà òðàíçàêöèè

 

COMMIT WORK                                                       ïîäòâåðæäåíèå òðàíçàêöèè

 

ROLLBACK WORK                                         îòêàò òðàíçàêöèè

4. Îïåðàòîðû îïðåäåëåíèÿ ïðàâ äîñòóïà

GRANT                                                      óñòàíîâêà óðîâíÿ ïðèâèëåãèé ê

òàáëèöå

{ ALL                                                                  âñå ïðèâèëåãèè

| INSERT                                                            ïðèâèëåãèè íà âñòàâêó

| DELETE                                                         ïðèâèëåãèè íà óäàëåíèå

| SELECT                                                           ïðèâèëåãèè íà ïðîñìîòð

| UPDATE                                                ïðèâèëåãèè íà èçìåíåíèå ñòðîê

| REFERENCES                                        ïðèâèëåãèè íà óñòàíîâêó

îãðàíè÷åíèé ïî ññûëêå íà ñòîëáöû

| INDEX                                                    ïðèâèëåãèè íà ïîñòðîåíèå èíäåêñà

| ALTER                                                             ïðèâèëåãèè íà èçìåíåíèå

}                                                                         ñòðóêòóðû òàáëèöû

ON table-name                                                         èìÿ òàáëèöû

TO {PUBLIC | user-list}                                          êîìó ïåðåäàþòñÿ ïðàâà

[WITH GRANT OPTION]                                   c ïðàâîì ïåðåäà÷è ýòèõ ïðàâ

 

GRANT                                   óñòàíîâêà óðîâíÿ ïðèâèëåãèé ê áàçå äàííûõ

{CONNECT                          ïðèâèëåãèè íà çàïðîñû è îáíîâëåíèå äàííûõ

| RESOURCE                ïðèâèëåãèè íà èçìåíåíèå ñòðóêòóðû áàçû äàííûõ

| DBA                                     ïðèâèëåãèè àäìèíèñòðàòîðà, çà èñêëþ÷åíèåì

}                                   âîçìîæíîñòè ìåíÿòü ñèñòåìíóþ òàáëèöó systables

TO {PUBLIC | user-list}                       êîìó ïåðåäàþòñÿ ïðàâà

 

REVOKE                                                   îòìåíà ïðèâèëåãèé íà ïîëüçîâàíèå

òàáëèöåé èëè áàçîé äàííûõ

{table-privilege ON table-name                            èìÿ òàáëèöû

|database-privilege }                                        ïðèâèëåãèÿ íà áàçó äàííûõ

FROM {PUBLIC | user-list}                                 ñïèñîê ïîëüçîâàòåëåé

 

LOCK TABLE table-name                             áëîêèðîâêà òàáëèöû

IN { SHARE                                                         â ðàçäåëÿåìîì ðåæèìå

| EXCLUSIVE}                                                    â ìîíîïîëüíîì ðåæèìå

MODE

 

UNLOCK TABLE table-name                             ñíÿòèå áëîêèðîâêè ñ òàáëèöû

 

SET ISOLATION TO                                          óñòàíîâêà óðîâíÿ èçîëÿöèè

 

{ CURSOR STABILITY                                                ïî ñòàáèëüíîìó êóðñîðó

| DIRTY READ                                                        ãðÿçíîå ÷òåíèå

| COMMITTED READ                                        ïîäòâåðæäåííîå ÷òåíèå

| REPEATABLE READ }                                      ïîâòîðÿåìîå ÷òåíèå

 

SET LOCK MODE TO                                       óñòàíîâêà ðåæèìà äîñòóïà

{ NOT WAIT | WAIT [seconds]}                     æäàòü/ íå æäàòü îñâîáîæäåíèÿ

áëîêèðîâàííîãî ðåñóðñà

5. Âñòðîåííûé SQL

DECLARE cursor-name [SCROLL] CURSOR         îïðåäåëåíèå êóðñîðà

FOR SELECT-statement                                àññîöèèðîâàííûé îïåðàòîð Select

| operator-name                                               èëè äèíàìè÷åñêè ïîäãîòîâëåííûé

                                                                 îïåðàòîð

[INTO host-name]                                                        êóäà âûáèðàòü

 

OPEN cursor-name                                                îòêðûòèå êóðñîðà

[USING host-name]                                       ãëàâíûå ïåðåìåííûå

 

FETCH [parameter-list] cursor-name          âûáîð äàííûõ ïî êóðñîðó

[INTO host-name]                                                        êóäà âûáèðàòü

 

CLOSE cursor-name                                                çàêðûòèå êóðñîðà

 

PREPARE operator-name FROM                                ïîäãîòîâêà äèíàìè÷åñêîãî

char-string                                                       îïåðàòîðà èç ñèìâîëüíîé ñòðîêè

EXECUTE operator-name                         âûïîëíåíèå äèíàìè÷åñêîãî SQL

[USING host-name]                                       ãëàâíûå ïåðåìåííûå

6. Òðèããåðû è ïðîöåäóðû

CREATE TRIGGER trigger-name                            ñîçäàíèå òðèããåðà

{INSERT |DELETE | UPDATE |                    óñëîâèå âêëþ÷åíèÿ òðèããåðà

UPDATE OF column-name} ON table-name           èìÿ òàáëèöû    

[{REFERENCING NEW AS correlation-name} |         èìÿ ïåðåìåííîé ñ óäàëÿåìîé,

 {REFERENCING OLD AS correlation -name} |  âñòàâëÿåìîé, ìîäèôèöèðóåìîé

{REFERENCING NEW AS correlation -name               ñòðîêîé (òîëüêî ñ For each row)

OLD AS correlation -name }

{BEFORE | FOR EACH ROW |AFTER}              ìîìåíò ïðèìåíåíèÿ òðèããåðà

[WHEN (condition)]                                       äîïîëíèòåëüíîå óñëîâèå

{INSERT-statement | DELETE-statement |           SQL-îïåðàòîð èëè õðàíèìàÿ

UPDATE-statement |                                             ïðîöåäóðà, âûïîëíÿåìûå

EXECUTE PROCEDURE procedure-name}                       òðèããåðîì

 

DROP TRIGGER trigger-name                            óäàëåíèå òðèããåðà èç áàçû

äàííûõ

 

CREATE PROCEDURE procedure-name                   ñîçäàíèå ïðîöåäóðû

( [expression[,...]])                                                ñïèñîê àðãóìåíòîâ ïðîöåäóðû

RETURNING type                                                        òèï âîçâðàùàåìîãî çíà÷åíèÿ

[define-stmt-list]                                                  ëîêàëüíûå ïåðåìåííûå

[exception-declaration]                                               êîíñòðóêöèè óñëîâèé

[statement-list]                                                          âûïîëíÿåìûå îïåðàòîðû

END PROCEDURE                                          ïðîöåäóðû

 

CALL procedure-name                            âûçîâ îäíîé ïðîöåäóðû èç äðóãîé

( [expression[,...]])                                       ïàðàìåòðû ïðîöåäóðû

[RETURNING var_name]                              âîçâðàùàåìîå çíà÷åíèå

 

EXECUTE PROCEDURE procedure-name âûçîâ ïðîöåäóðû èç êëèåíòñêîé

                                                                           ïðîãðàììû èëè òðèããåðà

( [expression[,...]])                                       ïàðàìåòðû ïðîöåäóðû

 

DROP PROCEDURE procedure-name                   óäàëåíèå ïðîöåäóðû èç áàçû

äàííûõ