Объявление 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, имеет следующие части:
| Часть | Описание |
| fieldlist | Имя выводимого поля/полей с псевдонимами и предикатами ALL, DISTINCT, DISTINCTROW или TOP и т.п. |
| tableexpression | Имя таблицы/таблиц с данными |
| criteria | Выражение, которому должны удовлетворять записи, попадающие в запрос. |
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"
| Часть | Описание |
| Expression | SQL-выражение, используемое в WHERE |
| pattern | Строка или литерал, с которым производится сравнение. |
Операция Like производит сравнение выражения с шаблоном и возвращает значение True или False. В параметрическом запросе pattern можно запрашивать как параметр. Шаблон может быть задан полностью (Like "Smith") или содержать символы подстановки(Like "Sm*"). Например, чтобы найти все значения поля, начинающиеся на C, нужно указать Like "C*", а Like "P[A-F]###" позволит найти значения, начинающиеся с P, закоторой следует буква от A до F и три цифры.
В следующей таблице приведены примеры использования специальных символов в шаблонах:
| Спрособ сравнения | Шаблон | Совпадение (True) | Не совпадение (False) |
| Несколько символов | a*a | aa, aBa, aBBBa | aBC |
| *ab* | abc, AABB, Xab | aZb, bac | |
| Специальный символ | a[*]a | a*a | aaa |
| Несколько символов | ab* | abcdefg, abc | cab, aab |
| Один символ | a?a | aaa, a3a, aBa | aBBBa |
| Одна цифра | a#a | a0a, a1a, a2a | aaa, a10a |
| Интервал символов | [a–z] | f, p, j | 2, & |
| Вне интервала | [!a–z] | 9, &, % | b, a |
| Не цифра | [!0–9] | A, a, &, ~ | 0, 1, 9 |
| Комбинация | a[!b–m]# | An9, az0, a99 | abc, aj0 |
В 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, . . .)
| Часть | Описание |
| expr | Выражение, указывающее поле, сожержимое которого проверяется |
| value1, value2 | Выражение или список выражений, с которыми сравнивается expr |
Если значение найдено в списке, 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 | Выражение, указывающее поле, сожержимое которого проверяется |
| value1, value2 | Выражения, с которыми сравнивается expr |
Эквивалентно
(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)
Подзапрос имеет следующие части:
| Часть | Описание |
| comparison | Выражение и оператор сравнения, сравнивающий его с результатом подзапроса |
| expression | Выражение, результат которого ищется в наборе, сформированным подзапросом |
| sqlstatement | Собственно подзапрос – оператор SELECT, составленный по обычным правилам и заключенный в скобки |
Подзапрос можно использовать вместо списка значений в выражениях 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, имеет следующие части:
| Часть | Описание |
| fieldlist | Имя выбранного поля или полей, их псевдонимы, агрегатные функции SQL, предикаты выбора ALL, DISTINCT, DISTINCTROW или TOP и т.п. |
| table | Имя таблицы – источника данных (см. выражение FROM) |
| selectcriteria | Критерий отбора. Microsoft Jet сортирует результат после применения критерия. |
| field1, field2 | Имена полей, по которым проводится сортировка |
Выражение 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, имеет следующие части:
| Часть | Описание |
| fieldlist | Имя поля/полей с псевдонимами, агрегатные функции, предикаты ALL, DISTINCT, DISTINCTROW, TOP, и т.п. |
| table | Имя таблицы/таблиц с данными (см. FROM) |
| criteria | Критерий отбора – выражение, которому должны удовлетворять записи, попадающие в запрос. Если есть выражение WHERE, Microsoft Jet группирует записи после наложения этого условия. |
| groupfieldlist | Имена полей для группировки (до 10). Порядок перечисления определяет уровень группировки от высшего к низшему. |
Выражение 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, имеет следующие части:
| Часть | Описание |
| fieldlist | Имя поля/полей с псевдонимами, агрегатные функции, предикаты ALL, DISTINCT, DISTINCTROW, TOP, и т.п. |
| table | Имя таблицы/таблиц с данными (см. FROM) |
| selectcriteria | Критерий отбора – выражение, которому должны удовлетворять записи, попадающие в запрос. Если есть выражение WHERE, Microsoft Jet группирует записи после наложения этого условия. |
| groupfieldlist | Имена полей для группировки (до 10). Порядок перечисления определяет уровень группировки от высшего к низшему. |
| groupcriteria | Выражение, определяющее, какие из сгруппированных записей нужно отображать |
Выражение 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 имеет следующие части:
| Часть | Описание |
| aggfunction | Агрегатная функция, обрабатывающая выбранные данные |
| selectstatement | Оператор SELECT |
| pivotfield | Имя поля или выражение, используемое для заголовков столбцов |
| value1, value2 | Постоянные – заголовки столбцов |
Перекрестный запрос представляет данные в более компактном виде, чем обычный, позволяя указать имя поля или выражение, используемое в качестве заголовков столбцов. 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 имеет следующие части:
| Часть | Описание |
| name | Имя процедуры |
| param1, param2 | Одно или несколько названий полей или параметров. Например, PROCEDURE Sales_By_Country [Beginning Date] DateTime, [Ending Date] DateTime; См. PARAMETERS |
| datatype | Один из основных типов данных Microsoft Jet или синоним |
Процудура 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 имеет следующие части:
| Часть | Описание |
| name | Имя параметра. Присваивается свойству Name объекта Parameter и используется для идентификации в наборе Parameters. Отображается в диалоге запроса параметра при выполнении запроса. Имена, содержащие пробелы или знаки пунктуации, нужно заключать в квадратные скобки. |
| datatype | Один из основных типов данных Microsoft Jet или синоним |
Обявление параметров позволяет менять условия запроса, т.к. они запрашиваются при его выполнении. Объявление не обязательно, но если есть, должно предшествовать другим операторам. Если оно включает более одного параметра, они разделяются запятыми. Следующее объявление включает два параметра:
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 Типы данных
| Поле таблицы | Параметр запроса | VBA | DAO-константа | Microsoft Jet |
| Yes/No | Yes/No | Boolean | dbBoolean | BOOLEAN, BIT, LOGICAL, LOGICAL1, YESNO |
| Number (Byte) | Byte | Byte | dbByte | BYTE, INTEGER1 |
| Number(Integer)[1] | Integer | Integer | dbInteger | SHORT, INTEGER2, SMALLINT |
| Number (Long Integer) | Long Integer | Long | dbLong | LONG, INT, INTEGER, INTEGER4 |
| Auto(Long Integer) | Long Integer | Long | dbLong | COUNTER, AUTOINCREMENT |
| Number(Single) | Single | Single | dbSingle | SINGLE, FLOAT4, IEEESINGLE, REAL |
| Number(Double) | Double | Double | dbDouble | DOUBLE, FLOAT, FLOAT8, IEEEDOUBLE, NUMBER, NUMERIC |
| Currency | Currency | Currency | dbCurrency | CURRENCY, MONEY |
| Date/Time | Date/Time | Date | dbDate | DATETIME, DATE, TIME, TIMESTAMP |
| Replikations-ID | Replication ID | dbGUID | GUID | |
| Text | Text | String | dbText | TEXT, ALPHANUMERIC, CHAR, CHARACTER, STRING, VARCHAR |
| Memo | Memo | String | dbMemo | LONGTEXT, LONGCHAR, MEMO, NOTE |
| Hyperlink | Memo | String | dbMemo | LONGTEXT, LONGCHAR, MEMO, NOTE |
| OLE-Objekt | OLE Object | String | dbLongBinary | LONGBINARY, GENERAL, OLEOBJECT |
| Binary | BINARY, VARBINARY[2] | |||
| Value | Variant | VALUE |
Изменение данных
Управление данными сводится к трем основным операциям: ввод изменение и удаление. При этом структура таблиц остается неизменной.
Оператор UPDATE
Создает запрос на обновление, изменяющий значения полей в указанной таблице согласно указанному критерию. Формат
UPDATE table SET newvalue WHERE criteria;
Оператор UPDATE имеет следующие части:
| Часть | Описание |
| table | Имя таблицы |
| newvalue | Выражение, определяющее значение, которое вносится в конкретное поле изменяемой записи |
| criteria | Выражение, определяющее какие записи должны быть изменены |
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 имеет следующие части:
| Часть | Описание |
| target | Имя таблицы, в которую добавляются данные |
| externaldatabase | Путь к внешней базе (см. выражение IN) |
| source | Имя таблицы/запроса, из которой берутся данные |
| field1, field2 | Имена полей, в которые добавляются данные (если следуют за target), или откуда берутся данные (если следуют за source) |
| tableexpression | Имя таблицы/таблиц, откуда берутся данные. Может быть сохраненным запросом или запросом на объединение (INNER JOIN, LEFT JOIN, RIGHT JOIN) |
| value1, value2 | Значения полей в новой записи. Список должен соответствовать списку полей. Значения разделяются запятыми, текстовые величины заключаются в апострофы. |
Для добавления одной записи в таблицу в указывается имя и значение каждого поля. Если какое-либо поле не указано, ему присваивается значение 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 имеет следующие части:
| Часть | Описание |
| table | Не обязательное имя таблицы, из которой удаляются записи. Если во FROM указана только одна таблица, имена таблиц в списке DELETE указывать не нужно |
| table | Имя таблицы, из которой удаляются записи |
| criteria | Критерий отбора записей |
Для удаления таблицы используется оператор 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 имеет следующие части:
| Часть | Описание |
| field1, field2,... | Названия полей, копируемых в новую таблицу |
| newtable | Имя создаваемой таблицы. Если оно совпадает с именем существующей, генерируется ошибка. |
| externaldatabase | Путь к внешней базе (см. выражение IN) |
| source | Имя существующей таблицы (таблиц) или запрос – источник записей |
С помощью этого запроса можно сделать архивную копию таблицы, копию для экспорка в другую базу или источник записей для отчета (например, отчет о месячных продажах по регионам можно сделать на основе одного и того же запроса). Чтобы узнать, какие записи будут скопированы, можно сперва выбрать их с помощью оператора 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]
Создает новую таблицу. Формат
CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])
Оператор CREATE TABLE имеет следующие части:
| Часть | Описание |
| table | Имя создаваемой таблицы |
| field1, field2,... | Названия создаваемых полей. В новой таблице нужно создать хотя бы одно поле. |
| type | Тип поля |
| size | Размер поля в символах (для Text и Binary полей) |
| index1, index2 | Выражения CONSTRAINT, определяющие простые индексы (см. оператор CONSTRAINT) |
| multifieldindex | Выражение CONSTRAINT, определяющее составной индекс (см. оператор CONSTRAINT) |
Оператор CREATE TABLE создает новую таблицу и устанавливает свойства ее полей. Если для поля указано NOT NULL, оно требует обязательного ввода. Свойство NOT NULL можно указывать для отдельного поля или в именованом выражении CONSTRAINT. Соответственно, оно будет применяться к одному полю или ко всем перечисленным. Его можно устанавливать для поля только один раз, иначе происходит ошибка.
Выражение CONSTRAINT устанавливает ограничения для отдельного поля и может использоваться для создания первичного ключа. Для создания ключей (в т.ч. первичного) в существующей таблице используется оператор CREATE INDEX.
Следующие запросы меняют таблицы в базе Northwind (если изменения нежелательны, нужно предварительно сделать копию). Запросы вводятся в SQL окне конструктора запросов, затем нужно выполнить запрос.
Создается таблица с двумя текстовыми полями:
CREATE TABLE FirstTable (FirstName TEXT, LastName TEXT);
Создается таблица с двумя текстовыми и Date/Time полем, создается уникальный составной индекс на основе всех трех полей:
CREATE TABLE SecondTable (FirstName TEXT, LastName TEXT, DateOfBirth DATETIME, CONSTRAINT MyTableConstraint UNIQUE (FirstName, LastName, DateOfBirth));
Создается таблица с двумя текстовыми и Integer полем, поле SSN является первичным ключом:
CREATE TABLE ThirdTable (FirstName TEXT, LastName TEXT, SSN INTEGER CONSTRAINT MyFieldConstraint PRIMARY KEY);
Выражение CONSTRAINT[4]
Используется в операторах ALTER TABLE и CREATE TABLE для установки свойств полей – наложения или удаления ограничений. Позволяет также устанавливать связь с другой таблицей. Имеются две формы – для одного поля и для нескольких.
Ограничения для одного поля:
CONSTRAINT name {PRIMARY KEY | UNIQUE | NOT NULL |
REFERENCES foreigntable [(foreignfield1, foreignfield2)]}
Ограничения для нескольких полей
CONSTRAINT name
{PRIMARY KEY (primary1[, primary2 [, ...]]) |
UNIQUE (unique1[, unique2 [, ...]]) |
NOT NULL (notnull1[, notnull2 [, ...]]) |
FOREIGN KEY (ref1[, ref2 [, ...]])
REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, ...]])]}
Выражение CONSTRAINT имеет следующие части:
| Часть | Описание |
| name | Имя создаваемого ограничения |
| primary1, primary2 | Имя поля/полей, образующих первичный ключ |
| unique1, unique2 | Имя поля/полей, образующих уникальный индекс |
| notnull1, notnull2 | Имя поля/полей, обязательных для заполнения |
| ref1, ref2 | Имя поля/полей, внешнего ключа, ссылающихся на другую таблицу |
| foreigntable | Имя другой таблицы, содержащей указанные поля |
| foreignfield1, foreignfield2 | Имя поля/полей другой таблицы, на которые ссылаются ref1, ref2. Поля, входящие в ее первичный ключ можно не указывать. |
Синтаксис для единичного поля можно использовать в операторах ALTER TABLE и CREATE TABLE в определении поля сразу после указания типа, синтаксис для нескольких полей можно использовать в тех же операторах вне определений полей со служебным словом CONSTRAINT.
Выражение CONSTRAINT может устанавливать следующие ограничения:
· UNIQUE – определяет поле с уникальными значениями, т.е. две записи не могут содержать в этом поле одинаковые значения. Если полей несколько, в двух записях невозможны одинаковые сочетания их значений.
· PRIMARY KEY – поле/поля образуют первичный ключ. Все значения должны быть разными и не Null. Т.к. в таблице только один первичный ключ, при попытке создать второй происходит ошибка.
· NOT NULL – поле/поля не могут содержать Null, т.е. обязательны для заполнения.
· FOREIGN KEY – поле/поля образуют внешний ключ. Если первичный ключ внешней таблицы состоит из нескольких полей, нужно использовать форму CONSTRAINT для нескольких полей, содержащую перечень ссылающихся полей, имя внешней таблицы и имена полей во внешней таблицы, на которые ссылаются перечисленные, указанные в том же порядке. Если ссылка идет не поля первичного ключа внешней таблицы, их указывать не нужно, т.к. эти поля подразумеваются по умолчанию.
Следующие запросы меняют таблицы в базе Northwind (если изменения нежелательны, нужно предварительно сделать копию). Запросы вводятся в SQL окне конструктора запросов, затем нужно выполнить запрос.
Создается таблица с двумя текстовыми и Date/Time полем, создается уникальный составной индекс на основе всех трех полей:
CREATE TABLE SecondTable (FirstName TEXT, LastName TEXT, DateOfBirth DATETIME,
CONSTRAINT MyTableConstraint UNIQUE (FirstName, LastName, DateOfBirth));
Создается таблица с двумя текстовыми и Integer полем, поле SSN является первичным ключом:
CREATE TABLE ThirdTable (FirstName TEXT, LastName TEXT, SSN INTEGER
CONSTRAINT MyFieldConstraint PRIMARY KEY);
Оператор ALTER TABLE[5]
Меняет структуру таблицы после ее создания оператором CREATE TABLE. Формат
ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL][CONSTRAINT index] | CONSTRAINT multifieldindex} |DROP {COLUMN field I CONSTRAINT indexname} }
Оператор ALTER TABLE имеет следующие части:
| Часть | Описание |
| table | Имя изменяемой таблицы |
| field | Имя добавляемого или удаляемого поля |
| type | Тип поля |
| size | Размер поля в символах (для Text и Binary полей) |
| index | Индекс для поля (см. оператор CONSTRAINT) |
| multifieldindex | Определение составного индекса, добавляемого к таблице (см. оператор CONSTRAINT) |
| indexname | Имя удаляемого составного индекса. |
С помощью ALTER TABLE в существующей таблице можно сделать различные изменения:
· ADD COLUMN добавляет поле в таблицу. Нужно указать имя и тип поля, а для типов Text и Binary – размер. Например, следующий оператор добавляет 25-символьное текстовое поле Notes в таблицу Employees:
ALTER TABLE Employees ADD COLUMN Notes TEXT(25)
Можно также добавить индекс для этого поля (см. CONSTRAINT). Если для поля указано NOT NULL, то в новые записи необходито будет вводит допустимые данные.
· ADD CONSTRAINT добавляет составной индекс (см. CONSTRAINT).
· DROP COLUMN удаляет поле. Указывается только имя поля.
· DROP CONSTRAINT удаляет составной индекс. Указывается только имя индекса.
За один раз нельзя добавить или удалить более одного поля или индекса. Для добавления индекса можно также использовать CREATE INDEX., а для удаления – DROP. Можно указывать NOT NULL для одного поля или внутри именованного выражения для нескольких полей (?). При повторном применении к тому же полю NOT NULL вызывает ошибку выполнения.
Следующие запросы меняют таблицы в базе Northwind (если изменения нежелательны, нужно предварительно сделать копию). Запросы вводятся в SQL окне конструктора запросов, затем нужно выполнить запрос.
Ввести в таблицу Employees поле Salary типа Currency:
ALTER TABLE Employees ADD COLUMN Salary CURRENCY;
Удалить поле Salary из таблицы Employees:
ALTER TABLE Employees DROP COLUMN Salary;
Следующий пример добавляет внешний ключ к таблице Orders. Внешний ключ основан на поле EmployeeID и ссылается на поле EmployeeID таблицы Employees. В данном случае не обязательно указывать поле EmployeeID после таблицы Employees в выражении REFERENCES, т.к. EmployeeID является ее первичным ключом.
Sub AlterTableX3()
Dim dbs As Database
' Modify this line to include the path to Northwind on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Add a foreign key to the Orders table.
dbs.Execute "ALTER TABLE Orders ADD CONSTRAINT OrdersRelationship " _
& "FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID);"
dbs.Close
End Sub
Следующая процедура удаляет внешний ключ из таблицы Orders:
Sub AlterTableX4()
Dim dbs As Database
' Modify this line to include the path to Northwind on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
' Remove the OrdersRelationship foreign key from the Orders table.
dbs.Execute "ALTER TABLE Orders DROP CONSTRAINT OrdersRelationship;"
dbs.Close
End Sub
Изменения, проводимые следующими двумя запросами, можно увидель в окне Relationships (меню Tools). Удаление внешнего ключа из таблицы Orders:
ALTER TABLE Orders DROP CONSTRAINT EmployeesOrders;
Добавление внешнего ключа в таблицу Orders:
ALTER TABLE Orders ADD CONSTRAINT EmployeesOrders FOREIGN KEY (EmployeeID)
REFERENCES Employees (EmployeeID);
Оператор CREATE INDEX[6]
Создает новый индекс в существующей таблице. Формат
CREATE [ UNIQUE ] INDEX index ON table (field [ASC|DESC][, field [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]
Оператор CREATE INDEX имеет следующие части:
| Часть | Описание |
| index | Имя создаваемого индекса |
| table | Имя существующей таблицы, в которой создается индекс |
| field | Имя индексируемого поля/полей. Перечисляются в скобках после имени таблицы (одно для простого, несколько для составного). Если указано DESC – индексируется по убыванию, иначе по возрастанию |
Если значения поля/полей должны быть уникальны, используется служебное слово UNIQUE. В не обязательном выражении WITH указываются ограничения:
· DISALLOW NULL – запретить значения Null в индексируемом поле/полях (обязательны для заполнения)
· IGNORE NULL – исключить незаполненные поля из индексации
· PRIMARY – использовать индекс как первичный ключ (индекс подразумевается уникальным, UNIQUE можно не указывать). При попытке создать в таблице второй первичный ключ происходит ошибка.
Можно использовать CREATE INDEX для создания псевдоиндекса в присоединенной таблице из ODBC-источника (например, SQL Server). Для этого не нужно разрешения или доступа к удаленному серверу, он не реагирует на создание псевдоиндекса. Формат оператора тот же. Это особенно полезно для таблиц, которые были бы «только для чтения» из-за отсутствия индекса.
Можно использовать ALTER TABLE для добавления индекса и ALTER TABLE или DROP для удаления индекса, созданного ALTER TABLE или CREATE INDEX.
Следующие запросы меняют таблицы в базе Northwind (если изменения нежелательны, нужно предварительно сделать копию). Запросы вводятся в SQL окне конструктора запросов, затем нужно выполнить запрос.
В таблице Employees создается индекс на основании полей Home Phone и Extension:
CREATE INDEX NewIndex ON Employees (HomePhone, Extension);
В таблице Customers создается уникальный индекс на основе поля CustomerID, поле обязательно для заполнения:
CREATE UNIQUE INDEX CustID ON Customers (CustomerID) WITH DISALLOW NULL;
Удалить созданный индекс можно оператором
DROP INDEX CustID ON Customers;
Его нельзя удалить в окне конструктора, пока не удалены связи таблицы Customers.