KIT_BD-Язык SQL
.pdfSQL
SQL 70-
System R. SEQUEL (Structered English Query Language)
! . ", ! # #
$ , , %
$ ;
! !!; ; $!
, #% $$ ; & ;
. ' (
: ! !, # #
.
' % SQL , $ ! & #
SQL, SQL . )
& .
SQL !
. * 1989 !., ! SQL/89 SQL1. ' 1992 ! SQL, SQL/92 SQL2. +
, , & , % .
' 1999 ! , SQL3. , SQL1 SQL2 !
, SQL3 . ' SQL3
, ! , &
# ( . , !!,
! , & .
% .
2.
SQL . + ! , #
, !, C. -
$ # C, ! . '
( - , ! , # ,
% .
# SQL: '. & #, $ # , #
.
SQL $ ,
! . ' $ SQL, ! ,
( % ) - .
' SQL SQL % !, !
( "+ +./ *).
) ! % $$ . +, ,
SQL & SQL. * SQL SQL ("passed off")
!, # .
3.SQL
$ SQL, # ! , . 0 ,
, ! SQL, . " #,
# . + # ANSI ,
& SQL ! # , %
$ ! SQL.
+ SQL # , ! $ . 1 #% ! :
∙+ DDL (Data Definition Language) - ( . ,
# ( ( , , , ) :
o |
CREATE SCHEMA - . |
o |
DROP SHEMA - . |
o |
CREATE TABLE - . |
o |
ALTER TABLE - . |
o |
DROP TABLE - . |
o |
CREATE DOMAIN - . |
o |
ALTER DOMAIN - . |
o |
DROP DOMAIN - . |
o |
CREATE COLLATION - . |
o |
DROP COLLATION - . |
o |
CREATE VIEW - . |
o |
DROP VIEW - . |
∙+ DML (Data Manipulation Language) - ,
# # . o SELECT - .
o INSERT - . o UPDATE - . o DELETE - .
∙+ DCD ( ) , #, &
# : o CREATE ASSERTION - !.
o DROP ASSERTION - !.
o GRANT - ! # # (. o REVOKE - ! .
∙+ :
o COMMIT - $ . o ROLLBACK - .
o SAVEPOINT - # .
" !, ! , $ ,
! SQL, ! SQL.
2 # (DML).
'SQL # #% , $ !
:
∙INTEGER - ( 10 % $ );
∙SMALLINT - " " ( 5 % $ );
∙DECIMAL(p,q) - , #% p $ (0 < p < 16) ; % # q $
(q < p, q = 0, %);
∙FLOAT - % 15 % $ , ;
∙CHAR(n) - $ n (0 < n < 256);
∙VARCHAR(n) - , & #% n (n > 0
, & 4096);
∙DATE - $ , ( # mm/dd/yy); !
, #% . . ! -
. .;
∙TIME - $ , , ( # hh.mm.ss);
∙DATETIME - ;
∙MONEY - ! $ , #% ($, , . .) !
( $$ $ ), ! .
'% % LOGICAL, DOUBLE !. INGRES
# ! , ,
, , - & ( , !
), , !$ , & (
) . .
5.SQL
# ! SQL. SQL !
, % ANSI. " #, ANSI ! & #
, ! # ANSI ! & . ANSI - ! - & ,
, .
6.
" # - # SQL. + ! ,
(. " # # 3/4./'251 "'/1.
SQL , #, !. -
, , , # & , # -
SQL.
", , # , % % # SQL . "
! . * #
# , ! # , # ! . 2,
, - " FROM Salespeope " " WHERE city = "London". / !
& # # . ' &, Salespeople - ! , FROM - #
FROM. / !, " city = "London" "
∙ ! WHERE. + ( - , # . +
# # , ( ), .
" ( [ ] ) , ! , ! ( ... ) ,
& #% # . , ! (<>) -
( #, #.
7.
0 # # , , !
, , # ! SQL. + # SQL.
* , , - #
. 2 ! . + # ,
# , ! #, !
#.
snum |
sname |
city |
comm |
1001 |
Peel |
London |
0.12 |
1002 |
Serres |
San Jose |
0.13 |
1004 |
Motika |
London |
0.11 |
1007 |
Rifkin |
Barcelona |
0.15 |
1003 |
Axelrod |
New York |
0.10 |
num |
name |
city |
rating |
snum |
2001 |
Hoffman |
London |
100 |
1001 |
2002 |
Giovanni |
Rome |
200 |
1003 |
2003 |
Liu |
SanJose |
200 |
1002 |
2004 |
Grass |
Berlin |
300 |
1002 |
2006 |
Clemens |
London |
100 |
1001 |
2008 |
Cisneros |
SanJose |
300 |
1007 |
2007 |
Pereira |
Rome |
100 |
1004 |
onum |
amt |
odate |
cnum |
snum |
3001 |
18.69 |
10/03/1990 |
2008 |
1007 |
3003 |
767.19 |
10/03/1990 |
2001 |
1001 |
3002 |
1900.10 |
10/03/1990 |
2007 |
1004 |
3005 |
5160.45 |
10/03/1990 |
2003 |
1002 |
3006 |
1098.16 |
10/03/1990 |
2008 |
1007 |
3009 |
1713.23 |
10/04/1990 |
2002 |
1003 |
3007 |
75.75 |
10/04/1990 |
2004 |
1002 |
3008 |
4723.00 |
10/05/1990 |
2006 |
1001 |
3010 |
1309.95 |
10/06/1990 |
2004 |
1002 |
3011 |
9891.88 |
10/06/1990 |
2006 |
1001 |
) # - *, 3 *,
.
0 , , ,
. 6 .
0 :
∙ snum - , (" % !").
∙sname - .
∙city - (!).
∙comm - $ .
0 :
∙cnum - , .
∙cname - .
∙city - (!).
∙rating - #% ! ! !.
# & (!).
∙snum - ! ( *).
0 :
∙ onum - , #.
∙amt - .
∙odate - .
∙cnum - #% ! ( 3).
∙snum - #% ! ( *).
8.
3 - , # & ! , % ,
# $# . ) $
# , , , &
, , $ ( ( #),
# $# ! .
3 # DML. + $#
, #, #
! # DML, , #
.
' SQL . ,
& .
) - SELECT ('5 +6).
9.SELECT
'$ , SELECT , $#
. 2, ! *, #%:
SELECT snum, sname, sity, comm FROM Salespeople
snum |
sname |
city |
comm |
1001 |
Peel |
London |
0.12 |
1002 |
Serres |
San Jose |
0.13 |
1004 |
Motika |
London |
0.11 |
1007 |
Rifkin |
Barcelona |
0.15 |
1003 |
Axelrod |
New York |
0.10 |
! , . & !
! &, # $ ,
$ .
( :
SELECT. " # , % , - . '
# , .
Snum, sname - , # . .# ,
, # . ), , ,
$ , $# ;
.
FROM. " # , SELECT, . +
$ . '
- *(Salespeople).
"; ". 0 SQL, % ,
! . ' (\) ,
.
,, ! #
. 0 , , ,
. +, # ,
. ) , . '
SQL : % # ! .
, , %,
. 3 (*) ! #% :
SELECT * FROM Salespeople;
) , & % .
' % , SELECT # ! SELECT, ! . * ! , , . ,
, (*). " #
FROM #% , , . '
#, ( ; ) ,
! #.
10. !
DISTINCT (+0.-,) - ! , '
& ! SELECT.
*, % #
*. * |
*, ! #% # |
|
! # |
. ' , ;
(snum). * ' :
SELECT snum FROM Orders
Snum
1007
1001
1004
1002
1007
1003
1002
1001
1002
1001
, , #%:
SELECT snum FROM Orders
Snum
1001
1002
1003
1004
1007
! , DISTINCT , ,
. ) - ,
, . , , !
DISTINCT, #- - . 2, ! , & . , - %
! Clemens 3, SELECT DISTINCT cname,
% . ' ! Clemens . 0
, DISTINCT.
DISTINCT SELECT. ,
! , DISTINCT , ! . ,
, , . DISTINCT, $ ,
, , ! , ! . ' DISTINCT, - ALL. ) $$ -
. 0 - , ! DISTINCT ALL, ALL - % , #% ! .
11. " WHERE
0 # # &, , &
& . *
# , SQL ,
.
WHERE - SELECT, ,
# . "
, .
2, , .. '
# :
SELECT sname, city FROM Salespeople WHERE city = "LONDON"
sname |
city |
Peel |
London |
Motika |
London |
" ! WHERE , ! #
# . , Peel,
! % city, , "London", #
. 3 Serres #, .
12. #
6 - ,
. ' , # , 2 + 3 = 5 city = "London". 2 # ! . *, *
& ! . ' "& " - (>). 6 , ! SQL:
=6
> &
<1 &
>= &
<= 1 &
< > 2
) # .
, $ , . SQL
$ .
, ! "1", , ,
. ' , $
- , "a" < "n", ! "a" $ - !
% # $ .
3, , # .
; 1 + 2 - , 3.
, , #
$ , +(#) *( ).
* # ,
SQL, , . *,
(rating) & 200. 0 200 - ,
, .
SELECT * FROM Customers WHERE rating > 200
snum |
cname |
city |
rating |
2004 |
Crass |
Berlin |
300 |
2008 |
Cirneros |
San Jose |
239 |
13. $
+ # SQL. ' - #
, . SQL #:
∙AND ! (A AND B) & # , .
∙OR ! (A OR B) , .
∙NOT ! (NOT A) ! !
.
, . *, San Jose, # ! & 200:
SELECT * FROM Customers WHERE city = " San Jose' AND rating > 200
snum |
cname |
city |
rating |
2008 |
Cirneros |
San Jose |
239 |
|
|
|
|
, OR , San Jose
& 200:
SELECT * FROM Customers WHERE city = " San Jose' OR rating > 200
snum |
cname |
city |
rating |
2003 |
Liu |
San Jose |
200 |
2004 |
Crass |
Berlin |
300 |
2008 |
Cirneros |
San Jose |
239 |
NOT . NOT:
SELECT * FROM Customers WHERE city = " San Jose' OR NOT rating > 200;
+ NOT & , ,
% . 2, :
rating NOT > 200
, !
! , ( , ( !
.
14. % IN
+ IN , #. 3:
SELECT * FROM Salespeople WHERE city = 'Barcelona' OR city = 'London'
1 $ :
SELECT * FROM Salespeople WHERE city IN ( 'Barcelona', 'London' )
IN % # # !
. + ! ,
. , , . " ! ,
, #.
% #% snum = 1001, 1007, 1004.
SELECT * FROM Customers WHERE cnum IN ( 1001, 1007, 1004 )
snum |
cname |
city |
rating |
cnum |
2001 |
Hoffman |
London |
100 |
1001 |
2006 |
Clemens |
London |
100 |
1001 |
2008 |
Cisneros |
San Jose |
300 |
1007 |
2007 |
Pereira |
Rome |
100 |
1004 |
15. % BETWEEN
+ BETWEEN IN. ' ,
IN, BETWEEN , ! &,
. ' # BETWEEN , # AND
. ' IN, BETWEEN ,
$ .
#% * .10
.12:
SELECT * FROM Salespeople WHERE comm BETWEEN .10 AND .12
# ! BETWEEN, , #% # !,
.
SQL # BETWEEN. ' & ! , # #% , -
!:
16. % LIKE
LIKE CHAR VARCHAR, ,
. 0 % , , ! . '
! (wildkards) - !
- .
# ! LIKE:
∙( _ ) % # . 2, 'b_t'
'bat' 'bit', 'brat'.
∙(%) % # ! (#
). 2 '%p%t' 'put', 'posit', 'opt', 'spite'.
SELECT * FROM Customers WHERE cname LIKE 'G%'
snum |
cname |
city |
rating |
2002 |
Giovanni |
Rome |
200 |
2004 |
Crass |
Berlin |
300 |
* ! , # G.
17. #
- , , # ! , ,
$ &, . SQL
, NULL (* 0+7) , . " !
NULL, , ! #%
! ( ). ) #,
, # ! . 0 ,
NULL , . + % #
. 0 , NULL SQL .
0 NULL , # !
NULL. " ! NULL # , !
NULL, , , - .
- - , %
# # NULL . *
, SQL IS, # NULL,
% NULL.
2 & 3 NULL city :
SELECT * FROM Customers WHERE city IS NULL
! & NOT. +
, NOT - . 2,
NULL & ! , NOT,
:
SELECT * FROM Customers WHERE city NOT NULL
18. & '
3 ! % ! !
. ) % # ! ! $ . /! ! $
! . $ :
∙COUNT - NULL , .
∙ SUM - $ # ! .
∙AVG - ! .
∙MAX - & ! .
∙MIN - & ! .
/! ! $ # SELECT ,
#, ! . 0 ! SUM AVG. COUNT, MAX, MIN, ! . " !
# , MAX MIN ASCII,
%, MIN , MAX $ . - SUM * #% :
SELECT SUM (amt) FROM Orders
26658.4
), , , % ,
!, . - !, ! ! $ !
, GROUP BY.
8 COUNT . + ,
. " ! , DISTINCT
.
19. " GROUP BY
* GROUP BY
! ! , $ # ! ! . ) (
! ! $ SELECT. 2, ,
& # , # . '
! , MAX (amt) * ! snum. GROUP BY,
, ' :
SELECT snum, MAX (amt) FROM Orders GROUP BY snum
snum |
|
1001 |
767.19 |
1002 |
1713.23 |
1003 |
75.75 |
1014 |
1309.95 |
1007 |
1098.16 |
GROUP BY ! ! $ ! , # % #
. ' , !
snum, MAX $ ! . ) ,
GROUP BY, , #, ! ,
! ! $ . 6 , ! !
( . ' GROUP BY ! .
& & , , & #
, # . - , !
* , $ # MAX ! , :
SELECT snum, odate, MAX ((amt)) FROM Orders GROUP BY snum, odate;
20. " ORDER BY
0 - , ,
# - . SQL ORDER BY,
. ) !
. 1 ! # ! !,
GROUP BY, (ASC) (DESC) ! . * # - .
6 , # % # :
SELECT * FROM Orders ORDER BY cnum DESC
Onum |
amt |
odate |
cnum |
snum |
001 |
18.69 |
10/03/1990 |
2008 |
1007 |
006 |
1098.16 |
10/03/1990 |
2008 |
1007 |
002 |
1900.10 |
10/03/1990 |
2007 |
1004 |
008 |
4723.00 |
10/05/1990 |
2006 |
1001 |
011 |
9891.88 |
10/06/1990 |
2006 |
1001 |
007 |
75.75 |
10/04/1990 |
2004 |
1002 |
010 |
1309.95 |
10/06/1990 |
2004 |
1002 |
005 |
5160.45 |
10/03/1990 |
2003 |
1002 |
009 |
1713.23 |
10/04/1990 |
2002 |
1003 |
003 |
767.19 |
10/03/1990 |
2001 |
1001 |
1 % # ! ! , % # amt,
cnum:
SELECT * FROM Orders ORDER BY cnum DESC
onum |
amt |
odate |
cnum |
snum |
3006 |
1098.16 |
10/03/1990 |
2008 |
1007 |
3001 |
18.69 |
10/03/1990 |
2008 |
1007 |
3002 |
1900.10 |
10/03/1990 |
2007 |
1004 |
3011 |
9891.88 |
10/06/1990 |
2006 |
1001 |
3008 |
4723.00 |
10/05/1990 |
2006 |
1001 |
3010 |
1309.95 |
10/06/1990 |
2004 |
1002 |
3007 |
75.75 |
10/04/1990 |
2004 |
1002 |
3005 |
5160.45 |
10/03/1990 |
2003 |
1002 |
3009 |
1713.23 |
10/04/1990 |
2002 |
1003 |
3003 |
767.19 |
10/03/1990 |
2001 |
1001 |
|
|
|
|
|
' ORDER BY # . +
, , # SELECT. ) - ANSI, &, !, . #% ,
, %:
SELECT cname, city FROM Customers GROUP BY cnum
' , , !
. ) ! ,
. ! , , SELECT , ORDER BY - 1,
! . 2, #% #
, *,
& # :
SELECT sname, comm FROM Salespeople GROUP BY 2 DESC;
21.
+ SQL -
! $# , #
. ) - (,
. (, $# #
, $! . * (, FROM , #
. * # # ,
, . +,
, , WHERE #. * $ , !
. # :
∙Salespeople.snum
∙Salespeople.city
∙Orders.odate
!, , & , SQL
, #% $ , . *, !,
. 2 ! 3 ! !
!. ) , #% # :
SELECT Customers.cname, Salespeople.sname, Salespeople.city FROM Salespeople, Customers WHERE Salespeople.city = Customers.city
cname |
sname |
city |
Hoffman |
Peel |
London |
Hoffman |
Peel |
London |
Liu |
Serres |
San Jose |
Cisneros |
Serres |
San Jose |
Hoffman |
Motika |
London |
Clemens |
Motika |
London |
- SQL ( - # #
, . ' % ,
Peel * ( *,