Синтаксис оператора SELECT

Практическая работа №9

Тема: Запросы SQL как источник данных с сортировкой и условием одбора.

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

Ход работы

Синтаксис оператора SELECT

Предложение SELECT (выбрать) имеет следующий формат: подзапрос [UNION [ALL] подзапрос] ... [ORDER BY {[таблица.]столбец | номер_элемента_SELECT} [[ASC] | DESC] [,{[таблица.]столбец | номер_элемента_SELECT} [[ASC] | DESC]] ...;

и позволяет объединить (UNION) а затем упорядочить (ORDER BY) результаты выбора данных, полученных с помощью нескольких "подзапросов". При этом упорядочение можно производить в порядке возрастания - ASC (ASCending) или убывания DESC (DESCending), а по умолчанию принимается ASC.

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

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

FROM (из) перечисленных таблиц, в которых расположены эти столбцы

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

GROUP BY (группируя по) указанному перечню столбцов с тем, чтобы получить для каждой группы единственное агрегированное значение, используя во фразе SELECT SQL-функции SUM (сумма), COUNT (количество), MIN (минимальное значение), MAX (максимальное значение) или AVG (среднее значение)

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

SELECT [[ALL] | DISTINCT]{ * | элемент_SELECT [,элемент_SELECT] ...} FROM {базовая_таблица | представление} [псевдоним] [,{базовая_таблица | представление} [псевдоним]] ... [WHERE фраза] [GROUP BY фраза [HAVING фраза]];

Элемент_SELECT - это одна из следующих конструкций: [таблица.]* | значение | SQL_функция | системная_переменная

где значение – это: [таблица.]столбец | (выражение) | константа | переменная

Синтаксис выражений имеет вид ( {[ [+] | - ] {значение | функция_СУБД} [ + | - | * | ** ]}... )

а синтаксис SQL_функций – одна из следующих конструкций: {SUM|AVG|MIN|MAX|COUNT} ( [[ALL]|DISTINCT][таблица.]столбец ) {SUM|AVG|MIN|MAX|COUNT} ( [ALL] выражение ) COUNT(*)

Фраза WHERE включает набор условий для отбора строк: WHERE [NOT] WHERE_условие [[AND|OR][NOT] WHERE_условие]...

где WHERE_условие – одна из следующих конструкций: значение { = | <> | < | <= | > | >= } { значение | ( подзапрос ) } значение_1 [NOT] BETWEEN значение_2 AND значение_3 значение [NOT] IN { ( константа [,константа]... ) | ( подзапрос ) } значение IS [NOT] NULL [таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ'] EXISTS ( подзапрос )

Кроме традиционных операторов сравнения (= | <> | < | <= | > | >=) в WHERE фразе используются условия BETWEEN (между), LIKE (похоже на), IN (принадлежит), IS NULL (не определено) и EXISTS (существует), которые могут предваряться оператором NOT (не). Критерий отбора строк формируется из одного или нескольких условий, соединенных логическими операторами:

AND - когда должны удовлетворяться оба разделяемых с помощью AND условия;

OR - когда должно удовлетворяться одно из разделяемых с помощью OR условий;

AND NOT - когда должно удовлетворяться первое условие и не должно второе;

OR NOT - когда или должно удовлетворяться первое условие или не должно удовлетворяться второе,

причем существует приоритет AND над OR (сначала выполняются все операции AND и только после этого операции OR). Для получения желаемого результата WHERE условия должны быть введены в правильном порядке, который можно организовать введением скобок.

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

Наконец, синтаксис фразы GROUP BY имеет вид

GROUP BY [таблица.]столбец [,[таблица.]столбец] ... [HAVING фраза]

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

С помощью фразы HAVING (синтаксис которой почти не отличается от синтаксиса фразы WHERE)

HAVING [NOT] HAVING_условие [[AND|OR][NOT] HAVING_условие]...

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

значение { = | <> | < | <= | > | >= } { значение | ( подзапрос )

| SQL_функция }

{значение_1 | SQL_функция_1} [NOT] BETWEEN

{значение_2 | SQL_функция_2} AND {значение_3 | SQL_функция_3}

{значение | SQL_функция} [NOT] IN { ( константа [,константа]... )

| ( подзапрос ) }

{значение | SQL_функция} IS [NOT] NULL

[таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ']

EXISTS ( подзапрос )

Задание условий

1. Задание условия отбора (выбрать поставщиков, в названии которых есть сокращение ООО, ОАО или ЗАО).

Для задания условия отбора используется фраза WHERE

В запросе используется условие Like для отбора части значения поля, символ * в данном контексте соответствует любому количестве цифр или символов

SELECT Поставщик

FROM Поставщики

WHERE Поставщик Like '*ООО*' Or Поставщик Like '*ОАО*' Or Поставщик Like '*ЗАО*';

Результат запроса приведен на Рис. 3-11, в.

2. Задание диапазонов в запросах (выбрать заказы за период с 1.02.04 по 31.03.04). Диапазон можно задать, используя конструкцию Between … And … (находится в интервале от … до …)

SELECT ДатаЗаказа, КодПост, КодПрод, КоличестовЗ

FROM Заказы

WHERE ДатаЗаказа Between #2-1-2004# And #3-31-2004#;

Результат запроса приведен на Рис. 4-1.

Вычисляемые поля

1. Переименование полей Иногда для удобства работы требуется переименовать некоторые поля в запросе (например, при наличии одноименных полей в разных таблицах). Выдать список всех поставленных продуктов (кодов продуктов и названий продуктов) без повторений.

SELECT DISTINCT Поставки.КодПрод AS КодПоставленногоПродукта, Продукт

FROM Поставки, Продукты

WHERE Поставки.КодПрод = Продукты.КодПрод;

Результат запроса приведен на Рис. 4-2.

2. Выборка вычисляемых значений

Выдать список поставленных продуктов (дата поставки, продукт). Вычислить дату окончания срока хранения продуктов. Отсортировать полученный результат по дате поставки.

В БД «Магазин» хранятся: дата изготовления продукта (поле ДатаИзгот таблицы Поставки) и срок хранения продукта (в днях) (поле СрокХран(дней) таблицы Продукты). Если к дате прибавить количество дней, то получится новая дата.

SELECT ДатаПоставки, Продукт, [ДатаИзгот] + [СрокХран(дней)] AS ДатаОкончХран

FROM Продукты, Поставки

WHERE Продукты.КодПрод = Поставки.КодПрод

ORDER BY ДатаПоставки;

SQL-функции

В SQL существует ряд специальных стандартных функций (SQL-функций). Кроме специального случая COUNT(*) каждая из этих функций оперирует совокупностью значений поля некоторой таблицы и создает единственное значение, определяемое так:

• COUNT – подсчет количества записей, содержащихся в заданном поле запроса

• SUM – вычисление суммы набора значений, содержащихся в заданном поле запроса

• AVG – вычисление арифметического среднего набора чисел, содержащихся в указанном поле запроса

• MAX – вычисление максимального значения из набора значений, содержащихся в указанном поле запроса

• MIN – вычисление минимального значения из набора значений, содержащихся в указанном поле запроса

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

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

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

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

1. Посчитать количество поставщиков

SELECT Count(*)AS Количество

FROM Поставщики;

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

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

2. Выдать данные о массе творога (КодПрод=8), поставленного поставщиками, и указать количество этих поставок

SELECT Sum(КоличестовП) AS Вес, Count(КоличестовП) AS Количество

FROM Поставки

WHERE КодПрод = 8;

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

3. Рассчитать ежедневную сумму продаж продуктов и вес проданных продуктов

SELECT ДатаПродажи, Sum([Количество]*[ЦенаПродажи]) AS СуммаПродажи

FROM Продажи

GROUP BY ДатаПродажи;

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

Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в поле, указанном в GROUP BY. В рассматриваемом примере записи таблицы Продажи группируются так, что в одной группе содержатся все записи с датой продажи ДатаПродажи = 25.03.03, в другой с датой продажи ДатаПродажи = 02.01.04 и т.д. (см. Таблицу 3-6). Далее к каждой группе применяется фраза SELECT. Каждое выражение в этой фразе должно принимать единственное значение для группы, т.е. оно может быть либо значением поля, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой, либо одной из SQL-функций, которая оперирует всеми значениями поля в группе и сводит эти значения к единственному значению (например, к сумме).

Письменно ответить на вопросы:

1. Какие возможности предоставляет инструкция SELECT для выборки данных по заданным условиям?

2. Как задать диапазон данных для выборки?

3. Как организовать вычисления в полях с помощью запросов?

4. Какие функции SQL можно применять для вычислений?

Сделать вывод по проделанной работе.