Автор: Капинус Мария Павловна
Должность: преподаватель
Учебное заведение: ГКПОУ Прокопьевский горнотехнический техникум им. В.П. Романова
Населённый пункт: г. Прокопьевск
Наименование материала: Методическая разработка
Тема: Разработка запросов на Sql
Раздел: среднее профессиональное
УТОЧНЕНИЕ ЗАПРОСА
1. Секция
WHERE
Основное выражение для выборки данных имеет вид:
SELECT списокСтолбцов
FROM списокТаблиц;
Такой
запрос
возвращает
таблицу,
полученную
из
указанной
в
операторе
FROM
путем
выделения
в
ней
только
тех
столбцов,
которые
определены в операторе
SELECT
.
Для
выделения
требуемых
записей
(строк)
исходной
таблицы
используется выражение, следующее за ключевым словом
WHERE
. Условия
поиска
в
операторе
WHERE
являются
логическими
выражениями,
т.е.
принимающими одно из двух возможных значений –
true
(ИСТИНА) или
false
(ЛОЖЬ).
Например, получим список всех книг о компьютерных технологиях. У
этих
книг
поле
subject_id
равно
4.
Соответственно
в
секцию
WHERE
включается оператор
=
, который проверяет это условие:
SELECT *
FROM books
WHERE subject_id = 4;
Выражение
subject_id = 4
является истинным (имеет значение
true
),
если
в
текущей
записи
таблицы
значение
столбца
subject_id
равно 4. В противном случае это выражение ложно (имеет значение
false
).
Одно
и
то
же
логическое
выражение
может
быть
истинным
для
одних
записей
и
ложным
для
других.
В SQL
логические
выражения
могут
принимать еще и неопределенное значение. Это происходить тогда, когда в
выражении некоторые элементы имеют значение
NULL
. Таким образом, SQL
имеет дело не с классической двухзначной, а с трехзначной логикой.
Секция
WHERE
может
содержать
несколько
условий,
объединенных
логическими операторами (например,
AND
или
OR
) и возвращающими одно
логическое значение.
Например, получим все записи для книг о компьютерах, которые, кроме
того, что они о компьютерах, написаны Марком Лутцем. Запрос уточняется
объединением двух условий при помощи логического оператора AND:
SELECT title
FROM books
WHERE subject_id = 4 AND author_id = 7805;
Первое
условие
проверяет,
что
книга
посвящена
компьютерным
технологиям
(поле
subject_id
равно 4), а второе – что автором книги
является Марк Лутц (поле
author_id
равно 7805). Объединение условий
уменьшает объем итогового набора.
Получим
все
книги,
посвященные
компьютерным
технологиям или
искусству; в этом случае два условия объединяются логическим оператором
OR
.
SELECT title
FROM books
WHERE subject_id = 4 OR subject_id = 0;
Прежнее
первое
условие
(книги
по
компьютерной
тематике)
объединяется со вторым условием: книги по искусству (поле
subject_id
равно 0). В результате объем итогового набора увеличивается, каждая его
запись удовлетворяет хотя бы одному из этих условий.
Количество условий, объединяемых в секции
WHERE
, не ограничено,
хотя при наличии двух и более условий обычно задается порядок выполнения
сравнений при помощи круглых скобок.
2. Предикаты
При составлении логических выражений используются специальные
ключевые
слова
и
символы
операций
сравнения,
которые
называют
предикатами. Наиболее часто используются предикаты сравнения, такие как
(
=
), (
<
), (
>
), (
<>
), (
<=
)
и (
>=
) . Однако имеются и другие. Далее приведен
список всех предикатов:
предикаты сравнения (
=
), (
<
), (
>
), (
<>
), (
<=
) и (
>=
);
BETWEEN
;
IN
,
NOT IN
;
LIKE
,
NOT LIKE
;
IS NULL
;
ALL
,
SOME
,
ANY
;
EXISTS
;
UNIQUE
;
DISTINCT
;
OVERLAPS
;
MATCH
;
SIMILAR
.
2.1.
Between
Предикат
BETWEEN
позволяет задать выражение проверки вхождения
какого-либо значения в диапазон, определяемый граничными значениями.
Например,
WHERE cost BETWEEN 100 AND 150
Здесь
ключевое
слово
AND
представляет
собой
логический
союз
И
.
Граничные значения (в примере это 100 и 150) входят в диапазон. Причем
первое граничное значение должно быть не больше второго.
Эквивалентным
приведенному
является
выражение
с
предикатами
сравнения:
WHERE cost >= 100 AND cost <= 150
Кроме
данных
числового
типа,
в
выражениях
с
between
можно
использовать данные следующих типов: символьные, битовые, даты-времени.
Например, чтобы выбрать записи, в которых фамилии авторов находятся в
диапазоне от
A
до
G
, можно использовать такое выражение:
SELECT first_name, last_name
FROM authors
WHERE last_name BETWEEN 'A' AND 'G';
2.2.
IN
и
NOT IN
Предикаты
IN
и
NOT IN
применяются
для
проверки
вхождения
какого-либо значения в заданный список значений. Например, для выборки
записей о книгах некоторой тематики можно использовать такое выражение:
SELECT title, author_id
FROM books
WHERE subject_id IN (7805, 0);
Если требуется получить данные о всех книгах не о компьютерах и
искусстве, то можно использовать предикат
NOT IN
:
SELECT title, author_id
FROM books
WHERE subject_id NOT IN (7805, 0);
2.3.
LIKE
и
NOT LIKE
Предикаты
LIKE
и
NOT LIKE
применяются для проверки частичного
соответствия символьных строк. Например, столбец
Телефон
в некоторой
таблице содержит полные номера телефонов, и требуется выбрать лишь те
записи, в которых номера телефонов начинаются с 8112 или содержат такое
сочетание цифр.
Критерий частичного соответствия задается с помощью двух символов-
масок: знака процента (
%
) и подчеркивания (
_
). Знак процента означает
любой набор символов, в том числе и пустой, а символ подчеркивания –
любой одиночный символ.
Например,
чтобы
выбрать
записи
об
изданиях,
у
которых
isbn
начинается с 044, можно использовать такое выражение:
SELECT isbn, edition, publication
FROM edition
WHERE isbn LIKE '044%';
Если требуется выбрать записи о книгах про
Python
, то для этого
подойдет следующее выражение:
SELECT title, author_id
FROM books
WHERE title LIKE '%Python%';
Если
необходимо
исключить
все
записи
о
книгах
про
Python
, то
можно воспользоваться следующим выражением:
SELECT title, author_id
FROM books
WHERE title NOT LIKE '%Python%';
2.4.
SIMILAR
П р е д и к а т
SIMILAR
применяется
для
проверки
частичного
соответствия символьных строк. Эту же задачу можно решить и с помощью
предиката
LIKE
, однако в ряде случаев
SIMILAR
более эффективен.
Пусть в некоторой таблице имеется столбец
OC
, содержащий названия
операционных систем. Нужно выбрать записи, соответствующие Windows XP,
Windows Vista и Windows 7. Тогда в выражении запроса можно использовать
такой оператор
WHERE
:
WHERE ОС SIMILAR TO 'Windows (XP|Vista|7)';
2.5.
IS NULL
Предикат
IS NULL
применяется для выявления записей, в которых тот
или иной столбец не имеет значения. Например, для получения записей о
жанрах,
для
которых
не
указано
местоположение,
можно
использовать
следующее выражение:
SELECT id, subject
FROM subjects
WHERE location IS NULL;
Для
получения
записей,
в
которых
столбец
location
содержит
некоторые
определенные
значения
(т.е.
отличные
от
NULL
)
можно
использовать аналогичное выражение, но с логическим оператором
NOT
:
SELECT id, subject
FROM subjects
WHERE location IS NOT NULL;
Не
следует
использовать
предикаты
сравнения
с
NULL
,
такие
как
location = NULL
.
2.6.
OVERLAPS
Предикат
OVERLAPS
используется для определения, перекрываются ли
два интервала времени. Выражение с предикатом
OVERLAPS
можно записать,
например, так:
(TIME '12:25:30', TIME '14:30:00') OVERLAPS
(TIME '12:45:00', INTERVAL '2' HOUR)
Поскольку
временные
интервалы
в
данном
примере
пересекаются,
то
предикат
OVERLAPS
возвращает значение
true
.
Предикаты
для
вложенных
запросов
ALL
,
SOME
,
ANY
,
EXISTS
,
UNIQUE
,
DISTINCT
будут рассмотрены в разделе "Сложные запросы".
Предикат
MATCH
применяется для проверки сохранения ссылочной
целостности при модификации данных, т.е. при добавлении, изменении и
удалении записей.
Практическая работа 8
Выполнить следующие запросы к базе данных
airports.
1.
Информацию о самолетах и количестве рейсов (считать), отсортировать
по возрастанию .
2.
Вся информация об аэропортах с ременной зоной
Asia/Yakutsk.
3.
Информация о рейсах, совершенных
16.09.2016 до 16.00
.
4.
Информация
об
имеющихся
самолетах.
Отсортировать
по
убыванию
максимальной дальности полета.
5.
Номер бронирования и полную сумму бронирования диапазоне от 50000
до 90000 в порядке возрастания стоимости.
6.
Номер билета и полную сумму перелета до 15000 в порядке убывания
стоимости перелета.
7.
Всю информацию о рейсах, совершенных с 06.10.2016 по 10.10.2016.
Отсортировать по дате.
8.
Не
повторяющуюся
информацию
о
самолетах
с
максимальной
дальностью полета от 5000 до 10000. Упорядочить по названию самолета.
9.
Все аэропорты города Москва.
10. Вывести первые 3 рейса, совершенные 16.09.2016.
11. Все
рейсы,
совершенные
Cessna 208 Caravan.
Упорядочить по
номеру рейса.
12. Информацию о рейсах, где в кратком названии аэропорта вторая буква
«о». Упорядочить по времени вылета.
13. Рейсы, где номер рейса не равен
PG0402, PG0405
. Упорядочить по
времени вылета.
14. Все рейсы, отсортированные сначала по номеру (по возрастанию), затем
по
Статусу
(по
возрастанию),
затем
по
Аэропорту
отправления
(по
убыванию), затем по Времени отправления (по убыванию).
15. Название аэропорта, его временную зону, где название начинается на Ка.
16. Название аэропорта, его временную зону, где в названии присутствует
роз.
17. Название аэропорта, его временную зону, где в названии нет ни Ли, ни
Ри.
18. Все рейсы со статусом
On Time
и
Scheduled.
19. Все места, которые есть у
Sukhoi SuperJet-100
, отсортировать по
убыванию.
20. Все места Бизнесс класса самолета, у которого максимальная дальность
полета больше 3000.