Баннер мобильный (1) Пройти тест

Полное руководство по запросам SQL

Основные команды, операторы и примеры для начинающих

Разбор

29 мая 2024

Поделиться

Скопировано
Полное руководство по запросам SQL

Содержание

    SQL (Structured Query Language) — это язык запросов, с помощью которого можно управлять данными в реляционных базах данных (БД). SQL-запросы состоят из операторов — специальных символов или ключевых слов, которые формируют команды. Разберемся, из чего состоят запросы SQL и как их писать.

    Базовые операторы

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

    Операторы сравнения

    Операторы
    Назначение
    =
    Проверяет равенство двух значений
    <, >
    Проверяют, меньше или больше одно значение другого
    <=, >=
    Проверяют, меньше или равно или больше или равно одно значение другому
    != или <>
    Проверяют, не равны ли два значения

    Арифметические операторы

    Операторы
    Назначение
    +
    Сложение значений
    Вычитание одного значения из другого
    *
    Умножение значений
    /
    Деление одного значения на другое
    %
    Модуль. Возвращает остаток от деления одного значения на другое

    Логические операторы AND, OR и NOT

    AND возвращает TRUE, если оба условия истинны, иначе — FALSE. В некоторых реализациях SQL (например, PostgreSQL) можно использовать ||.

    OR возвращает TRUE, если хотя бы одно из условий истинно, иначе — FALSE. В PostgreSQL допускается обозначение ~.

    NOT — инвертирует значение условия (делает истинное значение ложным и наоборот).

    Структура SQL-запроса

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

    Для этого используют структуру SQL-запроса, которая состоит из обязательных операторов — SELECT и FROM, а также опциональных: WHERE, GROUP BY, HAVING и ORDER BY. Эти операторы относятся к категории команд Data Query Language (DQL).

    строение SQL запроса
    Пример структуры запроса. Источник

    Для запросов SQL не критично, написаны они в одну строку или в столбик. Главное, чтобы запрос был корректным. Однако для повышения читаемости длинные запросы целесообразно форматировать в столбик.

    Категории команд в SQL

    категории команд SQL
    Источник — рисунок в Aseprite

    Data Query Language (DQL) — язык запросов

    Операторы этой категории используются для извлечения данных из БД, их сортировки и группировки. 

    Если баз данных несколько, а для работы нужна конкретная БД, используется оператор USE:

    USE database_name;

    Запрос установит БД database_name в качестве активной. Все последующие запросы SQL будут выполнены для нее.

    SELECT и FROM

    Являются основными и обязательными компонентами SQL-запроса для извлечения данных. Они работают в паре, где SELECT определяет, какие столбцы с данными нужно извлечь, а FROM указывает, из какой таблицы взять эти данные.

    SELECT column1, column2, ...
    FROM table_name;

    где:

    column1, column2, … — названия столбцов, которые нужно извлечь (можно использовать * для выбора всех столбцов);

    table_name — имя таблицы, из которой нужно извлечь данные.

    • Запрос для извлечения всех данных из таблицы:
    SELECT * 
    FROM employees;
    • Запрос для извлечения определенных столбцов из таблицы:
    SELECT first_name, last_name, email 
    FROM employees;

    SQL-агрегатные функции (COUNT, SUM, AVG, MAX, MIN)

    Используются для выполнения вычислений над наборами значений и возвращения единственного результирующего значения.

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

    • Вернуть количество строк в таблице:
    SELECT COUNT(*)
    FROM table_name;

    SUM вычисляет сумму значений в указанном столбце.

    • Вернуть общую сумму зарплат всех сотрудников:
    SELECT SUM(salary)
    FROM employees;

    AVG вычисляет среднее значение из указанного столбца.

    • Вернуть средний возраст работников:
    SELECT AVG(age) 
    FROM employees;

    MAX возвращает максимальное значение из указанного столбца.

    • Вернуть максимальную цену из всех продуктов:
    SELECT MAX(price) 
    FROM products;

    MIN возвращает минимальное значение из указанного столбца.

    • Вернуть минимальное количество товаров в наличии:
    SELECT MIN(stock) 
    FROM inventory;

    Оператор AS

    Задает более читабельный псевдоним столбцу или таблице.

    • Присваиваем псевдоним для результата функции:
    SELECT SUM(salary) AS total_salary 
    FROM employees;

    где:

    SUM(salary) — агрегатная функция, которая вычисляет общую сумму значений в столбце salary;

    AS total_salary задает псевдоним total_salary результату функции SUM.

    WHERE

    Этот оператор определяет, над какими данными будут производиться операции. Условия выбора целевых данных должны быть прописаны в предикатах — выражениях, которые оценивают значения как TRUE, FALSE или UNKNOWN.

    SELECT column1, column2, ...
    FROM table_nameWHERE condition;

    где:

    condition — условие (предикат), которому должны соответствовать данные.

    • Например, так можно реализовать запрос на выборку сотрудников с зарплатой выше 50 000:
    SELECT first_name, last_name, salary
    FROM employees
    WHERE salary > 50000;

    Логические операторы BETWEEN, LIKE, IN, IS NULL

    BETWEEN

    Нужен для выбора значений в пределах заданного диапазона.

    • Выборка всех сотрудников с зарплатой в диапазоне от 50 000 до 80 000:
    SELECT first_name, last_name, salary
    FROM employees
    WHERE salary BETWEEN 50000 AND 80000;

    LIKE

    Используется для сопоставления строк с шаблоном при использовании специальных символов (например, % для любого количества символов и _ для одного символа).

    • Выборка всех клиентов, у которых имя начинается с буквы «A»:
    SELECT first_name, last_name
    FROM customers
    WHERE first_name LIKE 'A%';

    IN

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

    • Выборка всех продуктов из категорий «Электроника» и «Одежда»:
    SELECT product_name, category
    FROM products
    WHERE category IN ('Electronics', 'Clothing');

    IS NULL

    Нужен для выбора строк, в которых отсутствует значение столбца (является NULL).

    • Выборка всех клиентов, у которых не заполнен номер телефона:
    SELECT first_name, last_name
    FROM customers
    WHERE phone_number IS NULL;

    GROUP BY

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

    SELECT column1, column2, ..., aggregate_function(column)
    FROM table_name
    GROUP BY column1, column2, ...;

    где:

    column1, column2, … — столбцы, по которым нужно сгруппировать данные;

    aggregate_function(column) — агрегатная функция, применяемая к столбцам для каждой группы.

    • Найти общее количество продуктов в каждой категории:
    SELECT category, COUNT(*) AS product_count
    FROM products
    GROUP BY category;
    • Вычислить общую сумму продаж для каждого месяца:
    SELECT MONTH(sale_date) AS month, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY MONTH(sale_date);

    HAVING

    Применяют для фильтрации результатов запроса, которые были сгруппированы с использованием оператора GROUP BY.

    SELECT column1, column2, ..., aggregate_function(column)
    FROM table_nameGROUP BY column1, column2, ...
    HAVING condition;

    где:

    condition — условие, которому должны соответствовать результаты после применения агрегатных функций.

    • Выборка категорий продуктов, среднее количество которых превышает 50:
    SELECT category, AVG(quantity) AS avg_quantity
    FROM products
    GROUP BY category
    HAVING AVG(quantity) > 50;
    • Выборка клиентов, у которых стоимость среднего заказа выше 1000:
    SELECT customer_id, AVG(order_amount) AS avg_order_amount
    FROM orders
    GROUP BY customer_id
    HAVING AVG(order_amount) > 1000;

    ORDER BY

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

    SELECT column1, column2, ...
    FROM table_name
    ORDER BY column1 [ASC], column2 [ASC], ...;

    где:

    ASC (или DESC) — необязательное ключевое слово, которое определяет порядок сортировки. По умолчанию используется ASC (порядок возрастания), но можно указать DESC (порядок убывания).

    • Сортировка заказов по дате в порядке убывания:
    SELECT order_id, order_date, customer_id
    FROM orders
    ORDER BY order_date DESC;

    Ограничительные конструкции LIMIT и OFFSET

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

    LIMIT

    Определяет количество строк, которые нужно вернуть. Если оно равно нулю, запрос возвращает пустой набор результатов.

    SELECT column1, column2, ...
    FROM table_nameLIMIT number_of_rows;

    где:

    number_of_rows — количество строк, которые нужно вернуть. Если значение равно нулю, запрос вернет пустой набор результатов.

    OFFSET

    Указывает, сколько строк нужно пропустить перед возвратом результата. Если не определять OFFSET, запрос возвращает данные, начиная с первой строки, указанной в SELECT.

    SELECT column1, column2, ...
    FROM table_name
    OFFSET number_of_rows_to_skip;

    где:

    number_of_rows_to_skip — количество строк, которые нужно пропустить перед возвратом результата.

    Операторы LIMIT и OFFSET лучше всего использовать вместе с ORDER BY. Это упорядочит возвращаемый результат.

    Data Definition Language (DDL) — язык определения данных

    Эти команды используются для определения и управления структурой БД и их объектов, таких как таблицы, индексы и т. д.

    CREATE 

    Используется для создания БД и ее объектов.

    CREATE DATABASE — создание БД.

    CREATE DATABASE my_database;

    CREATE TABLE — создание таблицы.

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        hire_date DATE
    );

    где:

    name VARCHAR(50) создает столбец с именем name, который будет содержать строковые значения (VARCHAR) длиной не более 50 символов;

    date DATE создает столбец с именем date, который будет содержать даты.

    CREATE INDEX — создание индекса.

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

    CREATE INDEX idx_last_name ON employees(last_name);

    Оператор ON указывает на то, что индекс будет создан на столбце last_name таблицы last_name.

    CREATE VIEW — создание представления.

    Представление (view) — это виртуальная таблица, основанная на результате запроса. Представления не хранят данные самостоятельно, они определяются SQL-запросами, которые извлекают данные из одной или нескольких таблиц.

    CREATE VIEW employee_view AS
    SELECT first_name, last_name 
    FROM employees;

    CREATE SCHEMA — создание схемы.

    Схема — это контейнер для хранения объектов БД, таких как таблицы, представления и индексы, которые могут быть организованы и управляться вместе.

    CREATE SCHEMA my_schema;

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

    CREATE TABLE my_schema.my_table 

    CREATE TRIGGER — создание триггера.

    Триггер — это набор инструкций SQL, который автоматически выполняется при наступлении определенного события в БД, такого как вставка, обновление или удаление записи из таблицы SQL.

    CREATE TRIGGER trigger_name
    BEFORE INSERT
    ON table_name
    FOR EACH ROW
    --тело триггера (код, который будет выполнен после срабатывания триггера)

    где:

    BEFORE (или AFTER) — указание времени срабатывания триггера (до или после выполнения операции);

    INSERT (или UPDATE, DELETE) — определение операции, до или после которой сработает триггер;

    ON table_name — указание таблицы, к которой привязан триггер;

    FOR EACH ROW (или STATEMENT) — условие, будет ли триггер выполняться для каждой строки (FOR EACH ROW) или для каждого оператора (FOR EACH STATEMENT). Эта часть синтаксиса может отсутствовать в некоторых СУБД.

    CREATE PROCEDURE

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

    Создание процедуры

    CREATE PROCEDURE get_employee_count()
    BEGIN
    SELECT COUNT(*) 
    FROM employees;
    END;

    Оператор BEGIN обозначает начало транзакции (совокупности операций), а END — ее завершение.

    Вызов процедуры

    CALL get_employee_count();

    CALL вызывает набор инструкций из процедуры или функции.

    DROP 

    Используется для удаления объектов.

    DROP TABLE my_table;

    Аналогично DROP можно применять и с другими объектами, в том числе и с БД.

    ALTER 

    Используется для изменения структуры существующих объектов БД.

    ALTER TABLE my_table
    ADD COLUMN age INT;

    Добавляем столбец (COLUMN) с именем age и форматом данных INT.

    TRUNCATE 

    Нужен для удаления всех записей из таблицы, при этом сохранив структуру таблицы.

    TRUNCATE TABLE my_table;

    RENAME 

    Оператор для переименования объектов БД.

    RENAME TABLE old_table TO new_table;

    Оператор TO указывает на новое значение (новое имя или местоположение).

    COMMENT

    Используется для добавления комментариев к объектам БД.

    COMMENT ON TABLE employees IS 'Таблица для хранения информации о сотрудниках'

    Оператор IS указывает на объект команды. В данном случае — на текст, который будет являться комментарием к таблице.

    Data Manipulation Language (DML) — язык манипулирования данными

    Используется для работы с данными внутри таблиц. К DML относятся операторы INSERT, UPDATE и DELETE. Сюда можно также отнести SELECT и FROM, но они являются частью DQL.

    INSERT

    Добавляет новые строки данных в таблицу.

    INSERT INTO table_name (column1, column2, ...)
    VALUES (value1, value2, ...);

    где:
    INTO указывает место, куда нужно поместить данные;

    VALUES указывает значения, которые будут вставлены в соответствующие столбцы таблицы.

    UPDATE

    Обновляет существующие строки данных в таблице.

    UPDATE table_name
    SET column1 = value1, column2 = value2, ...
    WHERE condition;

    где:

    SET — оператор для присвоения значения переменной (в данном случае столбцам).

    DELETE

    Удаляет строки данных из таблицы.

    DELETE FROM table_nameWHERE condition;

    Data Control Language (DCL) — язык управления данными

    Используется для управления правами доступа к данным и контролем над БД.

    GRANT

    Предоставляет пользователю или роли определенные привилегии на объект БД.

    Роль можно создать с помощью команды CREATE ROLE role_name. Вместо того чтобы назначать привилегии отдельным пользователям, их можно назначать ролям.

    GRANT SELECT, INSERT
    ON employees
    TO user1;

    Пользователь user1 получает привилегии SELECT и INSERT на таблицу employees.

    REVOKE

    Отменяет определенные привилегии у пользователя или роли на объект БД.

    REVOKE SELECT, INSERT
    ON employees
    FROM user1;

    У пользователя user1 отзываются привилегии SELECT и INSERT на таблицу employees.

    Transaction Control Language (TCL) — язык управления транзакциями

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

    COMMIT

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

    ROLLBACK

    Отменяет все изменения, сделанные в рамках текущей транзакции, и возвращает БД в состояние, в котором она была до начала транзакции.

    SAVEPOINT

    Создает точку сохранения внутри транзакции, к которой можно откатиться без отката всей транзакции. 

    RELEASE SAVEPOINT

    Удаляет ранее созданную точку сохранения. После удаления точки сохранения к ней больше нельзя откатиться.

    SET TRANSACTION

    Устанавливает характеристики транзакции.

    Здесь устанавливается уровень изоляции (ISOLATION LEVEL) самого высокого уровня — SERIALIZABLE. Уровни изоляции влияют на возможность других транзакций вносить изменения в те же данные.

    Внешние и внутренние запросы

    Внешние (основные) и внутренние запросы (подзапросы) позволяют выполнять один запрос внутри другого. Подзапрос выполняется первым, а его результат используется основным запросом.

    SELECT name
    FROM employees
    WHERE department_id = (
        SELECT id
        FROM departments
        WHERE name = 'Sales'
    );

    Внутренний запрос (подзапрос)

    SELECT id
    FROM departments
    WHERE name = 'Sales';

    Этот запрос выполняется первым. Он находит id отдела, где name равно Sales. Допустим, результатом будет id = 3.

    Внешний запрос

    SELECT name
    FROM employees
    WHERE department_id = 3;

    Внешний запрос использует результат подзапроса (id = 3) для фильтрации данных в таблице employees.

    Он выбирает имена сотрудников, у которых department_id = 3.

    Работа с внешними и внутренними запросами с использованием оператора EXISTS

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

    SELECT name
    FROM customers
    WHERE EXISTS (
        SELECT * 
        FROM orders 
        WHERE orders.customer_id = customers.id
    );

    Внешний запрос выбирает имена клиентов из таблицы customers.

    Подзапрос проверяет, существует ли хотя бы один заказ для каждого клиента в таблице orders, используя условие orders.customer_id = customers.id.

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

    Примеры использования команд SQL

    Создание и удаление БД

    CREATE DATABASE my_database;
    SHOW DATABASES;
    USE my_database;
    DROP DATABASE my_database;

    Создание и управление таблицами

    Создаем таблицу employees:

    CREATE TABLE employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50),
        age INT,
        department VARCHAR(50)
    );

    Добавляем столбец email:

    ALTER TABLE employees ADD COLUMN email VARCHAR(100);

    Изменяем тип столбца (MODIFY COLUMN) на INT:

    ALTER TABLE employees MODIFY COLUMN age INT UNSIGNED;

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

    Ограничение целостности

    Операции ограничения целостности применяются для обеспечения точности и надежности данных в таблице.

    Создаем структуру таблицы для хранения информации о заказах в БД.

    CREATE TABLE orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        product_id INT,
        quantity INT,
        FOREIGN KEY (product_id) REFERENCES products(id),
        CHECK (quantity > 0)
    );
    
    id INT AUTO_INCREMENT PRIMARY KEY

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

    product_id INT

    Создает столбец product_id типа INT, который будет содержать идентификатор продукта, связанного с данным заказом.

    quantity INT

    Создает столбец quantity типа INT, который будет содержать количество продуктов в заказе.

    FOREIGN KEY (product_id) REFERENCES products(id)

    Устанавливает ограничение внешнего ключа (FOREIGN KEY) на столбец product_id, который ссылается на столбец id в таблице products. Это гарантирует целостность данных: значение product_id в таблице orders будет соответствовать существующему id в таблице products.

    CHECK (quantity > 0)

    Устанавливает условие проверки (CHECK), которое гарантирует, что значение в столбце quantity всегда будет больше нуля. Это запретит добавление записей с некорректными значениями количества продуктов.

    Разбор

    Поделиться

    Скопировано
    0 комментариев
    Комментарии