ОП.08 - 18 - Переменные, условия и LAST_INSERT_ID

Примеры данной темы используют учебную БД:

Переменные

Переменные - это именованные значения, которые могут изменяться в процессе выполнения программы. В языке SQL, переменные позволяют сохранять и использовать значения, которые были вычислены ранее или введены пользователем.

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

В MySQL 8, синтаксис определения переменных выглядит следующим образом:

SET @variable_name = value;

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

Пример 1

Допустим, мы хотим определить переменную shelf_count, которая будет содержать количество полок в библиотеке. Мы можем использовать следующий запрос:

SET @shelf_count = (SELECT COUNT(*) FROM shelves);
SELECT @shelf_count;

Этот запрос выбирает количество строк из таблицы shelves и сохраняет его в переменной @shelf_count.

Пример 2

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

SET @year = 1970;
SET @book_count = (SELECT COUNT(*) FROM books WHERE year > @year);
SELECT @book_count;

Этот запрос использует переменную @year для указания года, после которого нужно выбрать книги, и переменную @book_count для подсчета количества таких книг.

Посмотреть что это за книги можно запросом:

SELECT title, year FROM books WHERE year > @year;

Пример 3

Мы можем использовать переменные для обновления данных в таблицах. Например, мы можем определить переменную shelves_id и обновить запись в таблице books, указав ее идентификатор:

SET @shelf_id = (SELECT id FROM shelves WHERE title = 'Полка в спальне' LIMIT 1);
SET @book_id = (SELECT id FROM books WHERE title = 'Война и мир' AND year = 1869 LIMIT 1);
UPDATE books SET shelves_id = @shelf_id WHERE id = @book_id;

Этот запрос использует переменную @shelf_id для указания идентификатора полки, на которую нужно переместить книгу, id которой хранится в переменной @book_id.

Условия

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

Оператор IF

Оператор IF имеет простой синтаксис и может использоваться в качестве конструкции условия:

IF(условие, выражение_1, выражение_2)

В этом случае, если условие истинно, оператор вернет значение выражения_1, в противном случае - значение выражения_2.

Рассмотрим пример использования оператора IF на таблице books, чтобы извлечь данные только тех книг, у которых есть полка:

SELECT title, YEAR, IF(shelves_id IS NULL, 'Нет полки', 'Есть полка') AS shelf FROM books;

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

Оператор CASE

Оператор CASE похож на оператор IF, но он более гибкий и может проверять несколько условий. Его синтаксис выглядит следующим образом:

CASE
    WHEN условие_1 THEN выражение_1
    WHEN условие_2 THEN выражение_2
    ...
    ELSE выражение_n
END

В этом случае, если условие_1 истинно, оператор вернет значение выражения_1, если условие_2 истинно - значение выражения_2 и так далее. Если ни одно из условий не истинно, оператор вернет значение выражения_n.

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

SELECT title,
       (CASE
            WHEN COUNT(authors_books.authors_id) = 1 THEN CONCAT(COUNT(authors_books.authors_id), ' автор')
            ELSE CONCAT(COUNT(authors_books.authors_id), ' авторов')
        END) AS authors_count
FROM books
JOIN authors_books ON books.id=authors_books.books_id
GROUP BY title;

Этот запрос извлекает данные из таблицы books и связанной таблицы authors_books. Затем он считает количество авторов для каждой книги и создает новый столбец authors_count, который содержит это количество, а также слово "автор" или "авторов" в зависимости от количества.

LAST_INSERT_ID

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

Для примеров мы будем использовать базу данных, состоящую из четырех таблиц: shelves (полки), friends (друзья), books (книги) и authors (авторы). Таблицы books и authors связаны многие-ко-многим через таблицу authors_books.

Предположим, что мы хотим добавить новую полку в таблицу shelves и затем добавить новую книгу в таблицу books, используя только что созданный идентификатор полки. Мы можем использовать LAST_INSERT_ID(), чтобы получить этот идентификатор.

Пример:

INSERT INTO shelves (title) VALUES ('Новая полка');
SET @shelf_id = LAST_INSERT_ID();
INSERT INTO books (title, year, shelves_id) VALUES ('Гарри Поттер и сдача практического экзамена по основам проектирования баз данных', 2023, @shelf_id);

В этом примере мы вставляем новую запись в таблицу shelves, затем сохраняем идентификатор этой записи в переменную @shelf_id с помощью LAST_INSERT_ID(). Затем мы используем эту переменную для вставки новой записи в таблицу books, связывая ее с только что созданной полкой.

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

Пример:

INSERT INTO friends (name, contacts) VALUES ('Денис', 'deniska@example.com');
SELECT LAST_INSERT_ID();

В этом примере мы вставляем новую запись в таблицу friends, которая имеет автоинкрементный первичный ключ. Затем мы используем LAST_INSERT_ID(), чтобы получить идентификатор только что созданной записи.

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