SQL — что это такое простым языком
SQL — простыми словами, это язык программирования структурированных запросов (SQL, Structured Query Language), который используется в качестве эффективного способа сохранения данных, поиска их частей, обновления, извлечения из базы и удаления.
Произносится как «Эскуэль/ЭсКьюЭль», реже «СиКуЭль/СиКьюЭль», но чаще всего можно услышать жаргонное «Сиквэл/Сиквел».
Главный инструмент оптимизации и обслуживания базы данных — вот, для чего нужен SQL, хотя он и не ограничен этими целями. Возможности обработки охватывают команды определения представлений, указания прав доступа, схем отношений (в том числе, их удаления и изменения), взаимодействие с другими языками программирования, проверку целостности, задание начала и завершения транзакций.
Для чего нужен SQL на конкретном примере
Чтобы непрофессионалу понять, что значит SQL для ИТ-отрасли, приведём простой пример.
Представьте таблицу с информацией о студентах: имена, возраст, предмет обучения и так далее. В ней есть определённое количество строк и столбцов. Один из рядов содержит успеваемость студентов.
Как только все данные будут внесены в таблицу, каждая из записей попадает в разные категории (столбцы или «аттрибуты»). Это и есть организованная база данных. Вся организованная внутри неё информация, которой можно управлять, называется Database Schema (схема данных).
Если вы захотите выдать стипендии учащимся, которые получают оценку 90% или выше, то выполняется запрос данных в SQL, что простыми словами значит «попросить базу данных предоставить информацию о студентах, получающих 90% и более баллов».
Команда будет иметь синтаксический вид:
SELECT * FROM Student WHERE Percentage>=90;
Когда количество данных мало (скажем, 10 студентов), то можно всё легко посчитать и написать на клочке бумаге. Но когда объём данных увеличивается до тысяч записей, становится нужен SQL — он помогает управлять огромными данными эффективно, то есть быстро получать расчёты на их основе.
Как используется SQL и в чём его польза?
С 1974 года, когда язык структурированных запросов только появился, он обеспечивает взаимодейтсвие с системами управления базами данных (СУБД) во всём мире.
SQL, как простой и лёгкий в изучении язык из области свободного программного обеспечения, сегодня активно применяется:
Язык универсален и обладает чётко определённой структурой за счёт устоявшихся стандартов. Взаимодействие с базами данных происходит быстро даже в ситуациях, когда объёмы данных велики (Big Data). Кроме того, эффективное управление возможно даже без особых познаний кода.
Области применения и где используется SQL:
SQL DDL
В качестве языка определения данных (DDL) он даёт возможность независимо создавать базу данных, определять её структуру, использовать, а затем cбрасывать по завершению манипуляций.
SQL DML
В качестве языка управления данными (DML) — для поддержки уже существующих баз данных на эффективном с точки зрения трудозатрат и производительности языке ввода, изменения и извлечения данных в отношении базы данных.
SQL DCL
Как язык контроля данных (DCL), когда нужно защитить свою базу данных от повреждения и неправильного использования.
SQL клиент/сервер
Открывают единую систему входа (SSO) с проверкой подлинности пользователя в нескольких веб-приложениях в рамках единого сеанса.
SQL трёхуровневой архитектуры
Гарантирует защиту информационной составляющей от несанкционированного использования и копирования в цифровом виде.
Почти все реляционные базы данных используют SQL. Некоторые из них даже включают аббревиатуру языка в своём названии: Microsoft SQL Server, MySQL, PostgreSQL, Non Stop SQL, SQLite. Но есть и те, кто именуется независимо, как Oracle, DB/2, Ingres. Есть ещё «NoSQL» — это собирательный термин, который относят ко всем нереляционным базам данных без SQL (либо, когда это не единственный язык запросов).
Видеолекция о том, как и где именно используется SQL, а также каким образом работают базы данных в реальных условиях, простым и доступным русским языком:
Обратитесь в компанию ИТ-аутсорсинга для дальнейшей экспертной поддержки и консультации по этой теме и любым другим техническим вопросам.
Что такое sql в программировании простыми словами
SQL — это язык запросов, созданный для того, чтобы получать из базы данных необходимую информацию. Если описать схему работы SQL простыми словами, то специалист формирует запрос и направляет его в базу. Та в свою очередь обрабатывает эту информацию, «понимает», что именно нужно специалисту, и отправляет ответ.
Данные хранятся в виде таблиц, они структурированы и разложены по строкам и столбцам, чтобы ими легче было оперировать. Такой способ хранения информации называют реляционными базами данных (от англ. relation — «отношения»). Название указывает на то, что объекты в такой базе связаны определенными отношениями.
Например, у маркетолога есть база, в которой собрана информация обо всех пиццериях в городе: названия, ассортимент, цены, график работы и прочее. Во время анализа конкурентов он решил выяснить, сколько пиццерий готовят пиццу с ананасами и оформляют доставку после 23:00. Для того чтобы получить такой список из базы, достаточно написать грамотный SQL-запрос.
Для чего нужен SQL
SQL — это не язык программирования, поэтому написать приложение или сайт с его помощью не получится, но при этом внутренняя работа сайта (backend) невозможна без запросов. Поиск информации в Google — это тоже модель использования SQL. Пользователь задает параметры, которые его интересуют, и отправляет запрос на сервер; затем происходит магия и в поисковой выдаче появляются результаты, соответствующие именно этому запросу.
SQL используют разные виды специалистов:
Как работают запросы
Чтобы разобраться, как именно работает магия запроса, давайте представим его путь от пользователя до нужных ему данных:
Пользователь → Клиент → Запрос → Система управления → База данных → Таблица с базами данных
Данные для работы с SQL хранятся в таблицах. Как именно они устроены — разберемся ниже; пока же просто представим их. На пути от пользователя к таблице находится несколько посредников:
Получите все необходимые знания и навыки аналитика данных: от от Google-таблиц до SQL и Power BI.
Скидка 45% по промокоду BLOG.
Что такое база данных в SQL
SQL-запросы обращаются к данным в виде таблиц, то есть к реляционным базам данных. Упрощенный вариант такой базы — это таблицы Excel, в которых информация также упорядочена в столбцы и строки.
Основные понятия реляционной модели:
1. Отношение — это сама таблица, она двумерная и состоит из столбцов и строк.
2. Атрибут — столбец в таблице, который содержит один конкретный параметр: название, тип, дату, стоимость или другую характеристику.
3. Домен — это допустимые значения для каждого атрибута. Например, в столбце «Имя» или «Название» значения должны представлять собой набор буквенных символов, но они не могут начинаться с «ь» или «ъ» и не могут быть записаны числами.
4. Кортеж (строка или запись) — это табличная строка с порядковым номером, в которой содержится информация об одном конкретном объекте.
5. Значение — элемент таблицы, который находится на пересечении столбцов и строк.
6. Ключ — это самый важный столбец в таблице, за счет этих значений и происходит взаимодействие в реляционной базе данных, он связывает таблицы между собой.
Ключи бывают нескольких видов:
Например, для решения задачи — выбрать все пиццерии, которые смогут доставить пиццу с ананасами после 23:00, — кроме основной таблицы с графиками работы понадобятся также таблицы с ассортиментом каждого заведения, а также таблицы с составом каждой пиццы (чтобы понять, есть ли в ней ананасы). Все эти таблицы будут связаны между собой с помощью ключей.
SQL-операторы
Работать с данными помогают операторы — определенные слова или символы, которые используются для выполнения конкретной операции — например, для выбора из множества по конкретному параметру. Если нам нужно из всех видов пиццы отсортировать те, в которых есть пармезан, — нужно использовать оператор SELECT (выбор в соответствии с условием).
Операторы в SQL делятся на несколько групп в соответствии с задачами, которые они решают.
DDL (Data Definition Language) — операторы определения данных. Они работают с объектами, то есть с целыми таблицами. Если базу нужно дополнить таблицей с новыми данными или, наоборот, убрать одну из таблиц с ошибочными данными — используется этот набор операторов.
DML (Data Manipulation Language) — операторы манипуляции данными. Эти операторы уже работают с содержимым таблиц — строками, атрибутами и значениями. С их помощью можно вносить изменения в конкретное значение. Например, заменить поле в колонке «Фамилия» в строке с данными сотрудницы компании посте того, как она вышла замуж. Или удалить строку с данными уволенного сотрудника.
DCL (Data Control Language) — оператор определения доступа к данным. Он определяет, кто из пользователей может отправлять запросы к базе, менять объекты и значения. Например, можно отозвать доступ у сотрудника, перешедшего в другой отдел, а также открыть доступ к базе новому маркетологу или разработчику.
TCL (Transaction Control Language) — язык управления транзакциями. Транзакции — это набор команд, которые выполняются поочередно. Если все команды выполнены, транзакция считается успешной, а если где-то произошла ошибка — транзакция откатывается назад, отменяя все выполненные команды. Наглядный пример такой транзакции — оплата онлайн, когда банк просит сначала ввести сумму и получателя, затем проверить и подтвердить операцию, а после ввести одноразовый код. На каждом из этих этапов оплату можно отменить и транзакция откатится назад.
Виды СУБД
Сами по себе таблицы или база данных не способны выполнять операции, а в СУБД можно создавать новые таблицы, удалять ненужные данные, настраивать ключи и обрабатывать запросы. Основные задачи СУБД:
Существуют разные виды таких систем, которые разрабатывает и техногиганты, вроде Google, Microsoft и Amazon, и более нишевые студии. Разработчики стремятся сделать свой продукт лучше, чтобы их система быстрее и качественнее других обрабатывала данные. Из-за этого появились разные виды языка SQL — так называемые SQL-диалекты. У каждой СУБД диалект имеет что-то общее со всеми, а также свои особенности, которые не будут работать в другой системе.
СУБД могут быть коммерческими или иметь открытый код. Системы управления с открытым кодом можно бесплатно использовать в проектах, а также дополнять их документацию и совершенствовать процесс работы с системой. Коммерческие СУБД имеют платный доступ к полным версиям — как правило, такие используют крупные корпорации.
Как начать работу с SQL
Для начала работы с SQL достаточно разбираться в основах Excel, чтобы понимать принцип работы запросов, а также иметь базовый уровень английского на уровне A1-A2. Эти навыки необходимы, чтобы понимать синтаксис SQL:
Системы для работы с SQL имеют схожую структуру: есть редактор запросов, результат запросов и список таблиц, которые используются для обработки.
Самостоятельно начать изучение SQL можно с просмотра уроков на YouTube и чтения тематических статей в профильных медиа. Для более системного усвоения информации и экономии времени, потраченного на обучение, лучше записаться на курсы к опытным преподавателям, где вы сразу попадете в профессиональное сообщество и будете получать поддержку менторов.
Вам не потребуется специальной подготовки, чтобы начать обучение: вы получите все необходимые знания и навыки для работы.
Памятка/шпаргалка по SQL
Доброго времени суток, друзья!
Изучение настоящей шпаргалки не сделает вас мастером SQL, но позволит получить общее представление об этом языке программирования и возможностях, которые он предоставляет. Рассматриваемые в шпаргалке возможности являются общими для всех или большинства диалектов SQL.
Для более полного погружения в SQL рекомендую изучить эти руководства по MySQL и PostgreSQL от Метанита. Они хороши тем, что просты в изучении и позволяют быстро начать работу с названными СУБД.
При обнаружении ошибок, опечаток и неточностей, не стесняйтесь писать мне в личку.
Содержание
Что такое SQL?
SQL — это язык структурированных запросов (Structured Query Language), позволяющий хранить, манипулировать и извлекать данные из реляционных баз данных (далее — РБД, БД).
Почему SQL?
Процесс SQL
При выполнении любой SQL-команды в любой RDBMS (Relational Database Management System — система управления РБД, СУБД, например, PostgreSQL, MySQL, MSSQL, SQLite и др.) система определяет наилучший способ выполнения запроса, а движок SQL определяет способ интерпретации задачи.
В данном процессе участвует несколького компонентов:
Классический движок обрабатывает все не-SQL-запросы, а движок SQL-запросов не обрабатывает логические файлы.
Команды SQL
| N | Команда | Описание |
|---|---|---|
| 1 | CREATE | Создает новую таблицу, представление таблицы или другой объект в БД |
| 2 | ALTER | Модифицирует существующий в БД объект, такой как таблица |
| 3 | DROP | Удаляет существующую таблицу, представление таблицы или другой объект в БД |
| N | Команда | Описание |
|---|---|---|
| 1 | SELECT | Извлекает записи из одной или нескольких таблиц |
| 2 | INSERT | Создает записи |
| 3 | UPDATE | Модифицирует записи |
| 4 | DELETE | Удаляет записи |
| 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. Поле — это колонка таблицы, предназначенная для хранения определенной информации о каждой записи в таблице.
Что такое запись или строка?
Запись или строка (record/row) — это любое единичное вхождение (entry), существующее в таблице. В таблице users 5 записей. Проще говоря, запись — это горизонтальное вхождение в таблице.
Что такое колонка?
Что такое нулевое значение?
Ограничения
Ограничения (constraints) — это правила, применяемые к данным. Они используются для ограничения данных, которые могут быть записаны в таблицу. Это обеспечивает точность и достоверность данных в БД.
Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.
Среди наиболее распространенных ограничений можно назвать следующие:
Любое ограничение может быть удалено с помощью команды ALTER TABLE и DROP CONSTRAINT + название ограничения. Некоторые реализации предоставляют сокращения для удаления ограничений и возможность отключать ограничения вместо их удаления.
Целостность данных
В каждой СУБД существуют следующие категории целостности данных:
Нормализация БД
Нормализация — это процесс эффективной организации данных в БД. Существует две главных причины, обуславливающих необходимость нормализации:
Нормализация предполагает соблюдение нескольких форм. Форма — это формат структурирования БД. Существует три главных формы: первая, вторая и, соответственно, третья. Я не буду вдаваться в подробности об этих формах, при желании, вы без труда найдете необходимую информацию.
Синтаксис SQL
Примеры синтаксиса
Типы данных
Каждая колонка, переменная и выражение в SQL имеют определенный тип данных (data type). Основные категории типов данных:
Точные числовые
Приблизительные числовые
| Тип данных | От | До |
|---|---|---|
| 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 + b = 30 |
| — (вычитание) | Вычитание правого операнда из левого | b — a = 10 |
| * (умножение) | Умножение значений | a * b = 200 |
| / (деление) | Деление левого операнда на правый | b / a = 2 |
| % (деление с остатком/по модулю) | Деление левого операнда на правый с остатком (возвращается остаток) | b % a = 0 |
Операторы сравнения
Логические операторы
| 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 |
Числовые
Используются для выполнения арифметических операций в запросе.
Простой пример использования числового выражения:
Также существует несколько встроенных функций для работы со строками:
Выражения для работы с датами
Эти выражения, как правило, возвращают текущую дату и время.
Другие функции для получения текущей даты и времени:
Функции для разбора даты и времени:
Функции для манипулирования датами:
Создание БД
Условие IF NOT EXISTS позволяет избежать получения ошибки при попытке создания БД, которая уже существует.
Название БД должно быть уникальным в пределах СУБД.
Получаем список БД:
Удаление БД
Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.
Обратите внимание: при удалении БД уничтожаются все данные, которые в ней хранятся, так что будьте предельно внимательны при использовании данной команды.
Проверяем, что БД удалена:
Выбор БД
Создание таблицы
Проверяем, что таблица была создана:
| 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 |
Удаление таблицы
Обратите внимание: при удалении таблицы, навсегда удаляются все хранящиеся в ней данные, индексы, триггеры, ограничения и разрешения, так что будьте предельно внимательны при использовании данной команды.
Удаляем таблицу 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 |
|---|---|---|
| 1 | Igor | 25 |
| 2 | Vika | 26 |
| 3 | Elena | 27 |
| 4 | Oleg | 28 |
Предложение WHERE
Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки ( » ), а числа, напротив, указываются как есть.
Операторы AND и OR
Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.
Возвращаемые записи должны удовлетворять всем указанным условиям.
Возвращаемые записи должны удовлетворять хотя бы одному условию.
Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:
Обновление полей
Обновим возраст пользователя с именем Igor :
Удаление записей
Удалим неактивных пользователей:
Предложения 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 лет и старше:
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
Сгруппируем активных пользователей по городам:
Ключевое слово DISTINCT
Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).
Сделаем выборку городов проживания пользователей:
Соединения
Соединения (joins) используются для комбинации записей двух и более таблиц.
| 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 |
|---|---|---|---|
| 2 | Vika | 26 | 3000 |
| 2 | Vika | 26 | 1500 |
| 3 | Elena | 27 | 2000 |
| 3 | Elena | 27 | 1000 |
Существуют разные типы объединений:
Предложение UNION
Однако, они могут быть разной длины.
Объединим наши таблицы 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 :
Синонимы
Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. «Временно» означает, что новое название используется только в текущем запросе, в БД название остается прежним.
Синтаксис синонима таблицы:
Синтаксис синонима колонки:
Пример использования синонимов таблиц:
| userId | userName | age | amount |
|---|---|---|---|
| 2 | Vika | 26 | 3000 |
| 2 | Vika | 26 | 1500 |
| 3 | Elena | 27 | 2000 |
| 3 | Elena | 27 | 1000 |
Пример использования синонимов колонок:
Индексы
Создание индексов
Индексы — это специальные поисковые таблицы (lookup tables), которые используются движком БД в целях более быстрого извлечения данных. Проще говоря, индекс — это указатель или ссылка на данные в таблице.
К индексам можно применять ограничение UNIQUE для того, чтобы обеспечить их уникальность.
Синтаксис создания индекса:
Синтаксис создания индекса для одной колонки:
Синтакис создания уникальных индексов (такие индексы используются не только для повышения производительности, но и для обеспечения согласованности данных):
Синтаксис создания индексов для нескольких колонок (композиционный индекс):
Решение о создании индексов для одной или нескольких колонок следует принимать на основе того, какие колонки будут часто использоваться в запросе WHERE в качестве условия для сортировки строк.
Для ограничений PRIMARY KEY и UNIQUE автоматически создаются неявные индексы.
Удаление индексов
Для удаления индексов используется инструкция DROP INDEX :
Несмотря на то, что индексы предназначены для повышения производительности БД, существуют ситуации, в которых их использования лучше избегать.
К таким ситуациям относится следующее:
Обновление таблицы
Команда ALTER TABLE используется для добавления, удаления и модификации колонок существующей таблицы. Также эта команда используется для добавления и удаления ограничений.
Добавляем в таблицу users новую колонку — пол пользователя:
Удаляем эту колонку:
Очистка таблицы
Команда TRUNCATE TABLE используется для очистки таблицы. Ее отличие от DROP TABLE состоит в том, что сохраняется структура таблицы ( DROP TABLE полностью удаляет таблицу и все ее данные).
Очищаем таблицу users :
Проверяем, что users пустая:
Представления
Представление (view) — это не что иное, как инструкция, записанная в БД под определенным названием. Другими словами, представление — это композиция таблицы в форме предварительно определенного запроса.
Представления могут содержать все или только некоторые строки таблицы. Представление может быть создано на основе одной или нескольких таблиц (это зависит от запроса для создания представления).
Представления — это виртутальные таблицы, позволяющие делать следующее:
Создание представления
Создаем представление для имен и возраста пользователей:
Получаем данные с помощью представления:
WITH CHECK OPTION
Если условие не удовлетворяется, выбрасывается исключение.
Обновление представления
Представление может быть обновлено при соблюдении следующих условий:
Пример обновления возраста пользователя с именем Igor в представлении:
Обратите внимание: обновление строки в представлении приводит к ее обновлению в базовой таблице.
С помощью команды DELETE можно удалять строки из представления.
Удаляем из представления пользователя, возраст которого составляет 26 лет:
Обратите внимание: удаление строки в представлении приводит к ее удалению в базовой таблице.
Удаление представления
Для удаления представления используется инструкция DROP VIEW :
Удаляем представление usersView :
HAVING
Транзакции
Транзакция — это единица работы или операции, выполняемой над БД. Это последовательность операций, выполняемых в логическом порядке. Эти операции могут запускаться как пользователем, так и какой-либо программой, функционирующей в БД.
Транзакция — это применение одного или более изменения к БД. Например, при создании/обновлении/удалении записи мы выполняем транзакцию. Важно контролировать выполнение таких операций в целях обеспечения согласованности данных и обработки возможных ошибок.
На практике, запросы, как правило, не отправляются в БД по одному, они группируются и выполняются как часть транзакции.
Свойства транзакции
Транзакции имеют 4 стандартных свойства (ACID):
Управление транзакцией
Для управления транзакцией используются следующие команды:
Удаляем пользователя, возраст которого составляет 26 лет, и отправляем изменения в БД:
Удаляем пользователя с именем Oleg и отменяем эту операцию:
Контрольные точки создаются с помощью такого синтаксиса:
Возврат к контрольной точке выполняется так:
Делаем выборку пользователей:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 31 | Moscow | active |
| 3 | Elena | 27 | Ekaterinburg | active |
| 4 | Oleg | 28 | Moscow | inactive |
Как видим, из таблицы был удален только пользователь с возрастом 26 лет.
Команда SET TRANSACTION используется для инициализации транзакции, т.е. начала ее выполнения. При этом, можно определять некоторые характеристики транзакции. Например, так можно определить уровень доступа транзакции (доступна только для чтения или для записи тоже):
Временные таблицы
Некоторые СУБД поддерживают так называемые временные таблицы (temporary tables). Такие таблицы позволяют хранить и обрабатывать промежуточные результаты с помощью таких же запросов, как и при работе с обычными таблицами.
Временные таблицы могут быть очень полезными при необходимости хранения временных данных. Одной из главных особенностей таких таблиц является то, что они удаляются по завершении текущей сессии. При запуске скрипта временная таблица удаляется после завершения выполнения этого скрипта. При доступе к БД с помощью клиентской программы, такая таблица будет удалена после закрытия этой программы.
Клонирование таблицы
Может возникнуть ситуация, когда потребуется получить точную копию существующей таблицы, а CREATE TABLE или SELECT окажется недостаточно в силу того, что мы хотим получить не только идентичную структуру, но также индексы, значения по умолчанию и т.д. копируемой таблицы.
Подзапросы
Подзапрос — это внутренний (вложенный) запрос другого запроса, встроенный (вставленный) с помощью WHERE или других инструкций.
Подзапрос используется для получения данных, которые будут использованы основным запросом в качестве условия для фильтрации возвращаемых записей.
Правила использования подзапросов:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 30 | Moscow | active |
| 3 | Elena | 27 | Ekaterinburg | active |
Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.
Последовательности
Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.
Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:
Для того, чтобы заново пронумеровать строки с помощью автоматически генерируемых значений (например, при удалении большого количества строк), можно удалить колонку, содержащую такие значения и создать ее заново. Обратите внимание: такая таблица не должна быть частью объединения.