Помните, как вы были студентами, и готовились к экзаменам по ночам?
Предлагаю вашему вниманию простую шпаргалку по SQL с теорией и практикой, которой вы сможете воспользоваться в любое время.
Статья пригодится:
-
ИТ специалисту, которому необходимо быстро освоить минимальный уровень SQL для выполнения рабочих задач,
-
Системному аналитику, которому требуется освежить знания перед собеседованием или научиться, если раньше не было задач с SQL.
В статье есть:
-
Минимум теории для задач на работе или собеседовании (прим. операторов в SQL больше, но в своей работе использовала чаще всего эти);
-
Практические задания, которые можно выполнить у себя на ПК бесплатно, предварительно установив сервер баз данных.
SQL теория
SQL (structured query language) — язык структурированных запросов, который позволяет работать с данными (найти, изменить, удалить или создать) в реляционной базе данных (БД).
Реляционные БД — это базы, где связанная информация, представленная в виде двумерных таблиц (например, Postgres, Mysql, Oracle и др.).
СУБД — система управления БД, программа с помощью которой можно создавать, наполнять и просматривать БД .
ER диаграммы (Entity-Relationship model) — показывает структуру и связи таблиц в БД. Помогает в написании SQL запросов.
Для работы мозга студенту нужна энергия. Проще всего ее получить из сладкого. Значит будем учиться на примере базы данных сладостей. Изучать теорию мы с вами будем на реальном примере.
Наша БД состоит из таблиц:
(прим. показана часть БД с необходимыми таблицами для выполнения практических заданий)
sweets_types — виды сладостей
|
Столбец |
Тип данных |
Обязательность |
Описание |
|
id |
integer |
not null (должно быть значение) |
Идентификатор вида сладости. PK |
|
name |
character varying |
not null |
Вид сладости |
manufacturers — производители
|
Столбец |
Тип данных |
Обязательность |
Описание |
|
id |
integer |
not null |
Идентификатор производителя. PK |
|
name |
character varying |
not null |
Производитель |
|
phone |
character varying |
|
Телефон |
|
adress |
character varying |
|
Адрес |
|
city |
character varying |
not null |
Населенный пункт |
|
country |
character varying |
not null |
Страна |
storehouses — склады
|
Столбец |
Тип данных |
Обязательность |
Описание |
|
id |
integer |
not null |
Идентификатор склада. PK |
|
name |
character varying |
not null |
Название склада |
|
adress |
character varying |
Адрес |
|
|
city |
character varying |
not null |
Населенный пункт |
|
country |
character varying |
not null |
Страна |
manufacturers_storehouses — связь производителя со складом
|
Столбец |
Тип данных |
Обязательность |
Описание |
|
id |
integer |
not null |
Идентификатор связи. PK |
|
storehouses_id |
character varying |
not null |
Идентификатор склада. FK |
|
manufacturers_id |
character varying |
|
Идентификатор производителя. FK |
sweets — сладости
|
Столбец |
Тип данных |
Обязательность |
Описание |
|
id |
integer |
not null |
Идентификатор сладости. PK |
|
sweets_types_id |
integer |
|
Идентификатор вида. FK |
|
name |
character varying |
not null |
Название сладости |
|
cost |
character varying |
not null |
Стоимость |
|
weight |
character varying |
not null |
Вес |
|
manufacturer_id |
integer |
not null |
Идентификатор производителя. FK |
|
with_sugar |
boolean |
|
С сахаром? true — да, false — нет |
|
requires_freezing |
boolean |
|
Требует заморозки? true — да, false — нет |
|
production_date |
date |
not null |
Дата изготовления |
|
expiration_date |
date |
not null |
Срок годности |
В таблице есть:
-
Столбцы,
-
Строки,
-
Ячейки,
-
Ограничения (constraint): PK — первичный ключ, FK — вторичный ключ,
-
Тип данных.
В SQL выделяют основные 4 группы операторов:
-
DDL (Data Definition Language) – работа со структурой БД,
-
DML (Data Manipulation Language) – работы с данными таблиц,
-
DCL (Data Control Language) – работа с правами,
-
TCL (Transaction Control Language) – работа с транзакциями.
DML чаще всего спрашивают на собеседовании. DCL и DML нужны в работе системного аналитика. DCL, TCL в моей практике не приходилось пользоваться ни на собеседовании, ни в работе системного аналитика, поэтому в данной статье не будем их рассматривать, так как за ночь нужно выучить или вспомнить то, что действительно могут спросить на собеседовании (экзамене) или пригодиться в работе.
А теперь шпаргалки SQL операторов
Работа со структурой БД (DDL)
CREATE. Создание таблицы.
CREATE TABLE public.sweets_types ( id integer NOT NULL, name character varying NOT NULL, PRIMARY KEY (id) );Имя создаваемой таблицы указано в формате: Имя схемы.Имя таблицы.
Например, схема = public, имя таблицы = sweets_types, тогда имя создаваемой таблицы = public.sweets_types
ALTER. Добавление, изменение или удаление столбцов в таблице.
Пример SQL запроса, который добавит столбец name_english с типом данных character varying в таблицу sweets_types
ALTER TABLE IF EXISTS public.sweets_types ADD COLUMN name_english character varying;DROP. Удаление целиком таблицы из БД.
DROP TABLE public.sweets_types;TRUNCATE. Удаление всех записей из таблицы.
TRUNCATE TABLE public.sweets_type;Работа с данными таблиц (DML)
INSERT. Добавление строки в таблицу.
INSERT INTO public.sweets_types(name) VALUES ('вафли'), ('конфеты');Столбец id в таблице sweets_types является PK и сгенерится автоматом при добавлении, поэтому в INSERT добавляем значение столбца name
UPDATE. Обновление данных строки в таблице.
UPDATE public.sweets_types SET name = 'вафли новые' WHERE id = 1;DELETE. Удаление строки из таблицы.
DELETE FROM public.sweets_types WHERE name = 'вафли';SELECT. Просмотр данных из таблицы.
Все виды сладостей (идентификатор и имя)
SELECT * FROM public.sweets_types;Только имена видов сладостей
SELECT name FROM public.sweets_types;DISTINCT. Возвращает уникальные значения, без повторений.
SELECT DISTINCT name FROM public.sweetsWHERE. Условие фильтрации записей при выборе данных.
Список сладостей, у которых стоимость равна 100
SELECT name FROM public.sweets WHERE cost = '100';AND, OR, BETWEEN. Оператор «И», «Или», «Между».
Список сладостей, у которых стоимость равна 100 и вес равен 100
SELECT name FROM public.sweets WHERE cost = '100' AND weight = '100';Список сладостей, у которых стоимость равна от 50 до 100
SELECT name FROM public.sweets WHERE cost BETWEEN '50' AND '100';GROUP BY. Группировка столбцов.
SELECT sweets_types_id FROM public.sweets GROUP BY sweets_types_id;HAVING. Используется для фильтрации по условию, когда есть группировка.
Найти вид сладости, у которого есть изделия с весом 300
SELECT sweets_types_id FROM public.sweets GROUP BY sweets_types_id, weight HAVING weight = '300';ORDER BY ASC, DESC. Сортировка в порядке возрастания (asc) или убывания (desc).
ASC можно не указывать.
SELECT * FROM public.sweets ORDER BY name DESC;COUNT. Количество строк.
Посчитать количество сладостей, у которых вес равен 300
SELECT COUNT(id) FROM public.sweets WHERE weight = '300';SUM, MAX, MIN, AVG. Сумма значений, максимальное, минимальное, среднее значение.
SELECT SUM(id) FROM public.sweets; SELECT MAX(id) FROM public.sweets; SELECT MIN(id) FROM public.sweets; SELECT AVG(id) FROM public.sweets;LIKE. Поиск заданного значения в столбце по совпадению.
С оператором LIKE используются два подстановочных знака:
-
% — любое количество символов;
-
_ — один символ.
Найти список сладостей, которые начинаются на М
SELECT * FROM public.sweets WHERE name LIKE 'М%';JOIN или INNER JOIN, LEFT JOIN, RIGHT JOIN. Объединение двух таблиц.
-
JOIN или INNER JOIN — возвращает записи, имеющие в обеих таблицах
-
LEFT JOIN — возвращает все записи из левой таблицы и те, которые есть в левой и правой таблице
-
RIGHT JOIN — возвращает все записи из правой таблицы и те, которые есть в правой таблице
Подробная работа с JOIN описана в статье.
SELECT * FROM public.sweets s JOIN public.sweets_types st ON s.sweets_types_id = st.id WHERE st.name = 'шоколад';SQL практика
Шаг 1. Установить инструменты для работы
Для выполнения практических заданий берем базу данных — Postgres и СУБД — pgAdmin
Скачать Postgres и pgAdmin можно по ссылке. Как поставить показано в видео по ссылке.
После установки у вас создастся локальный сервер на вашем ПК, в котором вы создадите базу данных.
Как настроить подключение к серверу через pgAdmin описано в статье по ссылке, раздел 2. Запуск.
ШАГ 1. Создаем таблицы в БД
В pgAdmin есть форма создания таблиц: Схемы -> public -> Таблицы -> Создать.
Заполните вкладки General, Столбцы.
Посмотреть картинки как это сделать в pgAdmin
Для столбцов id в таблицах устанавливаем ограничения
Скрипт SQL на создание таблиц в БД
CREATE TABLE public.sweets_types ( id integer NOT NULL, name character varying NOT NULL, PRIMARY KEY (id) ); ALTER TABLE IF EXISTS public.sweets_types OWNER to postgres; COMMENT ON TABLE public.sweets_types IS 'Виды сладостей'; CREATE TABLE public.sweets ( id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ), sweets_types_id integer, name character varying NOT NULL, cost character varying NOT NULL, weight character varying NOT NULL, manufacturer_id integer NOT NULL, with_sugar boolean, requires_freezing boolean, production_date date NOT NULL, expiration_date date NOT NULL, PRIMARY KEY (id) ); ALTER TABLE IF EXISTS public.sweets OWNER to postgres; COMMENT ON TABLE public.sweets IS 'Записи о сладостях'; CREATE TABLE public.manufacturers_storehouses ( id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ), storehouses_id integer NOT NULL, manufacturers_id integer NOT NULL, PRIMARY KEY (id) ) TABLESPACE pg_default; ALTER TABLE IF EXISTS public.manufacturers_storehouses OWNER to postgres; COMMENT ON TABLE public.manufacturers_storehouses IS 'Связь компании производителя и склада'; CREATE TABLE public.manufacturers ( id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ), name character varying NOT NULL, phone character varying, adress character varying, city character varying NOT NULL, country character varying NOT NULL, PRIMARY KEY (id) ); ALTER TABLE IF EXISTS public.manufacturers OWNER to postgres; COMMENT ON TABLE public.manufacturers IS 'Компании производители'; CREATE TABLE public.storehouses ( id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ), name character varying NOT NULL, adress character varying, city character varying NOT NULL, country character varying NOT NULL, PRIMARY KEY (id) ); ALTER TABLE IF EXISTS public.storehouses OWNER to postgres; COMMENT ON TABLE public.storehouses IS 'Склады';✅ Таблицы созданы!
ШАГ 2. Добавляем записи в БД
Наполняем таблицы данными с помощью SQL скрипта. Используем оператор INSERT INTO ... VALUES
В pgAdmin заходим в таблицу sweets_types и создаем скрипт Insert: Схемы -> public -> Таблицы -> sweets_types (правая кнопка мыши) -> Scripts -> Insert.
Аналогично наполняем данными другие таблицами.
Посмотреть картинки как это сделать в pgAdmin
Скрипт SQL на наполнение данными таблиц
INSERT INTO public.sweets_types( name) VALUES ('вафли'), ('конфеты'), ('мармелад'), ('печенье'), ('шоколад'); INSERT INTO public.storehouses( name, adress, city, country) VALUES ('MSK-1', '109235, г. Москва, Проектируемый проезд 4386, д.8', 'Moscow', 'Russia'), ('SPB-1', '197375, г. Санкт-Петербург, Суздальское шоссе, д. 26', 'Saint-petersburg', 'Russia'), ('EKB-1', '620137, г. Екатеринбург, Шефская улица, д. 1А', 'Ekaterinburg', 'Russia'); INSERT INTO public.manufacturers( name, phone, adress, city, country) VALUES ('Мишаня', '', '109235, г. Москва, Проектируемый проезд, д.15', 'Moscow', 'Russia'), ('Собакен', '78125748899', '197375, г. Санкт-Петербург, Суздальское шоссе, д. 75', 'Saint-petersburg', 'Russia'), ('Мартыха', '74657896525', '620137, г. Екатеринбург, Шефская улица, д. 5А', 'Ekaterinburg', 'Russia'); INSERT INTO public.manufacturers_storehouses( storehouses_id, manufacturers_id) VALUES (1, 1), (2, 2), (3, 3), (1, 2), (2, 1); INSERT INTO public.sweets( sweets_types_id, name, cost, weight, manufacturer_id, with_sugar, requires_freezing, production_date, expiration_date) VALUES (1, 'Мильтик', '100', '200',1, false, false, '2022-05-03', '2022-05-15'), (2, 'Микус', '150', '300', 1 , true, true, '2022-04-03', '2022-05-03'), (3, 'Миви', '110', '100', 1 , true, false, '2022-03-03', '2022-04-14'), (4, 'Ми', '120', '200', 1, false, true, '2022-03-04', '2022-04-04'), (5, 'Миса', '145', '570', 1, true, false, '2021-03-03', '2021-12-03'), (1, 'Сольтик', '115', '200', 2 , false, false, '2022-05-03', '2022-05-15'), (2, 'Сокус', '155', '300', 2 , true, true, '2022-03-03', '2022-05-03'), (3, 'Сови', '117', '500', 2 , true, false, '2022-03-03', '2022-04-14'), (4, 'Со', '129', '250', 2, false, true, '2022-03-04', '2022-04-04'), (5, 'Сор', '148', '500', 2, true, false, '2021-02-03', '2021-12-03'), (1, 'Мальтик', '210', '200', 3 , false, false, '2022-05-03', '2022-05-15'), (2, 'Макус', '350', '300', 3 , true, true, '2022-01-03', '2022-05-03'); ✅ Таблицы наполнены!
ШАГ 3. Отрабатываем поиск данных
Предлагаю вам сначала самим написать SQL запросы, а потом смотреть решение. Так вы научитесь искать данные на практических задачах и закрепите теоретические знания
№1. Какие компании производители есть в базе?
Решение №1
SELECT * FROM manufacturers;Выгрузите все столбцы из таблицы manufacturers
№2. Найдите все виды сладостей.
Примечание: виды сладостей в таблице не повторяются
Решение №2
SELECT name FROM public.sweets_types;№3. В каких городах есть склады?
Решение №3
SELECT DISTINCT city FROM storehouses;№4. Найти сладости с истекшим срок годности.
Подсказка: используйте для условия переменную current_date.
Решение №4
SELECT name FROM public.sweets WHERE expiration_date<current_date;№5. Найти сладости, у которых стоимость от 200 до 300
Решение №5.
SELECT * FROM public.sweets WHERE cost>='200' AND cost<'300'; --или SELECT * FROM public.sweets WHERE cost BETWEEN '200'AND'300';№6. Найти сладости, у которых название начинается на букву М
Решение №6
SELECT * FROM public.sweets WHERE name LIKE 'М%';№7. Составить список сладостей, отсортированных от А до Я
Решение №7
SELECT * FROM public.sweets ORDER BY name№8. Найти количество сладостей по каждому виду. В ответе вывести имя вида и количество
Решение №8
SELECT COUNT (s.id), st.name FROM public.sweets s JOIN public.sweets_types st ON s.sweets_types_id = st.id GROUP BY st.name;№9. Найти количество сладостей по каждому виду, у которых истек срок годности.
Решение №9
SELECT COUNT (s.id), st.name FROM public.sweets s JOIN public.sweets_types st ON s.sweets_types_id = st.id GROUP BY st.name, s.expiration_date HAVING s.expiration_date<current_date;№10. Найти количество сладостей по каждому виду, у которых название вида начинается на букву п.
Решение №10
SELECT COUNT (s.id), st.name FROM public.sweets s JOIN public.sweets_types st ON s.sweets_types_id = st.id GROUP BY st.name, s.expiration_date HAVING st.name LIKE 'п%';№11. В каких городах есть склады со сладостями Мильтик?
Решение №11
SELECT DISTINCT s.city FROM public.storehouses s JOIN public.manufacturers_storehouses ms ON s.id = ms.storehouses_id JOIN public.sweets sw ON sw.manufacturer_id = ms.manufacturers_id WHERE sw.name = 'Мильтик';№12. Какое максимальное значение идентификатора у сладости?
Решение №12
SELECT MAX(id) FROM public.sweets;№13. Какое количество сладостей на каждом складе?
Решение №13
SELECT s.name, COUNT (sw.id) FROM public.storehouses s JOIN public.manufacturers_storehouses ms ON s.id = ms.storehouses_id JOIN public.sweets sw ON sw.manufacturer_id = ms.manufacturers_id GROUP BY s.name;✅ Обучился несложным запросам SQL!
Конечно за ночь весь SQL не изучить, но разобраться с необходимым минимум для несложных задач или собеседования вполне реально. Главное желание учиться!
А какие каверзные задачки по SQL задавали вам на собеседовании или встречались в вашей работе?
ссылка на оригинал статьи https://habr.com/ru/post/664550/
Добавить комментарий