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

Разновидность языка, применяемая в конкретной СУБД, называется диалектом SQL. Например, диалект СУБД Oracle называется PL/SQL; в MS SQL Server и DB2 применяется диалект Transact-SQL; в Interbase и Firebird – isql. Каждый диалект SQL совместим до определенной степени со стандартом SQL, но может иметь отличия и специфические расширения языка, поэтому для выяснения синтаксиса того или иного SQL-оператора следует в первую очередь смотреть Help конкретной СУБД.

Для операций над базами данных и таблицами в стандарте SQL предусмотрены операторы:

Ниже приводится синтаксис этих операторов по стандарту SQL92. Поскольку их синтаксис в СУБД может отличаться от стандарта, при выполнении лабораторной работы рекомендуется обращаться к справочной системе СУБД.

Имена объектов базы данных (таблиц, столбцов и др.) могут состоять из буквенно-цифровых символов и символа подчеркивания. Специальные символы (@$# и т.п.) обычно указывают на особый тип таблицы (системная, временная и др.). Не рекомендуется использовать в именах национальные (русские) символы, пробелы и зарезервированные слова, но если они всё же используются, то такие имена следует писать в кавычках ".." или в квадратных скобках [..].

Далее при описании конструкций операторов SQL будут использоваться следующие обозначения: в квадратных скобках [ ] записываются необязательные части конструкции; альтернативные конструкции разделяются вертикальной чертой |; фигурные скобки {} выделяют логические блоки конструкции; многоточие … указывает на то, что предшествующая часть конструкции может многократно повторяться. «Раскрываемые» конструкции записываются в угловых скобках < >.

Создание базы данных

CREATE DATABASE Имя_базы_данных

Удаление одной и более баз данных

DROP DATABASE Имя_базы_данных [, Имя_базы_данных …]

Объявление текущей базы данных

USE Имя_базы_данных –- в SQL Server и MySQL

SET DATABASE Имя_базы_данных – в Firebird

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

CREATE TABLE Имя_таблицы (

<описание_столбца> [, < описание_столбца> |

< ограничение_целостности_таблицы> …]

)

<описание_столбца> ®

Имя_столбца ТИП [DEFAULT значение_по_умолчанию ]

[NOT NULL]

{[UNIQUE | PRIMARY KEY]}

|

{[REFERENCES Имя_таблицы (Имя_столбца)]

[ON DELETE

{NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE

{NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

}

[CHECK (условие_проверки)]

ТИП столбца может быть либо стандартным типом данных (см. таблицу 1), либо именем домена (см. п.6.2).

Некоторые СУБД позволяют создавать вычислимые столбцы (computed columns). Это виртуальные столбцы, значение которых не хранится в физической памяти, а вычисляется сервером СУБД при всяком обращении к этому столбцу по формуле, заданной при объявлении этого столбца. В формулу могут входить значения других столбцов этой строки, константы, встроенные функции и глобальные переменные.

Описание вычислимого столбца в SQL Server имеет вид:

<описание_столбца> ® Имя_столбца AS выражение

Описание вычислимого столбца в Firebird имеет вид:

<описание_столбца> ® Имя_столбца COMPUTED BY < выражение>

СУБД MySQL 3.23 вычислимые столбцы не поддерживает.

<ограничение_целостности_таблицы> ®

CONSTRAINT Имя_ограничения_целостности

{UNIQUE|PRIMARY KEY}(список_столбцов_образующих_ключ)

|FOREIGN KEY (список_столбцов_FK)

REFERENCES Имя_таблицы (список_столбцов_PK)

[ON DELETE

{NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE

{NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

|CHECK (условие_проверки)

Некоторые СУБД допускают объявление врéменных таблиц (существующих только во время сеанса). В SQL Server имена временных таблиц должны начинаться с символа # (локальные временные таблицы, видимые только создавшему их пользователю) или ## (глобальные таблицы, видимые всем пользователям); в MySQL для создания временных таблиц используется ключевое слово TEMPORARY, например:

CREATE TEMPORARY TABLE … (далее синтаксис см. CREATE TABLE).

Изменение структуры таблицы

Используется для изменения типа столбцов существующих таблиц, добавления и удаления столбцов и ограничений целостности.

ALTER TABLE Имя_таблицы

-- изменение типа столбца (в SQL Server и Firebird)

ALTER COLUMN Имя_столбца новый_ТИП

-- изменение типа, имени и ограничений столбца (в MySQL)

CHANGE COLUMN Имя_столбца < описание_столбца>

-- добавление обычного или вычислимого столбца

|ADD < описание_столбца>

-- добавление ограничения целостности

|[WITH CHECK|WITH NO CHECK] ADD

< ограничение_целостности_таблицы >

-- удаление столбца

|DROP COLUMN Имя_столбца

-- удаление ограничения целостности

|DROP CONSTRAINT Имя_ограничения_целостности

-- включение или отключение проверки ограничений целостности

-- в MS SQL Server

|{CHECK|NO CHECK} CONSTRAINT

{ Список_имен_ограничений_целостности |ALL}

Удаление таблицы

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

Далее рассмотрим, как при создании новых таблиц командой CREATE TABLE или изменении структуры существующих таблиц командой ALTER TABLE объявить декларативные ограничения целостности (подробнее они описаны в п.4.2).

1. Обязательное наличие данных (NULL–значения)

Объявляется словом NULL (столбец может иметь пустые ячейки) или NOT NULL (столбец обязательный). По умолчанию принимается NULL.

Пример создания таблицы[7]:

CREATE TABLE Clients(

ClientName NVARCHAR (60) NOT NULL,

DateOfBirth DATE NULL,

Phone CHAR (12)); -- по умолчанию тоже NULL

2. Значение по умолчанию (DEFAULT)

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

Рассмотрим пример создания таблицы Orders (Заказы). Столбец OrderDate принимает по умолчанию значение текущей даты, а столбец Quantity (количество) по умолчанию равен 0.

CREATE TABLE Orders(

OrderNum INT NOT NULL, -- номер заказа

OrderDate DATETIME NOT NULL -- дата заказа

DEFAULT GetDate(),

-- функция GetDate() возвращает текущую дату[8]

Quantity SMALLINT NOT NULL -- кол-во товара, DEFAULT 0);

3. Объявление первичных ключей (PRIMARY KEY)

Простой первичный ключ объявляется словами PRIMARY KEY при создании таблицы. Например,

CREATE TABLE Staff(-- таблица "Работники"

TabNum INT PRIMARY KEY, -- первичный ключ

WName NVARCHAR (40) NOT NULL, -- ФИО

... -- описание прочих столбцов);

Составной первичный ключ объявляется иначе:

-- способ 1 (объявление PK при создании таблицы)

CREATE TABLE Clients(

PasSeria NUMERIC (4,0) NOT NULL ,--серия паспорта

PasNumber NUMERIC (6,0) NOT NULL ,--номер паспорта

Name NVARCHAR (40) NOT NULL,

Phone CHAR (12),

-- объявление составного первичного ключа

CONSTRAINT Clients_PK

PRIMARY KEY (PasSeria,PasNumber));

-- способ 2(PK объявляется после создания таблицы)

-- сначала создаем таблицу без PK

CREATE TABLE Clients(

PasSeria NUMERIC (4,0) NOT NULL,-- серия паспорта

PasNumber NUMERIC (6,0) NOT NULL,-- номер паспорта

ClientName NVARCHAR (40) NOT NULL,

Phone CHAR (12));

-- модификация таблицы – добавляем РК

ALTER TABLE Clients

ADD CONSTRAINT Clients_PK

PRIMARY KEY (PasSeria,PasNumber);

4. Уникальность столбцов (UNIQUE)

Подобно Primary Key указывает, что столбец или группа столбцов не могут содержать повторяющихся значений, но не являются PK. Все столбцы, объявленные UNIQUE, должны быть NOT NULL. Пример объявления простого уникального столбца:

CREATE TABLE Students(

SCode INT PRIMARY KEY, -- суррогатный РК

FIO NVARCHAR (40) NOT NULL, -- ФИО

RecordBook CHAR (6) NOT NULL UNIQUE); -- № зачетки

Пример объявления составного уникального поля:

CREATE TABLE Staff(-- таблица "Работники"

TabNum INT PRIMARY KEY, -- табельный номер

WName NVARCHAR (40) NOT NULL, -- ФИО

PasSeria NUMERIC (4,0) NOT NULL, -- серия паспорта

PasNumber NUMERIC (6,0) NOT NULL, -- номер паспорта

-- объявление составного уникального поля

CONSTRAINT Staff_UNQ UNIQUE (PasSeria,PasNumber));

5. Ограничения на значения столбца (CHECK)

Это ограничение позволяет указать диапазон, список или «маску» логически допустимых значений столбца.

Пример создания таблицы Workers (Работники):

CREATE TABLE Workers(

-- табельные номера 4-значные

TabNum INT PRIMARY KEY

CHECK (TabNum BETWEEN 1000 AND 9999),

Name VARCHAR (60) NOT NULL, -- ФИО сотрудника

-- пол – буква 'м' или 'ж'

Gentry CHAR (1) NOT NULL

CHECK (Gentry IN ('м','ж')),

-- возраст не менее 14 лет

Age SMALLINT NOT NULL CHECK (Age>=14),

--№ свидет-ва пенсионного страхования (по маске)

PensionCert CHAR (14)

CHECK (PensionSert LIKE ' '));

В этом примере показаны разные типы проверок. Диапазон допустимых значений указывается конструкцией BETWEEN … AND; обычные условия (как для столбца Age) используют знаки сравнений =, <>, >, >=, <, <=, связанные при необходимости логическими операциями AND, OR, NOT (например, Age >=14 AND Age <=70); для указания списка допустимых значений используется предикат IN и его отрицание NOT IN; конструкция

LIKE маска_допустимых_значений EXCEPT список_исключений

используется для задания маски допустимых значений строковых столбцов. В маске применяются два спецсимвола: «%» – произвольная подстрока, и ­«_» – любой единичный символ. Конструкция EXCEPT является необязательной.

В условии отбора CHECK могут сравниваться значения двух столбцов одной таблицы и столбцы разных таблиц.