Компьютерные технологии подготовки текстовых документов, обработки экономической информации на основе табличных процессоров, использования систем управления базами данных (СУБД), интегрированных программных пакетов.
Обработка информации средствами структурированного языка запросов SQL.
1. Инструкции SQL. Запросы на выборку.
1. Инструкции SQL. Запросы на выборку
В SQL существует приблизительно сорок инструкций каждая из них «просит» СУБД выполнить определенное действие, например, извлечь данные, создать таблицу или добавить в таблицу новые данные.
Инструкции SQL группируются по выполняемым действиям:
обработка данных:
SELECT – извлекает данные из таблицы;
INSERT – добавляет новые строки в таблицу;
DELETE – удаляет строки из таблицы;
UPDATE – обновляет данные существующие в таблице;
определение данных:
CREATE TABLE – добавляет новую таблицу в БД;
DROP TABLE – удаляет таблицу;
ALTER TABLE – изменяет структуру существующей таблицы;
CREATE VIEW – добавляет новое представление в таблицу;
DROP VIEW – удаляет представление;
CREATE INDEX – создает индекс для столбца;
DROP INDEX – удаляет индекс;
CREATE SCHEMA – добавляет новую схему в БД;
DROP SCHEMA – удаляет схему;
CREATE DOMAIN – добавляет новый домен в БД;
ALTER DOMAIN – изменяет определение домена;
DROP DOMAIN – удаляет домен;
управление доступом:
GRANT – представляет пользователю определенную привилегию доступа;
REVOKE – отменяет указанную привилегию доступа;
управление транзакциями:
COMMIT – завершает текущую транзакцию;
ROLLBACK – отменяет текущую транзакцию;
SET TRANSACTION – определяет режим доступа к данным для текущей транзакции;
программный SQL:
DECLARE – определяет набор записей в который будут возвращены результаты запроса;
OPEN – открывает набор записей;
FETCH – извлекает строку из таблицы результатов запроса;
CLOSE – закрывает набор записей;
PREPARE – подготавливает инструкцию SQL к динамическому выполнению;
EXECUTE – динамически выполняет инструкцию SQL;
DESCRIBE – возвращает описание подготовленного запроса.
Все инструкции SQL имеют одинаковую структуру (рис.1).
Каждая инструкция начинается с команды, т.е. с ключевого слова, описывающего действие, выполняемое инструкцией. После команды идет одно или несколько предложений. Предложение описывает данные, с которыми работает инструкция, или содержит уточняющую информацию о действии выполняемом инструкцией. Предложение может начинаться и с ключевого слова указывающего на действие, которое необходимо выполнить с данными. Одни предложения в инструкции являются обязательными, а другие нет. Ключевых слов в SQL приблизительно 310.
Графически допустимые формы инструкций иллюстрируются с помощью синтаксических диаграмм (рис.2).
Имена в инструкциях SQL указывают, над каким объектом БД инструкция должна выполнит действие. Имена могут содержать от 1 до 128 символов, начинаться с буквы и не содержат пробелов и специальных символов.
Обращение к таблице может производиться в соответствии с именем или по схеме, в которую включена таблица. В полном имени таблицы указывается имя пользователя и имя таблицы (например, Sam.orders). Обращение к таблице, включенной в схему осуществляется с указанием названия схемы и имени таблицы (например, schema1.orders). Имена столбцов задаются с указанием имени таблицы (или без него), имени пользователя, которому принадлежит таблица (или без него), названия схемы, в которую включена таблица (или без него). Например, полное имя столбца может выглядеть следующим образом: orders.customer; schema1.orders.customer; Sam.orders.customer.
Язык SQL предназначен для выполнения запросов к БД. Наиболее мощной инструкцией языка является инструкция SELECT, с помощью которой производится выборка информации из таблиц БД. Например,
|
SELECT city, target, sales FROM offices |
Вывести список офисов с их плановыми и фактическими объемами продаж |
Синтаксическая диаграмма инструкции SELECT имеет вид (рис.3). Инструкция состоит из шести предложений. Предложения SELECT и FROM являются обязательными, а остальные включаются в инструкцию при необходимости.
SELECT – указывается список столбцов, которые должны быть возвращены инструкцией.
FROM – указывается список таблиц, которые содержат элементы данных, извлекаемые запросом.
WHERE – показывает, что в результаты запроса следует включать только некоторые строки (условия отбора).
GROUP BY – позволяет создать итоговый запрос.
HAVING – показывает, что в результаты запроса следует включать только некоторые из групп, созданных с помощью предложения GROUP BY.
ORDER BY – сортирует результаты запроса на основании данных, содержащихся в одном или нескольких столбцах.
Предикат Distinct позволяет исключить повторяющиеся строки.
Результатом выполнения SQL-запроса всегда является таблица, содержащая данные и ничем не отличающаяся от таблицы БД. Если пользователь набирает инструкцию SQL в интерактивном режиме, СУБД выводит результаты на экран в табличной форме. Если программа посылает запрос СУБД с помощью программного SQL, то СУБД возвращает таблицу результатов программе (рис.4).
Простые запросы извлекают данные из столбцов, расположенных в одной таблице БД. Инструкция SELECT для простых запросов состоит только из двух обязательных предложений.
Кроме столбцов, значения которых извлекаются непосредственно из БД, SQL-запрос на выборку может содержать вычисляемые столбцы, значения которых определяются на основании значений, хранящихся в БД. Чтобы получить вычисляемый столбец, в списке вычисляемых столбцов необходимо указать выражение, при этом столбцы должны содержать числовые данные. Например,
SELECT city, region, (sales – target)
FROM offices.
Запросы на выборку с условиями отбора. (Сравнение. Проверка на принадлежность диапазону значений. Проверка на членство в множестве. Проверка на соответствие шаблону. Составные условия отбора).
В SQL используется множество условий отбора, позволяющих эффективно и естественным образом создавать различные типы запросов. Условия отбора включаются в предложение WHERE.
Условия:
Сравнение – значение одного выражения сравнивается со значение другого выражения.
Проверка на принадлежность диапазону – проверяется, совпадает ли значение выражения с одним из значений заданного множества.
Проверка на соответствие шаблону – проверяется, соответствует ли строковое значение, содержащееся в столбце, определенному шаблону.
Проверка на равенство значению NULL – проверяется, содержится ли в столбце значение NULL.
Синтаксическая диаграмма операции сравнения выглядит следующим образом (рис.5).
Одной из форм проверки на принадлежность диапазону значений является оператор BETWEEN … AND (рис.6).
Проверка на членство в множестве осуществляется с помощью оператора IN (рис.7).
Проверка на соответствие шаблону выполняется с помощью оператора LIKE (рис.8). Проверка на соответствие шаблону позволяет определить соответствует ли значение данных в столбце некоторому шаблону.
Шаблон представляет собой строку, в которую может входить один или более подстановочных знаков. Эти знаки интерпретируются определенным образом.
Подстановочный знак «%» совпадает с любой последовательностью из нуля или более символов. Например:
SELECT company, credit
FROM customers
WHERE company LIKE ‘Smith% Corp.’, - при этом вместо знака «%» добавятся любые символы, содержащиеся в элементах данных.
Подстановочный знак «_» совпадает с любым отдельным символом. Например:
SELECT company, credit
FROM customers
WHERE company LIKE ‘Smiths_n Corp.’
Подстановочные знаки можно помещать в любое место строки шаблона, и в одной строке может содержаться несколько подстановочных знаков.
С помощью формы NOT LIKE можно находить строки, которые не соответствуют шаблону. Проверку LIKE можно применять только к столбцам, имеющим строковый тип данных.если в столбце содержится значение NULL, то результатом проверки будет NULL.
При проверке строк на соответствие шаблону может оказаться, что подстановочные знаки входят в строку символов в качестве литералов. Например, нельзя проверить, содержится ли знак процента в строке, просто включив его в шаблон, поскольку СУБД будет считать этот знак подстановочным. Поэтому для проверки наличия в строке литералов применяются символы пропуска. Символ пропуска определяется в виде строки, состоящей из одного символа, и предложения ESCAPE:
SELECT order_num, product
FROM orders
WHERE product LIKE ¢ A$%B% ¢ ESCAPE ¢ $ ¢.
Проверка на равенство значению NULL производится с помощью оператора IS NULL (рис.9).
Составные условия отбора – операторы AND, OR, NOT (рис.10).
Оператор OR используется для объединения двух условий отбора, из которых как минимум одно должно быть истинным. Для объединения двух условий отбора, оба из которых должны быть истинными, следует использовать оператор AND. Оператор NOT используется для выбора строк, для которых условие отбора ложно.
С помощью операторов AND, OR, NOT можно создавать сложные условия отбора.
Таблица истинности оператора AND
|
AND |
TRUE |
FALSE |
NULL |
|
TRUE |
TRUE |
FALSE |
NULL |
|
FALSE |
FALSE |
FALSE |
FALSE |
|
NULL |
NULL |
FALSE |
NULL |
Таблица истинности оператора OR
|
OR |
TRUE |
FALSE |
NULL |
|
TRUE |
TRUE |
TRUE |
TRUE |
|
FALSE |
TRUE |
FALSE |
NULL |
|
NULL |
TRUE |
NULL |
NULL |
Таблица истинности оператора NOT
|
NOT |
TRUE |
FALSE |
NULL |
|
|
FALSE |
TRUE |
NULL |
Иногда появляется необходимость объединения результатов двух или более запросов в одну таблицу. SQL поддерживает такую возможность с помощью операции UNION. Вывести список всех товаров, цена которых превышает 2000 или которых было заказано более чем на 300000 за один раз.
Первой части основного запроса удовлетворяет запрос (рис.11), изображенный в верхней части рисунка:
SELECT mfr_id, product_id
FROM products
WHERE price > 2000.
Подобным образом можно составить запрос на вторую часть исходного запроса:
SELECT DISTINCT mfr, product
FROM orders
WHERE amount > 30000.
Операция UNION создает одну таблицу результатов запроса, в которой содержатся строки результатов как верхнего, так и нижнего запросов:
SELECT mfr_id, product_id
FROM products
WHERE price > 2000
UNION
SELECT DISTINCT mfr, product
FROM orders
WHERE amount > 30000.
Чтобы таблицы результатов можно было объединить с помощью операции UNION они должны соответствовать следующим требованиям:
две таблицы должны содержать одинаковое число столбцов;
тип данных каждого столбца первой таблицы должен совпадать с типом данных соответствующего столбца во второй таблице;
ни одна из двух таблиц не может быть отсортирована с помощью предложения ORDER BY; однако отсортированные результаты можно отсортировать.
Запрос на объединение и повторяющиеся строки – операция UNION по умолчанию удаляет повторяющиеся строки, чтобы их оставить необходимо использовать предикат ALL (UNION ALL).
Запрос на объединение и сортировка – объединенные результаты запросов, возвращенные операцией UNION, можно отсортировать с помощью предложения ORDER BY, следующего за второй инструкцией SELECT.
В SQL существует понятие подчиненного запроса; механизм подчиненных запросов позволяет использовать результаты одного запроса в качестве составной части другого.
Подчиненным называется запрос, содержащийся в предложении WHERE или HAVING другой инструкции SQL. Подчиненные запросы позволяют естественным образом обрабатывать запросы, выраженные через результаты других запросов. Например:
Вывести список офисов, для которых плановый объем продаж превышает сумму плановых объемов продаж всех служащих.
В данном запросе требуется получить список офисов из таблицы offices, для которых значение столбца target удовлетворяет некоторому условию.
SELECT city
FROM offeces
WHERE target >???
Здесь величина ??? равна сумме плановых объемов продаж всех служащих, работающих в данном офисе.
SELECT SUM(quota)
FROM salesreps
WHERE rep_office = office
Таким образом, готовый SQL-запрос будет выглядеть следующим образом:
SELECT city
FROM offeces
WHERE target > (SELECT SUM(quota)
FROM salesreps
WHERE rep_office = office).
В этом запросе подчиненный (внутренний) запрос вычисляет для каждого офиса сумму плановых объемов продаж всех служащих, работающих в данном офисе. Главный (внешний) запрос сравнивает план продаж офиса с полученной суммой и, в зависимости от результата сравнения, либо добавляет данный офис в таблицу результатов запроса, либо нет.
Подчиненные SQL-запросы всегда выступают в качестве части предложения WHERE или HAVING. В предложении WHERE они помогают отбирать из таблицы результатов запроса отдельные строки, а в предложении HAVING – группы строк.
Подчиненный запрос всегда заключается в круглые скобки. В остальном его структура в точности соответствует структуре инструкции SELECT. Однако между подчиненным запросом и инструкцией SELECT имеется ряд отличий:
· таблица результатов подчиненного запроса всегда состоит из одного столбца. Это означает, что в предложении SELECT подчиненного запроса всегда указывается один возвращаемый столбец;
· в подчиненный запрос не может входить предложение ORDER BY. результаты подчиненного запроса используются только внутри главного запроса и для пользователя остаются невидимыми, поэтому нет смысла их сортировать;
· имена столбцов в подчиненном запросе могут являться ссылками на столбцы таблиц главного запроса;
· подчиненный запрос не может быть запросом на объединение (UNION) нескольких различных инструкций SELECT; допускается использование только одной инструкции SELECT.
В SQL используются следующие условия отбора в подчиненном запросе:
§ сравнение с результатом подчиненного запроса (=, , , =). Значение выражения проверяется на равенство, которое возвращается подчиненным запросом. Эта проверка происходит также как простое сравнение. Подчиненный запрос, участвующий в операции сравнения, должен возвращать в качестве результата единичное значение, т.е. одну строку, содержащую один столбец. Если результатом подчиненного запроса являются несколько строк или несколько столбцов, то сравнение не имеет смысла и СУБД выдает сообщение об ошибке. Если в результате выполнения подчиненного запроса не будет получено ни одной строки или будет получено значение NULL, то операция сравнения возвращает значение NULL. В стандарте SQL1 подчиненный запрос может находиться только справа от оператора сравнения. В стандарте SQL 2 – с любой стороны;
§ проверка на принадлежность результатам подчиненного запроса (предикат IN). Значение выражения проверяется на равенство одному из множества значений, которые возвращаются подчиненным запросом. Эта проверка происходит также как проверка на членство во множестве. Например:
Вывести список служащих тех офисов, где фактический объем продаж превышает плановый.
SELECT name
FROM salesreps
WHERE rep_office IN (SELECT office
FROM offices
WHERE sales > target);
§ проверка на существование (предикат EXISTS). Проверяется наличие строк в таблице результатов подчиненного запроса. Например:
вывести список всех товаров, на которые получен заказ 25000 или больше
можно перефразировать этот запрос:
вывести список товаров, для которых в таблице Orders существует по крайней мере один заказ, удовлетворяющий условиям: а)является заказом не данный товар, б) имеет стоимость не менее чем 25000
SELECT description
FROM products
WHERE EXISTS (SELECT order_num
FROM orders
WHERE product = product_id AND mfr = mfr_id
AND amount >= 25000)
Предикат EXISTS не использует результаты подчиненного запроса. Проверяется только наличие результатов. Поэтому в подчиненном запросе допускается использование формы «SELECT *».
§ многократное сравнение (предикаты ANY и ALL). Значение выражения сравнивается с каждым из множества значений, которые возвращаются подчиненным запросом.
При проверке ANY, для того чтобы сравнить проверяемое выражение со столбцом данных, отобранных подчиненным запросом, используется один их шести операторов сравнения (=, , , =). Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если любое из этих сравнений дает результат TRUE, то проверка ANY возвращает значение TRUE. Например:
Вывести список служащих принявших заказ на сумму большую, чем 10% от их плана
SELECT name
FROM salesreps
WHERE (0.1 * quota < ANY (SELECT amount
FROM orders
WHERE rep = empl_num))
Главный запрос проверяет все строки таблицы salesreps. Подчиненный запрос находит все заказы, принятые «текущим» служащим, и возвращает столбец, содержащий стоимости этих заказов. Предложение WHERE главного запроса вычисляет 10% от плана текущего служащего и использует это число в качестве проверяемого значения, сравнивая его со стоимостью каждого заказа, отобранного подчиненным запросом. Если есть хотя бы один заказ, стоимость которого превышает вычисленное проверяемое значение, то проверка ANY возвращает значение TRUE, а имя служащего заносится в таблицу результатов запроса. Форму «WHERE X < ANY (SELECT Y…)» следует читать «где для некоторого Y Х меньше, чем Y».
В проверке ALL, как и в проверке ANY, используется один из шести операторов (=, , , =) для сравнения проверяемого значение со столбцом данных, отобранных подчиненным запросом. Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если все сравнения дают результат TRUE, то проверка ALL возвращает результат TRUE.
Форму «WHERE X < ALL (SELECT Y…)» следует читать «где для все Y Х меньше, чем Y».
Уровни вложенности подчиненных запросов в большинстве современных СУБД на ограничиваются. Однако на практике необходимо иметь в виду, что с увеличением числа подчиненных запросов существенно снижается производительность системы. Пример трехуровневого запроса:
Вывести список клиентов, закрепленных за служащими, работающими в офисах восточного региона
SELECT company
FROM customers
WHERE cust_rep IN (SELECT empl_num
FROM salesreps
WHERE rep_office IN (SELECT office
FROM offices
WHERE region = ‘Eastern’
Выполняются такие запросы, начиная с самого внутреннего.