🖥 Создание базы данных для маркетплейса с помощью SQL

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

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

▪ Читать дальше

@sqlhub

🖥 Как создать временную таблицу в sql запросе

Для создания временной таблицы используется ключевое слово TEMPORARY в команде CREATE TABLE:

CREATE TEMPORARY TABLE название_таблицы

Удобно создавать временную таблицу на выборке из другой таблицы:

CREATE TEMPORARY TABLE customer_temp SELECT * FROM customer;

@sqlhub

🖥 Как работает CASE SQL

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

Оператор CASE может быть использован в двух форматах:

CASE без ELSE:
SELECT column1,
CASE
WHEN column2 = 'value1' THEN 'result1'
WHEN column2 = 'value2' THEN 'result2'
END AS column3
FROM table;

В этой форме оператора CASE используется блок WHEN для проверки значения столбца column2. Если значение равно value1, то будет возвращено значение result1. Если значение равно value2, то будет возвращено значение result2. Если значение не соответствует ни одному из условий, то для столбца column3 будет возвращено значение NULL.

CASE с ELSE:
SELECT column1,
CASE column2
WHEN 'value1' THEN 'result1'
WHEN 'value2' THEN 'result2'
ELSE 'result3'
END AS column3
FROM table;

В этой форме оператора CASE используется значение столбца column2 в блоке CASE. Если значение равно value1, то будет возвращено значение result1. Если значение равно value2, то будет возвращено значение result2. Если значение не соответствует ни одному из условий, то будет возвращено значение result3.

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

@sqlhub

🖥 Как в sql посчитать количество повторяющихся значений

Можно попробовать использовать функцию COUNT() в сочетании с оператором GROUP BY.

Предположим, у нас есть таблица "orders" с колонками "customer_name" и "order_date", и мы хотим посчитать, сколько заказов было сделано каждым клиентом. Мы можем написать следующий SQL-запрос:

SELECT customer_name, COUNT(*) as order_count
FROM orders
GROUP BY customer_name

Этот запрос выберет все уникальные значения из колонки "customer_name" в таблице "orders" и подсчитает количество строк, связанных с каждым уникальным значением. Результатом запроса будет таблица со столбцами "customer_name" и "order_count", в которой каждая строка будет показывать клиента и количество его заказов.

@sqlhub

🖥 Оператор FULL OUTER JOIN в SQL

Оператор FULL OUTER JOIN объединяет две таблицы на основе общего столбца и выбирает не только записи с совпадающими значениями в этих столбцах, но и все оставшиеся строки из обеих таблиц. Например:

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer;

На картинке видно как отработает данный код.

Здесь мы выбираем столбцы customer_id и first_name (из таблицы Customers) и столбец amount (из таблицы Orders). В результате получаем те строки, в которых есть совпадение между customer_id (таблицы Customers) и customer (таблицы Orders) вместе со всеми остальными строками из обеих таблиц.

Синтаксис оператора FULL OUTER JOIN
Синтаксис оператора FULL OUTER JOIN следующий:

SELECT столбцы
FROM таблица1
FULL OUTER JOIN таблица2
ON таблица1.имя_столбца = таблица2.имя_столбца;
Оператор FULL OUTER JOIN с оператором WHERE

Вот пример использования оператора FULL OUTER JOIN с оператором WHERE:

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer
WHERE Orders.amount >= 500;

Здесь мы объединяем две таблицы и выбираем строки, в которых сумма (amount) больше или равна 500.

Оператор FULL OUTER JOIN с псевдонимами AS
Мы можем использовать псевдонимы AS с оператором FULL OUTER JOIN, чтобы сделать наш код короче и чище. Например:

SELECT C.cat_name, P.prod_title
FROM Categories AS C
FULL OUTER JOIN Products AS P
ON C.cat_id = P.cat_id;

Здесь мы выбираем общие строки между таблицами Categories и Products.

@sqlhub

🖥 BACKUP DATABASE в SQL

Важно регулярно создавать резервные копии базы данных, чтобы данные не были потеряны в случае повреждения базы данных (БД). В SQL мы можем создавать резервные копии БД с помощью оператора BACKUP DATABASE.

Например:

BACKUP DATABASE orders
TO DISK = 'C:\orders_backup.bak';
Здесь мы создаем файл резервной копии базы данных orders на диске C с именем orders_backup.bak.

Примечание: Распространено использование расширения .bak для файлов резервных копий БД, однако это не является обязательным.

Резервное копирование только новых изменений в SQL
В SQL мы также можем сделать резервную копию только новых изменений по сравнению с предыдущей резервной копией, используя команду WITH DIFFERENTIAL. Например:

BACKUP DATABASE orders
TO DISK = 'C:\orders_backup.bak'
WITH DIFFERENTIAL;
Здесь мы добавляем только новые изменения в предыдущий файл резервной копии. Следовательно, эта команда работает быстрее, нежели создание резервной копии БД с нуля.

Восстановление базы данных из резервной копии

Для восстановления файла резервной копии в системе управления базой данных (СУБД) используется оператор RESTORE DATABASE. Например:

RESTORE DATABASE orders
FROM DISK = 'C:\orders_backup.bak';
Здесь мы восстанавливаем файл резервной копии orders_backup.bak в базе данных orders.

@sqlhub

🖥 Ограничение CHECK в SQL

Ограничение CHECK используется для указания условия, которое должно быть выполнено для вставки значения в таблицу.

Например:

CREATE TABLE Orders (
order_id INT PRIMARY KEY,
amount INT CHECK (amount > 0)
);

Здесь столбец amount принимает значения только больше 0, это является условием для проверки. Теперь давайте попробуем вставить данные в таблицу Orders.

Пример №1

-- Вставляем значение 100.
-- Данные добавлены
INSERT INTO Orders(amount) VALUES(100);

Пример №2

-- Вставляем значение -5.
-- Ошибка при добавлении данных
INSERT INTO Orders(amount) VALUES(-5);

Примечание: Ограничение CHECK используется для проверки данных только при вставке. Чтобы проверить, существует ли строка, следует использовать оператор EXISTS.

❗️Создать именованное ограничение CHECK

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

Например:

-- Создаем именнованое ограничение amountCK для столбца amount.
-- Ограничение проверяет, чтобы значение для вставки было больше 0
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
amount INT,
CONSTRAINT amountCK CHECK (amount > 0)
);

❗️Ограничение CHECK в существующей таблице

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

Например:

-- Добавляем неименованное ограничение CHECK
ALTER TABLE Orders
ADD CHECK (amount > 0);

Также можно добавить и именованное ограничение CHECK:

-- Добавляем именованное ограничение CHECK - amountCK
ALTER TABLE Orders
ADD CONSTRAINT amountCK CHECK (amount > 0);

Примечание: Если мы попытаемся добавить ограничение amount > 0 к столбцу, значение которого уже меньше 0, мы получим ошибку.

❗️Удалить ограничение CHECK

Мы можем удалить ограничение CHECK, используя оператор DROP.

Например:

SQL Server, PostgreSQL, Oracle

-- Удаляем ограничение CHECK - amountCK
ALTER TABLE Orders
DROP CONSTRAINT amountCK;

MySQL

-- Удаляем ограничение CHECK - amountCK
ALTER TABLE Orders
DROP CHECK amountCK;

🖥 Подборка бесплатных курсов по SQL

Собрали для вас список бесплатных курсов по SQL от базовых до продвинутых.

1. SQL for Data Analysis
2. Интерактивный тренажер по SQL
3. Kaggle: Intro to SQL
4. Advanced SQL
5. Introduction to Structured Query Language (SQL)
6. Advanced Databases and SQL Querying
7. Databases and SQL for Data Science with Python
8. Oracle SQL – A Complete Introduction
9. Intro to SQL
10. Advanced SQL
11. Oracle SQL Basics
12. Beginners Guide to SQL
13. SQL for Data Science
14. Introduction to Databases and SQL Querying
15. Intro to Relational Databases

#SQL #курсы #бд

@sqlhub

🖥 CREATE INDEX в SQL

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

Индекс — это объект базы данных, создаваемый с целью повышения производительности поиска данных. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Словно закладка в книге, индекс помогает быстро получить доступ к требуемым данным в таблице, согласно SQL-запросу. Таким образом, использование индексов позволяет ускорить получение данных.

Например:

-- Создаем таблицу
CREATE TABLE Colleges (
college_id INT PRIMARY KEY,
college_code VARCHAR(20) NOT NULL,
college_name VARCHAR(50)
);

-- Создаем индекс
CREATE INDEX college_index
ON Colleges(college_code);

Здесь мы создаем индекс с именем college_index в таблице Colleges, используя столбец college_code.

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

CREATE UNIQUE INDEX для уникальных значений
Если нужно создать индексы для уникальных значений в столбце, следует использовать ограничение CREATE UNIQUE INDEX.

Например:

-- Создаем уникальный индекс
CREATE UNIQUE INDEX college_index
ON Colleges(college_code);

Здесь мы создаем уникальный индекс с именем college_index в таблице Colleges, используя столбец college_code.

Удалить индекс из таблиц

Для удаления индекса из таблицы используется оператор DROP INDEX.

Например:

SQL Server

DROP INDEX Colleges.college_index;
PostgreSQL, Oracle

DROP INDEX college_index;
MySQL

ALTER TABLE Colleges
DROP INDEX college_index;
Здесь мы удаляем ограничение college_index из таблицы Colleges.

Примечание: Удаление индекса означает, что удаляется только индекс. Данные в исходной таблице остаются неизменными.

@sqlhub

🖥 Advanced SQL Injection Cheatsheet

Этот репозиторий содержит расширенную методологию всех типов SQL иньекций.

🖥 Github

@sqlhub

🖥 Полезные тренажеры SQL

Тренажеры, которые могут пригодится для "оттачивания" навыков и подготовки к собеседованиям. Конечно, нет ничего лучше реальных задач, но иногда реальных нет. Когда у меня была такая ситуация, я прорешала задачи на Hakerrank и это помогло мне в дальнейшем пройти собеседование. Там же, например на Hakerrank, можно получить сертификаты для подтверждения своего уровня.

● Hakerrank
● Strata scratch
● SQLBolt
● W3schools
● Курс по основам SQL, состоящий из 19 небольших уроков с лёгкой подачей

@sqlhub

🖥 Большой справочник по SQL на русском

📌Скачать

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

👇 Папка с книгами:
https://t.me/addlist/BkskQciUW_FhNjEy

Сохраняем себе, чтобы не потерять

@sqlhub

🖥 Как полностью очистить таблицу sql

Полностью очистить таблицу, не удаляя её можно с помощью запроса DELETE. Мы указываем имя таблицы, но опускаем блок WHERE. Тогда удаляются абсолютно все записи, вне зависимости от условий.

DELETE FROM wiped_table_name;
-- Мы очистили таблицу wiped_table_name

Кроме DELETE без условий можно использовать TRUNCATE. У неё есть свои особенности. Самая главная, она быстрее DELETE, так как не сканирует таблицы перед очисткой и наиболее полезна для очистки больших таблиц.

TRUNCATE cars, cups, parrots;
-- Тут можно перечислять сколько угодно таблиц

Крайне рекомендую глянуть документацию по TRUNCATE, она очень подробная: https://postgrespro.ru/docs/postgrespro/9.5/sql-truncate

@sqlhub

🖥 Как проектировать запросы на изменение данных sql

При проектировании запросов на изменение данных в SQL необходимо следовать нескольким рекомендациям:

Сделайте резервную копию данных перед выполнением запроса на изменение данных. Это поможет избежать потери данных в случае ошибки.

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

Используйте оператор SET для задания новых значений столбцов в таблице.

Например:

UPDATE orders
SET order_status = 'Completed'
WHERE order_id = 12345;

Здесь мы обновляем столбец order_status таблицы orders, устанавливая значение Completed для заказа с order_id равным 12345.

Обратите внимание на условие WHERE, которое определяет, какие строки будут изменены. Если условие не указано, запрос обновит все строки в таблице.

Если вы хотите изменить несколько столбцов в таблице, используйте оператор SET для каждого столбца, например:

UPDATE orders
SET order_status = 'Completed',
order_total = 100.00
WHERE order_id = 12345;
Здесь мы обновляем значения столбцов order_status и order_total таблицы orders для заказа с order_id равным 12345.

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

@sqlhub

🖥 Большая шпарглака по psql

Полезные советы с примерами.

▪Шпаргалка

@sqlhub

🖥 Задача: Выбрать самую актуальную запись с учетом статуса (успешная / отмененная транзакция) и временной метки

Задачу часто спрашивают на собеседованиях в формулировке “как удалить дубли / копии строк”, и решить ее можно несколькими способами. Я привык мыслить в терминах историзации данных в Хранилище, и удаление мне ни к чему, поэтому для решения задачи я воспользуюсь ранжирующей функцией ROWNUMBER().

SQL Fiddle (Oracle 11g R2): http://sqlfiddle.com/#!4/ad305/1

with decoded as (
select
"transaction_id"
,"is_successful"
,"ts"
,decode("is_successful", 'true', 0, 'false', 1, 2) as "order_is_successful"
from transactions
),
ordered as (
select
"transaction_id"
,"is_successful"
,"ts"
,row_number() over(partition by "transaction_id" order by "order_is_successful" asc, "ts" desc) as rn
from decoded
)
select
"transaction_id"
,"is_successful"
,"ts"
from ordered
where rn = 1
;

Результат

Пишите свое решение в комментариях👇

@sqlhub

🖥 Изучаем базы данных

Большой сборник теоретического материала по работе с базами данных.

▪основы: http://phpclub.ru/mysql/doc/tutorial.html

▪отношения между таблицами в БД: http://jtest.ru/bazyi-dannyix/sql-dlya-nachinayushhix-chast-3.html

▪внешние ключи: http://denis.in.ua/foreign-keys-in-mysql.htm

▪большой учебник по SQL: http://www.pyramidin.narod.ru/rusql/index.htm

▪сборник запросов на все случаи жизни (англ): http://www.artfulsoftware.com/infotree/queries.php

▪таблицы отличий в диалектах SQL в разных СУБД (англ): http://en.wikibooks.org/wiki/SQL_dialects_reference

▪манга-учебник про SQL в картинках: http://www.nostarch.com/mg_databases.htm

👉 делитесь с коллегами полезным материалом

@sqlhub

🖥 Подстановочные знаки (wildcards) в SQL

Подстановочный знак (wildcard) в SQL используется с оператором LIKE для замены одного или набора символов в строке. Например:

SELECT *

FROM Customers

WHERE last_name LIKE 'R%';

Здесь % (означает ноль или более символов) является подстановочным знаком (wildcard). Мы выбираем клиентов, чья фамилия (last_name) начинается с буквы R, за которой следует ноль или более символов.

▪Читать дальше

@sqlhub