Операции LEFT JOIN, RIGHT JOIN

SQL для Microsoft Access 97

(Курс молодого бойца)

Выборка данных................................................................................................................................................................. 1

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

Выражения SQL......................................................................................................................................................... 2

Выражение FROM..................................................................................................................................................... 3

Предикаты ALL, DISTINCT, DISTINCTROW, TOP.......................................................................................... 7

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

Выражение WHERE.................................................................................................................................................. 9

Выражение ORDER BY......................................................................................................................................... 13

Выражение GROUP BY.......................................................................................................................................... 14

Выражение HAVING.............................................................................................................................................. 14

Оператор UNION......................................................................................................................................................... 15

Оператор TRANSFORM............................................................................................................................................. 15

Выражение PROCEDURE..................................................................................................................................... 17

Объявление PARAMETERS.................................................................................................................................. 18

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

Оператор UPDATE...................................................................................................................................................... 19

Оператор INSERT INTO............................................................................................................................................ 20

Оператор DELETE....................................................................................................................................................... 21

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

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

Оператор CREATE TABLE....................................................................................................................................... 22

Выражение CONSTRAINT.................................................................................................................................... 23

Оператор ALTER TABLE.......................................................................................................................................... 23

Оператор CREATE INDEX........................................................................................................................................ 25

Оператор DROP............................................................................................................................................................ 25

Агрегатные функции........................................................................................................................................................ 26

Функция Count.............................................................................................................................................................. 26

Функция Avg.................................................................................................................................................................. 27

Функция Sum................................................................................................................................................................. 27

Функции Var и VarP..................................................................................................................................................... 28

Функции StDev и StDevP............................................................................................................................................ 28

Функции Min, Max....................................................................................................................................................... 29

Функции First, Last....................................................................................................................................................... 30

Structured Query Language – язык построения запросов для работы с данными, хранящимися в виде таблиц. Он позволяет

· Создавать и удалять таблицы и индексы;

· Менять структуру таблиц;

· Вводить и удалять данные;

· Выбирать и группировать имейщиеся данные.

Основная особенность SQL по сравнению с другими языками в том, что один оператор выполняет сразу несколько действий, очередность которых не описана им в явной форме. Отсюда (иногда) – неожиданные результаты.

Наиболее частое (и сложное) – выборка имеющихся данных. С этого и начнем.

Выборка данных

Запросы на выборку создаются с помощью операторов SELECT (выбор записей из существующих таблиц), UNION (объединение нескольких таблиц/запросов в одну) и TRANSFORM (создание сводной таблицы – перекрестный запрос).

«Основным» является оператор SELECT. В операторе SELECT сначала формируется источник (FROM), производится фильтрация (WHERE) и формируются записи из указанных полей с учетом предиката. Если используются агрегатные функции, они формируют записи с учетом группировки (GROUP BY) и сформированные записи фильтруются (HAVING). Полученный набор упорядочивается (ORDER BY).

Оператор SELECT

Возвращает набор записей (работает через Microsoft Jet). Формат:

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]

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

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

Обычно SELECT – первое слово в запросе, минимальный формат имеет вид

SELECT fields FROM table

Для выбора всех полей таблицы (источника) используется *

SELECT * FROM Employees;

Если имя поля встречается в нескольких таблицах, перечисленных в выражении FROM, перед ним указывается имя нужной таблицы, отделенное точкой. Например если поле Department имеется и в таблице Employees, и в таблице Supervisors, запрос, выбирающий Department из Employees и SupvName из Supervisors имеет вид

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

При создании объекта Recordset Microsoft Jet использует название поля в качестве имени объекта Field. Для изменения имени Field используется служебное слово AS. В следующем примере полю BirthDate присваивается псевдоним Birth:

SELECT BirthDate AS Birth FROM Employees;

При использовании агрегатной функции или запроса, возвращающего неоднозначное или дублирующееся имя объекта Field, необходимо использовать AS для введения однозначно определенного псевдонима:

SELECT COUNT(EmployeeID) AS HeadCount FROM Employees;

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

В окне запросов Microsoft Access можно посмотреть данные запроса в режиме таблицы. Имена столбцов – названия полей (field1, field2). Для отображения других названий используется служебное слово AS. Его использование эквивалентно установке свойства Alias в конструкторе запросов.

Выражения SQL

Выражения SQL – строки, из которых формируются операторы SQL.

Microsoft Jet использует VBA для вычисления выражений. Все операторы SQL-выражений, кроме Between, In и Like, определены в VBA. В VBA имеется также более 100 функций, которые можно использовать в SQL-выражениях. Например, можно использовать эти функции при создании запроса в конструкторе запросов или в методе OpenRecordset объекта DAO. Можно также использовать собственные функции, написанные на VBA.

Функции, включаемые в SQL-выражение, должны возвращать либо строку, либо Variant. Выполняемая функцией операция должна выполняться один раз – нельзя использовать функцию, которая выполняет операцию для каждой записи, т.к. оператор SQL передается Microsoft Jet только один раз. Нельзя, например, использовать функцию, формальным аргументом которой является значение поля.

Можно использовать функции в программе для создания как объекта QueryDef, так и динамического или неизменяемого объекта Recordset. В конструкторе запросов можно использовать их в ячейках условий и вычисляемых полей.

Далее процедура создает динамический объект Recordset, оператор SQL включает функцию Year в выражении WHERE:

Sub Orders96()

Dim dbs As Database, rst As Recordset, strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT DISTINCTROW OrderID, OrderDate " _
& "FROM Orders WHERE ((Year([OrderDate])=1996));"

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveLast

Debug.Print rst.RecordCount

End Sub

Следующий пример показывает, как функция включается в вывод запроса:

SELECT DISTINCTROW ShipName, Len([ShipName]) AS LengthOfShipName
FROM Orders;

Этот же запрос можно создать в конструкторе: в одну ячейку «перетащить» имя поля ShipName, в другую ввести LengthOfShipName: Len([ShipName]).

Выражение FROM

Указывает таблицы или запросы, содержащие поля списка SELECT. Формат

SELECT fieldlist FROM tableexpression [IN externaldatabase]

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

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

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

SELECT LastName, FirstName FROM Employees;

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

Все поля таблицы Employees:

SELECT * FROM Employees;

Товары, их цены и цены, увеличенные на 10%:

SELECT ProductName, UnitPrice AS Current, UnitPrice * 1.1 AS ProposedNewPrices
FROM Products;

Число товаров, их средняя и максимальная цена (используются агрегатные функции):

SELECT Count(*) AS [Total Products], Avg(UnitPrice) AS [Average Unit Price],
Max(UnitPrice) AS [Maximum Unit Price] FROM Products;

Посчет числа записей с установленным значением PostalCode:

SELECT Count(PostalCode) AS Tally FROM Customers;

Названия и цены товаров:

SELECT ProductName, 'has a unit price of', UnitPrice FROM Products;

В следующем примере предполагается, что таблица Employees содержит поле Salary.

Число служащих, их средняя и максимальная зарплата:

Sub SelectX3()

Dim dbs As Database, rst As Recordset

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

dbs = OpenDatabase("Northwind.mdb")

' Count the number of employees, calculate the average salary, and return

' the highest salary.

Set rst = dbs.OpenRecordset("SELECT Count (*)AS TotalEmployees, Avg(Salary) " _
& "AS AverageSalary, Max(Salary) AS MaximumSalary FROM Employees;")

rst.MoveLast ' Populate the Recordset

' Call EnumFields to print the contents of the Recordset.

' Pass the Recordset object and desired field width.

EnumFields rst, 17

dbs.Close

End Sub

Вывод записей в окне отладчика. rst – динамический Recordset, intFldLen – ширина вывода полей.

Sub EnumFields(rst As Recordset, intFldLen As Integer)

Dim lngRecords As Long, lngFields As Long

Dim lngRecCount As Long, lngFldCount As Long

Dim strTitle As String, strTemp As String

' Set the lngRecords variable to the number of records in the Recordset.

lngRecords = rst.RecordCount

' Set the lngFields variable to the number of fields in the Recordset.

lngFields = rst.Fields.Count

Debug.Print "There are " & lngRecords & " records containing " & lngFields _
& " fields in the recordset."

Debug.Print

' Form a string to print the column heading.

strTitle = "Record "

For lngFldCount = 0 To lngFields - 1

strTitle = strTitle & Left(rst.Fields(lngFldCount).Name _
& Space(intFldLen), intFldLen)

Next lngFldCount

Debug.Print strTitle ' Print the column heading

Debug.Print

' Loop through the Recordset; print the record number and field values.

rst.MoveFirst

For lngRecCount = 0 To lngRecords - 1

Debug.Print Right(Space(6) & Str(lngRecCount), 6) & " ";

For lngFldCount = 0 To lngFields - 1

If IsNull(rst.Fields(lngFldCount)) Then ' Check for Null values.

strTemp = "<null>"

Else

' Set strTemp to the field contents.

Select Case rst.Fields(lngFldCount).Type

Case 11
strTemp = ""

Case dbText, dbMemo
strTemp = rst.Fields(lngFldCount)

Case Else
strTemp = str(rst.Fields(lngFldCount))

End Select

End If

Debug.Print Left(strTemp & Space(intFldLen), intFldLen);

Next lngFldCount

Debug.Print

rst.MoveNext

Next lngRecCount

End Sub

Операции LEFT JOIN, RIGHT JOIN

Комбинируют записи из таблиц-источников в выражении FROM. В результате создаются записи с большим количеством полей (таблица «расширяется»). Формат

FROM table1 [ LEFT | RIGHT ] JOIN table2 ON table1.field1 compopr table2.field2

Операции LEFT JOIN и RIGHT JOIN имеют следующие части:

Операция LEFT JOIN объединяет каждую запись из первой (левой) таблицы с теми записями из второй (правой), которые удовлетворяют критерию compopr. При этом из первой таблицы в набор включаются все записи – левое внешнее объединение.

Операция RIGHT JOIN объединяет каждую запись из второй (правой) таблицы с теми записями изпервой (левой), которые удовлетворяют критерию compopr. При этом из второй таблицы в набор включаются все записи – правое внешнее объединение.

Например при объединении таблиц Departments (левой) и Employees (правой) с помощью LEFT JOIN будут выведены все отделы, включая те, где нет сотрудников. При объединении с помощью RIGHT JOIN будут выведены все сотрудники, включая тех, кто не приписан ни к какому отделу.

Следующий запрос объединяет таблицы Categories и Products по полю CategoryID, выводя все категории, включая те, которые не содержат продуктов:

SELECT CategoryName, ProductName FROM Categories LEFT JOIN Products
ON Categories.CategoryID = Products.CategoryID;

При этом значения CategoryID не выводятся, т.к. их нет в списке SELECT.

Для запросов, где значения полей связи одинаковы предпочтительнее операция INNER JOIN. Операции LEFT JOIN и RIGHT JOIN могут входить в INNER JOIN, но INNER JOIN не может входить в LEFT JOIN или RIGHT JOIN. Связь может устанавливаться несколькими выражениями ON. При попытке связать по Memo или OLE полям происходит ошибка.

Таблицу можно объединять саму с собой, но при этом нужно использовать псевдоним. Например, имеется таблица tbl с полями idx (повторяется) и val. Следующий запрос пронумерует val «внутри» каждого idx:

SELECT Count(t2.val)+1 AS N, t1.idx AS Code, t1.val AS Val
FROM tbl AS t1 LEFT JOIN tbl AS t2 ON (t1.val>t2.val) AND (t1.idx=t2.idx)
GROUP BY t1.idx, t1.val ORDER BY t1.idx, t1.val;

t1 и t2 используются как псевдонимы для таблицы tbl.

В следующем примере предполагается наличие полей Department Name и Department ID в таблице Employees (их нет в таблице базы Northwind). Выводятся все отделы, включая тех, где нет сотрудников (вызывается процедура EnumFields из примера к FROM):

Sub LeftRightJoinX()

Dim dbs As Database, rst As Recordset

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

Set dbs = OpenDatabase("Northwind.mdb")

' Select all departments, including those without employees.

Set rst = dbs.OpenRecordset("SELECT [Department Name], " _
& "FirstName & Chr(32) & LastName AS Name " _
& "FROM Departments LEFT JOIN Employees " _
& "ON Departments.[Department ID] = Employees.[Department ID] " _
& "ORDER BY [Department Name];")

rst.MoveLast ' Populate the Recordset

' Call EnumFields to print the contents of the Recordset.

' Pass the Recordset object and desired field width.

EnumFields rst, 20

dbs.Close

End Sub

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

SELECT Customers.CustomerID, CompanyName, OrderID FROM Customers LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID ORDER BY OrderID;

– все заказчики, включая тех, у кого нет заказов.

SELECT Suppliers.SupplierID, Suppliers.CompanyName, Products.ProductID, Products.ProductName FROM Products RIGHT JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID ORDER BY Products.SupplierID;

– все поставщики, включая тех, у кого нет продукции. Перед выполнением запроса нужно ввести данные в таблицу Suppliers:

INSERT INTO Suppliers (CompanyName, ContactName, ContactTitle)
VALUES ('Acme Supply Co.', 'That Coyote', 'Sales Manager');

Чтобы удалить введенные записи, нужно выполнить запрос

DELETE * FROM Suppliers WHERE CompanyName = 'Acme Supply Co.';

Операция INNER JOIN

Комбинирует записи из таблиц-источников в выражении FROM. Формат

FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2

Операция INNER JOIN имеет следующие части:

Наиболее частый тип объединения, может использоваться в любом выражении FROM. Соединяет записи из двух таблиц на основе критерия, создает набор объединенных записей.

Например при объединении таблиц Departments и Employees с помощью INNER JOIN будут выведены все отделы и все их сотрудники (см. также LEFT JOIN и RIGHT JOIN).

Можно связывать таблицы по полям одинаковых типов, например AutoNumber и Long (один и тот же тип), но по Single и Double связывать нельзя. При попытке связать по Memo или OLE полям происходит ошибка.

Следующий запрос объединяет таблицы Categories и Products по полю CategoryID:

SELECT CategoryName, ProductName FROM Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID;

При этом значения CategoryID не выводятся, т.к. их нет в списке SELECT. Чтобы его вывести, нужно включить в список Categories.CategoryID.

Можно использовать несколько выражений ON, связанных логическими операциями:

SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2) OR
ON table1.field3 compopr table2.field3)];

Объединения могут быть вложенными:

SELECT fields FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3 compopr tablex.fieldx)]
ON table2.field2 compopr table3.field3)
ON table1.field1 compopr table2.field2;

Операции LEFT JOIN и RIGHT JOIN могут входить в INNER JOIN, но INNER JOIN не может входить в LEFT JOIN или RIGHT JOIN.

В следующем примере связываются таблицы Order Details и Orders, и Orders и Employees.

Это нужно потому, что Employees не содержит сведений о продажах, а Order Details о служащих. Результат содержит список служащих и их объем продаж:

SELECT DISTINCTROW Sum(UnitPrice * Quantity) AS Sales,
FirstName & " " & LastName AS Name
FROM Employees INNER JOIN(Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY FirstName & " " & LastName;

Выражение IN

Указывает таблицу во внешней базе данных, с которой Microsoft Jet может установить соединение (например, dBASE, Paradox или внешняя Microsoft Jet). Формат указания целевой таблицы

[SELECT | INSERT] INTO destination IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

формат указания таблицы-источника:

FROM tableexpression IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

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

IN позволяет одновременно подсоединиться только к одной внешней базе.

Аргумент указывает директорию, содержащую данные. Например, в случае dBASE, FoxPro или Paradox – директорию с .dbf или .db файлами. Имя таблицы извлекается из destination или tableexpression аргументов.

Для не Microsoft Jet баз тип базызаключается в апострофы или кавычки, а в конец добавляется точка с запятой: 'dBASE IV;' или "dBASE IV;". Можно использовать служебное слово DATABASE. Следующие выражения эквивалентны:

... FROM Table IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];

... FROM Table IN "C:\DBASE\DATA\SALES" "dBASE IV;"

Вместо IN выгоднее использовать связанные таблицы, это повышает производительность.

Примеры обращения к различным базам (к таблице Customers):

1. Microsoft Jet

SELECT CustomerID FROM Customers IN OtherDB.mdb WHERE CustomerID Like "A*";

2. dBASE III или IV (для dBASE III нужно указывать "dBASE III;")

SELECT CustomerID FROM Customer IN "C:\DBASE\DATA\SALES" "dBASE IV;"
WHERE CustomerID Like "A*";

3. dBASE III или IV с использованием DATABASE

SELECT CustomerID FROM Customer IN "" [dBASE IV; Database=C:\DBASE\DATA\SALES;]
WHERE CustomerID Like "A*";

4. Paradox 3.x or 4.x (для Paradox version 3.x нужно указывать "Paradox 3.x;")

SELECT CustomerID FROM Customer IN "C:\PARADOX\DATA\SALES" "Paradox 4.x;"
WHERE CustomerID Like "A*";

5. Paradox 3.x or 4.x с использованием DATABASE

SELECT CustomerID FROM Customer
IN "" [Paradox 4.x;Database=C:\PARADOX\DATA\SALES;]
WHERE CustomerID Like "A*";

6. Лист Microsoft Excel

SELECT CustomerID, CompanyName FROM [Customers$]
IN "c:\documents\xldata.xls" "EXCEL 5.0;"
WHERE CustomerID Like "A*" ORDER BY CustomerID;

7. Именованая область листа

SELECT CustomerID, CompanyName FROM CustomersRange
IN "c:\documents\xldata.xls" "EXCEL 5.0;"
WHERE CustomerID Like "A*" ORDER BY CustomerID;

12
• 3
• Далее ⇒