Разработка урока: Язык SQL. Основные понятия

Выполнение математических операций

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

SELECT DISTINCT Product, Amount/Quantity FROM Sumproduct

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

Использование псевдонимов

В предыдущем примере мы рассчитывали среднюю стоимость покупки каждого товара и отобразили значение в расчетном столбце. Однако в дальнейшем, нам неудобно обращаться к этому полю, так как его название является неинформативным для нас (СУБД дала название полю - Expr1001). Однако мы можем назвать поле самостоятельно, заранее указав его название в запросе, то есть дать псевдоним. Давайте перепишем предыдущий пример и укажем псевдонима для расчетного поля:

SELECT DISTINCT Product, Amount/Quantity AS AvgPrice FROM Sumproduct

Видим, наше расчетное поле получило собственное название AvgPrice. Для этого мы использовали оператор AS, после которого указали необходимое нам название. Стоит отметить, что в SQL поддерживаются только основные математические операции: сложение (+), вычитание (-), умножение (*), деление (/). Также для изменения очередности выполнения операции можно использовать круглые скобки.

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

Соединение полей (конкатенация)

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

SELECT Month + ' ' + Product AS NewField, Quantity FROM Sumproduct

В этом примере мы соединили значение в двух столбцах и вывели результат в новое поле NewField.

Функции обработки данных

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

Обычно СУБД поддерживается стандартный набор типов функций, а именно:

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

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

· Функции даты и времени (осуществляют манипулирования датой и временем, рассчитывают период между датами, проверяют даты на корректность и т.п.)

· Статистические функции (для вычисления максимальных /минимальных значений, средних значений, подсчет количества и суммы…)

· Системные функции (предоставляют разного рода служебную информацию о СУБД, пользователе и др.).

Функции SQL для обработки текста

Реализация SQL в СУБД Access имеет следующие функции для обработки текста:

Переведем названия товаров в верхний регистр с помощью функции UCase():

SELECT Product, UCase(Product) AS Product_UCase FROM Sumproduct

Выделим первые три символа в тексте с помощью функции LEFT():

SELECT Product, LEFT (Product, 3) AS Product_LEFT FROM Sumproduct

Функции SQL для обработки чисел

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

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

Например, напишем запрос для получения корня квадратного для чисел в столбце Amount с помощью функции SQR():

SELECT Amount, SQR(Amount) AS Amount_SQR FROM Sumproduct

Функции SQL для обработки даты и времени

Функции манипулирования датой и временем являются одними из важнейших и часто используемых функций SQL. В базах данных значения дат и времени хранятся в специальном формате, поэтому их невозможно использовать напрямую без дополнительной обработки. Каждая СУБД имеет свой набор функций для обработки дат, что, к сожалению, не позволяет переносить их на другие платформы и реализации SQL.

Список некоторых функций для обработки даты и времени в СУБД Access:

Посмотрим на примере как работает функция DatePart():

SELECT Date1, DatePart («m», Date1) AS Month1 FROM Sumproduct

Функция DatePart () имеет дополнительный параметр, который нам позволяет отобразить необходимую часть даты. В примере мы использовали параметр «m», который отображает номер месяца (таким же образом мы можем отразить год - «yyyy», квартал - «q», день -» d», неделю -» w», час -» h», минуты - «n», секунды - «s» и т.д.).

Статистические функции SQL

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

Список статистических функций в СУБД Access

Примеры использования функции COUNT():

SELECT COUNT(*) AS Count1 FROM Sumproduct - возвращает количество всех строк в таблице

SELECT COUNT(Product) AS Count2 FROM Sumproduct - возвращаетколичествовсехнепустыхстроквполе Product

Мы намеренно удалили одно значение в столбце Product, чтобы показать разницу в работе двух запросов.

Примеры использования функции SUM():

SELECT SUM(Quantity) AS Sum1 FROM Sumproduct WHERE Month = 'April'

Данным запросу мы отразили общее количество проданного товара в апреле.

SELECT SUM (Quantity*Amount) AS Sum2 FROM Sumproduct

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

Пример использования функции MIN():

SELECT MIN(Amount) AS Min1 FROM Sumproduct

Пример использования функции MAX():

SELECT MAX(Amount) AS Max1 FROM Sumproduct

Пример использования функции AVG():

SELECT AVG(Amount) AS Avg1 FROM Sumproduct

Группировка данных (GROUP BY)

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

Создание групп (GROUP BY)

Группы создаются с помощью предложения GROUP BY оператора SELECT. Рассмотрим на примере.

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product

Данным запросом мы извлекли информацию о количестве реализованной продукции в каждом месяце. Оператор SELECT приказывает вывести два столбца Product - название продукта и Product_num - расчетное поле, которое мы создали для отображения количества реализованной продукции (формула поля SUM (Quantity)). Предложение GROUP BY указывает СУБД сгруппировать данные по столбцу Product. Стоит также отметить, что GROUP BY должен идти после предложения WHERE и перед ORDER BY.

Фильтрующие группы (HAVING)

Так же, как мы фильтровали строки в таблице, мы можем осуществлять фильтрацию по сгруппированным данным. Для этого в SQL существует оператор HAVING. Возьмем предыдущий пример и добавим фильтрацию по группам.

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>4000

Видим, что после того, как была посчитана количество реализованного товара в разрезе каждого продукта, СУБД «отсекла» те продукты, которых было реализовано меньше 4000 шт.

Как видим, оператор HAVING очень похож на оператора WHERE, однако между собой они имеют существенное отличие: WHERE фильтрует данные до того, как они будут сгруппированы, а HAVING - осуществляет фильтрацию после группировки. Таким образом, строки, которые были изъяты предложением WHERE НЕ будут включены в группу. Итак, операторы WHERE и HAVING могут использоваться в одном предложении. Рассмотрим пример:

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct WHERE Product<>'Skis Long' GROUP BY Product HAVING SUM(Quantity)>4000

Мы к предыдущему примеру добавили оператор WHERE, где указали товар SkisLong, что в свою очередь повлияло на группирование оператором HAVING. Как результат видим, что товар SkisLong не попал в перечень групп с количеством реализованной продукции больше 4000 шт.

Группировка и сортировка

Как и при обычной выборке данных, мы можем сортировать группы после группировки оператором HAVING. Для этого мы можем использовать уже знакомый нам оператор ORDER BY. В данной ситуации его применения аналогичное предыдущим примерам. К примеру:

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>3000 ORDER BY SUM(Quantity) или просто укажем номер поля по порядку, по которому хотим сортировать:

SELECT Product, SUM(Quantity) AS Product_num FROM Sumproduct GROUP BY Product HAVING SUM(Quantity)>3000 ORDER BY 2

Видим, что для сортировки сводных результатов нам нужно просто прописать предложения с ORDER BY после оператора HAVING. Однако есть один нюанс. СУБД Access не поддерживает сортировку групп по псевдонимами колонок, то есть в нашем примере, чтобы сортировать значения, мы не сможем в конце запроса прописать ORDER BY Product_num.

Подзапросы

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

Фильтрация с помощью подзапросов

Таблицы баз данных, которые используются в СУБД Access являются реляционными таблицами, т.е. все таблицы можно связать между собой по общим полям. Допустим у нас хранятся данные в двух разных таблицах и нам нужно выбрать данные в одной из них, в зависимости от того, какие данные в другой. Для этого создадим еще одну таблицу в нашей базе данных. Это будет, например, таблица Sellers с информацией о поставщиках:

Теперь мы имеем две таблицы - Sumproduct и Sellers, которые имеют одинаковое поле City. Предположим, нам нужно посчитать сколько товаров было продано только в Канаде. Сделать это нам помогут подзапросы. Итак, сначала напишем запрос для выборки городов, которые находятся в Канаде:

SELECT City FROM Sellers WHERE Country = 'Canada'

Теперь передадим эти данные в следующий запрос, который будет выбирать данные из таблицы Sumproduct:

SELECT SUM(Quantity) AS Qty_Canada FROM Sumproduct WHERE City IN ('Montreal', 'Toronto')

Также мы можем объединить эти два запроса в один. Таким образом, один запрос, который выводит данные будет главным, а второй запрос, которий передает входные данные, будет вспомогательным (подзапросом). Для вложения подзапроса используем конструкцию WHERE… IN (…), о которой говорилось в разделе Расширенное фильтрование: