Группировки и оконные функции в Oracle
Привет, Хабр! В компании, где я работаю, часто проходят (за мат извините) митапы. На одном из них выступал мой коллега с докладом об оконных функциях и группировках Oracle. Эта тема показалась мне стоящей того, чтобы сделать о ней пост.
С самого начала хотелось бы уточнить, что в данном случае Oracle представлен как собирательный язык SQL. Группировки и методы их применения подходят ко всему семейству SQL (который понимается здесь как структурированный язык запросов) и применимы ко всем запросам с поправками на синтаксис каждого языка.
Всю необходимую информацию я постараюсь кратко и доступно объяснить в двух частях. Пост скорее будет полезен начинающим разработчикам. Кому интересно — добро пожаловать под кат.
Часть 1: предложения Order by, Group by, Having
Здесь мы поговорим о сортировке — Order by, группировке — Group by, фильтрации — Having и о плане запроса. Но обо всем по-порядку.
Order by
Оператор Order by выполняет сортировку выходных значений, т.е. сортирует извлекаемое значение по определенному столбцу. Сортировку также можно применять по псевдониму столбца, который определяется с помощью оператора.
Преимущество Order by в том, что его можно применять и к числовым, и к строковым столбцам. Строковые столбцы обычно сортируются по алфавиту.
Сортировка по возрастанию применяется по умолчанию. Если хотите отсортировать столбцы по убыванию — используйте дополнительный оператор DESC.
SELECT column1, column2, … (указывает на название)
FROM table_name
ORDER BY column1, column2… ASC|DESC;
Давайте все рассмотрим на примерах: 
В первой таблице мы получаем все данные и сортируем их по возрастанию по столбцу ID.
Во второй мы также получаем все данные. Сортируем по столбцу ID по убыванию, используя ключевое слово DESC.
В третьей таблице используется несколько полей для сортировки. Сначала идет сортировка по отделу. При равенстве первого оператора для полей с одинаковым отделом применяется второе условие сортировки; в нашем случае — это зарплата.
Все довольно просто. Мы можем задать более одного условия сортировки, что позволяет более грамотно сортировать выходные списки.
Group by
В SQL оператор Group by собирает данные, полученные из базы данных в определенных группах. Группировка разделяет все данные на логические наборы, что дает возможность выполнять статистические вычисления отдельно в каждой группе.
Этот оператор используется для объединения результатов выборки по одному или нескольким столбцам. После группировки будет только одна запись для каждого значения, использованного в столбце.
С использованием оператора SQL Group by тесно связано использование агрегатных функций и оператор SQL Having. Агрегатная функция в SQL — это функция, возвращающая какое-либо одно значение по набору значений столбца. Например: COUNT(), MIN(), MAX(), AVG(), SUM()
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Group by стоит после условного оператора WHERE в запросе SELECT. По желанию можно использовать ORDER BY, чтобы отсортировать выходные значения.
Итак, опираясь на таблицу из предыдущего примера, нам нужно найти максимальную зарплату сотрудников каждого отдела. В итоговой выборке должно получиться название отдела и максимальная зарплата.
Решение 1 (без использования группировки):
Решение 2 (с использованием группировки):
В первом примере решаем задачу без использования группировки, но с использованием подселекта, т.е. в один селект вкладываем второй. Во втором решении используем группировку.
Второй пример вышел короче и читабельнее, хотя выполняет такие же функции, что и первый.
Как у нас работает Group by: сначала разбивает два отдела на группы qa и dev. Потом для каждого из них ищет максимальную зарплату.
Having
Having это инструмент фильтрации. Он указывает на результат выполнения агрегатных функций. Предложение Having используется в SQL там, где нельзя применить WHERE.
Если предложение WHERE определяет предикат для фильтрации строк, то Having используется после группировки для определения логичного предиката, фильтрующего группу по значениям агрегатных функций. Предложение необходимо для проверки значений, полученных при помощи агрегатных функций из групп строк.
Сначала мы выводим отделы со средней зарплатой больше 4000. Затем выводим максимальную зарплату с применением фильтрации.
Решение 1 (без использования GROUP BY и HAVING):
Решение 2 (с использованием GROUP BY и HAVING):
В первом примере используется два подселекта: один для нахождения максимальной зарплаты, другой для фильтрации средней зарплаты. Второй пример, опять же, вышел намного проще и лаконичнее.
План запроса
Нередко бывают ситуации, когда запрос работает долго, потребляя значительные ресурсы памяти и дисков. Чтобы понять, почему запрос работает долго и неэффективно, мы можем посмотреть план запроса.
План запроса — это предполагаемый план выполнения запроса, т.е. как СУБД будет его выполнять. СУБД распишет все операции, которые будут выполняться в рамках подзапроса. Проанализировав все, мы сможем понять, где в запросе слабые места и с помощью плана запроса сможем оптимизировать их.
Исполнение любого SQL предложения в Oracle извлекает так называемый “план исполнения”. Этот план исполнения запроса является описанием того, как Oracle будет осуществлять выборку данных, согласно исполняемому SQL предложению. План представляет собой дерево, которое содержит порядок шагов и связь между ними.
К средствам, позволяющим получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. Они выдают ряд показателей ресурсоемкости запроса, среди которых основными являются: cost — стоимость выполнения и cardinality (или rows) — кардинальность (или количество строк).
Чем больше значение этих показателей, тем менее эффективен запрос.
Ниже можно увидеть анализ плана запроса. В первом решении используется подселект, во втором — группировка. Обратите внимание, что в первом решении обработано 22 строки, во втором — 15.
Анализ плана запроса:
Ещё один анализ плана запроса, в котором применяется два подселекта:
Этот пример приведен как вариант нерационального использования средств SQL и я не рекомендую вам его использовать в своих запросах.
Все перечисленные выше функции упростят вам жизнь при написании запросов и повысят качество и читабельность вашего кода.
Часть 2: Оконные функции
Оконные функции появились ещё в Microsoft SQL Server 2005. Они осуществляют вычисления в заданном диапазоне строк внутри предложения Select. Если говорить кратко, то “окно” — это набор строк, в рамках которого происходит вычисление. “Окно” позволяет уменьшить данные и более качественно их обработать. Такая функция позволяет разбивать весь набор данных на окна.
Оконные функции обладают огромным преимуществом. Нет необходимости формировать набор данных для расчетов, что позволяет сохранить все строки набора с их уникальными ID. Результат работы оконных функций добавляется к результатирующей выборке в еще одно поле.
SELECT column_name(s)
Агрегирующая функция (столбец для вычислений)
OVER ([PARTITION BY столбец для группировки]
FROM table_name
[ORDER BY столбец для сортировки]
[ROWS или RANGE выражение для ограничения строк в пределах группы])
OVER PARTITION BY — это свойство для задания размеров окна. Здесь можно указывать дополнительную информацию, давать служебные команды, например добавить номер строки. Синтаксис оконной функции вписывается прямо в выборку столбцов.
Давайте рассмотрим все на примере: в нашу таблицу добавился еще один отдел, теперь в таблице 15 строк. Мы попытаемся вывести работников, их з/п, а также максимальную з/п организации.
В первом поле мы берем имя, во втором — зарплату. Дальше мы применяем оконную функцию over(). Используем её для получения максимальной зарплаты по всей организации, так как не указаны размеры “окна”. Over() с пустыми скобками применяется для всей выборки. Поэтому везде максимальная зарплата — 10 000. Результат действия оконной функции добавляется к каждой строчке.
Если убрать из четвертой строки запроса упоминание оконной функции, т.е. остается только max (salary), то запрос не сработает. Максимальную зарплату просто не удалось бы посчитать. Так как данные обрабатывались бы построчно, и на момент вызова max (salary) было бы только одно число текущей строки, т.е. текущего работника. Вот тут и можно заметить преимущество оконной функции. В момент вызова она работает со всем окном и со всеми доступными данными.
Давайте рассмотрим еще один пример, где нужно вывести максимальную з/п каждого отдела:
Фактически мы задаем рамки для “окна”, разбивая его на отделы. В качестве ранжирующего примера мы указываем department. У нас есть три отдела: dev, qa и sales.
“Окно” находит максимальную зарплату для каждого отдела. В результате выборки мы видим, что оно нашло максимальную зарплату сначала для dev, затем для qa, потом для sales. Как уже упоминалось выше, результат оконной функции записывается в результат выборки каждой строки.
В предыдущем примере в скобках после over не было указано. Здесь мы использовали PARTITION BY, которое позволило задать размеры нашего окна. Здесь можно указывать какую-то доп информацию, передавать служебные команды, например, номер строки.
Заключение
SQL не так прост, как кажется на первый взгляд. Все описанное выше — это базовые возможности оконных функций. С их помощью можно “упростить” наши запросы. Но в них скрыто намного больше потенциала: есть служебные операторы (например ROWS или RANGE), которые можно комбинировать, добавляя больше функциональности запросам.
Надеюсь, пост был полезен для всех интересующихся данной темой.
в чем разница между GROUP BY и ORDER BY в sql
когда вы используете что вообще? Примеры приветствуются!
Я имею в виду так MySql, но не могу представить, что концепция отличается от другой СУБД
11 ответов
ORDER BY изменяет порядок, в котором возвращаются элементы.
GROUP BY будет агрегировать записи по указанным столбцам, что позволяет выполнять агрегационные функции для некрупных столбцов (таких как SUM, COUNT, AVG и т. д.).
ORDER BY изменяет порядок, в котором возвращаются элементы.
GROUP BY будет агрегировать записи по указанным столбцам, что позволяет выполнять агрегационные функции для некрупных столбцов (таких как SUM, COUNT, AVG и т. д.).
ЗАКАЗ: сортировка данных в порядке возрастания или убывания.
рассмотрим клиенты стол:
ниже приведен пример, который будет сортировать результаты в порядке возрастания по имени:
это приведет к следующему результату:
ГРУППЫ: упорядочить идентичные данные в группы.
теперь клиенты таблица следующие записи с повторяющимися именами:
если вы хотите сгруппировать идентичные имена в одно имя, то группа по запросу будет выглядеть следующим образом:
это приведет к следующему результату: (для идентичных имен он выберет последний и, наконец, отсортирует столбец в порядке возрастания)
как вы заключили,что он бесполезен без SQL-функций, таких как sum, avg и т. д..
так пройти через это определение поймите правильное использование GROUP BY:
предложение GROUP BY работает над строками, возвращаемыми запросом путем суммирования идентичные строки в одну / отдельную группу и возвращает одну строку с резюме для каждой группы, используя соответствующий агрегат функция в списке выбора, например COUNT (), SUM (), MIN (), MAX (), AVG(), так далее.
теперь, если вы хотите знать общую сумму зарплаты на каждого клиента( имя), то группа по запросу будет как следует:
это приведет к следующему результату: (сумма зарплат идентичных имен и сортировка столбца имени после удаления идентичных имен)
разница именно в том, что подразумевает имя: группа по выполняет операцию группировки и порядок по сортировкам.
Если у вас SELECT * FROM Customers ORDER BY Name затем вы получаете список результатов, отсортированный по имени клиентов.
Если у вас SELECT IsActive, COUNT(*) FROM Customers GROUP BY IsActive вы получаете количество активных и неактивных клиентов. Группа по агрегирует результаты на основе указанного поля.
Они имеют совершенно другое значение и на самом деле не связаны вообще.
ПРИКАЗОМ позволяет сортировать результирующий набор по различным критериям, таким как первая сортировка по имени из a-z, а затем сортировка по цене от самой высокой до самой низкой.
(заказ по названию, цена DESC)
ГРУППА ПО позволяет взять результирующий набор, сгруппировать его в логические группы, а затем выполнить агрегированные запросы к этим группам. Вы можете, например, выбрать все сотрудники, сгруппировать их по месту их работы и рассчитать среднюю зарплату всех сотрудников каждого места работы.
простой ORDER BY заказывает данные и GROUP BY группы, или объединяет данные.
ORDER BY упорядочивает результирующий набор в соответствии с указанным полем по умолчанию в порядке возрастания.
на GROUP BY case, мы используем это с агрегатными функциями, и он группирует данные согласно агрегату функции, и мы получаем результат. Здесь, Если наш запрос имеет SUM (marks) вместе с GROUP BY (student_first_name) он покажет сумму баллов студентов, принадлежащих к каждой группе (где все члены группы будут иметь одинаковое имя).
GROUP BY используется для группировки строк в select, обычно при агрегировании строк(например, вычислении итогов, средних и т. д. для набора строк с одинаковыми значениями некоторых полей).
ORDER BY используется для упорядочения строк, полученных из инструкции select.
некоторые хорошие примеры там. Так же, как добавить свой собственный из webcheatsheet, который дает хорошие четкие примеры, а также позволяет выполнять собственный SQL.
ORDER BY показывает поля в порядке возрастания или убывания. В то время как GROUP BY показывает те же имена полей, идентификаторы и т. д. Только в одном выходе.
стоит отметить GROUP BY не всегда необходимо, так как (по крайней мере, в PostgreSQL и, вероятно, в других вариантах SQL) вы можете использовать ORDER BY список и можно использовать ASC или DESC per.
MySQL «Group By» и » Order By»
Я хочу иметь возможность выбирать кучу строк из таблицы электронных писем и группировать их по отправителю from. Мой запрос выглядит так:
например, он может вернуть:
когда записи в базе данных являются:
Если тема» вопрос программирования » является самой последней, как я могу заставить MySQL выбрать эту запись при группировании электронных писем?
6 ответов
простое решение заключается в том, чтобы обернуть запрос в подвыбор с помощью оператора ORDER первый и применение группы по позже:
это похоже на использование соединения, но выглядит намного лучше.
использование неагрегатных столбцов в предложении SELECT с предложением GROUP BY является нестандартным. MySQL обычно возвращает значения первой строки, которую он находит, и отбрасывает остальные. Любые предложения ORDER BY будут применяться только к возвращаемому значению столбца, не для брошенных.
ВАЖНОЕ ОБНОВЛЕНИЕ Выбрав неагрегатная столбцов, используемых для работы в практике, но не следует полагаться. За документация MySQL » это полезно в первую очередь, когда все значения в каждом неагрегированном столбце, не названном в GROUP BY, одинаковы для каждой группы. Сервер бесплатно выбрать любое значение от каждой группы, так что если они не совпадают, выбранные значения являются неопределенными.»
по состоянию на 5.6.21 я заметил проблемы с группой BY во временной таблице, возвращающей порядок путем сортировки.
по состоянию на 5.7.5 ONLY_FULL_GROUP_BY включен по умолчанию, т. е. невозможно использовать неагрегатные столбцы.
в основном, вы присоединяетесь к таблице на себя, ища более поздние строки. В предложении where вы указываете, что не может быть более поздних строк. Это дает вам только последнюю строку.
Если может быть несколько писем с одной и той же меткой времени, этот запрос потребуется уточнить. Если в таблице электронной почты есть столбец incremental ID, измените соединение следующим образом:
сделайте группу после заказа, обернув свой запрос с помощью группы следующим образом:
в соответствии со стандартом SQL вы не можете использовать неагрегатные столбцы в списке выбора. MySQL позволяет такое использование (используется режим uless ONLY_FULL_GROUP_BY), но результат не предсказуем.
как указано в ответе уже, текущий ответ неверен, потому что группа по произвольно выбирает запись из окна.
Если используется MySQL 5.6 или MySQL 5.7 с ONLY_FULL_GROUP_BY правильный (детерминированные) запрос:
для эффективного выполнения запроса требуется правильная индексация.
Я боролся с обоими этими подходами для более сложных запросов, чем показанные, потому что подход подзапроса был ужасно недостаточным, независимо от того, какие индексы я поставил, и потому что я не мог получить внешнее самосоединение через Hibernate
лучший (и самый простой) способ сделать это-сгруппировать по чему-то, что построено, чтобы содержать конкатенацию требуемых полей, а затем вытащить их с помощью выражений в предложении SELECT. Если вам нужно сделать MAX () make убедитесь, что поле, которое вы хотите MAX (), всегда находится в самом значимом конце объединенного объекта.
ключ к пониманию этого заключается в том, что запрос может иметь смысл только в том случае, если эти другие поля инвариантны для любой сущности, которая удовлетворяет Max (), поэтому в терминах сортировки другие части конкатенации можно игнорировать. Он объясняет, как это сделать в самом низу этой ссылке. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html
Если вы можете получить событие am insert/update (например, триггер) для предварительного вычисления конкатенации полей, вы можете индексировать его, и запрос будет таким же быстрым, как если бы group by была над полем, которое вы действительно хотели MAX(). Вы даже можете использовать его, чтобы получить максимум из нескольких полей. Я использую его, чтобы сделать запросы к многомерным expresssed деревья как вложенные множества.
Памятка/шпаргалка по SQL
Доброго времени суток, друзья!
Изучение настоящей шпаргалки не сделает вас мастером SQL, но позволит получить общее представление об этом языке программирования и возможностях, которые он предоставляет. Рассматриваемые в шпаргалке возможности являются общими для всех или большинства диалектов SQL.
Для более полного погружения в SQL рекомендую изучить эти руководства по MySQL и PostgreSQL от Метанита. Они хороши тем, что просты в изучении и позволяют быстро начать работу с названными СУБД.
При обнаружении ошибок, опечаток и неточностей, не стесняйтесь писать мне в личку.
Содержание
Что такое SQL?
SQL — это язык структурированных запросов (Structured Query Language), позволяющий хранить, манипулировать и извлекать данные из реляционных баз данных (далее — РБД, БД).
Почему SQL?
Процесс SQL
При выполнении любой SQL-команды в любой RDBMS (Relational Database Management System — система управления РБД, СУБД, например, PostgreSQL, MySQL, MSSQL, SQLite и др.) система определяет наилучший способ выполнения запроса, а движок SQL определяет способ интерпретации задачи.
В данном процессе участвует несколького компонентов:
Классический движок обрабатывает все не-SQL-запросы, а движок SQL-запросов не обрабатывает логические файлы.
Команды SQL
| N | Команда | Описание |
|---|---|---|
| 1 | CREATE | Создает новую таблицу, представление таблицы или другой объект в БД |
| 2 | ALTER | Модифицирует существующий в БД объект, такой как таблица |
| 3 | DROP | Удаляет существующую таблицу, представление таблицы или другой объект в БД |
| N | Команда | Описание |
|---|---|---|
| 1 | SELECT | Извлекает записи из одной или нескольких таблиц |
| 2 | INSERT | Создает записи |
| 3 | UPDATE | Модифицирует записи |
| 4 | DELETE | Удаляет записи |
| N | Команда | Описание |
|---|---|---|
| 1 | GRANT | Наделяет пользователя правами |
| 1 | REVOKE | Отменяет права пользователя |
Обратите внимание: использование верхнего регистра в названиях команд SQL — это всего лишь соглашение, большинство СУБД нечувствительны к регистру. Тем не менее, форма записи инструкций, когда названия команд пишутся большими буквами, а названия таблиц, колонок и др. — маленькими, позволяет быстро определять назначение производимой с данными операции.
Что такое таблица?
Данные в СУБД хранятся в объектах БД, называемых таблицами (tables). Таблица, как правило, представляет собой коллекцию связанных между собой данных и состоит из определенного количества колонок и строк.
Таблица — это самая распространенная и простая форма хранения данных в РБД. Вот пример таблицы с пользователями (users):
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 25 | Moscow | active |
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 3 | Elena | 27 | Ekaterinburg | active |
| 4 | Oleg | 28 | Moscow | inactive |
Что такое поле?
Каждая таблица состоит из небольших частей — полей (fields). Полями в таблице users являются userId, userName, age, city и status. Поле — это колонка таблицы, предназначенная для хранения определенной информации о каждой записи в таблице.
Что такое запись или строка?
Запись или строка (record/row) — это любое единичное вхождение (entry), существующее в таблице. В таблице users 5 записей. Проще говоря, запись — это горизонтальное вхождение в таблице.
Что такое колонка?
Что такое нулевое значение?
Ограничения
Ограничения (constraints) — это правила, применяемые к данным. Они используются для ограничения данных, которые могут быть записаны в таблицу. Это обеспечивает точность и достоверность данных в БД.
Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.
Среди наиболее распространенных ограничений можно назвать следующие:
Любое ограничение может быть удалено с помощью команды ALTER TABLE и DROP CONSTRAINT + название ограничения. Некоторые реализации предоставляют сокращения для удаления ограничений и возможность отключать ограничения вместо их удаления.
Целостность данных
В каждой СУБД существуют следующие категории целостности данных:
Нормализация БД
Нормализация — это процесс эффективной организации данных в БД. Существует две главных причины, обуславливающих необходимость нормализации:
Нормализация предполагает соблюдение нескольких форм. Форма — это формат структурирования БД. Существует три главных формы: первая, вторая и, соответственно, третья. Я не буду вдаваться в подробности об этих формах, при желании, вы без труда найдете необходимую информацию.
Синтаксис SQL
Примеры синтаксиса
Типы данных
Каждая колонка, переменная и выражение в SQL имеют определенный тип данных (data type). Основные категории типов данных:
Точные числовые
Приблизительные числовые
| Тип данных | От | До |
|---|---|---|
| float | -1.79E + 308 | 1.79E + 308 |
| real | -3.40E + 38 | 3.40E + 38 |
Дата и время
| Тип данных | От | До |
|---|---|---|
| datetime | Jan 1, 1753 | Dec 31, 9999 |
| smalldatetime | Jan 1, 1900 | Jun 6, 2079 |
| date | Дата сохраняется в виде June 30, 1991 | |
| time | Время сохраняется в виде 12:30 P.M. |
Строковые символьные
| N | Тип данных | Описание |
|---|---|---|
| 1 | char | Строка длиной до 8,000 символов (не-юникод символы, фиксированной длины) |
| 2 | varchar | Строка длиной до 8,000 символов (не-юникод символы, переменной длины) |
| 3 | text | Не-юникод данные переменной длины, длиной до 2,147,483,647 символов |
Строковые символьные (юникод)
| N | Тип данных | Описание |
|---|---|---|
| 1 | nchar | Строка длиной до 4,000 символов (юникод символы, фиксированной длины) |
| 2 | nvarchar | Строка длиной до 4,000 символов (юникод символы, переменной длины) |
| 3 | ntext | Юникод данные переменной длины, длиной до 1,073,741,823 символов |
Бинарные
| N | Тип данных | Описание |
|---|---|---|
| 1 | binary | Данные размером до 8,000 байт (фиксированной длины) |
| 2 | varbinary | Данные размером до 8,000 байт (переменной длины) |
| 3 | image | Данные размером до 2,147,483,647 байт (переменной длины) |
Смешанные
| N | Тип данных | Описание |
|---|---|---|
| 1 | timestamp | Уникальные числа, обновляющиеся при каждом изменении строки |
| 2 | uniqueidentifier | Глобально-уникальный идентификатор (GUID) |
| 3 | cursor | Объект курсора |
| 4 | table | Промежуточный результат, предназначенный для дальнейшей обработки |
Операторы
Оператор (operators) — это ключевое слово или символ, которые, в основном, используются в инструкциях WHERE для выполнения каких-либо операций. Они используются как для определения условий, так и для объединения нескольких условий в инструкции.
Арифметические
| Оператор | Описание | Пример |
|---|---|---|
| + (сложение) | Сложение значений | a + b = 30 |
| — (вычитание) | Вычитание правого операнда из левого | b — a = 10 |
| * (умножение) | Умножение значений | a * b = 200 |
| / (деление) | Деление левого операнда на правый | b / a = 2 |
| % (деление с остатком/по модулю) | Деление левого операнда на правый с остатком (возвращается остаток) | b % a = 0 |
Операторы сравнения
Логические операторы
| N | Оператор | Описание |
|---|---|---|
| 1 | ALL | Сравнивает все значения |
| 2 | AND | Объединяет условия (все условия должны совпадать) |
| 3 | ANY | Сравнивает одно значение с другим, если последнее совпадает с условием |
| 4 | BETWEEN | Проверяет вхождение значения в диапазон от минимального до максимального |
| 5 | EXISTS | Определяет наличие строки, соответствующей определенному критерию |
| 6 | IN | Выполняет поиск значения в списке значений |
| 7 | LIKE | Сравнивает значение с похожими с помощью операторов подстановки |
| 8 | NOT | Инвертирует (меняет на противоположное) смысл других логических операторов, например, NOT EXISTS, NOT IN и т.д. |
| 9 | OR | Комбинирует условия (одно из условий должно совпадать) |
| 10 | IS NULL | Определяет, является ли значение нулевым |
| 11 | UNIQUE | Определяет уникальность строки |
Выражения
Выражение (expression) — это комбинация значений, операторов и функций для оценки (вычисления) значения. Выражения похожи на формулы, написанные на языке запросов. Они могут использоваться для извлечения из БД определенного набора данных.
Базовый синтаксис выражения выглядит так:
Существуют различные типы выражений: логические, числовые и выражения для работы с датами.
Логические
Логические выражения извлекают данные на основе совпадения с единичным значением.
Предположим, что в таблице users имеются следующие записи:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 25 | Moscow | active |
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 3 | Elena | 27 | Ekaterinburg | active |
| 4 | Oleg | 28 | Moscow | inactive |
Выполняем поиск активных пользователей:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 25 | Moscow | active |
| 3 | Elena | 27 | Ekaterinburg | active |
Числовые
Используются для выполнения арифметических операций в запросе.
Простой пример использования числового выражения:
Также существует несколько встроенных функций для работы со строками:
Выражения для работы с датами
Эти выражения, как правило, возвращают текущую дату и время.
Другие функции для получения текущей даты и времени:
Функции для разбора даты и времени:
Функции для манипулирования датами:
Создание БД
Условие IF NOT EXISTS позволяет избежать получения ошибки при попытке создания БД, которая уже существует.
Название БД должно быть уникальным в пределах СУБД.
Получаем список БД:
Удаление БД
Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.
Обратите внимание: при удалении БД уничтожаются все данные, которые в ней хранятся, так что будьте предельно внимательны при использовании данной команды.
Проверяем, что БД удалена:
Выбор БД
Создание таблицы
Проверяем, что таблица была создана:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| userId | int(11) | NO | PRI | ||
| userName | varchar(20) | NO | |||
| age | int(11) | NO | |||
| city | varchar(20) | NO | |||
| status | varchar(8) | YES | NULL |
Удаление таблицы
Обратите внимание: при удалении таблицы, навсегда удаляются все хранящиеся в ней данные, индексы, триггеры, ограничения и разрешения, так что будьте предельно внимательны при использовании данной команды.
Удаляем таблицу users :
Добавление колонок
Названия колонок можно не указывать, однако, в этом случае значения должны перечисляться в правильном порядке.
Во избежание ошибок, рекомендуется всегда перечислять названия колонок.
В таблицу можно добавлять несколько строк за один раз.
Также, как было отмечено, при добавлении строки названия полей можно опускать:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 25 | Moscow | active |
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 3 | Elena | 27 | Ekaterinburg | active |
| 4 | Oleg | 28 | Moscow | inactive |
Заполнение таблицы с помощью другой таблицы
Выборка полей
Для выборки всех полей используется такой синтаксис:
| userId | userName | age |
|---|---|---|
| 1 | Igor | 25 |
| 2 | Vika | 26 |
| 3 | Elena | 27 |
| 4 | Oleg | 28 |
Предложение WHERE
Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки ( » ), а числа, напротив, указываются как есть.
Операторы AND и OR
Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.
Возвращаемые записи должны удовлетворять всем указанным условиям.
Возвращаемые записи должны удовлетворять хотя бы одному условию.
Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:
Обновление полей
Обновим возраст пользователя с именем Igor :
Удаление записей
Удалим неактивных пользователей:
Предложения LIKE и REGEX
LIKE
Предложение LIKE используется для сравнения значений с помощью операторов с подстановочными знаками. Существует два вида таких операторов:
% означает 0, 1 или более символов. _ означает точно 1 символ.
| N | Инструкция | Результат |
|---|---|---|
| 1 | WHERE col LIKE ‘foo%’ | Любые значения, начинающиеся с foo |
| 2 | WHERE col LIKE ‘%foo%’ | Любые значения, содержащие foo |
| 3 | WHERE col LIKE ‘_oo%’ | Любые значения, содержащие oo на второй и третьей позициях |
| 4 | WHERE col LIKE ‘f%%’ | Любые значения, начинающиеся с f и состоящие как минимум из 1 символа |
| 5 | WHERE col LIKE ‘%oo’ | Любые значения, оканчивающиеся на oo |
| 6 | WHERE col LIKE ‘_o%o’ | Любые значения, содержащие o на второй позиции и оканчивающиеся на o |
| 7 | WHERE col LIKE ‘f_o’ | Любые значения, содержащие f и o на первой и третьей позициях, соответственно, и состоящие из трех символов |
Сделаем выборку неактивных пользователей:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 4 | Oleg | 28 | Moscow | inactive |
Сделаем выборку пользователей 30 лет и старше:
REGEX
Предложение REGEX позволяет определять регулярное выражение, которому должна соответствовать запись.
В регулярное выражении могут использоваться следующие специальные символы:
Сделаем выборку пользователей с именами Igor и Vika :
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 30 | Moscow | active |
| 2 | Vika | 26 | Ekaterinburg | inactive |
Предложение TOP / LIMIT / ROWNUM
Данные предложения позволяют извлекать указанное количество или процент записей с начала таблицы. Разные СУБД поддерживают разные предложения.
Сделаем выборку первых трех пользователей:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 30 | Moscow | active |
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 3 | Elena | 27 | Ekaterinburg | active |
Параметр offset (смещение) определяет количество пропускаемых записей. Например, так можно извлечь первых двух пользователей, начиная с третьего:
Предложения ORDER BY и GROUP BY
ORDER BY
Предложение ORDER BY используется для сортировки данных по возрастанию ( ASC ) или убыванию ( DESC ). Многие СУБД по умолчанию выполняют сортировку по возрастанию.
Обратите внимание: колонки для сортировки должны быть указаны в списке колонок для выборки.
Сделаем выборку пользователей, отсортировав их по городу и возрасту:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 2 | Vika | 26 | Ekaterinburg | inactive |
| 3 | Elena | 27 | Ekaterinburg | active |
| 1 | Igor | 25 | Moscow | active |
| 4 | Oleg | 28 | Moscow | inactive |
Теперь выполним сортировку по убыванию:
Определим собственный порядок сортировки по убыванию:
GROUP BY
Сгруппируем активных пользователей по городам:
Ключевое слово DISTINCT
Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).
Сделаем выборку городов проживания пользователей:
Соединения
Соединения (joins) используются для комбинации записей двух и более таблиц.
| orderId | date | userId | amount |
|---|---|---|---|
| 101 | 2021-06-21 00:00:00 | 2 | 3000 |
| 102 | 2021-06-20 00:00:00 | 2 | 1500 |
| 103 | 2021-06-19 00:00:00 | 3 | 2000 |
| 104 | 2021-06-18 00:00:00 | 3 | 1000 |
| userId | userName | age | amount |
|---|---|---|---|
| 2 | Vika | 26 | 3000 |
| 2 | Vika | 26 | 1500 |
| 3 | Elena | 27 | 2000 |
| 3 | Elena | 27 | 1000 |
Существуют разные типы объединений:
Предложение UNION
Однако, они могут быть разной длины.
Объединим наши таблицы users и orders :
| userId | userName | amount | date |
|---|---|---|---|
| 1 | Igor | NULL | NULL |
| 2 | Vika | 3000 | 2021-06-21 00:00:00 |
| 2 | Vika | 1500 | 2021-06-20 00:00:00 |
| 3 | Elena | 2000 | 2021-06-19 00:00:00 |
| 3 | Elena | 1000 | 2021-06-18 00:00:00 |
| 4 | Alex | NULL | NULL |
Предложение UNION ALL
Существует еще два предложения, похожих на UNION :
Синонимы
Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. «Временно» означает, что новое название используется только в текущем запросе, в БД название остается прежним.
Синтаксис синонима таблицы:
Синтаксис синонима колонки:
Пример использования синонимов таблиц:
| userId | userName | age | amount |
|---|---|---|---|
| 2 | Vika | 26 | 3000 |
| 2 | Vika | 26 | 1500 |
| 3 | Elena | 27 | 2000 |
| 3 | Elena | 27 | 1000 |
Пример использования синонимов колонок:
Индексы
Создание индексов
Индексы — это специальные поисковые таблицы (lookup tables), которые используются движком БД в целях более быстрого извлечения данных. Проще говоря, индекс — это указатель или ссылка на данные в таблице.
К индексам можно применять ограничение UNIQUE для того, чтобы обеспечить их уникальность.
Синтаксис создания индекса:
Синтаксис создания индекса для одной колонки:
Синтакис создания уникальных индексов (такие индексы используются не только для повышения производительности, но и для обеспечения согласованности данных):
Синтаксис создания индексов для нескольких колонок (композиционный индекс):
Решение о создании индексов для одной или нескольких колонок следует принимать на основе того, какие колонки будут часто использоваться в запросе WHERE в качестве условия для сортировки строк.
Для ограничений PRIMARY KEY и UNIQUE автоматически создаются неявные индексы.
Удаление индексов
Для удаления индексов используется инструкция DROP INDEX :
Несмотря на то, что индексы предназначены для повышения производительности БД, существуют ситуации, в которых их использования лучше избегать.
К таким ситуациям относится следующее:
Обновление таблицы
Команда ALTER TABLE используется для добавления, удаления и модификации колонок существующей таблицы. Также эта команда используется для добавления и удаления ограничений.
Добавляем в таблицу users новую колонку — пол пользователя:
Удаляем эту колонку:
Очистка таблицы
Команда TRUNCATE TABLE используется для очистки таблицы. Ее отличие от DROP TABLE состоит в том, что сохраняется структура таблицы ( DROP TABLE полностью удаляет таблицу и все ее данные).
Очищаем таблицу users :
Проверяем, что users пустая:
Представления
Представление (view) — это не что иное, как инструкция, записанная в БД под определенным названием. Другими словами, представление — это композиция таблицы в форме предварительно определенного запроса.
Представления могут содержать все или только некоторые строки таблицы. Представление может быть создано на основе одной или нескольких таблиц (это зависит от запроса для создания представления).
Представления — это виртутальные таблицы, позволяющие делать следующее:
Создание представления
Создаем представление для имен и возраста пользователей:
Получаем данные с помощью представления:
WITH CHECK OPTION
Если условие не удовлетворяется, выбрасывается исключение.
Обновление представления
Представление может быть обновлено при соблюдении следующих условий:
Пример обновления возраста пользователя с именем Igor в представлении:
Обратите внимание: обновление строки в представлении приводит к ее обновлению в базовой таблице.
С помощью команды DELETE можно удалять строки из представления.
Удаляем из представления пользователя, возраст которого составляет 26 лет:
Обратите внимание: удаление строки в представлении приводит к ее удалению в базовой таблице.
Удаление представления
Для удаления представления используется инструкция DROP VIEW :
Удаляем представление usersView :
HAVING
Транзакции
Транзакция — это единица работы или операции, выполняемой над БД. Это последовательность операций, выполняемых в логическом порядке. Эти операции могут запускаться как пользователем, так и какой-либо программой, функционирующей в БД.
Транзакция — это применение одного или более изменения к БД. Например, при создании/обновлении/удалении записи мы выполняем транзакцию. Важно контролировать выполнение таких операций в целях обеспечения согласованности данных и обработки возможных ошибок.
На практике, запросы, как правило, не отправляются в БД по одному, они группируются и выполняются как часть транзакции.
Свойства транзакции
Транзакции имеют 4 стандартных свойства (ACID):
Управление транзакцией
Для управления транзакцией используются следующие команды:
Удаляем пользователя, возраст которого составляет 26 лет, и отправляем изменения в БД:
Удаляем пользователя с именем Oleg и отменяем эту операцию:
Контрольные точки создаются с помощью такого синтаксиса:
Возврат к контрольной точке выполняется так:
Делаем выборку пользователей:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 31 | Moscow | active |
| 3 | Elena | 27 | Ekaterinburg | active |
| 4 | Oleg | 28 | Moscow | inactive |
Как видим, из таблицы был удален только пользователь с возрастом 26 лет.
Команда SET TRANSACTION используется для инициализации транзакции, т.е. начала ее выполнения. При этом, можно определять некоторые характеристики транзакции. Например, так можно определить уровень доступа транзакции (доступна только для чтения или для записи тоже):
Временные таблицы
Некоторые СУБД поддерживают так называемые временные таблицы (temporary tables). Такие таблицы позволяют хранить и обрабатывать промежуточные результаты с помощью таких же запросов, как и при работе с обычными таблицами.
Временные таблицы могут быть очень полезными при необходимости хранения временных данных. Одной из главных особенностей таких таблиц является то, что они удаляются по завершении текущей сессии. При запуске скрипта временная таблица удаляется после завершения выполнения этого скрипта. При доступе к БД с помощью клиентской программы, такая таблица будет удалена после закрытия этой программы.
Клонирование таблицы
Может возникнуть ситуация, когда потребуется получить точную копию существующей таблицы, а CREATE TABLE или SELECT окажется недостаточно в силу того, что мы хотим получить не только идентичную структуру, но также индексы, значения по умолчанию и т.д. копируемой таблицы.
Подзапросы
Подзапрос — это внутренний (вложенный) запрос другого запроса, встроенный (вставленный) с помощью WHERE или других инструкций.
Подзапрос используется для получения данных, которые будут использованы основным запросом в качестве условия для фильтрации возвращаемых записей.
Правила использования подзапросов:
| userId | userName | age | city | status |
|---|---|---|---|---|
| 1 | Igor | 30 | Moscow | active |
| 3 | Elena | 27 | Ekaterinburg | active |
Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.
Последовательности
Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.
Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:
Для того, чтобы заново пронумеровать строки с помощью автоматически генерируемых значений (например, при удалении большого количества строк), можно удалить колонку, содержащую такие значения и создать ее заново. Обратите внимание: такая таблица не должна быть частью объединения.
