# Entity-Relationship и SQL
## Реляционные СУБД: Что это и Зачем?
Реляционные СУБД - строго структурированные СУБД в форме связанных таблиц. Каждая таблица отражает ту или иную сущность (_Entity_), которая связана (_относится_, _Relation_) с другими таблицами.
Считается, что таблицы имеют строгую структуру полей в записях (строках), но неограниченное количество строк.
### Примеры
- Школа-Классы-Ученики-Учителя
- Автомобиль-Детали
- Люди-Домашние питомцы
- Библиотека-Читатели-Книги
- Человек-Номера телефонов
- etc...
## Плюсы и минусы
### Плюсы
- Скорость работы
- Структурированность
- Легкость в проектировании на начальном этапе при хорошо описанной предметной области
- Хорошая теоретическая и практическая базы
### Минусы
- Сложности с расширением и изменением
- Масштабируемость
## Сущности и Связи
**Сущность** - тот или иной объект реального (или виртуального) мира, ради хранения информации о котором и делается СУБД. Набор данных о сущностях разделяется на поля и хранится в таблицах. При разработке СУБД заранее
определяются сущности предметной области и отношения и количественные отношения между ними, для установки соответствующих связей.
**Связь** - логическое отношение, реализуемое с помощью специальных полей в таблицах. Для установки связности СУБД используется уникальный идентификатор (поле) каждой записи (строки) в таблице.
## Поля
Типы полей во многом похожи на привычные типы данных в языках программирования: числа, строки и так далее. Однако структуры данных (объекты, ассоциативные и обычные массивы) согласно теории должны быть преобразованы
к тем или иным реляционным формам. Для идентификации записей используются специальный тип данных **Автоинкремент**, который гарантирует уникальность каждой записи в пределах таблицы.
## Типы связей
### Один к одному
Однозначное соответствие двух сущностей друг другу. Человек и его отпечаток пальца, например. Редкий вид связи, зачастую используется для расширения сущности дополнительными параметрами.
### Один ко многим
Самый частый случай. Класс и ученики, Город и его жители, Ребенок и его родители и так далее. Связь устанавливается с помощью сохранения значения автоинкремента _одного_ в каждом экземпляре _многих_.
### Многие ко многим
Тоже часто встречаемый случай, достаточно сложный в реализации, так как требует введения дополнительной таблицы для связности. Например книги в библиотеке и их читатели: каждый читатель может прочитать более одной книги,
каждая книга бывает прочитана более чем одним читателем. Для связи заводится смежная таблица, в которой хранятся автоинкременты обоих связываемых сущностей - например номер книги и номер паспорта читателя, а так же
дополнительная информация, относящаяся к связи (например дата и время выдачи книги читателю)
# ER-Диаграммы
**ER-Диаграммы** позволяют изобразить структуру реляционной СУБД в наглядном виде. Сущности отображаются таблицами с описанием полей и первичных ключей,
служащих для указания связности. Связи - линиями между таблицами разных видов.
## Задание 1
Нарисовать в общем виде структуру из нескольких таблиц со связями **один ко многим** и **многие ко многим**, придуманной предметной области. Сохранить в
гугл-диск.
# SQL
**SQL** (*Structured Query Language*, *Структурированный Язык Запросов*) - язык, на котором происходит взаимодействие с СУБД. Подразделяется на две части:
- **DDL** (*Data Definition Language*) - служит для создания таблиц, изменения их структуры и связей между ними.
- **DML** (*Data Manipulation Language*) - оперирует данными в таблицах (выборка и запись).
Так как на этом занятии мы рассматриваем структуру СУБД, то предметом нашего изучения сегодня будет DDL, а именно операции создания и изменения структуры
таблиц.
## Типы данных MySQL
Базы данных в целом хранят те же типы данных, что и обрабатываются компьютерными программами, однако, в силу ориентированности на хранение большого количества
данных, типы указываются более точно, для экономии места и ускорения обработки. Например для строк задается их длина, есть несколько видов целых и
вещественных чисел и так далее.
### Числа
#### Целочисленные типы данных
| Тип | Описание | Занимаемое место (байт) |
| --------------------------------- | ----------------------------------------------------------------------------- |:----------------:|
| INT | Стандартный целочисленный тип от -2147483648 до 2147483647 | 4 |
| TINYINT | Стандартный целочисленный тип от -128 до 127 | 1 |
| SMALLINT | Стандартный целочисленный тип от -32768 до 32767 | 2 |
| MEDIUMINT | Стандартный целочисленный тип от -8388608 до 8388607 | 3 |
| BIGINT | Стандартный целочисленный тип от -9223372036854775808 до 9223372036854775807 | 8 |
Каждый из этих типов может быть беззнаковым, если указать слово `UNSIGNED`. В таком случае его диапазон увеличивается в положительную сторону, т. е.
`UNSIGNED INT` - 0 до 4294967295, `UNSIGNED BIGINT` - 0 до 18446744073709551615 и так далее.
#### Типы с плавающей точкой (вещественные)
| Тип | Описание |
| --------------------------------- | ----------------------------------------------------------------------------- |
| FLOAT(M,D) | Вещественное число с M знаками из которых D знаков после запятой |
| DOUBLE(M,D) | Вещественное число двойной точности с M знаками из которых D знаков после запятой |
| REAL(M,D) | Тоже самое, что и DOUBLE |
*Вещественные числа* плохо подходят для хранения точных значений дробных чисел, так как состоят из двух частей: *мантиссы* и степени
числа 10, на которое умножается мантисса. Такой подход удобен для математических расчетов и экономен по памяти, однако если число очень большое, то в нём
не сохраняется дробная часть из-за ограниченной точности мантиссы. Это неприемлимо, например, для операций с деньгами, поэтому...
#### Тип с фиксированной точкой
...поэтому существуют типы с фиксированной точкой, которые представляют из себя что-то типа строки с цифрами, которая гарантированно сохранит значение в
нужном диапазоне: `DECIMAL(M,D)` или `NUMERIC(M,D)`. M и D - имеют тот же смысл, что и для вещественных типов (общее количество цифр в числе и количество
цифр дробной части).
### Дата и время
Любая современная СУБД умеет гибко оперировать с датой и временем. Например, можно делать запросы "за последние 3 недели", "от начала года до двух месяцев
назад" и так далее. Для этих целей в MySQL существуют следующие типы данных:
| Тип | Описание |
| --------------------------------- | ----------------------------------------------------------------------------- |
| DATE | Дата в формате YYYY-MM-DD |
| TIME | Время в формате HH:MM:SS |
| DATETIME | Дата и время в формате YYYY-MM-DD HH:MM:SS |
| TIMESTAMP | DATETIME, который автоматически заполняется временем сохранения или изменения записи |
| YEAR | Год, в двух (`YEAR(2)`) или четырехцифровом (`YEAR(4)`) формате |
### Строки и текст
Все эти типы данных ведут себя схоже, однако отличаются по внутренней реализации.
| Тип | Описание | Занимаемое местa (байт) |
| --------------------------------- | ----------------------------------------------------------------------------- | ----------------------- |
| CHAR(M) | Строка длиной до М (до 255) | M |
| VARCHAR(M) | Строка длиной до М (до 65535) | Зависит от длины строки |
В общем случае оба типа данных ведут себя одинаково, однако `VARCHAR` обрабатывается чуть медленнее, но экономичней по потреблению памяти.
Строки длиннее M обрезаются при сохранении в таблицу. **Суммарная длина всех полей типа VARCHAR** в записи не может превышать **65535**.
Следовательно, эти два типа данных предназначены для хранения небольших строк типа логинов, паролей, имен, фамилий и тому подобного.
#### Текст
Для хранения больших объемов текста существуют типы `TINYTEXT` (255), `TEXT` (65535), `MEDIUMTEXT` (16777216) и `LONGTEXT` (4294967296). Так же эти типы
предоставляют возможности *полнотекстового поиска* средствами MySQL, в отличии от `CHAR` и `VARCHAR`.
## Индексы
**Индекс** - специальная структура данных, хранящая в себе упорядоченную (сортированную) информацию о данных в полях таблицы. SQL-базы ориентированы на
хранение огромных структур данных на медленных носителях (HDD, SSD), сортировать ВСЕ данные которых неоправданно затратно по времени и ресурсам. Более того,
невозможно иметь один и тот же набор данных отсортированный одновременно по разным критериям, например по фамилии и по дате рождения. Для
решения этих задач и существуют индексы - они хранят отсортированные по нужным критериям *ссылки* на записи таблицы.
### Поиск
Одна из главных задач компьютеров в целом и СУБД в частности - быстрый поиск данных. Реляционные СУБД еще более склонны к этому, так как происходит поиск
связанных данных из других таблиц.
#### Поиск в несортированном наборе данных
Для того, что бы найти определенную запись в наборе из **N** записей требуется до **N** операций сравнения с искомым значением, т. е. поиск перебором. Другими
словами, такой поиск имеет вычислительную сложность **O(n)**, и время на него пропорционально **N**.
#### Поиск в сортированном наборе данных.
Для поиска в сортированном наборе данных обычно применяется двоичный поиск. Этот несложный алгоритм позволяет радикально уменьшить вычислительную сложность
поиска до **O(log2(N))**, то есть для **N** = 1000 записей надо 10 сравнений, а для **N** = 2000 всего 11, **N** = 4000 - 12 сравнений.
**Наличие индекса превращает несортированный набор данных в сортированный, уменьшая время запросов к СУБД**. MySQL может искать данные и без индексов, однако
на больших наборах данных проще вначале создать индекс (его можно добавить в любой момент, не только когда таблица создается), а потом выполнять запросы
поиска данных.
## SQL DDL
Для входа в консоль MySQL:
```bash
mysql -u root
```
Команда выше запускает консоль mysql из под пользователя root, имеющего полный доступ ко всем базам данных. Для входа с паролем:
```bash
mysql -u root -p
```
Если после ключа `-p` указан пароль (его надо указывать *без* пробела), то вы сразу попадете в консоль. Иначе у Вас спросят пароль для ввода.
В консоли, за исключением редких директив, общение происходит на языке SQL. Консоль - это [REPL](https://ru.wikipedia.org/wiki/REPL) языка SQL.
Для начала создадим базу данных:
```mysql
CREATE DATABASE IF NOT EXISTS test;
```
Команда выше создает новую базу данных test. В консоли каждое SQL-выражение оканчивается `;`, таким образом mysql-клиент понимает что выражение окончено
и отправляет его на исполнение. В программном коде `;` не применяется в SQL-запросах.
```mysql
use test;
```
Директива `use` *не* является частью SQL-синтаксиса. Эта директива просто указывает mysql-клиенту что в дальнейшем все операции касаются базы данных `test`.
```mysql
SHOW DATABASES;
```
Этот запрос выводит список баз данных.
### Создание таблиц.
Перед тем, как записывать данные в таблицы, нужно создать их, описав с помощью DDL `CREATE TABLE`. В общем упрощенном виде это выглядит следующим образом:
```mysql
CREATE TABLE IF NOT EXISTS (
,
,
,
,
,
,
);
```
**Например**:
```mysql
CREATE TABLE IF NOT EXISTS person (
person_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64),
surname VARCHAR(64),
father_name VARCHAR(64)
);
```
Узнаем, сколько у нас таблиц в данной БД:
```mysql
SHOW TABLES;
```
Какова структура таблицы:
```mysql
DESC person;
```
Как создать эту таблицу:
```mysql
SHOW CREATE TABLE person;
```
Обратите внимание, что результат последнего запроса **не** совпадает с запросом, с помощью которого таблица была создана. MySQL генерирует этот запрос, исходя
из *текущей* структуры таблицы.
### Модификация таблицы
Добавим поле Дата Рождения после `person_id`:
```mysql
ALTER TABLE person ADD COLUMN date_of_birth DATE AFTER person_id;
```
В процессе разработки вы часто будете делать `ALTER TABLE`, и если потом вам надо будет создать таблицу на другом (например, production) сервере, вы можете
использовать для этого `SHOW CREATE TABLE`:
```mysql
SHOW CREATE TABLE person;
```
### Добавление индекса
```mysql
ALTER TABLE person ADD INDEX (date_of_birth);
```
## Задание 2
Пользуясь `CREATE TABLE` создать структуру СУБД, разработанной вами в виде ER-диаграммы. Для ссылочных полей использовать тип INT UNSIGNED c тем же именем:
```mysql
CREATE TABLE IF NOT EXISTS phone (
phone_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
person_id INT UNSIGNED, /* заметьте, что это ПРОСТО целочисленное поле, в котором должен хранится person_id пользователя-владельца телефона */
type ENUM('home', 'mobile', 'job'), /* погуглите что такое тип ENUM в mysql */
phone_number VARCHAR(16)
);
```
## Домашнее задание
1. Доделать недоделанное
2. Почитать о `INSERT`, `UPDATE`, `DELETE`, `SELECT`
3. Почитать о разных видах `JOIN`