§ 24. Использование SQL для построения запросов

24.3. Манипулирование данными в таблице БД

Язык манипулирования данными DML включает операторы, изменяющие состояние данных. Этими операторами являются:

    • SELECT (выбрать) — выбор данных из таблиц.
    • INSERT (вставить) — добавление данных в таблицу.
    • UPDATE (обновить) — изменение данных в таблице.
    • DELETE (удалить) — удаление данных из таблицы.

INSERT

Синтаксис оператора INSERT:

INSERT INTO <имя таблицы>
[(<имя столбца>,...)]
VALUES (<значение поля>,…)

В разделе VALUES значения полей должны быть указаны в том порядке, в котором поля задавались при создании таблицы.

Список столбцов может отсутствовать. Если список не указан в операторе, то это означает, что список значений должен быть полным, т. е. содержать значения для всех полей таблицы (пример 24.8).

В Access одновременно можно добавить в таблицу только одну запись.

SELECT

Оператор SELECT — один из наиболее важных и используемых операторов DML, который имеет наиболее сложную структуру среди всех операторов. Он позволяет выбрать данные из БД и преобразовывать к нужному виду полученные результаты. Результатом выполнения оператора SELECT всегда является таблица.

Синтаксис оператора SELECT:

SELECT <список атрибутов>
FROM <соединяемые отношения>
[WHERE <условия выборки>]
[ORDER BY <критерии сортировки>]
[GROUP BY <критерии группировки>
[HAVING <условия отбора групп>]

В примерах 24.9—24.13 рассмотрены различные варианты использования оператора SELECT по отношению к таблице «Сеансы» (пример 24.1).

В качестве условия выборки в разделе WHERE оператора SELECT можно использовать логические выражения, использующие поля таблиц, константы, знаки сравнения, союзы AND и OR, отрицание NOT.

При записи условий используются

Для чисел и дат

Для строк
    • <, >, >=, <=
    • IN, BETWEEN — для указания диапазона
    • LIKE 'строка' — сравнение с образцом

Использование различных условий для выборки данных рассмотрено в примере 24.10.

В качестве критерия сортировки в разделе ORDER BY оператора SELECT указываются имена полей, по которым требуется упорядочить записи в таблице. Сортировку можно производить по нескольким полям. Принцип работы такой сортировки аналогичен многоуровневой сортировке в Excel. Сначала записи располагаются в том порядке, который определен для первого поля. Порядок, определенный для следующего поля, упорядочивает только те записи, которые имеют одинаковые значения в первом поле. Дальше сортировка применяется только к тем записям, которые имеют одинаковые значения как в первом поле, так и во втором.

По умолчанию записи таблицы сортируются по возрастанию (ASC), для сортировки в обратном порядке необходимо добавить ключевое слово DESC (пример 24.11).

При составлении запросов посредством оператора SELECT для получения некоторой статистической информации (суммы, количества, среднего значения и т. д.) используются агрегирующие функции. Это функции, которые работают с набором данных и возвращают итоговое значение. Основные из них:

Функция

Описание

COUNT

Возвращает количество значений в указанном столбце

SUM

Возвращает сумму значений в указанном столбце

AVG

Возвращает среднее значение в указанном столбце

MIN

Возвращает минимальное значение в указанном столбце

MAX

Возвращает максимальное значение в указанном столбце

Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к данным любого типа, а SUM и AVG используются только для данных числового типа.

Применять агрегирующие функции можно ко всем строкам таблицы (пример 24.12), строкам, определенным в разделе WHERE (пример 24.13) или к группам строк.  В одном запросе можно указывать несколько агрегирующих функций, записав их через запятую.

Синтаксис агрегирующих функций:

<Функция>(<выражение>)

В качестве аргумента агрегирующей функции чаще всего используется название поля таблицы, над значениями которого проводятся вычисления. Также допускается в качестве аргумента использовать выражения, включающие в себя произвольную комбинацию названий полей, констант, функций, объединенных арифметическими операциями.

В примере 24.14 рассмотрено применение агрегирующих функций COUNT, SUM и AVG в операторе SELECT по отношению к таблице «Сеансы в мае»:

В разделе GROUP BY оператора SELECT записывается имя поля, по которому должна осуществляться группировка данных. В этом случае значение агрегирующей функции вычисляется для каждой группы (пример 24.15).

Результаты запросов можно использовать в других запросах — получается вложенный запрос (пример 24.16).

UPDATE

Синтаксис оператора UPDATE:
UPDATE <Имя таблицы>
SET <имя поля> = <выражение>
[WHERE <условие обновления>]

С помощью одного оператора UPDATE могут быть заданы значения для любого количества столбцов. Однако в одном и том же операторе UPDATE можно вносить изменения в каждый столбец указанной таблицы только один раз. При отсутствии раздела WHERE будут обновлены все строки таблицы. В примере 24.17 приведен запрос на обновление с указанием в разделе WHERE, какие данные следует обновить.

DELETE

Используется для удаления записей из таблиц БД (пример 24.18).

Синтаксис оператора DELETE:
DELETE FROM <Имя таблицы>
WHERE <условие удаления>

Если раздел WHERE отсутствует, удаляются все строки из таблицы.

Пример 24.8. Добавление данных в таблицу.

INSERT INTO [Сеансы в мае] 
VALUES (1, 'Джентельмены', '21:00', 8.50)

Значение строки в этом случае можно вводить без кавычек, т. к. в строке нет пробелов:

   VALUES (1, Джентельмены, '21:00', 8.50)   

Результат:

Пример 24.9. Запрос на выбор всех данных из таблицы.

   SELECT * FROM Сеансы   

Результат:

Пример 24.10. Запросы на выбор данных, удовлетворяющих некоторому условию.

1. Выбрать данные из полей «Кинотеатр», «Фильм», «Время», «Стоимость» таблицы «Сеансы» для записей, в которых стоимость билета меньше 10 р.

SELECT Кинотеатр, Фильм, Время, Стоимость
FROM Сеансы
WHERE Стоимость < 10.00

Результат:

2. Выбрать данные из полей «Кинотеатр», «Фильм», «Время», «Стоимость» таблицы «Сеансы» для записей, в которых стоимость билета от 9 до 10 р.

SELECT Кинотеатр, Фильм, Время, Стоимость
FROM Сеансы
WHERE Стоимость BETWEEN 9 AND 10

Результат:

3. Выбрать данные из полей «Фильм», «Время», «Стоимость» таблицы «Сеансы» для записей, у которых значение поля «Кинотеатр» совпадает со значением 'Аврора'.

SELECT Фильм, Время, Стоимость
FROM Сеансы
WHERE Кинотеатр LIKE 'Аврора'

Результат:

4. Выбрать данные из полей «Фильм», «Время», «Стоимость» таблицы «Сеансы» для записей, у которых значение поля «Кинотеатр» заканчивается на 'р'.

SELECT Кинотеатр, Фильм, Время
FROM Сеансы
WHERE Кинотеатр LIKE '*р'

Результат:

Пример 24.11. Сортировка данных в таблице.

1. Выполнить сортировку данных в таблице «Сеансы» по полю «Время» в порядке возрастания.

SELECT Кинотеатр, Фильм, Время
FROM Сеансы
ORDER BY Время

Результат:

2. Выполнить сортировку данных в таблице «Сеансы» по полю «Кинотеатр» в порядке убывания, а по полю «Время» — в порядке возрастания.

SELECT Кинотеатр, Фильм, Время
FROM Сеансы
ORDER BY Кинотеатр DESC, Время ASC

Результат:

Пример 24.12. Применение агрегирующих функций ко всем строкам в запросе на выборку.

Найти в таблице «Сеансы» сеанс с минимальной стоимостью билета.

SELECT MIN (Стоимость) 
AS [Минимальная стоимость] 
FROM Сеансы

Результат:

Пример 24.13. Применение агрегирующих функций к определенным строкам таблицы в запросах на выборку.

Найти в таблице «Сеансы» самый поздний сеанс со стоимостью билета от 6 до 9 р.

SELECT MAX (Время) AS [Позний сеанс] FROM Сеансы
WHERE Стоимость BETWEEN 6 AND 9

Результат:

Пример 24.14. Применение агрегирующих функций в запросах на выборку.

1. SUM.

Найти в таблице «Сеансы в мае» суммарную стоимость билетов.

SELECT SUM (Стоимость) 
AS [Суммарная стоимость] 
FROM [Сеансы в мае]

Результат:

2. AVG.

Найти в таблице «Сеансы в мае» среднюю стоимость билетов с двумя цифрами после запятой.

SELECT ROUND(AVG (Стоимость), 2)
AS [Средняя стоимость] 
FROM [Сеансы в мае]

Результат:

Пример 24.15. Применение агрегирующих функций к группам строк в запросах на выборку.

Найти в таблице «Сеансы в мае» суммарное количество билетов для каждого фильма.

SELECT Фильм,
SUM ([Количество билетов])
AS [Кол_билетов]
FROM [Сеансы в мае] GROUP BY Фильм

Результат:

Пример 24.16. Вложенный запрос на выборку.

SELECT * FROM Туры 
WHERE Цена = (SELECT MIN(Цена) 
FROM Туры 
WHERE Страна = 'Норвегия')

Запрос возвращает данные из таблицы «Туры» о самом дешевом туре в Норвегию.

Пример 24.17. Запросы на обновление таблицы «Сеансы в мае».

UPDATE [Сеансы в мае]
SET [Количество билетов] = [Количество билетов] - 5
WHERE Кинотеатр LIKE 'Мир'

В результате выполнения запроса количество билетов на каждый из сеансов в кинотеатре «Мир» уменьшится на 5.

Пример 24.18. Запрос на удаление данных из таблицы «Сеансы в мае».

DELETE FROM [Сеансы в мае]
WHERE Кинотеатр LIKE 'Мир'

В результате выполнения запроса из таблицы будут удалены все сеансы в кинотеатре «Мир».