Организация Web-доступа к базам данных с использованием SQL-запросов

|15|2. |170 |340. |15|2. |170 |340. |

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

С помощью BETWEEN … AND … (находится в интервале от … до …) можно

отобрать строки, в которых значение какого-либо столбца находятся в

заданном диапазоне.

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

белка находится в диапазоне от 10 до 50:

| |Результат: | |

| | | |

|SELECT Продукт, | | |

|Белки | | |

|FROM Продукты | | |

|WHERE Белки | | |

|BETWEEN 10 AND | | |

|50; | | |

| |Продукт |Белки |

| |Майонез |31. |

| |Сметана |26. |

| |Молоко |28. |

| |Морковь |13. |

| |Лук |17. |

Можно задать и NOT BETWEEN (не принадлежит диапазону между), например:

| |Результа| |

| |т: | |

| | | |

|SELECT Продукт, | | |

|Белки, Жиры | | |

|FROM Продукты | | |

|WHERE Белки NOT | | |

|BETWEEN 10 AND 50| | |

| | | |

|AND Жиры 100; | | |

| |Продукт |Белки |Жиры |

| |Говядина|189. |124. |

| |Масло |60. |825. |

|Яйца |127. |115. | |

BETWEEN особенно удобен при работе с данными, задаваемыми интервалами,

начало и конец которых расположен в разных столбцах.

Для примера воспользуемся таблицей «минимальных окладов» (табл. 2.4),

величина которых непосредственно связана со студенческой стипендией. В этой

таблице для текущего значения минимального оклада установлена запредельная

дата окончания 9 сентября 9999 года.

|Миноклад |Начало |Конец |

|2250 |01-01-1993 |31-03-1993 |

|4275 |01-04-1993 |30-06-1993 |

|7740 |01-07-1993 |30-11-1993 |

|14620 |01-12-1993 |30-06-1994 |

|20500 |01-07-1994 |09-09-9999 |

Рисунок 2.4

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

окладов производились в 1993/94 учебном году, то можно выдать запрос

SELECT Начало, Миноклад

FROM Миноклады

WHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'

и получить результат:

|Начало |Миноклад |

|01-12-1993 |14620 |

|01-07-1994 |20500 |

Отметим, что при формировании запросов значения дат следует заключать

в апострофы, чтобы СУБД не путала их с выражениями и не пыталась вычитать

из 31 значение 8, а затем 1994.

Для выявления всех значений минимальных окладов, которые существовали

в 1993/94 учебном году, можно сформировать запрос

SELECT *

FROM Миноклады

WHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'

OR Конец BETWEEN '1-9-1993' AND '31-8-1994'

|Миноклад |Начало |Конец |

|7740 |01/07/1993|30/11/1993 |

|14620 |01/12/1993|30/06/1994 |

|20500 |01/07/1994|09/09/9999 |

Наконец, для получения минимального оклада на 15-5-1994:

| |Результат: | |

| |Миноклад |

|SELECT Миноклад | |

|FROM Миноклады | |

|WHERE '15-05-1994' | |

|BETWEEN Начало AND | |

|Конец | |

| |14620 |

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

Выдать сведения о блюдах на основе яиц, крупы и овощей

SELECT *

FROM Блюда

WHERE Основа IN (Яйца Крупа Овощи);

Результат:

|БЛ|Блюдо |В|Основа |Выход |Труд |

|1 |Салат летний |З|Овощи |200. |3 |

|3 |Салат витаминный |З|Овощи |200. |4 |

|16|Драчена |Г|Яйца |180. |4 |

|17|Морковь с рисом |Г|Овощи |260. |3 |

|19|Омлет с луком |Г|Яйца |200. |5 |

|20|Каша рисовая |Г|Крупа |210. |4 |

|21|Пудинг рисовый |Г|Крупа |160. |6 |

|23|Помидоры с луком |Г|Овощи |260. |4 |

Рассмотренная форма IN является в действительности просто краткой

записью последовательности отдельных сравнений, соединенных операторами OR.

Предыдущее предложение эквивалентно такому:

SELECT *

FROM Блюда

WHERE Основа=Яйца OR Основа=Крупа OR Основа=Овощи;

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

Выдать перечень салатов

| |Результат: | |

| |Блюдо |

|SELECT Блюдо | |

|FROM Блюда | |

|WHERE Блюдо LIKE | |

|'Салат%'; | |

| |Салат летний |

| |Салат мясной |

| |Салат витаминный |

| |Салат рыбный |

Обычная форма «имя_столбца LIKE текстовая_константа» для столбца

текстового типа позволяет отыскать все значения указанного столбца,

соответствующие образцу, заданному «текстовой_константой». Символы этой

константы интерпретируются следующим образом:

символ _ (подчеркивание) – заменяет любой одиночный символ,

символ % (процент) – заменяет любую последовательность из N символов (где N

может быть нулем),

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

Следовательно, в приведенном примере SELECT будет осуществлять выборку

записей из таблицы Блюда, для которых значение в столбце Блюдо начинается

сочетанием 'Салат' и содержит любую последовательность из нуля или более

символов, следующих за сочетанием 'Салат'. Если бы среди блюд были «Луковый

салат», «Фруктовый салат» и т.п., то они не были бы найдены. Для их

отыскания надо изменить фразу WHERE:

WHERE Блюдо LIKE '%салат%'

или при отсутствии различий между малыми и большими буквами (такую

настройку допускают некоторые СУБД):

WHERE Блюдо LIKE '%Салат%'

Это позволит отыскать все салаты.

Вовлечение неопределенного значения (NULL-значения)

Если при загрузке данных не введено значение в какое-либо поле

таблицы, то СУБД поместит в него NULL-значение. Аналогичное значение можно

ввести в поле таблицы, выполняя операцию изменения данных. Так, при

отсутствии сведений о наличии у поставщиков судака и моркови в столбцы Цена

и К_во соответствующих строк таблицы Поставки вводится NULL и там будет

храниться код NULL-значения, а не 0, 0. Или пробел. (Отметим, что в

распечатке таблицы Поставки в этих местах расположен пробел, установленный

в СУБД для представления NULL-значения при выводе на печать).

В этом случае для выявления названий продуктов, отсутствующих в кладовой,

шеф-повар может дать запрос

|Результат: |ПР |

|S| |2 |

|E| |9 |

|L| | |

|E| | |

|C| | |

|T| | |

|D| | |

|I| | |

|S| | |

|T| | |

|I| | |

|N| | |

|C| | |

|T| | |

|П| | |

|Р| | |

| | | |

|F| | |

|R| | |

|O| | |

|M| | |

|Н| | |

|а| | |

|л| | |

|и| | |

|ч| | |

|и| | |

|е| | |

| | | |

|W| | |

|H| | |

|E| | |

|R| | |

|E| | |

|К| | |

|_| | |

|в| | |

|о| | |

|I| | |

|S| | |

|N| | |

|U| | |

|L| | |

|L| | |

|;| | |

Естественно, что для выявления продуктов, существующих в кладовой,

следует дать запрос

SELECT DISTINCT ПР

FROM Наличие

WHERE К_во IS NOT NULL;

Использование условий

столбец IS NULL и столбец IS NOT NULL

вместо, например,

столбец = NULL и столбец < NULL

связано с тем, что ничто – и даже само NULL-значение – не считается равным

другому NULL-значению. (Несмотря на это, два неопределенных значения

рассматриваются, однако, как дубликаты друг друга при исключении

дубликатов, и предложение SELECT DISTINCT даст в результате не более одного

NULL-значения.)

Выборка с упорядочением

Простейший вариант этой фразы – упорядочение строк результата по

значению одного из столбцов с указанием порядка сортировки или без такого

указания. (По умолчанию строки будут сортироваться в порядке возрастания

значений в указанном столбце.)

Например, выдать перечень продуктов и содержание в них основных

веществ в порядке убывания содержания белка

| |Продукт|Белки |Жиры |Углев |

|SELECT Продукт,| | | | |

|Белки, Жиры, | | | | |

|Углев | | | | |

|FROM Продукты | | | | |

|ORDER BY Белки | | | | |

|DESC; | | | | |

| |Судак |190. |80. |0. |

| |Говядин|189. |124. |0. |

| |а | | | |

| |Творог |167. |90. |13. |

| |Яйца |127. |115. |7. |

| |Кофе |127. |36. |9. |

| |Мука |106. |13. |732. |

При включении в список ORDER BY нескольких столбцов СУБД сортирует

строки результата по значениям первого столбца списка пока не появится

несколько строк с одинаковыми значениями данных в этом столбце. Такие

строки сортируются по значениям следующего столбца из списка ORDER BY и

т.д.

Например, выдать содержимое таблицы Блюда, отсортировав ее строки по

видам блюд и основе:

| |Результат: | | | |

|SELECT * |БЛ|Блюдо |В|Основа|Выход|Труд |

|FROM Блюда | | | | | | |

|ORDER BY В | | | | | | |

|Основа; | | | | | | |

| |21|Пудинг рисовый |Г|Крупа |160. |6 |

| |20|Каша рисовая |Г|Крупа |210. |4 |

| |18|Сырники |Г|Молоко|220. |4 |

| |. | | | | | |

| |. | | | | | |

| |. | | | | | |

| |16|Драчена |Г|Яйца |180. |4 |

| |28|Крем творожный |Д|Молоко|160. |4 |

| |. | | | | | |

| |. | | | | | |

| |. | | | | | |

| |26|Яблоки печеные |Д|Фрукты|160. |3 |

| |7 |Сметана |З|Молоко|140. |1 |

| |8 |Творог |З|Молоко|140. |2 |

| |2 |Салат мясной |З|Мясо |200. |4 |

| |6 |Мясо с гарниром|З|Мясо |250. |3 |

| |1 |Салат летний |З|Овощи |200. |3 |

| |. | | | | | |

| |. | | | | | |

| |. | | | | | |

Кроме того, в список ORDER BY можно включать не только имя столбца, а

его порядковую позицию в перечне SELECT. Благодаря этому возможно

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

Например, запрос

SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)

FROM Продукты

ORDER BY 2;

позволит получить список продуктов, показанный на рис.2.3,в –

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

Агрегирование данных

SQL-функции

В SQL существует ряд специальных стандартных функций (SQL-функций).

Кроме специального случая COUNT(*) каждая из этих функций оперирует

совокупностью значений столбца некоторой таблицы и создает единственное

значение, определяемое так:

COUNT

5. число значений в столбце,

SUM

6. сумма значений в столбце,

AVG

7. среднее значение в столбце,

MAX

8. самое большое значение в столбце,

MIN

9. самое малое значение в столбце.

Для функций SUM и AVG рассматриваемый столбец должен содержать числовые

значения.

Следует отметить, что здесь столбец – это столбец виртуальной таблицы, в

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

данные, полученные путем функционального преобразования и (или) связывания

символами арифметических операций значений из одного или нескольких

столбцов. При этом выражение, определяющее столбец такой таблицы, может

быть сколь угодно сложным, но не должно содержать SQL-функций (вложенность

SQL-функций не допускается). Однако из SQL-функций можно составлять любые

выражения.

Аргументу всех функций, кроме COUNT(*), может предшествовать ключевое

слово DISTINCT (различный), указывающее, что избыточные дублирующие

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

Специальная же функция COUNT(*) служит для подсчета всех без исключения

строк в таблице (включая дубликаты).

Функции без использования фразы GROUP BY

Если не используется фраза GROUP BY, то в перечень элементов_SELECT

можно включать лишь SQL-функции или выражения, содержащие такие функции.

Другими словами, нельзя иметь в списке столбцы, не являющихся аргументами

SQL-функций.

Например, выдать данные о массе лука (ПР=10), проданного поставщиками,

и указать количество этих поставщиков:

| |Резул| |

| |ьтат:| |

| | | |

|SELECT SUM(К_во),COUNT(К_во) | | |

| | | |

|FROM Поставки | | |

|WHERE ПР = 10; | | |

| |SUM(К|COUNT(К_во) |

| |_во) | |

| |220 |2 |

Если бы для вывода в результат еще и номера продукта был сформирован

запрос

SELECT ПР,SUM(К_во),COUNT(К_во)

FROM Поставки

WHERE ПР = 10;

то было бы получено сообщение об ошибке. Это связано с тем, что SQL-функция

создает единственное значение из множества значений столбца-аргумента, а

для «свободного» столбца должно быть выдано все множество его значений. Без

специального указания (оно задается фразой GROUP BY) SQL не будет выяснять,

одинаковы значения этого множества (как в данном примере, где ПР=10) или

различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос

отвергается системой.

Правда, никто не запрещает дать запрос

SELECT 'Кол-во лука =',SUM(К_во),COUNT(К_во)

FROM Поставки

WHERE ПР = 10;

|Результат: | | |

|'Кол-во лука =' |SUM(К_во) |COUNT(К_во) |

|Кол-во лука = |220 |2 |

Отметим также, что в столбце-аргументе перед применением любой

функции, кроме COUNT(*), исключаются все неопределенные значения. Если

оказывается, что аргумент – пустое множество, функция COUNT принимает

Страницы: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12