Функции для обработки символьных выражений
LEN (< строка >) – определение длины строки.
АТ (<строка1>, <строка2>) – поиск первого вхождения строки <строка1> в строку <строка2> (результатом является номер позиции, в которой вхождение найдено).
UPPER (<строка>) – преобразование в строке латинских букв нижнего регистра в буквы верхнего регистра.
LOWER (<строка>) – преобразование в строке латинских букв верхнего регистра в буквы нижнего регистра.
ISALPHA (<строка>) – выполнение проверки, является ли первый символ строки буквой.
ISLOWER (<строка>) – выполнение проверки, является ли первый символ строки буквой нижнего регистра.
ISUPPER (<строка>) – выполнение проверки, является ли первый символ строки буквой верхнего регистра.
LEFT (<строка>, <n>) – вывод подстроки длиной n символов из указанной строки <строка>, начиная с 1‑й позиции.
RIGHT(<строка>, <n>) – вывод подстроки длиной n символов из указанной строки <строка>, начиная с последней позиции.
RTRIM (<строка>) – удаление завершающих пробелов в строке.
REPLICATE (<строка>, <n>) – копирование строки n раз.
SPACE (<n>) – формирование строки из n пробелов.
STUFF (<строка1>, <n>, <k>, <строка2>) – преобразование строки символов <строка1> путем удаления k символов и ввода строки <строка2> , начиная с n –го символа.
SUBSTR (<строка>, <n> [,<k>]) – формирование подстроки из строки <строка> длиной k символов, начиная с n –го символа.
VAL (<строка>) – преобразование строки символов, состоящей только из цифр, в числовое выражение.
Основная литература: 1[117:121], 2[20:23].
Контрольные вопросы:
1. Какая команда суммирует содержимое числового поля.
2. Какая функция преобразует строку символов в числовое выражение.
3. Какая функция определяет, достигнут ли конец файла.
4. Какая функция позволяет преобразовать буквы нижнего регистра в буквы верхнего регистра.
5. Какие возможные типы переменных могут существовать в языке в xBase.
Тема 5. Программирование в СУБД.
Для организации различных видов представления и обработки данных, хранящихся в базах данных, разрабатываются приложения - прикладные программы. Они могут разрабатываться как в универсальных инструментальных средах программирования, так и в среде программирования СУБД. При программировании в среде dBase-подобных СУБД в программы можно включать все команды и операторы языка программирования xBase.
Имя файла программы имеет расширение .PRG.
Это текстовый файл, который можно создать и корректировать в любом текстовом редакторе или в среде СУБД, в которых имеется встроенный редактор. Его создание или вызов осуществляется командой:
MODIFY COMMAND <имя_файла>
Если имя файла не указано, то система выведет подсказку о вводе имени файла. Если указанный файл не существует, система создаст его. Для изменения программного файла необходимо использовать эту же команду: MODIFY COMMAND <имя_файла>.
При наборе программы на одной строке записывается только одна команда. Если команда не поместилась на одной строке, она переносится на следующую строку, а в конце предыдущей строки ставится символ ;. Комментарии в конце строки указываются после двух символов && (амперсанд), в начале строки – после символа *.
Программа не имеет признаков начала и конца, строки не нумеруются.
Чтобы набранная программа записалась в указанный файл, нужно одновременно нажать клавиши CTRL и END (^ END или ^W).
Команда запуска программы на выполнение имеет формат:
DO <имя файла>.
Когда вызывается программный файл, команды в его составе выполняются в порядке их следования с учетом управляющих операторов xBase. Выполнение командного файла будет продолжаться до того момента, пока не встретится одна из команд RETURN, CANCEL, QUIT или не будет достигнут конец программы.
Команда CANCEL прерывает выполнение программы, при этом очищаются все собственные переменные памяти. RETURN является командой, завершающей программу, но ее использование необязательно, так как выполнение последней команды программы автоматически приведет к выполнению оператора RETURN. Команда QUIT осуществляет выход из СУБД.
При обнаружении ошибки выполнение программы автоматически прерывается и исходный текст программы представляется в окне текстового редактора, при этом строка с предполагаемой ошибкой выделяется. В диалоговом окне выдается сообщение об ошибке и предлагаются возможные действия пользователя.
В программе можно использовать подпрограммыи процедуры. Каждую подпрограмму отдельно создают командой MODIFY COMMAND <имя_подпрограммы>. Каждой подпрограмме соответствует свой файл на диске с уникальным именем и расширением .PRG. Вызывается подпрограмма командой:
DO <имя_подпрограммы> [WITH<список_параметров>]
Параметр WITH позволяет передавать программе требуемые параметры. В этом случае в начале подпрограммы должны указываться параметры. Команда указания параметров подпрограммы имеет формат:
PARAMETERS <список_параметров>
Команда PARAMETERS позволяет назначить данным, передаваемым вызывающей программой, имена локальных переменных. Эта команда должна быть первой в вызванной подпрограмме. Параметром в списке параметров может быть любая переменная памяти с доступным именем. Параметры в списке параметров разделяются запятыми и должны соответствовать количеству параметров, приведенных в списке вызывающей программы.
Команда возврата управления из подпрограммы вызвавшей программе имеет формат:
RETURN [ TO MASTER / <выражение>]
Параметр TO MASTER указывает, что управление передается в вызвавшую программу самого верхнего уровня, так как возможен вызов подпрограммы из подпрограмм. Если программа является процедурой – функцией, то возвращаемое значение указывается в выражении.
При использовании в программе большого количества подпрограмм, на обращение к ним требуется значительное время – время считывания их с диска. Для увеличения быстродействия вместо подпрограмм используют процедуры. Они могут располагаться после основной программы или должны быть объединены в отдельный файл – процедурный. При использовании процедурного файла в основной программе должна быть выполнена команда установки:
SET PROCEDURE TO <имя_процедурного_файла>
Процедурный файл должен быть закрыт после его использования. Команда закрытия процедурного файла:
CLOSE PROCEDURE
Вызывается процедура, как и подпрограмма, командой:
DO <имя_процедуры> [WITH<список_параметров>]
Команда начала каждой процедуры в программе имеет формат:
PROCEDURE <имя_процедуры>
[PARAMETERS <список_параметров>]
Необязательная вторая команда (PARAMETERS) используется, если вызов процедуры осуществляется с указанием передаваемых параметров. Команда окончания процедуры: RETURN.
При выполнении программ возникает необходимость отслеживания некоторых условий: появления ошибки, нажатия клавиши ESC, нажатия любой клавиши, нажатия определенной клавиши и т.д.
Команда отслеживания ошибки имеет формат:
ON ERROR [<команда>]
При появлении ошибки будет выполняться указанная команда.
Команда отслеживания нажатия клавиши ESC имеет формат:
ON ESCAPE [<команда>]
При нажатии на клавишу ESC будет выполняться указанная команда.
Команда отслеживания нажатия любой клавиши имеет формат:
ON KEY [<команда>]
При нажатии на любую клавишу выполняется указанная команда.
Команда отслеживания нажатия определенной клавиши имеет формат:
ON KEY=<числовое_выражение> <команда>
При нажатии клавиши, код которой соответствует числовому выражению, будет выполняться указанная команда.
Если используется несколько команд ON, их приоритет устанавливается в следующем порядке: ERROR, ESCAPE, KEY. Все команды остаются активными до тех пор, пока не будет обнаружена следующая команда ON, использующая то же условие. Отслеживание условий может быть отключено при использовании этих же команд без параметра <команда>:
ON ERROR / ON ESCAPE / ON KEY
Для включения команд MS DOS в программу, написанную на языке программирования xBase, необходимо использовать команду RUN:
RUN <команда_MS_DOS>
Команда SUSPEND позволяет приостановить выполнение программы. Команда RESUME продолжает выполнение приостановленной программы.
Программа, составленная на языке программирования xBase, может быть выполнена различными способами. Исходные программы (командные файлы с расширением .PRG) выполняются в СУБД, использующей язык программирования xBase, при выполнении команды: DO <имя_программы>
Исходные программы (командные файлы с расширением .PRG) можно откомпилировать. При этом создаются новые файлы с теми же именами, но с другим расширением (например, в FoxPro с расширением .FXP). Откомпилированные программы загружаются с диска со скоростью их считывания, в то время как исходные программы должны компилироваться по мере их считывания, что приводит к дополнительным временным задержкам. Поэтому рекомендуется выполнять предварительную компиляцию программ.
В именах программ можно использовать трафаретные символы используемой ОС, что позволяет компилировать несколько программ посредством одной команды. При наличии исходной и откомпилированной программы СУБД всегда выполняет компилированную версию программного файла.
Современные версии dBase-подобных СУБД позволяют создавать исполняемые файлы программ (с расширением .EXE). Эти файлы можно выполнять автономно без СУБД, но при наличии специальных библиотек.
В языке программирования xBase имеется несколько управляющих операторов, широко используемых при написании программ: оператор условия, оператор выбора и операторы цикла. В интерактивном режиме использование операторов невозможно.
Оператор условия имеет формат:
IF <условие>
<группа_команд_1>
[ ELSE <группа_команд_2>]
ENDIF
В этом операторе выражение <условие> анализируется и если оно истинно – выполняется <группа_команд_1>. При наличии параметра ELSE и ложности выражения <условие>, выполняется <группа_команд_2>.
Если выражение <условие> ложно и параметр ELSE отсутствует, осуществляется выход из оператора IF…ENDIF.
Выражения IF <условие> могут быть вложены одно в другое, при этом для каждого IF требуется соответствующее ENDIF.
При необходимости проверки нескольких условий вместо вложенных операторов условий удобнее использовать оператор выбора, имеющий следующий формат:
DO CASE
CASE < условие_1>
<группа_команд_1>
CASE < условие_2>
<группа_команд_2>
…...................................................................
CASE < условие_N >
<группа_команд_N>
[ OTHERWISE
<группа_команд>]
ENDCASE
В этом операторе анализируются <условие_1>, <условие_2>, …, <условие_N> по одному в поисках первого логически истинного условия. При обнаружении истинного условия выполняется соответствующая <группа_команд_i>, после чего управление передается первой команде, следующей за ENDCASE. В операторе DO CASE всегда выполняется только одна <группа_команд_i>, следующая за первым из обнаруженных истинных условий. Если ни одно из истинных условий не обнаружено, тогда дальнейшее выполнение оператора зависит от наличия параметра OTHERWISE. Если параметр OTHERWISE имеется в составе оператора, то выполняется <группа_команд>, следующая за ним. Если параметр OTHERWISE отсутствует, то управление передается первой команде, следующей за оператором ENDCASE. В этом случае ни одна <группа_команд_i>, входящая в структуру оператора DO CASE…ENDCASE, выполнена не будет.
Для организации циклов может использоваться один из трех операторов цикла: с условием, по параметру, по записям таблицы данных.
Оператор цикла с условиемимеет формат:
DO WHILE <условие>
<группа_команд_1>
[ LOOP]
<группа_команд_2>
[ EXIT]
ENDDO
В этом операторе анализируется выражение <условие> и в случае его истинности (.T.) – выполняются команды, указанные в теле цикла. В случае ложности выражения <условие> (.F.), осуществляется выход из оператора цикла.
LOOP является необязательным параметром, при обнаружении которого происходит переход к началу цикла, при этом <группа_команд_2> игнорируется. EXIT является необязательным параметром, который передает управление из цикла первой команде, следующей за ENDDO. Команды EXIT и LOOP могут быть размещены в любом месте внутри цикла и могут использоваться во всех операторах цикла.
Оператор цикла с параметромимеет формат:
FOR <имя_ВПП>=<выражение1> TO < выражение2>
[STEP < выражение3>]
<группа_команд_1>
[ LOOP]
<группа_команд_2>
[ EXIT]
ENDFOR
В этом операторе параметром цикла является ВПП и команды, указанные в теле цикла, выполняются каждый раз начиная с начального значения параметра (<выражение1>) и до конечного значения параметра (<выражение2>). Значение параметра изменяется автоматически после каждого цикла с шагом, равным значению < выражение3>. В случае отсутствия необязательного указания шага параметра, шаг берется равным 1.
Оператор цикла по таблице данных осуществляет сканирование записей таблицы данных в указанном диапазоне с учетом заданного условия. Формат оператора цикла сканирования таблицы данных:
SCAN [<диапазон>][<список_выpажений>][FOR/WHILE<условие>]
<группа_команд_1>
[ LOOP]
<группа_команд_2>
[ EXIT]
ENDSCAN
Основная литература: 1[111:117], 4[23:31].
Контрольные вопросы:
1. Какая команда позволяет создать командный файл.
2. Какие операторы циклов существуют.
3. Какой оператор позволяет реализовать цикл с параметром.
4. Какой оператор позволяет проверять условия.
5. Какой оператор позволяет реализовать ветвление при выполнении программ.
Тема 6. Язык реляционных баз данных SQL.
Для работы с базами данных используются специальные языки, в целом называемые языками баз данных. В ранних СУБД поддерживалось несколько специализированных по своим функциям языков. Чаще всего выделялись два языка – язык определения схемы БД (SDL – Schema Definition Language) и язык манипулирования данными (DML – Data Manipulation Language). В современных СУБД обычно поддерживается единый интегрированный язык, содержащий все необходимые средства для работы с БД, начиная от ее создания, и обеспечивающий базовый пользовательский интерфейс с базами данных – язык SQL (Structured Query Language). Язык для взаимодействия с БД SQL появился в середине 70-х годах.
Язык SQL сочетает средства SDL и DML, т.е. позволяет определять схему реляционной БД и манипулировать данными. При этом именование объектов БД (для реляционной БД – именование таблиц и их столбцов) поддерживается на языковом уровне в том смысле, что компилятор языка SQL производит преобразование имен объектов в их внутренние идентификаторы на основании специально поддерживаемых служебных таблиц-каталогов.
Язык SQL содержит специальные средства определения ограничений целостности БД. Ограничения целостности хранятся в специальных таблицах-каталогах, и обеспечение контроля целостности БД производится на языковом уровне, т.е. при компиляции операторов модификации БД компилятор SQL на основании имеющихся в БД ограничений целостности генерирует соответствующий программный код.
Специальные операторы языка SQL позволяют определять так называемые представления БД, фактически являющиеся хранимыми в БД запросами (результатом любого запроса к реляционной БД является таблица) с именованными столбцами. Поддержание представлений производится также на языковом уровне.
Авторизация доступа к объектам БД производится также на основе специального набора операторов SQL. Идея состоит в том, что для выполнения операторов SQL разного вида пользователь должен обладать различными полномочиями. Пользователь, создавший таблицу БД, обладает полным набором полномочий для работы с этой таблицей. В число этих полномочий входит полномочие на передачу всех или части полномочий другим пользователям, включая полномочие на передачу полномочий. Полномочия пользователей описываются в специальных таблицах-каталогах, контроль полномочий поддерживается на языковом уровне.
Реализация в SQL концепции операций, ориентированных на табличное представление данных, позволило создать компактный язык с небольшим (менее 30) набором предложений. SQL может использоваться как интерактивный (для выполнения запросов) и как встроенный (для построения прикладных программ). В нем существуют:
предложения определения данных (определение баз данных, а также определение и уничтожение таблиц и индексов);
запросы на выбор данных (предложение SELECT);
предложения модификации данных (добавление, удаление и изменение данных);
предложения управления данными (предоставление и отмена привилегий на доступ к данным, управление транзакциями и другие). Кроме того, он предоставляет возможность выполнять в этих предложениях:
арифметические вычисления (включая разнообразные функциональные преобразования), обработку текстовых строк и выполнение операций сравнения значений арифметических выражений и текстов;
упорядочение строк и (или) столбцов при выводе содержимого таблиц на печать или экран дисплея;
создание представлений (виртуальных таблиц), позволяющих пользователям иметь свой взгляд на данные без увеличения их объема в базе данных;
запоминание выводимого по запросу содержимого таблицы, нескольких таблиц или представления в другой таблице (реляционная операция присваивания).
агрегатирование данных: группирование данных и применение к этим группам таких операций, как среднее, сумма, максимум, минимум, число элементов и т.п.
Типы данных SQL.
В SQL используются следующие основные типы данных, форматы которых могут несколько различаться для разных СУБД:
INTEGER - целое число (обычно до 10 значащих цифр и знак);
SMALLINT - «короткое целое» (обычно до 5 значащих цифр и знак);
DECIMAL(p,q) - десятичное число, имеющее p цифр (0 < p < 16) и знак; с помощью q задается число цифр справа от десятичной точки (q < p, если q = 0, оно может быть опущено);
FLOAT - вещественное число с 15 значащими цифрами и целочисленным порядком, определяемым типом СУБД;
CHAR(n) - символьная строка фиксированной длины из n символов (0 < n < 256);
VARCHAR(n) - символьная строка переменной длины, не превышающей n символов (n > 0 и разное в разных СУБД, но не меньше 4096);
DATE - дата в формате, определяемом специальной командой (по умолчанию mm/dd/yy); поля даты могут содержать только реальные даты, начинающиеся за несколько тысячелетий до н.э. и ограниченные пятым-десятым тысячелетием н.э.;
TIME - время в формате, определяемом специальной командой, (по умолчанию hh.mm.ss);
DATETIME - комбинация даты и времени;
MONEY - деньги в формате, определяющем символ денежной единицы ($, руб, …) и его расположение (суффикс или префикс), точность дробной части и условие для показа денежного значения.
Понятие «таблица», как правило, связывается с реальной или базовой таблицей, т.е. c таблицей, для каждой строки которой в действительности имеется некоторый двойник, хранящийся в физической памяти машины. Однако SQL использует и создает ряд виртуальных (как будто существующих) таблиц: представлений, курсоров и неименованных рабочих таблиц, в которых формируются результаты запросов на получение данных из базовых таблиц и, возможно, представлений. Это таблицы, которые не существуют в базе данных, но как бы существуют с точки зрения пользователя.
База данных во многих СУБД представляет собой контейнер, содержащий таблицы, представления (виртуальные таблицы), триггеры (для отслеживания ограничений целостности – непротиворечивости данных), хранимые процедуры и другие объекты.
Для создания базы данных используется команда:
CREATE DATABASE <имя_БД>
[необязательные_параметры].
Состав необязательных параметров зависит от используемой СУБД, при их отсутствии в команде используются значения по умолчанию.
Для определения структуры таблицы данных используются команды создания (CREATE TABLE) и изменения (ALTER TABLE) структуры таблицы. Таблица создается в активной БД. Если нет активной БД, то в СУБД, допускающих наличие свободных таблиц, создается свободная таблица (не входящая в состав БД).
Команда создания структуры таблицы данных имеет следующий формат:
CREATE TABLE <имя_таблицы> (<определение_поля1> [,<определение_поля2>…][определение_ограничений])
Команда создает новую таблицу данных с указанным именем. Для каждого поля задаются его имя и тип. Для типов полей, которые могут иметь разную ширину, дополнительно необходимо указать требуемую ширину. Для числовых полей с плавающей запятой указывается также точность (число десятичных разрядов). Если создаваемое поле будет являться ключевым (индексом), то необходимо указать тип ключа (индекса). Кроме того, в определении поля можно указать признак (NOT NULL) обязательного заполнения поля при вводе данных в таблицу, значения полей по умолчанию и ограничения на ввод значений. Определение ограничений таблицы используется для задания ограничений на уровне таблицы, могут указываться также определения первичного и внешнего ключей.
В Visual FoxPro команда CREATE TABLE создает новую таблицу данных (DBF-файл) с указанным именем. Для каждого поля задаются его имя и тип (одной из букв: C – символьный; N – числовой; F – с плавающей запятой; D – дата; L – логический; M – примечание и т.д.).
Можно создать временную таблицу, с которой можно работать как с обычной таблицей данных, называемую курсором (CURSOR). Курсор располагается в оперативной памяти в свободной рабочей области и доступен сразу после создания до тех пор, пока он не будет закрыт. При закрытии курсора временная таблица удаляется из памяти. Команда создания курсора CREATE CURSOR аналогична команде CREATE TABLE.
Кроме того, с помощью команды CREATE можно создать индекс (CREATE INDEX), триггер (CREATE TRIGGER), представление (CREATE VIEW), значение по умолчанию (CREATE DEFAULT), ограничение на данные (CREATE RULE), хранимую процедуру (CREATE PROCEDURE), пользовательскую функцию (CREATE FUNCTION), схему данных (CREATE SCHEME).
При изменении структуры таблицы используется команда ALTER TABLE с ключевыми словами, которые позволяют добавить, удалить, переименовать поле, изменить параметры поля. Эта команда имеет формат:
ALTER TABLE <имя_таблицы>
[ADD <определение_добавляемого_поля>]
[DROP <имя_удаляемого_поля>]
[RENAME <старое_имя_поля> TO <новое_имя_поля>]
[ALTER <имя_изменяемого_поля>
<определение_изменяемых_параметров_поля>]
Кроме того, с помощью команды ALTER можно изменить триггер (ALTER TRIGGER), представление (ALTER VIEW), хранимую процедуру (ALTER PROCEDURE), пользовательскую функцию (ALTER FUNCTION).
С помощью команды DROP можно удалить любые объекты, созданные командой CREATE.
Можно удалить всю таблицу целиком командой:
DROP TABLE <имя_таблицы>
Для удаления базы данных со всеми таблицами используется команда:
DROP DATABASE <имя_базы_данных>
Дополнять записями базу данных можно используя в качестве источника данных выражения, массивы, переменные, константы, результат запроса.
Для дополнения записей в таблицу БД используется команда INSERT, которая предварительно открывает закрытую таблицу БД. Формат команды INSERT:
INSERT INTO <имя_таблицы> [(<имя_поля1>[, <имя_поля2> …])]
VALUES (<выражение1> [, <выражение2> …])
Команда добавляет записи в конец существующей таблицы, используя выражения, перечисленные после слова VALUES. Выражения заносятся в указанные поля. Если опущены имена полей, выражения будут записываться в последовательные поля таблицы данных в соответствии с ее структурой.
В качестве выражений могут использоваться значения переменных или элементов массивов переменных. Например, в СУБД Visual FoxPro для дополнения записей в таблицу данных с использованием массивов используется следующая команда SQL:
INSERT INTO <имя_таблицы>
FROM ARRAY <имя_массива>
Команда добавляет записи в конец таблицы данных, используя данные, содержащиеся в указанном массиве. Данные из массива заносятся последовательно в поля, начиная с первого. Типы соответствующих полей и элементов массива должны совпадать.
Во многих СУБД (но не во всех) SQL-команду INSERT можно использовать с подзапросом – вложенной командой SELECT, если множество дополняемых данных является результатом запроса:
INSERT INTO <имя_таблицы> [(<имя_поля1> [, <имя_поля2> …])] <подзапрос>
Для модификации данных в таблице БД используется команда UPDATE, которая имеет следующий формат:
UPDATE <имя_таблицы> SET <имя_поля1>= <выражение1> [,<имя_поля2> = <выражение2>..] [WHERE <условие>]
Замена значений происходит в записях, удовлетворяющих условию, указанному после опции WHERE. Если эта опция отсутствует, то замена значений происходит во всех записях таблицы.
Для удаления записей в SQL используется команда DELETE, имеющая следующий формат:
DELETE FROM <имя_таблицы> [WHERE <условия>]
Удаляются записи, удовлетворяющие условию, указанному после опции WHERE. Если эта опция отсутствует, то удаляются все записи из таблицы.
В Visual FoxPro поддерживается команда SQL – DELETE, которая помечает записи на удаление без физического удаления записей. Физическое удаление записей можно реализовать командой PACK.
Основная литература: 1[128:132], 2[9:16, 26:32], 6[12:19].
Контрольные вопросы:
1. К какому классу языковых средств СУБД относится язык SQL.
2. Перечислите категории команд SQL.
3. Перечислите основные типы данных, используемые в SQL.
4. Какая команда SQL используется для определения структуры таблицы данных.
5. Какая команда SQL позволяет добавлять записи в базу данных.
Тема 7. Запросы SQL.
Все запросы на получение практически любого количества данных из одной или нескольких таблиц выполняются с помощью единственного предложения SELECT. В общем случае результатом реализации предложения SELECT является другая таблица. К этой новой (рабочей) таблице может быть снова применена операция SELECT и т.д., т.е. такие операции могут быть вложены друг в друга. Предложение SELECT может использоваться как:
самостоятельная команда на получение и вывод строк таблицы, сформированной из столбцов и строк одной или нескольких таблиц (представлений);
элемент WHERE- или HAVING-условия (сокращенный вариант предложения, называемый «вложенный запрос»);
фраза выбора в командах CREATE VIEW, DECLARE CURSOR или INSERT;
средство присвоения глобальным переменным значений из строк сформированной таблицы (INTO-фраза).
Здесь в синтаксических конструкциях используются следующие обозначения:
звездочка (*) для обозначения «все» – употребляется в обычном для программирования смысле, т.е. «все случаи, удовлетворяющие определению»;
квадратные скобки ([]) – означают, что конструкции, заключенные в эти скобки, являются необязательными (т.е. могут быть опущены);
фигурные скобки ({}) – означают, что конструкции, заключенные в эти скобки, должны рассматриваться как целые синтаксические единицы, т.е. они позволяют уточнить порядок разбора синтаксических конструкций, заменяя обычные скобки, используемые в синтаксисе SQL;
многоточие (…) – указывает на то, что непосредственно предшествующая ему синтаксическая единица факультативно может повторяться один или более раз;
прямая черта (|) – означает наличие выбора из двух или более возможностей. Например обозначение ASC|DESC указывает, можно выбрать один из терминов ASC или DESC; когда же один из элементов выбора заключен в квадратные скобки, то это означает, что он выбирается по умолчанию (так, [ASC]|DESC означает, что отсутствие всей этой конструкции будет восприниматься как выбор ASC);
точка с запятой (;) – завершающий элемент предложений SQL;
запятая (,) – используется для разделения элементов списков;
пробелы ( ) – могут вводиться для повышения наглядности между любыми синтаксическими конструкциями предложений SQL;
прописные латинские буквы и символы – используются для написания конструкций языка SQL и должны (если это специально не оговорено) записываться в точности так, как показано;
строчные буквы – используются для написания конструкций, которые должны заменяться конкретными значениями, выбранными пользователем, причем для определенности отдельные слова этих конструкций связываются между собой символом подчеркивания (_);
термины таблица, столбец, … – заменяют (с целью сокращения текста синтаксических конструкций) термины имя_таблицы, имя_столбца, …, соответственно;
термин таблица – используется для обобщения таких видов таблиц, как базовая_таблица, представление или псевдоним; здесь псевдоним служит для временного (на момент выполнения запроса) переименования и (или) создания рабочей копии базовой_таблицы (представления).
Предложение SELECT (выбрать) имеет следующий формат:
SELECT [DISTINCT] [<псевдоним>.]<выражение> [AS <колонка>][, [<псевдоним>]<выражение> [AS <колонка>]..]
FROM<имя_таблицы1>[<псевдоним1>][,<имя_таблицы2> [<псевдоним2>].]
[[INTO <получатель>]/[TO FILE < имя_файла> [ADDITIVE]/TO PRINTER]]
[NOCONSOLE] [PLAIN] [NOWAIT]
[WHERE <условие_связи> [AND <условие_связи>]
[AND/OR <условие_связи >]]
[GROUP BY <колонка> [, <колонка>…]] [HAVING <условие_отбора>]
[ORDER BY <колонка> [ASC/DESC] [,<колонка> [ASC/DESC]...]]
Ниже указывается назначение опций команды.
SELECT - (выбрать) данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями;
DISTINCT исключает возможность вывода одинаковых строк в выборке;
FROM - (из) перечисленных таблиц, в которых расположены эти столбцы;
WHERE - (где) строки из указанных таблиц должны удовлетворять указанному перечню условий отбора строк;
GROUP BY - (группируя по) указанному перечню столбцов с тем, чтобы получить для каждой группы единственное агрегированное значение, используя во фразе SELECT SQL-функции SUM (сумма), COUNT (количество), MIN (минимальное значение), MAX (максимальное значение) или AVG (среднее значение) ;
HAVING - (имея) в результате лишь те группы, которые удовлетворяют указанному перечню условий отбора групп
Кроме традиционных операторов сравнения (= | <> | < | <= | > | >=) в WHERE фразе используются условия BETWEEN (между), LIKE (похоже на), IN (принадлежит), IS NULL (не определено) и EXISTS (существует), которые могут предваряться оператором NOT (не). Критерий отбора строк формируется из одного или нескольких условий, соединенных логическими операторами:
AND - когда должны удовлетворяться оба разделяемых с помощью AND условия;
OR - когда должно удовлетворяться одно из разделяемых с помощью OR условий;
AND NOT - когда должно удовлетворяться первое условие и не должно второе;
OR NOT - когда или должно удовлетворяться первое условие или не должно удовлетворяться второе, причем существует приоритет AND над OR (сначала выполняются все операции AND и только после этого операции OR). Для получения желаемого результата WHERE условия должны быть введены в правильном порядке, который можно организовать введением скобок.
При обработке условия числа сравниваются алгебраически – отрицательные числа считаются меньшими, чем положительные, независимо от их абсолютной величины. Строки символов сравниваются в соответствии с их представлением в коде, используемом в конкретной СУБД, например, в коде ASCII. Если сравниваются две строки символов, имеющих разные длины, более короткая строка дополняется справа пробелами для того, чтобы они имели одинаковую длину перед осуществлением сравнения.
Фраза SELECT может включать не только выражения, но и отдельные числовые или текстовые константы. Следует отметить, что текстовые константы должны заключаться в апострофы (').
В синтаксисе фразы WHERE показано, что для отбора нужных строк таблицы можно использовать операторы сравнения = (равно), <> (не равно), < (меньше), <= (меньше или равно), > (больше), >= (больше или равно), которые могут предваряться оператором NOT, создавая, например, отношения «не меньше» и «не больше».
Для упорядочения по заданной колонке или колонкам используется опция ORDER BY. По умолчанию сортировка выполняется по возрастанию (ASC), но может быть задана и по убыванию (DESC).
Сокращенный вариант команды SELECT с использованием опции ORDER BY выглядит следующим образом:
SELECT <выражение> FROM <имя_таблицы>
ORDER BY <колонка1> [ASC/DESC] [,<колонка2>..]
Указание колонки упорядочения может выполняться именем или номером колонки.
В опции ORDER BY обычно нельзя использовать вычисляемые выражения. В случае необходимости упорядочения по колонке с вычисляемыми значениями указывается номер колонки.
Для указания объекта получателя данных выборки используется опция INTO или TO.
Ниже приведен сокращенный вариант команды SELECT с опцией INTO/TO, используемый в Visual FoxPro:
SELECT <выражение> FROM <имя_таблицы>
[INTO TABLE <имя_таблицы>] / [INTO CURSOR <имя_курсора>] /
[INTO ARRAY <имя_массива>] /
[TO FILE <имя_файла> [ADDITIVE]] /
[TO PRINTER] [NOCONSOLE] [PLAIN] [NOWAIT]
Типы возможных получателей данных выборки в Visual FoxPro описаны ниже.
TABLE <имя_таблицы> - получателем является новая таблица с указанным именем.
CURSOR <имя_курсора> - результат запроса помещается в курсор с указанным именем. Курсор – это временный набор данных, который может быть областью памяти или временным файлом и имеет режим «только чтение». Данные курсора могут быть, например, предъявлены в команде BROWSE, напечатаны, из них может быть образовано меню и т.д. Курсор может быть обработан другой командой SELECT. К колонкам курсора надо обращаться по имени этих колонок с префиксом – именем курсора (через точку).
ARRAY <имя_массива> - в качестве получателя результата запроса будет использован новый двумерный массив с указанным именем.
Кроме того, данные выборки можно переслать в файл или на принтер. Для этого в команде указывается получатель TO FILE <имя_файла> [ADDITIVE] / TO PRINTER и выборка посылается в текстовый файл с указанным именем или на принтер. Если используется слово ADDITIVE, то выборка будет добавлена в конец существующего файла без его перезаписи.
Основная литература:1[122:128], 2[16:26], 6[12:19].
Контрольные вопросы:
1. Перечислите варианты использования предложения SELECT.
2. Каким образом в предложении SELECT задать условие для отбора строк, какие операторы при этом используются.
3. Какая опция предложения SELECT используется для упорядочения по заданной колонке (колонкам).
4. Какая опция предложения SELECT используется для указания объекта получателя данных выборки.
5. Перечислите типы возможных получателей данных выборки.
Тема 8. Сложные запросы SQL.
В SQL существует ряд специальных стандартных функций (агрегатные функции SQL). Кроме специального случая COUNT(*) каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение, определяемое так:
COUNT - число значений в столбце,
SUM - сумма значений в столбце,
AVG - среднее значение в столбце,
MAX - самое большое значение в столбце.
Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. Это, конечно, отличается от выбора поля, поскольку всегда возвращается одиночное значение, независимо от того, сколько строк находится в таблице. Аргументом агрегатных функций могут быть отдельные столбцы таблиц. Но для того, чтобы вычислить, например, количество различных значений некоторого столбца в группе, необходимо применить ключевое слово DISTINCT совместно с и