Entity-Relationship и SQL

Реляционные СУБД: Что это и Зачем?

Реляционные СУБД - строго структурированные СУБД в форме связанных таблиц. Каждая таблица отражает ту или иную сущность (Entity), которая связана (относится, Relation) с другими таблицами. Считается, что таблицы имеют строгую структуру полей в записях (строках), но неограниченное количество строк.

Примеры

Школа-Классы-Ученики-Учителя
Автомобиль-Детали
Люди-Домашние питомцы
Библиотека-Читатели-Книги
Человек-Номера телефонов
etc...

Плюсы и минусы

Плюсы

Скорость работы
Структурированность
Легкость в проектировании на начальном этапе при хорошо описанной предметной области
Хорошая теоретическая и практическая базы

Минусы

Сложности с расширением и изменением
Масштабируемость

Сущности и Связи

Сущность - тот или иной объект реального (или виртуального) мира, ради хранения информации о котором и делается СУБД. Набор данных о сущностях разделяется на поля и хранится в таблицах. При разработке СУБД заранее определяются сущности предметной области и отношения и количественные отношения между ними, для установки соответствующих связей.

Связь - логическое отношение, реализуемое с помощью специальных полей в таблицах. Для установки связности СУБД используется уникальный идентификатор (поле) каждой записи (строки) в таблице.

Поля

Типы полей во многом похожи на привычные типы данных в языках программирования: числа, строки и так далее. Однако структуры данных (объекты, ассоциативные и обычные массивы) согласно теории должны быть преобразованы к тем или иным реляционным формам. Для идентификации записей используются специальный тип данных Автоинкремент, который гарантирует уникальность каждой записи в пределах таблицы.

Типы связей

Один к одному

Однозначное соответствие двух сущностей друг другу. Человек и его отпечаток пальца, например. Редкий вид связи, зачастую используется для расширения сущности дополнительными параметрами.

Один ко многим

Самый частый случай. Класс и ученики, Город и его жители, Ребенок и его родители и так далее. Связь устанавливается с помощью сохранения значения автоинкремента одного в каждом экземпляре многих.

Многие ко многим

Тоже часто встречаемый случай, достаточно сложный в реализации, так как требует введения дополнительной таблицы для связности. Например книги в библиотеке и их читатели: каждый читатель может прочитать более одной книги, каждая книга бывает прочитана более чем одним читателем. Для связи заводится смежная таблица, в которой хранятся автоинкременты обоих связываемых сущностей - например номер книги и номер паспорта читателя, а так же дополнительная информация, относящаяся к связи (например дата и время выдачи книги читателю)

ER-Диаграммы

https://www.draw.io/

ER-Диаграммы позволяют изобразить структуру реляционной СУБД в наглядном виде. Сущности отображаются таблицами с описанием полей и первичных ключей, служащих для указания связности. Связи - линиями между таблицами разных видов.

Задание 1

Нарисовать в общем виде структуру из нескольких таблиц со связями один ко многим и многие ко многим, придуманной предметной области. Сохранить в гугл-диск.

SQL

SQL (Structured Query Language, Структурированный Язык Запросов) - язык, на котором происходит взаимодействие с СУБД. Подразделяется на две части:

DDL (Data Definition Language) - служит для создания таблиц, изменения их структуры и связей между ними.
DML (Data Manipulation Language) - оперирует данными в таблицах (выборка и запись).

Так как на этом занятии мы рассматриваем структуру СУБД, то предметом нашего изучения сегодня будет DDL, а именно операции создания и изменения структуры таблиц.

Типы данных MySQL

Базы данных в целом хранят те же типы данных, что и обрабатываются компьютерными программами, однако, в силу ориентированности на хранение большого количества данных, типы указываются более точно, для экономии места и ускорения обработки. Например для строк задается их длина, есть несколько видов целых и вещественных чисел и так далее.

Числа

Целочисленные типы данных

Тип	Описание	Занимаемое место (байт)
INT	Стандартный целочисленный тип от -2147483648 до 2147483647	4
TINYINT	Стандартный целочисленный тип от -128 до 128	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:

mysql -u root

Команда выше запускает консоль mysql из под пользователя root, имеющего полный доступ ко всем базам данных. Для входа с паролем:

mysql -u root -p

Если после ключа -p указан пароль (его надо указывать без пробела), то вы сразу попадете в консоль. Иначе у Вас спросят пароль для ввода.

В консоли, за исключением редких директив, общение происходит на языке SQL. Консоль - это REPL языка SQL. Для начала создадим базу данных:

CREATE DATABASE IF NOT EXISTS test;

Команда выше создает новую базу данных test. В консоли каждое SQL-выражение оканчивается ;, таким образом mysql-клиент понимает что выражение окончено и отправляет его на исполнение. В программном коде ; не применяется в SQL-запросах.

use test;

Директива use не является частью SQL-синтаксиса. Эта директива просто указывает mysql-клиенту что в дальнейшем все операции касаются базы данных test.

SHOW DATABASES;

Этот запрос выводит список баз данных.

Создание таблиц.

Перед тем, как записывать данные в таблицы, нужно создать их, описав с помощью DDL CREATE TABLE. В общем упрощенном виде это выглядит следующим образом:

CREATE TABLE IF NOT EXISTS <tablename> (
	<nameOfField> <type>,
	<nameOfField> <type>,
	<nameOfField> <type>,
	<nameOfField> <type>,
	<nameOfField> <type>,
	<nameOfField> <type>,
	<nameOfField> <type>);

Например:

CREATE TABLE IF NOT EXISTS person (
	person_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name      VARCHAR(64),
	surname   VARCHAR(64),
	father_name VARCHAR(64)
);

Узнаем, сколько у нас таблиц в данной БД:

SHOW TABLES;

Какова структура таблицы:

DESC person;

Как создать эту таблицу:

SHOW CREATE TABLE person;

Обратите внимание, что результат последнего запроса не совпадает с запросом, с помощью которого таблица была создана. MySQL генерирует этот запрос, исходя из текущей структуры таблицы.

Модификация таблицы

Добавим поле Дата Рождения после person_id:

ALTER TABLE person ADD COLUMN date_of_birth DATE AFTER person_id;

В процессе разработки вы часто будете делать ALTER TABLE, и если потом вам надо будет создать таблицу на другом (например, production) сервере, вы можете использовать для этого SHOW CREATE TABLE:

SHOW CREATE TABLE person;

Добавление индекса

ALTER TABLE person ADD INDEX (date_of_birth);

Задание 2

Пользуясь CREATE TABLE создать структуру СУБД, разработанной вами в виде ER-диаграммы. Для ссылочных полей использовать тип INT UNSIGNED c тем же именем:

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)
);

Домашнее задание

Доделать недоделанное
Почитать о INSERT, UPDATE, DELETE, SELECT
Почитать о разных видах JOIN

ER-SQL.md 21 KB History Raw