Объявление WITH OWNERACCESS OPTION

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

sqlstatement WITH OWNERACCESS OPTION

Следующий запрос позволяет посмотреть Salary даже пользователю, не имеющему доступа к таблице Employees, т.к. работает с правами владельца запроса:

SELECT LastName, FirstName, Salary FROM Employees ORDER BY LastName
WITH OWNERACCESS OPTION;

Часто используется для ограничения доступа к полям: пользователь не имеет доступа к таблице (в отличие от владельца запроса), но может посмотреть/изменить указанные в запросе поля. Если пользователь не имеет права создавать или менять таблицы, запрос с WITH OWNERACCESS OPTION позволит ему это сделать. Использование этого объявления равносильно установке свойства RunPermissions = Owner в окне свойств конструктора запросов, а отсутствие соответствует RunPermissions = User.

Объявление WITH OWNERACCESS OPTION требует доступа к файлу рабочих групп (System.mdw)

Выражение WHERE

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

SELECT fieldlist FROM tableexpression WHERE criteria

Оператор SELECT, содержащий выражение WHERE, имеет следующие части:

WHERE не обязательно, но если указано, следует за FROM. Microsoft Jet отбирает записи, удовлетворяющие условию в выражении WHERE. Например, можно выбрать всех сотрудников отдела продаж (WHERE Dept = 'Sales') или всех клиентов возрастом от 18 до 30 (WHERE Age Between 18 And 30). Если оно не указано, выбираются все записи. Если указано несколько таблиц и нет выражения WHERE или JOIN, формируется декартово произведение таблиц.

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

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

В выражении WHERE можно использовать до 40 логических выражений, объединенных операторами And или Or. Имена полей, содержащие пробелы или знаки пунктуации, заключаются в прямые скобки: SELECT [Customer's Favorite Restaurant]. Дата должна быть указана в американском формате – месяц/число/год, например 5/10/96 – 10 мая 1996 – и заключена в #:

SELECT * FROM Orders WHERE ShippedDate = #5/10/96#;

Можно использовать функцию DateValue, учитывающую региональные настройки:

SELECT * FROM Orders WHERE ShippedDate = DateValue('5/10/96');

– United States

SELECT * FROM Orders WHERE ShippedDate = DateValue('10/5/96');

– United Kingdom.

Для полей типа GUID используется следующий формат:

WHERE ReplicaID = {GUID {12345678-90AB-CDEF-1234-567890ABCDEF}}

Условия в выражении WHERE те же, что используются в конструкторе запросов.

Выбор всех служащих по фамилии King:

SELECT LastName, FirstName FROM Employees WHERE LastName = 'King';

Выбор всех служащих с фамилией, начинающейся на S:

SELECT LastName, FirstName FROM Employees WHERE LastName Like 'S*';

Выбор товаров с ценой от $20 до $50 включительно:

SELECT ProductName, UnitPrice FROM Products
WHERE (UnitPrice >=20.00 And UnitPrice <= 50.00);

Выбор товаров с названиями между "Cha" и "Out" включительно (не возвращает "Outback Lager" т.к. "Outback Lager" «больше» "Out"):

SELECT ProductName, UnitPrice FROM Products
WHERE ProductName Between 'Cha' And 'Out';

Выбор заказов за перыую половину 1995 года:

SELECT OrderID, OrderDate FROM Orders
WHERE OrderDate Between #1-1-95# And #6-30-95#;

Выбор заказов, доставленных в Idaho, Oregon и Washington:

SELECT OrderID, ShipRegion FROM Orders WHERE ShipRegion In ('ID', 'OR', 'WA');

Операция Like

Сравнивает строковое выражение с образцом. Формат

Expression Like "pattern"

Операция Like производит сравнение выражения с шаблоном и возвращает значение True или False. В параметрическом запросе pattern можно запрашивать как параметр. Шаблон может быть задан полностью (Like "Smith") или содержать символы подстановки(Like "Sm*"). Например, чтобы найти все значения поля, начинающиеся на C, нужно указать Like "C*", а Like "P[A-F]###" позволит найти значения, начинающиеся с P, закоторой следует буква от A до F и три цифры.

В следующей таблице приведены примеры использования специальных символов в шаблонах:

В Microsoft Access Like можно использовать в выражении запроса и в «вычисляемом» управляюшем элементе. В параметрическом запросе в Like можно использовать параметр.В конструкторе запросов вводится в ячейку Criteria, например, Like "C*".

Пусть таблица Employees содержит поле LastName. Создадим к ней новый запрос, «перетащим» поле LastName в ячейку конструктора, в ячейку Criteria введем

Like [Enter first few letters of name:]&"*"

Если запустить запрос на выполнение, появится окно диалога "Enter first few letters of name:". Если пользователь введет Sm, то запрос будет искать шаблон Sm*.

Операцию можно использовать в свойстве ValidationRule – Like "P[A-F]###" ограничит ввод величинами, соответствующими критерию.

В следующем примере выводится список служащих с именами, начинающимися на буквы от A до D:

SELECT * FROM Employees WHERE LastName Like '[A-D]*';

Операция In

Определяет, совпадает ли значение выражения с одним из перечисленных в списке. Формат

expr [Not] In(value1, value2, . . .)

Если значение найдено в списке, In возвращает True, иначе False. Оператор Not меняет результат на противоположный (нет в списке). В Microsoft Access In можно использовать в выражении запроса и в «вычисляемом» управляюшем элементе.

Заказы, доставленные в указанные города:

SELECT * FROM Orders WHERE ShipRegion In ('Avon','Glos','Som')

Пусть таблица Orders содержит поля ShipCountry и OrderID и нужно создать запрос на заказы для United States, Canada и United Kingdom. Создадим к ней новый запрос, «перетащим» поля ShipCountry и OrderID в ячейки конструктора, в ячейку Criteria для ShipCountry введем In('USA', 'Canada', 'UK'). То же результат получится, если ввести "USA" Or "Canada" Or "UK". При большом списке можно комбинировать In и Or.

В «вычисляемом» управляюшем элементе можно использовать In в функции IIf. Например, =IIf([ShipRegion] In ('WA','OR','ID'), "Local", "Nonlocal") воозвращает Local для WA, OR и ID, и Nonlocal для других значений.

Следующий пример выбирает из таблицы Orders базы Northwind.mdb все заказы для Lancashire и Essex:

SELECT * FROM Orders WHERE ShipCity In ('Colchester','Hedge End','London');

Операция Between...And

Определяет, попадает ли значение в указанный интервал. Формат

expr [Not] Between value1 And value2

Эквивалентно

(expr Between value1 And value2) ~ (expr >= value1) And (expr <= value2)

(expr Not Between value1 And value2) ~ (expr < value1) Or (expr > value2)

Например,

SELECT IIf(PostalCode Between 98101 And 98199, "Local", "Nonlocal") FROM Publishers

Between...And возвращает True или False, если одно из значений expr, value1 или value2 – Null, то Between...And также возвращает Null. Символ * в выражении рассматривается как литерал, а не символ подстановки.

Пример:

Sub SubQueryX()

Dim dbs As Database, rst As Recordset

' Modify this line to include the path to Northwind on your computer.

Set dbs = OpenDatabase("Northwind.mdb")

' List the name and contact of every customer who placed an order

' in the second quarter of 1995.

Set rst = dbs.OpenRecordset("SELECT ContactName,CompanyName,ContactTitle,Phone" _
& " FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders" _
& " WHERE OrderDate Between #04/1/95# And #07/1/95#);")

rst.MoveLast ' Populate the Recordset.

' Call EnumFields to print the contents of the Recordset.

' Pass the Recordset object and desired field width.

EnumFields rst, 25

dbs.Close

End Sub

Подзапрос

Подзапрос – оператор SELECT, вложенный в другой оператор SELECT, SELECT...INTO, INSERT...INTO, DELETE или UPDATE, или другой подзапрос. Возможны три формы использования подзапроса

comparison [ANY | ALL | SOME] (sqlstatement)

expression [NOT] IN (sqlstatement)

[NOT] EXISTS (sqlstatement)

Подзапрос имеет следующие части:

Подзапрос можно использовать вместо списка значений в выражениях WHERE или HAVING. Предикаты ANY и SOME (синонимы) используются для выбора в основном запросе записей, удовлетворяющих критерию сравнения с хотя бы одной записью подзапроса. Например, следующий запрос выбирает все товары, чьи цены выше цены какого-либо товара, продаваемого со скидкой более 25%:

SELECT * FROM Products WHERE UnitPrice > ANY (SELECT UnitPrice FROM OrderDetails
WHERE Discount >= .25);

Предикат ALL используется для выбора в основном запросе записей, удовлетворяющих критерию сравнения со всеми записями подзапроса. Если в предыдущем запросе заменть ANY на ALL, он вернет список товаров, дороже самого дорогого товара, продаваемого со скидкой более 25%, т.е. список более ограничен.

Предикат IN используется для выбора в основном запросе записей, соответствующих какой-либо записи в подзапросе. Например, следующий запрос вернет записи о всех товарах, скидка на которые больше 25%

SELECT * FROM Products WHERE ProductID IN (SELECT ProductID FROM OrderDetails
WHERE Discount >= .25);

Соответственно, NOT IN используется для выбора в основном запросе записей, которые не соответствуют записям подзапроса. Так, в предыдущем примере были бы выбраны товаря со скидкой 25% и меньше.

Предикат EXISTS используется для праверки, вернул ли подзапрос какие-либо записи, и соответственно возвращает true/false.

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

SELECT LastName, FirstName, Title, Salary FROM Employees AS T1

WHERE Salary >= (SELECT Avg(Salary) FROM Employees
WHERE T1.Title = Employees.Title) Order by Title;

В этом примере служебное слово AS не обязательно.

Подзапросы допустимы в перекрестных запросах как предикаты в выражениях WHERE, но не допустимы в качестве вывода (список оператора SELECT).

В следующем примере выводится имя и адрес всех заказчиков, сделавших заказ во втором квартале 1995(вызывается процедура EnumFields из примера к оператору SELECT):

Sub SubQueryX()

Dim dbs As Database, rst As Recordset

' Modify this line to include the path to Northwind on your computer.

Set dbs = OpenDatabase("Northwind.mdb")

' List the name and contact of every customer

' who placed an order in the second quarter of 1995.

Set rst = dbs.OpenRecordset("SELECT ContactName, CompanyName," _
& " ContactTitle, Phone FROM Customers WHERE CustomerID" _
& " IN (SELECT CustomerID FROM Orders" _
& " WHERE OrderDate Between #04/1/95# And #07/1/95#);")

rst.MoveLast ' Populate the Recordset.

' Call EnumFields to print the contents of the Recordset.

' Pass the Recordset object and desired field width.

EnumFields rst, 25

dbs.Close

End Sub

Все заказы без скидок, чьи объемы больше среднего:

SELECT OrderID, (UnitPrice * Quantity) As OrderTotal
FROM [Order Details] WHERE Discount = 0 AND
(UnitPrice * Quantity) > ALL(SELECT Avg(UnitPrice * Quantity)
FROM [Order Details]);

Названия и цены всех товаров, чья цена совпадает с ценой Aniseed Syrup:

SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice =

(SELECT UnitPrice FROM [Products]
WHERE ProductName = 'Aniseed Syrup');

Названия и адреса всех заказчиков, сделавших заказ во втором квартале 1995:

SELECT ContactName, CompanyName, ContactTitle, Phone FROM Customers

WHERE CustomerID IN (SELECT CustomerID FROM Orders
WHERE OrderDate BETWEEN #04/1/95# AND #06/30/95#);

Имена всех служащих, оформивших хотябы один заказ (можно было бы сделать с INNER JOIN):

SELECT FirstName, LastName FROM Employees WHERE EXISTS

(SELECT OrderID FROM Orders
WHERE Orders.EmployeeID = Employees.EmployeeID);

Выражение ORDER BY

Сортирует результат запроса по указанному полю/полям в порядке возрастания (по умолчанию) или убывания. Формат

SELECT fieldlist FROM table WHERE selectcriteria [ORDER BY field1 [ASC | DESC ][, field2 [ASC | DESC ]][, ...]]]

Оператор SELECT, содержащий выражение ORDER BY, имеет следующие части:

Выражение ORDER BY не обязательно, но если нужно упорядочить результат – необходимо. ORDER BY обычно последний элемент оператора SQL.По умолчанию результат сортируется по возрастанию (от A до Z, от 0 до 9). Следующие два оператора сортируют фамилии по возрастанию:

SELECT LastName, FirstName FROM Employees ORDER BY LastName;

SELECT LastName, FirstName FROM Employees ORDER BY LastName ASC;

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

SELECT LastName, Salary FROM Employees ORDER BY Salary DESC, LastName;

Если в выражении указаны Memo поле или OLE объект, происходит ошибка – Microsoft Jet не сортирует поля этих типов. Если в выражении указано несколько полей, то сначала результат сортируется по первому полю, внутри одинаковых значений первого поля – по второму полю, и т.д.

В Microsoft Access выражение эквивалентно установке значения в ячейке Sort конструктора запросов.

Для выполнения следующего примера нужно создать новый запрос в базе Northwind и ввести в режиме SQL

SELECT LastName, FirstName FROM Employees ORDER BY LastName DESC;

– результат тот же, что и в процедуре. Запрос

SELECT CategoryID, ProductName, UnitPrice FROM Products
ORDER BY CategoryID, ProductName;

Сортирует сперва по CategoryID, а затем по ProductName.

Выражение GROUP BY

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

SELECT fieldlist FROM table WHERE criteria [GROUP BY groupfieldlist]

Оператор SELECT, содержащий выражение GROUP BY, имеет следующие части:

Выражение GROUP BY не обязательно, итоговые величины не выводятся, если SELECT не содержит агрегатрных функций. Значения Null участвуют в группировке, но не обрабатываются агрегатными функциями. Для фильтрации исходных записей используется выражение WHERE, HAVING фильтрует сгруппированные записи.

В список полей GROUP BY можно включать любые поля из любых таблиц, перечисленных в выражении FROM, даже не включенные в список , не Memo и не OLE-поля. Все поля в списке SELECT должны быть либо в списке GROUP BY, либо быть аргументами агрегатных функций.

Средняя цена всех товаров каждого поставщика:

SELECT SupplierID, Avg(UnitPrice) AS AvgOfUnitPrice FROM Products GROUP BY SupplierID;

Максимальная цена товаров по каждой категории:

SELECT CategoryID, Max(UnitPrice) AS MaxOfUnitPrice FROM Products GROUP BY CategoryID;

Число заказов для каждого служащего:

SELECT EmployeeID, Count(OrderID) AS CountOfOrderID FROM Orders GROUP BY EmployeeID;

Выражение HAVING

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

SELECT fieldlist FROM table WHERE selectcriteria GROUP BY groupfieldlist
[HAVING groupcriteria]

Оператор SELECT, содержащий выражение HAVING, имеет следующие части:

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

SELECT CategoryID, Sum(UnitsInStock) FROM Products GROUP BY CategoryID HAVING Sum(UnitsInStock) > 100 And Like "BOS*";

Может содержать до 40 логических выражений, объединенных операторами And или Or.

Поставщики со средней ценой товара более $25:

SELECT SupplierID, Avg(UnitPrice) AS AvgOfUnitPrice FROM Products GROUP BY SupplierID HAVING (Avg(UnitPrice)>25);

Сотрудники, продавшие более 100 заказов:

SELECT EmployeeID, Count(OrderID) AS CountOfOrderID FROM Orders GROUP BY EmployeeID HAVING Count(OrderID) > 100;

Оператор UNION

Объединяет результаты нескольких независимых запросов или таблиц («удлиняет» таблицу). Формат

[TABLE] query1 UNION [ALL] [TABLE] query2 [UNION [ALL] [TABLE] queryn [ ... ]]

query1 ... queryn – операторы SELECT, имена сохраненных запросов или имена таблиц (перед именами таблиц должно стоять TABLE). Можно объединять в любой комбинации. Все запросы в объединении должны возвращать одинаковое число полей, хотя поля могут различаться типами и размерами. Например:

TABLE [New Accounts] UNION ALL SELECT * FROM Customers
WHERE OrderAmount > 1000;

– объединяет существующую таблицу New Accounts и результаты запроса (SELECT).

По умолчанию UNION не возвращает дубликаты записей, однако их можно включить в результат с помощью предиката ALL,который, кстати, увеличивает скорость запроса.

· Псевдонимы можно использовать только в первом операторе SELECT, в остальных они игнорируются.

· В выражении ORDER BY нужно ссылаться на имена полей первого оператора SELECT.

· Выражения GROUP BY и HAVING могут быть в любом из участвующих запросов.

· Выражение ORDER BY в конце последнего запроса сортирует все объединение.

В конструкторе запросов UNION можно увидель только в SQL-режиме.

В следующем примере выводятся названи фирм и городов всех поставщиков и клиентов из Бразилии

SELECT CompanyName, City FROM Suppliers WHERE Country = 'Brazil' UNION SELECT CompanyName, City FROM Customers WHERE Country = 'Brazil';

То же с сортировкой по городам и поставщикам (3-е поле):

SELECT CompanyName, City, 'Supplier' AS Source FROM Suppliers WHERE Country = 'Brazil' UNION SELECT CompanyName, City, 'Customer' FROM Customers WHERE Country = 'Brazil' ORDER BY City, Source;

Названия и коды всех поставщиков и клиентов (предполагается одинаковых порядок полей в таблицах):

TABLE Customers UNION TABLE Suppliers;

Оператор TRANSFORM

Создает перекрестный запрос. Формат

TRANSFORM aggfunction selectstatement PIVOT pivotfield [IN (value1[, value2[, ...]])]

Оператор TRANSFORM имеет следующие части:

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

Значения, возвращаемые в pivotfield, используются как заголовки столбцов в результирующем запросе. Например, использование названия месяца в качестве pivotfield приведет к запросу с двенадцатью столбцами. Можно ограничить число столбцов, создав заголовки из набора констант, перечисленных в выражении IN (значения, не входящие в набор, будут отфильтрованы).

В следующем примере используется для создания перекрестного запроса поквартального числа заказов для каждого служащего за 1994 год (используется функция SQLTRANSFORMOutput):

Sub TransformX1()

Dim dbs As Database

Dim strSQL As String

Dim qdfTRANSFORM As QueryDef

strSQL = "PARAMETERS prmYear SHORT; TRANSFORM Count(OrderID) " _
& "SELECT FirstName & "" "" & LastName AS FullName " _
& "FROM Employees INNER JOIN Orders ON " _
& "Employees.EmployeeID = Orders.EmployeeID " _
& "WHERE DatePart(""yyyy"", OrderDate) = [prmYear] "

strSQL = strSQL & "GROUP BY FirstName & "" "" & LastName " _
& "ORDER BY FirstName & "" "" & LastName " _
& "PIVOT DatePart(""q"", OrderDate)"

' Modify this line to include the path to Northwind on your computer.

Set dbs = OpenDatabase("Northwind.mdb")

Set qdfTRANSFORM = dbs.CreateQueryDef("", strSQL)

SQLTRANSFORMOutput qdfTRANSFORM, 1994

dbs.Close

End Sub

Несколько усложненный вариант – стоимость заказов:

Sub TransformX2()

Dim dbs As Database

Dim strSQL As String

Dim qdfTRANSFORM As QueryDef

strSQL = "PARAMETERS prmYear SHORT; TRANSFORM Sum(Subtotal) " _
& "SELECT FirstName & "" "" & LastName AS FullName " _
& "FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] " _
& "ON Orders.OrderID = [Order Subtotals].OrderID) " _
& "ON Employees.EmployeeID = Orders.EmployeeID WHERE DatePart" _
& "(""yyyy"", OrderDate) = [prmYear] "

strSQL = strSQL & "GROUP BY FirstName & "" "" & LastName " _
& "ORDER BY FirstName & "" "" & LastName " _
& "PIVOT DatePart(""q"",OrderDate)"

' Modify this line to include the path to Northwind on your computer.

Set dbs = OpenDatabase("Northwind.mdb")

Set qdfTRANSFORM = dbs.CreateQueryDef("", strSQL)

SQLTRANSFORMOutput qdfTRANSFORM, 1994

dbs.Close

End Sub

Function SQLTRANSFORMOutput(qdfTemp As QueryDef, intYear As Integer)

Dim rstTRANSFORM As Recordset

Dim fldLoop As Field

Dim booFirst As Boolean

qdfTemp.PARAMETERS!prmYear = intYear

Set rstTRANSFORM = qdfTemp.OpenRecordset()

Debug.Print qdfTemp.SQL

Debug.Print

Debug.Print , , "Quarter"

With rstTRANSFORM

booFirst = True

For Each fldLoop In .Fields

If booFirst = True Then
Debug.Print fldLoop.Name
Debug.Print , ;
booFirst = False

Else
Debug.Print , fldLoop.Name;

End If

Next fldLoop

Debug.Print

Do While Not .EOF

booFirst = True

For Each fldLoop In .Fields

If booFirst = True Then
Debug.Print fldLoop
Debug.Print , ;
booFirst = False

Else
Debug.Print , fldLoop;

End If

Next fldLoop

Debug.Print

.MoveNext

Loop

End With

End Function

Следующие примеры можно ввести в SQL-окне конструктора запросов:

1. Помесячный объем продаж. Месяцы – названия столбцов, товары – названия строк

PARAMETERS [Sales for which year?] LONG;

TRANSFORM Sum([Order Details].Quantity *
([Order Details].UnitPrice - ([Order Details].Discount / 100) *
[Order Details].UnitPrice)) AS Sales

SELECT ProductName FROM Orders INNER JOIN
(Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID)
ON Orders.OrderID = [Order Details].OrderID
WHERE DatePart("yyyy", OrderDate) = [Sales for which year?]
GROUP BY ProductName ORDER BY ProductName

PIVOT DatePart("m", OrderDate);

2. Поквартальный объем продаж. Кварталы – названия столбцов, поставщики – названия строк

PARAMETERS [Sales for which year?] LONG;

TRANSFORM Sum([Order Details].Quantity *
([Order Details].UnitPrice - ([Order Details].Discount / 100) *
[Order Details].UnitPrice)) AS Sales

SELECT CompanyName FROM Orders
INNER JOIN ((Suppliers INNER JOIN Products
ON Suppliers.SupplierID = Products.SupplierID)
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID)
ON Orders.OrderID = [Order Details].OrderID
WHERE DatePart("yyyy", OrderDate) = [Sales for which year?]
GROUP BY CompanyName ORDER BY CompanyName

PIVOT "Qtr " & DatePart("q", OrderDate)
In ('Qtr 1', 'Qtr 2', 'Qtr 3', 'Qtr 4');

Выражение PROCEDURE

Определяет имя и, при необходимости, параметры запроса. Формат:

PROCEDURE name [param1 datatype[, param2 datatype[, ...]]

Оператор PROCEDURE имеет следующие части:

Процудура SQL состоит из выражения PROCEDURE, определяющего имя процедуры, не обязательного списка определения параметров и одного оператора SQL. Если выражение включает несколько определений полей (пары param-datatype), они разделяются запятыми. За выражением должен следовать оператор SQL (SELECT, UPDATE и т.п.).

Введенное в SQL-режиме конструктора запросов выражение PROCEDURE, удаляется при переключении в другой режим, при этом результат запроса не меняется. Если были определены параметры, то выражение PROCEDURE заменяется выражением PARAMETERS.

В следующем примере вводится имя запроса CategoryList (используется функция SQLTRANSFORMOutput):

Sub ProcedureX()

Dim dbs As Database, rst As Recordset

Dim qdf As QueryDef, strSql As String

' Modify this line to include the path to Northwind on your computer.

Set dbs = OpenDatabase("Northwind.mdb")

strSql = "PROCEDURE CategoryList; " _
& "SELECT DISTINCTROW CategoryName, " _
& "CategoryID FROM Categories ORDER BY CategoryName;"

' Create a named QueryDef based on the SQL statement.

Set qdf = dbs.CreateQueryDef("NewQry", strSql)

' Create a temporary snapshot-type Recordset.

Set rst = qdf.OpenRecordset(dbOpenSnapshot)

rst.MoveLast ' Populate the Recordset.

' Call EnumFields to print the contents of the Recordset.

' Pass the Recordset object and desired field width.

EnumFields rst, 15

' Delete the QueryDef because this is a demonstration.

dbs.QueryDefs.Delete "NewQry"

dbs.Close

End Sub

Объявление PARAMETERS

Объявляет имя и тип параметра в параметрическом запросе. Формат

PARAMETERS name datatype [, name datatype [, ...]]

Объявление PARAMETERS имеет следующие части:

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

PARAMETERS [Low price] Currency, [Beginning date] DateTime;

Объявленные имена можно использовать в выражениях WHERE и HAVING:

PARAMETERS [Low price] Currency, [Beginning date] DateTime;

SELECT OrderID, OrderAmount FROM Orders WHERE OrderAmount > [Low price] AND OrderDate >= [Beginning date];

Параметры запрашиваются при каждом выполнении запроса, что позволяет пользователю ввести нужные значения. Объявление PARAMETERS эквивалентно определению параметров в ячейках Criteria конструктора запросов.

Для выполнения следующего примера нужно создать новый запрос в базе Northwind и ввести в режиме SQL

PARAMETERS [Enter a Last Name:] Text;

SELECT * FROM Employees WHERE LastName = [Enter a Last Name:];

Запрос будет запрашивать у пользователя Last Name. Аналогично, запрос

PARAMETERS [Enter a Category ID:] Value;

SELECT CategoryID, ProductName, Count([Order Details].OrderID) AS Tally FROM Products
INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
GROUP BY CategoryID, ProductName HAVING CategoryID = [Enter a Category ID:];

будет запрашивать Category ID.

Tabelle 1 Типы данных

Изменение данных

Управление данными сводится к трем основным операциям: ввод изменение и удаление. При этом структура таблиц остается неизменной.

Оператор UPDATE

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

UPDATE table SET newvalue WHERE criteria;

Оператор UPDATE имеет следующие части:

UPDATE особенно полезен при изменении большого числа записей или записей в нескольких таблицах. Позволяет изменять сразу несколько полей:

UPDATE Orders SET OrderAmount = OrderAmount * 1.1,
Freight = Freight * 1.03 WHERE ShipCountry = 'UK';

– увеличивает в таблице Order все значения Amount на 10% и Freight на 3% для заказов, доставляемых в United Kingdom.

· Операцию UPDATE нельзя отменить; если нужно предварительно узнать, какие записи будут изменены, используется оператор SELECT с тем же критерием отбора

· Полезно иметь страховочную копию – при неправильном изменении можно восстановить старые значения.

Поскольку UPDATE не формирует результат как набор записей, в Microsoft Access нельзя непосредственно вывести результат оператора. Если нужно подтверждать каждое изменение, можно использовать команду Replace меню Edit в форме.

Следующие запросы меняют таблицы в базе Northwind (если изменения нежелательны, нужно предварительно сделать копию, отменить нельзя)

UPDATE Employees SET ReportsTo = 5 WHERE ReportsTo = 2;

– полю ReportsTo присваивается значение 5 во всех записях таблицы Employees, в которых ReportsTo было равно 2

UPDATE Products SET UnitPrice = UnitPrice * 1.1
WHERE SupplierID = 8 AND Discontinued = No;

– цена UnitPrice в таблице Products увеличивается на 10% для всех товаров поставщика №8, у которых нет скидки.

UPDATE Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
SET UnitPrice = UnitPrice * .95
WHERE CompanyName = 'Tokyo Traders' AND Discontinued = No;

– цена UnitPrice уменьшается на 5% для всех товаров без скидки, поставляемых Tokyo Traders. Таблицы Products и Suppliers имеют отношение n:1.

Оператор INSERT INTO

Добавляет одну или несколько записей в таблицу. Формат добавления нескольких записей:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...] FROM tableexpression

Формат добавления одной записи:

INSERT INTO target [(field1[, field2[, ...]])] VALUES (value1[, value2[, ...])

Оператор INSERT INTO имеет следующие части:

Для добавления одной записи в таблицу в указывается имя и значение каждого поля. Если какое-либо поле не указано, ему присваивается значение Null, запись добавляется в конец таблицы. Если список полей не указан, значения в выражении VALUES должны ссответствовать каждому полю таблицы, иначе произойдет ошибка. В окне конструктора Microsoft Access преобразует выражение VALUES в SELECT. Рузультат тот же.

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

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

Для создания новой таблицы вместо запроса на создание лучше использовать SELECT...INTO. INSERT INTO не влияет на таблицы источники. Использование оператора INSERT INTO эквивалентно установке свойства DestinationTable в окне свойств запроса на добавление в конструкторе запросов.

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

В следующем примере запрос добавляет все записи из таблицы NewCustomers в Customers (NewCustomers нужно сделать как копию Customers):

INSERT INTO Customers SELECT * FROM NewCustomers;

Добавление записи в таблицу Employees

INSERT INTO Employees (FirstName,LastName, Title)
VALUES ('Harry', 'Washington', 'Trainee');

Следующий запрос вводит в таблицу Employees всех практикантов (Trainees), проработавших более 30 дней:

INSERT INTO Employees SELECT Trainees.* FROM Trainees WHERE HireDate < Now() - 30;

Оператор INSERT INTO можно использовать для создания резервной копии перед внесением изменений. Для следующего примера нужно скопировать таблицу Employees в EmployeeHistory (только структуру), создать запрос

INSERT INTO EmployeesHistory (FirstName, LastName, Title)
VALUES (Forms!Employees!FirstName, Forms!Employees!Lastname, Forms!Employees!Title);

сохранив его под именем BackUpQuery, затем открыть форму Employees в режиме редактирования ввести процедуру обработки события BeforeUpdate:

DoCmd.OpenQuery "BackUpQuery"

Теперь таблица будет копироваться перед каждым изменением.

Оператор DELETE

Удаляет записи из таблицы/таблиц, указанных в выражении FROM, удовлетворяющие условию WHERE. Формат:

DELETE [table.*] FROM table WHERE criteria

Оператор DELETE имеет следующие части:

Для удаления таблицы используется оператор DROP, но при этом удаляется и ее структура. DELETE удаляет только данные. Запись удаляется целиком, если нужно удалить только значение поля/полей, используется запрос на обновление и в поле заносится Null. Оператор DELETE особенно полезен, если нужно удалить сразу много записей. Оператор DELETE не создает набора записей. Чтобы узнать, какие записи удаляются, нужно предварительно сделать запрос на выборку с тем же criteria. Операцию нельзя отменить, неправильно удаленные записи можно восстановить только из резервной копии.

Если удаляется запись из таблицы, входяшей в отношение 1:n со стороны 1и разрешено каскадное удаление, то записи, относящиеся к ней со стороны n, тоже будут удалены. Например, если такое отношение установлено для таблиц Customers и Orders (1:n), то удаление записи о клиенте автоматически вызовет удаление записей о его заказах.

В следующем примере удаляются все служащие Trainee ():

DELETE * FROM Employees WHERE Title = 'Trainee';

Изменение структуры

Прежде чем работать с данными непосредственно нужно подготовить структуру таблиц для их хранения и указать некоторые свойства этих данных. Прежде всего нужно создать таблицу либо на основе существующей (SELECT...INTO), либо новую (CREATE TABLE). В последнем случае нужно также указать свойства полей – тип, размер, уникальность и т.п.. Последнее выполняется выражением CONSTRAINT. Существующую таблицу можно изменить (ALTER TABLE) или удалить (DROP). Для ускорения поиска в таблице строятся индексы (CREATE INDEX), которые также можно удалять (DROP). С помощью индекса можно также обуспечить уникальность значений поля.

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

Оператор SELECT...INTO

Создает запрос, формирующий таблицу. Формат

SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

Оператор SELECT...INTO имеет следующие части:

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

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

Следующий пример копирует все записи из таблицы Employees в новую таблицу Emp Backup:

SELECT * INTO [Employees Backup] FROM Employees;

Следующий запрос создает таблицу Sales Representatives, содержающую только со служащих Sales Representative:

SELECT Employees.FirstName, LastName INTO [Sales Representatives]
FROM Employees WHERE Title = 'Sales Representative';

Следующий запрос создает копию таблицы Employees и помещает ее в базу Backup.mdb:

SELECT Employees.* INTO Employees IN Backup.mdb FROM Employees;

Следующий запрос создает новую таблицу Trainees на основе таблиц Employees и Payroll (отношение 1:1), добавляя к данным из Employees поле Salary из Payroll для всех служащих Trainee:

SELECT Employees.*, Salary INTO Trainees FROM Employees
INNER JOIN Payroll ON Employees.EmployeeID = Payroll.EmployeeID
WHERE Title = 'Trainee';

Оператор CREATE TABLE[3]