13 Инструкций SQL для решения 90% ваших задач по обработке данных

Язык структурированных запросов (SQL) – это язык программирования, предназначенный для управления реляционными базами данных. Он широко используется специалистами для извлечения информации из больших датасетов.

SQL – это мощный инструмент, который можно использовать для выполнения широкого спектра задач по обработке данных, включая фильтрацию, сортировку, группировку и агрегирование данных. В этой статье мы рассмотрим 13 основных инструкций SQL, которые помогут вам выполнить 90% ваших задач по обработке данных. Эти инструкции просты для понимания и реализации и обеспечат вам прочную основу для работы с SQL.

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

1. Select

Инструкция SELECT используется для извлечения данных из одной или нескольких таблиц в базе данных. Вы должны освоить SELECT для фильтрации, сортировки и группировки данных с использованием различных функций, таких как WHERE, ORDER BY и GROUP BY. Вот пример инструкции SELECT:

SELECT column1, column2, column3
FROM table_name
WHERE condition;

В этом примере column1, column2 и column3 – это имена столбцов, из которых вы хотите извлечь данные, а table_name – это имя таблицы, содержащей данные. Инструкция WHERE является необязательной, но используется для указания условия, которое должно быть выполнено для того, чтобы запрос извлёк данные.

Вот пример, который выбирает все записи из таблицы под названием “customers”, где возраст клиента больше или равен 18:

SELECT *
FROM customers
WHERE age >= 18;

2. JOIN

Оператор JOIN используется для объединения данных из двух или более таблиц. Вы должны освоить использование JOIN для извлечения данных из нескольких таблиц и указать тип соединения (например, INNER, LEFT, RIGHT, FULL OUTER) соответствующим образом.

Вот несколько примеров инструкций JOIN:

INNER JOIN

INNER JOIN возвращает только те строки, в которых есть совпадение между столбцами в обеих таблицах. Вот пример:

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

В этом примере таблица orders и таблица customers объединяются с помощью столбца customer_id. Результирующая таблица будет включать только столбцы order_id и customer_name, в которых есть совпадение между столбцами customer_id в обеих таблицах.

LEFT JOIN

LEFT JOIN возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если в правой таблице нет совпадения, результат будет содержать нулевые значения. Вот пример:

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

В этом примере таблица customers – это левая таблица, а таблица orders – правая. Столбец customer_id используется для объединения таблиц. Результирующая таблица будет включать все строки из таблицы customers и соответствующие строки из таблицы orders. Если в таблице orders нет совпадения, столбец order_id будет содержать нулевые значения.

RIGHT JOIN

RIGHT JOIN возвращает все строки из правой таблицы и соответствующие строки из левой таблицы. Если в левой таблице нет совпадения, результат будет содержать нулевые значения. Вот пример:

SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

В этом примере таблица orders– это левая таблица, а таблица customers – правая. Столбец customer_id используется для объединения таблиц. Результирующая таблица будет включать все строки из таблицы orders и соответствующие строки из таблицы customers. Если в таблице customers нет совпадения, столбец customer_name будет содержать нулевые значения

OUTER JOIN

OUTER JOIN в SQL используется для возврата всех строк из одной или обеих таблиц, включая несовпадающие строки. Существует два типа OUTER JOIN: LEFT OUTER JOIN и RIGHT OUTER JOIN.

Вот пример LEFT OUTER JOIN:

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

В этом примере таблица customers – это левая таблица, а таблица orders – правая. Столбец customer_id используется для объединения таблиц. Результирующая таблица будет включать все строки из таблицы customers и соответствующие строки из таблицы orders. Если в таблице ordersнет совпадения, столбец order_id будет содержать нулевые значения.

Вот пример RIGHT OUTER JOIN:

SELECT customers.customer_name, orders.order_id
FROM customers
RIGHT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

В этом примере таблица orders – это левая таблица, а таблица customers – правая. Столбец customer_id используется для объединения таблиц. Результирующая таблица будет включать все строки из таблицы orders и соответствующие строки из таблицы customers . Если в таблице customers нет совпадения, столбец customer_name будет содержать нулевые значения.

Стоит отметить, что некоторые базы данных могут не поддерживать RIGHT OUTER JOIN, но вы можете достичь того же результата, используя LEFT OUTER JOIN и поменяв порядок таблиц местами.

3. WHERE

Инструкция WHERE используется для фильтрации данных на основе указанного условия. Вы должны освоить использование WHERE для извлечения только тех данных, которые соответствуют определённым критериям.

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

Предположим, у нас есть таблица с названием “employees” со столбцами для имени, отдела и зарплаты. Мы можем использовать инструкцию WHERE, чтобы выбрать только тех сотрудников, которые работают в отделе “Sales” и имеют зарплату более 50 000 долларов:

SELECT name, department, salary
FROM employees
WHERE department = 'Sales' AND salary > 50000;

Этот запрос вернёт список всех сотрудников, которые работают в отделе “Sales” и имеют зарплату более 50 000 долларов, с их именами, отделами и зарплатами, отображаемыми в результатах.

4. GROUP BY

Инструкция GROUP BY используется для группировки данных на основе одного или нескольких столбцов, а агрегатные функции (например, COUNT, SUM, AVG) могут использоваться для вычисления сводок сгруппированных данных. Вам следует освоить использование GROUP BY для анализа данных по категориям.

Предположим, у нас есть таблица с именем “employees” со столбцами для имени, отдела и зарплаты. Мы можем использовать инструкцию GROUP BY, чтобы сгруппировать сотрудников по отделам и рассчитать среднюю зарплату для каждого отдела:

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

Этот запрос вернёт список всех отделов и среднюю заработную плату для каждого отдела, рассчитанную путём взятия суммы всех зарплат сотрудников в этом отделе и деления её на количество сотрудников в этом отделе. Инструкция GROUP BY используется для группировки сотрудников по отделам, а функция AVG используется для расчёта средней заработной платы по каждому отделу.

department | avg_salary
-----------------------
Sales      | 65000
Marketing  | 55000
Engineering| 80000

В этом примере мы можем видеть, что средняя зарплата отдела продаж составляет 65 000 долларов, отдела маркетинга – 55 000 долларов, а инженерного отдела – 80 000 долларов.

5. HAVING

Оператор HAVING используется для фильтрации данных после того, как они были сгруппированы с помощью оператора GROUP BY. Вы должны освоить использование HAVING, чтобы фильтровать сгруппированные данные на основе конкретных условий.

Вот пример использования HAVING в SQL:

Предположим, у нас есть таблица с именем “orders” со столбцами для “order_id”, “customer_id”, “product_id” и “quantity”. Мы хотим найти клиентов, которые заказали в общей сложности не менее 50 единиц продукции. Мы можем использовать инструкцию GROUP BY, чтобы сгруппировать заказы по клиентам и рассчитать общее количество каждого продукта, заказанного каждым клиентом. Затем мы можем использовать HAVING для фильтрации результатов, чтобы включить в них только клиентов, заказавших общее количество не менее 50 единиц:

SELECT customer_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_id
HAVING SUM(quantity) >= 50;

Этот запрос вернул бы список всех клиентов и общее количество заказанных ими товаров, но включал бы только клиентов, заказавших общее количество не менее 50 единиц. Инструкция GROUP BY используется для группировки заказов по клиентам, функция SUM используется для вычисления общего количества продуктов, заказанных каждым клиентом, а HAVING используется для фильтрации результатов, чтобы включить только клиентов, заказавших общее количество не менее 50 единиц.

Выходные данные запроса будут выглядеть примерно так:

customer_id | total_quantity
---------------------------
123        | 60
456        | 70

В этом примере мы можем видеть, что клиент 123 заказал в общей сложности 60 единиц продукции, а клиент 456 заказал в общей сложности 70 единиц продукции. Оба этих клиента соответствуют условию, указанному в HAVING.

6. Оконные функции

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

1. ROW_NUMBER(): Эта функция присваивает уникальный порядковый номер каждой строке в разделе. Синтаксис для функции ROW_NUMBER() следующий:

SELECT column1, column2, ..., ROW_NUMBER() OVER (ORDER BY column1) AS row_num
FROM table_name;

Этот запрос вернёт результирующий набор с дополнительным столбцом “row_num”, который содержит порядковые номера, присвоенные каждой строке в соответствии с порядком “column1”.

2. SUM(): Эта функция вычисляет сумму столбца внутри раздела. Синтаксис функции SUM() следующий:

SELECT column1, column2, ..., SUM(column3) OVER (PARTITION BY column1) AS column3_sum
FROM table_name;

Этот запрос вернёт результирующий набор с дополнительным столбцом “column3_sum”, который содержит сумму “column3” для каждого раздела на основе значений “column1”.

3. RANK(): Эта функция присваивает ранг каждой строке в разделе на основе значений указанного столбца. Синтаксис для функции RANK() следующий:

SELECT column1, column2, ..., RANK() OVER (PARTITION BY column1 ORDER BY column3 DESC) AS rank_num
FROM table_name;

Этот запрос вернёт результирующий набор с дополнительным столбцом “rank_num”, который содержит ранг каждой строки в каждом разделе на основе порядка убывания “column3”.

4. AVG(): Эта функция вычисляет среднее значение столбца внутри раздела. Синтаксис для функции AVG() следующий:

SELECT column1, column2, ..., AVG(column3) OVER (PARTITION BY column1) AS column3_avg
FROM table_name;

Этот запрос вернёт результирующий набор с дополнительным столбцом “column3_avg”, который содержит среднее значение “column3” для каждого раздела на основе значений “column1”.

Обратите внимание, что синтаксис оконных функций может варьироваться в зависимости от конкретной используемой системы управления базами данных (СУБД).

7. UNION

В SQL оператор UNION используется для объединения результатов двух или более операторов SELECT в единый результирующий набор. Инструкции SELECT должны содержать одинаковое количество столбцов, а столбцы должны иметь совместимые типы данных. Повторяющиеся строки автоматически удаляются из результирующего набора.

Вот пример использования оператора UNION в SQL:

Предположим, у нас есть две таблицы с именами “customers” и “employees”, обе со столбцами для имени и города. Мы хотим создать список всех людей (как клиентов, так и сотрудников), которые живут в Нью-Йорке. Мы можем использовать оператор UNION для объединения результатов двух операторов SELECT, по одному для каждой таблицы:

SELECT name, city
FROM customers
WHERE city = 'New York'
UNION
SELECT name, city
FROM employees
WHERE city = 'New York';

Этот запрос вернёт список всех людей, которые живут в Нью-Йорке, включая как клиентов, так и сотрудников. Первая инструкция SELECT извлекает всех клиентов, которые живут в Нью-Йорке, а вторая инструкция SELECT извлекает всех сотрудников, которые живут в Нью-Йорке. Оператор UNION объединяет результаты этих двух операторов SELECT и удаляет все повторяющиеся строки.

Выходные данные запроса будут выглядеть примерно так:

name        | city
-------------------
John Smith  | New York
Jane Doe    | New York
Bob Johnson | New York
Samantha Lee| New York

В этом примере мы можем видеть, что есть четыре человека, которые живут в Нью-Йорке, двое из таблицы “customers” и двое из таблицы “employees”, и оператор UNION объединил результаты двух операторов SELECT.

8. CREATE

Инструкция CREATE используется для создания новой таблицы базы данных, представления или других объектов базы данных. Вот пример использования инструкции CREATE в SQL:

Предположим, мы хотим создать новую таблицу под названием “customers” со столбцами “id”, “имя”, “электронная почта” и “телефон”. Для этого мы можем использовать инструкцию CREATE:

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100),
  phone VARCHAR(20)
);

Этот запрос создаст новую таблицу под названием “customers” с четырьмя столбцами: “идентификатор”, “имя”, “электронная почта” и “телефон”. Столбец “id” определяется как целое число и устанавливается в качестве первичного ключа таблицы. Столбец “имя” определяется как строка с максимальной длиной 50 символов, а столбцы “электронная почта” и “телефон” также определяются как строки с максимальной длиной 100 и 20 символов соответственно.

После выполнения запроса мы можем вставить новые строки в таблицу “customers” и извлечь из неё данные:

INSERT INTO customers (id, name, email, phone)
VALUES (1, 'John Doe', 'johndoe@example.com', '555-555-1234');

SELECT * FROM customers;

Этот запрос вставил бы новую строку в таблицу “customers” с идентификатором 1, именем “Джон Доу”, электронной почтой “johndoe@example.com ”, и номером телефона “555-555-1234”. Второй запрос извлёк бы все строки из таблицы “customers”, которая включала бы новую строку, которую мы только что вставили:

id | name     | email                | phone
--------------------------------------------
1  | John Doe | johndoe@example.com | 555-555-1234

В этом примере мы использовали инструкцию CREATE для создания новой таблицы в базе данных и вставили в таблицу новую строку.

9. INSERT

Инструкция INSERT используется для вставки данных в таблицу базы данных. Вы должны освоить использование INSERT для добавления новых данных в таблицу базы данных. Вот пример использования инструкции INSERT в SQL:

Предположим, у нас есть таблица с именем “students” со столбцами “id”, “имя”, “специальность” и “средний балл”. Мы хотим вставить новую строку в таблицу для студента с идентификатором 1234, именем “Джон Доу”, специальностью “Информатика” и средним баллом 3,5. Для этого мы можем использовать инструкцию INSERT:

INSERT INTO students (id, name, major, gpa)
VALUES (1234, 'John Doe', 'Computer Science', 3.5);

Этот запрос вставит новую строку в таблицу “students” с указанными значениями для столбцов “идентификатор”, “имя”, “специальность” и “средний балл”. Инструкция INSERT указывает имя таблицы, за которым следует список столбцов, в которые мы хотим вставить значения. Затем мы используем ключевое слово VALUES, чтобы указать значения, которые мы хотим вставить в каждый столбец, в том порядке, в котором были перечислены столбцы.

После выполнения запроса в таблице “students” появится новая строка со следующими значениями:

id   | name      | major            | gpa
-----------------------------------------
1234 | John Doe | Computer Science | 3.5

В этом примере мы вставили новую строку в таблицу “students”, используя инструкцию INSERT.

10. UPDATE

Инструкция UPDATE используется для изменения существующих данных в таблице базы данных. Вы должны освоить использование UPDATE для обновления значений одного или нескольких столбцов в таблице. Вот пример использования инструкции UPDATE в SQL:

Предположим, у нас есть таблица с именем “students” со столбцами “id”, “имя”, “специальность” и “средний балл”. Мы хотим обновить специальность и средний балл студента с идентификатором 1234. Для этого мы можем использовать инструкцию UPDATE:

UPDATE students
SET major = 'Mathematics', gpa = 3.7
WHERE id = 1234;

Этот запрос обновил бы столбцы “major” и “gpa” строки в таблице “students” с идентификатором 1234. Инструкция UPDATE указывает имя таблицы, которую мы хотим обновить, за которым следует ключевое слово SET и список пар столбец-значение, которые мы хотим обновить. Затем мы используем WHERE, чтобы указать, какие строки мы хотим обновить. В этом случае мы хотим обновить строку с идентификатором 1234, поэтому мы указываем “WHERE id = 1234”.

После выполнения запроса таблица “students” будет содержать обновлённые значения для столбцов “major” и “gpa” в строке с идентификатором 1234:

id   | name      | major        | gpa
--------------------------------------
1234 | John Doe | Mathematics | 3.7

В этом примере мы обновили столбцы “major” и “gpa” строки в таблице “students”, используя инструкцию UPDATE.

11. DELETE

Инструкция DELETE используется для удаления одной или нескольких строк из таблицы базы данных. Вы должны освоить использование DELETE для удаления данных из таблицы. Вот пример использования инструкции DELETE в SQL:

Предположим, у нас есть таблица с именем “students” со столбцами “id”, “имя”, “специальность” и “средний балл”. Мы хотим удалить студента с идентификатором 1234 из таблицы. Для этого мы можем использовать оператор DELETE:

DELETE FROM students
WHERE id = 1234;

Этот запрос удалит строку с идентификатором 1234 из таблицы “students”. Оператор DELETE указывает имя таблицы, из которой мы хотим удалить, за которым следует инструкция WHERE, чтобы указать, какие строки мы хотим удалить. В этом случае мы хотим удалить строку с идентификатором 1234, поэтому мы указываем “WHERE id = 1234”.

После выполнения запроса в таблице “students” больше не будет строки с идентификатором 1234:

id   | name      | major        | gpa
--------------------------------------
5678 | Jane Doe | Computer Science | 3.5

В этом примере мы использовали оператор DELETE для удаления строки из таблицы “students”.

12. DROP

Оператор DROP используется для удаления таблицы базы данных или другого объекта базы данных. Вы должны освоить использование DROP для удаления ненужных таблиц или других объектов из базы данных. Синтаксис инструкции DROP варьируется в зависимости от типа удаляемого объекта. Некоторыми распространенными примерами являются:

1. DROP TABLE: Эта инструкция используется для удаления существующей таблицы вместе со всеми её данными и индексами. Синтаксис для инструкции DROP TABLE следующий:

DROP TABLE table_name;

2. DROP INDEX: эта инструкция используется для удаления существующего индекса из таблицы. Синтаксис для инструкции DROP INDEX следующий:

DROP INDEX index_name ON table_name;

3. DROP VIEW: эта инструкция используется для удаления существующего представления. Синтаксис для инструкции DROP VIEW следующий:

DROP VIEW view_name;

4. DROP PROCEDURE: эта инструкция используется для удаления существующей хранимой процедуры. Синтаксис инструкции DROP PROCEDURE следующий:

DROP PROCEDURE procedure_name;

Обратите внимание, что точный синтаксис инструкции DROP может варьироваться в зависимости от конкретной используемой системы управления базами данных (СУБД). Кроме того, будьте осторожны при использовании инструкции DROP, так как она безвозвратно удаляет указанный объект и все связанные с ним данные и индексы. Обязательно создайте резервную копию своих данных перед использованием инструкции DROP.

13. ALTER

Инструкция ALTER используется для изменения структуры таблицы базы данных или другого объекта базы данных. Вы должны освоить использование ALTER для добавления или удаления столбцов, изменения типов данных или других аспектов таблицы. Синтаксис инструкции ALTER варьируется в зависимости от типа изменяемого объекта. Некоторыми распространёнными примерами являются:

1. ALTER TABLE: этот оператор используется для изменения структуры существующей таблицы, например, добавления или удаления столбцов, изменения типов данных или установки ограничений. Синтаксис для инструкции ALTER TABLE следующий:

ALTER TABLE table_name
ADD column_name data_type [constraint],
MODIFY column_name data_type [constraint],
DROP column_name,
ADD CONSTRAINT constraint_name constraint_definition,
DROP CONSTRAINT constraint_name;

2. ALTER INDEX: эта инструкция используется для изменения структуры существующего индекса, например, добавления или удаления столбцов или изменения типа индекса. Синтаксис для инструкции ALTER INDEX следующий:

ALTER INDEX index_name
ADD column_name,
DROP column_name;

3. ALTER VIEW: эта инструкция используется для изменения определения существующего представления, например, для изменения инструкции SELECT, используемой для его создания. Синтаксис для инструкции ALTER VIEW следующий:

ALTER VIEW view_name
AS select_statement;

Обратите внимание, что точный синтаксис инструкции ALTER может варьироваться в зависимости от конкретной используемой системы управления базами данных (СУБД).