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 :
| predicate | ALL, DISTINCT, DISTINCTROW TOP. ALL |
| * | / |
| table | , |
| field1, field2 | , . |
| alias1, alias2 | , |
| tableexpression | / |
| externaldatabase | , / . |
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 :
| fieldlist | , , SQL, ALL, DISTINCT, DISTINCTROW TOP .. |
| tableexpression | /, . , , INNER JOIN, LEFT JOIN RIGHT JOIN. |
| externaldatabase | , / . |
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 :
| table2, table2 | |
| field1, field2 | , . , -. |
| compopr | : "=", "<", ">", "<=", ">=" "<>". |
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 :
| table2, table2 | |
| field1, field2 | , . , -. |
| compopr | : "=", "<", ">", "<=", ">=" "<>". |
, 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, :
| destination | , |
| tableexpression | /, . , , INNER JOIN, LEFT JOIN RIGHT JOIN. |
| path | |
| type | , , dBASE III, dBASE IV, Paradox 3.x Paradox 4.x |
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;
