БД, SQL, MySQL

База данных (БД)

это набор сведений, хранящихся некоторым упорядоченным образом. На англ. база данных - database (DB).

Используют для работы с базами данных

системы управления базами данных (СУБД). На англ. – Database Management System (DBMS).

Система управления базами данных (СУБД)

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

Преимущества использования БД

1. Хранилище данных с высокой производительностью и сжатием;
2. Настройка прав доступа;
3. Транзитивность;
4. Журналирование (логирование);
5. Удобные языки;
6. Проверка валидации данных;
7. Удобные программы для работы с базами данных.

Транзитивность (основы БД)

в случае ошибки данные откатываются к предыдущему корректному сохраненному состоянию.

Журналирование (логирование) (основы БД)

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

Виды БД

1. Реляционные;
2. Сетевые;
3. Объектно-ориентированные.

Способ хранения данных в реляционной БД

данные хранятся в виде таблиц.

Способ хранения данных в сетевой БД

данные хранятся в виде графа (связанных между собой узлов).

Способ хранения данных в объектно-ориентированной БД

данные хранятся в виде объектов.

Популярные реляционные БД

1. Oracle;
2. MySQL;
3. MS SQL;
4. PostgreSQL;
5. SQLite.

Классификация по способу доступа БД

1. Клиент-серверная СУБД;
2. Встраиваемая СУБД.

Популярные клиент-серверные СУБД

1. Oracle;
2. MySQL;
3. MS SQL;
4. PostgreSQL.

Популярные встраиваемые СУБД.

SQLite.

Суть клиент-серверной СУБД

Сама СУБД установлена на сервере, и БД находятся на сервере. Клиенты делают запросы к серверу СУБД.

Суть встраиваемой СУБД

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

NoSQL БД

общее название для не реляционных СУБД.

Например:
MongoDB, Redis, Neo4j.

Таблица в реляционной БД

это хранилище сущностей (данных) одного вида

Как устроена таблица реляционной БД

состоит из столбцов (их еще называют поля и атрибуты) и строк (их еще называют записями и кортежами).

Столбец таблицы реляционной БД

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

Строка таблицы реляционной БД

отвечают за конкретные сущности (данные), которые хранятся в таблице.

Представление понятий "Таблица", "столбец" и "строка" по аналогии с ООП

Таблица это класс, Столбцы это поля, Строки это объекты класса.

Какие строки запрещено иметь в таблице реляционной БД?

полностью одинаковые строки.

Необходимый минимум в таблицах реляционной БД

может не быть ни одной строки, но должен быть хотя бы один столбец.

Порядок столбцов в таблице

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

Первичный ключ

Таблица должна иметь некоторое поле (или совокупность полей), по которым можно однозначно найти любую строку

Второе название первичного ключа который состоит из нескольких столбцов

составным.

Идентификатор (ID) первичного ключа

первичный ключ не имеющий осмысленных данных имеет отдельный числовой столбец.

Суррогатный ключ

первичный ключ который не несет определенного информационного смысла.

Важное свойство первичного ключа

уникальность т.е. не должен повторяться в пределах таблицы.

Если в таблице не создать первичный ключ, то что им будет являться?

В таком случае первичным ключом является вся строка.

Виды связей таблиц

1. Один ко многим;
2. Многие ко многим;
3. Один к одному.

Связь таблиц
"один ко многим"

каждой строке одной таблицы может соответствовать много строк в другой таблице.

Способы связи
"один ко многим"

Способ с внешним ключом и способ с вспомогательной таблицей.

Связь таблиц
"многие ко многим"

каждой строка первой таблицы имеет много строк во второй, а также каждая строка второй таблицы имеет множество строк в первой.

Способы связи
"многие ко многим"

Способ с вспомогательной таблицей.

Связь таблиц
"один к одному"

каждой строке одной таблицы соответствует одна строка в другой таблице.

Способы связи
"один к одному"

1. Обычно моделируют в одну таблицу;
2. Сделать первичный ключ одной из таблиц внешним ключом на другую таблицу;
3. Добавить внешний ключ в любую из таблиц. Но при этом на него нужно будет навесить ограничение уникальности;
4. Через вспомогательную таблицу. Но нужно навесить ограничение уникальности на оба внешних ключа из этой таблицы.

Преимущества связи способом с внешним ключом

Проще в реализации. Работает быстрее.

Преимущества и недостатки способом с вспомогательной таблицей

Сложнее в реализации. Работает медленно. Но зато мы не меняем данные в таблицах.

Нормальная форма (НФ)

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

Функциональная зависимость

ситуация, когда некоторое поле (или совокупность полей) напрямую зависит от другого поля (или совокупности полей).

В чем смысл использования нормальной формы (НФ)

устранение избыточности данных - чтобы каждая единица данных хранилась всего 1 раз.

Количество нормальных форм

6

Требования к первой нормальной форме

Все поля являются атомарными, т.е. неделимыми.
Нет одинаковых строк в таблице.

Требования ко второй нормальной форме

Соблюдена 1 НФ. Каждое не ключевое поле неприводимо (функционально полно) зависит от первичного ключа.

Неприводимость

не ключевое поле зависит от всего ключа в целом, а не только от его части.

Требования к третьей нормальной форме

Соблюдена 2 НФ.
Каждое не ключевое поле не транзитивно зависит от первичного ключа. Т.е. имеется в виду, что каждое не ключевое поле зависит именно от первичного ключа, а не от какого-то другого не ключевого поля.

SQL

"Structured Query Language" язык запросов, позволяющий создавать/изменять/удалять базы данных, а также искать в них данные. Является стандартом, т.е. четко задокументирован, его синтаксис и конструкции и т.д.

В каком регистре в SQL можно писать ключевые слова, имена таблиц и так далее?

в любом.

Кавычки в которые рекомендуется заключать литералы строк в SQL

в одинарные.

Если внутри строки потребуется написать знак ', то что нужно сделать? (SQL)

нужно удвоить одинарную кавычку.

Типы комментариев в SQL

однострочные и многострочные.

Синтаксис однострочного комментария

-- Комментарий

Синтаксис многострочного комментария

/* Комментарий */

Команда для создания базы данных в SQL

CREATE DATABASE имя_базы;

Что нужно делать если имя БД, таблиц, столбцов являются зарезервированным словом или содержат спец символы?

заключить в кавычки `. Например: SELECT * FROM `products`

Команда для выбора нужной базы данных в SQL

USE имя_базы;

Команда создания таблицы в SQL

CREATE TABLE имя_таблицы ( перечень_столбцов);

Обозначить что данное поле является первичным ключом

id INT AUTO_INCREMENT PRIMARY KEY или так PRIMARY KEY (id1, id2)

Что означает NULL если его принимает поле?

отсутствие данных.

Можно ли помечать поля на то могут они принимать NULL или нет?

да

Обозначить поле что бы оно принимала NULL

name INT NULL

Обозначить поле что бы оно не принимала NULL

name INT NOT NULL

Обозначение чтобы значения полей были уникальными

name INT UNIQUE

Присвоить полю значение по умолчанию

price INT DEFAULT 0

Проверка корректности при вставке и изменении данных (SQL)

price INT NOT NULL CHECK (price > 0)

Команда для удаления базы данных

DROP DATABASE имя_базы_данных;

Команда для удаления таблицы

DROP TABLE имя_таблицы;

Создание внешнего ключа в таблице

FOREIGN KEY (supplierId) REFERENCES suppliers(id)

Как написать запрос чтобы удалить фиксированное количество строк, например 4?

DELETE FROM products
WHERE id = 3
LIMIT 4;

Внешний ключ

это когда таблица содержит столбец (или совокупность столбцов), который является первичным ключом в другой таблице.

Требования при создании таблиц

если у вас БД из нескольких таблиц, то очень важен порядок создания таблиц. В объявлении внешних ключей нельзя ссылаться на таблицы, которые еще не существуют.

Варианты поведения при настройке внешнего ключа

1. Удаление ON DELETE из главной таблицы;
2. Обновление строки ON UPDATE из главной таблицы.

Какие варианты для команды ON DELETE и ON UPDATE вы помните?

1. NO ACTION;
2. CASCADE;
3. RESTRICT;
4. SET NULL;
5. SET DEFAULT.

Команда для вставки строки в таблицу

INSERT INTO имя_таблицы(имя_столбца1, имя_столбца2) VALUES (значение_столбца1, значение_столбца2);

Команда для удаления всех строк в таблице

DELETE FROM имя_таблицы;

Команда
ON DELETE NO ACTION

выдает ошибку если кто-то ссылается на строку главной таблицы, удаление не делается.

Команда
ON UPDATE NO ACTION

выдает ошибку если кто-то ссылается на строку главной таблицы, обновление не делается.

Команда
ON DELETE CASCADE

каскадное удаление. Если из главной таблицы удаляется строка, то удаляются все связанные строки других таблиц.

Команда
ON UPDATE CASCADE

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

Команда
ON DELETE RESTRICT

аналогично NO ACTION. Выдает ошибку если кто-то ссылается на строку главной таблицы, удаление не делается.

Команда
ON UPDATE RESTRICT

аналогично NO ACTION. Выдает ошибку если кто-то ссылается на строку главной таблицы, обновление не делается.

Команда
ON DELETE SET NULL

выставить NULL во внешнем ключе при удалении строки из главной таблицы.

Команда
ON UPDATE SET NULL

выставить NULL во внешнем ключе при изменении первичного ключа строки из главной таблицы.

Команда
ON DELETE SET DEFAULT

выставить значение по умолчанию для ON DELETE или NULL, если значения по умолчанию нет.

Команда
ON UPDATE SET DEFAULT

выставить значение по умолчанию для ON UPDATE или NULL, если значения по умолчанию нет.

Команда
CREATE DATABASE

создает базу данных.

Команда
USE

служит для выбора нужной базы данных.

Команда
CREATE TABLE

создает таблицу.

Команда
UNIQUE

не позволяет повторяться значениям поля.

Команда
DEFAULT

позволяет задать значение поля по умолчанию.

Команда
CHECK
(SQL)

проверить корректность при вставке и изменении данных.

Команда
DROP DATABASE

удаление базы данных.

Команда
DROP TABLE

удаление таблицы.

Команда
INSERT INTO имя_таблицы(столбец1, столбец2) VALUES ('строка', 5)

вставляет строки в таблицу.

Команда
DELETE FROM имя_таблицы;

удаляет все строки из таблицы.

Команда для удаления определенного количества строк в таблице.
(пример)

Так можно удалить 7 строк:
DELETE FROM products
WHERE id = 3
LIMIT 7;

Группы типов данных в SQL

1. Числовые;
2. Строковые;
3. Дата и время.

Типы данных для целых чисел и их размеры

1. TINYINT 1 байт;
2. SMALLINT 2 байта;
3. MEDIUMINT 3 байта;
4. INT или INTEGER 4 байта;
5. BIGINT 8 байт.

Типы данных для вещественных чисел и их размеры

1. FLOAT 4 байта;
2. DOUBLE 8 байт;
3. DECIMAL(M, D).

Строковые типы данных и их размеры

1. CHAR(M) M от 0 до 255;
2. VARCHAR(M) M от 0 до 65535.

Какой из строковых типов рекомендуется использовать по умолчанию?

VARCHAR, а CHAR только если данные фиксированной длины.

Типы данных для даты и времени

1. DATE;
2. TIME;
3. DATETIME;
4. TIMESTAMP.

Форматы записи даты и времени

Дата: ‘2021-12-31’ (год-месяц-день);

Время: ’12:34:31’;

Дата и время: ‘2021-12-31 12:34:31’.

Команда для изменения данных в строке

UPDATE

Предназначение команды UPDATE (пример)

Изменение данных строки.

Например:
UPDATE products
SET price = ‘Inter Core i5’
WHERE id = 4;

Арифметические операции в SQL

1. сложение;
2. вычитание;
3. деление;
4. умножение.

Hаилучший стиль для имен таблиц, столбцов и т.д.

лучше использовать единую нотацию или с _ или же верблюжью нотацию.

Команда
SELECT

используется для получения данных из одной или нескольких таблиц.

Команда для получения данных из одной или нескольких таблиц

SELECT

Команда
CONCAT

стандартная функция, объединяющая переданные значения в одну строку.

Команда для объединения переданных значений в одну строку

CONCAT

Оператор
AS

задает алиас (псевдоним) - в общем, дает полученному столбцу имя.

Оператор для задания столбцу имени (псевдонима)

AS

Команда
ORDER BY

отсортировать результат запроса по возрастанию.

Команда для сортировки результата запроса по возрастанию

ORDER BY имяСтолбца

Команда для сортировки результата запроса по убыванию

ORDER BY имяСтолбца ASC

Команда
ORDER BY имяСтолбца ASC

сортирует результат запроса по убыванию.

Явное указание сортировки по возрастанию

ORDER BY имяСтолбца DESC

Команда
ORDER BY имяСтолбца DESC

сортирует результат запроса по возрастанию.

Команда
WHERE

для фильтрации строк по заданному условному выражению.

Команда для фильтрования строк

WHERE

Операторы сравнения используемые в SQL

1. =
2. <>
3. >
4. <
5. <=
6. >=

Операторы для обозначения логических связок

1. AND;
2. OR;
3. NOT.

Оператор
AND

логическое "И" (конъюнкция). Результат истинный только если все подусловия истинны.

Оператор
OR

логическое "ИЛИ" (дизъюнкция). Результат истинный, если хотя бы одно из подусловий истинно.

Оператор
NOT

логическое отрицание. Инвертирует значение логического выражения.

Какой командой в выражении можно проверить значения используя конъюнкцию?

AND

Какой командой в выражении можно проверить значения используя дизъюнкцию?

OR

Какой командой в выражении можно инвертировать логическое условие?

NOT

Конструкции для проверки значения на NULL

1. IS NULL;
2. IS NOT NULL.

Конструкция для проверки что значение является NULL

IS NULL

Конструкция
IS NULL

для проверки что значение является NULL.

Конструкция для проверки что значение не является NULL

IS NOT NULL

Конструкция
IS NOT NULL

для проверки что значение не является NULL.

Оператор
LIKE

оператор который позволяет проверить соответствие строки некоторому шаблону.

Оператор для проверки соответствие строки некоторому шаблону

LIKE

Специальные символы которые можно использовать при написании шаблона с использованием оператора LIKE

1. % означает любое количество любых символов (может быть и 0 символов);
2. _ означает 1 символ.

Пример того как можно используя LIKE получить товар с именем из 5 символов.

SELECT * FROM products
WHERE name LIKE ‘_____’;

Пример того как можно используя LIKE получить товар с именем начинающимся со слова "Intel"

SELECT * FROM products
WHERE name LIKE ‘Intel%’;

Что делать если мы используем оператор LIKE и нужно чтобы в строке использовались символы _ и %?

используем экранирование с помощью символа \.

Например так:
SELECT * FROM products
WHERE name LIKE ‘%\_%’;

Команда для проверки что число входит в заданный диапазон

BETWEEN

Оператор
BETWEEN

осуществляет проверку что число входит в заданный диапазон.

Варианты использования команды BETWEEN
(пример)

Первый вариант:
WHERE price >= 100 AND price <= 1000;

Второй вариант:
WHERE price BETWEEN 100 AND 1000;

Оператор
IN

для проверки что значение поля имеет одно из некоторых значений.

Оператор
NOT IN

для проверки что значение поля НЕ имеет одно из некоторых значений.

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

IN.

Например так:
SELECT * FROM requests
WHERE status IN (1, 2);

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

NOT IN.

Например так:
SELECT * FROM requests
WHERE status NOT IN (1, 2);

Оператор
GROUP BY

для группирования строк по некоторым значениям.

Команда для группирования строки по некоторому значению

GROUP BY

Агрегатные функции

1. MAX;
2. MIN;
3. COUNT;
4. AVG;
5. SUM.

Агрегатная функция MAX

вычисляет наибольшее значение.

Агрегатная функция MIN

вычисляет наименьшее значение.

Агрегатная функция COUNT

вычисляет количество строк в запросе.

Агрегатная функция AVG

вычисляет среднее значение.

Агрегатная функция SUM

вычисляет сумму значений.

Агрегатная функция с помощью которой можно узнать максимальное значение строки

MAX

Агрегатная функция с помощью которой можно узнать минимальное значение строки

MIN

Агрегатная функция с помощью которой можно узнать среднее значение среди всех строк

AVG

Агрегатная функция с помощью которой можно узнать количество определенных строк

COUNT

Агрегатная функция с помощью которой можно чтобы получить сумму значений строк

SUM

Агрегатная функция COUNT(*)

выдаст число строк.

Агрегатная функция COUNT(имяСтолбца)

выдает число не NULL значений в указанном столбце.

Агрегатную функцию COUNT(DISTINCT имяСтолбца)

выдает число уникальных не NULL значений в указанном столбце.

При использовании GROUP BY что можно выводить по стандарту в выражении SELECT?

1. Столбцы, которые перечислены в GROUP BY.
2. Агрегатные функции от других столбцов.

Команда
HAVING

фильтрация групп после группировки.

Команда для фильтрации строк после применения группировки

HAVING

Математические функции в SQL

1. ABS(x);
2. MOD(x, y);
3. SIN(x);
4. COS(x);
5. TAN(x);
6. COT(x);
7. RADIANS(x);
8. DEGREES(x);
9. PI() и т.д.

Функция
ABS(x)

для получения модуля числа.

Функция
MOD(x, y)

остаток от деления x на y.

Функция
SIN(x)

вычисление синуса угла. Требуется угол в радианах.

Функция
COS(x)

вычисление косинуса угла. Требуется угол в радианах.

Функция
TAN(x)

вычисление тангенса угла. Требуется угол в радианах.

Функция
COT(x)

вычисление котангенса угла. Требуется угол в радианах.

Функция
RADIANS(x)

преобразование угла в радианы.

Функция
DEGREES(x)

преобразование угла в градусы.

Функция
PI()

выдает константу Пи.

Функция для получения модуля числа

ABS(x)

Функция для получения остатка от деления

MOD(x, y)

Функция для получения синуса угла

SIN(x)

Функция для получения косинуса угла

COS(x)

Функция для получения тангенса угла

TAN(x)

Функция для получения котангенса угла

COT(x)

Функция для преобразования угла в градусы

DEGREES(x)

Функция для преобразования угла в радианы

RADIANS(x)

Функция для получения числа Пи

PI()

Строковые функции в SQL

1. CONCAT(str1, str2, ...);
2. LOWER(str);
3. UPPER(str);
4. CHAR_LENGTH(str);
5. SUBSTRING(str, pos, len);
6. REPLACE(str, from, to);
7. LOCATE(substr, str) и др.

Функция CONCAT(str1, str2, ...)

конкатенация строк.

Функция
LOWER(str)

перевод строки в нижний регистр.

Функция
UPPER(str)

перевод строки в верхний регистр.

Функция CHAR_LENGTH(str)

длина строки (количество символов).

Функция SUBSTRING(str, pos, len)

получение подстроки из строки.

Функция REPLACE(str, from, to)

замена подстроки.

Функция LOCATE(substr, str)

выдает индекс подстроки в строке (отсчитывается от 1). Если не нашлось, выдает 0.

Функция для конкатенации строк

CONCAT(str1, str2, ...)

Функция для перевода строки в нижний регистр

LOWER(str)

Функция для перевода строк в верхний регистр

UPPER(str)

Функция для получения длины строки

CHAR_LENGTH(str)

Функция для получения подстроки из строки

SUBSTRING(str, pos, len)

Функция для замены подстроки в строке

REPLACE(str, from, to)

Функция для получения индекса подстроки в строке

LOCATE(substr, str)

Функции для даты и времени в SQL

1. NOW();
2. DAY(date);
3. MONTH(date);
4. YEAR(date);
5. WEEKDAY(date);
6. DATE_ADD(date,expr);
7. DATE_SUB(date, expr);
8. DATEDIFF(expr1, expr2);
9. LAST_DAY(date).

Функция
NOW()

получить текущую дату и время.

Функция
DAY(date)

получить день.

Функция
MONTH(date)

получить месяц.

Функция
YEAR(date)

получить год.

Функция
WEEKDAY(date)

получить день недели в виде числа от 0 (понедельник) до 6 (воскресенье).

Функция
DATE_ADD(date, expr)

прибавить указанное время.

Функция
DATE_SUB(date, expr)

вычесть указанное время.

Функция
DATEDIFF(expr1, expr2)

разница между датами в днях, время игнорируется.

Функция
LAST_DAY(date)

выдает дату-последний день месяца из этой даты.

Функция для получения текущей даты и времени

NOW()

Функция для получения дня из даты

DAY(date)

Функция для получения месяца из даты

MONTH(date)

Функция для получения года из даты

YEAR(date)

Функция для получения дня недели в виде числа из даты

WEEKDAY(date)

Функция для прибавления указанного времени

DATE_ADD(date, expr)

Функция для вычитания указанного времени

DATE_SUB(date, expr)

Функция для получения разницы между датами в днях

DATEDIFF(expr1, expr2)

Функция для получения последнего дня месяца из указанной даты

LAST_DAY(date)

Команда
DISTINCT

выкидывает из результата полностью дублирующиеся строки.

Команда для удаления из результата полностью дублирующихся строк

DISTINCT

Команда
LIMIT

для выдачи указанного количества строк.

Команда для выдачи определенного количества строк

LIMIT

С какой командой рекомендуется использовать команду LIMIT и почему?

следует всегда использовать совместно с сортировкой ORDER BY, иначе порядок не гарантируется и может получится так что будут выбраны любые строки.

Что выдаст команда, если написать ее подобным образом? LIMIT 5, 10;

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

Виды соединения таблиц

1. CROSS JOIN;
2. INNER JOIN;
3. OUTER JOIN;
3.1. LEFT JOIN;
3.2. RIGHT JOIN;
3.3. FULL JOIN.

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

CROSS JOIN

Команда
CROSS JOIN

соединяет строки двух таблиц всеми возможными способами.

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

INNER JOIN

Команда
INNER JOIN

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

Команда
ON в INNER JOIN

осуществляет фильтрацию строк по указанному условию.

Команды
OUTER JOIN

в отличии от INNER JOIN если не нашлось соответствия , то строка в результат попадет, а все значения из этой таблицы будут NULL.

Команда
LEFT JOIN

берет все строки первой таблицы, ищет соответствие во второй, и, если что, ставит NULL.

Команда
RIGHT JOIN

берет все строки второй таблицы, ищет соответствие в первой, и, если что, ставит NULL.

Команда
FULL JOIN

берутся все записи всех таблиц, если не нашлось соответствие, то ставится NULL.

Назначение префиксов в SQL

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

Вложенные запросы

это когда запросы можно вкладывать друг в друга.

Что используется для того чтобы ускорить поиск по таблице?

индексы

Как формируется индекс?

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

Какие положительные и отрицательные качества вы можете сказать об использовании индексов?

- плюс в том что ускоряет поиск;
- минусы в том что замедляет вставку, удаление и изменение строк.

Пример создания индекса

CREATE INDEX ix_create_date
ON request (create_date);

Пример удаления индекса

DROP INDEX ix_create_date
ON request;

Виртуальные таблицы (View)

это просто некоторый запрос, который сохранен как объект БД. То есть просто взяли запрос, который выдает таблицу, и
дали ему имя.

Создание виртуального запроса (View)

CREATE VIEW Countries
AS SELECT *
FROM country;

Транзакция

это последовательность операций с БД, которая представляет собой логическую единицу работы с данными.

Может ли быть выполнена транзакция частично?

может быть выполнена только целиком.

Если во время транзакции что-то пошло не так, то что произойдет с уже произошедшими изменениями?

они откатываются к первоначальному виду.

Свойства удовлетворяющие транзакции

ACID.
1. Atomicity (атомарность);
2. Consistency (согласованность);
3. Isolation (изолированность);
4. Durability (долговечность).

Свойство транзакции Atomicity (атомарность)

транзакция должна быть выполнена целиком, либо не выполнена целиком.

Свойство транзакции Consistency (согласованность)

транзакция переводит данные из одного корректного состояния в другое корректное состояние.

Свойство транзакции Isolation (изолированность)

параллельные транзакции не должны оказывать влияние на результат текущей транзакции.

Свойство транзакции Durability (долговечность)

если транзакция завершена, то изменения должны оставаться сохраненными даже если будут низкоуровневые проблемы, например, с оборудованием.

Процесс работы с транзакциями в MySQL

Начинаем транзакцию с START TRANSACTION; затем выполняем команды, которые составляют транзакцию, если хотим откатить изменения используем ROLLBACK;, а если хотим применить изменения используем команду COMMIT;

Пример транзакции

START TRANSACTION;
UPDATE account
SET money = money – 1000
WHERE id = 1;
COMMIT;

Можно ли в MySQL создавать функции, хранимые процедуры и триггеры?

да

Функция

это некоторый код, который можно вызывать, например, из SELECT. Они не должны ничего менять, т.е. вызывать INSERT, UPDATE, DELETE.

Хранимая процедура

то же самое что и функция, но имеет другой синтаксис вызова – через CALL или EXECUTE. И процедуры могут что-то менять – вызывать INSERT, UPDATE, DELETE.

Триггер

код, который будет срабатывать при некотором событии. Например, при добавлении строки в таблицу.

Команда
DELIMETER

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

Команда для замены разделителя команд

DELIMETER.

Команда для изменения существующей таблицы

ALTER TABLE

Команда для изменения существующей БД

ALTER DATABASE

Команда
ALTER TABLE

изменяет существующую таблицу.

Команда
ALTER DATABASE

изменяет существующую БД.

Пример добавления столбца в таблицу

ALTER TABLE product ADD count INT;

Пример удаления столбца из таблицы

ALTER TABLE product DROP count;

Пример добавления ограничения про внешний ключ

ALTER TABLE product
ADD FOREIGN KEY (supplierId) REFERENCES suppliers(id);

Пример удаления ограничения про внешний ключ

ALTER TABLE product
DROP FOREIGN KEY fk_name;

Какие ограничения можно удалять или добавлять у столбцов таблицы?

1. UNIQUE;
2. PRIMARY KEY;
3. DEFAULT;
4. FOREIGN KEY.

Оператор который можно использовать в качестве условия или в него можно передать запрос

EXISTS - условие будет истинно, если подзапрос выдаст хотя бы одну строку.

NOT EXISTS - условие будет истинно, если подзапрос выдал 0 строк.

Оператор для сбора данных в единый набор по нескольким разным таблицам

UNION

Важная особенность оператора UNION

он откидывает полностью совпадающие строки.

Команда для сбора данных в единый набор по нескольким разным таблицам и для отображения дубликатов

UNION ALL

Пара операторов позволяющая выполнять сравнение между значением одного столбца и диапазоном других значений

ANY и ALL.

Оператор
EXISTS

используется в качестве условия, в него передается запрос, условие будет истинно если подзапрос выдаст хотя бы одну строку.

Оператор
NOT EXISTS

используется в качестве условия, в него передается запрос, условие будет истинно, если подзапрос выдал 0 строк.

Оператор
UNION

собирает данные в единый набор по нескольким разным таблицам и не отображает дубликаты.

Оператор
UNION ALL

собирает данные в единый набор по нескольким разным таблицам и отображает дубликаты.

Оператор
ANY

если хотя бы для одного значения, получаемого из подзапроса, результат операции равняется TRUE, то будет выдано TRUE.

Оператор
ALL

значение будет истинным, если для всех значений, получаемых из подзапроса, будет выдано TRUE.

Оператор
CASE

когда при выводе результатов есть необходимость добавить условную логику – в зависимости от условия выводить разные значения. Использование при сортировке. Использование внутри агрегатных функций (чаще всего COUNT) при группировке.

Пример использования оператора CASE

SELECT requestId,
CASE status
WHEN 0 THEN ‘Новая’
WHEN 1 THEN ‘В работе’
ELSE ‘Неизвестный статус’
END AS statusName
FROM requests;