Хомоненко А.Д., Цыганков В.М., Мальцев М.Г. - Базы данных. Учебник для высших учебных заведений (6-е изд.) - 2009
.pdf10. СУБД Access 2002 |
313 |
Связь языков QBE и SQL
ВAccess между языками QBE и SQL имеется тесная связь. Запросные таблицы (бланки, формы) на языке QBE, заполняемые пользователем, перед непосредственным выполнением преобразуются в выражения (или сообщения) SQL. То есть язык SQL является внутренним стандартом на выполнение запросов. Такой механизм имеет преимущество, поскольку позволяет внутри системы Access унифицировать подготовку запросов к выполнению на локальном и удаленном компьютерах. В последнем случае SQL-сообще- ние реально передается к компьютеру-серверу запроса.
ВAccess запрос может находиться в одном из трех режимов (состояний):
Конструктора, SQL и таблицы. Режим Конструктора применяют для разработки нового запроса с чистого листа (без использования Мастеров или других средств) или для изменения макета существующего запроса. Режим SQL применяют для ввода или просмотра инструкций SQL. Режим таблицы применяют для работы с результатами выполнения запроса.
В режим таблицы запрос переходит при выборе нужного запроса во вклад-
ке З а п р о с ы ( Q u e r i e s ) |
окна БД и нажатии кнопки О т к р ы т ь ( O p e n ) . Нажатие |
кнопки К о н с т р у к т о р |
( D e s i g n ) или С о з д а т ь ( N e w ) в окне БД переводит зап- |
рос в режим Конструктора. В режим SQL можно попасть из других режимов |
|
по команде основного меню В и д | Р е ж и м S Q L (View | SQL) . |
Приведем пример состояний для одного и того же запроса. Предположим, мы открыли БД «Борей», в которой нас интересует запрос под названием «Десять самых дорогих товаров» (рис. 10.22).
Борей : база данных (формат Access 2000) |
|
- i P l x l |
||||
j§j Открыть |
Конструктор фСоздать ; X |
|
|
|||
|
Объекты |
Щ Создание запроса в режиме конструктора |
|Р |
Продажи по типам |
||
Щ Таблицы |
t J |
Создание запроса с помощью мастера |
|!Р |
Продажи по типам в 1997 |
||
(|[р |
Запросы |
ifi |
Выбор счета |
(jР |
Продажи товаров в 1997 |
|
幕Десять самых дороги тоЕ.ариЕ. |
Р |
Промежуточная сумма заказа |
||||
Э |
Формы |
|||||
еР |
Запрос Заказы |
|ijР |
Сведения о заказах |
|||
т |
Отчеты |
|||||
т |
Итоги продаж по объему |
(jjР |
Список имеющихся товаров |
|||
D |
Страницы |
оР |
Квартальные обороты |
0Р |
Список товаров |
|
а |
Макросы |
ш |
Квартальные обороты по товарам |
ЦР |
Сумма продаж по кварталам |
|
GD Клиенты и поставщики по городам |
[1Р |
Суммы продаж по годам |
||||
|
Модули |
|||||
|
оР |
Продажи по годам |
(jР |
Счета |
||
|
|
|||||
|
Группы |
оР |
Продажи по сотрудникам и странам |
ЦР |
Товары по типам |
|
|
|
l L |
1 |
|
|
Рис. 10.22. Окно примера БД «Борей»
3 1 4 Часть 3. Современные СУБД и их применение
П о с л е о т к р ы т и я з а п р о с а м о ж н о у в и д е т ь р е з у л ь т а т е г о в ы п о л н е н и я ( р и с . 1 0 . 2 3 ) .
gjp Десять самых дорогих товаров : запрос на выбо... И р й Ц З
С а и ы еД о р о г и е Т о в а р ы |
|
Ц е н а |
|
|
• |
|
1 1 8 5 7 5 0 , 0 0 р . |
||
T h u r i n g e r R o s t b r a t w u r s t |
|
5 5 7 |
0 5 5 , 0 0 р . |
|
M i s h i K o b e N i k u |
|
4 3 6 |
5 0 0 0 0 p |
|
Sir R o d n e y ' s M a r m a l a d e |
|
3 6 4 |
5 0 0 |
, 0 0 р . |
C a r n a r v o n T i g e r s |
Г |
2 8 1 |
2 5 0 .OOp1 |
|
R a c l e t t e C o u r d a v a u l t |
|
2 4 7 |
5 0 0 , 0 0 р . |
|
M a n j i m u p D r i e d A p p l e s |
|
2 3 8 |
5 0 0 , 0 0 р . |
|
T a r t e a u s u c r e |
|
2 2 1 8 5 0 Д ) р 1 |
||
Ipoh Coffee |
|
2 0 7 |
0 0 0 , 0 0 р . |
|
R o s s l e S a u e r k r a u t |
|
2 0 5 2 0 0 , 0 0 р . |
* |
|
Запись: J i J i l l |
1 • 1 и . | > * | Всего: 10 |
Р и с . 10.23. Р е з у л ь т а т в ы п о л н е н и я з а п р о с а « Д е с я т ь с а м ы х д о р о г и х т о в а р о в »
П е р е й д я в р е ж и м К о н с т р у к т о р а , в и д и м с т р у к т у р у з а п р о с а в в и д е б л а н к а
Q B E ( р и с . 1 0 . 2 4 ) . |
|
|
|
|
|
gjj-В Десять самых дорогих товаров : запрос на выборку |
П Т о Ш |
||||
КодТовара |
|
|
|
|
|
Марка |
|
|
|
|
|
КодПоставщ |
|
|
|
|
|
КодТипа |
^ J |
|
|
|
|
«I I |
|
|
|
|
|
Поле: |
|СамыеДорогиеТ о | Н Цена |
— ж |
|||
|
|||||
Имя таблицы: |
Товары |
Товары |
|
||
Сортировка: |
|
|
по убыванию |
|
|
Вывод на экран: |
|
М |
м |
U |
|
Условие отбора: |
|
|
|
|
|
или: |
|
|
|
•п |
|
|
|
.1.1 |
1 |
|
|
|
|
|
|
Р и с . 10.24. З а п р о с на я з ы к е Q B E
10. СУБД Access 2002 |
315 |
В режиме SQL в окне отображается формулировка запроса в виде инструкций на языке SQL (рис. 10.25).
gjp Десять самых дорогих товаров : запрос на выборку |
• - I n l x l |
SELECT DISTINCTROW ТОР 10 Товары.Марка AS |
|
СамыеДорогиеТовары, Товары.Цена |
|
FROM Товары |
|
ORDER BV Товары.Цена DESC; |
|
Рис. 10.25. Запрос на языке SQL
SQL в формах и отчетах
Основными источниками записей в экранных формах и отчетах являются таблицы и запросы. Во втором случае запросом может быть готовый запрос к БД или создаваемый при разработке формы или отчета. Описать новый запрос как источник записей при разработке формы или отчета в режиме Конструктора можно следующим образом.
1. Открыть окно базы данных и на вкладке Ф о р м ы ( F o r m s ) или
( R e p o r t s ) н а ж а т ь к н о п к у С о з д а т ь ( N e w ) .
2.В очередном окне выбрать режим создания объекта с помощью Конструктора и нажать ОК.
3.В окне создания новой формы или отчета подвести курсор мыши к за-
головку окна и в к о н т е к с т н о м меню выбрать пункт С в о й с т в а
( P r o p e r t i e s ) .
4. В появившемся окне Ф о р м а ( F o r m ) или О т ч е т (Report) на вкладке Д а н - н ы е (Data) определить источник записей с помощью SQL-выражения, вводимого в поле ввода свойства «Источник записей», или с помощью Построителя запросов, вызываемого нажатием кнопки напротив поля ввода SQL-выражения. При вызове Построителя запросов появляется окно добавления таблиц и запросов в модель запроса (рис. 10.26).
5. Ввести нужные таблицы/запросы в схему запроса-источника записей, получив тем самым формируемый запрос, возможный вид которого показан на рис. 10.27.
316 Часть 3. Современные СУБД и их применение
Добавление таблицы |
ИШ |
|
|
Ш Таблицы I цР Запросы j |р) Таблицы и запросы | |
Добавить |
|
|
|
Закрыть |
Заказано |
|
Заказы |
|
Клиенты |
|
Поставщики |
|
Сотрудники |
|
Типы |
|
Товары |
|
Рис. 10.26. Окно добавления таблиц и/или запросов
gjp Инструкция SQL : построт"ель |
запросов |
|
B S D |
|
Товары |
|
Поставщики |
|
|
КоаТовара |
|
КодПосганц |
|
|
Марка |
|
Название |
|
|
КодПоставщ |
jiJ |
Обращаться! |
|
|
КодТипа |
Должность |
|
|
|
Поле; |
КодТовара |
Марка |
Название |
Телефон |
Имя таблицы: Товары |
Товары |
Поставщики |
Поставщики— |
|
Сортировка: |
м |
|
м |
и |
Вывод наэкрай: |
и |
|||
Условие отбора; |
|
|
|
|
|
Ш |
|
|
• Г |
Рис. 10.27. Вид формируемого запроса
От этого запроса легко перейти к SQL-выражению одним из описанных способов, например, по команде В и д | Р е ж и м S Q L (View | S Q L ) .
10. СУБД Access 2002 |
3 1 7 |
SQL в |
макрокомандах |
Макрокоманды входят в состав макросов, которые используются для автоматизации выполнения часто повторяющихся действий в работе с БД. Макрос представляет собой одну или несколько макрокоманд с аргументами.
Макросы вызываются из окна БД или автоматически при наступлении определенных событий. В последнем случае требуется связать макрос с нужным событием. Событием, по которому вызывается макрос, может быть, например, нажатие кнопки в области экранной формы или открытие окна БД.
Наряду с выполнением некоторых действий над объектами БД макросы могут вызывать другие макросы, программы на Visual Basic и внешние приложения.
Из множества макрокоманд (около 50) с SQL непосредственно связаны две макрокоманды: 3 a n y c i < 3 a n p o c a S Q L ( R u n S Q L ) и О т к р ы т ь З а п р о с
( O p e n Q u e r y ) .
Макрокоманда 3anycK3anpocaSQL запускает запрос на изменение или управляющий запрос Access с помощью соответствующей инструкции SQL. Эта макрокоманда делает возможным выполнение действий в макросе без предварительного создания сохраненных запросов. С помощью макрокоманды
можно выполнять и сохраненные запросы. |
|
|
|
||
Запросами на изменение |
являются инструкции SQL (подраздел 3.9), ре- |
||||
ализующие следующие функции: добавление ( I N S E R T |
I N T O ) , |
удаление |
|||
( D E L E T E ) , создание таблицы ( S E L E C T . . . I N T O ) |
и обновление ( U P D A T E ) . |
||||
Управляющими |
запросами |
являются инструкции SQL, выполняющие сле- |
|||
дующие функции: создание таблицы (CREATE TABLE), изменение |
таблицы |
||||
( A L T E R T A B L E ) , |
удаление |
таблицы ( D R O P |
T A B L E ) , создание |
индекса |
|
(CREATE INDEX) и удаление индекса ( D R O P |
I N D E X ) . |
|
|
||
Е д и н с т в е н н ы м и о б я з а т е л ь н ы м аргументом |
макрокоманды |
||||
3anycx3anpocaSQL |
является инструкция SQL. Максимальная длина инструк- |
ции SQL составляет 255 символов. Для выполнения инструкции SQL длиной более 255 символов следует вызвать метод RunSQL объекта DoCmd в программе VBA. В программах VBA допускается использование инструкций SQL длиной до 32768 символов.
Аргумент макрокоманды в виде текста SQL-инструкции вводится вручную в окне ввода макрокоманды или копируется из окна SQL, что часто удобнее. Для выполнения последней манипуляции можно поступить так: войти в режим Конструктора запросов, создать запрос, получить эквивалентный оператор SQL, выделить его и поместить в буфер обмена. Пример готовой к выполнению макрокоманды с оператором SQL приведен на рис. 10.28.
3 1 8 |
|
Часть 3. Современные |
СУБД и их |
применение |
|
Ш. Макрос! : макрос |
|
|
|
|
тшш |
Макрокоманда |
1 |
Примечание |
' |
L |
|
3anycK3anpocaSQL |
|
| |
|
|
— |
|
|
|
|
|
|
— |
|
|
|
|
|
|
|
Аргументы макрокоманды |
|
|
|
Инструкция SQL |
SELECT DISTINCTROW TOF |
|
|
|
Столбец для ввода примечаний.
Рис. 10.28. Макрокоманда с оператором SQL
Макрокоманда ОткрытьЗапрос позволяет открывать запрос на выборку или перекрестный запрос (в режимах Таблицы, Конструктора и Предварительного просмотра), запускать запрос на изменение или ввод данных. Вызов ее эквивалентен нажатию кнопки О т к р ы т ь ( O p e n ) или К о н с т р у к т о р ( D e s i g n ) в окне базы данных после выбора запроса на вкладке З а п р о с ы
( Q u e r i e s ) .
В макрокоманде задаются три аргумента: имя запроса, режим и режим данных. Первый аргумент задает имя открываемого запроса и является обязательным. Второй аргумент задает режим открытия запроса. Допустимыми значениями являются: «Таблица» (используется по умолчанию), «Конструктор» и «Просмотр».
Третий аргумент описывает режим ввода данных в запрос. Аргумент можно применять к запросам, открываемым в режиме таблицы. Допустимые значения: «Добавление» (можно вводить новые записи, нельзя изменять существующие), «Изменение» (можно вводить новые и изменять существующие записи; действует по умолчанию) и «Только чтение» (разрешается просматривать записи).
Для ускоренного создания макроса с этой макрокомандой можно воспользоваться следующим приемом. Выбрать запрос в окне базы данных и переместить с помощью мыши в строку макрокоманды в макросе. При этом в макрос автоматически добавляется макрокоманда О т к р ы т ь З а п р о с , открывающая запрос в режиме таблицы.
Для вызова макрокоманды О т к р ы т ь З а п р о с в программе VBA использу-
ют метод O p e n Q u e r y объекта D o C m d .
10. СУБД Access 2002 |
319 |
SQL в программах |
на VBA |
Напомним, что программы на VBA, как и макросы, предназначены для автоматизации выполнения повторяющихся операций над объектами БД Access. Для выполнения программы следует запустить содержащую ее процедуру Sub
или ф у н к ц и ю Function. Программа записывается в процедуре как набор ин-
струкций и методов, с помощью которых выполняются требуемые действия. Процедуры, как известно, хранятся в модулях и выполняются в ответ на события или вызываются из выражений, макросов и других процедур.
В Access существуют следующие способы запуска программ VBA:
•включение программы в процедуру обработки события;
•вызов функции в выражении;
• вызов процедуры Sub в другой процедуре или в окне отладки;
• выполнение макрокоманды З а п у с к П р о г р а м м ы ( R u n C o d e ) в макросе.
Например, чтобы вызвать программу выполнения определенных действий при открытии формы, нужно включить ее текст в процедуру обработки события Н а ж а т и е к н о п к и ( C l i c k ) для кнопки, при нажатии которой будет открываться форма.
Функции применяются в выражениях, определяющих вычисляемые поля в формах, отчетах или запросах. Выражения используются для указания условий в запросах и фильтрах, а также в макросах, в инструкциях и методах VBA, а также в инструкциях SQL. В процедуру Sub можно включать общедо-
ступные VBA-подпрограммы, вызываемые из других процедур. |
|
Рассмотрим выполнение запроса к базе данных с помощью |
инструкций |
SQL в программе на Visual Basic для приложений.
В запросе производится отбор в базе данных записей, удовлетворяющих определенным условиям (запрос на выборку), либо выдается инструкция на выполнение указанных действий с записями, удовлетворяющими определенным условиям (запрос на изменение).
Если запрос SQL используется для возвращения данных, ядром базы данных Microsoft Jet создается объект R e c o r d s e t . После создания этого объекта можно использовать методы поиска ( F i n d ) и перемещения по записям набора.
Существуют следующие способы выполнения запросов:
• вызов метода E x e c u t e (для выполнения запросов SQL на изменение);
• создание и выполнение специального объекта Q u e r y D e f ; |
|
|
• и с п о л ь з о в а н и е и н с т р у к ц и и SQL в |
качестве аргумента |
метода |
O p e n R e c o r d s e t ; |
|
|
• в ы п о л н е н и е метода O p e n R e c o r d s e t |
для существующего |
объекта |
Q u e r y D e f ; |
|
|
• вызов методов R u n S Q L и O p e n Q u e r y . |
|
|
3 20 Часть 3. Современные СУБД и их применение
Метод E x e c u t e используется, если требуется выполнить такое изменение в БД, при котором не возвращаются записи. Это, например, операции вставки или удаления записей.
В качестве простейшего примера приведем команды Visual Basic для приложений выполнения запроса на изменение, в котором выполняется обновление записей таблицы «Должности», не имеющих значение в столбце ISBN. При возникновении ошибки все изменения отменяются.
Dim strSQL as String
strSQL = «DELETE FROM Должности WHERE ISBN IS NULL» dbsBiblio.Execute strSQL, dbFailOnError
Объект Q u e r y D e f представляет собой сохраненное определение запроса в базе данных. Его можно рассматривать как откомпилированную инструкцию SQL.
Приведенная ниже программа выполняет создание нового объекта Q u e r y D e f , после чего соответствующий запрос открывается в режиме таблицы.
Sub |
NewQueryQ |
|
|
|
|
|
|
||
' Объявление |
переменных |
|
|
|
|
||||
Dim dbs As Database, |
qdf As QueryDef, |
strSQL |
As |
String |
|||||
|
' Установка значения переменной |
типа |
Database, |
||||||
|
' представляющей |
текущую |
базу |
данных |
|
||||
|
Set dbs = |
CurrentDb |
|
|
|
|
|||
' Строка |
запроса |
|
|
|
|
|
|
||
|
strSQL |
= «SELECT * FROM |
Сотрудники |
WHERE |
|||||
[ДатаНайма |
]>= |
# 1-1 -95 # » |
|
|
|
|
|||
|
' Создание нового объекта |
QueryDef |
|
|
|||||
Set qdf = dbs.CreateQueryDef(«HoebieCompydHUKU», |
strSQL) |
||||||||
|
' Открытие |
запроса |
|
|
|
|
|||
DoCmd.OpenQuery |
qdf.Name |
|
|
|
|
||||
End |
Sub |
|
|
|
|
|
|
|
|
Метод OpenRecordSet используется, чтобы открыть объект типа RecordSet для выполнения последующих операций над ним.
В следующей процедуре с помощью инструкции SQL создается объект Recordset типа динамического набора записей. В предложение WHERE инструкции SQL включена функция Year, определяющая отбор заказов, размещенных в 1998 году.
Sub Orders98()
Dim dbs As Database, rstAs Recordset, strSQL As String
10. СУБД Access 2002 |
|
|
321 |
|
Dim fid |
As |
Field |
|
|
Set dbs |
= |
CurrentDb |
|
|
strSQL |
= «SELECT DISTINCTROW |
Заказ, ДатаРазмещения |
« & |
|
|
«FROM Заказы WHERE |
((Уеаг([ДатаРашещения])=1998));» |
||
Set rst = dbs.OpenRecordset(strSQL, |
dbOpenDynaset.) |
|
||
rst.MoveLast |
|
|
||
Debug.Print |
rst.RecordCount |
|
|
|
End Sub |
|
|
|
|
Метод R u n S Q L выполняет макрокоманду 3 a n y c K 3 a n p o c a S Q L |
( R u n S Q L ) |
в программе VBA. В следующем примере изменяется название должности всех агентов по продажам в таблице «Сотрудники».
DoCmd.RunSQL «UPDATE Сотрудники « &
«SET Сотрудники.Тк1е = 'Региональный представитель' « & «WHERE Сотрудники.ТШе = 'Агент по продажам';»
Метод OpenQuery в ы п о л н я е т макрокоманду О т к р ы т ь З а п р о с ( O p e n Q u e r y ) в программе VBA. С его помощью можно открыть запрос в режиме таблицы, Конструктора или просмотра. При этом устанавливается один из следующих режимов работы с данными: добавление, изменение или только чтение.
В следующем примере запрос «Выработка сотрудников» открывается в режиме таблицы, в котором пользователю разрешается просмотр записей.
DoCmd.OpenQuery «Выработка сотрудников»,, acReadOnly
Выбор варианта выполнения запросов определяется программистом с учетом особенностей решаемой задачи.
10.7. Защита баз данных
В Access реализованы следующие способы защиты баз данных: парольная защита, защита на уровне пользователей и шифрование.
Парольная защита БД
Парольная защита является простым и часто достаточным средством обеспечения защиты БД от открытия несанкционированными пользователями. Используемый при этом пароль называют паролем базы данных.
Зная пароль БД, любой пользователь сможет ее открыть и использовать, а также выполнить все необходимые операции с ней. Установка пароля может
11 Зак. 541
3 22 Часть 3. Современные СУБД и их применение
быть запрещена в случае, если для БД установлена защита на уровне пользователя и наложен запрет на парольную защиту.
Парольная защита может использоваться в дополнение к защите на уровне пользователя. В этом случае устанавливать парольную защиту может пользователь, обладающий правами администратора БД.
Поскольку парольную защиту можно применять наряду с защитой на уровне пользователя, возникает вопрос: «Можно ли защитить БД паролем, а затем защитить ее на уровне пользователя?». Ответ — нет. Дело в том, что защита на уровне пользователя состоит в создании новой БД, которая защищена и имеет структуру исходной БД. Из-за того, что Access не позволяет защищенную на уровне пользователя БД именовать так же, как и исходную, то новая БД всегда будет без пароля. После открытия БД ее можно легко защитить с помощью пароля. Таким образом, для установки обоих видов защиты БД сначала защищают на уровне пользователя, а потом устанавливают пароль.
Если парольная защита действует наряду с защитой на уровне пользователя, то пользователю предоставляется возможность выполнять над объектами БД действия, предусмотренные правами доступа.
Парольную защиту БД нельзя использовать в случае, если предполагается выполнять репликацию БД. Система Access не позволяет создавать копии (реплики) БД, защищенных паролем.
Метод парольной защиты достаточно надежен, так как пароль система Access шифрует, и к паролю нет прямого доступа. Он хранится вместе с защищаемой БД, а поэтому открыть БД не удается и на другом компьютере с установленной там системой.
Увидеть пароль или найти место в файле БД, которое он занимает, — занятие неперспективное. Попытки авторов сделать это с помощью средств прямого редактирования файлов (например, утилитой d i s k e d i t комплекта утилит N o r t o n Utilities) и специально для этого разработанных программ на языке Паскаль ни к чему не привели. При использовании парольной защиты от пользователя требуется подобрать удачный пароль и надежно его сохранить от потери и от хищения.
Процедура установки парольной защиты БД включает следующие шаги. 1. Закрытие базы данных, если она открыта. Если база данных используется в сети, следует проверить, что все остальные пользователи тоже зак-
рыли ее. |
|
2. Выбор в меню команды Ф а й л | О т к р ы т ь |
(File | O p e n ) . Появится диало- |
говое окно Открытие файла базы данных |
( O p e n ) . |
3. Установка с помощью соответствующего списка в правой нижней части окна открытия файла режима монопольного доступа ( М о н о п о л ь н о ( E x c l u s i v e ) ) и открытие базы данных.