41119

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

Лекция

Информатика, кибернетика и программирование

Сегодня Oracle поставляет на мировой рынок огромное количество продуктов, услуг и решений, ее штат насчитывает более 43 тыс. человек, и доход этой компании, превысивший в 2000 году 10 млрд. долл., определяется не только продажами СУБД.

Русский

2013-10-22

3.27 MB

19 чел.

СОДЕРЖАНИЕ

РАЗДЕЛ 1 SQL

[1] 2. Общие сведения о SQL

[2] 3. Основные правила написания SQL-операторов

[3] 4. Инструкции DML

[4] 5. ИНСТРУКЦИЯ SELECT

[5] 6. Предложения SELECT и FROM

[6] 7. Отбор строк (предложение WHERE)

[7] 8. Многотабличные запросы

[8] 9. Внутренние и внешние соединения

[9] 10. ПРЕДЛОЖЕНИЯ GROUP BY и HAVING

[10] 11. СТАТИСТИЧЕСКИЕ ФУНКЦИИ

[11] 12. ПОДЧИНЕННЫЕ ЗАПРОСЫ (ПОДЗАПРОСЫ)

[12] 13. ДОБАВЛЕНИЕ ДАННЫХ (инструкция INSERT)

[13] 14. УДАЛЕНИЕ ДАННЫХ (инструкция delete)

[14] 15. ОБНОВЛЕНИЕ ДАННЫХ (инструкция UPDATE)

[15] 16. Инструкции DDL

[16] 17. стандартные Типы данных SQL

[17] 18. Создание таблиц (инструкция CREATE TABLE)

[18] 19. Удаление таблицы (инструкция DROP TABLE)

[19] 20. Изменение определения таблицы (инструкция ALTER TABLE)

[20] 21. УСЛОВИЯ ЦЕЛОСТНОСТИ ДАННЫХ

[21] 22. СОЗДАНИЕ ПРЕДСТАВЛЕНИЙ

[22] 23. ПСЕВДОНИМЫ ТАБЛИЦ (ИНСТРУКЦИИ CREATE / DROP SYNONYM)

[23] 24. ИНДЕКСЫ (ИНСТРУКЦИИ CREATE/DROP INDEX)

[24] 25. Последовательности (инструкция CREATE/DROP SEQUENCE)

[25] 26. ТРАНЗАКЦИИ

[26] 27. Журнал транзакций. проблемы обработки параллельных транзакций

[27] 28. Блокировки транзакций

[28] 29. Понятие транзакции и виды блокировок Oracle

[29] 30. КОНЦЕПЦИЯ многоверсионной модели согласованности по чтению

[30] 31. Управление транзакциями в Oracle

[31] 32. ПРИНЦИПЫ ЗАЩИТЫ ДАННЫХ в стандартном SQL

[32] 33. Предоставление и отмена привилегий

[33] 34. Способы обеспечения безопасности в Oracle

 

Красным выделены пункты, которые не нужно изучать заочникам ИИТ БГУИР при подготовке к экзамену


American National Standard Institute (ANSI) — Американский национальный институт стандартов.

International Organization of Standards (ISO) — Международная организация по стандартизации.

Раздел 1

sql


1 ОСНОВНЫЕ СВЕДЕНИЯ О СУБД Oracle

История развития. Корпорация Oracle была основана в 1977 году, как компания, специализирующаяся на создании реляционных СУБД. Сначала компания носила название Software Development Laboratories, а в 1978 году была переименована в Relational Software, Inc.

Сегодня Oracle поставляет на мировой рынок огромное количество продуктов, услуг и решений, ее штат насчитывает более 43 тыс. человек, и доход этой компании, превысивший в 2000 году 10 млрд. долл., определяется не только продажами СУБД. На сегодняшний день это вторая по величине доходов компания, специализирующаяся на производстве программного обеспечения. Коротко проследим историю развития:

1979 г. выпущена первая официальная версия СУБД Oracle - V2.

1980 г. - выпущена версия Oracle 2 для операционной системы VAX/VMS.

1982 г. - выпущена версия Oracle 3, которая стала первой СУБД, поддерживающей обработку транзакций и способной выполняться на разных платформах, в том числе на мэйнфреймах и мини-ЭВМ.

1983 г. - Relational Software, Inc. переименована в Oracle Corporation.

1984 г. - выпущена и перенесена на несколько различных платформ версия Oracle 4.

1986 г. - выпущена версия Oracle 5, поддерживающая возможности создания приложений в архитектуре «клиент-сервер», распределенную обработку данных, кластеры VAX.

1988 г. - выпущена версия Oracle 6, а чуть позже объявлено, что Oracle 6.2 будет поддерживать симметричные кластеры с применением Oracle ParALLel Server.

1989 г. - Oracle реализовала поддержку OLTP, продукты фирмы стали доступны в 86 странах мира.

1992 г. - выпущена версия Oracle 7 для Unix. Эта СУБД поддерживала распределенные запросы, удаленное администрирование, осуществляла поддержку различных сетевых протоколов.

1994 г. - выпущена версия Oracle 7 для IBM PC (до этого времени компания Oracle не рассматривала данную платформу как серверную, ограничиваясь лишь созданием для нее клиентских частей своих СУБД).

1997 г. - выпущена версия Oracle 8, основными особенностями которой стали более высокая надежность по сравнению с предыдущей версией, а также поддержка большего числа пользователей и больших объемов данных. Выпущены версии Oracle Designer/2000 2.1 и Oracle Developer/2000, которые существенно облегчили создание приложений, работающих с базами данных, а также Oracle Applications for theWeb.

1999 г. - выпущена версия Oracle8i (Oracle 8.1), которая во многом была основана на применении Java: виртуальная Java-машина находится в самой СУБД, на этом же языке написаны клиентские утилиты, инсталлятор, средства администрирования. В конце того же года выпущена вторая версия Oracle 8i (Oracle 8.1.6), поддерживающая XML и содержащая некоторые новшества, связанные с созданием хранилищ данных.

2000 г. - выпущена третья версия Oracle8i (Oracle 8.1.7), содержащая Java Virtual Machine Accelerator, а также Internet File System.

Июнь 2001 г. - выпущена Oracle9i, которая отличается новыми средствами для обеспечения масштабируемости (Oracle9i Real Application Clusters), упрощения администрирования (в частности, для автоматизации конфигурирования серверов, управления ресурсами используемых компьютеров, мониторинга загрузки и производительности), обеспечения безопасности данных, восстановления данных после сбоев (в том числе из-за ошибок персонала), новыми средствами интеграции приложений. Кроме того, в составе СУБД имеется OLAP-сервер (ранее серверные OLAP-средства Oracle, такие как Oracle Express, поставлялись в виде отдельных продуктов).

2004 г. - выпущена версия Oracle10g Release 1 (10.1.0); «g» обозначает «grid» («сеть»), символизируя поддержку распределенных вычислений (grid-вычислений).

2005 г. - выпущена версия Oracle10g Release 2 (10.2.0.1).

2007 г. - выпущена версия Oracle11g Release 1 (11.1.0.6).

2009 г. -  выпущена версия Oracle 1g Release 2 (11.2.0.1).

Основные характеристики продуктов Oracle. К основным характеристикам относятся следующие:

1. Поддержка различных платформ. Подход Oracle к поддержке различных операционных систем радикально отличается от подхода Microsoft - СУБД этой фирмы существуют для огромного количества платформ. Хотя поддержка большого количества платформ (а не нескольких наиболее прибыльных, как это характерно для других производителей СУБД) требует немалых вложений, переход к широкому применению Java, который сейчас происходит в Oracle, позволяет существенно сократить затраты на разработку и поддержку продуктов.  

2. Настольные и однопользовательские версии. Oracle многое делает для поддержки настольных версий своих СУБД или применения их в указанном режиме.

Среди продуктов Oracle также имеются две СУБД, позиционируемые в качестве настольных. Продукт Personal Oracle, известный уже много лет, представляет собой однопользовательскую, но полнофункциональную версию Oracle, требующую некоторых навыков администрирования при ее применении. Вторая настольная СУБД этой компании, Oracle Lite, может применяться в качестве однопользовательской СУБД на обычных рабочих станциях, но в действительности она предназначена для применения в мобильных устройствах. В отличие от других СУБД этот продукт поддерживает только язык Java, но не поддерживает PL/SQL (язык, на котором создается серверный код в других СУБД Oracle).

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

Цена на продукты Oracle обычно намного превышает цены на аналогичные продукты Microsoft.

4. Инструменты и утилиты. Административные утилиты и другие инструменты для ранних версий Oracle обычно производились независимыми компаниями (выпускались как дорогие продукты, так и бесплатные), а основные усилия самой корпорации Oracle были сосредоточены на разработке самой СУБД. Затем ситуация существенно изменилась, и в настоящее время Oracle поставляет на рынок немалое количество разнообразных инструментов, среди которых помимо средств администрирования имеются средства разработки приложений, проектирования данных, моделирования бизнес-процессов, серверы приложений, корпоративный портал.

5. Производительность. Тесты не могут дать однозначного ответа на вопрос, какая из СУБД будет производительнее при решении конкретной задачи: в одних случаях производительность СУБД Oracle превосходит производительность SQL Server, а в других наоборот. Реально проверить это можно только с помощью тестирования, основанного на запросах, типичных именно для конкретной задачи.

Также к основным характеристикам можно отнести и новые возможности, такие как:

6. Поддержка Internet. В состав серверной части этой СУБД входит виртуальная Java-машина, в дополнение к имевшимся ранее средствам выполнения кода на языках SQL и PL/SQL, создан Oracle Portal - инструмент для создания корпоративных решений и интеграции корпоративных приложений на базе Web-технологий; созданы средства поддержки мультимедиа-контента в базе данных Oracle - Oracle8i interMedia, которые можно с успехом применять как в обычных приложениях, так и в Internet-приложениях.

В Oracle8i впервые появилась Internet File System (iFS) - Java-приложение, выполняющееся внутри виртуальной Java-машины Oracle8i и позволяющее пользователям обращаться с базой данных как с сетевым диском. Вследствие этого в Oracle8i можно теперь хранить данные практически любых типов. В последствии iFS был переименован в Oracle Content Management SDK.

В Oracle8i Release 2 (8.1.6) появилась поддержка XML как средства интеграции данных и реализации транзакций в приложениях business-to-business (B2B).

В Oracle9i присутствуют средства интеграции SQL, XML и Java, а также средства интеграции приложений Oracle9i Dynamic Services, представляющие собой инструменты и утилиты для создания сервисов на базе уже существующих приложений.

7. Администрирование. Oracle Enterprise Manager в последних версиях Oracle8i содержит разделяемый репозитарий, который позволяет координировать доступ пользователей к данным, хранящимся в различных серверах Oracle, с помощью Oracle Management Server - компоненты, предназначенной для централизации управления доступом клиентов к серверам.

Oracle8i поддерживает индексы, основанные на функциях и выражениях. Кроме того, в этой СУБД можно строить индексы в порядке не возрастания, а убывания, и для создания индексов в этой версии Oracle не требуется полная блокировка таблицы.

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

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

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

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

8. Безопасность данных. В Oracle8i впервые была применена концепция Virtual Private Database для упрощения управления доступом и средства защиты данных встроены в саму базу данных, а не в приложения.

В средства Oracle Advanced Security добавлены поддержка протоколов Secure Socket Layer (SSL) и Remote Authentication Dial-In User Service (RADIUS). Это сделано с целью поддержки безопасности транзакций, проводимых через Internet.

9. Прочее. Последние версии Oracle поддерживают управление базами данных большого объема. Для этой цели в сервере имеется поддержка так называемых материализованных представлений (materialized views).

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

Для обеспечения надежности и доступности в последних версиях Oracle имеются средства восстановления после сбоев (а в версии Oracle9i — также и после сбоев, вызванных ошибками персонала или пользователей).

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

Таблица

Название

Ограничения

Операционные
платформы

Enterprise Edition

Standard Edition

не может устанавливаться на системы, имеющие более 4-х процессорных разъёмов

Standard Edition One

не может устанавливаться на системы, имеющие более 2-х процессорных разъёмов

Personal Edition

один пользователь

Lite

для мобильных и встраиваемых устройств

Express Edition

бесплатная редакция; используемая оперативная память - 1 Гбайт. Максимальный объем базы данных Oracle Database XE составляет 5 гигабайт (Гб). Из них от 0.5 до 0.9 Гб используются словарем данных, внутренними схемами и временным дисковым пространством. Поэтому остается 4.0 Гб для пользовательских данных.

Windows 32-bit
Linux x86

2. Общие сведения о 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) с небольшими модификациями, внесёнными позже.

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

Таблица

Год

Название

Иное

название

Изменения

1986

SQL-86

SQL-87

Первый вариант стандарта, принятый институтом ANSI и одобренный ISO в 1987-ом году

1989

SQL-89

FIPS 127-1

Немного доработанный вариант предыдущего стандарта.

1992

SQL-92

SQL2,

FIPS 127-2

Значительные изменения (ISO 9075); уровень Entry Level стандарта SQL-92 был принят как стандарт FIPS 127-2

1999

SQL:1999

SQL3

Добавлена поддержка регулярных выражений, рекурсивных запросов, поддержка триггеров, базовые процедурные расширения, нескалярные типы данных и некоторые объектно-ориентированные возможности

2003

SQL:2003

 

Введены расширения для работы с XML-данными, оконные функции (применяемые для работы с OLAP-базами данных, генераторы последовательностей и основанные на них типы данных

2006

SQL:2006

 

Функциональность работы с XML-данными значительно расширена. Появилась возможность совместно использовать в запросах SQL и XQuery

2008

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)

3. Основные правила написания 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-операторы не чувствительны к регистру за исключением символьных данных в полях таблиц;
    1.  запись SQL-оператора имеет свободный формат, однако существует ряд общепринятых рекомендаций, которые облегчают читабельность SQL программ:

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

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

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

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

  1.  в описании синтаксиса 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 предназначен для сохранения сделанных потенциальным арендатором комментариев.

4. Инструкции DML

К 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, которые подробно рассмотрены далее.

5. ИНСТРУКЦИЯ SELECT

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

Предложение SELECT может использоваться как:

самостоятельная команда на получение и вывод строк таблицы, сформированной из столбцов и строк одной или нескольких таблиц (представлений);

  •  элемент WHERE- или HAVING-условия ("вложенный запрос");
  •  фраза выбора в командах CREATE VIEW, DECLARE CURSOR или INSERT;
  •  средство присвоения глобальным переменным значений из строк сформированной таблицы (фраза INTO).

Инструкция SELECT – наиболее мощная из всех SQL-инструкций и, по сути, аналогична таким операторам реляционной алгебры как SELECTion, projection, join.

Инструкция SELECT состоит из шести предложений.

Предложения SELECT и FROM являются обязательными. Четыре остальных включаются в инструкцию только при необходимости. Ниже перечислены функции каждого из предложений.

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

В предложении FROM указывается список таблиц, которые содержат элементы данных, извлекаемые запросом.

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

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

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

Предложение order by сортирует результаты запроса на основании данных, содержащихся в одном или нескольких столбцах. Если это предложение не указано, результаты запроса не будут отсортированы.

Полный формат инструкции SELECT, применяемой в Oracle имеет вид:

SELECT [{DISTINCT | UNIQUE | ALL}]

{[схема.]{[имя_таблицы. представление. моментальная_копия.]. имя_столбца}

выражение [[AS] псевдоним] [, выражение [[AS] псевдоним]…]

| *}

FROM

{имя_таблицы [@связь_БД] [AS OF {SCN | TIMESTAMP} выражение]

| имя_таблицы PARTITION (имя_раздела) [AS OF {SCN | TIMESTAMP} выражение]

| имя_таблицы SUBPARTITION (имя_подраздела) [AS OF{SCN | TIMESTAMP} выражение]

| имя_таблицы SAMPLE[BLOCK] процент_выборки [AS OF{SCN | TIMESTAMP} выражение]

представление[@связь_БД] [AS OF {SCN | TIMESTAMP} выражение]

представление PARTITION (имя_ раздела) [AS OF {SCN | TIMESTAMP} выражение]

представление SUBPARTITION (имя_подраздела) [AS OF {SCN | TIMESTAMP} выражение]

представление SAMPLE [BLOCK] процент_выборки [AS OF {SCN | TIMESTAMP} выражение]

моментальная_копия[@связь_БД] [AS OF{SCN | TIMESTAMP} выражение]

моментальная_копия PARTITION (имя_ раздела) [AS OF {SCN | TIMESTAMP} выражение]

моментальная_копия SUBPARTITION (имя_подраздела) [AS OF {SCN | TIMESTAMP} выражение]

моментальная_копия SAMPLE [BLOCK] процент_выборки [AS OF {SCN | TIMESTAMP} выражение]

| (подзапрос)

имя_таблицы 

{[тип_соединения] JOIN имя_таблицы 

{ ON условие 

|USING (столбец [столбец]…)

|{CROSS JOIN|NATURAL [тип_соединения] JOIN имя_таблицы}

}

[, [псевдоним_таблицы]

[, имя таблицы …]

[WHERE условие]

{[GROUP BY {выражение | {выражение [, выражение …]}

  | CUBE (выражение [, выражение …])

  | ROLLUP (выражение [, выражение …])

}

GROUPING SETS (

  {выражение | {выражение [, выражение …]}

  |CUBE (выражение [, выражение …])

  |ROLLUP (выражение [, выражение …])

}

[HAVING условие]

[[START WITH условие] CONNECT BY условие]

[{UNION [ALL]] | INTERSECT | MINUS} {подзапрос}

[,{UNION [ALL] | INTERSECT | MINUS} {подзапрос}…]]

[ORDER BY{выражение | позиция псевдоним} [ASC | DESC]

[, {выражение позиция псевдоним} [ASC | DESC] …]

FOR UPDATE[OF] {таблица | представление}.столбец 

[{таблица представление}.столбец ]

[NOWAIT]

При описании инструкции используются следующие ключевые слова:

DISTINCT – указывает, что должна быть возвращена только одна копия строки, даже в случае наличия дубликатов строк;

AS задает псевдоним для столбца или выражения, может отсутствовать;

ALL указывает, что должны быть возвращены все строки, включаю дубликаты. По умолчанию ALL.

AS OF обеспечивает работу с данными по состоянию на конкретный номер системного изменения (SCN)  или временную метку как указывает выражение;

SAMPLE [BLOCK] -выполняется случайная (или блочная) выборка строк таблицы;

START WITH – указывает строки, выступающие в качестве корневых в иерархическом запросе (если эти ключевые слова пропущены, то все строки таблицы считаются корневыми);

CONNECT BY – определяет отношение между родительскими и дочерними строками иерархии;

INNER внутреннее соединение (установлено по умолчанию);

RIGHT правое внешнее соединение;  

LEFT левое внешнее соединение;

FULL полное внешнее соединение;

ON условие задает условие соединения, которое  не привязано к условию WHERE;

USING - соединение по равенству для столбцов, имеющих одинаковые имена;

CROSS JOIN - перекрёстное соединение, т.е. прямое произведение двух отношений;

NATURAL - естественное соединение по одноименным столбцам двух таблиц с одинаковыми значениями;

JOIN - явное указание на соединение, эквивалентно перечислению таблиц через запятую;

CUBE группировка на основе всех возможных комбинаций значений предложенного списка отношений;

ROLLUP группировка на основе значений предложенного списка выражений и сводных строк, возвращённых для каждого выражения наряду с дополнительной строкой общего итога;

GROUPING SETS – задает несколько групп данных для более удобного агрегирования (если указаны только нужные группы, то серверу не приходится выполнять все множество агрегатирований, которые требуют CUBE и ROLLUP);

FOR UPDATE [OF] – означает, что выбранные строки будут заблокированы. Если указывается ключевое слово OF, то будут заблокированы только строки названной таблицы;

NOWAIT – означает, что если таблица уже заблокирована, то сервер Oracle не будет ждать снятия блокировки.

6. Предложения SELECT и FROM

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

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

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

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

Предложение FROM состоит из ключевого слова FROM, за которым следует список спецификаторов таблиц, разделенных запятыми. Каждый спецификатор таблицы идентифицирует таблицу, содержащую данные, которые извлекает запрос. Такие таблицы называются исходными таблицами запроса.

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

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

То что, SQL-запрос всегда возвращает таблицу, означает следующее:

  1.  результаты запроса можно записать обратно в базу данных в виде таблицы,
  2.  результаты двух запросов, имеющие похожую структуру, можно объединить в одну таблицу,
  3.  результаты запроса сами могут стать предметом дальнейших запросов.

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

Простые запросы. Простые запросы извлекают данные из столбцов, расположенных в одной таблице базы данных. Например, следующий запрос извлекает из таблицы STAFF три столбца:

Вывести для каждого сотрудника имя, фамилию и занимаемую должность:

SELECT fname, lname, position 

FROM staff;

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

Вычисляемые столбцы. Кроме столбцов, значения которых извлекаются непосредственно из базы данных, SQL-запрос на выборку может содержать вычисляемые столбцы, значения которых определяются на основании значений, хранящихся в базе данных. Чтобы получить вычисляемый столбец, в списке возвращаемых столбцов необходимо указать выражение. Выражения могут включать в себя любые арифметические операции, встроенные функции, скобки. Столбцы, участвующие в арифметическом выражении, должны содержать числовые данные. При попытке выполнения арифметических операций над столбцами, содержащие текстовые данные, будет выдано сообщение об ошибке, так как к ним применима только операция конкатенации.

При выполнении следующего запроса создается вычисляемый столбец:

Выдать строки сотрудников с указанием зарплаты с 10 % надбавкой

SELECT fname, lname, position, (salary + 0.1*salary) AS percent

FROM staff;

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

Фраза SELECT может включать не только выражения, но и отдельные числовые или текстовые константы. Текстовые константы должны заключаться в апострофы ('). Это необходимо для создания результата запроса в более удобном для пользователя виде.

SELECT pno, type, 'with rent', rent, 'per cent'

FROM property_for_rent;

Выборка всех столбцов. Иногда требуется получить содержимое всех столбцов таблицы, например, необходимо составить представление о структуре незнакомой базы и хранимых в ней данных. С учетом этого в SQL разрешается использовать вместо списка возвращаемых столбцов символ *, который означает, что требуется извлечь все столбцы в том порядке, в каком они расположены в исходной таблице:

SELECT * FROM staff;

В стандарте ANSI/ISO сказано, что в предложении SELECT может использоваться либо символ выборки всех столбцов либо список возвращаемых столбцов, однако большинство коммерческих СУБД допускают использование и того и другого одновременно, например: SELECT *, salary FROM staff. В Oracle, как и требует стандарт, такой запрос не работает.

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

Определить в каких городах существуют отделения предприятия

SELECT DISTINCT city 

FROM branch;

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

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

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

Вместо DISTINCT можно указать - ALL тогда дублирование строк вывода сохранится.

Сортировка результатов запроса (предложение ORDER BY). Строки результатов запроса, как и строки таблицы БД, не имеют определенного порядка. Поэтому, включив в инструкцию SELECT предложение ORDER BY, можно отсортировать результаты запроса. При этом упорядочение можно производить в порядке возрастания - ASC (ASCending) или убывания DESC (DESCending), по умолчанию принимается ASC.

В предложение ORDER BY входит список имен, разделенных запятыми:

Вывести отделения компании, отсортированные в алфавитном порядке по названию городов:

SELECT bno, city, tel_no

FROM branch

ORDER BY city;

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

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

Для каждого отделения  компании вывести ФИО работников, отвечающих за какие-либо объекты, а также перечень этих объектов в порядке убывания:

SELECT s.bno, s.sno, fname, lname, pno

FROM staff s, property_for_rent p

WHERE s.sno=p.sno

ORDER BY s.bno, s.sno, pno DESC;

7. Отбор строк (предложение WHERE)

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

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

Предложение WHERE состоит из ключевого слова WHERE, за которым следует условие отбора, определяющее, какие именно строки требуется извлечь. Если условие отбора имеет значение TRUE, строка будет включена в результаты запроса. Если же оно имеет значение FALSE или NULL, то строка исключается из результатов запроса.

В SQL обычно используются пять основных условий отбора (в стандарте ANSI/ISO они называются предикатами):

сравнение;

проверка на принадлежность диапазону;

проверка на членство во множестве;

проверка на соответствие шаблону;

проверка на равенство значению NULL.

Операторы сравнения. Наиболее распространенным условием отбора в SQL является сравнение. При сравнении СУБД вычисляет и сравнивает значения двух выражений для каждой строки данных. В SQL имеется шесть различных способов сравнения выражений (по количеству операций сравнения). В ORACLE операции сравнения обозначаются посредством следующих символов: =, !=(не равно), <, >, <=, >=.

Приведем примеры использования запросов с условиями отбора типа сравнения:

Найти служащих, родившихся до 1988 года

SELECT fname, lname

FROM staff

WHERE dob<'01.01.88';

Вывести адреса и номера телефонов всех не Минских отделений

SELECT city, tel_no

FROM branch

WHERE city!='Минск';

Условие сравнение можно делать и составным, объединяя простые сравнения логическими предикатами:

AND когда должны удовлетворяться оба разделяемых с помощью AND условия;

OR когда должно удовлетворяться одно из разделяемых с помощью OR условий;

AND NOT когда должно удовлетворяться первое условие и не должно второе;

OR NOT когда или должно удовлетворяться первое условие или не должно удовлетворяться второе.

Существует приоритет AND над OR (сначала выполняются все операции AND и только после этого операции OR). Для получения желаемого результата WHERE условия должны быть введены в правильном порядке, который можно организовать введением скобок.

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

Проверка на принадлежность значений заданному диапазону (оператор BETWEEN … AND). При этом проверяется, находится ли элемент данных между двумя заданными значениями.

Найти служащих, родившихся в интервале времени с 1октября 1988 по 1 декабря 1990 года:

SELECT fname, lname

FROM staff

WHERE dob between '01.10.88' and '01.12.90';

Следует отметить, что проверка на принадлежность диапазону не расширяет возможностей SQL, поскольку её можно выразить в виде двух сравнений, т. е. выражение A BETWEEN B AND C эквивалентно (A>=B) AND (A<=C).

Помимо рассмотренной проверки в SQL существует также и инверсная проверка на принадлежность диапазону, реализованная с помощью оператора NOT BETWEEN … AND.

В стандарте ANSI/ISO определены относительно сложные правила обработки значений NULL в проверке BETWEEN … AND:

  1.  Если проверяемое выражение имеет значение NULL либо оба выражения, определяющие диапазон, равны NULL, то проверка возвращает NULL.
  2.  Если выражение, определяющее нижнюю границу диапазона, имеет значение NULL, то проверка возвращает FALSE, когда проверяемое значение больше верхней границы диапазона, и NULL в противном случае.
  3.  Если выражение, определяющее верхнюю границу диапазона, имеет значение NULL, то проверка возвращает FALSE, когда проверяемое значение меньше нижней границы диапазона, и NULL в противном случае.

Проверка на членство во множестве (оператор IN). В этом случае проверяется, соответствует ли элемент данных какому-либо значению из заданного списка.

Следующий пример иллюстрирует процедуру проверки на членство во множестве:

Вывести информацию об офисах, расположенных в Минске, Витебске и Бресте:

SELECT city, tel_no 

FROM branch

WHERE city IN ('Minsk', 'Vitebsk', 'Brest');

Проверка IN также не добавляет новых возможностей, так как условие X IN (A, B, C) полностью эквивалентно условию (X=A) OR (X=B) OR (X=C).

Проверка на соответствие шаблону (оператор LIKE). Иногда для получения требуемых данных с выборкой по значениям строковых атрибутов, в условии отбора инструкций на извлечение информации целесообразно применять шаблоны для поиска.

Следующий пример иллюстрирует применение проверки на соответствие шаблону:

Вывести информацию обо всех сотрудниках, фамилии которых начинаются на букву К:

SELECT fname, address, tel_no

FROM staff

WHERE fname LIKE 'S%';

Следует обратить внимание на запись шаблона строки сравнения в условии LIKE. Указанный способ задания строки шаблона характерен для диалекта SQL СУБД ORACLE и отличен от регламентированного стандартом.

Символ процент ‘%’ замещает произвольную последовательность символов, а символ подчеркивание ‘_’ одиночный символ. Шаблоны, так же как и обыкновенные строчные данные заключаются в одинарные кавычки.

Проверка на равенство значению NULL (оператор IS [NOT] NULL). Иногда бывает необходимо проверять значения столбцов на равенство NULL и непосредственно их обрабатывать.

Например найдем служащих, которые не закреплены ни за одним офисом:

SELECT fname 

FROM staff

WHERE bno IS NULL;

Необходимо помнить, что NULL-значения не равны между собой  в операциях сравнения, но при применении предиката DISTINCT считаются одинаковыми.

8. Многотабличные запросы

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

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

Составить список всех сотрудников, работающих в Минском отделении.

SELECT fname, lname, position, S.tel_no

FROM branch B, staff S

WHERE B.bno=S.bno AND city='Minsk';

Представленный в примере синтаксис соединения является классическим синтаксисом, который используется в СУБД Oracle. При отработке запроса СУБД должна произвести ряд действий, связанных с соединением таблиц branch и staff. Так сначала просматривается столбец city с целью фильтрации строк со значениями отличными от значения Minsk, далее для отфильтрованных строк таблицы определяются значения столбца bno как идентификаторов Минских отделений. После этого просматривается таблица staff с целью выявления строк со значениями в столбце bno соответствующими идентификатору первого выявленного Минского офиса. В найденных строках оставляются значения столбцов указанных после ключевого слова SELECT . Далее эта же таблица просматривается для выявления строк соответствующих второму Минскому офису и т. д. до последнего. В итоге формируется таблица результатов с запрошенной информацией.

Отметим некоторые особенности многотабличных запросов. Как видно из примера, в многотабличном запросе часто используются полные имена столбцов, при этом в предложении FROM через пробел указываются псевдонимы таблиц, чтобы упростить обращение к столбцам по полному имени, а также обеспечить однозначность ссылок на столбцы. Кроме этого особый смысл может иметь выбор всех столбцов (SELECT *), например в Oracle поддерживается следующий синтаксис:

SELECT s.*, city

FROM staff s, branch b

WHERE b.bno=s.bno;

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

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

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

Кроме того, комбинирование таблиц в запросе можно осуществлять с помощью операторов UNION – объединение, INTERSECT – пересечение, EXCEPT (minus) – разность.

При этом на таблицы накладываются определенные ограничения. Объединяемые таблицы должны быть совместимы, т.е. иметь одинаковое количество столбцов, а данные в столбцах должны быть одного типа и длины.

Обязанность проверять принадлежность данных к одному домену возлагается на пользователя. Общий синтаксис применения операторов комбинирования:

Operator[all][corresponding[by{column1[, …]}]];

Пример. Вывести список  городов, в которых либо находится отделение компании, либо располагаются сдаваемые в аренду объекты:

SELECT city

FROM branch

WHERE city IS NOT NULL

UNION

SELECT city

FROM property_for_rent

WHERE city IS NOT NULL;

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

Пример. Вывести список всех городов, в которых находятся и отделение компании и сдаваемые в аренду объекты:

SELECT city

FROM branch

INTERSECT

SELECT city

FROM property_for_rent;

Пример.  Вывести список всех городов, в которых  находятся  отделения компании, но нет  сдаваемых в аренду объектов:

SELECT city

FROM branch

MINUS

SELECT city

FROM property_for_rent;

Все эти примеры можно реализовать и по-другому, без использования операторов комбинирования запросов. Возможность использования нескольких эквивалентных форм запроса – один из самых существенных недостатков языка SQL.

9. Внутренние и внешние соединения

Внутренние соединения. В стандарте SQL применяются отличные от рассмотренной выше синтаксические конструкции для соединения нескольких таблиц, которые могут быть использованы и в СУБД Oracle. Причем различают внешние и внутренние соединения, которые рассмотрим на примере двух абстрактных таблиц girls (id, g_fname, g_lname,city, age) и boys (id,b_fname, b_lname,city, age).

Инструкцию внутреннего соединения по полю city можно записать следующим образом:

SELECT *

FROM girls [INNER] JOIN boys ON girls.city=boys.city;

В этом случае две таблицы объединяются явно посредством операции join, а условие отбора, описывающее соединение, находится теперь в предложении ON внутри предложения FROM. В условии отбора могут быть заданы любые критерии сравнения строк двух соединяемых таблиц.

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

Стандарт SQL допускает еще один вариант запроса на простое внутреннее соединение таблиц. Так как связанные столбцы этих таблиц имеют одинаковые имена и сравниваются на предмет равенства (что делается довольно часто), то можно использовать альтернативную форму предложения ON, в которой задается список имен связанных столбцов:

SELECT *

FROM girls [INNER] JOIN boys USING (city, age);

В предложении USING перечисляются через запятую имена связанных столбцов; котрые должны быть идентичными в обеих таблицах. Это предложение полностью эквивалентно предложению ON, в котором каждая пара связанных столбцов задается явно, но намного компактнее и, следовательно, легче для понимания. Конечно, если связанные столбцы имеют разные имена в таблицах, то необходимо использовать предложение ON или WHERE со знаком равенства. Предложение ON требуется использовать также в том случае, если соединение не производится по равенству связанных столбцов. Например, если вы хотите выбрать пары девочка/мальчик, в которых девочка старше мальчика, то должны задать соединение с помощью предложения ON:

SELECT * FROM girls [INNER] JOIN boys 

ON (girls.city=boys.city AND girls.age>boys.age);

Наконец, имеется еще один вариант этого простого запроса. Соединение двух таблиц, в котором связанные столбцы имеют идентичные имена, называется естественным соединением, так как обычно это действительно самый «естественный» способ соединения двух таблиц. Запрос на выборку пар девочка/мальчик, живущих в одних и тех же городах и имеющих тот же самый возраст, можно выразить как естественное соединение следующим образом:

SELECT * FROM girls NATURAL [INNER] JOIN boys;

Если задано ключевое слово natural, предложения on и using могут отсутствовать в запросе на соединение, так как в естественном соединении точно определено условие отбора для соединения двух таблиц - сравниваются все столбцы с идентичными именами в обеих таблицах.

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

Внешние соединения. Стандарт SQL обеспечивает полную поддержку внешних соединений, расширяя языковые конструкции, используемые для внутренних соединений. Например, полное внешнее соединение таблиц girls и boys (без столбцов age) создается следующим запросом:

SELECT *

FROM girls FULL [OUTER] JOIN boys ON girls.city=boys.city;

Таблица результатов запроса будет содержать по одной строке для каждой связанной по полю city пары девочка/мальчик, а также по одной строке для каждой несвязанной равенством значений в поле city записи для девочки или мальчика, расширенной значениями NULL в столбцах другой таблицы. В стандарте SQL для внешних соединений допустимы те же самые вариации, что и для внутренних соединений, т.е. запрос можно было бы записать так:

SELECT *

FROM girls NATURAL FULL [OUTER] JOIN boys;

Или так:

SELECT *

FROM girls FULL [OUTER] JOIN boys USING (city);

Ключевое слово outer также не является обязательным. По слову fulL СУБД сама определяет, что запрашивается внешнее соединение.

Левое и правое внешние соединения обозначаются словами left и right вместо слова full. Вот вариант того же запроса, определяющий левое внешнее соединение:

SELECT *

FROM girls LEFT [OUTER] JOIN boys USING (city);

В таком запросе в результат войдут строки связанных пар девочка/мальчик и все несвязанные строки из таблицы girls, содержащие NULL для значений полей таблицы boys, но не войдут несвязанные строки из таблицы boys.

Правое внешнее соединение:

SELECT *

FROM girls RIGHT [OUTER] JOIN boys USING (city);

В данном случае в таблице результатов будут представлены пары девочка/мальчик и несвязанные строки из таблицы boys («правая» таблица объединения) содержащие значения NULL для несвязанных значений полей таблицы girls,  но будут отсутствовать несвязанные строки из таблицы girls.

Расширенное предложение FROM поддерживает также два других способа соединения таблиц - декартово произведение и собственное соединение. Вот запрос, создающий декартово произведение таблиц girls и boys:

SELECT *

FROM girls CROSS JOIN boys;

По определению, декартово произведение (называемое перекрестным соединением (cross join)) содержит все возможные пары строк из двух таблиц. Оно является результатом «умножения» двух таблиц и превращает таблицы с данными, например, о трех девочках и двух мальчиках в таблицу шести пар девочка/мальчик (3*2=6). Перекрестным соединениям не сопутствуют никакие «связанные столбцы» или «условия отбора», поэтому предложения ON и USING в них не допускаются. Следует отметить, что операция перекрестного соединения не добавляет ничего нового к возможностям SQL. Те же результаты можно получить с помощью простого внутреннего соединения, если не задать в нем условия отбора. Поэтому предыдущий запрос можно переписать так:

SELECT * FROM girls, boys;

Ключевые слова CROSS JOIN в предложении FROM просто в явном виде указывают на то, что создается декартово произведение. Для большинства БД такая операция вряд ли будет представлять практический интерес. Она полезна лишь тогда, когда на основе полученной таблицы строятся более сложные выражения, например итоговые запросы.

И, наконец, таблица может соединяться сама с собой – так называемое собственное соединение (self-join). Например, вернемся к основному примеру БД и составим все возможные пары между сотрудниками:

SELECT s1.fname||'  и  '||s2.fname

FROM staff s1

JOIN staff s2

ON (s2.sno!=s1.sno);

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

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

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

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

Таблицу можно соединять саму с собой, при этом необходимо использовать псевдоним таблицы.

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

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

На практике левое и правое внешние соединения более полезны, чем полное внешнее соединение, особенно если таблицы связаны через внешний и первичный ключи.

10. ПРЕДЛОЖЕНИЯ GROUP BY и HAVING

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

Столбцы, указанные в предложении GROUP BY, называются столбцами группировки, поскольку именно они определяют, по какому признаку строки делятся на группы. Приведем пример запроса с предложением GROUP BY:

Определить сколько в среднем получают сотрудники в зависимости от занимаемой ими должности:

SELECT position, AVG(salary)

FROM staff

GROUP BY position;

На логическом уровне запрос выполняется следующим образом:

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

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

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

На запросы, в которых используется группировка, накладываются дополнительные ограничения:

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

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

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

На практике в список возвращаемых столбцов запроса с группировкой всегда входят столбец группировки и статистическая функция. Если последняя не указана, значит, запрос можно более просто выразить с помощью предиката DISTINCT без использования предложения GROUP BY. И наоборот, если не включить в результаты запроса столбец группировки, то невозможно будет определить, к какой группе относится каждая строка результатов. Другими словами, все имена столбцов в SELECT должны присутствовать и во фразе GROUP BY, за исключением случаев, когда имя столбца используется в обобщающей функции. Обратное утверждение неверно.

В стандарте ANSI/ISO определено, что два значения NULL в предложении GROUP BY равны. Если две строки имеют значение NULL в одинаковых столбцах группировки и идентичные значения во всех остальных столбцах группировки, то они помещаются в одну группу.

Условие отбора групп (предложение HAVING). Точно так же, как предложение WHERE используется для отбора отдельных строк, участвующих в запросе, предложение HAVING можно применить для отбора групп строк. Его формат соответствует формату предложения WHERE, т.е. состоит из ключевого слова HAVING, за которым следует условие отбора. Рассмотрим пример:

Подсчитать количество сотрудников работающих в каждом из офисов, исключив офисы, в которых работает менее 2 человек:

SELECT bno, count (sno) Kolichestvo

FROM staff

GROUP BY bno

HAVING COUNT(sno)>2;

Отметим, что в предложениях GROUP BY и HAVING не используются псевдонимы.

Ограничения на условия отбора групп. Предложение HAVING используется для того, чтобы включать и исключать группы строк из результатов запроса, поэтому используемое в нем условие отбора применяется не к отдельным строкам, а к группе в целом, т.е. используется только совместно с GROUP BY. Это значит, что в условие отбора может входить:

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

На практике условие отбора предложения HAVING всегда должно включать в себя как минимум одну статистическую функцию. Если это не так, значит, условие отбора можно переместить в предложение WHERE. Чтобы определить, где следует указать условие отбора - в предложении WHERE или HAVING - необходимо вспомнить, как применяются эти предложения:

  •  предложение WHERE применяется к отдельным строкам, поэтому выражения, содержащиеся в нем, должны вычисляться для отдельных строк и в нем никогда не применяются статистические функции;
  •  предложение HAVING применяется к группам строк, поэтому выражения, содержащиеся в нем, должны вычисляться для групп строк. При этом стандарт требует, чтобы имена столбцов, использующиеся в HAVING, обязательно присутствовали в списке GROUP BY или применялись в статистических функциях.

Как и условия отбора в предложении WHERE, условие отбора в предложении HAVING может дать один из следующих результатов:

  •  если условие отбора имеет значение TRUE, группа строк остается и для нее генерируется одна строка результатов запроса;
  •  если условие отбора имеет значение FALSE или NULL, группа строк исключается, и строка для нее в результатах запроса не генерируется.

11. СТАТИСТИЧЕСКИЕ ФУНКЦИИ

Для подведения итогов по информации, содержащейся в базе данных, в SQL предусмотрены статистические (агрегатные) функции. Статистическая функция принимает в качестве аргумента какой-либо столбец данных целиком, а возвращает одно значение, которое определенным образом подытоживает этот столбец, исключая все пустые значения.

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

- SUM() – для вычисления суммы всех значений столбца-аргумента;

- AVG() – для вычисления среднего значения столбца;

- MIN() – определяет минимальное значение столбца;

- MAX() – определяет максимальное значение столбца;

- COUNT() – подсчитывает число всех определенных значений столбца;

- COUNT(*) – подсчитывает число строк таблицы.

Аргументом статистической функции может быть простое имя столбца или выражение.

В Oracle применяется более 20 статистических функций, например, добавлены такие функции, как:

CORR() - определяет коэффициент корреляции пар;

KEEP() - используется для работы со множеством значений, отобранных SUM(), MIN(), MAX();

PERCENT_RANK() - вычисляет относительную позицию строки;

RANK() - вычисляет ранг строки по отношению к другим строкам в группе (используется с ORDER BY) и др.

Приведем примеры запросов со стандартными статистическими функциями:

Определить среднемесячную и среднегодовую зарплату сотрудников предприятия

SELECT AVG(salary) AS per_month, AVG(12*salary) AS per_year

FROM staff;

В примере в списке возвращаемых столбцов результирующего множества используются псевдонимы столбцов, иначе значения в возвращаемой строке были бы представлены под именами avg(salary) и avg(12*salary).

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

SELECT COUNT (sno) manager

FROM staff

WHERE position='Manager';

Следует заметить, что для функции COUNT() важны не конкретные значения ячеек, а количество ячеек, удовлетворяющих заданному критерию.

В отличие от функции COUNT() статистическая функция COUNT(*) подсчитывает строки, а не значения в них. Функция COUNT() не учитывает NULL-значений в столбцах, также как и любая другая статистическая функция.

В SQL существует ряд ограничений на применение статистических функций в инструкциях SELECT:

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

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

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

В целом статистические функции могут использоваться только в SELECT и HAVING, при чем, если они есть в SELECT, а в GROUP BY нет, то никакие другие столбцы в списке SELECT быть не могут.

В стандарте ANSI/ISO сказано, что значения NULL статистическими функциями игнорируются и определены следующие точные правила обработки значений NULL в статистических функциях:

  •  если какие-либо из значений, содержащихся в столбце, равны NULL, при вычислении результата функции они исключаются;
  •  если все значения в столбце равны NULL, то функции SUM(), AVG(), MIN() и MAX() возвращают значение NULL; функция COUNT() возвращает ноль;
  •  если в столбце нет значений (т.е. столбец пустой), то функции SUM(), AVG(), MIN() и MAX() возвращают значение NULL; функция COUNT() возвращает ноль;
  •  функция COUNT(*) подсчитывает количество строк и не зависит от наличия или отсутствия в столбце значений NULL; если строк в таблице нет, эта функция возвращает ноль.

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

12. ПОДЧИНЕННЫЕ ЗАПРОСЫ (ПОДЗАПРОСЫ)

Подчиненным называется запрос, содержащийся в предложении WHERE или HAVING другой инструкции SQL. В предложении WHERE они помогают отбирать из таблицы результатов запроса отдельные строки, а в предложении HAVING – группы строк. Кроме этого подчиненные запросы используются в инструкциях INSERT, UPDATE, DELETE, а также в списке столбцов инструкции SELECT.

Механизм подчиненных запросов позволяет использовать результаты одного запроса в качестве составной части другого.

По типу возвращаемых значений существует три типа подзапросов:

1. скалярный – возвращает единственное значение;

2. строковый – возвращает значения нескольких столбцов таблицы, но в одной строке;

3. табличный – возвращает значения одного и более столбцов таблицы, размещенные более чем в одной строке.

Понятие подчиненного запроса играет важную роль в SQL по трем следующим причинам:

  1.  инструкция SQL с подчиненным запросом зачастую является самым естественным способом выражения запроса, так как она лучше всего соответствует его словесному описанию;
  2.  подчиненные запросы облегчают написание инструкции SELECT, поскольку они позволяют разбивать запрос на части (на запрос и подчиненные запросы);
  3.  существуют запросы, которые нельзя сформулировать на SQL иначе как с помощью подзапросов.

Подчиненный запрос всегда заключается в круглые скобки и сохраняет структуру инструкции SELECT, содержащей предложение from и необязательные предложения where, group by и having. Структура этих предложений в подчиненном запросе идентична их структуре в инструкции select; в подчиненном запросе эти предложения выполняют свои обычные функции.

Однако между подчиненным запросом и инструкцией SELECT в соответствии со стандартом имеется ряд отличий:

  1.  таблица результатов подчиненного запроса должна состоять только из одного столбца. Это означает, что в предложении SELECT подчиненного запроса всегда указывается только один возвращаемый столбец.
  2.  в подчиненный запрос не может входить предложение ORDER BY. Результаты подчиненного запроса используются только внутри главного запроса и для пользователя остаются невидимыми, поэтому нет смысла их сортировать.
  3.  имена столбцов в подчиненном запросе могут являться ссылками (внешними ссылками) на столбцы таблиц главного запроса.
  4.  подчиненный запрос не может быть запросом на объединение нескольких различных инструкций select (т.е. нельзя использовать UNION).
  5.  подчиненный запрос в операции сравнения может стоять только справа от оператора сравнения, т.е. неравенство А<(подчиненный запрос) разрешается, а неравенство (подчиненный запрос)>А недопустимо. Сказанное не ограничивает возможности операции сравнения, поскольку знак любого неравенства всегда можно "перевернуть" так, чтобы подчиненный запрос оказался с правой стороны. Однако это говорит о том, что иногда требуется «переворачивать» логику словесного запроса так, чтобы он формально соответствовал действительной инструкции SQL.

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

Условия отбора в подчиненном запросе. В SQL используются следующие условия отбора в подчиненном запросе:

  1.  сравнение с результатом подчиненного запроса. Значение выражения сравнивается с одним значением, которое возвращается подчиненным запросом.
  2.  проверка на принадлежность результатам подчиненного запроса. Значение выражения проверяется на равенство одному из множества значений, которые возвращаются подчиненным запросом. Эта проверка напоминает простую проверку на членство во множестве.
  3.  проверка на существование. Проверяется наличие строк в таблице результатов подчиненного запроса.
  4.  многократное сравнение. Значение выражения сравнивается с каждым из множества значений, которые возвращаются подчиненным запросом.

Сравнение с результатом подчиненного запроса (операторы =, !=, <, <=, >, >=). Сравнение с результатом подчиненного запроса является модифицированной формой простого сравнения. Значение выражения сравнивается со значением, которое возвращается подчиненным запросом, и если условие сравнения выполняется, то проверка дает результат TRUE. Эта проверка используется для сравнения значения из проверяемой строки с одним значением, полученным от подчиненного запроса, как показано в следующих примерах:

Вывести список сотрудников, котрые работают в отделении на заданной улице:

SELECT sno, fname, lname, position 

FROM staff 

WHERE bno=(SELECT bno 

FROM branch 

WHERE street like('%Kazinca%'));

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

SELECT sno, fname, lname, position, salary-(SELECT AVG(salary) FROM staff) AS sal_diff

FROM staff

WHERE salary>(SELECT AVG(salary)

      FROM staff);

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

Проверка на принадлежность результатам подчиненного запроса (предикат IN). Эта проверка является видоизмененной формой простой проверки на членство во множестве. Одно значение сравнивается со столбцом данных, которые возвращаются подчиненным запросом, и если это значение равно одному из элементов столбца, проверка дает результат TRUE. Данная проверка используется, когда необходимо сравнить значение из проверяемой строки с множеством значений, отобранных подчиненным запросом. Рассмотрим пример:

Вывести список сотрудников, работающих в Минских отделениях компании

SELECT fname, lname, position 

FROM staff

WHERE bno IN (SELECT bno 

FROM branch 

WHERE city='Minsk');

Этот подчиненный запрос возвращает список идентификаторов отделений, расположенных в Минске. Главный (внешний запрос) посредством проверки IN возвращает выборочную информацию о сотрудниках из тех строк таблицы staff , которые по значению в bno удовлетворяют ее истинности.

Следует отметить, что в запросах с подчинением возможно употребление также и инверсной формы проверки на принадлежность результатам подчиненного запроса посредством предиката NOT IN .

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

Если подчиненный запрос не возвращает ни одной строки, проверка EXISTS возвращает значение FALSE. Эта проверка не может возвращать NULL. Можно изменить логику проверки EXISTS и использовать форму NOT EXISTS.

Предикат EXISTS в действительности совсем не использует результаты подчиненного запроса. Проверяется только наличие результатов. По этой причине в SQL смягчается правило, согласно которому «подчиненный запрос должен возвращать один столбец данных», и в подчиненном запросе проверки EXISTS допускается использование формы SELECT *, что и наблюдается на практике. Рассмотрим еще один вариант создания предыдущего вопроса:

Вывести список сотрудников, работающих в Минских отделениях компании

SELECT sno, fname, lname, position 

FROM staff s

WHERE EXISTS (SELECT *

FROM branch b

WHERE s.bno=b.bno AND city='Minsk');

Отметим, что в приведенном примере подчиненный запрос содержит внешнюю ссылку на столбец таблицы из главного запроса (s.bno). Внешняя ссылка представляет собой имя столбца, не входящего ни в одну из таблиц, перечисленных в предложении FROM подчиненного запроса, и принадлежащего таблице, указанной в предложении FROM главного запроса. На практике в подчиненном запросе проверки EXISTS всегда имеется внешняя ссылка, «связывающая» подчиненный запрос со строкой, проверяемой в настоящий момент главным запросом.

Многократное сравнение (предикаты ANY и ALL). В SQL имеются также две разновидности многократного сравнения – ANY и ALL, расширяющие предыдущие проверки до уровня других операторов сравнения, таких как больше (>) или меньше (<). В обеих проверках некоторое значение сравнивается со столбцом данных, отобранных подчиненным запросом.

Предикат ANY. В проверке ANY, для того чтобы сравнить проверяемое значение со столбцом данных, отобранных подчиненным запросом, используется один из шести операторов сравнения (=, !=, <, <=, >, >=). Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если любое из этих сравнений дает результат TRUE, то проверка ANY возвращает значение TRUE. Вот пример запроса с предикатом ANY:

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

SELECT fname, lname, tel_no

FROM renter

WHERE max_rent >= ANY (SELECT rent 

FROM property_for_rent 

WHERE type='f' AND rooms=2);

В соответствии со стандартом ANSI/ISO вместо предиката ANY можно использовать предикат SOME. Обычно можно употреблять любой из них, но некоторые СУБД не поддерживают предикат SOME.

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

Если подчиненный запрос возвращает результат в виде пустого столбца, то проверка ANY возвращает значение FALSE (в результате выполнения подчиненного запроса не получено ни одного значения, для которого выполнялось бы условие сравнения).

Если операция сравнения имеет значение TRUE хотя бы для одного значения в столбце, то проверка ANY возвращает значение TRUE (имеется некоторое значение, полученное подчиненным запросом, для которого условие сравнения выполняется).

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

Если операция сравнения не имеет значение TRUE ни для одного значения в столбце, и в нем имеется одно или несколько значений NULL, то проверка ANY возвращает результат NULL. (В этой ситуации невозможно с определенностью утверждать, существует ли полученное подчиненным запросом значение, для которого выполняется условие сравнения; может быть, существует, а может и нет — все зависит от «настоящих» значений неизвестных данных.)

На практике проверка ANY иногда может приводить к ошибкам, которые трудно выявить, особенно когда применяется оператор сравнения "не равно" (!=).

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

Предикат ALL. В проверке ALL, как и в проверке ANY, используется один из шести операторов сравнения проверяемого значения со столбцом данных, отобранных подчиненным запросом. Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если все сравнения дают результат TRUE, то проверка ALL возвращает значение TRUE. Вот пример запроса с предикатом ALL:

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

SELECT fname, lname, tel_no

FROM renter

WHERE max_rent >= ALL(SELECT rent 

FROM property_for_rent 

WHERE type='f' AND rooms=2);

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

Если подчиненный запрос в проверке ALL не возвращает ни одной строки или если столбец результатов запроса содержит значения NULL, то в различных СУБД проверка ALL может выполняться по-разному. В стандарте ANSI/ISO для языка SQL содержатся подробные правила, определяющие результаты проверки ALL, когда проверяемое значение сравнивается со столбцом результатов подчиненного запроса. Если подчиненный запрос возвращает результат в виде пустого столбца, то проверка ALL возвращает значение TRUE. Считается, что условие сравнения выполняется, даже если результаты подчиненного запроса отсутствуют.

Если операция сравнения дает результат FALSE для какого-нибудь значения в столбце, то проверка ALL возвращает значение FALSE. В этом случае можно утверждать, что условие сравнения выполняется не для каждого значения, возвращенного подчиненным запросом.

Если операция сравнения не дает результат FALSE ни для одного значения в столбце, но для одного или нескольких значений дает результат NULL, то проверка ALL возвращает значение NULL. В этой ситуации нельзя с определенностью утверждать, для всех ли значений, возвращенных подчиненным запросом, справедливо условие сравнения; может быть, для всех, а может и нет — все зависит от "настоящих" значений неизвестных данных.

Ошибки, которые могут случиться, если проверка ANY содержит оператор сравнения "не равно" (<>), происходят и в проверке ALL. Проверку ALL, так же как и проверку ANY, всегда можно преобразовать в эквивалентную проверку на существование (EXISTS), перенеся операцию сравнения в подчиненный запрос.

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

Составить перечень сдаваемых в аренду объектов, за которые отвечают работники отделения компании, расположенного на улице, начинающейся на букву K:

SELECT street, city, type, rooms, rent

FROM property_for_rent 

WHERE sno IN (SELECT sno

 FROM staff

 WHERE bno=(SELECT bno

 FROM branch

 WHERE street LIKE '%K%'));

По такой же методике можно создавать запросы с четырьмя и более уровнями вложенности. Стандарт ANSI/ISO не определяет максимальное число уровней вложенности, но на практике с ростом их числа очень быстро увеличивается время выполнения запроса. Когда запрос имеет более двух уровней вложенности, он становится трудным для чтения и понимания. Во многих СУБД количество уровней вложенности запросов ограничено относительно небольшим числом.

13. ДОБАВЛЕНИЕ ДАННЫХ (инструкция INSERT)

Наименьшей единицей информации, которую можно добавить в реляционную базу данных, является одна строка. Рассмотрим три способа добавления новых строк в базу данных:

  1.  Однострочная инструкция INSERT позволяет добавить в таблицу одну новую строку. Она широко используется в повседневных приложениях, например в программах ввода данных.
  2.  Многострочная инструкция INSERT обеспечивает извлечение строк из одной части базы данных и добавление их в другую таблицу. Она обычно используется, когда “старые” строки таблицы пересылаются в неиспользуемую таблицу для сохранения.
  3.  Инструкция MERGE.

Однострочная инструкция INSERT. Однострочная инструкция INSERT, добавляет в таблицу новую строку.

INSERT INTO имя_таблицы [(список_столбцов)]

VALUES (список_значений_данных);

В предложении INTO указывается имя таблицы или представления, в которые добавляется новая строка, а в предложении VALUES содержатся значения данных для новой строки. Список столбцов определяет, какие значения, в какой столбец заносятся.

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

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

Список столбцов в инструкции INSERT – необязательный параметр, он служит для того, чтобы установить соответствие между значениями данных, содержащимися в предложении VALUES, и столбцами, для которых эти данные предназначены. Списки значений и столбцов должны содержать одинаковое число элементов, а тип данных каждого значения должен быть совместимым с типом соответствующего столбца. Если списка столбцов нет, то используются все столбцы в том порядке, в котором они перечислены в CREATE TABLE.

Добавление значений NULL. При добавлении в таблицу новой строки всем столбцам, имена которых отсутствуют в списке столбцов инструкции INSERT, автоматически присваивается значение NULL.

Значение NULL можно присвоить и явным образом, включив эти столбцы в список, а в списке значений задав для них ключевое слово NULL:

INSERT INTO staff (sno, fname, lname, adress, tel_no, position, sex, salary, dob, bno)

VALUES (6, 'Иванов', 'Иван', 'Nitova 34', '6455811', NULL, 'M', 5000, '03.05.92', 2);

Добавление всех столбцов. Для удобства в SQL разрешается не включать список столбцов в инструкцию INSERT. Если список столбцов опущен, он генерируется автоматически и в нем слева направо перечисляются все столбцы таблицы. При выполнении инструкции SELECT * генерируется такой же список столбцов. Пользуясь этой сокращенной формой записи, предыдущую инструкцию можно переписать таким образом:

insert into staff 

values (6, 'Иванов', 'Иван ', 'Nitova 34', '6455811', NULL, 'M', 5000, '03.05.92', 2);

Как видно, если список столбцов опущен, то в списке значений явно указаны значения NULL, а последовательность значений данных в точности соответствует порядку столбцов в таблице.

Многострочная инструкция INSERT. Многострочная инструкция INSERT добавляет в таблицу несколько строк путем копирования множества строк одной таблицы в другую таблицу.

INSERT INTO имя_таблицы [(список_столбцов)]

SELECT запрос;

В этой разновидности инструкции INSERT значения для новых строк явно не задаются. Источником новых строк служит запрос на выборку (SELECT), содержащийся внутри инструкции.

На запрос, содержащийся внутри многострочной инструкции INSERT, накладывается несколько ограничений:

  •  в запрос нельзя включать предложение ORDER BY. He имеет смысла сортировать таблицу результатов запроса, поскольку она добавляется в таблицу, которая, как и все остальные, не упорядочена;
  •  таблица результатов запроса должна содержать количество столбцов, равное длине списка столбцов в инструкции INSERT (или полностью повторять список столбцов таблицы, если список столбцов отсутствует), а типы данных соответствующих столбцов таблицы результатов запроса и целевой таблицы должны быть совместимыми;
  •  имя таблицы, указанное в инструкции INSERT не может присутствовать в предложении FROM запроса на выборку или любого запроса, вложенного в него. Тем самым запрещается добавление таблицы самой к себе.

Пример:

Пусть существует таблица staff_prop_count (sno, fname, lname, prop_count), которая содержит имена работников компании и их учетные номера, а также количество объектов аренды, за которые они отвечают.

INSERT INTO staff_prop_count

SELECT s.sno, fname, lname, COUNT(p.sno)

FROM staff s, property_for_rent p

WHERE s.sno=p.sno

GROUP BY s.sno, fname, lname

UNION

SELECT s.sno, fname, lname,0

FROM staff s

WHERE s.sno NOT IN (SELECT DISTINCT p.sno

FROM property_for_rent p);

Отметим, что инструкция UNION в таком применении поддерживается не во всех диалектах SQL.

Инструкция MERGE. Начиная с версии Oracle 9i, появилась возможность использовать инструкцию MERGE INTO, которая объединяет в себе возможности инструкций вставки, обновления и удаления, делая их более удобными для пользователей.

MERGE INTO – это DML-оператор вставки (INSERT), обновления (UPDATE) и удаления (DELETE, начиная с Oracle Database 10g) данных при слиянии таблиц. Kогда выполняется оператор MERGE, в сервере Oracle9i Database немедленно сработывают триггеры BEFORE UPDATE и BEFORE INSERT, так как в этом операторе есть предложения WHEN MATCHED THEN UPDATE (когда совпадают, то обновить) и WHEN NOT MATCHED THEN INSERT (когда не совпадают, то вставить) которые являются обязательными. В сервере Oracle Database 10g будут срабатывать триггеры BEFORE UPDATE, INSERT и/или DELETE – в зависимости от указанных в операторе MERGE операций.

Общий синтаксис инструкции MERGE INTO:

MERGE INTO destination_table dest

 USING (SELECT col1, col2, col3 FROM source_table) source1

     ON (dest.col1 = source1.col1)

     WHEN MATCHED THEN

         UPDATE SET dest.col2 = source1.col2,

                                  dest.col3 = source1.col3

         WHERE source1.col2 IS NOT NULL

         DELETE source1.col2 IS NULL

     WHEN NOT MATCHED THEN

          INSERT (dest.col1, dest.col2, dest.col3)

          VALUES (source1.col1, source1.col2, source1.col3)

          WHERE source1.col2 IS NOT NULL

Это простое слияние таблиц destination_table и source_table,для которых проверяется условие dest.col1=source1.col1, и если оно истинно, то выполняется UPDATE, если нет – INSERT. Причем нельзя изменять поля таблицы destination_table в секции UPDATE, по которым идет связывание двух таблиц. В качестве source_table чаще всего используется подзапрос.

Пример: в дополнительной таблице copy_staff обновлять фамилию, имя и зарплпату сотрудников, если они уже есть в этой таблице, иначе добавлять новых сотрудников:

MERGE INTO copy_staff d

USING (SELECT * FROM staff) s

ON (d.sno=s.sno)

WHEN MATCHED THEN

UPDATE SET

d.fname=s.fname,

d.lname=s.lname,

d.salary=s.salary

WHEN NOT MATCHED THEN

INSERT VALUES (s.sno, s.fname, s.lname, s.adress, s.tel_no, s.position, s.sex, s.dob, s.salary, s.bno);

 

Назначение операции MERGE – ускорить обновление больших таблиц. Обратите внимание, что обновление выполняется применительно к одному состоянию БД и поэтому не сводимо к последовательному выполнению команд INSERT, UPDATE и DELETE.

14. УДАЛЕНИЕ ДАННЫХ (инструкция delete)

Инструкция DELETE удаляет выбранные строки из одной таблицы.

delete FROM имя_таблицы

[WHERE условие];

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

Предположим, что недавно принятый на работу Иванов решил уволиться из компании. Инструкция DELETE, удаляющая относящуюся к служащему строку из таблицы staff выглядит следующим образом:

delete FROM staff  

WHERE lname=‘Иванов’;

Условия отбора, которые можно задать в предложении WHERE инструкции DELETE, полностью совпадают с условиями отбора, доступными в одноименном предложении инструкции SELECT рассмотренными ранее.

Удаление всех строк. Хотя предложение WHERE в инструкции DELETE является необязательным, оно присутствует почти всегда. Если же оно отсутствует, то удаляются все таблицы. Например:

Удалить информацию обо всех сотрудниках организации:

DELETE FROM staff;

В результате выполнения приведенной инструкции таблица STAFF становится пустой, но из базы данных она не удаляется, так как определение таблицы и ее столбцов остается и в нее по-прежнему можно добавлять новые строки. Чтобы удалить из базы данных определение таблицы, необходимо использовать DDL-инструкцию DROP TABLE.

Инструкция DELETE, приведенная в примере несет в себе потенциальную угрозу удаления необходимых строк, поэтому всегда следует задавать условие отбора и обращать внимание на то, отбирает ли оно действительно ненужные строки. Желательно вначале проверить предложение WHERE в интерактивном режиме в составе инструкции SELECT и отобразить выбранные строки на экране. Убедившись, что это именно те строки, которые требуется удалить, можно использовать предложение WHERE в инструкции на удаление.

Инструкция DELETE с подчиненным запросом. Инструкции DELETE с простыми условиями отбора отбирают строки для удаления исключительно на основании содержимого этих строк. Но иногда отбор строк необходимо производить, опираясь на данные из других таблиц. Предположим, вы хотите удалить все объекты, принадлежащие какому-либо владельцу. Не зная его идентификатора, невозможно найти принадлежащие ему объекты. Чтобы найти эти объекты, надо обратиться с запросом к таблице владельцев (OWNER). Для решения подобных задач SQL предоставляет возможность задания подзапросов в предложении WHERE инструкции на удаление. Задача удаления информации об объектах, принадлежащих владельцу Иванову, решается следующим образом:

DELETE FROM property_for_rent 

WHERE property_for_rent.ono=(SELECT ono

FROM owner 

WHERE fname='Иванов');

Подчиненные запросы в предложении WHERE могут иметь несколько уровней вложенности. Они могут также содержать внешние ссылки на целевую таблицу инструкции DELETE. В этом случае предложение FROM инструкции DELETE играет такую же роль, как и предложение FROM инструкции SELECT.

15. ОБНОВЛЕНИЕ ДАННЫХ (инструкция UPDATE)

Инструкция UPDATE обновляет значения одного или нескольких столбцов в выбранных строках одной таблицы.

update имя_таблицы 

set имя_столбца_1=значение_1 [, имя_столбца_2=значение_2 …]

[WHERE условие];

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

Увеличить заработную плату менеджеров на 10%

update staff

set salary=salary*1.1

WHERE position='Manager';

Условия отбора, которые могут быть заданы в предложении WHERE инструкции UPDATE, в точности соответствуют условиям отбора, доступным в инструкциях SELECT и DELETE.

Как и инструкция DELETE, инструкция UPDATE может одновременно обновить несколько строк, соответствующих условию отбора. Например:

Перевести всех служащих из отделения № 2 в отделение № 1

update staff 

set bno=1

WHERE bno=2;

 

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

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

Если выражение в операции присваивания содержит ссылку на один из столбцов таблицы, то для вычисления выражения используется значение этого столбца в текущей строке, которое было перед обновлением. То же самое справедливо для ссылок на столбцы в предложении WHERE.

Обновление всех строк. Предложение WHERE в инструкции UPDATE является необязательным. Если оно опущено, то обновляются все строки целевой таблицы. Например:

Увеличить заработную плату всех сотрудников на 5%:

update staff

set salary=1.05*salary;

Инструкции UPDATE с подчиненным запросом. В инструкции UPDATE, так же как и в инструкции DELETE, подчиненные запросы могут играть важную роль, поскольку они дают возможность отбирать строки для обновления, опираясь на информацию из других таблиц. Например:

Всем служащим отделения Минского отделения увеличить зарплату на 10%

update staff 

set salary=1.1*salary

WHERE bno IN (SELECT bno

FROM branch 

WHERE city='Minsk');

Подчиненные запросы в предложении WHERE инструкции UPDATE, так же как и в инструкции DELETE, могут иметь любой уровень вложенности и содержать внешние ссылки на целевую таблицу инструкции UPDATE.

16. Инструкции DDL

С помощью инструкций DDL можно:

  •  определить структуру новой таблицы и создать её;
  •  удалить таблицу, которая больше не нужна;
  •  изменить определение существующей таблицы;
  •  определить виртуальную таблицу (или представление) данных;
  •  обеспечить безопасность базы данных;
  •  создать индекс для ускорения доступа к таблице;
  •  управлять физическим размещением данных и т.д.

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

Ядро языка определения данных образуют три команды:

  •  CREATE (создать), позволяющая определить и создать объект базы данных;
  •  DROP (удалить), служащая для удаления существующего объекта базы данных;
  •  ALTER (изменить), с помощью, которой можно изменить определение объекта базы данных.

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

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

Список основных DDL операторов, применяемых в Oracle,приведен в таблице:

Таблица

Инструкция

Назначение

ALTER RESOURCE COST

изменяет формулу, по которой  вычисляется общая стоимость ресурсов сеанса

ALTER SYSTEM

производит динамическое изменение экземпляра БД

ASSOCIATE STATISTICS

определяет, как будет вычисляться статистика для указанных объектов БД

CALL

исполнение хранимого процесса или функции PL/SQL

COMMENT ON

добавляет комментарий для таблицы, представления, моментальной копии или столбца словаря данных

CREATE | ALTER | DROP

CLUSTER

FUNCTION

INDEX

JAVA

MATERIALIZED VIEW

OPERATOR

PACKAGE

PROCEDURE

PROFILE

ROLE

SNAPSHOT

SEQUENCE

TABLE

TABLE SPACE

TRIGGER

TYPE

USER

VIEW

Создает, изменяет или удаляет:

кластер – объект схемы, который содержит одну или несколько таблиц, имеющих несколько общих столбцов

функцию

индекс

объект, содержащий исходный текст, класс или ресурс Java

материализованное представление

оператор

пакет

процедуру

профиль

роль

моментальную копию

последовательность

таблицу

табличное пространство

триггер

тип

пользователя

представление

CREATE | DROP

CONTEXT

DATABASE LINK

DIMENSION

DIRECTORY

LIBRARY

INDEXTYPE

SYNONYM

Создает или удаляет

пространство имен для контекста

ссылку на удаленную БД

измерение

объект каталога

объект схемы, сопоставленный разделяемой библиотеке ОС)

индексный тип

общий или частный синоним

CREATE 

CONTROLFILE

TEMPORARY  TABLESPACE

SCHEMA

Создает:

управляющий файл

временное табличное пространство

несколько таблиц и (или) представлений и выдает права на них в одной инструкции

CREATE | ALTER DATABASE

Создает и изменяет БД

RENAME

Изменяет имя существующей таблицы, представления, последовательности или частного синонима

17. стандартные Типы данных SQL

Каждый столбец базы данных имеет свой тип, указываемый при создании столбца.

В стандарте SQL определены следующие типы:

  1.  символьные:

CHARACTER (len); CHAR (len);

CHARACTER VARYING (len); CHAR VARYING (len); VARCHAR (len);

NATIONAL CHARACTER (len); NATIONAL CHAR (len); NCHAR (len);

NATIONAL CHARACTER VARYING (len);

NATIONAL CHAR VARYING (len); NCHAR VARYING (len);

  1.  двоичные:

BIT (len); BIT VARYING (len);

  1.  числовые:

NUMERIC; DECIMAL; DEC; INTEGER; INT; SMALLINT;

FLOAT; REAL; DOUBLE PRECISION;

  1.  даты/времени:

DATE; TIME; TIME WITH TIME ZONE;

TIMESTAMP; TIMESTAMP WITH TIME ZONE;

  1.  интервальный:  INTERVAL.

Для символьных типов возможно указание фразы CHARACTER SET { set_name | using_form}, устанавливающей используемый набор символов.

Приведем описание наиболее часто используемых типов данных:

CHAR (num) – текстовая строка фиксированной длины (в памяти выделяется место под всю строку);

VARCHAR (num) – текстовая строка переменной длины, содержащая не более num символов (на диске выделяется место в зависимости от длины строки);

INTEGER или INT – целое;

NUMERIC – число с плавающей точкой, возможно определение числа знаков после запятой;

DECIMAL или DEC – число с плавающей точкой, возможно задание минимального значения точности;

FLOAT – число с плавающей точкой, позволяющее задавать точность (количество знаков после запятой);

REAL – число с плавающей точкой, точность которого определяется реализацией;

DATE – тип даты;

TIME WITH TIME ZONE – тип времени, содержащий поля, описывающие сдвиг зонального времени.

Типы данных, описывающие дату и время, состоят из нескольких полей, в которых хранятся части даты времени.

Так, тип DATE содержит поля YEAR, MONTH и DAY.

Тип TIME содержит поля HOUR, MINUTE и SECOND.

Тип TIMESTAMP содержит как поля даты, так и поля времени.

Значение типа TIMESTAMP записывается следующим образом: '10.1.2003 08:30:00'. Порядок следования полей при написании даты, как правило, определяется установками ОС компьютера.

Для работы с данными, имеющими тип даты/времени в языке SQL предусмотрены следующие функции:

CURRENT_TIME – определяет текущее время;

CURRENT_DATE – определяет текущую дату;

CURRENT_TIMESTAMP – определяет текущую дату и время.

Cоответствие между типами данных Oracle и типами данных ANSI/ISO представлено в таблице

Тип данных ANSI/ISO...

Тип данных Oracle

CHARACTER(n), CHAR(n)

CHAR(n) – символьное поле длиной до 2000 символов

CHARACTER VARYING(n),

CHAR VARYING(n)

VARCHAR2(n) – символьное поле переменной длины, до 4000 символов

NATIONAL CHARACTER(n),

NATIONAL CHAR (n), NCHAR(n)

NCHAR(n)

NATIONAL CHARACTER VARYING(n)

NATIONAL CHAR VARYING(n)

NCHAR VARYING(n)

NVARCHAR2(n)

NUMERIC(p,s), DECIMAL(p,s)

NUMBER(p,s) – числовое поле длиной до 38 позиций

INTEGER, INT, SMALLINT

NUMBER(38)

FLOAT, DOUBLE PRECISION, REAL

NUMBER

DATE –  дата в пределах от 1-1-4712г до н.э. до 31-12-4712г н.э. Формат данных определяется настройками сервера.

В 10-й версии введены соответствующие стандарту IEEE754 типы данных – BINARY_FLOAT и BINARY_DOUBLE.

Примечание. В процедурах PL/SQL могут использоваться совместимые с ANSI типы данных: DEC(p,s), DECIMAL(p,s), INT, INTEGER, REAL, FLOAT(p), DOUBLE PRECISION.

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

18. Создание таблиц (инструкция CREATE TABLE)

Инструкция CREATE TABLE, стандартная синтаксическая диаграмма которой изображена на рисунке, определяет структуру новой таблицы БД и подготавливает ее к приему данных. Предложения инструкции задают основные элементы определения таблицы, такие как названия и тип данных, различные виды ограничений.

После выполнения инструкции CREATE TABLE в БД появляется новая таблица, которой присваивается имя, указанное в инструкции, и владельцем которой становится создавший её пользователь. Имя таблицы должно быть идентификатором, допустимым в SQL, и не должно конфликтовать с именами других таблиц в схеме. Созданная таблица является пустой, данных записываются в нее с помощью DML инструкции INSERT INTO.

Определения столбцов. Столбцы новой таблицы задаются в инструкции CREATE TABLE. Определения столбцов представляют собой заключенный в скобки список, элементы которого отделены друг от друга запятыми. Порядок следования определений столбцов в списке соответствует расположению столбцов в таблице. При описании столбца указывается следующая информация:

  •  имя столбца, которое используется для ссылки на столбец в инструкциях SQL. Каждый столбец в таблице должен иметь уникальное имя, но в разных таблицах имена столбцов могут совпадать.
  •  тип данных столбца, указывающий, данные какого вида должны храниться в столбце. Если для типа данных требуется дополнительная информация (например, размерность или число цифр после запятой), то она указывается в скобках за ключевым словом, задающим тип данных.
  •  значение по умолчанию, которое заносится в столбец после ключевого слова DEFAULT в том случае, если в инструкции INSERT для таблицы не будет указано другое значение данного столбца.
  •  указание на то, обязательно ли столбец должен содержать данные. Ограничение NOT NULL, если оно задано, следит за обязательным наличием данных в столбце при заполнении таблицы, в противном случае значения NULL допускаются.

Определение первичного и внешнего ключей. Кроме определений столбцов таблицы, в инструкции CREATE TABLE указывается информация о первичном ключе таблицы и ее связях с другими таблицами базы данных. Эта информация содержится в предложениях PRIMARY KEY и FOREIGN KEY.

В предложении PRIMARY KEY задается столбец, или столбцы, которые образуют первичный ключ таблицы. Как известно, этот столбец (или комбинация столбцов) служит в качестве уникального идентификатора строк таблицы. СУБД автоматически следит за тем, чтобы первичный ключ каждой строки таблицы имел уникальное значение. Кроме того, в столбцах, использующихся в качестве первичного ключа не  должны допускаться значения NULL (имеют ограничение NOT NULL).

В предложении FOREIGN KEY задается внешний ключ таблицы и определяется столбец связи с другой таблицей. В нем указываются:

  •  необязательное имя связи для этого отношения после ключевого слова CONSTRAINT; оно не используется в инструкциях SQL, но может появляться в сообщениях об ошибках и потребуется, если будет необходимо удалить внешний ключ и разорвать связь между таблицами;
  •  столбец или столбцы создаваемой таблицы, которые уже определены и будут использоваться как внешний ключ для связи с другой таблицей-предком (определяемая таблица в данном отношении является потомком);
  •  необязательное правило удаления для данного отношения, которое определяет действие, предпринимаемое при удалении строки-предка;
  •  необязательное правило обновления для данного отношения, которое определяет действие, предпринимаемое при обновлении первичного ключа в строке-предке;
  •  если связываемые столбцы в таблице-предке и таблице-потомке имеют различные имена, то имя столбца в таблице-предке необходимо указать при описании внешнего ключа в скобках после имени таблицы-предка.

В стандартном SQL правил удаления, как и правил обновления всего четыре:  RESTRICT, CASCADE, SET NULL и SET DEFAUL. Однако правила SET NULL и SET DEFAULT некоторыми СУБД вообще не поддерживаются, а правило RESTRICT не нуждается в явном указании, так как в большинстве случаев принимается по умолчанию.  На синтаксической диаграмме для наглядности задания инструкции CREATE TABLE включено правило каскадного обновления (ON UPDATE CASCADE).

Непосредственно в Oracle существует 3 правила:

  •  CASCADE удаляет или обновляет родительскую запись и вместе с ней все дочерние.
  •  SET NULL устанавливает пустые значения для дочерних строк в случае удаления(но не обновления) родительской.
  •  NO ACTION (установлено по умолчанию) запрещает выполнение операций удаления или обновления родительской записи, если у нее есть дочерние.

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

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

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

Ограничение CHECK также задает дополнительные ограничения в инструкции CREATE TABLE. Оно содержит условие на значения (идентично условию отбора WHERE в запросе на выборку), проверяемое всякий раз при попытке модификации содержимого таблицы (с помощью инструкций INSERT, UPDATE или DELETE). Если после модификации условие остается истинным, такое изменение допускается; в противном случае СУБД отвергает изменения и выдает сообщение об ошибке.

Пример: Создать таблицу с ограничениями уникальности и проверки значений

create table salesreps (

sale_n integer not null,

name varchar2(15),

position varchar2(10),

sex char(1),

salary float(2),

age integer,

unique (name),

primary key (sale_n),

check (age<=35),

check (sex in ('m', 'f ')),

check (salary<=500.00)

);

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

create table orders (

order_n integer not null,

ord_date date not null,

cust_n integer,

sale_n integer,

prod_n varchar2(10),

qty integeR,

amount integer,

primary key (order_n),

constraint placed_by foreign key (cust_n) references customers,

constraint taken_by foreign key (sale_n) references salesreps,

constraint is_for foreign key (prod_n) references products);

Если первичный или внешний ключ включает в себя только один столбец, либо если условие уникальности или условие на значение касаются одного столбца, стандарт разрешает использовать упрощенную форму записи инструкции create table:

create table offices (

office_n integer not null primary key,

city varchar2(15) not null unique,

region_n varchar2(10) not null references regions);

19. Удаление таблицы (инструкция DROP TABLE)

С течением времени структура базы данных изменяется. Ненужные таблицы можно удалять посредством инструкции DROP TABLE. Стандартная синтаксическая диаграмма приведена на рисунке

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

В соответствии со стандартом инструкция DROP TABLE должна включать в себя либо параметр CASCADE, либо RESTRICT, которые определяют, как влияет удаление таблицы на другие объекты базы данных (например, представления), зависящие от этой таблицы. Если задан параметр CASCADE и в базе данных имеются объекты, которые содержат ссылку на удаляемую таблицу, то выполнение инструкции закончится неуспешно. В большинстве коммерческих СУБД допускается применение инструкции DROP TABLE без каких-либо параметров.

Синтаксическая диаграмма DROP TABLE в Oracle имеет следующий вид:

DROP TABLE [ schema.] table_name

 [CASCADE CONSTRAINTS] [PURGE];

Конструкция CASCADE CONSTRAINTS  позволит удалить таблицу, если на ее столбцы определены ссылки внешними ключами других таблиц (внешний ключ, ссылающийся на значения столбцов «собственной» таблицы, не препятствует ее удалению). При этом СУБД удалит сначала «мешающее» правило внешнего ключа, а столбцы другой, оставшейся таблицы в результате сохранят свои значения, но они уже не будут обременены ограничением ссылочной целостности. Фактически использование CASCADE CONSTRAINTS равносильно последовательному удалению всех правил внешнего ключа, ссылающихся на удаляемую таблицу (таковых может быть несколько), и выполнению простой команды DROP TABLE.

Конструкция PURGE появилась в 10 версии Oracle и изменила смысл команды DROP. Теперь после ее выполнения описание и данные таблицы продолжают храниться на своих местах, но под новыми, присвоенными системой автоматически именами. Для пользователя таблица удалена, однако все, что нужно для ее восстановления, если такая необходимость возникнет, продолжает храниться в БД. Тем самым для таблиц реализована техника «мусорной корзины» (recycle bin), хорошо известная по файловым системам.

Список содержимого мусорной корзины можно получить из системной таблицы USER_RECYCLEBIN (публичный синоним — RECYCLEBIN) командой:

SELECT object_name, original_name, droptime FROM user_recyclebin;

Восстановить таблицу по исходному имени (поле ORIGINAL_NAME из USER_RECYCLEBIN) можно, например, так:

FLASHBACK TABLE table_name TO BEFORE DROP;

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

Для удаления таблицы из мусорной корзины нужно использовать команду PURGE, например:

PURGE TABLE table_name;

Можно очистить корзину целиком:

PURGE RECYCLEBIN;

Чтобы таблица удалялась сразу без возможности восстановления, следует использовать конструкцию PURGE в команде DROP:

:

DROP TABLE table_name PURGE;

20. Изменение определения таблицы (инструкция ALTER TABLE)

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

 

Как видно из рисунка, инструкция ALTER TABLE может:

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

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

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

Добавление и удаление столбца. Чаще всего инструкция ALTER TABLE применяется для добавления столбца в существующую таблицу. Предложение с определением столбца в инструкции ALTER TABLE имеет точно такой же вид, как и в инструкции CREATE TABLE. Новое определение добавляется в конец определений столбцов таблицы, и в последующих запросах новый столбец будет крайним справа. СУБД обычно предполагает, что новый столбец во всех существующих строках содержит значения NULL. Если столбец объявлен как NOT NULL WITH DEFAULT, то СУБД считает, что он содержит значения по умолчанию. Нельзя объявлять столбец просто как NOT NULL, поскольку СУБД подставляла бы в существующие строки значения NULL, нарушая тем самым заданное условие. В действительности, когда вы добавляете новый столбец, СУБД не заносит во все существующие строки нового столбца значения NULL или значения по умолчанию. СУБД обнаруживает тот факт, что строка слишком коротка для нового определения таблицы, только при выборке этой строки пользователем, и расширяет ее значениями NULL или значениями по умолчанию непосредственно перед выводом на экран или передачей в программу пользователя.

С помощью инструкции ALTER TABLE можно удалить из существующей таблицы один или несколько столбцов, если в них больше нет необходимости. Например, следующая инструкция удаляет столбец position из таблицы staff.

ALTER TABLE staff 

DROP COLUMN position;

Стандарт требует, чтобы одна инструкция ALTER TABLE использовалась для удаления только одного столбца, но в ряде ведущих СУБД такое ограничение снято.

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

Описанные проблемы решаются так же, как и в случае инструкций DELETE и UPDATE – с помощью правила удаления. Можно выбрать одно из двух правил:

RESTRICT: если с удаляемым столбцом связан какой-либо объект в базе данных (внешний ключ, ограничение и т.п.), инструкция ALTER TABLE завершится выдачей сообщения об ошибке и столбец не будет удален;

CASCADE: любой объект базы данных (внешний ключ, ограничение и т.п.), связанный с удаляемым столбцом, также будет удален.

Правило CASCADE может вызвать целую «лавину» изменений, поэтому применять его следует с осторожностью. Лучше указывать правило RESTRICT, а связанные внешние ключи или ограничения обрабатывать с помощью дополнительных инструкций.

Изменение первичных и внешних ключей. Еще одним распространенным случаем применения инструкции ALTER TABLE является изменение или добавление определений первичных и внешних ключей таблицы.

Используя инструкцию ALTER TABLE, определения первичного и внешних ключей (в отличие от определений столбцов) можно как добавлять в таблицу, так и удалять из нее. Предложения, добавляющие определения первичного и внешнего ключей, являются точно такими же, как в инструкции CREATE TABLE, и выполняют те же функции.

Предложения, удаляющие первичный или внешний ключи, являются довольно простыми, однако удалить внешний ключ можно только тогда, когда создаваемая им связь имеет имя. Если имя присвоено не было, то задать эту связь в инструкции ALTER TABLE невозможно. В этом случае для удаления внешнего ключа необходимо удалить таблицу и воссоздать ее в новом виде.

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

alter table staff

add constraint live_in 

foreign key (adress) references adresses;

21. УСЛОВИЯ ЦЕЛОСТНОСТИ ДАННЫХ

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

  •  Обязательное наличие данных. По сути хранимой информации некоторые столбцы в базе данных должны содержать значения в каждой строке; строки в таких столбцах не могут содержать значения NULL. Например, в базе данных для каждого объекта недвижимости  должен существовать владелец, сдающий этот объект. Поэтому столбец ono в таблице property_for_rent  является обязательным и необходимо указать СУБД, что запись значения NULL в такой столбец недопустима.
  •  Условие на значение. Каждый столбец в базе данных определен на своем домене, т.е. имеет тот набор значений, которые допускается хранить в данном столбце. Можно указать СУБД, что запись значений, не входящих в определенный диапазон, в такие столбцы недопустима.
  •  Целостность таблицы (сущности). Первичный ключ таблицы должен в каждой строке иметь уникальное значение, отличное от значений во всех остальных строках. Можно указать СУБД, чтобы она обеспечивала целостность таблиц за счет использования первичных ключей.
  •  Ссылочная целостность. Каждая строка таблицы-потомка с помощью внешнего ключа связана со строкой таблицы-предка, содержащей первичный ключ, значение которого равно значению внешнего ключа. Значение столбца bno таблицы staff связывает служащего с офисом, в котором он работает. Столбец bno должен содержать значение из столбца bno таблицы branch; в противном случае служащий будет закреплен за несуществующим офисом. Можно указать СУБД, чтобы она обеспечивала ограничение на значения внешнего ключа.
  •  Бизнес правила. Обновление информации в базе данных может быть ограничено деловыми правилами, которым подчиняются сделки, представляемые подобными обновлениями. Например, организация может установить деловое правило, запрещающее сотруднику офиса работать одновременно более чем с пятью объектами. Можно указать СУБД, что следует проверять каждую новую строку, добавляемую в таблицу property_for_rent  , и убеждаться, что количество объектов, закрепленных за одним сотрудником, не превышает установленное деловое правило.
  •  Непротиворечивость. Многие реальные деловые операции вызывают в базе данных несколько изменений одновременно. Например, операция “увольнение сотрудника и передача всех закрепленных за ним объектов другим сотрудникам этого же отделения” может включать в себя удаление строки из таблицы staff с предварительным изменением содержимого соответствующих столбцов sno таблицы property_for_rent. Инструкции DELETE и UPDATE  должны быть выполнены вместе для того, чтобы база данных осталась в правильном, непротиворечивом состоянии. Можно указать СУБД, что следует обеспечивать непротиворечивость изменяемых данных.

Обязательное наличие данных. Это условие целостности данных требует, чтобы некоторые столбцы не содержали значений NULL. Стандарт ANSI/ISO и большинство коммерческих СУБД поддерживают выполнение подобного условия, позволяя пользователю при создании таблицы объявить, что некоторые столбцы не могут содержать значений NULL. Условие задается как часть инструкции CREATE TABLE в виде ограничения NOT NULL.

Если на столбец наложено ограничение NOT NULL, то для выполнения этого условия СУБД обеспечивает следующее:

  •  ни в одной инструкции INSERT, добавляющей в таблицу строку или строки, нельзя указывать значение NULL для этого столбца; попытка добавить строку, содержащую (явно или неявно) значение NULL для такого столбца, вызовет ошибку;
  •  ни в одной инструкции UPDATE, обновляющей столбец, нельзя присваивать столбцу значение NULL; попытка обновить такой столбец, присвоив ему, значение NULL, вызовет ошибку.

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

Ограничение на значения столбца аналогично условию отбора в предложении WHERE и возвращает значение TRUE или FALSE. Если для столбца задано ограничение, то при каждом добавлении новой строки или обновлении старой СУБД автоматически проверяет, выполняется ли ограничение для значения в этом столбце. Если оно не выполняется, то инструкция INSERT или UPDATE завершается ошибкой. Ограничение на значения столбца задается при определении столбца в инструкции CREATE TABLE.

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

Проблемы, связанные со ссылочной целостностью. Существует четыре типа изменений базы данных, которые могут нарушить ссылочную целостность отношений предок/потомок. Рассмотрим каждую из этих четырех ситуаций на примере таблиц branch и staff.

  •  Добавление новой строки-потомка. Когда происходит добавление новой строки в таблицу staff, значение ее внешнего ключа bno должно быть равно одному из значений первичного ключа bno в таблице-предке branch. Если значение внешнего ключа не равно ни одному из значений первичного ключа, то добавление такой строки разрушит целостность базы данных, поскольку появится потомок без предка (“сирота”). Добавление строки в таблицу branch не вызовет проблем; она просто станет предком без потомков.
  •  Обновление внешнего ключа в строке-потомке. Это та же проблема, что и в, предыдущей ситуации, но выраженная в иной форме. Если внешний ключ обновляется инструкцией UPDATE, то его новое значение должно быть равно одному из значений первичного ключа в таблице-предке. В противном случае обновленная строка окажется сиротой.
  •  Удаление строки-предка. Если из таблицы-предка branch будет удалена строка, у которой есть хотя бы один потомок, то строки-потомки станут сиротами. Значения внешних ключей в этих строках больше не будут равны ни одному из значений первичного ключа таблицы-предка. Удаление строки из таблицы-потомка не вызовет проблем. В этом случае предок этой строки после удаления будет иметь на одного потомка меньше.
  •  Обновление первичного ключа в строке-предке. Если происходит изменение первичного ключа некоторой строки в таблице-предке, все существующие потомки этой строки становятся сиротами, поскольку их внешние ключи больше не равны ни одному первичному ключу.

Средства поддержки ссылочной целостности стандарта ANSI/ISO, позволяют обрабатывать каждую из четырех описанных ситуаций. Первая проблема решается путем проверки значений в столбцах внешнего ключа перед выполнением инструкции INSERT. Если они не равны ни одному из значений первичного ключа, то выполнение инструкции INSERT влечет появление сообщения об ошибке.

Вторая проблема (обновление таблицы – потомка) решается аналогично: путем проверки нового значения внешнего ключа. Если нет ни одного равного ему значения первичного ключа, инструкция UPDATE отбрасывается с выдачей сообщения об ошибке.

Третья проблема является более сложной. Предположим, например, что вы закрыли отделение в Гродно и хотите удалить соответствующую строку из таблицы branch. Как в этом случае поступить со строками-потомками таблицы staff зависит от ситуации:

  •  не удалять из базы данных отделение до тех пор, пока служащие не будут переведены в другое отделение;
  •  автоматически удалить всех соответствующих служащих из таблицы staff;
  •  в столбце staff.bno установить значение NULL для всех соответствующих служащих, показывая тем самым, что идентификатор их офиса неизвестен;
  •  в столбце staff.bno для соответствующих служащих установить по умолчанию некоторое значение, например идентификатор главного отделения в Минске, указывая тем самым, что служащие автоматически переводятся в это отделение.

Аналогичные сложности существуют и в четвертой ситуации. Допустим, по каким-либо причинам требуется изменить идентификатор отделения в Витебске с 1 на 2. Подобно предыдущему примеру, возникает вопрос о том, как поступить со строками-потомками в таблице staff, представляющими служащих Витебского офиса. И снова проблему можно решить четырьмя способами:

  •  не изменять идентификатор отделения до тех пор, пока служащие не будут переведены в другое отделение; в таком случае в таблицу branch следует вначале добавить строку с новым идентификатором отделения в Витебске, затем обновить таблицу staff и, наконец, удалить строку со старым идентификатором Витебского отделения;
  •  автоматически обновить идентификатор отделения этих служащих в таблице staff для того, чтобы их строки были по-прежнему связаны с Витебской строкой в таблице branch через ее новый идентификатор;
  •  в столбце staff.bno установить для соответствующих служащих значение NULL, показывая тем самым, что идентификатор офиса неизвестен;
  •  в столбце staff.bno установить по умолчанию для соответствующих служащих некоторое значение, например идентификатор главного офиса в Минске.

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

  •  RESTRICT (NO ACTION) – запрещает удаление строки из таблицы – предка, если строка имеет потомков. Следует также иметь в виду, что данное правило зачастую воспринимается СУБД по умолчанию и не требует явного задания.
  •  CASCADE – определяет, что при удалении строки-предка все строки-потомки также автоматически удаляются из таблицы-потомка. Данное правило опасно в употреблении, в случае если существуют многоуровневые связи между таблицами.
  •  SET NULL – определяет, что при удалении строки-предка внешним ключам во всех ее строках-потомках автоматически присваивается значение NULL.
  •  SET DEFAULT – определяет, что при удалении строки-предка внешним ключам, всем ее строкам-потомкам присваивается определенное значение, по умолчанию установленное для данного столбца.

Аналогично тому, как правила удаления определяют действия СУБД при попытке удалить строку из таблицы – предка, так правила обновления определяют действия СУБД, когда пользователь пытается обновить значение первичного ключа в таблице-предке. Регламентируются четыре правила обновления, аналогичные правилам удаления.

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

В заключение следует отметить, что некоторые СУБД, в частности ORACLE не поддерживают правил SET NULL и SET DEFAULT , а правило CASCADE требует явного задания и поддерживается только для удалений. Правила каскадного обновления в системе ORACLE недействительны, поддерживается только запрет на изменение строк-предков.

Правило RESTRICT является “одноуровневым” – в отношении предок/потомок оно затрагивает только таблицу-предок. Правило CASCADE, напротив, “многоуровневое”, его следует применять с осторожностью, поскольку некорректное его использование может вызвать широкомасштабное автоматическое удаление данных. Правила каскадного обновления могут привести к подобным многоуровневым обновлениям, если внешний ключ в таблице-потомке одновременно является и ее первичным ключом. На практике такая ситуация встречается не часто, поэтому каскадное обновление обычно не имеет таких далеко идущих последствий, как каскадное удаление.

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

Рисунок  Ссылочный цикл

На представленном рисунке таблица staff содержит столбец bno– внешний ключ для таблицы branch. Таблица branch, в свою очередь может содержать столбец mgr (с информацией об управляющих отделений) – внешний ключ для таблицы staff. Как видно из рисунка, эти два отношения образуют ссылочный цикл. Любая строка таблицы staff имеет ссылку на строку таблицы branch, которая имеет ссылку на строку таблицы staff, и т.д.

Независимо от количества таблиц, принимающих в них участие, ссылочные циклы представляют особую проблему ссылочной целостности. Предположим, например, что в двух таблицах, изображенных на рисунке, для первичных и внешних ключей не допускаются значения NULL. Рассмотрим, например, следующий запрос на добавление и соответствующие ему инструкции INSERT:

Поместить в базу данных информацию о новом сотруднике Иване Иванове с назначением его управляющим в только что открывшемся отделении в Москве:

insert into staff (sno, fname, lname, salary, bno)

values (23, ‘Иван’, ‘Иванов’, 500, 3);

insert into branch (bno, street, area, city, mgr, tel_no)

values (3, ‘…’, ‘…’, ‘Москва’, 23, ‘…’);

Выполнение первой инструкции будет безрезультатным, потому что в новой строке есть ссылка на идентификатор отделения, которого в базе данных еще нет. Очевидно, что изменение порядка инструкции INSERT ни к чему не приведет, так как при выполнении инструкции для branch встречается ссылка на идентификатор служащего (руководитель офиса), который еще отсутствует в таблице staff.

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

insert into staff (sno, fname, lname, salary, bno)

values (23, ‘Иван’, ‘Иванов’, 500, null);

insert into branch (bno, street, area, city, mgr, tel_no)

values (3, ‘…’, ‘…’, ‘Москва’, 23, ‘…’);

update staff 

set bno=3

where sno=23;

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

Если ограничения препятствуют вводу данных и их нужно временно отменить можно воспользоваться оператором:

ALTER TABLE table_name DISABLE CONSTRAINT имя_ограничения [CASCADE];

а затем можно снова включить его оператором:

ALTER TABLE table_name ENABLE CONSTRAINT имя_ограничения;

22. СОЗДАНИЕ ПРЕДСТАВЛЕНИЙ

Представлением называется именованный запрос на выборку, сохраненный  в базе данных. Иногда представления называют “виртуальными таблицами”, содержимое которых определяется запросом. Для пользователя базы данных представление выглядит подобно обычной таблице, состоящей из строк и столбцов. Однако, в отличие от таблицы, представление как совокупность значений в базе данных реально не существует. Строки и столбцы данных, которые пользователь видит с помощью представления, являются результатами запроса, лежащего в его основе. При создании представление получает имя, и его определение сохраняется в базе данных.

Представления используются по нескольким причинам:

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

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

CREATE OR REPLACE VIEW view_name [(view_col [, view_col ...])]

AS SELECT [WITH CHECK OPTION];

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

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

Если определение представления простое, то СУБД формирует каждую строку представления "на лету", извлекая данные из исходных таблиц. Если же определение сложное, СУБД приходится материализовывать представление. Это означает, что СУБД выполняет запрос, определяющий представление, и сохраняет его результаты во временной таблице. Из нее СУБД берет данные для формирования результатов пользовательского запроса, а когда временная таблица становится ненужной, удаляет ее. Но независимо от того, как СУБД выполняет инструкцию, являющуюся определением представления, для пользователя результат будет одним и тем же. Ссылаться: на представление в инструкции SQL можно так же, как если бы оно было реальной таблицей базы данных. Так после определения представления к нему можно обращаться с помощью инструкции SELECT как к обычной таблице:

select * from view_name;

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

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

  •  Безопасность. Каждому пользователю можно разрешить доступ к небольшому числу представлений, содержащих только ту информацию, которую ему позволено знать (примеры горизонтальных и вертикальных представлений). Таким образом,  можно осуществить ограничение доступа пользователей к хранимой информации.
  •  Простота запросов. С помощью представления можно извлечь данные из нескольких таблиц и представить их как одну таблицу, превращая тем самым запрос ко многим таблицам в однотабличный запрос к представлению (такие представления также называют объединенными).
  •  Простота структуры. С помощью представлений для каждого пользователя можно создать собственную структуру базы данных, определив ее как множество доступных пользователю виртуальных таблиц.
  •  Защита от изменений. Представление может возвращать непротиворечивый и неизменный образ структуры базы данных, даже если исходные таблицы разделяются, реструктуризируются или переименовываются.
  •  Целостность данных. Если доступ к данным или ввод данных (возможно не для всех представлений) осуществляется с помощью представления, СУБД может автоматически проверять, выполняются ли определенные условия целостности.

Недостатки представлений. Наряду с перечисленными выше преимуществами, представления обладают и двумя существенными недостатками:

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

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

Приведем примеры инструкций, создающих различные виды представлений:

Пример. Создать представление, показывающее информацию о служащих, работающих в отделении компании города Минска.

create OR REPLACE view Minsk AS

select * from staff 

where bno in (select bno 

from branch 

where city='Minsk');

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

Пример. Создать представление, показывающее информацию о ФИО и должности служащих.

create OR REPLACE view info AS

select fname, lname, position

from staff;

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

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

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

Пример. Создать представление с информацией о средней заработной плате сотрудников по каждому отделению.

create OR REPLACE view average_salary as

select bno, avg(salary) Srednya_zarplata

from staff

group by bno;

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

Согласно стандарту, представление можно обновлять в том случае, если определяющий его запрос соответствует перечисленным ниже требованиям:

Обязательно должен присутствовать ключ и все столбцы, которые определены как NOT NULL

  1.  Должен отсутствовать предикат DISTINCT, т.е. повторяющиеся строки не должны исключаться из результата запроса.
  2.   В предложении FROM должна быть задана только одна таблица или представление.
  3.  Каждое имя в списке возвращаемых столбцов должно быть ссылкой на простой столбец, не должны содержаться выражения, вычисляемые столбцы и статистические функции, но обязательно должен присутствовать ключевой столбец и столбцы, заданные с ограничением NOT NULL при создании таблицы.
  4.  В предложении WHERE не должен содержаться подчиненный запрос.
  5.  В запросе не должны присутствовать предложения GROUP BY и HAVING.

Обобщая сказанное можно отметить, что если между строками представления и строками исходной таблицы есть соответствие “один к одному”, то такое представление можно считать обновляемым. Если между строками представления и исходной таблицы нет однозначного соответствия, то добавление, удаление и изменение строк представления не имеет смысла и поэтому запрещены. Однако некоторые коммерческие СУБД позволяют обновлять “неоднозначные” представления. Так в ORACLE такие обновления возможны посредством триггеров INSTEAD OF .

В ORACLE инструкция имеет вид:

CREATE OR REPLACE [FORCE|NOFORCE] VIEW view-name

AS sql-запрос [WITH CHECK OPTION [CONSTRAINT имя_ограничения]] [WITH READ ONLY];

где OR REPLACE  – пересоздает представление, если оно уже существует. Можно использовать эту опцию для изменения определения представления без того, чтобы удалять его, создавать заново и вновь назначать все объектные привилегии, которые были назначены по данному представлению;

FORCE – создает представление независимо от того, существуют ли базовые таблицы этого представления, и от того, имеет ли владелец схемы, содержащей представление, привилегии по этим таблицам. Необходимо чтобы оба названных условия были удовлетворены, прежде чем по данному представлению можно будет выдавать любые предложения SELECTINSERTUPDATE или DELETE. По умолчанию применяется параметр NOFORCE;

NOFORCE – создает представление только в том случае, если существуют базовые таблицы этого представления, а владелец схемы, содержащей представление, имеет привилегии по этим таблицам;

WITH CHECK OPTION – указывает, что вставки и обновления, которые будут осуществляться через этот запрос, должны давать в результате только такие строки, которые могут быть выбраны запросом этого же представления. Другими словами, если представление создается посредством запроса с предложением WHERE, то в представлении будут видны только строки, удовлетворяющие условию отбора. Остальные строки в исходной таблице присутствуют, но в представлении их нет.

Например, представление Minsk содержит только строки таблицы staff с определенными значениями в столбце bno.

Это представление является обновляемым как по стандарту ANSI/ISO, так и в ORACLE, следовательно, в него можно добавить информацию о новом служащем посредством инструкции INSERT:

insert into Minsk (sno, fname, lname, address, position, sex, dob, salary, bno)

values ( ‘s129’, ‘…’, ‘…’, ‘…’, ‘менеджер’, ‘f’, ’01.01.81’, 300, 1).

СУБД добавит новую строку в исходную таблицу staff; она будет видна также в представлении Minsk. Также без опции WITH CHECK OPTION ничего не препятствует выполнению следующей инструкции:

insert into Minsk (sno, fname, lname, address, position, sex, dob, salary, bno)

values ( ‘s129’, ‘…’, ‘…’, ‘…’, ‘менеджер’, ‘f’, ’01.01.81’, 300, 2).

После этого в запросе select * from Minsk добавленная строка будет отсутствовать. Тот факт, что в результате выполнения инструкции INSERT или UPDATE из представления исчезают строки, в лучшем случае вызывает замешательство.

При указании параметра WITH CHECK OPTION  (пример ) пользователь не сможет вводить, удалять и обновлять информацию таблицы, из которой он не имеет возможности считать информацию через простое представление (создаваемое из данных одной таблицы). Обновляемое представление, использующее несколько связанных таблиц, нельзя создавать с данным параметром, так как опция CHECK OPTION не может гарантировать контроль, если существует подзапрос в запросе этого представления или любого представления, на котором базируется  данное представление.

CONSTRAINT имя_ограничение – задает имя, которое присваивается ограничению CHECK OPTION. Если этот идентификатор опущен, то ORACLE автоматически назначает этому ограничению уникальное имя.

create OR REPLACE view Minsk as

select * from staff 

where bno in (select bno 

from branch 

where city='Minsk')

with check option;

Когда для представления установлен режим контроля, СУБД автоматически проверяет каждую операцию INSERT или UPDATE, выполняемую над представлением, чтобы удостовериться в том, что полученные в результате строки удовлетворяют условиям отбора в определении представления. Если добавляемая или обновляемая строка не удовлетворяет этим условиям, то выполнение инструкции INSERT или UPDATE завершается ошибкой; другими словами, операция не выполняется.

WITH READ ONLY – запрещает операции DML над  представлением. Если команды обновления DML (INSERT, UPDATE, DELETE) можно применить к представлению, то говорят, что представление является обновляемым (updatable); в противном случае оно является только читаемым (read-only).

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

  •  представление должно включать первичный ключ таблицы;
  •  не должно содержать полей, полученных в результате применения функций агрегирования;
  •  не должно содержать DISTINCT, GROUP BY, HAVING в своем определении;
  •  может быть определено на другом представлении, но это представление должно быть обновляемым;
  •  не может содержать константы, строки или выражения (например, comm*100) в списке выбираемых выходных полей;

Удаляются представления посредством инструкции DROP VIEW. Так представление Minsk может быть удалено при выполнении инструкции:

drop view Minsk;

23. ПСЕВДОНИМЫ ТАБЛИЦ (ИНСТРУКЦИИ CREATE / DROP SYNONYM)

Промышленные базы данных часто бывают организованы так, что все основные таблицы собраны вместе и принадлежат администратору. Администратор базы данных дает другим пользователям разрешения на доступ к таблицам, руководствуясь правилами обеспечения безопасности. Поэтому и только в случае получения разрешения на доступ к таблицам другого пользователя, для ссылки на них необходимо использовать полные имена таблиц. На практике это означает, что в каждом запросе к таким таблицам следует указывать полные имена таблиц, в результате чего запросы становятся длинными, а их ввод – утомительным:

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

В ORACLE для создания псевдонимов используется инструкция CREATE SYNONYM. В других СУБД для аналогичных целей используется инструкция CREATE ALIAS.

CREATE [PUBLIC] SYNONYM имя_синонима 

FOR [схема.] имя_таблицы[@связь_БД]

После создания псевдонима его можно использовать в запросах SQL как обычное имя таблицы. Применение псевдонимов смысл запроса не изменяет, так как и в этом случае необходимо иметь разрешение на доступ к таблицам других пользователей. Тем не менее, псевдонимы упрощают инструкции SQL, и последние приобретают такой вид, как если бы вы обращались к своим собственным таблицам. Если позднее вы решите, что больше не нуждаетесь в псевдонимах, то можете их удалить посредством инструкции DROP SYNONYM.

24. ИНДЕКСЫ (ИНСТРУКЦИИ CREATE/DROP INDEX)

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

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

Например, в случае поиска сотрудника по фамилии будем использовать столбец lname. Если бы индекса для столбца не существовало, то СУБД была бы вынуждена выполнять запрос путем последовательного “сканирования” таблицы staff, строка за строкой, просматривая в каждой строке значения столбца  lname. Для получения гарантии того, что найдены все строки, удовлетворяющие условию отбора, СУБД должна просмотреть каждую строку таблицы. Если таблица имеет сотни тысячи строк, то ее просмотр может занять достаточно много времени.

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

Как видно из примера, индекс имеет то преимущество, что ускоряет выполнение инструкций с условиями отбора, имеющими ссылки на индексный столбец (столбцы). К недостаткам индекса относится то, что, во-первых, он занимает на диске дополнительную память и, во-вторых, индекс необходимо обновлять каждый раз, когда в таблицу добавляется строка или обновляется индексный столбец таблицы. Это требует дополнительных затрат на выполнение инструкций INSERT и UPDATE, которые обращаются к данной таблице.

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

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

На практике в большинстве популярных СУБД (включая ORACLE, SQServer, INFORMIX, SYBASE) для создания индекса используется та или иная форма инструкции CREATE INDEX. В инструкции указывается имя индекса и таблица, для которой он создается, индексируемый столбец и порядок его сортировки (по возрастанию или убыванию).

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

CREATE [OR REPLACE] [UNIQUE | BITMAP] INDEX [схема.] имя_индекса 

ON [схема.] имя_таблицы [псевдоним] (столбец | выражение_для_столбца [ASC | DESC][, …]);

где UNIQUE – означает, что значения столбцов, на которые ссылается индекс, должны быть уникальными; BITMAP – изменение структуры индекса со сбалансированного дерева на структуру растровой карты.

В следующем примере создадим индекс с именем lname_IDX, для столбца  таблицы staff:

CREATE INDEX lname_IDX ON staff (lname)

   TABLESPACE ALL_ORACLE_TBS

STORAGE (INITIAL 20K

         NEXT 20K

         PCTINCREASE 75

         PCTFREE 0);

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

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

Для создания уникального индекса используется оператор – CREATE UNIQUE INDEX. В следующем примере покажем как это сделать:

CREATE UNIQUE INDEX ANIKNAME_UNIQUE_IDX ON ALL_ORACLE_ADMIN (ANIKNAME)

   TABLESPACE ALL_ORACLE_IDX;

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

Создание индексов связанных с ограничением целостности

Oracle обеспечивает выполнение ограничения целостности UNIQUE или PRIMARY KEY для таблицы, создавая уникальный индекс для уникального или первичного ключа. Этот индекс создается автоматически, когда включается ограничение целостности. Когда выполняется CREATE TABLE или ALTER TABLE, для создания индекса не надо предпринимать никаких действий, но при желании можно указать предложение USING INDEX, чтобы контролировать его создание.

Чтобы включить ограничение целостности UNIQUE или PRIMARY KEY, создавая, таким образом, связанный с ним индекс, владелец таблицы должен иметь квоту табличного пространства, где будет храниться этот индекс, или системную привилегию UNLIMITED TABLESPACE. Индекс, связанный с ограничением целостности всегда получает имя этого ограничения, если вы не укажете иное.

Параметры хранения для индексов связанных с ограничением целостности UNIQUE или PRIMARY KEY, можно устанавливать с помощью предложения USING INDEX.

http://www.all-oracle.ru/content/view/?part=1&id=102

25. Последовательности (инструкция CREATE/DROP SEQUENCE)

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

Без генератора последовательностей порядковые номера можно создавать лишь программным способом. Новое значение первичного ключа можно было бы получать выбором последнего программно вычисленного значения и наращиванием его. Этот метод требует блокировки во время выполнения транзакции и заставляет одновременно работающих пользователей ожидать очередного значения первичного ключа; такое ожидание известно как СЕРИАЛИЗАЦИЯ (буквально – "выстраивание в очередь"). Если  есть в пользовательских приложениях такие программные конструкции, то их нужно заменять на обращения к последовательностям. Последовательности устраняют сериализацию и улучшают конкурентные способности приложений.

Рассмотрим, как создавать, изменять и удалять последовательности с помощью команд SQL.

Общий синтаксис создания:

CREATE SEQUENCE [schema].sequence_name

[INCREMENT BY increment_num]

[START WITH start_num]

[MAXVALUE maximum_num | NOMAXVALUE]

[MINVALUE minimum_num | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE cache_num | NOCACHE]

[ORDER | NOORDER];

где,

sequence_name – имя последовательности;

increment_num – шаг последовательности, по умолчанию, это 1. Абсолютное значение этого параметра должно быть меньше, чем разница между конечным и начальным значениями;

start_num – целочисленное значение с которого начинается отсчет, по умолчанию это 1;

maximum_num – максимальное значение последовательности; значение maximum_num должно быть больше или равно значению start_num, и больше, чем значение minimum_num;

NOMAXVALUE – устанавливает максимальное значение равным 1027 для возрастающей последовательности, или –1 для убывающей. NOMAXVALUE используется по умолчанию;

minimum_num - минимальное значение последовательности; minimum_num должно быть меньше либо равно start_num, и меньше, чем maximum_num;

NOMINVALUE – определяет минимальное значение равное 1 для возрастающей последовательности и -1026 для убывающей. NOMINVALUE используется по умолчанию;

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

NOCYCLE – указывает прекратить генерацию значений при достижении максимума или минимума последовательности. NOCYCLE используется по умолчанию.

cache_num – количество значений сохраняемых в памяти. По умолчанию это 20. Минимальное количество кэшированных значений – 2, максимальное значение высчитывается по формуле:

CEIL(maximum_num -minimum_num)/ABS(increment_num);

NOCACHE – отключает кэширование. Это не позволит базе данных выделить некоторое количество значений заблаговременно, что позволит избежать пробелов в последовательности, но увеличит затраты системных ресурсов. Пробелы могут возникнуть при остановке базы данных, когда кэшированные значения теряются. Если опции CACHE и NOCACHE не указываются, то по умолчанию кэшируется 20 значений.

ORDER – обеспечивает генерацию значений в порядке запросов. Как правило, ORDER используется в среде Real Application Clusters. NOORDER – не дает таких гарантий. По умолчанию используется NOORDER.

Например, следующая команда создает последовательность, которую можно использовать для генерации номеров сотрудников для столбца sno таблицы staff:

CREATE SEQUENCE sno_sequence

   INCREMENT BY 1

   START WITH 1

   MAXVALUE 99999

   MINVALUE 1

   NOCYCLE

  CACHE 10;

Чтобы изменить последовательность (за исключением начального номера) используется команда SQL ALTER SEQUENCE. Например, следующее предложение изменяет последовательность:

ALTER SEQUENCE sno_sequence

   INCREMENT BY 10

   MAXVALUE 10000

   CYCLE

   CACHE 20;

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

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

Обращение к последовательности осуществляется в предложениях SQL через псевдостолбцы NEXTVAL и CURRVAL; каждый новый номер данной последовательности генерируется обращением к ее псевдостолбцу NEXTVAL, тогда как текущий номер последовательности можно извлекать неоднократно путем обращения к ее псевдостолбцу CURRVAL.

NEXTVAL и CURRVAL не являются зарезервированными или ключевыми словами; их можно использовать как имена псевдостолбцов в предложениях SQL, таких как SELECT, INSERT или UPDATE.

Чтобы сгенерировать и возвратить очередной номер данной последовательности, обратитесь к seq_name.NEXTVAL, где seq_name – имя последовательности.

Самым простым примером может быт