(. 8.1.1 ).
SELECT DML SQL, () , .
SELECT : ( ) ( , , ..).
SELECT :
SELECT
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[FROM table_references]
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC],...]
: , , , . , , .
:
SELECT FROM WHERE
, SELECT:
- WHERE , GROUP BY.
- GROUP BY .
- HAVING , GROUP BY .
- ORDER BY , .
( ) , . :
SELECT *
FROM
WHER = ''
AND > 200;
( ) ( ) . :
SELECT DISTINCT , ,
FROM ;
, , , . :
SELECT , ,
FROM
WHER = ''
UNION SELECT , ,
FROM
WHER = '';
, , . :
SELECT
FROM
WHERE IN (SELECT FROM );
, , . :
SELECT
FROM
WHERE NOT IN (SELECT FROM );
. _ :
SELECT _.*, .*
FROM _, ;
(, , )
(. 8.1.2 ).
(, , , )
|
|
|
(. 8.1.3 ).
CREATE TABLE, CREATE INDEX, CREATE PROCEDURE, CREATE USER, CREATE GROUP, CREATE VIEW, ADD USER, DROP USER, DROP GROUP, ALTER TABLE, ALTER USER, ALTER DATABASE, DROP, GRANT, REVOKE CONSTRAINT.
SQL-92,
() , . (, ), - . , , . , .
SQL , SQL. , , , . .
. . SQL, , . , .
SQL :
- ;
- , .. , ;
- ;
- , ;
- , .. , .
. , , . . . . : ..
. : SELECT, UPDATE, INSERT, DELETE. , , , SQL.
PL/SQL : . , .
, . , , . , .
|
|
|
PL/SQL, .
:
- DECLARE .
- OPEN , .
- FETCH .
- CLOSE .
1. %ISOPEN TRUE, .
2. %FOUND , , .
3. %NOTFOUND TRUE, .
4. %ROWCOUNT .
.
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;
( Oracle) :
DECLARE TYPE CustCursor IS REF CURSOR RETURN Customers %ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE BEGIN
OPEN CustCursor;
LOOP
FETCH CustCursor INTO CustRecord;
EXIT WHEN CustCursor%NOTFOUND;
END LOOP; CLOSE CustCursor; END;
, SQL-, . , , , . ( DDL, DML). , , .
, .
, . .
, , , DDL- ( !) , . - , , , , .
( ). .
, , . , (wrapping) .
, .
SQL-, , .
|
|
|
:
<_>::=
{CREATE | ALTER } [PROCEDURE] _
[;]
[{@_ _ } [VARYING ]
[=default][OUTPUT] ][,...n]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION }]
[FOR REPLICATION]
AS
sql_ [...n]
sp_, #, ##, . , , , , . , , CREATE PROCEDURE . , . . . , , .
, . , , .
, , , @. , . , .
, , SQL, . CURSOR , .. OUTPUT.
OUTPUT , . , . OUTPUT , . , OUTPUT . , .
VARYING OUTPUT, CURSOR. , .
DEFAULT , . , .
, . . RECOMPILE .
FOR REPLICATION .
ENCRYPTION , , .
|
|
|
AS , .. SQL, . SQL, , . RETURN.
:
DROP PROCEDURE {_} [,...n]
:
[[ EXEC [ UTE] _ [;]
[[@_=]{ | @_}
[OUTPUT ]|[DEFAULT ]][,...n]
, EXECUTE . , .
OUTPUT , OUTPUT.
DEFAULT, . , DEFAULT , .
EXECUTE , . , . , . , , . , .
, , . .
: , .
CREATE PROC my_proc3
@k VARCHAR(20)
AS
SELECT .,
.*.
AS , .
FROM INNER JOIN
( INNER JOIN
ON .=.)
ON .=.
WHERE .=@k
:
EXEC my_proc3 '' my_proc3 @k=''
(. trigger) , , () INSERT DELETE , UPDATE . -. , . , , . , .
:
1. INSERT TRIGGER INSERT.
2. UPDATE TRIGGER UPDATE.
3. DELETE TRIGGER DELETE.
BEFORE ( ; , ) AFTER ( ). , , (, , ). , (, , , . .).
, , (VIEW). . BEFORE AFTER , (, ) .
|
|
|
CREATE TRIGGER :
<_>::=
CREATE TRIGGER _
BEFORE | AFTER <_>
ON <_>
[REFERENCING
<____>]
[FOR EACH { ROW | STATEMENT}]
[WHEN(_)]
<_>
DROP TRIGGER {_} [,...n]
(FOR EACH ROW), , (FOR EACH STATEMENT).
<____> , (OLD / NEW), (OLD TABLE / NEW TABLE). , , .
, : inserted deleted. , . inserted deleted , . inserted deleted, . , , inserted deleted :
1. INSERT inserted , ; deleted ; inserted ;
2. DELETE deleted , ; , ; inserted ;
3. UPDATE deleted , . inserted. .
, , @@ROWCOUNT; , . , , . , .
, 100 , , , . , .
, . , , , ROLLBACK TRANSACTION.
: , ( =3). "+", , "-", . .
CREATE TRIGGER _ins
ON FOR INSERT
AS
IF @@ROWCOUNT=1
BEGIN
IF NOT EXISTS(SELECT *
FROM inserted
WHERE -inserted.<=ALL(SELECT
.
FROM ,
WHERE .=
.))
BEGIN
ROLLBACK TRAN
' : '
END
END
14 ACID ,
ACID (Atomicity, Consistency, Isolation, Durability (, , , )), .
Atomicity ():
, . , . , (rollback): , .
Consistency ():
ACID. , . (integrity). , , : (domain integrity), (referential integrity), (entity integrity), .
. , , , , . - , . - , , .
, , . , , , . , , . , , . .
Isolation ():
. , , , , , .
Durability ():
( , ) , , . , , , , - - .
, .
, , , , .
. , . , . . , ACID. .
:
1. , ;
2. , , .
, . , , . , . , , .
:
- , ;
- ;
- ;
- .
, . , INSERT, UPDATE DELETE. , ( CREATE TABLE) : . DROP TABLE .
, , . , , , .
, :
- COMMIT ;
- ROLLBACK ;
- SAVEPOINT .
, , . , , .
COMMIT , . , COMMIT ROLLBACK.
ROLLBACK , . , COMMIT ROLLBACK.
SAVEPOINT ( ) , ( ). :
SAVEPOINT __
, . .
, , ROLLBACK :
ROLLBACK TO __
SAVEPOINT , .
