Запросы с использованием функций
Язык запросов SQL в FOXPRO 2.0
В язык FoxPro включен ряд команд из языка запросов SQL. Команды SQL могут непосредственно включаться в программы наряду с собственными командами FoxPro.
Создание баз данных
· CREATE DBF <DBF-файл> (<имя поля>< тип> [(<размер>[,<дробных разрядов>]) [,<имя поля> . . .]])
Команда создает новую базу данных: DBF-файл с указанным именем. Для каждого поля задаются его имя, тип (одной из букв C, N, D, M, F, L), длина и число десятичных разрядов. Длина и точность не задаются для типов дата (D), логический (L) и примечаний (M).
Пример. Создание базы KADR.DBF
CREATE DBF kadr (FAM C(15), TAB N(3), DTR D, POL C(1), SEM C(1), DET N(1), ;
PODR C(15), SZAR N(9), PER M)
Дополнение базы данных
· INSERT INTO <файл БД> [(< поле1> [,< поле2>...] VALUES (<выр1>[,< выр2>...])
Пример.
INSERT INTO kadr (fam, tab, szar) VALUES ('Иванов', 350, 800000)
Формирование запросов
· SELECT < что выводится >
FROM < откуда (источник)>INTO < куда (получатель)>
WHERE < условие >
GROUP BY < колонки, по которым выполняется группирование>
HAVING < условие группирования записей в одну строку>
ORDER BY < в каком порядке выводить данные>
Каждый параметр команды SELECT имеет опции
SELECT [DISTINCT] [<псевдоним >. ]<выражение > [AS <колонка > ]
FROM <БД>[<псевдоним > ] [,<БД > [<псевдоним > ]...]
Псевдоним используется в тех случаях, когда выборка делается из нескольких баз данных, где имена полей совпадают. Выражение может быть полем записи из БД, константой, функцией. Если не устраивают имена, формируемые по умолчанию, можно назначить свои, указав их после слова AS . Такое переименование имеет смысл, если выборка помещается во вновь создаваемую базу данных.
DISTINCT - исключает возможность вывода одинаковых строк в выборке.
INTO получатель
Получателем может быть
ARRAY <массив > - задается вновь создаваемый двумерный массив
CURSOR <курсор > - задается имя Курсора
DBF/TABLE <БД > - новая база данных с указанным именем
Курсор - это временный набор данных, который может быть областью памяти или временным файлом.
Кроме того, данные можно переслать в файл или на принтер.
TO FILE <файл > ADDITIVE / TO PRINTER - выборка посылается в текстовый файл или на принтер. Если используется слово ADDITIVE, то выборка будет добавлена будет добавлена в конец существующего файла без его перезаписи.
GROUP BY < колонка> - производит группирование выходных данных.
HAVING <предикат > - опция задает критерий отбора данных.
ORDER BY <колонка > [ASC/DESC] - опция задает упорядочение по заданной колонке. По умолчанию сортировка выполняется по возрастанию (ASC) или по убыванию (DESC).
Стандартные функции.
COUNT( ) – число значений в столбце.
SUM( ) – сумма значений какого-либо столбца.
AVG( ) – среднее значение.
MAX( ) – самое большое значение в столбце.
MIN( ) – самое малое значение в столбце.
Пример выполнения лабораторной работы
1. Создайте базу данных с таблицами
kadr(fam, tab, dtr, pol, sem, det, podr, szar, per)
brig1(nar, tab, vir)
brig2(nar, tab, vir)
Здесь: fam-имя, tab-табельный номер, pol-пол, sem-семейное положение, det-наличие детей, podr-подразделение, szar-сред.зарплата, per-перемещения по службе, nar-номер наряда, vir-выробатка в штуках
2. Введите данные
3. Сформируйте запросы на языке SQL
Примеры запросов
1. Выборка всех полей из базы данных kadr.dbf
SELECT * FROM kadr
2. Вывод минимального, максимального и среднего значений поля SZAR (средняя зарплата).
SELECT MIN(szar), MAX(szar), AVG(szar) FROM kadr
3. Вывод фамилий всех сотрудников, получающих от 4000 до 7000 рублей.
SELECT fam FROM kadr WHERE szar BETWEEN 4000 AND 7000
4. Выборка названий всех подразделений (поле PODR) предприятия.
SELECT DISTINCT podr FROM kadr
Опция DISTINCT предотвращает повторный вывод одних и тех же названий.
5. Организуйте выборку мужчин из всей базы данных
6. Выдайте список сотрудников с выроботками по первой бригаде
SELECT s.fam, t.vir FROM kadr s, brig1 t WHERE s.tab=t.tab
Здесь для сокращения записи введены псевдопеременные s и t.
7. Выборка фамилий всех родившихся в текущем месяце с указанием дня (числа) рождения, количества лет и премии по этому поводу - 50% от значения средней зарплаты.
SELECT fam, DAY(dtr), 'число', YEAR(DATE())-YEAR(dtr), 'лет',;
'премия', 0.5*szar FROM kadr WHERE MONTH(dtr)=MONTH(DATE())
8. Вывод полей FAM и TAB, отсортированных по полям POL (главное поле) FAM (подчиненное поле) в базу FAMTAB.DBF
SELECT fam, tab FROM kadr ORDER BY pol, fam INTO TABL famtab
Далее содержимое этой базы просмотрите по команде BROWSE
9. Вывод для каждого работника выработок из баз BRIG1 и BRIG2, а также суммарной выработки работника в обеих бригадах.
SELECT kadr, tab, brig1.vir, brig2.vir, brig1.vir+brig2.vir; ;
FROM kadr, brig1, brig3 ; ;
WHERE kadr.tab=brig1.tab AND kadr.tab=brig2.tab
10. Вывод фамилий всех работников, работавших ранее в конструкторском бюро (КБ). Поиск ведется в мемо-поле PER.
SELECT fam FROM kadr WHERE per LIKE "%КБ%"
11. Вывод табельных номеров и суммарной выработки каждого работника в бригаде номер 1. Вывод осуществляется в порядке увеличения табельных номеров.
SELECT tab, SUM(vir) FROM brig1 GROUP BY tab ORDER BY tab
12. Вывод названий всех подразделений, количества сотрудников и значений суммарной заработной платы (фонда оплаты). Информация выводится только для подразделений, где количество сотрудников больше пяти.
SELECT podr, COUNT(*), SUM(szar) FROM kadr GROUP BY podr HAVING COUNT(*)>5
Сформулируйте запросы самостоятельно
Пусть будет база данных поставок, состоящая из трех таблиц:
S (N_P, FAM, SOST, Gorod) - таблица поставщиков
P (N_D, Name, Color, Ves, Gorod) - таблица деталей
SP(N_P, N_D, Kol) - таблица поставок
1. Произведите полные выборки из таблиц S, P, SP и просмотрите данные
2. Выберите поставщиков, находящихся в городе Перми и имеющих состояние не более 20
3. Произведите выборку поставщиков в порядке убывания их состояния
4. Выдайте сведения о поставщиках и деталях, находящихся в одинаковых городах.
Подзапросы
5. Выдать фамилии поставщиков, которые поставляют деталь Р2
Используйте обычный запрос с подзапросом и коррелированный подзапрос.
6. Выдать фамилии поставщиков, которые поставляют, по крайней мере, одну красную деталь
7. Выдать номера поставщиков, которые поставляют, по крайней мере, одну деталь, поставляемую поставщиком S2. Используйте псевдонимы (алиасы).
8. Выдайте сведения о поставщиках, которые поставляют деталь Р3 с использованием квантора существования EXISTS.
Запросы с использованием функций
9. Подсчитать количество записей в SP
10. Вычислить общий объем поставок для каждой детали
11. Выдать номера поставщиков со значением поля SOST меньшем чем текущее максимальное состояние.