Управление данными. Язык SQL. (Лекция 6) презентация

Управление данными Лекция 6. Язык SQL

Рассматриваемые темы Язык SQL. Его назначение. Подмножества языка DDL и DML. Операторы DDL: Create, Alter, Drop. Операторы DML: Insert, Update, Delete, Select. Управление правами пользователя Grant, Revoke. Реализация специальной логики приложений – триггеры и хранимые процедуры. Расширения ANSI SQL.

Язык SQL Язык структурированных запросов (Structured queries language); Текстовый язык, семантически приближенный к английскому языку; Каждый запрос – отдельная команда для СУБД (оператор) с фиксированным синтаксисом. Имеет подмножества операторов DDL (определение данных) и DML (манипулирование данными)

DDL Операторы создания, изменения и удаления баз данных и объектов схемы данных Создание: CREATE <OBJECT> <NAME> [параметры] Типы объектов: DATABASE – база данных; SCHEMA – схема данных; TABLE – таблица (отношение); CONSTRAINT – ограничение; ATTRIBUTE – атрибут; VIEW – представление; INDEX – индекс; SEQUENCE – последовательность; STORED PROCEDURE – хранимая процедура; TRIGGER – триггер; USER – пользователь БД.

DDL. Создание таблиц CREATE TABLE <NAME> (<attributes> [,<constraints>]) <attribute>: name <datatype> [<constraint>] <constraint>: CONSTRAINT [name] <c_type> <parameters>

DDL. Типы данных атрибутов Числовые: Счетчик – counter, serial, auto_increment Целое – integer (+ unsigned) Длинное целое – long (+ unsigned) C плавающей запятой – float, double Логический – bit, boolean, smallint Строковые Один символ – char Строка n символов – char[n], varchar[n] Дата, время – date, time, datetime, timestamp Бинарные данные – (long-)(var-)binary

DDL. Ограничения Default <val> – принимать значение по умолчанию; Not Null – запрет на отсутствие значений Unique – запрет повторов Primary key – первичный ключ (not null + unique) Foreign key references <table> (<PK attribute>) <mode> – внешний ключ (ссылка) Check <condition> – требование соблюдать условие

DDL. Пример создания таблицы

DDL. Изменение объекта ALTER <object> <name> [действия по изменению] Alter table (add column, alter column, drop column) – изменение таблицы Alter view – изменение представления Alter database – изменение базы данных Alter procedure – изменение процедуры

DDL. Удаление объекта DROP <object> <name>

DDL. Порядок создания и удаления объектов схемы: Создается пользователь; Создается база данных; Создается схема; Создаются последовательности; Создаются таблицы (сначала родительские, потом дочерние); Создаются индексы, триггеры и процедуры; Создаются представления. Удаление – в обратном порядке.

DML Операторы манипулирования данными: Извлечение данных – SELECT; Вставка новых данных – INSERT; Изменение данных – UPDATE; Удаление данных – DELETE; Объект работы – отношение (таблица) или соединение отношений Единица манипулирования – запись

DML. Оператор SELECT Оператор предназначен для извлечения из отношения или соединения отношений набора записей, отвечающих заданным условиям. Результат работы – новое отношение. Реализует все операции реляционной алгебры (объединение, пересечение, проекция, соединение, выборка)

DML. Оператор SELECT Формат: SELECT <список атрибутов> FROM <соединяемые отношения> [WHERE <условия выборки>] [ORDER BY <критерии сортировки>] [GROUP BY <критерии группировки>] [HAVING <условия отбора групп>]

DML. Оператор SELECT <список атрибутов>: Реализует проекцию РА. Указываются имена тех атрибутов, извлекаемых из соединенных отношений, которые войдут в результат. Для одноименных атрибутов указывается отношение, из которого он извлекается (table.attribute) Порядок вхождения определяется порядком перечисления. Если нужно включить все атрибуты, указывается *. Для переименования атрибута в результирующем отношении применяется ключевое слово AS (attribute as new_name); Вместо атрибута в результат может вставляться результат, возвращаемый функцией (sin(angle) as “sine of angle”); Для запрета повторений в результате используется директива DISTINCT

DML. Оператор SELECT <соединяемые отношения>: Реализует соединение РА. Указываются имена соединяемых отношений, декартово произведение которых формирует результат. Для удобства записи каждому отношению может быть присвоен псевдоним (name synonim1, synonim2) Перечисление отношений: Простое перечисление: FROM table1, table2, … Полное декартово произведение; Соединение по условию: FROM table1 [INNER | LEFT | RIGHT | FULL] JOIN table2 on <conditon> Декартово произведение, содержащее только строки, отвечающие условию

Примеры соединения:

Примеры соединения:

DML. Оператор SELECT <условия выборки>: Реализует выборку РА. Указывается одно логическое выражение, которому должны удовлетворять все записи соединенного отношения. Записи, не удовлетворяющие условию, отбрасываются. Допускаются логические связки AND, OR, NOT. Основные условия: Для чисел и дат: <, >, >=, <= , BETWEEN min AND max; Для строк: like ‘pattern’ – сравнение с образцом (_, %); Для всех типов: =, <> IS NULL, IS NOT NULL - для всех типов (нельзя = NULL!); IN (set or subquery), EXISTS (subquery)

Примеры условий SELECT Фамилия, Курс FROM СТУДЕНТ WHERE Специальность=’Математика’ AND Курс=5 SELECT Фамилия FROM СТУДЕНТ WHERE Специальность IN (’Математика’, ’Экономика’) SELECT Фамилия, Специальность FROM СТУДЕНТ WHERE НомерСтудента BETWEEN 200 AND 300 SELECT Фамилия, Специальность FROM СТУДЕНТ WHERE НомерСтудента >= 200 AND НомерСтудента <= 300 SELECT Фамилия AS ‘ФИО’ FROM СТУДЕНТ WHERE Фамилия LIKE ‘Р%’ SELECT Фамилия FROM СТУДЕНТ WHERE Курс IS NULL

Примеры условий на соединение SELECT Фамилия FROM СТУДЕНТ WHERE НомерСтудента IN (SELECT НомерСтудента FROM ЗАПИСЬ WHERE Предмет = ‘А’) SELECT Фамилия FROM СТУДЕНТ WHERE СТУДЕНТ.НомерСтудента IN (SELECT ЗАПИСЬ.НомерСтудента FROM ЗАПИСЬ WHERE ЗАПИСЬ.Предмет IN (SELECT ЗАНЯТИЯ. Предмет FROM ЗАНЯТИЯ WHERE ЗАНЯТИЯ.ДеньНедели = 2)) SELECT СТУДЕНТ.НомерСтудента, СТУДЕНТ.Фамилия, ЗАПИСЬ.Предмет FROM СТУДЕНТ, ЗАПИСЬ WHERE СТУДЕНТ.НомерСтудента = ЗАПИСЬ.НомерСтудента SELECT НомерСтудента, Предмет, ДеньНедели FROM СТУДЕНТ, ЗАПИСЬ, ЗАНЯТИЯ WHERE СТУДЕНТ.НомерСтудента = ЗАПИСЬ.НомерСтудента AND ЗАПИСЬ.Предмет = ЗАНЯТИЯ. Предмет AND СТУДЕНТ.Фамилия = ‘Сидоров’

DML. Оператор SELECT <критерии сортировки>: Указываются имена атрибутов, по значениям которых требуется упорядочить записи в результате. При указании более одного атрибута – лексикографическая сортировка. Для каждого атрибута может быть задано свое направление сортировки: ASC – по возрастанию DESC – по убыванию Вместо имени атрибута может быть указан его порядковый номер в результате Если направление сортировки не указано, сортировка производится по возрастанию

Примеры сортировки SELECT Фамилия, Специальность, Курс FROM СТУДЕНТ WHERE Специальность=’Экономика’ ORDER BY Фамилия SELECT Фамилия, Специальность, Курс FROM СТУДЕНТ WHERE Курс IN (1, 2, 4) ORDER BY Фамилия ASC, 3 DESC

DML. Оператор SELECT Агрегирующие функции При необходимости запрос может вернуть не сами записи, а их агрегированные величины: COUNT – количество значений поля SUM – сумма значений поля MIN – минимальное значение поля MAX – максимальное значение поля AVG – среднее (арифметическое) значение поля STDDEV – стандартное отклонение поля В этом случае всегда возвращается ОДНА запись. НЕЛЬЗЯ в один запрос вставлять поле и агрегированную величину: SELECT COUNT([DISTINCT] ФАМИЛИЯ), SUM(СТИПЕНДИЯ) FROM СТУДЕНТЫ SELECT ФАМИЛИЯ, COUNT(ИМЯ) FROM СТУДЕНТЫ

DML. Оператор SELECT <критерии группировки>: Указываются имена атрибутов, одинаковые значения которых образуют одинаковую групповую запись в результате. При использовании группировки для каждой группы можно вычислить агрегированное значение (SUM, COUNT etc.). Группировать можно только по тем атрибутам, которые указаны после SELECT, а не по любым атрибутам соединяемых отношений.

DML. Оператор SELECT <условия отбора групп>: Указываются требования, которым должны удовлетворять сформированные группы, чтобы быть отобранными в результат. Если группа не удовлетворяет условию, она вся отбрасывается. При использовании в одном запросе секций WHERE и HAVING сначала выполняется WHERE (отбор записей), потом GROUP BY (группировка), а потом – HAVING (отбраковка групп).

Примеры группировки SELECT Специальность, COUNT(*) FROM СТУДЕНТ GROUP BY Специальность SELECT Специальность, COUNT(*) FROM СТУДЕНТ GROUP BY Специальность HAVING COUNT(*) > 2 SELECT Специальность, MAX(НомерСтудента) FROM СТУДЕНТ WHERE Курс = 4 GROUP BY Специальность HAVING COUNT(*) > 1

Реализация теоретико-множественных операций Объединение R1R2: (SELECT * FROM R1) UNION (SELECT * FROM R2) Пересечение R1R2: SELECT * FROM R1 WHERE IN (SELECT * FROM R2) Разность R1\R2: SELECT * FROM R1 WHERE NOT IN (SELECT * FROM R2) Симметрическая разность R1R2: (SELECT * FROM R1 WHERE NOT IN (SELECT * FROM R2)) UNION (SELECT * FROM R2 WHERE NOT IN (SELECT * FROM R1)) Декартово произведение R1R2: SELECT * FROM R1, R2

DML. Оператор UPDATE Оператор предназначен для изменения в отношении (или соединении отношений, если это допускает БД) набора записей, отвечающих заданным условиям. Изменяются указанные в запросе поля записей. Результат работы – отношение с измененными записями. Сам оператор никакого результата не возвращает (нельзя использовать в SELECT). При обновлении могут срабатывать триггеры, а так же выполняться обновления значений внешних ключей в ссылающихся таблицах. Запрос может быть не исполнен, если новое значение будет нарушать ограничения.

DML. Оператор UPDATE UPDATE <Name> SET <field=val [, field=val, …]> [WHERE <Condition>]

Примеры обновления UPDATE Факультеты f INNER JOIN Кафедры k ON f.ID = k.FacID SET f.Бюджет = 0 WHERE k.Выпускающая=true UPDATE persons SET street = 'Nissestien 67', city = 'Sandnes' WHERE lastname = 'Tjessem' AND firstname = 'Jakob‘ UPDATE emp a SET deptno = (SELECT deptno FROM dept WHERE loc = ‘BOSTON’), (sal, comm) = (SELECT 1.1*AVG(sal), 1.5*AVG(comm) FROM emp b WHERE a.deptno = b.deptno) WHERE deptno IN (SELECT deptno FROM dept WHERE loc = ‘DALLAS’ OR loc = ‘DETROIT’) UPDATE sales SET SaleDate=Null, Count=0

DML. Оператор DELETE Оператор предназначен для удаления в отношении (или соединении отношений, если это допускает БД) набора записей, отвечающих заданным условиям. Записи удаляются целиком (нельзя удалить часть записи). Результат работы – отношение с удаленными записями. Сам оператор никакого результата не возвращает (нельзя использовать в SELECT) При удалении могут срабатывать триггеры, а так же выполняться «об-null-ивание» значений внешних ключей в ссылающихся таблицах; Удаление ссылающихся записей в ссылающихся таблицах

DML. Оператор DELETE DELETE FROM <Name> [WHERE <Condition>]

Примеры удаления DELETE FROM products WHERE price = 10; DELETE FROM products; DELETE FROM Authors a JOIN Articles b ON a.ID=b.Author WHERE AuthorLastName='Henry';

DML. Оператор INSERT Оператор предназначен для вставки в отношение одной или более записей. Записи вставляются целиком (нельзя вставить часть записи). Результат работы – отношение с добавленными записями. Сам оператор никакого результата не возвращает (нельзя использовать в SELECT) При вставке записей могут срабатывать триггеры Запись(и) может быть не вставлена, если нарушается условие на ее значения. В качестве источника записей может быть использован оператор SELECT Неуказанные в запросе поля принимают значение DEFAULT

DML. Оператор INSERT INSERT INTO <Name> ([<Col>, ... ]) VALUES (<val>,...) INSERT INTO <Name> VALUES (<val>,...) INSERT INTO <Name> SELECT <cols> FROM <tables> WHERE <Condition>

Примеры вставки INSERT INTO films VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes') INSERT INTO films (code, title, did, date_prod, kind) VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama') INSERT INTO films VALUES ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes') INSERT INTO films (code, title, did, date_prod, kind) VALUES ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'); INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

Операторы управления пользователем БД CREATE USER <username> ALTER DATABASE <name> SET OWNER=<username> GRANT <privilege> ON <name> TO <username> REVOKE <privilege> ON <name> FROM <username> DROP USER <username>

Привилегии пользователя SELECT INSERT UPDATE DELETE REFERENCES TRIGGER ALL PRIVILEGES

Управление транзакциями Транзакция – последовательность логически связанных запросов, целенаправленно и логически связанно меняющих состояние БД; У транзакции имеется начало, набор точек сохранения (отката) и конец. В конце транзакцию можно применить (зафиксировать) или откатить В процессе исполнения транзакции, до ее завершения (фиксации или отката) объекты, которыми на манипулирует, могут быть «захвачены»

Операторы управления транзакциями: BEGIN – применяется для того, чтобы: Зафиксировать, что транзакция началась Указать (при необходимости), какие объекты захватываются и уровень их блокировки SAVEPOINT <NAME> Указывает точку возврата, к которой можно откатиться при частичном откате транзакции RELEASE SAVEPOINT <NAME> Удаление успешно пройденной точки возврата

Операторы управления транзакциями: COMMIT – применяется для того, чтобы: сделать «постоянными» все изменения, сделанные в текущей транзакции (реально данные могут быть изменены несколько позже) очистить все точки сохранения данной транзакции завершить транзакцию освободить все блокировки данной транзакции

Операторы управления транзакциями: ROLLBACK – применяется для того, чтобы: отменить все изменения, внесённые начиная с момента начала транзакции или с какой-то точки сохранения (SAVEPOINT). очистить все точки сохранения данной транзакции завершить транзакцию освободить все блокировки данной транзакции

Примеры:

Хранимые процедуры и триггеры Используются для реализации сложной бизнес-логики (положений делового регламента, не описываемых ограничениями); Хранятся на сервере СУБД Пишутся на расширенном языке SQL, содержащем специальные операторы: Передача управления (CALL, GO TO, RETURN) Проверка условий (IF … ELSE, SWITCH) Организация циклов (FOR, WHILE)

Хранимые процедуры Вызываются приложением, как запрос с использованием ключевого слова EXECUTE или CALL; Могут иметь аргументы и возвращать результат (в том числе – отношение, как и SELECT); Аргументы могут быть входящие и исходящие; Могут выполнить любое количество запросов на SQL, в том числе – несколько транзакций; Результаты внутренних запросов SELECT обрабатываются в виде курсоров

Курсор Курсор – временная структура данных (аналог таблицы), хранящий результаты запроса SELECT построчно Предназначен для обработки в процедурах Имеет операции OPEN – открыть курсор FETCH – перейти к очередной записи CLOSE – закрыть курсор Бывают однонаправленные и реверсивные курсоры

Пример хранимой процедуры

Триггеры Хранимые процедуры, привязываемые к таблицам, и вызываемые при ее изменении: Вставка, удаление и/или изменение записей Триггеры бывают: Табличные – вызывается при изменении для всей таблицы 1 раз при изменении; Строчные – вызывается при изменении для каждой записи; У одной таблицы может быть несколько триггеров, одна и та же процедура может выполнять роль разных триггеров. Триггеры могут быть «до» и «после»-триггеры. Триггер имеет доступ как к старым (до изменения) так и новым (после изменения) данным.

Пример триггера CREATE OR REPLACE TRIGGER DistrictUpdatedTrigger AFTER UPDATE ON district BEGIN INSERT INTO info VALUES ('table "district" has changed'); END; /* Триггер на уровне строки */ CREATE OR REPLACE TRIGGER DistrictUpdatedTrigger AFTER UPDATE ON district FOR EACH ROW BEGIN INSERT INTO info VALUES ('one string in table "district" has changed'); END;