Bazy_dannykh_Uchebnoe_posobie
.pdf
|
|
30. |
. |
SELECT |
|
SELECT DISTINCT |
|
FROM |
; |
FROM |
; |
------- |
|
------- |
|
1007 |
|
1001 |
|
1001 |
|
1002 |
|
1004 |
|
1003 |
|
1002 |
|
1004 |
|
1007 |
|
1007 |
|
1003 |
|
|
|
1002 |
|
|
|
1001 |
|
|
|
1002 |
|
|
|
1001 |
|
|
|
|
DISTINCT |
ALL |
|
Distinct. |
. |
WHERE ( |
) |
|
WHERE |
SELECT, |
|
. |
« |
» |
. |
|
|
25: |
|
|
, |
= 100, ( |
) |
WHERE |
= 100 |
|
|
31. |
. |
SELECT * |
|
|
FROM |
|
|
WHERE |
= 100; |
|
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
2001 |
|
100 |
1001 |
2006 |
|
100 |
1001 |
2007 |
. |
100 |
1004 |
|
WHERE |
, |
SQL: |
= |
|
> |
|
< |
|
>= |
|
<= |
|
81
<>
rating = 100 rating >= 200
WHERE
AND |
( |
A AND B) |
|
|
|
, |
|
. |
|
OR |
( |
A OR B) |
|
|
, |
|
. |
|
|
NOT |
|
( |
NOT A) |
|
|
|
|
|
. |
26: |
|
|
|
|
, |
|
|
|
, |
( |
) |
200. |
|
|
|
|
|
32. |
. |
SELECT * |
|
|
|
|
FROM |
|
|
|
|
WHERE |
= ' |
|
' |
|
AND |
> 200; |
|
|
|
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
2003 |
200 |
1002 |
2008 |
300 |
1007 |
27:
SELECT * |
|
|
|
|
FROM |
|
|
|
|
WHERE |
= ' |
' |
|
|
OR city = ' |
|
'; |
|
|
28: |
|
|
|
|
|
33. |
|
|
. |
SELECT * |
|
|
|
|
FROM |
|
|
|
|
WHERE |
= ' |
' OR NOT |
> 200; |
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
2001 |
|
100 |
1001 |
2002 |
. |
200 |
1003 |
2003 |
|
200 |
1002 |
2004 |
|
300 |
1002 |
2006 |
|
100 |
1001 |
82
2007 |
. |
100 |
1004 |
SELECT *
FROM
WHERE NOT = '' OR > 200;
, , ..
.
SELECT *
FROM
WHERE NOT ( = '' OR > 200);
IN ()
IN ,
.
SELECT *
FROM |
|
|
|
|
WHERE |
In (' |
', ' |
'); |
|
|
|
|
34. |
. |
SELECT * |
|
|
|
|
FROM |
|
|
|
|
WHERE |
IN (1001, 1007, 1004); |
|
----------------------------------------------------------------------------------------------------------
-----------
----------------------------------------------------------------------------------------------------------
----------- |
|
|
|
2001 |
|
100 |
1001 |
2008 |
|
300 |
1007 |
2007 |
. |
100 |
1004 |
BETWEEN ( |
) |
|
|
BETWEEN |
|
IN. |
|
, |
IN, |
BETWEEN |
, |
|
, |
|
. |
BETWEEN ,
AND . IN, BETWEEN
83
,
.
|
35. |
SELECT * |
|
FROM |
|
WHERE |
BETWEEN .10 AND .12; |
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
1001 |
|
|
0.12 |
|
1004 |
|
|
0.11 |
|
1003 |
|
|
0.10 |
|
LIKE ( |
) |
|
|
|
LIKE |
|
CHAR |
VARCHAR, |
|
, |
|
. |
, |
, |
, |
|
|
. |
|
|
|
(wildcards) |
- |
|
|
- |
. |
|
|
|
|
LIKE: |
|
_ . , 'b_t' 'bat' 'bit', 'brat'.
(%)
(). '%p%t' 'put', 'posit', 'opt', 'spite'.
29:
, «».
|
36. |
SELECT |
|
FROM |
|
WHERE |
LIKE ' %'; |
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
2002 . 200 1003
30:
84
, , «». .
|
38. |
SELECT * |
|
FROM |
|
WHERE |
LIKE '% '; |
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
2001 |
100 |
1001 |
2003 |
200 |
1002 |
2004 |
300 |
1002 |
2008 |
300 |
1007 |
|
|
|
|
, |
«.. |
». |
, |
|
|
|
? |
LIKE |
|
|
ESC. |
ESC |
|
|
|
|
, |
, |
|
|
|
, |
|
. |
ESCAPE |
'/' |
ESC. |
ESC |
LIKE |
, |
, |
|
, |
ESCAPE, |
|
, |
|
|
. |
ESC |
|
|
|
|
. |
, |
|
|
, |
, |
: |
|
|
|
|
|
|
|
39. |
|
SELECT * |
|
|
|
|
FROM |
|
|
|
|
WHERE |
LIKE '%/_%'ESCAPE'/'; |
|
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
|
|
, . |
, |
, |
. |
|
|
|
NULL ( |
) |
|
NULL |
|
, |
, |
NULL. NULL
85
, NULL, , ,
. , ' = NULL' ' IN (NULL)'
, .
IS ()
NULL «»:
SELECT *
FROM
WHERE IS NULL;
, «», , ,
NOT IS NULL
IS NOT NULL
.
:
COUNT NULL ,
.
SUM .
AVG .
MAX .
MIN .
COUNT, MAX, MIN, .
31:
«»
|
40. |
SELECT SUM ( |
) |
FROM |
; |
----------------------------------------------------------------------------------------------------------
SUM ()
----------------------------------------------------------------------------------------------------------
26658.4
, , , , , .
() .
86
COUNT
COUNT .
, .
, DISTINCT,
.
32:
|
|
. |
|
|
41. |
SELECT COUNT (DISTINCT |
) |
|
FROM |
; |
|
----------------------------------------------------------------------------------------------------------
COUNT (DISTINCT )
----------------------------------------------------------------------------------------------------------
5
, DISTINCT, , , SELECT,
.
33:
, COUNT
|
42. |
SELECT COUNT (*) |
|
FROM |
; |
----------------------------------------------------------------------------------------------------------
COUNT (*)
----------------------------------------------------------------------------------------------------------
7
GROUP BY ()
GROUP BY
,
.
SELECT. GROUP BY , . , «», MAX
. , GROUP
87
BY, |
, |
, |
|
|
, |
|
|
|
. |
|
|
, |
|
|
|
|
. |
|
|
GROUP BY |
|
|
. |
|
|
|
|
|
34: |
|
|
|
|
|
|
|
, |
|
|
, |
|
|
|
|
. |
|
|
|
|
|
|
|
|
43. |
|
|
|
SELECT |
, MAX ( |
) |
|
|
|
|
FROM |
|
|
|
|
|
|
GROUP BY |
; |
|
|
|
|
---------------------------------------------------------------------------------------------------------- |
|||||
|
|
|
|
MAX( |
) |
|
|
---------------------------------------------------------------------------------------------------------- |
|||||
|
2001 |
|
|
767.19 |
|
|
|
2002 |
|
|
1713.23 |
|
|
|
2004 |
|
|
1309.95 |
|
|
|
2008 |
|
|
1098.16 |
|
|
|
35: |
|
|
|
|
|
|
|
, |
|
|
, |
|
|
|
|
|
|
44. |
|
|
|
SELECT |
, |
, MAX ( |
) |
|
|
|
FROM |
|
|
|
|
|
|
GROUP BY |
, |
; |
|
|
|
---------------------------------------------------------------------------------------------------------- |
|||||
|
|
|
|
|
MAX ( |
) |
|
---------------------------------------------------------------------------------------------------------- |
|||||
|
2001 |
|
10/03/1990 |
|
767.19 |
|
|
2006 |
|
10/05/1990 |
|
4723.00 |
|
|
2006 |
|
10/06/1990 |
|
9891.88 |
|
|
2003 |
|
10/03/1990 |
|
5160.45 |
|
|
2004 |
|
10/04/1990 |
|
75.75 |
|
|
2004 |
|
10/06/1990 |
|
1309.95 |
|
|
2002 |
|
10/04/1990 |
|
1713.23 |
|
|
2007 |
|
10/03/1990 |
|
1900.10 |
|
|
2008 |
|
10/03/1990 |
|
1098.16 |
|
HAVING ()
HAVING |
WHERE |
GROUP BY. HAVING ,
,
88
WHERE |
|
|
. |
HAVING |
|
|
, |
SELECT, |
|
|
GROUP BY. |
|
|
. |
|
HAVING |
|
|
IN, BETWEEN. |
36: |
|
|
|
|
|
|
$3000.00, |
|
|
|
45. |
SELECT |
, |
, MAX ( |
) |
FROM |
|
|
|
GROUP BY |
|
, |
|
HAVING MAX ( |
|
) > 3000.00; |
|
---------------------------------------------------------------------------------------------------------
MAX ()
---------------------------------------------------------------------------------------------------------
2006 |
10/05/1990 |
4723.00 |
2006 |
10/06/1990 |
9891.88 |
2003 |
10/03/1990 |
5160.45 |
37: |
|
, |
, |
10.03.1988. |
|
SELECT , MAX ()
FROM
GROUP BY
HAVING = 10/03/1988;
- |
odate |
HAVING, . . |
, |
|
. |
|
|
46. |
SELECT |
, MAX ( |
) |
FROM |
|
|
WHERE |
= 10/03/1990 |
|
GROUP BY |
; |
|
----------------------------------------------------------------------------------------------------------
MAX ()
----------------------------------------------------------------------------------------------------------
2001 |
767.19 |
2003 |
5160.45 |
2007 |
1900.10 |
2008 |
1098.16 |
ORDER BY ( |
) |
89
- , ,
, |
- |
. SQL ORDER BY,
|
|
. |
|
|
|
|
|
|
. |
|
|
|
, |
GROUP |
BY, |
|
(ASC) |
|
(DESC) |
. |
|
|
|
|
38: |
|
|
|
|
, |
|
|
|
, |
|
|
. |
|
|
. |
|
|
|
|
SELECT * |
|
|
|
|
FROM |
|
|
|
|
ORDER BY |
; |
|
|
|
|
|
|
ASC. |
|
|
|
, |
|
, |
|
|
|
. |
|
39: |
|
|
|
|
, |
|
|
|
, |
|
|
|
. |
|
|
. |
|
|
|
SELECT * |
|
|
|
|
FROM |
|
|
|
|
ORDER BY |
|
DESC, |
DESC; |
|
|
|
|
47. |
|
SELECT * |
|
|
|
|
FROM |
|
|
|
|
ORDER BY |
DESC, |
DESC; |
|
|
---------------------------------------------------------------------------------------------------------- |
||||
---------------------------------------------------------------------------------------------------------- |
||||
2008 |
|
1098.16 |
|
|
2008 |
|
18.69 |
|
|
2007 |
|
1900.10 |
|
|
2002 |
|
1713.23 |
|
|
2003 |
|
5160.45 |
|
|
2004 |
|
1309.95 |
|
|
2004 |
|
75.75 |
|
90