Модуль 14. База данных MySQL, язык SQL

Question 1

Виды баз данных

Answer 1

Есть разные виды БД, самые основные это SQL и NoSQL

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

Question 2

Структура SQL Базы данных

Answer 2

Информация в БД хранится в виде таблиц, в одной БД может быть очень много таблиц.
● Таблица - набор упорядоченных данных по строкам (записям) и столбцам (полям). По своей сути
каждая таблица является отдельным файлом. Можно провести прямую аналогию с Excel таблицей.
● Поля (столбцы) в таблице определяют тип данных для колонки таблицы.
● Запись в таблице - это строка таблице. Каждая запись в одной таблице содержит одинаковый список
полей

Question 3

Виды таблиц MySQL

Answer 3

InnoDB разрабатывался специально для больших таблиц. Основным отличием от других типов является
поддержка транзакций и внешних ключей.
2
MyISAM одна из основных систем хранения данных наряду с InnoDB, подходит для небольших баз данных
где требуется полнотекстовый поиск и преобладают запросы на чтение.

Question 4

Установка и настройка MySQL

Answer 4

Чтобы установить базу данных, необходимо установить два пакета. Сервер, установит собственно сам веб-сервер mysql, а клиент, установит необходимые библиотеки, чтобы мы
могли взаимодействовать с базой данных.
sudo apt install mysql-server mysql-client

Question 5

Запуск сервера БД в консоли

Answer 5

Сервер запускается атоматически, если этого не произошло то используется команда sudo service mysql start

Question 6

Как настроить работу с БД через php скрипт

Answer 6

Для того, чтобы у нас появилась возможность работать с БД из PHP скрипта, для PHP необходимо установить
расширение для работы с MySQL
sudo apt install php-mysql

Question 7

Как подключиться к БД в консоли

Answer 7

Для подключения используется команда sudo mysql
если для БД был установлен пароль то сам пароль указывается после опции -p
sudo mysql -pPASSWORD

Question 8

Как происходит создание БД

Answer 8

Внутри одного сервера SQL может быть множество баз данных. Для создания базы данных используется команда CREATE DATABASE:
CREATE DATABASE IF NOT EXISTS example_db CHARACTER SET utf8mb4 COLLATE
utf8mb4_general_ci;
IF NOT EXISTS - необязательный параметр, указывает, что база данных будет создана, только в том случае
если не существует базы данных с таким же названием. В случае если база данных существует и данный параметр
не указан, возникнет ошибка выполнения запроса.
CHARACTER SET - Используется для установки кодировки таблиц базы данных.
COLLATE - указывает порядок сортировки. Указанный порядок сортировки должен поддерживаться текущей
кодировкой.

Question 9

Как получить список всех баз данных SQL сервера

Answer 9

Для отображения списка всех существующих баз данных используется команда SHOW DATABASES.

Question 10

Как выбрать нужную БД

Answer 10

Выбрать БД можно командоый USE
USE test_db

Question 11

Удаление БД

Answer 11

Для удаления БД используется команда DROP DATABASE test_db
Можно указать второй не обязательный параметр IF EXISTS чтобы небыло при удалении не существующей БД

Question 12

Как создать учетную запись в БД

Answer 12

Для создания учетной записи на сервере MySQL используется команда CREATE USER:
CREATE USER ‘php_user’@’%’ IDENTIFIED BY ‘pass’;
● php_user - логин нашего пользователя
● % - это адреса из каких этот пользователь может подключаться, % означает из любых
● pass - его пароль

Question 13

Выдача прав пользователю

Answer 13

Для этого используется команда GRANT.
GRANT ALL PRIVILEGES ON php_project_db.* TO ‘php_user’@’%’;
После имени базы данных, указываются таблицы. Можно указать звездочку, это значит, что права выдаются
на все таблицы в этой базе данных. А можно указать и конкретную таблицу.
ALL PRIVILEGES - означает, что пользователю выдаются привилегии на все операции, кроме возможности
выдавать доступ другим пользователям.

Question 14

Закрепление прав пользователя

Answer 14

После того как были установлены необходимые права для пользователя, необходимо применить изменения,
для этого используется команда:
FLUSH PRIVILEGES;

Question 15

Подключение к БД от другого пользователя

Answer 15

Чтобы подключиться к mysql под другим пользователем, то необходимо указать опции для команды mysql
mysql -uphp_user -ppass -hlocalhost php_project_db

● После опции -u указывается логин пользователя
● После опции -p указывается пароль пользователя
● После опции -h указывается хост для подключения к бд, в моем случае БД установлена локально,
поэтому можно указать localhost
● А в конце указано название БД, к которой осуществлено подключение

Question 16

Типы данных в БД

Answer 16

В бд у каждой колонки может быть один из 3х типов данных
числовые, строковые, время.
Для хранения даты и времени используются типы: DATETIME, TIME или DATE
Для хранения строк переменной длины используется тип VARCHAR, для большого текстового поля TEXT.
Разные числовые типы данных определяют диапазон значений, которые можно хранить в этом поле. TINYINT INT BIGINT BOOLEAN DECIMAL FLOAT DOUBLE

Question 17

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

Answer 17

Для создания таблицы используется команда CREATE TABLE tablename (
field name INT NOT NULL,
);
структура описания полей таблицы следующая, сначала идет название поля в апострофах, затем тип поля,
затем различные модификаторы, в конце указывается индекс (он же ключ).

Question 18

Какие есть модификаторы при создании полей таблицы

Answer 18

Модификаторы [NOT NULL | NULL] - обязательно ли поле для заполнения. Если не указаны эти
модификаторы, то поле может быть пустым.
AUTO_INCREMENT - может быть только у одного столбца в таблице, при добавлении записи значение записи в этом поле будет максимальным значением во всей клонке +1
PRIMARY KEY - указывает, что данный столбец является первичным ключом таблицы.
UNIQUE - указывает, что значение столбца должно быть уникальным в рамках таблицы.
DEFAULT - значение столбца по умолчанию если значение не переданно

Question 19

Просмотр структуры таблицы

Answer 19

С помощью команды DESCRIBE можно посмотреть структуру созданной таблицы
DESCRIBE users;

Question 20

Как изменить поля и данные таблицы

Answer 20

Команда ALTER TABLE позволяет менять структуру таблицы.
Для того, чтобы переименовать таблицу, используется команда RENAME:
ALTER TABLE users RENAME tmp_users;

Для добавления новой колонке, команда ADD:
ALTER TABLE tmp_users ADD age INT UNSIGNED AFTER email;

Для изменения существующего поля используется команда MODIFY:
ALTER TABLE tmp_users MODIFY age INT UNSIGNED NOT NULL DEFAULT 0;

Переименовать колонку можно с помощью команды CHANGE COLUMN. Помимо этого с ее помощью можно
делать то же, что и с помощью команды MODIFY:
ALTER TABLE tmp_users CHANGE COLUMN age tmp_age INT UNSIGNED NOT NULL
DEFAULT 0;

Чтобы удалить колонку и все ее данные, используется команда DROP:
ALTER TABLE tmp_users DROP tmp_age;

Question 21

Как удалить таблицу

Answer 21

Для удаления таблицы используется команда DROP TABLE. С ее помощью можно удалить сразу несколько
таблицы, указав их через запятую.
DROP TABLE IF EXISTS tmp_users;

Question 22

Добавление данных в таблицу

Answer 22

Для добавления данных в таблицу используется команда INSERT

INSERT INTO cars
(name, price, old_price, image, category)
VALUES
(‘K5’, 1577899, 1577900, ‘/assets/pictures/car_K5-half.png’, ‘Седан’);
Команда добавления начинается с INSERT INTO, затем указывается название таблицы, после нее в скобках
через запятую перечисляются названия колонок таблицы, следом ключевое слово VALUES и так же в скобках через
запятую значения, которые необходимо установить. Строки обрамляются кавычками.

Question 23

Изменение данных в таблице

Answer 23

Для обновления данных в таблице используется команда UPDATE.
UPDATE cars
SET name = ‘Some Car’, price = 9999999
WHERE id = 2
LIMIT 1;
Команда начинается с UPDATE и указания названия таблицы, а затем ключевое слово SET и через запятую
перечисляются поля с присваиванием значений, на которые нужно обновить поля в записях.

Question 24

Удаление данных из таблицы

Answer 24

Для удаления данных используется команда DELETE.
DELETE FROM cars WHERE id = 1 LIMIT 1;
Если ограничение не указывать то будут удалены все записи в таблице
DELETE FROM cars;

Question 25

Как выполнить экспорт БД

Answer 25

ля экспорта данных из БД используется команда mysqldump, она устанавливается вместе с mysql. Синтаксис
этой команды очень схож с командой подключения к БД.

mysqldump -uphp_user -ppass -hlocalhost php_project_db > dump.sql

Для MySQL 8
mysqldump –no-tablespaces -uphp_user -ppass -hlocalhost php_project_db > dump.sql

Команда подключается под указанным пользователем в выбранную БД и формирует SQL, необходимый для
воссоздания текущего состояния БД, вывод работы этого скрипта перенаправляют в файл, в нашем случае файл
dump.sql

Question 26

Какие нужны права доступа для создания дампа ?

Answer 26

Начиная с версии MySQL 8 немного изменились права пользователей при работе с БД, которые отразились на
создании дампа, опция –no-tablespaces поможет создать такому пользователю дамп с данными. Либо этому
пользователю нужно выдать глобальную привилегию PROCESS. Или делать дамп из-под sudo пользователя

Question 27

Как сделать ипорт БД

Answer 27

Чтобы импортировать этот дамп, в другую БД, используется команда mysql, которая будет выглядеть похожей
с созданием дампа структурой, с единственным различием, меняется направление потока файла.
mysql -uphp_user -ppass -hlocalhost db_for_import < dump.sql

Если вам нужно экспортировать не данные БД, а саму БД, вместе с ее названием, или несколько БД
одновременно, то используется опция –databases
mysqldump -uphp_user -ppass -hlocalhost –databases php_project_db db_for_import >
double_dump.sql

Для экспорта только структуры БД, без самих данных, нужно использовать опцию –no-data
mysqldump -uphp_user -ppass -hlocalhost –no-data php_project_db > structure_dump.sql

Question 28

Что такое нормализация БД

Answer 28

это метод проектирования базы данных, который позволяет привести базу данных к минимальной избыточности. При нормализации таблицы приводят в соответствие нормальным формам

Question 29

Нормальные формы

Answer 29

Чтобы база данных находилась в 1 нормальной форме, необходимо чтобы ее таблицы соблюдали следующие
реляционные принципы:
● В таблице не должно быть дублирующих строк
● В каждой ячейке таблицы хранится атомарное значение (одно не составное значение)
● В столбце хранятся данные одного типа
● Отсутствуют массивы и списки в любом виде

Чтобы база данных находилась во второй нормальной форме (2NF), необходимо чтобы ее таблицы
удовлетворяли следующим требованиям:
● Таблица должна находиться в первой нормальной форме
● Таблица должна иметь ключ
● Все неключевые столбцы таблицы должны зависеть от полного ключа (в случае если он составной)

Требование третьей нормальной формы (3NF) заключается в том, чтобы в таблицах отсутствовала
транзитивная зависимость.
Транзитивная зависимость – это когда неключевые столбцы зависят от значений других неключевых
столбцов.

Главное правило нормальной формы Бойса-Кодда (BCNF) звучит следующим образом: часть составного
первичного ключа не должна зависеть от неключевого столбца.

Question 30

Связь один ко многим

Answer 30

Когда одна запись в первой таблице имеет связь с многими записями изи бругой таблицы
SQL команда для создания таких таблиц и внешнего ключа в таблице статей
FOREIGN KEY ‘author_id_fk’ (author_id) REFERENCES users(id)
После ключевого слова FOREIGN KEY можно опционально указать
название ключа, в скобках указывается поле текущей таблицы, которое должно быть ключом. Затем идет ключевое
слово REFERENCES и указывается название таблицы и поле, на которое этот ключ ссылается.

Question 31

Связь один к одному

Answer 31

Реализуется аналогично связи один ко многим только поле содержащее внешний ключ должно быть уникальным. Для этого нужно установить поле внешнего ключя с параметром UNIQUE

Question 32

Связь многие-ко-многим

Answer 32

Связь когда множество значений из одной таблицы связываются с множеством значений из другой таблицы
Сводная таблица должна содержать два поля id каждой из моделей. Наименование таблицы правильно делать
так: в алфавитном порядке через подчеркивание название моделей в единственном числе. Также в этой связующей
таблице должен быть установлен составной первичный ключ из этих двух полей, чтобы гарантировать
уникальность каждой пары id.
PRIMARY KEY (car_id, color_id),
FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE,
FOREIGN KEY (color_id) REFERENCES colors(id) ON DELETE CASCADE

ON DELETE CASCADE будет автоматически удалять записи из этой таблицы, при удалении записей
из основных

Question 33

Повтори просто SQL :)

Answer 33

хорошо)