Введение в язык SQL DDL (Data Definition Language) DML (Data Manipulation Language) DCL (Data Control Language) TCL (Transaction Control Language) - презентация

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

37 Оператор IN

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

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

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

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

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

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

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

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

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

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

48 Значения NULL

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

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

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

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

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

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

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

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

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