Выбор данных из произвольной таблицы. Тема 2

1.

Тема 2.
Выбор данных из
произвольной таблицы

2.

SELECT столбцы
FROM таблицы
[JOIN описание_соединения_таблиц]
[WHERE условие_отбора]
[GROUP BY группируемые_столбцы]
[HAVING условия_поиска]
[ORDER BY сортируемые_столбцы]
;
2

3.

SELECT столбцы
FROM таблицы
[JOIN описание_соединения_таблиц]
[WHERE условие_отбора]
[GROUP BY группируемые_столбцы]
[HAVING условие_отбора]
[ORDER BY сортируемые_столбцы]
;
3

4.

2.1. Выбор столбцов
с помощью предложений
SELECT и FROM
4

5.

2.1. Выбор столбцов с помощью
предложений SELECT и FROM
Выбор одного столбца из таблицы
SELECT столбец FROM таблица ;
Выбор нескольких произвольных столбцов
из одной таблицы
SELECT столбец1, столбец2, … столбецN,
FROM таблица ;
5

6.

2.1. Выбор столбцов с помощью предложений
SELECT и FROM
Пример 2-1
Перечислить названия
городов, где живут авторы.
город
SELECT город
FROM авторы;
СанктПетербург
Москва
Москва
Выборг
Ангарск
Тверь
Казань
Иркутск
6

7.

2.1. Выбор столбцов с помощью предложений
SELECT и FROM
Перечислить имена и
фамилии авторов, а
также города, где
они живут.
SELECT имя,
фамилия,
город
FROM авторы;
Пример 2-2
Имя
Фамилия
город
Иван
Наумов
СанктПетербург
Наталья
Савельев
а
Москва
Владимир
Щербинин Москва
Анастасия
Арсеньев
а
Выборг
Константин Кузнецов
Ангарск
Николай
Кузнецов
Тверь
Наталья
Саввина
Казань
Сара
Бахман
Иркутск
7

8.

2.1. Выбор столбцов с помощью
предложений SELECT и FROM
Выбор всех столбцов одной таблицы
SELECT * FROM таблица ;
8

9.

2.1.1. Создание псевдонимов столбцов с
помощью предложения AS
SELECT столбец1 [ AS ] псевдоним1,
столбец2 [ AS ] псевдоним2,
столбец3 [ AS ] псевдоним3,
…
столбецN [ AS ] псевдонимN
FROM таблица;
9

10.

2.1.1. Создание псевдонимов столбцов
Примеры
SELECT имя AS Имя
FROM авторы
Имя
Иван
Наталья
Владимир
Анастасия
SELECT имя "Имя автора"
FROM авторы
Имя автора
Иван
Наталья
Владимир
Константин
Николай
Наталья
Сара
Анастасия
Константин
Николай
Наталья
Сара
10

11.

2.1.2. Удаление повторяющихся строк
с помощью ключевого слова DISTINCT
SELECT DISTINCT столбцы
FROM таблица;
11

12.

2.1.2. Удаление повторяющихся строк
с помощью ключевого слова DISTINCT
Пример 2.4
1) Перечислить регионы,
где живут авторы
регион
Санкт-Петербург
Москва
Москва
SELECT регион
FROM авторы;
Санкт-Петербург
Красноярский край
Тверская обл.
респ.Татарстан
Красноярский край
12

13.

2.1.2. Удаление повторяющихся строк
с помощью ключевого слова DISTINCT
Пример 2.5
2) Перечислить регионы,
в которых живут авторы,
без повторов
регион
Красноярский край
SELECT DISTINCT регион
FROM авторы;
Москва
респ.Татарстан
Санкт-Петербург
Тверская обл.
13

14.

2.2. Сортировка строк
с помощью предложения
ORDER BY
14

15.

2.2. Сортировка строк с помощью предложения
ORDER BY
Сортировка по одному произвольному столбцу
SELECT столбцы
FROM таблица
ORDER BY сортируемый_столбец [ASC | DESC];
15

16.

2.2. Сортировка строк с помощью предложения
ORDER BY
Сортировка по нескольким столбцам
SELECT столбцы
FROM таблица
ORDER BY сортируемый_столбец1 [ASC | DESC],
сортируемый_столбец2 [ASC | DESC],
сортируемый_столбец3 [ASC | DESC],
…
сортируемый_столбецN [ASC | DESC];
16

17.

2.2. Сортировка строк с помощью предложения
ORDER BY
Сортировка по нескольким столбцам с указанием их местоположения
SELECT
столбцы
FROM
таблица
ORDER BY номер_сортируемого_столбца1 [ASC | DESC],
номер_сортируемого_столбца2 [ASC | DESC],
номер_сортируемого_столбца3 [ASC | DESC],
…
номер_сортируемого_столбцаN [ASC | DESC],
17

18.

2.2. Сортировка строк с помощью предложения
ORDER BY
В предложении ORDER BY в качестве сортируемого
столбца может быть указано:
• столбец (имя или выражение)
- не обязательно показан явно в списке SELECT
• порядковый номер столбца из списка SELECT
• псевдоним столбца, присвоенный в предложении
SELECT
18

19.

2.2. Сортировка строк с помощью предложения
ORDER BY
Пример 2.6
Перечислить имена и фамилии
авторов так, чтобы фамилии
авторов располагались в
обратном алфавитном порядке.
SELECT фамилия,
имя
FROM авторы
ORDER BY 2 DESC;
Фамилия
Имя
Бахман
Сара
Кузнецов
Николай
Синицына
Наталья
Савельева
Наталья
Кузнецов
Константин
Наумов
Иван
Щербинин
Владимир
Арсеньева
Анастасия
19

20.

2.2. Сортировка строк с помощью предложения
ORDER BY
Пример (2.7)
Перечислить имена и фамилии авторов, а также
города и регионы проживания авторов.
Расположить в алфавитном порядке сначала
по регионам, а для совпадающих регионов – по
городам.
20

21.

2.2. Сортировка строк с помощью предложения
ORDER BY
Пример (2.7)
SELECT
FROM
имя, фамилия, город, регион
авторы
ORDER BY регион, город
Имя
Фамилия
регион
город
Константин Кузнецов
Красноярский край Ангарск
Сара
Бахман
Красноярский край Иркутск
Наталья
Савельева
Москва
Москва
Владимир
Щербинин
Москва
Москва
Наталья
Синицына
респ.Татарстан
Казань
Иван
Наумов
Санкт-Петербург
Санкт-Петербург
Анастасия
Арсеньева
Санкт-Петербург
Санкт-Петербург
Николай
Кузнецов
Тверская обл.
Тверь
21

22.

2.2. Сортировка строк с помощью предложения
ORDER BY
Пример (2.11)
SELECT ид_кни, цена, продажи,
цена * продажи AS Выручка
FROM книги
ORDER BY Выручка DESC;
---------------------------------------------------------------------------------------------------------------------------------------------
Варианты для MS Access:
ORDER BY цена * продажи DESC
ORDER BY 4 DESC
22

23.

2.3. Фильтрация строк
с помощью предложения
WHERE
23

24.

2.5. Фильтрация строк с помощью предложения
WHERE
SELECT столбцы
FROM таблица
WHERE условие_отбора;
условие_отбора
- логическое выражение (предикат),
может принимать одно из 3-х значений:
"истина", "ложь", "не определено"
( TRUE, FALSE, NULL )
Предикат проверяется для каждой строки результирующей таблицы.
В таблице останутся только те строки, для каждой из которых
значение предиката равно TRUE.
24

25.

2.3. Фильтрация строк с помощью предложения
WHERE
Условие поиска может быть построено с помощью
следующих операторов:
Тип условия
Операторы SQL
Сравнение
<>, <, <=, >, >=, =
Сопоставление с образцом
LIKE
Проверка на вхождение в диапазон
BETWEEN
Проверка на вхождение в список
IN
Проверка на NULL
IS NULL
Комбинирование логических выражений
AND, OR, NOT
25

26.

2.3. Фильтрация строк с помощью предложения
WHERE
Условие поиска может быть построено с помощью
следующих операторов (продолжение):
Тип условия
В сочетании с оператором сравнения
требует, чтобы соответствовали все
значения из множества
В сочетании с оператором сравнения
требует, чтобы соответствовало хотя бы
с одно значение из множества
Существует хотя бы одно значение
Операторы SQL
ALL
ANY
EXISTS
26

27.

2.3.1. Операторы сравнения в предложении WHERE
SELECT столбцы
FROM таблица
WHERE проверяемый_столбец op значение;
Оператор
сравнения
Описание
=
Равно
<>
Не равно
<
Меньше
<=
Не больше
>
Больше
>=
Не меньше
op - оператор сравнения
27

28.

2.3.1. Операторы сравнения в предложении WHERE
Примеры (2-12, 2-13, 2-14)
SELECT ид_авт, имя, фамилия
FROM авторы
WHERE фамилия <> 'Hull';
SELECT название, контракт
FROM книги
WHERE контракт = 0;
SELECT название, датаиздания
FROM книги
WHERE датаиздания >= DATE '2001-01-01';
28

29.

2.3.1. Операторы сравнения в предложении
WHERE
Пример (2-15)
SELECT название AS "Название книги",
цена * продажи AS "выручка"
FROM книги
WHERE цена * продажи > 1000000;
Название книги
выручка
Домашний компьютер
1025396.65
Общая психология
1400008.00
Нарочно не придумаешь
35929790.00
Родник жемчужин
1299012.99
29

30.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Таблица истинности оператора AND
AND
True
False
Unknown
True
True
False
Unknown
False
False
False
False
Unknown
False
Unknown
Unknown
30

31.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Коммутативность оператора AND
WHERE
условие1 AND условие2
WHERE
условие2 AND условие1
31

32.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Примеры (2.16, 2.17)
SELECT название, жанр, цена
FROM книги
WHERE жанр = 'история' AND цена < 20;
SELECT имя, фамилия, регион
FROM авторы
WHERE фамилия >= 'Н'
AND фамилия <= 'Ся';
32

33.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Таблица истинности оператора OR
OR
True
False
Unknown
True
True
True
True
False
True
False
Unknown
Unknown
True
Unknown
Unknown
33

34.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Пример (2.18)
SELECT имя, фамилия, город, регион
FROM
авторы
WHERE (регион = 'Тверская обл.')
OR (регион = 'Красноярский край')
OR (город = 'Казань');
34

35.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Таблица истинности оператора NOT
Условие
Инверсия
(Condition)
(NOT Condition)
True
False
False
True
Unknown
Unknown
35

36.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
WHERE NOT регион = 'Красноярский край'
WHERE регион <> 'Красноярский край'
36

37.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Примеры (2.19, 2.20)
SELECT имя, фамилия, регион
FROM авторы
WHERE NOT (регион = 'Москва');
SELECT название, продажи, цена
FROM книги
WHERE NOT (цена < 20) AND (продажи > 15000);
37

38.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Приоритет выполнения операторов AND, OR и NOT
при одновременном использовании
NOT
х AND NOT у OR z
AND
( х AND ( NOT у ) ) OR z
OR
38

39.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Пример (2.21)
Показать список книг в жанрах 'история' и
'биография', цена которых ниже 20.
39

40.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Пример (2.21)
SELECT ид_кни, жанр, цена
FROM книги
WHERE жанр = 'история'
OR жанр = 'биография'
AND цена < 20;
SELECT ид_кни, жанр, цена
FROM книги
WHERE ( жанр = 'история'
OR жанр = 'биография' )
ид_кни
жанр
цена
К01
история
21.99
К02
история
19.95
К06
биография
19.95
К13
история
29.99
К14
история
19.95
жанр
цена
К02
история
19.95
К06
биография
19.95
К14
история
19.95
ид_кни
AND цена < 20;
40

41.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Пример (2.21)
SELECT ид_кни, жанр, цена
FROM книги
жанр
цена
К02
история
19.95
AND жанр = 'история'
К06
биография
19.95
OR жанр = 'биография' ;
К14
история
19.95
WHERE цена < 20
ид_кни
Почему ответ правильный при ошибочном запросе?
- Потому что в таблице нет книг в жанре биография с ценой больше 20
Для проверки добавьте в таблицу ещё одну строку с книгой
в жанре биография и ценой больше 20.
Выполните запрос ещё раз и убедитесь, что ответ не верный.
41

42.

2.3.2. Комбинирование условий
с помощью операторов AND, OR и NOT
Эквивалентность логических выражений с оператором NОТ
Выражение
Эквивалентное выражение
NОТ (p AND q)
(NOT p) OR (NOT q)
NOT (p OR q)
(NOT p) AND (NOT q)
NOT (NOT p)
p
Задание:
Написать примеры эквивалентных логических выражений каждого из
представленных в таблице типов на основе таблиц из БД Книги_ру
42

43.

2.3.3. Сравнение по шаблону оператором LIKE
Операторы-метасимволы
Операторметасимвол
Соответствует
%
любое количества произвольных символов
_
Один любой символ
43

44.

2.3.3. Сравнение по шаблону оператором LIKE
Дополнительные символы-шаблоны
в Transact-SQL
Операторметасимвол
Для SQL Server
Соответствует
[]
Любой одиночный символ, содержащийся в
диапазоне ([a-f]) или наборе ([abcdef]).
Можно перечислить сразу несколько
диапазонов ([0-9a-z])
[^]
Любой одиночный символ, не содержащийся в
диапазоне ([^a-f]) или наборе ([^abcdef]).
https://docs.microsoft.com/ru-ru/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15
44

45.

2.3.3. Сравнение по шаблону оператором LIKE
Примеры применения метасимволов в шаблонах
Шаблон
Соответствует
‘A%’
'Anonymous',
‘%s’
'DMBSes',
‘%in%’
'in',
‘____’
'АБВГ’,
‘Я ем‘,
‘Jack'
‘Qua__’
'Quack',
'Quaff‘,
'Quake'
‘_re%’
'Tree',
'[К-Н]%'
'Наталья', 'Константин', 'Николай'
'AC/DC'
'Victoria Falls'
‘inch', 'Pine', 'linchpin', 'lynchpin'
'area',
'fret',
'fretful‘,
'freeze'
'сав[^в]%' 'Савельева', 'Савушкин'
'[а-бк-н]%' 'Наумов', 'Кузнецов', 'Бахман', ‘Мур'
45

46.

2.3.3. Сравнение по шаблону оператором LIKE
WHERE город LIKE 'Москва'
WHERE город = 'Москва'
46

47.

2.3.3. Сравнение по шаблону оператором LIKE
Фильтрация строк по произвольному шаблону значений
SELECT столбцы
FROM таблица
WHERE проверяемый_столбец [NOT] LIKE 'шаблон ' ;
47

48.

2.3.3. Сравнение по шаблону оператором LIKE
Примеры (2.23, 2.24)
SELECT имя, фамилия
FROM авторы
WHERE фамилия LIKE 'Са%';
SELECT имя, фамилия
FROM авторы
WHERE au.lname LIKE '_____н%';
48

49.

2.3.3. Сравнение по шаблону оператором LIKE
Пример (2.25)
SELECT имя, фамилия, телефон
FROM авторы
WHERE телефон NOT LIKE '495-___-____'.
AND телефон NOT LIKE '812-___-%'
AND телефон NOT LIKE '861-%' ;
49

50.

Сравнение строк
'А' = 'а'
50

51.

Сравнение строк
Результат сравнения строк зависит от установленных в системе
параметров сортировки.
Параметры сортировки, установленные в операционной системе
и в SQL Server могут отличаться.
В SQL Server могут быть выставлены разные параметры
сортировки для базы данных, таблицы, поля.
Правила сортировки в SQL Server задаются параметром COLLATE
Подробнее о параметрах сортировки:
https://habr.com/ru/company/otus/blog/461231
https://docs.microsoft.com/ru-ru/sql/t-sql/statements/collations?view=sql-server-ver15
Получить список всех параметров сортировки:
SELECT name, description
FROM fn_helpcollations();
51

52.

Сравнение строк
Пример запроса с переопределенным параметром
сортировки, в котором учитывается регистр букв
SELECT * FROM Авторы
WHERE фамилия
COLLATE Latin1_General_BIN
like '[Н]%'
52

53.

2.3.4. Сравнение с диапазоном – BETWEEN
WHERE проверяемый_столбец
BETWEEN min_значение AND max_значение
WHERE (проверяемый_столбец >= min_значение) AND
(проверяемый_столбец <= max_значение)
53

54.

2.3.4. Сравнение с диапазоном – BETWEEN
Фильтрация строк по любому диапазону
SELECT столбцы
FROM таблица
WHERE проверяемый_столбец
[NOT] BETWEEN min_значение AND max_значение;
54

55.

2.3.4. Сравнение с диапазоном – BETWEEN
Пример (2.26, 2.27, 2.28)
SELECT имя, фамилия, индекс
FROM авторы
WHERE индекс NOT BETWEEN '20000' AND '89999';
SELECT название, цена
FROM книги
WHERE цена BETWEEN 10 AND 19.95;
SELECT название, "дата издания"
FROM книги
WHERE "дата издания" BETWEEN DATE '2000-01-01'
AND DATE '2000-12-З1';
55

56.

2.3.5. Фильтрация с помощью оператора IN
WHERE
проверяемый_столбец
IN (значение1, значение2, значение3)
WHERE (проверяемый_столбец = значение1)
OR (проверяемый_столбец = значение2)
OR (проверяемый_столбец = значение3)
56

57.

2.3.5. Фильтрация с помощью оператора IN
Фильтрация строк по произвольному списку
SELECT
столбцы
FROM
таблица
WHERE
проверяемый_столбец
[NOT] IN (значение1, значение2, ...);
57

58.

2.3.5. Фильтрация с помощью оператора IN
Пример (2.30)
SELECT имя, фамилия, регион
FROM авторы
WHERE регион NOT IN ( 'Москва',
'Санкт-Петербург',
'Ленинградская обл.‘ ) ;
Пример (2.32)
SELECT ид_кни, датаиздания
FROM книги
WHERE датаиздания IN ('2000-01-01',
'2001-01-01',
'2002-01-01' );
58

59.

2.3.5. Фильтрация с помощью оператора IN
WHERE
регион NOT IN (знач1, знач2, ... , значN )
WHERE
регион <> 'знач1 '
AND регион <> 'знач2 '
…
AND регион <> 'значN '
59

60.

2.3.6. Проверка на значение null
с помощью оператора IS NULL
Пример (2.33)
Показать список всех издательств, а также
города, регионы и страны, в которых
издательства расположены.
SELECT ид_изд, издательство, регион, страна
FROM
ид_изд
издательства ;
издательство
регион
страна
И01
Родникъ
Москва
Россия
И02
Питер
Санкт-Петербург
Россия
И03
Небо Кубани
Краснодарский край
Россия
И04
Деловой Мир
Москва
Россия
И05
Schadenfreude Press
NULL
Германия
60

61.

2.3.6. Проверка на значение null
с помощью оператора IS NULL
SELECT ид_изд, издательство, регион, страна
FROM
издательства
WHERE
(регион = 'Москва') OR (регион <> 'Москва');
ид_изд
издательство
И01
Родникъ
Москва
Россия
И02
Питер
Санкт-Петербург
Россия
И03
Небо Кубани
Краснодарский край Россия
И04
Деловой Мир
Москва
регион
страна
Россия
61

62.

2.3.6. Проверка на значение null
с помощью оператора IS NULL
Пример (2.34)
Перечислить названия и адреса издательств,
которые находятся в регионе Москва.
SELECT ид_изд, издательство, регион, страна
FROM
издательства
WHERE
регион = 'Москва';
ид_изд
издательство
И01
Родникъ
Москва
Россия
И04
Деловой Мир
Москва
Россия
регион
страна
62

63.

2.3.6. Проверка на значение null
с помощью оператора IS NULL
Пример (2.35)
Показать названия всех издательств, которые
расположены вне региона Москва.
SELECT ид_изд, издательство, регион, страна
FROM
издательства
WHERE
регион <> 'Москва' OR регион IS NULL ;
ид_изд
издательство
регион
страна
И02
Питер
Санкт-Петербург
Россия
И03
Небо Кубани
Краснодарский край
Россия
И05
Schadenfreude Press
NULL
Германия
63

64.

2.3.6. Проверка на значение null
с помощью оператора IS NULL
Выбор строк, (не) содержащих значения null
SELECT столбцы
FROM таблица
WHERE проверяемый_столбец IS [NOT] NULL;
64

65.

2.3.6. Проверка на значение null
с помощью оператора IS NULL
Пример (2.36)
Выбрать книги-биографии, даты публикации
(прошлые или будущие) которых известны.
SELECT
ид_кни, жанр, датаиздания
FROM книги
WHERE жанр = 'биография' AND датаиздания IS NOT NULL;
65