, . , , - - .
.
. . , .
, . , - . , , BETWEEN, <, >, GROUP BY, ORDER BY, MAX, MIN, COUNT.
, ( ) . , .
, , , . , , "/", "/", .
, GROUP BY JOIN.
, , , - .
, , , , (INSERT). , , " " -. , .
, , .
10, 11, 12, 13, 14
SQL
, , . , , SQL, QBE (Query-by-Example, ) (Query Language). SQL QBE IBM 70- . SQL , QBE . QUEL INGRES, , 70- .
|
|
|
1986 ANSI SQL. () 1989 () 1992. SQL ANSI .
SQL , , , , .
, SQL . (.. ), ( ), , . SQL . SQL , SQL- , .
SQL , . , SQL .
SQL :
(, , , , )
(, , , )
(, , , , )
SQL-92 :
:
Integer -
Small integer
Numeric (p,s) -
Decimal (p,s)
, s
:
Real
Double precision
Float
:
Char
VarChar
:
Bit (n)
Varbit (n)
.
-:
Date
Time
Timestamp
Time with time zone
Timestamp with time zone ,
Interval ()
Year-month
Day-Time
.
.
, . , . . , .
CREATE DOMAIN NUMERIC(4) DEFAULT 0 CHECK (VALUE IS NOT NULL).
:
1.
2.
3. .
:
CREATE TABLE < >
(< > [{,< >}...]),
< ->::=
{< > |
| < > |
| < > |
|
|
|
| < > |
| < >}
. CREATE TABLE , .. .
:
<- >::=
< > < -> [<>]
[DEFAULT { <> | USER | NULL }]
[< - >...]
< - >::=
NOT NULL [UNIQUE | PRIMARY KEY]
| <- >
| CHECK (<->)
, , , . , ; USER, , ( ); NULL, , .
:. REFERENCES < > [(< >)]
.
:
< >::=
< {UNIQUE | PRIMARY KEY} (< >)
< >::=
FOREIGN KEY (< >) REFERENCES < > [(< >)]
< >::=
CHECK (<->)
, , . , SQL.
T , .
, , NOT NULL.
CREATE TABLE
( PRIMARY KEY,
char (12) NOT NULL,
numeric (5,2),
char (12),.
numeric(4),
FOREIGN KEY REFERENCES ON DELETE SET NULL;
CREATE TABLE
( PRIMARY KEY,
char (20),.
char (10) DEFAULT
CHECK ( IN ,,, ),.
numeric(2),
numeric(1) DEFAULT 1 CHECK (>0 AND <4)
CREATE TABLE
( ,
,
date,
interval date(3),
PRIMARY KEY (, ),
FOREIGN KEY REFERENCES ON DELETE CASCADE;
FOREIGN KEY REFERENCES ON DELETE CASCADE);
. ,
, . , , .
, , PRIMARY KEY UNIQUE. , , .
, , . , "" , .
|
|
|
, INSERT UPDATE , . , . . (NULL) , NULL , NOT NULL. . (DELETE) , .
ANSI , , . , , , .
ON DELETE CASCADE , , , , .
ON DELETE ( ) ON UPDATE ( ) :
RESTRICT -,
CASCADE - -
SET NULL -
SET DEFAULT - , .
DROP TABLE .
ALTER < >
ADD/DROP (< > < >
[,< > < >...]);
. , , .
ALTER TABLE DROP FOREIGH KEY
ALTER TABLE DROP PRIMARY KEY ADD PRIMARY KEY ()
, , .
, , . , .
| 1, 3 | |
.
, . , . , . . , , . . , . .
CREATE INDEX ON ( [{ASC | DESC}] [, [{ASC | DESC}]])
DROP INDEX .
INSERT, UPDATE DELETE, , , , .
INSERT , .
|
|
|
INSERT INTO [()] {VALUES () |
SELECT}
, . , , NULL. NULL , NULL.
INSERT INTO VALUES(1001, ,5.4,,1200)
DELETE
DELETE FROM [WHERE ]
WHERE, . .
.
DELETE FROM WHERE =
UPDATE
UPDATE SET {= [,=]}
[WHERE ]
UPDATE SET =6.0 WHERE =
, .
. ?
select from where =
select , . , . , .
FROM , . SELECT , FROM. SQL92 , , SQL. FROM.
WHERE , .
: FROM, WHERE, SELECT.
, DISTINCT.
.
SELECT DISTINCT FROM
. .
SELECT * FROM WHERE =
.
. ?
SELECT , =, 40*
FROM
WHERE =
ORDER BY
SELECT.
ORDER BY - , DESC. , , .
, , . ORDER BY SELECT.
. 3 5 ?
SELECT * FROM WHERE >=3 and <=5
6 =, <, >, <>, >=, <=. AND, OR NOT. .
BETWEEN.
SELECT * FROM WHERE BETWEEN 3 and 5
BETWEEN .
. , .
SELECT * FROM
WHERE IN ('','','')
IN , .
-. , , .
. , .
SELECT * FROM WHERE LIKE (%')
SQL : % _. . , .
.
|
|
|
SELECT * FROM WHERE LIKE ( ____')
4 .
. ,
SELECT * FROM WHERE LIKE (%')
LIKE ESCAPE, x, "x_" "x%" "_" "%", .
SELECT *
FROM
WHERE LIKE %/_% ESCAPE /;
, _.
NULL true false. "x IS NULL" true , x . "x NOT IS NULL" "NOT x IS NULL".
. , 345?
, ,
SELECT FROM ,
WHERE .=. and =345
FROM. , . WHERE. . , . , .
. , .
SELECT FROM , ,
WHERE .=. and .=. and =''
.
. , .
SELECT ., . FROM ,
WHERE .=.
FROM , . .
.
. , 345?
SELECT FROM
WHERE IN
(SELECT FROM
WHERE =345)
SELECT . WHERE .
, , . , .
, .
.
.. , .
SELECT FROM
WHERE IN
(SELECT FROM
WHERE IN
(SELECT FROM
WHERE ='))
, .
.
, , .
. , , .
SELECT FROM A
WHERE A. >
(SELECT . FROM
WHERE .=.)
:
1. . , .
2. . , WHERE. , , , , .
3. , , . . . , . , . , .
EXISTS
, .
SELECT FROM WHERE <>345
, .
:
SELECT FROM
WHERE NOT EXISTS
(SELECT * FROM
WHERE .=. AND
<>345)
EXISTS (NOT EXISTS) . EXISTS true false, true , .
. , IN
SELECT FROM
WHERE NOT IN
(SELECT FROM
WHERE .=. AND
<>345)
, EXISTS, . , . , .
, .
SQL . : , , .
SELECT FROM
WHERE NOT EXISTS
(SELECT FROM
WHERE NOT EXISTS
(SELECT * FROM
WHERE .=. AND
.=.
<> < > <> <>
- ALL SOME(ANY).
x , S - .
"x < > ALL S" true, S "x < > s" true s, S. "x < > ALL S" false, "x < > s" false s, S.
"x < > SOME S" false, S "x < > s" false s, S. "x < > SOME S" true, "x < > s" true s, S.
. , :
SELECT FROM
WHERE >= ALL
(SELECT FROM
WHERE .=.)
SQL 5 , . MAX, MIN, AVG, SUM, COUNT.
.
SELECT MAX(), MIN() FROM
COUNT . .
. ?
SELECT COUNT(*)FROM WHERE =
, , DISTINCT.
. ?
SELECT COUNT(DISTINCT ) FROM
AVG SUM . , COUNT, .
.
SELECT , MAX() FROM
GROUP BY
. , . SELECT . , . SELECT , ().
SELECT , GROUP BY.
, AVG(MAX()) . . , .
GROUP BY WHERE, GROUP BY. , GROUP BY. HAVING.
. , , .
SELECT , MAX() FROM
GROUP BY
HAVING COUNT(*)>1
WHERE HAVING , WHERE , HAVING .
WHERE, HAVING. WHERE .
SELECT HAVING. SELECT c .
. ?
SELECT FROM
WHERE >
(SELECT AVG() FROM )
.
. ?
SELECT . FROM
WHERE .>
(SELECT AVG(.) FROM
WHERE .=.)
.
, , . SQL , .
. 1 - , , 2 , , . .
SELECT , FROM T1
UNION
SELECT , FROM T2
, . , UNION ALL.
. .
SELECT FROM T1
INTERSECT
SELECT FROM T2
. ALL.
. , .
SELECT FROM T1
EXCEPT
SELECT FROM T2
:
. , 10.05.00
SELECT
FROM JOIN ON .=.
WHERE =10.05.00
. , .
SELECT
FROM LEFT JOIN ON .=.
WHERE . IS NULL
. , .
. . .
CREATE VIEW [( )]
AS SELECT
[WITH CHECK OPTION]
, SELECT.
.
CREATE VIEW
AS SELECT , , FROM .
, . SELECT? , .
.
CREATE VIEW
AS SELECT , ,
FROM JOIN ON .=.
WHERE =
.
. 345?
SELECT FROM WHERE =345.
.
.
CREATE VIEW (, )
AS SELECT, ()
FROM
GROUP BY
, .
