Ивановский государственный энергетический университет им В И Ленина

Ивановский государственный энергетический университет им. В. И. Ленина SQL Унифицированный язык для работы с БД Лектор: Булатова Е. Е. ИГЭУ 2010

История § В 1970 г. компания IBM разработала экспериментальную реляционную СУБД - IBM System. R, для которой был создан специальный язык обработки данных SEQUEL (Structured English QUEry Language), позднее SQL. § В 1979 г. появилась СУБД Oracle, использующая SQL. § В 1982 году фирма IBM стала поставлять коммерческий продукт DB 2. § В 1986 г. стандарт языка SQL был принят ANSI. Фирма SYBASE предложила механизм триггеров, положив начало бизнес-логике в теле сервера БД. Благодаря большой популярности (IBM, Oracle) и тщательной проработке SQL приобрел статус стандарта реляционного языка манипулирования данными. 3

Операторы модификации Создать Изменить Удалить Базу данных / таблицу / процедуру / триггер и т. д. CREATE ALTER DROP Строку INSERT UPDATE DELETE 4

Создание БД. Синтаксис CREATE DATABASE <имя базы> ON [PRIMARY] ( NAME = <имя файла>, FILENAME = <путь к файлу>, [SIZE = <размер файла>], [MAXSIZE = <максимальный размер файла>], [FILEGROWTH = <приращение размера файла>] ) LOG ON ( NAME = <имя журнала>, FILENAME = <путь к журналу>, [SIZE = <размер файла журнала>], [MAXSIZE = <максимальный размер файла журнала>], [FILEGROWTH = <приращение размера файла журнала>]); 5

Создание БД. Пример CREATE DATABASE Test ON PRIMARY ( NAME = N'test', FILENAME = N‘c: test. mdf' , SIZE = 3072 KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% ) LOG ON ( NAME = N'test_log', FILENAME = N'c: test_log. ldf' , SIZE = 1024 KB , MAXSIZE = 2048 GB , FILEGROWTH = 10% ); 6

Создание таблицы. Синтаксис CREATE TABLE <имя таблицы> ( { <имя поля> <тип> [NOT NULL] [UNIQUE] [DEFAULT <значение>] }* [ PRIMARY KEY (<список полей>) ] */ Первичный ключ /* [ UNIQUE (<список полей>) */ Альтернативный ключ /* [ { FOREIGN KEY (<список полей>) */ Вторичный ключ /* [ REFERENCES <имя таблицы> [(<список полей>)] [ON DELETE <правило>] [ON UPDATE <правило>] }* ], [CHECK <условие>] ); , где <правило> : : = RESTRICTED / CASCADE / SET NULL / SET DEFAULT 7

Создание таблицы. Пример CREATE TABLE DETAIL ( DETAIL_CODE char(3), DETAIL_NAME char(10), DETAIL_WEIGHT integer NOT NULL, MATERIAL_CODE char(3), PRIMARY KEY (DETAIL_CODE), FOREIGN KEY (MATERIAL_CODE) REFERENCES MATERIAL (MATERIAL_CODE) ON DELETE RESTRICTED ON UPDATE RESTRICTED, CHECK (DETAIL_WEIGHT>0) ); 8

Реструктуризация таблицы ALTER TABLE <имя таблицы> { [ADD <определение столбца>] | [ALTER <определение столбца>] | [DROP <имя столбца>] | [ADD CONSTRAINT <ограничение>] | [DROP CONSTRAINT <имя ограничения>] }*; 9

Реструктуризация таблицы. Пример § ALTER TABLE DETAIL ADD PRICE REAL DEFAULT 0; § ALTER TABLE ORDER DROP ORDER_DATE; § ALTER TABLE SUPPLIER ADD CONSTRAINT REGION FOREIGN KEY (CITY_ID) REFERENCES CITY (CITY_ID); 10

Удаление БД / таблицы DROP DATABASE <имя таблицы> ; § DROP DATABASE warehouse; DROP TABLE <имя таблицы> ; § DROP TABLE supplier ; 11

Пользовательский тип данных CREATE DOMAIN < имя > < тип > CHECK <условие>; Пример: § CREATE DOMAIN D_WEIGHT INTEGER CHECK (VALUE BETWEEN 10 AND 100); 12

Добавление одной записи (строки) INSERT [INTO] <имя таблицы> [(<список полей>)] VALUES (<список значений>); § INSERT MATERIAL VALUES (‘ 2’, ‘Сталь’); § INSERT detail (detail_name) VALUES ('Подшипник'); § INSERT supplier (supplier_type, supplier_name) VALUES (DEFAULT, 'Смирнов С. С. '); 13

Добавление нескольких записей (строк) INSERT [INTO] <имя таблицы> [ (<список полей>)] <запрос>; § INSERT SUPPLY (DETAIL_CODE) SELECT DETAIL_CODE FROM DETAIL WHERE (MATERIAL_CODE in (‘M 10’, ‘M 11’, ‘M 12‘); 14

Изменение записей UPDATE <таблица> SET <столбец> = <значение> [, <столбец> = <значение>] [FROM <таблица(ы) источников>] [WHERE <условие>] § UPDATE detail SET weight = 210 § UPDATE delivery SET delivery_date = delivery_date + 1 WHERE department_id > 3 15

Удаление записей DELETE <таблица> [WHERE] <условие> § DELETE detail WHERE detail_id IN (2, 5, 8) § DELETE detail WHERE weight IS NULL § DELETE detail 16

Выборка данных. Синтаксис SELECT [DISTINCT] { * | <имя столбца> [AS <новое имя>] } FROM {<имя таблицы>}* [WHERE <условие>] [GROUP BY <список полей>] [HAVING <условие>] [ORDER BY <список полей> [DESC]]; 17

Выборка данных. Пример 18

Создание вида / представления CREATE VIEW <имя вида> [<список полей>] AS ; Пример: § CREATE VIEW BIG_DETAIL AS SELECT * FROM DETAIL d WHERE d. DETAIL_WEIGHT > 50; 19

Работа с видом Вся работа с видом приравнивается к работе с обыкновенной таблицей: § SELECT d. DETAIL_NAME, d. DETAIL_WEIGHT FROM BIG_DETAIL d WHERE d. DETAIL_NAME=‘Болт’; 20

Вид - группировка CREATE VIEW ORDER_G ( o. DETAIL_CODE, o. ORDER_QUANTITY ) AS SELECT o. DETAIL_CODE, SUM (o. ORDER_QUANTITY) FROM ORDER o GROUP BY DETAIL_CODE; DETAIL_ID ORDER_QUANTITY D 1 800 D 2 1100 D 3 400 D 4 230 21

Объявление прав доступа. Синтаксис § GRANT {<привилегии> | ALL} ON { <имя таблицы> | <имя вида> } TO {PUBLIC | <имя пользователя>}; § DENY {<привилегии> | ALL} ON { <имя таблицы> | <имя вида> } TO {PUBLIC | <имя пользователя>}; § REVOKE {<привилегии> | ALL} ON { <имя таблицы> | <имя вида> } FROM {PUBLIC | <имя пользователя>}; 22

Объявление прав доступа. Пример GRANT SELECT ON DETAIL TO ‘admin’; Привилегия ALL INSERT Т а б л и ц а UPDATE DENY DELETE ON ORDER TO ‘New_user’; DELETE SELECT REFERENCES BACKUP DATABASE CREATE TABLE REVOKE ALL ON ORDER FROM PUBLIC; CREATE DATABASE Б Д CREATE FUNCTION CREATE PROCEDURE EXECUTE Ф 23

Создание индекса CREATE [UNIQUE] INDEX <имя индекса> ON <имя таблицы> ({<имя поля> [ASC/DESC]}*); Пример: § CREATE INDEX idx 1 ON SUPPLIER (SUPPLIER_NAME); SUPPLIER_NAME Alias SUPPLIER_ID SUPPLIER_NAME RATING Абрамов П. О. 2 1 Воронин Л. Д. 45 Березкин М. С. 3 2 Абрамов П. О. 789 Воронин Л. Д. 1 3 Березкин М. С. 34 24

Объявление транзакций COMMIT TRAN[SACTION] [ transaction_name]; ROLLBACK TRAN[SACTION] [ transaction_name]; BEGIN TRANSACTION; UPDATE Client SET Account = Account - 100 WHERE Client_ID = 1; UPDATE Client SET Account = Account + 100 WHERE Client_ID = 2; COMMIT TRANSACTION; 25

Хранимая процедура. Синтаксис CREATE PROCEDURE <имя процедуры> [{@ <имя параметра> <тип> [= значение параметра по умолчанию] [OUTPUT]}*]; AS ; Вызов: EXEC[UTE] <имя процедуры> [{<имя входного параметра> =<значение>}] 26

Хранимая процедура. Пример CREATE PROCEDURE sp_date_supp @start DATETIME, @end DATETIME AS SELECT s. supplier_name, d. detail_name, o. order_quantity FROM supplier s INNER JOIN order o ON s. supplier_code = o. supplier_code INNER JOIN detail d ON o. detail_code = d. detail_code WHERE o. order_date BETWEEN @start AND @end 27

Хранимая процедура. Вызов EXECUTE sp_date_supp ’ 01. 2004’, ’ 31. 01. 2004’; или EXECUTE sp_date_supp @start= ’ 01. 2004’, @end=’ 31. 01. 2004’; supplier_name detail_name order_quantity Иванов Шайба 300 Иванов Гайка 150 Семенов Гайка 240 28

Триггер. Синтаксис CREATE TRIGGER <Имя_триггера> ON <Имя_таблицы_или_вида> {FOR | AFTER | INSTEAD OF} {[INSERT] [, ] [UPDATE] [, ] [DELETE]} AS 29

Алгоритм обработки триггеров На сервер поступает команда модификации таблицы, например INSERT Да Триггер INSTEAD OF существует? Нет Заполнение таблиц INSERTED и DELETED Проверка ограничений целостности таблицы Выполнение тела триггера Выполнение операции модификации таблицы в оперативной памяти сервера Да Триггер FOR | AFTER существует? Заполнение таблиц INSERTED и DELETED Выполнение тела триггера Физическая фиксация изменений в таблице (фиксация транзакции) Конец 30

CREATE TRIGGER tr_supply_fi ON supply FOR INSERT AS UPDATE detail SET d. detail_quantity = d. detail_quantity + i. supply_quantity FROM detail d JOIN INSERTED i ON d. detail_id = i. detail_id 31