Основные операторы языка определения данных

Основные операторы языка определения данных

Ядро языка определения данных образуют три команды:

· CREATE (создать), позволяющая определить и создать объект базы данных;

· DROP (удалить), служащая для удаления существующего объекта базы данных;

· ALTER (изменить), с помощью которой можно изменить определение объекта базы данных.

------------------------------------------------------------------------------------------------------

CREATE TABLE tаble_name

{(colum_name dataType [NOTNULL] [UNIQUE]

[DEFAULT default_option ][CHECK (search_condition)] [, …]

[PRIMARYKEY (key_columns), ]

[UNIQUE (list_of_columns), ] [,…]

[CONSTRAINT constr_name CHECK (search_condition)][,…]

[FOREIGNKEY (list_of_foreign_key_columns)

REFERENCES Parent_Table_Name [ (list_of_candidate_key_coluims) ],

[MATCH {PARTIAL | FULL} ]

[ON UPDATE referential_action ]

[ON DELETE referential_action ][ ,… ]

)};

clientNo VARCHAR(5) NOT NULL,

propertyNo VARCHAR(5) NOT NULL,

UNIQUE (clientNo, propertyNo)

Ссылочная целостность

В конструкции FOREIGN KEY оператора CREATE TABLE:

[FOREIGNKEY (list_of_foreign_key_columns)

REFERENCES Parent_Table_Name [ (list_of_candidate_key_coluims) ],

[MATCH {PARTIAL | FULL} ]

[ON UPDATE referential_action ]

[ON DELETE referential_action ][ ,… ]

------------------------------------------------------------------------------------------------------

CREATE DOMAIN domain_name [AS] dataType

[DEFAULT default_option]

[CHECK (search_condition)];

------------------------------------------------------------------------------------------------------

Примеры:

CREATE DOMAIN sex_type AS CHAR

DEFAULT ' м '

CHECK (VALUE IN (' м ', ' ж '));

CREATE DOMAIN BranchNumber AS VARCHAR(4)

CHECK (VALUE IN(SELECT branchNo FROM Персонал));

CREATE DOMAIN salary AS NUMERIC(7, 2)

DEFAULT1000.00

CHECK(VALUE BETWEEN 1000.00AND10000.00 )

CONSTRAINT sal_not_null CHECK(VALUE IS NOT NULL);

------------------------------------------------------------------------------------------------------

ALTER DOMAIN domain_name

[SET default_definition |DROP DEFAULT]

[ADD domain_constr_definition | DROP CONSTRAINT constr_name ];

Примеры:

ALTER DOMAIN salary SET DEFAULT1100.00;.

ALTER DOMAIN salary DROP DEFAULT;

ALTER DOMAIN salary ADD CHECK(VALUE <> 1500.00);

ALTER DOMAIN salary DROP CONSTRAINT sal_not_null;

------------------------------------------------------------------------------------------------------

DROP DOMAIN domain_name [RESTRICT | CASCADE];

------------------------------------------------------------------------------------------------------

Пример версии оператора CREATE TABLE.

CREATE DOMAIN OwnerNumber AS VARCHAR(5)

CHECK(VALUE IN (SELECT ownerNo FROM Владелец_Недвижимости));

CREATE DOMAIN StaffNumber AS VARCHAR(5)

CHECK (VALUE IN (SELECT staffNo FROM Персонал));

CREATE DOMAIN BranchNumber AS CHAR(4)

CHECK(VALUE IN (SELECT branchNo FROM Отделение));

CREATE DOMAIN PropertyNumber AS VARCHAR(5);

CREATE DOMAIN Street AS VARCHAR(25);

CREATE DOMAIN City AS VARCHAR(15);

CREATE DOMAIN Postcode AS VARCHAR(5);

CREATE DOMAIN PropertyType AS CHAR(l)

CHECK(VALUE IN('B', 'C', 'D', 'E', 'F', 'M', 'S'));

CREATE DOMAIN PropertyRooms AS SMALLINT;

CHECK (VALUEBETWEEN 1 AND 15);

CREATE DOMAIN PropertyRent AS DECIMAL(6,2)

CHECK VALUE BETWEEN0 AND 9999.99);

CREATE TABLE Объекты_Недвижимости (

propertyNo PropertyNumber NOT NULL,

street Street NOT NULL,

city CityNOT NULL,

postcode Postcode,

type PropertyType NOT NULL DEFAULT 'Кв.',

rooms PropertyRoomsNOT NULL DEFAULT 4,

rent PropertyRentNOT NULL DEFAULT 600,

ownerNo OwnerNumberNOT NULL,

staffNo StaffNumber

CONSTRAINT StaffNotHandlingTooMuch

CHECK(NOT EXISTS (SELECT staffNo

FROM Объекты_Недвижимости

GROUP BY StaffNo

HAVING COUNT(*)>30)),

branchNo BranchNumber NOT NULL,

PRIMARY KEY (propertyNo),

FOREIGN KEY (staffNo) REFERENCES Персонал ON DELETE SET NULL ON UPDATE CASCADE,

FOREIGN KEY (ownerNo) REFERENCES Владелец_Недвижимости ON DELETE NO ACTION ON UPDATE CASCADE,

FOREIGN KEY(branchNo) REFERENCES Отделение ON DELETE NO ACTION ON UPDATE CASCADE);

------------------------------------------------------------------------------------------------------

DROP TABLE table_name [RESTRICT | CASCADE];

------------------------------------------------------------------------------------------------------

Пример:

DROP TABLE Объекты_Недвижимости;

------------------------------------------------------------------------------------------------------

ALTER TABLE TableName

[ADD[COLUMN] column_name dataType [NOT NULL] [UNIQUE]

[DEFAULT default_option ] [CHECK (search_condition)]]

[DROP [COLUMN] column_name [RESTRICT | CASCADE]],

[ADD [CONSTRAINT constraint_name ] table_constraint_definitian ]

[DROP CONSTRAINT constraint_name [RESTRICT | CASCADE]]

[ALTER[COLUMN] column_name SET DEFAULT default_option ]

[ALTER [COLUMN] column_name DROP DEFAULT];

------------------------------------------------------------------------------------------------------

Пример:

ALTER TABLE Персонал

ALTER position DROP DEFAULT;

ALTER TABLE Персонал

ALTER sex SET DEFAULT ' ж ';

ALTER TABLE Объекты_Недвижимости

DROP CONSTRAINT staff_not_handling_toomuch;

ALTER TABLE Клиенты

ADD prefNoRooms PropertyRooms;

------------------------------------------------------------------------------------------------------

CREATE VIEW View_Name [ ( new_column_name [ ,… ]) ]

AS subselect [WITH[CASCADED | LOCAL] CHECK OPTION];

------------------------------------------------------------------------------------------------------

Пример 1. Создание горизонтального представления.

CREATE VIEW Руководитель_Персонала

AS SELECT *

FROM Персонал

WHERE branchNo ='В003';

SELECT * FROM Руководитель_Персонала;

Таблица 1. Данные, доступные в представлении Руководитель_Персонала

staffNo	fName	lName	position	sex	DOB	salary	branchNo
SS1	Петр	Сидоренко	руководитель	м	17.10.1965		B003
SS2	Сергей	Ветров	пом. руководителя	м	10.10.1978		B003

Пример 2. Создание вертикального представления

CREATE VIEW Персонал_1

AS SELECT staffNo, fName, lName, position, sex

FROM Персонал

WHERE branchNo= 'B003';

CREATE VIEW Персонал_1

AS SELECT staffNo, fName, lName, position, sex

FROM Руководитель_Персонала;

Таблица 2. Данные, доступные в представлении Персонал_1

staffNo	fName	lName	position	sex
SS1	Петр	Сидоренко	руководитель	м
SS2	Сергей	Ветров	пом. руководителя	м

Пример 3. Пример представления, формируемого путем группирования и соединения.

CREATE VIEW Персонал_Объекты (branchNo, staffNo, cnt)

AS SELECT s.branchNo, s.staffNo, COUNT(*)

FROM Персонал s, Объекты_Недвижимости p

WHERE s.staffNo = p.staffNo

GROUP BY s.branchNo, s,staffNo;

Таблица 3. Данные, доступные в представлении Персонал_Объекты

staffNo	branchNo	cnt
SK2	B005
SS1	B003
SS2	B003
SKh2	B001
SKh3	B001
SK3	B007

Использование конструкции WITH CHECK OPTION

CREATE VIEW Руководитель_Персонала

AS SELECT *

FROM Персонал

WHERE branchNo ='В003'

WITH CHECK OPTION;

CREATE VIEW LowSalary CREATE VIEW HighSalary

AS SELECT * AS SELECT *

FROM Персонал FROM LowSalary

WHERE salary > 2000 WHERE salary > 2700;

WITH LOCAL CHECK OPTION;

CREATE VIEW Руководитель_Персонала

AS SELECT *

FROM HighSalary

WHEREbranchNo = 'B003';

------------------------------------------------------------------------------------------------------

DROP VIEW View_Name [RESTRICT | CASCADE];

------------------------------------------------------------------------------------------------------

Пример:

DROP VIEW Руководитель_Персонала;

Дата добавления: 2015-08-29