Лекция 79. Язык SQL. Средства манипулирования данными

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

Операторы базы данных

Лекция 78. Язык SQL. Средства описания данных

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

78.1. DDL: Операторы создания схемы базы данных.

При описании команд предполагается, что:

• текст, набранный строчными буквами (например, CREATE TABLE) является обязательным
• текст, набранный прописными буквами и заключенный в угловые скобки (например, <имя_базы_данных>) обозначает переменную, вводимую пользователем
• в квадратные скобки (например, [NOT NULL]) заключается необязательная часть команды
• взаимоисключающие элементы команды разделяются вертикальной чертой (например, [UNIQUE | PRIMARY KEY]).

Команда	Описание
CREATE DATABASE <имя_базы_данных>	Создание базы данных.
DROP DATABASE <имя_базы_данных>	Удаление базы данных.

78.1.2.1. Создание таблицы:

CREATE TABLE <имя_таблицы> (<имя_столбца> <тип_столбца> [NOT NULL] [UNIQUE | PRIMARY KEY] [REFERENCES <имя_мастер_таблицы> [<имя_столбца>]],...)

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

• NOT NULL - в этом случае элементы столбца всегда должны иметь определенное значение (не NULL)
• один из взаимоисключающих параметров UNIQUE - значение каждого элемента столбца должно быть уникальным или PRIMARY KEY - столбец является первичным ключом.
• REFERNECES <имя_мастер_таблицы> [<имя_столбца>] - эта конструкция определяет, что данный столбец является внешним ключом и указывает на ключ какой мастер_таблицы он ссылается.

Контроль за выполнением указанных условий осуществляет СУБД.

Пример: создание базы данных publications:

CREATE DATABASE publications; CREATE TABLE authors (au_id INT PRIMARY KEY, author VARCHAR(25) NOT NULL); CREATE TABLE publishers (pub_id INT PRIMARY KEY, publisher VARCHAR(255) NOT NULL,url VARCHAR(255)); CREATE TABLE titles (title_id INT PRIMARY KEY, title VARCHAR(255) NOT NULL, yearpub INT, pub_id INT REFERENCES publishers(pub_id)); CREATE TABLE titleautors (au_id INT REFERENCES authors(au_id), title_id INT REFERENCES titles(title_id)); CREATE TABLE wwwsites (site_id INT PRIMARY KEY, site VARCHAR(255) NOT NULL, url VARCHAR(255)); CREATE TABLE wwwsiteauthors (au_id INT REFERENCES authors(au_id), site_id INT REFERENCES wwwsites(site_id));

78.1.2.2. Удаление таблицы:

DROP TABLE <имя_таблицы>

78.1.2.3. Модификация таблицы:

Добавить столбцы	ALTER TABLE <имя_таблицы> ADD (<имя_столбца> <тип_столбца> [NOT NULL] [UNIQUE | PRIMARY KEY] [REFERENCES <имя_мастер_таблицы> [<имя_столбца>]] ,...)
Удалить столбцы	ALTER TABLE <имя_таблицы> DROP (<имя_столбца>,...)
Модификация типа столбцов	ALTER TABLE <имя_таблицы> MODIFY (<имя_столбца> <тип_столбца> [NOT NULL] [UNIQUE | PRIMARY KEY] [REFERENCES <имя_мастер_таблицы> <имя_столбца>]] ,...)

78.2. DDL: Операторы создания индексов

78.2.1. Создание индекса:

CREATE [UNIQUE] INDEX <имя_индекса> ON <имя_таблицы> (<имя_столбца>,...)

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

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

CREATE INDEX au_names ON authors (author);

78.2.2. Создание индексов для первичных ключей:

CREATE INDEX au_index ON authors (au_id);
CREATE INDEX title_index ON titles (title_id);
CREATE INDEX pub_index ON publishers (pub_id);
CREATE INDEX site_index ON wwwsites (site_id);

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

78.2.3. Удаление индекса:

DROP INDEX <имя_индекса>

78.3. DDL: Операторы управления правами доступа

По соображениям безопасности не каждому пользователю прикладной системы может быть разрешено получать информацию из какой-либо таблицы, а тем более изменять в ней данные. Для определения прав пользователей относительно объектов базы данных (таблицы, представления, индексы) в SQL определена пара команд GRANT и REVOKE. Синтаксис операции передачи прав на таблицу:

GRANT <тип_права_на_таблицу> ON <имя_таблицы> [<список_столбцов>] TO <имя_пользователя>

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

• SELECT - получение информации из таблицы
• UPDATE - изменение информации в таблице
• INSERT - добавление записей в таблицу
• DELETE - удаление записей из таблицы
• INDEX - индексирование таблицы
• ALTER - изменение схемы определения таблицы
• ALL - все права

В поле <тип_права_на_таблицу> может быть указано либо ключевое слово ALL или любая комбинация других ключевых слов. Например, предоставим все права на таблицу publishers пользователю andy:

GRANT ALL ON publishers TO andy;

Пользователю peter предоставим права на извлечение и дбавление записей на эту же таблицу:

GRANT SELECT INSERT ON publishers TO peter;

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

GRANT SELECT ON publishers TO PUBLIC;

Отмена прав осуществляется командой REVOKE:

REVOKE <тип_права_на_таблицу> ON <имя_таблицы> [<список_столбцов>] FROM <имя_пользователя>

Все ключевые слова данной команды эквивалентны оператору GRANT.

Большинство систем поддерживают также команду GRANT для назначения привилегий на базу данных в целом. В этом случае формат команды:

GRANT <тип_права_на_базу_данных> ON <имя_базы данных> TO <имя_пользователя>

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

1. CONNECT - права на доступ к данным и их модификацию, если это разрешено на уровне таблицы;
2. RESOURCE - права на управление ресурсами. Все перечисленное выше плюс права на создание новых объектов (таблиц, индексов и т.д.) и удаление и изменение тех объектов, которыми данный пользователь владеет;
3. DBA - права на администрирование. Все права на управление ресурсами плюс права на удаление базы данных, удаление любых объектов, назначение и отмена прав других пользователей.

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

REVOKE <тип_права_на_базу_данных> FROM <имя_пользователя>

79.1. DML: Команды модификации данных

К этой группе относятся операторы добавления, изменения и удаления записей.

79.1.1. Добавить новую запись в таблицу:

INSERT INTO <имя_таблицы> [ (<имя_столбца>,<имя_столбца>,...) ] VALUES (<значение>,<значение>,..)

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

INSERT INTO publishers VALUES (16,"Microsoft Press","http://www.microsoft.com");

Пример с указанием списка столбцов:

INSERT INTO publishers (publisher,pub_id) VALUES ("Super Computer Publishing",17);

79.1.2. Модификация записей:

UPDATE <имя_таблицы> SET <имя_столбца>=<значение>,... [WHERE <условие>]

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

UPDATE publishers SET url="http://www.superpub.com" WHERE pub_id=17;

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

· операции сравнения: >, <, >=, <=, =, <>,!= . В SQL эти операции могут применяться не только к числовым значениям, но и к строкам ( "<" означает раньше, а ">" позже в алфавитном порядке) и датам ( "<" раньше и ">" позже в хронологическом порядке).

· оперции проверки поля на значение NULL: IS NULL, IS NOT NULL

· операции проверки на вхождение в диапазон: BETWEEN и NOT BETWEEN.

· операции проверки на вхождение в список: IN и NOT IN

· операции проверки на вхождение подстроки: LIKE и NOT LIKE

· отдельные операции соединяются связями AND, OR, NOT и группируются с помощью скобок.

Подробно все эти ключевые слова будут описаны и проиллюстрированы в параграфе, посвященном оператору SELECT. Здесь мы ограничимся приведением несложного примера:

UPDATE publishers SET url="url not defined" WHERE url IS NULL;

Эта команда находит в таблице publishers все неопределенные значения столбца url и заменяет их строкой "url not defined".

Дата добавления: 2015-05-09