Помните, как вы были студентами, и готовились к экзаменам по ночам?
Предлагаю вашему вниманию простую шпаргалку по 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.sweets
WHERE. Условие фильтрации записей при выборе данных.
Список сладостей, у которых стоимость равна 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 задавали вам на собеседовании или встречались в вашей работе?