SQL Учебник
SQL — это стандартный язык для хранения, обработки и получения данных в базах данных.
Наш учебник по SQL научит вас использовать SQL в: MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres и других системах баз данных.
Примеры в каждом разделе
С помощью нашего онлайн-редактора SQL вы можете редактировать SQL инструкции и нажимать кнопку, чтобы просмотреть результат.
Пример
Кликните на кнопку "Попробуйте сами", чтобы увидеть, как она работает.
SQL Упражнения
SQL Примеры
Учитесь на примерах! Этот учебник дополняет все объяснения поясняющими примерами.
SQL Проверочная Викторина
Проверьте ваши SQL навыки на W3Schools!
SQL Справочники
На W3Schools вы найдете полный справочник по ключевым словам и функциям:
SQL Типы данных
Типы и диапазоны данных для Microsoft Access, MySQL и SQL Server.
Пройдите SQL Экзамен — получите свой Диплом!
Онлайн Сертификация W3Schools
Идеальное решение для профессионалов, которым необходимо сбалансировать работу, семью и карьеру.
Больше 25 000 сертификатов уже выдано!
ПАЛИТРА ЦВЕТОВ
ПРИСОЕДИНЯЙТЕСЬ!
Связь с админом
Если вы хотите сообщить об ошибке, а также внести предложение о работе сайта, добавить объявление или рекламу на сайт, не стесняйтесь отправить админу электронное письмо на email:
Топ Учебники
Топ Справочники
Топ Примеры
Веб Сертификаты
Этот сайт оптимизирован для обучения и тестирования. Примеры могут быть упрощены для улучшения чтения и базового понимания. Учебные пособия, ссылки и примеры постоянно пересматриваются, чтобы избежать ошибок, но мы не можем гарантировать полную правильность и работоспособность всего контента. Используя этот сайт, вы соглашаетесь с тем, что прочитали и приняли условия использования, cookie и политику конфиденциальности.
Также вы можете абсолютно бесплатно скачать офлайн версию сайта W3Schools на русском архивом с GitHub и пользоваться локально на своём компьютере.
Также доступна версия сайта W3Schools на украинском языке.
Copyright 1999-2023 by Refsnes Data. All Rights Reserved.
Сайт работает на фреймворке W3.CSS.
Уроки SQL
Язык структурированных запросов (сокр. «SQL» от англ. «Structured Query Language») — это стандартный язык запросов, используемый для работы с реляционными базами данных. SQL используется для создания и удаления баз данных, создания и удаления таблиц в базе данных, чтения, вставки, обновления и удаления данных из таблиц, а также во многих других операций с базами данных.
Зачем изучать SQL?
SQL используется для «коммуникации» между реляционными базами данных. Он используется в таких СУБД, как MySQL, PostgreSQL, Oracle, SQL Server и многих других. Знание SQL обычно требуется во многих вакансиях на должность программиста, бизнес-аналитика, специалиста по Big Data и др.
В этом туториале мы пройдемся по основным аспектам SQL, а также рассмотрим различия команд в MySQL, Oracle, SQL Server, PostgreSQL и других широко используемых СУБД.
Памятка/шпаргалка по SQL
Изучение настоящей шпаргалки не сделает вас мастером SQL, но позволит получить общее представление об этом языке программирования и возможностях, которые он предоставляет. Рассматриваемые в шпаргалке возможности являются общими для всех или большинства диалектов SQL.
Для более полного погружения в SQL рекомендую изучить эти руководства по MySQL и PostgreSQL от Метанита. Они хороши тем, что просты в изучении и позволяют быстро начать работу с названными СУБД.
При обнаружении ошибок, опечаток и неточностей, не стесняйтесь писать мне в личку.
Содержание
Что такое SQL?
SQL — это язык структурированных запросов (Structured Query Language), позволяющий хранить, манипулировать и извлекать данные из реляционных баз данных (далее — РБД, БД).
Почему SQL?
- получать доступ к данным в системах управления РБД
- описывать данные (их структуру)
- определять данные в БД и управлять ими
- взаимодействовать с другими языками через модули SQL, библиотеки и предваритальные компиляторы
- создавать и удалять БД и таблицы
- создавать представления, хранимые процедуры (stored procedures) и функции в БД
- устанавливать разрешения на доступ к таблицам, процедурам и представлениям
Процесс SQL
При выполнении любой SQL-команды в любой RDBMS (Relational Database Management System — система управления РБД, СУБД, например, PostgreSQL, MySQL, MSSQL, SQLite и др.) система определяет наилучший способ выполнения запроса, а движок SQL определяет способ интерпретации задачи.
В данном процессе участвует несколького компонентов:
- диспетчер запросов (Query Dispatcher)
- движок оптимизации (Optimization Engines)
- классический движок запросов (Classic Query Engine)
- движок запросов SQL (SQL Query Engine) и т.д.
Классический движок обрабатывает все не-SQL-запросы, а движок SQL-запросов не обрабатывает логические файлы.
Команды SQL
Стандартными командами для взаимодействия с РБД являются CREATE , SELECT , INSERT , UPDATE , DELETE и DROP . Эти команды могут быть классифицированы следующим образом:
- DDL — язык определения данных (Data Definition Language)
| N | Команда | Описание |
|---|---|---|
| 1 | CREATE | Создает новую таблицу, представление таблицы или другой объект в БД |
| 2 | ALTER | Модифицирует существующий в БД объект, такой как таблица |
| 3 | DROP | Удаляет существующую таблицу, представление таблицы или другой объект в БД |
- DML — язык изменения данных (Data Manipulation Language)
| N | Команда | Описание |
|---|---|---|
| 1 | SELECT | Извлекает записи из одной или нескольких таблиц |
| 2 | INSERT | Создает записи |
| 3 | UPDATE | Модифицирует записи |
| 4 | DELETE | Удаляет записи |
- DCL — язык управления данными (Data Control Language)
| N | Команда | Описание |
|---|---|---|
| 1 | GRANT | Наделяет пользователя правами |
| 1 | REVOKE | Отменяет права пользователя |
Обратите внимание: использование верхнего регистра в названиях команд SQL — это всего лишь соглашение, большинство СУБД нечувствительны к регистру. Тем не менее, форма записи инструкций, когда названия команд пишутся большими буквами, а названия таблиц, колонок и др. — маленькими, позволяет быстро определять назначение производимой с данными операции.
Что такое таблица?
Данные в СУБД хранятся в объектах БД, называемых таблицами (tables). Таблица, как правило, представляет собой коллекцию связанных между собой данных и состоит из определенного количества колонок и строк.
Таблица — это самая распространенная и простая форма хранения данных в РБД. Вот пример таблицы с пользователями (users):
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 25 | Moscow | active |
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 3 | Elena | 27 | Ekaterinburg | active |
| 4 | Oleg | 28 | Moscow | inactive |
Что такое поле?
Каждая таблица состоит из небольших частей — полей (fields). Полями в таблице users являются userId, userName, age, city и status. Поле — это колонка таблицы, предназначенная для хранения определенной информации о каждой записи в таблице.
Обратите внимание: вместо userId и userName можно было бы использовать id и name , соответственно. Но при работе с несколькими объектами, содержащими свойство id , бывает сложно понять, какому объекту принадлежит идентификатор, особенно, если вы, как и я, часто прибегаете к деструктуризации. Что касается слова name , то оно часто оказывается зарезервизованным, т.е. уже используется в среде, в которой выполняется код, поэтому я стараюсь его не использовать.
Что такое запись или строка?
Запись или строка (record/row) — это любое единичное вхождение (entry), существующее в таблице. В таблице users 5 записей. Проще говоря, запись — это горизонтальное вхождение в таблице.
Что такое колонка?
Колонка (column) — это вертикальное вхождение в таблице, содержащее всю информацию, связанную с определенным полем. В таблице users одной из колонок является city , которая содержит названия городов, в которых проживают пользователи.
Что такое нулевое значение?
Нулевое значение (NULL) — это значение поля, которое является пустым, т.е. нулевое значение — это значение поля, не имеющего значения. Важно понимать, что нулевое значение отличается от значения 0 и от значения поля, содержащего пробелы ( `). Поле с нулевым значением — это такое поля, которое осталось пустым при создании записи. Также, следует учитывать, что в некоторых СУБД пустая строка ( » ) — это NULL`, а в некоторых — это разные значения.
Ограничения
Ограничения (constraints) — это правила, применяемые к данным. Они используются для ограничения данных, которые могут быть записаны в таблицу. Это обеспечивает точность и достоверность данных в БД.
Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.
Среди наиболее распространенных ограничений можно назвать следующие:
- NOT NULL — колонка не может иметь нулевое значение
- DEFAULT — значение колонки по умолчанию
- UNIQUE — все значения колонки должны быть уникальными
- PRIMARY KEY — первичный или основной ключ, уникальный идентификатор записи в текущей таблице
- FOREIGN KEY — внешний ключ, уникальный идентификатор записи в другой таблице (таблице, связанной с текущей)
- CHECK — все значения в колонке должны удовлетворять определенному условию
- INDEX — быстрая запись и извлечение данных
Любое ограничение может быть удалено с помощью команды ALTER TABLE и DROP CONSTRAINT + название ограничения. Некоторые реализации предоставляют сокращения для удаления ограничений и возможность отключать ограничения вместо их удаления.
Целостность данных
В каждой СУБД существуют следующие категории целостности данных:
- целостность объекта (Entity Integrity) — в таблице не должно быть дубликатов (двух и более строк с одинаковыми значениями)
- целостность домена (Domain Integrity) — фильтрация значений по типу, формату или диапазону
- целостность ссылок (Referential integrity) — строки, используемые другими записями (строки, на которые в других записях имеются ссылки), не могут быть удалены
- целостность, определенная пользователем (User-Defined Integrity) — дополнительные правила
Нормализация БД
Нормализация — это процесс эффективной организации данных в БД. Существует две главных причины, обуславливающих необходимость нормализации:
- предотвращение записи в БД лишних данных, например, хранения одинаковых данных в разных таблицах
- обеспечение «оправданной» связи между данными
Нормализация предполагает соблюдение нескольких форм. Форма — это формат структурирования БД. Существует три главных формы: первая, вторая и, соответственно, третья. Я не буду вдаваться в подробности об этих формах, при желании, вы без труда найдете необходимую информацию.
Синтаксис SQL
Синтаксис — это уникальный набор правил и рекомендаций. Все инструкции SQL должны начинаться с ключевого слова, такого как SELECT , INSERT , UPDATE , DELETE , ALTER , DROP , CREATE , USE , SHOW и т.п. и заканчиваться точкой с запятой ( ; ) (точка с запятой не входит в синтаксис SQL , но ее наличия, как правило, требуют консольные клиенты СУБД для обозначения окончания ввода команды). SQL не чувствителен к регистру, т.е. SELECT , select и SeLeCt являются идентичными инструкицями. Исключением из этого правила является MySQL , где учитывается регистр в названии таблицы.
Примеры синтаксиса
Типы данных
Каждая колонка, переменная и выражение в SQL имеют определенный тип данных (data type). Основные категории типов данных:
Точные числовые
| Тип данных | От | До |
|---|---|---|
| bigint | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 |
| int | -2,147,483,648 | 2,147,483,647 |
| smallint | -32,768 | 32,767 |
| tinyint | 0 | 255 |
| bit | 0 | 1 |
| decimal | -10^38 +1 | 10^38 -1 |
| numeric | -10^38 +1 | 10^38 -1 |
| money | -922,337,203,685,477.5808 | +922,337,203,685,477.5807 |
| smallmoney | -214,748.3648 | +214,748.3647 |
Приблизительные числовые
| Тип данных | От | До |
|---|---|---|
| float | -1.79E + 308 | 1.79E + 308 |
| real | -3.40E + 38 | 3.40E + 38 |
Дата и время
| Тип данных | От | До |
|---|---|---|
| datetime | Jan 1, 1753 | Dec 31, 9999 |
| smalldatetime | Jan 1, 1900 | Jun 6, 2079 |
| date | Дата сохраняется в виде June 30, 1991 | |
| time | Время сохраняется в виде 12:30 P.M. |
Строковые символьные
| N | Тип данных | Описание |
|---|---|---|
| 1 | char | Строка длиной до 8,000 символов (не-юникод символы, фиксированной длины) |
| 2 | varchar | Строка длиной до 8,000 символов (не-юникод символы, переменной длины) |
| 3 | text | Не-юникод данные переменной длины, длиной до 2,147,483,647 символов |
Строковые символьные (юникод)
| N | Тип данных | Описание |
|---|---|---|
| 1 | nchar | Строка длиной до 4,000 символов (юникод символы, фиксированной длины) |
| 2 | nvarchar | Строка длиной до 4,000 символов (юникод символы, переменной длины) |
| 3 | ntext | Юникод данные переменной длины, длиной до 1,073,741,823 символов |
Бинарные
| N | Тип данных | Описание |
|---|---|---|
| 1 | binary | Данные размером до 8,000 байт (фиксированной длины) |
| 2 | varbinary | Данные размером до 8,000 байт (переменной длины) |
| 3 | image | Данные размером до 2,147,483,647 байт (переменной длины) |
Смешанные
| N | Тип данных | Описание |
|---|---|---|
| 1 | timestamp | Уникальные числа, обновляющиеся при каждом изменении строки |
| 2 | uniqueidentifier | Глобально-уникальный идентификатор (GUID) |
| 3 | cursor | Объект курсора |
| 4 | table | Промежуточный результат, предназначенный для дальнейшей обработки |
Операторы
Оператор (operators) — это ключевое слово или символ, которые, в основном, используются в инструкциях WHERE для выполнения каких-либо операций. Они используются как для определения условий, так и для объединения нескольких условий в инструкции.
В дальнейших примерах мы будем исходить из предположения, что переменная a имеет значение 10 , а b — 20 .
Арифметические
| Оператор | Описание | Пример |
|---|---|---|
| + (сложение) | Сложение значений | a + b = 30 |
| — (вычитание) | Вычитание правого операнда из левого | b — a = 10 |
| * (умножение) | Умножение значений | a * b = 200 |
| / (деление) | Деление левого операнда на правый | b / a = 2 |
| % (деление с остатком/по модулю) | Деление левого операнда на правый с остатком (возвращается остаток) | b % a = 0 |
Операторы сравнения
| Оператор | Описание | Пример |
|---|---|---|
| = | Определяет равенство значений | a = b -> false |
| != | Определяет НЕравенство значений | a != b -> true |
| <> | Определяет НЕравенство значений | a <> b -> true |
| > | Значение левого операнда больше значения правого операнда? | a > b -> false |
| < | Значение левого операнда меньше значения правого операнда? | a < b -> true |
| >= | Значение левого операнда больше или равно значению правого операнда? | a >= b -> false |
| <= | Значение левого операнда меньше или равно значению правого операнда? | a <= b -> true |
| !< | Значение левого операнда НЕ меньше значения правого операнда? | a !< b -> false |
| !> | Значение левого операнда НЕ больше значения правого операнда? | a !> b -> true |
Логические операторы
| N | Оператор | Описание |
|---|---|---|
| 1 | ALL | Сравнивает все значения |
| 2 | AND | Объединяет условия (все условия должны совпадать) |
| 3 | ANY | Сравнивает одно значение с другим, если последнее совпадает с условием |
| 4 | BETWEEN | Проверяет вхождение значения в диапазон от минимального до максимального |
| 5 | EXISTS | Определяет наличие строки, соответствующей определенному критерию |
| 6 | IN | Выполняет поиск значения в списке значений |
| 7 | LIKE | Сравнивает значение с похожими с помощью операторов подстановки |
| 8 | NOT | Инвертирует (меняет на противоположное) смысл других логических операторов, например, NOT EXISTS, NOT IN и т.д. |
| 9 | OR | Комбинирует условия (одно из условий должно совпадать) |
| 10 | IS NULL | Определяет, является ли значение нулевым |
| 11 | UNIQUE | Определяет уникальность строки |
Выражения
Выражение (expression) — это комбинация значений, операторов и функций для оценки (вычисления) значения. Выражения похожи на формулы, написанные на языке запросов. Они могут использоваться для извлечения из БД определенного набора данных.
Базовый синтаксис выражения выглядит так:
Существуют различные типы выражений: логические, числовые и выражения для работы с датами.
Логические
Логические выражения извлекают данные на основе совпадения с единичным значением.
Предположим, что в таблице users имеются следующие записи:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 25 | Moscow | active |
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 3 | Elena | 27 | Ekaterinburg | active |
| 4 | Oleg | 28 | Moscow | inactive |
Выполняем поиск активных пользователей:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 25 | Moscow | active |
| 3 | Elena | 27 | Ekaterinburg | active |
Числовые
Используются для выполнения арифметических операций в запросе.
Простой пример использования числового выражения:
| addition |
|---|
| 15 |
Существует несколько встроенных функций, таких как count() , sum() , avg() , min() , max() и др. для выполнения так называемых агрегирующих вычислений данных таблицы или колонки.
| records |
|---|
| 4 |
- AVG — вычисляет среднее значение
- SUM — вычисляет сумму значений
- MIN — вычисляет наименьшее значение
- MAX — вычисляет наибольшее значение
- COUNT — вычисляет количество записей в таблице
Также существует несколько встроенных функций для работы со строками:
- CONCAT — объединение строк
- LENGTH — возвращает количество символов в строке
- TRIM — удаляет пробелы в начале и конце строки
- SUBSTRING — извлекает подстроку из строки
- REPLACE — заменяет подстроку в строке
- LOWER — переводит символы строки в нижний регистр
- UPPER — переводит символы строки в верхний регистр и т.д.
- ROUND — округляет число
- TRUNCATE — обрезает дробное число до указанного количества знаков после запятой
- CEILING — возвращает наименьшее целое число, которое больше или равно текущему значению
- FLOOR — возвращает наибольшее целое число, которое меньше или равно текущему значению
- POWER — возводит число в указанную степень
- SQRT — возвращает квадратный корень числа
- RAND — генерирует случайное число с плавающей точкой в диапазоне от 0 до 1
Выражения для работы с датами
Эти выражения, как правило, возвращают текущую дату и время.
| Current_Timestamp |
|---|
| 2021-06-20 12:45:00 |
CURRENT_TIMESTAMP — это и выражение, и функция ( CURRENT_TIMESTAMP() ). Другая функция для получения текущей даты и времени — NOW() .
Другие функции для получения текущей даты и времени:
- CURDATE / CURRENT_DATE — возвращает текущую дату
- CURTIME / CURRENT_TIME — возвращает текущее время и т.д.
Функции для разбора даты и времени:
- DAYOFMONTH(date) — возвращает день месяца в виде числа
- DAYOFWEEK(date) — возвращает день недели в виде числа
- DAYOFYEAR(date) — возвращает номер дня в году
- MONTH(date) — возвращает месяц
- YEAR(date) — возвращает год
- LAST_DAY(date) — возвращает последний день месяца в виде даты
- HOUR(time) — возвращает час
- MINUTE(time) — возвращает минуты
- SECOND(time) — возвращает секунды и др.
Функции для манипулирования датами:
- DATE_ADD(date, interval) — выполняет сложение даты и определенного временного интервала
- DATE_SUB(date, interval) — выполняет вычитание из даты определенного временного интервала
- DATEDIFF(date1, date2) — возвращает разницу в днях между двумя датами
- TO_DAYS(date) — возвращает количество дней с 0-го дня года
- TIME_TO_SEC(time) — возвращает количество секунд с полуночи и др.
Для форматирования даты и времени используются функции DATE_FORMAT(date, format) и TIME_FORMAT(date, format) , соответственно.
Создание БД
Для создания БД используется инструкция CREATE DATABASE .
Условие IF NOT EXISTS позволяет избежать получения ошибки при попытке создания БД, которая уже существует.
Название БД должно быть уникальным в пределах СУБД.
Создаем БД testDB :
Получаем список БД:
| Database |
|---|
| information_schema |
| postgres |
| testDB |
Удаление БД
Для удаления БД используется инструкция DROP DATABASE .
Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.
Обратите внимание: при удалении БД уничтожаются все данные, которые в ней хранятся, так что будьте предельно внимательны при использовании данной команды.
Проверяем, что БД удалена:
Для получения списка таблиц используется инструкция SHOW TABLES .
| Database |
|---|
| information_schema |
| postgres |
Выбор БД
При наличии нескольких БД, перед выполнением каких-либо операций, необходимо выбрать БД. Для этого используется инструкция USE .
Предположим, что мы не удаляли testDB . Тогда мы можем выбрать ее так:
Создание таблицы
Создание таблицы предполагает указание названия таблицы и определение колонок таблицы и их типов данных. Для создания таблицы используется инструкция CREATE TABLE .
Для создания таблицы путем копирования другой таблицы используется сочетание CREATE TABLE и SELECT .
Пример создания таблицы users , где первичным ключом являются идентификаторы пользователей, а поля для имени и возраста пользователя не могут быть нулевыми:
Проверяем, что таблица была создана:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| userId | int(11) | NO | PRI | ||
| userName | varchar(20) | NO | |||
| age | int(11) | NO | |||
| city | varchar(20) | NO | |||
| status | varchar(8) | YES | NULL |
Удаление таблицы
Для удаления таблицы используется инструкция DROP TABLE .
Обратите внимание: при удалении таблицы, навсегда удаляются все хранящиеся в ней данные, индексы, триггеры, ограничения и разрешения, так что будьте предельно внимательны при использовании данной команды.
Удаляем таблицу users :
Теперь, если мы попытаемся получить описание users , то получим ошибку:
Добавление колонок
Для добавления в таблицу колонок используется инструкция INSERT INTO .
Названия колонок можно не указывать, однако, в этом случае значения должны перечисляться в правильном порядке.
Во избежание ошибок, рекомендуется всегда перечислять названия колонок.
Предположим, что мы не удаляли таблицу users . Заполним ее пользователями:
В таблицу можно добавлять несколько строк за один раз.
Также, как было отмечено, при добавлении строки названия полей можно опускать:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 25 | Moscow | active |
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 3 | Elena | 27 | Ekaterinburg | active |
| 4 | Oleg | 28 | Moscow | inactive |
Заполнение таблицы с помощью другой таблицы
Выборка полей
Для выборки полей из таблицы используется инструкция SELECT . Она возвращает данные в виде результирующей таблицы (результирующего набора, result-set).
Для выборки всех полей используется такой синтаксис:
Произведем выборку полей userId , userName и age из таблицы users :
| userId | userName | age |
|---|---|---|
| 1 | Igor | 25 |
| 2 | Vika | 26 |
| 3 | Elena | 27 |
| 4 | Oleg | 28 |
Предложение WHERE
Предложение WHERE используется для фильтрации возвращаемых данных. Оно используется совместно с SELECT , UPDATE , DELETE и другими инструкциями.
Условие (condition), которому должны удовлетворять возвращаемые записи, определяется с помощью операторов сравнения или логических операторов типа > , < , = , NOT , LIKE и т.д.
Сделаем выборку полей userId , userName и age активных пользователей:
| userId | userName | age |
|---|---|---|
| 1 | Igor | 25 |
| 3 | Elena | 27 |
Сделаем выборку полей userId , age и city пользователя с именем Vika .
| userId | age | city |
|---|---|---|
| 2 | 26 | Ekaterinburg |
Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки ( » ), а числа, напротив, указываются как есть.
Операторы AND и OR
Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.
AND
Возвращаемые записи должны удовлетворять всем указанным условиям.
Сделаем выборку полей userId , userName и age активных пользователей старше 26 лет:
| userId | userName | AGE |
|---|---|---|
| 3 | Elena | 27 |
OR
Возвращаемые записи должны удовлетворять хотя бы одному условию.
Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:
| userId | userName | age |
|---|---|---|
| 1 | Igor | 25 |
| 2 | Vika | 26 |
Обновление полей
Для обновления полей используется инструкция UPDATE . SET . Эта инструкция, обычно, используется в сочетании с предложением WHERE .
Обновим возраст пользователя с именем Igor :
Если в данном случае опустить WHERE , то будет обновлен возраст всех пользователей.
Удаление записей
Для удаления записей используется инструкция DELETE . Эта инструкция также, как правило, используется в сочетании с предложением WHERE .
Удалим неактивных пользователей:
Если в данном случае опустить WHERE , то из таблицы users будут удалены все записи.
Предложения LIKE и REGEX
LIKE
Предложение LIKE используется для сравнения значений с помощью операторов с подстановочными знаками. Существует два вида таких операторов:
- проценты ( % )
- нижнее подчеркивание ( _ )
% означает 0, 1 или более символов. _ означает точно 1 символ.
| N | Инструкция | Результат |
|---|---|---|
| 1 | WHERE col LIKE ‘foo%’ | Любые значения, начинающиеся с foo |
| 2 | WHERE col LIKE ‘%foo%’ | Любые значения, содержащие foo |
| 3 | WHERE col LIKE ‘_oo%’ | Любые значения, содержащие oo на второй и третьей позициях |
| 4 | WHERE col LIKE ‘f%%’ | Любые значения, начинающиеся с f и состоящие как минимум из 1 символа |
| 5 | WHERE col LIKE ‘%oo’ | Любые значения, оканчивающиеся на oo |
| 6 | WHERE col LIKE ‘_o%o’ | Любые значения, содержащие o на второй позиции и оканчивающиеся на o |
| 7 | WHERE col LIKE ‘f_o’ | Любые значения, содержащие f и o на первой и третьей позициях, соответственно, и состоящие из трех символов |
Сделаем выборку неактивных пользователей:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 4 | Oleg | 28 | Moscow | inactive |
Сделаем выборку пользователей 30 лет и старше:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 30 | Moscow | active |
REGEX
Предложение REGEX позволяет определять регулярное выражение, которому должна соответствовать запись.
В регулярное выражении могут использоваться следующие специальные символы:
- ^ — начало строки
- $ — конец строки
- . — любой символ
- [символы] — любой из указанных в скобках символов
- [начало-конец] — любой символ из диапазона
- | — разделяет шаблоны
Сделаем выборку пользователей с именами Igor и Vika :
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 30 | Moscow | active |
| 2 | Vika | 26 | Ekaterinburg | inactive |
Предложение TOP / LIMIT / ROWNUM
Данные предложения позволяют извлекать указанное количество или процент записей с начала таблицы. Разные СУБД поддерживают разные предложения.
Сделаем выборку первых трех пользователей:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 30 | Moscow | active |
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 3 | Elena | 27 | Ekaterinburg | active |
Параметр offset (смещение) определяет количество пропускаемых записей. Например, так можно извлечь первых двух пользователей, начиная с третьего:
Предложения ORDER BY и GROUP BY
ORDER BY
Предложение ORDER BY используется для сортировки данных по возрастанию ( ASC ) или убыванию ( DESC ). Многие СУБД по умолчанию выполняют сортировку по возрастанию.
Обратите внимание: колонки для сортировки должны быть указаны в списке колонок для выборки.
Сделаем выборку пользователей, отсортировав их по городу и возрасту:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 3 | Elena | 27 | Ekaterinburg | active |
| 1 | Igor | 25 | Moscow | active |
| 4 | Oleg | 28 | Moscow | inactive |
Теперь выполним сортировку по убыванию:
Определим собственный порядок сортировки по убыванию:
GROUP BY
Предложение GROUP BY используется совместно с инструкцией SELECT для группировки записей. Оно указывается после WHERE и перед ORDER BY .
Сгруппируем активных пользователей по городам:
| city | amount |
|---|---|
| Ekaterinburg | 2 |
| Moscow | 2 |
Ключевое слово DISTINCT
Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).
Сделаем выборку городов проживания пользователей:
| city |
|---|
| Ekaterinburg |
| Moscow |
Соединения
Соединения (joins) используются для комбинации записей двух и более таблиц.
Предположим, что кроме users , у нас имеется таблица orders с заказами пользователей следующего содержания:
| orderId | date | userId | amount |
|---|---|---|---|
| 101 | 2021-06-21 00:00:00 | 2 | 3000 |
| 102 | 2021-06-20 00:00:00 | 2 | 1500 |
| 103 | 2021-06-19 00:00:00 | 3 | 2000 |
| 104 | 2021-06-18 00:00:00 | 3 | 1000 |
Сделаем выборку полей userId , userName , age и amount из наших таблиц посредством их соединения:
| userId | userName | age | amount |
|---|---|---|---|
| 2 | Vika | 26 | 3000 |
| 2 | Vika | 26 | 1500 |
| 3 | Elena | 27 | 2000 |
| 3 | Elena | 27 | 1000 |
При соединении таблиц могут использоваться такие операторы, как = , < , > , <> , <= , >= , != , BETWEEN , LIKE и NOT , однако наиболее распространенным является = .
Существуют разные типы объединений:
- INNER JOIN — возвращает записи, имеющиеся в обеих таблицах
- LEFT JOIN — возвращает записи из левой таблицы, даже если такие записи отсутствуют в правой таблице
- RIGHT JOIN — возвращает записи из правой таблицы, даже если такие записи отсутствуют в левой таблице
- FULL JOIN — возвращает все записи объединяемых таблиц
- CROSS JOIN — возвращает все возможные комбинации строк обеих таблиц
- SELF JOIN — используется для объединения таблицы с самой собой
Предложение UNION
Предложение/оператор UNION используется для комбинации результатов двух и более инструкций SELECT . При этом, возвращаются только уникальные записи.
В случае с UNION , каждая инструкция SELECT должна иметь:
- одинаковый набор колонок для выборки
- одинаковое количество выражений
- одинаковые типы данных колонок и
- одинаковый порядок колонок
Однако, они могут быть разной длины.
Объединим наши таблицы users и orders :
| userId | userName | amount | date |
|---|---|---|---|
| 1 | Igor | NULL | NULL |
| 2 | Vika | 3000 | 2021-06-21 00:00:00 |
| 2 | Vika | 1500 | 2021-06-20 00:00:00 |
| 3 | Elena | 2000 | 2021-06-19 00:00:00 |
| 3 | Elena | 1000 | 2021-06-18 00:00:00 |
| 4 | Alex | NULL | NULL |
Предложение UNION ALL
Предложение UNION ALL также используется для объединения результатов двух и более инструкций SELECT . При этом, возвращаются все записи, включая дубликаты.
Существует еще два предложения, похожих на UNION :
- INTERSECT — используется для комбинации результатов двух и более SELECT , но возвращаются только строки из первого SELECT , совпадающие со строками из второго SELECT
- EXCEPT|MINUS — возвращаются только строки из первого SELECT , отсутствующие во втором SELECT
Синонимы
Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. «Временно» означает, что новое название используется только в текущем запросе, в БД название остается прежним.
Синтаксис синонима таблицы:
Синтаксис синонима колонки:
Пример использования синонимов таблиц:
| userId | userName | age | amount |
|---|---|---|---|
| 2 | Vika | 26 | 3000 |
| 2 | Vika | 26 | 1500 |
| 3 | Elena | 27 | 2000 |
| 3 | Elena | 27 | 1000 |
Пример использования синонимов колонок:
| user_id | user_name | user_age |
|---|---|---|
| 1 | Igor | 30 |
| 3 | Elena | 27 |
Индексы
Создание индексов
Индексы — это специальные поисковые таблицы (lookup tables), которые используются движком БД в целях более быстрого извлечения данных. Проще говоря, индекс — это указатель или ссылка на данные в таблице.
Индексы ускоряют работу инструкции SELECT и предложения WHERE , но замедляют работу инструкций UPDATE и INSERT . Индексы могут создаваться и удаляться, не оказывая никакого влияния на данные.
Для создания индекса используется инструкция CREATE INDEX , позволяющая определять название индекса, индексируемые колонки и порядок индексации (по возрастанию или по убыванию).
К индексам можно применять ограничение UNIQUE для того, чтобы обеспечить их уникальность.
Синтаксис создания индекса:
Синтаксис создания индекса для одной колонки:
Синтакис создания уникальных индексов (такие индексы используются не только для повышения производительности, но и для обеспечения согласованности данных):
Синтаксис создания индексов для нескольких колонок (композиционный индекс):
Решение о создании индексов для одной или нескольких колонок следует принимать на основе того, какие колонки будут часто использоваться в запросе WHERE в качестве условия для сортировки строк.
Для ограничений PRIMARY KEY и UNIQUE автоматически создаются неявные индексы.
Удаление индексов
Для удаления индексов используется инструкция DROP INDEX :
Несмотря на то, что индексы предназначены для повышения производительности БД, существуют ситуации, в которых их использования лучше избегать.
К таким ситуациям относится следующее:
- индексы не должны использоваться в маленьких таблицах
- в таблицах, которые часто и в большом объеме обновляются или перезаписываются
- в колонках, которые содержат большое количество нулевых значений
- в колонках, над которыми часто выполняются операции
Обновление таблицы
Команда ALTER TABLE используется для добавления, удаления и модификации колонок существующей таблицы. Также эта команда используется для добавления и удаления ограничений.
Добавляем в таблицу users новую колонку — пол пользователя:
Удаляем эту колонку:
Очистка таблицы
Команда TRUNCATE TABLE используется для очистки таблицы. Ее отличие от DROP TABLE состоит в том, что сохраняется структура таблицы ( DROP TABLE полностью удаляет таблицу и все ее данные).
Очищаем таблицу users :
Проверяем, что users пустая:
Представления
Представление (view) — это не что иное, как инструкция, записанная в БД под определенным названием. Другими словами, представление — это композиция таблицы в форме предварительно определенного запроса.
Представления могут содержать все или только некоторые строки таблицы. Представление может быть создано на основе одной или нескольких таблиц (это зависит от запроса для создания представления).
Представления — это виртутальные таблицы, позволяющие делать следующее:
- структурировать данные способом, который пользователи находят наиболее естественным или интуитивно понятным
- ограничивать доступ к данным таким образом, что пользователь может просматривать и (иногда) модифицировать только то, что ему нужно и ничего более
- объединять данные из нескольких таблиц для формирования отчетов
Создание представления
Для создания представления используется инструкция CREATE VIEW . Как было отмечено, представления могут создаваться на основе одной или нескольких таблиц, и даже на основе другого представления.
Создаем представление для имен и возраста пользователей:
Получаем данные с помощью представления:
| userName | age |
|---|---|
| Igor | 30 |
| Vika | 26 |
| Elena | 27 |
| Oleg | 28 |
WITH CHECK OPTION
WITH CHECK OPTION — это настройка инструкции CREATE VIEW . Она позволяет обеспечить соответствие всех UPDATE и INSERT условию, определенном в представлении.
Если условие не удовлетворяется, выбрасывается исключение.
Обновление представления
Представление может быть обновлено при соблюдении следующих условий:
- SELECT не содержит ключевого слова DISTINCT
- SELECT не содержит агрегирующих функций
- SELECT не содержит функций установки значений
- SELECT не содержит операций установки значений
- SELECT не содержит предложения ORDER BY
- FROM не содержит больше одной таблицы
- WHERE не содержит подзапросы
- запрос не содержит GROUP BY или HAVING
- вычисляемые колонки не обновляются
- все ненулевые колонки из базовой таблицы включены в представление в том же порядке, в каком они указаны в запросе INSERT
Пример обновления возраста пользователя с именем Igor в представлении:
Обратите внимание: обновление строки в представлении приводит к ее обновлению в базовой таблице.
В представление могут добавляться новые строки с помощью команды INSERT . При выполнении этой команды должны соблюдаться те же правила, что и при выполнении команды UPDATE .
С помощью команды DELETE можно удалять строки из представления.
Удаляем из представления пользователя, возраст которого составляет 26 лет:
Обратите внимание: удаление строки в представлении приводит к ее удалению в базовой таблице.
Удаление представления
Для удаления представления используется инструкция DROP VIEW :
Удаляем представление usersView :
HAVING
Предложение HAVING используется для фильтрации результатов группировки. WHERE используется для применения условий к колонкам, а HAVING — к группам, созданным с помощью GROUP BY .
HAVING должно указываться после GROUP BY , но перед ORDER BY (при наличии).
Транзакции
Транзакция — это единица работы или операции, выполняемой над БД. Это последовательность операций, выполняемых в логическом порядке. Эти операции могут запускаться как пользователем, так и какой-либо программой, функционирующей в БД.
Транзакция — это применение одного или более изменения к БД. Например, при создании/обновлении/удалении записи мы выполняем транзакцию. Важно контролировать выполнение таких операций в целях обеспечения согласованности данных и обработки возможных ошибок.
На практике, запросы, как правило, не отправляются в БД по одному, они группируются и выполняются как часть транзакции.
Свойства транзакции
Транзакции имеют 4 стандартных свойства (ACID):
- атомарность (atomicity) — все операции транзакции должны быть успешно завершены. В противном случае, транзакция прерывается, а все изменения отменяются (происходит откат к предыдущему состоянию)
- согласованность (consistency) — состояние должно изменться в полном соответствии с операциями транзакции
- изоляция или автономность (isolation) — транзакции не зависят друг от друга и не оказывают друг на друга никакого влияния
- долговечность (durability) — результат звершенной транзакции должен сохраняться при поломке системы
Управление транзакцией
Для управления транзакцией используются следующие команды:
- BEGIN|START TRANSACTION — запуск транзакции
- COMMIT — сохранение изменений
- ROLLBACK — отмена изменений
- SAVEPOINT — контрольная точка для отмены изменений
- SET TRANSACTION — установка характеристик текущей транзакции
Команды для управления транзакцией могут использоваться только совместно с такими запросами как INSERT , UPDATE и DELETE . Они не могут использоваться во время создания и удаления таблиц, поскольку эти операции автоматически отправляются в БД.
Удаляем пользователя, возраст которого составляет 26 лет, и отправляем изменения в БД:
Удаляем пользователя с именем Oleg и отменяем эту операцию:
Контрольные точки создаются с помощью такого синтаксиса:
Возврат к контрольной точке выполняется так:
Выполняем три запроса на удаление данных из users , создавая контрольные точки перед каждый удалением:
Отменяем два последних удаления, возвращаясь к контрльной точке sp2 , созданной после первого удаления:
Делаем выборку пользователей:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 31 | Moscow | active |
| 3 | Elena | 27 | Ekaterinburg | active |
| 4 | Oleg | 28 | Moscow | inactive |
Как видим, из таблицы был удален только пользователь с возрастом 26 лет.
Для удаление контрольной точки используется команда RELEASE SAVEPOINT . Естественно, после удаления контрольной точки, к ней нельзя будет вернуться с помощью ROLLBACK TO .
Команда SET TRANSACTION используется для инициализации транзакции, т.е. начала ее выполнения. При этом, можно определять некоторые характеристики транзакции. Например, так можно определить уровень доступа транзакции (доступна только для чтения или для записи тоже):
Временные таблицы
Некоторые СУБД поддерживают так называемые временные таблицы (temporary tables). Такие таблицы позволяют хранить и обрабатывать промежуточные результаты с помощью таких же запросов, как и при работе с обычными таблицами.
Временные таблицы могут быть очень полезными при необходимости хранения временных данных. Одной из главных особенностей таких таблиц является то, что они удаляются по завершении текущей сессии. При запуске скрипта временная таблица удаляется после завершения выполнения этого скрипта. При доступе к БД с помощью клиентской программы, такая таблица будет удалена после закрытия этой программы.
Временная таблица создается с помощью инструкции CREATE TEMPORARY TABLE , в остальном синтаксис создания таких таблиц идентичен синтаксису создания обычных таблиц.
Временная таблица удаляется точно также, как и обычная таблица, с помощью инструкции DROP TABLE .
Клонирование таблицы
Может возникнуть ситуация, когда потребуется получить точную копию существующей таблицы, а CREATE TABLE или SELECT окажется недостаточно в силу того, что мы хотим получить не только идентичную структуру, но также индексы, значения по умолчанию и т.д. копируемой таблицы.
В mysql , например, это можно сделать так:
- вызываем команду SHOW CREATE TABLE для получения инструкции, выполненной при создании таблицы, включая индексы и прочее
- меняем название таблицы и выполняем запрос. Получаем точную копию таблицы
- опционально: если требуется содержимое копируемой таблицы, можно также использовать инструкции INSERT INTO или SELECT
Подзапросы
Подзапрос — это внутренний (вложенный) запрос другого запроса, встроенный (вставленный) с помощью WHERE или других инструкций.
Подзапрос используется для получения данных, которые будут использованы основным запросом в качестве условия для фильтрации возвращаемых записей.
Подзапросы могут использоваться в инструкциях SELECT , INSERT , UPDATE и DELETE , а также с операторами = , < , > , >= , <= , IN , BETWEEN и т.д.
Правила использования подзапросов:
- они должны быть обернуты в круглые скобки
- подзапрос должен содержать только одну колонку для выборки, если основной запрос не содержит несколько таких колонок, которые сравниваются в подзапросе
- в подзапросе нельзя использовать команду ORDER BY , это можно сделать в основном запросе. В подзапросе для замены ORDER BY можно использовать GROUP BY
- подзапросы, возвращающие несколько значений, могут использоваться только с операторами, которые работают с наборами значений, такими как IN
- список SELECT не может содержать ссылки на значения, которые оцениваются (вычисляются) как BLOB , ARRAY , CLOB или NCLOB
- подзапрос не может быть сразу передан в функцию для установки значений
- команду BETWEEN нельзя использовать совместно с подзапросом. Тем не менее, в самомподзапросе указанную команду использовать можно
Подзапросы, обычно, используются в инструкции SELECT .
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 30 | Moscow | active |
| 3 | Elena | 27 | Ekaterinburg | active |
Подзапросы могут использоваться в инструкции INSERT . Эта инструкция добавляет в таблицу данные, возвращаемые подзапросом. При этом, данные, возвращаемые подзапросом, могут быть модифицированы любыми способами.
Подзапросы могут использоваться в инструкции UPDATE . При этом, данные из подзапроса могут использоваться для обновления любого количества колонок.
Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.
Последовательности
Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.
Приведенные ниже примеры рассчитаны на mysql .
Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:
Для того, чтобы заново пронумеровать строки с помощью автоматически генерируемых значений (например, при удалении большого количества строк), можно удалить колонку, содержащую такие значения и создать ее заново. Обратите внимание: такая таблица не должна быть частью объединения.
По умолчанию значения, генерируемые с помощью AUTO_INCREMENT , начинаются с 1. Для того, чтобы установить другое начальное значение достаточно указать, например, AUTO_INCREMENT = 100 — в этом случае нумерация строк начнется со 100.
Что такое SQL: как устроен, зачем нужен и как с ним работать
Рассказываем о языке, на котором «говорят» большинство баз данных.
Иллюстрация: Оля Ежак для Skillbox Media
Вся информация, с которой вы сталкиваетесь в интернете, содержится в базах данных. В них же хранятся данные о сотрудниках и клиентах крупных компаний, научных и социологических исследованиях, расписании рейсов ближайшего к вам аэропорта и много о чём ещё.
Работать с этими циклопическими массивами информации вручную было бы долго, муторно и непродуктивно. Поэтому придумали SQL — специальный язык для общения с БД.
Что такое SQL
SQL (Structured Query Language, или язык структурированных запросов) — это декларативный язык программирования (язык запросов), который используют для создания, обработки и хранения данных в реляционных БД.
На чистом SQL нельзя написать программу — он предназначен только для взаимодействия с базами данных: получения, добавления, изменения и удаления информации в них, управления доступом и так далее.
Поэтому перед изучением SQL нужно разобраться, как устроены базы данных.
В каких базах данных используют SQL
Все БД можно поделить на два вида: реляционные и нереляционные. Язык SQL нужен для работы с первыми.
SQL настолько тесно связан с реляционными БД, что все нереляционные БД в противовес стали называть NoSQL. Вот и получилось, что SQL — это язык программирования, а NoSQL — тип баз данных.
Про реляционные БД часто говорят, что это набор двумерных таблиц. Прямо как в Excel: со столбцами, строками и ячейками. Это понятная визуализация, хотя и не совсем точная.
Представим, что мы создаём базу данных для небольшой строительной фирмы. Она проектирует загородные дома и передаёт проекты подрядчикам, которые занимаются самим строительством:
Чем же база данных отличается от таблицы? Тем, что в базе:
- У столбцов и строк нет определённого положения. Нельзя сказать, что столбец status находится до или после столбца num_floors, а имя Анастасии Романиной — до или после имени Дмитрия Пожарова.
- Каждый столбец диктует свой домен, то есть тип данных, к которому могут относиться его значения. Например, в столбцах cost и num_floors могут храниться только числа, а в столбце client — только строки.
- Каждая строка должна быть уникальной и не может повторять какую-то другую строку.
Из-за этих отличий применительно к базам данных используют другую терминологию. Столбец называется атрибутом, строка — записью или кортежем, а сама БД — их отношением друг к другу.
Нормализация в реляционных базах данных
Вернёмся к БД нашей строительной фирмы. Она может казаться удобной, но на самом деле не лишена недостатков.
Возьмём дом, который строится для Марии Медичиной. Сейчас он только проектируется, и мы ещё не выбрали для него подрядчика. Поэтому значение атрибута contractor равно NULL, то есть поле пустое. Но рано или поздно мы выберем подрядчика — например, ООО «Коттеджи». Тогда, кроме имени подрядчика, нам нужно будет заново указать его телефон. Сейчас значение этого атрибута тоже NULL. Пока что сделать это несложно.
В реальной же базе данных о подрядчике будет храниться гораздо больше информации: адрес, почта, ИНН, банковские реквизиты и так далее. Чтобы каждый раз переписывать всю эту информацию, придётся делать много лишних движений — а это не наш метод.
Если подрядчик вдруг сменит номер телефона, во всех старых записях останется устаревшая информация. А таких записей могут быть сотни и тысячи (если наша компания станет совсем успешной). Ровно та же ситуация с данными клиентов. Уследить за таким числом нюансов проблемно, и наша БД рискует превратиться в хранилище фейков.
Чтобы этого не происходило, в реляционных БД используют нормализацию. Это когда одну таблицу разбивают (декомпозируют) на несколько, а каждой записи присваивают уникальный ключ, по которому её можно идентифицировать.
Всего существует шесть нормальных форм. Чем выше номер формы, тем большему количеству правил она должна подчиняться. Приведём базу данных нашей строительной фирмы в соответствие с третьей нормальной формой.
Теперь, если у любого клиента или подрядчика сменится телефон, нужно будет внести изменения всего один раз. Добавлять новые записи теперь тоже будет проще.
Таблицы связывают между собой ключами. Они бывают трёх видов.
Первичный — указывает на запись, к которой он относится. В одном отношении не может быть нескольких записей с одним и тем же первичным ключом, и значение первичного ключа не может быть NULL. Первичным ключом может быть любое уникальное значение. Например, в таблице contractors так можно было бы использовать ИНН, если б он был в нашей базе.
Внешний — содержит ссылку на первичный ключ из другой таблицы и привязывает одну таблицу к другой.
Родительский — это первичный ключ, на который ссылается внешний ключ.
Язык программирования SQL: как управлять базами данных
Ещё одно отличие реляционных БД от обычных таблиц — в них нельзя вносить изменения напрямую. Для этого нужны СУБД, или системы управления базами данных.
СУБД — это посредник, который получает от пользователя команды, что сделать с базой данных, и выполняет их. Эти-то команды и написаны на языке SQL.
SQL — декларативный язык. Это значит, что при написании кода мы говорим, что хотим получить от программы. Логика того, как именно СУБД будет выполнять поставленную задачу, скрыта от нас.
Конечно, если вы хотите сделать свои запросы более быстрыми и эффективными или обезопасить базы данных, знать алгоритмы СУБД полезно. Но даже не разбираясь в этих тонкостях, вы сможете писать на SQL.
Все SQL-команды делятся на четыре вида:
- DDL (Data Definition Language, или язык описания данных). Их используют, чтобы создавать, изменять и удалять целые таблицы.
- DML (Data Manipulation Language, или язык управления данными). Их применяют к содержимому таблиц, чтобы создавать, изменять, удалять атрибуты и записи. Если нужно получить какую-то информацию из базы данных, то пользуются именно DML-операторами.
- DCL (Data Control Language, или язык контроля данных). Они нужны, чтобы выдавать конкретным пользователям доступ к базам данных и отзывать его.
- TCL (Transaction Control Language, или язык контроля транзакций). Позволяет управлять транзакциями. Транзакция — это набор из нескольких команд, которые выполняются поочерёдно. Если одна из команд внутри транзакции не срабатывает, то все уже совершённые действия отменяются. То есть транзакция может быть совершена либо полностью, либо никак.
Где применяют SQL
В индексе TOPDB популярность СУБД определяется по тому, как часто их гуглят. В декабре 2022 года первые пять мест в нём занимают именно реляционные СУБД — вместе они дают больше 70% поисковых запросов.
Рейтинг DB-Engines даёт похожие цифры. В декабре 2022 года доля реляционных СУБД составляет 71,7%.
Без баз данных не будет ни сайтов, ни сетевых приложений, ни крупных информационных систем — нужно же где-то хранить всю информацию. При этом реляционных БД — большинство, а чтобы управлять ими, нужен SQL. Поэтому мало какая вакансия бэкенд-разработчика обходится без требования владеть SQL. По крайней мере, мы такой не нашли.
Но умение работать с базами данных пригодится не только программисту.
Аналитики данных напрямую работают с «сырой» информацией. Чем лучше и свободнее они общаются с БД, тем проще им добывать и обрабатывать нужные данные в нужном виде.
Маркетологам SQL тоже будет полезен для решения аналитических задач.
Тестировщикам понадобится обращаться к БД, потому что это важный компонент любого информационного продукта.
Руководители, менеджеры и бизнес-консультанты благодаря информации из БД смогут лучше понимать, как функционирует их бизнес, и принимать более взвешенные решения.
Как работать с SQL: основные операторы
Запросы в SQL похожи на естественный английский язык и выглядят как полноценные предложения.
Например, если мы захотим в базе данных нашей строительной фирмы получить номер телефона ООО «Коттеджи», нам нужно написать такую команду:
Перевести на русский её можно так: «Выбери значение из столбца tel в таблице contractors, где значение столбца id равно единице». Символ ; означает конец команды.
SQL-инструкции общаются не напрямую с базой данных, а с СУБД. Многие производители СУБД хотели расширить функциональность запросов, поэтому добавляли к языку собственные расширения.
Так у SQL появились несовместимые между собой диалекты. Например, PL/SQL , PL/pgSQL , T-SQL . Но структура запросов и основные «встроенные» команды от диалекта к диалекту неизменны.
Вот список самых распространённых операторов SQL.
CREATE DATABASE — создаёт БД.
DROP DATABASE — удаляет БД.
USE — указывает СУБД, в какой БД работать в дальнейшем.
CREATE TABLE — создаёт новую таблицу внутри БД.
DROP TABLE — удаляет таблицу.
INSERT — добавляет данные в таблицу. Используется вместе с операторами INTO (указывает на таблицу) и VALUES (ему передают значения, которые нужно добавить).
UPDATE — обновляет данные в таблице. UPDATE указывает на саму таблицу, а потом используется оператор SET, после которого и прописываются новые значения для атрибутов. Чтобы указать на конкретную запись, используют оператор WHERE.
DELETE — удаляет данные из таблицы. Используется перед оператором FROM.
SELECT — выбирает данные. Ему передают название атрибута или атрибутов. Если нужно выбрать все атрибуты, то пишут SELECT *. Находится перед оператором FROM.
FROM — указывает на таблицу, к которой обращена команда.
WHERE — указывает на условие или условия, которым должна удовлетворять строка. Пишется после оператора FROM. Необязательный элемент инструкции. Если его не указывать, то команда применяется ко всем записям в таблице.
ORDER BY — сортирует результаты запроса. По умолчанию — в порядке возрастания. Для сортировки по убыванию можно использовать слово DESC.
JOIN — объединяет значения нескольких колонок. Бывает нескольких видов: внутренний (INNER), внешний (OUTER), левый (LEFT) и правый (RIGHT).
Давайте напишем какой-нибудь запрос к базе данных нашей строительной фирмы.
Он означает: выбери все столбцы из таблицы houses, чей status „Не построен“, и отсортируй их по убыванию атрибута cost. СУБД выдаст нам такую таблицу:
Также в SQL существуют агрегатные функции. Они позволяют производить с данными дополнительные операции и указываются вместо атрибутов. Агрегатные функции записываются в формате FUNCTION(ATTRIBUTE).
Вот некоторые из них.
COUNT — считает количество записей в колонке.
SUM — складывает содержимое значений колонки.
MIN — указывает на минимальное значение в колонке.
MAX — указывает на максимальное значение в колонке.
AVG — считает среднее значение в колонке.
ROUND — округляет значение в колонке.
Для работы с инструкциями, которые содержат агрегатные функции, есть специальные операторы.
GROUP BY — группирует выходные значения для колонок, к которым применили агрегатную функцию.
HAVING — работает как WHERE, но может применяться к агрегатным функциям.
Конечно, это далеко не все операторы, функции и ключевые слова, которые есть в SQL. Но уже этот набор даёт широкие возможности для работы с базами данных.