Тема 6

Управление реляционной базой данных с помощью SQL

ЯЗЫКИ ЗАПРОСОВ

1. Реляционная алгебра

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

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

SQL используется при:

·        формировании запросов к реляционным БД;

·        обновлении БД;

·        управлении БД.

С помощью команд языка SQL-сервер можно:

  

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

пользователей и групп.

Если вы формируете QB –запрос в окне конструирования запросов, на заднем плане Access констатирует соответствующий SQL- запрос.

Объекты SQL

1.     Директивы  (например,  «кто  из  клиентов  за  последние  полгода  не делал новых заказов?»

2.     Групповые  функции  («Сколько  клиентов  за  последние  полгода  не делал новых заказов?»

3.     Запросы  на   выполнение   действий   («Определить  всех   клиентов, которые клиентов за последние полгода не делал новых заказов и

удалить их из таблицы»)

Синтаксис SQL

При   наборе   запроса   для   перехода   на    другую   строку   следует использовать комбинацию <CTRL>+<ENTER> .

ОператорSELECT

Пример:

SELECT Клиенты.Фирма

FROM Клиенты, [Заинтересованные лица]

WHERE Клиенты.Фирма = [Заинтересованные лица].Фирма;

Выборка будет содержать все фирмы, которые присутствуют как в таблице “Заинтересованные лица”, так и в таблице “Клиенты”.

Внимание!

1.     SELECT является первым ключевым словом в определении SQL-запроса.

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

которой они д.б. помещены в выборку.

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

4.     Если в запросе обрабатывается несколько таблиц, то во избежание

неопределенности в списках полей  рекомендуется приводить полную спецификацию поля, т.е. <имя_таблицы>.<имя_поля> .

Директива FROM

Директива FROM задает имя таблицы или запросы, которые содержат поля, перчисленные в операторе SELECT.

Внимание!

1.     Директива FROM является обязательной.

2.     При перечислении таблиц самые короткие таблицы (содержащие меньшее количество записей) рекомендуется указывать в первую

очередь.

3.     С помощью “*” из таблицы можно выбрать все поля :

SELECT Клиенты.* FROM Клиенты;

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

В следующем примере поле «Фирма» д.б. представлено в выборке как поле «Название клиента»:

SELECT Фирма AS [Название клиента] FROM Клиенты;

ДирективаWHERE

WHERE является необязательной директивой. Если WHERE отсутствует, будут выбраны все записи данных.

ДирективаORDER BY

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

Синтаксис:

ORDER BY поле1 [ASC | DESC] , поле2 [ASC | DESC] [,[…]]

Внимание!

1.     Директива ORDER BY не является обязательной. Если она не используется, данные представляются в неотсортированном виде. Однако если используется предикат TOP, то Директива ORDER BY становится обязательной.

2.     По умолчанию используется группировка поо возрастанию (ACS). DECS – группировка по убыванию.

3.     Порядок перечисления полей задает иерархию уровней сортировки.

4.     Директива ORDER BY является последней директивой в запросе.

ДирективаGROUP BY

Группирует записи данных и объединяет в одну запись все записи данных, которые содержат идентичные значения в указанном поле (или полях).

Пример:

SELECT [название товара], SUM([Стоимость]) FROM Товары

GROUP BY [Название товара]

В этом примере исходные записи с одинаковыми названиями товара группируются и объединяются в одну запись в выборке.

Внимание!

1.     Директива не является обязательной. Если условие указывается, оно должно следовать за FROM или WHERE.

2.     Для фильтрации записей, которые должны группироваться, может использоваться директива WHERE, директива HAVING может использоваться для фильтрации записей, полученных в результате этой

группировки.

3.     В директиве GROUP BY можно использовать любые поля, упомянутые в директиве FROM; эти поля не обязательно д.б. перечислены в списке полей оператора SELECT.

4.     В операторе SELECT для всех полей, включенных в выборку, но не перечисленных в директиве GROUP BY, обязательно должны присутствовать фукции группирования, такие как SUM, COUNT, AVG.

ДирективаHAVING

Используется для фильтрации записей после группирования только в случае использования в запросе директивы GROUP BY.

Внимание!

1.     Директива    HAVING    не     является    обязательной.    Если    она присутствует, то должна следовать за директивой GROUP BY.

2.     Функция директивы HAVING схожа с WHERE – обе используются для фильтрации. Директива WHERE определяет, какие записи должны участвовать в группировании, т.е. фильтрует записи до группирования. HAVING определяет, какие из получившихся в результате группировки

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

Пример:

SELECT Отделение, Count ([Отделение]) FROM Сотрудники

GROUP BY Отделение

HAVING Count ([Отделение]) > 50;

В выборку будут включены отделения, в которых число сотрудников больше 50.

Директива IN

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

использует данные таблицы Access «Заинтересованные лица» и файл БД в формате dBASE IV CLIENTS.DBF :

SELECT CLIENTS.DBF.Firm

FROM CLIENTS.DBF, [Заинтересованные  лица] IN C:\ dBASE\DATA\ CLIENTS «dBASE;»

WHERE CLIENTS.DBF.Firm = [Заинтересованные лица].[Фирма]

Внимание!

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

2.     Для указания базы данных, которая имеет формат, отличный от

формата Access, добавьте к обозначению типа базы данных точку с запятой и заключите его в кавычки, например, «dBASE;»

Предикаты

Синтаксис:

[ALL | DISTINCT | DISTINCTROW |  [TOP n [PERCENT]]]

FROM <исходная таблица>

Предикат ALL

SELECT ALL ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО FROM Клиенты

Выбираются перечисленные поля из всех записей таблицы Клиенты

Если предикат не указывается, то предполагается наличие предиката ALL. Предикаты служат для установки дополнительных фильтров после применения критериев директивы WHERE.

Предикат DISTINCT

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

SELECT DISTINCT Клиенты.Фирма FROM Клиенты, [Заинтересованные лица]

WHERE Клиенты.Фирма = [Заинтересованные лица].Фирма;

Если в этом примере предикат DISTINCT не будет указан, в выбоку будут включены названия фирм из всех записей таблицы клиентов, которые

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

дублирующие записи. Предикат DISTINCT приведет к отбрасыванию дублирующих значений поля “Фирма”.

Предикат DISTINCTROW

Используется, когда следует пропустить данные, представляющие собой полностью дублирующие записи в выборке, но не дублирование значений

отдельных полей.

Таблица «Клиенты» не содержит дублирующих кодов клиентов, в то время, как таблица «Заказы» может содержать дублирующие коды клиентов, поскольку один клиент может сделать несколько заказов. В приведенном ниже примере показано, как можно использовать предикат DISTINCTROW для того, чтобы составить список фирм, которые сделали хотя бы один заказ, не вдаваясь в подробности о количесве таких заказов:

SELECT DISTINCTROW [Название фирмы] FROM Клиенты INNER JOIN Заказы

ON Клиенты.[Код клиента] = Заказы.[Код клиента] ОRDER BY [Название фирмы];

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

Использование DISTINCTROW эквивалентно установке значения ДА для параметра Уникальные записи в окне свойств запроса при

проектировании QBE- запросов в режиме конструирования.

ПредикатTOP

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

помощью критерия отбора WHERE и упорядоченных с помощью директивы ORDER BY.

Предположим, мы хотим отобрать 25 лучших студентов выпуска 1999 года:

SELECT TOP 25 [имя],[отчество],[фамилия] FROM Студенты

WHERE [год выпуска] = 1999

ORDER BY [средний балл] DESC

Если не будет включена директива ORDER BY, то в выборку будут включены любые 25 студентов БД “Студенты”, удовлетворяющие условиям отбора, заданным в директиве WHERE.

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

же средний балл, как двадцать шестой и двадцать седьмой, то в выборку

будут включены 27, а не 25 записей.

Можно также использовать ключевое слово PERCENT для того, чтобы включить в выборку определенный процент из верхней или нижней части диапазона, отсортированного в соответствии с директивой ORDER BY. Например, вместо 25 студентов можно запросить 10% курса:

SELECT TOP 10 PERCENT [имя],[отчество],[фамилия] FROM Студенты

WHERE [год выпуска] = 1999 ORDER BY [средний балл] DESC

Используемое число в предикате ТОР д.б. целым без знака. Использование TOP эквивалентно использованию параметра НАБОР

ЗНАЧЕНИЙ в окне свойств запроса при проектировании  QBE- запроса в режиме конструирования. Использование ключевого слова PERCENT эквивалентно использованию знака процента (%) при указании свойства НАБОР ЗНАЧЕНИЙ.

Операция объединения INNER JOIN

С помощью этого оператора формируется связь эквивалентности. В объединение при таком типе связи включаются данные из двух таблиц, если в поле связи, имеющемся в обеих таблицах, найдены совпадающие значения. Есть еще два типа связи – LEFT JOIN , RIGHT JOIN. В случае применения таких связей в объединение включаются также записи из левой или правой таблицы, которым не соответствует ни одна запись в связываемой с ними

таблице.

Операция

Операция объединения INNER является частью условия директивы FROM. Синтаксис:

FROM таблица1 INNER JOIN таблица2 ON таблица1.поле1 = таблица2.поле2

Пример:

SELECT DISTINCTROW [Название фирмы] FROM Клиенты INNER JOIN Заказы

ON Клиенты.[Код клиента] = Заказы.[Код клиента] ОRDER BY [Название фирмы];

Поля, по которым производится объединение таблиц, должны быть числовыми, либо, если они не числовые, они должны иметь одинаковый тип и размер, но необязательно одинаковые имена. Поля типа МЕМО или OLE – объекты не могут использоваться для операции объединения.

Групповые функции

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

Пример1

SELECT SUM([цена] * [количество ]) AS [Общая стоимость] FROM [Заказы]

WHERE [Город] = “Екатеринбург”

Пример2

SELECT AVG([цена] )

FROM [Заказы]

WHERE [цена] > 1000000

Пример3

SELECT Min([цена] ) FROM [Заказы]

WHERE [город] > “Екатеринбург”

Пример4

SELECT COUNT([город] ) AS [Всего по Екатеринбургу] FROM [Заказы]

WHERE [Город] = “Екатеринбург” Пример5

SELECT First([Дата заказа]) AS [Первый заказ]

FROM [Заказы]

WHERE [Город] = “Екатеринбург”

Запросы на выполнение действия

Пример1

INSERT INTO Клиенты

SELECT [Город] = “Первоуральск” FROM [Заинтересованные лица]

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

Пример2

UPDATE [Прейскурант] SET цена = цена * 1,1

WHERE [Категория] = “Шторы”

Цены на все шторы в таблице Прейскурант повышаются на 10%.

ПРИМЕР3

DELETE FROM Заказы

WHERE [Дата заказа] < 1.01.95

Пример4

SELECT Заказы.* INTO [Архив заказов] FROM Заказы

WHERE [Дата заказа] < 1.01.95

Из записей таблицы Заказы формируется новая таблица «Архив заказов», которая содержит заказы до января 1995 года.

Формирование SQL- запросов

1.     Преобразование запроса по образцу QBE- запроса в SQL – запрос.

·        В окне запросов сформируйте запрос, из которого вы хотите создать SQL – запрос.

·        Выберите инструмент SQL или войдите в меню ВИД и выберите команду SQL. На экране появится окно с текстом готового SQL – запроса.

2.     Непосредственное формирование SQL – запроса.

·        Перейдите в режим конструирования запроса, оставьте окно конструирования пустым.

·        Войдите в меню ВИД и выберите команду SQL .

·        Сформируйте запрос. Помните, что нельзя использовать просто клавишу Enter для перехода к новой строке ( нажмите <Ctrl>+ <Enter>)

·        Для сохранения запроса выполните команду меню ФАЙЛ, СОХРАНИТЬ.

·        Если SQL – запрос не содержит синтаксических ошибок, то после сохранения Access автоматически преобразует SQL – запрос в

запрос по образцу, и вы можете проверить его в окне конструирования

QBE- запроса.

·        Для выполнения запроса выберите инструмент ВЫПОЛНИТЬ (кнопка с восклицательным знаком) на панели инструментов или

войдите в меню ЗАПРОС и выберите команду ВЫПОЛНИТЬ.

Еще раз об языке запросов на примерах.

2. Структурированный язык запросов SQL Запрос представляет собой специальным образом описанное

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

Для подготовки запросов с помощью различных СУБД чаще всего используются два основных языка описания запросов:

·        QBE (Query By Example) – язык запросов по образцу;

·        SQL (Structured Query Language) – структурированный язык запросов.

По возможностям манипулирования данными при описании запросов указанные языки практически эквивалентны. Отличаются языки способом формирования запросов: язык QBE предполагает ручное или визуальное формирование запроса, в то время как использование SQL означает программирование запроса.

Язык SQL имеет несколько стандартов, наиболее распространенными из которых являются SQL-89 и SQL-92. Язык предназначен для выполнения операций над таблицами (создание, удаление, изменение структуры) и над данными таблиц (выборка, изменение, добавление и удаление), а также некоторых сопутствующих операций. SQL является непроцедурным языком и не содержит операторов управления, организации подпрограмм, ввода- вывода и т.п. В связи с этим SQL автономно не используется, обычно он погружен в среду встроенного языка программирования СУБД (например, FoxPro СУБД Visual FoxPro, ObjectPAL СУБД Paradox, Visual Basic for Applications СУБД Access).

Операторы языка SQL можно условно разделить на два подъязыка: язык определения данных (Data Definition Language – DDL) и язык манипулирования данными (Data Manipulation Language – DML). Основные операторы языка SQL представлены в табл. 4.1.

Операторы языка SQL

Таблица

Рассмотрим основные операторы языка SQL, реализованного в Access. Инструкция CREATE TABLE.

Назначение: создание новой таблицы. Синтаксис:

CREATE [TEMPORARY] TABLE таблица (поле_1 тип [(размер)] [NOT NULL]

[индекс_1] [, поле_2 тип [(размер)] [NOT NULL] [индекс_2] [, ...]] [,составной_индекс [, ...]])

Аргументы инструкции CREATE TABLE: таблица - имя создаваемой таблицы;

поле_1, поле_2 - имена одного или нескольких полей, создаваемых в новой таблице. Таблица должна содержать хотя бы одно поле;

тип - тип данных поля в новой таблице;

размер - размер поля в знаках (только для текстовых и двоичных полей);

индекс_1, индекс_2 - предложение CONSTRAINT, предназначенное для создания простого индекса;

составной_индекс - предложение CONSTRAINT, предназначенное для создания составного индекса.

Дополнительные сведения:

·        Если для поля добавлено ограничение NOT NULL, то при добавлении новых записей это поле должно содержать допустимые данные.

·        Предложение CONSTRAINT устанавливает различные ограничения на поле и может быть использовано для определения ключа. Кроме того,

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

·        Создаваемая  временная   (TEMPORARY)  таблица  будет   доступна только  в              том    сеансе,       котором  эта   таблица  была  создана.  По

завершении данного сеанса она автоматически удаляется. Например, инструкция

TABLE Разделы (Код_раздела INTEGER NOT NULL, Название TEXT(30) NOT NULL);

создаст таблицу с двумя полями: целочисленным и текстовым. Предложение CONSTRAINT.

Назначение: создание или удаление индексов в инструкциях CREATE TABLE и ALTER TABLE.

Существуют два типа предложений CONSTRAINT: одно для создания простого индекса (по одному полю), а второе для создания составного индекса (по нескольким полям).

Синтаксис предложения CONSTRAINT для создания простого индекса:

CONSTRAINT имя {PRIMARY KEY | UNIQUE | NOT NULL |

REFERENCES внешняя таблица [(внешнее_поле_1, внешнее_поле_2)] [ON UPDATE CASCADE | SET NULL]

[ON DELETE CASCADE | SET NULL]}

Синтаксис предложения CONSTRAINT для создания составного индекса:

CONSTRAINT имя