Лекции SQL. Лекции по sql учебное пособие для студентов
 Скачать 0.84 Mb.
|
|
REFERENCES fakultet(shortname) √ m18 - это условное название добавляемого ограничения CONSTRAINT. Оно должно быть образовано по правилам образования идентификаторов и не должно совпадать по имени с ранее объявленными ограничениями. — 43 — √ Наилучший способ – добавлять ограничение, пока таблицы еще не содержат записей. Иначе вы должны быть уверены, что имеющиеся данные не противоречат тем ограничениям, которые вы собираетесь наложить на таблицу. Пример 6.6. Изменение структуры таблицы Spisok – удаление поля из таблицы ALTER TABLE spisok DROP gruppa Пример 6.7. Удаление таблицы из БД. DROP TABLE region √ В дальнейшем мы опишем эти действия более подробно, но сейчас этого вполне достаточно для работы с БД. 6.4. Добавление ограничений на таблицу Ограничения на таблицу являются одним из средств поддержания целостности данных. Их можно объявлять при создании таблицы, но не возбраняется добавлять и позже, с помощью команд ALTER TABLE. Естественно, нужно следить, чтобы добавляемые ограничения выполнялись для имеющихся в таблице данных, иначе они будут отвергнуты сервером СУБД. ! Синтаксис ограничений для полей таблицы < ограничение > = { VALUE | VALUE [NOT] BETWEEN | VALUE [NOT] LIKE | VALUE [NOT] IN ( | VALUE IS [NOT] NULL | VALUE [NOT] CONTAINING | VALUE [NOT] STARTING [WITH] | (<ограничение >) | NOT < ограничение > | < ограничение > OR < ограничение > | < ограничение > AND < ограничение > } Если ограничение добавляется в уже созданную таблицу, вместо VALUE следует записывать имя поля таблицы. Если ограничение добавляется при — 44 — создании таблицы или вместе с добавлением поля, следует записывать именно служебное слово VALUE. Пример 6.8. Представим себе, что таблица spisok создавалась командой CREATE TABLE spisok ( id INTEGER NOT NULL, fam VARCHAR(30), im VARCHAR(30), ot VARCHAR(30), date_r DATE, kod_fakulteta VARCHAR(10) ), при создании никаких ограничений не было установлено. Добавим эти ограничения несколькими командами ALTER TABLE spisok ADD CONSTRAINT m15 CHECK (fam IS NOT NULL) Это ограничение запрещает добавление записей, у которых поле fam не заполнено. ALTER TABLE spisok ADD CONSTRAINT m16 PRIMARY KEY(id) Эта команда устанавливает первичным ключом поле id. ALTER TABLE spisok ADD CONSTRAINT m17 FOREIGN KEY(kod_fakulteta) REFERENCES fakultet(shortname) Эта команда устанавливает на поле kod_fakulteta ограничение ссылочной целостности по полю shortname таблицы fakultet. ALTER TABLE spisok ADD CONSTRAINT m18 CHECK (date_r>=’01.01.2000’) Эта команда устанавливает для поля ДАТА РОЖДЕНИЯ минимальное значение. ALTER TABLE spisok ADD CONSRAINT m20 CHECK (kurs BETWEEN 1 AND 5) Эта команда устанавливает для поля КУРС – значение должно быть в пределах от 1 до 5. В некоторых случаях бываыет ALTER TABLE fakultet ADD CONSTRAINT m19 UNIQUE (fullname) Эта команда устанавливает ограничение – в таблице ФАКУЛЬТЕТЫ не может быть двух записей с одинаковым значением поля ПОЛНОЕ НАЗВАНИЕ ФАКУЛЬТЕТА. 6.5. Определение домена Домен в SQL – это тип данных, который определяют на базе стандартных типов. Домены предназначены для дальнейшего использования при создании и изменении структуры полей таблиц БД на тех же правах, что и стандартные типы. — 45 — √ Синтаксис определения домена CREATE DOMAIN <имя домена> [AS] <тип данных> [DEFAULT { [CHECK (<ограничение>)] [COLLATE <правило сравнения>]; Параметр <имя домена> следует записывать с использованием только латиницы, цифр (цифра на первой позиции не допускается) и символа подчеркивания. Параметр <тип данных> - это один из типов, описанных в предыдущем параграфе. Параметр DEFAULT определяет значение по умолчанию, то есть то значение, которое вводится, если при добавлении данных оно не задано. За этим служебным словом может стоять Ограничение NOT NULL указывает, что такое поле обязательно должно быть заполнено. Параметр COLLATE <правило сравнения> устанавливает способ сортировки для домена. Дело в том, что для одной и той же кодировки может быть несколько правил сравнения. Для кодировки WIN1251, например, определены правила сравнения WIN1251 и PXW_CYRL. В первом из них заглавные и строчные буквы не различаются, во втором – различаются. √ Параметр CHECK определяет разнообразные ограничения, которые можно наложить на значение поля. Синтаксис ограничений < ограничение > = { VALUE | VALUE [NOT] BETWEEN | VALUE [NOT] LIKE | VALUE [NOT] IN ( | VALUE IS [NOT] NULL | VALUE [NOT] CONTAINING | VALUE [NOT] STARTING [WITH] | (<ограничение >) | NOT < ограничение > | < ограничение > OR < ограничение > | < ограничение > AND < ограничение > } — 46 — Удобно ограничения объяснить на примерах. Ограничение CHECK VALUE>100 определяет, что вводимое значение должно быть больше 100. Ограничение CHECK VALUE BETWEEN 50 AND 60 определяет, что значение должно быть между 50 и 60. Ограничение CHECK VALUE IN ("военнообязанный", "невоеннобязанный") опеределяет, что значение может быть только одним из этих вариантов. Можно объединять ограничения с помощью логических связок OR, AND, отрицания NOT, и скобок, например, CHECK (VALUE>=50)AND(VALUE<=60) означает то же, что и CHECK VALUE BETWEEN 50 AND 60. Примеры определений домена Следующая инструкция создает домен, который может принимать положительные значения больше 1000, со значением по умолчанию 9999. Ключевое слово VALUE заменяется именем столбца основанном на этом домене. CREATE DOMAIN custno AS INTEGER DEFAULT 9999 CHECK (VALUE > 1000); Следующая инструкция создает домен с типом BLOB-текст и определяет кодировку CYRL (аналог MSDOS 866): CREATE DOMAIN descript AS BLOB SUB_TYPE TEXT SEGMENT SIZE 80 CHARACTER SET CYRL; 6.6. Изменение структуры домена ALTER DOMAIN изменяет любые свойства существующего домена, кроме типа данных и установки NOT NULL. Изменения, над доменом воздействуют на все столбцы, основанные на домене, которые не были отменены на уровне таблицы. Обратите внимание: Для изменения типа данных или установки NOT NULL, удалите домен и создаете его заново с желаемыми свойствами. Правда, при этом он не должен использоваться ни в одной таблице. Домен может быть изменен его создателем или пользователем SYSDBA. Синтаксис ALTER DOMAIN <имя домена> { [SET DEFAULT { [DROP DEFAULT] | [ADD [CONSTRAINT] CHECK (<ограничение>)] | [DROP CONSTRAINT] }; Примеры — 47 — Следующая инструкция создает домен с допустимыми значениями > 1000, за тем устанавливает его значение по умолчанию к 9999. CREATE DOMAIN custno AS INTEGER CHECK (VALUE > 1000); ALTER DOMAIN custno SET DEFAULT 9999; 6.7. Создание таблиц Команда CREATE TABLE устанавливает новую таблицу, ее столбцы и ограничения целостности в существующей базе данных. Пользователь, который создает таблицу, становится владельцем таблицы и получает полные привилегии для этого, включая возможность предоставления (GRANT) привилегий другим пользователям, триггерам, и сохраненным процедурам. CREATE TABLE поддерживает несколько опций для определения столбцов: Локальные столбцы, определяющие имя и тип данных для данных, введенных в столбец. Вычисляемые столбцы, базирующиеся на расширении. Значение столбца вычисляется каждый раз при доступе к таблице. Столбцы, к которым обращается выражение, должны существовать раньше, чем столбец может быть определен. Основанные на доменах столбцы, наследуемые все характеристики домена, но определение столбца может включать новое значение по умолчанию, атрибут NOT NULL, дополнительные ограничения CHECK или переопределять порядок сортировки, которые отменяют определение домена. Описание типа данных для столбца типа CHAR, VARCHAR или BLOB-техт может включать предложение CHARACTER SET определяя специфическую кодировку для одиночного столбца, отличную от определенной для базы данных. Предложение COLLATE позволяет указать специфический порядок сортировки для типов данных CHAR, VARCAHR и BLOB-текст. Атрибут NOT NULL предотвращает ввод NULL или неизвестного значения в столбец. Ограничения целостности могут быть определены для таблицы, когда она создана. Ограничения целостности это правила, которые контролируют базу данных и ее компоненты, связи типа столбец-таблица и таблица- таблица, и проверку вводимых данных. Они охватывают все транзакции к — 48 — базе данных и автоматически поддерживаются системой. CREATE TABLE может создавать следующие типы ограничений целостности: PRIMARY KEY (первичный ключ) - уникально идентифицирует каждую строку таблицы. Значение в этом столбце либо в упорядоченном наборе столбцов не могут повторятся в более чем одной строке. Столбец PRIMARY KEY должен быть определен только с атрибутом NOT NULL. Таблица может иметь только один PRIMARY KEY, который может быть определен на одном или более столбцов. UNIQUE (уникальные) ключи гарантируют, что не существует двух строк имеющих одно и тоже значение в специфическом столбце или упорядоченном наборе столбцов. Уникальный столбец должен быть определен с атрибутом NOT NULL. Таблица может иметь один или более UNIQUE ключей. UNIQUE ключ может быть использован FOREIGN KEY (внешний ключ) в другой таблице. Справочные ограничения гарантируют, что значения в наборе столбцов, которые определены в FOREIGN KEY принимают те же самые значения, которые присутствуют в столбце UNIQUE или PRIMARY KEY в справочной таблице. CHECK ограничения предписывают Для не именованных ограничений, система сама создает уникальное имя, сохраненное в системной таблице RDB$RELATION_CONSTRAINTS. Синтаксис CREATE TABLE <имя таблицы> (<определение столбца> [,<определение столбца> | <ограничение на столбец> ...]); < определение столбца > = {<тип данных> | COMPUTED [BY] (<выражение>) | <имя домена>} [DEFAULT {literal | NULL | USER}] [NOT NULL] [<ограничение на столбец >] [COLLATE collation] Параметр <тип данных> - это один из стандартных типов InterBase, или домен. — 49 — <Выражение> = Допустимое выражение SQL, которое возвращает единственное значение. При построении выражений можно использовать числовые константы, а также имена других полей. Необычным выглядит соединение строк – для этого используется знак «две вертикальные черты», например, FAM||’ ‘||IM. Параметры DEFAULT, NOT NULL, COLLATE описаны ранее при определении доменов. [ | CHECK ( | REFERENCES <название другой таблицы> [(other_col [, other_col ...])]} [ | FOREIGN KEY (col [, col ...]) REFERENCES other_table | CHECK ( { | | | | | {ALL | SOME | ANY} ( | EXISTS ( — 50 — | SINGULAR ( | | | ( | NOT | | | NULL | USER | RDB$DB_KEY } [COLLATE collation] COUNT (* | [ALL] | SUM ([ALL] | AVG ([ALL] | MAX ([ALL] | MIN ([ALL] | CAST ( | UPPER ( | GEN_ID (generator, } — 51 — Примеры Следующая инструкция создает таблицу с UNIQUE ограничением и на уровне столбца и на уровне таблицы: FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY), CHECK (MIN_SALARY < MAX_SALARY)); Следующая инструкция создает таблицу с вычисляемым столбцом fio (фамилия_имя_ отчество по отдельным полям fam, im,ot): CREATE TABLE spisok ( id INTEGER NOT NULL, fam VARCHAR(30), im VARCHAR(30), ot VARCHAR(30), fio COMPUTED BY (fam||” “||im” “||ot), PRIMARY KEY (id)); В следующей инструкции первый столбец сохраняет тип сортировки, установленный по умолчанию для базы данных. Второй столбец имеет отличный тип сортировки, а определение третьего столбца включает, и кодировку и порядок сортировки. CREATE TABLE BOOKADVANCE (BOOKNO CHAR(6), TITLE CHAR(50) COLLATE ISO8859_1, EUROPUB CHAR(50) CHARACTER SET ISO8859_1 COLLATE FR_FR); 6.8. Изменение структуры таблицы Команда ALTER TABLE дает возможность изменить структуру существующей таблицы. Одиночная инструкция ALTER TABLE может выполнить множественные добавления и удаления. Синтаксис ALTER TABLE <имя таблицы> (<операция> [, < операция > ...]; < операция > = {ADD < определение столбца > | ADD <ограничение на таблицу> | DROP <имя столбца> | DROP CONSTRAINT <имя ограничения>} — 52 — Инструкция ADD позволяет добавить в таблицу поле или ограничение, при этом используется тот же синтаксис, что и описанный в предыдущем параграфе при определении полей. Инструкция DROP удаляет поле или ограничение. 7. Определение прав доступа к данным При инсталляции сервера СУБД InterBase для системного администратора СУБД в списке пользователей регистрируется запись с именем SYSDBA и паролем masterkey. Он обладает полными правами на СУБД. В реальной практике следует сразу же изменить пароль администратора (можно изменить и имя, но это обычно не делается). Администратор может создавать записи для других пользователей. Для этого в SQL добавлены команды, которые иногда рассматривают как отдельный раздел SQL и называют его «язык безопасности данных». Рассмотрим эти команды в варианте SQL для СУБД InterBase. ! Передать привилегии на таблицу другому пользователю можно командой GRANT <список привилегий> ON <название таблицы> TO <имя пользователя> [WITH GRANT OPTION] Возможны следующие привилегии: SELECT – разрешается делать запросы к таблице, то есть обеспечивается доступ к чтению таблицы INSERT – разрешается добавлять записи в таблицу DELETE - разрешается удалять записи из таблицы UPDATE – разрешается редактировать записи в таблице REFERENCES – разрешается использовать поля данной таблицы для организации вторичного ключа в другой таблице. Если администратор желает дать пользователю все привилегии к таблице, можно вместо списка привилегий указывать ALL. Если администратор желает дать всем пользователям некоторую привилегию или список привилегий, вместо имени пользователя достаточно указать PUBLIC. Если в конце команды GRANT указано WITH GRANT OPTION, то пользователь, получивший привилегии к таблице, получает право давать такие же привилегии в дальнейшем другим пользователям. Например, администратор может дать разработчику право на создание и работу с таблицей, а тот, в свою очередь, дает право пользователям работать с этими таблицами. — 53 — Командой GRANT <список привилегий> ON <название таблицы> FROM <имя пользователя> можно лишить пользователя ранее данной ему привилегии. Для удобства администрирования введено понятие «роль». Роль – это список привилегий для нескольких таблиц базы данных. CREATE ROLE <имя роли>; GRANT <список привилегий> TO <имя роли>; CONNECT “ database” USER “ username” PASSWORD “ password” ROLE “ rolename”; GRANT <имя роли> ON <название таблицы> TO <имя пользователя>; GRANT < privileges> ON [TABLE] { tablename } TO <имя роли>; < privileges> = {ALL [PRIVILEGES] | < privilege_list>} < privilege_list> = { SELECT| DELETE| INSERT| UPDATE [( col [, col …])]| REFERENCES [( col [, col …])][, < privilege_list> …]}} GRANT UPDATE (CONTACT, PHONE) ON CUSTOMERS TO PUBLIC; Только для Update и References 8. Некоторые особенности SQL в Interbase В этой главе описаны некоторые частные случаи, расширения и добавления к SQL, которые связаны со спецификой СУБД Interbase. |