В. В. Воронин информационное обеспечение систем управления

Вид материалаДокументы

Содержание


SELECT похожа на операторы реляционной алгебры. Любой оператор реляционной алгебры может быть выражен подходящим образом сформул
VFP входит конструктор и мастер запросов на выборку. Если запрос построен с использованием конструктора, то мы всегда можем полу
SELECT, но просматривая QPR
Select from
Select [distinct]
Select distinct
DBF-файлов, в которых есть поля с одинаковыми именами. Например, TABN
Пример использования
SELECT fio, DAY(dr), ’число’, YEAR(DATE())-YEAR(dr),’лет’, ’премия-’, 0.5*okl FROM СОТРУДНИКИ WHERE MONTH(dr)= MONTH(DАTE()) PLA
SELECT; TOVAR.tname AS Наименование
FROM СОТРУДНИКИ ORDER BY fio.
WHERE. SELECT R.fio, R.tabn, D.fio, D.gr
SELECT R.fio, R.tabn, D.fio, D.gr
DBF-файлов, хранящих эти таблицы задано следующим выражением ON ГОРОД.код_города = ПОСТАВЩИК.код_города.
Подобный материал:
1   ...   6   7   8   9   10   11   12   13   14


Таблица 6.1

Форма

Тип запроса

Пример

1. А(0)=?

Запрос значения атрибута

Звание служащего с номером 0001(Tnom=0001)

2. A(?)=V

Запрос объектов с заданным значением атрибута

Кто из служащих имеет оклад>300руб (Okl>300)

3. ?(0) r V

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

За какие месяцы заработки служащего 0004 превысили 350 руб.

4. ?(0)=?

Запрос всей информации о данном объекте

Сообщить всю хранимую информацию о служащем 0002

5. А(?)=?

Перечислить значение данного атрибута для каждого объекта

Перечислить зар.плату за последний месяц каждого служащего

6. ?(?)=V

Перечислить все атрибуты объектов, имеющие данные значения

Для каждого служащего определить месяца, когда его заработок превышал 350 руб.

7. ?(?)=?

Запрос содержимого всего DBF-файла

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


В таблице использованы следующие обозначения: О – объект, А – атрибут, V – значение атрибута, r – знак отношения.

Реализация запросов на выборку в VFP осуществляется командой SELECT, которая имеет очень много возможностей. Она является самой важной и самой сложной для пользователя командой SQL.

Команда SELECT всегда выполняется над некоторыми таблицами, входящими в базу данных. На самом деле в базах данных могут быть не только постоянно хранимые таблицы, а также временные таблицы и так называемые представления. Представления   это просто хранящиеся в базе данные SELECT-выражения. С точки зрения пользователей представления   это таблица, которая не хранится постоянно в базе данных, а "возникает" в момент обращения к ней. С точки зрения команды SELECT и постоянно хранимые таблицы, и временные таблицы и представления выглядят одинаково. Конечно, при реальном выполнении команды SELECT системой учитываются различия между хранимыми таблицами и представлениями, но эти различия скрыты от пользователя.

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

В состав VFP входит конструктор и мастер запросов на выборку. Если запрос построен с использованием конструктора, то мы всегда можем получить текст команды SELECT, соответствующий этому запросу. Запрос сохраняется в файле с расширением QPR как обычный текстовый файл и его можно редактировать, а откомпилированная версия храниться в файле с расширением QPX. Запрос можно запустить командой DO <имя>.QPR. Эту команду можно использовать в PRG-файле либо связать с определенным событием элемента управления на форме.

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

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

SELECT <что выводится >

FROM <откуда (источник)> INTO <куда (получатель)>

WHERE <каким условиям должен отвечать>

GROUP BY<колонки, по которым выполняются группирования>

HAVING <условие группирования записей в одну строку>

ORDER BY <в каком порядке выводить данные>

UNION <объединение результатов>.

Полный формат команды имеет следующий вид:

SELECT [DISTINCT]

[<псевдним>.]<выражение> [AS <колонка>]

[,[<псевдоним>.]<выражение> [AS <колонка>]…]

FROM [!] [AS <лок. псевдним>]

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

[!] [AS <лок. псевдним>]

[ON <условие связи DBF-файлов> …]

[[INTO<получатель>]/[TO FILE<файл>[ADDITIVE]/

/TO PRINTER [PROMPT]/TO SCREEN]]

[NOCONSOLE][PLAIN][NOWAIT]

[WHERE<усл.фильтра1> [AND/OR<усл.фильтра2>…]]

[GROUP BY <колонка1>[,<колонка2>…]]

[HAVING <условие фильтра группы>]

[ORDER BY<колонка>[ASC/DESC][,<колонка>][ASC/DESC]…]]

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

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

6.2. Простые запросы на выборку данных

Пример 1. Приведем самый простой пример команды, реализующей запрос – "Выбрать все данные из таблицы поставщиков":

SELECT * FROM ПОСТАВЩИК.

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

Пример 2. Запрос   "Выбрать все строки из таблицы поставщиков, удовлетворяющих некоторому условию":

SELECT DISTINCT * ;

FROM ПОСТАВЩИК P WHERE P.Город="Москва";

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

Имена полей могут быть с псевдонимами. Он необходимым, если выборка делается из нескольких DBF-файлов, в которых есть поля с одинаковыми именами. Например, TABN в DBF-файлах СОТРУДНИКИ и ДЕТИ. Псевдоним может быть не только "официальным" псевдонимом (ALIAS) DBF-файла, но и любое другое локальное имя, которое вы ему дали в команде SELECT. Никаких последствий за пределами команды SELECT это имя не имеет.

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

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

Таблица 6.2

Операция

Пример использования

=, ==,
>, <,
>=, <=,
!=, , <>,

<Имя поля1> = <Имя поля2>; <Имя поля> ==<константа>
ДЕТАЛЬ.код = ПОСТАВКА.код
СОТРУДНИКИ.фио
"Иванов"
стоимость>
2500

AND, OR, NOT

<Поле1> = <Поле2> AND <ПолеЗ> >= <Поле4>
<Поле1>
!= <константа> OR <Поле2> = <ПолеЗ>
ДЕТАЛЬ
.код = ПОСТАВКА.код AND ДЕТАЛЬ.name  "Болт"

BETWEEN

<Поле> BETWEEN <выражение1> AND <выражение2>
ДЕТАЛЬ
.код BETWEEN 90000 AND 99999

LIKE

<Поле> LIKE "<шаблон(%, _)>"
ДЕТАЛЬ.name LIKE "_Болт%"

IN

<Поле> IN (Список выражений)
СОТРУДНИКИ.должность NOT IN ("
профессор", "доцент")

ALL, ANY, SOME

<Поле | выражение> < сравнениe> < ANY | SOME > (подзапрос)
фирма
< ANY ;
(SELECT фирма FROM ПОСТАВЩИК WHERE страна = "RU")

EXISTS

EXISTS (подзапрос)
EXISTS (SELECT * FROM ДЕТАЛЬ WHERE ДЕТАЛЬ
.код = ;
ПОСТАВКА.код)


Пример 3. Выбрать некоторые поля из исходной таблицы. Запрос – "Выбрать FIO всех сотрудников, родившихся в текущем месяце с указанием дня рождения, количества лет и премии по этому поводу – 50% от оклада.

SELECT fio, DAY(dr), ’число’, YEAR(DATE())-YEAR(dr),’лет’, ’премия-’, 0.5*okl FROM СОТРУДНИКИ WHERE MONTH(dr)= MONTH(DАTE()) PLAIN.

Если не указать PLAIN, то колонки получат имена FAM и от EXP_2 до EXP_7. Последовательность выражений в команде определяет последовательность колонок в результирующей таблице. Перед ключевым словом FROM указываются отбираемые в запрос <выражения>, а после   имена DBF-файлов. Фрагмент <выражение> может быть константой, выводимой в каждой строке выборки, функцией, полем и т.п.

Пример 4. Использование вычисляемых полей и переименование колонок в результирующей таблице запроса.

SELECT;

TOVAR.tname AS Наименование,;

TOVAR.kol AS Количество,;

TOVAR.price AS Цена, "=" AS Равно,;

TOVAR.KOL*TOVAR.PRICE AS Сумма FROM TOVAR;

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

Пример 5. Упорядочение результатов запроса.

SELECT fio AS Фамилия, dr AS Дата рождения ;

FROM СОТРУДНИКИ ORDER BY fio.

Здесь выводятся фамилии и даты рождения сотрудников с нестандартными заголовками. Записи упорядочены по алфавиту. Порядок можно задавать по нескольким полям. Если явно не указаны ключевые слова ASC или DESC, то по умолчанию принимается упорядочение по возрастанию (ASC).

6.3. Сложные запросы на выборку данных

Сложные запросы реализуют выборку данных из двух и более таблиц. Эти таблицы должны быть связаны по определенному условию. Существует два способа организации такой связи, каждый из них соответствует своему стандарту SQL. В старом стандарте связь осуществлялась опцией WHERE. В новом стандарте (ANSI-стандарт) для данной цели введена специальная опция ON, которая относится к опции FROM и имеет следующий формат:

FROM <первая_таблица>

<тип_объединения> <вторая_таблица>

<условие_объединения>

Пример 6. Вначале приведем пример, иллюстрирующий связь посредством опции WHERE.

SELECT R.fio, R.tabn, D.fio, D.gr ;

FROM Кафедра!СОТРУДНИКИ R, Кафедра!ДЕТИ D ;

WHERE R.tabn=D.tabn.

В VPF DBF-файлы могут быть помещены в контейнер, сведения о котором хранятся в файле с расширением DBC. Поэтому при обращении к таблицам из различных контейнеров мы обязаны указать сложные имена по формату [!] [[AS] <лок. псевдним>].

Предыдущей командой мы выбираем фамилии родителей, детей из таблиц СОТРУДНИКИ и ДЕТИ сцепленных по полю tabn. Результат выборки – это совокупность колонок, заголовками которых могут быть имена полей. Если имена полей совпадают, то такие колонки получают совпадающие имена, к которым присоединяются одна из букв (по алфавиту), например, FIO_A, FIO_B, и т.д. Колонки, полученные в результате вычисления выражений, получают имена EXP с соответствующим номером. Например, EXP1,EXP2. Исключение составляют выражения, использующие собственные функции SQL, например MIN() и MAX(). Имена колонок в этом случае будут включать имена функций.

Этот же запрос будем использовать для иллюстрации второго способа организации связей таблиц.

SELECT R.fio, R.tabn, D.fio, D.gr ;

FROM Кафедра!СОТРУДНИКИ R;

INNER JOIN Кафедра!ДЕТИ D ; ON R.tabn=D.tabn.

Четыре типа объединения (INNER, LEFT, RIGHT, FULL), задаваемые ключевым словом JOIN, будем иллюстрировать следующим примером. Имеем две логически взаимосвязанные таблицы ГОРОД и ПОСТАВЩИК.

ГОРОД ПОСТАВЩИК

Код_
города


Город




Код_
поставщика


Код_
города


Поставщик

1

Хабаровск




1

3

АО «Байт»

2

Владивосток




2

2

ТОО Пролог

3

Петропавловск




3

1

Фирма 555

4

Елизово




4

0

Завод ЖБИ

Пусть условие объединения DBF-файлов, хранящих эти таблицы задано следующим выражением
ON ГОРОД.код_города = ПОСТАВЩИК.код_города.

Тогда внутреннее объединение, задаваемое фрагментом
ГОРОД Inner Join ПОСТАВЩИК
даст результат, в который включаются только те записи из обеих таблиц, которые отвечают условию объединения.

Код_
города


Код_
поставщика


Поставщик

Город

1

3

Фирма 555

Хабаровск

2

2

ТОО Пролог

Владивосток

3

1

АО «Байт»

Петропавловск

Второй тип объединения   левое внешнее объединение, задается фразой
ГОРОД Left Join ПОСТАВЩИК,
и его результат включает все записи из таблицы слева и записи, отвечающие условию объединения, из таблицы справа.

Код_
города


Код_
поставщика


Поставщик

Город

1

3

Фирма 555

Хабаровск

2

2

ТОО Пролог

Владивосток

3

1

АО «Байт»

Петропавловск

4

-

-

Елизово

Третий тип объединения   правое внешнее объединение, задается фразой
ГОРОД Right Join ПОСТАВЩИК,
и его результат включает все записи из таблицы справа и записи, отвечающие условию объединения, из таблицы слева

Код_
города


Код_
поставщика


Поставщик

Город

1

3

Фирма 555

Хабаровск

2

2

ТОО Пролог

Владивосток

3

1

АО «Байт»

Петропавловск

0

4

Завод ЖБИ

-

Четвертый тип   полное внешнее объединение, задается фразой
ГОРОД Full Outer Join ПОСТАВЩИК,
и его результат включает все записи из обеих таблицы

Код_
города


Код_
поставщика


Поставщик

Город

1

3

Фирма 555

Хабаровск

2

2

ТОО Пролог

Владивосток

3

1

АО «Байт»

Петропавловск

4

-

-

Елизово

0

4

Завод ЖБИ

-


6.4. Использование агрегатных функций с группировками

Язык SQL в VFP имеет следующие встроенные функции: COUNT()   количество строк в выборке, COUNT(DISTINCT) – количество разных строк (например, COUNT(*)); MIN() – наименьшее число в колонке; MAX()   наибольшее число в колонке; AVG() – среднее значение числовых данных в колонке; AVG(DISTINCT) – среднее значение без повторения данных; SUM() и SUM(DISTINCT) – сумма значений колонки и сумма без повторений.