10 ЭЛЕМЕНТЫ ЯЗЫКА SQL Содержание темы 10 1

10. ЭЛЕМЕНТЫ ЯЗЫКА SQL Содержание темы: 10. 1. Стандарт языка SQL 10. 2. Реализации языка SQL 10. 3. Расширения языка SQL 10. 4. Основные категории команд языка SQL 10. 5. Запись SQL-операторов 10. 6. Описание учебной базы данных 10. 7. Манипулирование данными с использованием языка SQL 10. 8. Определение ограничений целостности данных

10. 1. Стандарт языка SQL Рост количества данных, необходимость их хранения и обработки привели к тому, что возникла потребность в создании стандартного языка баз данных (БД), который мог бы функционировать в многочисленных компьютерных системах различных видов. С его помощью пользователи могут манипулировать данными независимо от того, работают ли они на персональном компьютере, сетевой рабочей станции или универсальной ЭВМ. Язык SQL (Structured Query Language − язык структурных запросов; другое название, встречающееся в литературе, − «структурированный язык запросов» ) в настоящее время получил очень широкое распространение и превратился в стандартный язык реляционных БД. 1986 г. − Американским национальным институтом стандартов (ANSI) был выпущен стандарт на язык SQL. 1987 г. − Международная организация стандартов (ISO) приняла стандарт на язык SQL в качестве международного. Нынешний стандарт SQL известен под названием SQL/92.

10. 2. Реализации языка SQL Реализация языка SQL − программный продукт SQL соответствующего производителя. Для расширения функциональных возможностей многие разработчики, придерживающиеся принятых стандартов, добавляют к стандартному языку SQL различные расширения. Стандарты требуют от любой реализации языка SQL наличия определенных характеристик и отражают основные тенденции, которые: n приводят к совместимости между всеми реализациями; n способствуют повышению значимости программистов SQL и пользователей реляционных БД на современном рынке программного обеспечения.

10. 3. Расширения языка SQL Все конкретные реализации языка SQL несколько отличаются друг от друга. В интересах производителей гарантировать, чтобы их реализация соответствовала современным стандартам ANSI в части переносимости и удобства работы пользователей. Тем не менее, каждая реализация SQL содержит усовершенствования, отвечающие требованиям того или иного сервера баз данных. Эти усовершенствования (расширения языка SQL) представляют собой дополнительные команды и опции, являющиеся добавлениями к стандартному пакету и доступные в данной конкретной реализации. В настоящее время язык SQL поддерживают десятки СУБД различных типов, разработанных для самых разнообразных вычислительных платформ.

10. 4. Основные категории команд языка SQL: n n n DDL (Data Definition Language) – язык определения данных; DML (Data Manipulation Language) – язык манипулирования данными; DQL (Data Query Language) – язык запросов; DCL (Data Control Language) – язык управления данными; команды администрирования данных; команды управления транзакциями.

Операторы определения данных (DDL) Позволяют создавать и изменять структуру объектов базы данных, а также удалять объекты базы данных Будут рассмотрены далее ОПЕРАТОР ДЕЙСТВИЕ CREATE TABLE Создает новую таблицу БД DROP TABLE Удаляет таблицу из БД ALTER TABLE Изменяет структуру существующей таблицы CREATE VIEW Создает виртуальную таблицу, соответствующую некоторому SQL-запросу DROP VIEW Удаляет ранее созданное представление ALTER VIEW Изменяет ранее созданное представление CREATE INDEX Создает индекс для некоторой таблицы в целях обеспечения быстрого доступа к ней по атрибутам, входящим в индекс DROP INDEX Удаляет ранее созданный индекс

Операторы манипулирования данными (DML) – будут рассмотрены далее Используются для манипулирования информацией внутри объектов базы данных ОПЕРАТОР ДЕЙСТВИЕ DELETE Удаляет одну или несколько строк, соответствующих условиям фильтрации, из базовой таблицы INSERT Вставляет одну строку в базовую таблицу. Допустимы модификации данного оператора, при которых сразу несколько строк могут быть перенесены из одной таблицы или запроса в базовую таблицу UPDATE Обновляет значения одного или нескольких столбцов в одной или нескольких строках, соответствующих условиям фильтрации

Оператор запросов (DQL) Используется для формирования запросов к базе данных ОПЕРАТОР SELECT ДЕЙСТВИЕ Позволяет сформировать результирующее отношение, соответствующее запросу

Операторы управления данными (DCL) Используются для работы с объектами, связанными с доступом к данным ОПЕРАТОР ДЕЙСТВИЕ DECLARE Определяет курсор для запроса, задает имя и определяет связанный с ним запрос к БД OPEN Открывает курсор и объект БД FETCH Устанавливает курсор на определенную запись и считывает ее CLOSE Закрывает курсор и объект БД EXECUTE Выполняет сгенерированный ранее запрос

Операторы администрирования данных Позволяют осуществлять контроль за выполняемыми действиями и анализировать операции базы данных ОПЕРАТОР ДЕЙСТВИЕ CREATE DATABASE Создает новую БД DROP DATABASE Удаляет БД ALTER DATABASE Изменяет набор основных объектов в БД CREATE DBAREA Создает новую область хранения БД DROP DBAREA Удаляет область хранения БД ALTER DBAREA Изменяет ранее созданную область хранения ALTER PASSWORD Изменяет пароль для всей БД GRANT Предоставляет права доступа к БД или отдельным ее элементам REVOKE Лишает права доступа к БД или отдельным ее элементам

Операторы управления транзакциями Транзакция – неделимая с точки зрения воздействия на БД последовательность операторов манипулирования данными, приводящая к одному из двух результатов: либо последовательность выполняется (если все операторы успешно выполнены), либо откатывается (если хотя бы один оператор не может быть успешно выполнен). ОПЕРАТОР ДЕЙСТВИЕ COMMIT Завершает комплексную взаимосвязанную обработку информации, объединенную в транзакцию ROLLBACK Отменяет изменения, проведенные в ходе выполнения транзакций SAVEPOINT Сохраняет промежуточное состояние БД, т. е. помечает его для того, чтобы в дальнейшем

10. 5. Запись SQL-операторов Язык, в терминах которого дается описание языка SQL, называется метаязыком. Синтаксические определения обычно задают с помощью специальной символики, называемой Бэкуса. Науэра формулами (БНФ). Прописные буквы используются для записи зарезервированных слов и должны указываться в операторах точно так, как это будет показано. Строчные буквы употребляются для записи слов, определяемых пользователем.

10. 5. Запись SQL-операторов (продолжение) Применяемые в нотации БНФ символы и их обозначения: Симво л Обозначение : : = Равно по определению | Необходимость выбора одного из нескольких приведенных значений <…> Описанная с помощью метаязыка структура языка {…} Обязательный выбор некоторой конструкции из списка […] Необязательный выбор некоторой конструкции из списка [, …n] Необязательная возможность повторения конструкции от нуля до нескольких раз

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

10. 7. Манипулирование данными с использованием языка SQL

10. 7. 1. Запрос добавления Оператор INSERT применяется для добавления записей в таблицу. Формат оператора: <оператор_вставки>: : =INSERT INTO <имя_таблицы> [(имя_столбца [, . . . n])] {VALUES (значение[, . . . n]) | }

10. 7. 1. Запрос добавления (продолжение) Первая форма оператора INSERT с параметром VALUES предназначена для вставки единственной строки в указанную таблицу. Список столбцов указывает столбцы, которым будут присвоены значения в добавляемых записях. Список может быть опущен, тогда подразумеваются все столбцы таблицы, причем в определенном порядке, установленном при создании таблицы. Список значений должен следующим образом соответствовать списку столбцов: n количество элементов в обоих списках должно быть одинаковым; n должно существовать прямое соответствие между позицией одного и того же элемента в обоих списках, поэтому первый элемент списка значений должен относиться к первому столбцу в списке столбцов, второй – ко второму столбцу и т. д. ; n типы данных элементов в списке значений должны быть совместимы с типами данных соответствующих столбцов таблицы.

10. 7. 1. Запрос добавления (продолжение) Пример 1. Добавить в таблицу «Товар» новую запись. INSERT INTO Товар (Код. Товара, Название, Тип, Сорт, Цена, Остаток, Город. Товара) VALUES ("111111", "Чистюля", "салфетки", "", 50, 100, "Москва") Если столбцы таблицы «Товар» указаны в полном составе и в том порядке, в котором они перечислены при создании таблицы «Товар» , оператор можно упростить: INSERT INTO Товар VALUES ("111111", "Чистюля", "салфетки", "", 50, 100, "Москва")

10. 7. 1. Запрос добавления (продолжение) Вторая форма оператора INSERT с параметром SELECT позволяет скопировать множество строк из одной таблицы в другую. Предложение SELECT может представлять собой любой допустимый оператор SELECT. Вставляемые в указанную таблицу строки будут в точности соответствовать строкам результирующей таблицы, созданной при выполнении вложенного запроса. Все ограничения, указанные выше для первой формы оператора SELECT, применимы и в этом случае. Т. к. оператор SELECT в общем случае возвращает множество записей, то оператор INSERT в такой форме приводит к добавлению в таблицу аналогичного числа новых записей.

10. 7. 1. Запрос добавления (продолжение) Пример 2. Добавить в итоговую таблицу сведения об общей сумме ежемесячных продаж каждого типа товара. INSERT INTO Итог(Тип, Месяц, Стоимость) SELECT Товар. Тип, Month(Сделка. Дата) AS Месяц, Sum(Товар. Цена*Сделка. Количество) AS Стоимость FROM Товар INNER JOIN Сделка ON Товар. Код. Товара=Сделка. Код. Товара GROUP BY Товар. Тип, Month(Сделка. Дата)

10. 7. 2. Запрос удаления Оператор DELETE предназначен для удаления группы записей из таблицы. Формат оператора: <оператор_удаления>: : =DELETE FROM <имя_таблицы> [WHERE <условие_отбора>] Если предложение WHERE присутствует, из таблицы удаляются записи, удовлетворяющие условию отбора. Если опустить предложение WHERE, из таблицы будут удалены все записи, однако сама таблица сохранится. Пример 3. Удалить все прошлогодние сделки. DELETE FROM Сделка WHERE Year(Сделка. Дата)=Year(Date())-1

10. 7. 3. Запрос обновления Оператор UPDATE применяется для изменения значений в группе записей или в одной записи указанной таблицы. Формат оператора: <оператор_изменения>: : =UPDATE <имя_таблицы> SET имя_столбца=<выражение>[, . . . n] [WHERE <условие_отбора>] В предложении SET указываются имена одного и более столбцов, данные в которых необходимо изменить. Предложение WHERE является необязательным. Если оно опущено, значения указанных столбцов будут изменены во всех строках таблицы. Если предложение WHERE присутствует, то обновлены будут только те строки, которые удовлетворяют условию отбора. Выражение представляет собой новое значение соответствующего столбца и должно быть совместимо с ним по типу данных.

10. 7. 3. Запрос обновления (продолжение) Пример 4. Увеличить цену товаров первого сорта на 25%. UPDATE Товар SET Товар. Цена=Товар. Цена*1. 25 WHERE Товар. Сорт="первый"

10. 8. Определение ограничений целостности данных

10. 8. 1. Таблицы с ограничениями в стандарте языка SQL При создании баз данных большое внимание должно быть уделено средствам поддержания данных в целостном состоянии. Рассмотрим предусмотренные стандартом языка SQL функции, которые предназначены для поддержания целостности данных. Эта поддержка включает средства задания ограничений, они вводятся с целью защиты базы данных от нарушения согласованности сохраняемых в ней данных. Типы поддержки целостности данных (подробнее см. п. 10. 8. 1. 1): обязательные данные; n требования конкретного предприятия; n ограничения для доменов полей; n целостность сущностей; n ссылочная целостность. Большая часть перечисленных ограничений задается в операторах CREATE TABLE и ALTER TABLE. n

10. 8. 1. 1. Создание таблиц Оператор создания таблицы CREATE TABLE включает средства определения требований целостности данных, а также другие конструкции. Имеются очень большие вариации в наборе функциональных возможностей этого оператора, реализованных в различных диалектах языка SQL. Рассмотрим назначение параметров команды CREATE TABLE, используемых для поддержки целостности данных. Обязательные данные. Для некоторых столбцов требуется наличие в каждой строке таблицы допустимого значения, отличного от значения NULL (NULL – значение для указания отсутствующих или неизвестных данных). Для заданий ограничений подобного типа стандарт SQL предусматривает использование спецификации NOT NULL. Требования конкретного предприятия. Обновления данных в таблицах могут быть ограничены существующими в организации требованиями (бизнес-правилами). Стандарт SQL позволяет реализовать бизнес-правила предприятий с помощью предложения CHECK и ключевого слова UNIQUE.

10. 8. 1. 1. Создание таблиц (продолжение) Ограничения для доменов полей. Каждый столбец может иметь собственный домен – некоторый набор допустимых значений. Стандарт SQL предусматривает два различных механизма определения доменов. Первый состоит в использовании предложения CHECK, позволяющего задать требуемые ограничения для столбца или таблицы в целом, а второй предполагает применение оператора CREATE DOMAIN. Целостность сущностей. Первичный ключ таблицы должен иметь уникальное непустое значение в каждой строке. Стандарт SQL позволяет задавать подобные требования поддержки целостности данных с помощью фразы PRIMARY KEY. В пределах таблицы она может указываться только один раз. Однако существует возможность гарантировать уникальность значений и для любых альтернативных ключей таблицы, что обеспечивает ключевое слово UNIQUE. Кроме того, при определении альтернативных ключей рекомендуется использовать и спецификаторы NOT NULL.

10. 8. 1. 1. Создание таблиц (продолжение) Ссылочная целостность Внешние ключи представляют собой столбцы или наборы столбцов, предназначенные для связывания каждой из строк дочерней таблицы, содержащей этот внешний ключ, со строкой родительской таблицы, содержащей соответствующее значение потенциального ключа. Стандарт SQL предусматривает механизм определения внешних ключей с помощью предложения FOREIGN KEY, а фраза REFERENCES определяет имя родительской таблицы. При использовании этого предложения СУБД отклонит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значение внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы. Когда действия СУБД выполняются при поступлении операторов UPDATE и DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблицы, то они зависят от правил поддержки ссылочной целостности, указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY.

10. 8. 1. 1. Создание таблиц (продолжение) Ссылочная целостность (продолжение) Если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие правила поддержки ссылочной целостности: CASCADE – выполняется удаление строки из родительской таблицы, сопровождающееся автоматическим удалением всех ссылающихся на нее строк дочерней таблицы; n SET NULL – выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL; n SET DEFAULT – выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию; n NO ACTION – операция удаления строки из родительской таблицы отменяется. Это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена. Те же самые правила применяются в языке SQL и тогда, когда значение потенциального ключа родительской таблицы обновляется. n

10. 8. 1. 2. Изменение и удаление таблиц Для внесения изменений в уже созданные таблицы стандартом SQL предусмотрен оператор ALTER TABLE, предназначенный для выполнения следующих действий: добавление в таблицу нового столбца; n удаление столбца из таблицы; n добавление в определение таблицы нового ограничения; n удаление из определения таблицы существующего ограничения; n задание для столбца значения по умолчанию; n отмена для столбца значения по умолчанию. Параметры оператора ALTER TABLE имеют то же самое назначение, что и в определении оператора CREATE TABLE (см. п. 10. 8. 1. 1). n Для удаления таблицы используется команда DROP TABLE.

Продолжение следует…

Литература Разработка и эксплуатация автоматизированных информационных систем: учебник для студ. сред. проф. образования / Д. Э. Фуфаев, Э. В. Фуфаев. – М. : Издательский центр «Академия» , 2010. – 304 с. (имеется в библиотеке КТИ) 2. Разработка и эксплуатация автоматизированных информационных систем: учеб. пособие / Под ред. проф. Л. Г. Гагариной. – М. : ИД «ФОРУМ» : ИНФРА-М, 2011. – 384 с. (имеется в библиотеке КТИ) 3. http: //www. intuit. ru/studies/courses/5/5/info – Основы SQL / Л. Полякова. – Национальный Открытый Университет «ИНТУИТ» , опубликовано 10. 09. 2004 г. 1.