Структура языка SQL

Введение
Глава 1. Описание SQL
1.1 Применение языка SQL
Глава 2. Выражения SQL
2.1 Использование выражения SELECT
2.2 Простые формы SELECT
2.3 Фильтрация данных с помощью предложения WHERE
2.4 Операторы, применяемые в предложении WHERE
2.5 Оператор BETWEEN
2.6 Оператор IN
2.7 Логические операторы
2.8 Предложение WHERE и вложенные команды SELECT
2.9 Сортировка данных
2.10 Группировка столбцов
2.11 Использование предложения HAVING
2.12 Объединение таблиц
2.13 Оператор INNER JOIN
2.14 Оператор LEFT JOIN
2.15 Оператор RIGHT JOIN
1.2.15 Объединение запросов
2.17 Переименование столбцов результата
2.18 Добавление записей
2.19 Обновление данных
2.20 Удаление данных
2.21 Вызов функций из команд SQL
2.22 Хранимые процедуры
Глава 3. Создание запросов
3.1 Создание запросовв базах данных
Заключение
Приложения
Приложение 1 Таблицы, используемые в базе.
Приложение 2 Запросы, используемые в приложении
Приложение 3 Формы, используемые в приложении
Глоссарий
Листинг программы
Список использованных источников

Структура языка SQL

Например, база данных о покупателях может состоять, скажем, из двух таблиц, одна из которых содержит некоторые "статические" поля (имя, почтовый адрес и пр.), а другая предназначена для хранения переменного числа записей, относящихся к каждому покупателю (например, информации о номерах кредитных карточек). Теперь набор кредитных карточек каждого покупателя будет описываться именно таким количеством записей таблицы, какое нужно, — не больше и не меньше.
Реляционные базы данных предполагают наличие механизма объединения хранящейся в нескольких таблицах информации об объекте в целостную "картинку". Прежде для решения подобной задачи достаточно было переместиться к конкретной записи единственной таблицы, — конечно, такой способ намного проще, но он сопряжен с потерями, о которых мы говорили выше.
Число таких таблиц реляционной базы данных, которые содержат однородные порции определенной информации (имеющей отношение к объектам одного типа), не ограничено — их может быть две, три и более. Объединение выполняется с помощью дополнительных ключевых столбцов и, безусловно, более трудоемко в практической реализации по сравнению с методами обработки "плоских" таблиц — но выигрыш, тем не менее, очевиден.
Процесс и результат сбора таких данных об определенном объекте, которые хранятся в нескольких таблицах, принято называть объединением таблиц.
Пример запроса:
SELECT * FROM Customer, Credit_Cards
WHERE Customer.Id = Credit_Cards.Customer_Id
(Выражение Customer. Id означает ссылку на поле ID таблицы CUSTOMER. Это
пример использования общеупотребительного — полного — синтаксиса обращения к
полю таблицы.) Если таблица CREDIT_CARDS не содержит сведений о кредитных карточках какого-либо покупателя, предложение WHERE не "сработает" и в возвращенном наборе данных этот покупатель вообще не будет упомянут. Это как раз тот случай, когда вас выручит выражение на основе служебного слова JOIN. Оператор JOIN работает с двумя аргументами-таблицами: первую называют левой, а вторую — правой. Существует три разновидности конструкций JOIN — INNER JOIN, LEFT JOIN и RIGHT JOIN. Каждая из них служит определенной цели.
2.13 Оператор INNER JOIN
Конструкция INNER JOIN равнозначна условию эквивалентности, используемому в предложении WHERE. Оператор INNER JOIN позволяет возвратить все записи, для которых выполняется условие равенства содержимого столбцов двух объединяемых таблиц. Пример:
SELECT *
FROM Music INNER JOIN Tracks ON Music.Id = Tracks.Music_Id
Данная команда возвратит все записи таблиц MUSIC и TRACKS, для которых MUSIC. ID = TRACKS.MUSIC. ID. Она равносильна следующему выражению:
SELECT * FROM Music, Tracks
WHERE Music.Id = Tracks.Music_Id
2.14 Оператор LEFT JOIN
Оператор LEFT JOIN применяется в тех случаях, когда следует вернуть все записи левой таблицы и только те строки правой, значения полей которых соответствуют данным левой таблицы. Поэтому в полях правой таблицы возвращенного множества данных допускаются значения null. Рассмотрим пример:
SELECT *
FROM Music LEFT JOIN Tracks On Music.Id = Tracks.Music_Id
В результате выполнения указанного запроса будут возвращены все записи таблицы MUSIC — даже те, для которых в таблице TRACKS нет соответствий. В последнем случае поля результата, относящиеся к таблице TRACKS, окажутся пустыми.
2.15 Оператор RIGHT JOIN
Конструкция RIGHT JOIN прямо противоположна по назначению оператору LEFT JOIN, рассмотренному выше. При использовании RIGHT JOIN возвращенный набор данных будет содержать все записи правой таблицы и только те строки левой, для которых в правой таблице имеются соответствия. Например:
SELECT *
FROM Music RIGHT JOIN Tracks On Music.Id = Tracks.Music_Id
Результат выполнения запроса будет содержать все записи таблицы TRACKS, включая те, для которых отсутствуют ключевые значения в таблице MUSIC.
2.16 Объединение запросов
Два запроса SQL, возвращающие одинаковое число полей совместимых типов, разрешается объединять с помощью служебного слова UNION. Запросы не зависят от соседних, но выполняются вместе, как одна команда SQL, давая в результате единый набор данных. По умолчанию оператор UNION устраняет из возвращенного множества данных повторяющиеся строки. Чтобы в результат включались все записи, после оператора UNION необходимо добавить служебное слово ALL.
Никто не запрещает вам использовать конструкции UNION, действующие как единственное выражение SELECT с оператором OR, хотя это не самый удачный выбор. Лучший вариант применения связан с необходимостью сочетания в одном возвращенном наборе данных, близких по природе, но расположенных в разных таблицах. Приведенный ниже пример возвращает значения столбца ID из таблиц MUSIC и TRACKS.
SELECT Id FROM Music
UNION
SELECT Id FROM Tracks
В результате будут получены все значения столбцов ID таблиц MUSIC и TRACKS.
2.17 Переименование столбцов результата
Встречаются ситуации, когда бывает полезным объединить данные двух или более столбцов результата выборки в один столбец. Например необходимо отсортировать записи по фамилии исполнителя, без дополнительного кода сделать это будет довольно трудно. Невольно напрашивается вывод о необходимости разбиения столбца ARTIST на два — скажем,
FIRST_NAME и LAST_NAME. Действительно, это достаточно гибкое решение. Но возникают подозрения, удастся ли в дальнейшем получать такие же результаты (т.е. с корректно отформатированным полным именем исполнителя), как и прежде.
Проблема решается с помощью средств переименования столбцов результата запроса. SQL позволяет объединять данные нескольких столбцов в один и присваивать ему новое имя посредством оператора AS. Предположим, что мы все-таки расчленили столбец ARTIST на два, FIRST_NAME и LAST_NAME, предназначенных для хранения имен и фамилий. Тогда выражение запроса, возвращающего полные имена исполнителей, могло бы выглядеть так:
SELECT First_Name + ' ' + Last_Name AS Artist FROM Music
ORDER BY Last_Name
Приведенный запрос "склеивает" значения имен и фамилий в единое целое под именем - ARTIST. Поскольку фамилии и имена хранятся отдельно, стало возможным использовать предложение ORDER BY Last_Name.
Оператор AS особо пригодится в тех случаях, когда результат отбора
содержит вычисляемые значения или неудачно названные столбцы таблиц.
2.18 Добавление записей
Команда INSERT INTO позволяет добавлять записи в таблицу базы данных и допускает несколько способов применения.
Наиболее употребительный вариант использования команды INSERT INTO предусматривает добавление записи в существующую таблицу с указанием списка полей.
Ниже приведена синтаксическая формула подобного выражения:
INSERT INTO ИмяТаблицы (ИмяПоля! [, ИмяПоля2, . . . ] )
VALUES (Значение! [, Значение2, . . . ] )
В верхнем регистре набраны служебные слова SQL. После фразы INSERT INTO указывается имя таблицы, за которым следует список наименований полей, заключенный в круглые скобки. Список может содержать только те поля, в которые вы хотите занести значения (если поле помечено признаком обязательного заполнения, его имя должно присутствовать в списке.). Количество значений, перечисленных в круглых скобках после служебного слова VALUES, и их типы должны соответствовать содержимому списка полей.
Следующий пример иллюстрирует процедуру пополнения реестра музыкальной коллекции данными о новом приобретении — очередном
компакт-диске Джонни Кэша (Johnny Cash).
INSERT INTO Music (First_Name, Last_Name, Title, Format, Publisher)
VALUES ('Johnny', 'Cash', 'JOHNNY CASH AT FOLSOM PRISON AND SAN_
QUENTIN', 'CD', 'Columbia')
После выполнения этой команды в таблицу MUSIC будет добавлена запись со следующими значениями полей:
FIRST_NAME = 'Johnny',
LAST_NAME = 'Cash',
TITLE = = 'JOHNNY CASH AT FOLSOM PRISON AND SAN QUENTIN',
FORMAT = 'CD',
PUBLISHER = 'Columbia'.
Еще один распространенный вариант команды INSERT предполагает задание полного списка значений полей новой записи. Порядок перечисления значений должен соответствовать перечню полей таблицы, используемому при ее создании командой CREATE TABLE. Синтаксическая формула в этом случае становится более краткой ввиду отсутствия списка наименований полей.
INSERT INTO ИмяТаблицы
VALUES (Значение! [, Значение2, ...])
Порядок следования значений, указанных после служебного слова VALUES, и их типы должны соответствовать структуре таблицы. В качестве символического обозначения величины, заносимой в поле AutoNumber, используется литерал 0; Access самостоятельно позаботится о том, чтобы вычислить и сохранить в поле AutoNumber нужное значение. Например:
INSERT INTO Music
VALUES (0, 'Jewel', 'Kilcher', 'PIECES OF YOU', 'CD', 'Atlantic')
Фраза INSERT INTO Music означает, что данные будут добавлены в таблицу MUSIC. После имени таблицы список полей не приводится — вместо него сразу следуют слово VALUES и полный список значений.
Версия SQL, которая реализуется в Access, допускает формат команды INSERT, содержащий параметры и предполагающий динамический интерактивный ввод их значений. Параметры размещаются в списке VALUES и обозначаются именами, заключенными в квадратные скобки. Возможность задания параметров поддерживается в обоих рассмотренных выше вариантах INSERT — со списком полей и без такового.
Единственной синтаксической особенностью параметрической версии INSERT является наличие аргументов, взятых в квадратные скобки. Приведенные ниже примеры демонстрируют использование параметров в обоих вариантах INSERT — с заданием полной строки и указанием списка имен полей.
Пример параметрической команды INSERT с заданием полной строки данных:
INSERT INTO Music
VALUES (0, [Имя], [Фамилия], [Название], [Формат], [Компания] )
Пример параметрической команды INSERT с указанием списка имен полей
INSERT INTO Music (First_Name, Last_Name, Title, Format, Publisher)
VALUES ([Имя], [Фамилия], [Название], [Формат], [Компания])
В пределах одной и той же команды INSERT могут задаваться как литеральные значения, так и параметры — в любых сочетаниях. Например, заведомо зная значения, которые следует сохранить в большинстве полей, вы можете указать параметры только для одного или двух — именно так чаще всего и происходит.
Весьма полезный способ применения команды INSERT связан с задачей копирования данных из одной таблицы в другую. В этом случае вместо предложения VALUES используется вложенный запрос на основе SELECT. Ниже приведена соответствующая синтаксическая формула.
INSERT INTO ИмяТаблицыПриемника
(ИмяПоля! [, ИмяПоля2, ...])
SELECT ИмяТаблицыИсточника.ИмяПоля! [,
ИмяТаблицыИсточника.ИмяПоля2, . . . ]
FROM ИмяТаблицыИсточника
Практически, все осталось на месте, кроме предложения VALUES, в котором указывается выражение SELECT. Напомним, что перечни полей SELECT и INSERT INTO должны совпадать по типам и количеству элементов.
Пример команды INSERT INTO с вложенным запросом, выполняющим
импорт полей из идентичной базы данных:
INSERT INTO MUSIC (ID, ARTIST, TITLE, FORMAT, PUBLISHER)
SELECT 0, ID, ARTIST, TITLE, FORMAT, PUBLISHER FROM MUSIC2
Поскольку в качестве первичного ключа используется автоматически созда-
ваемый счетчик, первый элемент команды SELECT равен 0. После лите-
рального значения 0 каждое поле исходной таблицы (во вложенном за-
просе) помещается в список полей команды SELECT.
2.19 Обновление данных
Команда UPDATE применяется для одновременного изменения содержимого полей одной или нескольких записей. Основной вариант выражения позволяет обновить значения всех столбцов таблицы. Расширенная версия дает возможность сузить набор записей, подвергающихся воздействию, с помощью предложения WHERE. Ниже приведена общая синтаксическая формула команды UPDATE.
UPDATE ИмяТаблицы
SET ИмяПоля! = Значение! [, Им.яПоля2 = Значение2, ...]
[WHERE Предложение]
Служебные слова SQL в соответствии с общепринятым соглашением вводятся в верхнем регистре — так, как в данном случае набраны UPDATE, SET и WHERE. Предложение SET должно включать, по меньшей мере, один предикат вида ИмяПоля = Значение, и их количество не ограничено. Предложение WHERE
необязательно.
Примеры использования команды UPDATE
1: UPDATE Music SET Title = UCase( TITLE );
2: UPDATE Music SET First_Name = ICapf [First_Name] );
3: UPDATE Music SET Publisher = 'Columbia Records'_
WHERE Publisher = 'Columbia1
В строке 1 используется встроенная VBA функция UCase, переводящая
содержимое поля TITLE всех записей таблицы MUSIC в верхний регистр.
Строка 2 иллюстрирует применение пользовательской функции Iсapf,
предназначенной для преобразования первого символа переданной строки (в данном случае — значения поля FIRST_NAME) в верхний регистр.
Строка 3 демонстрирует команду UPDATE, содержащую предложение WHERE и обновляющую только те записи таблицы MUSIC, в поле PUBLISHER которых хранится значение "Columbia".
2.20 Удаление данных
Информацию, попавшую в базу данных, когда-либо наверняка придется
удалить. Эта обязанность в языке SQL возложена на команду DELETE.
Синтаксис выражения DELETE таков:
DELETE FROM ИмяТаблицы [WHERE Предложение]
При удалении записей одной таблицы зачастую следует позаботиться об
изъятии логически взаимосвязанных записей из других таблиц. То же
справедливо и в отношении операций изменения данных.
Во многих системах управления базами данных поддерживаются
процедуры каскадного удаления и обновления информации. Имеется в виду, что система самостоятельно следит за логической целостностью данных. Для реализации возможностей каскадного выполнения операций обычно необходимы дополнительные действия со стороны администратора базы данных и авторов приложений. Если необходимая настройка не произведена, ответственность за целостность данных возлагается на код прикладной программы SQL или VBA.
Заметно упрощают работу также хорошие средства программирования и
администрирования баз данных.
2.21 Вызов функций из команд SQL
Если создаваемый SQL-код проявляет тенденцию к усложнению, попробуйте воспользоваться существующими функциями или написать собственные, чтобы упростить выражение SQL.
SQL предлагает целый ряд встроенных функций — достаточно назвать COUNT, предназначенную для подсчета числа записей, или SUM — для суммирования числовых значений.
Правила обращения к функциям в SQL практически не отличаются от тех, которые приняты в VBA. Необходимо задать имя функции и передать ей верное количество значений параметров требуемых типов.
2.22 Хранимые процедуры
С появлением средств ActiveX Data Objects арсенал программиста, использующего Access, пополнился возможностями создания и применения хранимых процедур. Хранимая процедура в SQL равнозначна функции. Аппарат хранимых процедур в таких крупномасштабных системах управления базами данных, как, например, Oracle, существует с давних пор. (В Oracle имеется даже самостоятельный процедурный язык программирования под названием PL/SQL.)
Среди многочисленных достоинств, присущих хранимым процедурам, достаточно
отметить два основных: хранимые процедуры выполняются на серверах баз данных и позволяют определять интерфейсы. Понятно, что при запуске процедур на сервере (который, как правило, намного более производителен по сравнению с персональными станциями) прикладная программа будет выполняться существенно быстрее. Этот вывод справедлив даже в том случае, если Access 2002 работает на том же компьютере, что и ваше приложение, поскольку хранимые процедуры выполняются непосредственно ядром Access. А именованные интерфейсы обеспечивают настолько же простые возможности обращения к хранимым процедурам, как и при использовании обычных функций. Вы передаете значения аргументов, затем определенные вами операции выполняются и возвращают требуемые результаты. Процедуры хранятся в базе данных, в коллекции Procedures, входящей в состав объекта Catalog. Каждый элемент коллекции Procedures — это объект класса Procedure.
Объект Procedure содержит атрибуты DateCreated, DateModified, Name и
Command.
Command — это объект данных, содержащий собственно код тела процедуры. Хотя хранимые процедуры, на самом деле, физически располагаются в одной из системных таблиц базы данных — доступ к процедурам вы получаете с помощью коллекции Procedures объекта Catalog. Прежде чем воспользоваться процедурой, ее необходимо создать и добавить в объект Catalog.
Синтаксис определения хранимой процедуры.
PARAMETERS [Параметр!] Тип {, [Параметр2] Тип, . . . } ;
После служебного слова PARAMETERS следует список пар Параметр Тлп, определяющих интерфейс процедуры. Объем списка не ограничен. Квадратные скобки в данном случае служат составной частью конструкции, а не признаком необязательности синтаксического элемента. Список параметров завершается символом точки с запятой, после которого набирается текст тела процедуры на языке SQL.
Примеры хранимых процедур:
1: PARAMETERS [APublisher] TEXT;
2: SELECT First_Name + •' ' + Last_Name As Artist, Title,_
Format, Publisher
3: FROM Music WHERE Publisher = [APublisher]
4:
5: PARAMETERS [APublisher] TEXT, [AFormat] TEXT;
6: SELECT First_Name + ' ' + Last_Name As Artist, Title,_
Format, Publisher
7: FROM Music
8: WHERE Publisher = [APublisher] AND Format = [AFormat]
Строки 1-3 задают текст первой процедуры — далее показано, как
в виде единой строки он присваивается переменной-свойству Command-
Text объекта ADODB. Command. Процедура принимает текстовое значение,
переданное в качестве параметра [APublisher], и отбирает все строки
таблицы MUSIC, содержимое поля PUBLISHER которых равно значению
параметра. Текст второй процедуры приведен в строках 5—8. Она выпол-
няет схожую операцию, но работает уже с двумя аргументами ([APublisher] и [AFormat]), задающими искомые значения полей PUBLISHER И FORMAT.
Чтобы хранимая процедура стала доступной для использования, ее следует добавить в базу данных с помощью свойств и методов объектов ADODB. Command и ADODB.Catalog.
Пример добавления хранимой процедуры в базу данных
Sub CreateStoredProcedure( )
Dim Connection As ADODB.Connection
Set Connection = CurrentProject.Connection
Dim Command As New ADODB.Command
Dim Catalog As New ADOX.Catalog
Set Command.ActiveConnection = Connection
Command.CommandText = "PARAMETERS [APublisher] TEXT;" &
"SELECT ARTIST, TITLE, FORMAT," &
"PUBLISHER FROM Music WHERE Publisher = [APublisher]"
Set Catalog.ActiveConnection = Connection
Call Catalog.Procedures.Append I
Set Command = Nothing
Set Catalog = Nothing
Set Connection = Nothing
End Sub
Когда хранимая процедура создана, можно обращаться к ней в любой
момент по мере надобности. Чтобы выполнить процедуру, следует воспользоваться методом Execute класса Command. Если хранимая процедура возвращает некий набор данных, надлежит присвоить результат выполнения метода Execute переменной класса ADODB.Recordset. Если же хранимая процедура выполняет команды SQL (такие как INSERT, DELETE или UPDATE), не возвращающие значений или наборов данных, объект Recordset не нужен.
Пример выполнения хранимой процедуры
Sub ExecuteProcedureI
Dim Connection As ADODB.Connection
Set Connection = CurrentProject.Connection
Dim Catalog As New ADOX.Catalog
Set Catalog.ActiveConnection = Connection
Dim Command As ADODB.Command
Set Command = Catalog.Procedures("Artist By Publisher").Command
Dim Publishers As ADODB.Recordset
Dim RecordsAffected As Long