]
, SQL-, . , .
]
SQL .
]
SQL , . , , SQL-. , . , , , .
SQL
]
. , . , SQL . , SQL [8]: (null) =
]
SQL , , .
]
ANSI SQL-92, , (, Oracle, Sybase, Microsoft, MySQL AB), SQL, , . , .
SQL - . SQL - SQL, .
5 SQL- [9]:
1. DDL (Data Definition Language - ) :
* (CREATE), (ALTER), (DROP) ( (TABLE), (VIEW), (INDEX), (SEQUENCE), (TRIGGER) .);
* (GRANT) (REVOKE) (ROLE) ;
* (ANALYZE) , , ;
* (AUDIT) - , ;
* (COMMENT) .
2. DML (Data Manipulation Language - ) (, ):
* (SELECT);
* (INSERT);
|
|
|
* (UPDATE);
* (DELETE);
* (MERGE).
3. Transaction Control ( ) , DML-. DML- ( ) , , , .. , . , , ( ) . :
* (COMMIT) ;
* (ROLLBACK) ;
* (SAVEPOINT).
4. Session Control ( ) :
* (SET ROLE) , ;
* (ALTER SESSION), , .;
5. System Control ( ) (ALTER SYSTEM), , , . , SQL , .. SQL- (DDL, DML, Transaction Control) , (, */++, Pro*COBOL .); Oracle SQL- , .
SQL
1. , , SQL.
2. , , , .
3. , , ( ) .
4. , 3., / .
, \ .
6. , [ ], .
7. () :
;
.
8. , , , .
| 5. |
9. :=
SQL- DML, (SELECT).
:
SELECT {*| [{DISTINCTl AUJ] [, ...]} [INTO ] FROM [, ...] [WHERE [ [and ...]] [{and| OR} ... ]]]/ and OR
|
|
|
[GROUP BY [, ...]] [HAVING ]
[ORDER BY [{ASC_| DESC}] [, [{ASC | DESC}]...]]
ALL , .. ( ) DISTINCT -
:= | |
:= | | | | := [ .] := +1 -1 /1 *
:= ! := , := AVG ( ) I COUNT (*)| COUNT ([{DISTINCTl ALL}] ) | MIN ( ) | ( )] SUM ( ) COUNT (*) / / COUNT (" ") / NOT NULL .
: = [] / - . . : =
:= [{| | }...]
: = 1 2
1 2: = , , ,
: = =| <| >| <=| >=| <>
: = { | [NOT] BETWEEN AND | [NOT] IN { [ }! [NOT] LIKE | IS [NOT] NULlI {ALLl ANYl SOME} [NOT] EXISTS } : = SELECT...
() , .
: = SELECT
: = SELECT / /
: CREATE TABLE ( [] [ ...] [ ] [, [] [ ...] [ ]...] [, ...])
" ": = NOT NULL | UNIQUE | PRIMARY KEY | CHECK ()!
REFERENCES [( )] /, / PRIMERY KEY UNIQUE ,
PRIMERY KEY : = UNIQUE ( )| PRIMARY KEY ( )| / / FOREIGN KEY ( )! REFERENCES [( )] CHECK ()
= / /
: = DEFAULT VALUE =
SQL , (. 3.42).
|
|
|
|
|
|
| . 3.42. SQL |
SQL :
, , SQL.
|
|
|
, , , . ;.
(SELECT) (FROM) :
SELECT FROM ;
, * .
SELECT * FROM ;
, :
SELECT , FROM ;
DISTINCT SELECT , .
, , ( ), DISTINCT:
SELECT DISTINCT FROM ;
, WHERE , .
(=,>,<,<>), (true, false) . ( ). , AND () OR (), . , AND OR.
SELECT FROM _ WHERE ;
, 19- , 30:
SELECT , _, _ FROM
WHERE = ' 19' AND _ > 30;
, SQL , :
* BETWEEN... AND... , AND
* IN (list) -
* LIKE -
* IS NULL - ,
, , , '7':
SELECT * FROM
WHERE _ like '7%';
_ ; % .
, () . . .
:
* LOWER ( )
* UPPER ( )
* INITCAP ( , )
* CONCAT ( - )
* SUBSTR ( )
* LENGTH ( )
* INSTR ( )
* TRIM ( / )
* LPAD/RPAD ( / )
* REPLACE ( )
, , :
SELECT _, FROM
WHERE UPPER(SUBSTR(OHO,1,6)) like ''; :
* MONTH_BETWEEN ( 2 )
* ADD_MONTH ( )
* NEXT_DAY ( , )
* LAST_DAY ( )
* SYSDATE ( )
, , :
SELECT NEXT_DAY(SYSDATE, '') FROM DUAL;
|
|
|
Oracle DUAL, DUMMY () 'X'; , , .. .
. , , SELECT. , .
- , , :
* TRUNC ( ) -
* ROUND ( ) -
, , , 19- :
SELECT , ROUND (_, -1) FROM
WHERE - '19';
ROUND, TRUNC 2- , () , - , (1, 2 ..) () ( ), .. 1 - /-, 2 - /- ..
, ORDER BY, ( SELECT) : (ASC - ) (DESC).
SELECT _ FROM
ORDER BY ()_ ^;
, 8521 (.. ) , (.. ).
SELECT _, , _, _ FROM
WHERE _ - '8521'
ORDER BY , _ DESC;
. GROUP BY, , . , GROUP BY. : COUNT ( ), SUM ( ), AVG ( ), ( ), MIN ( ) (SUM, AVG ). , . SELECT , , , . HAVING, . WHERE.
SELECT __, _(_) FROM
GROUP BY __ HAVING ;
, : ( '8' ), ( 10 ) .
SELECT _, 1;(_), sum (_) FROM
WHERE LIKE '8%' GROUP BY HAVING 1:(_) >10
ORDER BY () _ ^]
:
1) FROM ( );
2) , WHERE , ( , , '8');
3) , GROUP BY ( , '8');
4) , HAVING ( , '8', , 10 );
5) , SELECT ( );
|
|
|
6) , ORDER BY ( SELECT).
, . . 2- . ( .) - , :
- ( ) ( );
, SELECT .
, , , .. . WHERE, : , , (-1) .
:
SELECT 1. 1, 2. 1, 2.2, . 1
FROM 1, 2, 3 WHERE 1. 1 = 2. 1 AND 2.2 = . 1;
, 8521 ( ).
SELECT ._, ._, ._, ., ., ., ._, ._ FROM , _
WHERE ._ = ._ AND ._ = '8521' ORDER BY ._, .__, ._; 2- :
* , . .
* , . , .
, , , . ( + , ) , , , (.. ).
SELECT .*
FROM ,
WHERE . (+) = .
ORDER BY ._, ._, .__, ._;
, , , . ( (+) , ) , , , (.. ).
|
| - |
| 3.43. 3.6. ) |
, . , , ( ), , .. - .
, , :
SELECT . , . FROM , WHERE ._ = . ORDER BY 1;
.
SQL-86, FROM , WHERE - , . ANSI SQL-92 : FROM , JOIN ON , . (+) LEFT/RIGHT , , OUTER [10]:
SELECT 1.1, 2. !, 2.2 FROM 1
[LEFT/RIGHT/OUTER] JOIN 2 ON 1. 1 = - 2. 1
[LEFT/RIGHT/OUTER] JOIN ON 2.2 = . 1 WHERE ;
, , , , , :
] . , WHERE.
] , , ON -
. , . , , 19- 507 ( , ).
SELECT ._, ._, ._, , ., . , . FROM
JOIN ON . = .
WHERE . = ' 19' AND . = '507'
ORDER BY . , . , .;
, ( ), . :
SELECT _
FROM
WHERE
(SELECT FROM WHERE )
, , .
SELECT FROM
WHERE _ - (SELECT _
FROM
WHERE = ' '); - , . - .
: 1 1 . . 1 , .
1 .
- , : =,<,>,<>. , , 1 - .
: , .
SELECT *
FROM
WHERE =
(SELECT (_) FROM );
- , : IN - ;
ANY - , ;
ALL - , .
: , , 8521:
SELECT * FROM
WHERE _ > ALL
(SELECT _ FROM
