Язык SQL (Structured Query Language)
SQL , или Structured Query Language, язык структурированных запросов — это декларативный язык программирования, который применяется для создания, управления и модификации данных в реляционной базе данных. SQL является важным инструментом для работы с реляционными системами управления базами данных (СУБД, database management systems), например, MySQL, PostgreSQL и другими.
Язык SQL применяется в сферах, где есть необходимость хранения и структурирования данных в цифровом виде. Например, его применяют в финансовых организациях, где нужно безопасно хранить личные данные клиентов, в сфере логистики, где нужно также хранить данные о клиентах или грузах, или даже при проведении научных исследований.
Преимущества SQL:
- так как SQL — это декларативный язык, пользователю достаточно описать, что он хочет сделать, не указывая, как конкретно это сделать;
- SQL позволяет совершать широкий спектр операций — создавать объекты (database objects), менять их структуру, добавлять, модифицировать и удалять данные, управлять транзакциями, настраивать права доступа и так далее;
- SQL поддерживается многими реляционными СУБД, что делает его универсальным инструментом;
- SQL разработан таким образом, что даже при работе с большими объемами данных сохраняется высокая производительность.
SQL-операторы — это один из компонентов языка, применяются они для взаимодействия с базой данных и решения некоторых задач. Операторы можно разделить на четыре группы: DDL, DMl, DCL и TCL. Каждая группа операторов в SQL имеет свое назначение и функции.
Что значит DDL — Data Definition Language
DDL (Data Definition Language) — это группа операторов SQL, с помощью которых можно управлять объектами базы данных (таблицами, представлениями, индексами и так далее), а именно: создавать, модифицировать и удалять объекты. К основным DDL-операторам относятся: CREATE, ALTER, DROP.
Команда CREATE
DDL-оператор CREATE используется для создания новых объектов базы данных: таблиц, представлений, индексов и других. Например, такой запрос можно применить для создания таблицы с данными об учениках:
CREATE TABLE students (
student_id int PRIMARY KEY,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
birth_date date
);
В результате применения CREATE будет создана таблица из четырех столбцов (student_ID, first_name, last_name и birth_date). student_ID — это первичный ключ (PRIMARY KEY), в данном случае использован для того, чтобы ID каждого ученика был уникальным. После имени каждого столбца указывается его тип данных: int — целое число, varchar — символьные данные переменной длины, в скобках указывается максимальная длина; date — дата. NOT NULL означает, что столбец не может содержать null-значения.
Для того чтобы получить информацию о структуре таблицы в базе данных (имена столбцов, типы данных, возможность содержания null-значения и так далее) можно применить DESCRIBE или DESC:
DESCRIBE students;
Команда ALTER
DDL-оператор ALTER позволяет изменять текущие объекты базы данных. Например, добавлять новые столбцы, изменять тип уже существующих, удалять столбцы и так далее.
Добавим в таблицу учеников новый столбец, в котором будут храниться адреса их электронной почты:
ALTER TABLE students
ADD email varchar(100) NOT NULL;
Чтобы изменить тип данных столбца, можно применить следующий запрос:
ALTER TABLE students
ALTER COLUMN last_name nvarchar(100);
Удаление столбца (в данном случае last_name) может выглядеть так:
ALTER TABLE students
DROP COLUMN last_name
Команда DROP
DDL-оператор DROP применяется для удаления объектов (таблицы, индексы и так далее) базы данных. Использовать DROP нужно с осторожностью, так как удаленные данные не подлежат восстановлению. Иногда лучше иметь резервную копию базы данных.
Пример с удалением таблицы, содержащей данные об учениках:
DROP TABLE students
Этот запрос удалит саму таблицу students и все данные, которые она содержит.
Что такое DML (Data Manipulation Language)
DML (Data Manipulation Language) — это группа операторов SQL, которые применяются для работы с данными, которые уже хранятся в базе данных. DML-операторы позволяют извлекать, вставлять, обновлять и удалять данные. Основные операторы: SELECT, INSERT INTO, DELETE, UPDATE.
Команда SELECT
DML-оператор SELECT применяется для извлечения данных из базы данных. Можно выбрать одну, несколько, или сразу все столбцы таблицы.
Для выбора конкретных столбцов (здесь — first_name и last_name) можно использовать следующий запрос:
SELECT first_name, last_name FROM students;
Если же нужно извлечь сразу всю таблицу, то применяется такой запрос:
SELECT * FROM students;
Дополнительно можно применить оператор WHERE — это позволить отфильтровать данные по заданным условиям, например:
SELECT first_name, last_name FROM students WHERE birth_date > '2004-01-01';
Таким образом выбраны будут только те студенты, которые родились после 1 января 2004 года.
Также можно добавить оператор ORDER BY, который отсортирует результаты по заданному столбцу:
SELECT first_name, last_name FROM students ORDER BY last_name ASC;
В результате фамилии будут отсортированы по алфавиту в порядке возрастания.
Команда INSERT INTO
DML-оператор INSERT INTO применяется для добавления новых строк в таблицу.
Для вставки новой записи в таблицу students можно применить следующий запрос:
INSERT INTO students (student_id, first_name, last_name, birth_date)
VALUES (15, 'Ivan', 'Ivanov', '2010-12-05');
Таким образом в таблицу будет добавлен новый ученик, указанные значения (ID, имя, фамилия, дата рождения) будут записаны в соответствующие столбцы.
Можно вставить и сразу несколько записей, например, так:
INSERT INTO students (student_id, first_name, last_name, birth_date)
VALUES (10, 'Ivan', 'Ivanov', '2010-12-05'),
(11, 'Ivan', 'Koshkin', '2010-10-09'),
(12, 'Ivan', 'Andreev', '2010-04-11');
Чтобы импортировать данные из одной таблицы в другую, можно совместить операторы SELECT и INSERT INTO:
INSERT INTO students (student_id, first_name, last_name, birth_date)
SELECT student_id, first_name, last_name, birth_date
FROM students_1
WHERE birth_date > '2004-01-01';
В результате этого запроса из таблицы students _1 извлекаются те строки, где значение в столбце birth_date больше даты 2004-01-01 (то есть ученики родились после 1 января 2004 года). Эти строки импортируются (вставляются) в таблицу students.
Команда DELETE
DML-оператор DELETE применяется в SQL для удаления из таблицы тех записей, которые соответствуют заданному с помощью WHERE условию.
Например, чтобы удалить ученика с ID равным 1, можно применить следующий запрос:
DELETE FROM students WHERE student_id = 1;
Если условие с WHERE отсутствует, то удалены будут все записи данной таблицы.
К оператору DELETE можно добавить JOIN, тогда запрос будет выглядеть так:
DELETE students
FROM students
JOIN students_1 ON students.student_id = students_1.student_id
WHERE students.student_id = 1;
Этот запрос удалит из таблицы students строку, где student_id равно 1, но только если существует соответствующая строка в таблице students_1 с тем же student_id равным 1.
Команда UPDATE
DML-оператор UPDATE в SQL нужен для модификации существующих в таблице данных.
Например, если нужно изменить имя ученика, то можно применить такой запрос:
UPDATE students SET first_name = 'Ivan' WHERE student_id = 1;
В результате имя ученика с ID равным 1 будет изменено на значение Ivan.
Если пропустить оператор WHERE в таком случае, то будут изменены все данные в таблице, поэтому при обновлении данных важно обращать внимание на его наличие.
Допускается использование и других условий, например:
UPDATE students SET first_name = 'Ivan' WHERE student_id > 10;
Тогда все имена студентов, имеющих ID больше 10, изменятся на значение Ivan. Этот пример бессмысленный и нужен только для демонстрации возможности.
Что такое DCL (Data Control Language)
Data Control Language (DCL) — это группа операторов SQL, которые применяются для управления правами доступа к данным в базе данных, а именно: для предоставления и отзыва прав доступа. Основные операторы — GRANT и REVOKE.
Команда GRANT
DCL-оператор GRANT используется для предоставления пользователям определенных прав на объекты базы данных.
Чтобы предоставить пользователю право на выполнение SELECT-команд, можно использовать следующий запрос:
GRANT SELECT ON students TO user_name;
Пользователь с именем user_name получит право на выполнение SELECT-команд в таблице students.
Если нужно, чтобы пользователь мог вносить записи в таблицу, то применяется оператор INSERT:
GRANT INSERT ON students TO user_name;
Для того чтобы дать разрешение на изменение любого столбца, применяется оператор UPDATE:
GRANT UPDATE ON students TO user_name;
Разрешение на изменение определенных столбцов:
GRANT UPDATE (first_name) ON students TO user_name;
Также можно составлять списки привилегий и пользователей, тогда они разделяются запятой:
GRANT SELECT, INSERT ON students TO user_name_1, user_name_2;
Если нужно предоставить все права на какой-то объект, то применяется ALL PRIVILEGES:
GRANT ALL PRIVILEGES ON students TO user_name;
WITH GRANT OPTION позволяет пользователю, которому передали эту привилегию, тоже передавать привилегии:
GRANT ALL PRIVILEGES ON students TO user_name WITH GRANT OPTION;
Команда REVOKE
DCL-оператор REVOKE в SQL нужен, наоборот, для того, чтобы отозвать ранее предоставленные привилегии.
Отозвать право на выполнение оператора SELECT в таблице student можно так:
REVOKE SELECT ON students FROM user_name;
Так же, как в случае с GRANT допускается формирование списков привилегий и пользователей:
REVOKE UPDATE, SELECT ON students FROM user_name;
Если необходимо отозвать все привилегии, применяется следующий запрос:
REVOKE ALL PRIVILEGES ON students TO user_name;
Если отозвать привилегии у данного пользователя, то они будут автоматически отозваны у всех пользователей, которым данный пользователь их предоставлял.
Что такое TCL (Transaction Control Language)
TCL (Transaction Control Language) — это набор операторов SQL, которые применяются для управления транзакциями: их начала, фиксации и отката. Основные команды: BEGIN/COMMIT и ROLLBACK.
Транзакция — это набор операций, который должен быть выполнен как единое целое. Другими словами, транзакции объединяют несколько операций в атомарную операцию.
Команда BEGIN/COMMIT
TCL-операторы BEGIN/COMMIT применяются в SQL для объявления транзакции: ее начала и окончания соответственно. Пример применения BEGIN/COMMIT:
BEGIN;
UPDATE students
SET first_name = 'Ivan'
WHERE student_id = 1;
INSERT INTO students (student_id, first_name, last_name, birth_date)
VALUES (2, 'Andrew', 'Andreev', '2004-01-10');
COMMIT;
В процессе данной транзакции для ученика с ID равным 1 значение столбца first_name будет изменено на Ivan, также в таблицу будет добавлена новая запись.
Любая ошибка внутри транзакции отменяет все изменения, которые были совершены после BEGIN.
Команда ROLLBACK
TCL-оператор ROLLBACK в SQL применяется для отката транзакции: отмены сделанных в рамках транзакции изменений.
Выглядеть применение ROLLBACK может следующим образом:
BEGIN;
UPDATE students
SET first_name = 'Ivan'
WHERE student_id = 1;
INSERT INTO students (student_id, first_name, last_name, birth_date)
VALUES (2, 'Andrew', 'Andreev', '2004-01-10');
ROLLBACK;