26  Реляционные базы данных на примере MySQL. Проектирование простой базы данных

26.1 Настройка и конфигурация сервера

Дисклеймер

Работу можно выполнять на любой операционной системе, но я рекомендую на основе Ubuntu и инструкцию буду приводить по ней. Обязательно покажите преподавателю, что у вас получилось настроить сервер.

26.1.1 Литература

26.1.2 Установка сервера

Оригинал инструкции

  1. Перейти на страницу скачивания MySQL APT репозитория

  2. Выбрать и скачать дистрибутив для своей ОС

  3. Установить скачанный конфиг

    sudo dpkg -i mysql-apt-config_w.x.y-z_all.deb

  4. Во время установки пакета вам будет предложено выбрать версии сервера MySQL и других компонентов (например, MySQL Workbench), которые вы хотите установить. Если вы не уверены, какую версию выбрать, не меняйте выбранные для вас параметры по умолчанию. Вы также можете выбрать none, если не хотите, чтобы какой-либо компонент устанавливался. После выбора всех компонентов нажмите Ok , чтобы завершить настройку и установку пакета релиза. MySQL Workbench это среда работы с БД. Я использую Dbeaver, но необязательно использовать именно его.

  5. Обязательно обновить apt

    sudo apt-get update

  6. Установить сервер

    sudo apt-get install mysql-server

  7. Проверить работу

    sudo systemctl status mysql

26.1.3 Создание пользователей и регулирование прав

  1. Необходимо сконфигурировать корневого пользователя

    sudo mysql_secure_installation # Возможны варианты sudo mysql или sudo mariadb-secure-installation

  2. Устанавливает новый пароль в соответствии с рекомендациями.

    ВАЖНО

    СОХРАНИТЕ СЕБЕ ПАРОЛЬ ОТ ROOT! ЗАПИШИТЕ НА БУМАЖКУ, ДЕРЖИТЕ ПРИ СЕБЕ И НЕ ТЕРЯЙТЕ! ЗАМУЧАЕТЕСЬ ВОССТАНАВЛИВАТЬ!

  3. Чтобы подключиться к серверу СУБД воспользуйтесь следующей командой:

    sudo mysql -u root -p

  4. Продолжаем работу и создаем нового пользователя. Строки кода пронумерованы, аннотация под ячейкой списком.

    CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password'; # <1>
CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; # <2> 
ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; # <3>
    1. Всё управление осуществляется с помощью SQL запросов. Это синтаксис по созданию пользователя
    2. Конкретный пример создания пользователя
    3. Конкретный пример изменения пользователя

  5. Выдаем права. Про существующие права (привилегии в терминологии MySQL) можно почитать в документации.

GRANT PRIVILEGE ON database.table TO 'username'@'host'; # <1>
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION; # <2>
FLUSH PRIVILEGES; # <3>
  1. Синтаксис на выдачу привелегий. Конкретные привелегии выдаются конкретному пользователю, подключающемуся с определенного диапазона адресов на конкретную таблицу(-ы) конкретной БД
  2. Пример выдачи прав. Все привелегии на все базы данных и на все таблицы в них выданы sammy, подключаещумуся с локального хоста с правом наделения других пользователей привелегиями. Крайне небезопасная операция.
  3. После каждого изменения привелегий их надо зафиксировать в СУБД этой командой. Отозвать права можно аналогичным образом запросом REVOKE.

26.2 Проектирование спецификации будущей БД

26.2.1 Входные данные

Представьте, что вы работаете в мультидисциплинарной команде врачей-клиницистов и лабораторных генетиков. Все данные, которые они получают складывают в одну большую гугл-таблицу, в которой регулярно все путаются, заносят некорректные данные, кто-то что-то стирает не в том месте и тому подобное. Вы после долгих часов ругательств и кропотливой работы смогли причесать эту таблицу и она доступна по ссылке. Вы проделали первый этап по систематизации бардака в команде. Второй этап это создание БД, а третий этап - создание небольшой информационной системы для доступа к этой БД. Третий этап мы пока опустим. Сейчас необходимо спроектировать БД.

  1. Необходимо построить концептуальную модель: сколько и каких будет таблиц. Кто с кем будет связан?
  2. Построить логическую модель (ER-диаграмму). С описанием типов связей, содержимого отношений.
  3. Построить физическую модель. Описать домены атрибутов, типы данных MySQL возможные ограничения (constraints)

Всё вместе называется спецификацией БД.

Типы ограничений:

  • NOT NULL - В столбце не должно быть пропущенных значений

  • UNIQUE - В столбце все значения уникальны, нет одинаковых

  • PRIMARY KEY - Комбинация NOT NULL and UNIQUE. Определение первичного ключа

  • FOREIGN KEY - Внешний ключ. Предотвращает действия по уничтожению связей между таблицами

  • CHECK - Проверяет соблюдения условия

  • DEFAULT - Устанавливает значение по умолчанию

  • CREATE INDEX - Атрибут используется в созаднии индекса

26.2.2 Типы данных в MySQL

Подробно прочитать про типы данных можно почитать в документации. Здесь цитируется следующий источник(n.d.). Важно помнить, что в отличии Python здесь никто кроме вас не позаботиться о типах данных.

26.2.2.1 Символьные

  • CHAR: представляет строку фиксированной длины.

    Длина хранимой строки указывается в скобках, например, CHAR(10) - строка из десяти символов. И если в таблицу в данный столбец сохраняется строка из 6 символов (то есть меньше установленной длины в 10 символов), то строка дополняется 4 пробелами и в итоге все равно будет занимать 10 символов

    Тип CHAR может хранить до 255 байт.

  • VARCHAR: представляет строку переменной длины.

    Длина хранимой строки также указыватся в скобках, например, VARCHAR(10). Однако в отличие от CHAR хранимая строка будет занимать именно столько места, сколько необходимо. Например, если определенная длина в 10 символов, но в столбец сохраняется строка в 6 символов, то хранимая строка так и будет занимать 6 символов плюс дополнительный байт, который хранит длину строки.

    Всего тип VARCHAR может хранить до 65535 байт.

Начиная с MySQL 5.6 типы CHAR и VARCHAR по умолчанию используют кодировку UTF-8, которая позволяет использовать до 3 байт для хранения символа в зависимости от языка ( для многих европейских языков по 1 байту на символ, для ряда восточно-европейских и ближневосточных - 2 байта, а для китайского, японского, корейского - по 3 байта на символ).

Ряд дополнительных типов данных представляют текст неопределенной длины:

  • TINYTEXT: представляет текст длиной до 255 байт.

  • TEXT: представляет текст длиной до 65 КБ.

  • MEDIUMTEXT: представляет текст длиной до 16 МБ

  • LONGTEXT: представляет текст длиной до 4 ГБ

26.2.2.2 Числовые

  • TINYINT: представляет целые числа от -128 до 127, занимает 1 байт

  • BOOL: фактически не представляет отдельный тип, а является лишь псевдонимом для типа TINYINT(1) и может хранить два значения 0 и 1. Однако данный тип может также в качестве значения принимать встроенные константы TRUE (представляет число 1) и FALSE (предоставляет число 0).

    Также имеет псевдоним BOOLEAN.

  • TINYINT UNSIGNED: представляет целые числа от 0 до 255, занимает 1 байт

  • SMALLINT: представляет целые числа от -32768 до 32767, занимает 2 байтa

  • SMALLINT UNSIGNED: представляет целые числа от 0 до 65535, занимает 2 байтa

  • MEDIUMINT: представляет целые числа от -8388608 до 8388607, занимает 3 байта

  • MEDIUMINT UNSIGNED: представляет целые числа от 0 до 16777215, занимает 3 байта

  • INT: представляет целые числа от -2147483648 до 2147483647, занимает 4 байта

  • INT UNSIGNED: представляет целые числа от 0 до 4294967295, занимает 4 байта

  • BIGINT: представляет целые числа от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807, занимает 8 байт

  • BIGINT UNSIGNED: представляет целые числа от 0 до 18 446 744 073 709 551 615, занимает 8 байт

  • DECIMAL: хранит числа с фиксированной точностью. Данный тип может принимать два параметра precision и scale: DECIMAL(precision, scale).

    Параметр precision представляет максимальное количество цифр, которые может хранить число. Это значение должно находиться в диапазоне от 1 до 65.

    Параметр scale представляет максимальное количество цифр, которые может содержать число после запятой. Это значение должно находиться в диапазоне от 0 до значения параметра precision. По умолчанию оно равно 0.

    Данный тип также имеет псевдонимы NUMERIC, DEC, FIXED.

  • FLOAT: хранит дробные числа с плавающей точкой одинарной точности от -3.4028 * 1038 до 3.4028 * 1038, занимает 4 байта

    Может принимать форму FLOAT(M,D), где M - общее количество цифр, а D - количество цифр после запятой

  • DOUBLE: хранит дробные числа с плавающей точкой двойной точности от -1.7976 * 10308 до 1.7976 * 10308, занимает 8 байт. Также может принимать форму DOUBLE(M,D), где M - общее количество цифр, а D - количество цифр после запятой.

    Данный тип также имеет псевдонимы REAL и DOUBLE PRECISION, которые можно использовать вместо DOUBLE.

26.2.2.3 Дата и время

  • DATE: хранит даты с 1 января 1000 года до 31 деабря 9999 года (c “1000-01-01” до “9999-12-31”). По умолчанию для хранения используется формат yyyy-mm-dd. Занимает 3 байта.

  • TIME: хранит время от -838:59:59 до 838:59:59. По умолчанию для хранения времени применяется формат “hh:mm:ss”. Занимает 3 байта.

  • DATETIME: объединяет время и дату, диапазон дат и времени - с 1 января 1000 года по 31 декабря 9999 года (с “1000-01-01 00:00:00” до “9999-12-31 23:59:59”). Для хранения по умолчанию используется формат “yyyy-mm-dd hh:mm:ss”. Занимает 8 байт

  • TIMESTAMP: также хранит дату и время, но в другом диапазоне: от “1970-01-01 00:00:01” UTC до “2038-01-19 03:14:07” UTC. Занимает 4 байта

  • YEAR: хранит год в виде 4 цифр. Диапазон доступных значений от 1901 до 2155. Занимает 1 байт.

26.2.2.4 Составные

  • ENUM: хранит одно значение из списка допустимых значений. Занимает 1-2 байта

  • SET: может хранить несколько значений (до 64 значений) из некоторого списка допустимых значений. Занимает 1-8 байт.

26.2.2.5 Бинарные

  • TINYBLOB: хранит бинарные данные в виде строки длиной до 255 байт.

  • BLOB: хранит бинарные данные в виде строки длиной до 65 КБ.

  • MEDIUMBLOB: хранит бинарные данные в виде строки длиной до 16 МБ

  • LONGBLOB: хранит бинарные данные в виде строки длиной до 4 ГБ

26.3 Задача

  1. Установить сервер
  2. Настроить сервер и создать пользователя с правами отличного от root
  3. Обязательно должен быть выключен доступ на сервер для анонимных пользователей и доступ без пароля. Дополнительно посмотреть, как это настроить.
  4. Разработать спецификацию для вашей будущей таблицы по вашим входным данным.

26.4 Вопросы

  1. Базы данных. Анатомия БД в MySQL
  2. Пользователи и их привелегии
  3. Принципы проектирования БД. Нормализация отношений.
  4. Отношение. Строение и виды отношений
  5. ER-диаграмма.
  6. Типы данных в MySQL