Как думать на SQL?
Если вы похожи на меня, то согласитесь: SQL — это одна из тех штук, которые на первый взгляд кажутся легкими (читается как будто по-английски!), но почему-то приходится гуглить каждый простой запрос, чтобы найти правильный синтаксис.
А потом начинаются джойны, агрегирование, подзапросы, и получается совсем белиберда. Вроде такой:
Буэ! Такое спугнет любого новичка, или даже разработчика среднего уровня, если он видит SQL впервые. Но не все так плохо.
Легко запомнить то, что интуитивно понятно, и с помощью этого руководства я надеюсь снизить порог входа в SQL для новичков, а уже опытным предложить по-новому взглянуть на SQL.
Не смотря на то, что синтаксис SQL почти не отличается в разных базах данных, в этой статье для запросов используется PostgreSQL. Некоторые примеры будут работать в MySQL и других базах.
1. Три волшебных слова
2. Наша база
Давайте взглянем на базу данных, которую мы будем использовать в качестве примера в этой статье:
У нас есть книжная библиотека и люди. Также есть специальная таблица для учета выданных книг.
3. Простой запрос
Давайте начнем с простого запроса: нам нужны имена и идентификаторы (id) всех книг, написанных автором “Dan Brown”
Запрос будет таким:
| id | title |
|---|---|
| 2 | The Lost Symbol |
| 4 | Inferno |
Довольно просто. Давайте разберем запрос чтобы понять, что происходит.
3.1 FROM — откуда берем данные
Сейчас это может показаться очевидным, но FROM будет очень важен позже, когда мы перейдем к соединениям и подзапросам.
FROM указывает на таблицу, по которой нужно делать запрос. Это может быть уже существующая таблица (как в примере выше), или таблица, создаваемая на лету через соединения или подзапросы.
3.2 WHERE — какие данные показываем
WHERE просто-напросто ведет себя как фильтр строк, которые мы хотим вывести. В нашем случае мы хотим видеть только те строки, где значение в колонке author — это “Dan Brown”.
3.3 SELECT — как показываем данные
Весь запрос можно визуализировать с помощью простой диаграммы:
4. Соединения (джойны)
Теперь мы хотим увидеть названия (не обязательно уникальные) всех книг Дэна Брауна, которые были взяты из библиотеки, и когда эти книги нужно вернуть:
| Title | Return Date |
|---|---|
| The Lost Symbol | 2016-03-23 00:00:00 |
| Inferno | 2016-04-13 00:00:00 |
| The Lost Symbol | 2016-04-19 00:00:00 |
borrowings JOIN books ON borrowings.bookid=books.bookid — это, считай, новая таблица, которая была сформирована комбинированием всех записей из таблиц «books» и «borrowings», в которых значения bookid совпадают. Результатом такого слияния будет:
А потом мы делаем запрос к этой таблице так же, как в примере выше. Это значит, что при соединении таблиц нужно заботиться только о том, как провести это соединение. А потом запрос становится таким же понятным, как в случае с «простым запросом» из пункта 3.
Давайте попробуем чуть более сложное соединение с двумя таблицами.
Теперь мы хотим получить имена и фамилии людей, которые взяли из библиотеки книги автора “Dan Brown”.
На этот раз давайте пойдем снизу вверх:
Шаг Step 1 — откуда берем данные? Чтобы получить нужный нам результат, нужно соединить таблицы “member” и “books” с таблицей “borrowings”. Секция JOIN будет выглядеть так:
Результат соединения можно увидеть по ссылке.
Шаг 2 — какие данные показываем? Нас интересуют только те данные, где автор книги — “Dan Brown”
Шаг 3 — как показываем данные? Теперь, когда данные получены, нужно просто вывести имя и фамилию тех, кто взял книги:
Супер! Осталось лишь объединить три составные части и сделать нужный нам запрос:
| First Name | Last Name |
|---|---|
| Mike | Willis |
| Ellen | Horton |
| Ellen | Horton |
Отлично! Но имена повторяются (они не уникальны). Мы скоро это исправим.
5. Агрегирование
Грубо говоря, агрегирования нужны для конвертации нескольких строк в одну. При этом, во время агрегирования для разных колонок используется разная логика.
Давайте продолжим наш пример, в котором появляются повторяющиеся имена. Видно, что Ellen Horton взяла больше одной книги, но это не самый лучший способ показать эту информацию. Можно сделать другой запрос:
Что даст нам нужный результат:
| First Name | Last Name | Number of books borrowed |
|---|---|---|
| Mike | Willis | 1 |
| Ellen | Horton | 2 |
Каждая строка в результате представляет собой результат агрегирования каждой группы.
В примере выше функция count обрабатывала все строки (так как мы считали количество строк). Другие функции вроде sum или max обрабатывают только указанные строки. Например, если мы хотим узнать количество книг, написанных каждым автором, то нужен такой запрос:
| author | sum |
|---|---|
| Robin Sharma | 4 |
| Dan Brown | 6 |
| John Green | 3 |
| Amish Tripathi | 2 |
Здесь функция sum обрабатывает только колонку stock и считает сумму всех значений в каждой группе.
6. Подзапросы
Подзапросы это обычные SQL-запросы, встроенные в более крупные запросы. Они делятся на три вида по типу возвращаемого результата.
6.1 Двумерная таблица
Есть запросы, которые возвращают несколько колонок. Хороший пример это запрос из прошлого упражнения по агрегированию. Будучи подзапросом, он просто вернет еще одну таблицу, по которой можно делать новые запросы. Продолжая предыдущее упражнение, если мы хотим узнать количество книг, написанных автором “Robin Sharma”, то один из возможных способов — использовать подзапросы:
| author | sum |
|---|---|
| Robin Sharma | 4 |
6.2 Одномерный массив
Запросы, которые возвращают несколько строк одной колонки, можно использовать не только как двумерные таблицы, но и как массивы.
Допустим, мы хотим узнать названия и идентификаторы всех книг, написанных определенным автором, но только если в библиотеке таких книг больше трех. Разобьем это на два шага:
1. Получаем список авторов с количеством книг больше 3. Дополняя наш прошлый пример:
| author |
|---|
| Robin Sharma |
| Dan Brown |
Можно записать как: [‘Robin Sharma’, ‘Dan Brown’]
2. Теперь используем этот результат в новом запросе:
| title | bookid |
|---|---|
| The Lost Symbol | 2 |
| Who Will Cry When You Die? | 3 |
| Inferno | 4 |
Это то же самое, что:
6.3 Отдельные значения
Бывают запросы, результатом которых являются всего одна строка и одна колонка. К ним можно относиться как к константным значениям, и их можно использовать везде, где используются значения, например, в операторах сравнения. Их также можно использовать в качестве двумерных таблиц или массивов, состоящих из одного элемента.
Давайте, к примеру, получим информацию о всех книгах, количество которых в библиотеке превышает среднее значение в данный момент.
Среднее количество можно получить таким образом:
Теперь, наконец, можно написать весь запрос:
Это то же самое, что:
| bookid | title | author | published | stock |
|---|---|---|---|---|
| 3 | Who Will Cry When You Die? | Robin Sharma | 2006-06-15 00:00:00 | 4 |
7. Операции записи
Большинство операций записи в базе данных довольно просты, если сравнивать с более сложными операциями чтения.
7.1 Update
Синтаксис запроса UPDATE семантически совпадает с запросом на чтение. Единственное отличие в том, что вместо выбора колонок SELECT ‘ом, мы задаем знаения SET ‘ом.
Если все книги Дэна Брауна потерялись, то нужно обнулить значение количества. Запрос для этого будет таким:
7.2 Delete
7.3 Insert
8. Проверка
Вот он в более удобном для чтения виде:
Этот запрос выводит список людей, которые взяли из библиотеки книгу, у которой общее количество выше среднего значения.
| Full Name |
|---|
| Lida Tyler |
Надеюсь, вам удалось разобраться без проблем. Но если нет, то буду рад вашим комментариям и отзывам, чтобы я мог улучшить этот пост.
Свойства шага задания — создание шага задания (страница «Общие»)
В Управляемом экземпляре Azure SQL в настоящее время поддерживается большинство функций агента SQL Server (но не все). Подробные сведения см. в статье Различия в T-SQL между Управляемым экземпляром SQL Azure и SQL Server.
Эта страница используется для просмотра и изменения свойств шага задания агента Microsoft SQL Server или для определения нового шага задания.
Для перехода к данной странице в обозревателе объектов среды SQL Server Management Studio разверните агент SQL Server, щелкните правой кнопкой мыши Задания, выберите пункт Создать задание, выберите страницу Шаги и нажмите кнопку Создать. Перейти к этой странице можно также, щелкнув правой кнопкой мыши задание в обозревателе объектов, наведя курсор мыши на пункт Свойства, затем выбрав страницу Шаги и нажав кнопку Создать, Вставить или Изменить.
Параметры
Имя шага
Задайте имя для шага задания.
Тип
Укажите подсистему, используемую на шаге задания. Исходя из выбранной подсистемы, параметры отображаются для определения изменения шагов задания.
Выполнять как
Укажите учетную запись-посредник для этого шага задания. Члены предопределенной роли сервера sysadmin также могут указать Учетную запись службы агента SQL Server.
База данных
Задайте базу данных, в которой запускается шаг задания. Этот параметр доступен не для всех типов шагов задания.
Команда
Задайте команду, запускаемую шагом задания.
Параметры для шагов задания языка Transact-SQL
Открыть
Загрузить команду из файла.
Выделить все
Выбрать текст команды.
Копировать
Копировать выделенный текст в буфер обмена.
Вставить
Вставить содержимое буфера обмена.
Анализ
Проверить синтаксис команды.
Параметры шагов задания скриптов ActiveX
Подсистема сценариев ActiveX будет удалена из агента SQL Server в последующей версии MicrosoftSQL Server. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
VBScript
Указывает Microsoft Visual Basic Scripting Edition в качестве языка, используемого шагами задания.
Язык JScript
Указывает JScript в качестве языка, используемого шагами задания.
Другое
Введите имя языка для шагов задания, написанных на другом языке сценариев.
Открыть
Загрузить команду из файла.
Выделить все
Выбрать текст команды.
Копировать
Копировать выделенный текст.
Вставить
Вставить содержимое буфера обмена.
Параметры для шагов задания операционной системы (CmdExec)
Код завершения процесса успешной команды
Введите код завершения, возвращаемый командой после успешного выполнения.
Открыть
Загрузить команду из файла.
Выделить все
Выбрать текст команды.
Копировать
Копировать выделенный текст.
Вставить
Вставить содержимое буфера обмена.
Параметры для шагов задания языка PowerShell
Открыть
Загрузить скрипт из файла.
Выделить все
Выбрать текст скрипта.
Копировать
Копировать выделенный текст.
Вставить
Вставить содержимое буфера обмена.
Параметры шагов задания распространителя репликации
Выделить все
Выбрать текст команды.
Копировать
Копировать выделенный текст.
Вставить
Вставить содержимое буфера обмена.
Параметры шагов задания по слиянию репликации
Выделить все
Выбрать текст команды.
Копировать
Копировать выделенный текст.
Вставить
Вставить содержимое буфера обмена.
Параметры шагов заданий для агента чтения очереди репликации
База данных
База данных, которая будет использоваться на шаге задания.
Выделить все
Выбрать текст команды.
Копировать
Копировать выделенный текст.
Вставить
Вставить содержимое буфера обмена.
Параметры шагов задания моментальных снимков репликации
Выделить все
Выбрать текст команды.
Копировать
Копировать выделенный текст.
Вставить
Вставить содержимое буфера обмена.
Параметры шагов задания агента чтения журнала транзакций репликации
Выделить все
Выбрать текст команды.
Копировать
Копировать выделенный текст.
Вставить
Вставить содержимое буфера обмена.
Параметры шагов задания команды служб SQL Server Analysis Services
Server
Выбрать сервер, на котором будет выполняться шаг задания.
Открыть
Загрузить команду из файла.
Выделить все
Выбрать текст команды.
Копировать
Копировать выделенный текст.
Вставить
Вставить содержимое буфера обмена.
Параметры шагов задания очереди служб SQL Server Analysis Services
Server
Выбрать сервер, на котором будет выполняться шаг задания.
База данных
База данных, которая будет использоваться на шаге задания.
Открыть
Загрузить команду из файла.
Выделить все
Выбрать текст команды.
Копировать
Копировать выделенный текст.
Вставить
Вставить содержимое буфера обмена.
Параметры шагов задания выполнения пакетов служб Integration Services
Вкладка «Общие»
Позволяет указать размещение пакета Службы Integration Services (службыIntegration Services) и используемый метод проверки подлинности. При выборе этой вкладки доступны следующие параметры.
SQL Server
Файловая система
Хранилище пакетов служб SSIS
Использовать проверку подлинности Windows
Имена входа SQL Server используют проверку подлинности Microsoft Windows.
SQL Server поддерживается для обратной совместимости. Для повышения безопасности, по возможности, используйте проверку подлинности Windows.
Пакет
Введите местонахождение пакета.
Вкладка «Конфигурации»
Файлы конфигурации
Содержит список файлов конфигурации пакета.
Добавление
Добавить файл конфигурации пакета.
Удалить
Удалить файл конфигурации пакета.
Вверх
Переместить выбранный файл конфигурации вверх.
Вниз
Переместить выбранный файл конфигурации вниз.
Вкладка «Командные файлы»
Позволяет выбрать командные файлы для пакета. Командные файлы обрабатываются в порядке отображения их в списке. При выборе этой вкладки доступны следующие параметры.
Командные файлы
Содержит список командных файлов пакета.
Добавление
Добавить командный файл.
Удалить
Удалить выбранный командный файл.
Вверх
Переместить выбранный командный файл вверх.
Вниз
Переместить выбранный командный файл вниз.
Вкладка «Источники данных»
На этой вкладке можно просмотреть источники данных, заданные для пакета.
Диспетчер соединений
Просмотр имени источника данных.
Описание
Позволяет просмотреть описание источника данных.
Строка подключения
Позволяет просмотреть строку соединения к источнику данных.
Вкладка «Параметры выполнения»
На этой вкладке можно просмотреть или изменить параметры выполнения пакета.
Завершить работу с ошибкой при предупреждениях проверки
Выберите этот параметр для остановки выполнения пакета в случае появления предупреждений проверки.
Проверить пакет без выполнения
Выберите этот параметр для проверки на данном шаге задания без выполнения этого пакета.
Максимальное число одновременно исполняемых объектов
Максимальное число исполняемых файлов, которые могут быть запущены одновременно.
Включить контрольные точки пакета
Выберите этот параметр, чтобы использовать контрольные точки пакета на данном шаге задания.
Файл контрольных точек
Введите имя файла контрольных точек пакета.
.
Позволяет выполнить обзор для поиска файла контрольных точек пакета.
Переопределить режим перезапуска
Выберите этот параметр, чтобы указать параметры перезапуска данного шага задания, отличные от параметров перезапуска, указанных для пакета.
Параметр перезапуска
Выберите действие, выполняемое при перезапуске пакета.
Вкладка «Регистрация»
На этой вкладке можно просмотреть или изменить регистраторы для пакета.
Регистратор
Выберите идентификатор ClassID регистратора.
Строка конфигурации
Введите строку конфигурации для регистратора.
Удалить
Удаляет регистратор.
Вкладка «Установка значений»
На этой вкладке можно просмотреть или изменить значения свойств пакета.
Путь к свойству
Позволяет просмотреть или изменить путь к свойству.
Значение
Позволяет просмотреть или изменить значение свойства.
Удалить
Позволяет удалить свойство.
Вкладка «Проверка»
На этой вкладке можно выбрать параметры проверки для данного шага задания.
Выполнять только подписанные пакеты
Позволяет запускать только подписанные пакеты. При выборе этого параметра шаг задания выдаст сбой в случае неподписанного пакета.
Проверить номер сборки пакета
Позволяет запускать только пакеты с определенным номером сборки. При выборе этого параметра шаг задания выдаст сбой в случае несоответствия номера сборки пакета указанному.
Сборка
Введите номер сборки пакета.
Проверить идентификатор пакета
Позволяет запускать только пакеты с определенным идентификатором. При выборе этого параметра шаг задания сформирует ошибку в случае отсутствия у пакета указанного идентификатора.
Идентификатор пакета
Введите идентификатор пакета.
Проверить идентификатор версии
Позволяет запускать только пакеты с определенным идентификатором версии. При выборе этого параметра шаг задания сформирует ошибку в случае отсутствия у пакета указанного идентификатора версии.
Идентификатор версии
Введите идентификатор версии.
Вкладка «Командная строка»
Позволяет задать параметры командной строки для пакета. При выборе этой вкладки доступны следующие параметры.
Восстановить первоначальные значения параметров
Позволяет использовать параметры командной строки, установленные в этом диалоговом окне.
Изменить командную строку вручную
Позволяет задать параметры в окне командной строки.
Командная строка
Введите параметры командной строки для использования в данном пакете.
Т.к. подобный запрос мы будем использовать каждый раз, когда нам необходимо будет добавить нового покупателя, то вполне уместно оформить его в виде процедуры:
Обратите внимание, как задаются параметры: необходимо дать имя параметру и указать его тип, а в теле процедуры мы уже используем имена параметров. Один нюанс. Как вы помните, точка с запятой означает конец запроса и отправляет его на выполнение, что в данном случае неприемлемо. Поэтому, прежде, чем написать процедуру необходимо переопределить разделитель с ; на «//», чтобы запрос не отправлялся раньше времени. Делается это с помощью оператора DELIMITER // :
Таким образом, мы указали СУБД, что выполнять команды теперь следует после //. Следует помнить, что переопределение разделителя осуществляется только на один сеанс работы, т.е. при следующем сеансе работы с MySql разделитель снова станет точкой с запятой и при необходимости его придется снова переопределять. Теперь можно разместить процедуру:
Согласитесь, что так гораздо проще, чем писать каждый раз полный запрос. Проверим, работает ли процедура, посмотрев, появился ли новый покупатель в таблице Покупатели (customers):
Как было сказано в начале урока, процедуры позволяют объединить последовательность запросов. Давайте посмотрим, как это делается. Помните в уроке 11 мы хотели узнать, на какую сумму нам привез товар поставщик «Дом печати»? Для этого нам пришлось использовать вложенные запросы, объединения, вычисляемые столбцы и представления. А если мы захотим узнать, на какую сумму нам привез товар другой поставщик? Придется составлять новые запросы, объединения и т.д. Проще один раз написать хранимую процедуру для этого действия.
Казалось бы, проще всего взять уже написанные в уроке 11 представление и запрос к нему, объединить в хранимую процедуру и сделать идентификатор поставщика (id_vendor) входным параметром, вот так:
А потом создадим запрос, который просуммирует суммы поставок интересующего нас поставщика, например, с id_vendor=2:
Вот теперь мы можем объединить два этих запроса в хранимую процедуру, где входным параметром будет идентификатор поставщика (id_vendor), который будет подставляться во второй запрос, но не в представление:
Проверим работу процедуры, с разными входными параметрами:
Как видите, процедура срабатывает один раз, а затем выдает ошибку, говоря нам, что представление report_vendor уже имеется в БД. Так происходит потому, что при обращении к процедуре в первый раз, она создает представление. При обращении во второй раз, она снова пытается создать представление, но оно уже есть, поэтому и появляется ошибка. Чтобы избежать этого возможно два варианта.
Перед использованием этого варианта не забудьте удалить процедуру sum_vendor, а затем проверить работу: 
Как видите, сложные запросы или их последовательность действительно проще один раз оформить в хранимую процедуру, а дальше просто обращаться к ней, указывая необходимые параметры. Это значительно сокращает код и делает работу с запросами более логичной.
Видеоуроки php + mysql
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
