SQL (Structured Query Language) – это специальный язык запросов для работы с реляционными базами данных (например MySQL, PostgreSQL, Oracle, MariaDB). SQL-запросы строятся из набора операторов, которые представляют из себя обычные слова английского языка.
Все приведенные примеры были успешно протестированы в PostgreSQL версии 15.
- Основы SQL
- Индексы
В данном разделе рассматриваются примеры основных операций (созданиe/получениe/изменениe/удалениe) для работы с данными в SQL-таблицах.
CREATE DATABASE store;В базе данных можно определять неограниченное количество таблиц в которых и будут хранится нужные данные.
Заметьте, что каждый SQL-запрос заканчивается точкой с запятой.
На этапе создания таблицы указываются типы данных и определяются различные атрибуты для всех столбцов.
CREATE TABLE clients (
id SERIAL PRIMARY KEY,
firstName VARCHAR(50) NOT NULL,
lastName VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE,
phone VARCHAR(20) UNIQUE,
age SMALLINT NOT NULL,
gender VARCHAR(6) NOT NULL,
isMarried BOOLEAN,
createdAt TIMESTAMP,
updatedAt TIMESTAMP
);Ниже приведен список основных типов для базы данных PostgreSQL.
В других СУБД приведенные типы данных и их описание может немного отличаться. Поэтому при возникновении ошибок обратитесь к документации.
| Тип | Значения | Описание |
|---|---|---|
smallint int2 |
Числа от -32768 до +32767 | Занимает 2 байта. |
integer int4 int |
Числа от -2147483648 до +2147483647 | Занимает 4 байта. |
bigint int8 |
Числа от -9223372036854775808 до +9223372036854775807 | Занимает 8 байт. |
numeric decimal |
Числа с целой часть до 131072 знаков и до 131072 знаков после запятой | Принимает 2 параметра precision (общее количество цифр) и scale (количество цифр после запятой). numeric(5, 3) – 22,725 decimal(10, 1) – 52538,4 |
real float4 |
Числа от 1E-37 до 1E+37 | Занимает 4 байта. |
double precision float8 |
Числа от 1E-307 до 1E+308 | Занимает 8 байт. |
serial |
Автоинкрементирующееся числовые значения от 1 до 2147483647 | Занимает 4 байта. Значение для данного типа подбираются автоматом в зависимости от значений предыдущего элемента. Отлично подходит для уникальных ID. |
smallserial |
Автоинкрементирующееся числовые значения от 1 до 32767 | Занимает 2 байта. |
bigserial |
Автоинкрементирующееся числовые значения от 1 до 9223372036854775807 | Занимает 8 байт. |
| Тип | Значения | Описание |
|---|---|---|
character char |
Строки фиксированной длины | Принимает параметр, который задает количество символов в строке. char(5) – hello |
character varying varchar |
Строки переменной длины | Принимает параметр, который задает максимальное количество символов в строке. varchar(5) – abc, abcd, abcde |
text |
Текст произвольной длины | Подойдет для хранения текста статей, отзывов, описаний. |
| Тип | Значения | Описание |
|---|---|---|
timestamp |
Дата и вермя от 4713 г до н.э до 294276 г н.э. | Занимает 8 байт. |
timestamp with time zone |
Дата и вермя от 4713 г до н.э до 294276 г н.э. включая данные о часовом поясе | Занимает 8 байт. |
date |
Дата от 4713 г. до н.э. до 5874897 г н.э. | Занимает 4 байта. |
time |
Время от 00:00:00 до 24:00:00 | Занимает 8 байт. |
time with time zone |
Время от 00:00:00+1459 до 24:00:00-1459. | Занимает 12 байт. |
| Тип | Значения | Описание |
|---|---|---|
point |
Точка формата (x,y) | Занимает 16 байт. |
line |
Линия в формате {A,B,C} | Занимает 32 байта. |
lseg |
Отрезок в формате ((x1,y1),(x2,y2)) | Занимает 32 байта. |
box |
Прямоугольник в формате ((x1,y1),(x2,y2)) | Занимает 32 байта. |
path |
Набор соединенных точек в формате ((x1,y1),...) | Занимает 16+16n байт. |
polygon |
Многоугольник в формате ((x1,y1),...). | Занимает 40+16n байт. |
circle |
Окружность в формате <(x,y),r> | Занимает 24 байта. |
| Тип | Значения | Описание |
|---|---|---|
boolean |
true / false | Вместо true можно указывать следующие значения: TRUE, 't', 'true', 'y', 'yes', 'on', '1'. Вместо FALSE: FALSE, 'f', 'false', 'n', 'no', 'off', '0'. |
bytea |
Данные в виде бинарных строк | |
json |
JSON в текстовом виде | |
jsonb |
JSON в бинарном формате | |
uuid |
Хранит строки UUID | |
xml |
Данные в формате XML |
Атрибуты позволяют указать дополнительные свойства для столбцов таблицы.
PRIMARY KEY– указывает, что столбец хранит уникальный идентификатор.
CREATE TABLE test (
id SERIAL PRIMARY KEY
);UNIQUE– указывает, что каждый элемент в столбце будет уникальным.
CREATE TABLE emails (
email VARCHAR(50) UNIQUE
);-
NULL– указывает, что значение в столбце может отсутствовать. (По умолчанию, все столбцы, кромеPRIMARY KEY, допускают отсутствие значений, поэтому явно его указывать не нужно.) -
NOT NULL– указывает, что значение в столбце не может быть пустым.
CREATE TABLE users (
firstName VARCHAR(30) NOT NULL,
lastName VARCHAR(30) NOT NULL
);DEFAULT– указывает значение, которое будет присваиваться по умолчанию.
CREATE TABLE messages (
text VARCHAR(200) DEFAULT 'Hello World'
);CHECK– указывает диапазон значений, которые могут храниться в столбце.
CREATE TABLE users (
firstName VARCHAR(50),
age INTEGER NOT NULL CHECK(age > 0 AND age < 100)
);INSERT INTO clients (firstName, lastName, age, gender, isMarried)
VALUES ('Alex', 'Smith', 25, 'male', false);Можно вставлять сразу несколько элементов, перечисляя значения для нового элемента в новых скобках:
INSERT INTO messages (title, body) VALUES
('MSG-1', 'Hello World'),
('MSG-2', 'SQL is awesome'),
('MSG-3', 'Have a nice day!');Помните, что многие операторы можно комбинировать друг с другом.
Получить все элементы таблицы со значениями всех её столбцов:
SELECT * FROM clients;Получить все элементы таблицы со значениями определенных столбцов:
SELECT firstName, lastName, phone FROM clients;Получить первые 20 элементов таблицы:
SELECT * FROM clients LIMIT 20;Получить первые 10 элементов таблицы начиная с позиции 50 (пагинация):
SELECT * FROM clients LIMIT 10 OFFSET 50;Получить все элементы, где столбец gender равен значению "male":
SELECT * FROM clients WHERE gender = 'male';Получить все элементы, где столбец age равен 25 и столбец isMarried равен false:
SELECT * FROM clients WHERE age = 25 AND isMarried = false;Получить все элементы, где столбец firstName равен "Alex" или столбец lastName равен "Smith":
SELECT * FROM clients WHERE firstName = 'Alex' OR lastName = 'Smith';Получить все элементы таблицы, где столбец firstName может иметь одно из перечисленных значений: "John", "Mike", "Kane":
SELECT * FROM clients WHERE firstName IN ('John', 'Mike', 'Kane');Получить все элементы, где значения столбца age находятся в диапазоне от 20 до 30:
SELECT * FROM clients WHERE age BETWEEN 20 AND 30;Получить все элементы, где значения столбца phone не являются пустыми:
SELECT * FROM clients WHERE phone IS NOT NULL;Получить все значения столбца lastName без повторений (то есть, только уникальные значения):
SELECT DISTINCT(lastName) FROM clients;Для поиска данных по шаблонам используются операторы LIKE и NOT LIKE.
В самих шаблонах использются специальные подстановочные знаки:
%– подстановочный знак, который указывает, что на его месте может быть любое кол-во символов._– подстановочный знак, который указывает, что на его месте может быть только один символ.
Получить все элементы таблицы, где значение столбца firstName начинается с символа "A":
SELECT * FROM clients WHERE firstName LIKE 'A%';Получить все элементы таблицы, где значение столбца firstName начинается с одного из перечисленных символов: "A", "B", "C":
SELECT * FROM clients WHERE firstName LIKE '[ABC]%';Получить все элементы таблицы, где 2-ой символ в значении столбца firstName не равен символу "o":
SELECT * FROM clients WHERE firstName NOT LIKE '_o%';Получить все элементы таблицы отсортированные по столбцу firstName в порядке возрастания:
SELECT * FROM clients ORDER BY firstName ASC;Получить все элементы таблицы отсортированные по столбцу age в порядке убывания:
SELECT * FROM clients ORDER BY age DESC;Получить все элементы таблицы отсортированные по столбцу lastName в порядке убывания, а затем по столбцу id в порядке возрастания:
SELECT * FROM clients ORDER BY lastName DESC, id ASC;Изменить значение столбца phone у элемента со значением столбца id равным 42:
UPDATE clients SET phone = '+123987654' WHERE id = 42;Изменить значения столбцов city и age у элементов со значением столбцов gender = "female" и name = "Sophia":
UPDATE clients SET city = 'Paris', age = 33 WHERE gender = 'famale' AND name = "Sophia";Удалить элемент из таблицы, где значение столбца id = 1:
DELETE FROM clients WHERE id = 137;Удалить элементы из таблицы, где значения столбцов city = "Prague" и age = 22:
DELETE FROM clients WHERE city = 'Prague' AND age = 22;SELECT first_name AS name, last_name AS surname FROM clients; name | surname
-------------+----------
Fowler | Ebbutt
Huntley | Giabucci
Michel | Cogman
Bartholomew | Mecco
Donelle | LambinДобавить новый столбец city в таблицу clients:
ALTER TABLE clients ADD COLUMN city VARCHAR(50);Удалить столбец isMarried из таблицы clients:
ALTER TABLE clients DROP COLUMN isMarried;Переименовать столбец firstName в fName в таблицe clients:
ALTER TABLE clients RENAME COLUMN firstName TO fName;Переименовать таблицу clients в users
ALTER TABLE clients RENAME TO users;Агрегатные (aggregate – совокупный) функции используются для обобщения/подсчета данных.
Подсчитать общее количество элементов в таблице:
SELECT COUNT(*) FROM clients;Получить максимальное/минимальное значение столбца age:
SELECT MAX(age) FROM clients;SELECT MIN(age) FROM clients;Подсчитать общую сумму всех элементов столбца age:
SELECT SUM(age) FROM clients;Подсчитать среднее значение элементов столбца age:
SELECT AVG(age) FROM clients;Сгруппировать данные из таблицы clients по столбцу gender и вывести в столбец total общее количество элементов для каждого значения gender:
SELECT gender, COUNT(gender) AS total FROM clients GROUP BY gender;Вместо названия столбца по которому идёт группировка, можно указать его порядковый номер в операторе
SELECT:
SELECT gender, COUNT(gender) AS total FROM clients GROUP BY 1; # Аналогично запросу выше gender | total
-------------+-------
Male | 368
Female | 245Сгруппировать данные из таблицы clients по столбцу gender, а затем по столбцу age, вывести для каждого полученного элемента среднее значение столбца balance и отсортировать всё в порядке возрастания по столбцу age:
SELECT gender, age, AVG(balance) AS avg_money FROM clients GROUP BY gender, age ORDER BY age; gender | age | avg_money
-------------+-----+------------------------
Male | 18 | 31699.250000000000
Female | 18 | 21025.000000000000
Male | 19 | 16963.166666666667
Female | 19 | 25118.400000000000
Male | 20 | 23203.500000000000
Female | 20 | 22956.875000000000
Male | 21 | 19032.400000000000
Female | 21 | 27047.800000000000Данные в таблицах хранятся в неупорядоченном виде (по сути в таком порядке, в котором они туда и попадают). Из-за этого, когда таблица наполняется большим объемом данных, различные операции по выборке, поиску или группировке данных начинают занимать все больше времени. Для решения этой проблемы существуют индексы. Индексы – это структура данных, которая хранит в себе ссылки на строки таблицы. Записи в индексе хранятся в отсортированном виде, что значительно ускоряет поиск.
При создании индекса указывается его название, таблица и столбец для которого создается индекс:
CREATE INDEX first_name_index ON clients (firstName);Название можно опускать, оно будет создано автоматически.
Создание индекса для большой таблицы может занимать много времени. По умолчанию PostgreSQL позволяет параллельно с созданием индекса выполнять чтение (операторы
SELECT) таблицы, но операции записи (INSERT,UPDATEиDELETE) блокируются до окончания построения индекса. Если для вашей системы это не допустипо, можете почитать об неблокирующем построении индексов.
Для столбцов с хранящих уникальные значениями не нужно вручную создавать отдельные индексы — они просто продублируют индексы, созданные автоматически.
Когда индекс уже создан, о его поддержании в актуальном состоянии заботится СУБД. Следует учитывать, что на это будут расходоваться некоторые ресурсы.
Индексы, которые используются в запросах редко или вообще никогда, должны быть удалены, чтобы не тратить ресурсы на их поддержание.
DROP INDEX first_name_index;Добавлять и удалять индексы можно в любое время.
В разных СУБД можно создавать индексы различных типов, но по умолчанию используется так называемое B-дерево. Такой индекс наиболее универсален и подходит для большинства типовых задач. Другие индексы необходимо использовать если Вы понимаете зачем.
- B-Tree - самый универсальный.
- Hash - эффективен только для простых операций с проверками на равенство.
- GiST - позволяет создавать пользовательские типы индексов и обеспечивает эффективное индексирование для сложных структур данных или полнотекстового поиска.
- SP-GiST - продвинутая версия GiST.
- GIN - предназначен для полнотекстового поиска и составных значений.
- BRIN - позволяет эффективно сканировать и запрашивать большие наборы данных из больших отсортированных таблиц.
Чтобы создать индекс определенного типа, необходимо использовать оператор USING:
CREATE INDEX email_index ON clients USING HASH (email);Индексы можно создавать по нескольким столбцам таблицы. Например, в таблице клиентов мы часто делаем поиск в связке полей firstName и lastName. Для оптимизации такого поиска мы можем создать составной индекс:
CREATE INDEX fn_ln_index ON clients (firstName, lastName);Составными могут быть только индексы типов B-Tree, GiST, GIN и BRIN.
Использовать такие индексы нужно без фанатизма. Индексы по более чем трём столбцам вряд ли будут полезными, если только таблица не используется крайне однообразно.
Индекс можно создать для функции или скалярного выражения с одним или несколькими столбцами таблицы.
Например, для таблицы clients столбцы firstName и lastName удобно использовать в связке, чтобы выполнять поиск по полному имени клиента. Для этого часто может использоватся операция конкатинации (объединения) и для которой полезно создать следующий индекс:
CREATE INDEX full_name_index ON clients ((firstName || ' ' || lastName));Если часто приходится искать клиентов с определенным возрастом, то можно создать такой индекс:
CREATE INDEX young_clients_index ON clients (age) WHERE age = 18;