В СУБД Access применяются два типа запросов: QBE – запрос по образцу и SQL (Structured Query Language)- язык структурированных запросов. Запрос по образцу формируется путем заполнения специального бланка запроса в окне "Конструктора запросов". SQL – запросы создаются программистами из последовательности SQL – инструкций . SQL формируется, как правило, программистами на бланке запроса, который открывается командой "Конструктор запросов" на вкладке "Создание" и выбирается "Режим SQL" из меню Вид. Язык SQL предназначен для работы с данными, т.е. для создания, модификации и управления данными в реляционных БД.
Следует отметить, что существует несколько режимов запросов SQL (запросов в режиме ANSI-89 SQL и ANSI-92 SQL), которые соответствуют стандартам ANSI-89 SQL и ANSI-92 SQL.
Инструкции содержат описание набора данных на языке SQL. Инструкции SQL состоят из предложений (SELECT, FROM, WHERE и т.д.). Предложения на языке SQL состоят из терминов (операторов или команд, идентификаторов, констант и т.д.). Инструкция начинается оператором (одной из команд SELECT, CREATE, INSERT, UPDATE, DELETE и т.д.) и заканчивается точкой с запятой. Основные операторы SQL: SELECT, FROM и WHERE.
Например, инструкция SQL:
SELECT Студенты.КодСтудента
FROM Студенты;
состоит из предложения "SELECT Студенты.КодСтудента" и предложения "FROM Студенты".
Предложение SELECT содержит оператор SELECT и идентификатор "Студенты.КодСтудента". Здесь полное имя поля "КодСтудента" предваряется именем таблицы "Студенты" базы данных. SELECT - определяет поле, которое содержит требуемые данные. Предложение FROM состоит из оператора FROM и идентификатора "Студенты". FROM - определяет таблицу, которая содержат поля, указанные в предложении SELECT.
Необходимо отметить, что при формировании запроса на языке SQL необходимо учитывать его синтаксис. Несмотря на то, что синтаксис языка SQL основан на синтаксисе английского языка, но для различных СУБД синтаксис версий языка SQL может различаться.
Существует несколько типов запросов : на выборку, на обновление, на добавление и на удаление записей, перекрестный запрос, создание и удаление таблиц, соединение таблицы и т.д. Наиболее распространенным является запрос на выборку. Запросы на выборку используются для отбора нужной пользователю информации, содержащейся в таблицах. Они создаются только для связанных таблиц.
Чтобы посмотреть SQL – запросы на выборку в СУБД Access 2003 или 2007 необходимо в активном окне проектирования запроса по образцу (рис. 1) выполнить команду Вид/режим SQL.
Рис. 1.
Получим инструкцию SQL (SELECT) на выборку данных из БД Access 2003 по критерию успеваемости студентов "Оценка=5" (рис. 2).
Рис. 2.
Как следует из инструкции SELECT (рис. 1), она описывает набор данных на языке SQL: SELECT - определяет имена полей, предваряемые именами таблиц, в которых содержатся данные; FROM - определяет таблицы и их взаимосвязи через ключевые поля таблиц (для этого используется конструкция INNER JOIN ... ON), на основе которых отбираются данные; WHREME - определяет условия отбора полей; ORDER BY - определяет способ сортировки по возрастанию (по умолчанию выполняется сортировка по возрастанию) значений поля "Фамилия" таблицы "Студенты".
Как следует из инструкции на выборку данных из БД, язык SQL описывает, что необходимо получить из базы данных, при этом исполнение возлагается на СУБД, так как язык SQL не имеет своих средства управления выполнением программы.
Сравнение Microsoft Access SQL и ANSI SQL z. Microsoft Access SQL в основном отвечает стандарту ANSI-89 (уровень 1) z. Некоторые средства ANSI SQL не используются в Microsoft Access SQL z. Microsoft Access SQL использует зарезервированные слова и средства, не поддерживаемые ANSI SQL
Расширенный синтаксис z. В Access 2000 (MS Jet 4. 0) внесены расширения, приближающие язык к стандарту ANSI SQL-92 - режим доступен только при использовании MS OLE DB Provider для Jet
Другие правила применяются для конструкции Between. . . And, которая имеет следующий синтаксис: выражение 1 Between значение 1 And значение 2 В языке Microsoft Access SQL значение 1 может быть больше, чем значение 2; в языке ANSI SQL значение 1 должно быть меньше значения 2 или равно ему.
подстановочные знаки z В языке Microsoft Access SQL при использовании оператора Like поддерживаются как подстановочные знаки языка ANSI SQL, так и подстановочные знаки (Подстановочные знаки. Эти знаки используют в запросах и выражениях для включения всех записей, имен файлов или других элементов, которые содержат определенные знаки или отвечают определенному образцу.), относящиеся к Microsoft Access. Одновременное использование подстановочных знаков ANSI и Microsoft Access невозможно. Допускается использование только одного набора знаков, их нельзя смешивать.
Возможности языка ANSI SQL, не поддерживаемые в языке Microsoft Access SQL z. Инструкция TRANSFORM обеспечивает поддержку перекрестных запросов z (Перекрестный запрос. Запрос, в котором подсчитывается сумма, среднее, число значений или выполняются другие статистические расчеты, после чего результаты группируются в виде таблицы по двум наборам данных, один из которых определяет заголовки столбцов, а другой заголовки строк.).
Возможности языка ANSI SQL, не поддерживаемые в языке Microsoft Access SQL z. Использование предложения LIMIT TO nn ROWS для ограничения числа строк, возвращаемых запросом. z. Предусмотрены дополнительные статистические функции SQL, такие как St. Dev и Var. P
Возможности языка ANSI SQL, не поддерживаемые в языке Microsoft Access SQL z. Для определения параметров запроса (Запрос с параметрами. Запрос, в котором одно или несколько значений, определяющих условия отбора, вводятся в интерактивном режиме пользователем. Запрос с параметрами не является отдельным типом запроса; это функциональное расширение запросов на выборку.) используется объявление PARAMETERS.
Синтаксис команды SELECT [предикат] { * | таблица. * | [таблица. ]поле_1 [, [таблица. ]поле_2 [, . . . ]]} FROM выражение [, . . . ]
Аргументы инструкции SELECT Предикат Один из следующих предикатов отбора: ALL, DISTINCTROW или TOP. Предикаты используются для ограничения числа возвращаемых записей. Если они отсутствуют, по умолчанию используется предикат ALL.
Предикаты ALL, DISTINCTROW, TOP z. SELECT ]] FROM таблица z. ALL - Отбираются все записи, соответствующие условиям, заданным в инструкции SQL.
Предикат DISTINCT - исключает записи, которые содержат повторяющиеся значения в отобранных полях. Результирующий набор данных необновляемый
Предикат DISTINCTROW - Опускает данные, основанные на целиком повторяющихся записях, а не отдельных повторяющихся полях. Предикат DISTINCTROW игнорируется, если запрос содержит только одну таблицу или все поля всех таблиц.
DISTINCTROW z. SELECT DISTINCTROW сотрудник. фио, расписание. код_предмета z. FROM сотрудник INNER JOIN расписание z. ON сотрудник. код_сотрудника = расписание. код_сотрудника;
DISTINCT SELECT DISTINCT СОТРУДНИК. ФИО, расписание. код_предмета FROM СОТРУДНИК INNER JOIN расписание ON СОТРУДНИК. Код_сотрудника = расписание. код_сотрудника;
Если пользоваться табличным языком как построителем запросов на SQL, то для включения в запрос DISTINCT надо для свойства запроса «уникальные значения» выбрать значение «да» , а для включения в запрос DISTINCTROW надо выбрать значение «да» для свойства запроса «уникальные записи» .
Предикат TOP z. TOP n - Возвращает определенное число записей, находящихся в начале или в конце диапазона, описанного с помощью предложения ORDER BY.
Пример. Отобрать 5 самых многочисленных кафедр SELECT TOP 5 кафедра. [Наименование_ кафедры_полное], Count(сотрудники. Код_сотрудника) AS [Число сотрудников] FROM кафедра INNER JOIN сотрудники ON кафедра. Код_кафедры = сотрудники. Код_кафедры GROUP BY кафедра. [Наименование_ кафедры_полное] ORDER BY Count(сотрудники. Код_сотрудника) DESC;
WITH OWNERACCESS OPTION Используется в многопользовательской среде с составе защищенной рабочей группы, для предоставления пользователю, работающему с запросом, разрешений, соответствующих разрешениям владельца запроса.
Аргументы инструкции SELECT поле_1, поле_2 - имена полей, из которых должны быть отобраны данные. Если включить несколько полей, они будут извлекаться в указанном порядке.
Аргументы инструкции SELECT Псевдоним_1, псевдоним_2 - имена, которые станут заголовками столбцов вместо исходных названий столбцов в таблице.
Примеры использования псевдонима для задания имени вычисляемого поля Пример 1 SELECT сотрудник. ФИО, [оклад]*0. 5 AS Премия FROM сотрудник; Пример 2 SELECT Avg(сотрудник. оклад) AS Средний_оклад FROM сотрудник;
Аргументы инструкции SELECT Внешняя. База. Данных имя базы данных, которая содержит таблицы, указанные с помощью аргумента выражение, если они не находятся в текущей базе данных.
Предложение FROM SELECT список. Полей FROM выражение z Выражение - выражение, определяющее одну или несколько таблиц, откуда извлекаются данные. Это выражение может быть именем отдельной таблицы, именем сохраненного запроса или результатом операции INNER JOIN, LEFT JOIN или RIGHT JOIN.
Совместная обработка нескольких (3 -х) таблиц SELECT сотрудник. ФИО, предмет. [наименование предмета краткое] FROM сотрудник INNER JOIN (предмет INNER JOIN [владение предметами] ON предмет. [Код предмета] = [владение предметами]. [код дисциплины]) ON сотрудник. Код = [владение предметами]. [код сотрудника];
конструкция Between. . . And выражение 1 BETWEEN выражение 2 AND выражение 3 z. В Microsoft Access SQL выражение 2 может быть больше, чем выражение 3, а в ANSI SQL - нет.
Предикат Like Символы шаблона Разные символы шаблона используются с предикатом Like. Символ шаблона ANSI SQL MS Access SQL z Любой один символ? _ (подчеркивание) z Любая группа любых символов * % z Любой одиночный знак, входящий в список_знаков [список_знаков] отсутствует z Любой одиночный знак, не входящий в список_знаков [!список_знаков] отсутствует
z. Две последние возможности - только для Access 2000 z. В Access 2000 в режиме ANSI SQL-92 возможно использование подстановочных знаков ANSI z. В одном запросе смешивать знаки нельзя
Предложение GROUP BY z. SELECT список. Полей FROM таблица WHERE условие. Отбора z группируемые. Поля - имена полей (до 10), которые используются для группировки записей. Порядок имен полей в аргументе группируемые. Поля определяет уровень группировки для каждого из этих полей.
Предложение GROUP BY z Используйте предложение WHERE для исключения записей из группировки, а предложение HAVING для применения фильтра к записям после группировки. z При использовании предложения GROUP BY все поля в списке полей инструкции SELECT должны быть либо включены в предложение GROUP BY, либо использоваться в качестве аргументов статистической функции SQL.
Пример. Кафедры, на которых работает более 5 сотрудников. SELECT сотрудник. [Код кафедры], Count(сотрудник. ФИО) AS [Число_сотрудников] FROM сотрудник GROUP BY сотрудник. [Код кафедры] HAVING (((Count(сотрудник. ФИО))>5));
Задача Есть таблицы «фирмы» , «сотрудники» и «аттестации» . Надо определить, сколько аттестованных сотрудников есть на каждой фирме (один сотрудник может быть аттестован по нескольким ПП).
Инструкция SELECT. . . INTO Синтаксис z. Создает запрос на создание таблицы. SELECT поле_1[, поле_2[, . . . ]] INTO новая. Таблица FROM источник
Запрос на объединение (пример 1) SELECT Название, Город FROM Поставщики UNION SELECT Название, Город FROM Клиенты ORDER BY Город;
Запрос на объединение (пример 2) SELECT Название, Город FROM Поставщики UNION ALL SELECT Название, Город FROM Клиенты; - UNION ALL обеспечивает возвращение всех записей, в том числе повторяющихся
Создание подчиненного запроса с использованием построителя запросов QBE Если подчиненный запрос используется для определения условий для поля, введите инструкцию SELECT в ячейку строки Условие отбора в столбце этого поля. Инструкцию SELECT необходимо заключить в круглые скобки.
Инструкция DELETE z Запрос на удаление удаляет записи целиком, а не только содержимое указанных полей. Чтобы удалить данные конкретного поля, создайте запрос на обновление записей, который заменяет имеющиеся значения на значения Null z Запрос на удаление без заданных условий поиска удалит все записи из таблицы. В отличии от команды DROP структура таблицы и все свойства сохраняются
z. Если задано «каскадное удаление» , то удалятся все связанные записи z. Удаленные записи нельзя восстановить
Создание таблицы. Синтаксис команды CREATE TABLE таблица (поле 1 тип [(размер)] [индекс1] [, поле 2 тип [(размер)] [индекс2] [, . . . ]] [, CONSTRAINT индекс_нескольких_полей [, . . . ]])
Создание таблицы. Синтаксис команды в Access Существенно отличается от стандарта: zреализованы не все возможности стандарта SQL-92 zвключены новые конструкции zотличается синтаксис
TEMPORARY Временная (TEMPORARY) таблица доступна только в том сеансе, где эта таблица была создана. По завершении данного сеанса она автоматически удаляется. Временные таблицы могут быть доступны для нескольких пользователей.
WITH COMPRESSION z. Использование атрибута WITH COMPRESSION допускается только для типов данных CHARACTER и MEMO. z. Компенсирует последствия перехода к формату представления знаков Юникод
Корректировка структуры таблицы ALTER TABLE таблица {ADD {COLUMN тип поля[(размер)] | ALTER COLUMN тип поля[(размер)] | CONSTRAINT индекс_набора_полей} | DROP {COLUMN поле I CONSTRAINT имя_индекса} }
z Размер поля в знаках задается только для полей с типом данных TEXT и BINARY z ADD COLUMN - для добавления в таблицу нового поля z ALTER COLUMN - для изменения типа данных существующего поля z DROP COLUMN - для удаления поля. z ADD CONSTRAINT - для добавления индекса z DROP CONSTRAINT - для удаления индекса z Невозможно одновременно добавить или удалить несколько полей или индексов
Создание индекса при создании таблицы CREATE TABLE table (field 1 type [(size)] [, field 2 type [(size)] [, …]] [, CONSTRAINT multifieldindex [, …]])
Создание индекса. Синтаксис команды CREATE [ UNIQUE ] INDEX индекс ON таблица (поле [, поле , . . . ])
Создание индекса. Синтаксис команды CREATE [ UNIQUE ] INDEX индекс ON таблица (поле [, поле , . . . ])
Создание индекса z. DISALLOW NULL - запрещает наличие значений Null в индексированных полях новых записей z. IGNORE NULL запрещает включение в индекс записей, имеющих значения Null в индексированных полях z. PRIMARY - назначить индексированные поля ключом
Примеры создания индексов Пример1. CREATE INDEX New. Index ON Employees (Home. Phone, Extension); Пример 2. CREATE UNIQUE INDEX Cust. ID ON Customers (Customer. ID) WITH DISALLOW NULL;
Использование ALTER TABLE для создания индекса ALTER TABLE таблица {ADD {COLUMN тип поля[(размер)] | ALTER COLUMN тип поля[(размер)] | CONSTRAINT индекс_набора_полей} | DROP {COLUMN поле I CONSTRAINT имя_индекса} }
Создание представлений. Синтаксис команды CREATE VIEW представление [(поле_1[, поле_2[, . . . ]])] AS инструкция. Select
Изменение структуры таблицы ALTER TABLE таблица {ADD {COLUMN тип поля[(размер)] | ALTER COLUMN тип поля[(размер)] | CONSTRAINT составной. Индекс} | DROP {COLUMN поле I CONSTRAINT имя. Индекса} }
Удаление объектов DROP {TABLE таблица | INDEX индекс ON таблица | PROCEDURE процедура | VIEW представление}
ALTER USER or DATABASE z. ALTER DATABASE PASSWORD newpassword oldpassword z. ALTER USER user PASSWORD newpassword oldpassword
Синактсис GRANT {privilege[, privilege, …]} ON {TABLE table | OBJECT object| CONTAINER container } TO {authorizationname[, authorizationname, …]}
Privilege (привилегия) z SELECT z DELETE z INSERT z UPDATE z DROP z SELECTSECURITY z UPDATESECURITY z DBPASSWORD z UPDATEIDENTITY z CREATE z SELECTSCHEMA z UPDATEOWNER
z. Object (объект) – может обозначать любой объект, не являющийся таблицей, например, запрос, представление z. Authorizationname – имя пользователя или группы
ADD USER user[, user, …] TO group Добавление существующего(их) пользователей к существующей группе. Пользователи будут обладать всеми правами, переданными группе
DROP USER or GROUP z. DROP USER user[, user, …] DROP USER выводит пользователя из группы, но не уничтожает пользоватля z. DROP GROUP group[, group, …] DROP GROUP удаляет группу, но не затрагивает пользователей группы; они просто перстают быть членами группы
REVOKE – отмена заданных ограничений REVOKE {privilege[, privilege, …]} ON {TABLE table | OBJECT object| CONTAINTER container} FROM {authorizationname[, authorizationname, …]}
Дополнительные возможности MS Acces SQL z. Инструкция TRANSFORM, предназначенная для создания перекрестных запросов z Дополнительные групповые функции, например, St. Dev и Var. P z Описание PARAMETERS, предназначенное для создания запросов с параметрами
Инструкция SELECT. . . INTO SELECT поле 1[, поле 2[, . . . ]] INTO новая_таблица FROM источник
Сравнение Microsoft Access SQL и ANSI SQL Microsoft Access SQL в основном отвечает стандарту ANSI-89 (уровень 1) некоторые средства ANSI SQL не используются в Microsoft Access SQL Microsoft Access SQL использует зарезервированные слова и средства, не поддерживаемые ANSI SQL В Access 2000 (MS Jet 4.0) внесены расширения, приближающие язык к стандарту ANSI SQL-92 - режим доступен только при использовании MS OLE DB Provider для Jet
Синтаксис команды SELECT (Microsoft Access) SELECT [ предикат ] { * | таблица.* | [ таблица.] поле_1 [, [ таблица.] поле_2 [,...]]} FROM выражение [,...] Предложение FROM SELECT список Полей FROM выражение Выражение - выражение, определяющее одну или несколько таблиц, откуда извлекаются данные. Это выражение может быть именем отдельной таблицы, именем сохраненного запроса или результатом операции INNER JOIN, LEFT JOIN или RIGHT JOIN. INNER JOIN,LEFT JOIN RIGHT JOIN.
Аргументы инструкции SELECT Предикат (ALL, DISTINCT, DISTINCTROW или TOP) - одно из условий отбора. Предикаты используются для ограничения числа возвращаемых записей. Если они отсутствуют, по умолчанию используется предикат ALL (отбираются все записи, соответствующие условиям, заданным в инструкции SQL).инструкции SQL DISTINCT - исключает записи, которые содержат повторяющиеся значения в отобранных полях. DISTINCTROW - опускает данные, основанные на целиком повторяющихся записях, а не отдельных повторяющихся полях. Предикат DISTINCTROW игнорируется, если запрос содержит только одну таблицу или все поля всех таблиц. TOP n - возвращает определенное число записей, находящихся в начале или в конце диапазона, описанного с помощью предложения ORDER BY. SELECT ]] FROM таблица 5 самых многочисленных кафедр: SELECT TOP 5 сотрудник.[Код кафедры], Count(сотрудник.ФИО) AS число_сотрудников FROM сотрудник GROUP BY сотрудник.[Код кафедры] ORDER BY Count(сотрудник.ФИО) DESC;
Аргументы инструкции SELECT Таблица - имя таблицы, из которой должны быть отобраны записи. WITH OWNERACCESS OPTION - используется в многопользовательской среде с составе защищенной рабочей группы, для предоставления пользователю, работающему с запросом, разрешений, соответствующих разрешениям владельца запроса. поле_1, поле_2 - имена полей, из которых должны быть отобраны данные. Если включить несколько полей, они будут извлекаться в указанном порядке. Псевдоним_1, псевдоним_2 - имена, которые станут заголовками столбцов вместо исходных названий столбцов в таблице. Выражение -имена одной или нескольких таблиц, которые содержат отбираемые данные Внешняя БазаДанных - имя базы данных, которая содержит таблицы, указанные с помощью аргумента выражение, если они не находятся в текущей базе данных. SELECT сотрудник.ФИО, [оклад]*0.5 AS Премия FROM сотрудник; SELECT Avg(сотрудник.оклад) AS Средний_оклад FROM сотрудник;
Совместная обработка нескольких таблиц SELECT сотрудник.ФИО, предмет.[наименование предмета краткое] FROM сотрудник INNER JOIN (предмет INNER JOIN [владение предметами] ON предмет.[Код предмета] = [владение предметами].[код дисциплины]) ON сотрудник.Код = [владение предметами].[код сотрудника];
Конструкция Between...And выражение 1 BETWEEN выражение 2 AND выражение 3 (в Microsoft Access SQL выражение 2 может быть больше, чем выражение 3, а в ANSI SQL – нет). SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) Between 1000 And 2000)); Запрос: SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) Between 2000 And 1000)); не выдает ошибки и дает тот же ответ SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) 15000)); SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) Not Between 1000 And 2000));
Символы шаблона Разные символы шаблона используются с предикатом Like. Символ шаблона MS Access SQL ANSI SQL один символ? _ (подчеркивание) группа символов * % одиночный знак, входящий в список_знаков [ список_знаков] отсутствует одиночный знак, не входящий в список_знаков [! список_знаков] отсутствует Две последние возможности - только для Access В Access 2000 в режиме ANSI SQL-92 возможно использование подстановочных знаков ANSI. В одном запросе смешивать знаки нельзя SELECT сотрудник.ФИО FROM сотрудник WHERE (((сотрудник.ФИО) Like "Д*"));
Предложение GROUP BY SELECT список Полей FROM таблица WHERE условие Отбора где группируемые Поля - имена полей (до 10), которые используются для группировки записей. Порядок имен полей в аргументе группируемые Поля определяет уровень группировки для каждого из этих полей. Используйте предложение WHERE для исключения записей из группировки, а предложение HAVING для применения фильтра к записям после группировки. HAVING При использовании предложения GROUP BY все поля в списке полей инструкции SELECT должны быть либо включены в предложение GROUP BY, либо использоваться в качестве аргументов статистической функции SQL. Кафедры, на которых работает более 5 сотрудников: SELECT сотр.[Код кафедры], Count(сотр.ФИО) AS [Число_сотрудников] FROM сотр GROUP BY сотр.[Код кафедры] HAVING (((Count(сотр.ФИО))>5));
5));">
Вложенный запрос. Предикат Exists. SELECT сотр.фирма, Count(сотр.сотр) AS число_аттест_сотр FROM сотр WHERE (((Exists (SELECT DISTINCT сотр.фирма, сотр.сотр FROM сотр INNER JOIN экз ON сотр.сотр = экз.сотр))False)) GROUP BY сотр.фирма; False)) GROUP BY сотр.фирма;"> False)) GROUP BY сотр.фирма;"> False)) GROUP BY сотр.фирма;" title="Вложенный запрос. Предикат Exists. SELECT сотр.фирма, Count(сотр.сотр) AS число_аттест_сотр FROM сотр WHERE (((Exists (SELECT DISTINCT сотр.фирма, сотр.сотр FROM сотр INNER JOIN экз ON сотр.сотр = экз.сотр))False)) GROUP BY сотр.фирма;"> title="Вложенный запрос. Предикат Exists. SELECT сотр.фирма, Count(сотр.сотр) AS число_аттест_сотр FROM сотр WHERE (((Exists (SELECT DISTINCT сотр.фирма, сотр.сотр FROM сотр INNER JOIN экз ON сотр.сотр = экз.сотр))False)) GROUP BY сотр.фирма;">
Создание подчиненного запроса с использованием построителя запросов QBE Если подчиненный запрос используется для определения условий для поля, введите инструкцию SELECT в ячейку строки Условие отбора в столбце этого поля. Инструкцию SELECT необходимо заключить в круглые скобки.
Удаление записей. Запрос на SQL (сгенерированный) DELETE DISTINCTROW студент.ФИО FROM студент WHERE (((студент.ФИО)="Бурлак Г. Н."));
Инструкция DELETE Запрос на удаление удаляет записи целиком, а не только содержимое указанных полей. Чтобы удалить данные конкретного поля, создайте запрос на обновление записей, который заменяет имеющиеся значения на значения Null Запрос на удаление без заданных условий поиска удалит все записи из таблицы. В отличии от команды DROP структура таблицы и все свойства сохраняются
Удаление записей. Запрос на SQL Запрос: DELETE * FROM студент WHERE студент.ФИО="Бурлак Г. Н."; дает аналогичный результат.
35
41
Для извлечения данных из базы данных используется язык SQL. SQL - это язык программирования, который очень напоминает английский, но предназначен для программ управления базами данных. SQL используется в каждом запросе в Access.
Понимание принципов работы SQL помогает создавать более точные запросы и упрощает исправление запросов, которые возвращают неправильные результаты.
Это статья из цикла статей о языке SQL для Access. В ней описаны основы использования SQL для выборки данных и приведены примеры синтаксиса SQL.
В этой статье
Что такое SQL?
SQL - это язык программирования, предназначенный для работы с наборами фактов и отношениями между ними. В программах управления реляционными базами данных, таких как Microsoft Office Access, язык SQL используется для работы с данными. В отличие от многих языков программирования, SQL удобочитаем и понятен даже новичкам. Как и многие языки программирования, SQL является международным стандартом, признанным такими комитетами по стандартизации, как ISO и ANSI .
На языке SQL описываются наборы данных, помогающие получать ответы на вопросы. При использовании SQL необходимо применять правильный синтаксис. Синтаксис - это набор правил, позволяющих правильно сочетать элементы языка. Синтаксис SQL основан на синтаксисе английского языка и имеет много общих элементов с синтаксисом языка Visual Basic для приложений (VBA).
Например, простая инструкция SQL, извлекающая список фамилий контактов с именем Mary, может выглядеть следующим образом:
SELECT Last_Name
FROM Contacts
WHERE First_Name = "Mary";
Примечание: Язык SQL используется не только для выполнения операций над данными, но еще и для создания и изменения структуры объектов базы данных, например таблиц. Та часть SQL, которая используется для создания и изменения объектов базы данных, называется языком описания данных DDL. Язык DDL не рассматривается в этой статье. Дополнительные сведения см. в статье Создание и изменение таблиц или индексов с помощью запроса определения данных .
Инструкции SELECT
Инструкция SELECT служит для описания набора данных на языке SQL. Она содержит полное описание набора данных, которые необходимо получить из базы данных, включая следующее:
таблицы, в которых содержатся данные;
связи между данными из разных источников;
поля или вычисления, на основе которых отбираются данные;
условия отбора, которым должны соответствовать данные, включаемые в результат запроса;
необходимость и способ сортировки.
Предложения SQL
Инструкция SQL состоит из нескольких частей, называемых предложениями. Каждое предложение в инструкции SQL имеет свое назначение. Некоторые предложения являются обязательными. В приведенной ниже таблице указаны предложения SQL, используемые чаще всего.
Предложение SQL | Описание | Обязательное |
|---|---|---|
|
Определяет поля, которые содержат нужные данные. |
||
|
Определяет таблицы, которые содержат поля, указанные в предложении SELECT. |
||
|
Определяет условия отбора полей, которым должны соответствовать все записи, включаемые в результаты. |
||
|
Определяет порядок сортировки результатов. |
||
|
В инструкции SQL, которая содержит статистические функции, определяет поля, для которых в предложении SELECT не вычисляется сводное значение. |
Только при наличии таких полей |
|
|
В инструкции SQL, которая содержит статистические функции, определяет условия, применяемые к полям, для которых в предложении SELECT вычисляется сводное значение. |
Термины SQL
Каждое предложение SQL состоит из терминов, которые можно сравнить с частями речи. В приведенной ниже таблице указаны типы терминов SQL.
Термин SQL | Сопоставимая часть речи | Определение | Пример |
|---|---|---|---|
|
идентификатор |
существительное |
Имя, используемое для идентификации объекта базы данных, например имя поля. |
Клиенты.[НомерТелефона] |
|
оператор |
глагол или наречие |
Ключевое слово, которое представляет действие или изменяет его. |
|
|
константа |
существительное |
Значение, которое не изменяется, например число или NULL. |
|
|
выражение |
прилагательное |
Сочетание идентификаторов, операторов, констант и функций, предназначенное для вычисления одного значения. |
>= Товары.[Цена] |
Основные предложения SQL: SELECT, FROM и WHERE
Общий формат инструкций SQL:
SELECT field_1
FROM table_1
WHERE criterion_1
;
Примечания:
Access не учитывает разрывы строк в инструкции SQL. Несмотря на это, каждое предложение рекомендуется начинать с новой строки, чтобы инструкцию SQL было удобно читать как тому, кто ее написал, так и всем остальным.
Каждая инструкция SELECT заканчивается точкой с запятой (;). Точка с запятой может стоять как в конце последнего предложения, так и на отдельной строке в конце инструкции SQL.
Пример в Access
В приведенном ниже примере показано, как в Access может выглядеть инструкция SQL для простого запроса на выборку.
1. Предложение SELECT
2. Предложение FROM
3. Предложение WHERE
Разберем пример по предложениям, чтобы понять, как работает синтаксис SQL.
Предложение SELECT
SELECT , Company
Это предложение SELECT. Оно содержит оператор (SELECT), за которым следуют два идентификатора ("[Адрес электронной почты]" и "Компания").
Если идентификатор содержит пробелы или специальные знаки (например, "Адрес электронной почты"), он должен быть заключен в прямоугольные скобки.
В предложении SELECT не нужно указывать таблицы, в которых содержатся поля, и нельзя задать условия отбора, которым должны соответствовать данные, включаемые в результаты.
В инструкции SELECT предложение SELECT всегда стоит перед предложением FROM.
Предложение FROM
FROM Contacts
Это предложение FROM. Оно содержит оператор (FROM), за которым следует идентификатор (Контакты).
В предложении FROM не указываются поля для выборки.
Предложение WHERE
WHERE City="Seattle"
Это предложение WHERE. Оно содержит оператор (WHERE), за которым следует выражение (Город="Ростов").
С помощью предложений SELECT, FROM и WHERE можно выполнять множество действий. Дополнительные сведения об использовании этих предложений см. в следующих статьях:
Сортировка результатов: ORDER BY
Как и в Microsoft Excel, в Access можно сортировать результаты запроса в таблице. Используя предложение ORDER BY, вы также можете указать способ сортировки результатов при выполнении запроса. Если используется предложение ORDER BY, оно должно находиться в конце инструкции SQL.
Предложение ORDER BY содержит список полей, для которых нужно выполнить сортировку, в том же порядке, в котором будут применена сортировка.
Предположим, например, что результаты сначала нужно отсортировать по полю "Компания" в порядке убывания, а затем, если присутствуют записи с одинаковым значением поля "Компания", - отсортировать их по полю "Адрес электронной почты" в порядке возрастания. Предложение ORDER BY будет выглядеть следующим образом:
ORDER BY Company DESC,
Примечание: По умолчанию Access сортирует значения по возрастанию (от А до Я, от наименьшего к наибольшему). Чтобы вместо этого выполнить сортировку значений по убыванию, необходимо указать ключевое слово DESC.
Дополнительные сведения о предложении ORDER BY см. в статье Предложение ORDER BY .
Работа со сводными данными: предложения GROUP BY и HAVING
Иногда возникает необходимость работы со сводными данными, такими как итоговые продажи за месяц или самые дорогие товары на складе. Для этого в предложении SELECT к полю применяется агрегатная функция. Например, если в результате выполнения запроса нужно получить количество адресов электронной почты каждой компании, предложение SELECT может выглядеть следующим образом:
Возможность использования той или иной агрегатной функции зависит от типа данных в поле и нужного выражения. Дополнительные сведения о доступных агрегатных функциях см. в статье Статистические функции SQL .
Задание полей, которые не используются в агрегатной функции: предложение GROUP BY
При использовании агрегатных функций обычно необходимо создать предложение GROUP BY. В предложении GROUP BY указываются все поля, к которым не применяется агрегатная функция. Если агрегатные функции применяются ко всем полям в запросе, предложение GROUP BY создавать не нужно.
Предложение GROUP BY должно следовать сразу же за предложением WHERE или FROM, если предложение WHERE отсутствует. В предложении GROUP BY поля указываются в том же порядке, что и в предложении SELECT.
Продолжим предыдущий пример. Пусть в предложении SELECT агрегатная функция применяется только к полю [Адрес электронной почты], тогда предложение GROUP BY будет выглядеть следующим образом:
GROUP BY Company
Дополнительные сведения о предложении GROUP BY см. в статье Предложение GROUP BY .
Ограничение агрегированных значений с помощью условий группировки: предложение HAVING
Если необходимо указать условия для ограничения результатов, но поле, к которому их требуется применить, используется в агрегированной функции, предложение WHERE использовать нельзя. Вместо него следует использовать предложение HAVING. Предложение HAVING работает так же, как и WHERE, но используется для агрегированных данных.
Предположим, например, что к первому полю в предложении SELECT применяется функция AVG (которая вычисляет среднее значение):
SELECT COUNT(), Company
Если вы хотите ограничить результаты запроса на основе значения функции COUNT, к этому полю нельзя применить условие отбора в предложении WHERE. Вместо него условие следует поместить в предложение HAVING. Например, если нужно, чтобы запрос возвращал строки только в том случае, если у компании есть несколько адресов электронной почты, можно использовать следующее предложение HAVING:
HAVING COUNT()>1
Примечание: Запрос может включать и предложение WHERE, и предложение HAVING, при этом условия отбора для полей, которые не используются в статистических функциях, указываются в предложении WHERE, а условия для полей, которые используются в статистических функциях, - в предложении HAVING.
Дополнительные сведения о предложении HAVING см. в статье Предложение HAVING .
Объединение результатов запроса: оператор UNION
Оператор UNION используется для одновременного просмотра всех данных, возвращаемых несколькими сходными запросами на выборку, в виде объединенного набора.
Оператор UNION позволяет объединить две инструкции SELECT в одну. Объединяемые инструкции SELECT должны иметь одинаковое число и порядок выходных полей с такими же или совместимыми типами данных. При выполнении запроса данные из каждого набора соответствующих полей объединяются в одно выходное поле, поэтому выходные данные запроса имеют столько же полей, сколько и каждая инструкция SELECT по отдельности.
Примечание: В запросах на объединение числовой и текстовый типы данных являются совместимыми.
Используя оператор UNION, можно указать, должны ли в результаты запроса включаться повторяющиеся строки, если таковые имеются. Для этого следует использовать ключевое слово ALL.
Запрос на объединение двух инструкций SELECT имеет следующий базовый синтаксис:
SELECT field_1
FROM table_1
UNION
SELECT field_a
FROM table_a
;
Предположим, например, что имеется две таблицы, которые называются "Товары" и "Услуги". Обе таблицы содержат поля с названием товара или услуги, ценой и сведениями о гарантии, а также поле, в котором указывается эксклюзивность предлагаемого товара или услуги. Несмотря на то, что в таблицах "Продукты" и "Услуги" предусмотрены разные типы гарантий, основная информация одна и та же (предоставляется ли на отдельные продукты или услуги гарантия качества). Для объединения четырех полей из двух таблиц можно использовать следующий запрос на объединение:
SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services
;
Дополнительные сведения об объединении инструкций SELECT с помощью оператора UNION см. в статье
В предыдущих статьях были рассмотрены вопросы . Рассмотрена технология создания структуры таблиц базы данных «sql_training_st.mdb» на основе SQL запросов. Кроме того, с помощью SQL запросов было осуществлено заполнение таблиц СУБД ACCESS "sql_training_st.mdb".
Известно, что в реляционных базах данных язык SQL предназначен для манипулирования данными, определения структуры баз данных и ее составных частей, управления доступом пользователей к БД и для управления транзакциями или управления изменениями в БД.
Язык SQL состоит из четырех групп:
- язык манипулирования данными DML;
- язык определения данных DDL;
- язык управления данными DCL;
- язык управления транзакциями TCL.
К группе DML относятся четыре основных типа запросов SQL:
- INSERT - предназначен для добавления одной или нескольких записей в конец таблицы;
- UPDATE - предназначен для изменения уже существующих записей в столбцах таблицы или модификации данных в таблице;
- DELETE - предназначен для удаления записей из таблицы;
- SELECT - предназначен для выборки данных из таблиц.
Первые три типа SQL запросов (INSERT, UPDATE, DELETE), которые относятся к корректирующим запросам к базе данных, были рассмотрены на страничке
В данной статье рассмотрим запросы на выборку данных из таблиц БД Access.
Для извлечения информации, хранящейся в базе данных БД Access 2003 или 2007, можно применить запрос SELECT на выборку данных из таблиц.
Составим следующий SQL запрос (инструкцию SQL) на выборку, для этого выберем режим SQL, выполнив команду Вид/Режим SQL. Вводим с клавиатуры следующую инструкцию SQL:
SELECT *
FROM Студенты;
Эта инструкция состоит из двух предложений "SELECT *" и "FROM Студенты". Первое предложение содержит оператор SELECT и идентификатор * ("идентификатор *" означает вывод всех столбцов таблицы). Второе предложение содержит оператор FROM и идентификатор "Студенты".
FROM - определяет таблицу "Студенты", которая содержат поля, указанные в предложении SELECT. Следует отметить, что в запросе на выборку всегда присутствуют два оператора: SELECT и FROM. В зависимости от условий отбора в запросе на выборку могут присутствовать и другие операторы. На рисунке 1 представлен скриншот запроса на выборку данных.
Рис. 1. SQL запрос SELECT на выборку данных
В данном примере формируется выборка данных из всех столбцов таблицы Студенты.
Сохраняем запрос с именем "Студенты-запрос1". В результате выполнения команды "Сохранить" в «Области переходов» появится объект - «Запросы: Студенты-запрос1».
После сохранения запроса на выборку необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». Результаты выполнения команды «Выполнить» представлены на рис. 2.
Рис. 2. Выборка данных из всех столбцов таблицы Студенты