Запросы в реляционной СУБД
Для подготовки запросов с помощью различных СУБД чаще всего используются два основных языка описания запросов: • QBE (Query By Example) — язык запросов по образцу; По возможностям манипулирования данными при описании запросов указанные языки практически эквивалентны. Более того, на практике запрос, составленный на QBE, обычно транслируется в SQL – запрос и лишь затем выполняется. Главное отличие между данными языками в способе формирования запросов: язык QBE предполагает ручное или визуальное формирование запроса, в то время как использование SQL означает программирование запроса. Теоретическая основа языка QBE –реляционное исчисление с переменными-доменами. Язык QBE позволяет задавать сложные запросы к БД путем заполнения предлагаемой СУБД запросной формы. В каждой из современных реляционных СУБД свой вариант языка QBE. С помощью запросов на языке QBE можно выполнять следующие основные oперации: выборку данных; вычисление над данными; вставку новых записей; удаление записей; модификацию (изменение) данных. Выборка данных или запрос – команда, которая выводит определенную информацию из таблиц в память. Эта информация обычно посылается непосредственно на экран компьютера или терминал. Запросы формируются командой SELECT. В самой простой форме команда или оператор SELECT извлекает информацию из таблицы. В качестве примера при реализации операторов SQL используется учебная БД условной торговой фирмы «ЗАКАЗЫ». БД состоит из трех таблиц (таблицы 1-3). Имена полей в таблице Продавцы: номер – пном; имя – пимя; город, в котором он живет, - город; комиссионные, которые он получает с каждого оформленного заказа, - комм. Имена полей в таблице Заказчики: номер – зном; имя – зимя; город, в котором он живет, - город; оценка заказчика – рейтинг; номер продавца, который обслуживает данного заказчика, - пном. Таблица 1 – Продавцы
Имена полей в таблице Заказы: номер – ном; сумма приобретения – сумпр; дата оформления заказа – датпр; номер заказчика, который оплатил данный заказ, - зном; номер продавца, который обслуживает данный заказ, - пном. Таблица 2 – Заказчики
Таблица 3 – Заказы
Приняты следующие соглашения: · один продавец может обслуживать несколько заказчиков; · один заказчик работает только с одним продавцом фирмы. Пример 1 Вывести всю таблицу Продавцы: SELECT пном, пимя, город, комм FROM Продавцы; Точка с запятой (;) используется во всех командах интерактивного SQL, чтобы сообщать базе данных, что команда закончена и готова выполниться. Звездочка (*) в команде может применяться для вывода полного списка столбцов в той последовательности, как они были созданы, и предыдущая процедура может выполняться командой: SELECT * FROM Продавцы; Команда SELECT имеет множество форм своего представления, например, в примере 1 можно задать не все поля или переупорядочить их. Предложение WHERE команды SELECT позволяет устанавливать предикаты, условие которых может быть или верным, или неверным для любой строки таблицы. Команда извлекает только те строки из таблицы, для которых такое утверждение верно. Пример 2 Найти всех продавцов, живущих в Москве SELECT пимя, город FROM Продавцы WHERE город = ‘Москва’; В синтаксисе предложения WHERE для отбора нужных строк таблицы можно использовать: · операторы сравнения: = (равно), <> (не равно), < (меньше), <= (меньше или равно), > (больше), > = (больше или равно); · логические операции: OR, NOT, AND; · операторы: IN, BETWEEN …. AND, LIKE, IS NULL. Оператор IN определяет, включено ли значение левого операнда в набор значений (список) правого операнда. Значение предиката, образованного с помощью оператора IN, равно true в том и только в том случае, когда значение левого операнда совпадает хотя бы с одним значением списка правого операнда. Пример 3 Найти всех продавцов, живущих в Москве или в Перми: SELECT * FROM Продавцы WHERE город IN ('Москва', 'Пермь');С помощью оператора BETWEEN... AND... (находится в интервале от... до...) можно отобрать строки, в которых значение какого-либо столбца находятся в заданном диапазоне. Результат «x BETWEEN y AND z» тот же самый, что результат «x >= y AND x <= z». Пример 4 Выбрать заказчиков, чьи имена попали в определенный алфавитный диапазон: SELECT * FROM ЗаказчикиWHERE зимя BETWEEN 'И' AND 'П';Обычная форма оператора LIKE «имя_столбца» LIKE «текстовая_константа» для столбца текстового типа позволяет отыскать все значения указанного столбца, соответствующие образцу, заданному «текстовой_константой». Символы этой константы интерпретируются следующим образом: · символ «?» заменяет любой одиночный символ; · символ «*» заменяет любую последовательность из N символов (где N может быть нулем); · все другие символы означают просто сами себя. Для оператора LIKE типы данных столбца левого операнда и образца должны быть типами символьных строк. Пример 5 Найти заказчиков, чьи имена начинаются с буквы ‘К’: SELECT * FROM Заказчики WHERE зимя LIKE 'К%';Наличие неопределенных (NULL) значений повышает гибкость обработки информации, хранящейся в БД. В рассматриваемом примере можно предположить ситуацию, что появился новый заказчик, которому еще не был назначен продавец. Можно ввести строку для заказчика со значением NULL в поле пном и заполнить это поле значением позже, когда продавец будет назначен. Значение «IS NULL» равно true тогда и только тогда, когда значение x не определено. Значение предиката «x NOT IS NULL» равно значению «NOT x IS NULL». Пример 6 Найти все записи в таблице Заказчики с NULL значениями в пном столбце: SELECT * FROM Заказчики WHERE пном IS NULL;SQL содержит также ряд собственных функций, называемых агрегатными, которые определяют: · COUNT – количество строк в таблице; · SUM – арифметическая сумма всех выбранных значений данного поля; · AVG – среднее значение выбранных значений поля; · MAX или MIN – наибольшее или наименьшее из всех выбранных значений данного поля. Пример 7 Вывести сумму всех покупок в таблице Заказы: SELECT SUM (сумпр) FROM Заказы; В результате будет выведено значение 26658.4 без подписи поля. Пример 8 Подсчитать число строк в таблице Заказчики: SELECT COUNT (*) FROM Заказчики;Фраза GROUP BY (группировать по …) инициирует перекомпоновку указанной в предложении FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, заданном в столбце GROUP BY, затем к каждой группе применяется заданная агрегатная функция, и оператор SELECT выводит значения функции для каждой группы. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Пример 9 Найти наибольшую сумму приобретений, полученную каждым продавцом: SELECT пном, MAX (сумпр) FROM Заказы GROUP BY пном;Результат выборки по запросу имеет вид: пном 1001 767.19 1002 1713.23 1003 75.75 1014 1309.95 1007 1098.16. В список отбираемых полей оператора SELECT, содержащего раздел GROUP BY, можно включать толькоагрегатные функции и поля, которые входят в условие группировки. Поэтому команда SELECT зном, пном, MAX (сумпр) FROM Порядки GROUP BY пном; не будет выполнена и появится сообщение о синтаксической ошибке. Причина ошибки в том, что в список отбираемых полей включено поле зном, которое не входит в раздел GROUP BY.Оператор GROUP BY можно также использовать с несколькими полями, задавая уровни группировки. Пример 10 Вывести наибольшую сумму приобретений, получаемую каждым продавцом каждый день: SELECT пном, датпр, MAX (сумпр) FROM ПорядкиGROUP BY пном, датпр;Команда SELECT с GROUP BY может также содержать предложение HAVING, определяющее критерии, необходимые для удаления определенных групп из вывода. Пример 11 Предположим, что в предыдущем примере, необходимо увидеть только максимальные суммы приобретений, значение которых выше 3000.00:Тогда команда имеет вид SELECT пном, датпр, MAX (сумпр) FROM Заказы GROUP BY пном, датпр HAVING MAX (сумпр) > 3000.00; Условие в предложении HAVING строится по тем же правилам, что и в предложении WHERE. SQL позволяет совершать вычисления с числовыми данными (составлять вычисляемые выражения) и затем помещать их в выводимую таблицу. Пример 12 Рассчитать значения поля комм в процентах: SELECT пном, пимя, город, комм * 100, '%' FROM Продавцы; Пример 13 Упорядочить вывод по одному или нескольким полям, например, по убыванию суммы приобретений: SELECT * FROM Заказы ORDER BY сумпр DESC; Возможна реализация запросов на выборку из нескольких таблиц. При этом используются те же средства, что и для одной таблицы. Пример 14 Поставить в соответствие каждому продавцу его заказчиков в том городе, в котором они живут: SELECT Заказчики.зимя, Продавцы.пимя, Продавцы.город FROM Продавцы, Заказчики WHERE Продавцы.город = Заказчики.город; Полное имя столбца таблицы фактически состоит из имени таблицы, сопровождаемого точкой и затем имени столбца, например, Продавцы.пном. Аналогично выглядит и команда соединения трех таблиц и более. Возможно соединение таблицы самой с собой, эта схема может использоваться для проверки правильности заполнения таблицы. Для этого используются синонимы таблиц. Пример 15 Найти все пары заказчиков, имеющих одинаковый рейтинг: SELECT перв.прном, втор.зном, перв.пном, втор.прном, втор.зном, втор.пном FROM Заказчики перв, Заказчики втор WHERE перв.зном = втор.зном AND перв.пном <> втор.пном; В этой команде SQL ведет себя так, как если бы он соединял две таблицы, называемые «перв» и «втор». Обе они – фактически таблицы Заказчики, но псевдонимы разрешают им быть обработанными независимо. Псевдонимы «перв» и «втор» были установлены в предложении FROM запроса, сразу после имени копии таблицы. Для устранения избыточности потребовалось добавить еще одно условие в предикат «перв.пном <> втор.пном», чтобы сделать предикат ассиметричным, и те же самые значения в обратном порядке не будут выбираться снова. При работе с несколькими таблицами запрос может быть составным, использующим (не использующим) операции обновления. Внутренние запросы называют подзапросами и заключают их в скобки. Пример 16 Извлечь все заказы из таблицы Заказы, если известно имя продавца (Аврорин), но значение его поля пном неизвестно: SELECT * FROM Заказы WHERE пном = (SELECT пном FROM Продавцы WHERE пимя = ‘Аврорин’); Чтобы оценить внешний (основной) запрос, SQL сначала оценивает внутренний запрос (подзапрос) предложения WHERE. Ответ: пном = 1004. Однако SQL не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предикат прочитал WHERE пном = 1004. Для работы с несколькими таблицами может использоваться операция объединения (UNION). Пример 17 Получить имена всех продавцов и заказчиков, размещенных в Москве: SELECT пном, пимя FROM Продавцы WHERE город = «Москва» UNION SELECT зном, зимя FROM Заказчики WHERE город = «Москва»; Столбцы, выбранные двумя командами, выводятся так, как если бы команда была одна. Заголовки столбца исключены, потому что ни один из столбцов, выведенных объединением, не был извлечен непосредственно только из одной таблицы. Последний запрос заканчивается точкой с запятой. Отсутствие точки с запятой дает понять SQL, что имеется еще один запрос или более.
|
