Язык SQL (Structured Query Language, язык структурированных запросов) – специализированный язык, предназначенный для написания запросов к реляционной БД. Основной единицей этого языка является SQL-оператор. Запрос к БД представляет собой один или несколько операторов языка, интерпретируемых СУБД и позволяющих создать, модифицировать, удалить саму БД, а также ее объекты.
Прототипом SQL является язык SEQUEL, который был разработан в начале 1970-х гг. компанией IBM для экспериментальной СУБД SystemR. В 1986 г. ANSI (American National Standards Institute, Американский национальный институт стандартизации) принял первый стандарт языка SQL. Позже было опубликовано еще несколько стандартов, но наибольшую известность среди них получил SQL-92 (другое наименование SQL-2), названный так по году (1992) выпуска, который по сей день является ядром SQL-диалектов большинства современных реляционных СУБД. На сегодняшний день действует еще ряд стандартов: SQL-2003, SQL-2006, SQL-2008, которые в той или иной степени поддерживаются различными СУБД.
Операторы SQL делятся на несколько непересекающихся групп: DDL (Data Definition Language, язык определения данных), DCL (Data Control Language, язык управления данными), DML (Data Manipulation Language, язык манипулирования данными) и TCL (Transaction Control Language, язык управления транзакциями) (рис. 3.1).
Рис. 3.1. Структура языка SQL
Операторы DDL SQL
Операторы DDL предназначены для создания, удаления и изменения объектов БД или сервера СУБД. В этой книге будут в основном рассматриваться операторы уровня БД. DDL включает три оператора: CREATE, ALTER, DROP (рис. 3.2).
Рис. 3.2. Операторы DDL SQL
Оператор CREATE предназначен для создания объектов БД или сервера СУБД. Структура оператора представлена на рис. 3.3.
Рис. 3.3. Структура оператора CREATE
Каждый объект БД имеет тип (например, процедура, таблица, представление и т. д.), имя, а также ряд дополнительных параметров (на рис. 3.3 обозначены словом «дополнение»), характеризующих данный тип объекта. В некоторых случаях дополнительные параметры могут не указываться – в этом случае для объекта автоматически устанавливаются значения параметров объекта по умолчанию.
На рис. 3.4 представлен пример создания БД (тип database) с именем BSTU – объекта сервера СУБД. БД BSTU располагается в двух файлах операционной системы с именами, указанными параметрами filename, имеющими логические имена (параметр с именем name) соответственно BSTU и BSTU_log. Параметры size, maxsize и filegrowth указывают на начальный размер, максимальный размер, а также приращение размера файла.
Рис. 3.4. Пример использования оператора CREATE
для создания базы данных
На рис. 3.5 приведен пример создания таблицы (тип table) – объекта БД. Таблица имеет имя AUDITORIUM и состоит из пяти столбцов. Для столбца с именем AUDITORIUM указывается ограничение целостности первичный ключ (primary key) с именем AUDITORIUM_PK.
Рис. 3.5. Пример использования оператора CREATE
для создания таблицы
Создание программного объекта БД – скалярной функции с именем CALC_CAPACITY представлено на рис. 3.6. Функция возвращает единственное целочисленное значение – сумму значений в столбце AUDITORIUM_ CAPACITY всех строк таблицы AUDITORIUM.
Рис. 3.6. Пример использования оператора CREATE
для создания скалярной функции
Для модификации существующих объектов БД или сервера СУБД применяется оператор ALTER. Структура оператора представлена на рис. 3.7.
Рис. 3.7. Структура оператора ALTER
На рис. 3.8 приведен пример трехкратного применения оператора ALTER к базе данных (тип database) с именем BSTU. В первом случае оператор добавляет (ключевое слово add) новую файловую группу (filegroup) с именем G1. Во втором добавляет файл (add file) в файловую группу G1. Третий оператор изменяет (modify) свойство файловой группы G1, устанавливая признак того, что эта группа подразумевается в некоторых операторах SQL по умолчанию, если имя группы не указано явно.
Рис. 3.8. Пример использования нескольких операторов ALTER
для изменения базы данных
Пример на рис. 3.9 демонстрирует применение оператора ALTER к таблице (тип table) с именем AUDITORIUM. Оператор изменяет свойства столбца (alter column) c именем AUDITORIUM_ NAME.
Рис. 3.9. Пример использования оператора ALTER
для изменения свойства столбца таблицы
На рис. 3.10 демонстрируется применение оператора ALTER к функции (тип function) c именем CALC_ CAPACITY. Такое использование оператора позволяет изменить программный код функции, не меняя других ее свойств.
Рис. 3.10. Пример использования оператора ALTER
для изменения функции
Удалить (иногда применяют слова «уничтожить», «разрушить») существующий объект сервера или БД можно с помощью оператора DROP. Структура оператора представлена на рис. 3.11.
Рис. 3.11. Структура оператора DROP
Пример на рис. 3.12 демонстрирует двукратное применение оператора DROP.
Рис. 3.12. Пример использования операторов DROP
для удаления таблицы и функции
В первом случае удаляется таблица (тип table) с именем TRACHER. Во втором удаляется функция (function) с именем CALC_ CAPACITY.
Операторы DML SQL
Операторы DML предназначены для работы с одним (наиболее важным) типом объектов БД – таблицами. DML включает четыре оператора: SELECT, INSERT, DELETE, UPDATE (рис. 3.13). Иногда к этой группе относят оператор TRUNCATE.
Рис. 3.13. Операторы DML SQL
Наиболее мощным DML-оператором является SELECT. Он позволяет выбрать множество строк из одной или нескольких таблиц. При успешном выполнении этого оператора формируется результирующий набор, представляющий собой множество однотипных (с одинаковыми столбцами) строк. В общем случае результирующий набор может содержать ни одной, одну или более строк. Структура оператора SELECT достаточно сложная и детально будет разобрана позже, но в первом приближении ее можно представить как на рис. 3.14.
Рис. 3.14. Структура оператора SELECT
Любой оператор SELECT содержит список (на рис. 3.14 обозначен словом «список»), определяющий перечень столбцов (в общем случае и содержимое) результирующего набора. Дополнительная часть оператора (на рис. 3.14 обозначена словом «дополнение») описывает множество строк из одной или нескольких таблиц, служащее источником для формирования результирующего набора.
На рис. 3.15 приведены примеры использования оператора SELECT.
Рис. 3.15. Примеры использования операторов SELECT
В первом случае в результате выполнения оператора SELECT будет сформирован результирующий набор, состоящий из всех строк таблицы dbo. TEACHER, причем строки будут содержать все столбцы этой таблицы.
Второй оператор SELECT сформирует результирующий набор, состоящий из одного столбца с именем PULPIT_ NAME таблицы PULPIT.
Третий оператор SELECT демонстрирует создание новой таблицы с именем NEWTABLE, включающей два столбца с именами PN и FY. Cодержимое новой таблицы будет определяться результирующим набором, сформированным в результате выполнения другого (внутреннего) оператора SELECT, записанного в скобках после ключевого слова FROM.
Четвертый и пятый операторы формируют результирующие наборы, состоящие из одной строки, содержимое которой определяется значением выражений, указанных после ключевого слова SELECT.
Добавить одну или несколько строк в существующую таблицу можно с помощью оператора INSERT. Структура этого оператора представлена на рис. 3.16.
Рис. 3.16. Структура оператора INSERT
Ключевое слова INTO указывает на то, что далее следует имя таблицы, в которую будут добавляться строки.
На рис. 3.17 приведены четыре примера использования оператора INSERT.
Рис. 3.17. Примеры использования операторов INSERT
В первом примере оператор INSERT добавляет одну строку в таблицу с именем AUDITORIUM_ TYPE. Добавляемая строка содержит столбцы с именами AUDITORIUM_ TYPE и AUDITORIUM_ TYPENAME, а соответствующие им значения указаны в скобках после ключевого слова VALUES.
Во втором примере добавляется три строки в таблицу GROUPS.
Третий пример на рис. 3.17 демонстрирует добавление в таблицу GROUPS строки из результирующего набора, сформированного оператором SELECT.
Последний пример на рис. 3.18 добавляет одну строку. При этом предполагается, что порядок в списке значений, указанных в скобках за ключевым словом VALUES, соответствует исходному (указанному в операторе CREATE) порядку столбцов таблицы AUDITORIUM_ TYPE.
Для удаления строк из таблицы предназначен оператор DELETE. Структура этого оператора приведена на рис. 3.18.
Рис. 3.18. Структура оператора DELETE
В результате выполнения оператора DELETE из таблицы удаляется ноль, одна или несколько строк.
На рис. 3.19 приведены три примера применения оператора DELETE.
В первом случае удаляются все строки из таблицы с именем TTTT.
Во втором примере из таблицы SUBJECT (ключевое слово FROM может быть опущено) удаляются строки, которые в столбце PULPIT имеют значение, указанное в кавычках после символа =.
Рис. 3.19. Примеры использования операторов DELETE
Третий пример демонстрирует удаление из таблицы TEACHER строк, в столбце TEACHER_ NAME которых содержится подстрока, обрамленная символами % в выражении, следующем за ключевым словом LIKE.
Для изменения строк таблицы предназначен оператор UPDATE. Структура этого оператора приведена на рис. 3.20.
Рис. 3.20. Структура оператора UPDATE
В результате выполнения оператора UPDATEв таблице изменяется ноль, одна или несколько строк.
На рис. 3.21 приведены два примера применения оператора UPDATE.
Рис. 3.21. Примеры использования операторов UPDATE
В первом случае изменяются строки таблицы AUDITORIUM. Во всех строках таблицы, значение столбца AUDITORIUM_ TYPE которых равно строке ЛБ-К, числовое значение в столбце AUDITORIUM_ CAPACITY умножается на 1.15.
Во втором примере во всех строках таблицы TEACHER значение в столбце PULPIT изменяется на строку ИСиТ.
Операторы TCL SQL
Операторы TCL предназначены для создания транзакций. Что такое транзакция подробно будет разобрано позже, но в первом приближении отметим, что транзакция – это несколько DML-операторов, которые либо все успешно выполнятся, либо все не выполняются. TCL SQL включает четыре оператора: BEGIN TRAN, SAVE TRAN, COMMIT TRAN и ROLLBACK TRAN (рис. 3.22).
Рис. 3.22. Операторы TCL SQL
Оператор BEGIN TRAN указывает на начало транзакции. Структура оператора представлена на рис. 3.23.
Рис. 3.23. Структура оператора BEGIN TRAN
Предполагается, что за оператором BEGIN TRAN будут следовать один или несколько DML-операторов, входящих в транзакцию.
Снизу группу DML-операторов, входящую в транзакцию, ограничивает один из операторов: COMMIT TRAN или ROLLBACK TRAN.
Оператор COMMIT TRAN фиксирует транзакцию – подтверждает ее успешное завершение. Структура оператора COMMIT TRAN представлена на рис. 3.24.
Рис. 3.24. Структура оператора COMMIT
Оператор ROLLBACK TRAN откатывает транзакцию – отменяет все изменения, произведенные DML-операторами в таблицах БД в рамках данной транзакции. Структура оператора ROLLBACK TRAN представлена на рис. 3.25.
Рис. 3.25. Структура оператора ROLLBACK
Оператор SAVE TRAN применяется для формирования контрольной точки – промежуточного состояния транзакции, к которому может быть осуществлен откат. В тексте SQL-скрипта оператор SAVE TRAN должен находиться между операторами BEGIN TRAN и ROLLBACK TRAN. Структура оператора SAVE TRAN представлена на рис. 3.26.
Рис. 3.26. Структура оператора SAVETRAN
На рис. 3.27 представлен пример, демонстрирующий применение TCL-операторов.
Рис. 3.27. Пример использования операторов BEGIN, COMMIT и ROLLBACK
Транзакция с именем T1 начинается оператором BEGIN TRAN, включает в себя пять операторов INSERT и два оператора UPDATE. В случае успешного выполнения всех DML-операторов, входящих в транзакцию, осуществляется фиксация (оператор COMMIT), иначе управление передается в блок BEGIN CATCH в котором выполняется откат.
Операторы DCL SQL
Операторы DCL предназначены для управления процессом авторизации. Авторизация – это процедура проверки разрешений (permission) на выполнения определенных операций. Авторизация может выполняться на двух уровнях: на уровне сервера или на уровне БД. Каждому уровню соответствует свой перечень операций, которыми можно управлять с помощью DCL.
Разрешениями могут обладать специальные объекты сервера или БД, которые объединены под общим названием – принципалы. Или иначе, принципал – это объект сервера или БД, которому может быть выдано разрешение на выполнение операции, а также отобрано или запрещено разрешение.
Все операции на уровне сервера или БД выполняются от имени принципалов. Например, при подключении к серверу любой пользователь сервера обязан пройти процедуру аутентификации (проверку подлинности), которая в простейшем случае сводится к вводу имени и пароля. После этого все действия пользователя на уровне сервера выполняются от имени объекта сервера, имеющего тип Login (учетная запись), а на уровне БД от имени объекта, имеющего тип User (пользователь БД). Объекты типов Login и User – принципалы, которым могут быть выданы разрешения.
Управление разрешениями осуществляется тоже от имени принципала, обладающего соответствующими разрешениями. Таким образом, принципалы могут быть связаны между собой отношением «выдал разрешение – получил разрешение». В начале цепочки этих связей находятся предопределенные принципалы, обладающие исключительными разрешениями и соответствующие учетным записям администраторов сервера.
DCL включает в себя три оператора GRANT, REVOKE и DENY (рис. 3.26).
Рис. 3.28. Операторы DCL SQL
Оператор GRANT предназначен для выдачи разрешений. Структура этого оператора представлена на рис. 3.29.
Рис. 3.29. Структура оператора GRANT
После ключевого слова GRANT следует список разрешений, который будет назначен принципалу, имя которого указывается после ключевого слова TO.
Отобрать разрешение у принципала можно с помощью оператора REVOKE, структура которого представлена на рис. 3.30.
Рис. 3.30. Структура оператора REVOKE
После ключевого слова REVOKE следует список разрешений, который будет отобран у принципала, имя которого указывается после ключевого слова FROM.
Оператор DENY не входит в состав стандарта SQL и может применяться только в рамках Microsoft SQL Server. Он позволяет запретить разрешения, выданные ранее. Структура этого оператора представлена на рис. 3.31.
Рис. 3.31. Структура оператора DENY
После ключевого слова DENY следует список разрешений, который будет запрещен у принципала, имя которого указывается после ключевого слова TO.
На рис. 3.32 представлены примеры использования DCL-операторов. Инструкция USE, применяемая в начале скрипта, переключает текущий контекст на системную БД с именем MASTER. Далее с помощью оператора CREATE LOGIN создается объект типа Login (учетная запись сервера, принципал) с именем BSTULogin.
Следующий далее оператор USE переключает текущий контекст на пользовательскую БД с именем BSTU, а потом с помощью оператора CREATE USER создается объект типа USER (пользователь БД, принципал) с именем BSTUUser, который с помощью ключевых слов FOR LOGIN ссылается на объект типа Login c именем BSTULogin.
Рис. 3.32. Пример использования операторов GRANT и DENY
Оператор GRANT на рис. 3.32 разрешает выполнять пользователю BSTUUser все DML-операторы применительно ко всем объектам схемы с именем DBO.
Следующий за оператором GRANT оператор DENY запрещает выполнять DML-операторы применительно к таблице DBO. FACULTY, находящейся в схеме DBO.
Таким образом, в результате пользователь BSTUUser имеет право применять DML-операторы ко всем объектам схемы DBO за исключением таблицы с именем DBO. FACULTY.
Следует обратить внимание на конструкцию WITH GRANT OPTION, применяемую в операторе GRANT, указывающую на то, что пользователь BSTUUser вместе с разрешением на выполнение DML-операций приобретает административные права на полученные разрешения. Другими словами, пользователь BSTUUser с этого момента имеет право выдавать разрешения (с помощью оператора GRANT) на выполнение DML-операторов применительно к объектам схемы DBO (за исключением таблицы BSTUUser) другим пользователям, а также отбирать и запрещать эти разрешения (REVOKE и DENY).
На рис. 3.33 представлен пример использования оператора REVOKE.
Рис. 3.33. Пример использования оператора REVOKE
Оператор REVOKE применяется к пользователю BSTUUser БД BSTU. Оператор отбирает право у пользователя на выполнение DML-операторов применительно к объектам схемы DBO.
Ключевое слово CASCADE используется в тех случаях, когда вместе с разрешениями пользователь обладает административным правом на отбираемые разрешения. После выполнения оператора REVOKE CASCADE пользователь утрачивает не только разрешения, но и административные права на них. Более того, разрешения и административные права отбираются у всех цепочек принципалов, которые унаследовали от пользователя BSTUUser эти разрешения.
Операторы DCL напрямую связаны с архитектурой информационной безопасности СУБД. Более подробное изучение операторов DCL выходит за рамки данного пособия. Для самостоятельного изучения архитектуры безопасности Microsoft SQL Server 2008, а также возможностей, предоставляемых DCL-операторами, рекомендуется ознакомиться с изданиями [4, 5].
Дата: 2019-02-25, просмотров: 226.
