Запросы манипулирования данными

Запросы выборки

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