ТЕМА Лекции: Использование SQL Access
Язык SQL (StructuredQueryLanguage) — один из языков, появившихся в результате разработки реляционной модели данных. В настоящее время этот язык получил очень широкое распространение и фактически превратился в стандартный язык реляционных баз данных. SQL поддерживают сотни СУБД различных типов, которые созданы для самых разнообразных платформ от персоналок до мэйнфреймов.
Понятно, что базовый стандарт не может предусмотреть все потребности пользователей, поэтому многие фирмы-производители СУБД предлагают собственные и часто непереносимые расширения SQL.
Существуют также специальные процедурные расширения SQL-диалектов. Они похожи на обычные процедурные языки, т.е. у них есть и нормальные переменные, и метки, и циклы, и все прочее, а также полностью поддерживается синтаксис SQL. Жесткого стандарта на процедурные расширения нет, поэтому фирмы- изготовители СУБД определяют синтаксис так, как считают нужным.
Назначение языка SQL
Язык SQL дает возможность разработчику баз данных:
- создавать базы данных и таблицы, полностью описывая их структуру;
- выполнять манипулирование данными, используя операции добавления, удаления и модификации;
- создавать и выполнять простые и сложные запросы к базе данных.
Язык SQL, обладая перечисленными выше серьезными возможностями, достаточно прост и легкодоступен для изучения.
В его состав входят два основных компонента:
- язык DML (DataManipulationLanguage) — используется для выборки данных и их обновления;
- язык DDL (DataDefinitionLanguage) — используется для описания структур баз данных и управления доступом к ним.
Диалект языка SQL для MS Access относится к непроцедурным языкам. Он не требует указания методов доступа к данным и поддерживает свободный формат записи операторов. У него нет ни переменных, ни меток, ни циклов, ни всего прочего, с чем привык работать нормальный программист. Надо четко представлять себе, что SQL оговаривает способ передачи данных в клиентскую программу, но никак не оговаривает то, как эти данные должны в клиентской программе обрабатываться и представляться пользователю.
Запросы на выборку
Оператор SELECT
Структура оператора SELECTотличается от остальных операторов языка DML (INSERT, UPDATEи DELETE) несколько большей сложностью, а сам оператор — частотой употребления, поэтому вначале рассмотрим возможности именно этого оператора. Для построения примеров с помощью операторов SQL будем использовать данные таблиц учебного примера RealEstate.
Результатом работы оператора SELECTв MS Access 2010 является выборка необходимых строк из базы данных и размещение их в динамическом объекте набора записей. Синтаксис оператора довольно прост:
SELECT< Список_столбцов >
FROM< Список_таблиц >
[WHERE< Условие_выборки >]
[GROUPBY< Список_полей_группы >]
[HAVING< Условие >]
[ORDERBY< Список_атрибутов >]
Прописными буквами выделены ключевые слова. Курсивом обозначены места, в которых следует ввести имена своих таблиц, столбцов, значений и т.д. В квадратных скобках размещены необязательные элементы.
Предложение FROM
Ключевое слово FROMопределяет имена таблиц, которые являются источником записей для создаваемого запроса. Для выборки всех столбцов таблицы улиц tblStree tбазы данных RealEstate необходимо написать:
SELECT Street, Name, Sign, First
FROM tblStreet;
В частях I I и II I все таблицы, созданные ранее, получили в своем названии приставку "tbl". Этот прием всегда используется при создании профессионального приложения для того, чтобы отличить таблицу от запроса в окне построителя, т. к. в серьезных разработках их число может быть довольно значительным. Запрос же идентифицируют приставкой"qwr".
MS Access не предоставляет пользователю командной строки для непосредственного ввода SQL-конструкций. Но выход из этой ситуации есть — это конструктор запросов. Алгоритм работы, следующий:
1. Откройте вторую вкладку Создание на ленте MSAccess 2010.
2. Запустите конструктор запросов. Он расположен в разделе Другие этой вкладки.
3. Появится окно Добавление таблицы. Закройте его.
4. Выберите первую пиктограмму вкладки Режим SQL.
5. Откроется окно конструктора в режиме ввода кода языкаSQL.
6. Сохраните запрос под именем "Учебный запрос" в базе данных.
Откройте "Учебный запрос" в режиме конструктора. Теперь у нас есть командная строка! Введите в нее текст запроса (рис.1).
Рисунок 1 - Текст простейшего запроса
Точку с запятой в конце последней строки MS Access добавит сам. Перейдите в режим таблицы, сделав щелчок по пиктограмме Режим. Или закройте окно конструктора запросов и запустите запрос на выполнение
(рис. 2).
Рисунок 2 - Результат выполнения запроса по улицам
В запрос попали все улицы (1032 записи) из таблицы tblStreet. В предложении SELECTдля выборки всех столбцов таблицы можно использовать звездочку (*). Результат будет такой же:
SELECT *
FROM tblStreet;
Предложение WHERE
Использование в операторе SELECT предложения, определяемого ключевым словом WHERE, позволяет задавать выражение условия, принимающее значение Истина или Ложь для значений полей таблиц, к которым обращается оператор SELECT. Предложение WHERE определяет, какие строки указанных таблиц должны быть выбраны. В запрос включаются только те строки, для которых условие, указанное в предложении WHERE, принимает значение Истина.
Текст запроса, выполняющего выборку всех проживающих с фамилией (Family) Иванов, сведения о которых находятся в таблице tblOwners, таков:
SELECT Family, Name, Second
FROM tblOwners
WHERE Family ='Иванов'
ORDERBY Name,Second;
В результате выполнения получим список из 79 проживающих (рис. 3). 
Выбор ка отсортирована по полямName(имя)иSecond(отчество).
Рисунок 3 - Фамилию Иванов имеют 79 проживающих
Предложение ORDERBY
Как мы уже знаем, таблицы — это неупорядоченные наборы данных, и данные, которые выводятся из них, не обязательно появляются в какой-то определенной последовательности. Язык SQL использует ключевое слово ORDERBY, чтобы дать возможность упорядочить вывод. Оно упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов. Вы можете использовать ORDERBYодновременно с любым числом столбцов. Обратите внимание, что во всех случаях столбцы, которые упорядочиваются, должны быть указаны в выборе SELECT. По умолчанию установлено возрастание. Текстовые поля будут отсортированы в алфавитном порядке. Столбцы типа Дата/время — в хронологическом.
В предыдущем примере сначала будет выполнена сортировка по имени, а затем по отчеству проживающего.
Предикат DISTINCT
SQL-запросы могут содержать строки с одинаковыми значениями атрибутов. Для исключения повторяющихся записей применяют ключевое слово DISTINCT. Внесем изменения в предыдущий запрос:
SELECT DISTINCT Family, Name, Second
FROM tblOwners
WHERE Family ='Иванов'
ORDERBY Name,Second;
Полученная выборка будет содержать 71 строку. В результаты запроса включаются только уникальные значения каждого из полей, перечисленных в операторе SELECT. Если две записи содержат абсолютно одинаковые фамилию, имя и отчество, то приведенная выше инструкция SQL возвращает только одну запись, содержащую данные о проживающем. Скорее всего, это будут разные люди. Кому придет в голову оформить оплату коммунальных услуг за одновременное проживание сразу в двух квартирах одного города? Да и институт прописки этого не позволит.
Ошибки заполнения базы данных здесь мы не рассматриваем, хотя в любом программном комплексе их достаточно!
Предикат TOP
Предназначен для возвращения заданного количества записей, находящихся в числе первых или последних в выборке. Запрос на отображение первых десяти фамилий в предыдущем примере имеет вид:
SELECT DISTINCT TOP 10 Family, Name, Second FROM tblOwners
WHERE Family ='Иванов'
ORDERBY Name,Second;
Если необходимо отобразить последние 10 записей, измените порядок сортировки (DESC— по убыванию):
SELECT DISTINCT TOP 10 Family, Name, Second FROM tblOwners
WHERE Family ='Иванов'
ORDER BY Name, SecondDESC;
Если не включить предложение ORDERBY, в ответ на запрос будет выдан произвольный набор 10 записей из таблицы tblOwners, удовлетворяющий предложениюWHERE.
Можно также использовать зарезервированное слово PERCENTдля получения заданного процента первых или последних записей диапазона, заданного предложением ORDERBY.
Предложение GROUPBY
Объединяет записи с одинаковыми значениями, находящиеся в указанном списке полей, в одну запись:
SELECT< Список_столбцов >
FROM< Список_таблиц >
[GROUPBY< Список_полей_группы >]
Получим выборку всех однофамильцев с фамилией Иванов:
SELECT Family, Name, Second
FROM tblOwners
WHERE Family ='Иванов'
GROUP BY Family, Name,Second
ORDER BY Name,Second;
Все записи, содержащие повторяющиеся фамилию, имя и отчество, будут объединены в одну. В выборке будет также 71 запись из 79 Ивановых, имеющихся в таблице tblOwners. Это не удивительно, т.к. этот запрос идентичен рассмотренному ранее:
SELECT DISTINCT Family, Name, Second
FROM tblOwners
WHERE Family='Иванов'
ORDERBY Name,Second;
только обладает большей возможностью для расширения функциональности. Воспользуемся этим. Сформируем выборку, содержащую список однофамильцев, у которых полностью совпадает имя и отчество. Эта выборка также должна содержать и число повторений записей.
Предложение HAVING
Определяет сгруппированные записи, которые должны отображаться в операторе SELECT с предложением GROUPBY. После того как записи будут сгруппированы предложением GROUPBY, предложение HAVING покажет те из них, которые отвечают его условиям.
Для поиска повторяющихся строк напишем запрос:
SELECT Family, Name, Second, Count(Family) AS Повторы
FROM tblOwners
WHERE Family = 'Иванов'
GROUP BY Family, Name,Second
HAVING Count(Name)>1
AND Count(Second)>1
ORDER BY Name,Second;
В нашем случае из 79 Ивановых 7 имеют одинаковые имена и отчества, причем запись "Иванов Иван Иванович" встречается три раза (рис. 4).
В запрос добавлено дополнительное поле с названием Повторы. Для его формирования используется функция Count(). Она подсчитывает количество совпадающих записей. Аргумент представляет собой строковое выражение. Операндом в выражении может быть имя таблицы или функция, встроенная или определяемая пользователем, но не статистическая функция SQL. Подсчитать можно количество записей любого типа, включая текстовые.
Рисунок 4 - Список однофамильцев
Выборка данных из нескольких таблиц
До сих пор мы рассматривали создание выборок только из одной таблицы. При работе с нормализованной базой данных непременно потребуется создавать запросы, использующие данные из нескольких таблиц. Их объединение обязательно. Оно выполняется по одному или нескольким столбцам. При объединении двух таблиц по 10 строк в каждой без объединения получится результат уже из 100 строк, а это практически всегда ник чему.
В MS Access существуют два способа объединения таблиц. Первый способ основан на использовании ключевого слова WHERE, а второй — на связке ключевых слов INNER-JOIN-ON.
Сделаем выборку из двух таблиц: tblBuildingи tblStreet, содержащую адреса всех зданий и год их постройки:
SELECT tblStreet.Name, tblStreet.Sign, tblBuilding.House,tblBuilding.Year
FROM tblStreet,tblBuilding
WHERE tblstreet.Street=tblbuilding.Street
ORDER BYYear;
Результат запроса приведен на рис. 5. В запрос попали все здания из таблицы tblBuilding (334 штуки). Они отсортированы по году постройки. Без объединения таблиц результирующий запрос содержал бы 344 688 записей (334 здания 1032улицы).
Рисунок 5 - Адреса зданий и год их постройки
Сразу заметна ошибка в первой строке. Год постройки здания слишком мал - 199. Попробуйте ее исправить — и у вас ничего не выйдет! Получен необновляемый набор записей.
Перепишем запрос с использованием связки ключевых слов FROM-INNER-JOIN-ON:
SELECTtblStreet.Name,tblStreet.Sign, tblBuilding.House, tblBuilding.Year
FROM tblStreet INNER JOIN tblBuilding ON tblStreet.Street=tblBuilding.Street ORDER BYYear;
Результат тот же, но набор записей — обновляемый! Исправьте год постройки здания. Такой запрос позволит это сделать. Исправления будут автоматически внесены в соответствующую запись таблицы tblBuilding.
Всегда используйте для объединения таблиц второй вариант. Он написан в соответствии с более новым стандартом языка SQL и предоставляет разработчику больше возможностей.
MS Access 2010 позволяет объединять в операторе SELECT данные более чем из двух таблиц. Синтаксис в этом случае выглядит так:
FROM(...(Таблица1 INNERJOIN Таблица2 ON Условие1) INNERJOIN(Таблица3 ON Условие2) INNERJOIN
(Таблица4 ON Условие3) INNER JOIN...)
В отчет включены названия улиц (столбец NAME таблицы tblStreet) и имена владельцев квартир (столбец NAME таблицы tblOwners). Несомненно, столбцы в разных таблицах могут иметь одинаковые названия, но при размещении их в одной выборке MS Access будет вынужден добавить к ним названия таблиц. Это, несомненно, не добавит отчету функциональности. К тому же адрес проживающего (улица, признак, номер дома и квартиры) и ФИО (фамилия, имя, отчество) в итоговом документе должны занимать только по одной колонке.
В языке SQL имеется возможность изменять названия итоговых столбцов и объединять несколько из них в один. Для переименования столбца в запросе применяется строка: AS< Псевдоним >, а для объединения полей — символ &. Текст SQL- запроса приведен в листинге 1. На рис. 6 показаны первые строки выборки. Всего строк — 206.
| Листинг 1. Текст запроса для отдела социальной защиты |
SELECT tblOwners.FAMILY & ' ' &tblOwners.NAME & ' ' &tblOwners.SECOND AS ФИО, tblStreet.NAME & ' ' &Lcase(tblStreet.SIGN) & ' ' &tblBuilding.HOUSE& ', кв.' &tblFlats.FLAT AS Адрес, tblOwners.BORN AS [Датарождения]
FROM ((tblStreet INNER JOIN tblBuilding ON tblStreet.STREET=tblBuilding.STREET) INNER JOIN tblFlats ON (tblBuilding.STREET=tblFlats.STREET) AND (tblBuilding.HOUSE=tblFlats.HOUSE)) INNER JOIN tblowners ON (tblFlats.STREET=tblOwners.STREET) AND (tblFlats.HOUSE=tblOwners.HOUSE) AND (tblFlats.FLAT=tblOwners.FLAT)
WHERE Year(tblOwners.BORN)<1940 AND tblBuilding.DISTRICT=1
ORDER BYtblOwners.FAMILY;
Рисунок 6 - Результаты выполнения запроса для отдела социальной защиты
Подчиненные запросы
В состав оператора SELECT, формирующего запрос к базе данных, могут входить подзапросы, результат выполнения которых используется для определения окончательного результата внешнего запроса. Подзапросы могут входить в состав конструкций WHERE и HAVING внешнего запроса и иметь несколько уровней вложения.
Воспользуемся этой возможностью языка SQL для поиска квартир в таблице tblFlats, в которых нет проживающих. Текст запроса приведен в листинге 2.
| Листинг 2. Выборка пустующих квартир |
SELECT*
FROM tblFlats WHERE NOTEXISTS
(SELECT * FROMtblOwners
WHERE Street=tblFlats.Street AND House=tblFlats.House AND Flat=tblFlats.Flat)
ORDER BY Street, House,Flat;
В составе запроса появились новые для вас ключевые слова: NOTEXIST. Они используются только с подзапросами. Результат их обработки представляет собой логическое Trueили False. По этим ключевым словам проверяется отсутствие строк в таблице tblOwners. Для проверки противоположного условия (строки есть) можно применить предикатEXIST.
В запрос попало 9 квартир из 18 475 имеющихся в таблице txtFlats. Несомненно, это ошибки набора информации. Исправить их сейчас практически невозможно, т. к. в выборке вместо названий улиц содержатся ссылки на них. Модифицируем запрос для придания ему надлежащего вида (листинг 3).
| Листинг 3. Модификация запроса по пустующим квартирам |
SELECTtblStreet.Name, tblStreet.Sign, tblFlats.House, tblFlats.Flat,tblFlats.Rooms
FROM tblStreet INNER JOIN tblFlatsON tblStreet.Street=tblFlats.Street
WHERE NOTEXISTS
(SELECT * FROMtblOwners
WHERE Street=tblFlats.Street AND House=tblFlats.House AND Flat=tblFlats.Flat)
ORDER BY Name, House,Flat;
На рис. 7 приведена выборка квартир, для которых нет соответствующих записей в таблице проживающих.
В запросах такого типа вместо функции EXIST или NOTEXIST можно использовать стандартную функцию Microsoft Access Count(*). Если в качестве параметра поставить знак *, то она возвратит количество записей в запросе (листинг 4).
Рисунок 7 - Результаты выполнения запроса по пустующим квартирам
| Листинг 4. Применение Count(*)вместо EXIST |
SELECT tblStreet.Name, tblStreet.Sign, tblFlats.House, tblFlats.Flat,tblFlats.Rooms
FROM tblStreet INNER JOIN tblFlatsON tblStreet.Street=tblFlats.Street
WHERE1>
(SELECT Count(*) FROMtblOwners
WHERE Street=tblFlats.Street AND House=tblFlats.House AND Flat=tblFlats.Flat)
ORDER BY Name, House,Flat;
Время выполнения запроса с применением функции Count(*) несколько больше, чем с использованием ключевого словаEXIST.