Введение в язык SQL DDL (Data Definition Language) DML (Data Manipulation Language) DCL (Data Control Language) TCL (Transaction Control Language)

SQL (англ. Structured Query Language язык структурированных запросов) Универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. Вопреки существующим заблуждениям, SQL является информационно-логическим языком, а не языком программирования. SQL основывается на реляционной алгебре.

Основные группы SQL Операторы определения данных (англ. Data Definition Language, DDL) – Создание структуры данных Операторы манипуляции данными (англ. Data Manipulation Language, DML) – Извлечение данных – Обновление данных Операторы определения доступа к данным (англ. Data Control Language, DCL)

Часть 1: DDL Создание структуры базы данных в SQL

Типы данных in SQL Символьные: – CHAR(20)-- fixed length – VARCHAR(40)-- variable length Числовые: – BIGINT, INT, SMALLINT, TINYINT – REAL, FLOAT -- differ in precision Время и дата: – DATE – TIMESTAMP – DATETIME-- SQL Server Другие…

Создание таблицы Самая важная из DDL-операций является создание таблицы: уникальное имя таблицы «Table elements»: определение имени и типа данных для каждого столбца определение ограничений Синтаксис объявления таблицы:

Создание таблицы Создание таблицы FotballMatch содержащей результаты футбольных матчей

Создание таблицы: структура

Создание таблицы: столбец Объявление содержит – имя колонки – тип значения – спец ограничение Синтаксис объявления столбца

Создание таблицы: столбец Синтаксис ограничений столбца Объявление содержит – имя колонки – тип значения – спец ограничение

Ограничения таблицы Вторая часть определения таблицы – задание ограничений. Синтаксис ограничений таблицы:

Ограничение CHECK При каждом изменении строки данных условие этого ограничения должно удовлетворятся для указанного столбца

Ограничение UNIQUE Семантика: не может быть две строки с одинаковым значением для указанного столбца Исключение: значения NULL могут повторятся В таблице возможно иметь несколько столбцов с таким ограничением Если в таблице есть хоть одно UNIQUE поле, то не будет ни одной записи-дубликата

Ограничение PRIMARY KEY Ограничение PRIMARY KEY(PK) уникально идентифицирует каждую запись в таблице PK должен содержать уникальные значения PK не может быть иметь значение NULL Каждая таблица должна иметь PK и он должен быть один

Ограничение PRIMARY KEY Синтаксис создания PK – PK состоящий из одного столбца может быть объявлен в контексте его объявления... PRIMARY KEY – «мульти-столбцовый» PK объявляется в контексте ограничений таблицы PRIMARY KEY ( ) PRIMARY KEY – не тоже самое что UNIQUE!

Значение по умолчанию (DЕFAULT) Есть возможность задания значения по умолчанию для столбца Синтаксис:

Ограничение FOREIN KEY В контексте ограничений таблицы можно задать так называемое ограничение «по внешнему ключу» - FOREIN KEY(FK) Синтаксис: Если список столбцов отсутствует – то будет принят PK Пример:

Ситуация FK Столбец(столбцы) таблицы A объявлены как ссылки на столбец(столбцы) таблицы B Условие: FK столбец(столбцы) таблицы A могут содержать только значения существующие в таблице B Колонки формиру ющие FK

Часть 2: DМL В этой части мы поговорим о манипуляции данными в SQL

Простой SQL запрос на выборку данных Основной компонент всех SQL запросов на выборку данных из таблиц - SELECT-FROM- WHERE Пример Найти все столицы с населением более миллиона

Расшифровка: Select-From-Where

Пример работы запроса [1]

Пример работы запроса [2] На первом шаге результирующая таблица содержит 46 * 77 = 3542 записей

Пример работы запроса [3] На следующем шаге происходит отсеивание записей в соответствии с WHERE выражением. (Всего 46 столиц отсеянных по критерию численности населения)

Пример работы запроса [4] Далее отбрасываем ненужные столбцы. Получаем результирующую таблицу:

WHERE-условия Предусмотрены 6 следующих операций сравнения: Выражение может быть комбинированно с использованием логических операторов - связок:

Понимание логических операторов

Сокращенные и полные ссылки на таблицы Без префикса: С префиксом:

Пример табличных ссылок В предыдущем примере вместо населения мы использовали столбец «year». Если поменять «year» на «population» то возникает конфликт так как этот столбец есть в обоих таблицах: Разрешение конфликта:

Псевдоним (Alias) для таблиц Для краткости записи запроса можно использовать псевдонимы таблиц: Псевдонимы таблиц можно смешивать со стандартным использованием ссылок на таблицы:

Множество данных таблицы

Операции с множествами Объедение – все содержит элементы обоих множеств Пересечение – содержит только те элементы которые есть в обоих множествах Разность – Содержит все элементы A, которых нет в B Ключевые SQL слова для выполнения этих операций с множествами

Пример операций с множествами

Объединение в SQL

Блоки SELECT-FROM-WHERE как операторы реляционной алгебры (РА) Операторы РА Проекция – исключение всех столбцов кроме A и B Выборка – исключение всех строк исключая те которые удовлетворяют выражению cond Производство – множество всех комбинаций записей из R и S Пример: Порядок обработки: 1) Производство 2) Выборка 3) Проекция

Оператор JOIN Существует специальная нотация для ситуации: Это так называемое выражение объединения: JOIN JOIN доступно только в части запроса FROM. Эту форму объединения называют INNER JOIN

Оператор IN

NOT IN для представления операции «РАЗНОСТЬ» множеств

Эмуляция «Пересечения» с помощью JOIN В некоторых реализациях БД (например MS Access) не поддерживается оператор INTERSECT. Проблема решается оператором JOIN: В этом случае порядок входных таблиц не важен. Например такой запрос даст аналогичный результат:

Удаление дублирующих записей Для удаления дублирующих используется ключевое слово DESTINCT

Агрегатные функции Важная часть SQL – встроенные функции COUNT – Количество SUM – Сумма AVG – Вычисление среднеарифметического Maximum – Вычисление максимального Minimum – Вычисление минимального

Пример использование встроенной функции Вычисление суммы зарплат всех профессоров с рангом «С3» Кто из профессоров ранга «С3» старше самого пожилого профессора «С4»

Группировка записей SELECT FROM WHERE condition ; GROUP BY groupexpr [HAVING requirement] Синтаксис: groupexpr – список столбцов группировки WHERE condition – условие налагаемое на результат до того как группы будут сформированы HAVING requirement – наложение условия при котором группа попадет в выборку

Группировки записей Основная идея в том что результат полученный с помощью SQL запроса делится на под таблицы (группы) с уникальными значения для указанных столбцов. Пример:

Иллюстрация GROUP BY

Пример группировки записей Выводятся средние результат теста у мальчиков по классам – в вывод попадут только те классы в которых более 3-х мальчиков SELECT AVG(mtest), class FROM student WHERE sex="M" GROUP BY class HAVING COUNT(*) >= 3 Result

Сортировка полученных записей Сортировка указывается в самом конце запроса (после GROUP BY если она есть) Пример: Направления сортировки: ASC – по возрастанию (по умолчанию) DESC – по убыванию

Значения NULL

Операции с NULL NULL может присваиваться переменным и записываться в поля, независимо от объявленного типа данных этих переменных (полей); NULL может передаваться в процедуры и функции как легальное значение параметра. Результаты выполнения такой процедуры или функции определяются операциями, выполняемыми с параметрами внутри неё. Любая операция с NULL, кроме операции сравнения, в результате даёт NULL, независимо от значения прочих операндов. Существует специальная системная функция или операция (обычно expr IS [NOT] NULL), возвращающая логическое значение «истина» (TRUE), если expr является (не является) NULL и FALSE в противном случае.

Операция сравнения с NULL Любая операция сравнения с NULL (даже операция «NULL = NULL»), даёт в результате значение «неизвестность» (UNKNOWN). Если сравнение с NULL есть вся логическая операция целиком (а не её часть), то результат её аналогичен FALSE (выражение вида IF = NULL THEN ELSE END IF всегда будет приводить к выполнению действия2).

NULL и агрегатные функции Агрегатные функции игнорируют значение NULL

Операции изменения данных Существуют три операции изменения данных: – INSERT – вставка строк – UPDATE – модификация значений столбцов – DELETE – удаление строк

Операция INSERT Формат: Два варианта использования: 1) напрямую 2) через запрос

Автоматическая генерация PRIMARY KEY create table MY_TABLE ( S_ID integer generated by default as identity (start with 1) primary key ); -- удаление таблицы drop table MY_TABLE;

Операции UPDATE и DELETE Формат: Синтаксис присваивания: Пример: Удаление:

Часть 3: DСL DCL (data control language) – язык контроля данных Основной представитель группы контроля данных SQL это оператор GRANT Общий синтаксис: GRANT { INSERT|SELECT|DELETE} ON EMP TO {USER} (дает права на операции выборку, вставку и удаление с таблицей EMP пользователю USER)

Часть 4: TСL TCL (transaction control language) – группа операций для управления транзакциями – COMMIT – фиксация транзакции – ROLLBACK – откат транзакции – SAVEPOINT – создание точки сохранения