БАЗЫ ДАННЫХ
ПРЕДЛОЖЕНИЯ SQL ВЫБОРКА - SELECT
ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECT’а
ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECT’а
ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECT’а
ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ
ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ
ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ
ПРЕДЛОЖЕНИЯ SQL SELECT без повторов
ПРЕДЛОЖЕНИЯ SQL SELECT без повторов
ПРЕДЛОЖЕНИЯ SQL SELECT с условием
ПРЕДЛОЖЕНИЯ SQL SELECT с условием
ПРЕДЛОЖЕНИЯ SQL SELECT с условием
ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE
ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE
ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE
ПРЕДЛОЖЕНИЯ SQL BETWEEN
ПРЕДЛОЖЕНИЯ SQL BETWEEN
ПРЕДЛОЖЕНИЯ SQL BETWEEN
ПРЕДЛОЖЕНИЯ SQL ORDER BY
ПРЕДЛОЖЕНИЯ SQL ORDER BY
ПРЕДЛОЖЕНИЯ SQL ORDER BY
ПРЕДЛОЖЕНИЯ SQL ORDER BY
ПРЕДЛОЖЕНИЯ SQL ORDER BY
ПРЕДЛОЖЕНИЯ SQL AND & OR
ПРЕДЛОЖЕНИЯ SQL AND & OR
ПРЕДЛОЖЕНИЯ SQL AND & OR
ПРЕДЛОЖЕНИЯ SQL AND & OR
ПРЕДЛОЖЕНИЯ SQL ФУНКЦИИ
ПРЕДЛОЖЕНИЯ SQL AVG
ПРЕДЛОЖЕНИЯ SQL COUNT
ПРЕДЛОЖЕНИЯ SQL COUNT
ПРЕДЛОЖЕНИЯ SQL MAX
ПРЕДЛОЖЕНИЯ SQL MIN
ПРЕДЛОЖЕНИЯ SQL SUM
Некоторые функции MS Access
Некоторые функции MS Access
Некоторые функции MS SQL Server
ПРЕДЛОЖЕНИЯ SQL SELECT IN
Примеры оператора SQL LIKE
SELECT * FROM Universities WHERE Site LIKE '_ _ _ _.ru
SELECT * FROM Universities WHERE Site LIKE '[k-o]%'
SELECT * FROM Universities WHERE Location LIKE '_[^e-s]%'

Базы данных. Великий и ужасный select

1. БАЗЫ ДАННЫХ

1

2. ПРЕДЛОЖЕНИЯ SQL ВЫБОРКА - SELECT

SELECT [предикат] { * | таблица.* | [таблица.]поле_1
[AS псевдоним_1] [, [таблица.]поле_2 [AS псевдоним_2] [, ...]]}
FROM выражение [, ...] [IN внешняяБазаДанных]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]
2

3. ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECT’а

SELECT column_name FROM table_name;
3

4. ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECT’а

Persons
LastName FirstName
Hansen
Svendson
Pettersen
Ola
Tove
Kari
Address
City
Timoteivn 10 Sandnes
Borgvn 23
Sandnes
Storgt 20
Stavanger
SELECT LastName, FirstName FROM Persons;
LastName FirstName
Hansen
Ola
Svendson
Pettersen
Tove
Kari
4

5. ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECT’а

Persons
LastName FirstName
Hansen
Svendson
Pettersen
Ola
Tove
Kari
Address
City
Timoteivn 10 Sandnes
Borgvn 23
Sandnes
Storgt 20
Stavanger
SELECT * FROM Persons;
LastName FirstName
Hansen
Svendson
Pettersen
Ola
Tove
Kari
Address
City
Timoteivn 10 Sandnes
Borgvn 23
Sandnes
Storgt 20
Stavanger
5

6. ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ

SELECT column AS column_alias FROM table;
SELECT column FROM table AS table_alias;
6

7. ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ

Persons
LastName FirstName
Hansen
Svendson
Pettersen
Ola
Tove
Kari
Address
City
Timoteivn 10 Sandnes
Borgvn 23
Sandnes
Storgt 20
Stavanger
SELECT LastName AS Family, FirstName AS Name FROM Persons;
Family Name
Hansen Ola
Svendson Tove
Pettersen Kari
7

8. ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ

Persons
LastName FirstName
Hansen
Svendson
Pettersen
Ola
Tove
Kari
Address
City
Timoteivn 10 Sandnes
Borgvn 23
Sandnes
Storgt 20
Stavanger
SELECT LastName, FirstName FROM Persons AS Employees
Employees
LastName FirstName
Hansen
Svendson
Pettersen
Ola
Tove
Kari
8

9. ПРЕДЛОЖЕНИЯ SQL SELECT без повторов

SELECT DISTINCT column_name(s) FROM table_name;
9

10. ПРЕДЛОЖЕНИЯ SQL SELECT без повторов

"Orders"
Company OrderNumber SELECT Company
FROM Orders;
Sega
3412
W3Schools 2312
Trio
4678
W3Schools 6798
Company
Sega
W3Schools
Trio
W3Schools
SELECT DISTINCT Company
FROM Orders;
Company
Sega
W3Schools
Trio
10

11. ПРЕДЛОЖЕНИЯ SQL SELECT с условием

SELECT column FROM table
WHERE column operator value;
=
<>
>
Равно
Не равно
Больше чем
<
>=
<=
Меньше чем
Больше или равно
Меньше или равно
BETWEEN Между
LIKE
Похоже на
11

12. ПРЕДЛОЖЕНИЯ SQL SELECT с условием

"Persons"
LastName FirstName
Address
City
Year
Hansen
Ola
Timoteivn 10 Sandnes
1951
Svendson
Tove
Borgvn 23
Sandnes
1978
Svendson
Stale
Kaivn 18
Sandnes
1980
Pettersen
Kari
Storgt 20
Stavanger 1960
SELECT * FROM Persons WHERE City='Sandnes‘;
LastName FirstName
Address
City
Year
Hansen
Ola
Timoteivn 10 Sandnes 1951
Svendson
Tove
Borgvn 23
Sandnes 1978
Svendson
Stale
Kaivn 18
Sandnes 1980
12

13. ПРЕДЛОЖЕНИЯ SQL SELECT с условием

SELECT * FROM Persons WHERE FirstName='Tove';
SELECT * FROM Persons WHERE FirstName=Tove;
SELECT * FROM Persons WHERE Year>1965;
SELECT * FROM Persons WHERE Year>'1965';
13

14. ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE

SELECT column FROM table
WHERE column LIKE pattern;
14

15. ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE

Store_Information
store_name
Sales
Date
LOS ANGELES
$1500
Jan-05-1999
SAN DIEGO
$250
Jan-07-1999
SAN FRANCISCO
$300
Jan-08-1999
BOSTON
$700
Jan-08-1999
SELECT * FROM Store_Information
WHERE store_name LIKE '%AN%‘;
store_name
Sales
Date
LOS ANGELES
$1500
Jan-05-1999
SAN FRANCISCO
$300
Jan-08-1999
SAN DIEGO
$250
Jan-07-1999
15

16. ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE

SELECT * FROM Persons WHERE FirstName LIKE 'O%';
SELECT * FROM Persons WHERE FirstName LIKE '%a';
SELECT * FROM Persons WHERE FirstName LIKE '%la%';
SELECT * FROM Persons WHERE FirstName LIKE ‘*la*';
SELECT * FROM Persons WHERE FirstName LIKE '%la_ _ _a';
16

17. ПРЕДЛОЖЕНИЯ SQL BETWEEN

SELECT column_name FROM table_name
WHERE column_name BETWEEN value1 AND value2;
ЗАВИСИТ ОТ КОНКРЕТНОЙ СУБД!
17

18. ПРЕДЛОЖЕНИЯ SQL BETWEEN

LastName
Hansen
Nordmann
Pettersen
Svendson
FirstName
Ola
Anna
Kari
Tove
Address
Timoteivn 10
Neset 18
Storgt 20
Borgvn 23
City
Sandnes
Sandnes
Stavanger
Sandnes
SELECT * FROM Persons
WHERE LastName BETWEEN 'Hansen' AND 'Pettersen';
LastName FirstName
Hansen
Ola
Nordmann Anna
Address
City
Timoteivn 10 Sandnes
Neset 18
Sandnes
18

19. ПРЕДЛОЖЕНИЯ SQL BETWEEN

LastName
Hansen
Nordmann
Pettersen
Svendson
FirstName
Ola
Anna
Kari
Tove
Address
Timoteivn 10
Neset 18
Storgt 20
Borgvn 23
City
Sandnes
Sandnes
Stavanger
Sandnes
SELECT * FROM Persons
WHERE LastName NOT BETWEEN 'Hansen' AND 'Pettersen';
LastName FirstName Address
City
Pettersen Kari
Storgt 20 Stavanger
Svendson Tove
Borgvn 23 Sandnes
19

20. ПРЕДЛОЖЕНИЯ SQL ORDER BY

Orders
Company OrderNumber
Sega
3412
ABC Shop 5678
W3Schools 2312
W3Schools 6798
SELECT Company, OrderNumber
FROM Orders
ORDER BY Company;
Company OrderNumber
ABC Shop 5678
Sega
3412
W3Schools 6798
W3Schools 2312
20

21. ПРЕДЛОЖЕНИЯ SQL ORDER BY

Orders
Company OrderNumber
Sega
3412
ABC Shop 5678
W3Schools 2312
SELECT Company, OrderNumber
FROM Orders
ORDER BY Company,
OrderNumber;
W3Schools 6798
Company OrderNumber
ABC Shop 5678
Sega
3412
W3Schools 6798
W3Schools 2312
Company OrderNumber
ABC Shop 5678
Sega
3412
W3Schools 2312
W3Schools 6798
21

22. ПРЕДЛОЖЕНИЯ SQL ORDER BY

Orders
Company OrderNumber
Sega
3412
ABC Shop 5678
W3Schools 2312
SELECT Company, OrderNumber
FROM Orders
ORDER BY Company DESC;
W3Schools 6798
Company OrderNumber
ABC Shop 5678
Sega
3412
Company OrderNumber
W3Schools 6798
W3Schools 2312
W3Schools 6798
W3Schools 2312
Sega
3412
ABC Shop 5678
22

23. ПРЕДЛОЖЕНИЯ SQL ORDER BY

Orders
Company OrderNumber
Sega
3412
ABC Shop 5678
W3Schools 2312
SELECT Company, OrderNumber
FROM Orders
ORDER BY Company DESC,
OrderNumber ASC;
W3Schools 6798
Company
ABC Shop
Sega
OrderNumber
5678
3412
W3Schools 2312
W3Schools 6798
Company OrderNumber
W3Schools 2312
W3Schools 6798
Sega
3412
ABC Shop 5678
23

24. ПРЕДЛОЖЕНИЯ SQL ORDER BY

SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY 2 DESC
ПО ВТОРОМУ
СТОЛБЦУ
24

25. ПРЕДЛОЖЕНИЯ SQL AND & OR

ПРЕДЛОЖЕНИЯ SQL
AND & OR
LastName
Hansen
Svendson
Svendson
FirstName
Address
City
Ola
Timoteivn 10 Sandnes
Tove
Borgvn 23
Sandnes
Stephen
Kaivn 18
Sandnes
SELECT * FROM Persons
WHERE FirstName='Tove' AND LastName='Svendson';
LastName FirstName Address
City
Svendson Tove
Borgvn 23 Sandnes
25

26. ПРЕДЛОЖЕНИЯ SQL AND & OR

ПРЕДЛОЖЕНИЯ SQL
AND & OR
LastName
Hansen
Svendson
Svendson
FirstName
Address
City
Ola
Timoteivn 10 Sandnes
Tove
Borgvn 23
Sandnes
Stephen
Kaivn 18
Sandnes
SELECT * FROM Persons
WHERE firstname='Tove' OR lastname='Svendson' ;
LastName FirstName Address
City
Svendson Tove
Borgvn 23 Sandnes
Svendson
Stephen
Kaivn 18
Sandnes
26

27. ПРЕДЛОЖЕНИЯ SQL AND & OR

ПРЕДЛОЖЕНИЯ SQL
AND & OR
LastName
Hansen
Svendson
Svendson
FirstName
Address
City
Ola
Timoteivn 10 Sandnes
Tove
Borgvn 23
Sandnes
Stephen
Kaivn 18
Sandnes
SELECT * FROM Persons
WHERE (FirstName='Tove' OR FirstName='Stephen')
AND LastName='Svendson' ;
LastName FirstName Address
City
Svendson
Tove
Borgvn 23 Sandnes
Svendson
Stephen
Kaivn 18
Sandnes
27

28. ПРЕДЛОЖЕНИЯ SQL AND & OR

ПРЕДЛОЖЕНИЯ SQL
AND & OR
Store_Information
store_name
Los Angeles
San Diego
San Francisco
Boston
Sales
Date
$1500 Jan-05-1999
$250 Jan-07-1999
$300 Jan-08-1999
$700 Jan-08-1999
store_name
Los Angeles
San Francisco
SELECT store_name FROM Store_Information
WHERE Sales > 1000 OR (Sales < 500 AND Sales > 275);
28

29. ПРЕДЛОЖЕНИЯ SQL ФУНКЦИИ

SELECT function(column) FROM table;
• AVG – среднее значение в столбце
• COUNT – число значений в столбце
• MAX – самое большое значение в столбце
• MIN – самое малое значение в столбце
• SUM – сумма значений по столбцу
29

30. ПРЕДЛОЖЕНИЯ SQL AVG

Persons
Name
Hansen, Ola
Svendson, Tove
Pettersen, Kari
Age SELECT AVG(Age) FROM Persons
34 32.67
45 SELECT AVG(Age) FROM Persons WHERE Age>20
19 39.5
30

31. ПРЕДЛОЖЕНИЯ SQL COUNT

Store_Information
store_name
Sales
Los Angeles
San Diego
Los Angeles
$1500 Jan-05-1999
$250 Jan-07-1999
$300 Jan-08-1999
Boston
Date
$700 Jan-08-1999
SELECT COUNT(store_name) FROM Store_Information;
Count(store_name)
4
31

32. ПРЕДЛОЖЕНИЯ SQL COUNT

Store_Information
store_name
Sales
Los Angeles
San Diego
Los Angeles
$1500 Jan-05-1999
$250 Jan-07-1999
$300 Jan-08-1999
Boston
Date
$700 Jan-08-1999
SELECT COUNT(DISTINCT store_name)
FROM Store_Information;
Count(store_name)
3
32

33. ПРЕДЛОЖЕНИЯ SQL MAX

Persons
Name
Hansen, Ola
Svendson, Tove
Pettersen, Kari
Age
34
45
19
SELECT MAX(Age) FROM Persons
45
33

34. ПРЕДЛОЖЕНИЯ SQL MIN

Persons
Name
Hansen, Ola
Svendson, Tove
Pettersen, Kari
Age
34
45
19
SELECT MIN(Age) FROM Persons
19
34

35. ПРЕДЛОЖЕНИЯ SQL SUM

Store_Information
store_name
Sales
Los Angeles
San Diego
Los Angeles
$1500 Jan-05-1999
$250 Jan-07-1999
$300 Jan-08-1999
Boston
Date
$700 Jan-08-1999
SELECT SUM(Sales) FROM Store_Information;
$1500 + $250 + $300 + $700 = $2750
SUM(Sales)
$2750
35

36. Некоторые функции MS Access

Функция
Описание
AVG(column)
Returns the average value of a column
COUNT(column)
Returns the number of rows (without a NULL value) of a column
COUNT(*)
Returns the number of selected rows
FIRST(column)
Returns the value of the first record in the specified field
LAST(column)
Returns the value of the last record in the specified field
MAX(column)
Returns the highest value of a column
MIN(column)
Returns the lowest value of a column
STDEV(column)
STDEVP(column)
SUM(column)
Returns the total sum of a column
VAR(column)
VARP(column)
36

37. Некоторые функции MS Access

Функция
Описание
UCASE(c)
Converts a field to upper case
LCASE(c)
Converts a field to lower case
MID(c,start[,end])
Extract characters from a text field
LEN(c)
Returns the length of a text field
INSTR(c)
Returns the numeric position of a named character within a text field
LEFT(c,number_of_char)
Return the left part of a text field requested
RIGHT(c,number_of_char) Return the right part of a text field requested
ROUND(c,decimals)
Rounds a numeric field to the number of decimals specified
MOD(x,y)
Returns the remainder of a division operation
NOW()
Returns the current system date
FORMAT(c,format)
Changes the way a field is displayed
DATEDIFF(d,date1,date2)
Used to perform date calculations
37

38. Некоторые функции MS SQL Server

Функция
AVG(column)
Описание
Returns the average value of a column
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AGG
COUNT(column)
Returns the number of rows (without a NULL value) of a column
COUNT(*)
Returns the number of selected rows
COUNT(DISTINCT column)
Returns the number of distinct results
FIRST(column)
Returns the value of the first record in the specified field
(not supported in SQLServer2K)
LAST(column)
Returns the value of the last record in the specified field
(not supported in SQLServer2K)
MAX(column)
Returns the highest value of a column
MIN(column)
Returns the lowest value of a column
STDEV(column)
STDEVP(column)
SUM(column)
Returns the total sum of a column
VAR(column)
VARP(column)
38

39. ПРЕДЛОЖЕНИЯ SQL SELECT IN

SELECT column_name FROM table_name
WHERE column_name IN (value1,value2,..);
LastName FirstName
Hansen
Ola
Address
City
Timoteivn 10 Sandnes
Nordmann Anna
Neset 18
Sandnes
Pettersen
Kari
Storgt 20
Stavanger
Svendson
Tove
Borgvn 23
Sandnes
SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen');
LastName FirstName
Address
City
Hansen
Ola
Timoteivn 10 Sandnes
Pettersen
Kari
Storgt 20
Stavanger
39

40.

где, expression — любое символьное выражение
pattern — шаблон, по которому будет происходить проверка
выражения expression. Шаблон может включать в себя
следующие спец. символы:
Символ
%
_
[]
[^]
Описание
Строка любой длины
Любой одиночный
символ
Диапазон или
последовательность симв
олов
Исключающий диапазон
или
последовательность
символов
Примеры
Пример 1
Пример 2
Пример 3
Пример 4
40

41. Примеры оператора SQL LIKE

Profess
ID
UniversityName
Students
Faculties
Location
Site
• SELECT * FROM Universities WHERE
Site
LIKE
'[kores
Perm State National
o]%‘
1
12400
12
1229
Perm
psu.ru
Research University
Saint Petersburg State
Saint2
21300
24
13126
spbu.ru
University
Petersburg
'_[^e-s]%'
Novosibirsk State
3
7200
13
1527 Novosibirsk nsu.ru
University
Moscow State
4
35100
39
14358 Moscow
msu.ru
University
Higher School of
5
20335
12
1615
Moscow
hse.ru
Economics
Yekaterinbur
6 Ural Federal University 57000
19
5640
urfu.ru
g
National Research
7
8600
10
936
Moscow mephi.ru
41
Nuclear University
• SELECT * FROM Universities WHERE Location LIKE

42. SELECT * FROM Universities WHERE Site LIKE '_ _ _ _.ru

ID
UniversityName
2
Saint Petersburg State
University
6 Ural Federal University
Students Faculties
21300
24
57000
19
Profess
ores
Location
Site
Saintspbu.ru
Petersburg
Yekaterinbur
5640
urfu.ru
g
13126
42

43. SELECT * FROM Universities WHERE Site LIKE '[k-o]%'

ID
3
4
7
UniversityName
Novosibirsk State
University
Moscow State
University
National Research
Nuclear University
Students Faculties
Profess
ores
Location
Site
7200
13
1527
Novosibirsk
nsu.ru
35100
39
14358
Moscow
msu.ru
8600
10
936
Moscow
mephi.ru
43

44. SELECT * FROM Universities WHERE Location LIKE '_[^e-s]%'

ID
2
7
UniversityName
Saint Petersburg State
University
National Research
Nuclear University
Students Faculties
Profess
ores
Location
Site
21300
24
13126
SaintPetersburg
spbu.ru
8600
10
936
Moscow
mephi.ru
44

45.

SELECT supplier_city, supplier_state
FROM suppliers
WHERE supplier_name = 'Intel'
ORDER BY supplier_city DESC, supplier_state ASC;
возвращает все отсортированные записи по
полю supplier_city в порядке убывания,
а по полю supplier_state в порядке возрастания
45

46.

SELECT * FROM mytable
ORDER BY column1 ASC, column2 DESC,
column3 ASC
Первый столбец по возрастанию, второй по
убыванию, третий опять по возрастанию.
Запрос упорядочит строки по первому столбцу,
затем, не разрушая первого правила, по
второму столбцу. Затем, так же, не нарушая
имеющихся правил, по третьему.
46
English     Русский Правила