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

Основные типы запросов

Запрос — объект базы данных, который используется для извлечения информации из одной или нескольких таблиц или для выполнения определенных действий с данными.

По способу формирования запросы можно разделить на два вида:

  • запросы по образцу, или QBE-запросы (Query By Example), при создании которых необходимо указать параметры запроса в окне конструктора, задавая образцы для поиска информации;
  • структурированные запросы, или SQL-запросы (Structured Query Language), для создания которых необходимо описать запрос с помощью языка запросов SQL.

В действительности любой запрос в Microsoft Access реализуется с помощью языка SQL. И хотя большинство запросов можно создавать в режиме конструктора, используя возможности запроса по образцу, каждый созданный запрос хранится в виде инструкции SQL. При создании запроса по образцу Microsoft Access автоматически формирует соответствующий SQL-запрос. Можно просмотреть инструкцию SQL для существующего запроса и внести в нее изменения. В этом случае автоматически будет обновляться определение соответствующего запроса по образцу в режиме конструктора.

По результатам действий и особенностям выполнения запросы можно разделить следующим образом:

  • запросы на выборку, используемые для того, чтобы ото брать и представить в удобном виде данные из одной или нескольких таблиц (или запросов);
  • параметрические запросы, которые позволяют задавать конкретные условия отбора непосредственно при выполнении запроса;
  • перекрестные запросы, которые позволяют провести группировку и вычисления, а также представить данные в компактном виде, близком к сводной таблице Microsoft Excel;
  • запросы на изменение, которые позволяют изменять таблицы базы данных: обновлять их, дополнять новыми записями, удалять некоторые записи. Они могут использоваться для создания новых реальных таблиц, которые,в отличие от обычных выборок, в дальнейшем существуют уже независимо от тех таблиц базы данных, которые были использованы для их построения.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

§ 24. Использование 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. В запросе SQL из примера 24

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

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

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

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

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

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

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

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

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

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

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

Перекрестный запрос

Перекрестный запрос позволяет представить данные в виде таблицы, в которой отображаются результаты вычислсний (сумма, количество записей, среднее значение и т. ), выполненных по данным из какого-либо поля таблицы. Результаты вычислений группируются по двум наборам данных, один из которых располагается в лервом столбце таблицы, выступая в качестве заголовков строк, а второй — в верхней строке, образуя заголовки столбцов. Таким образом, при создании перекрестного запроса главная задача — определить роль и местопо ложение каждого поля таблицы.

Пои создании перекрестного з апроса в режиме конструктора необходимо указать поля, значения которых будут заголовками столбцов и строк, а также поле, значения которого следует использовать в вычислениях. Для этого по команде Запрос / Перекрестный в бланк запроса добавляются строки Групповая операция и Перекрестная таблица. Строка Перекрестная таблица используется для определения роли поля в перекрестном запросе (Заголовки строк, Заголовки столбцов, Значение). Строка Групповая операция позволяет указать, какие именно вычисления необходимо произвести в перекрестном запросе.

Запрос на изменение

Запросом на изменение называют запрос, который за одну операцию изменяет или перемещает несколько записей. Существует четыре типа запросов на изменение.

  • На удаление записи. Запрос на удаление удаляет группу записей из одной или нескольких таблиц. Например, запрос на удаление позволяет удалить записи о товарах, поставки которых прекращены или на которые нет заказов. С помощью запроса на удаление можно удалять только всю запись, а не отдельные поля внутри нее.
  • На обновление записи. Запрос на обновление вносит общие изменения в группу записей одной или нескольких таблиц. Например, на 10 процентов поднимаются цены на все молочные продукты или на 5 процентов увеличивается зарплата сотрудников определенной категории. Запрос на обновление записей позволяет изменять данные в существующих таблицах.
  • На добавление записей. Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц. Например, появилось несколько новых клиентов, а также база данных, содержащая сведения о них. Чтобы не вводить все данные вручную, их можно добавить в таблицу «Клиенты».
  • На создание таблицы. Запрос на создание таблицы создает новую таблицу на основе всех или части данных из одной или нескольких таблиц. Запрос на создание таблицы полезен при создании таблицы для экспорта в другие базы данных Microsoft Access или при создания архивной таблицы, содержащей старые записи.

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

Запрос на создание таблицы

Запрос на создание таблицы используется для сохранения результата запроса. Этот запрос основан на запросе на выборку, но в отличие от него сохраняет таблицу с результатами запроса. Необходимость в запросе на создание таблицы возникает при построении запроса на обновление полей таблицы с использованием операций группировки данных. В этом случае кроме обновляемой таблицы требуется таблица — источник данных.

При открытии запроса будут выданы предупреждения о создании таблицы и о количестве добавляемых записей. При последующих открытиях — выдается предупреждение об удалении старой таблицы Число студентов.

Запрос на обновление

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

Первоначально Запрос на обновление создается как Запрос на выборку, а затем в окне конструктора запросов превращается в запрос на обновление с помощью кнопки Вид запроса (Обновление) на панели инструментов или команды меню Запрос / Обновление. После выполнения этой команды в бланке запроса появляется строка Обновление.

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

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

После выполнения команды Запрос / Запуск или нажатия соответствующей кнопки открывается диалоговое окно с сообщением о числе обновляемых записей и вопросом о продолжении операции обновления.

Заметим, если в схеме данных установлен параметр Обеспечение целостности данных и Каскадное обновление связанных полей, то при обновлении ключевых полей результат зависит от взаимосвязей обновляемой таблицы с другими таблицами.

Запрос на добавление

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

Первоначально запрос на добавление создается как запрос на выборку из одной или нескольких взаимосвязанных таблицах. Затем в окне конструктора запросов он превращается в запрос на добавление с помощью кнопки Вид запроса (Добавление) на панели инструментов или команды меню Запрос / Добавление. При этом открывается диалоговое окно Добавление.

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

В этом окне в поле Имя таблицы вводится или выбирается имя таблицы, в которую надо добавить записи.

После выполнения этой команды в бланке запроса появляется строка Добавление.

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

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

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

Для добавления записей надо нажать кнопку Запуск на панели инструментов. Открывается диалоговое окно с сообщением о числе обновляемых записей и вопросом о продолжении операции обновления.

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

Вычисления в запросе

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

Имя вычисляемого поля — Выражение1 — становится заголовком столбца в таблице с результатами выполнения запроса. Это имя можно изменить.

Для вычисляемых полей допускается сортировка, задание условий отбора и расчет итоговых значений, как и для любых других полей.

Для создания вычисляемого выражения можно использовать построитель выражений, окно которого представлено на рисунке

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

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

В левом списке отображаются папки, содержащие объекты базы данных, встроенные и определенные пользователем функции, константы, операторы и общие выражения. Для просмотра доступных элементов определенной папки необходимо выполнить двойной щелчок мышью по папке. При этом в среднем поле будет выведен список элементов или категорий элементов выбранной папки. В правом поле выводится список значений (если они существуют) для категории элементов, выбранной в среднем поле. Для вставки элемента в поле выражения следует выбрать его в нижней части окна построителя выражений и щелкнуть по кнопке Вставить или же выполнить двойной шелчок мышью по выбранному элементу. Завершив создание выражения необходимо щелкнуть по кнопке ОК. Microsoft Access вставит созданное выражение в ту позицию бланка запроса, из которой был вызван построитель выражений.

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

  • групповая функция Sum вычисляет сумму всех значений заданного поля в каждой группе;
  • групповая функция Avg вычисляет среднее арифметическое всех значений данного поля в каждой группе;
  • групповая функция Min (Max) возвращает наименьшее (наибольшее) значение, найденное в этом поле в каждой группе;
  • групповая функция Count определяет количество записей в каждой группе и др.

Для создания итогового запроса в бланк запроса по команде Вид / Групповые операции (кнопка на панели инструментов) добавляется строка Групповая операция, которая позволяет указать, по какому полю должна быть выполнена группировка записей, и какие вычисления итоговых значений по группам данных необходимо провести.

SQL — Урок 3. Создание таблиц и наполнение их информацией

Все значения полей обязательны для заполнения, значит надо добавить тип NOT NULL.

Первый столбец, как вы помните из концептуальной модели нашей БД, является первичным ключом (т. его значения уникальны, и они
однозначно идентифицируют запись). Следить за уникальностью самостоятельно можно, но не рационально. Для этого в SQL есть
специальный атрибут — AUTO_INCREMENT, который при обращении к таблице на добавление данных высчитывает
максимальное значение этого столбца, полученное значение увеличивает на 1 и заносит его в столбец. Таким образом, в этом столбце
автоматически генерируется уникальный номер, а следовательно тип NOT NULL излишен. Итак, присвоим атрибут столбцу с первичным
ключом:

Итак, таблица готова, и ее окончательный вариант выглядит так:

Теперь разберемся со второй таблицей — topics (темы). Рассуждая аналогично, имеем следующие поля:

id_topic int (10) AUTO_INCREMENT
topic_name varchar(100) NOT NULL
id_author int (10) NOT NULL
PRIMARY KEY (id_topic)

FOREIGN KEY (имя_столбца_которое_является_внешним_ключом) REFERENCES имя_таблицы_родителя (имя_столбца_родителя);

Укажем, что id_author — внешний ключ:

Обратите внимание, внешних ключей у таблицы может быть несколько, а первичный ключ в MySQL может быть только один. В первом уроке мы удалили нашу БД forum, пришло время создать ее вновь.

Запускаем сервер MySQL (Пуск — Программы — MySQL — MySQL Server 5. 1 — MySQL Command Line Client), вводим пароль, создаем
БД forum (create database forum;), выбираем ее для использования (use forum;) и создаем три наших таблицы:

Обратите внимание, одну команду можно писать в несколько строк, используя клавишу Enter (MySQL автоматически подставляет
символ новой строки ->), и только после разделителя (точки с запятой) нажатие клавиши Enter приводит к выполнению запроса.

Помните, если вы сделали что-то не так, всегда можно удалить таблицу или всю БД с помощью оператора DROP. Исправлять что-то
в командной строке крайне неудобно, поэтому иногда (особенно на начальном этапе) проще писать запросы в каком-нибудь редакторе,
например в Блокноте, а затем копировать и вставлять их в черное окошко.

Итак, таблицы созданы, чтобы убедиться в этом вспомним о команде show tables:

Теперь становятся понятны значения всех полей структуры, кроме поля DEFAULT. Это поле значений по умолчанию. Мы могли бы
для какого-нибудь столбца (или для всех) указать значение по умолчанию. Например, если бы у нас было поле с названием
«ЖенатыЗамужем» и типом ENUM (‘да’, ‘нет’), то было бы разумно сделать одно из значений значением по умолчанию. Синтаксис был бы следующий:

married enum (‘да’, ‘нет’) NOT NULL default(‘да’)

это ключевое слово пишется через пробел после указания типа данных, а в скобках указывается значение по умолчанию.

Но вернемся к нашим таблицам. Теперь нам необходимо внести данные в наши таблицы. На сайтах, вы обычно вводите информацию в
какие-нибудь html-формы, затем сценарий на каком-либо языке (php, java. ) извлекает эти данные из формы и заносит их в БД. Делает он это посредством SQL-запроса на внесение данных в базу. Писать сценарии на php мы пока не умеем, а вот отправлять SQL-запросы
на внесение данных сейчас научимся.

Для этого используется оператор INSERT. Синтаксис можно использовать двух видов. Первый вариант используется
для внесения данных во все поля таблицы:

INSERT INTO имя_таблицы VALUES (‘значение_первого_столбца’,’значение_второго_столбца’,. , ‘значение_последнего_столбца’);

Второй вариант используется для внесения данных в некоторые поля таблицы:

INSERT INTO имя_таблицы (‘имя_столбца’, ‘имя_столбца’) VALUES (‘значение_первого_столбца’,’значение_второго_столбца’);

Если бы у нас были поля с типом NULL, т. необязательные для заполнения, мы бы тоже могли их проигнорировать. А вот если
попытаться оставить пустым поле со значением NOT NULL, то сервер выдаст сообщение об ошибке и не выполнит запрос. Кроме того,
при внесении данных сервер проверяет связи между таблицами. Поэтому вам не удастся внести в поле, являющееся внешним ключом, значение,
отсутствующее в связанной таблице. В этом вы убедитесь, внося данные в оставшиеся две таблицы.

Но прежде внесем информацию еще о нескольких пользователях. Чтобы добавить сразу несколько строк, надо просто перечислять
скобки со значениями через запятую:

Итак, у нас есть 3 таблицы, в которых есть данные. Встает вопрос — как посмотреть, какие данные хранятся в таблицах. Этим
мы и займемся на следующем уроке.

Предыдущий урок
Вернуться в раздел
Следующий урок

Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.