WWW.DISSERS.RU

БЕСПЛАТНАЯ ЭЛЕКТРОННАЯ БИБЛИОТЕКА

   Добро пожаловать!


Pages:     | 1 || 3 | 4 |   ...   | 6 |

С точки зрения использования в качестве источников данных при составлении запроса, нет разницы между запросами и «постоянными» таблицами. В языке SQL есть возможность не только обращаться по имени к созданным ранее объектам – запросам, но и определять «вложенные» («подчиненные») запросы непосредственно внутри основного запроса.

Пример 1.10 (один из источников данных – вложенный запрос).

SELECT Клиент.Фамилия, Подписка2.Журнал FROM Клиент, (SELECT * FROM Подписка) AS Подписка… Ключевое слово «AS» задает имя (псевдоним), по которому следует обращаться к полям временной таблицы, сформированной вложенным запросом, в пределах «главного» запроса.

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

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

Пример 1.11 (трехуровневый запрос, действует аналогично двум предыдущим).

SELECT Клиент.Фамилия, Подписка2.Журнал FROM Клиент, (SELECT * FROM (SELECT * FROM Подписка) ) AS Подписка… В операторе «SELECT», применяемом при создании вложенных запросов, могут быть использованы любые предложения, как и в операторах верхнего уровня («FROM», «WHERE», «GROUP BY», «ORDER BY» …).

Действующий пример с использованием вложенного запроса будет приведен ниже.

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

Пример 1.12. Пусть существует таблица Зарплата(Номер, ФИО, Оклад) Запрос, выводящий ФИО и оклад работников, оклад которых выше среднего, может иметь следующий вид:

SELECT ФИО, Оклад FROM Зарплата WHERE Оклад > (SELECT AVG(Оклад) FROM Зарплата) ORDER BY ФИО;

Для того чтобы выполнить проверку условия «выше среднего», требуется вычислить это самое среднее. Эту работу выполняет вложенный запрос, который обращается к той же самой таблице «Зарплата».

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

Пример 1.13. Запрос выводит список работников, получающих максимальный оклад:

SELECT ФИО, Оклад FROM Зарплата WHERE Оклад = (SELECT MAX(Оклад) FROM Зарплата) ORDER BY ФИО;

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

Пример 1.14. Запрос выполняет те же действия, что и предыдущий:

SELECT ФИО, Оклад FROM Зарплата WHERE Оклад >= ALL (SELECT Оклад FROM Зарплата) ORDER BY ФИО;

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

Пример 1.15. Вывести список работников, оклад которых НЕ САМЫЙ НИЗКИЙ (есть кто-то, кто получает еще меньше):

SELECT ФИО, Оклад FROM Зарплата WHERE Оклад > ANY (SELECT Оклад FROM Зарплата) ORDER BY ФИО;

Пример 1.16. Эту же задачу можно решить иначе, без оператора ANY:

SELECT ФИО, Оклад FROM Зарплата WHERE Оклад <> (SELECT MIN(Оклад) FROM Зарплата) ORDER BY ФИО;

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

Пример 1.17. Список работников с окладом выше среднего:

SELECT ФИО, Оклад FROM Зарплата WHERE Оклад NOT IN ( SELECT Оклад FROM Зарплата WHERE Оклад <= (SELECT AVG(Оклад) FROM Зарплата) ) ORDER BY ФИО;

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

Нетрудно убедиться, что IN можно заменить на «= ANY», а NOT IN на «<> ALL».

Как видно из приведенного примера, вложенные запросы в предложении WHERE также могут иметь больше одного уровня вложенности.

Для обработки вложенных запросов могут использоваться операторы EXISTS (существует) и NOT EXISTS (не существует). Оператор EXISTS возвращает значение «истина», если вложенный запрос содержит хотя бы одну запись (не важно, с какими данными). Оператор NOT EXISTS имеет противоположное назначение.

Пример 1.18. Список работников, у которых НЕ самый низкий оклад (это уже третий вариант запроса для решения той же самой задачи – см. примеры 1.15, 1.16):

SELECT ФИО, Оклад FROM Зарплата AS ЗпWHERE EXISTS (SELECT Оклад FROM Зарплата WHERE Оклад < Зп1.Оклад) ORDER BY ФИО;

Для таблицы «Зарплата» из внешнего запроса определяется псевдоним «Зп1», поскольку имя ее поля используется во вложенном запросе, в котором фигурирует таблица с таким же именем и полями.

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

Предложение UNION … SELECT. Иногда может возникнуть необходимость объединить данные из нескольких источников, как показано на рис. 1.2.

Рис. 1.2. Объединение данных из двух источников Для этой цели используется предложение UNION оператора SELECT (см.

выше общую форму оператора SELECT). В основном операторе SELECT выполняется выборка, формирующая первый результирующий набор, затем, после ключевого слова UNION помещается еще один оператор SELECT, формирующий второй набор. При выполнении такого запроса оба набора объединяются. Объединяться могут данные более чем двух источников. При этом предложение UNION … SELECT будет использовано несколько раз.

Количество и типы данных полей, формируемых каждым из запросов SELECT, должны быть одинаковыми. Имена (псевдонимы) столбцов можно задавать только в основном операторе SELECT. Если их задать в дополнительных операторах, Access их проигнорирует.

Пример 1.19. Есть две таблицы. Одна содержит данные о книгах, вторая – о журналах:

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

SELECT НазваниеЖурнала + " / № " + STR(НомерЖурнала) AS Заголовок, ГодЖурнала AS Год, "Журнал" AS Тип FROM Журнал UNION SELECT НазваниеКниги + " / " + АвторКниги, ГодИзданияКниги, "Книга" FROM Книга ORDER BY Заголовок;

Результат работы запроса:

Предложение ORDER BY применяется не ко второму запросу, а к результату объединения.

По умолчанию при слиянии двух наборов, Access объединяет строки, которые полностью совпадают. Если этого делать не нужно, следует использовать ключевое слово ALL (… UNION ALL SELECT …).

Предложение INTO … Использование в операторе SELECT конструкции вида SELECT <поля> INTO <новая таблица> … позволяет поместить результат запроса в таблицу, которая будет для этого создана.

Пример 1.20.

SELECT ИДЖурнала, НазваниеЖурнала INTO ЖурналНовая FROM Журнал;

Создается таблица с названием «ЖурналНовая» и в нее помещаются следующие данные:

Конструкция TRANSFORM … PIVOT. Данная конструкция предназначена для создания перекрестных запросов (см. [1]). Общая форма:

TRANSFORM <выражение> SELECT <предложение> FROM <предложение> … GROUP BY <предложение> PIVOT <имя поля>;

Конструкция TRANSFORM … PIVOT отсутствует в стандарте языка SQL.

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

Пример 1.21.

TRANSFORM Count(Подписка.Журнал) AS Количество SELECT Список.Фамилия FROM (SELECT Клиент.КодКлиента, Клиент.Фамилия, Журнал.КодЖурнала, Журнал.Название FROM Журнал, Клиент ) AS Список LEFT JOIN Подписка ON (Список.КодЖурнала=Подписка.Журнал) AND (Список.КодКлиента=Подписка.Клиент) GROUP BY Список.Фамилия PIVOT Список.Название;

Результат работы запроса:

Заголовки строк – фамилии клиентов, заголовки столбцов – названия журналов, а содержимое таблицы – количество подписок данного клиента на данный журнал.

Этот запрос отличается от представленного в [1] при описании конструктора запросов тем, что в нем отображены все клиенты, независимо от того, подписаны ли они на какой-то журнал, и все журналы, независимо от того, подписан ли на них кто-то из клиентов.

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

Результат вложенного запроса с помощью LEFT JOIN объединяется с данными из таблицы «Подписка», после чего выполняется группировка строк (по фамилии и названию) и групповая операция Count().

Поскольку этот перекрестный запрос содержит вложенный запрос, его невозможно адекватно представить в режиме конструктора (вернее, конструктор способен отобразить такой запрос, но не дает возможности его создать или отредактировать). Изменять его можно только в режиме SQL.

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

Оператор INSERT INTO. Оператор добавляет новые записи в таблицу.

INSERT INTO имя_таблицы [(имя_столбца[,имя_столбца …])] VALUES (значение[, значение …]) Добавляет в таблицу строку, присваивая указанным полям перечисленные значения.

INSERT INTO имя_таблицы [(имя_столбца[,имя_столбца …])] SELECT … Добавляет в таблицу строки, сформированные оператором SELECT.

Пример 1.22.

INSERT INTO Журнал (НазваниеЖурнала, НомерЖурнала, ГодЖурнала) VALUES ("Новый мир", 4, 1987);

Оператор DELETE FROM DELETE FROM имя_таблицы [WHERE условие] Оператор удаляет из указанной таблицы записи в соответствии с заданным условием. При отсутствии предложения WHERE удаляются все записи.

Пример 1.23.

DELETE FROM Журнал WHERE НазваниеЖурнала="Новый мир";

Оператор UPDATE UPDATE имя_таблицы SET имя_столбца = значение|(SELECT…) [,имя_столбца = значение|(SELECT…)…] [WHERE условие] Устанавливает значения для указанных столбцов в строках таблицы, соответствующих условию отбора. Значение может быть получено как результат вложенного запроса.

Пример 1.24 (увеличение минимального размера оплаты труда – МРОТ).

UPDATE Зарплата SET Оклад = WHERE Оклад < 1200;

Оператор CREATE TABLE. Оператор создает таблицу с заданным именем и набором столбцов.

Упрощенная форма:

CREATE TABLE имя_таблицы ( имя_столбца тип_данных[(размер)] [,имя_столбца тип_данных [(размер)] …] ) Названия и описание основных типов данных, допустимых при создании таблиц, приведены ниже.

Пример 1.25. Создается таблица с именем «МояТаблица», содержащая три столбца (поля) с именами «Поле1», «Поле2» и «Поле3».

CREATE TABLE МояТаблица ( Поле1 COUNTER, Поле2 INTEGER, Поле3 CHAR );

Кроме имен и типов данных, при создании таблицы могут быть заданы различные ограничения. Различают простые и составные ограничения. Простые ограничения задаются в форме атрибутов конкретных столбцов, составные указываются отдельно и могут применяться к нескольким столбцам. Все ограничения могут быть заданы также визуальными средствами – в конструкторе таблиц или схеме данных.

Общая форма оператора:

CREATE TABLE имя_таблицы ( имя_столбца тип[(размер)] [атрибуты] [,имя_столбца тип[(размер)] [атрибуты] …] [,PRIMARY KEY (имя_столбца [, имя_столбца …])] [,FOREIGN KEY (имя_столбца [, имя_столбца …]) REFERENCES имя_таблицы [(имя_столбца [, имя_столбца])] [ON UPDATE CASCADE | SET NULL] [ON DELETE CASCADE | SET NULL] ] ) Некоторые атрибуты, задающие простые ограничения для столбцов:

• NULL или NOT NULL Соответственно разрешает или запрещает помещать пустые значения в этот столбец.

• UNIQUE Накладывает требование уникальности значений. Для любых двух строк таблицы значения в этом столбце не могут быть одинаковыми.

• PRIMARY KEY Объявляет столбец первичным ключом таблицы. Имеет тот же смысл, что и задание ключевого поля в конструкторе таблиц (см. [1]).

• REFERENCES имя_таблицы [(имя_столбца)] [ON UPDATE CASCADE | SET NULL] [ON DELETE CASCADE | SET NULL] Объявляет данный столбец внешним ключом, который связан с заданным полем заданной таблицы. Поле, с которым устанавливается связь, должно быть ключевым, или, по крайней мере, уникальным.

Необязательные атрибуты ON UPDATE и ON DELETE могут принимать значения CASCADE или SET NULL.

Задание ограничения REFERENCES имеет тот же смысл, что и создание связи между таблицами с использованием схемы данных (см. [1]).

Использование атрибутов ON UPDATE и ON DELETE равносильно заданию соответствующих свойств этой связи («Обеспечение целостности данных», «Каскадное обновление связанных полей», «Каскадное удаление связанных записей») в схеме данных.

Пример 1.26.

CREATE TABLE МояТаблица ( Поле1 COUNTER PRIMARY KEY, Поле2 INTEGER UNIQUE NOT NULL, Поле3 CHAR NOT NULL );

Рассмотрим теперь составные ограничения:

• PRIMARY KEY (имя_столбца [, имя_столбца …]) Объявляет первичный ключ таблицы. В скобках перечисляются столбцы, входящие в первичный ключ.

• FOREIGN KEY (имя_столбца [, имя_столбца …]) REFERENCES имя_таблицы [(имя_столбца [, имя_столбца …])] [ON UPDATE CASCADE | SET NULL] [ON DELETE CASCADE | SET NULL] Объявляет столбцы, перечисленные в скобках, внешним ключом. Предложение REFERENCES указывает имя таблицы и ее столбцов, с которыми устанавливается связь.

Пример 1.27. Ниже приведены операторы, создающие три связанные таблицы:

CREATE TABLE Клиент ( КодКлиента COUNTER PRIMARY KEY, Фамилия CHAR NOT NULL, Имя CHAR NOT NULL, Отчество CHAR NOT NULL );

CREATE TABLE Журнал ( КодЖурнала COUNTER PRIMARY KEY, Название CHAR NOT NULL );

CREATE TABLE Подписка ( КодКлиента INTEGER REFERENCES Клиент(КодКлиента), КодЖурнала INTEGER REFERENCES Клиент(КодЖурнала), PRIMARY KEY (КодКлиента, КодЖурнала) );

Таблицы соответствуют схеме данных, рассмотренной в [1]:

Оператор DROP TABLE DROP TABLE имя_таблицы Удаляет таблицу с заданным именем.

2. Основы проектирования баз данных Рассмотренные здесь методы проектирования и анализа моделей баз данных не ориентированы жестко на Access и могут использоваться применительно к любым реляционным СУБД. Вопросы построения таблиц и связей собственно в реляционной модели, реализованной в Access, были рассмотрены в разделе 1.2 и в [1].

Проектирование – важный этап разработки БД. При разработке базы данных необходимо сначала построить и проанализировать модель данных, которые предполагается хранить в БД.

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

Ниже перечислены некоторые возможные последствия ошибок проектирования:

• Избыточность (дублирование) данных.

Например, в информацию о каждом заказе включается имя клиента.

• Аномалии обновления, противоречивость (несогласованность) данных, как следствие их избыточности.

Если в одном из заказов имя клиента введено с ошибкой, его невозможно соотнести с другими заказами этого клиента. С точки зрения СУБД это уже другой клиент.

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

• Аномалии добавления – невозможность разместить данные о некотором объекте без внесения лишних (или ложных) данных.

Невозможно добавить в БД клиента, который не связан с каким-либо заказом.

• Аномалии удаления – невозможность удалить устаревшую информацию без удаления той, которая может быть еще актуальна.

При удалении информации обо всех заказах клиента будет утрачена информация и о самом клиенте.

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

• Инфологическая модель.

Она предназначена для описания предметной области.

Pages:     | 1 || 3 | 4 |   ...   | 6 |






© 2011 www.dissers.ru - «Бесплатная электронная библиотека»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.