Презентация "Управление данными. Язык SQL"


Подписи к слайдам:
Слайд 1

Управление данными

  • Лекция 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. Пример создания таблицы

  • (пример для СУБД PostgreSQL)

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>
    • Декартово произведение, содержащее только строки, отвечающие условию

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

  • 1) SELECT * FROM table1, table2
  • 2) SELECT * FROM table1 a INNER JOIN table2 b ON a.num=b.num

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

  • 3) SELECT * FROM table1 a XXX JOIN table2 b ON a.num=b.num
  • XXX = LEFT XXX = RIGHT XXX = FULL

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>]
  • Name – имя обновляемой таблицы, или их соединение (join)*
  • Field – имя обновляемого поля
  • Val – присваиваемое полю значение (может быть выражение, в том числе, использующее СТАРОЕ значение поля)
  • 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>]
  • Name – имя таблицы или соединение (join)*
  • 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>
  • Name – имя таблицы
  • Col – имя столбца (поля)
  • Val – значение поля во вставляемой записи
  • SELECT – запрос, извлекающий набор записей, использующихся для вставки

Примеры вставки

  • 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
  • GRANT SELECT ON mytable TO PUBLIC;
  • GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
  • GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
  • REVOKE INSERT ON films FROM PUBLIC;
  • REVOKE ALL PRIVILEGES ON kinds FROM manuel;

Управление транзакциями

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

Операторы управления транзакциями:

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

Операторы управления транзакциями:

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

Операторы управления транзакциями:

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

Примеры:

  • BEGIN;
  • INSERT INTO table1 VALUES (1);
  • SAVEPOINT my_savepoint;
  • INSERT INTO table1 VALUES (2);
  • ROLLBACK TO SAVEPOINT my_savepoint;
  • INSERT INTO table1 VALUES (3);
  • COMMIT;
  • BEGIN;
  • INSERT INTO table1 VALUES (3);
  • SAVEPOINT my_savepoint;
  • INSERT INTO table1 VALUES (4);
  • RELEASE SAVEPOINT my_savepoint;
  • COMMIT;

Хранимые процедуры и триггеры

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

Хранимые процедуры

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

Курсор

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

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

  • CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
  • a_running_job_count INTEGER;
  • PRAGMA AUTONOMOUS_TRANSACTION;
  • BEGIN
  • LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
  • SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
  • IF a_running_job_count > 0 THEN COMMIT;
  • raise_application_error(-20000, 'Unable to create a new job: a job is currently running.');
  • END IF;
  • DELETE FROM cs_active_job;
  • INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
  • BEGIN INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
  • EXCEPTION WHEN dup_val_on_index THEN NULL;
  • END;
  • COMMIT;
  • END;

Триггеры

  • Хранимые процедуры, привязываемые к таблицам, и вызываемые при ее изменении:
    • Вставка, удаление и/или изменение записей
  • Триггеры бывают:
    • Табличные – вызывается при изменении для всей таблицы 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;