2014-02-24
1802
O BLOB
O BYTE
O BINARY
· Последовательные типы данных - используются для представления возрастающих числовых последовательностей.
o SERIAL - тип данных на основе INTEGER, позволяющий сформировать уникальное значение (например, для первичного ключа). При добавлении записи СУБД автоматически присваивает полю данного типа значение, получаемое из возрастающей последовательности целых чисел.
В заключение следует сказать, что для всех типов данных имеется общее значение NULL - "не определено". Это значение имеет каждый элемент столбца до тех пор, пока в него не будут введены данные. При создании таблицы можно явно указать СУБД могут ли элементы того или иного столбца иметь значения NULL (это не допустимо, например, для столбца, являющего первичным ключом).
4.3 DDL: создание и модификация базы данных.
При описании команд предполагается, что:
· текст, набранный строчными буквами (например, CREATE TABLE) является обязательным
· текст, набранный прописными буквами и заключенный в угловые скобки (например, <имя_базы_данных>) обозначает переменную, вводимую пользователем
· в квадратные скобки (например, [NOT NULL]) заключается необязательная часть команды
· взаимоисключающие элементы команды разделяются вертикальной чертой (например, [UNIQUE | PRIMARY KEY]).
| Команда | Описание |
| CREATE DATABASE <имя_базы_данных> | Создание базы данных. |
| DROP DATABASE <имя_базы_данных> | Удаление базы данных. |
Создание и удаление таблиц
Создание таблицы:
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));
Удаление таблицы: DROP TABLE <имя_таблицы>
Модификация таблицы:
| Добавить столбцы | ALTER TABLE <имя_таблицы> ADD (<имя_столбца> <тип_столбца> [NOT NULL] [UNIQUE | PRIMARY KEY] [REFERENCES <имя_мастер_таблицы> [<имя_столбца>]],...) |
| Удалить столбцы | ALTER TABLE <имя_таблицы> DROP (<имя_столбца>,...) |
| Модификация типа столбцов | ALTER TABLE <имя_таблицы> MODIFY (<имя_столбца> <тип_столбца> [NOT NULL] [UNIQUE | PRIMARY KEY] [REFERENCES <имя_мастер_таблицы> <имя_столбца>]],...) |
4.4 DDL: Индексирование базы данных.
Создание индекса:
CREATE [UNIQUE] INDEX <имя_индекса> ON <имя_таблицы> (<имя_столбца>,...)
Эта команда создает индекс с заданным именем для таблицы <имя_таблицы> по столбцам, входящим в список, указанный в скобках. Индекс часто представляет из себя структуру типа B-дерева (см. параграф 1.2), но могут использоваться и другие структуры. Создание индексов значительно ускоряет работу с таблицами. В случае указания необязательного параметра UNIQUE СУБД будет проверять каждое значение индекса на уникальность.
Очень часто встает вопрос, какие поля необходимо индексировать. Обязательно надо строить индексы для первичных ключей, поскольку по их значениям осуществляется доступ к данным при операциях соединения двух и более таблиц. Также в ответе на этот вопрос поможет анализ наиболее частых запросов к базе данных. Например, для БД publications можно ожидать, что одним из наиболее частых запросов будет выборка всех публикаций данного автора. Для минимизации времени этого запроса необходимо посроить индекс для таблицы authors по именам авторов:
CREATE INDEX au_names ON authors (author);
Создание индексов для первичных ключей:
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);
Первоначальное определение структуры индексов производится разработчиком на стадии создания прикладной системы. В дальнейшем она уточняется администратором системы по результатам анализа ее работы, учета наиболее часто выполняющихся запросов и т.д.
Удаление индекса:
DROP INDEX <имя_индекса>
4.5 DML: Команды модификации данных.
К этой группе относятся операторы добавления, изменения и удаления записей.
Добавить новую запись в таблицу:
INSERT INTO <имя_таблицы> [ (<имя_столбца>,<имя_столбца>,...) ]
VALUES (<значение>,<значение>,..)
Список столбцов в данной команде не является обязательным параметром. В этом случае должны быть указаны значения для всех полей таблицы в том порядке, как эти столбцы были перечислены в команде CREATE TABLE, например:
INSERT INTO publishers VALUES (16,"Microsoft Press","https://www.microsoft.com");
Пример с указанием списка столбцов:
INSERT INTO publishers (publisher,pub_id)
VALUES ("Super Computer Publishing",17);
Модификация записей:
UPDATE <имя_таблицы> SET <имя_столбца>=<значение>,...
[WHERE <условие>]
Если задано ключевое слово WHERE и условие, то команда UPDATE применяется только к тем записям, для которых оно выполняется. Если условие не задано, UPDATE применяется ко всем записям. Пример:
UPDATE publishers SET url="https://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".
