Презентация "Основы СУБД ORACLE"

Основы СУБД ORACLE Лекция №2 Основные объекты ORACLE. Средства манипулирования данными языка SQL. Структура запроса. Простейшие запросы. Формирование критерия отбора. Сортировка Объекты модели хранения данных ORACLE

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

Логические структуры

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

Взаимосвязь между логическими структурами Каталоги Объект каталог (directory) является логической ссылкой в базе данных на каталог файловой системы сервера, где установлена БД ORACLE. Владельцем всех объектов directory в базе данных является пользователь SYS, даже если объект directory создан другим пользователем. Имена объектов directory уникальны внутри всей БД. Все объекты Directory хранятся в табличном пространстве SYS. Пользователи

• В базе данных учетная запись пользователя не является физической структурой, но она связана важными взаимоотношениями с объектами базы данных: пользователям принадлежат объекты. Пользователь SYS владеет таблицами словаря данных, содержащими информацию об остальных структурах базы данных. Пользователю SYSTEM принадлежат представления, обращающиеся к этим таблицам словаря данных, что позволяет остальным пользователям базы данных использовать их.
• Объекты в базе данных создаются с учетными записями пользователей. Для каждой учетной записи можно задать конкретное табличное пространство в качестве табличного пространства по умолчанию.

Схемы Набор объектов, принадлежащих учетной записи пользователя, называется схемой. Можно создать пользователей, не имеющих права входа в базу данных. Такие учетные записи предлагают схему, которую можно использовать для хранения наборов объектов базы данных отдельно от схем других пользователей. В ORACLE схема привязывается только к одному пользователю (USER) и является логическим набором объектов базы данных. Схема создается при создании пользователем первого объекта, и все последующие объекты, созданные этим пользователем, становятся частью этой схемы. Схема может включать другие объекты, принадлежащие этому пользователю. Роли Для сокращения объема информации по управлению доступом и для обеспечения более гибких возможностей управления обе СУБД применяют группирование привилегий – возможность одним действием администратора предоставить разным пользователям одинаковый набор привилегий. Однако представления концепций группирования различны в наших двух СУБД. ORACLE использует для этих целей роли. Роль – это объект базы данных, представляющий собой именованный набор привилегий, который может предоставляться пользователю или другой роли. Профили Профили имеют двойную функцию, это реализация парольной политики и распределение ресурсов. Парольная политика исполняется всегда, контроль за использованием ресурсов осуществляется, если значение параметра RESOURCE_LIMIT равно TRUE, по умолчанию оно равно FALSE. Профили используются автоматически, но профиль, назначаемый всем пользователям по умолчанию, а именно пользователям SYS, SYSTEM и др., – DEFAULT очень простой. Таблицы Таблицы представляют собой механизм сохранения информации в базе данных ORACLE. Они содержат фиксированный набор столбцов, в которых описываются атрибуты объекта, с которым эта таблица работает. У каждого столбца есть имя и уникальные характеристики. Временная таблица является механизмом хранения данных в базе данных ORACLE. Временная таблица состоит из столбцов, имеющих типы данных и длину. В отличие от регулярной таблицы описание временной таблицы сохраняется, но данные, внесенные в таблицу, остаются в ней во время сеанса или во время транзакции. Кластеры Таблицы, с которыми часто работают совместно, можно физически хранить совместно. Для этого создается кластер, который будет их содержать. Данные таких таблиц сохраняются вместе в кластере, что уменьшает число операций ввода/вывода и повышает производительность. Связанные столбцы таблиц называются кластерным ключом. Кластерный ключ индексируется с помощью кластерного индекса, причем его значение сохраняется только один раз для нескольких таблиц кластера. Кластерный индекс необходимо создать до введения (insert) новых строк в таблицы кластера. Ограничения На столбцы таблицы можно налагать ограничения; при этом каждая ее строка должна удовлетворять указанному в описании ограничению.

• Ограничение NOT NULL проверяет чтобы каждая строка таблицы содержала значение для данного столбца.
• Ограничение DEFAULT генерирует значение столбца при включении (insert) строки в таблицу, но для него не указывается никакого значения.
• Ограничение CHECK позволяет удостовериться, что значения в указанном столбце соответствуют определенному критерию.

Ограничения

• Ограничение UNIQUE гарантирует уникальность столбца, который должен быть уникальным, но не является частью первичного ключа.
• Ограничение первичного ключа PRIMARY KEY гарантирует что каждая строка таблицы должна содержать значение уникальное не пустое для данного столбца.
• Ограничение внешнего ключа FOREIGN KEY определяет природу взаимоотношений между таблицами. Внешний ключ одной таблицы ссылается на первичный ключ, который был ранее определен где-то в другом месте базы данных.

Последовательности Определение последовательностей (sequences) содержится в словаре данных. Последовательности позволяют упростить процесс программирования, поскольку предоставляют последовательный список уникальных номеров. При первом обращении к последовательности в запросе она возвращает предопределенное значение. Каждый следующий запрос возвращает значение, которое больше предыдущего на указанное приращение. Последовательности могут быть циклическими, а могут увеличиваться до достижения заданного максимального значения. Индексы Индекс - это структура базы данных, используемая сервером для быстрого поиска строки в таблице. Существуют три типа индексов: кластерные, табличные и индексы битовой карты, или битовые индексы. Кластерные индексы содержат значения ключей кластеров в кластерах. Табличный индекс содержит значения строк таблиц вместе с физическим расположением строки (RowlD). Битовый индекс является особым типом табличного индекса, предназначенным для поддержки запросов больших таблиц со столбцами, содержащими несколько отдельных значений. ROWID Для того чтобы ORACLE мог найти данные, каждая строка в каждой таблице помечается с помощью идентификатора RowID. Этот идентификатор содержит информацию о том, где конкретно расположена строка (файл, блок внутри этого файла и строка внутри этого блока). ВНИМАНИЕ: Таблица, организованная по индексу, не содержит традиционных для ORACLE идентификаторов RowlD. Вместо этого в качестве логических идентификаторов используется первичный ключ. Снимок

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

Представление На самом деле представление – это таблица, содержащая столбцы, и обращение к нему осуществляется точно так же, как и к таблице. Однако оно не содержит данных. Концептуально представление можно считать маской, перекрывающей одну или несколько таблиц, так как столбцы представления содержатся в одной или нескольких таблицах. Но физически представления не содержат данных. Определение представления (включающее запрос, на котором оно основано, расположение его столбцов и назначенные привилегии) содержится в словаре данных. Хранимые процедуры и функции Процедура – это блок операторов PL/SQL, сохраняемый в словаре данных и вызываемый приложениями. Процедуры позволяют сохранять в базе данных часто используемую логику приложений. При выполнении процедуры все ее операторы выполняются как единое целое. Процедуры не возвращают никаких значений вызвавшей их программе. Функции, как и процедуры, представляют собой блоки кода, сохраняемые в базе данных. Однако в отличие от процедур функции могут возвращать значения вызвавшей их программе. Можно создавать свои собственные функции и обращаться к ним в операторах SQL, а можно использовать только те функции, которые предоставляются средой ORACLE. Пакеты С помощью пакетов можно упорядочить процедуры и функции и объединять их в логические группы. Спецификации и тела пакетов сохраняются в словаре данных. Пакеты бывают очень полезны при решении задач администрирования по управлению процедурами и функциями. Триггеры Триггеры – это процедуры, выполняемые при наступлении указанного события базы данных. С помощью триггеров можно укрепить ссылочную целостность, обеспечить дополнительную безопасность или повысить доступные возможности аудита. Существуют два типа триггеров:

• Операторные триггеры. Срабатывают один раз для каждого активизирующего оператора.
• Строковые триггеры. Срабатывают один раз для каждой строки таблицы, на которую влияют данные операторы.
Для любого типа триггера можно создать триггеры BEFORE (до) и AFTER (после), относящиеся к каждому типу активизирующих событий. К числу таких событий относятся команды insert, update и delete

Средства манипулирования данными языка SQL ORACLE поддерживает 4 стандартных оператора манипулирования данными:

• INSERT – используется для ввода данных;
• SELECT – используется для выборки данных;
• UPDATE – используется для обновления данных;
• DELETE – используется для удаления данных.

Ввод данных Оператор INSERT используется для добавления строк в таблицу. Вы можете указать следующую информацию при использовании оператора INSERT:

• Таблица, в которую необходимо добавить строку.
• Список столбцов, для которых будут заданы значения.
• Список значений, которые будут храниться в указанных столбцах.
Во время добавления строки необходимо указать значения для первичного ключа и всех других столбцов, которые определены как NOT NULL. Нет необходимости указывать значения для остальных столбцов; им автоматически будет присвоено значение NULL.

Простые манипуляции по выборке данных из одной таблицы. Оператор SELECT используется для выборки данных из таблиц базы данных. В самом простом примере вы указываете таблицу и столбцы, которые вам необходимо выбрать из базы данных Сразу за ключевым словом SELECT указываются имена столбцов, которые вам необходимо получить, а после ключевого слова FROM указывается имя таблицы. Оператор языка SQL заканчивается точкой с запятой (;). Операторы SELECT часто называют запросами. SELECT <список столбцов> FROM <список таблиц>; Использование WHERE Если необходимо вывести только определенные строки, необходимо добавить к оператору SELECT ключевое слово WHERE. Это очень важно, поскольку ORACLE вмещает огромное количество строк в таблице, можно получить небольшое подмножество этих строк. Для этого необходимо после ключевого слова FROM и имени таблицы поместить ключевое слово WHERE и указать условие отбора: SELECT <список столбцов> FROM <список таблиц> WHERE <условие отбора>; Операторы сравнения применяемые с WHERE

Оператор	Описание
=	Равно
<> или !=	Не равно
<	Меньше
>	Больше
<=	Меньше или равно
>=	Больше или равно
ANY	Сравнивает значение с любыми значениями из списка
SOME	Идентично оператору ANY; используется реже, чем ANY
ALL	Сравнивает значение со всеми значениями в списке.

Использование SQL операторов при формировании запросов

Оператор	Описание
LIKE	Проверяет соответствие строки заданному шаблону
IN	Проверяет значение на присутствие в списке
BETWEEN	Проверяет значение на вхождение в диапазон
IS NULL	Проверяет, является ли значение пустым
IS NAN	Проверяет, является ли значение не числовым значением
IS INFINITE	Проверяет, является ли значение бесконечным BINARY_FLOAT или BINARY_DOUBLE

Сортировка данных Для сортировки выбираемых строк используется ключевое слово ORDER BY. При использовании ORDER BY можно указать один и более столбцов по которым необходимо отсортировать полученные строки. Выражение ORDER BY должно следовать за выражением FROM или WHERE (если с помощью WHERE указывается условие отбора). Следующий пример использует ORDER BY для сортировки строк из таблицы CUSTOMERS по столбцу LAST_NAME: SELECT * FROM customers ORDER BY last_name; Обновление данных Для изменения данных в таблице используется оператор UPDATE. При использовании оператора UPDATE обычно указывается следующая информация:

• Имя таблицы
• Выражение WHERE, определяющее какие строки будут изменены.
• Список столбцов и их значений, определенных с помощью ключевого слова SET.
С помощью одного и того же запроса UPDATE можно изменить одну и несколько строк. При изменении нескольких строк нужно помнить, что новое значение будет применено во всех строках. Например, следующий запрос UPDATE устанавливает для столбца last_name значение Orange в строке, чей customer_id равен 2. UPDATE customers SET last_name = 'Orange' WHERE customer_id = 2;

Удаление данных Для удаления строк используется оператор DELETE. Обычно необходимо указать с помощью выражения WHERE строки, которые необходимо удалить; в противном случае будут удалены все строки. Следующий запрос DELETE удаляет из таблицы покупателей строку, в которой customer_id равен 10: DELETE FROM customers WHERE customer_id = 10; 1 row deleted. SQL*Plus подтверждает, что одна строка была удалена. Также можно использовать подзапрос с оператором DELETE. Подзапросы будут рассматриваться в 4-й лекции. Завершение работы с SQL*Plus При использовании операторов INSERT, UPDATE и DELETE необходимо фиксировать транзакции с помощью оператора COMMIT, т.к. до момента выхода или завершении сессии измененные значения хранятся только во временном пространстве, а не в постоянной базе данных. Если после подтверждения изменений необходимо вернуть данные в исходное состояние можно выполнить оператор ROLLBACK.