. , .
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 | |
[az] | f, p, j | 2, & | |
[!az] | 9, &, % | b, a | |
[!09] | A, a, &, ~ | 0, 1, 9 | |
a[!bm]# | 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.