Инструкции DML

Основные правила написания SQL-операторов

Общие сведения о SQL

SQL (Structured Query Language - язык структурированных запросов) является языком четвертого уровня, предназначенным для работы с реляционными базами данных.

В начале 1970-х годов в одной из исследовательских лабораторий компании IBM была разработана экспериментальная реляционная СУБД System R (англ.), для которой затем был создан специальный язык SEQUEL, позволявший относительно просто управлять данными в этой СУБД. Аббревиатура SEQUEL расшифровывалась как Structured English QUEry Language - структурированный английский язык запросов. Позже по юридическим соображениям язык SEQUEL был переименован в SQL и официальным произношением стало [,es kju:' el] - эс-кью-эл.

Первыми СУБД, поддерживающими новый язык, стали в 1979 году OracleV2 для машин VAX от компании Relational Software Inc. и System/38 от IBM, основанная на System/R.

Первый официальный стандарт языка SQL был принят ANSI в 1986 году и ISO в 1987 году (так называемый SQL-86) и несколько уточнён в 1989 году. Дальнейшее развитие языка поставщиками СУБД потребовало принятия в 1992 году нового расширенного стандарта (ANSI SQL-92 или просто SQL2). Следующим стандартом стал SQL:1999 (SQL3). В настоящее время действует стандарт, принятый в 2003 году (SQL:2003) с небольшими модификациями, внесёнными позже.

История версий стандарта представлена в таблице:

Таблица

Год	Название	Иное название	Изменения
	SQL-86	SQL-87	Первый вариант стандарта, принятый институтом ANSI и одобренный ISO в 1987-ом году
	SQL-89	FIPS 127-1	Немного доработанный вариант предыдущего стандарта.
	SQL-92	SQL2, FIPS 127-2	Значительные изменения (ISO 9075); уровень Entry Level стандарта SQL-92 был принят как стандарт FIPS 127-2
	SQL:1999	SQL3	Добавлена поддержка регулярных выражений, рекурсивных запросов, поддержка триггеров, базовые процедурные расширения, нескалярные типы данных и некоторые объектно-ориентированные возможности
	SQL:2003		Введены расширения для работы с XML-данными, оконные функции (применяемые для работы с OLAP-базами данных, генераторы последовательностей и основанные на них типы данных
	SQL:2006		Функциональность работы с XML-данными значительно расширена. Появилась возможность совместно использовать в запросах SQL и XQuery
	SQL:2008		Улучшены возможности оконных функций, устранены некоторые неоднозначности стандарта SQL:2003

Основные преимущества использования SQL:

1. Независимость от конкретной СУБД. Несмотря на наличие диалектов, и различий в синтаксисе, в большинстве своём тексты SQL-запросов, содержащие DDL и DML операторы, могут быть достаточно легко перенесены из одной СУБД в другую. Естественно, что при применении некоторых специфичных для реализации возможностей такой переносимости добиться очень трудно.

2. Наличие стандартов. Наличие стандартов и набора тестов для выявления совместимости и соответствия конкретной реализации SQL общепринятому стандарту способствует «стабилизации» языка. Однако стандарт местами чересчур формализован и раздут в размерах, например, Core-часть стандарта SQL:2003 представляет собой более 1300 страниц текста.

3. Декларативность. С помощью SQL программист описывает, какие данные нужно извлечь или модифицировать. То, каким образом это сделать, решает СУБД непосредственно при обработке SQL-запроса, но при этом полезно представлять, как СУБД будет разбирать текст его запроса. Особенно критичными такие моменты становятся при работе с большими базами данных и со сложными запросами, - чем сложнее сконструирован запрос, тем больше он допускает вариантов написания, различных по скорости выполнения, но одинаковых по итоговому набору данных.

Кроме этого, можно отметить и ряд недостатков:

1. Несоответствие реляционной модели данных. Создатель реляционной модели данных Эдгар Кодд, Кристофер Дейт и их сторонники указывают на то, что SQL не является истинно реляционным языком. В частности они указывают на следующие проблемы SQL:

- повторяющиеся строки;

- неопределённые значения (NULL);

- явное указание порядка колонок слева направо;

- колонки без имени и дублирующиеся имена колонок;

- использование указателей и т.д.

2. Сложность. Хотя SQL и задумывался, как средство работы конечного пользователя, в конце концов, он стал настолько сложным, что превратился в инструмент программиста.

3. Отступления от стандартов. Несмотря на наличие международного стандарта ANSI SQL-92, многие компании, занимающиеся разработкой СУБД (например, Oracle, Sybase, Microsoft, MySQL), вносят изменения в язык SQL, применяемый в разрабатываемой СУБД, тем самым отступая от стандарта. Таким образом, появляются специфичные для каждой конкретной СУБД диалекты языка SQL. Существует четыре уровня соответствия реализации SQL стандарту:

- entry (базовый);

- transitional (переходный);

- intermediate (промежуточный);

- full (полный).

4. Сложность работы с иерархическими структурами. Ранее SQL не предлагал стандартного способа манипуляции древовидными структурами. Некоторые поставщики СУБД предлагали свои решения. Например, Oracle использует выражение CONNECT BY. В настоящее время в качестве стандарта принята рекурсивная конструкция WITH.

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

Таблица

СУБД	Краткое название	Расшифровка
Borland InterBase, Firebird	PSQL	Procedural SQL
IBM DB2	SQL PL	SQL Procedural Language (расширяет SQL/PSM)
Microsoft SQL Server, Sybase ASE	Transact-SQL	Transact-SQL
MySQL	SQL/PSM	SQL/Persistent Stored Module (стандарт SQL:2003)[
Oracle	PL/SQL	Procedural Language/SQL (основан на языке Ada)
PostgreSQL	PL/pgSQL	Procedural Language/PostgreSQL (очень похож на Oracle PL/SQL)

В SQL-операторах необходимо придерживаться точных синтаксических и структурных правил, правильно использовать ключевые слова, а также идентификаторы, операторы и константы. Символы, используемые в различных частях SQL-оператора, могут зависеть от реализации СУБД и определяются в основном множеством символов, которое SQL-сервер использует по умолчанию.

SQL-операторы часто называют инструкциями. Инструкция – это набор ключевых слов, связанных друг с другом и используемых как единое целое в командах SQL. Напомним, что в составе SQL условно выделяют следующие группы инструкций:

- DDL (Data Definition Language) – инструкции языка описания данных;

- DML (Data Manipulation Language) – инструкции языка манипулирования данными.

К общим и наиболее распространенным инструкциям SQL относятся:

- инструкция_авторасширения (AUTOEXTEND), которая указывает, разрешено ли для файла увеличение его размера;

- инструкция_ограничения_столбца (таблицы) (CONSTRAINT), которая задаёт ограничение целостности столбца (таблицы);

- инструкция_состояния_ограничения (DEFERRABLE), которая позволяет избирательно применять и не применять ограничение целостности;

- инструкция_атрибутов_индекса, которая включает инструкцию_физиче­ских_атрибутов и инструкцию_хранения и задает физические и логические характеристики индекса;

- инструкция_параметров_LOB, которая опеределяет параметры хранения сегментов данных больших размеров;

- инструкция_хранения_LOB (LOB), которая определяет, каким образом сегменты данных LOB будут храниться в таблице, разделе или подразделе;

- инструкция_раздела (partition by range), которая задает параметры секционирования;

- инструкция_физических_атрибутов, которая определяет характеристики объектов схемы, влияющие на использование пространства в блоке;

- инструкция_хранения STORAGE (), которая определяет метод выделения места в табличном пространстве Oracle для отдельного объекта.

Каждая инструкция SQL начинается с ключевого слова, описывающего действие, выполняемое инструкцией (например, create, insert, delete, commit), после чего следует одно или несколько предложений. Предложение описывает данные, с которыми работает инструкция, или содержит уточняющую информацию о действии, выполняемом инструкцией. Каждое предложение также начинается с ключевого слова, например, такого как WHERE, FROM, into, having. Одни предложения в инструкции являются обязательными, другие - нет. Конкретная структура и содержимое предложения могут изменяться. Многие предложения содержат имена таблиц или столбцов; некоторые из них могут содержать дополнительные ключевые слова, константы и выражения.

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

При написании SQL-операторов следует учитывать следующее:

1. SQL-операторы не чувствительны к регистру за исключением символьных данных в полях таблиц;

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

- ключевые (зарезервированные) слова следует писать прописными буквами;

- слова, определяемые пользователем, следует писать строчными буквами;

- ключевые слова в пределах одного SQL оператора следует писать с новой строки и выравнивать по одной и той же позиции. При использовании вложенных операторов SELECT их следует сдвигать внутрь предложения на позицию второго слова в предыдущей строке;

- если фраза SQL-оператора превышает длину строки, то ее продолжение следует смещать относительно начала следующей строки;

3. в описании синтаксиса SQL-операторов используются следующие специальные символы:

- вертикальная черта (|), которая указывает на необходимость выбора одного из нескольких значений (например, a | b |c);

- фигурные скобки ({}), которые указывают, что заключенный в них элемент является обязательным;

- квадратные скобки ([ ]), которые указывают, что заключенный в них элемент является необязательным;

- многоточие (...), которое используется для указания необязательной возможности повторения указанной конструкции от нуля до нескольких раз (например, запись { a|b }, [ c …] означает, что за а или b может следовать от нуля до нескольких повторений c, разделенных запятыми.

- звездочка (*) для обозначения "все" - употребляется в обычном для программирования смысле, т.е. "все случаи, удовлетворяющие определению";

- точка с запятой (;) – завершающий элемент предложений SQL;

- запятая (,) – используется для разделения элементов списков;

- пробелы () – могут вводиться для повышения наглядности между любыми синтаксическими конструкциями предложений SQL.

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

Стандарт SQL задает набор символов, который используется по умолчанию, – он включает строчные и прописные буквы латинского алфавита (A-Z, a-z), цифры (0-9) и символ подчеркивания (_). На формат идентификатора накладываются следующие ограничения:

- идентификатор может иметь длину до 128 символов;

- идентификатор должен начинаться с буквы;

- идентификатор не может содержать пробелы.

На практике поддержка имен в различных СУБД реализована по-разному, поэтому для повышения переносимости лучше делать, имена сравнительно короткими и избегать употребления в них специальных символов.

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

Кроме этого, пользователь может создавать именованное множество таблиц, называемое схемой. При этом пользователь может создавать таблицы с одинаковыми именами в различных схемах. Обращаться к таблицам, которые имеют одинаковые имена можно с помощью полного имени таблицы, т.е. имени, включающего идентификатор владельца или схемы и непосредственно имя таблицы, указанные через точку. Например, полное имя таблицы branch, владельцем которой является пользователь по имени user, имеет следующий вид: user.branch. При обращении к таблицам другого пользователя необходимо иметь на это соответствующее разрешение.

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

Полные имена таблиц и столбцов можно использовать вместо коротких во всех инструкциях SQL.

Константы. В некоторых инструкциях SQL необходимо явно указывать значения чисел, строк или даты. В стандарте ANSI/ISO определен формат числовых и строковых констант, или литералов, которые представляют конкретные значения данных. Этот формат используется в большинстве СУБД.

Целые и десятичные константы (точные числовые литералы) в инструкциях SQL представляются в виде обычных десятичных чисел с необязательным знаком плюс или минус перед ними.

Константы с плавающей запятой (приблизительные числовые литералы) определяются с помощью символа Е и имеют такой же формат, как и в большинстве языков программирования.

В соответствии со стандартом ANSI/ISO строковые константы (строковые литералы) в SQL должны быть заключены в одинарные кавычки.

Константы типа даты в диалекте SQL для ORACLE имеют специфичный формат представления. Как и строковые литералы, даты при их явном представлении берутся в одинарные кавычки.

Кроме пользовательских констант, в SQL существуют специальные именованные константы, возвращающие значения, хранимые в самой СУБД. Так, например, в ORACLE существует константа SYSDATE, при указании которой в запросе, СУБД возвращает в результат или подставляет в поля базы данных текущую дату и время. Также стандартом регламентировано применение константы USER для фиксации имени пользователя сделавшего изменения в записях таблиц баз данных и т.д.

Учебная БД. Для пояснения работы некоторых инструкций в курсе лекций и лабораторных работах предлагается рассмотреть учебную базу данных малого предприятия по аренде недвижимости, которая содержит шесть таблиц: branch, staff, property _ for _ rent, renter, owner, viewing.

Таблица branch предназначена для хранения информации об отделениях (офисах) предприятия и имеет следующие атрибуты: bno, street, city, tel _no. Атрибут bno является первичным ключом и в соответствии с правилом целостности сущности не может принимать неопределенных значений.

Таблица staff предназначена для хранения информации о сотрудниках и содержит следующие атрибуты: sno, fname, lname, address, tel _ no, position, sex, dob, salary, bno. Sno – первичный ключ, идентификатор записей о сотрудниках; position – строковый атрибут, определяет занимаемую должность; dob – атрибут типа дата\время с данными о днях рождения сотрудников; salary – числовой атрибут с данными о зароботной плате сотрудников. Атрибут bno – внешний ключ для связи с таблицей branch.

Таблица property _ for _ rent содержит информацию об объектах недвижимости, предлагаемых в аренду и имеет следующие атрибуты: pno, street, city, type, rooms, rent, ono, sno, bno. Где pno – первичный ключ, type – строковый атрибут с информацией о типе предлагаемого объекта недвижимости; в данном случае на значения атрибута наложено ограничение, т.е. данный атрибут может принимать либо значение ’h’, либо ‘f’. Rooms и rent – числовые атрибуты. Ono, sno, bno – внешние ключи таблицы для связи с таблицами owner, staff, branch соответственно.

Таблица renter содержит информацию о потенциальных арендаторах и содержит следующие атрибуты: rno, fname, lname, address, tel _ no, pref _ type, max _ rent, bno. Rno – первичный ключ, pref _ type – строковый атрибут, определяющий предпочтительный для клиента тип объекта аренды и ограниченный значениями ‘h’ и ‘f’. Max _ rent – числовой атрибут, имеющий смысл максимальной рентной стоимости объекта с точки зрения арендатора, bno – внешний ключ для связи с таблицей branch.

Таблица owner определяет владельцев объектов недвижимости, которые сдаются в аренду и включает следующие поля: ono, fname, lname, address, tel _ no.

Таблица viewing содержит результаты осмотра арендаторами предполагаемых объектов аренды и имеет поля: rno, pno, date _ o, comment _ o. Таблица имеет составной первичный ключ, включающий атрибуты rno и pno, каждый из которых является также внешним ключом для связи с таблицами renter (указывает, кто из потенциальных арендаторов производил осмотр) и property _ for _ rent (какой из объектов осматривался). Атрибут date _ o определяет дату осмотра, а comment _ o предназначен для сохранения сделанных потенциальным арендатором комментариев.

К DML инструкциям в Oracle относятся команды вставки, обновления, удаления, чтения, изменения способа работы сервера при обращении к данным.

Перечислим основные DML инструкции в таблице:

Таблица

Инструкция	Назначение
ALTER SESSION	изменяет функциональные характеристики текущего сеанса связи с БД (+NLS) многие из параметров определены в файле инициализации INTT.DBA или SPFILE
ANALYZE	собирает или удаляет статистическую информацию об объекте БД, проверяет структуру объекта или идентифицирует переменные и сцепленные строки таблицы (кластера).
DELETE	удаляет строки из таблицы, представления или моментальной копии
EXPAIN PLAN	создаёт пояснение для плана использования (исполнения) команды SQL.
INSERT INTO	вставка строки данных в таблицу или представление
MERGE INTO	выбирает строки таблицы для обновления или вставки в другую таблицу(с Oracle9i Release 2) (позволяет избегать многократного использования INSERT, UPDATE)
SAVE POINT точка сохранения	определяет точку сохранения транзакции, до которой можно было выполнить откат при помощи команды ROLLBACK
SELECT	извлекаем данные из таблиц, представлений и момент копий
SET CONSTRAINT	задаёт на уровне транзакций, будут ли ограничения проверяться после каждой DML команды или только в конце транзакций
SET ROLE	включить или отключить роль для текущего сеанса
SET TRANSACTION	установить базовые характеристики транзакции
TRUNCATE	удаляет все строки из таблицы или кластера (не создаёт записей отката, выполняется быстро, аналог DELETE FROM)
UPDATE	изменяет значение, хранящегося в одном или нескольких столбцах данных в одной или нескольких таблицах, представлениях или моментальных копиях

В стандарте SQL описаны наиболее распространенные инструкции SELECT, INSERT INTO, UPDATE и DELETE, которые подробно рассмотрены далее.

Дата добавления: 2013-12-12