SQL Создание объектов План Виды объектов

SQL. Создание объектов.

План • Виды объектов • Создание таблиц • Определение домена • Создание представлений • Уничтожение объектов • Создание индексов • Изменение в определении существующей таблицы • ERWin. Генерация схемы

Виды объектов • Table – таблица • View – представление • Schema – схема • Domain – домен • Assertion – утверждение (проверка ограничений) • Character set – набор символов • Collation – последовательность сортировки • Translation – преобразование одного набора символов в другой.

Объекты создаются командой CREATE и удаляются командой DROP

Создание таблиц

CREATE TABLE CREATE [{GLOBAL|LOCAL}|TEMPORARY] TABLE имя таблицы ({определение столбца|[ограничение таблицы]}. , . . [ON COMMIT {DELETE|PRESERVE} ROWS]);

• TEMPORARY – временная –разрушаются в • конце каждого сеанса SQL – GLOBAL – доступны для разных модулей в пределах сеанса – LOCAL ON COMMIT – может использоваться только для временных таблиц. Определяет действия, выполняемые при завершении транзакции.

Определение столбца определение столбца : : = имя столбца|{имя домена |тип данных [размер]} [ограничение столбца…] [DEFAULT значение по умолчанию] [COLLATE имя сравнения]

В стандарте SQL-92 было определено семь типов данных. Однако большинство из них имеют «подтипы» с собственными именами, используемыми для задания типа данных при определении таблицы. В SQL-99 введено четыре новых типа данных, причем два из них – составные.

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

COLLATE – определение последовательности сортировки

Ограничения • NOT NULL • PRIMARY KEY • UNIQUE • CHECK • FOREIGN KEY/ REFERENCES

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

• Вводятся понятия «неотложенное» и «отложенное» ограничение целостности. • По умолчанию ограничения определяются как неотложенные. • Неотложенные ограничения проверяются при выполнении каждого оператора SQL. • Если базовая таблица, ассоциированная с ограничением целостности, не удовлетворяет его условию, то оператор SQL не выполняется.

Целостность связи (FOREIGN KEY/ REFERENCES) • В родительской таблице для столбца/ • совокупности столбцов, на которые идет ссылка, должно быть задано ограничение на уникальность. Если внешний ключ составной, то при описании таблиц должны быть выполнены следующие условия: – для каждого из полей, на которые идет ссылка, должно быть задано ограничение NOT NULL; – число столбцов в ссылке на таблицу и столбцы должно соответствовать числу столбцов во внешнем ключе; – i-й столбец во внешнем ключе соответствует i-му столбцу в ссылке на таблицу и столбцы, тип данных и длина поля каждого столбца ссылки должны соответствовать типу данных соответствующего целевого столбца и иметь равную длину.

Пример. Описание 3 -х связанных таблиц CREATE TABLE spr_mat (kod_mat CHAR(3) NOT NULL PRIMARY KEY, naim_mat CHAR (30) NOT NULL UNIQUE); CREATE TABLE spr_post (kod_post CHAR(5) NOT NULL, naim_post CHAR (30) NOT NULL, PRIMARY KEY (kod_post), UNIQUE (naim_post)); CREATE TABLE post (kod_post CHAR(5) NOT NULL, kod_mat CHAR(3) NOT NULL, data_post DATE NOT NULL, kolv NUMERIC NOT NULL, PRIMARY KEY (kod_post, kod_mat, data_post), FORIGN KEY (kod_mat) REFERENCES spr_mat (kod_mat), FORIGN KEY (kod_post) REFERENCES spr_post (kod_post));

CHECK (выражение) Пример 1: CREATE TABLE sotrudnic ( …. , oklad money CHECK (oklad>2000) …. );

Пример 2 CREATE TABLE Salespeople (snum integer NOT NULL UNIQUE, sname char(10) NOT NULL, city char(10) CHECK (city IN ('London', 'New York', 'San Jose', 'Barselona')), comm decimal CHECK (comm < 1 )); … ); Пояснения: - перечислены города, в которых есть подразделения - comm - размер комиссионных отчислений

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

Пример. Значения по умолчанию (snum integer NOT NULL UNIQUE, sname char(10) NOT NULL, city char(10) CHECK (city IN ('London', 'New York', 'San Jose', 'Barselona')) DEFAULT = 'New York ', comm decimal CHECK (comm < 1 ));

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

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

РАСШИРЕННЫЕ ВОЗМОЖНОСТИ ОПЕРАТОРА CREATE TABLE В ПОСЛЕДУЮЩИХ СТАНДАРТАХ

SQL: 2003 • новые свойства столбцов - IDENTITY и GENERATED

возможность определения новой базовой таблицы, подобной (like) одной или нескольким существующим таблицам, появилась в SQL: 1999. В SQL: 2003 она была расширена.

Пример CREATE TABLE T 4 ( LIKE T 1, INCLUDING DEFAULTS);

выполнение оператора CREATE TABLE с разделом LIKE не приводит к образованию какой-либо зависимости между новой таблицей и той, которая использовалась в разделе LIKE (т. е. их структура может независимо изменяться).

Раздел AS оператора CREATE TABLE • CREATE TABLE table_name [ column_name_commalist ] AS subquery { WITH NO DATA | WITH DATA } • Используется, когда надо скопировать только часть структуры существующих таблиц, а в общем случае – создать таблицу по образу некоторого выражения запросов.

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

Создание таблицы на основе уже существующей (их) Другие варианты

Пример (примечание: в стандарте не поддерживается) SELECT сотрудники. Код_сотрудника, сотрудники. Фамилия, [сотрудники]![Оклад]*2 AS Премия, INTO [премия-1007] FROM сотрудники;

Изменение структуры таблицы

ALTER TABLE имя таблицы {ADD [COLUMN] определение столбца} |{ALTER [COLUMN] имя столбца изменяющее действие} |{DROP [COLUMN] имя столбца RESTRICT | CASCADE} | {ADD определение ограничения для таблицы} |{DROP CONSTRAINT имя ограничения RESTRICT | CASCADE}; Изменяющее действие : : =

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

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

• • Если используемая система не поддерживает ALTER TABLE , или если вы хотите избежать ее использования, можно просто создать новую таблицу с необходимыми изменениями при создании, и использовать команду INSERT с SELECT * запросом чтобы переписать в нее данные из старой таблицы. Пользователям, которым был предоставлен доступ к старой

Удаление таблицы • DROP TABLE < table name >; • Удалить таблицу может только ее создатель или пользователь, которому переданы эти права. • SQL требует очистить таблицу прежде, чем ее удалить • Надо убедиться, что удаляемая таблица не ссылается внешним ключом к другой таблице и что она не используется в определении Представления

Определение домена

CREATE DOMAIN имя домена [AS] тип данных [DEFAULT значение по умолчанию] [определение ограничения …] [COLLATE имя сравнения]

DOMAIN домен – позволяет задать альтернативный тип данных Пример: CREATE DOMAIN id_name AS integer CHECK (VALUE>0);

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

Синтаксис CREATE VIEW <имя представления> [(<список столбцов выборки>)] AS < SELECT оператор> [WITH [CASCADED |LOCAL] CHECK OPTION]);

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

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

Ограничения при использовании представлений • нельзя корректировать данные представлений, • • • полученных на основе нескольких базовых таблиц (в некоторых СУБД это ограничение менее жесткое) при определении представления не должно использоваться соединение таблицы самой с собой нельзя манипулировать данными представлений, полученных на основе оператора группирования (GROUP BY) нельзя корректировать вычисляемые поля представление должно содержать один и только один запрос, т. е. не допускается использование UNION, EXCEPT, INTERSECT включающий запрос представления не может содержать предложение DISTINCT ни на один столбец нельзя ссылаться в SELECT более одного раза

CHECK OPTION – может задаваться только для изменяемых представлений

Примеры Пример 1: CREATE VIEW postmet AS SELECT * FROM post WHERE kodmat LIKE 'M%'; Пример 2: CREATE VIEW postr AS SELECT naim_post, naim_mat, dat_post, kolv FROM sp_post, postavka, sp_mat WHERE postavka. kod_post=sp_post. kod_post AND sp_mat. kod_mat=postavka. kod_mat Запрос к SELECT * FROM postr WHERE naim_mat="сталь"

Примеры Пример 3: CREATE VIEW post 2 (kod_post, dat_post, kolv, summa) AS SELECT kod_post, dat_post, kolv* cena AS summa FROM postavka;

Примеры Пример 4 CREATE VIEW postitog (kod_post, kol_post, post_ob, post_sr) AS SELECT kod_post, COUNT (*) AS kol_post, SUM (kolv) AS post_ob, AVG (kolv) AS post_sr FROM postavka;

Примеры Пример 5 CREATE VIEW mat_k AS SELECT * FROM sp_mat WHERE naim_mat IN ('Сталь', Чугун) WITH CHECK OPTION;

КТО МОЖЕТ СОЗДАВАТЬ ПРЕДСТАВЛЕНИЯ? Чтобы создавать представление, пользователь должен иметь привилегию SELECT во всех таблицах, на которые имеются ссылки в представлении.

Создание представлений в ERWin

• Вид окна редактора представлений зависит выбранной целевой СУБД Ниже рассмотрен пример для СУБД ORACLE

Для ORACLE

Для Access

СХЕМА

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

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

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

CREATE SCHEMA предложение, определяющее имя схемы [DEFAULT CHARACTER SET набор символов ] [{оператор CREATE DOMAIN | оператор CREATE TABLE | оператор CREATE VIEW | оператор GRANT | оператор CREATE ASSERTION | оператор CREATE CHARACTER SET | оператор CREATE COLLATION | оператор CREATE TRANSLATION [набор символов]}]…;

предложение, определяющее имя схемы : : = |AUTHORIZATION идентификатор авторизации |имя схемы AUTHORIZATION идентификатор авторизации

УНИЧТОЖЕНИЕ ОБЪЕКТОВ

Уничтожение объектов Команда Вид объекта TABLE DROP VIEW ……. Имя объекта

Удаление таблицы • Удалять можно только пустую таблицу. Чтобы удалить все данные из таблицы, надо использовать оператор DELETE • Затем можно аннулировать определение таблицы с помощью оператора DROP TABLE

DROP TABLE <имя таблицы> CASCADE | RESTRICT; • Если определен параметр RESTRICT, то не должно • существовать никаких ссылок на удаляемую таблицу в представлениях или ограничениях. Параметр CASCADE приводит к удалению всех объектов, ссылающихся на таблицу, вместе таблицей.

Пример DROP TABLE sotr CASCADE;

СОЗДАНИЕ ИНДЕКСОВ

CREATE INDEX • • • В стандарте SQL – отсутствует. Во многих реализациях – присутствует. Отличия для различных реализаций языка в допустимых опциях оператора CREATE INDEX наблюдаются очень большие Индексы могут быть: – А – В • Простые • Составные • Уникальные • Неуникальные • СУБД сама определяет, надо ли использовать ( «открывать» ) индекс в процессе выполнения той или иной обработки

Рекомендации по созданию индексов • Создавайте индексы только в тех случаях, • • когда число строк > 200. Создавайте индексы на столбцах, которые часто используются в разделе WHERE. Индексируйте столбцы, часто используемые в операторах SQL для соединения таблиц. Используйте для индексирования только те столбцы, в которые входит небольшой процент строк с одним и тем же значением. Не индексируйте столбцы, которые используются только в функциях.

Рекомендации по созданию индексов (продолжение) • Не индексируйте часто изменяемые столбцы. • Не применяйте индексацию в тех случаях, • когда повышение эффективности за счет создания индекса приводит к снижению эффективности при выполнении операций INSERT, UPDATE и DELETE, поскольку при выполнении каждой из отмеченных операций набор индексов будет перестраиваться. В некоторых СУБД - в наиболее критических случаях с помощью ключевого слова Cluster создается так называемый кластерный индекс, при котором записи в таблице физически переупорядочиваются в таблице.

Синтаксис СУБД Access CREATE [ UNIQUE ] INDEX индекс ON таблица (поле [ASC|DESC][, поле [ASC|DESC], . . . ]) [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]; PRIMARY – первичный ключ DISALLOW NULL – запретить появление пустых значений для новых записей IGNORE NULL - предотвратить индексирование записей со значениями Null в одном или нескольких индексированных полях

Примеры 1. CREATE INDEX name_idx ON employee (name); 2. CREATE UNIQUE INDEX t_nom_idx ON employee (tab_nom); 3. CREATE INDEX ord_idx ON orders_tbl (cust_id, prod_id);

ERWin Генерация схемы

• На следующих слайдах приведены фрагменты • описания БД для СУБД ORACLE Использование ERWin «смягчает» требования к знанию особенностей синтаксиса конкретной системы

CREATE TABLE СОТРУДНИК ( фамилия VARCHAR 2(20) NOT NULL, Имя VARCHAR 2(20) NULL, код_сотрудника INTEGER NOT NULL, Отчество VARCHAR 2(20) NULL, дата_рождения DATE NULL CHECK (Date. Diff('yyyy'; СОТРУДНИК!Дата_рождения; Date())<55), код_подразделения INTEGER NOT NULL, пол VARCHAR 2(1) NULL CHECK (пол IN ('м', 'ж')), категория VARCHAR 2(20) NULL, воинская_обязанность SMALLINT NULL, оклад INTEGER NULL CHECK (оклад BETWEEN 8000 AND 40000) );

CREATE UNIQUE INDEX XPKсотру ON СОТРУДНИК ( код_сотрудника ASC ); CREATE INDEX XIE 1 СОТРУДНИК ON СОТРУДНИК ( фамилия ASC, Имя ASC, Отчество ASC ); ALTER TABLE СОТРУДНИК ADD PRIMARY KEY (код_сотрудника);

CREATE TABLE сотрудник_предмет ( код_сотрудника INTEGER NOT NULL, код_предмета INTEGER NOT NULL ); CREATE UNIQUE INDEX XPKсотрудник_предмет ON сотрудник_предмет ( код_сотрудника ASC, код_предмета ASC ); ALTER TABLE сотрудник_предмет ADD PRIMARY KEY (код_сотрудника, код_предмета);

ALTER TABLE СОТРУДНИК ADD FOREIGN KEY (код_подразделения) REFERENCES ПОДРАЗДЕЛЕНИЕ ( код_подразделения); ALTER TABLE сотрудник_предмет ADD FOREIGN KEY (код_предмета) REFERENCES ПРЕДМЕТ (код_предмета); ALTER TABLE сотрудник_предмет ADD FOREIGN KEY (код_сотрудника) REFERENCES СОТРУДНИК (код_сотрудника);

CREATE OR REPLACE VIEW V_54 AS SELECT СОТРУДНИК. фамилия, ПРЕДМЕТ. наименование_предмета_краткое FROM СОТРУДНИК, ПРЕДМЕТ;

ТРАНЗАКЦИИ

Транзакции (Transaction) • Транзакция – набор операций, которые обрабатываются как единый блок. В стандарте SQL-92 – отсутствует. • Транзакции начинается инструкцией Begin. Trans, завершается с помощью инструкции Commit. Trans, а все изменения, внесенные после инструкции Begin. Trans можно отменить инструкцией Rollback. • Допускаются вложенные Транзакции. Число уровней вложенности зависит от СУБД. • Транзакции увеличивают скорость операций, в которых изменяются данные, и позволяют легко отменять результаты таких операций.

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

• Команды управления транзакциями используются только с командами DML INSERT, UPDATE и DELETE. • При пользовании транзакциями измененные данные хранятся во временной области пока не будут выполнены команды Commit или Rollback