9.5.5. Изменение структуры таблицы

Для изменения структуры существующей таблицы используется команда добавления (Add) или удаления (Drop) поля или индекса.

Alter Table <таблица> {Add {Column <поле> <тип> [(<размер>)]

[Constraint <индекс>] |

Drop {Column <поле> | Constraint <имя индекса} }

Пример. Alter Table Kadr Add Column Adress Text (40)

9.5.6. Объединение таблиц

Запрос объединяет две таблицы с одинаковой структурой.

Пример.Объединить все записи текущей и архивной таблиц.

Select * From Kadr Union All select * From KadrArchive

Если убрать фразу All, то дублирующие записи из архивной таблицы игнорируются.

9.5.7. Перекрестные запросы

Перекрестные запросы (Crosstab Query) позволяют группировать данные по двум измерениям.

Пример. Имеется таблица (Prodaga) с ежедневной выручкой (Pocupka) от продаж по датам (Dataprodag) и покупателям (Pokupatel). Сформируем таблицу, в первой колонке которой указаны покупатели, в остальных колонках ‑ сумма по каждому покупателю за определенный день; строка содержит данные по одному покупателю.

Transform Sum(Pocupka) As Totalprodag Select Pokupatel From Prodaga

Where ((Dataprodag) Between #01/01/99# and #01/05/99#)

Group By Pokupatel Order By Pokupatel, Dataprodag Pivot Dataprodag

9.5.8. Подзапросы

Подзапрос (Subquery) - это запрос, результат которого используется в условии отбора в выражении Where другого запроса. Подзапрос заклю­ча­ет­ся в круглые скобки.

Пример. Вывести наименования покупателей с объемом покупок выше среднего.

Select Dataprodag, Pokupatel, Pocupka From Prodaga

Where Pocupka > (Select Avg(Pocupka) From Prodaga)

9.5.9. Создание и использование базы данных средствами запросов иAdo

Рассмотрим порядок создания и использования базы данных на сле­дующем примере (автор программы ‑ Д.Г. Шилин).

(General)(Declarations) ‘раздел глобальных объявлений формы

Dim cnL As New ADODB.Connection, cmA As New ADODB.Command

Dim rsM As New ADODB.Recordset, ADOX As New ADOX.Catalog

Private Sub Crbase_Click() 'создание пустой базы данных

On Error GoTo ErrOb ‘переход на обработку ошибок

' используя библиотеку ADOX, создаем базу MS Access стандарта

' MS Jet 3.51 (MS Access 97) или MS Jet 4.0 (Access 2000)

ADOX.Create (“Provider=Microsoft.Jet.OLEDB.3.51;Data Source=“ &_

App.Path & “\ado.mdb“) ‘создание пустой базы Accessв папке приложения

Set cnL = ADOX.ActiveConnection ' получение установленного соединения

cnL.Close ' и его закрытие, чтобы создание базы завершилось нормально

Addtbl.Enabled = True

Exit Sub

ErrOb: 'обработка ошибки

MsgBox Err.Description, vbOKOnly + vbApplicationModal + vbCritical, “Ошибка!“

End Sub

Private Sub Addtbl_Click()'создание и добавление новых таблиц в базу

On Error GoTo ErrOb ‘переход на обработку ошибок

'установка соединения с провайдером базы данных, задание источника ‘данных, тип доступа к базе, идентификатора пользователя

Call cnL.Open(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=“&App.Path_

& “\ado.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False“,_ “Admin“)

' Создается таблица Stroiki (стройки) с полями NameStroi - имя стройки

' AddrStroiki- адрес стройки, KodStroiki- код стройки (числовой)

cnL.Execute “CREATE TABLE Stroiki (NameStroiki TEXT(50), AddrStroiki “ & _ “TEXT(150), KodStroiki INTEGER);“

' Создание таблицы путем SQL запроса, используя объект Command

Set cmA.ActiveConnection = cnL ' задание текущего открытого объекту cmA ' Создается таблица Zak (заказчики) с полями NameZak ‑ название

‘заказ­­чика, RekZak - реквизиты заказчика, KodZak - код заказчика cmA.CommandText=“CREATE TABLE Zak(NameZak TEXT(50), “ & _

“RekZak TEXT(200), KodZak INTEGER);“

Call cmA.Execute(, adCmdText) ' выполнение команды типа SQL-запрос

' Cоздается таблица Zakazy (заказы) с полями KodZak - код заказчика, ‘KodStroiki- код стройки, NameZakaz - название (содержание) заказа

cmA.CommandText = “CREATE TABLE Zakazy (KodZak INTEGER, “& _

“KodStroiki INTEGER, NameZakaz TEXT(50));“

Call cmA.Execute(, adCmdText) ' выполнение команды типа SQL-запрос

CrInd.Enabled = True; cnL.Close ' закрытие соединения

Exit Sub: ErrOb: Call ErrObr ' вызов процедуры обработки ошибок

End Sub

Private Sub CrInd_Click() ' создание индексов

On Error GoTo ErrOb ' обработка ошибки

Call cnL.Open(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=“ & _

App.Path & “\ado.mdb;Mode=ReadWrite|Share Deny None;Persist Security “ &_

“Info=False“, “Admin“)

Set cmA.ActiveConnection = cnL

' Cоздается ключевой индекс в таблице Stroiki по полю KodStroi

cmA.CommandText = “CREATE UNIQUE INDEX KodStroi ON Stroiki “ & _

“(KodStroi ASC) WITH PRIMARY“

Call cmA.Execute(, adCmdText) ' выполнение команды

' Создается ключевой индекс в таблице Zak по полю KodZak

cmA.CommandText = “CREATE UNIQUE INDEX KodZak “ & _

“ON Zak (KodZak ASC) WITH PRIMARY“

Call cmA.Execute(, adCmdText) ' выполнение команды

cnL.Close: CrRel.Enabled = True: CrQuer.Enabled = True ' закрытие

Exit Sub : ErrOb: Call ErrObr ' вызов процедуры обработки ошибок

End Sub

Private Sub CrRel_Click() ' создание связей

On Error GoTo ErrOb ' обработка ошибки

Call cnL.Open(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=“ & _

App.Path & “\ado.mdb;Mode=ReadWrite|Share Deny None;Persist Security” &_

“Info=False“, “Admin“)

Set cmA.ActiveConnection = cnL ' задание соединения ALTER TABLE ‑ исполь­‘зуется для изменения структуры таблицы и создания межтабличных связей

' Связывается таблица Zakazy с таблицей Zak по полю KodZak

cmA.CommandText = “ALTER TABLE Zakazy ADD CONSTRAINT KodZak “ & _ “FOREIGN KEY (KodZak) REFERENCES Zak“

Call cmA.Execute(, adCmdText) ' выполнение команды

' Связывается таблица Zakazy с таблицей Stroiki по полю KodStroiki

cmA.CommandText = “ALTER TABLE Zakazy ADD CONSTRAINT KodStroiki” & _ “FOREIGN KEY (KodStroiki) REFERENCES Stroiki“

Call cmA.Execute(, adCmdText) ' выполнение команды

cnL.Close' закрытие соединения

ExitSub:ErrOb:CallErrObr' вызов подсистемы обработки ошибок

End Sub

Sub ErrObr() ' процедура обработки ошибочной ситуации

Dim cnErrors As Errors ' задание коллекции ошибок

Dim errr As Error ' задание объекта ошибки

Set cnErrors = cnL.Errors ' получение коллекции ошибок

' из объекта connection, вызвавшего ошибку

' Расшифровка всех ошибочных ситуаций, возникших в данном объекте

' путем обращения к каждому существующему объекту Error в коллекции Errors

For Each errr In cnErrors

MsgBox errr.Description & “ SQL State: “ & errr.SQLState, vbOKOnly + _

vbApplicationModal + vbCritical, “Ошибка!“

Next errr

' закрытие соединения,если ошибка была после того,как оно было установлено

If cnL.State = adStateOpen Then cnL.Close

End Sub

Private Sub cmTabout_Click() ‘вывод таблицы

On Error GoTo ErrOb ' обработка ошибки

cnL.Open (“Provider=Microsoft.Jet.OLEDB.3.51;Data Source=“ & App.Path & “\ado.mdb“) ' получение данных из базы выполнением запроса Mainzakaz

rsM.Open “mainzakaz“, cnL, adOpenKeyset, adLockOptimistic, adCmdTable

' вывод полученных данных в таблицу MainGrid

Set MainGrid.DataSource = rsM

Exit Sub: ErrOb: Call ErrObr ' вызов процедуры обработки ошибок

End Sub

Private Sub CrQuer_Click() ' создание хранимой процедуры Mainzakaz

On Error GoTo ErrOb ' обработка ошибки

cnL.Open (“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=“ & App.Path & _ “\ado.mdb“)

' создание команды-запроса, на основе которой будет создана хранимая

' процедура в базе данных (вывод полной информации по заказам)

cmA.CommandText = “SELECT Zak.*, Stroiki.*, Zakazy.* FROM “ & _

“Zak INNER JOIN (Stroiki INNER JOIN Zakazy ON Stroiki.KodStroi =” & _ “Zakazy.KodStroi) ON Zak.KodZak = Zakazy.KodZak;“

Set ADOX.ActiveConnection = cnL

' используя объектную библиотеку ADOX, создаем объектQUERY

' с именем Mainzakaz, с помощью команды, определенной ранее

ADOX.Procedures.Append “mainzakaz“, cmA

cnL.Close ' закрытие соединения

Exit Sub: ErrOb: Call ErrObr ' вызов процедуры обработки ошибок

End Sub