Модуль 14. База данных MySQL, язык SQL Flashcards
Виды баз данных
Есть разные виды БД, самые основные это SQL и NoSQL
SQL - это реляционная база данных. Такие БД хранят информацию в строго структурированном виде.
NoSQL - это не реляционная база данных. В ней строгой типизации, данные хранятся в формате документов,
каждый документ - это набор множества взаимосвязанных моделей.
Структура SQL Базы данных
Информация в БД хранится в виде таблиц, в одной БД может быть очень много таблиц.
● Таблица - набор упорядоченных данных по строкам (записям) и столбцам (полям). По своей сути
каждая таблица является отдельным файлом. Можно провести прямую аналогию с Excel таблицей.
● Поля (столбцы) в таблице определяют тип данных для колонки таблицы.
● Запись в таблице - это строка таблице. Каждая запись в одной таблице содержит одинаковый список
полей
Виды таблиц MySQL
InnoDB разрабатывался специально для больших таблиц. Основным отличием от других типов является
поддержка транзакций и внешних ключей.
2
MyISAM одна из основных систем хранения данных наряду с InnoDB, подходит для небольших баз данных
где требуется полнотекстовый поиск и преобладают запросы на чтение.
Установка и настройка MySQL
Чтобы установить базу данных, необходимо установить два пакета. Сервер, установит собственно сам веб-сервер mysql, а клиент, установит необходимые библиотеки, чтобы мы
могли взаимодействовать с базой данных.
sudo apt install mysql-server mysql-client
Запуск сервера БД в консоли
Сервер запускается атоматически, если этого не произошло то используется команда sudo service mysql start
Как настроить работу с БД через php скрипт
Для того, чтобы у нас появилась возможность работать с БД из PHP скрипта, для PHP необходимо установить
расширение для работы с MySQL
sudo apt install php-mysql
Как подключиться к БД в консоли
Для подключения используется команда sudo mysql
если для БД был установлен пароль то сам пароль указывается после опции -p
sudo mysql -pPASSWORD
Как происходит создание БД
Внутри одного сервера SQL может быть множество баз данных. Для создания базы данных используется команда CREATE DATABASE:
CREATE DATABASE IF NOT EXISTS example_db CHARACTER SET utf8mb4 COLLATE
utf8mb4_general_ci;
IF NOT EXISTS - необязательный параметр, указывает, что база данных будет создана, только в том случае
если не существует базы данных с таким же названием. В случае если база данных существует и данный параметр
не указан, возникнет ошибка выполнения запроса.
CHARACTER SET - Используется для установки кодировки таблиц базы данных.
COLLATE - указывает порядок сортировки. Указанный порядок сортировки должен поддерживаться текущей
кодировкой.
Как получить список всех баз данных SQL сервера
Для отображения списка всех существующих баз данных используется команда SHOW DATABASES.
Как выбрать нужную БД
Выбрать БД можно командоый USE
USE test_db
Удаление БД
Для удаления БД используется команда DROP DATABASE test_db
Можно указать второй не обязательный параметр IF EXISTS чтобы небыло при удалении не существующей БД
Как создать учетную запись в БД
Для создания учетной записи на сервере MySQL используется команда CREATE USER:
CREATE USER ‘php_user’@’%’ IDENTIFIED BY ‘pass’;
● php_user - логин нашего пользователя
● % - это адреса из каких этот пользователь может подключаться, % означает из любых
● pass - его пароль
Выдача прав пользователю
Для этого используется команда GRANT.
GRANT ALL PRIVILEGES ON php_project_db.* TO ‘php_user’@’%’;
После имени базы данных, указываются таблицы. Можно указать звездочку, это значит, что права выдаются
на все таблицы в этой базе данных. А можно указать и конкретную таблицу.
ALL PRIVILEGES - означает, что пользователю выдаются привилегии на все операции, кроме возможности
выдавать доступ другим пользователям.
Закрепление прав пользователя
После того как были установлены необходимые права для пользователя, необходимо применить изменения,
для этого используется команда:
FLUSH PRIVILEGES;
Подключение к БД от другого пользователя
Чтобы подключиться к mysql под другим пользователем, то необходимо указать опции для команды mysql
mysql -uphp_user -ppass -hlocalhost php_project_db
● После опции -u указывается логин пользователя
● После опции -p указывается пароль пользователя
● После опции -h указывается хост для подключения к бд, в моем случае БД установлена локально,
поэтому можно указать localhost
● А в конце указано название БД, к которой осуществлено подключение
Типы данных в БД
В бд у каждой колонки может быть один из 3х типов данных
числовые, строковые, время.
Для хранения даты и времени используются типы: DATETIME, TIME или DATE
Для хранения строк переменной длины используется тип VARCHAR, для большого текстового поля TEXT.
Разные числовые типы данных определяют диапазон значений, которые можно хранить в этом поле. TINYINT INT BIGINT BOOLEAN DECIMAL FLOAT DOUBLE
Как создать таблицу
Для создания таблицы используется команда CREATE TABLE tablename (field name INT NOT NULL,
);
структура описания полей таблицы следующая, сначала идет название поля в апострофах, затем тип поля,
затем различные модификаторы, в конце указывается индекс (он же ключ).
Какие есть модификаторы при создании полей таблицы
Модификаторы [NOT NULL | NULL] - обязательно ли поле для заполнения. Если не указаны эти
модификаторы, то поле может быть пустым.
AUTO_INCREMENT - может быть только у одного столбца в таблице, при добавлении записи значение записи в этом поле будет максимальным значением во всей клонке +1
PRIMARY KEY - указывает, что данный столбец является первичным ключом таблицы.
UNIQUE - указывает, что значение столбца должно быть уникальным в рамках таблицы.
DEFAULT - значение столбца по умолчанию если значение не переданно
Просмотр структуры таблицы
С помощью команды DESCRIBE можно посмотреть структуру созданной таблицы
DESCRIBE users;
Как изменить поля и данные таблицы
Команда 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;
Как удалить таблицу
Для удаления таблицы используется команда DROP TABLE. С ее помощью можно удалить сразу несколько
таблицы, указав их через запятую.
DROP TABLE IF EXISTS tmp_users;
Добавление данных в таблицу
Для добавления данных в таблицу используется команда INSERT
INSERT INTO cars
(name, price, old_price, image, category)
VALUES
(‘K5’, 1577899, 1577900, ‘/assets/pictures/car_K5-half.png’, ‘Седан’);
Команда добавления начинается с INSERT INTO, затем указывается название таблицы, после нее в скобках
через запятую перечисляются названия колонок таблицы, следом ключевое слово VALUES и так же в скобках через
запятую значения, которые необходимо установить. Строки обрамляются кавычками.
Изменение данных в таблице
Для обновления данных в таблице используется команда UPDATE.
UPDATE cars
SET name = ‘Some Car’, price = 9999999
WHERE id = 2
LIMIT 1;
Команда начинается с UPDATE и указания названия таблицы, а затем ключевое слово SET и через запятую
перечисляются поля с присваиванием значений, на которые нужно обновить поля в записях.
Удаление данных из таблицы
Для удаления данных используется команда DELETE.
DELETE FROM cars WHERE id = 1 LIMIT 1;
Если ограничение не указывать то будут удалены все записи в таблице
DELETE FROM cars;
Как выполнить экспорт БД
ля экспорта данных из БД используется команда 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
Какие нужны права доступа для создания дампа ?
Начиная с версии MySQL 8 немного изменились права пользователей при работе с БД, которые отразились на
создании дампа, опция –no-tablespaces поможет создать такому пользователю дамп с данными. Либо этому
пользователю нужно выдать глобальную привилегию PROCESS. Или делать дамп из-под sudo пользователя
Как сделать ипорт БД
Чтобы импортировать этот дамп, в другую БД, используется команда 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
Что такое нормализация БД
это метод проектирования базы данных, который позволяет привести базу данных к минимальной избыточности. При нормализации таблицы приводят в соответствие нормальным формам
Нормальные формы
Чтобы база данных находилась в 1 нормальной форме, необходимо чтобы ее таблицы соблюдали следующие
реляционные принципы:
● В таблице не должно быть дублирующих строк
● В каждой ячейке таблицы хранится атомарное значение (одно не составное значение)
● В столбце хранятся данные одного типа
● Отсутствуют массивы и списки в любом виде
Чтобы база данных находилась во второй нормальной форме (2NF), необходимо чтобы ее таблицы
удовлетворяли следующим требованиям:
● Таблица должна находиться в первой нормальной форме
● Таблица должна иметь ключ
● Все неключевые столбцы таблицы должны зависеть от полного ключа (в случае если он составной)
Требование третьей нормальной формы (3NF) заключается в том, чтобы в таблицах отсутствовала
транзитивная зависимость.
Транзитивная зависимость – это когда неключевые столбцы зависят от значений других неключевых
столбцов.
Главное правило нормальной формы Бойса-Кодда (BCNF) звучит следующим образом: часть составного
первичного ключа не должна зависеть от неключевого столбца.
Связь один ко многим
Когда одна запись в первой таблице имеет связь с многими записями изи бругой таблицы
SQL команда для создания таких таблиц и внешнего ключа в таблице статей
FOREIGN KEY ‘author_id_fk’ (author_id) REFERENCES users(id)
После ключевого слова FOREIGN KEY можно опционально указать
название ключа, в скобках указывается поле текущей таблицы, которое должно быть ключом. Затем идет ключевое
слово REFERENCES и указывается название таблицы и поле, на которое этот ключ ссылается.
Связь один к одному
Реализуется аналогично связи один ко многим только поле содержащее внешний ключ должно быть уникальным. Для этого нужно установить поле внешнего ключя с параметром UNIQUE
Связь многие-ко-многим
Связь когда множество значений из одной таблицы связываются с множеством значений из другой таблицы
Сводная таблица должна содержать два поля 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 будет автоматически удалять записи из этой таблицы, при удалении записей
из основных
Повтори просто SQL :)
хорошо)
