Запросы манипулирования данными
Запросы выборки
Основу запросов ЯМД составляет инструкция SELECT.
SELECT [ALL|DISTINCT|DISTINCTROW|TOP] <список полей либо выражений>
FROM <список таблиц> [IN <имя базы данных>]
[<Таблица1> INNER|RIGHT|LEFT JOIN <Таблица2>
ON <Таблица1>.<Поле1> = <Таблица2>.<Поле2>]
[WHERE <условие отбора>]
[GROUP BY <список полей>]
[HAVING <условие отбора>]
[ORDER BY <спецификация> [,<спецификация>] ...]
Инструкция SELECT позволяет производить выборку и вычисления над данными из одной или нескольких таблиц. Результатом выполнения инструкции является ответная таблица. Простейшая форма инструкции SELECT включает операторы SELECT и FROM. Оператор SELECT определяет поля, подлежащие выводу в выходной набор, а оператор FROM определяет имена таблиц, включенных в запрос. Имена полей и таблиц отделяются запятыми, а все предложение запроса заканчивается точкой с запятой. Если в запросе участвуют несколько таблиц, то для исключения двусмысленности имена полей следует записывать в полной форме: <таблица>.<поле> (например, Клиенты.Адрес). Для повышения эффективности вначале указываются меньшие таблицы. Если имена полей и таблиц включают пробелы, то их необходимо заключать в квадратные скобки. Список полей следует задавать в той последовательности, в которой они должны быть представлены в выходном наборе. Например:
SELECT Фамилия, Имя, Отчество, [год рождения] FROM Клиенты;
Для выбора всех полей применяется операция *: SELECT * FROM Клиенты;
Список данных может содержать имена полей, участвующих в запросе, выражения над столбцами, а также строковые константы, заключенные в кавычки. В выражениях (вычисляемых полях) могут принимать участие имена полей, знаки арифметических операций (+, – ,* ,/, ^), множество встроенных функций, константы, круглые скобки и следующие операции:
\ – возвращает целое от деления двух арифметических выражений (заранее округленных);
MOD – возвращает остаток от деления двух арифметических выражений (заранее округленных);
& – операция конкатенации;
IIF(условие, выражение1, выражение2) – Если условие истинно, то возвращается результат выражения1, в противном случае – выражения2 (но в любом случае вычисляется как выражение1, так и выражение2). Функция IIF позволяет исправлять значения пустых полей на 0: IIF(IsNull([поле]), 0, [поле]). В противном случае, если одно из полей, участвующих в сложении, будет пустым, то и весь результат будет пустым. Например,
SELECT [Фамилия] & ' ' & [Имя] & ' ' & [Отчество] AS Полное_имя, “возраст”, DateDiff('yyyy', [Дата Рождения], Date()) AS Возраст FROM Клиенты;
Именам полей и таблиц можно назначать альтернативные имена (псевдонимы). Псевдонимы записываются через оператор AS (<имя таблицы> или <имя поля> AS <псевдоним>). В предыдущем примере псевдонимы использовались для задания имен вычисляемым полям. В противном случае имя вычисляемого поля имело бы вид: Выражение1 и т.д. В большинстве случаев псевдонимы используются для сокращения набора длинных имен. Особенно это эффективно для замены длинных имен таблиц, поскольку в многотабличных запросах приходится включать имена таблиц в описание каждого поля. Псевдонимы также используются для создания рекурсивных запросов, когда приходится соединять записи из одной и той же таблицы. Тогда для различия копий таблиц приходится им присваивать псевдонимы.
Если в запрос включены не все поля некоторой таблицы, то выходной набор может содержать одинаковые строки. Предикаты ALL, DISTINCT, DISTINCTROW (записываются сразу после оператора SELECT) служат для управления выводом повторяющихся строк. По умолчанию принимается предикат ALL, т.е. в ответную таблицу включаются все строки, в том числе и повторяющиеся. Предикат DISTINCT исключает записи, которые содержат повторяющиеся значения в выбранных полях. В выходной набор включаются только уникальные значения каждого из полей, находящегося в списке инструкции SELECT. Если предложение SELECT содержит более одного поля, то для включения записи в результат выполнения запроса необходимо, чтобы совокупность значений во всех этих полях была уникальной. DISTINCTROW исключает полностью повторяющиеся записи. Предикат DISTINCTROW применяется для многотабличных запросов и игнорируется, если запрос содержит только одну таблицу или все поля всех таблиц. В Access может дополнительно применяться предикат TOP, возвращающий определенное число записей, находящихся в начале или в конце диапазона, описанного с помощью предложения ORDER BY.
SQL позволяет в одном запросе обращаться к данным нескольких баз данных различных форматов. Такие запросы получили название гетерогенных запросов. В разных СУБД синтаксис записи гетерогенных запросов несколько отличается. В Access для полного описания имени и типа таблицы применяется оператор IN: имя таблицы IN “путь к файлу” “тип таблицы”. Например, SELECT * FROM Клиенты IN “c:\dBase\Clients.dbf” “dBASE”. В системах, ориентированных на доступ к данным через BDE (Borland Database Engine) для указания пути к таблице необходимо указать имя псевдонима базы (BDE Alias), заключенного в двоеточие с обеих сторон. Псевдоним базы данных в BDE подобен DSN (Data Source Name) в ODBC (Open Database Connectivity) и указывает путь к файлам БД, тип БД и некоторые дополнительные параметры типа имени пользователя и пароля. Например, SELECT * FROM :BCDEMOS:Clients.
Оператор WHERE необязателен. Он задает условия, которым должны удовлетворять записи в результирующей таблице. Выражение <условие отбора> является логическим. Его элементами могут быть имена столбцов, операции сравнения <, <=, >, >=, =, <>, арифметические операции, логические операторы (NOT, AND, OR, XOR, IMP, EQV), скобки, функции IN, BETWEEN, LIKE, IS (NOT) NULL и множество встроенных функций. Строки заключаются в кавычки, а константы типа Дата/Время – в символы #.
Функция IN проверяет на равенство любому значению из списка: [поле1] IN (“Минск”, “Москва”, “Киев”);
Функция BETWEEN задает диапазон значений. Границы диапазона разделяются оператором And: [поле2] BETWEEN 50 And 100 (эквивалентно выражению [поле2] <=100 AND [поле2] >=50).
Функция LIKE проверяет на соответствие заданному шаблону символов. В качестве символов шаблона используются:
* – любое число произвольных символов. Может использоваться также %;
? – один произвольный символ. Может использоваться также _;
# – одна произвольная цифра;
[] – диапазон допустимых символов. К примеру, [А - Я], [3 - 9]. Если наоборот необходимо исключить эти символы, то перед ними ставится !: [!А - Я]. Например, LIKE “A*” – любая строка, начинающуюся с A, LIKE “220###” задает условие для почтового индекса, .начинающегося с 220, LIKE “####AA[A-X] – отбирает номера автомобилей серий AAA, AAB, … , AAX.”
Запрос может быть основан на нескольких таблицах. Простое включение полей из нескольких таблиц дает простой перебор всех их возможных значений. Для двух таблиц общее число записей будет равно произведению числа записей в первой и второй таблицах. Но так как реляционная база данных практически всегда состоит из таблиц, связанных между собой посредством совпадающих значений полей, участвующих в связи, то для правильного объединения данных необходимо включать в запрос явное определение соответствующих связей. Связь можно задать с помощью двух способов: с помощью оператора INNER|RIGHT|LEFT JOIN и с помощью дополнительного условия выборки после оператора WHERE. Причем в SQL объединение данных можно произвести даже по неравенству, т.е. поддерживаются операции сравнения =, <>, <, <= ,> , >=. Оператор INNER|RIGHT|LEFT JOIN является необязательной частью инструкции SELECT и оформляется как часть оператора FROM:
SELECT Товары.[Наименование товара], Заказы.Дата, Заказы.[Полная цена] FROM Товары INNER JOIN Заказы ON Товары.Код_товара = Заказы.Код_товара;
Этот же запрос можно записать следующим образом (второй способ задания связи): SELECT Товары.[Наименование товара], Заказы.Дата, Заказы.[Полная цена] FROM Товары, Заказы WHERE Товары.Код_товара = Заказы.Код_товара;
Для большинства многотабличных запросов набор записей формируется на основе совпадающих значений полей, участвующих в связи, т.е. с помощью внутреннего объединения (эквисоединение). Внутреннее соединение задается с помощью оператора INNER JOIN. Для двух таблиц, связанных отношением «один ко многим», из главной таблицы будут отобраны только те записи, которые имеют связанные записи в подчиненной таблице. Допустим, мы имеем две таблицы, «Клиенты» и «Заказы». С помощью внутреннего объединения мы получим сведения по клиентам, имеющим хотя бы один заказ. Если заказов у некоторых клиентов несколько, то сведения по ним повторятся столько раз, сколько было сделано заказов. Но предположим, что мы хотим получить информацию по всем клиентам и посмотреть, кто заказывал что-либо, а кто нет. Ответ на такой вопрос позволяют дать запросы с внешним объединением (LEFT|RIGHT [OUTER] JOIN). Тогда в запрос будут включены все записи из таблицы «Клиенты» и те записи с таблицы «Заказы», которые имеют связанные записи в главной таблице. Выбор LEFT либо RIGHT зависит от того, с какой стороны от оператора JOIN находится та таблица, из которой необходимо отобрать все записи:
SELECT Фамилия, Имя, [Дата заказа], Цена FROM Клиенты LEFT JOIN Заказы ON Клиенты.код_клиента = Заказы.код_клиента;
Этот же запрос можно записать и с правым объединением Клиенты RIGHT JOIN Заказы. При задании ссылочной целостности такой запрос не имеет большого смысла, так как результат ничем не будет отличаться от внутреннего соединения, поскольку записей в подчиненной таблице, не связанных с записями в главной таблице, просто не может быть. Кроме соединения записей из нескольких таблиц можно также провести рекурсивное соединение записей из одной и той же таблицы (используется, когда в таблице есть записи, которые ссылаются на другие записи этой же таблицы). Предположим, мы имеем таблицу о сотрудниках, где кроме полей Фамилия, Имя, Должность и т.д. есть поле Подчиняется, в которой записывается тот код сотрудника (первичный ключ данной таблицы), которому данный сотрудник подчиняется:
SELECT Сотр.Должность, Сотр.Фамилия, Сотр.Имя, Сотр1.Должность AS Подчиняется FROM Сотрудники As Сотр LEFT JOIN Сотрудники AS Сотр1 ON Сотр.Подчиняется = Сотр1.КодСотрудника; (в этом запросе целесообразно выполнить внешнее левое объединение для отображения и тех сотрудников, которые не подчиняются никому другому).
Запросы с группировкой
Иногда интерес будут представлять не каждая строка таблицы в отдельности, а итоговые значения по группам данных. Например, может понадобиться общая сумма продаж для клиентов, проживающих в определенном районе или интересно знать средний объем продаж по месяцам, чтобы выяснить тенденции сбыта. Получить ответы на такие вопросы можно с помощью итоговых запросов (запросов с группировкой). Оператор GROUP BY позволяет выделять группы в результирующем множестве записей. Группой являются записи с совпадающими значениями в полях, перечисленных за оператором GROUP BY. Оператор перегруппирует таблицу таким образом, чтобы в каждой группе все строки имели одно и тоже значение поля. Инструкция SELECT затем применяется уже к группам перекомпонованной таблицы. Каждое выражение во фразе SELECT должно принимать единственное значение для группы, т.е. оно может быть либо самим полем, либо арифметическим выражением, включающем это поле, либо константой, либо агрегатной функцией, возвращающей единственное значение для группы. В запросах с группировкой необходимо тщательно следить за включением полей во фразу SELECT, так как в противном случае можно не получить желаемого результата. Если во фразу SELECT будет помещено хотя бы одно поле, которое не является единственным для группы, например ключевое поле подчиненной таблицы, то создание групп будет прервано, так как в результате каждая строка запроса будет уникальна.
В результате запроса только с группировкой по некоторому полю получится таблица, содержащая по одной записи для каждой группы. Группирование записей само по себе ничего не дает. Обычно производятся вычисления для групп. Для этой цели имеется ряд групповых (иначе агрегатных) функций:
SUM – вычисляет сумму всех значений заданного поля в каждой группе;
AVG – вычисляет среднее арифметическое всех значений заданного поля в каждой группе;
STDEV – вычисляет стандартное отклонение всех значений заданного поля в каждой группе;
VAR – вычисляет дисперсию всех значений заданного поля в каждой группе;
COUNT – вычисляет число записей, для которых значение заданного поля отлично от NULL. Для подсчета всех записей необходимо использовать операцию *: Count(*);
MIN – возвращает минимальное значение заданного поля в каждой группе;
MAX – возвращает максимальное значение заданного поля в каждой группе;
FIRST – возвращает первое значение заданного поля в каждой группе;
LAST – возвращает последнее значение заданного поля в каждой группе;
Групповые функции могут применяться сами по себе, без выполнения группировки. Тогда группой будет считаться все отобранные оператором WHERE записи. Например, SELECT Count(*) FROM Заказы; – подсчитает все записи в таблице заказы:
SELECT Sum([Отпускная цена] + [Транс издержки]) As Полная_цена FROM Заказы WHERE Город = “Минск”; – подсчитает полную сумму цен по всем заказам, сделанным из Минска;
SELECT Клиенты.Фамилия, Sum(Заказы.Цена) AS Стоимость FROM Клиенты INNER JOIN Заказы ON Клиенты.КодКлиента = Заказы.КодКлиента WHERE Клиенты.Город ="Минск" GROUP BY Клиенты.Фамилия; – подсчитает полную сумму цен по всем заказам для каждого клиента, проживающим в Минске.
Оператор HAVING действует совместно с оператором GROUP BY и используется для дополнительной селекции записей во время определения групп. Он выполняет те же функции, что и WHERE, но уже в рамках выходного набора. Оператор HAVING устанавливает, какие записи, сгруппированные посредством GROUP BY, должны отображаться и участвовать в групповых операциях. Правила записи < условия отбора > аналогичны правилам формирования <условия отбора> оператора WHERE:
SELECT код_товара FROM Заказы GROUP BY код_товара HAVING Count(*) > 1; - отбирает коды товаров, покупаемых более чем одним покупателем.
Оператор ORDER BY задает порядок сортировки результирующего множества. Обычно он замыкает инструкцию SELECT. Каждая <спецификация> сортировки представляет собой пару вида: <имя поля> [ASC/DESC]. Большинство СУБД требуют, чтобы поле, участвующее в сортировке, присутствовало в выходном наборе:
SELECT Наименование FROM Товары ORDER BY Наименование;
Параметрические запросы
До сих пор условие отбора мы записывали явно после оператора WHERE. Но во многих случаях условие отбора становится известным только во время выполнения программы. SQL позволяет вводить условия отбора в виде параметров запроса. В этом случае такие запросы называются параметрическими или динамическими. Для увеличения эффективности выполнения таких запросов в некоторых СУБД можно вызвать функцию PREPARE, которая подготовит запрос к запуску, т.е. зарезервирует необходимую память и проведет его оптимизацию. Тогда сразу после задания параметра запрос будет готов к запуску. Для освобождения зарезервированной памяти в таком случае применяется функция UNPREPARE. Для задания параметра в Access необходимо некоторый текст, являющийся именем параметра, заключить в квадратные скобки. Этот же текст будет выводится в виде приглашения в окне задания значения параметра. Имя параметра должно отличаться от названий полей таблиц, включенных в запрос. Дополнительно можно явно определить типы параметров с помощью инструкции PARAMETERS в виде [имя параметра] тип данных, [имя параметра] тип данных, …, что позволяет контролировать значения параметров на соответствие типу еще при их вводе. В таком случае инструкция PARAMETERS располагается перед описанием запроса.
PARAMETERS [Введите Год:] INT;
SELECT Клиенты.Фамилия FROM Клиенты INNER JOIN Заказы ON Клиенты.КодКлиента = Заказы.КодКлиента WHERE Year(Заказы.Дата) = [Введите Год:];
Вложенные запросы
Инструкции SELECT могут многократно вкладываться друг в друга. Вложенная инструкция SELECT записывается после оператора WHERE и служит для отбора записей основного запроса. SQL выполняет вложенный подзапрос и затем сравнивает каждую строку основного запроса с результатом вложенного. Вложенные запросы записываются внутри скобок. Например,
SELECT Фамилия, Имя FROM Клиенты WHERE Кредит < (SELECT AVG(Кредит) FROM Клиенты);
Если подчиненный запрос возвращает набор записей, то вместо операторов сравнения можно использовать функции ALL, SOME, ANY, EXISTS, IN, получившие по функции EXISTS название кванторов существования. Квантор существования EXISTS обычно записывается следующим образом EXISTS(SELECT * FROM …). Перед ним можно поставить NOT для инверсии результата. Выражение EXISTS считается истинным тогда и только тогда, когда результат вычисления подзапроса является непустым множеством. В запросах данного типа необходимо явно определять связь между таблицами в виде условия отбора после оператора WHERE вложенного запроса. В качестве примера выберем фамилии покупателей, которым был продан компьютер «Pentium II 350»:
SELECT Фамилия FROM Клиенты WHERE EXISTS(SELECT * FROM Заказы WHERE Заказы.код_клиента = Клиенты.код_клиента And [Наименование товара] = ”Pentium II 350”);
Кванторы SOME и ANY (синонимы) используются для отбора в главном запросе записей, которые удовлетворяют сравнению с записями, отобранными подчиненным запросом. Например: SELECT * FROM Товары WHERE Цена > ANY(SELECT Цена FROM Заказано WHERE Скидка >= 0.25); Этот запрос отбирает все товары, цена которых больше, чем цена любого товара (т.е. самого недорогого), проданного со скидкой 25%.
Квантор ALL используется для отбора в главном запросе записей, которые удовлетворяют сравнению со всеми записями, отобранными подчиненным запросом.
SELECT Марка FROM Товары WHERE Цена > ALL(SELECT Цена FROM Заказано WHERE Скидка >= 0.25); Этот запрос отбирает все товары, цена которых больше, чем цена всех товаров (т.е. самого дорогого), проданных со скидкой 25%.
Квантор IN используются для отбора в главном запросе только тех записей, которые содержат значения, совпадающие с одним из отобранных подчиненным запросом.
SELECT Марка FROM Товары WHERE Цена IN(SELECT Цена FROM Заказано WHERE Скидка >= 0.25); Этот запрос отбирает все товары, цена которых совпадает с ценой товаров, проданных со скидкой 25%.
Запросы действий
С помощью запросов действий пользователь может изменять или переносить данные в таблицах, обновлять, добавлять или удалять группы записей, а также создавать новые таблицы. Некоторые возможности редактирования существуют и в запросах выборки, т.е. изменения, сделанные в запросе, автоматически переносятся на базовые таблицы запроса. Но редактируемыми может быть только небольшая группа запросов. Это простые однотабличные запросы выборки без применения предикатов, группировки и агрегатных функций.
Существует четыре запроса действий: на добавление записей, на удаление записей, на обновления записей и на создание таблицы.
Запрос на обновление
С помощью запроса на обновление можно изменить группу записей, отобранных по заданному критерию. Инструкция запроса на обновление имеет формат вида:
UPDATE <имя таблицы>
SET <имя поля> = {<выражение> , NULL } [, SET <имя поля > = {<выражение> , NULL } ... ]
[WHERE <условие отбора>]
Новые значения полей в записях могут быть пустыми (NULL), либо вычисляться в соответствии с арифметическим выражением. Правила записи арифметических и логических выражений аналогичны соответствующим правилам для вычисляемых полей. Например,
UPDATE Товары
SET Наименование = “Pentium III 800”, Цена = Цена + 250
WHERE Наименование = “Pentium II 350”;
Запрос на добавление
С помощью запроса на добавление записи одной таблицы (все или отобранные запросом) можно добавить в конец другой таблицы. Этот запрос также позволяет добавить в таблицу всего одну запись, состоящую из литералов. Соответственно запрос на добавление имеет форматы двух видов:
INSERT INTO <имя таблицы> [(<список полей>)] VALUES (<список значений>) и
INSERT INTO <имя таблицы> [(<список полей>)] <инструкция SELECT>
В первом формате оператор INSERT предназначен для ввода одной записи состоящей из литералов или выражений. Порядок перечисления полей должен соответствовать порядку значений, перечисленных в списке значений оператора VALUES. При явном перечислении можно опускать задание некоторых полей. Если список полей опущен, то в списке значений должны быть перечислены все значения в порядке следования полей таблицы. Во втором формате оператор INSERT предназначен для добавления записей, отобранных из другой таблицы с помощью инструкции SELECT (таблицы должны быть разными). Здесь также необходимо обеспечить соответствие полей (типов и размеров полей или, по крайней мере, возможность полноценной конвертации данных), перечисленных как после оператора INSERT INTO , так и после SELECT. Например,
INSERT INTO Товары(Наименование, Цена) VALUES («Pentium II 233», 450);
INSERT INTO Клиенты Фамилия, Имя, Отчество) SELECT Фамилия, Имя, Отчество FROM Поставщики WHERE Город = “Минск”;
Запрос на удаление
С помощью запроса на удаление можно сразу удалить группу записей, удовлетворяющих определенному критерию. Этот запрос особенно эффективен при удалении большого числа записей. С помощью запроса на удаление можно явно удалить записи только из одной таблицы. Но если было определено каскадное удаление, то будут также удалены связанные записи из подчиненных таблиц. Запрос на удаление имеет формат вида:
DELETE FROM <имя таблицы> [WHERE <условие отбора>]
Если необязательный оператор WHERE опущен, т. е. условие отбора удаляемых записей отсутствует, удалению подлежат все записи таблицы. Например,
DELETE FROM Товары WHERE Наименование LIKE “Celeron*”;