Компьютерные технологии подготовки текстовых документов, обработки экономической информации на основе табличных процессоров, использования систем управления базами данных (СУБД), интегрированных программных пакетов.
Обработка информации средствами структурированного языка запросов 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.