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

Метод 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 предписывает ядру СУБД Microsoft Access вернуть сведения из базы данных в виде набора записей.

Синтаксис

SELECT [предикат ] { * | таблица .* | [таблица .]поле1 [, [таблица .]поле2 [, ...]]}
FROM табличное_выражение [, ...]

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

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

Примечания.

Для выполнения данной операции ядро СУБД Microsoft Access выполняет поиск указанной таблицы или таблиц, извлекает указанные столбцы, выбирает строки, отвечающие условию и сортирует полученные строки в указанном порядке.

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

SELECT обычно является первым словом в инструкции SQL. SELECT и SELECT...INTO - наиболее распространенные инструкции SQL.

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

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

Чтобы выбрать все поля в таблице, можно использовать звездочку (*). В приведенном ниже примере показано, что в таблице "Сотрудники" выбраны все поля.

SELECT * FROM Employees;

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

SELECT Employees.Department, Supervisors.SupvName FROM Employees INNER JOIN Supervisors WHERE Employees.Department = Supervisors.Department;

При создании объекта RecordSet имя поля таблицы используется ядром СУБД Microsoft Access в качестве имени объекта "Поле" в объекте Recordset . Если имя поля нуждается в изменении или не предоставляется выражением, с помощью которого генерируется поле, используйте зарезервированное слово AS. В следующем примере показано, как заголовок "Дата_рождения" используется для присвоения имени возвращенному объекту Поле в полученном объекте Recordset .

SELECT BirthDate AS Birth FROM Employees;

При использовании агрегатных функций или запросов, которые возвращают неоднозначные или повторяющиеся имена объекта Поле , необходимо использовать предложение AS, чтобы присвоить объекту Поле другое имя. В приведенном ниже примере возвращенному объекту Поле в полученном объекте Recordset присваивается имя "Количество_сотрудников".

SELECT COUNT(EmployeeID) AS HeadCount FROM Employees;

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

Данный урок посвящен SQL запросам к базе данных на VBA Access . Мы рассмотрим, как на VBA осуществляется запросы INSERT, UPDATE, DELETE к базе данных, а также научимся получать конкретное значение из запроса SELECT.

Те, кто программируют на VBA Access и работая при этом с базой данных SQL сервера, очень часто сталкиваются с такой простой и нужной задачей как посыл SQL запроса к базе данных, будь то INSERT, UPDATE или простой SQL запрос SELECT . А так как мы начинающие программисты мы тоже должны уметь это делать, поэтому сегодня займемся именно этим.

Мы уже затрагивали тему получения данных с SQL сервера, где как раз на VBA писали код для получения этих данных, например в статье про Выгрузку данных в текстовый файл из MSSql 2008 или также немного затрагивали в материале Выгрузка данных из Access в шаблон Word и Excel , но так или иначе там мы рассматривали это поверхностно, а сегодня предлагаю поговорить об этом чуть более подробней.

Примечание! Все примеры ниже рассмотрены с использованием ADP проекта Access 2003 и базы данных MSSql 2008. Если Вы не знаете что вообще такое ADP проект то это мы рассматривали в материале Как создать и настроить ADP проект Access

Исходные данные для примеров

Допустим, у нас есть таблица test_table, которая будет содержать номера и названия месяцев в году (запросы выполнены с использованием Management Studio )

CREATE TABLE .( NOT NULL, (50) NULL) ON GO

Как я уже сказал, мы будем использовать ADP проект, настроенный на работу с MS SQL 2008, в котором я создал тестовую форму и добавил кнопку start с подписью «Выполнить» , которая нам понадобится для тестирования нашего кода, т.е. весь код мы будем писать в обработчике события «Нажатие кнопки ».

Запросы к базе INSERT, UPDATE, DELETE на VBA

Чтобы долго не тянуть сразу приступим, допустим, нам нужно добавить строку в нашу тестовую таблицу (код прокомментирован )/

Private Sub start_Click() "Объявляем переменную для хранения строки запроса Dim sql_query As String "Записываем в нее нужный нам запрос sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "Июнь")" "Выполняем его DoCmd.RunSQL sql_query End Sub

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

Как видим, данные вставились.

Для того чтобы удалить одну строку пишем вот такой код.

Private Sub start_Click() "Объявляем переменную для хранения строки запроса Dim sql_query As String "Записываем в нее запрос на удаление sql_query = "DELETE test_table WHERE id = 6" "Выполняем его DoCmd.RunSQL sql_query End Sub

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

Для обновления данных записываем в переменную sql_query запрос update, надеюсь, смысл понятен.

Запрос SELECT к базе на VBA

Здесь дела обстоят чуть интересней, чем с остальными конструкциями SQL.

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

Private Sub start_Click() "Объявляем переменные "Для набора записей из базы Dim RS As ADODB.Recordset "Строка запроса Dim sql_query As String "Строка для вывода итоговых данных в сообщении Dim str As String "Создаем новый объект для записей set RS = New ADODB.Recordset "Строка запроса sql_query = "SELECT id, name_mon FROM test_table" "Выполняем запрос с использованием текущих настроек подключения проекта RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Циклом перебираем записи While Not (RS.EOF) "Заполняем переменную для вывода сообщения str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "переход к следующей записи RS.MoveNext Wend "Вывод сообщения msgbox str End Sub

Здесь мы уже используем циклы VBA Access для того чтобы перебрать все значения в нашем наборе записей.

Но, достаточно часто бывает необходимо получить не все значения из набора записей, а всего лишь одно, например, название месяца по его коду. И для этого использовать цикл как-то накладно, поэтому мы можем просто написать запрос, который вернет всего одно значение и обращаться именно к нему, например, получим название месяца по коду 5

Private Sub start_Click() "Объявляем переменные "Для набора записей из базы Dim RS As ADODB.Recordset "Строка запроса Dim sql_query As String "Строка для вывода итогового значения Dim str As String "Создаем новый объект для записей set RS = New ADODB.Recordset "Строка запроса sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Выполняем запрос с использованием текущих настроек подключения проекта RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Получаем наше значение str = RS.Fields(0) msgbox str End Sub

Для универсальности здесь мы уже обратились не по имени ячейки, а по ее индексу, т.е. 0, а это самое первое значение в Recordset , в итоге мы получили значение «Май» .

Как видите, все достаточно просто. Если Вам достаточно часто требуется получать конкретное значение из базы (как в последнем примере ), то рекомендую вывести весь код в отдельную функцию (Как написать функцию на VBA Access 2003) с одним входящим параметром, например, код месяца (если рассматривать наш пример ) и просто, где необходимо вывести это значение, вызывать нужную нам функцию с нужным параметром и все, этим мы значительно уменьшим код VBA и улучшим восприятие нашей программы.

На сегодня это все. Удачи!

Примеры запросов SQL могут использованы для изучения и практикума написания запросов SQL в MS Access.

Один запрос 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 процентов от суммы продаж:

amount*price/100*7 AS profit
FROM m_outcome;

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

SELECT dt, product_id, amount, price, amount*price AS outcome_sum,
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.

Правил квадратные скобки означают [необязательную часть] конструкции. Вертикальная черта обозначает выбор между вариантами {вар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 (по умолчанию).) и содержать данные одного вида. Однако имена этих полей могут быть разными
оператор_сравнения	Любой оператор сравнения: (=, <, >, <=, >= или <>)