Слайд 1Базы данных. Язык SQL.
Москва
2011
НИЯУ "МИФИ"
Слайд 21.1
Основы проектирования запросов SQL
Результатом запроса SELECT всегда является таблица.
Данные, возвращаемые
запросом содержат данные из таблиц БД, или результаты операций над
ними.
Как правило один и тот же набор результирующих данных может быть получен различными способами.
Скорость двух эквивалентных с точки зрения результата запросов может различаться на порядок.
Слайд 31.2
Основы проектирования запросов SQL
Манипулятивная часть языка SQL (DML) является декларативным
языком программирования.
Процедурные возможности, как правило, являются СУБД-зависимыми (Transact SQL, PL/SQL)
Запросы
могут быть объединены в хранимую процедуру для повторных вызовов.
Курсоры позволяют выполнить процедурные действия над отдельными строками, возвращенными запросом
Слайд 41.1
Основы проектирования запросов SQL
Предложение SELECT
SELECT список_вывода
FROM источники
WHERE условие_отбора_строк
GROUP BY список_для_группирования
HAVING
условие_отбора_групп
ORDER BY список_для_упорядочивания
SELECT определяет совокупность колонок результирующей таблицы;
WHERE определяет дополнительные
условия, накладываемые на строки;
GROUP BY позволяет сгруппировать строки по определенному признаку для последующей обработки;
HAVING позволяет из всех групп выбрать некоторые группы;
ORDER BY позволяет упорядочить строки результирующей таблицы;
FROM определяет совокупность источников данных;
Слайд 51.1
Основы проектирования запросов SQL
Порядок обработки запроса
Шаг 1 (FROM). Выделяются
таблицы и представления, из которых выбираются нужные данные.
Шаг 2
(WHERE). Из выбранных таблиц выбираются строки, удовлетворяющие указанному условию отбора.
Шаг 3 (GROUP BY). Выбранные строки объединяются в группы (группируются) по указанным признакам.
Шаг 4 (HAVING). Из созданных групп выделяются группы, удовлетворяющие условию отбора групп. В качестве условия могут использоваться только атрибуты, участвующие в групировке
Шаг 5 (SELECT). Из выбранных групп в соответствии со списком вывода выделяются и обрабатываются необходимые данные, создавая строки и столбцы результирующей таблицы
Слайд 61.1
Основы проектирования запросов SQL
Порядок обработки запроса
SELECT [DISTINCT] список_вывода
список_вывода -
определяет колонки в результирующей таблице
DISTINCT – исключает вывод дубликатов
имя_таблицы.имя_колонки –
уточненное имя.
* или имя_таблицы.* - получение всех колонок таблицы.
элемент AS новое_имя – задат новое имя колонки
Слайд 71.1
Основы проектирования запросов SQL
Порядок обработки запроса
SELECT [DISTINCT] список_вывода
Пример1:
SELECT *
FROM DEPARTMENT
DEPARTMENT
Слайд 81.1
Основы проектирования запросов SQL
Порядок обработки запроса
SELECT [DISTINCT] список_вывода
Пример2:
SELECT Dep_id,
Dep_name
FROM DEPARTMENT
Пример3:
SELECT Dep_id as D, Dep_name as N
FROM DEPARTMENT
SELECT
DISTINCT Manager_id as M_ID
FROM DEPARTMENT
Пример4:
Слайд 91.1
Основы проектирования запросов SQL
Конструкция FROM,
FROM элемент1, элемент2, …
Элементом может быть:
Ссылка
на таблицу.
Соединение таблиц.
Вложенное табличное выражение.
Ссылка на таблицу:
имя_таблицы [[AS] корреляционное имя
]
Имя таблицы существующую таблица базы данных.
Корреляционное имя – псевдоним таблицы в рамках данного запроса.
Пример:
SELECT D1.Dep_id, D2.Dep_name
FROM Department D1, Department D2
РЕЗУЛЬТАТ ?
Слайд 101.1
Основы проектирования запросов SQL
Соединение таблиц
Соединение таблиц:
элемент1 [тип_соединения] JOIN элемент2
ON условие_соединения
Элементом может быть:
Ссылка на таблицу.
Соединение таблиц.
Вложенное табличное
выражение.
Тип соединения:
внутреннее (INNER)
внешнее (OUTER)
левое (LEFT)
правое (RIGHT)
полное (FULL)
Слайд 111.1
Основы проектирования запросов SQL
Особенности внешнего соединения
RIGHT JOIN объединяет все записи
из правой таблицы с результатом соединения левой и правой таблиц.
LEFT
JOIN объединяет все записи из левой таблицы с результатом соединения левой и правой таблиц.
FULL JOIN объединяет все записи из левой и правой таблиц с результатом соединения левой и правой таблиц.
Слайд 121.1
Основы проектирования запросов SQL
Соединение таблиц
Соединение таблиц:
элемент1 [тип_соединения] JOIN элемент2
ON условие_соединения
Условие соединения имеет ограничения:
1. В условии поиска
должны быть колонки только тех таблиц, которые участвуют в соединении.
2. Условие соединения не может содержать никаких подзапросов.
Пример
T1
T2
Слайд 131.1
Основы проектирования запросов SQL
Соединение таблиц
Внутреннее соединение –
T1 INNER JOIN
T2 ON W = Y
T1
T2
Левое внешнее соединение –
T1 LEFT
OUTER JOIN T2 ON W=Y
Правое внешнее соединение –
T1 RIGHT OUTER JOIN T2 ON W=Y
Слайд 141.1
Основы проектирования запросов SQL
Соединение таблиц
Полное внешнее соединение –
T1 FULL
OUTER JOIN T2 ON W=Y
T1
T2
Пример нескольких соединений:
TB1 LEFT JOIN
TB2 ON TB1.C1=TB2.C1 RIGHT JOIN
TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1 ON TB1.C1=TB3.C1
(TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1) RIGHT JOIN
(TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1) ON TB1.C1=TB3.C1
Слайд 151.1
Основы проектирования запросов SQL
Вложенное табличное выражение (подзапрос)
Подзапрос:
(подзапрос) [AS] корреляционное_имя [(имя_колонки,
…)]
Пример вложенного табличного выражения:
SELECT T1.A, T2.B FROM T1, (SELECT
… FROM TT) AS T2…
Пример внутреннего подзапроса:
SELECT T1.A FROM T1 WHERE EXISTS (SELECT … FROM TT)
Слайд 161.1
Основы проектирования запросов SQL
Вложенное табличное выражение (подзапрос)
Пример: получить информацию об
отделах и сотрудниках, используя подзапрос.
SELECT
FROM Dept D,
(Select Emp_id
as Emp_id, Salary S from EMP) As E
D.Dept_name, E.Emp_id, E.S
Dept
Emp
WHERE D.Dept_id=E.Dept_id
Слайд 171.1
Основы проектирования запросов SQL
Вложенное табличное выражение (подзапрос)
Типы подзапросов
Основной запрос
Подзапрос
Однострочный подзапрос
Продажи
Возвращает
Основной
запрос
Подзапрос
Кадры
Продажи
Возвращает
Многострочный подзапрос
Слайд 181.1
Основы проектирования запросов SQL
Вложенное табличное выражение (подзапрос)
Однострочный подзапрос
Свойства
Возвращает одну строку
Использует
однострочные
операторы сравнения: (=; >; >=;
Emp_id, Dep_id, salary
FROM emp
WHERE salary >
(SELECT salary
FROM emp
WHERE emp_id = 20);
Emp
Слайд 191.1
Основы проектирования запросов SQL
Вложенное табличное выражение (подзапрос)
Однострочный подзапрос
SELECT Emp_id, Dep_id,
salary
FROM emp
WHERE salary =
(SELECT MIN(salary)
FROM emp);
Emp
100
SELECT Emp_id, Dep_id, salary
FROM emp
WHERE salary =
(SELECT MIN(salary)
FROM emp
GROUP BY Dep_id);
Ошибочное использование однострочного подзапроса:
Слайд 201.1
Основы проектирования запросов SQL
Вложенное табличное выражение (подзапрос)
Многострочный подзапрос
Свойства
Возвращает более чем
одну строку
Использует многострочные
операторы сравнения: (IN, ANY, ALL)
SELECT Emp_id, Dep_id,
salary
FROM emp
WHERE salary
(SELECT salary
FROM emp
WHERE Dep_id = 2);
Emp
200,250
РЕЗУЛЬТАТ ?
Слайд 211.1
Основы проектирования запросов SQL
Вложенное табличное выражение (подзапрос)
Многострочный подзапрос
SELECT Emp_id, Dep_id,
salary
FROM emp
WHERE salary
(SELECT salary
FROM emp
WHERE Dep_id = 2);
Emp
200,250
РЕЗУЛЬТАТ ?
SELECT Emp_id, Dep_id, salary
FROM emp
WHERE Dep_ID NOT IN
(SELECT Dep_id
FROM emp
WHERE Dep_id = 1);
Слайд 221.1
Основы проектирования запросов SQL
Вложенное табличное выражение (подзапрос)
Пример: получить информацию об
отделах и сотрудниках, используя подзапрос.
Emp
SELECT E.Emp_id
FROM EMP E
WHERE Salary >
(Select salary from emp where emp_id=20)
Слайд 231.1
Основы проектирования запросов SQL
Конструкция WHERE
SELECT список_вывода
FROM источники
WHERE условие_отбора_строк
GROUP BY список_для_группирования
HAVING
условие_отбора_групп
ORDER BY список_для_упорядочивания
WHERE условие_поиска
В результате использования WHERE, будут получены только
те строки, для которых условие_поиска истенно.
Условие поиска имеет ограничения:
Имя колонки имя должно уникально идентифицировать колонку исходной таблицы .
В условии поиска конструкции WHERE НЕ МОГУТ!!! быть использованы агрегатные функции
Слайд 241.1
Основы проектирования запросов SQL
Конструкция GROUP BY
SELECT список_вывода
FROM источники
WHERE условие_отбора_строк
GROUP BY
список_для_группирования
HAVING условие_отбора_групп
ORDER BY список_для_упорядочивания
GROUP BY элемент [, элемент …]
Используется когда
в список вывода конструкции SELECT включаются, в том числе, и выражения, содержащие агрегатные функции.
Элемент представляется в виде некоторого выражения группирования, содержащего имена колонок и используемого для определения группы строк.
Каждому выражению списка вывода, не содержащему агрегатную функцию, должно соответствовать выражение группирования. Т.е. выражения к которым не применяется агрегатная функция должны присутствовать в GROUP BY.
Слайд 251.1
Основы проектирования запросов SQL
Конструкция HAVING
SELECT список_вывода
FROM источники
WHERE условие_отбора_строк
GROUP BY список_для_группирования
HAVING
условие_отбора_групп
ORDER BY список_для_упорядочивания
HAVING условие_поиска
Синтаксические правила условие_поиска соответствует WHERE.
В условии поиска
HAVING допускается использование имен колонок, идентифицирующих каждую группу таблицы, и агрегатных функций, применяемых к полученным группам.
SELECT DeptID, SUM(Salary)
FROM Emp
WHERE dep_id=1 and
SUM(Salary) >100
GROUP BY DeptID
SELECT DeptID, SUM(Salary)
FROM Emp
GROUP BY DeptID
HAVING SUM(Salary) >100
Слайд 261.1
Основы проектирования запросов SQL
Конструкция HAVING
SELECT список_вывода
FROM источники
WHERE условие_отбора_строк
GROUP BY список_для_группирования
HAVING
условие_отбора_групп
ORDER BY список_для_упорядочивания
ORDER BY ключ_сортировки [направление_сортировки] , …
В качестве ключа_сортировки
могут быть указаны имя колонки или простое целое.
Направление_сортировки
По возрастанию(ASC)
по умолчанию
По убыванию(DESC)
Слайд 271.1
Основы проектирования запросов SQL
Теоретико-множественные операции
UNION или UNION ALL –
для операции объединения,
MINUS – для операции вычитания,
INTERSECT – для операции
пересечения.
Операция UNION
Возвращает результат объединения двух запросов, исключая дубликаты.
SELECT Dep_id
FROM Dep
UNION
SELECT Dep_id
FROM Emp
--------
1
2
3
Слайд 281.1
Основы проектирования запросов SQL
Теоретико-множественные операции
Операция UNION ALL
Возвращает результат объединения
двух запросов, включая дубликаты.
SELECT Dep_id
FROM Dep
UNION ALL
SELECT Dep_id
FROM
Emp
--------
1
1
2
3
Слайд 291.1
Основы проектирования запросов SQL
Теоретико-множественные операции
Операция INTERSECT
Возвращает строки, которые присутствуют
в обоих запросах.
SELECT Dep_id
FROM Dep
WHERE Dep_id 3
INTERSECT
SELECT Dep_id
FROM
Emp
--------
1
2
Слайд 301.1
Основы проектирования запросов SQL
Теоретико-множественные операции
Операция MINUS
Возвращает строки первого запроса,
которые отсутствуют во втором
SELECT Dep_id
FROM Dep
MINUS
SELECT Dep_id
FROM Emp
WHERE
Dep_id NOT IN(1,2)
--------
1
2
3
Слайд 311.9
Основы проектирования запросов SQL
Агрегирующие функции:
Пример:
Посчитать количество оформленных накладных.
SELECT COUNT(idНакладная) as 'Количество
накладных'
FROM Накладная
COUNT - количество, MAX - максимальное значение,
MIN - минимальное
значение, SUM - сумма значений,
AVG - среднее значение
Слайд 321.10
Основы проектирования запросов SQL
Правила использования агрегирующих функций:
Запрещается:
1) Вложенное использование агрегирующих
функций.
SELECT MAX(COUNT(Количество))
FROM Товар_в_Накладной
2) Использование агрегирующих функций в явном виде в
секции WHERE.
SELECT *
FROM Товар_в_Накладной
WHERE Количество > AVG(Количество)
Слайд 331.12
Основы проектирования запросов SQL
Правила использования агрегирующих функций:
1) Если в секции
SELECT используется агрегирующая функция, то все остальные атрибуты, выводящиеся с
помощью запроса должны быть включены в секцию GROUP BY
SELECT Клиент_idКлиент, Дата, COUNT(idНакладная) as
'Количество накладных'
FROM Накладная
GROUP BY Клиент_idКлиент, Дата
Слайд 341.13
Основы проектирования запросов SQL
Фильтрация групп:
HAVING
Пример:
Вывести номера клиентов
с количеством накладных более 2.
SELECT Клиент_idКлиент
FROM Накладная
GROUP BY Клиент_idКлиент
HAVING COUNT(idНакладная)>2
Слайд 351.21
Основы проектирования запросов SQL
Использование коррелированных подзапросов в секции WHERE:
Вывести номера
клиентов с количеством накладных для клиентов, которые совершили первую покупку
после 24 сентября 2009 года.
SELECT idКлиент, COUNT(idНакладная) as
'Количество накладных'
FROM Накладная RIGHT JOIN Клиент ON
Накладная.Клиент_idКлиент = Клиент.idКлиент
WHERE NOT EXISTS (
SELECT idНакладная
FROM Накладная
WHERE Клиент_idКлиент = Клиент.idКлиент AND Дата < '09/24/2009')
GROUP BY Клиент.idКлиент
Слайд 361.1
Основы проектирования запросов SQL
Создание представлений
Ограниченный доступ
к данным
Упрощенное создание сложных запросов
Независимость
от данных
Представление по разному одних и тех же данных
Представление это
объект БД на основе одной или нескольких таблиц и/или других представлений.
Преимущества представлений
Слайд 371.1
Основы проектирования запросов SQL
Создание представлений
CREATE VIEW имя_представления [(имя_колонки, …)] AS
запрос
имя_представления – именует представление
имя_колонки – именует колонку представления.
AS
– указывает начало определения представления.
запрос – определяет представление.
Слайд 381.1
Основы проектирования запросов SQL
Создание представлений
Пример 1
CREATE VIEW MA_PROJ
AS
SELECT
* FROM PROJECT
WHERE SUBSTR(ProjNo, 1, 2) = ’MA’
Пример
2
CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
AS
SELECT d.department_name,
MIN(e.salary),
MAX(e.salary),
AVG(e.salary)
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
GROUP BY d.department_name;
Слайд 39Процедурные элементы Transact SQL
Переменные
DECLARE @var1
SET @var1 =
SELECT @var1
= Ai
Условные операторы
IF(
Слайд 40Триггеры как инструмент ограничения целостности в
Transact SQL
7.2
Триггер — хранимая процедура,
автоматически вызываемая системой при наступлении заданного события.
Основные DML события: INSERT,
UPDATE, DELETE.
Таблица inserted: вставляемые данные.
Таблица deleted: удаляемые данные.
время
Вызов DML операции
Фиксация данных
instead of триггер
after триггер
Слайд 41Триггеры как инструмент ограничения целостности в
Transact SQL
7.2
CREATE TRIGGER имя_триггера
ON
имя_целевого_объекта
время_активации_триггера
контролируемая операция
тело_триггера
Общий вид создания триггера
имя_триггера – определяет имя триггера
имя_целевого_объекта – задает целевую таблицу
время_активации_триггера – определяет этап срабатывания триггера: INSTEAD OF, AFTER
контролируемая операция – определяет на какое SQL-событие срабатывает триггер
Тело триггера указывает действие, которое должно быть выполнено при активации триггера
Слайд 42Триггеры как инструмент ограничения целостности в
Transact SQL
7.3
INSTEAD OF:
Изменения к таблице
еще не применены. Данные могут нарушать текущие ограничения.
Изменения необходимо применить
к таблице вручную
В случае ошибки достаточно завершить работу триггера.
Слайд 43Триггеры как инструмент ограничения целостности в
Transact SQL
7.4
AFTER:
Изменения к таблице уже
применены. Данные соответствуют всем текущим ограничениям.
В случае ошибки необходимо явно
откатить изменения.
ROLLBACK.
Слайд 44Триггеры как инструмент ограничения целостности в
Transact SQL
7.5
CREATE TRIGGER Tr1
ON emp
AFTER INSERT
AS
BEGIN
if (select salary from inserted)
о сотруднике с окладом менее 10 000 рублей'
END
Пример 1. Запретить добавлять
запись о сотруднике зарплата которого, менее 10000 рублей
Слайд 45Курсор, как средство процедурного доступа к табличным данным
в Transact SQL
7.6
Протокол
работы
Объявление курсора
DECLARE CURSOR FOR
Открытие курсора (первоначальная загрузка
данных)
OPEN <имя курсора>
Извлечение одной строки из курсора
FETCH [NEXT | LAST | ...] FROM <имя курсора> INTO <имя переменной>, <имя переменной>, ....
Проверка наличия результата операции FETCH (Значение 0 говорит об успешности завершения операции)
IF @@FETCH_STATUS = 0
Освобождение курсора
CLOSE <имя курсора>;
DEALLOCATE <имя курсора>;