Запросы к бд - команда select. представления

SELECT

[ALL | DISTINCT]

[TOP числовое_выражение [PERCENT]]*

Список выборки

FROM Список_имен_базовых_таблиц

[[INTO Приемник_выборки]

| [TO FILE Имя_файла [ADDITIVE]

| TO PRINTER [PROMPT] | TO SCREEN]]*

[NOCONSOLE]*

[WHERE Условия_связи_и_условия_фильтрации]

[GROUP BY Список_столбцов_группировки]

[HAVING Условия_на_группу]

[UNION [ALL] команда SELECT]

[ORDER BY Список_столбцов_упорядочения]

где

ALL | DISTINCT - запрос на выборку всех (опция по умолчанию)| только различных значений списка выборки; последние считаются различными, если они различаются по значению хотя бы одной компоненты;

TOP числовое_выражение [PERCENT]* - запрос на выборку лишь нескольких первых различных строк выборки; количество различных строк (или процент от общего количества) задается значением числового выражения; используется только при задания порядка выборки - т.е. при использовании опции ORDER BY;

СПИСОК ВЫБОРКИ - список выражений вида

Компонента_выборки [AS Пользовательское имя поля]

Компонентой выборки может быть произвольное - в том числе константное - выражение над полями базовых таблиц; чаще всего это - просто имя поля базовой таблицы. Если базовые таблицы содержат одинаковые имена полей, во избежание коллизии (неоднозначности) имен необходимо использовать полные (квалифицированные) имена полей вида

[Имя или алиас таблицы.] Имя_поля

Имена полей в результирующей таблице (т.е. самой выборке) генерируются автоматически; опция AS позволяет давать им свои - более осмысленные имена.

FROM Список_имен_базовых таблиц

список имен таблиц, "опрашиваемых" данной выборкой, вида

[[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN *

[Имя_БД!] Имя_таблицы [Локальный алиас]

[ON Условие_связи]

В том случае, когда базовые таблицы принадлежат разным (или - неоткрытой) БД, необходимо использовать полные имена таблиц вида Имя_БД!Имя_таблицы. В случае, когда команда SELECT ссылается на одну базовую таблицу несколько раз (например, в случае вложенных запросов, т.е. использования одной выборки в предикатах другой выборки) во избежание коллизии имен необходимо использовать псевдонимы/алиасы таблиц; последние задают временные имена (копий) таблицы, действительные только на момент выполнения данной выборки;

Выборка из нескольких базовых таблиц означает (с точки зрения логики, но не реализации исполнения) выборку из их декартова произведения - т.е. выборку всевозможных комбинаций строк базовых таблиц. Такая полная выборка редко бывает необходимой, поэтому ее ограничивают наложением дополнительных условий (отношений, связей, объединения) на пары базовых таблиц - в FoxPro условия связи представляют собой сравнения полей связываемых таблиц (см. выше), на практике чаще всего используются равенства первичного и внешнего ключей. Очевидно, что в последнем случае в результате запроса мы получим композицую таблиц (отношений), обратную к операции декомпозиции, применяемой при определении структуры базы.

В стандартном SQL связи реализуются с помощью опций WHERE и UNION. FoxPro предлагает расширенный синтаксис (и более оптимальную реализацию) наиболее популярных видов связей.

Внутреннее объединение (INNER JOIN) предполагает отбор из обеих таблиц только записей, удовлетворяющих соответствующему условию связи; кроме того, в левое внешнее объединение (LEFT [OUTER] JOIN) отбираются дополнительно все те строки из таблицы, стоящей слева от слова JOIN, в правое внешнее объединение (RIGHT [OUTER] JOIN) - все те строки из таблицы, стоящей справа от слова JOIN, и в полное внешнее объединение (FULL [OUTER] JOIN) - все те строки обеих таблиц, для которых соответствия в другой таблице не существует.

INTO Приемник_выборки| [TO FILE Имя_файла [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN]]*

По умолчанию, результат выборки показывается в стандартном (BROWSE-) экранном окне. По желанию, можно указать другое место хранения результирующей таблицы · массив (опция INTO ARRAY имя_массива), временную таблицу "только для чтения", или - курсор (опция INTO CURSOR имя_курсора), таблица (т.е. DBF-файл - опция INTO DBF | TABLE Имя_таблицы [DATABASE Имя_БД [NAME Длинное_имя_таблицы]]), текстовый ASCII-файл (опция TO FILE имя_файла, подопция ADDITIVE указывается при дописывании в уже существующий файл), принтер ( опция TO PRINTER, подопция PROMPT временно останавливает вывод для подготовки принтера), или главное экранное окно (опция TO SCREEN). Опция NOCONSOLE при этом подавляет "эхо"-вывод на экран

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

GROUP BY список_полей_группировки

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

- COUNT(Выражение) - количество значений заданного выражения в группе, не равных NULL;

- COUNT(*) - число строк в группе;

- MIN(Выражение) - минимальное значение данного выражения по группе;

- MAX(Выражение) - максимальное значение данного выражения по группе;

- SUM(Числовое_выражение) - сумма значений заданного выражения по группе;

- AVG(Числовое_выражение), - среднее значение заданного выражения по группе;

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

HAVING Условие_на_группу

Эта опция по семантике схожа (и иногда путается ) с опцией WHERE, но, в отличие от последней, фильтрует не одиночные записи, а группы. Используется только совместно с GROUP BY. Задаваемое условие на группу не может содержать предикатов с подзапросами и ссылается только на поля из списка группировки. Как правило, это условие.является проверкой значений некоторых агрегатных функций - для проверки значений полей эффективнее использовать опцию WHERE.

[UNION [ALL] команда SELECT] Объединяет результирующие таблицы двух или более выборок (совпадающих по структуре, т.е. количеству и типу компонентов выборки) в одну; в случае объединения более двух выборок необходимы расставить скобки. Заметьте, что тип поля результирующих таблиц в FoxPro определяется по типу первого выбранного значения, что не всегда удобно, т.к. первое значение может оказаться короче последующих. По умолчанию, опция UNION убирает дубликаты из окончательной результирующей таблицы - используйте подопцию ALL (все), если такое положение не устраивает.

Отметим также, что

- UNION нельзя использовать в подзапросах;

- При использовании UNION разрешается использовать опцию упорядочения ORDER BY лишь последней объединяемой команды SELECT, со ссылкой на номер (не имя) столбца; в этом случае упорядочение относится ко всей результирующей таблице-объединению.

ORDER BY список_полей_упорядочения [ASC|DESC]

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

СОЗДАНИЕ ПРЕДСТАВЛЕНИЯ

CREATE SQL VIEW [Имя_представления ] [REMOTE]

[CONNECTION Имя_соединения_с_удаленным_источником [SHARE]

| CONNECTION Имя_удаленного_источника_данных]

[AS команда SELECT]

REMOTE опция создания удаленного представления, выбирающего данные от источника данных, отличного от локальных таблиц FoxPro. При этом имя удаленного источника может быть задано либо явно - опция CONNECTION Имя_удаленного_источника_данных, либо указанием на именованное соединение - опция CONNECTION Имя_соединения_с_удаленным_источником. Подопция SHARE указывает на распределенное соединение, обслуживающее несколько представлений. Наиболее простой способ определить именованное соединение в диалоговом режиме обеспечивается командой CREATE CONNECTION ? (другие подопции этой команды см. в документации).

AS команда_SELECT

собственно определение представления

Замечание.

В FoxPro допустимы

1) параметризованные представления, ссылающиеся на переменные FoxPro - при использовании параметров нужно поставить знак ? перед именем переменной.

2) модифицируемые представления; cуть определения модифицируемости состоит в следующем

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

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

Более точное определение модифицируемости представлений можно найти в документации.

СОЗДАНИЕ КУРСОРА

Курсор – это временная таблица, с которой можно обращаться точно также, как с обычной таблицей БД, за одним, но важным исключением - в отличие от таблиц и представлений, курсор не считается частью БД; его содержимое и само определение действительно только в текущем сеансе работы и "теряется" немедленно по его закрытии.

CREATE CURSOR имя_курсора

(список_описания_полей_курсора)

где описание поля курсора имеет вид

(имя_поля тип_поля [(ширина_поля [, точностьe])

[NULL | NOT NULL]

[CHECK Правило_корректности_поля

[ERROR Текст_сообщения_о_нарушении_правила]]

[DEFAULT значение_по_умолчанию]

[UNIQUE]

См. описание опций в команде CREATE TABLE.

Поскольку курсоры не считаются неотъемлемой частью SQL, мы снова отошлем к документации любознательного читателя, интересующегося деталями обработки курсоров в FoxPro.

КОМАНДЫ ПОДДЕРЖКИ ТРАНЗАКЦИЙ

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

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

Начало транзакции оформляется командой SQL

BEGIN TRANSACTION

и закачивается либо командой корректного завершения транзакции

END TRANSACTION

либо командой "отката", т.е. возврата к состоянию БД к моменту начала транзакции

ROLLBACK

FoxPro поддерживает до 5 уровней вложенности транзакций. Однако, поскольку другие пользователи сети не имеют доступа к записи таблиц БД, модифицируемым во время исполнения транзакции, необходимо минимизировать время ее выполнения.

Некоторые команды - в основном, это команды модификации структуры БД (такие, например, как ALTER TABLE и CREATE TABLE) не могут быть включены в транзакцию - см. документацию.

Примерные типы заданий.

Пример простой БД “Учет заказов”.

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

При этом предполагается хранить следующую информацию.

Сведения о покупателях:

- УЧЕТный №;

- ФАМИЛИЯ;

- КРЕДИТ (сумма, на которую покупатель имеет право покупать товары у данной фирмы)

- ДАТА рождения

- ГОРОД проживания

- АДРЕС

- ТЕЛЕФОН

- ТАБЕЛЬный № обслуживающего продавца;

Сведения о продавцах:

- ТАБЕЛЬный №;

- ФАМИЛИЯ;

- КОМИССИЯ - число из интервала [0,1] - доля от суммы заказа, составляющая выручку продавца;

- ДАТА рождения

- ГОРОД проживания

Сведения о заказах:

- ПРОДАВЕЦ - табельный № продавца;

- ПОКУПАТЕЛЬ - учетный № покупателя;

- ТОВАР - артикул (идентификатор) товара

- ЗАКАЗАН (дата заказа);

- ПОСТАВЛЕН (дата поставки)

- СУММА заказа, в рублях;

- КОЛИЧЕСТВО поставляемого товара ( в штуках или других подходящих единицах)

Упражнения.

1) Определите правила проверки корректности для полей и записей таблиц, делая разумные допущения о том, что такое:

- реальная дата заказа, если фирма основана 1.01.1990;

- реальная сумма заказа, если фирма продает автомобили ценой от 10000 до 100000 рублей, в текущих ценах;

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

1) Определите правила целостности в БД, предполагая, в частности, что:

- при увольнении продавца информация о его заказах сохраняется;

- при разрыве отношений с покупателем информация о его заказах далее не хранится

1) Определите структуру БД "Учет заказов" в вашей СУБД

2) Измените структуру БД, для того, чтобы отразить старшинство членов фирмы, полагая, что у каждого продавца, кроме главы фирмы, есть непосредственный начальник.

3) Дополните модель сведениями о продаваемых фирмой товарах – а именно, их

- АРТИКУЛ,

- НАИМЕНОВАНИЕ,

- код ТИПа товара (пример кодировки - 0101 - 'Автомобили', 0903 - 'Канцелярские товары' и пр.),

- коде предприятия-ИЗГОТОВИТЕЛЯ,

- коде ЕДИНИЦЫ измерения (метры, кв. метры, килограммы, штуки),

- ЦЕНЕ за единицу

- и имеющемся на складе КОЛИЧЕСТВЕ.

Как изменится структура БД, если допустить, что

- Каждый покупатель всегда покупает только один товар?

- Каждый продавец продает постоянно лишь один товар?

- В общем (и подразумеваемом далее) случае неверно ни то, ни другое?

ЗАПРОСЫ К БАЗЕ ДАННЫХ.

Введем несколько определений, полезных для компактной формулировки задач. Каждая строка таблицы БД описывает некоторый объектпредметной области (или отношение между объектами, которое снова удобно считать объектом). Атрибутами объекта назовем значения одного или нескольких полей соответствующей ему таблицы. Свойством или категорией объектов назовем некоторую естественно определяемое, в терминах предметной области, множество объектов. Категории объектов разделим на простые и сложные, в зависимости от того, содержится ли необходимая для определения категории информация в одной или нескольких таблицах БД. Примеры атрибутов и категорий для объектов БД “Учет заказов” приведены ниже.

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

Определите

(атрибуты покупателей:)

- учетные номера

- фамилии, телефоны и адреса

- даты рождения, по возрастанию

- возраст, по убыванию

- города проживания, в алфавитном порядке

- табельные номера обслуживающих продавцов

покупателей,

( простые категории покупателей:)

- живущих в данном городе (например, Казани)