Îïåðàòîðû ÿçûêà 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
óäàëåíèå ïðîöåäóðû èç áàçû
äàííûõ