DEV Community

Erlan Akbaraliev
Erlan Akbaraliev

Posted on • Edited on

База Данных 5 Views

#sql #tutorial #database
  1. Введение
  2. 👁️ Представления (Views)
  3. Упрощение (Simplifying)
  4. Агрегация (Aggregating)
  5. Common Table Expression (CTE, Общее Табличное Выражение)
  6. 🧱 Разделение (Partitioning)
  7. 🛡️ Обеспечение Безопасности (Securing)
  8. 👻 Мягкое Удаление (Soft Deletions)

9. 🚀 Триггеры (Trigger)

1. Введение

Мы уже научились как создавать базы данных и добавлять данные. Теперь мы рассмотрим способы получения представлений (View) данных.

        авторы              авторы_книги              книги
+----+----------------+ +----------+----------+ +----+----------------+
| id | имя            | | автор_id | книга_id | | id | название       |
+----+----------------+ +----------+----------+ +----+----------------+
| 23 | Ева Балтасар   | | 23       | 1        | | 1  | Валун          |
+----+----------------+ +----------+----------+ +----+----------------+
| 31 | Хан Кан        | | 31       | 74       | | 74 | Белая книга    |
+----+----------------+ +----------+----------+ +----+----------------+
| 27 | Гоз            | | 27       | 4        | | 4  | Стоящий тяжело |
+----+----------------+ +----------+----------+ +----+----------------+
| 58 | Ольга Токарчук | | 58       | 78       | | 78 | Полёты         |
+----+----------------+ +----------+----------+ +----+----------------+
Enter fullscreen mode Exit fullscreen mode

Для того чтобы найти книгу автора Хан Кан, нам пришлось бы проходить через все три таблицы:

  • сначала найти ID автора
  • затем соответствующие ID книг
  • а потом уже названия книг. Можно ли одним запросом вывести все книги и авторы этих вместо того чтобы писать подзапросы?

Да, мы можем использовать команду JOIN в SQL, чтобы объединять строки из двух или более таблиц на основе общего связанного столбца. Вот наглядное представление того, как эти таблицы могут быть соединены, чтобы сопоставить авторов и их книги.

+----------------+----+----------+----------+----+----------------+
| имя            | id | автор_id | книга_id | id | название       |
+----------------+----+----------+----------+----+----------------+
| Ева Балтасар   | 23 | 23       | 1        | 1  | Валун          |
+----------------+----+----------+----------+----+----------------+
| Хан Кан        | 31 | 31       | 74       | 74 | Белая Книга    |
+----------------+----+----------+----------+----+----------------+
| Гоз            | 27 | 27       | 4        | 4  | Стоящий тяжело |
+----------------+----+----------+----------+----+----------------+
| Ольга Токарчук | 58 | 58       | 78       | 78 | Полёты         |
+----------------+----+----------+----------+----+----------------+
Enter fullscreen mode Exit fullscreen mode

Это позволяет легко увидеть, что Хан Канг написала «Белую книгу».

Можно также представить, что мы убрали столбцы с ID, и тогда наше представление будет выглядеть вот так:

+----------------+----------------+
| имя            | название       |
+----------------+----------------+
| Ева Балтасар   | Валун          |
+----------------+----------------+
| Хан Кан        | Белая Книга    |
+----------------+----------------+
| Гоз            | Стоящий тяжело |
+----------------+----------------+
| Ольга Токарчук | Полёты         |
+----------------+----------------+
Enter fullscreen mode Exit fullscreen mode

2. 👁️ Представления (Views)

Представление (View) — это виртуальная таблица, которая определяется с помощью запроса.

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

✅ Представления полезны для следующих целей:

  • Упрощение (Simplifying): Объединение данных из разных таблиц для более простого составления запросов к ним.
  • Агрегация (Aggregating): Выполнение агрегатных функций (например, нахождение суммы) и сохранение результатов.
  • Разделение (Partitioning): Деление данных на логические части.
  • Безопасность (Securing): Скрытие столбцов, которые должны оставаться конфиденциальными.

3. Упрощение (Simplifying)

Чтобы найти книги автора Фернанда Мельчор, мы пишем такой подзапрос:

SELECT название FROM книги
WHERE id IN (
    SELECT книга_id FROM авторы_книги
    WHERE автор_id = (
        SELECT id FROM авторы
        WHERE имя = 'Фернанда Мельчор'
    )
);
Enter fullscreen mode Exit fullscreen mode
  • Этот запрос сложный — вложенный запрос содержит целых три команды SELECT. Чтобы упростить это, давайте сначала используем JOIN для создания Представления (View), содержащего авторов и их книги. 
SELECT авторы.имя, книги.название FROM авторы
JOIN авторы_книги ON авторы.id=авторы_книги.автор_id
JOIN книги ON книги.id=авторы_книги.книга_id;
Enter fullscreen mode Exit fullscreen mode
  • Обратите внимание: Важно указывать, как именно две таблицы соединяются, то есть по каким столбцам происходит соединение (JOIN).
  • 💡: Столбец первичного ключа (Primary Key) одной таблицы обычно соединяется с соответствующим столбцом внешнего ключа (Foreign Key) в другой таблице!
  • Выполнение этого запроса выведет таблицу, содержащую все имена авторов рядом с названиями книг, которые они написали.

Чтобы сохранить виртуальную таблицу, созданную на предыдущем шаге, в качестве Представления (View), нам необходимо изменить запрос.

CREATE VIEW авторы_книги_предс AS
SELECT авторы.имя, книги.название FROM авторы
JOIN авторы_книги ON авторы.id=авторы_книги.автор_id
JOIN книги ON книги.id=авторы_книги.книга_id;
Enter fullscreen mode Exit fullscreen mode 
SELECT * FROM авторы_книги_предс;
Enter fullscreen mode Exit fullscreen mode

Используя это Представление, мы можем значительно упростить запрос, необходимый для поиска книг, написанных Фернандой Мельчор.

SELECT * FROM авторы_книги_предс
WHERE имя='Фернанда Мельчор';
Enter fullscreen mode Exit fullscreen mode

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

4. Агрегация (Aggregating)

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

SELECT книга_id, ROUND(AVG(рейтинг), 2) AS "рейтинг" 
FROM рейтинг
GROUP BY книга_id;
Enter fullscreen mode Exit fullscreen mode

Результаты этого запроса можно сделать более полезными, если отобразить название каждой книги, а также, возможно, год публикации. Эта информация присутствует в таблице книги. Поэтому нужно объединить таблицы книги и рейтинг (join).

SELECT рейтинг.книга_id, книги.название, книги.год, рейтинг.рейтинг FROM книги
JOIN рейтинг ON книги.id=рейтинг.книга_id;
Enter fullscreen mode Exit fullscreen mode

Чтобы получить средний рейтинг каждой книги и год публикации:

SELECT к.id, к.название, к.год, ROUND(AVG(р.рейтинг),2) AS "средний_рейтинг"
FROM книги к
JOIN рейтинг р ON к.id=р.книга_id
GROUP BY к.id;
Enter fullscreen mode Exit fullscreen mode

Чтобы сортировать по id книг:

SELECT к.id, к.название, к.год, ROUND(AVG(р.рейтинг),2) AS "средний_рейтинг"
FROM книги к
JOIN рейтинг р ON к.id=р.книга_id
GROUP BY к.id
ORDER BY к.id;
Enter fullscreen mode Exit fullscreen mode

Эти агрегированные данные могут быть сохранены в Представлении (View):

CREATE VIEW книги_средний_рейтинг AS 
SELECT к.id, к.название, к.год, ROUND(AVG(р.рейтинг),2) AS "средний_рейтинг"
FROM книги к
JOIN рейтинг р ON к.id=р.книга_id
GROUP BY к.id
ORDER BY к.id;
Enter fullscreen mode Exit fullscreen mode

Давайте теперь посмотрим на это представление (view).

select * from книги_средний_рейтинг;
Enter fullscreen mode Exit fullscreen mode

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

Посмотри на средний рейтинг книги 'Валун'.
Добавьте новый рейтинг в таблицу рейтинг на книгу 'Валун':

INSERT INTO рейтинг (книга_id, рейтинг)
VALUES
(1, 5);
Enter fullscreen mode Exit fullscreen mode 
select * from книги_средний_рейтинг;
Enter fullscreen mode Exit fullscreen mode

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

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

Для создания временных представлений, которые не сохраняются в схеме базы данных, мы можем использовать команду CREATE TEMPORARY VIEW.

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

Чтобы найти средний рейтинг книг по годам, мы можем использовать Представление (View), которое мы уже создали.

SELECT год, ROUND(AVG(средний_рейтинг), 2) 
FROM книги_средний_рейтинг
GROUP BY год;
Enter fullscreen mode Exit fullscreen mode

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

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

SELECT к.год, ROUND(AVG(р.рейтинг), 2) AS "средний рейтинг"
FROM книги к
JOIN рейтинг р ON к.id=р.книга_id
GROUP BY к.год;
Enter fullscreen mode Exit fullscreen mode

Мы можем хранить этот средний рейтинг книг по годам в временной представлении:

CREATE TEMPORARY VIEW средний_рейтинг_по_годам AS
SELECT год, ROUND(AVG(средний_рейтинг), 2) 
FROM книги_средний_рейтинг
GROUP BY год;
Enter fullscreen mode Exit fullscreen mode

5. Common Table Expression (CTE, Общее Табличное Выражение)

Обычное Представление (View) существует в схеме нашей базы данных постоянно. Временное Представление (Temporary View) существует в течение всего времени нашего соединения с базой данных. CTE (Common Table Expression, Общее Табличное Выражение) — это Представление, которое существует только для одного единственного запроса.

Давайте воссоздадим Представление, содержащее средние рейтинги книг по годам, используя CTE вместо временного Представления.

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

drop view книги_средний_рейтинг;
Enter fullscreen mode Exit fullscreen mode

Далее мы создаем Общее Табличное Выражение (CTE), которое содержит средние оценки по каждой книге. Затем мы используем эти средние оценки по книгам, чтобы вычислить средний рейтинг по годам, во многом так же, как мы это делали ранее.

WITH книги_средний_рейтинг AS (
    SELECT к.id, к.название, к.год, ROUND(AVG(р.рейтинг),2) AS "средний_рейтинг"
    FROM книги к
    JOIN рейтинг р ON к.id=р.книга_id
    GROUP BY к.id
    ORDER BY к.id
)
SELECT год, ROUND(AVG(средний_рейтинг), 2) 
FROM книги_средний_рейтинг
GROUP BY год;
Enter fullscreen mode Exit fullscreen mode

6. 🧱 Разделение (Partitioning)

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

🧐 Как это работает?
Вместо того чтобы заставлять приложение или пользователя работать со всей огромной таблицей, мы можем создать несколько Представлений, каждое из которых будет содержать данные только по определенному критерию (например, книги, вышедшие до 2020 года, или книги, переведенные с испанского языка).

CREATE VIEW "2020" AS
SELECT id, название FROM книги
WHERE год = 2020;
Enter fullscreen mode Exit fullscreen mode 
SELECT * FROM "2020";
Enter fullscreen mode Exit fullscreen mode

🔄 Можно ли обновлять Представления (Views)?
Нет, Представления нельзя обновлять (напрямую), потому что они не хранят данные, как это делают таблицы.
На самом деле, Представления извлекают данные из базовых таблиц каждый раз, когда к ним поступает запрос.
Это означает, что когда обновляется базовая таблица, при следующем запросе Представление отобразит актуальные данные из этой таблицы!

7. 🛡️ Обеспечение Безопасности (Securing)

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

Рассмотрим базу данных компании, предоставляющей услуги совместных поездок (rideshare), в которой есть таблица rides (поездки), которая выглядит следующим образом:

rides

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

Имена пассажиров, вероятно, относятся к Личной Идентифицирующей Информации (PII), которую компаниям не разрешено распространять без разбора.

В такой ситуации Представления (Views) могут быть очень полезны: мы можем предоставить аналитику представление, содержащее только точки отправления и назначения поездок, но без имен пассажиров.

CREATE VIEW analysis AS
SELECT id, origin, destination, 'Anonymous' AS "rider" 
FROM rides;
Enter fullscreen mode Exit fullscreen mode

anon

8. 👻 Мягкое Удаление (Soft Deletions)

Мягкое удаление (soft deletion) включает в себя пометку строки как удаленной, вместо ее полного удаления из таблицы.

Например, произведение искусства под названием «Farmers working at dawn» помечается как удаленное из таблицы collections (коллекции) путем изменения значения в столбце deleted (удалено) с 0 на 1.

collections

Таким образом мы можем изменять значение столбца 'deleted', мягко указать удалены ли строки.

UPDATE collections
SET deleted = 1 
WHERE title = 'Farmers working at dawn';
Enter fullscreen mode Exit fullscreen mode

Чтобы добавить новый столбец 'deleted':

ALTER TABLE collections
ADD COLUMN deleted INTEGER DEFAULT 0;
Enter fullscreen mode Exit fullscreen mode

Еще из не удаленных данных можно создать представление (view):

CREATE VIEW current_collections AS
SELECT id, title
FROM collections
WHERE deleted = 0;

SELECT * FROM current_collections;
Enter fullscreen mode Exit fullscreen mode

Запрос для создании и добавлении данных:

CREATE TABLE collections (
    id SERIAL PRIMARY KEY,
    title TEXT,
    deleted INT DEFAULT 0
);

INSERT INTO collections (title)
VALUES 
('Farmers working'),
('Imaginative land'),
('Profusion of'),
('Peonies and');
Enter fullscreen mode Exit fullscreen mode

9. 🚀 Триггеры (Triggers)?

Триггер — это автоматический "сторож" в базе данных, прикрепленный к определенной таблице.

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

  • Добавление (INSERT)
  • Изменение (UPDATE)
  • Удаление (DELETE)

В таблице users при каждом обновлении пароля он автоматически запишет в таблицу user_logs: "Пароль был обновлен в такое-то время".

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

Давайте удалим таблицы users, user_logs и все триггерные функции и заново все напишем
triggers

Создайте таблицы:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(15) NOT NULL UNIQUE,
    password TEXT NOT NULL
);

CREATE TABLE user_logs (
    id SERIAL PRIMARY KEY,
    type VARCHAR(10) NOT NULL CHECK(type IN ('insert', 'delete', 'update')),
    old_username VARCHAR(15),
    new_username VARCHAR(15),
    old_password TEXT,
    new_password TEXT,
    data_time timestamp DEFAULT CURRENT_TIMESTAMP
);
Enter fullscreen mode Exit fullscreen mode

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

  • Она записывает тип события ('insert') и данные нового пользователя (NEW.username, NEW.password). 
CREATE OR REPLACE FUNCTION log_user_inserts_func()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_logs (type, old_username, new_username, old_password, new_password)
    VALUES ('insert', NULL, NEW.username, NULL, NEW.password);
    RETURN NEW;
END;
$$ language plpgsql;

CREATE TRIGGER log_user_inserts
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_inserts_func();
Enter fullscreen mode Exit fullscreen mode

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

CREATE OR REPLACE FUNCTION log_user_updates_func()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_logs (type, old_username, new_username, old_password, new_password)
    VALUES ('update', OLD.username, NEW.username, OLD.password, NEW.password);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_user_updates
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_updates_func();
Enter fullscreen mode Exit fullscreen mode

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

CREATE OR REPLACE FUNCTION log_user_deletes_func()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_logs (type, old_username, new_username, old_password, new_password)
    VALUES ('delete', OLD.username, NULL, OLD.password, NULL);
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_user_deletes
AFTER DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION log_user_deletes_func();
Enter fullscreen mode Exit fullscreen mode

Top comments (0)