SQL Fundamentals

Обзор – День #1
Организационные вопросы
Введение в SQL
Извлечение информации из таблиц
Формирование вывода запроса
Агрегатные функции
Соединение таблиц
SQL Fundamentals, Performance Lab

Обзор – День #2
Вложенные запросы
Соотнесенные запросы
Изменение содержимого таблиц
Создание таблиц
Поддержка целостности данных
SQL Fundamentals, Performance Lab

Введение
Организационные вопросы
Что такое реляционная БД
Стандарт ANSI и Oracle SQL
Интерактивный и встраиваемый SQL
Способы доступа к БД
Знакомство с Toad
SQL Fundamentals, Performance Lab

Доступ к удаленным станциям
Нажать: windows+R
Ввести: mstsc
Вести компьютер:
rdp.pflb.ru:56920
Login\pass: UserX\UserSQLX, где X это номер от 1 до 15
В Toad for Oracle выбрать learn/learn
SQL Fundamentals, Performance Lab

Классификации СУБД по модели данных
Иерархические
Сетевые
Реляционные
Объектно-ориентированные
SQL Fundamentals, Performance Lab

Реляционная БД
SQL Fundamentals, Performance Lab

Стандарт ANSI и Oracle SQL
SQL Fundamentals, Performance Lab

Интерактивный и встраиваемый SQL
Интерактивный SQL
Непосредственно в БД используется;
После ввода команды, она сразу выполнится;
Встраиваемый SQL
Команды помещённых внутри программ на других языках программирования;
SQL Fundamentals, Performance Lab

Подразделы SQL
DDL (Язык Определения Данных)
DML (Язык Манипулирования Данными)
DCD (Язык Управления Данными)
SQL Fundamentals, Performance Lab

Способы доступа к БД
Способ доступа
Файл серверные
Клиент серверные
Встраиваемые
SQL Fundamentals, Performance Lab

Знакомство с Toad
Поддерживает: Oracle Database, Microsoft SQL Server, Adaptive Server Enterprise, DB2, MySQL , Hadoop, MongoDB, SimpleDB, Apache Cassandra и Windows Azure
TOAD содержит три основных компонента: Database Browser, SQL Editor и PL/SQL Procedure Editor и SQL Modeller.
Официально работает на 32-битных Windows-платформах: Windows 95, 98, NT, 2000, XP и Vista.
https://www.toadworld.com/
SQL Fundamentals, Performance Lab

Извлечение информации
Оператор SELECT
Ключевое слово DISTINCT
Ключевое слово WHERE
Столбцы упорядочены, строки – нет
Ключевое слово ORDER BY
Что такое предикат
Условия в предикатах: сравнения, логические (=, >, <. =>, <=, <>, and, or, not)
Ключевые слова IN, BETWEEN, LIKE
NULL-значения
Псевдостолбцы ROWID, LEVEL, ROWNUM
SQL Fundamentals, Performance Lab

Задание #1
Написать запрос, выводящий...
дату заказа, номер заказа и его стоимость для всех заказов (всех строк таблицы Orders)
всех продавцов с номером, большим 1005 (таблица Sales)
все заказы, оформленные в промежутке с ‘10/03/1990’ по ‘10/04/1990’ (таблица Orders)
в алфавитном порядке всех покупателей из города ‘SanJose’ (таблица Clients)
заказы с номером 3000, 3004 или 3005 (таблица Orders)
всех покупателей, проживающих в городах, не начинающихся с R, чей рейтинг больше 130 (таблица Clients)
SQL Fundamentals, Performance Lab

Формирование вывода
Именование столбцов AS
Использование констант
Использование скалярных функций
Функции LENGTH, UPPER, LOWER, INITCAP, CONCAT, SUBSTR, INSTR
Функции ROUND, TRUNC, REMAINDER, MOD
Функции NVL, NULLIF, DECODE, CASE
Таблица DUAL
Вложенные функции
SQL Fundamentals, Performance Lab

Задание #2
Написать запрос, выводящий…
стоимости (amt) всех заказов, округленные до целого
имя и город проживания продавцов, причем если город не задан (т.е. null), выводить «не задан» вместо null
один столбец со строками вида "Рейтинг покупателя cname равен rating (высокий, средний или низкий)" для всех записей таблицы Clients, при этом имя покупателя должно быть выведено большими буквами. В конце строки в скобках указать «высокий» - если рейтинг выше 200, «средний» - если равен 200 и «низкий» – во всех остальных случаях.
SQL Fundamentals, Performance Lab

Агрегатные функции
Функции COUNT, SUM, AVG, MAX, MIN
Различия *, DISTINCT, ALL в агрегатных функциях
Вложенные агрегатные функции
Ключевое слово GROUP BY
Ключевое слово HAVING
SQL Fundamentals, Performance Lab

GROUP BY
В конструкции GROUP BY можно указать сколько угодно выражений, в том числе – названия столбцов;
Обычно GROUP BY используется для указания столбцов, значения в которых должны быть одинаковыми для того, чтобы соответствующие строки вошли в группу;
Если в теле запроса есть GROUP BY, то в селект-листе этого запроса допускается использовать только:
Выражения, использованные в GROUP BY;
Агрегатные функции;
Константы;
Выражения из GROUP BY необязательно подставлять в селект-лист.
SQL Fundamentals, Performance Lab

Порядок обработки операторов
Логический порядок
Синтаксический порядок
SQL Fundamentals, Performance Lab
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
SELECT [DISTINCT | ALL]{*
| [<выражение для столбца> [[AS] <псевдоним>]] [,…]}
FROM <имя таблицы> [[AS] <псевдоним>] [,…]
[WHERE <предикат>]
[[GROUP BY <список столбцов>]
[HAVING <условие на агрегатные значения>] ]
[ORDER BY <список столбцов>]

ORDER BY совместно с GROUP BY
Если в теле запроса есть GROUP BY, то в конструкции ORDER BY можно указывать только:
Выражения, указанные в GROUP BY;
Выражения, указанные в селект-листе (можно использовать порядковый номер столбца, его имя или алиас);
Агрегатные функции (независимо от того, присутствуют ли они где-то еще в теле запроса);
Если в теле запроса есть GROUP BY, то в конструкции ORDER BY нельзя указывать:
Столбцы таблицы, которые не присутствуют в GROUP BY.
SQL Fundamentals, Performance Lab

Задание #3
Написать запрос, выводящий…
общее количество сделанных заказов
среднюю длину имени покупателя
дату и максимальную стоимость заказа за эту дату, отсортированные по убыванию максимальной стоимости заказа
названия городов, в которых суммарный рейтинг покупателей превышает 100
SQL Fundamentals, Performance Lab

Соединение таблиц
Разница между INNER и OUTER JOIN
Разница между LEFT OUTER, RIGHT OUTER и FULL OUTER JOIN
NATURAL JOIN
Ключевое слово USING
Ключевое слово CROSS JOIN
Соединение более двух таблиц
«Самосоединение» таблиц
SQL Fundamentals, Performance Lab

Различный виды операции JOIN
SQL Fundamentals, Performance Lab

Задание #4
Написать запрос, выводящий…
имена продавцов и соответствующие им имена клиентов, в том числе для продавцов без клиентов
имена и номера продавцов, имеющих клиентов в Лондоне (London), Москве (Moscow) или Сочи (Sochi)
строки, показывающие всю информацию о тех продавцах и их клиентах , которые живут в одном и том же городе
имя продавца и сумму всех совершенных его клиентами заказов, отсортированные по убыванию суммы
SQL Fundamentals, Performance Lab

Вложенные запросы
Что такое подзапрос
Принцип работы вложенного запроса
Single- и multirow подзапросы
Использование IN в подзапросах
Ключевые слова ALL, ANY, SOME
Операции над множествами: UNION, INTERSECT, MINUS
Операции над множествами: ORDER BY
Использование агрегатных функций в подзапросах
Запросы с WITH
SQL Fundamentals, Performance Lab

Задание #5
Написать запрос, выводящий…
все заказы, оформленные продавцами из Лондона (London) без использования join
всех продавцов, имеющих комиссию выше средней
общее количество продавцов и покупателей из каждого города, отсортированное по убыванию количества
2, 3, и 4 строки таблицы Sales, отсортированной по возрастанию имени продавца
SQL Fundamentals, Performance Lab

Соотнесенные запросы
Принцип работы соотнесенного запроса
Ключевое слово EXISTS
Скалярные подзапросы
SQL Fundamentals, Performance Lab

Принцип работы соотнесенного подзапроса
Выбрать строку из таблицы, именованной во внешнем запросе. Это будет текущая строка-кандидат.
Сохранить значения из этой строки-кандидата в псевдониме с именем в предложении FROM внешнего запроса.
Выполнить подзапрос. Везде, где псевдоним, заданный для внешнего запроса, найден, использовать значение текущей строки-кандидата. (Использование значения из строки-кандидата внешнего запроса в подзапросе называется внешней ссылкой.)
Оценить предикат внешнего запроса на основе результатов подзапроса, выполняемого в шаге 3. Он определяет, выбирается ли строка-кандидат для вывода.
Повторить процедуру для следующей строки-кандидата таблицы, и так далее, пока все строки таблицы не будут проверены.
SQL Fundamentals, Performance Lab

Скалярные подзапросы
Это single-row subquery, в которой только 1 столбец;
Скалярные подзапросы обязательно заключаются в скобки;
Могут быть соотнесенными;
Могут быть использованы почти в любом месте, где может быть использовано выражение, за исключением:
В конструкции GROUP BY
В прочих случаях (не исследуются в рамках этого курса)
SQL Fundamentals, Performance Lab

Задание #6
Написать запрос, выводящий…
имена и номера всех продавцов, которые имеют не менее одного заказчика
всех продавцов, имеющих комиссию не ниже средней по своему городу
[подзапросом] номера и имена всех продавцов, имеющих в своем городе заказчиков, которых они не обслуживают
[join’ом] номера и имена всех продавцов, имеющих в своем городе заказчиков, которых они не обслуживают
номера всех заказов, а также разницу между стоимостью каждого заказа и средней стоимостью заказа за тот же день
SQL Fundamentals, Performance Lab

Изменение содержимого таблиц
Операторы INSERT, UPDATE, DELETE
Использование подзапросов при изменении содержимого
Понятие транзакции, ключевые слова COMMIT, ROLLBACK
Типы запросов: DML, DDL, TCL
SQL Fundamentals, Performance Lab

Порядок выполнения INSERT
Проверка, что таблица, указанная в INTO, существует
Проверка, что колонки указанные в INSERT, существуют в таблице
Выражения, указанные в списке VALUES, вычисляются
Проверяется совместимость типов выражений в VALUES и типов соответствующих колонок таблицы
Выражения в VALUES проверяются на соответствие имеющимся в таблице ограничениям целостности (они будут рассмотрены позднее)
SQL Fundamentals, Performance Lab

ACID
Atomicity (Атомарность)
Consistency (Согласованность)
Isolation (Изолированность)
Durability (Надежность)
SQL Fundamentals, Performance Lab

Многопользовательские коммиты
SQL Fundamentals, Performance Lab

Задание #7
Написать запрос, который…
делает все города проживания клиентов, начинающиеся с S, написанными большими буквами, затем откатывает изменения
добавляет нового продавца (некоторые из полей можно оставить пустыми), причем новый продавец должен стать видимым для остальных слушателей курса
удаляет всех продавцов, у которых не указан город проживания и чьи имена длиннее восьми символов
SQL Fundamentals, Performance Lab

Создание таблиц
Правила именования объектов БД
Основные типы данных: NUMBER, CHAR, VARCHAR2, DATE
Оператор CREATE, оператор DESC
Неявные коммиты
Использование подзапросов для создания таблиц
Операторы ALTER, DROP
Создание и модификация представлений (VIEW)
SQL Fundamentals, Performance Lab

Правила именования объектов БД
Длина имени – хотя бы один символ, но не больше 30 символов;
Первый символ в имени должен быть буквой (латинского алфавита);
После первого символа могут идти любые буквы, цифры, а также знаки $, _, # в любом порядке (никаких других символов в имени не допускается);
Имена не могут быть зарезервированными словами языка SQL (такими как SELECT, CREATE и т.д.)
SQL Fundamentals, Performance Lab

Пространства имен
SQL Fundamentals, Performance Lab

NUMBER(n, m)
Синоним DECIMAL(n, m)
Параметры n и m – опциональные
n ∈ [1, 38] – максимальное количество значащих цифр. Значение по умолчанию = 38.
m ∈ [-84, 127] – количество цифр справа от запятой. Значение по умолчанию = 0.
x ∈ NUMBER ⇒ |x| ∈ [1*10-130, 1*10126)
SQL Fundamentals, Performance Lab

NUMBER(n, m)
SQL Fundamentals, Performance Lab

CHAR(n)
Строка фиксированной длины, состоящая из букв и цифр;
n ∈ [1, 2000] – длина строки. Необязательный параметр. Значение для n по умолчанию = 1;
В столбцах типа CHAR(3) значения из двух символов ( например, ‘r2’) будут храниться как ‘r2 ‘, т.е. дополняться пробелами справа (до длины n)
SQL Fundamentals, Performance Lab

DATE
Хранит информацию о дате и времени
Примеры форматирования дат (функции TO_CHAR, TO_DATE)
SQL Fundamentals, Performance Lab
TIMESTAMP(n)
Расширение DATE – хранит также доли секунд
n ∈ [1, 9] – количество цифр после запятой (точность). Значение по умолчанию = 9.

Преобразование типов
Некоторые правила:
Неявное преобразование в целевой тип при INSERT и UPDATE, вызове функций
Операции над типом NUMERIC производятся с максимально возможной точностью
При сравнении CHAR* vs NUMERIC значение CHAR* преобразуется в NUMERIC
При сравнении CHAR* vs DATE значение CHAR* преобразуется в DATE
В операциях присваивания выражение справа от = преобразуется в тип переменной слева от =
SQL Fundamentals, Performance Lab

Функции конвертации типов
TO_NUMBER(e1[, format_model[, nls_params]])
Для CHAR*: TO_CHAR(c)
Для NUMBER: TO_CHAR(n[, format_model[, nls_params]])
Для DATE: TO_CHAR(d[, format_model[, nls_params]])
TO_DATE(c[, format_model[, nls_params]])
TO_TIMESTAMP(c[, format_model[, nls_params]])
SQL Fundamentals, Performance Lab

Задание #8
Написать запрос, который…
создает новую таблицу, содержащую только те строки из таблицы клиентов, в каждой из которых нет значений null
создает представление на основе таблицы из п.1, включающее только столбцы cname и city
удаляет таблицу из п.1
пробует запросить содержимое из представления из п.2
выполнить п.1, затем снова п.4
SQL Fundamentals, Performance Lab

Поддержка целостности данных
Ограничения (constraints): UNIQUE, NOT NULL, CHECK
Вспомним про свойство транзакций «Consistency»
Просмотр ограничений таблицы
Изменение ограничений
Первичные и внешние ключи
Inline- и out-of-line-синтаксис описания ограничений
Создание последовательностей (SEQUENCE)
SQL Fundamentals, Performance Lab

Поддержка целостности данных
Ограничения (constraints): UNIQUE, NOT NULL, CHECK
Просмотр ограничений таблицы
Изменение ограничений
Первичные и внешние ключи
Inline- и out-of-line-синтаксис описания ограничений
SQL Fundamentals, Performance Lab

Синтаксис
SQL Fundamentals, Performance Lab

Синтаксис
SQL Fundamentals, Performance Lab

UNIQUE
Может быть применено к одному или более столбцам;
Допускаются значения null;
Создание – in-line либо out-of-line синтаксис;
ALTER TABLE table_name DROP UNIQUE (column1, column2, . . . )
SQL Fundamentals, Performance Lab

NOT NULL
Может быть применено только к одному столбцу;
Не допускает значения null в столбце;
Создание – только in-line синтаксис;
ALTER TABLE table_name MODIFY column_name NOT NULL;
ALTER TABLE table_name MODIFY column_name NULL;
SQL Fundamentals, Performance Lab

CHECK
Позволяет использовать сложные выражения для описания нетривиальных правил для добавляемых строк, например:
Хотя бы одна из n колонок is not null;
Значения в столбце должны быть больше x;
Значения в столбце должны быть из некого предопределенного набора;
Создание – in-line либо out-of-line синтаксис;
ALTER TABLE table_name DROP CONSTRAINT name;
ALTER TABLE table_name RENAME CONSTRAINT name TO new_name;
SQL Fundamentals, Performance Lab

PRIMARY KEY
Один или более столбцов, однозначно идентифицирующий каждую строку в таблице;
В любой таблице может быть не более одного первичного ключа;
PRIMARY KEY = UNIQUE + NOT NULL;
Создание – in-line либо out-of-line синтаксис;
ALTER TABLE table_name DROP CONSTRAINT name;
ALTER TABLE table_name RENAME CONSTRAINT name TO new_name;
SQL Fundamentals, Performance Lab

FOREIGN KEY
Применяется к одному или нескольким столбцам в таблице;
Поддерживает ссылочную целостность БД – гарантирует, что для каждого столбца (столбцов) таблицы существует соответствующий столбец (столбцы) в другой таблице;
FOREIGN KEY одной таблицы может ссылаться только на PRIMARY KEY (или UNIQUE) другой таблицы.
SQL Fundamentals, Performance Lab

SEQUENCE
Объект в БД, который используется (в основном) для генерации значений первичных ключей;
NEXTVAL – переводит сиквенс на следующее значение и возвращает это (новое) значение;
Сиквенс передвинется на следующее значение даже в случае, если использовавший его запрос не выполнится (из-за ошибки);
CURRVAL – возвращает текущее значение сиквенса. CURRVAL нельзя вызывать, если еще ни разу (в текущей сессии) не вызывался NEXTVAL.
SQL Fundamentals, Performance Lab

Задание #9
(все названия каждый придумывает самостоятельно, т.е. у каждого должен быть свой объект БД)
Создать новую таблицу (таблица C), содержащую только уникальные строки таблицы Clients
Создать новую таблицу (таблица S), содержащую только уникальные строки таблицы Sales
Добавить PRIMARY KEY на столбец snum таблицы S (при необходимости удалить повторяющиеся snumы и null-значения)
Добавить FOREIGN KEY на столбец snum таблицы С (при необходимости из C удалить строки с такими snum, которых нет в S)
SQL Fundamentals, Performance Lab

Индексы
Неявное создание
Явное создание
Модификация и удаление
SQL Fundamentals, Performance Lab

Индексы
Неявное создание
Явное создание
Модификация и удаление
Понятия SELECTIVITY, CARDINALITY
SQL Fundamentals, Performance Lab

Индексы
SQL Fundamentals, Performance Lab

Индексы
SQL Fundamentals, Performance Lab

Индексы
SQL Fundamentals, Performance Lab
Пример поиска по индексу: отбираем строки со значением 57 в индексированном столбце

B+ tree index
SQL Fundamentals, Performance Lab

Преимущества B+ tree index
Высота дерева фиксированная ⇒ поиск любой записи в индексе занимает примерно одинаковое время
Автоматически балансируются
Ускоряют большинство запросов, включая запросы с предикатами, содержащими равенство либо диапазон значений
Быстрые операции UPDATE, INSERT, DELETE
Обеспечивают приемлемую производительность для таблиц больших и малых объемов
Скорость поиска записей в индексе не падает с ростом числа записей (строк в таблице)
SQL Fundamentals, Performance Lab

Composite Index
SQL Fundamentals, Performance Lab

Задание #10
SQL Fundamentals, Performance Lab