SELECT statement
The command Microsoft Jet database engine returns information from the database as a set of records.
grammar
SELECT [PREDICATE] {* | table. * | [Table.] Field1 [As Alias] [, [Table.] Field2 [As Alias] [, ...]]} from tableexpression [, ...] [in the outside Database] [WHERE ...]] [Group by ...]] [Having ...]] [ORDER BY ...]] [WITH OWNERACCESS Option]
The SELECT statement has the following parts:
Some describe one of the following predicates: All, Distinct, DistINCTROW, or TOP. You can use the predicate to limit the number of records returned. If no predicate is specified, the default value is all. * Specify all fields from a specific table. The name of the Table table contains the fields that have been selected in this table. The name of the Field1, Field2 field contains the data you want to get. If the data contains multiple fields, they will be obtained in order in order. Alias1, Alias2 name, used to make a header to replace Table. Central Plains list. The name of the TableExpression table contains the data to be obtained. The name of the ExternalDatabase database contains tables in TableExpression if these tables are not in the current database.
Description
To complete this operation, the Microsoft Jet database engine searches for the specified table, extracts the selected columns, and selects the row that meets the condition and selects the selected row in order, or group them.
The SELECT statement does not change the data in the database.
SELECT is usually a SQL statement. Most SQL statements or SELECT statements, or SELECT ... INTO statements.
The shortest syntax of the SELECT statement is:
Select Fields from Table
You can use an asterisk (*) to select all the fields in the table. The following example selects all the fields in the employee table:
Select * from Employees;
If there is a plurality of tables in the FROM clause contains a field name, the field is previously a table name and. (Point) operator. In the following example, the "Department" field will appear in the employee table and the super user table. The SQL statement will select the department from the employee table and the super user table:
Select Employees.Department, Supervisors.suPvname
From Employees Inner Join Supervisors
WHERE Employees.Department = supervisors.Department;
When the Recordset object is created, the Microsoft Jet database engine names the field name of the table as the Field object in the Recordset object. If you want a different field name, or if you want a name that is not exported by the generated expression, use the AS reserved word. Name the Field object returned in the Recordset object obtained in the following example:
Select Birthdate
AS BIRTH from Employees;
Whenever you use a synthetic function or query, and the query returns a vague or duplicate Field object name, you must use the AS clause to provide an alternate name of the field object. Name the Field object with the Headcount title in the Recordset object in the following example:
Select Count (EmployeeID)
AS Headcount from Employees; Other clauses in the SELECT statement can be used to further limit and organize the data returned. For more information, see the topic help of the clauses used.
============================================================================================================================================================================================================= ====
SELECT ... INTO statement
Create a table search.
grammar
Select Field1 [, Field2 [, ...]] INTO New Table [IN External Database] from Source
SELECT ... INTO statement can be divided into the following sections:
Some describe the name of the field to the new table. NewTable's name for the table wants to create. It should follow the standard naming conventions. If the NEWTABLE name is the same as the name of the existing table, the error can be captured. EXTERNALDATABASE enters the path to the external database. For a description of the path, see the IN clause. Source from the name of the existing list of records. It can be a single table or multiple tables or a query.
Description
You can use the generated table query to archive records, generate a copy backup of the table, or generate a copy of the table that outputs to another database, or serves as a basis for regularly displaying data. For example, you can run the same generated table query once a month, build a regional sales monthly report.
note
Maybe you want to define a primary key for a new table. When you create a new table, the fields in the new table will inherit the data type and size of each field in the basic table, but do not pass other fields or table properties. Use the INSERT INTO statement without creating an additional search to add data to the current table. Before running the table query, you want to know which records you choose, you can first see the results of the SELECT statement using the same selection criteria.
============================================================================================================================================================================================================= ===
INSERT INTO statement
Add one or more records to a table. This is called an additional query.
grammar
Multi-record additional query:
INSERT INTO TARGET [(Field1 [, Field2 [, ...]])] [IN external database] SELECT FIELD1 [, Field2 [, ...]] from tableExpression
Single record additional query:
INSERT INTO TARGET [(Field1 [, Field2 [, ...])] Values (value1 [, value2 [, ...])
The INSERT INTO statement can be divided into the following sections:
Some describe the name of the Target to add the recorded table or query. Field1, Field2 If the Target parameter is followed, it is a field name to add data; if the back is the Source parameter, the field name is obtained from it. EXTERNALDATABASE enters the path to the external database. For a description of the path, see the IN clause. Source Copy Record Source Table or Name of Query. TableExpression gives the table name of the record to be inserted. This change may be a single table name, or it may be a complex consisting of Inner Join, Left Join, or Right Join, or a stored query. Value1, Value2 wants to insert the value of the new recorded specific field. Each value will insert the relevant field in order in the list in the list: value1 will be inserted into the Field1 of the appended record, and Value2 is inserted into the field2, and so on. These values must be separated using a comma, and the text fields are enclosed in quotation marks (''). Description
You can use the INSERT INTO statement to add a single record to a table, as shown above, add query syntax using a single record. In this example, the code specifies the name and value of each field of the record. You must specify the value of each field of the recording of the added value and the value of that field. If you do not specify each field, the default or NULL value will be inserted into a field without data. These records will be added to the tail of the table.
Additional records can also be added to another table or query by using the SELECT ... FROM clause, such as the multiple record syntax shown above. In this example, the SELECT clause will specify the append field to the specified target table.
The Source or Target table can specify a table or query. If the query is specified, the Microsoft Jet database engine adds the record to all tables specified by the query.
INSERT INTO is optional, but when in use, please be placed before the SELECT statement.
If your target table contains a primary key, you must append the unique non-null value into the primary key field, otherwise the Microsoft Jet database engine will not add records.
If you want to add a record to a table with the autonumber field, you still want to rearray the additional record, please do not include the autonumber field in your query. If you want to keep the original value in the field, add the auto number in your query.
Use the IN clause to add a table recorded in another database.
To create a new table, please use the SELECT ... INTO statement instead of the creation of the tabby query.
To find out which records are appended before running additional queries, first perform and check the results obtained using the selected query using the same selection condition.
Add the query to replicate record to another from one or more tables. The table you have added containing records will not be affected by the query.
In addition to adding existing records from another table, you can specify using the VALUES clause in a single additional record to specify the value of each field. If you omit a list of fields, the VALUES clause must contain the value of each field in the table; otherwise, the INSERT operation will
============================================================================================================================================================================================================= ======
The UPDATE statement creates an update query to change the field value in the specified table based on a particular criterion.
grammar
Update table set new value WHERE criterion;
The UPDATE statement can be divided into the following sections:
Some describe the name of the Table table, which contains the data to be changed. NewValue expression, used to calculate the value to insert a specific field in the update record. Criteria expression is used to calculate the updated record. Only recorded records will be updated.
Description
Update is especially useful when you need to change multiple records, or if you want to change in multiple tables.
You can change multiple fields at the same time. The following examples add 10% of the United Kingdom to one 10% of Order Amount and 3% of FREIGHT (freight):
Update Orders
Set OrderaMount = OrderaMount * 1.1,
FREIGHT = FREIGHT * 1.03
Where shipcountry = 'uk';
Focus
Update does not generate results set. And after using the update query update record, this operation cannot be canceled. If you want to know which records are updated, let's take a look at the results of the selected query using the same condition, and then run the update query. Pay attention to the copy backup of maintaining data. If you update the error record, you can restore these data from the backup copy.
============================================================================================================================================================================================================= ==
DELETE statement
Create a delete query to list the record from the from the future and comply with one or more of the WHERE clause.
grammar
Delete [table. *] From table Where standard
DELETE statements can be divided into the following sections:
Some describe the optional names of Table from which to delete records. Table deletes the name of the recorded table. Criteria determines which recorded expression.
Description
You can use Delete to delete multiple records.
The entire table can be removed from the database with the Execute method and the DROP statement. However, if you remove the table with this method, you will lose the structure of the table. Different is to use Delete, only the data will be deleted; the structure of the table and all attributes of the table are still reserved, such as field properties and indexes.
You can use Delete from the table with a multi-relational table with other tables. If the corresponding record of the table in the relationship is deleted in a query, the cascaded deletion operation will delete the record of the table of 'many' in the relationship. For example, in the relationship between the client table and the order table, the client table is 'one' party, and the order table is 'many'. If you specify a cascaded deletion, remove a record from the customer data, and the appropriate order record will also be deleted.
Deleting a query does not only delete data in the specified field, which will delete all records. If you want to delete the specified field value, create an update query make this value to NULL.
Focus
This operation cannot be canceled after using the delete query to delete the record. If you want to know which records have been deleted, first verify the results of the selected query using the same conditions, then run the delete query. Pay attention to the copy backup of maintaining data. If you misuse records, you can restore data from backup copies.
========================================================================= xcute Statement
Used to activate Procedure.
grammar
Execute process [parameter 1 [, parameter 2 [, ...]]
The UPDATE statement can be divided into the following sections:
Part of the process name Param1 to be executed, Param2 is defined by the process of parameter S value
==============================================
TRANSACTION statement
Used to start and end explicit transactions.
grammar
Start a new transaction.
Begin Transaction
End the transaction by submitting all the work performed during the transaction.
Commit [Transaction | Work]
End the transaction through all the work done by the rollback transaction.
Rollback [Transaction | Work]
Description
The transaction will not start from start. To start the transaction, you must start using Begin Transaction.
The transaction can nested up to 5 floors. To start the nested transaction, use the Begin Transaction in the current transaction.
The connection table does not support the transaction.
=================================================================================
TRANSFORM statement
Create a connection query.
grammar
TRANSFORM Total Function SelectStatement Transform AggunctionSelectStatementPivot Pivotfield [in (Value1 [, Value2 [, ...]])]
The Transform statement can be divided into the following sections:
Some explanation of the SQL syndic function SelectStatementSELECT statement for the AggFunction operation selected. PivotField is used to create a field or expression of the column header in the query result. Value1, Value2 is used to create a fixed value of column headings.
Description
When using a cross-table query to summarize data, the selected value is selected from the specified field or expression as a column header, which can be used to observe the data with a more compact format than the selected query.
Transform is optional, but when it is included as the first sentence of the SQL string. It appears before the SELECT statement (specified as a field of the row title), it also appears before the group BY clause (specified line packet). You can selectively include other clauses, such as a WHERE clause, which specifies additional selection or sorting conditions. It is also possible to query the child as a predicate, especially in the WHERE clause of the fork table.
The value returned by Pivotfield is used as column headings in the query result set. For example, 12 columns will be created according to the sales chart in a set of sales. The pivotfield can be restricted to create a title in a fixed value (value1, value2) column in an optional IN clause (Value1, Value2). You can also create additional columns with fixed values without data. ============================================================================================================================================================================================================= =====
Inner Join Operation
A record in two tables is combined as long as there is a value that is consistent among the common fields.
grammar
From table1 inner join table2 on table1.field1 compopr Table2.field2
Inner Join operations can be divided into the following parts:
Some describe the name of Table1, Table2 records the combined table. Field1, Field2 The name of the field being coupled. If they are not composed of numbers, these fields must be the same data type and contain similar data, but they do not have the same name. CompoPR Any relationship comparative operation operator: "=," <, "">, "<=," "> =," or "<>"
Description
INNER JOIN operations can be used in the FROM clause. This is the most common connection type. As long as there is a synon value in the common fields of these two tables, the internal join will combine records in the two tables.
All employees in each department can be selected using Inner Join and department tables and employee tables. Conversely, you can create Outer Join using the Left Join or Right Join operations, thereby choosing all departments (even if there are some employees) or all employees (even if some have not been assigned to the department).
If you try to join a field containing MEMO or OLECT data, it will cause errors.
You can join any two identical value fields. For example, you can join the Autonumber and the long field because they are similar. But you cannot join the fields of Single and Double types.
The following example shows how to connect class identifier field coupons and product tables:
Select CategoryName, ProductName, ProductName, PRODUCTNAME
From Categories Inner Join Products
ON categories.categoryid = products.categoryId;
In the above example, the class identifier is the field that has been coupled, but it is not included in the query output because it is not included in the SELECT statement. In this example, if you want to include a join field, include the field name in the SELECT statement, categories.categoryId.
You can also use the following syntax, link multiple ON clauses in a join statement:
SELECT FIELDSFROM TABLE1 INNER JOIN TABLE2 ON TABLE1.FIELD1 COMPOPR TABLE2.FIELD1 ANDOON TABLE1.FIELD2 COMPOPR TABLE2.FIELD2) OR On Table1.field3 Compopr Table2.field3)]; You can also use the following syntax, nested JOIN statements:
Select Fieldsfrom Table1 Inner Join (Table2 Inner Join "(] TableX [Inner Join ...) on table3.field3 compopr Tablex.fieldx)] on table2.field2 compopr table3.field3) on table1. Field1 compopr Table2.field2;
In an Inner Join, you can nested LEFT JOIN or RIGHT JOIN, but inner Join is not nested in Left Join or Right Join.
============================================================================================================================================================================================================= ============
Left Join and Right Join Operations
When used for the FROM clause, the source table record is combined.
grammar
From table 1 [left | Right] JOIN Table 2 ON Table 1. Field 1 CompoPR Table 2. Field 2
Left Join and Right Join operations can be divided into the following sections:
Some describe the name of Table1, Table2 records the combined table. Field1, Field2 The name of the field being coupled. And these fields must have the same data type and data containing the same type, but they don't need the same name. CompoPR Any relationship comparative operation operator: "=," <, "">, "<=," "> =," or "<>"
Description
Create a left external join with Left Join operation. The external connection of the left will contain all the records in the two tables starting from the first (left), even in the second (right side) table, there is no synergistic record.
Use the Right Join operation to create the right external join. The right external join will contain all records in the two tables starting from the second (right), even in the first (left) table, there is no matching record.
For example, you can use the LEFT JOIN and the department (left) and employee (right) table to select all departments, including the department that is not assigned to the employee. All employees can be selected using Right Join, including employees that are not assigned to the department.
The following example shows how to join class tables and product tables in the class identifier field. The query will list all kinds of lists that include those who do not have products in it:
Select categoryname,
ProductName
From categories left join productson categories.categoryId = product
In this example, CategoryID is a join field, but because it is not included in the SELECT statement, it is not included in the query result. To include the link field, enter a field name in the SELECT statement - in this example for categories.categoryID.
note
To create a query that only contains the same records as the data in the join field, use the Inner Join.
You can write a nested LEFT JOIN or a Right Join in Inner Join, but you cannot write nested inner Join in an Left Join or a Right Join. Please refer to the INNER JOIN topic for discussions using nested, from which to know how to write nested joints in other connections. You can link multiple ON clauses. For more information, see the discussion of the clause links in the INNER JOIN topic.
If you try to join a field containing MEMO or OLECT data, it will cause errors.
============================================================================================================================================================================================================= =========
UNION operation
Create a joint query that combines two or more independent queries or tables.
grammar
[TABLE] Query1 Union [all] Query2 [union [all] [TABLE] queryn [...]]
UNION operations can be divided into the following sections:
Some describe the name-NSELECT statement, the name of the stored query, or the name of the crown with the storage table of the Table keyword.
Description
In any combination, in a single UNION operation, the results of two or more queries, tables, and SELECT statements can be combined. The following examples merge the existing table named new Accounts and a SELECT statement:
Table [New Accounts] Union ALL
SELECT *
From customer
Where OrderaMount> 1000;
According to the default regulations, the repetitive records are not returned when using the UNION operation; however, it can contain all the records to ensure that all records are returned. In this way, the speed of running the query will be fine.
All queries in the primary Union operation must require the same number of fields; however, the fields do not have to have the same size or data type.
You can only use an alias in the first SELECT statement because they have been omitted in other statements. In the ORDER BY clause, you can reference the field name called in the first SELECT statement.
note
Group BY or HAVING clauses can be used in each query change to set the data packet. To display the returned data in the specified order, you can use the Order By clause at the end of the last query parameter.
============================================================================================================================================================================================================= ======== Parameters declaration
Describe the names and data types of the parameters in the parameter query.
grammar
Parameters name DataType [, Name Datatype [, ...]]]]]]
Parameters declared these parts:
Some describe the name of the Name parameter. Has Name features (this feature belongs to the Parameter object) and is used to identify this parameter in Parameters. When the application runs the query, you can use Name as a string in the dialog box. Text application square brackets containing spaces or punctuation symbols ([]) enclose. For example, [Low Price] and [Begin Report With Which Month?] Are all valid Name parameters. DataType Basic Microsoft Jet SQL Data Type or One of its synonyms.
Description
For queries with rules, you can create a parameter query with the Parameters declaration. Parameter query is easy to automatically change query guidelines. For parameter queries, the code must provide the parameters used each time the query runtime.
Parameters declares is optional, but when used, you must include a SELECT statement before any other statement.
If the declaration contains more than one parameter, divide them with a comma. Two parameters declared in the following examples:
Parameters [Low Price] Currency, [Beginning Date] DateTime;
Name can be used in the WHERE or HAVING clause, but the data type cannot be used. Two parameters are provided in the following examples, then use this condition to query the records in the ORDERS table.
Parameters [low price] Currency,
[Beginning Date] DateTime;
Select OrderId, Orderamount
From Orders
Where OrderaMount> [Low Price]
And ORDERDATE> = [Beginning Date];
============================================================================================================================================================================================================= ===
WITH OWNERACCESS OPTION declaration
In a multi-user environment with a security work group, use the declaration and query to run the query to the same permissions as the query owner.
grammar
SqlStatement with Owneraccess Option;
Description
The WITH OWNERACCESS Option is optional.
The following example enables users to view wage information (even if the user does not have permission to view the salary list), provide the authority owned by the owner:
Select Lastname, Firstname, Salary
From Employees
ORDER by lastname
WITH OWNERACCESS OPTION;
If the user can't create a table or add, you can use the WITH OWNERACCESS Option to run a tab or add a query.
If you want to force the workgroup security settings and users' permissions, don't include the WITH OWNERACCESS Option declaration.
This option requires you to access the System.mdw file with the database. It is only useful in safe multi-user implementation.
================================================================================★
SQL subquery
The subquery is a SELECT statement, which is nested in a SELECT, SELECT ... INTO statement, INSERT ... INTO statement, delete statement, or UPDATE statement or nested in another subquery.
grammar
You can create subqueries with three grammar:
Comparison [any | all | Some] (SQLStatement)
Expression [NOT] in (SqlStatement)
[NOT] EXISTS (SQLStatement)
Subsequet can be divided into the following parts:
Some describe a expression of Comparison and a comparison operator to compare the results of the expression with the subquery. Expression is used to search for the expression of the child query result set. SQLSTATEMENTSELECT statement, comply with the same format and rules as other SELECT statements. It must be included in parentheses.
Description
Can take a child instead of an expression for the SELECT statement field table or a WHERE or HAVING clause. In the subquery, in the expression of where or haVing clauses, the specific value for calculation is provided by the SELECT statement.
Using ANY or Some predicates, they are synonymous words to retrieve records in the main query, these records to meet any records of any records retrieved in the subquery. The following examples will return all units that are solder than any of the products sold at 25% or more discounts:
Select * from products
Where unitprice> Any
(Select Unitprice from OrderDetails
WHERE discount> = .25;
Use all predicates to search only these records in the main query, which satisfies the comparison conditions of all records retrieved in the subquery. If you change any ANY in the previous example to all, the query will only return a product that is high-selling products that are sold at 25% or more discounts. This is more limitations.
With the in predicate, only those records can be retrieved in the primary query, and some records in the subquery also include the same values as they. The following example returns all products with 25% or higher discounts:
Select * from products
WHERE ProductID in
(Select ProductID from OrderDetails
WHERE discount> = .25;
Instead, it can be recorded in the primary query in the primary query, and there is no records as the value of them in the subquery.
Use the exists predicate (with optional Not reserved words) in True / False comparison to determine whether the subquerix will return any records. The table name alias in the subquery can be used to query the list of the FROM clauses outside the sub-query. The following example returns the employee name of the payroll equal to or higher than the average salary of all positions. The alias of this employee table is "T1":
Select Lastname,
Firstname, Title, Salary
From Employees as T1
Where salary> =
(SALECT AVG (Salary)
From Employees
Order by Title; Order by Title; / EMPLOYEES.TITLE =
In the above example, the AS retention word is optional.
Some subquers are allowed in the crosstab query, especially predicates (those in the WHERE clause). A child query is used as an output (those columns in SELECT) are not allowed in the crosstab query.
