Московский государственный университет имени М.В.Ломоносова
Опубликован: 05.01.2004 | Доступ: свободный | Студентов: 9474 / 1246 | Оценка: 4.04 / 3.84 | Длительность: 10:03:00
ISBN: 978-5-94774-539-9
Лекция 2:

Основы SQL

< Лекция 1 || Лекция 2: 12 || Лекция 3 >

Применение языка SQL

Подключение к СУБД

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

Первым шагом в любом случае следует выполнить подключение к СУБД. Например, CONNECT TO MyDB1 USER User1/Password1;.

Фраза TO специфицирует источник данных, с которым устанавливается соединение. Фраза USER определяет имя и пароль пользователя, который будет работать с базой данных.

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

Перед началом работы с данными должны быть выполнены следующие действия:

  • разработана модель базы данных и на ее основании создана схема базы данных - все взаимосвязанные таблицы;
  • в каждую созданную таблицу должны быть введены данные.

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

Для создания таблицы используется оператор CREATE TABLE , имеющий в стандарте SQL92 следующее формальное описание:

CREATE [ [ { GLOBAL | LOCAL } ] TEMPORARY] 
    TABLE имя_таблицы
    ( { column | [table_constraint] } . , .. 
    [ ON COMMIT { DELETE | PRESERVE} ROWS ] );

column определяется как

имя_поля {domain | datatype [size]}
    [column_constraint:]
    [ DEFAULT default_value ]
    [ COLLATE collate_value ]

Фразы GLOBAL TEMPORARY или LOCAL TEMPORARY указывают на создание временной таблицы.

Фраза ON COMMIT может быть указана только для временных таблиц. По умолчанию для временных таблиц подразумевается фраза ON COMMIT DELETE ROWS.

После имени таблицы в круглых скобках через запятую указывается список полей (называемых также столбцами) и ограничений. Каждое поле имеет имя и тип (datatype). Тип может быть определен как любой допустимый тип языка SQL или как домен. Например, язык SQL допускает такие типы как : integer, char (число_символов), varchar (число_символов), int, smallint, float, date.

Фраза DEFAULT определяет значение по умолчанию. Это значение имеет более высокий приоритет, чем значение по умолчанию, указанное в домене (если вместо типа данных используется домен).

Ограничения для таблицы ( table_constraint ) и ограничения для столбца ( column_constraint ), называемые также ограничениями целостности, накладывают определенные условия на вводимые в таблицу данные.

Ограничения для столбца указываются непосредственно после описания столбца, а ограничения для таблицы - через запятую после описания любого столбца.

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

Ограничения для столбца могут указываться следующими фразами:

  • NOT NULL - в любой добавляемой или изменяемой строке столбец всегда должен иметь значение, отличное от NULL .
  • UNIQUE - все значения столбца должны быть уникальны.
  • PRIMARY KEY - устанавливает один столбец как первичный ключ и одновременно подразумевает, что все значения столбца будут уникальны.
  • CHECK ( condition ) - указываемое в скобках условие использует для сравнения значение столбца и возвращает TRUE, FALSE или UNKNOWN. Если при попытке выполнения SQL-оператора возвращаемое значение равно FALSE, то оператор выполнен не будет.
  • REFERENCES table ( fields_list ) - ограничение требует совпадения значений столбцов данной таблицы с указанными столбцами родительской таблицы.

Ограничения для таблицы могут указываться следующими фразами:

  • CHECK ( condition ) - указываемое в скобках условие использует для сравнения значение столбца и возвращает TRUE, FALSE или UNKNOWN. Если при попытке выполнения SQL-оператора возвращаемое значение равно FALSE, то оператор выполнен не будет.
  • FOREIGN KEY ( fields_list ) - это ограничение по внешнему ключу аналогично ограничению REFERENCES для столбцов и гарантирует, что все значения, указанные во внешнем ключе, будут соответствовать значениям родительского ключа, обеспечивая ссылочную целостность. Следует отметить, что типы данных столбцов, используемых в этом ограничении, должны совпадать, а типы таблиц (постоянная базовая таблица, глобальная временная таблица, локальная временная таблица) родительского и внешнего ключа - соответствовать друг другу.

Стандарт SQL92 позволяет устанавливать режим контроля ограничений как перед выполнением каждого SQL-оператора, так и в конце текущей транзакции. В последнем случае допускается нарушение ограничения целостности внутри транзакции. Этот режим очень полезен для внесения данных в таблицы, связанные ограничением REFERENCES .

Определение ограничений для таблицы

Объявление ограничений имеет в стандарте SQL92 следующее формальное описание:

table_constraint определяется как

[ CONSTRAINT constraint_name ]
{ PRIMARY KEY (имя_поля .,:) }
| { UNIQUE (имя_поля .,:) } 
| { FOREIGN KEY (имя_поля .,:) }
{ REFERENCES имя_таблицы [(имя_поля .,:)] 
  [ref_specification] }
| { CHECK (condition) }
[[ NOT ] DEFFERABLE ]

column_constraint определяется как

[ CONSTRAINT constraint_name ]
{ NOT NULL } | { PRIMARY KEY } | UNIQUE
| { REFERENCES имя_таблицы [(имя_поля .,:) ]
    [ref_specification]}
| { CHECK (condition) }
| [ INITIALLY DEFFERED | INITIALLY IMMEDIATE ]
[[ NOT ] DEFFERABLE ]

ref_specification определяется как

[ MATCH {FULL | PARTIAL } ]
[ ON UPDATE
   { CASCADE | SET NULL | SET DEFAULT | 
     NO ACTION } ]
[ ON DELETE
   { CASCADE | SET NULL | SET DEFAULT | 
     NO ACTION } ]

Ссылочная спецификация ( ref_specification ) определяет для ограничений FOREIGN KEY и REFERENCES тип совпадения и действия, выполняемые при внесении изменений в родительский ключ. Совпадение может быть определено как MATCH FULL (полное совпадение) или MATCH PARTIAL (частичное совпадение).

При полном совпадении значения родительского и внешнего ключей должны полностью совпадать, или все значения внешнего ключа должны быть NULL.

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

Если тип совпадения не указан, то предполагается что любое значение внешнего ключа присутствует в родительском ключе, но при этом во внешнем ключе допускаются значения NULL (частично или полностью).

Рассмотрим таблицу tbl1, для которой определен родительский ключ как столбцы f2 и f3, и таблицу tbl2 с внешним ключом по столбцам c1 и c2. Если таблицы tbl1 tbl2 имеют следующие значения:

tbl1 tbl2
f1 f2 f3 c0 c1 c2 c3
1 11 100 1 NULL NULL ff
2 12 200 2 11 NULL gg
3 13 300 3 11 100 hh
4 13 300 4 NULL 200 ii

то при указании фразы MATCH FULL ограничение ссылочной целостности допустит ввести во вторую таблицу только первую и третью строки (первая содержит в качестве значений внешнего ключа значение NULL, а третья - внешний ключ, полностью совпадающий с родительским). При указании фразы MATCH PARTIAL ограничение ссылочной целостности допустит ввести во вторую таблицу все указанные строки (первую строку - как допустимое несовпадающее значение, вторую и четвертую - как совпадающие с частичным значением NULL, третью - как уникально совпадающую).

Действия, выполняемые при внесении ограничений в родительский ключ, могут быть указаны фразами ON UPDATE и ON DELETE.

Фразы ON UPDATE и ON DELETE могут иметь одну из следующих опций:

  • CASCADE - распространение изменений, произведенных в родительском ключе, на совпадающие строки внешнего ключа (для MATCH PARTIAL - только на уникально совпадающие строки).
  • SET NULL - значения внешнего ключа изменяются на NULL по следующим правилам: для MATCH FULL - заменяются все значения внешнего ключа; для MATCH PARTIAL - в уникально совпадающих строках заменяются значения только тех столбцов, значения которых в родительском ключе были изменены; если тип совпадения не указан, то заменяются значения только тех столбцов, значения которых в родительском ключе были изменены.
  • SET DEFAULT - значения внешнего ключа изменяются на значение по умолчанию по тем же правилам, что и для фразы SET NULL , но при типе соответствия MATCH FULL заменяются значения только тех столбцов внешнего ключа, которые уникально соответствуют родительскому ключу (значение NULL внешнего ключа не заменяется).

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

Фраза DEFFERABLE в ссылочной спецификации указывает отсроченную проверку ограничения до конца транзакции. Фраза NOT DEFFERABLE определяет, что контроль ссылочной целостности будет выполняться при каждом операторе INSERT , DELETE и UPDATE .

Режим проверки ограничения - сразу или с отсрочкой - устанавливается в начале каждого сеанса, а затем он может быть изменен SQL-оператором SET CONSTRAINTS MODE или локально фразами DEFFERABLE и NOT DEFFERABLE.

< Лекция 1 || Лекция 2: 12 || Лекция 3 >