SQL - Урок 4. Выборка данных - оператор SELECT

Итак, в нашей БД forum есть три таблицы: users (пользователи), topics (темы) и posts (сообщения). И мы хотим посмотреть, какие данные в них содержатся. Для этого в SQL существует оператор SELECT . Синтаксис его использования следующий:

SELECT что_выбрать FROM откуда_выбрать;

Вместо "что_выбрать" мы должны указать либо имя столбца, значения которого хотим увидеть, либо имена нескольких столбцов через запятую, либо символ звездочки (*), означающий выбор всех столбцов таблицы. Вместо "откуда_выбрать" следует указать имя таблицы.

Давайте сначала посмотрим все столбцы из таблицы users:

SELECT * FROM users;

Вот и все наши данные, которые мы вносили в эту таблицу. Но предположим, что мы хотим посмотреть только столбец id_user (например, в прошлом уроке, нам надо было для заполнения таблицы topics (темы) знать, какие id_user есть в таблице users). Для этого в запросе мы укажем имя этого столбца:

SELECT id_user FROM users;

Ну, а если мы захотим посмотреть, например, имена и e-mail наших пользователей, то мы перечислим интересующие столбцы через запятую:

SELECT name, email FROM users;

Аналогично, вы можете посмотреть, какие данные содержат и другие наши таблицы. Давайте посмотрим, какие у нас существуют темы:

SELECT * FROM topics;

Сейчас у нас всего 4 темы, а если их будет 100? Хотелось бы, чтобы они выводились, например, по алфавиту. Для этого в SQL существует ключевое слово ORDER BY после которого указывается имя столбца по которому будет происходить сортировка. Синтаксис следующий:

SELECT имя_столбца FROM имя_таблицы ORDER BY имя_столбца_сортировки;

По умолчанию сортировка идет по возрастанию, но это можно изменить, добавив ключевое слово DESC

Теперь наши данные отсортированы в порядке по убыванию.

Сортировку можно производить сразу по нескольким столбцам. Например, следующий запрос отсортирует данные по столбцу topic_name, и если в этом столбце будет несколько одинаковых строк, то в столбце id_author будет осуществлена сортировка по убыванию:

Сравните результат с результатом предыдущего запроса.

Очень часто нам не нужна вся информация из таблицы. Например, мы хотим узнать, какие темы были созданы пользователем sveta (id=4). Для этого в SQL есть ключевое слово WHERE , синтаксис у такого запроса следующий:

Для нашего примера условием является идентификатор пользователя, т.е. нам нужны только те строки, в столбце id_author которых стоит 4 (идентификатор пользователя sveta):

Или мы хотим узнать, кто создал тему "велосипеды":

Конечно, было бы удобнее, чтобы вместо id автора, выводилось его имя, но имена хранятся в другой таблице. В последующих уроках мы узнаем, как выбирать данные из нескольких таблиц. А пока узнаем, какие условия можно задавать, используя ключевое слово WHERE.

Оператор	Описание
= (равно)	Отбираются значения равные указанному Пример: SELECT * FROM topics WHERE id_author=4; Результат:
> (больше)	Отбираются значения больше указанного Пример: SELECT * FROM topics WHERE id_author>2; Результат:
< (меньше)	Отбираются значения меньше указанного Пример: SELECT * FROM topics WHERE id_author Результат:
>= (больше или равно)	Отбираются значения большие и равные указанному Пример: SELECT * FROM topics WHERE id_author>=2; Результат:
<= (меньше или равно)	Отбираются значения меньшие и равные указанному Пример: SELECT * FROM topics WHERE id_author Результат:
!= (не равно)	Отбираются значения не равные указанному Пример: SELECT * FROM topics WHERE id_author!=1; Результат:
IS NOT NULL	Отбираются строки, имеющие значения в указанном поле Пример: SELECT * FROM topics WHERE id_author IS NOT NULL; Результат:
IS NULL	Отбираются строки, не имеющие значения в указанном поле Пример: SELECT * FROM topics WHERE id_author IS NULL; Результат: Empty set - нет таких строк.
BETWEEN (между)	Отбираются значения, находящиеся между указанными Пример: SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3; Результат:
IN (значение содержится)	Отбираются значения, соответствующие указанным Пример: SELECT * FROM topics WHERE id_author IN (1, 4); Результат:
NOT IN (значение не содержится)	Отбираются значения, кроме указанных Пример: SELECT * FROM topics WHERE id_author NOT IN (1, 4); Результат:
LIKE (соответствие)	Отбираются значения, соответствующие образцу Пример: SELECT * FROM topics WHERE topic_name LIKE "вел%"; Результат: Возможные метасимволы оператора LIKE будут рассмотрены ниже.
NOT LIKE (не соответствие)	Отбираются значения, не соответствующие образцу Пример: SELECT * FROM topics WHERE topic_name NOT LIKE "вел%"; Результат:

Метасимволы оператора LIKE

Поиск с использованием метасимволов может осуществляться только в текстовых полях.

Самый распространенный метасимвол - % . Он означает любые символы. Например, если нам надо найти слова, начинающиеся с букв "вел", то мы напишем LIKE "вел%", а если мы хотим найти слова, которые содержат символы "клуб", то мы напишем LIKE "%клуб%". Например:

Еще один часто используемый метасимвол - _ . В отличие от %, который обозначает несколько или ни одного символа, нижнее подчеркивание обозначает ровно один символ. Например:

Обратите внимание на пробел между метасимволом и "рыб", если его пропустить, то запрос не сработает, т.к. метасимвол _ обозначает ровно один символ, а пробел - это тоже символ.

На сегодня достаточно. В следующем уроке мы научимся составлять запросы к двум и более таблицам. А пока попробуйте самостоятельно составить запросы к таблице posts (сообщения).

Правил квадратные скобки означают [необязательную часть] конструкции. Вертикальная черта обозначает выбор между вариантами {вар1|вар2}. Многоточие означает возможное повторение несколько раз - раз 1, раз 2 [, …]

Инструкция SELECT

Предписывает ядру базы данных Microsoft Access вернуть сведения из базы данных в виде набора записей.

Синтаксис

SELECT [предикат ] { * | таблица .* | [таблица .]поле1

[, [таблица .]поле2 [, ...]]}
FROM выражение_таблицы [, ...]

Инструкция SELECT включает следующие элементы.

Элемент	Описание
Предикат	Один из следующих предикатов: ALL, DISTINCT, DISTINCTROW или TOP. Предикаты используются для ограничения числа возвращаемых записей. Если предикат не задан, по умолчанию используется ALL.
	Указывает на то, что из заданной таблицы или таблиц выбираются все поля
Таблица	Имя таблицы, из полей которой выбираются записи
поле1 , поле2	Имена полей, содержащих извлекаемые данные. Если задано несколько полей, данные будут извлекаться в порядке перечисления их имен
псевдоним1 , псевдоним2	Имена, используемые как заголовки столбцов вместо исходных названий столбцов таблицы
выражение_таблицы	Одно или несколько имен таблиц, содержащих извлекаемые данные.
внешняя_база_данных	Имя базы данных, содержащей таблицы, указанные в компоненте выражение_таблицы , если они не находятся в текущей базе данных

Замечания

Для выполнения этой операции ядром базы данных Microsoft Access осуществляются поиск указанной таблицы (таблиц), извлечение нужных столбцов, выбор строк, отвечающих заданным условиям, и сортировка или группировка полученных строк в заданном порядке.

Инструкции SELECT не изменяют данные базы.

Инструкция SELECT обычно является первым словом инструкции SQL (Инструкция (строка) SQL. Выражение, определяющее команду SQL, например SELECT, UPDATE или DELETE, и включающее предложения, например WHERE или ORDER BY. Инструкции/строки SQL обычно используются в запросах и в статистических функциях.). Большинство инструкций SQL представляют собой либо инструкции SELECT, либо инструкции SELECT...INTO.

Минимальный синтаксис инструкции SELECT выглядит следующим образом:

SELECT поля FROM таблица

Для выбора всех полей в таблице можно использовать звездочку (*). В следующем примере выбираются все поля таблицы «Сотрудники».

SELECT * FROM Сотрудники;

Если имя поля включено в несколько таблиц в предложении FROM, поместите перед ним имя таблицы и оператор «.» (точка). В следующем примере поле «Отдел» имеется в таблицах «Сотрудники» и «Начальники». С помощью инструкции SQL выбираются отделы из таблицы «Сотрудники» и имена начальников из таблицы «Начальники».

SELECT Сотрудники. Отдел, Руководители. ИмяРуководителя FROM Сотрудники INNER JOIN Руководители WHERE Сотрудники. Отдел = Руководители. Отдел;

При создании объекта RecordSet имя поля таблицы используется ядром базы данных Microsoft Access как имя объекта «Поле» в объекте RecordSet . Если имя поля нуждается в изменении или не предоставляется выражением, с помощью которого генерируется поле, воспользуйтесь зарезервированным словом (Зарезервированное слово. Слово, являющееся элементом языка, например языка Visual Basic. К зарезервированным словам относятся имена инструкций, встроенных функций и типов данных, методов, операторов и объектов.) AS. В следующем примере показано, как заголовок «День» используется для присвоения имени возвращенному объекту Поле в полученном объекте RecordSet .

SELECT ДеньРождения AS День FROM Сотрудники;

При работе со статистическими функциями или запросами, возвращающими неоднозначные или одинаковые имена объекта Поле , следует воспользоваться предложением AS для создания другого имени объекта Поле . В следующем примере возвращенному объекту Поле в полученном объекте RecordSet присваивается имя «Перепись».

SELECT COUNT(КодСотрудника) AS Перепись FROM Сотрудники;

При работе с инструкцией SELECT можно использовать дополнительные предложения для дальнейшего ограничения и упорядочения полученных данных. Дополнительные сведения см. в разделе справки, посвященном используемому предложению.

Предложение FROM

Задает таблицы и запросы, которые содержат перечисленные в инструкции SELECT поля.

Синтаксис

SELECT список_полей
FROM выражение_таблицы

Инструкция SELECT, содержащая предложение FROM, включает в себя следующие элементы:

Элемент	Описание
список_полей
выражение_таблицы	Выражение, определяющее одну или несколько таблиц - источников данных. Выражение может представлять собой имя таблицы, сохраненное имя запроса или результирующее выражение, составленное с использованием операций INNER JOIN, LEFT JOIN или RIGHT JOIN
внешняя_база_данных	Полный путь к внешней базе данных, содержащей все таблицы, указанные в выражении_таблицы

Замечания

Наличие предложения FROM после инструкции SELECT обязательно.

Порядок перечисления таблиц в выражении_таблицы не имеет значения.

Применяя связанные таблицы (Связанная таблица. Таблица, которая сохраняется в файле, не принадлежащем открытой базе данных, однако является доступной из Microsoft Access. Пользователь имеет возможность добавлять, удалять и изменять записи в связанной таблице, но не может изменять ее структуру.) вместо предложения IN, можно сделать процесс извлечения данных из внешней базы данных более простым и эффективным.

В приведенном ниже примере показано, как извлечь данные из таблицы «Сотрудники».

SELECT Фамилия, Имя

FROM Сотрудники;

Указывают записи, выбранные для запросов SQL (Язык SQL (Structured Query Language). Язык структурированных запросов и программирования баз данных, широко используемый для доступа, запросов, обновления и управления данными в реляционных СУБД.).

Синтаксис

SELECT ]]
FROM таблица

Инструкция SELECT, содержащая эти предикаты, включает в себя следующие компоненты:

Компонент	Описание
	Подразумевается, если не включен ни один из предикатов. Ядро базы данных Microsoft Access выбирает все записи, отвечающие условиям инструкции SQL (Инструкция (строка) SQL. Выражение, определяющее команду SQL, например SELECT, UPDATE или DELETE, и включающее предложения, например WHERE или ORDER BY. Инструкции/строки SQL обычно используются в запросах и в статистических функциях.). На следующих двух идентичных примерах показано, как осуществить возврат всех записей таблицы «Сотрудники». FROM Сотрудники ORDER BY КодСотрудника; FROM Сотрудники ORDER BY КодСотрудника;
	Исключает записи, содержащие повторяющиеся данные в выбранных полях. В результаты запроса включаются только уникальные значения каждого из полей, перечисленных в инструкции SELECT. Например, некоторые сотрудники, перечисленные в таблице «Сотрудники», могут быть однофамильцами. Если две записи содержат в поле «Фамилия» фамилию «Иванов», приведенная ниже инструкция SQL возвращает только одну запись, содержащую фамилию «Иванов». SELECT DISTINCT LastName Если компонент DISTINCT опускается, запрос возвращает обе записи с фамилией «Иванов». Если предложение SELECT содержит несколько полей, сочетание значений всех полей включается в результаты запроса только при условии уникальности для данной записи. Результаты запроса, использующего компонент DISTINCT, не обновляются и не отражают последующих изменений, внесенных другими пользователями.
	Исключает данные записей, повторяющихся целиком, а не содержащих отдельные поля с одинаковыми данными. Предположим, что создан запрос, соединяющий таблицы «Клиенты» и «Заказы» по полю «КодКлиента». Таблица «Клиенты» не содержит повторяющихся полей «КодКлиента», однако они имеются в таблице «Заказы», поскольку у каждого клиента может быть несколько заказов. В следующей инструкции SQL показано, как с помощью компонента DISTINCTROW составить список организаций, сделавших по крайней мере один заказ, без упоминания сведений об этих заказах. SELECT DISTINCTROW Название FROM Клиенты INNER JOIN Заказы ON Клиенты. КодКлиента = Заказы. КодКлиента ORDER BY Название; Если компонент DISTINCTROW опущен, в результате запроса выводится несколько строк для каждой организации, делавшей заказы неоднократно. Компонент DISTINCTROW действует только при выборе полей из некоторых таблиц, используемых в запросе. Компонент DISTINCTROW не учитывается, если запрос включает только одну таблицу или если поля извлекаются из всех таблиц.
TOP n	Возвращает определенное количество записей, находящихся в числе первых или последних записей диапазона, заданного предложением ORDER BY. Предположим, что требуется отобразить имена 25 лучших студентов выпуска 1994 года. FirstName, LastName WHERE GraduationYear = 2003 ORDER BY GradePointAverage DESC; Если не включить предложение ORDER BY, в ответ на запрос будет выдан произвольный набор 25 записей из таблицы «Студенты», удовлетворяющий предложению WHERE. Предикат TOP не предполагает выбора между равными значениями. Если бы в 25-ой и 26-ой записях предыдущего примера был указан одинаковый средний балл, запрос возвратил бы 26 записей. Можно также использовать зарезервированное слово PERCENT для получения некоторого процента первых или последних записей диапазона, заданного предложением ORDER BY. Предположим, что вместо 25 лучших требуется отобразить 10% худших студентов выпуска. SELECT TOP 10 PERCENT FirstName, LastName WHERE GraduationYear = 2003 ORDER BY GradePointAverage ASC; Предикат ASC задает вывод значений из нижней части диапазона. Значение, которое следует за предикатом TOP, должно быть значением типа Integer (Тип данных Integer. Основной тип данных, используемый для сохранения целых значений. Переменная типа Integer сохраняется как 64-битовое (8-байтовое) число в диапазоне от -32768 до 32767.) без знака. Предикат TOP не влияет на возможность обновления запроса.
таблица	Имя таблицы, из которой извлекаются записи.

См. также

Инструкция SELECT

Предложение FROM

Предложение WHERE

Определяет, какие записи из таблиц, перечисленных в предложении FROM, обрабатываются инструкциями SELECT, UPDATE или DELETE.

Синтаксис

SELECT список_полей
FROM выражение_таблицы
WHERE условия_отбора

Инструкция SELECT, содержащая предложение WHERE, включает следующие части.

Часть	Описание
список_полей	Имя поля или полей, извлекаемых вместе с любыми псевдонимами (Псевдоним (SQL). Альтернативное имя для таблицы или поля в выражении. Псевдонимы обычно используют как более короткие имена таблиц или полей для удобства последующих ссылок в программах, для предотвращения неоднозначных ссылок, а также для получения более информативных имен при выводе результатов запроса.), предикатами (ALL, DISTINCT, DISTINCTROW или TOP) или с любым другим параметром инструкции SELECT.
выражение_таблицы	Имя таблицы или таблиц, из которых извлекаются данные.
условия_отбора	Выражение (Выражение. Сочетание математических и логических операторов, констант, функций, имен полей, элементов управления и свойств, в результате обработки которого получается единственное значение. Выражение может выполнять вычисления, обрабатывать текст или проверять данные.), которому должны соответствовать записи, включаемые в результаты запроса.

Замечания

Ядро базы данных Microsoft Access выбирает записи, отвечающие условиям, перечисленным в предложении WHERE. Если предложение WHERE не задано, запрос возвращает все строки таблицы. Если в запросе указано несколько таблиц, но не задано предложение WHERE или JOIN, запрос создает декартово произведение (Декартово произведение. Является результатом выполнения инструкции SQL SELECT, в которой имеется предложение FROM со ссылками на две или более таблиц и нет предложения WHERE или JOIN, указывающего способ объединения.) таблиц.

Предложение WHERE использовать не обязательно, но если оно используется, то должно следовать за предложением FROM. Например, можно выбрать всех сотрудников из отдела продаж (WHERE Отдел = "Продажи") или всех заказчиков в возрасте от 18 до 30 (WHERE Возраст Between 18 And 30).

Если предложение JOIN не используется для операции соединения SQL нескольких таблиц, полученный объект Набор записей невозможно будет обновлять.

Предложение WHERE аналогично предложению HAVING и определяет выбранные записи. После того как записи сгруппированы предложением GROUP BY, предложение HAVING также определяет отображаемую запись.

Предложение WHERE используется для исключения записей, которые не требуется группировать с помощью предложения GROUP BY.

С помощью различных выражений определите, какие записи возвращаются инструкцией SQL. Например, следующая инструкция SQL выбирает всех сотрудников, зарплата которых превышаетрублей.

SELECT Фамилия, Зарплата FROM Сотрудники WHERE Зарплата > 21000;

Предложение WHERE может содержать до 40 выражений, соединенных логическими операторами (например, AND и OR ).

При вводе имени поля с пробелами или знаками препинания следует заключить его в квадратные скобки (). Например, таблица сведений о клиентах может содержать сведения о конкретных клиентах.

SELECT [Любимый ресторан клиента]

Задавая аргумент условия_отбора , литералы даты (Литерал даты. Любая последовательность знаков допустимого формата, заключенная в знаки номера (#). Допустимыми форматами являются формат даты, заданный в настройках языка и стандартов, и универсальный формат даты.) следует представлять в американском формате, даже если используется неамериканская версия ядра базы данных Microsoft Access. Например, дата «10 мая 1996» записывается как 10/5/96 в Великобритании и как 10.05.1996 в России. Не забывайте заключать литералы даты в знаки номера (#), как показано в приведенных ниже примерах.

Чтобы найти записи за 10 мая 1996 в базе данных Великобритании, используйте следующую инструкцию SQL:

SELECT * FROM Заказы WHERE ДатаОтгрузки = #10.05.1996#;

Можно также воспользоваться функцией DateValue , распознающей международные параметры, установленные Microsoft Windows®. Например, для России используйте такой код:

SELECT * FROM Заказы WHERE ДатаОтгрузки = DateValue("10.05.1996");

И следующий код - для Великобритании:

SELECT * FROM Заказы WHERE ДатаОтгрузки = DateValue("10/5/96");

Примечание. Если столбец, указанный в строке условий отбора, относится к типу GUID (Код реплики (GUID). 16-байтовое поле в базе данных Microsoft Access, используемое для уникальных идентификаторов при репликации. Коды GUID используются для идентификации реплик, наборов реплик, таблиц, записей и других объектов. В базах данных Microsoft Access коды GUID называются кодами реплик.), в условиях отбора используется немного другой синтаксис.

WHERE ReplicaID = {GUID {AB-CDEF0ABCDEF}}

Убедитесь, что вложенные скобки и дефисы расположены правильно.

Исходная страница: http://office. /ru-ru/access/HA.aspx? pid=CH

Предложение GROUP BY

Соединяет записи с одинаковыми значениями, находящиеся в указанном списке полей, в одну запись. Сводное значение создается для каждой записи, если в инструкцию SELECT включена статистическая функция SQL, например Sum или Count .

Синтаксис

SELECT список_полей
FROM таблица
WHERE условие_отбора

Инструкция SELECT, содержащая предложение GROUP BY, включает в себя следующие элементы:

Элемент	Описание
список_полей	Имена полей, извлекаемых вместе с любыми псевдонимами (Псевдоним (SQL). Альтернативное имя для таблицы или поля в выражении. Псевдонимы обычно используют как более короткие имена таблиц или полей для удобства последующих ссылок в программах, для предотвращения неоднозначных ссылок, а также для получения более информативных имен при выводе результатов запроса.) и статистическими функциями SQL, предикатами (ALL, DISTINCT, DISTINCTROW или TOP) или другими параметрами инструкции SELECT
таблица
условия_отбора	Условие отбора. Если инструкция содержит предложение WHERE, то после его применения к записям значения будут сгруппированы ядром базы данных Microsoft Access.
список_полей_группы	списке_полей_группы

Замечания

Предложение GROUP BY не является обязательным.

Если в инструкцию SELECT не включены статистические функции SQL, сводные значения не вычисляются.

Значения полей GROUP BY, равные Null (Null. Значение, которое можно ввести в поле или использовать в выражениях и запросах для указания отсутствующих или неизвестных данных. В Visual Basic ключевое слово Null указывает значение Null. Некоторые поля, такие как поля первичного ключа, не могут содержать значения Null.) , группируются и не опускаются. Однако значения Null не вычисляются ни одной из статистических функций SQL.

Предложение WHERE используется для исключения строк, группировать которые не требуется. Предложение HAVING используется для фильтрации записей после группировки.

Поля из списка полей GROUP BY, не содержащие данных типа Memo (Тип данных «Поле МЕМО». Тип данных поля в базе данных Microsoft Access. Поле МЕМО может содержать до 65535 символов.) или Объект OLE (Тип данных «Поле объекта OLE». Тип данных поля, используемый для сохранения объектов из других приложений, связанных или внедренных в базу данных Microsoft Access.), могут ссылаться на любое поле в любой таблице, указанной в предложении FROM, даже если поле не включено в инструкцию SELECT. Для этого достаточно наличия в инструкции SELECT хотя бы одной статистической функции SQL. Ядро баз данных Microsoft Access не позволяет выполнять группировку по полям, содержащим данные типа «поле MEMO» или «Объект OLE».

Все поля в списке полей SELECT должны либо содержаться в предложении GROUP BY, либо быть аргументами статистической функции SQL.

См. также

Инструкция SELECT

Инструкция SELECT...INTO

Предикаты ALL, DISTINCT, DISTINCTROW, TOP

Предложение FROM

Предложение HAVING

Предложение ORDER BY

Предложение WHERE

Статистические функции SQL

Исходная страница: http://office. /ru-ru/access/HA.aspx? pid=CH

Предложение HAVING

Определяет сгруппированные записи, которые должны отображаться в инструкции SELECT с предложением GROUP BY. После того как записи будут сгруппированы предложением GROUP BY, предложение HAVING покажет те из них, которые отвечают его условиям.

Синтаксис

SELECT список_полей
FROM таблица
WHERE выбор_условия
GROUP BY список_полей_группы

Инструкция SELECT, содержащая предложение HAVING, включает в себя следующие элементы:

Элемент	Описание
список_полей	Имена полей, загружаемых вместе с любыми псевдонимами (Псевдоним (SQL). Альтернативное имя для таблицы или поля в выражении. Псевдонимы обычно используют как более короткие имена таблиц или полей для удобства последующих ссылок в программах, для предотвращения неоднозначных ссылок, а также для получения более информативных имен при выводе результатов запроса.) и статистическими функциями SQL, предикатами (ALL, DISTINCT, DISTINCTROW или TOP) или с другими параметрами инструкции SELECT.
таблица	Имя таблицы, из которой загружаются записи
условие_отбора	Условие отбора. Если инструкция содержит предложение WHERE, то после его применения к записям ядро базы данных Microsoft Access сгруппирует значения.
список_полей_группы	Имена полей (не более 10), используемых для группировки записей. Порядок следования имен в списке_полей_группы определяет уровень группировки - от самого высокого до самого низкого
условие_группы	Выражение, определяющее отображаемые записи

Замечания

Предложение HAVING не является обязательным.

Предложение HAVING аналогично предложению WHERE, которым определяется выбор записей. После группировки записей предложением GROUP BY, предложение HAVING определяет отображаемые записи.

SELECT КодТипа,

Sum(НаСкладе)

FROM Товары

GROUP BY КодТипа

HAVING Sum(НаСкладе) > 100 And Like "ТЕЛ*";

Предложение HAVING может содержать до 40 выражений, связанных логическими операторами, такими как And и Or .

Исходная страница: http://office. /ru-ru/access/HA.aspx? pid=CH

Предложение ORDER BY

Сортирует записи, возвращенные запросом, по возрастанию или по убыванию значений указанного поля (полей).

Синтаксис

SELECT список_полей
FROM таблица
WHERE условие_отбора
[, поле2 ][, ...]]]

Инструкция SELECT, содержащая предложение ORDER BY, включает следующие элементы.

Элемент	Описание
список_полей	Имена полей, извлекаемых вместе с любыми псевдонимами (Псевдоним (SQL). Альтернативное имя для таблицы или поля в выражении. Псевдонимы обычно используют как более короткие имена таблиц или полей для удобства последующих ссылок в программах, для предотвращения неоднозначных ссылок, а также для получения более информативных имен при выводе результатов запроса.) и статистическими функциями SQL, предикатами (ALL, DISTINCT, DISTINCTROW или TOP) или с другими параметрами инструкции SELECT.
таблица	Имя таблицы, из которой извлекаются записи
условия_отбора	Условия отбора. Если инструкция содержит предложение WHERE, то после его применения к записям ядро базы данных Microsoft Access упорядочит значения записей
поле1 , поле2	Имена полей, по которым выполняется сортировка записей.

Замечания

Предложение ORDER BY не является обязательным. Его следует использовать, когда необходимо отобразить данные в отсортированном виде.

По умолчанию используется порядок сортировки (Порядок сортировки. Способ упорядочивания данных в зависимости от их значений и типа. Данные могут быть отсортированы по алфавиту , по числовым значениям или по дате. Порядок сортировки может быть возрастающим (от 0 до 100, от А до Я) или убывающим (от 100 до 0, от Я до A).) по возрастанию (от A к Z, от 0 к 9). Приведенные ниже примеры демонстрируют сортировку имен сотрудников по фамилиям.

SELECT Фамилия, Имя

FROM Сотрудники

ORDER BY Фамилия;

SELECT Фамилия, Имя

FROM Сотрудники

ORDER BY Фамилия ASC;

Чтобы сортировка полей выполнялась по убыванию (от Z к A, от 9 к 0), добавьте к имени каждого из этих полей зарезервированное слово DESC. Следующий пример демонстрирует сортировку по убыванию в зависимости от размера заработной платы сотрудников.

SELECT Фамилия, Зарплата

FROM Сотрудники

ORDER BY Зарплата DESC, Фамилия;

Если в предложении ORDER BY указать поля, содержащие данные типа Поле MEMO (Тип данных «Поле МЕМО». Тип данных поля в базе данных Microsoft Access. Поле МЕМО может содержать до 65535 символов.) или Поле объекта OLE (Тип данных «Поле объекта OLE». Тип данных поля, используемый для сохранения объектов из других приложений, связанных или внедренных в базу данных Microsoft Access.), это приведет к возникновению ошибки. Ядро базы данных Microsoft Access не может выполнять сортировку этих типов полей.

Предложение ORDER BY обычно является последним в инструкции SQL (Инструкция (строка) SQL. Выражение, определяющее команду SQL, например SELECT, UPDATE или DELETE, и включающее предложения, например WHERE или ORDER BY. Инструкции/строки SQL обычно используются в запросах и в статистических функциях.).

В предложение ORDER BY можно включать дополнительные поля. Сначала записи сортируются по полю, указанному в предложении ORDER BY первым. Затем для записей с одинаковыми значениями первого поля выполняется сортировка по полю, указанному вторым, и так далее.
См. также

Инструкция SELECT

Инструкция SELECT...INTO

Предикаты ALL, DISTINCT, DISTINCTROW, TOP

Предложение FROM

Предложение GROUP BY

Предложение HAVING

Предложение WHERE

Статистические функции SQL

Исходная страница: http://office. /ru-ru/access/HA.aspx? pid=CH

Операция INNER JOIN

Соединяет записи из двух таблиц, если в связующих полях этих таблиц содержатся одинаковые значения.

Синтаксис

FROM таблица1 INNER JOIN таблица2 ON таблица1 .поле1 оператор_сравнения таблица2 .поле2

Операция INNER JOIN состоит из следующих элементов:

Элемент	Описание
таблица1 , таблица2	Имена таблиц, содержащих соединяемые записи
поле1 , поле2	Имена связываемых полей. Поля, не являющиеся числовыми, должны относиться к одному типу данных (Тип данных. Характеристика поля, определяющая тип данных, который может содержать это поле. Существуют следующие типы данных: Boolean, Integer, Long, Currency, Single, Double, Date, String и Variant (по умолчанию).) и содержать данные одного вида. Однако имена этих полей могут быть разными
оператор_сравнения	Любой оператор сравнения: (=, <, >, <=, >= или <>)

Один запрос SQL можно вкладывать в другой. Подзапрос - есть не что иное, как запрос внутри запроса. Обычно, подзапрос используется в конструкции WHERE. Но возможны и другие способы использования подзапросов.

Запрос Q011. Выводится информация о товарах из таблицы m_product, коды которых есть и в таблице m_income:

SELECT *
FROM m_product
WHERE id IN (SELECT product_id FROM m_income);

Запрос Q012. Выводится список товаров из таблицы m_product, кодов которых нет в таблице m_outcome:

SELECT *
FROM m_product
WHERE id NOT IN (SELECT product_id FROM m_outcome);

Запрос Q013. В этом запросе SQL выводится уникальный список кодов и названий товаров, коды которых есть в таблице m_income, но которых нет в таблице m_outcome:

SELECT DISTINCT product_id, title
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);

Запрос Q014. Выводится из таблицы m_category уникальный список категорий, названия которых начинаются на букву М:

SELECT DISTINCT title
FROM m_product
WHERE title LIKE "М*";

Запрос Q015. Пример выполнения арифметических операций над полями в запросе и переименования полей в запросе (alias). В этом примере для каждой записи о расходе товара подсчитываются сумма расхода = количество*цена и размер прибыли, при предположении, что прибыль составляет 7 процентов от суммы продаж:

Price, amount*price AS outcome_sum,
amount*price/100*7 AS profit
FROM m_outcome;

Запрос Q016. Проанализировав и упростив арифметические операции, можно увеличить скорость выполнения запроса:

outcome_sum*0.07 AS profit
FROM m_outcome;

Запрос Q017. При помощи инструкции INNER JOIN можно объединить данные нескольких таблиц. В следующем примере, в зависимости от значения ctgry_id, каждой записи таблицы m_income, сопоставляется название категории из таблицы m_category, к которой принадлежит товар:

SELECT c.title, b.title, dt, amount, price, amount*price AS income_sum
FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
INNER JOIN m_category AS c ON b.ctgry_id=c.id
ORDER BY c.title, b.title;

Запрос Q018. Такие функции как SUM - сумма, COUNT - количество, AVG – среднее арифметическое значение, MAX – максимальное значение, MIN – минимальное значение называются агрегатными функциями. Они принимают множество значений, и после их обработки возвращают единственное значение. Пример подсчета суммы произведения полей amount и price при помощи агрегатной функции SUM:

SELECT SUM(amount*price) AS Total_Sum
FROM m_income;

Запрос Q019. Пример использования нескольких агрегатных функций:

SELECT Sum(amount) AS Amount_Sum, AVG(amount) AS Amount_AVG,
MAX(amount) AS Amount_Max, Min(amount) AS Amount_Min,
Count(*) AS Total_Number
FROM m_income;

Запрос Q020. В этом примере подсчитана сумма всех товаров с кодом 1, оприходованных в июне 2011 года:

SELECT
FROM m_income
WHERE product_id=1 AND dt BETWEEN #6/1/2011# AND #6/30/2011#;.

Запрос Q021. Следующий запрос SQL вычисляет на какую сумму было продано товаров, имеющих код 4 или 6:

SELECT
FROM m_outcome
WHERE product_id=4 OR product_id=6;

Запрос Q022. Вычисляется на какую сумму было продано 12 июня 2011 года товаров, имеющих код 4 или 6:

SELECT Sum(amount*price) AS outcome_sum
FROM m_outcome
WHERE (product_id=4 OR product_id=6) AND dt=#6/12/2011#;

Запрос Q023. Задача такова. Вычислить на какую общую сумму было оприходовано товаров категории "Хлебобулочные изделия".

Для решения этой задачи нужно оперировать тремя таблицами: m_income, m_product и m_category, потому что:
- количество и цена оприходованных товаров хранятся в таблице m_income;
- код категории каждого товара хранится в таблице m_product;
- название категории title хранится в таблице m_category.

Для решения данной задачи воспользуемся следующим алгоритмом:
- определение кода категории "Хлебобулочные изделия" из таблицы m_category посредством подзапроса;
- соединение таблиц m_income и m_product для определения категории каждого оприходованного товара;
- вычисление суммы прихода(= количество*цена) для товаров, код категории которых равен коду, определенному вышеуказанным подзапросом.

FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title="Хлебобулочные изделия");

Запрос Q024. Задачу вычисления общей суммы оприходованных товаров категории "Хлебобулочные изделия" решим следующим алгоритмом:
- каждой записи таблицы m_income, в зависимости от значения его product_id, из таблицы m_category, сопоставить название категории;
- выделить записи, для которых категория равна "Хлебобулочные изделия";
- вычислить сумму прихода = количество*цена.

SELECT Sum(amount*price) AS income_sum
FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)
WHERE c.title="Хлебобулочные изделия";

Запрос Q025. В этом примере вычисляется сколько наименований товаров было израсходовано:

SELECT COUNT(product_id) AS product_cnt
FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;

Запрос Q026. Инструкция GROUP BY используется для группировки записей. Обычно записи группируются по значению одного или нескольких полей, и относительно каждой группы применяется какая-либо агрегатная операция. Например, следующий запрос состявляет отчет о продаже товаров. То есть генерируется таблица, в которой будут названия товаров и сумма, на которую они проданы:

SELECT title, SUM(amount*price) AS outcome_sum
FROM m_product AS a INNER JOIN m_outcome AS b
ON a.id=b.product_id
GROUP BY title;

Запрос Q027. Отчет о продажах по категориям. То есть генерируется таблица, в которой будут названия категорий товаров, общая сумма, на которую проданы товары данных категорий, и средняя сумма продаж. Функция ROUND использована для округления среднего значения до сотой доли (второй знак после разделителя целой и дробной частей):

SELECT c.title, SUM(amount*price) AS outcome_sum,
ROUND(AVG(amount*price),2) AS outcome_sum_avg
FROM (m_product AS a INNER JOIN m_outcome AS b ON a.id=b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id=c.id
GROUP BY c.title;

Запрос Q028. Вычисляется для каждого товара общее и среднее количество его поступлений и выводит информацию о товарах, общее количество поступления которых не менее 500:

SELECT product_id, SUM(amount) AS amount_sum,
Round(Avg(amount),2) AS amount_avg
FROM m_income
GROUP BY product_id
HAVING Sum(amount)>=500;

Запрос Q029. В этом запросе вычисляется для каждого товара сумма и среднее значение его поступлений, осуществленных во втором квартале 2011 года. Если общая сумма прихода товара не менее 1000, то отображается информация об этом товаре:

SELECT title, SUM(amount*price) AS income_sum
FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
WHERE dt BETWEEN #4/1/2011# AND #6/30/2011#
GROUP BY title
HAVING SUM(amount*price)>=1000;

Запрос Q030. В некоторых случаях нужно сопоставлять каждой записи некоторой таблицы каждую запись другой таблицы; что называется декартовым произведением. Таблица, образующаяся в результате такого соединения, называется таблицей Декарта. Например, если некоторая таблица А имеет 100 записей и таблица В имеет 15 записей, то их таблица Декарта будет состоять из 100*15=150 записей. Следующий запрос соединяет каждую запись таблицы m_income с каждой записью таблицы m_outcome:

SELECT *FROM m_income, m_outcome;

Запрос Q031. Пример группирования записей по двум полям. Следующий запрос SQL вычисляет по каждому поставщику сумму и количество поступивщих от него товаров:

SUM(amount*price) AS income_sum

Запрос Q032. Пример группирования записей по двум полям. Следующий запрос вычисляет для каждого поставщика сумму и количество его продуктов, проданных нами:

SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
GROUP BY supplier_id, product_id;

Запрос Q033. В этом примере два вышеприведенных запроса (q031 и q032) использованы как подзапросы. Результаты этих запросов методом LEFT JOIN объединены в один отчет. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже поступил, но еще не реализован, то клетка outcome_sum для этой записи будет пустой. Также необходимо отметить, что данный запрос служит только примером использования относительно сложных запросов в качестве подзапроса. Производительность данного запроса SQL при большом объеме данных сомнительна:

SELECT *
FROM
SUM(amount*price) AS income_sum
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS a
LEFT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS b
ON (a.product_id=b.product_id) AND (a.supplier_id=b.supplier_id);

Запрос Q034. В этом примере два вышеприведенных запроса (q031 и q032) использованы как подзапросы. Результаты этих запросов методом RIGTH JOIN объединены в один отчет. Следующий запрос выводит отчет о сумме платежей каждого клиента по использованным им платежным системам и сумме сделанных им инвестиций. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже реализован, но еще не поступил, то клетка income_sum для этой записи будет пустой. Наличие таких пустых клеток является показателем ошибки в учете продаж, так как до продажы сначала необходимо, чтобы соответствующий товар поступил:

SELECT *
FROM
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS a
RIGHT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS b
ON (a.supplier_id=b.supplier_id) AND (a.product_id=b.product_id);

Запрос Q035. Выводится отчет о сумме доходов и расходов по продуктам. Для этого создается список продуктов по таблицам m_income и m_outcome, затем для каждого продукта из этого списка вычисляется сумма его приходов по таблице m_income и сумма его расходов по таблице m_outcome:

SELECT product_id, SUM(in_amount) AS income_amount,
SUM(out_amount) AS outcome_amount
FROM
(SELECT product_id, amount AS in_amount, 0 AS out_amount
FROM m_income
UNION ALL
SELECT product_id, 0 AS in_amount, amount AS out_amount
FROM m_outcome) AS t
GROUP BY product_id;

Запрос Q036. Функция EXISTS возвращает значение TRUE, если переданное ей множество содержит элементы. Функция EXISTS возвращает значение FALSE, если переданное ей множество пустое, то есть не содержит элементов. Следующий запрос выводит коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:

SELECT DISTINCT product_id
FROM m_income AS a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b

Запрос Q037. Выводятся коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:

SELECT DISTINCT product_id
FROM m_income AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)

Запрос Q038. Выводятся коды товаров, которые содержатся как в таблице m_income, но не содержатся в таблице m_outcome:

SELECT DISTINCT product_id
FROM m_income AS a
WHERE NOT EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);

Запрос Q039. Выводится список товаров, сумма продаж которых максимальная. Алгоритм таков. Для каждого товара вычисляется сумма его продаж. Затем, определяется максимум этих сумм. Затем, для каждого товара снова вычисляется сумма его продаж, и выводятся код и сумма продаж товаров, сумма продаж которых равна максимальной:

SELECT product_id, SUM(amount*price) AS amount_sum
FROM m_outcome
GROUP BY product_id
HAVING SUM(amount*price) = (SELECT MAX(s_amount)
FROM (SELECT SUM(amount*price) AS s_amount FROM m_outcome GROUP BY product_id));

Запрос Q040. Зарезервированное слово IIF (условный оператор) используется для оценки логического выражения и выполнения того или иного действия в зависимости от результата (TRUE или FALSE). В следующем примере поставка товара считается «малой», если количество меньше 500. В противном случае, то есть количество поступления больше или равно 500, поставка считается «большой»:

SELECT dt, product_id, amount,
IIF(amount<500,"малая","большая") AS mark
FROM m_income;

Запрос SQL Q041. В случае, когда оператор IIF используется несколько раз, удобнее заменить его оператором SWITCH. Оператор SWITCH (оператор множественного выбора) используется для оценки логического выражения и выполнения того или иного действия в зависимости от результата. В следующем примере поставленная партия считается «малой», если количество товара в партии меньше 500. В противном случае, то есть если количество товара больше или равно 500, партия считается «большой»:

SELECT dt, product_id, amount,
SWITCH(amount<500,"малая",amount>=500,"большая") AS mark
FROM m_income;

Запрос Q042. <300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:

SELECT dt, product_id, amount,
IIF(amount<300,"малая",
IIF(amount<1000,"средняя","большая")) AS mark
FROM m_income;

Запрос SQL Q043. В следующем запросе если количество товара в поступившей партии меньше 300, то партия считается «малой». В противном случае, то есть если условие amount<300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:

SELECT dt, product_id, amount,
SWITCH(amount<300,"малая",
amount<1000,"средняя",
amount>=1000,"большая") AS mark
FROM m_income;

Запрос SQL Q044. В следующем запросе продажи разделяются на три группы: малые (до 150), средние (от150 до 300), большие (300 и более). Далее, для каждой группы вычисляется итоговая сумма:

SELECT Category, SUM(outcome_sum) AS Ctgry_Total
FROM (SELECT amount*price AS outcome_sum,
IIf(amount*price<150,"малая",
IIf(amount*price<300,"средняя","большая")) AS Category
FROM m_outcome) AS t
GROUP BY Category;

Запрос SQL Q045. Функция DateAdd используется для прибавления дней, месяцев или лет к данной дате и получения новой даты. Следующий запрос:
1) к дате из поля dt прибавляет 30 дней и отображает новую дату в поле dt_plus_30d;
2) к дате из поля dt прибавляет 1 месяц и отображает новую дату в поле dt_plus_1m:

SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m
FROM m_income;

Запрос SQL Q046. Функция DateDiff предназначена для вычисления разницы между двумя датами в различных единицах (днях, месяцах или годах). Следующий запрос вычисляет разницу между датой в поле dt и текущей датой в днях, месяцах и годах:

SELECT dt, DateDiff("d",dt,Date()) AS last_day,
DateDiff("m",dt,Date()) AS last_months,
DateDiff("yyyy",dt,Date()) AS last_years
FROM m_income;

Запрос SQL Q047. Вычисляются количество дней со дня поступления товара (таблица m_income) до текущей даты с помощью функции DateDiff и сопоставляется срок годности (таблица m_product):

DateDiff("d",dt,Date()) AS last_days
FROM m_income AS a INNER JOIN m_product AS b
ON a.product_id=b.id;

Запрос SQL Q048. Вычисляются количество дней со дня поступления товара до текущей даты, затем проверяется превышает ли это количество срок годности:

SELECT a.id, product_id, dt, lifedays,
DateDiff("d",dt,Date()) AS last_days, IIf(last_days>lifedays,"Да","Нет") AS date_expire
FROM m_income a INNER JOIN m_product b
ON a.product_id=b.id;

Запрос SQL Q049. Вычисляются количество месяцев со дня поступления товара до текущей даты. В столбце month_last1 вычисляется абсолютное количество месяцев, в столбце month_last2 вычисляется количество полных месяцев:

SELECT dt, DateDiff("m",dt,Date()) AS month_last1,
DateDiff("m",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
FROM m_income;

Запрос SQL Q050. Выводится поквартальный отчет о количестве и сумме оприходованных товаров за 2011 год:

SELECT kvartal, SUM(outcome_sum) AS Total
FROM (SELECT amount*price AS outcome_sum, month(dt) AS m,
SWITCH(m<4,1,m<7,2,m<10,3,m>=10,4) AS kvartal
FROM m_income WHERE year(dt)=2011) AS t
GROUP BY kvartal;

Запрос Q051. Следующий запрос помогает выяснить, удалось ли пользователям ввести в систему информацию о расходе товара на сумму большую, чем сумма прихода товара:

SELECT product_id, SUM(in_sum) AS income_sum, SUM(out_sum) AS outcome_sum
FROM (SELECT product_id, amount*price as in_sum, 0 as out_sum
from m_income
UNION ALL
SELECT product_id, 0 as in_sum, amount*price as out_sum
from m_outcome) AS t
GROUP BY product_id
HAVING SUM(in_sum)

Запрос Q052. Нумерацию строк, возвращаемых запросом, реализуют по-разному. Например, можно перенумеровать строки отчета, подготовленного в MS Access, средствами самого MS Access. Перенумеровать можно и с использованием языков программирования, например, VBA или PHP. Однако иногда это необходимо сделать в самом запросе SQL. Итак, следующий запрос пронумерует строки таблицы m_income в соответствии с порядком возрастания значений поля ID:

SELECT COUNT(*) as N, b.id, b.product_id, b.amount, b.price
FROM m_income a INNER JOIN m_income b ON a.id <= b.id
GROUP BY b.id, b.product_id, b.amount, b.price;

Запрос Q053. Выводится пятерка лидеров среди продуктов по сумме продаж. Вывод первых пяти записей осуществляется с помощью инструкции TOP:

SELECT TOP 5, product_id, sum(amount*price) AS summa
FROM m_outcome
GROUP BY product_id
ORDER BY sum(amount*price) DESC;

Запрос Q054. Выводится пятерка лидеров среди продуктов по сумме продаж, и нумерует строки в результате:

SELECT COUNT(*) AS N, b.product_id, b.summa
FROM

FROM m_outcome GROUP BY product_id) AS a
INNER JOIN
(SELECT product_id, sum(amount*price) AS summa,
summa*10000000+product_id AS id
FROM m_outcome GROUP BY product_id) AS b
ON a.id>=b.id
GROUP BY b.product_id, b.summa
HAVING COUNT(*)<=5
ORDER BY COUNT(*);

Запрос Q055. Следующий SQL-запрос показывает использование математических функций COS, SIN, TAN, SQRT, ^ и ABS в MS Access SQL:

SELECT (select count(*) from m_income) as N, 3.1415926 as pi, k,
2*pi*(k-1)/N as x, COS(x) as COS_, SIN(x) as SIN_, TAN(x) as TAN_,
SQR(x) as SQRT_, x^3 as "x^3", ABS(x) as ABS_
FROM (SELECT COUNT(*) AS k
FROM m_income AS a INNER JOIN m_income AS b ON a.id<=b.id
GROUP BY b.id) t;

Вставка, удаление, обновление записей в базе данных

Метод ExecuteReader() извлекает объект чтения данных, который позволяет просматривать результаты SQL-оператора Select с помощью потока информации, доступного только для чтения в прямом направлении. Однако если требуется выполнить операторы SQL, модифицирующие таблицу данных, то нужен вызов метода ExecuteNonQuery() данного объекта команды. Этот единый метод предназначен для выполнения вставок, изменений и удалений, в зависимости от формата текста команды.

Понятие не запросный (nonquery) означает оператор SQL, который не возвращает результирующий набор. Следовательно, операторы Select представляют собой запросы, а операторы Insert, Update и Delete - нет. Соответственно, метод ExecuteNonQuery() возвращает значение int, содержащее количество строк, на которые повлияли эти операторы, а не новое множество записей.

Чтобы показать, как модифицировать содержимое существующей базы данных с помощью только запроса ExecuteNonQuery(), следующим шагом будет создание собственной библиотеки доступа к данным, в которой инкапсулируется процесс работы с базой данных AutoLot.

В реальной производственной среде ваша логика ADO.NET почти наверняка будет изолирована в.dll-сборке.NET по одной простой причине - повторное использование кода! В предыдущих статьях это не было сделано, чтобы не отвлекать вас от решаемых задач. Но было бы лишними затратами времени разрабатывать ту же самую логику подключения, ту же самую логику чтения данных и ту же самую логику выполнения команд для каждого приложения, которому понадобится работать с базой данных AutoLot.

В результате изоляции логики доступа к данным в кодовой библиотеке.NET различные приложения с любыми пользовательскими интерфейсами (консольный, в стиле рабочего стола, в веб-стиле и т.д.) могут обращаться к существующей библиотеке даже независимо от языка. И если разработать библиотеку доступа к данным на C#, то другие программисты в.NET смогут создавать свои пользовательские интерфейсы на любом языке (например, VB или C++/CLI).

Наша библиотека доступа к данным (AutoLotDAL.dll) будет содержать единое пространство имен (AutoLotConnectedLayer), которое будет взаимодействовать с базой AutoLot с помощью подключенных типов ADO.NET.

Начните с создания нового проекта библиотеки классов (C# Class Library) по имени AutoLotDAL (сокращенно от "AutoLot Data Access Layer" - "Уровень доступа к данным AutoLot"), а затем смените первоначальное имя файла C#-кода на AutoLotConnDAL.cs.

Потом переименуйте область действия пространства имен в AutoLotConnectedLayer и измените имя первоначального класса на InventoryDAL, т.к. этот класс будет определять различные члены, предназначенные для взаимодействия с таблицей Inventory базы данных AutoLot. И, наконец, импортируйте следующие пространства имен.NET:

Using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; namespace AutoLotConnectedLayer { public class InventoryDAL { } }

Добавление логики подключения

Первая наша задача - определить методы, позволяющие вызывающему процессу подключаться к источнику данных с помощью допустимой строки подключения и отключаться от него. Поскольку в нашей сборке AutoLotDAL.dll будет жестко закодировано использование типов класса System.Data.SqlClient, определите приватную переменную SqlConnection, которая будет выделяться при создании объекта InventoryDAL.

Кроме того, определите метод OpenConnection(), а затем еще CloseConnection(), которые будут взаимодействовать с этой переменной:

Public class InventoryDAL { private SqlConnection connect = null; public void OpenConnection(string connectionString) { connect = new SqlConnection(connectionString); connect.Open(); } public void CloseConnection() { connect.Close(); } }

Для краткости тип InventoryDAL не будет проверять все возможные исключения, и не будет генерировать пользовательские исключения при возникновении различных ситуаций (например, когда строка подключения неверно сформирована). Однако при создании производственной библиотеки доступа к данным вам наверняка пришлось бы задействовать технику структурированной обработки исключений, чтобы учитывать все аномалии, которые могут возникнуть во время выполнения.

Добавление логики вставки

Вставка новой записи в таблицу Inventory сводится к форматированию SQL-оператора Insert (в зависимости от введенных пользователем данных) и вызову метода ExecuteNonQuery() с помощью объекта команды. Для этого добавьте в класс InventoryDAL общедоступный метод InsertAuto(), принимающий четыре параметра, которые соответствуют четырем столбцам таблицы Inventory (CarID, Color, Make и PetName). На основании этих аргументов сформируйте строку для добавления новой записи. И, наконец, выполните SQL-оператор с помощью объекта SqlConnection:

Public void InsertAuto(int id, string color, string make, string petName) { // Оператор SQL string sql = string.Format("Insert Into Inventory" + "(CarID, Make, Color, PetName) Values(@CarId, @Make, @Color, @PetName)"); using (SqlCommand cmd = new SqlCommand(sql, this.connect)) { // Добавить параметры cmd.Parameters.AddWithValue("@CarId", id); cmd.Parameters.AddWithValue("@Make", make); cmd.Parameters.AddWithValue("@Color", color); cmd.Parameters.AddWithValue("@PetName", petName); cmd.ExecuteNonQuery(); } }

Определение классов, представляющих записи в реляционной базе данных - распространенный способ создания библиотеки доступа к данным. Вообще-то, ADO.NET Entity Framework автоматически генерирует строго типизированные классы, которые позволяют взаимодействовать с данными базы. Кстати, автономный уровень ADO.NET генерирует строго типизированные объекты DataSet для представления данных из заданной таблицы в реляционной базе данных.

Создание оператора SQL с помощью конкатенации строк может оказаться опасным с точки зрения безопасности (вспомните атаки вставкой в SQL). Текст команды лучше создавать с помощью параметризованного запроса, который будет описан чуть позже.

Добавление логики удаления

Удаление существующей записи не сложнее вставки новой записи. В отличие от кода InsertAuto(), будет показана одна важная область try/catch, которая обрабатывает возможную ситуацию, когда выполняется попытка удаления автомобиля, уже заказанного кем-то из таблицы Customers. Добавьте в класс InventoryDAL следующий метод:

Public void DeleteCar(int id) { string sql = string.Format("Delete from Inventory where CarID = "{0}"", id); using (SqlCommand cmd = new SqlCommand(sql, this.connect)) { try { cmd.ExecuteNonQuery(); } catch (SqlException ex) { Exception error = new Exception("К сожалению, эта машина заказана!", ex); throw error; } } }

Добавление логики изменения

Когда дело доходит до обновления существующей записи в таблице Inventory, то сразу же возникает очевидный вопрос: что именно можно позволить изменять вызывающему процессу: цвет автомобиля, дружественное имя, модель или все сразу? Один из способов максимального повышения гибкости - определение метода, принимающего параметр типа string, который может содержать любой оператор SQL, но это, по меньшей мере, рискованно.

В идеале лучше иметь набор методов, которые позволяют вызывающему процессу изменять записи различными способами. Однако для нашей простой библиотеки доступа к данным мы определим единый метод, который позволяет вызывающему процессу изменить дружественное имя указанного автомобиля:

Public void UpdateCarPetName(int id, string newpetName) { string sql = string.Format("Update Inventory Set PetName = "{0}" Where CarID = "{1}"", newpetName, id); using (SqlCommand cmd = new SqlCommand(sql, this.connect)) { cmd.ExecuteNonQuery(); } }

Добавление логики выборки

Теперь необходимо добавить метод для выборки записей. Как было показано ранее, объект чтения данных конкретного поставщика данных позволяет выбирать записи с помощью курсора, допускающего только чтение в прямом направлении. Посредством вызова метода Read() можно обработать каждую запись поочередно. Все это замечательно, но теперь необходимо разобраться, как возвратить эти записи вызывающему уровню приложения.

Одним из подходов может быть получение данных с помощью метода Read() с последующим заполнением и возвратом многомерного массива (или другого объекта вроде обобщенного List).

Еще один способ - возврат объекта System.Data.DataTable, который вообще-то принадлежит автономному уровню ADO.NET. DataTable - это класс, представляющий табличный блок данных (наподобие бумажной или электронной таблицы).

Класс DataTable содержит данные в виде коллекции строк и столбцов. Эти коллекции можно заполнять программным образом, но в типе DataTable имеется метод Load(), который может автоматически заполнять их с помощью объекта чтения данных! Вот пример, где данные из таблицы Inventory возвращаются в виде DataTable:

Public DataTable GetAllInventoryAsDataTable() { DataTable inv = new DataTable(); string sql = "Select * From Inventory"; using (SqlCommand cmd = new SqlCommand(sql, this.connect)) { SqlDataReader dr = cmd.ExecuteReader(); inv.Load(dr); dr.Close(); } return inv; }

Работа с параметризованными объектами команд

Пока в логике вставки, изменения и удаления для типа InventoryDAL мы использовали жестко закодированные строковые литералы для каждого SQL-запроса. Вы, видимо, знаете о существовании параметризованных запросов, которые позволяют рассматривать параметры SQL как объекты, а не просто кусок текста.

Работа с SQL-запросами в более объектно-ориентированной манере не только помогает сократить количество опечаток (при наличии строго типизированных свойств), ведь параметризованные запросы обычно выполняются значительно быстрее запросов в виде строковых литералов, поскольку они анализируются только один раз (а не каждый раз, как это происходит, если свойству CommandText присваивается SQL-строка). Кроме того, параметризованные запросы защищают от атак внедрением в SQL (широко известная проблема безопасности доступа к данным).

Для поддержки параметризованных запросов объекты команд ADO.NET поддерживают коллекцию отдельных объектов параметров. По умолчанию эта коллекция пуста, но в нее можно занести любое количество объектов параметров, которые соответствуют параметрам-заполнителям (placeholder parameter) в SQL-запросе. Если нужно связать параметр SQL-запроса с членом коллекции параметров некоторого объекта команды, поставьте перед параметром SQL символ @ (по крайней мере, при работе с Microsoft SQL Server, хотя не все СУБД поддерживают это обозначение).

Задание параметров с помощью типа DbParameter

Прежде чем приступить к созданию параметризованных запросов, ознакомимся с типом DbParameter (базовый класс для объектов параметров поставщиков). У этого класса есть ряд свойств, которые позволяют задать имя, размер и тип параметра, а также другие характеристики, например, направление просмотра параметра. Некоторые важные свойства типа DbParameter приведены ниже:

DbType

Выдает или устанавливает тип данных из параметра, представляемый в виде типа CLR

Direction

Выдает или устанавливает вид параметра: только для ввода, только для вывода, для ввода и для вывода или параметр для возврата значения

IsNullable

Выдает или устанавливает, может ли параметр принимать пустые значения

ParameterName

Выдает или устанавливает имя DbParameter

Size

Выдает или устанавливает максимальный размер данных для параметра (полезно только для текстовых данных)

Value

Выдает или устанавливает значение параметра

Для демонстрации заполнения коллекции объектов команд совместимыми с DBParameter объектами переделаем метод InsertAuto() так, что он будет использовать объекты параметров (аналогично можно переделать и все остальные методы, но нам будет достаточно и настоящего примера):

Public void InsertAuto(int id, string color, string make, string petName) { // Оператор SQL string sql = string.Format("Insert Into Inventory" + "(CarID, Make, Color, PetName) Values("{0}","{1}","{2}","{3}")", id, make, color, petName); // Параметризованная команда using (SqlCommand cmd = new SqlCommand(sql, this.connect)) { SqlParameter param = new SqlParameter(); param.ParameterName = "@CarID"; param.Value = id; param.SqlDbType = SqlDbType.Int; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@Make"; param.Value = make; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@Color"; param.Value = color; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); param = new SqlParameter(); param.ParameterName = "@PetName"; param.Value = petName; param.SqlDbType = SqlDbType.Char; param.Size = 10; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); } }

Обратите внимание, что здесь SQL-запрос также содержит четыре символа-заполнителя, перед каждым из которых находится символ @. С помощью свойства ParameterName в типе SqlParameter можно описать каждый из этих заполнителей и задать различную информацию (значение, тип данных, размер и т.д.), причем строго типизированным образом. После подготовки всех объектов параметров они добавляются в коллекцию объекта команды с помощью вызова Add().

Для оформления объектов параметров здесь используются различные свойства. Однако учтите, что объекты параметров поддерживают ряд перегруженных конструкторов, которые позволяют задавать значения различных свойств (что дает более компактную кодовую базу). Учтите также, что в Visual Studio 2010 имеются различные графические конструкторы, которые автоматически создадут за вас большой объем этого утомительного кода работы с параметрами.

Создание параметризованного запроса часто приводит к большему объему кода, но в результате получается более удобный способ для программной настройки SQL-операторов, а также более высокая производительность. Эту технику можно применять для любых SQL-запросов, хотя параметризованные запросы наиболее удобны, если нужно запускать хранимые процедуры.

На уроке будет рассмотрен язык запросов: оператор SELECT sql — на выборку данных

SQL-запрос Select предназначен для обычной выборки из базы данных. Т.е. если нам необходимо просто получить данные, не делая с ними никакой обработки и не внося изменений в базу данных, то можно смело использовать данный запмагарос.

Синтаксис оператора SELECT

SELECT * FROM имя_таблицы;

Это самый простой вариант работы с оператором, когда мы выбираем все записи из таблицы БД.

Символ * обозначает выборку всех записей из таблицы . При этом столбцы и строки результирующего набора не упорядочены.

Рассмотрим примеры sql запросов select:

Пример : если вы создали локальную базу данных и заполнили таблицы, как в рассмотренном ранее (или же воспользовались сервисом sqlFiddle), то выполним следующий пример.
Необходимо выбрать все записи из таблицы teachers

SELECT * FROM имя_таблицы LIMIT 2,3;

В примере происходит выборка 3 записей из таблицы, начиная со 2 записи.
Этот запрос особо необходим при создании блока страниц навигации.

Чтобы упорядочить поля результирующего набора , их следует перечислить через запятую в нужном порядке после слова SELECT:

SELECT name, zarplata, premia FROM teachers ORDER BY name;

Выберет значения полей name , zarplata , premia и отсортирует по полю name (по алфавиту)

Пример: БД «Компьютерный магазин». Выбрать данные о скорости и памяти компьютеров. Требуется упорядочить результирующий набор по скорости процессора в порядке возрастания.

SELECT `Скорость`,`Память` FROM `pc` ORDER BY 1 ASC

Результат:

Сортировку можно выполнять по двум полям:

SELECT name, zarplata, premia FROM teachers ORDER BY name DESC;

Выберет значения полей name , zarplata , premia и отсортирует по полю name по убыванию

Удаление повторяющихся значений в SQL

В случае когда необходимо получить уникальные строки, можно использовать ключевое слово DISTINCT .

DISTINCT (в переводе с английского ОТЛИЧИЕ) - аргумент, который устраняет двойные значения :

Пример БД «Институт»: требуется узнать возможные варианты размера премий. Если не использовать Distinct , в результате будет выдаваться два одинаковых значения. Удалить в sql повторяющиеся значения можно при введении Distinct — в результате дублирующиеся значения не повторяются.

SELECT Скорость, Память FROM PC;

Результат:


В таблице PC первичным ключом является поле code . Поскольку это поле отсутствует в запросе, в приведенном выше результирующем наборе имеются дубликаты строк.

Когда требуется получить уникальные строки (например, нас интересуют только различные комбинации скорости процессора и объема памяти, а не характеристики всех имеющихся компьютеров), то нужно использовать Distinct:

SELECT DISTINCT Скорость, Память FROM PC;

SELECT DISTINCT Скорость, Память FROM PC;

Результат:


Задание sql select 1_1. БД «Институт» Выполните запрос на выборку id и name из таблицы учителей. Отсортируйте фамилии учителей по убыванию

Язык sql: where условие

Условие выполняется предложением
WHERE
которое записывается после предложения FROM .

При этом в результирующий набор попадут только те записи, для которых значение предиката равно TRUE (истина).

Пример БД «Институт»: Выводить данные преподавателя из таблицы teachers , фамилия которого Иванов



Несколько условий в SQL

Предикаты (условия) могут состоять как из одного выражения, так и из любой комбинации выражений, построенных с помощью булевых операторов:

AND ,

или NOT

Пример БД «Институт»: вывести код преподавателя, зарплата которого составляет 10000 , а премия 500

Реляционные операторы, встречающиеся в условиях:
= Равный
> Больше чем
>= Больше чем или равно
Не равно

Between в SQL (между)

Предикат BETWEEN проверяет, попадают ли значения проверяемого выражения в диапазон, задаваемый пограничными выражениями, соединяемыми служебным словом AND .

Синтаксис:

<Проверяемое выражение> BETWEEN <Начальное выражение> AND <Конечное выражение>

Пример БД "Институт": Вывести фамилию и зарплату преподавателя, зарплата которого между 5000 и 10000.



Пример БД "Институт": Вывести фамилию и зарплату преподавателя, зарплата которого не находится в диапазоне от 5000 до 10000.



Предикат IN

Предикат IN определяет, будет ли значение проверяемого выражения обнаружено в наборе значений, который явно определен.

Синтаксис:

< Проверяемое выражение> [ NOT ] IN (< набор значений> )

<Проверяемое выражение> IN (<набор значений>)

Пример БД "Институт": вывести имена преподавателей, зарплата которых составляет 5000 , 10000 или 11000



Пример БД "Институт": вывести имена преподавателей, зарплата которых не находится среди значений: 5000 , 10000 или 11000



Задание sql select 1_3. БД "Институт" Вывести фамилию, зарплату и премию учителей, премия которых от 2000 до 5000 рублей.