 |
SQL – оператор INSERT INTO
|
|
|
|
INSERT INTO Poshk_k nomp, famp, gord, stat
VALUES (“П6”, “БЫКОВ”, “МОСКВА”, 10)
Другой синтаксис этой команды:
INSERT INTO <имя ТБД> FROM ARRAY <массив> FROM MEMVAR
Переносит данные, содержащиеся в указанном <массиве> (опиция ARRAY), или данные из временных переменных (опция MEMVAR). Такие переменные должны существовать и иметь те же самые имена, что и поля базы данных (но с префиксом M). Такие переменные, например, вырабатываются по команде SCATTER MEMVAR. Поля, для которых нет переменных с подходящими именами, останутся пустыми.
Таким образом, SQL-оператор INSERT INTO соответствует паре команд базового языка СУБД FoxPro – APPEND BLANK и REPLACE.
Дополняемая ТБД может быть и не открыта к моменту выполнения команды, однако после этого она останется открытой и активной.
7.1.4 Формирование запросов из ТБД командным способом
Команда SELECT является мощным средством обработки запросов. С ее помощью из ТБД-источника выделяются нужные данные и пересылаются на экран, в файл-приемник или в массив. Данные могут быть извлечены из разных ТБД, а также сгруппированы и упорядочены желаемым образом.
Рассмотри полный формат команды SELECT.
SELECT
[DISTINCT] [<псевдоним>.]<выражение> [AS <колонка>]
[,[<псевдоним>.]<выражение> [AS <колонка>]…]
FROM <ТБД> [,<ТБД> …]
[[INTO <получатель> ]/[TO FILE <файл>
[ADDITIVE]/TO PRINTER]]
[NOCONSOLE] [PLAIN] [NOWAIT]
[WHERE <условие связи>
[AND <условие связи>…]
[AND/OR <условие отбора>
[AND/OR <условие отбора> …]]]
[GROUP BY <колонка> [, <колонка> …]]
[HAVING <условие отбора>]
[ORDER BY <колонка> [ASC/DESC] [,<колонка> [ASC/DESC]…]]
Термин «колонка» здесь очень близок к понятию «поле ТБД», но может быть и выражением. Кроме того, вследствие выборки мы можем получить как новую ТБД, так и текстовый файл, или даже только отображение на экране, т.е. колонки.
|
|
|
Команда SELECT допускает включение в себя других внутренних команд SELECT (формирование подзапросов).
Указание результатов выборки и источников данных
SELECT [DISTINCT] [<псевдоним>.]<выражение> [AS <колонка>]
FROM <ТБД> [<псевдоним>] [,<ТБД> [<псевдоним>] …]
Здесь указывается, что и откуда берется в выборку. Перед словом FROM перечисляются отбираемые <выражения>, а после – имена ТБД, из которых берутся данные.
<Выражение> может быть полем записи из ТБД, константой (выводимой в каждой строке выборки), функцией (в том числе и пользовательской функцией - ПФ) от переменных, полем и т.п. Если <выражение> является именем поля, то оно может быть составным (с включением имени ТБД или псевдонима), в особенности, если выборка делается из нескольких ТБД, где имена полей совпадают. Псевдоним может быть не только «официальный» псевдоним (ALLIAS) таблицы БД, но и любое другое имя, которое вы ей присвоите в команде SELECT. Это задаваемое временное имя указывается в опции <псевдоним> после слова FROM. Никаких последствий за пределами команды SELECT такое назначение не имеет.
Если необходимо построить выборку из всех полей ТБД, вместо их перечня можно указать символ «*».
В результате выполненной выборки получается совокупность колонок, заголовками которых могут быть имена полей. Если имена совпадают, то такие колонки получают совпадающие имена, к которым присоединяется одна из букв (по алфавиту), например, nomp_A, nomp_B и т.д. Аналогичным образом даются имена колонкам, полученным в результате вычисления выражений. Их имена состоят из слова EXP_1, EXP_2 и т.д. Исключения составляют выражения, использующие собственные функции SQL, например, функции MIN(), MAX() и др. Имена колонок в этом случае будут включать имена функций.
Если нас не устраивают имена, формируемые по умолчанию, можно назначить свои, указав их после слова AS в виде:
|
|
|
<выражение> AS <новое имя колонки>
Такое переименование имеет смысл, если выборка помещается во вновь создаваемую ТБД. Имя дается по правилам, принятым в FoxPro, оно должно быть задано латинскими символами.
В <выражении> могут быть использованы любые функции FoxPro. Кроме того, здесь есть еще собственные специальные арифметические функции, действующие «по вертикали». Это функции вычисления среднего, минимального и максимального значений, суммирования, а также количества записей:
AVG(<выр>), MIN(<выр>), MAX(<выр>), SUM(<выр>),COUNT(<выр>).
Последняя функция может иметь в качестве аргумента звездочку (COUNT(*)), что означает подсчет всех записей, попавших в выборку.
Включение опции DISTINCT исключает возможность вывода одинаковых строк в выборке - только для одного поля.
Указание объекта, куда пересылается выборка
Следующие опции задают «получатель» данных выборки. Им может быть ТБД, массив, текстовый файл, экран и принтер.
Кроме того, информация может быть переслана в так называемый Курсор (будем писать с большой буквы). Курсор – это временный набор данных, который может быть областью памяти или временным файлом FoxPro (этот процесс от нас не зависит) и имеет режим «Только чтение». Данные Курсора могут быть, например, предъявлены в команде BROWSE, напечатаны, из них может быть образовано меню и т.д. Курсор может быть обработан другой командой SELECT. К колонкам Курсора надо обращаться по имени этих колонок, возможно, с префиксом – именем Курсора (через точку).
Итак:
INTO <получатель>
<Получатель> может быть одного из следующих типов:
ARRAY <массив> - здесь вновь создаваемый двумерный <массив>
CURSOR <курсор> - задается имя Курсора.
DBF/TABLE <БД> - новая таблица базы данных с указанным именем. Слова DBF и TABLE здесь являются синонимами.
Кроме того, данные можно переслать в файл или на принтер.
TO FILE <файл> [ADDITIVE]/TO PRINTER – выборка посылается в текстовый <файл> или на принтер. Если используется слово ADDITIVE, то выборка будет добавлена в конец существующего файла без его перезаписи.
Следующие опции имеют смысл только при выдаче на экран (команда используется без слова INTO):
|
|
|
NOCONSOLE – выборка не выдается на экран.
PLAIN – заголовки колонок не выдаются.
NOWAIT – не делаются паузы при заполнении экрана.
Критерий отбора данных
WHERE <условие связи> [AND <условие связи> …]
[AND/OR <условие отбора> [AND/OR <условие отбора> …]]
Здесь:
<Условие связи> - применяется в случае, если выборка делается более чем из одной ТБД, и указывает критерий, которому должны отвечать поля из разных таблиц. В условии связи указываются поля из разных таблиц. Здесь разрешается использовать знаки отношения =, #, ==, >, >=, <, <=. Допускается задание нескольких критериев, соединенных знаком AND.
<Условие отбора> - строится аналогично, но из выражений только одной ТБД, и допускается использование логических операторов OR и NOT.
Условия, кроме любых функций FoxPro, могут содержать следующие операторы SQL:
LIKE – позволяет построить условие сравнения по шаблону, где символ “ _ ” указывает единичный неопределенный символ в строке, “ % ” – любое их количество. Эти символы аналогичны символам маски “? ” и “ * ” в MS DOS. Формат оператора:
<выражение> LIKE <шаблон>
BETWEEN – проверяет, находится ли выражение в указанном диапазоне. Формат оператора:
<выражение> BETWEEN <нижнее знач.> AND <верхнее знач.>
IN – проверяет, находится ли выражение, стоящее слева от слова IN, среди перечисленных справа от него. Формат оператора:
<выражение> IN (<выражение>,<выражение>,…)
Все указанные операторы можно комбинировать с помощью связок OR, AND, NOT и скобок. Операторы LIKE и BETWEEN не следует путать с одноименными функциями FoxPro, которые, впрочем, также можно использовать.
Группирование данных
GROUP BY <колонка>[,<колонка>…] – задаются колонки, по которым производится группирование выходных данных. Все записи ТБД, для которых значения колонок совпадают, отображаются в выборке единственной (итоговой) строкой. Группирование удобно для получения некоторых сводных характеристик (сумм, количеств записей, средних) группы.
HAVING <условие отбора> - опция задает критерий отбора данных в каждую сформированную в процессе выборки группу.
|
|
|
Сортировка
ORDER BY <колонка> [ASC/DESC][,<колонка> [ASC/DESC]…] – опция задает упорядочение по заданной колонке/колонкам. По умолчанию сортировка выполняется по возрастанию (ASC), но может быть задана и по убыванию (DESC).
Примеры запросов:
1. Выборка всех полей из ТБД POSHK.DBF. Все колонки выборки будут иметь имена полей ТБД.
SELECT * FROM Data1!POSHK
2. Вывод минимального, максимального и среднего значений поля KOLW (среднее значение). Колонки получат имена MIN_ KOLW, MAX_ KOLW и AVG_ KOLW.
SELECT MIN(KOLW),MAX(KOLW),AVG(KOLW) FROM Data1!POSHK
3. Вывести номера поставщиков, поставляющих количество деталей от 500 до 1200 руб.
SELECT nomp, kolw FROM Data1!POSKI WHERE kolw BETWEEN 500 AND 1200
4.Вывод фамилий всех поставщиков, кроме поставщиков не проживающих в МОСКВЕ и КИЕВЕ.
SELECT famp, gord FROM Data1!POSHK WHERE gord NOT IN (‘ МОСКВА ’,‘ КИЕВ ’)
5.Выборка количества поставок без повторений.
SELECT DISTINCT nomp,kolw FROM data1!poski
6. Выборка фамилий поставщика по первой букве (из POSHK.DBF).
SELECT famp FROM poshk WHERE famp="С*"
7. Выборка всех номеров и наименований деталей файла из DETL.DBF, сцепленных с номерами деталей из ТБД poski.DBF, для записей, у которых номера деталей равны значению «Д5».
SELECT detl.nomd, detl.naim, poski.kolw, poski.data;
FROM Data1!detl, Data1!Poski;
Where detl.nomd=” Д5”.
8.Если мы хотим задать собственные имена колонкам, а не использовать умолчания, нужно воспользоваться опцией AS.
Пусть нужно вывести номера поставщиков и фамилии (поля nomp и famp) по алфавиту и с другими именами колонок, например, Номер_поставщика и Фамилия_поставщика.
SELECT nomp AS Номер_поставщика, famp AS Фамилия_поставщика FROM Data1!POSHK ORDER BY famp
9. Вывод полей nomd и cena из файла DETL, kolw и data из файла POSKI с расчетом суммы за поставки и с расчетом скидки. Присвоение имен Сумма и Скидка полям вычисленной суммы и скидки.
SELECT detl.nomd, detl.cena, poski.kolw, poski.data, (poski.kolw * detl.cena) as Сумма, (poski.kolw * detl.cena)*0.2 as Скидка FROM data1!detl, poski
Чтобы увидеть содержимое этой ТБД, можно, например, сразу ввести команду BROWSE.
10. Вывод из ТБД POSHK.DBF всех поставщиков, имеющих в фамилии сочетания букв "РО", и их города.
SELECT poshk.famp, poshk.gord FROM data1!poshk WHERE poshk.famp LIKE "%РО%"
Команда DELETE (SQL-оператор)
DELETE FROM [DatabaseName!]TableName
[WHERE FilterCondition1 [AND|OR FilterCondition2…]]
Эта команда помечает записи для удаления.
7.2 Оператор SELECT, формируемый интерактивным способом
Вход в ДО «Конструктор запросов»
1 СПОСОБ: ДО Project Manager - Data (вкладка) - Queries – New (как показано на рис.7.1)
2 СПОСОБ: File - New - Query - New file (как показано на рис.7.2)
|
|
|
Рис.7.1 Создание нового запроса через ДО Project Manager
|
Рис.7.2 Создание нового запроса
|
|
|
Результат: появляются два окна: “ Add table or View ” (см.рис.7.3) и “ Query Designer ” (см.рис.7.4).
Рис.7.3 ДО Add Table or View
Рис.7.4 ДО Query Designer
Работа в конструкторе запросов
Вкладки нижней части ДО Query Designer представлена в табл.7.1
Таблица 7.1 ДО Query Designer
| Вкладка | Назначение |
| Fields | Указывает поля исходных ТБД, выбираемые в результирующую таблицу, т.е. в таблицу результата запроса |
| Join | Определяет условия объединения таблиц |
| Filter | Определяет фильтры, накладываемые для выбора записи |
| Order By | Определяет критерий упорядочивания |
| Group By | Условия группировки данных |
| Miscellaneous | Дополнительные установки, такие, как признак выборки повторяющихся значений, количество или % выбранных данных. |
SELECT * FROM data!poshk;
WHERE ((STAT>=10); and (FAMP BETWEEN ‘Иванов’ and ‘СИДОРОВ’))
OR GORD Like ‘%Киев%’
Выбрать все поля соответствующих строк из ТБД poshk, где значение столбца (поля) stat >= 10, а также значение поля famp, находящихся в промежутке от Иванов до Сидоров, или же значением поля gord является Киев.
Файлы запросов на языке SQL и результаты их выполнения.
Запрос 1: запрос по точному совпадению значения одного поля
Из ТБД POSHK: выдать номера, фамилии и города поставщиков, проживающих в городе Киев.
Из ДО Add Table or View необходимо выбрать таблицу Poshk, как показано на рис. 7.5, и нажать кнопку Add, после чего закрыть ДО кнопкой Close.
|
|
Рис.7.5 Добавление таблицы Poshk для создания запросов
Зоны вкладки Fields ДО Query Designer представлены на рис.7.6, 7.7 Соответственно.
Available fields. Все поля, выбранные из БД.
Selected Fields в исходный момент пуста. Выделить строку Poshk.nomp – Add. Заполнить зону Selected fields (famp, gord) (как показано на рис.7.6).
|
|
Рис.7.6 Вкладка Fields ДО Query Designer
Перейти на вкладку Filter: Field name - поле БД. Field name - щёлкнуть в поле для выделения всех полей. Выбрать Poshk.gord. Not - отрицание условия. Case – неточное соответствие условию. Criteria - критерий выборки (=). В поле Example ввести символьную информацию в «...»: Poshk.gord = ”Киев” (как показано на рис.7.7).
Рис.7.7 Вкладка Filter ДО Query Designer
Предварительный просмотр текста запроса на языке SQL:
1) кнопка SQL на панели Query designer
2) правая кнопка в окне Query Designer - View SQL
3) Query – view SQL (как показано на рис.7.8)
Сохранение текста запроса: File – Save As
|
|
|
Рис.7.8 Способы предварительного просмотра текста запроса
Запуск запроса на исполнение
Query – Run query или нажать кнопку Run на стандартной панели инструментов
query1.qpr:
SELECT Poshk.nomp, Poshk.famp, Poshk.gord, Poshk.famp, Poshk.nomp;
FROM;
data1!poshk;
WHERE Poshk.gord = ("КИЕВ")
Запрос 2: запрос по точному несовпадению значения одного поля
В таблице Poshk дать фамилии и города поставщиков, не проживающих в городе Киев.
См. запрос 1. Все так же + поставить флажок в зоне Not: Poshk.gord not = “Киев”.
Query2.qpr:
SELECT Poshk.nomp, Poshk.famp, Poshk.gord;
FROM;
data1!poshk;
WHERE Poshk.gord <> ("КИЕВ")
Запрос 3: запрос по неточному совпадению значения одного поля
Скопировать POSHK в POSHK_K. В файле POSHK_K создать дополнительные поля: firstname, lastname. Дополнить записи. Запрос: выбрать поставщиков, имена которых начинаются на "ИВА" или "Ива".
Query3.qpr:
SELECT Poshk_k.famp, Poshk_k.name, Poshk_k.lastname;
FROM;
data1!poshk_k;
WHERE Poshk_k.famp = ("ИВА");
OR Poshk_k.famp = ("Ива")
Запрос 4: запрос с вычисляемым полем
Из таблицы poshk_k выбрать: номер поставщика, его ФИО, объединив ФИО в один столбец с именем "ФИО". Уникальное условие запроса: ALLTRIM при объединении удаляет лишние концевые пробелы значения поля.
На вкладке Fields выбрать поле nomp и добавить его в зону Selected Fields. На вкладке Fields в строке Functions and Expressions ввести выражение:
(Alltrim(famp)+” “+ Alltrim(firstname)+” “ +Alltrim(lastname)).
Нажать Add для добавления выражения в зону Selected Fields.
Query4.qpr:
SELECT Poshk_k.nomp,;
ALLTRIM(Poshk_k.famp)+" "+ALLTRIM(Poshk_k.firstname)+" "+ALLTRIM(Poshk_k.lastname) AS фио;
FROM;
data1!poshk_k
Query5.qpr:
Из ТБД POSKI: выдать информацию за дату 18/09/01.
SELECT *;
FROM;
data1!poski;
WHERE Poski.data = CTOD("18/09/01")
Query6.qpr:
Из ТБД POSKI: выдать информацию о NOMP, NOMD, NOMR при количестве поставленных деталей >=400 деталей за дату 18/09/01.
SELECT *;
FROM;
data1!poski;
WHERE Poski.kolw >= (400);
AND Poski.data = CTOD("18/09/01")
Query7.qpr:
Из таблицы poski:
1. выбрать nomr, nomd;
2. сохранить результат запроса в области ОП cursor под именем nomrd;
3. вывести результат запроса из ОП на экран.
SELECT Poski.nomr, Poski.nomd;
FROM;
data1!poski;
ORDER BY Poski.nomr, Poski.nomd;
INTO CURSOR Nomrd
*Способы сохранения результата выборки не на экране:
*1) Команда ГМ: Query - Query destination
*2) Правой кнопкой мыши щелкнуть на пустом месте бланка запроса и выполнить команду Output Settings
browse
Query8.qpr:
Выдать из таблицы poshk_k поставщиков с фамилией, начинающейся на "И".
SELECT Poshk_k.nomp, Poshk_k.famp, Poshk_k.name, Poshk_k.lastname,;
Poshk_k.gord, Poshk_k.stat;
FROM;
data1!poshk_k;
WHERE Poshk_k.famp = ("И")
Query9.qpr:
Из POSKI подсчитать: общее количество записей, сумму, среднее, максимальное и минимальное значения по полю количество и дата поставок - подсчитать. Информацию сохранить в области ОП "difficult".
SELECT COUNT(*), SUM(Poski.kolw), AVG(Poski.kolw), MAX(Poski.kolw),;
MIN(Poski.kolw), MAX(Poski.data), MIN(Poski.data);
FROM;
data1!poski;
INTO CURSOR difficult
Browse
Многотабличные запросы
query10.qpr:
Выдать номера поставщиков, их ФИО, номера и наименование деталей, номера разработчиков, количество и даты поставок при условии:
1. nomd <= "Д3";
2. диапазон дат от 09/12/01 до 09/20/01;
3. порядок выдачи результата: nomp, nomd, nomr;
4. результат сохранить в файле mnt_pr;
5. вывести результат из файла на экран.
SELECT Poshk.nomp, Poshk.famp, Detl.nomd, Detl.naim, Poski.nomr, Poski.kolw,;
Poski.data;
FROM;
data1!poshk;
INNER JOIN data1!poski;
ON Poshk.nomp = Poski.nomp;
INNER JOIN data1!detl;
ON Poski.nomd = Detl.nomd;
WHERE Detl.nomd <= ("Д3");
AND Poski.data BETWEEN CTOD("09/12/01") AND CTOD("09/20/01");
ORDER BY Poshk.nomp, Detl.nomd, Poski.nomr;
INTO TABLE mnt_pr.dbf
USE mnt_pr
BROWSE
query11.qpr:
Продолжение многотабличных запросов mnt_pr, proek, выдать то же + наименование проекта.
SELECT Mnt_pr.nomp, Mnt_pr.famp, Mnt_pr.nomd, Mnt_pr.naim, Mnt_pr.nomr,;
Proek.npro, Mnt_pr.kolw, Mnt_pr.data;
FROM;
data1!mnt_pr;
INNER JOIN data1!proek;
ON Mnt_pr.nomr = Proek.nomr;
ORDER BY Mnt_pr.nomp, Mnt_pr.nomd, Mnt_pr.nomr;
INTO CURSOR Ask
BROWSE
query12.qpr:
Выбранные записи упорядочить по фамилии и имени разработчика. Информацию сохранить в области ОП "Z11".
SELECT Poshk.famp, Poski.nomp, SUM(Poski.kolw), COUNT(*) AS p1;
FROM;
data1!poshk;
INNER JOIN data1!poski;
ON Poshk.nomp = Poski.nomp;
GROUP BY Poshk.famp, Poski.nomp;
INTO CURSOR Z11
BROWSE
Сложные запросы.
max.prg:
Переименование полей в запросе.
SELECT COUNT(*), SUM(Poski.kolw), AVG(Poski.kolw), MAX(Poski.kolw),;
MIN(Poski.kolw), MAX(Poski.data), MIN(Poski.data);
FROM data!poski;
INTO CURSOR difficult
browse;
title "Сложный запрос";
FIELDS Cnt: H = "Всего записей",;
Sum_kolw: H = "Сумма всего",;
Avg_kolw: H = "Среднее кол-во",;
Max_kolw: H = "Макс. кол-во",;
Min_kolw: H = "Мин. кол-во",;
Max_data: H = "Макс. дата",;
Min_data: H = "Мин. дата"
mnt.prg:
SELECT Poshk.nomp, Poshk.famp, Detl.nomd, Detl.naim, Poski.nomr, Poski.kolw,;
Poski.data;
FROM;
data1!poshk;
INNER JOIN data1!poski;
ON Poshk.nomp = Poski.nomp;
INNER JOIN data1!detl;
ON Poski.nomd = Detl.nomd;
WHERE Detl.nomd <= ("Д3");
AND Poski.data BETWEEN CTOD("09/12/01") AND CTOD("09/20/01");
ORDER BY Poshk.nomp, Detl.nomd, Poski.nomr;
INTO TABLE mnt_pr.dbf
USE mnt_pr
BROWSE
SELECT Mnt_pr.nomp, Mnt_pr.famp, Mnt_pr.nomd, Mnt_pr.naim, Mnt_pr.nomr,;
Proek.npro, Mnt_pr.kolw, Mnt_pr.data;
FROM;
data1!mnt_pr;
INNER JOIN data1!proek;
ON Mnt_pr.nomr = Proek.nomr;
ORDER BY Mnt_pr.nomp, Mnt_pr.nomd, Mnt_pr.nomr;
INTO CURSOR Ask
BROWSE
nomr_nomd.prg:
Оформление заголовков в шапке запроса
SELECT Poski.nomr, Poski.nomd;
FROM data1!poski;
ORDER BY Poski.nomr, Poski.nomd;
INTO CURSOR Nomrd
BROWSE;
title "Ctrl+N - вставка Ctrl+W - Выход Ctrl+T - удаление";
fields Nomr: H = "Номер разработчика": 27,;
Nomd: H = "Номер детали": 20
zap_k_vosm1.prg:
SELECT *;
FROM vozm;
WHERE vozm.nomp = "П5";
ORDER BY vozm.nomd
zap_k_vosm2.prg:
SELECT *;
FROM vozm;
WHERE vozm.nomd = "Д6";
ORDER BY vozm.nomp
Вложенные запросы.
vlog_zapr1.prg:
Выдать famp поставщиков, осуществившего наибольшее (наименьшее);
количество участий в поставках.
CLEAR
SELECT poshk.famp, poshk.nomp, sum(poski.kolw), COUNT(*) as Р1;
FROM data1!poshk INNER JOIN data1!poski;
ON poshk.nomp = poski.nomp;
GROUP BY poshk.nomp,poshk.famp;
INTO CURSOR Z11
BROWSE
WAIT
SELECT MAX (Р1) AS MP;
FROM Z11;
INTO CURSOR Z112
BROWSE
WAIT
SELECT Z11.famp, MP;
FROM Z11, Z112;
WHERE Р1 = MP;
INTO CURSOR Z113
BROWSE
WAIT
RETURN
SQL – оператор INSERT INTO
INSERT INTO db1_name [(bname1[,bname2,…])]
имя таблицы для список полей таблицы
добавления записи
VALUES (eExpression1 [,eExpression2,…])
список заносимых значений в эти поля
Если список полей опущен, то значения должны быть указаны в таком же порядке, в каком они определены в строке таблицы.
USE poshk_k
|
|
|
