External connection
Inner Join only returns a line that matches the field value, and Outer Join returns a row of all rows and other tables in another table. There are two types of Outer Join.
LEFT OTER JOIN Returns the row of all rows and statements of the left table or result set on the left table or the result set. WHERE clause, * = operator Specific refers to Left Outer Join
Right Outer Join Returns the Right Outer Join statement to the left table of all rows and statements of the result set. In the WHERE clause, = * Operator Table Right Outer Join.
Typically, it is created in one to more. That is, the main table of "one" in the representative relationship appears on the left side of the Join expression or the operator in the WHERE clause, and the "multi" party in the relationship appears on the right side of the expression or operator. LEFT OTER JOIN can display all records of the primary table, regardless of matching records in the related table; Right Outer Join is useful for finding orphan records. The so-called "orphan record" is the records in the related tables that have not been recorded in the primary table, or the orphan record is the result of violation of the reference consistency rules.
Note: Jet SQL does not support * = and = * operators in the WHERE clause. Use the Jet database engine to reserve the word to create an external connection with the Left Join or Right Join.
The following SQL statements are tested in the query of Access XP
Construction form:
Create Table Tab1
ID Counter,
Name String,
Age INTEGER,
[DATE] DATETIME);
skill:
Self-add field declaration with Counter.
The field named field name is enclosed in square brackets [], and the number is also valued as a field name.
Establish an index:
The following statement establishes repeatable index on the Date column of TAB1
CREATE INDEX Idate on Tab1 ([DATE]);
After completion, the field date index attribute is displayed as - there is (have repetitive).
The following statement creates a non-repeatable index on the Name column of TAB1.
CREATE UNIQUE INDEX INAME ON TAB1 (NAME);
After completion, the field name index attribute is displayed as - there is (no repetition).
The following statement deletes the two indexes established.
Drop Index Idate on Tab1;
Drop Index Iname on Tab1;
ACCESS comparison with UPDATE statements in SQLServer:
Update the Update statement of multi table in SQLServer:
Update Tab1
Set a.name = B.Name
From tab1 a, tab2 b
WHERE A.ID = B.ID;
The same functionality SQL statement should be in Access
Update Tab1 A, Tab2 B
Set a.name = B.Name
WHERE A.ID = B.ID;
That is, the UPDATE statement in Access does not have an from clause, and all references are listed after the UPDATE keyword.
In the above example, if Tab2 is not a table, but a query, an example:
Update Tab1 A, (Select ID, Name from Tab2) B
Set a.name = B.Name
WHERE A.ID = B.ID;
Access multiple different access databases - use in clauses in SQL:
Select a. *, B. * From tab1 a, tab2 b in 'db2.mdb' where a.id = B.ID;
The above SQL statement queries all records associated with the ID in the current database in the current database (current folder).
Disadvantages - External databases cannot with password.
Supplement: See the reply of Ugvanxk in a post, you can use
Select * from [C: /AA/A.mdb; PWD = 1111] .table1;
Access XP Test By accessing other ODBC data sources in Access
The following example query data in SQL Server in Access
Select * from tab1 in [odbc]
[Odbc; driver = SQL Server; UID = SA; PWD =; server = 127.0.0.1; Database = demo;]
The complete parameters of external data source connection properties are:
[Odbc; driver = driver; server = server; database = data; uid = user; pwd = password;]
Where driver = driver can be in the registry
HKEY_LOCAL_MACHINE / SOFTWARE / ODBC / ODBCINST.INI /
Found
Access support child inquiry
Access supports external connections, but does not include complete external join, such as support
Left Join or Right Join
But not support
Full Outer Join or Full Join
Date in ACCESS
Note: Date time separator in Access is # instead of quotation marks
Select * from tab1 where [date]> # 2002-1-1 #;
In Delphi, I use this
SQL.Add (Format)
'Select * from tab1 where [date]> #% s #;',
[DATETOSTR (date)]));
The strings in Access can be separated by double quotation marks, but SQL Server does not recognize, so in order to migrate convenient and compatible.
It is recommended to use single quotes as a string separator.
ACCESS constraint
In Jet SQL reference, the content about constraints is not detailed, you can refer to SQL Server's online series
The following SQL is incremented by Name fields for a table.
Alter Table A Add Constraint A_Checkname Check (not [name] is null)
Note: Every constraint is an object, there is a name
The following statement sets the ID column as the primary key
ALTER TABLE [Table] Add Primary Key (ID)
The following statement changes the ID column to the automatic number type and set it to the primary key.
Alter Table [Table] ALTER [ID] Counter Constraint [Table _P] Primary Key
Add a composite master key
The following SQL adds a composite primary key (ID, ID2) to the TB_DEMO table.
ALTER TABLE TB_DEMO
Add constraint TB_DEMO_PK
Primary Key (ID, ID2)
