Как называется ссылка на результат запроса

0

PL / SQL — ссылка на результаты запроса в вызовах API

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

Я пытаюсь создать действие события в нашей системе ERP, которое будет запускать следующее при выпуске заказа на покупку:

Я могу ввести несколько идентификаторов пользователя в разделе to_user_name, но мне сложно понять, как я могу получить результаты этого запроса в разделе to_user_name?

РЕДАКТИРОВАТЬ: Я все еще пытаюсь это сделать, но не могу заставить его работать:

Я обновился до этого:

Которая компилируется без ошибок. Однако электронные письма пользователям не отправляются.

Компьютерная грамотность с Надеждой

Заполняем пробелы — расширяем горизонты!

Что такое запрос в поисковой системе, классификация и статистика запросов

Стало привычным обращаться с запросами в поисковых системах. Пользователь компьютера или мобильного гаджета вводит в строке поиска свой запрос, и в ответ получает от поисковика запрашиваемую информацию.

Всегда ли в ответ на Ваш запрос приходит та информация, которая требуется?Далеко не всегда. И тогда приходится отдельно тратить силы и время на поиск информации уже среди той, что была предоставлена по первому запросу.

Давайте разберемся, что такое запрос в поисковике и как можно сформулировать свой запрос, чтобы получить наиболее «точный» ответ или ответы.

Что такое запрос в поисковике

Запрос в поисковике – это словосочетание, отдельные слова или комбинация знаков, которые вводит пользователь в строку поиска для того, чтобы узнать нужные данные в интернете.

Поисковая строка, строка поиска, строка запроса в поисковой системе – все это разные названия одного и того же поля для ввода запроса (рис. 1).

Рис. 1 (клик по рис. для увеличения). Поисковая строка Яндекса и Google

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

Зачастую то, что ищет пользователь, можно описать двумя или большим числом слов, так как по одному слову поиск информации может получиться неопределенным и сложным. К тому же, результат поиска по одному-единственному слову, скорее всего, не понравится пользователю.

Для того чтобы повысить скорость и качество нахождения информации, поисковые системы наподобие Яндекс или Google пользуются сложными алгоритмами. Благодаря этим алгоритмам, поисковая система в результате работы выдает пользователю наиболее подходящие ссылки в ответ на введенный им запрос.

К сожалению, в самом начале тех результатов поиска, которые пользователь получает от поисковика, может находиться реклама. Причем, ее иногда очень сложно отличить от полезной информации, которая не является рекламой, но при этом является ответом на запрос пользователей. Наличие рекламы в поисковике – это уже совсем другая история.

Информацию можно искать классическим образом, то есть, путем ввода запроса с помощью клавиатуры. Замечу, что с недавних пор появилась отличная возможность искать ответы, используя собственный голос для ввода запроса – голосовой поиск.

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

Далее рассмотрим, как использование специальных символов, которые иначе называют операторами, помогает пользователю найти необходимую информацию без лишних усилий.

Оператор «!» для поиска слова в заданной форме

Помимо ввода запроса в виде короткого текста, многие поисковые системы имеют дополнительные символы, которые можно использовать для ускорения и облегчения нахождения необходимой информации. Например, в Яндексе в поисковой строке можно использовать оператор точной формы «!». Этот оператор позволяет поисковику различать и правильно обрабатывать запрос в форме, заданной пользователем.

Например, если к слову «напечатал» добавить без пробела восклицательный знак, получится так: «!напечатал». Тогда поисковая система выдаст документы, в которых можно найти слово в заданной форме (рис. 2), а не в виде, скажем «напечатает», «напечатать» и так далее. А без оператора «!» поиск слова «напечатал» будет происходить одновременно с поиском слов «напечатает», «напечатать» и тому подобное:

Рис. 2. Что нашел Яндекс в ответ на запрос «!напечатал»

А если набрать «!напечатать», то поисковик выдаст другие документы:

Рис. 3. Результат поиска Яндексом ответа на запрос «!напечатать»

Оператор «+» для добавления в поиск

«+» служит для поиска документов, в которых обязательно присутствует выделенное слово.

Если вам нужны документы, где необходимо найти нужное слово, ставится «+» непосредственно перед ним. В одном запросе может присутствовать несколько слов со знаком «+».

К примеру, если в поисковик ввести запрос без кавычек «смартфоны +Nokia +Honor», то в ответ будет выдана информация о смартфонах марок Nokia и Honor.

Оператор «-» для исключения из поиска

Если изменить запрос так, что вместо знака «+» будет присутствовать знак «-», то есть, набрать запрос «смартфоны -Nokia -Honor», то в результате поиска получим информацию о смартфонах других марок, среди которых уже не будет смартфонов Nokia и Honor.

Кавычки «» для поиска по цитате

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

Допустим, если набрать строки «сначала я молчать хотела», то поисковик выдаст автора строк и название произведения, где они упоминаются.

Рис. 4. Поиск по цитате

В цитате вместо забытого слова (или забытых слов) можно использовать символ «*». Один оператор «*» соответствует одному пропущенному слову. После «*» требуется пробел.

Если набрать «* дуб * златая цепь», то поисковик по такому запросу найдет полный текст стихотворения:

У лукоморья дуб зелёный;
Златая цепь на дубе том:
И днём и ночью кот учёный
Всё ходит по цепи кругом;

Не имеет значения, в какой форме введены слова запроса, ведь все поисковые системы используют правила морфологии (неважно, какой падеж слова, наклонение глагола и прочие тонкости языка) и производят поиск с учетом всех форм слова.

Классификация запросов

По назначению поисковые запросы можно разделить на:

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

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

Навигационные запросы – чаще всего запрашивают, чтобы найти конкретный адрес сайта, адрес учреждения, организации или иного материала в интернете.

Частотность запросов

В зависимости от частоты использования запросы разделяют на:

• низкочастотные,
• среднечастотные и
• высокочастотные (от нескольких сотен и более).

Запрос можно отнести к низкочастотным, если небольшое количество пользователей (например, не более 100-150 пользователей в месяц) вводили такой запрос в поисковую строку.

Сезонность запросов

Имеются сезонные запросы, которые имеют ярко выраженный спрос и являются высокочастотными в период сезона.

К примеру, запросы дачного характера «как посадить картофель», «как вырастить георгины» популярны среди пользователей ближе к лету (это «высокий сезон» для таких запросов) и практически не используются ими в зимний период («не сезон»).

События и запросы, связанные с ними

Событийные запросы связаны с происходящими «здесь и сейчас» событиями.

К таким можно отнести, например, запросы, связанные с чемпионатом мира по футболу, который проходил в России летом 2018 года.

Статистика запросов в Яндексе

В наше время поисковая система Яндекс дает неограниченный доступ ко всем своим статистическим данным по использованию запросов пользователями рунета. Для этого имеется инструмент Вордстат (wordstat). Но эта статистика предоставляется только зарегистрированным пользователям.

Итак, для получения статических данных о запросах в рунете потребуется наличие аккаунта в Яндексе и переход по ссылке:

В качестве примера приведена статистика в Вордстате по запросу «дед мороз», который в конце декабря имеет хорошо заметный сезонный характер:

Рис. 5 (клик по рис. для увеличения). Статистика по сезонному запросу «дед мороз»

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

Рис. 6 (клик по рис. для увеличения). Выбор регионов для получения статистики запросов в Яндексе в этих регионах

Запросы в Гугл Тренде

Google Trends является разработкой компании Google, которое показывает, как часто конкретный запрос ищут по отношению к общему объему поисковых запросов в различных регионах мира и на различных языках.

Здесь, в отличие от Яндекса, даже не нужна регистрация пользователя, не нужен аккаунт в Гугле. Правда, аккаунт может пригодиться, если потребуется хранить статистику длительное время у себя в аккаунте.

Вот так выглядит главная страница Трендов от Гугла:

Рис. 7. Анализ запросов пользователей в Гугл Тренде

Цифра 1 на рис. 7 – выбор страны. Это означает одновременно и выбор языка, который соответствует выбранной стране.

2 на рис. 7 – ввод запроса.

3 на рис. 7 – меню Гугл Трендов. Если по нему кликнуть, можно выбрать вкладки: анализ, популярные запросы, справка и др.

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

Все ли можно найти в Яндексе или в Гугле

Если на первой странице выдачи информации от Гугла или Яндекса нет искомой информации, не нужно отчаиваться. Нужно набраться терпения и просматривать не только первую страницу в результатах поиска, а изучить хотя бы десять первых страниц (рис. 8), которые поисковик выдает в ответ на запрос. Это связано с пока еще несовершенством алгоритмов в поисковых системах, увы, поэтому далеко не всегда искомая информация оказывается на самой первой странице выдачи. Да и реклама занимает много места на первой странице, занимая частично место полезной информации.

Российский и глобальный мировой поисковик работают без устали круглосуточно, используя мощнейшие серверы. Они перебирают, разбирают и «раскладывают по полочкам» информацию, хранящуюся на просторах Интернета. Данный процесс еще называют «индексированием и ранжированием информации».

Индексирование информации – это ее поиск и занесение в «индекс» (в базу) поисковика. Ранжирование необходимо для того, чтобы поисковик отсортировал все найденные материалы по степени их полезности для пользователя. Найденную и отсортированную таким образом информацию поисковик выдает пользователю по тому или иному запросу.

Серверы Гугла и Яндекса индексируют и ранжируют данные со всех мыслимых и немыслимых сайтов в Интернете всего мира, которые для них открыты.

Но даже такие мощные поисковики не в состоянии «объять необъятное», говоря словами классика. Слишком много данных теперь хранится в Интернете. Словосочетание «слишком много» здесь, пожалуй, даже неуместно. Правильнее, наверное, — это «жуткие немыслимые объемы» или Big Data.

Где искать, кроме Гугла и Яндекса

Для поиска особенной или специфической, специальной, узкоспециализированной и другой подобной информации полезно будет пользоваться кроме Яндекса и Гугла:

• во-первых, другими поисковиками (Bing, Yahoo, Mail.ru и др. – см. рис. 8),
• во-вторых, поиском информации на специальных сайтах, например, на сайтах музеев, энциклопедий, различных справочников и тому подобное.

Рис. 8. Искать можно на других страницах в Яндексе (2, 3, 4 и дальше), либо перейти к поиску с помощью поисковиков Bing, Google, Mail.ru

Также полезным может быть поиск информации на разных языках. Этот вариант, конечно, в первую очередь адресован тем, кто владеет иностранными языками. Хотя, в некоторых случаях и Гугл переводчик здесь может помочь: сначала переводим поисковый запрос на другой язык, например, на английский, затем вводим переведенный запрос в поисковую строку, а полученную в ответ информацию переводим обратно на русский язык опять же с помощью Гугл переводчика.

В общем, кто ищет, тот всегда найдет. И не всегда информация находится с помощью Гугла или Яндекса. Хотя для огромного множества запросов вполне достаточно уметь пользоваться поиском информации с помощью этих двух крупнейших поисковиков.

Видео «Google — Год в поиске 2019»

Полезные ссылки

Язык запросов Яндекса:

Поисковые запросы Google

Прошу Вас принять участие в опросе. Можно указать много поисковых систем, если Вы ими пользуетесь или собираетесь пользоваться:

Как называется ссылка на результат запроса

Что такое запрос в поисковой системе, классификация и статистика запросов

Стало привычным обращаться с запросами в поисковых системах. Пользователь компьютера или мобильного гаджета вводит в строке поиска свой запрос, и в ответ получает от поисковика запрашиваемую информацию.

Всегда ли в ответ на Ваш запрос приходит та информация, которая требуется?Далеко не всегда. И тогда приходится отдельно тратить силы и время на поиск информации уже среди той, что была предоставлена по первому запросу.

Давайте разберемся, что такое запрос в поисковике и как можно сформулировать свой запрос, чтобы получить наиболее «точный» ответ или ответы.

Что такое запрос в поисковике

Запрос в поисковике – это словосочетание, отдельные слова или комбинация знаков, которые вводит пользователь в строку поиска для того, чтобы узнать нужные данные в интернете.

Поисковая строка, строка поиска, строка запроса в поисковой системе – все это разные названия одного и того же поля для ввода запроса (рис. 1).

Рис. 1 (клик по рис. для увеличения). Поисковая строка Яндекса и Google

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

Зачастую то, что ищет пользователь, можно описать двумя или большим числом слов, так как по одному слову поиск информации может получиться неопределенным и сложным. К тому же, результат поиска по одному-единственному слову, скорее всего, не понравится пользователю.

Для того чтобы повысить скорость и качество нахождения информации, поисковые системы наподобие Яндекс или Google пользуются сложными алгоритмами. Благодаря этим алгоритмам, поисковая система в результате работы выдает пользователю наиболее подходящие ссылки в ответ на введенный им запрос.

К сожалению, в самом начале тех результатов поиска, которые пользователь получает от поисковика, может находиться реклама. Причем, ее иногда очень сложно отличить от полезной информации, которая не является рекламой, но при этом является ответом на запрос пользователей. Наличие рекламы в поисковике – это уже совсем другая история.

Информацию можно искать классическим образом, то есть, путем ввода запроса с помощью клавиатуры. Замечу, что с недавних пор появилась отличная возможность искать ответы, используя собственный голос для ввода запроса – голосовой поиск.

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

Далее рассмотрим, как использование специальных символов, которые иначе называют операторами, помогает пользователю найти необходимую информацию без лишних усилий.

Оператор «!» для поиска слова в заданной форме

Помимо ввода запроса в виде короткого текста, многие поисковые системы имеют дополнительные символы, которые можно использовать для ускорения и облегчения нахождения необходимой информации. Например, в Яндексе в поисковой строке можно использовать оператор точной формы «!». Этот оператор позволяет поисковику различать и правильно обрабатывать запрос в форме, заданной пользователем.

Например, если к слову «напечатал» добавить без пробела восклицательный знак, получится так: «!напечатал». Тогда поисковая система выдаст документы, в которых можно найти слово в заданной форме (рис. 2), а не в виде, скажем «напечатает», «напечатать» и так далее. А без оператора «!» поиск слова «напечатал» будет происходить одновременно с поиском слов «напечатает», «напечатать» и тому подобное:

Рис. 2. Что нашел Яндекс в ответ на запрос «!напечатал»

А если набрать «!напечатать», то поисковик выдаст другие документы:

Рис. 3. Результат поиска Яндексом ответа на запрос «!напечатать»

Оператор «+» для добавления в поиск

«+» служит для поиска документов, в которых обязательно присутствует выделенное слово.

Если вам нужны документы, где необходимо найти нужное слово, ставится «+» непосредственно перед ним. В одном запросе может присутствовать несколько слов со знаком «+».

К примеру, если в поисковик ввести запрос без кавычек «смартфоны +Nokia +Honor», то в ответ будет выдана информация о смартфонах марок Nokia и Honor.

Оператор «-» для исключения из поиска

Если изменить запрос так, что вместо знака «+» будет присутствовать знак «-», то есть, набрать запрос «смартфоны -Nokia -Honor», то в результате поиска получим информацию о смартфонах других марок, среди которых уже не будет смартфонов Nokia и Honor.

Кавычки «» для поиска по цитате

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

Допустим, если набрать строки «сначала я молчать хотела», то поисковик выдаст автора строк и название произведения, где они упоминаются.

Рис. 4. Поиск по цитате

В цитате вместо забытого слова (или забытых слов) можно использовать символ «*». Один оператор «*» соответствует одному пропущенному слову. После «*» требуется пробел.

Если набрать «* дуб * златая цепь», то поисковик по такому запросу найдет полный текст стихотворения:

У лукоморья дуб зелёный;
Златая цепь на дубе том:
И днём и ночью кот учёный
Всё ходит по цепи кругом;

Не имеет значения, в какой форме введены слова запроса, ведь все поисковые системы используют правила морфологии (неважно, какой падеж слова, наклонение глагола и прочие тонкости языка) и производят поиск с учетом всех форм слова.

Классификация запросов

По назначению поисковые запросы можно разделить на:

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

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

Навигационные запросы – чаще всего запрашивают, чтобы найти конкретный адрес сайта, адрес учреждения, организации или иного материала в интернете.

Частотность запросов

В зависимости от частоты использования запросы разделяют на:

• низкочастотные,
• среднечастотные и
• высокочастотные (от нескольких сотен и более).

Запрос можно отнести к низкочастотным, если небольшое количество пользователей (например, не более 100-150 пользователей в месяц) вводили такой запрос в поисковую строку.

Сезонность запросов

Имеются сезонные запросы, которые имеют ярко выраженный спрос и являются высокочастотными в период сезона.

К примеру, запросы дачного характера «как посадить картофель», «как вырастить георгины» популярны среди пользователей ближе к лету (это «высокий сезон» для таких запросов) и практически не используются ими в зимний период («не сезон»).

События и запросы, связанные с ними

Событийные запросы связаны с происходящими «здесь и сейчас» событиями.

К таким можно отнести, например, запросы, связанные с чемпионатом мира по футболу, который проходил в России летом 2018 года.

Статистика запросов в Яндексе

В наше время поисковая система Яндекс дает неограниченный доступ ко всем своим статистическим данным по использованию запросов пользователями рунета. Для этого имеется инструмент Вордстат (wordstat). Но эта статистика предоставляется только зарегистрированным пользователям.

Итак, для получения статических данных о запросах в рунете потребуется наличие аккаунта в Яндексе и переход по ссылке:

В качестве примера приведена статистика в Вордстате по запросу «дед мороз», который в конце декабря имеет хорошо заметный сезонный характер:

Рис. 5 (клик по рис. для увеличения). Статистика по сезонному запросу «дед мороз»

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

Рис. 6 (клик по рис. для увеличения). Выбор регионов для получения статистики запросов в Яндексе в этих регионах

Запросы в Гугл Тренде

Google Trends является разработкой компании Google, которое показывает, как часто конкретный запрос ищут по отношению к общему объему поисковых запросов в различных регионах мира и на различных языках.

Здесь, в отличие от Яндекса, даже не нужна регистрация пользователя, не нужен аккаунт в Гугле. Правда, аккаунт может пригодиться, если потребуется хранить статистику длительное время у себя в аккаунте.

Вот так выглядит главная страница Трендов от Гугла:

Рис. 7. Анализ запросов пользователей в Гугл Тренде

Цифра 1 на рис. 7 – выбор страны. Это означает одновременно и выбор языка, который соответствует выбранной стране.

2 на рис. 7 – ввод запроса.

3 на рис. 7 – меню Гугл Трендов. Если по нему кликнуть, можно выбрать вкладки: анализ, популярные запросы, справка и др.

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

Все ли можно найти в Яндексе или в Гугле

Если на первой странице выдачи информации от Гугла или Яндекса нет искомой информации, не нужно отчаиваться. Нужно набраться терпения и просматривать не только первую страницу в результатах поиска, а изучить хотя бы десять первых страниц (рис. 8), которые поисковик выдает в ответ на запрос. Это связано с пока еще несовершенством алгоритмов в поисковых системах, увы, поэтому далеко не всегда искомая информация оказывается на самой первой странице выдачи. Да и реклама занимает много места на первой странице, занимая частично место полезной информации.

Российский и глобальный мировой поисковик работают без устали круглосуточно, используя мощнейшие серверы. Они перебирают, разбирают и «раскладывают по полочкам» информацию, хранящуюся на просторах Интернета. Данный процесс еще называют «индексированием и ранжированием информации».

Индексирование информации – это ее поиск и занесение в «индекс» (в базу) поисковика. Ранжирование необходимо для того, чтобы поисковик отсортировал все найденные материалы по степени их полезности для пользователя. Найденную и отсортированную таким образом информацию поисковик выдает пользователю по тому или иному запросу.

Серверы Гугла и Яндекса индексируют и ранжируют данные со всех мыслимых и немыслимых сайтов в Интернете всего мира, которые для них открыты.

Но даже такие мощные поисковики не в состоянии «объять необъятное», говоря словами классика. Слишком много данных теперь хранится в Интернете. Словосочетание «слишком много» здесь, пожалуй, даже неуместно. Правильнее, наверное, — это «жуткие немыслимые объемы» или Big Data.

Где искать, кроме Гугла и Яндекса

Для поиска особенной или специфической, специальной, узкоспециализированной и другой подобной информации полезно будет пользоваться кроме Яндекса и Гугла:

• во-первых, другими поисковиками (Bing, Yahoo, Mail.ru и др. – см. рис. 8),
• во-вторых, поиском информации на специальных сайтах, например, на сайтах музеев, энциклопедий, различных справочников и тому подобное.

Рис. 8. Искать можно на других страницах в Яндексе (2, 3, 4 и дальше), либо перейти к поиску с помощью поисковиков Bing, Google, Mail.ru

Также полезным может быть поиск информации на разных языках. Этот вариант, конечно, в первую очередь адресован тем, кто владеет иностранными языками. Хотя, в некоторых случаях и Гугл переводчик здесь может помочь: сначала переводим поисковый запрос на другой язык, например, на английский, затем вводим переведенный запрос в поисковую строку, а полученную в ответ информацию переводим обратно на русский язык опять же с помощью Гугл переводчика.

В общем, кто ищет, тот всегда найдет. И не всегда информация находится с помощью Гугла или Яндекса. Хотя для огромного множества запросов вполне достаточно уметь пользоваться поиском информации с помощью этих двух крупнейших поисковиков.

Видео «Google — Год в поиске 2019»

Полезные ссылки

Язык запросов Яндекса:

Поисковые запросы Google

Прошу Вас принять участие в опросе. Можно указать много поисковых систем, если Вы ими пользуетесь или собираетесь пользоваться:

Основные команды SQL, которые должен знать каждый программист

Основные команды SQL не ограничиваются стандартными CREATE , UPDATE и DELETE . Данная статья будет полезна тем, кто хочет освежить свои знания по SQL перед собеседованием на работу.

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

Примечание Вы читаете улучшенную версию некогда выпущенной нами статьи.

Настройка базы данных

Перед началом создайте БД с тестовыми данными. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql. После установите MySQL, откройте терминал и войдите в консоль MySQL с помощью команды:

Затем введите пароль и выполните следующую команду. Назовём базу данных «university»:

SHOW DATABASES

SQL-команда, которая отвечает за просмотр доступных баз данных.

CREATE DATABASE

Команда для создания новой базы данных.

С помощью этой SQL-команды USE <database_name> выбирается база данных, необходимая для дальнейшей работы с ней.

SOURCE

А SOURCE <file.sql> позволит выполнить сразу несколько SQL-команд, содержащихся в файле с расширением .sql.

DROP DATABASE

Стандартная SQL-команда для удаления целой базы данных.

SHOW TABLES

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

CREATE TABLE

SQL-команда для создания новой таблицы:

Ограничения целостности при использовании CREATE TABLE

Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения:

• ячейка таблицы не может иметь значение NULL ;
• первичный ключ — PRIMARY KEY(col_name1, col_name2, …) ;
• внешний ключ — FOREIGN KEY(col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn) .

Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ.

Пример

Создайте таблицу «instructor»:

DESCRIBE

С помощью DESCRIBE <table_name> можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы.

INSERT

Команда INSERT INTO <table_name> в SQL отвечает за добавление данных в таблицу:

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

UPDATE

SQL-команда для обновления данных таблицы:

DELETE

SQL-команда DELETE FROM <table_name> используется для удаления данных из таблицы.

DROP TABLE

А так можно удалить всю таблицу целиком.

SELECT

Далее мы рассмотрим основные команды SQL, которые позволяют работать непосредственно с данными. К одной из таких SQL-команд относится SELECT для получения данных из выбранной таблицы:

Следующей командой можно вывести все данные из таблицы:

SELECT DISTINCT

В столбцах таблицы могут содержаться повторяющиеся данные. Используйте SELECT DISTINCT для получения только неповторяющихся данных.

WHERE

Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе:

В запросе можно задавать следующие условия:

• сравнение текста;
• сравнение численных значений;
• логические операции AND (и), OR (или) и NOT (отрицание).

Пример

Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE :

GROUP BY

Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT , MAX , MIN , SUM и AVG , для группировки выходных значений.

Пример

Выведем количество курсов для каждого факультета:

HAVING

Ключевое слово HAVING было добавлено в SQL по той причине, что WHERE не может использоваться для работы с агрегатными функциями.

Пример

Выведем список факультетов, у которых более одного курса:

ORDER BY

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC .

Пример

Выведем список курсов по возрастанию и убыванию количества кредитов:

BETWEEN

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

Пример

Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000:

Оператор LIKE используется в WHERE , чтобы задать шаблон поиска похожего значения.

Есть два свободных оператора, которые используются в LIKE :

• % (ни одного, один или несколько символов);
• _ (один символ).

Пример

Выведем список курсов, в имени которых содержится «to» , и список курсов, название которых начинается с «CS-» :

С помощью IN можно указать несколько значений для оператора WHERE :

Пример

Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.:

JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением:

Пример

Выведем список всех обязательных курсов и детали о них:

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

Создание

Удаление

Агрегатные функции

Это не совсем основные команды SQL, однако знать их тоже желательно. Агрегатные функции используются для получения совокупного результата, относящегося к рассматриваемым данным:

• COUNT(col_name) — возвращает количество строк;
• SUM(col_name) — возвращает сумму значений в данном столбце;
• AVG(col_name) — возвращает среднее значение данного столбца;
• MIN(col_name) — возвращает наименьшее значение данного столбца;
• MAX(col_name) — возвращает наибольшее значение данного столбца.

Вложенные подзапросы

Вложенные подзапросы — это SQL-запросы, которые включают выражения SELECT , FROM и WHERE , вложенные в другой запрос.

Памятка/шпаргалка по 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)

• DML — язык изменения данных (Data Manipulation Language)

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

Обратите внимание: использование верхнего регистра в названиях команд SQL — это всего лишь соглашение, большинство СУБД нечувствительны к регистру. Тем не менее, форма записи инструкций, когда названия команд пишутся большими буквами, а названия таблиц, колонок и др. — маленькими, позволяет быстро определять назначение производимой с данными операции.

Что такое таблица?

Данные в СУБД хранятся в объектах БД, называемых таблицами (tables). Таблица, как правило, представляет собой коллекцию связанных между собой данных и состоит из определенного количества колонок и строк.

Таблица — это самая распространенная и простая форма хранения данных в РБД. Вот пример таблицы с пользователями (users):

Что такое поле?

Каждая таблица состоит из небольших частей — полей (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). Основные категории типов данных:

Точные числовые

Приблизительные числовые

Дата и время

Строковые символьные

Строковые символьные (юникод)

Бинарные

Смешанные

Операторы

Оператор (operators) — это ключевое слово или символ, которые, в основном, используются в инструкциях WHERE для выполнения каких-либо операций. Они используются как для определения условий, так и для объединения нескольких условий в инструкции.

В дальнейших примерах мы будем исходить из предположения, что переменная a имеет значение 10 , а b — 20 .

Арифметические

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

Логические операторы

Выражения

Выражение (expression) — это комбинация значений, операторов и функций для оценки (вычисления) значения. Выражения похожи на формулы, написанные на языке запросов. Они могут использоваться для извлечения из БД определенного набора данных.

Базовый синтаксис выражения выглядит так:

Существуют различные типы выражений: логические, числовые и выражения для работы с датами.

Логические

Логические выражения извлекают данные на основе совпадения с единичным значением.

Предположим, что в таблице users имеются следующие записи:

Выполняем поиск активных пользователей:

Числовые

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

Простой пример использования числового выражения:

Существует несколько встроенных функций, таких как count() , sum() , avg() , min() , max() и др. для выполнения так называемых агрегирующих вычислений данных таблицы или колонки.

• AVG — вычисляет среднее значение
• SUM — вычисляет сумму значений
• MIN — вычисляет наименьшее значение
• MAX — вычисляет наибольшее значение
• COUNT — вычисляет количество записей в таблице

Также существует несколько встроенных функций для работы со строками:

• CONCAT — объединение строк
• LENGTH — возвращает количество символов в строке
• TRIM — удаляет пробелы в начале и конце строки
• SUBSTRING — извлекает подстроку из строки
• REPLACE — заменяет подстроку в строке
• LOWER — переводит символы строки в нижний регистр
• UPPER — переводит символы строки в верхний регистр и т.д.

• ROUND — округляет число
• TRUNCATE — обрезает дробное число до указанного количества знаков после запятой
• CEILING — возвращает наименьшее целое число, которое больше или равно текущему значению
• FLOOR — возвращает наибольшее целое число, которое меньше или равно текущему значению
• POWER — возводит число в указанную степень
• SQRT — возвращает квадратный корень числа
• RAND — генерирует случайное число с плавающей точкой в диапазоне от 0 до 1

Выражения для работы с датами

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

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 :

Получаем список БД:

Удаление БД

Для удаления БД используется инструкция DROP DATABASE .

Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.

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

Проверяем, что БД удалена:

Для получения списка таблиц используется инструкция SHOW TABLES .

Выбор БД

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

Предположим, что мы не удаляли testDB . Тогда мы можем выбрать ее так:

Создание таблицы

Создание таблицы предполагает указание названия таблицы и определение колонок таблицы и их типов данных. Для создания таблицы используется инструкция CREATE TABLE .

Для создания таблицы путем копирования другой таблицы используется сочетание CREATE TABLE и SELECT .

Пример создания таблицы users , где первичным ключом являются идентификаторы пользователей, а поля для имени и возраста пользователя не могут быть нулевыми:

Проверяем, что таблица была создана:

Удаление таблицы

Для удаления таблицы используется инструкция DROP TABLE .

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

Удаляем таблицу users :

Теперь, если мы попытаемся получить описание users , то получим ошибку:

Добавление колонок

Для добавления в таблицу колонок используется инструкция INSERT INTO .

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

Во избежание ошибок, рекомендуется всегда перечислять названия колонок.

Предположим, что мы не удаляли таблицу users . Заполним ее пользователями:

В таблицу можно добавлять несколько строк за один раз.

Также, как было отмечено, при добавлении строки названия полей можно опускать:

Заполнение таблицы с помощью другой таблицы

Выборка полей

Для выборки полей из таблицы используется инструкция SELECT . Она возвращает данные в виде результирующей таблицы (результирующего набора, result-set).

Для выборки всех полей используется такой синтаксис:

Произведем выборку полей userId , userName и age из таблицы users :

Предложение WHERE

Предложение WHERE используется для фильтрации возвращаемых данных. Оно используется совместно с SELECT , UPDATE , DELETE и другими инструкциями.

Условие (condition), которому должны удовлетворять возвращаемые записи, определяется с помощью операторов сравнения или логических операторов типа > , < , = , NOT , LIKE и т.д.

Сделаем выборку полей userId , userName и age активных пользователей:

Сделаем выборку полей userId , age и city пользователя с именем Vika .

Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки ( » ), а числа, напротив, указываются как есть.

Операторы AND и OR

Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.

AND

Возвращаемые записи должны удовлетворять всем указанным условиям.

Сделаем выборку полей userId , userName и age активных пользователей старше 26 лет:

OR

Возвращаемые записи должны удовлетворять хотя бы одному условию.

Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:

Обновление полей

Для обновления полей используется инструкция UPDATE . SET . Эта инструкция, обычно, используется в сочетании с предложением WHERE .

Обновим возраст пользователя с именем Igor :

Если в данном случае опустить WHERE , то будет обновлен возраст всех пользователей.

Удаление записей

Для удаления записей используется инструкция DELETE . Эта инструкция также, как правило, используется в сочетании с предложением WHERE .

Удалим неактивных пользователей:

Если в данном случае опустить WHERE , то из таблицы users будут удалены все записи.

Предложения LIKE и REGEX

LIKE

Предложение LIKE используется для сравнения значений с помощью операторов с подстановочными знаками. Существует два вида таких операторов:

• проценты ( % )
• нижнее подчеркивание ( _ )

% означает 0, 1 или более символов. _ означает точно 1 символ.

Сделаем выборку неактивных пользователей:

Сделаем выборку пользователей 30 лет и старше:

REGEX

Предложение REGEX позволяет определять регулярное выражение, которому должна соответствовать запись.

В регулярное выражении могут использоваться следующие специальные символы:

• ^ — начало строки
• $ — конец строки
• . — любой символ
• [символы] — любой из указанных в скобках символов
• [начало-конец] — любой символ из диапазона
• | — разделяет шаблоны

Сделаем выборку пользователей с именами Igor и Vika :

Предложение TOP / LIMIT / ROWNUM

Данные предложения позволяют извлекать указанное количество или процент записей с начала таблицы. Разные СУБД поддерживают разные предложения.

Сделаем выборку первых трех пользователей:

Параметр offset (смещение) определяет количество пропускаемых записей. Например, так можно извлечь первых двух пользователей, начиная с третьего:

Предложения ORDER BY и GROUP BY

ORDER BY

Предложение ORDER BY используется для сортировки данных по возрастанию ( ASC ) или убыванию ( DESC ). Многие СУБД по умолчанию выполняют сортировку по возрастанию.

Обратите внимание: колонки для сортировки должны быть указаны в списке колонок для выборки.

Сделаем выборку пользователей, отсортировав их по городу и возрасту:

Теперь выполним сортировку по убыванию:

Определим собственный порядок сортировки по убыванию:

GROUP BY

Предложение GROUP BY используется совместно с инструкцией SELECT для группировки записей. Оно указывается после WHERE и перед ORDER BY .

Сгруппируем активных пользователей по городам:

Ключевое слово DISTINCT

Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).

Сделаем выборку городов проживания пользователей:

Соединения

Соединения (joins) используются для комбинации записей двух и более таблиц.

Предположим, что кроме users , у нас имеется таблица orders с заказами пользователей следующего содержания:

Сделаем выборку полей userId , userName , age и amount из наших таблиц посредством их соединения:

При соединении таблиц могут использоваться такие операторы, как = , < , > , <> , <= , >= , != , BETWEEN , LIKE и NOT , однако наиболее распространенным является = .

Существуют разные типы объединений:

• INNER JOIN — возвращает записи, имеющиеся в обеих таблицах
• LEFT JOIN — возвращает записи из левой таблицы, даже если такие записи отсутствуют в правой таблице
• RIGHT JOIN — возвращает записи из правой таблицы, даже если такие записи отсутствуют в левой таблице
• FULL JOIN — возвращает все записи объединяемых таблиц
• CROSS JOIN — возвращает все возможные комбинации строк обеих таблиц
• SELF JOIN — используется для объединения таблицы с самой собой

Предложение UNION

Предложение/оператор UNION используется для комбинации результатов двух и более инструкций SELECT . При этом, возвращаются только уникальные записи.

В случае с UNION , каждая инструкция SELECT должна иметь:

• одинаковый набор колонок для выборки
• одинаковое количество выражений
• одинаковые типы данных колонок и
• одинаковый порядок колонок

Однако, они могут быть разной длины.

Объединим наши таблицы users и orders :

Предложение UNION ALL

Предложение UNION ALL также используется для объединения результатов двух и более инструкций SELECT . При этом, возвращаются все записи, включая дубликаты.

Существует еще два предложения, похожих на UNION :

• INTERSECT — используется для комбинации результатов двух и более SELECT , но возвращаются только строки из первого SELECT , совпадающие со строками из второго SELECT
• EXCEPT|MINUS — возвращаются только строки из первого SELECT , отсутствующие во втором SELECT

Синонимы

Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. «Временно» означает, что новое название используется только в текущем запросе, в БД название остается прежним.

Синтаксис синонима таблицы:

Синтаксис синонима колонки:

Пример использования синонимов таблиц:

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

Индексы

Создание индексов

Индексы — это специальные поисковые таблицы (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 . Как было отмечено, представления могут создаваться на основе одной или нескольких таблиц, и даже на основе другого представления.

Создаем представление для имен и возраста пользователей:

Получаем данные с помощью представления:

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 , созданной после первого удаления:

Делаем выборку пользователей:

Как видим, из таблицы был удален только пользователь с возрастом 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 .

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

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

Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.

Последовательности

Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.

Приведенные ниже примеры рассчитаны на mysql .

Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:

Для того, чтобы заново пронумеровать строки с помощью автоматически генерируемых значений (например, при удалении большого количества строк), можно удалить колонку, содержащую такие значения и создать ее заново. Обратите внимание: такая таблица не должна быть частью объединения.

По умолчанию значения, генерируемые с помощью AUTO_INCREMENT , начинаются с 1. Для того, чтобы установить другое начальное значение достаточно указать, например, AUTO_INCREMENT = 100 — в этом случае нумерация строк начнется со 100.

Механизм запросов

Механизм запросов — это один из способов доступа к данным, которые поддерживает платформа. Используя этот механизм, разработчик может читать и обрабатывать данные, хранящиеся в информационной базе; изменение данных с помощью запросов невозможно. Это объясняется тем, что запросы специально предназначены для быстрого получения и обработки некоторой выборки из больших массивов данных, которые могут храниться в базе данных.

Табличный способ доступа к данным

Запросы реализуют табличный способ доступа к данным, которые хранятся в базе данных. Это означает, что все данные представляются в виде совокупности связанных между собой таблиц, к которым можно обращаться как по-отдельности, так и к нескольким таблицам во взаимосвязи:

Такой способ работы с данными позволяет получать сложные выборки данных, сгруппированные и отсортированные определенным образом. Для этих выборок могут быть рассчитаны общие и промежуточные итоги, наложены ограничения на количество или состав записей и пр.

Язык запросов

Для того чтобы разработчик имел возможность использовать запросы для реализации собственных алгоритмов, в платформе реализован язык запросов. Этот язык основан на SQL, но при этом содержит значительное количество расширений, ориентированных на отражение специфики финансово-экономических задач и на максимальное сокращение усилий по разработке прикладных решений. Можно перечислить наиболее существенные возможности, реализуемые языком запросов:

Обращение к полям через точку (».»)

Если поля какой-либо таблицы имеют ссылочный тип (хранят ссылки на объекты другой таблицы), разработчик может в тексте запроса ссылаться на них через «.», при этом количество уровней вложенности таких ссылок система не ограничивает.

Обращение к вложенным таблицам (табличным частям документов и элементов справочников)

Система поддерживает обращения к вложенным табличным частям и как к отдельным таблицам, и как к целым полям одной таблицы. Например, при обращении к документу Реализация товаров (содержащему табличную часть Товары с составом отгружаемых товаров), мы можем считать табличную часть как отдельную таблицу:

Но также мы можем считать заголовочную запись документа, в которой значением поля Товары будут все записи вложенной таблицы, подчиненные этому объекту (документу):

Автоматическое упорядочивание

Для выбора наиболее правильного («естественного») порядка вывода информации на экран или в отчет разработчику в большинстве случаев достаточно задать режим автоматического упорядочивания.

Многомерное и многоуровневое формирование итогов

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

Поддержка виртуальных таблиц

Виртуальные таблицы, предоставляемые системой, позволяют получить практически готовые данные для большинства прикладных решений без необходимости составления сложных запросов. Например, такая виртуальная таблица может предоставить данные по остаткам товаров в разрезе периодов на какой-то момент времени. При этом виртуальные таблицы максимально используют хранимую информацию, например, ранее рассчитанные итоги и т. д.

Стандартные SQL операции

В языке запросов поддерживаются стандартные для SQL операции, такие, как объединение (Union), соединение (Join) и т. д.

Временные таблицы

Язык запросов позволяет использовать в запросах временные таблицы. С их помощью можно повысить производительность запросов, в некоторых случаях снизить количество блокировок и сделать текст запроса более легким для восприятия.

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

Запрос, использующий временную таблицу, будет иметь вид:

Пакетные запросы

Для более удобной работы с временными таблицами в языке запросов поддерживается работа с пакетными запросами — таким образом, создание временной таблицы и ее использование помещаются в один запрос. Пакетный запрос представляет собой последовательность запросов, разделенных символом «;». Запросы исполняются один за другим. Результатом выполнения пакетного запроса в зависимости от используемого метода будет являться либо результат, возвращаемый последним запросом пакета, либо массив результатов всех запросов пакета в той последовательности, в которой следуют запросы в пакете.

Конструкторы запроса

• Конструктор запроса позволяет составить только текст запроса. Подробнее…
• Конструктор запроса с обработкой результата помимо текста запроса формирует фрагмент программного кода, который исполняет запрос и выводит результаты в табличный документ или диаграмму. Подробнее…

Консоль запросов

Инструмент «Консоль запросов» позволяет разработчикам конфигураций и специалистам по внедрению отлаживать запросы и просматривать результаты их выполнения в режиме «1С:Предприятие 8». Подробнее…

Памятка/шпаргалка по 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)

• DML — язык изменения данных (Data Manipulation Language)

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

Обратите внимание: использование верхнего регистра в названиях команд SQL — это всего лишь соглашение, большинство СУБД нечувствительны к регистру. Тем не менее, форма записи инструкций, когда названия команд пишутся большими буквами, а названия таблиц, колонок и др. — маленькими, позволяет быстро определять назначение производимой с данными операции.

Что такое таблица?

Данные в СУБД хранятся в объектах БД, называемых таблицами (tables). Таблица, как правило, представляет собой коллекцию связанных между собой данных и состоит из определенного количества колонок и строк.

Таблица — это самая распространенная и простая форма хранения данных в РБД. Вот пример таблицы с пользователями (users):

Что такое поле?

Каждая таблица состоит из небольших частей — полей (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). Основные категории типов данных:

Точные числовые

Приблизительные числовые

Дата и время

Строковые символьные

Строковые символьные (юникод)

Бинарные

Смешанные

Операторы

Оператор (operators) — это ключевое слово или символ, которые, в основном, используются в инструкциях WHERE для выполнения каких-либо операций. Они используются как для определения условий, так и для объединения нескольких условий в инструкции.

В дальнейших примерах мы будем исходить из предположения, что переменная a имеет значение 10 , а b — 20 .

Арифметические

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

Логические операторы

Выражения

Выражение (expression) — это комбинация значений, операторов и функций для оценки (вычисления) значения. Выражения похожи на формулы, написанные на языке запросов. Они могут использоваться для извлечения из БД определенного набора данных.

Базовый синтаксис выражения выглядит так:

Существуют различные типы выражений: логические, числовые и выражения для работы с датами.

Логические

Логические выражения извлекают данные на основе совпадения с единичным значением.

Предположим, что в таблице users имеются следующие записи:

Выполняем поиск активных пользователей:

Числовые

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

Простой пример использования числового выражения:

Существует несколько встроенных функций, таких как count() , sum() , avg() , min() , max() и др. для выполнения так называемых агрегирующих вычислений данных таблицы или колонки.

• AVG — вычисляет среднее значение
• SUM — вычисляет сумму значений
• MIN — вычисляет наименьшее значение
• MAX — вычисляет наибольшее значение
• COUNT — вычисляет количество записей в таблице

Также существует несколько встроенных функций для работы со строками:

• CONCAT — объединение строк
• LENGTH — возвращает количество символов в строке
• TRIM — удаляет пробелы в начале и конце строки
• SUBSTRING — извлекает подстроку из строки
• REPLACE — заменяет подстроку в строке
• LOWER — переводит символы строки в нижний регистр
• UPPER — переводит символы строки в верхний регистр и т.д.

• ROUND — округляет число
• TRUNCATE — обрезает дробное число до указанного количества знаков после запятой
• CEILING — возвращает наименьшее целое число, которое больше или равно текущему значению
• FLOOR — возвращает наибольшее целое число, которое меньше или равно текущему значению
• POWER — возводит число в указанную степень
• SQRT — возвращает квадратный корень числа
• RAND — генерирует случайное число с плавающей точкой в диапазоне от 0 до 1

Выражения для работы с датами

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

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 :

Получаем список БД:

Удаление БД

Для удаления БД используется инструкция DROP DATABASE .

Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.

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

Проверяем, что БД удалена:

Для получения списка таблиц используется инструкция SHOW TABLES .

Выбор БД

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

Предположим, что мы не удаляли testDB . Тогда мы можем выбрать ее так:

Создание таблицы

Создание таблицы предполагает указание названия таблицы и определение колонок таблицы и их типов данных. Для создания таблицы используется инструкция CREATE TABLE .

Для создания таблицы путем копирования другой таблицы используется сочетание CREATE TABLE и SELECT .

Пример создания таблицы users , где первичным ключом являются идентификаторы пользователей, а поля для имени и возраста пользователя не могут быть нулевыми:

Проверяем, что таблица была создана:

Удаление таблицы

Для удаления таблицы используется инструкция DROP TABLE .

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

Удаляем таблицу users :

Теперь, если мы попытаемся получить описание users , то получим ошибку:

Добавление колонок

Для добавления в таблицу колонок используется инструкция INSERT INTO .

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

Во избежание ошибок, рекомендуется всегда перечислять названия колонок.

Предположим, что мы не удаляли таблицу users . Заполним ее пользователями:

В таблицу можно добавлять несколько строк за один раз.

Также, как было отмечено, при добавлении строки названия полей можно опускать:

Заполнение таблицы с помощью другой таблицы

Выборка полей

Для выборки полей из таблицы используется инструкция SELECT . Она возвращает данные в виде результирующей таблицы (результирующего набора, result-set).

Для выборки всех полей используется такой синтаксис:

Произведем выборку полей userId , userName и age из таблицы users :

Предложение WHERE

Предложение WHERE используется для фильтрации возвращаемых данных. Оно используется совместно с SELECT , UPDATE , DELETE и другими инструкциями.

Условие (condition), которому должны удовлетворять возвращаемые записи, определяется с помощью операторов сравнения или логических операторов типа > , < , = , NOT , LIKE и т.д.

Сделаем выборку полей userId , userName и age активных пользователей:

Сделаем выборку полей userId , age и city пользователя с именем Vika .

Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки ( » ), а числа, напротив, указываются как есть.

Операторы AND и OR

Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.

AND

Возвращаемые записи должны удовлетворять всем указанным условиям.

Сделаем выборку полей userId , userName и age активных пользователей старше 26 лет:

OR

Возвращаемые записи должны удовлетворять хотя бы одному условию.

Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:

Обновление полей

Для обновления полей используется инструкция UPDATE . SET . Эта инструкция, обычно, используется в сочетании с предложением WHERE .

Обновим возраст пользователя с именем Igor :

Если в данном случае опустить WHERE , то будет обновлен возраст всех пользователей.

Удаление записей

Для удаления записей используется инструкция DELETE . Эта инструкция также, как правило, используется в сочетании с предложением WHERE .

Удалим неактивных пользователей:

Если в данном случае опустить WHERE , то из таблицы users будут удалены все записи.

Предложения LIKE и REGEX

LIKE

Предложение LIKE используется для сравнения значений с помощью операторов с подстановочными знаками. Существует два вида таких операторов:

• проценты ( % )
• нижнее подчеркивание ( _ )

% означает 0, 1 или более символов. _ означает точно 1 символ.

Сделаем выборку неактивных пользователей:

Сделаем выборку пользователей 30 лет и старше:

REGEX

Предложение REGEX позволяет определять регулярное выражение, которому должна соответствовать запись.

В регулярное выражении могут использоваться следующие специальные символы:

• ^ — начало строки
• $ — конец строки
• . — любой символ
• [символы] — любой из указанных в скобках символов
• [начало-конец] — любой символ из диапазона
• | — разделяет шаблоны

Сделаем выборку пользователей с именами Igor и Vika :

Предложение TOP / LIMIT / ROWNUM

Данные предложения позволяют извлекать указанное количество или процент записей с начала таблицы. Разные СУБД поддерживают разные предложения.

Сделаем выборку первых трех пользователей:

Параметр offset (смещение) определяет количество пропускаемых записей. Например, так можно извлечь первых двух пользователей, начиная с третьего:

Предложения ORDER BY и GROUP BY

ORDER BY

Предложение ORDER BY используется для сортировки данных по возрастанию ( ASC ) или убыванию ( DESC ). Многие СУБД по умолчанию выполняют сортировку по возрастанию.

Обратите внимание: колонки для сортировки должны быть указаны в списке колонок для выборки.

Сделаем выборку пользователей, отсортировав их по городу и возрасту:

Теперь выполним сортировку по убыванию:

Определим собственный порядок сортировки по убыванию:

GROUP BY

Предложение GROUP BY используется совместно с инструкцией SELECT для группировки записей. Оно указывается после WHERE и перед ORDER BY .

Сгруппируем активных пользователей по городам:

Ключевое слово DISTINCT

Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).

Сделаем выборку городов проживания пользователей:

Соединения

Соединения (joins) используются для комбинации записей двух и более таблиц.

Предположим, что кроме users , у нас имеется таблица orders с заказами пользователей следующего содержания:

Сделаем выборку полей userId , userName , age и amount из наших таблиц посредством их соединения:

При соединении таблиц могут использоваться такие операторы, как = , < , > , <> , <= , >= , != , BETWEEN , LIKE и NOT , однако наиболее распространенным является = .

Существуют разные типы объединений:

• INNER JOIN — возвращает записи, имеющиеся в обеих таблицах
• LEFT JOIN — возвращает записи из левой таблицы, даже если такие записи отсутствуют в правой таблице
• RIGHT JOIN — возвращает записи из правой таблицы, даже если такие записи отсутствуют в левой таблице
• FULL JOIN — возвращает все записи объединяемых таблиц
• CROSS JOIN — возвращает все возможные комбинации строк обеих таблиц
• SELF JOIN — используется для объединения таблицы с самой собой

Предложение UNION

Предложение/оператор UNION используется для комбинации результатов двух и более инструкций SELECT . При этом, возвращаются только уникальные записи.

В случае с UNION , каждая инструкция SELECT должна иметь:

• одинаковый набор колонок для выборки
• одинаковое количество выражений
• одинаковые типы данных колонок и
• одинаковый порядок колонок

Однако, они могут быть разной длины.

Объединим наши таблицы users и orders :

Предложение UNION ALL

Предложение UNION ALL также используется для объединения результатов двух и более инструкций SELECT . При этом, возвращаются все записи, включая дубликаты.

Существует еще два предложения, похожих на UNION :

• INTERSECT — используется для комбинации результатов двух и более SELECT , но возвращаются только строки из первого SELECT , совпадающие со строками из второго SELECT
• EXCEPT|MINUS — возвращаются только строки из первого SELECT , отсутствующие во втором SELECT

Синонимы

Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. «Временно» означает, что новое название используется только в текущем запросе, в БД название остается прежним.

Синтаксис синонима таблицы:

Синтаксис синонима колонки:

Пример использования синонимов таблиц:

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

Индексы

Создание индексов

Индексы — это специальные поисковые таблицы (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 . Как было отмечено, представления могут создаваться на основе одной или нескольких таблиц, и даже на основе другого представления.

Создаем представление для имен и возраста пользователей:

Получаем данные с помощью представления:

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 , созданной после первого удаления:

Делаем выборку пользователей:

Как видим, из таблицы был удален только пользователь с возрастом 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 .

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

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

Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.

Последовательности

Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.

Приведенные ниже примеры рассчитаны на mysql .

Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:

Для того, чтобы заново пронумеровать строки с помощью автоматически генерируемых значений (например, при удалении большого количества строк), можно удалить колонку, содержащую такие значения и создать ее заново. Обратите внимание: такая таблица не должна быть частью объединения.

По умолчанию значения, генерируемые с помощью AUTO_INCREMENT , начинаются с 1. Для того, чтобы установить другое начальное значение достаточно указать, например, AUTO_INCREMENT = 100 — в этом случае нумерация строк начнется со 100.