Печатать книгуПечатать книгу

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

Сайт: Профильное обучение
Курс: Информатика. 10 класс (Повышенный уровень)
Книга: § 24. Использование SQL для построения запросов
Напечатано:: Гость
Дата: Понедельник, 6 Май 2024, 13:06

Оглавление

24.1. Язык запросов SQL

Мастер запросов и Конструктор — всего лишь визуальные инструменты для создания запросов. Любой запрос, созданный в Access с помощью визуальных инструментов, можно просмотреть в режиме SQL (пример 24.1). В этом режиме запрос отображается как запись на языке SQL (англ. Structured Query Language — структурированный язык запросов).

SQL является важнейшим компонентом технологии реляционных баз данных и поддерживается практически всеми системами управления БД. Любому профессионалу, как квалифицированному пользователю баз данных, так и прикладному программисту или администратору БД, необходимо знать SQL.

Некоторые возможности языка SQL:

1. Создание, изменение и удаление таблиц БД. 
2. Выборка информации из БД. 
3. Добавление записей в таблицу БД. 
4. Редактирование и удаление записей в таблице БД.

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

Достоинства языка SQL:

1. Декларативность — указывает, какие операции нужно выполнить. Способ их реализации выбирается автоматич  ески. 
2. Наличие международных стандартов.
3. Независимость от конкретной СУБД.

Недостатки языка SQL:

1. Стандарт языка сложен и объемен.
2. Возможность отступления от стандарта (многие компании вносят свои изменения в язык).

Синтаксис SQL близок к естественному английскому языку. Команды SQL выглядят как обычные предложения английского языка и просты в понимании.

Команда SQL состоит из операторов (ключевых слов) и идентификаторов (пример 24.2). Если в запросе несколько команд, то в конце команды ставится точка с запятой (;).  В Access одновременно можно выполнить только одну команду SQL, поэтому точку с запятой можно не ставить. Согласно общепринятому стилю для записи ключевых слов рекомендуется использовать прописные буквы.

Если идентификатор содержит пробелы, то он заключается в квадратные скобки (пример 24.3).

В Access используется интерактивная версия языка SQL — Microsoft Jet SQL. Чтобы создать запрос SQL в Access (пример 24.4), необходимо:

1) перейти в меню Создание;

2) выбрать Конструктор запросов;

3) закрыть окно Добавление таблицы;

4) выбрать Режим SQL;

5) в открывшемся окне ввести текст запроса и при необходимости сохранить запрос;

6) выполнить запрос, нажав   кнопку на Панели инструментов.

В Access реализованы следующие составные части языка SQL:

1. Язык определения данных (Data Definition Language — DDL). Используется для определения структуры базы данных.
2. Язык манипулирования данными (Data Manipulation Language — DML). Предоставляет возможность выборки информации из базы данных и ее преобразования.

Пример 24.1. Представление запроса на языке SQL.

Таблица БД:

Запрос в режиме Конструктор, который позволяет найти сеансы, стоимость билетов на которые не превышает 9 р. 50 к.:

Результат запроса (режим таблицы):

Запрос SQL:

 

Язык SQL был предложен компанией IBM в начале 70-х гг. для проверки возможностей реляционной модели БД.

Разработчики языка запросов SQL:

 

Дональд Чэмбэрлин
(Donald D. Chamberlin)

Рэй Бойс
(Ray Boyce)

Первый международный стандарт языка SQL был принят в 1989 г.

В настоящее время действует стандарт, принятый в 2003 г. (SQL:2003), с некоторыми модификациями и дополнениями, внесенными позже (SQL:2008, SQL:2011, SQL:2016).

SQL является непроцедурным языком программирования. Команды SQL могут выполняться непосредственно в интерактивном режиме, а также встраиваются в базовый язык программирования, которым может быть любой стандартный язык, например C++.

Пример 24.2. В запросе SQL из примера 24.1:

Операторы

SELECT — определяет поля, которые содержат запрашиваемые данные.

FROM — определяет таблицу, содержащую поля, указанные в SELECT.

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

Идентификаторы полей

Сеансы.Кинотеатр, Сеансы.Фильм, Сеансы.Время, Сеансы.Стоимость — содержат адрес (имя объекта БД) и название поля.

Пример 24.3. Синтаксис идентификаторов SQL.

Запрос:

SELECT Товары.[Номер склада], Товары.Наименование, Товары.Поставщик, Товары.Цена, Товары.Количество

FROM Товары

WHERE (((Товары.[Номер склада])=2))

Результат:

Пример 24.4. Создание запроса SQL в Access.

Выбор режима SQL:

Окно для ввода запроса SQL:

24.2. Определение структуры базы данных

С помощью операторов DDL языка SQL определяется структура БД, т. е. создаются объекты базы данных (пример 24.5) и задаются их свойства.

Назначение команд DDL определяется первым словом в записи команды:

  • CREATE (создать) — создает объект базы данных (таблицу, представление и др.);
  • ALTER (изменить) — изменяет объект;
  • DROP (удалить) — удаляет объект.

Синтаксис команды DDL:

<OPERATOR><OBJECT> <NAME> [параметры]

Синтаксис команды на создание таблицы базы данных:

CREATE TABLE <имя_таблицы>
  (<имя_поля> <тип_данных в поле>
  [<ограничения_ в поле>], ...)

Основные типы данных, используемые в DDL, в сравнении с типами данных в Access:

Тип

Тип данных в Access

COUNTER

Счетчик

INT

Длинное целое

SMALLINT

Целое

DECIMAL

Одинарное с плавающей точкой

FLOAT

Двойное с плавающей точкой

BIT

Логический

CHAR

Короткий текст (не более 255 символов)

NCHAR

Длинный текст

DATETIME

Дата и время

MONEY

Денежный

Основные ограничения SQL для данных в полях таблицы БД:

Ограничение

Описание

DEFAULT

Принимать значение по умолчанию

NOT NULL

Запрет на отсутствие значений

UNIQUE

Запрет повторов значений

PRIMARY KEY

Первичный ключ

Список ограничений в операторе CREATE TABLE может отсутствовать, поэтому в представлении синтаксиса он заключен в квадратные скобки.

В примере 24.6 приведены команды на создание и удаление таблицы базы данных.

В DDL реализована возможность изменения объектов БД — оператор ALTER. С помощью этого оператора можно изменять структуру таблицы, добавляя или удаляя поля, изменять тип поля, добавлять или удалять ограничения (пример 24.7).

Синтаксис команды на изменение таблицы БД:

ALTER TABLE <имя_таблицы> <действия по изменению>

Пример 24.5. Некоторые из объектов в DDL.

DATABASE

База данных

TABLE

Таблица

SCHEMA

Схема

CONSTRAINT

Ограничение

ATTRIBUTE

Свойство

VIEW

Представление

INDEX

Индекс

Пример 24.6. Создание и удаление таблицы базы данных с помощью запросов SQL.

1. Создание:

CREATE TABLE [Сеансы в мае] 
(Код INT PRIMARY KEY, 
Фильм VARCHAR(50) NOT NULL, 
Время TIME NOT NULL, 
Стоимость MONEY)

Команда создает таблицу «Сеансы в мае» следующей структуры:

Поля таблицы:

    • Код — целое (INT), первичный ключ таблицы (PRIMARY KEY).
    • Фильм — строка длиной до 50 символов (VARCHAR(50)), установлен запрет на отсутствие значений (NOT NULL).
    • Время — поле для хранения времени (TIME), установлен запрет на отсутствие значений (NOT NULL).
    • Стоимость — поле для хранения денежной суммы (MONEY).

2. Удаление:

   DROP TABLE [Сеансы в мае]  

Пример 24.7. Использование оператора ALTER для изменения таблицы БД.

1. Добавление поля:

ALTER TABLE [Сеансы в мае] 
ADD COLUMN [Наличие билетов] CHAR

В таблицу «Сеансы в мае» будет добавлено поле «Наличие билетов» с типом данных «Короткий текст». Длина текста ограничена значением по умолчанию (255 символов).

2. Обновление поля:

ALTER TABLE [Сеансы в мае] 
ALTER COLUMN [Наличие билетов] CHAR(10)

Для поля «Наличие билетов» таблицы «Сеансы в мае» будет изменено ограничение по длине текста (установлено ограничение 10 символов).

3. Удаление поля:

ALTER TABLE [Сеансы в мае] 
DROP COLUMN [Наличие билетов]

Поле «Наличие билетов» будет удалено из таблицы «Сеансы в мае».

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 'Мир'

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

Вопросы к параграфу

1. Как создать запрос в Access, не используя возможностей визуальных инструментов создания запросов?

2. Какие возможности в работе с БД имеются в языке SQL?

3. Какие составные части можно выделить в языке SQL?

4. Каков синтаксис написания идентификаторов, если в них присутствуют пробелы?

5. Для чего предназначены операторы языка DDL?

6. Какой оператор позволяет создавать таблицы БД?

7. С какими типами данных работают операторы языка DDL?

8. В чем разница между типом данных CHAR и VARCHAR?

9. Какие ограничения для данных в полях таблицы БД могут быть записаны в запросе SQL?

10. Использование какого оператора позволяет сделать выборку данных из БД?

11. Для чего предназначены операторы языка DML?

12. С помощью какого оператора можно добавлять в таблицу БД записи?

13. Какой знак используется, если в запросе SQL предполагается вывод данных из всех полей таблицы?

14. Какая ошибка допущена в запросе?

SELECT Дата, Имя from Заказы WHERE Имя = Никита;

15. Что будет результатом запроса?

SELECT * FROM Заказы WHERE Дата BETWEEN '2019-01-01' AND '2019-12-31'

16. В каком разделе запроса SQL задаются критерии сортировки записей?

17. Каким образом сортируются данные, если критерий сортировки не указан?

18. Как называются функции, которые работают с набором данных и возвращают в запросе SELECT итоговые значения?

19. Каким образом указывается в запросе SELECT набор данных, с которым работают агрегирующие функции?

20. Возможно ли использование нескольких агрегирующих функций в одном запросе?

Упражнения

    

1. Создайте базу данных «Туры.accdb».

1. Создайте новую пустую базу данных и введите ее имя.
2. Создайте таблицу базы данных с помощью SQL-запроса.
3. Введите следующую команду для создания таблицы:

CREATE TABLE Туры (
  Код INTEGER NOT NULL PRIMARY KEY,
  Страна VARCHAR(50) NOT NULL,
  Транспорт VARCHAR(20) NOT NULL,
  Цена MONEY NOT NULL)

4. Выполните эту команду. Проверьте, что таблица «Туры» действительно создана.

2. Добавьте в таблицу «Туры» записи (по одной):

INSERT INTO Туры
              VALUES (1, 'Австрия', 'автобус', 800);
INSERT INTO Туры
              VALUES (2, 'Норвегия', 'самолет', 1500);

Аналогично добавьте в таблицу еще несколько записей:

Код

Страна

Транспорт

Цена

3

Швеция

паром

900 р.

4

Германия

автобус

700 р.

5

Греция

самолет

1200 р.

6

Норвегия

автобус

800 р.

7

Германия

самолет

1500 р.

 

3. Выполните и сохраните запросы на выборку данных из таблицы «Туры»:

1. Отобразить все данные таблицы:

SELECT * FROM Туры

Посмотрите на результат.

2. Для всех записей отобразить только поля «Страна» и «Цена»:

SELECT Страна, Цена FROM Туры

3. Отобразить записи, содержащие данные о турах в Норвегию:

SELECT * FROM Туры WHERE Страна LIKE Норвегия

4. Составьте, выполните и сохраните запросы на выбор из таблицы «Туры»:

1. Значения полей «Страна», «Транспорт» и «Цена» для всех автобусных туров.

2. Составьте и выполните запрос, который выбирает из таблицы «Туры» значения всех полей для туров c ценой меньше 1000 р.

5. Проверьте работу запроса:

SELECT * FROM Туры ORDER BY Цена

Добавьте в конце предыдущего запроса ключевое слово DESC. Что изменилось? Сохраните результат.

6. Составьте, выполните и сохраните запросы на выбор из таблицы «Туры»:

1. Значения всех полей для туров c ценой больше 1000 р. и сортирует результаты по убыванию цены.

2. Минимальной цены для туров в Норвегию.

3. Тура с максимальной ценой на самолет.

7. Составьте, выполните и сохраните запросы для таблицы «Туры»:

1. Увеличение цены всех туров на 10 %.

2. Реализация скидки на билеты, так что цены всех туров на самолетах составляют 80 % от исходных.

8. Скопируйте таблицу «Туры», назвав копию «Туры2». Составьте и выполните запрос на удаление всех туров в Германию. Проверьте, что данные в таблице «Туры2» действительно изменились. Удалите таблицу «Туры2», которая больше не нужна, с помощью запроса.

9. Откройте базу данных «Аренда автомобилей.accdb». Составьте, выполните и сохраните запросы.

    1. Выбрать все автомобили в порядке года выпуска. Для тех, у которых год выпуска одинаковый — по убыванию стоимости аренды.
    2. Найти автомобили с объемом двигателя >3.
    3. Найти автомобили красного цвета.
    4. Найти автомобили с объемом двигателя =2 и арендой не более 300.
    5. Вывести информацию об автомобилях, год выпуска которых между 2000 и 2010.
    6. Вывести всех арендаторов, проживающих на улице Пономарева.
    7. Для каждого автомобиля определить стоимость техосмотра, которая составляет 70 % от стоимости аренды.
    8. Используя группировку, получить итоговую сумму аренды для каждой группы автомобилей с одинаковым видом топлива.
    9. Найти минимальную, максимальную и среднюю стоимость аренды для автомобилей с одинаковым типом кузова.

10. Выполните запрос на поиск всех взятых в аренду автомобилей.

SELECT *
FROM Автомобили
WHERE Код IN (SELECT ‘Автомобили_Код’ FROM Аренда);

11. Найдите всех арендаторов, которые не брали ни одну машину в аренду.