3. Uploading data with SQL
Друзья, этот материал был подготовлен с применением GPT. Усли вам интерсно, как формировались запросы (promts), то видео находится здесь.
Руководство по стилю SQL · SQL Style Guide
✅ 3.1 Создание таблиц
1. Выберем базу Pet care clinic на сайте:
2. Подключим базу в DBeaver. Введем хост, порт (если он отсутствует), имя пользователя, пароль и имя базы.
3. Выведем все доступные таблицы из схемы public. Их должно быть всего три: animals, appointments и owners.
Этот запрос выводит список всех таблиц в базе данных, включая их схемы. Конкретно, он выводит два столбца: “schemaname” и “tablename”. Столбец “schemaname” содержит имена схем, в которых находятся таблицы, а столбец “tablename” содержит имена таблиц. Вы можете использовать этот запрос в PostgreSQL для получения списка всех таблиц в базе данных. Вот пример запроса:
SELECT schemaname, tablename
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
Этот скрипт использует системную таблицу information_schema.tables, которая содержит информацию о всех таблицах в базе данных. Он выбирает только те таблицы, которые находятся в схеме “public”, и сортирует их по имени таблицы в алфавитном порядке. Обратите внимание, что в PostgreSQL имена и схем регистрозависимы, поэтому если вы используете другой регистр для имени схемы, то вам нужно будет изменить условие table_schema = ‘public’ в соответствии с вашими потребностями.
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;
Этот скрипт использует системную таблицу pg_catalog.pg_tables, которая содержит информацию о всех таблицах в базе данных. Он выбирает только те таблицы, которые находятся в схеме “public”, и сортирует их по имени таблицы в алфавитном порядке. Обратите внимание, что в PostgreSQL имена и схемы регистрозависимы, поэтому если вы используете другой регистр для имени схемы, то вам нужно будет изменить условие schemaname = ‘public’ в соответствии с вашими потребноями.
SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;
Этот скрипт использует системную таблицу pg_class, которая содержит информацию о всех объектах данных, включая таблицы. Он выбирает только те таблицы, которые находятся в схеме “public”, и сортирует их по имени таблицы в афавитном порядке. Обратите внимание, что в PostgreSQL имена и схемы регистрозависимы, поэтому если вы используете другой регистр для имени схемы, то вам нужно будет изменить условие nspname = ‘public’ в соответствии с вашими потребностями.
SELECT relname
FROM pg_class
WHERE relkind = 'r' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
ORDER BY relname;
4. Создадим новые таблицы из исходных. Например, обрезав несколько полей.
Выведем вообще какие столбцы имеются в таблицых, например ‘appointments’
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'appointments';
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'animals';
Чтобы вывести названия всех таблиц и их столбцы в PostgreSQL из схемы ‘public’, вы можете использовать следующий SQL-запрос:
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;
Этот запрос использует системную таблицу information_schema.columns, которая содержит информацию о столбцах всех таблиц в базе данных. Здесь мы выбираем только те строки, где схема таблицы равна ‘public’, чтобы получить только таблицы из этой схемы. Запрос вернет список всех таблиц и их столбцов в алфавитном порядке. Столбцы для каждой таблицы будут отображаться в порядке их позиции в таблице. Если вы хотите вывести дополнительную информацию о таблицах, такую как их типы данных или ограничения, вы можете добавить соответствующие столбцы из таблицы information_schema.columns в запрос. Например, чтобы вывести имена таблиц, их столбцы, и типы данных каждого столбца в схеме ‘public’, вы можете использовать следующий запрос:
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;
CREATE TABLE new_animals (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
type VARCHAR(255) NOT NULL,
age INTEGER NOT NULL,
registered_at TIMESTAMP NOT NULL,
owner_id INTEGER NOT NULL
)
INSERT INTO new_animals (name, type, age, registered_at, owner_id)
SELECT name, type, age, to_timestamp(registered_at, 'YYYY-MM-DD HH24:MI:SS'), owner_id
FROM animals
select * from new_animals
Для вывода первых и последних 10 строк таблицы можно использовать разные подходы в зависимости от используемой СУБД. Вот несколько примеров: Для PostgreSQL:
SELECT *
FROM (
SELECT *
FROM new_animals
ORDER BY id ASC
LIMIT 10
) AS first_rows
UNION ALL
SELECT *
FROM (
SELECT *
FROM new_animals
ORDER BY id DESC
LIMIT 10
) AS last_rows
ORDER BY id ASC;
Чтобы выбрать определенные номера id (например, 1, 56, 8, 45) в SQL, вы можете использовать оператор IN. Вот пример SQL-запроса, который выбирает строки из таблицы animals, где id равен 1, 56, 8 или 45:
SELECT id, name, type
FROM animals
WHERE id IN (1, 56, 8, 45);
Для создания новых таблиц в PostgreSQL вы можете использовать оператор CREATE TABLE. Вот пример создания таблицы users с тремя столбцами:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);
Этот запрос создает таблицу users с тремя столбцами: id, name и email Столбец idимеет тип SERIAL, который автоматически генерирует уникальные значения при вставке новых строк. Столбец nameимеет тип VARCHAR(50) и не может быть пустым (NOT NULL). Столбец email также имеет тип VARCHAR, но имеет ограничение UNIQUE, которое гарантирует, что каждый адрес электронной почты в таблице будет уникальным.
Вы можете добавлять другие столбцы в таблицу, указывая их имена и типы данных после ключевого слова CREATE TABLE. Вы также можете добавлять ограничения на столбцы, такие как NOT NULL, UNIQUE, CHECK и FOREIGN KEY.
После создания таблицы вы можете добавлять новые строки в нее с помощью оператора INSERT INTO, а также извлекать данные из таблицы с помощью оператора SELECT.
Приведем в первоначальный вид - удалим созданные таблицы и оставим только как было вначале - animals, appointments и owners. Вариант удаления всех таблиц, кроме animals, appointments и owners, без перечисления удаляемых таблиц в PostgreSQL - это использовать запрос на удаление таблиц, который генерируетинамический SQL на основе информации из системной таблицы pg_tables. Вот пример такого запроса:
DO $$ DECLARE
r RECORD;
BEGIN
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename NOT IN ('animals', 'appointments', 'owners'))
LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END $$;
✅ 3.2 Создание Views
Views (представления) - это виртуальные таблицы, которые создаются на основе запроса SELECT к одной или нескольким таблицам в базе данных. Они позволяют создавать “вид” на, которые уже хранятся в таблицах, и предоставлять доступ к этим данным в удобном формате. Представления могут использоваться для упрощения сложных запросов, ограничения доступа к данным, а также для улучшения производительности запросов.
При создании представления, запрос SELECT определяет структуру и содержание представления. Представление не хранит данные отдельно от таблицы, на основе которой оно создано. Вместо этого, оно является виртуальной таблицей, котор создается на лету при выполнении запроса SELECT.
Представления могут быть использованы в качестве источника данных для других запросов SELECT, а также для выполнения операций INSERT, UPDATE и DELETE. Они могут быть созданы с различными условиями фильтрации, сортировки и группировки данных, что позволяетать данные в нужном формате.
Вот несколько примеров использования представлений:
- Создание представления, которое выводит только необходимые столбцы из таблицы, чтобы упростить запросы SELECT.
- Создание представления, которое фильтрует данные на основе определенного условия, чтобы ограничить доступ к данным.
- Создание представления, которое объединяет данные из нескольких таблиц, чтобы упростить запросы SELECT.
1. Создадим представление (view) на основе исходной таблицы appointments, которую мы использовали в базе данных Pet care clinic в предыдущем занятии. Применим условие фильтрации: отберем такие записи, где поле confirmed равно 1.
CREATE VIEW confirmed_appointments AS
SELECT *
FROM appointments
WHERE confirmed = 1;
Вывести view можно также как и обычные таблицы:
select * from confirmed_appointments
2. Изменим запрос, добавив условие, что нас интересуют только те пользователи, у которых ID больше 100.
CREATE or replace VIEW confirmed_animals AS
SELECT id, name, type
FROM animals
where id = 100;
Этот запрос выберет все строки из таблицы animals, где id равен 1, 56, 8 или 45. Если вы хотите создать представление на основе этого запроса, вы можете использовать следующий SQL-запрос:
CREATE OR REPLACE VIEW confirmed_animals AS
SELECT id, name, type
FROM animals
WHERE id IN (1, 56, 8, 45);
Этот запрос создаст представление appointments_view, которое будет содержать только записи с appointment_id от 1 до 100 включительно. Вы можете изменить диапазон appointment_id в соответствии с вашими потребностями.
CREATE OR REPLACE VIEW appointments_view AS
SELECT *
FROM appointments
WHERE id BETWEEN 1 AND 100;
3. Пересоздадим представление и проверьте, что изменения были применены успешно.
Чтобы пересоздать представление appointments_view и выбрать только записи с appointment_id от 1 до 100, вы можете использовать следующий SQL-зап:
CREATE OR REPLACE VIEW appointments_view AS
SELECT *
FROM appointments
WHERE appointment_id BETWEEN 1 AND 100;
Этот запрос пересоздаст представление appointments_view и выберет только записи с appointment_id от 1 до 100 из таблицы appointments. Если представление уже существует, то оператор CREATE OR REPLACE VIEW заменит его новым представлением. Чтобы проверить, что изменения были применены успешно, вы можете выполнить запрос, который выберет данные из представления appointments_view:
SELECT *
FROM appointments_view;
4. Удалим созданное представление. Выполним запрос SELECT * FROM pg_views WHERE schemaname = 'public', чтобы убедиться, что удаление прошло успешно.
DROP VIEW v_appointment;
✅ 3.3 SQL с использованием подзапросов
Использование GPT для изучения нового предмета и решения задач становится все интереснее и я решил в дальнейшем продолжении статьи указывать и сам запрс с ответом.
Предлагаем закрепить полученные знания. Используйте всё ту же базу клиники животных (Pet care clinic) и выполните действия по следующему алгоритму:
- Найдите одинаковые столбцы в разных таблицах.
Если мне что-то надо доуточнить:
- Следуя примерам из видео, напишите запрос с подзапросом. Найдите владельцев, проживающих в городах, названия которых начинаются с new.
Однако, GPT не учел, что первая буква может быть большой и надо перевести слово при проверки в нижний регистр, впрочем, подкорректировав свой запрос, я получил нужный ответ:
- По желанию можете использовать оператор JOIN, чтобы найти имена владельцев и их питомцев.
SELECT owners.full_name, appointment_date, animals.name,
owners.city
FROM owners
JOIN animals ON owners.id = animals.owner_id
JOIN appointments ON animals.id = appointments.animal_id
WHERE owners.id IN (
SELECT id
FROM owners
WHERE LOWER(city) LIKE 'new %'
);
✅ 3.4 Чистка данных и оконные функции
3.4 Чистка данных и оконные функции.pdf
В качестве практики предлагаем написать аналитические запросы, используя оконные функции. Нужно ответить на вопросы, используя входные данные. Для примера будем использовать небольшую таблицу, которая содержит оценки учеников по разным предметам. Код создания таблицы выглядит следующим образом:
-- Создание таблицы
CREATE TABLE student_grades (
id INT,
student_name VARCHAR,
student_id INT,
subject VARCHAR,
grade INT,
exam_date DATE
);
-- Наполнение таблицы данными
INSERT INTO student_grades (id, student_name, student_id, subject, grade, exam_date)
VALUES
(1, 'Петя', 1, 'русский', 4, '2021-06-15'),
(2, 'Петя', 1, 'физика', 5, '2022-01-20'),
(3, 'Петя', 1, 'история', 4, '2022-05-29'),
(4, 'Маша', 2, 'математика', 4, '2021-01-12'),
(5, 'Маша', 2, 'русский', 3, '2022-06-27'),
(6, 'Маша', 2, 'физика', 5, '2022-01-03'),
(7, 'Маша', 2, 'история', 3, '2022-05-12')
;
-- Запрос всех данных из таблицы
SELECT *
FROM student_grades;
- Присвойте порядковый номер каждой записи.
SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_num, * FROM student_grades; - Пронумеруйте записи с оценками в порядке их получения каждым студентом.
SELECT *,
ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY
exam_date) AS rn
FROM student_grades;
- Добавьте ещё раз записи в таблицу, указав при этом новые идентификационные номера (ID).
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY exam_date ORDER BY id)
rn
FROM student_grades
) q1
WHERE rn > 1;
- Напишите запрос для нахождения дублей.
SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY exam_date ORDER BY id) rn FROM student_grades ) q1 WHERE rn > 1; - Удалите найденные дубли и проверьте результат. Результат будет считаться корректным, если запрос из пункта 4 ничего не выведет.
DELETE FROM student_grades WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY exam_date ORDER BY id) rn FROM student_grades ) q1 WHERE rn > 1 );✅ 3.5 Обновление значений таблиц
3.5 Обновление значений таблиц.pdf
Поработайте с таблицей, содержащей данные о планетах (назовём её planets):
CREATE TABLE planets (
ID INT PRIMARY KEY,
PlanetName VARCHAR(255),
Radius FLOAT,
SunSeason VARCHAR(255),
OpeningYear INT,
HavingRings VARCHAR(255),
Opener VARCHAR(255)
);
INSERT INTO planets (ID, PlanetName, Radius, SunSeason, OpeningYear, HavingRings, Opener)
VALUES
(1, 'Mars', 3396, 687, 1659, 'No', 'Christiaan Huygens'),
(2, 'Saturn', 60268, 10759.22, NULL, 'Yes', NULL),
(3, 'Neptune',24764, 60190, 1846, 'Yes', 'John Couch Adams'),
(4, 'Mercury', 2439, 115.88, 1631, 'No', 'Nicolaus Copernicus'),
(5, 'Venus', 6051, 243, 1610, 'No', 'Galileo Galilei');
- Используя оператор UPDATE, измените название планеты Neptune на Pluto.
UPDATE Planets SET PlanetName = 'Pluton' WHERE PlanetName = 'Neptune';
✅ 3.6 Union
Поработаем с таблицей Ecommerce website. Данные для подключения к этой базе можно найти там же, где мы брали данные для подключения к базе Pet care clinic: