Управление данными

Задание 1. Откройте в подкаталоге вашей группы, созданный проект «Стол заказов». Создайте для него, пользуясь режимом «автоформат», форму для таблицы «Склад».

Методика и порядок выполнения задания 2

1. Откройте проект «Стол заказов».

2. Перейдите во вкладку Documents. В ней выберите группу Forms и нажмите кнопку New, в результате чего откроется диалоговое окно New Form.

3. В этом окне нажмите на кнопку Form Wizard. На экране появится окно диалога, в которого необходимо указать, создается ли форма для одной таблицы или нескольких связанных таблиц. По умолчанию предполагается создание однотабличной формы. Для перехода к следующему шагу вновь нажмите кнопку ОК.

4. Выберите таблицу или поля таблицы, которые будут входить в создаваемую форму. Для выбора базы данных и содержащейся в ней таблицы используются два списка, расположенные в области «Databases and Tables». Для выбора требуемой таблицы нажмите кнопку «>>», расположенную рядом с кнопками раскрытия списков таблиц и баз данных. На экране откроется диалоговое окно Open, позволяющее найти таблицу, которая будет использована в форме.

5. После выбора таблицы список «Available Fields» содержит перечень всех полей открытой таблицы. Перенесите из списка «Available Fields» в список «Selected Fields» те поля, которые будут внесены в создаваемую экранную форму. Если возникнет необходимость удалить поле из списка «Selected Fields», то следует использовать кнопку Remove. После выбора полей перейдите к следующему шагу, нажав на кнопку Next.

6. Установите стиль отображения объектов формы и типов кнопок управления. Список «Style» содержит шесть различных вариантов отображения объектов в экранной форме. «Мастер» форм позволяет при выборе каждого из стилей просмотреть результат в верхней части окна диалога. Опции переключателя Button Type позволяют выбрать тип отображения кнопок управления: Text Buttons; Picture Buttons; No Buttons; Custom. После выбора нужных опций нажмите кнопку Next.

8. Укажите критерий сортировки данных в списке «Selected Fields». Для выбора поля, по которому будет осуществляться сортировка, установите на него курсор в списке «Available Fields or indexed tag» и нажмите кнопку Add. Опции Ascending и Descending определяют, по возрастанию или по убыванию будут упорядочены данные в выбранном поле. Для перехода к следующему шагу нажмите кнопку Next.

9. Задайте заголовок формы в поле «Type a title for your form» и выберите один из возможных вариантов продолжения работы с ней: Save form for later use; Save and run form; Save form and modify it in Form Designer.

10. После установки требуемых опций нажмите кнопку Finish для завершения создания объекта с помощью мастера. В открывшемся окне диалога Save as выберите каталог, в котором будет храниться форма, и введите имя созданной формы.

Задание 2. Создайте с помощью «мастера» форм форму для таблицы «Потребители».

Методика и порядок выполнения задания 3

Для выполнения задания 3 изучите подпункты «Создание экранной формы в конструкторе форм», «Свойства и методы объектов формы», «Размещение текстовой информации», «Размещение полей ввода», «Создание кнопок управления», «Создание одиночных кнопок», «Режим редактирования составных объектов», «Создание группы кнопок», «Запуск формы на выполнение» раздела «Теоретическое обоснование» данной лабораторной работы.

Задание 3. Создайте с помощью конструктора форм: формы для таблиц «Потребители», «Журнал прихода» из базы данных «Стол заказов».

Содержание отчета и его форма

Отчет по лабораторной работе должен состоять из:

1) названия лабораторной работы;

2) ответов на контрольные вопросы;

3) формулировки заданий к лабораторной работе и порядка их выполнения.

Отчет о выполнении лабораторной работы в письменном виде сдается преподавателю.

Вопросы для защиты работы

1. Понятие формы в Visual FoxPro.

2. Основные способы создания форм в Visual FoxPro.

3. Создание формы в режиме «автоформат».

4. Создание формы с помощью мастера.

5. Создание форм в конструкторе проектов.

6. Создание одиночной кнопки.

7. Создание группы кнопок.

8. Запуск формы на выполнение.

Лабораторная работа 9

Выборка данных

Цель и содержание: рассмотреть основные способы выполнения выборки данных в СУБД Visual FoxPro.

Теоретическое обоснование

На практике часто требуется выбрать из исходной таблицы часть записей, удовлетворяющих определенным критериям, и упорядочить выборку. Критерии могут определяться рядом условий. Для решения задач такого класса предназначены конструктор запросов и команда SELECT встроенного языка СУБД Visual FoxPro.

При помощи этих средств можно:

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

2) указать поля, которые должны быть отображены для выбранных записей;

3) производить вычисления, используя выбранные данные.

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

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

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

Конструктор запросов

Для вызова конструктора запросов в окне проекта выберите группу Queries на вкладке Data и нажмите кнопку New Query. На экране открывается окно диалога выбора таблиц Add Table Or View . Выберите таблицу и используя кнопку Add …, перенесите ее в окно конструктора запросов, которое содержит выбранную вами таблицу, а в основном меню появляется пункт Query.

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

Окно конструктора запросов содержит панель для отображения используемых в запросе таблиц, а также вкладки для выбора полей запроса и формирования условия выборки. Основными вкладками являются Fields, Join, Filter, Order By, Group By, Miscellaneous.

Для формирования запросов к базе данных могут также использоваться пункт главного меню СУБД Visual FoxPro Query и панель инструментов Query Designer, которые выполняют следующие функции: Add Table; Remove Table; Remove Join Condition; Selection Criteria; Output Fields; Join;

Filter; Order By; Group By; Miscellaneous; Query Destination; View SQL; Comments и Run Query.

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

Заполнив окно конструктора запросов, его можно сохранить. Для этого выполните команду File > Save as из главного меню. В открывшемся окне диалога Save as откройте необходимый каталог, введите в поле имя файла и нажмите кнопку Сохранить.

Определение полей результирующей таблицы

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

В Visual FoxPro существует несколько вариантов выбора полей результирующей таблицы.

1. Откройте вкладку Fields, выделите в списке Available fields поля, которые необходимо отобразить в запросе, и с помощью кнопки Add … перенесите их в список Selected fields . Кнопка Add All … позволяет произвести выбор сразу всех полей таблицы. Если для формирования запроса необходимо выбрать большую часть полей, то воспользуйтесь этой кнопкой, а затем выделите курсором поля, которые не нужно помещать в запрос, и удалите их из списка Selected fields с помощью кнопки Remove.

2. В Visual FoxPro широко используется механизм dgar-and-drop. Для использования этого механизма при выборе полей перейдите на панель, содержащую образы таблиц, используемых в запросе. Выделите нужные поля и переместите их с помощью манипулятора «мышь» в список Selected fields.

3. Для добавления в список отдельных полей таблицы можно выбрать поле и дважды нажать левую кнопку манипулятора «мышь».

Запуск запроса на выполнение

После задания условий для выбора необходимой информации из базы данных и указания результирующих полей в окне конструктора можно просмотреть результаты выполнения запроса. Это можно выполнить одним из следующих способов:

1) нажмите кнопку Run (Выполнить) на стандартной панели инструментов;

2) выберите команду контекстного меню Run Query (Выполнить запрос);

3) выберите в меню Query (Запрос) команду Run Query (Выполнить запрос);

4) нажмите комбинацию клавиш «Ctrl» + «Q».

На экране появятся результаты запроса, представленные в табличном виде. Для просмотра результатов запроса, не открытого в окне конструктора запросов, необходимо в окне проекта установить курсор (указатель манипулятора «мышь») на имя запроса и нажать кнопку Run (Выполнить).

Аппаратура и материалы. Для выполнения лабораторной работы необходим персональный компьютер со следующими характеристиками: процессор Intel с тактовой частотой 2000 МГц и выше; оперативная память - не менее 128 Мбайт; свободное дисковое пространство - не менее 800 Мбайт; устройство для чтения компакт-дисков; монитор типа Super VGA (число цветов - 256) с диагональю не менее 17 . Программное обеспечение - операционная система WINDOWS 98 / NT / ME / 2000 / XP, Microsoft Visual FoxPro 8.0.

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

Методика и порядок выполнения работы

1. Откройте созданный проект «Стол заказов».

2. На вкладке Data выберите группу Queries. После этого нажмите кнопку New.

3. На экран будет выведено диалоговое окно New Queries, в котором также нажмите кнопку New Queries.

4. В диалоговом окне Add Table or View выберите таблицу, из которой надо произвести выборку значений. Например, «Журнал реализации».

5. Нажмите кнопку Add … для переноса её в окно конструктора запросов.

6. Выбор полей необходимых для создания запроса осуществите следующим образом:

1) активизируйте вкладку Fields;

2) в списке Available fields выделите поля, которые необходимо перенести в запрос;

3) нажмите кнопку Add для переноса выделенных полей в список Selected fields, если же необходимо перенести все поля, то нажмите кнопку Add All.

7. Запустите сгенерированный запрос на выполнение, нажав кнопку Run.

Задание. Используя конструктор запросов, организуйте выборку информации из базы данных «Стол заказов»:

1) выберите всех потребителей, начинающихся на букву «С»;

2) выберите все товары, произведенные в Российской Федерации;

3) выберите всех поставщиков, расположенных на территории Ставропольского края.

Содержание отчета и его форма

Отчет по лабораторной работе должен состоять из:

1) названия лабораторной работы;

2) ответов на контрольные вопросы;

3) формулировки заданий к лабораторной работе и порядка их выполнения.

Отчет о выполнении лабораторной работы в письменном виде сдается преподавателю.

Вопросы для защиты работы

1. Способы создания запросов в Visual FoxPro.

2. Охарактеризуйте создание запросов с помощью мастера.

3. Охарактеризуйте создание запросов в конструкторе проектов.

4. Каким образом можно определить поля результирующей таблицы?

5. Каким образом можно просмотреть результат выборки (запроса)?

7. Как можно просмотреть запрос в виде SQL?

Лабораторная работа 10

Расширенные SQL-запросы. Безусловная выборка значений

Цель и содержание: изучить средства языка SQL для безусловной выборки значений.

Теоретическое обоснование

Вводить SQL-запросы можно непосредственно в командном окне (Command window) или в окне дизайнера запросов (Query Designer). Для формирования запросов на языке SQL используется конструкция SELECT. Результатом выполнения запроса является таблица, которая хранится во временном буфере сервера базы данных. Выбранные данные можно использовать для просмотра, формирования графиков или печати отчетов. Длина строки на языке SQL до 255 символов, регистр не имеет значения. Синтаксис команды SELECT:

SELECT [ALLDISTINCT] список выбираемых полей

FROM список таблиц

[WHERE условие выборки или соединения]

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

[HAVING условие выборки группы]]

[ORDER BY список полей, по которым упорядочить вывод]

При формировании запросов можно использовать уточненные имена полей (например, CUSTOMER.CUSTOMERNO, т.е. Имя_таблицы. Имя_поля).

Ключевое слово ALL подразумевается по умолчанию.

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

SELECT*FROM таблица.

Запрос вида

SELECT*FROM R1, R2

соответствует декартову произведению таблиц R1 и R2, т. е R=R1х R2.

Запрос

SELECT R1.А, R2.В FROM R1, R2

соответствует проекции декартова произведения таблиц R1 и R2 на столбцы A из таблицы R1 и B - из таблицы R2, то есть R = R1. A, R2. B(R1х R2).

Простая выборка

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

SELECT kod FROM R1

является столбец kod из таблицы R1.

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

Выборка уникальных значений

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

SELECT DISTINCT kod FROM R1

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

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

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

1) в окне конструктора запросов активизировать вкладку Fields;

2) с помощью манипулятора «мышь» или клавиши Tab перейти в поле «Functions and expressions»

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

4) в окне диалога Expression Builder создайть выражение для вычисляемого поля.

5) нажать кнопку Add для переноса данного выражения в список выходных полей запроса.

Пример 1. Пусть в некоторой базе данных содержится таблица Товары, в которой цена (UNITPRICE) указана без учёта налога на добавленную стоимость (НДС). Поля таблицы Товары представлены в таблице10.1. Тип переменной UNITPRICE - Currency.

Таблица 10.1 - Поля таблицы Товары

Kod1	UNITPRICE
1	1000.0000
2	2000.0000
3	10000.0000

Необходимо организовать выборку информации из таблицы Товары, указав цены с учетом налога на добавленную стоимость (18%).

SQL-запрос, соответствующий примеру 1:

SELECT Kod1, UNITPRICE * (1 + 0,18) AS ЦЕНА FROM Товары

Результат выполнения данного запроса представлен в таблице 10.2.

Таблица 10.2 - Результат выборки из таблицы Товары

Kod1	ЦЕНА
1	1180.0000
2	2360.0000
3	11800.0000

В запросе после ключевого слова AS записано новое название столбца таблицы.

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

Если в SQL-запрос, соответствующий примеру 1, добавить строку «Цена указана с учетом НДС», то он примет вид:

SELECT Kod1, UNITPRICE * (1 + 0,18) AS ЦЕНА, «Цена указана с учетом НДС» FROM Товары

Результат выполнения данного запроса представлен в таблице 10.3.

Таблица 10.3 - Результат выполнения запроса

Kod1	ЦЕНА	Цена указана с учетом НДС
1	1180.0000	Цена указана с учетом НДС
2	2360.0000	Цена указана с учетом НДС
3	11800.0000	Цена указана с учетом НДС

При выборке с помощью команды SELECT можно использовать статистические функции:

1) MIN(X) - вычисляет минимальное значение из множества X;

2) MAX(X) - вычисляет максимальное значение из множества X;

3) AVG(X) - вычисляет среднее арифметическое значений из множества X;

4) SUM(X) - вычисляет сумму значений множества X;

5) COUNT(X) - определяет число элементов множества X.

Примеры использования этих функций:

1) SELECT COUNT(*) FROM GOODS - создает выборку, состоящую из одной строки и одного поля, содержащего количество всех строк таблицы GOODS (товары);

2) SELECT MAX(Цена), MIN(Цена), AVG(Цена) FROM GOODS - создает выборку, состоящую из одной строки и трех полей, содержащих минимальное значение цены, максимальное значение цены и её среднее значение.

Аппаратура и материалы. Для выполнения лабораторной работы необходим персональный компьютер со следующими характеристиками: процессор Intel с тактовой частотой 2000 МГц и выше; оперативная память - не менее 128 Мбайт; свободное дисковое пространство - не менее 800 Мбайт; устройство для чтения компакт-дисков; монитор типа Super VGA (число цветов - 256) с диагональю не менее 17 . Программное обеспечение - операционная система WINDOWS 98 / NT / ME / 2000 / XP, Microsoft Visual FoxPro 8.0.

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

Методика и порядок выполнения работы

Для выполнения заданий к лабораторной работе необходимо изучить раздел «Теоретическое обоснование» к данной работе.

Задание. Откройте таблицу «Orders (Приказы)», созданную в лабораторной работе 6. Для данной таблицы произведите выборку:

1) всех строк;

2) всех неповторяющихся строк;

3) всех строк, для которых определены поля: order_id, order_sum, order_nds, здесь order_nds - вычисляемое поле, представляющее собой величину НДС; ставку НДС принять равной 18%;

4) выборку минимального значения order_sum, максимального значения order_sum, среднего значения order_sum, причем все значения должны содержаться в одной выборке;

5) выборку, содержащую количество всех строк таблицы.

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

Содержание отчета и его форма

Отчет по лабораторной работе должен состоять из:

1) названия лабораторной работы;

2) ответов на контрольные вопросы;

3) формулировки заданий к лабораторной работе и порядка их выполнения.

Отчет о выполнении лабораторной работы в письменном виде сдается преподавателю.

Вопросы для защиты работы

1. Охарактеризуйте команду SELECT.

2. Простая выборка.

3. Для чего предназначена команда DISTINCT.

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

5. Использование статистических функций для организации запросов.

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

Лабораторная работа 11

Расширенные SQL-запросы. Выборка с условием

Цель и содержание: изучить средства языка SQL для выборки значений с условием.

Теоретическое обоснование

Построение условий для выбора записей

Все вышеописанные средства позволяют отобразить в результирующей таблице все или часть данных, упорядоченных определенным образом. На практике в большинстве случаев требуется получить не все записи исходной таблицы, а лишь ту часть, которая удовлетворяет определенным условиям. Для выбора записей служит вкладка Filter. Здесь можно задать условия точного или неточного совпадения значений поля, определить диапазон выбираемых значений и т.д.

Ограничение выборки

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

Для задания условия выборки в SQL-запросе используется команда (ключевое слово) WHERE. Условие, следующее за ключевым словом WHERE, может включать:

1) арифметические операторы сравнения: =, <>, > , <, >=, <=;

2) логические операторы - AND, OR, NOT;

3) скобки, определяющие порядок вычислений.

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

Пример 1. Пусть существует некоторая база данных, в которой имеется таблица Table3, содержащая информацию о поставщиках, покупателях, товарах. Предполагается, что покупатель может приобретать товар в кредит. Произвести из данной базы выбор всех кодов (kod) и фамилий (NAME) покупателей, которые находятся в Москве и имеют кредит(CREDITLIMIT) более 200 000.

С помощью языка SQL запрос, соответствующий примеру 1, можно представить в виде:

SELECT kod, NAME

FROM Table3

WHERE CITY=“Москва”

AND CREDITLIMIT>200000

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

Вкладка Ordered By в окне дизайнера запросов позволяет управлять порядком расположения записей в результирующей таблице. Для упорядочивания выделите указателем (курсором) поля, которые будут определять порядок сортировки выбранных данных, и перенесите их последовательно в список Ordering criteria. Для каждого выбранного поля можно с помощью переключателя (кнопки) Order options установить критерий упорядочивания по возрастанию (Ascending) или по убыванию (Descending).

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

Пример 2. Для таблицы Table3 из примера 1 необходимо произвести выбор кода (kod), имени (NAME) и суммы кредита (CREDITLIMIT) всех покупателей, проживающих в Ставрополе, расположив их в порядке убывания.

SQL-запрос, реализующий пример 2, имеет вид:

SELECT kod, NAME, CREDITLIMIT

FROM Table3

WHERE CITY=“Ставрополь”

ORDER BY CREDITLIMIT DESC

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

имя поля[упорядочение]

[,имя поля [упорядочение]] ...,

где аргумент «упорядочение» может принимать значение ASC(возрастание) или DESC (убывание). По умолчанию устанавливается значение ASC. В качестве аргументов имя поля могут использоваться только поля результирующей таблицы. Поэтому недопустима следующая конструкция:

SELECT kod, NAME, CREDITLIMIT

FROM Table3

ORDER BY CITY

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

SELECT Kod1, UNITPRICE * (1 + 0,18)

FROM Товары

ORDER BY 2

будет являться результирующая таблица, во второй столбец которой будет помещена информация о стоимости товаров с учетом НДС. Записи в выборке будут упорядочены по второму столбцу.

Выборка с использованием оператора BETWEEN

Для организации выборки информации из базы данных, принадлежащей некоторому диапазону, в SQL-запросах используются операторы:
«>(Больше)»; «<(Меньше)» и Between (Между). Выбор каждого из этих операторов осуществляется в окне мастера запросов из списка Criteria (Критерий), расположенного во вкладке Filter (Фильтр). Операторы
«>(Больше)» и «<(Меньше)» используются в том случае, если задана только нижняя или верхняя граница диапазона, a оператор Between (Между) - если известны обе границы диапазона.

С помощью SQL-запроса, набранного в командном окне,

SELECT kod, NAME, UNITPRICE

FROM Table4

WHERE UNITPRICE BETWEEN 20000 AND 100000

на экран будет выведена таблица, содержащая сведения о товарах, стоимость которых (UNITPRICE) находится в диапазоне от 20000 до 100000 включительно. Оператор BETWEEN возвращает истинные значения и для граничных точек.

Кроме того, при организации выборки из таблицы значений, не принадлежащих некоторому диапазону, может быть использовано условие NOT BETWEEN (не принадлежит диапазону между величинами), например:

SELECT kod, NAME, UNITPRICE

FROM Table4

WHERE UNITPRICE NOT BETWEEN 20000 AND 100000

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

СУБД Visual FoxPro позволяет при создании запросов формировать несколько условий. В том случае, если все задаваемые условия накладываются на одно поле, их можно разместить в одной строке. Для этих целей используется оператор IN (принадлежит).

В частности, результатом выполнения SQL-запроса, приведенного ниже

SELECT kod, NAME, UNITPRICE

FROM Table4

WHERE UNITPRICE IN (100000, 200000, 500000)

является таблица, содержащая информацию о товарах, цена которых равна 100000, 200000 или 500000.

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

SELECT kod, NAME, UNITPRICE

FROM Table4

WHERE UNITPRICE=100000

OR UNITPRICE=200000

OR UNITPRICE=500000

Как и в случае с оператором BETWEEN можно также использовать конструкцию NOT IN (не принадлежит), например:

SELECT kod, NAME, UNITPRICE

FROM Table4

WHERE UNITPRICE NOT IN (100000, 200000, 500000)

Выборка с использованием шаблонов

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

SELECT kod, NAME, UNITPRICE

FROM Table4

WHERE NAME LIKE “T%”

пользователь может выбрать из базы данных все товары, наименование которых начинается с буквы “Т”( таблица 11.1).

Таблица 11.1 - результат выборки по шаблону

kod	NAME	UNITPRICE
2	Toshiba 1200	600.00
4	Turbo Pascal	200.00

Шаблоны в языке SQL описываются с помощью оператора LIKE, который может быть представлен в виде:

имя поля LIKE строковая константа

Результат выполнения оператора LIKE принимает значение «истина», если значение в указанном поле соответствует образцу, указанному аргументом «строковая константа», символы которой интерпретируются следующим образом:

1) _ (пробел или подчеркивание) - любой одиночный символ;

2) % (процент) - произвольная последовательность символов.

3) Все другие символы обозначают сами себя. Специальный смысл специальных символов отменяет знак «\».

Например, совокупность символов «\%» означает процент, а не последовательность символов.

В приведенном примере конструкция SELECT будет осуществлять выборку записей из таблицы Table4, для которых значение в поле NAME начинается с буквы «Т» и содержит далее любую последовательность символов. Например, если из таблицы TAB7 необходимо выбрать все строки, в которых поле string1 содержит «+», а предпоследняя буква «S», то SQL-запрос, реализующий данное условие примет вид:

SELECT * FROM TAB7 WHERE string1 LIKE «%+%S_».

Аппаратура и материалы. Для выполнения лабораторной работы необходим персональный компьютер со следующими характеристиками: процессор Intel с тактовой частотой 2000 МГц и выше; оперативная память - не менее 128 Мбайт; свободное дисковое пространство - не менее 800 Мбайт; устройство для чтения компакт-дисков; монитор типа Super VGA (число цветов - 256) с диагональю не менее 17 . Программное обеспечение - операционная система WINDOWS 98 / NT / ME / 2000 / XP, Microsoft Visual FoxPro 8.0.

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

Методика и порядок выполнения работы

Перед выполнением заданий к лабораторной работе необходимо изучить соответствующие пункты раздела «Теоретическое обоснование».

Задания к лабораторной работе

Задание 1. Произведите выборку строк из таблицы «Orders», значение поля order_sum для которых меньше либо равны 100.

Задание 2. Произведите выборку неповторяющихся строк из таблицы «Orders» при условиях: значение в поле order_sum больше либо равно 50, но меньше либо равно 250; а значение поля order_date находится в диапазоне с 01.01. ХХ г. по 31.12. ХХ, где ХХ - текущий год.

Задание 3. Произведите выборку из таблицы Orders всех строк, для которых значение поля order_sum равно либо 50, либо 100, либо 200.

Задание 4. Произведите выборку минимального, максимального значения среднего значения поля order_sum для всех строк, имеющих значение поля order_acpt = .T.

Задание 5. Создайте выборку, содержащую количество строк таблицы Orders, где поле order_num содержит подстроку «doc».

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

2. Для выполнения задания 2 использовать оператор BETWEEN.

3. Запрос, соответствующий заданию 3, представьте в двух вариантах первый - с использованием оператора IN, а второй, используя операторы сравнения, в частности оператор «=».

4. Все найденные в результате поиска значения должны содержаться в одной выборке.

Содержание отчета и его форма

Отчет по лабораторной работе должен состоять из:

1) названия лабораторной работы;

2) ответов на контрольные вопросы;

3) формулировки заданий к лабораторной работе и порядка их выполнения.

Отчет о выполнении лабораторной работы в письменном виде сдается преподавателю.

Вопросы для защиты работы

1. Каким образом осуществляется построение условий для выбора записей?

2. Ограничение выборки.

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

4. Каким образом можно найти в базе данных информацию, если её значения принадлежат некоторому диапазону?

5. Выборка с использованием оператора IN.

6. Каким образом организуется выборка информации из базы данных с помощью шаблонов?

7. Какой оператор используется при организации выборки с помощью шаблонов?

8. Для чего используется выборка по шаблону?

9. Можно ли в качестве шаблона использовать символ «%»?

10. Каким образом происходит отмена спецсмысла спецсимволов?

11. Выборка с использованием оператора NOT IN.

12. Выборка с использованием оператора BETWEEN.

13. Можно ли использовать вычисляемые значения и шаблоны в одном запросе?

14. Можно ли использовать шаблоны в конце или в середине слова?

Лабораторная работа 12

Расширенные SQL-запросы. Выборка из связанных
таблиц. Группировка данных

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

Теоретическое обоснование

Выборка из связанных таблиц

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

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

При создании многотабличного запроса в окно конструктора запросов добавляются все участвующие в выборке таблицы и определяются условия их объединения. Таблицу в окно конструктора запросов при организации выборки из нескольких связанных таблиц можно добавить одним из способов:

1) выполните команду F10 > Query > Add Table;

2) нажмите кнопку Add Table.

После этого на экран будет выведено диалоговое окно Add Table or View. В этом окне следует выбрать необходимые для формирования запроса таблицы, затем нажать кнопку Add. Таким образом, выбранные таблицы будут размещены в окне конструктора запросов. Если между участвующими в запросе таблицами в базе данных установлены постоянные отношения, то в окне конструктора запросов эта связь будет отображаться в виде линии, соединяющей таблицы, а на вкладке Join (Объединение) появится запись, содержащая условие объединения таблиц.

Простое соединение

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

SELECT Tab8.kod2, Tab9.NAME

FROM Tab8, Tab9

WHERE Tab8.STOCK= Tab9.STOCK

будет получен список кодов(kod2) и наименований (NAME) проданных покупателям товаров, представленный в таблице 12.1.

Таблица 12.1 - Результат выборки из двух таблиц

Kod2	NAME
2	BORLAND C++
10	PARADOX for Windows
18	Книга TURBO C++
20	Книга TURBO C++

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

Соединение с дополнительным условием

При выполнении выборки информации из базы данных можно использовать механизм соединения с дополнительным условием, которое ограничивает выбираемую пользователем информацию. Использование выборки на основе соединения с дополнительным условием возможно только в том случае, если таблицы, включенные в запрос, связаны по одному и тому же ключевому полю. Например, для выбора из базы данных кодов и фамилий покупателей, которым проданы компьютеры Pentium4, служит SQL- запрос:

SELECT ORDSALE.CUSTOMERNO, ORDSALE.FIRSTNAME

FROM ORDSALE, GOODS

WHERE ORDSALE.STOCK=GOODS.STOCK

and GOODS.NAME=“Pentium4”

Результат выполнения данного запроса представлен в таблице 12.2.

Таблица 12.2 - Результат выборки из двух таблиц с условием

ORDSALE.CUSTOMERNO	FIRSTNAME
2	Иванов
2	Иванов
11	Петров
16	Сидоров
19	Кузнецов

Соединение трех таблиц

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

1. Откройте окно конструктора запросов.

2. Разместите в нем те таблицы, из которых необходимо произвести выборку информации.

3. Перейдите во вкладку Fields.

4. Из списка Select fields выберите наименования полей таблиц, которые будут в дальнейшем включены в запрос.

5. Перейдите во вкладку Join и проверьте правильно ли установлены связи между таблицами.

Для выборки данных из трех таблиц можно использовать и программный код. После ключевого слова WHERE необходимо указать два условия связи таблиц. Пример SQL-запроса для выбора информации из трех связанных таблиц: вывести на экран фамилии и имена всех покупателей, которые приобрели Pentium4.

SELECT DISTINCT CUSTOMER.FIRSTNAME,

CUSTOMER.LASTNAME

FROM CUSTOMER, ORDSALE, GOODS

WHERE CUSTOMER.CUSTOMERNO=ORDSALE.CUSTOMERNO

and ORDSALE.STOCK=GOODS.STOCK

and GOODS.NAME=“Pentium4”

После выполнения данного запроса на экран будет выведена таблица 12.3.

Таблица 12.2 - Результат выборки из трех таблиц

CUSTOMER.FIRSTNAME	CUSTOMER.LASTNAME
Иванов	Андрей
Петрова	Дарья
Сидоров	Максим

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

Использование группировки данных при организации запросов

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

Для выполнения группировки данных в SQL-запросах используется оператор GROUP BY. Оператор GROUP BY перекомпоновывает данные, включаемые в результирующую таблицу. Указание на группировку данных в SQL-запросе осуществляется после ключевого слова FROM. Сам процесс группировки данных при выполнении выборки информации из базы данных в разделы или группы заключается в объединении в одну группу всех строк, которые имеют одно и то же значение поле, указанное после оператора GROUP BY.

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

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

Использование конструкции GROUP BY не предполагает одновременного применения в SQL-запросах и оператора ORDER BY. Для упорядочения результата, полученного после выполнения SQL-запроса, конструкцию ORDER BY <имя поля> необходимо разместить после оператора, указывающего на группировку данных.

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

SELECT kod, SUM(QUANT)

FROM ORDSALE

GROUP BY kod

Использование группировки данных совместно с условием

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

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

SELECT kod, SUM(QUANT)

FROM ORDSALE

WHERE CUSTOMERNO <> 23

GROUP BY kod

служит для выборки информации о товаре, проданном покупателям кроме покупателя с кодом 23.

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

Конструкции GROUP BY свойственно ограничение, которое заключается в том, что она работает только на одном уровне. Невозможно разбить каждую из групп на группы более низкого уровня, а затем применить некоторую стандартную функцию, например, SUM или AVERAGE на каждом уровне группировки.

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

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

SELECT kod FROM ORDSALE GROUP BY kod HAVING COUNT(*)>1

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

Аппаратура и материалы. Для выполнения лабораторной работы необходим персональный компьютер со следующими характеристиками: процессор Intel с тактовой частотой 2000 МГц и выше; оперативная память - не менее 128 Мбайт; свободное дисковое пространство - не менее 800 Мбайт; устройство для чтения компакт-дисков; монитор типа Super VGA (число цветов - 256) с диагональю не менее 17 . Программное обеспечение - операционная система WINDOWS 98 / NT / ME / 2000 / XP, Microsoft Visual FoxPro 8.0.

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

Методика и порядок выполнения работы

Методика и порядок выполнения задания 1

Для выполнения задания 1 следует использовать теоретический материал, приведенный в лабораторных работах 2, 3, 5 и 6 данных методических указаний.

Задание 1. Создайте таблицу «Orders_Field (дополнительные данные приказов)», структура, которой может быть представлена следующими полями:

1) fld_id - уникальный код поля данных в таблице, тип Integer(AutoInc), создать индекс по этому полю, сортировка по возрастанию, поле не может принимать NULL-значения;

2) fld_name - имя поля приказа, тип Character, длина 50, может принимать NULL-значения;

3) fld_value - значение поля приказа, тип Character, длина 100, может принимать NULL-значения;

4) order_id - ссылка на уникальный код приказа в таблице Orders, тип Integer, поле не может принимать NULL-значения.

Заполнить поля созданной таблицы произвольными данными. В заполненной таблице должно быть не менее 10 записей. Cвяжите значения в поле order_id с реальными существующими значениями поля order_id в таблице Orders.

Методика и порядок выполнения задания 2

Для выполнения задания 2 необходимо изучить раздел «Теоретическое обоснование» данной лабораторной работы.

Задание 2. Разработайте SQL-запросы для организации следующих выборок из таблиц «Orders_Field» и «Orders»:

1) выберите все приказы, начинающиеся на букву «п» и имеющие код больше 5;

2) выполните выборку всех приказов из таблицы «Orders», сгруппированных по типу приказа (по полю order_type);

3) произведите выборку всех приказов из таблицы «Orders», имеющих значение поля order_sum больше 100 и сгруппированных по типу приказа (по полю order_type).

Содержание отчета и его форма

Отчет по лабораторной работе должен состоять из:

1) названия лабораторной работы;

2) ответов на контрольные вопросы;

3) формулировки заданий к лабораторной работе и порядка их выполнения.

Отчет о выполнении лабораторной работы в письменном виде сдается преподавателю.

Вопросы для защиты работы

1. Выборка из связанных таблиц. Общая характеристика

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

3. Простое соединение.

4. Соединение с дополнительным условием.

5. Каким образом можно осуществить выборку информации из трех связанных таблиц?

6. Для чего производится группировка данных при организации SQL-запросов?

7. Использование группировки данных совместно с условием.

8. Для чего предназначен оператор HAVING?

Лабораторная работа 13

Расширенные SQL-запросы в Visual FoxPro. Использование квантора существования в запросах. Объединение множеств

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

Теоретическое обоснование

Использование квантора существования в запросах

Квантор существования является понятием, заимствованным из формальной логики. Его смысл заключается в следующем: при задании квантора существования говорят о том, что существует некоторая переменная. В языке SQL квантор существования заменяется оператором EXISTS (существует).

Пусть символ «X» обозначает некоторую произвольную переменную. Тогда в формальной логике оператор с примененным квантором существования EXISTS X (предикат, зависящий от X) принимает значение «истина» тогда и только тогда, когда «предикат, зависящий от X» имеет значение «истина» при каком-либо значении переменной X. Если переменная X может быть любым целым числом, принадлежащим интервалу от 1 до 10, то предикат: EXISTS X (X < 5) принимает значение «истина», тогда как предикат EXISTS X(X < 0) принимает значение «ложь».

В языке SQL предикат с квантором существования представлен может быть представлен выражением вида:

EXISTS (SELECT * FROM …)

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

Пример SQL- запроса с квантором существования для выбора фамилий покупателей, которым продан компьютер «Pentium»:

SELECT NAME FROM tab10

WHERE EXISTS (SELECT * FROM tab11

WHERE tab10.kod= tab11.kod and.STOCK = “Pentium”)

Фактически любой запрос, который может быть выражен с использованием оператора IN, альтернативным образом может быть сформулирован с помощью EXISTS.

Можно сконструировать отрицание существования, используя NOT EXISTS. С помощью SQL-запроса

SELECT NAME FROM tab10

WHERE NOT EXISTS (SELECT * FROM tab11

WHERE tab10.kod= tab11.kod and.STOCK = “Pentium”)

можно получить информацию о покупателях, которые не купили «Pentium».

Заключенный в скобки подзапрос, входящий в конструкцию EXISTS не обязательно использует конструкцию SELECT * ... . В конструкции SELECT можно также указать имя поля, т.е. использовать предложение вида: SELECT имя_поля FROM ... . Однако на практике почти всегда используется вид SELECT * FROM... . Операторы EXISTS и NOT EXISTS всегда помещаются перед подзапросом.

Объединение множеств

Объединением двух множеств называется множество всех элементов, принадлежащих какому-либо одному или обоим множествам. Поскольку отношение является множеством записей, то можно построить объединение двух отношений. Результатом будет отношение, состоящее из всех строк, входящих в какое-либо одно или в оба сразу отношения. Однако строки этих двух отношений должны быть совместимы по объединению.

В языке SQL две таблицы совместимы по объединению и к ним может быть применен оператор объединения UNION тогда и только тогда, когда:

1) они имеют одинаковое число полей, например m;

2) для всех i () i-е поле первой таблицы и i-е поле второй таблицы имеют в точности одинаковый тип данных.

В SQL-92 реализованы некоторые операции реляционной алгебры в явном виде с помощью операторов: UNION, Intersect, EXCEPT, JOIN.

По отношению к таблицам операторы объединения (UNION), пересечения (Intersect) и разности (EXCEPT) можно применять только в случае объединительной совместимости.

Пример 1. SQL-запрос

SELECT kod FROM tab12 WHERE UNITPRICE>1000

UNION

SELECT kod FROM tab13 WHERE CUSTOMERNO=23

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

Использование оператора UNION исключает из результатов выборки повторяющиеся значения. Если это не устраивает пользователя, то вместо оператора UNION используют UNION ALL.Тогда повторы не будут исключены из выборки.

Оператором UNION можно соединить любое количество конструкций SELECT.

Пример 2. С помощью SQL-запроса

SELECT kod FROM tab12 WHERE UNITPRICE>1000

UNION

SELECT kod FROM tab13 WHERE CUSTOMERNO=23

UNION

SELECT kod FROM tab12 WHERE UNITPRICE< 500

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

Если к запросу, приведенному в примере 1, добавить строку OR UNITPRICE < 500, то его результирующая таблица будет аналогична результирующей таблице, соответствующей запросу 2.

Оператор ORDER BY в запрос с использованием оператора UNION может входить только в последнее предложение SELECT. При использовании критерия упорядочивания используются номера полей результирующей таблицы. Пример с включением константы в результирующую таблицу:

SELECT kod,”Стоимость товара >1000$” FROM tab12

WHERE UNITPRICE>1000

UNION

SELECT kod, ”Товар куплен покупателем 23” FROM tab13

WHERE CUSTOMERNO=23

ORDER BY 2,1

Другой синтаксис оператора объединения:

(SELECT*FROM STOCK) UNION (SELECT*FROM ORDSALE)

Еще один вариант запроса:

SELECT*FROM (TABLE tab12 UNION TABLE tab13)

Если эти операции необходимо применить к отдельным столбцам, то используют команду CORRESPONDING BY (имя_поля). В этом случае объединительная совместимость таблиц не требуется, но обязательно указанные поля должны иметь одинаковый тип данных.

Аппаратура и материалы. Для выполнения лабораторной работы необходим персональный компьютер со следующими характеристиками: процессор Intel с тактовой частотой 2000 МГц и выше; оперативная память - не менее 128 Мбайт; свободное дисковое пространство - не менее 800 Мбайт; устройство для чтения компакт-дисков; монитор типа Super VGA (число цветов - 256) с диагональю не менее 17 . Программное обеспечение - операционная система WINDOWS 98 / NT / ME / 2000 / XP, Microsoft Visual FoxPro 8.0.

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