Базы данных. Лекции - База данных, лекционный курс. Базы данных лекционный курс
 Скачать 1.23 Mb.
|
|
Адрес
Требуется назначить пилотов на те рейсы, пункт отправления которых совпадает с аэропортом, к которому приписан пилот. Эквисоединение отношений Маршрут и Адрес по столбцам Пункт_отправления и Аэропорт:
Основное отличие эквисоединения от естественного в том, что при последнем соединяемые столбцы не повторяются. По справедливому замечанию Дейта, реляционная алгебра Кодда обладает несколькими недостатками. Во-первых, восемь перечисленных операций по охвату своих функций, с одной стороны, избыточны, так как минимально необходимый набор составляют пять операций: объединение, вычитание, произведение, проекция и выборка. Три другие операции (пересечение, соединение и деление) можно определить через пять минимально необходимых. Во-вторых, этих восьми операций недостаточно для построения реальной СУБД на принципах реляционной алгебры. Требуются расширения, включающие операции: переименования атрибутов, образования новых вычисляемых атрибутов, вычисления итоговых функций, построения сложных алгебраических выражений, присвоения, сравнения и т. д. 4.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. Таблица 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 имя {PRIMARY KEY (ключевое_1[, ключевое_2 [, ...]]) | UNIQUE (уникальное_1[, уникальное_2 [, ...]]) | NOT NULL (непустое_1[, непустое_2 [, ...]]) | FOREIGN KEY [NO INDEX] (ссылка_1[, ссылка_2 [, ...]]) REFERENCES внешняя таблица [(внешнее_поле_1 [, внешнее_поле_2 [, ...]])] [ON UPDATE CASCADE | SET NULL] [ON DELETE CASCADE | SET NULL]} Аргументы предложения CONSTRAINT: имя - имя создаваемого индекса; ключевое_1, ключевое_2 - имена одного или нескольких полей, которые следует обозначить как ключевые; уникальное_1, уникальное_2 - имена одного или нескольких полей, которые следует включить в уникальный индекс; непустое_1, непустое_2 - имена одного или нескольких полей, в которых запрещаются значения Null; ссылка_1, ссылка_2 - имена одного или нескольких полей, включенных во внешний ключ, которые содержат ссылки на поля в другой таблице; внешняя таблица - имя внешней таблицы, которая содержит поля, указанные с помощью аргумента внешнее поле; внешнее_поле_1, внешнее_поле_2 - имена одного или нескольких полей внешней таблицы, указанные с помощью ссылки_1 и ссылки_2. Если адресуемое поле является ключом внешней таблицы, данное предложение можно опустить. Предложение CONSTRAINT позволяет создавать для поля индекс одного из описанных ниже типов: Для создания уникального индекса используется зарезервированное слово UNIQUE. Это означает, что в таблице не может быть двух записей, имеющих одно и то же значение в этом поле. Уникальный индекс создается для любого поля или любой группы полей. Если в таблице определен составной уникальный индекс, то комбинация значений включенных в него полей должна быть уникальной для каждой записи таблицы, хотя отдельные поля и могут иметь совпадающие значения. Для создания ключа таблицы, состоящего из одного или нескольких полей, используется зарезервированные слова PRIMARY KEY. Все значения ключа таблицы должны быть уникальными и отличаться от значения Null. Кроме того, в таблице может быть только один ключ. Примечание. Зарезервированные слова PRIMARY KEY нельзя использовать при создании индекса в таблице, в которой уже определен ключ; в противном случае возникнет ошибка. Для создания внешнего ключа используются зарезервированные слова FOREIGN KEY. Если ключ внешней таблицы состоит из нескольких полей, необходимо использовать предложение CONSTRAINT, предназначенное для создания составного индекса. При этом следует перечислить все поля, содержащие ссылки на поля во внешней таблице, а также указать имя внешней таблицы и имена полей внешней таблицы, на которые ссылаются поля, перечисленные выше, причем в том же порядке. Если адресуемые поля являются ключами внешней таблицы, указывать эти поля не следует. Ограничения для внешних ключей определяют конкретные действия, выполняемые в случае изменения значения соответствующего ключа: Например, инструкция CREATE TABLE Книги (Код_книги TEXT(7) NOT NULL CONSTRAINT ключ2 PRIMARY KEY, Раздел TEXT(5) NOT NULL, Автор TEXT(40) NOT NULL, Название TEXT(50) NOT NULL); создаст таблицу с четырьмя текстовыми полями, определив первое из них как ключевое. Инструкция CREATETABLE Выдача_возврат (Код_книги TEXT(7) NOTNULL, Код_читателя TEXT(5) NOTNULL, Дата_выдачи DATETIMENOTNULL, Дата_возврата DATETIME, CONSTRAINT Ключ4 PRIMARYKEY (Код_книги, Дата_выдачи)); создаст таблицу с четырьмя полями и ключевым полем, включающим два из них. Инструкция ALTER TABLE. Назначение: изменение структуры таблицы, созданной с помощью инструкции CREATE TABLE. Синтаксис: ALTER TABLE таблица {ADD {COLUMN поле тип [(размер)] [NOT NULL] [CONSTRAINT индекс] | ALTER COLUMN тип поля[(размер)] | CONSTRAINT составной_индекс} | DROP {COLUMN поле | CONSTRAINT имя_индекса} } Аргументы инструкции ALTER TABLE: таблица - имя изменяемой таблицы; поле - имя поля, добавляемого в таблицу или заменяемого в таблице или удаляемого из нее; тип - тип данных поля; размер - размер поля в знаках (только для текстовых и двоичных полей); индекс - индекс для поля; составной_индекс - описание составного индекса, добавляемого к таблице; имя_индекса - имя составного индекса, который следует удалить. С помощью инструкции ALTER TABLE существующую таблицу можно изменить несколькими способами: Добавить новое поле в таблицу с помощью зарезервированных слов ADD COLUMN. В этом случае необходимо указать имя поля, его тип и (для текстовых и двоичных полей) необязательный размер. Например, инструкция ALTER TABLE Книги ADD COLUMN Год INTEGER NOT NULL; добавит в таблицу Книги целочисленное поле Год. Изменить тип существующего поля с помощью зарезервированных слов ALTER COLUMN. В этом случае необходимо указать имя поля, его тип и (для текстовых и двоичных полей) необязательный размер. Например, инструкция ALTERTABLE Разделы ALTERCOLUMN Код_раздела TEXT(5); позволит в таблице Разделы изменить тип поля Код (первоначально определенный как INTEGER), переопределив это поле как текстовое. Добавить составной индекс с помощью зарезервированных слов ADD CONSTRAINT. Удалить поле с помощью зарезервированных слов DROP COLUMN. В этом случае необходимо указать только имя поля. Например, инструкция ALTER TABLE Книги DROP COLUMN Год; удалит из таблицы Книги поле Год. Удалить составной индекс с помощью зарезервированных слов DROP CONSTRAINT. В этом случае необходимо указать только имя составного индекса, следующее за зарезервированным словом CONSTRAINT. Инструкция CREATE INDEX. Назначение: создание нового индекса для существующей таблицы. Синтаксис: CREATE [ UNIQUE ] INDEX индекс ON таблица (поле [ASC|DESC][, поле [ASC|DESC], ...]) [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }] Аргументы: индекс - имя создаваемого индекса; таблица - имя существующей таблицы, для которой создается индекс; поле - имена одного или нескольких полей, включаемых в индекс. Для создания простого индекса (состоящего из одного поля) имя поля в круглых скобках вводится сразу после имени таблицы. Для создания составного индекса (состоящего из нескольких полей) перечисляются имена всех этих полей. Для расположения элементов индекса в убывающем порядке используется зарезервированное слово DESC; в противном случае будет принят порядок по возрастанию. Дополнительные сведения: Зарезервированное слово UNIQUE используется для запрета совпадения значений индексированных полей в разных записях. Необязательное предложение WITH позволяет задать условия на значения. Например: С помощью параметра DISALLOW NULL запретить значения Null в индексированных полях новых записей. С помощью параметра IGNORE NULL запретить включение в индекс записей, имеющих значения Null в индексированных полях. С помощью зарезервированного слова PRIMARY назначить индексированные поля ключом. Такой индекс по умолчанию является уникальным, следовательно, зарезервированное слово UNIQUE можно опустить. Зарезервированное слово PRIMARY нельзя использовать при создании нового индекса в таблице, в которой уже определен ключ; в противном случае возникнет ошибка. Например, инструкция CREATE INDEX Ключ1 ON Разделы(Код_раздела) WITH PRIMARY; определит в таблице Разделы поле Код_раздела как ключевое. Инструкция CREATE INDEX Индекс ON Книги(Автор); создаст в таблице Книги индекс по полю Автор. Инструкция DROP. Назначение: удаление таблицы, процедуры, представления из базы данных или удаление индекса из таблицы. Синтаксис: DROP {TABLE таблица | INDEX индекс ON таблица | PROCEDURE процедура | VIEW представление} Аргументы: таблица - имя таблицы, которую следует удалить или из которой следует удалить индекс; процедура - имя удаляемой процедуры; представление - имя удаляемого представления; индекс - имя индекса, удаляемого из таблицы. Например, инструкция DROP INDEX Индекс ON Книги; удалит из таблицы Книги индекс по полю Автор. Инструкция DROP TABLE Книги; удалит из базы данных таблицу Книги. Инструкция SELECT. Назначение: возвращение данных из базы данных в виде набора записей. Синтаксис: SELECT [предикат] {* | таблица.* | [таблица.]поле_1 [AS псевдоним_1] [, [таблица.]поле_2 [AS псевдоним_2] [, ...]]} FROM выражение [, ...] [IN внешняя_база_данных] [WHERE... ] [GROUP BY... ] [HAVING... ] [ORDER BY... ] [WITH OWNERACCESS OPTION] Аргументы: предикат - один из следующих предикатов отбора: ALL, DISTINCT, DISTINCTROW или TOP. Предикаты используются для ограничения числа возвращаемых записей. По умолчанию используется предикат ALL; * - указывает, что выбраны все поля заданной таблицы или таблиц; таблица - имя таблицы, из которой должны быть отобраны записи; поле_1, поле_2 - имена полей, из которых должны быть отобраны данные. Если включить несколько полей, они будут извлекаться в указанном порядке; псевдоним_1, псевдоним_2 - имена, которые станут заголовками столбцов вместо исходных названий столбцов в таблице; выражение - имена одной или нескольких таблиц, которые содержат отбираемые данные; внешняя_база_данных - имя базы данных, которая содержит таблицы, указанные с помощью аргумента «выражение», если они не находятся в текущей базе данных. Минимальный синтаксис инструкции SELECT: SELECT поля FROM таблица. Например, инструкция SELECT * FROM Книги; отберет все поля из таблицы Книги. Инструкция SELECT Книги.Код_книги, Книги.Автор, Книги.Название FROM Книги; отберет три поля из таблицы Книги. Имя поля заключается в квадратные скобки, если оно содержит пробелы или знаки препинания. Предложение WHERE определяет, какие записи из таблиц, перечисленных в предложении FROM, следует включить в результат выполнения инструкции SELECT. Предложение WHERE не является обязательным, однако, если оно присутствует, то должно следовать после предложения FROM. Например, инструкция SELECT Книги.Автор, Книги.Название FROM Книги WHERE (Книги.Автор=”Конан Дойл”); отберет из таблицы Книги все книги Конан Дойла. С помощью инструкции SELECT Выдача_возврат.Код_книги FROM Выдача_возврат WHERE (Выдача_возврат.Дата_возврата IS NULL); можно найти все коды книг, которые находятся на руках (выданы). Предложение ORDER BY сортирует записи, полученные в результате запроса, в порядке возрастания (ASC) или убывания (DESC) на основе значений указанного поля или полей. Синтаксис: ORDER BY поле_1 [ASC | DESC] [, поле_2 [ASC | DESC]][, ...]. Если предложение ORDER BY содержит несколько полей, то сначала записи сортируются по первому полю. Затем записи, имеющие совпадающие значения в первом поле, сортируются по второму полю и т. д. По умолчанию используется порядок сортировки по возрастанию (от «A» до «Я» и от 0 до 9). Предложение ORDER BY не является обязательным. Например, инструкция SELECT Выдача_возврат.Код_книги FROM Выдача_возврат WHERE (Выдача_возврат.Дата_возврата IS NULL) ORDER BY Выдача_возврат.Код_книги; выведет коды книг, находящихся на руках, в алфавитном порядке. Предложение GROUP BY объединяет записи с одинаковыми значениями в указанном списке полей в одну запись. Если инструкция SELECT содержит статистическую функцию SQL, например Sum или Count, то для каждой записи будет вычислено итоговое значение. Синтаксис: GROUP BY группируемыеПоля. При использовании предложения GROUP BY все поля в списке полей инструкции SELECT должны быть либо включены в предложение GROUP BY, либо использоваться в качестве аргументов статистической функции SQL. Значения NULL которые находятся в полях, заданных в предложении GROUP BY, группируются и не опускаются. Однако статистические функции SQL не обрабатывают значения NULL. Предложение GROUP BY не является обязательным. Например, инструкция SELECT Книги.Раздел, Count(Книги.Раздел) AS [Количество книг] FROM Книги GROUP BY Книги.Раздел; позволит подсчитать количество книг из каждого раздела. А с помощью инструкции SELECT Выдача_возврат.Код_книги, Count(Выдача_возврат.Код_книги) AS [Сколько раз брали книгу] FROM Выдача_возврат GROUP BY Выдача_возврат.Код_книги; можно определить популярность книг, подсчитав для каждой книги количество раз, которое она была на руках. Предложение HAVING определяет, какие сгруппированные записи отображаются при использовании инструкции SELECT с предложением GROUP BY. После того как записи будут сгруппированы с помощью предложения GROUP BY, предложение HAVING отберет те из полученных записей, которые удовлетворяют условиям отбора, указанным в предложении HAVING. Предложение HAVING не является обязательным. Синтаксис: HAVING условиеГруппировки. Например, инструкция SELECT Выдача_возврат.Код_книги, Count(Выдача_возврат.Код_книги) AS [Сколько раз брали книгу] FROM Выдача_возврат GROUP BY Выдача_возврат.Код_книги HAVING Count(Выдача_возврат.Код_книги)>5; включит в список популярных книг только те из них, которые более пяти раз были на руках. Нередко для построения запроса необходимы данные не из одной, а из нескольких таблиц. Для этого используется какая-либо операция SQL: INNER JOIN, LEFT JOIN или RIGHT JOIN. Операция INNER JOIN объединяет только те записи из двух таблиц, в которых связываемые поля обеих таблиц содержат одинаковые значения. LEFT JOIN используется для создания левого объединения, при котором все записи из первой (левой) таблицы включаются в результат выборки, даже если во второй таблице нет соответствующих им записей. RIGHT JOIN используется для создания правого объединения, при котором все записи из второй (правой) таблицы включаются в результат выборки, даже если в первой таблице нет соответствующих им записей. Синтаксис: FROM таблица_1 INNER JOIN | LEFT JOIN | RIGHT JOIN таблица_2 ON таблица_1.поле_1 оператор таблица_2.поле_2, где оператор – любой оператор сравнения. Например, инструкция SELECT Книги.Код_книги, Книги.Раздел, Книги.Автор, Книги.Название FROM Книги LEFT JOIN Книги_на_руках ON Книги.Код_книги = Книги_на_руках.Код_книги WHERE (Книги_на_руках.Код_книги IS NULL); выдаст список книг, имеющихся в данный момент в библиотеке (не выданных). В приведенной инструкции Книги_на_руках - это имя запроса, отбирающего коды всех выданных книг. Инструкция UPDATE. Назначение: создание запроса на обновление, который изменяет значения полей указанной таблицы на основе заданного условия отбора. Синтаксис: UPDATE таблица SET новое_значение WHERE условие_отбора; Аргументы инструкции UPDATE: таблица - имя таблицы, данные в которой следует изменить; новое_значение - выражение, определяющее значение, которое должно быть вставлено в указанное поле обновленных записей; условие_отбора - выражение, отбирающее записи, которые должны быть изменены. При выполнении этой инструкции будут изменены только записи, удовлетворяющие указанному условию. Инструкцию UPDATE особенно удобно использовать для изменения сразу многих записей или в том случае, если записи, подлежащие изменению, находятся в разных таблицах. Например, инструкция UPDATE Выдача_возврат SET Выдача_возврат.Дата_возврата = DATE() WHERE (Выдача_возврат.Код_книги=”Д1”); изменит значение NULL поля Дата_возврата для книги с кодом Д1 на значение текущей даты. Инструкция INSERT INTO. Назначение: добавление записи или записей в таблицу. Синтаксис запроса на добавление нескольких записей: INSERTINTO назначение [(поле_1[, поле_2[, ...]])] [IN внешняя_база_данных] SELECT [источник.]поле_1[, поле_2[, ...]] FROM выражение Синтаксис запроса на добавление одной записи: INSERT INTO назначение [(поле_1[, поле_2[, ...]])] VALUES (значение_1[, значение_2[, ...]]) Аргументы инструкции INSERT INTO: назначение - имя таблицы или запроса, в который добавляются записи; поле_1, поле_2 - имена полей для добавления данных, если они следуют за аргументом назначение; имена полей, из которых берутся данные, если они следуют за аргументом источник; внешняя_база данных - путь к внешней базе данных; источник - имя таблицы или запроса, откуда копируются записи; выражение - имена таблицы или таблиц, откуда вставляются данные. Это выражение может быть именем отдельной таблицы или результатом операции INNER JOIN, LEFT JOIN или RIGHT JOIN а также сохраненным запросом; значение_1, значение_2 - значения, добавляемые в указанные поля новой записи. Каждое значение будет вставлено в поле, занимающее то же положение в списке: значение_1 вставляется в поле_1 новой записи, значение_2 - в поле_2 и т. д. Каждое значение текстового поля следует заключать в кавычки. Например, инструкция INSERTINTO Утерянные_книги (Код_книги) SELECT Книги.Код_книги FROM Книги WHERE (Книги.Название="Мудрость отца Брауна"); добавит в таблицу Утерянные_книги код соответствующей книги. Инструкция NSERTINTO Книги (Код_книги, Раздел, Автор, Название) VALUES ("Д5", "Д", "Неизвестный", "Интересная"); добавит в таблицу Книги информацию по новой книге. Инструкция DELETE. Назначение: удаление записей из одной или нескольких таблиц, перечисленных в предложении FROM, которые удовлетворяют предложению WHERE. Синтаксис: DELETE [таблица.*] FROM таблица WHERE условие_отбора Аргументы инструкции DELETE: таблица - необязательное имя таблицы, из которой удаляются записи; таблица - имя таблицы, из которой удаляются записи; условие_отбора - выражение, определяющее удаляемые записи. Дополнительные сведения: Инструкция DELETE особенно удобна для удаления большого количества записей. Инструкцию DELETE можно использовать для удаления записей из таблиц, связанных отношением «один ко многим» с другими таблицами. Операции каскадного удаления приводят к удалению записей из таблиц, находящихся на стороне отношения «многие», когда в запросе удаляется соответствующая им запись на стороне «один». Например, в отношении между таблицами Книги и Выдача_возврат, таблица Книги расположена на стороне «один», а таблица Выдача_возврат - на стороне «многие». Если разрешить каскадное удаление, то удаление записи из таблицы Книги приведет к удалению соответствующих записей из таблицы Выдача_возврат. Запрос на удаление удаляет записи целиком, а не только содержимое указанных полей. Чтобы удалить данные в конкретном поле, следует создать запрос на обновление, который заменит имеющееся значение на значение NULL. Примечание. Записи, удаленные с помощью запроса на удаление, нельзя восстановить. Например, инструкция DELETE Выдача_возврат.* FROM Выдача_возврат WHERE ((Выдача_возврат.Дата_выдачи < #31/12/2000#) AND (Выдача_возврат.Дата_возврата ISNOTNULL)); удалит из таблицы Выдача_возврат все записи по книгам, выданным до 31 декабря 2000 года и возвращенным в библиотеку. В заключение отметим, что, по словам Дейта, язык SQL является гибридом реляционной алгебры и реляционного исчисления. В нем есть и элементы алгебры, и элементы исчисления. Кроме того, язык SQL обладает реляционной полнотой. |
