Команда SELECT – SQL

Конструктор запросов представляет собой визуальный построитель выражения команды выборки. Разработчик может в любой момент из конструктора представления данных посмотреть его программный эквивалент, используя соответствующий управляющий элемент на панели инструментов View Designer(рис.18).

Рис. 18 Панель View Designer

По нажатию на элемент просмотра SQL на экране появляется стандартное окно редактирования текстовых выражений, в котором отображается сформированный конструктором представлений программный код, реализующий работу представления данных. В самом начале всегда располагается конструкция SELECT – SQL, в которой включены только те опциональные части, которые были определены разработчиком в конструкторе представления. После оператора выборки располагается большой блок команд DBSetProp. Эта часть программного представления отвечает за настройку обратной связи данных, т.е. за то, чтобы редактирование данных в представлении отображалось в исходных таблицах. Таким образом, представление данных состоит из двух логических частей: собственно самой выборки данных и блока обратной связи.

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

Вторым (рекомендуемым) способом является явное формирование выражения оператора выборки. Полученное выражение может располагаться в любом допустимом месте: окне команд, программном модуле, в коде событий и методов элементов форм и т.п.

Синтаксис команды SELECT – SQL представляет довольно сложную конструкцию:

SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]] Select_List_Item [, ...]

FROM [FORCE] Table_List_Item [, ...]

[[JoinType] JOIN DatabaseName!]Table [[AS] Local_Alias]

[ON JoinCondition [AND | OR [JoinCondition | FilterCondition] ...]

[WITH (BUFFERING = lExpr)]

[WHERE JoinCondition | FilterCondition [AND | OR JoinCondition | FilterCondition] ...]

[GROUP BY Column_List_Item [, ...]] [HAVING FilterCondition [AND | OR ...]]

[UNION [ALL] SELECTCommand]

[ORDER BY Order_Item [ASC | DESC] [, ...]]

[INTO StorageDestination | TO DisplayDestination]

[PREFERENCE PreferenceName] [NOCONSOLE] [PLAIN] [NOWAIT]

однако обязательными являются только те элементы, которые не включены в квадратные скобки []. Таким образом, минимальная конструкция оператора выборки выглядит следующим образом:

SELECT Select_List_Item FROM Table_List_Item

В этом выражении Select_List_Item обозначает список имен полей, разделенных запятой, которые будут входить в результирующую выборку, а Table_List_Item – список открытых таблиц, из которых будет производиться выборка. Если в результате необходим полный набор полей, допускается использование служебного символа *.

Например:

SELECT cnmGoods FROM Goods

по этой команде будет создана выборка данных из таблицы GOODS, включающая в себя все значения поля cnmGoods. Выборка будет автоматически открыта в окне табличного просмотра BROWSE.

SELECT icdGoods, cnmGoods, cnmGoodsGrp, nUnitPrice FROM Goods

по этой команде в результат выборки будут помещены значения всех полей из таблицы GOODS. Обратите внимание, что эквивалентной является команда SELECT * FROM Goods.

Примечательно, что в результате команды выборки может нарушаться требование наличия первичного ключа для реляционных таблиц. То есть требование нарушается в логическом плане, т.к. пользователь видит набор повторяющихся записей, но в физическом плане сохраняется, т.к. в системе VFP каждая запись обладает своим уникальным, невидимым пользователем номером. Для ликвидации логического нарушения используется служебное слово DISTINCT.

Например, по команде SELECT cnmGoddsGrp From Goods пользователь получит полный список значений выражения поля cnmGoodsGrp. Причем количество записей в выборке будет совпадать с количеством записей в исходной таблице Goods, т.е. если было 50 товаров, принадлежащих одной группе, то в результате будут 50 одинаковых записей групп, что само по себе не имеет глубокого смысла. Команда SELECT DISTINCT cnmGoddsGrp From Goods в результате выдаст только неповторяющиеся записи, не зависимо от того, сколько раз значение встречается в исходной таблице, что приведет к формированию полного списка всех групп товаров, что уже является достаточно осмысленным. Полученный список групп удобно использовать как источник данных для элементов выбора на формах ввода.

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

JOIN – служебное слово, позволяющее произвести соединение данных нескольких таблиц. Перед ним необходимо указать тип соединения (INNER, LEFT, RIGHT, FULL). Допускается указывать только один тип соединения. После служебного слова ON указывается условие соединения. Т.к. выборка в данном случае проводится из нескольких таблиц, перед именами полей следует указать имя таблицы через точку (ИмяТаблицы . ИмяПоля). Допускается указывать несколько условий, разделенных логическими операторами.

Например:

SELECT * FROM Customer LEFT JOIN Phoncust ON Customer.icdCustomer=Phoncust.icdCustomer

по этой команде пользователь получит полный набор данных о заказчиках и их телефонах, если у заказчика нет телефона, недостающие поля будут заполнены пустым значением NULL. Изменением типа соединения на RIGHT можно добиться результата, когда будет выдана информация только о тех заказчиках, у которых ЕСТЬ телефоны (SELECT * FROM Customer RIGHT JOIN Phoncust ON Customer.icdCustomer=Phoncust.icdCustomer).

WHERE – служебное слово, позволяющее указать условие, налагаемое на результат выборки. В принципе его можно использовать как эквивалент выражения соединения, но это не рекомендуется, т.к. при сложных выражениях можно получить неверный результат.

Например:

SELECT * FROM Customer WHERE cCity=”Рыбница”

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

Команда SELECT * FROM Customer WHERE cCity=”Рыбница” AND cCity=”Резина” выдаст вообще пустой результат, т.к. по логике должны быть выбраны заказчики, проживающие в двух городах сразу. Для выбора заказчиков из двух городов в данном случае следовало использовать логический оператор OR (SELECT * FROM Customer WHERE cCity=”Рыбница” OR cCity=”Резина”).

Строго говоря, логический оператор AND, при указании условия для одного поля, можно использовать для указания диапазона значений. Команда SELECT * FROM Goods WHERE nUnitPrice>=10 AND nUnitPrice<100 выберет только те товары, цены на которые входят в диапазон [10, 100). Обратите внимание, что товар с ценой 10 попадет в результат выборки, а товар с ценой 100 – нет.

GROUP BY – служебное слово, позволяющее указать группировку данных. В основном используется при работе с вычисляемыми полями. Можно указать несколько полей через запятую, по которым будет производиться группировка данных, при этом порядок группировки будет соответствовать указанному списку полей.

ORDER BY – позволяет указать условие сортировки результата выборки. Служебные поля ASC и DESC служат для определения сортировки данных.

Например:

SELECT DISTINCT cCity FROMCustomer ORDER BY cCity ASC

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

SELECT * FROM Goods WHERE cnmGoodsGrp=”Мебель” ORDER BY nUnitPrice DESC

выдаст товары из таблицы Goods, имеющие группу «Мебель», расположенные в порядке убывания цены.

INTO – служебное слово, позволяющее указать место назначения результата выборки. Местом назначения может быть массив, курсор или таблица (ARRAY, CURSOR, TABLE). Курсор в данном случае представляет собой временную таблицу в оперативной памяти, открытую в уникальной рабочей области. С курсором допускается проводить все операции, применимые к таблицам.

В случае использование служебного слова TO можно указать в качестве места назначения файл, принтер или экран (FILE, PRINTER, SCREEN).

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

При использовании команды выборки система VFP позволяет применять агрегирующие функции для организации работы с вычисляемыми полями. Выражение вычисляемого поля должно быть указано в основном списке полей. Можно использовать служебное слово AS для указания уникального имени вычисляемого поля в результирующей выборке.

Например:

SELECT cnmGoodsGrp, COUNT(icdGoods) AS Kolvo FROM Goods GROUP BY cnmGoodsGrp

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

SELECT Ordsaled.icdOrder, Ordsaled.icdGoods, (nOrderQuant*nUnitPrice) AS S1 FROM Ordsaled LEFT JOIN Goods ON Ordsaled.icdGoods=Goods.icdGoods

по этой команде результирующая выборка будет содержать информацию о номере заказа, номере товара и общей сумме за товар в данном заказе. Общая сумма будет помещена в поле с именем S1.

Запомните:

1. Оператор SELECT – SQL замкнут относительно понятия таблицы, т.е. операции проводятся над таблицами и результатом является тоже таблица. Таким образом, допускается использование многократно вложенных операторов SELECT, когда выражение одного оператора входит в качестве источника или условия для другого оператора. Однако рекомендуется избегать вложенности по нескольким причинам, а именно: многократная вложенность существенно усложняет «читаемость» программного кода. В промышленных системах размеры сложного оператора SELECT могут превышать несколько экранных страниц, при этом очень сложно отследить логику запроса (даже если текст снабжен подробными комментариями). Решением данной проблемы могут служить несколько последовательных операторов выборки, когда результат предыдущего оператора в явном виде указан в следующем. Дополнительным удобством нескольких последовательных операторов можно считать возможный контроль промежуточных результатов, что позволяет отслеживать возможные ошибки вычислений. Единственным недостатком можно считать множество открываемых курсоров, которые требуется отслеживать и закрывать насильственно.

2. Требования к синтаксису оператора SELECT – SQL в 9-й версии VFP были существенно усилены. Это иногда приводит к возникновению ошибок в, кажущихся простыми, запросах. Эти ошибки почти всегда возникают при указании группировки данных. При этом система требует, чтобы ко всем полям, указанным в группировке, были применены агрегирующие функции, что не всегда удобно. Снизить уровень требований, и соответственно избежать ошибок, можно командой SET ENGINEBEHAVIOR 70, которую допускается прописать в командном окне или непосредственно в теле самой программы. Восстановление уровня осуществляется командой SET ENGINEBEHAVIOR 90.

3. В 9-й версии VFP было упразднено понятие ODBC драйверов, и вместо них введен OLE DB Provider. Это означает, что для доступа к базам данных, созданных в 9-й версии следует устанавливать фирменный OLE DB драйвер, который имеется в наличии в установочном комплекте, но автоматически не устанавливается. Доступ к остальным источникам через удаленные представления осуществляется по-старому, посредством использования источников данных ODBC.

Задания

1. Выберите из таблицы всех покупателей, чей кредит превышает 1000.

2. Выберите всех покупателей, живущих в России и Украине.

3. Выберите всех покупателей, чей кредит находится в промежутке от 1000 до 50000.

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

5. Сгруппируйте данные в предыдущем запросе вначале по заказчикам, а затем по товарам. Отметьте и объясните разницу в результатах.

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

7. Создайте представления данных, позволяющие отредактировать кредит каждого покупателя и стоимость одной единицы товара.

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

9. Создайте представление данных, позволяющее редактировать телефоны покупателей. Представление должно содержать ФИО покупателя (как единое целое, а не как три поля) и его телефон.

10. Создайте представление данных, показывающее по каждому покупателю итоговую сумму его покупок за заданный период. В дальнейшем используйте это представление, как источник данных в отчете.

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

12. Создайте источник данных для доступа к вашей базе данных, используя удаленное представление данных. После этого создайте временный проект Temp, который должен содержать только пустую базу данных. Создайте в этой базе данных удаленное представление данных, используя в качестве соединения созданный вами источник данных. Просмотрите созданное удаленное представление и сравните его данные с теми, что хранятся в самой базе данных. Попробуйте проделать то же самое с базой данных соседа.