Лекция 10. Запросы определения данных (DDL). Свойства столбцов и таблиц. На основе запроса выборки можно построить представление. В SQL представление является виртуальной таблицей, построенной на основе данных одной или нескольких таблиц, т. е. запрос выборки может быть многотабличным. По одним и тем же таблицам можно построить несколько представлений. Поведение представлений отличается от поведения запросов. Представление всегда содержит только «свежие» данные. Любые изменения в таблицах немедленно отражаются и в представлении. Забота об обновлении данных лежит на СУБД. Таким образом, представление дает возможность работы с выделенными данными как с некоторой локальной таблицей.

Как и запросы, представления не всегда могут быть редактируемыми. Как правило, редактируемыми могут быть только представления, основанные на одной таблице, без использования предикатов, группировки и агрегатных функций. В некоторых СУБД, например в Access, не делается различия между запросом и таблицей. Это позволяет основывать запросы на других запросах, что внешне напоминает работу с представлениями. Само представление описывается путем указания идентификатора представления и запроса, который должен быть выполнен для его получения. Инструкция создания представления имеет следующий формат: CREAT VIEW [(,[ ]...)] AS Если имена столбцов в представлении не указываются, то будут использоваться имена столбцов из запроса, описываемого соответствующим оператором SELECT. Инструкция удаления представления имеет формат вида: DROP VIEW.

Использование представлений для однопользовательской БД имеет целью лишь упрощение структуры запросов. Однако для многопользовательской сетевой СУБД представления играют ключевую роль в определении доступа к данным и защите информации. Использование представлений дает следующие преимущества: 1. Независимость от данных. С помощью представлений можно создать согласованную, неизменную картину структуры БД, которая будет оставаться стабильной даже в случае изменения (незначительного) исходных таблиц. 2. Актуальность. Представление содержит только «свежие» данные. 3. Повышение защищенности данных. Каждому пользователю может быть предоставлен ограниченный набор представлений, дающих доступ. Только к определенной информации. 4. Снижение сложности. Использование представлений позволяет упростить структуру запросов. 5. Возможность индивидуальной настройки. Каждый пользователь может работать только с теми данными, которые ему действительно нужны, и к тому же в определенной форме. К недостаткам можно отнести снижение производительности, наличие структурных ограничений и ограниченные возможности обновления.

Создание доменов Инструкция создания домена имеет формат вида: CREAT DOMAIN [AS] тип данных [DEFAULT ] [CHECK ( )] Условие проверки СНЕСК позволяет задать ограничители целостности на значения, которые может принимать домен. Например: CREAT DOMAIN sex_type AS CHAR СНЕСК (VALUE IN (M, F)); Значения в операторе IN могут также выбираться и из некоторой таблицы, например IN (SELECT s_type FROM STypes). Изменить определение домена можно с помощью инструкции ALTER DOMAIN. Удалить созданный домен можно с помощью инструкции DROP DOMAIN, имеющего следующий формат записи: DROP DOMAIN [RESTRICT|CASCADE] Опция CASCADE позволяет после удаления домена изменить все типы полей, основанных на этом домене, на соответствующий тип данных и произвести необходимую их конвертацию, насколько это будет возможно.

Ключевые столбцы и индексы Вы можете использовать следующие конструкции: [CONSTRAINT ] PRIMARY KEY ( ). Определяет первичный ключ таблицы. В таблице может быть только один первичный ключ, состоящий из одного или нескольких столбцов. Столбцам, входящим в первичный ключ, автоматически присваивается свойство NOT NULL. Ключевое слово CONSTRAINT и имя ключа можно опустить, так как для первичного ключа заданное имя игнорируется и используется имя PRIMARY. Если в состав первичного ключа входят столбцы с типом TEXT и BLOB, необходимо указать количество символов в начале значения столбца; при этом первичный ключ содержит не полные значения столбца, а только начальные подстроки значений. Если мы решили не использовать дополнительный столбец в таблице а образовать первичный ключ из столбцов, то в команду создания таблицы нужно было бы включить следующее определение:

PRIMARY KEY (, ); INDEX [ ] ( ). Создает индекс для указанных столбцов. Индекс это вспомогательный объект, позволяющий значительно повысить производительность запросов с условием на значение столбцов, включенных в индекс. Аналогично первичному ключу, при создании индекса для столбцов типом TEXT и BLOB необходимо указать количество символов в начале значения столбца, по которым будет проведено индексирование. (Имя индекса указывать не обязательно. Если вы не зададите имя индекса, оно сгенерируется автоматически. Вместо ключевого слова INDEX можно использовать его синоним – слово KEY.

[CONSTRAINT ] UNIQUE [ ] (список столбцов>); Создает уникальный индекс для указанных столбцов. Уникальный индекс отличается от обычного наличием дополнительного ограничения: наборы значений в столбцах, включенных в уникальный индекс, должны быть различны. Иными словами, в таблице не должно быть строк, у которых значения во всех этих столбцах совпадают. Исключение составляют неопределенные значения (NULL): индекс может содержать два (и более) одинаковых набора значений, если хотя бы одно из значений в этих наборах NULL. Для столбцов TEXT и BLOB необходимо указать количество символов в начале значения столбца, по которым будет проведено индексирование. Имя ограничения и имя индекса указывать не обязательно. Если ни имя ограничения, ни имя индекса не указаны, имя индекса присваивается программой автоматически. Вместо ключевого слова UNIQUE можно использовать его синонимы выражения UNIQUE INDEX или UNIQUE KEY.

FULLTEXT [ ] ( ). Создает полнотекстовый индекс для указанных столбцов. Полнотекстовый индекс обеспечивает ускоренный поиск по значениям символьных столбцов (типы CHAR и TEXT) независимо от длины значений. Такой индекс подобен предметному указателю в книге: он представляет собой список всех слов, встречающихся в значениях столбцов, со ссылками на те значения, в которых каждое слово содержится. Полнотекстовый индекс можно создать только в таблицах с типом MyISAM. Для поиска с использованием полнотекстового индекса предназначен оператор МАТСН... AGAINST. Имя индекса указывать не обязательно. Если вы не зададите имя индекса, оно сгенерируется автоматически. [CONSTRAINT ] FOREIGN KEY [ ] ( ) REFERENCES ( [ ] Определяет внешний ключ таблицы. Настроив внешний ключ, мы тем самым создадим связь между данными (дочерней) таблицей и родительской таблицей. Внешние ключи поддерживаются только для таблиц с типом InnoDB (причем и дочерняя, и родительская таблица должны иметь тип InnoDB), для остальных типов таблиц выражение FOREIGN KEY игнорируется.

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

Для операций удаления вы можете указать одно из следующих выражений: ON DELETE CASCADE каскадное удаление строк дочерней таблицы (строка родительской таблицы удаляется вместе со всеми ссылающимися на нее строками дочерней таблицы); ON DELETE SET NULL обнуление значений внешнего ключа в соответствующих строках дочерней таблицы; ON DELETE RECTRICT или ON DELETE NO ACTION (в MySQL эти выражения являются синонимами) запрет удаления строк родительской таблицы при наличии ссылающихся на них строк дочерней таблицы. Если вы не задали правило поддержания целостности для операций удаления, по умолчанию используется правило ON DELETE RECTRICT.

Для операций изменения строк родительской таблицы вы можете указать одно из следующих выражений: ON UPDATE CASCADE каскадное обновление значений внешнего ключа дочерней таблицы (вместе со значением первичного ключа в строке родительской таблицы изменяется значение внешнего ключа во всех ссылающихся на нее строках дочерней таблицы); ON DELETE SET NULL обнуление значений внешнего ключа в соответствующих строках дочерней таблицы; ON UPDATE RESTRICT запрет изменения значений первичного ключа в строках родительской таблицы при наличии ссылающихся на них строк дочерней таблицы. если вы не задали правило поддержания целостности для операций изменения, по умолчанию используется правило ON UPDATE RESTRICT. Для столбцов внешнего ключа автоматически создается индекс, поэтому проверки значений внешних ключей в ходе контроля целостности связи выполняются быстро.

FOREIGN KEY (product_id) REFERENCES Products (id) ON DELETE RECTRICT ON UPDATE CASCADE Это выражение означает, что столбец product_id (товар) дочерней таблицы является внешним ключом, который ссылается на столбец id (идентификатор) родительской таблицы Products (Товары). При этом запрещается удаление строки таблицы Products, если на нее ссылается хотя бы одна строка дочерней таблицы, а изменение значения в столбце id таблицы Products приводит к автоматическому обновлению значений столбца product_id таблицы.

Создание таблиц Инструкция создания таблицы имеет формат вида: CREAT TABLE ( «тип данных> [NOT NULL] [, [NOT NULL]]... ) Обязательными операндами инструкции являются имя создаваемой таблицы и имя хотя бы одного поля с указанием типа данных. При создании таблицы для отдельных полей могут указываться некоторые дополнительные правила контроля вводимых в них значений. Конструкция NOT NULL требует, чтобы в этом столбце должно быть определено значение. Например: CREAT TABLE Товары ( Код CHAR(5) NOT NULL, Тип СНАR(8), Наименование VARCHAR(20) NOT NULL, Цена DECIMAL(8,2));

Инструкция изменения структуры таблицы имеет формат вида: ALTER TABLE ( {ADD, MODIFY, DROP} [ ] [NOT NULL] [ADD, MODIFY, DROP } [ ] [NOT NULL]]...) Изменение структуры таблицы может состоять в добавлении (ADD), изменении (MODIFY) или удалении (DROP) одного или нескольких столбцов. Правила записи инструкции ALTER TABLE такие же, как и инструкции CREAT TABLE, разве что при удалении столбца указывать тип данных не требуется. Для примера добавим одно поле: ALTER TABLE Товары (ADD Категория VARCHAR(20)); Инструкция удаления таблицы имеет формат вида: DROP TABLE ;

Инструкции создания и изменения таблицы имеют и более сложный вид записи, позволяющий не только задать ограничители целостности значений, но и определить ссылочную целостность связанных таблиц. Ограничители целостности можно также задать отдельно с помощью оператора ASSERTION. Расширенный вариант инструкции создания таблицы имеет формат вида: CREATE TABLE { [NOT NULL] [UNIQUE] [DEFAULT ] [СНЕСК ( )] [...]} PRIMARY KEY ( ), ] { [UNIQUE ( ), ][...]} {[FOREIGN KEY ( )] REFERENCES [ ] МАТСН (PARTIAL| FULL) [ON UPDATE ] [ON DELETE ] [,…]} {[СНЕСК( )] [,...]}.

Фраза PRIMARY KEY определяет первичный ключ таблицы. Фраза UNIQUE позволяет определить альтернативные (потенциальные) ключи. Фразы PRIMARY KEY и REFERENCES используются для задания связей между таблицами. Можно дополнительно определить каскадное удаление и каскадное обновление. Для выполнения ссылочной целостности SQL определяют четыре вида действий: CASCADE, SET NULL, SET DEFAULT, NO ACTION (используется по умолчанию). Фраза СНЕСК служит для задания дополнительных условий для значений полей таблицы. Вышеперечисленные ограничители целостности могут дополнительно предваряться фразой CONSTRAINT, что позволит впоследствии отменить это ограничение в операторе ALTER TABLE. Например: CREATE TABLE Заказы (КодЗаказа INTEGER PRIMARY KEY, КодКлиента INTEGER, ДатаЗаказа DATE, ПримечанияЗаказа VARCHAR(255)) CONSTRAINT ВнКлЗаказыКодКлиента FOREIGN KEY (КодКлиента) REFERENCES Клиенты ON UPDATE CASCADE ON DELET CASCADE;

Расширенный вариант инструкции изменения таблицы имеет формат вида: ALTER TABLE [ADD [NOT NULL] [UNIQUE] [DEFAULT ] [СНЕСК ( )]] [DROP [RESTRICT|CASCADE]] [ADD CONSTRAINT PRIMARY KEY | UNIQUE | FOREIGN KEY | СНЕСК] [DROP CONSTRAINT [RESTRICT|CASCADE]] [ALTER SET DEFAULT ] [ALTER DROP DEFAULT]

Свойства столбцов При создании или изменении таблицы вы можете указать следующие свойства столбцов: NOT NULL Это свойство указывает, что в данном столбце не допускаются неопределенные значения (NULL). В качестве примера рассмотрим столбец product_id (товар) таблицы Orders (Заказы), который мы определили как product_id BIGINT UNSIGNED NOT NULL Тем самым мы запретили неопределенные номера товаров, поскольку регистрировать заказ с неизвестным товаром не имеет смысла. Если для столбца задано свойство NOT NULL, то, в частности, NULL не может использоваться в качестве значения по умолчанию для этого столбца. Значение по умолчанию, отличное от NULL, вы можете задать с помощью свойства DEFAULT, которое описано ниже. Если же вы задали для столбца свойство NOT NULL, но не задали значение по умолчанию и не указали значение для этого столбца при вставке строки в таблицу, то поведение программы зависит от того, в каком режиме вы работаете.

NULL. Данное свойство указывает, что в столбце разрешены неопределенные значения. Задавать это свойство имеет смысл только для столбцов с типом TIMESTAMP, которые по умолчанию не допускают неопределенных значений. Остальные типы столбцов допускают неопределенные значения, если только для них не задано свойство NOT NULL. DEFAULT. Данное свойство определяет значение по умолчанию для столбца, которое используется, если при вставке строки в таблицу значение столбца не задано явно. Значением по умолчанию может быть только константа; исключение составляют столбцы с типом TIMESTAMP, для которых в качестве значения по умолчанию можно задать переменную величину CURRENT_TIMESTAMP (текущую дату и время). Нельзя установить значение по умолчанию для столбцов с типом BLOB и TEXT (всех разновидностей), а также для числовых столбцов, для которых задано свойство AUTO_INCREMENT. Кроме того, нельзя использовать неопределенное значение по умолчанию (NULL), если для столбца задано свойство NOT NULL. Например, чтобы задать для поля phone (телефон) значение по умолчанию, равное пустой строке, можно определить это поле следующим образом: phone VARCHAR (20) DEFAULT ' '

COMMENT 'Текст комментария'. Произвольное текстовое описание столбца длиной до 255 символов. Например, описание для поля rating (рейтинг) можно задать следующим образом: rating INT COMMENT 'Рейтинг клиента'

Опциональные свойства таблицы При создании таблицы указывать опциональные свойства не обязательно. Тем не менее, рассмотрим некоторые свойства, которые вы можете задать для таблицы: ENGINE. Тип таблицы является наиболее важным из опциональных свойств таблицы. В MySQL существует множество типов таблиц, каждый из которых лучше всего подходит для решения определенной задачи. Основными типами таблиц являются InnoBD и MyISAM. Вместо ключевого слова ENGINE можно использовать его синоним – слово ТУРЕ.

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

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

Если при настройке сервера MySQL вы выбрали в качестве типа базы данных вариант Multifunctional Database (Многофункциональная база данных) или Transactional Database Only (Транзакционная база данных), либо если вы настраивали сервер в стандартном режиме (в этом случае тип базы данных был задан автоматически), то по умолчанию применяется тип таблиц InnoBD. В этом случае, если требуется создать таблицу с типом MyISAM добавим в команду создания таблицы выражение ENGINE MyISAM. Если же при настройке вы предпочли вариант Non- Transactional Database Only (Нетранзакционная база данных), то по умолчанию применяется тип таблиц MyISAM, а таблицы типа InnoBD не поддерживаются.

AUTO_INCREMENT. Задание этого свойства для таблицы, в которой есть столбец со свойством AUTO_INCREMENT, позволяет начать нумерацию в этом столбце не с единицы, а с указанного вами значения. Например, если номера заказов должны начинаться с 1000, нужно в команду создания таблицы включить параметр AUTO_INCREMENT CHARACTER SET. Данный параметр определяет кодировку по умолчанию для символьных столбцов таблицы. Если не задана кодировка для таблицы, то по умолчанию используется кодировка, установленная для базы данных. Если и для базы данных кодировка не была указана, то используется кодировка, установленная по умолчанию при настройке MySQL.

COLLATE. Данный параметр определяет правило сравнения значений, используемое по умолчанию для символьных столбцов таблицы. Если не задано правило сравнения для таблицы, то по умолчанию используется правило, установленное для базы данных. CHECKSUM. Данный параметр включает проверку контрольной суммы для строк таблицы MyISAM, что позволяет быстро обнаруживать поврежденные таблицы. COMMENT 'Текст комментария'. Произвольное текстовое описание таблицы длиной до 60 символов. Например, описание для таблицы Клиенты можно задать, включив в команду создания таблицы параметр COMMENT 'Сведения о клиентах'.

СREATE TABLE Orders ( id SERIAL, date DATE, product_id BIGINT UNSIGNED NOT NULL, qty INT UNSIGNED, amount DECIMAL(10,2), customer_id BIGINT UNSIGNED, PRIMARY KEY (id), FOREIGN KEY (product_id) REFERENCES Products (id) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (customer_id) REFERENCES Customers (id) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE InnoDB CHARACTER SET utf8;