The basic syntax of SQL statements: a full syntax unknown .SELECT statement is: SELECT [ALL | DISTINCT | DISTINCTROW | TOP] {* | talbe * | [table.] Field1 [AS alias1] [, [table] field2.. [As alias2] [, ...]]} from tableexpression [, ...] [in externalDatabase] [where ...] [group by ...] [Having ...] [ORDER BY ...] [WITH OWNERACCESS OPTION] Description: Use in parentheses ([[[[ ]) The partially representation is optional, and the part that is enclosed with braces ({}) is that one of the necessary one must be selected. 1. 1 From clause from clause specifies the source of fields in the SELECT statement. After the FROM clause is an expression containing one or more expressions (separated by commas), where the expression can be a single table name, saved query or composite results obtained by INNER JOIN, LEFT JOIN or RIGHT JOIN. If the table or query is stored in an external database, indicating its full path after the IN clause. Example: The following SQL statement returns all customized customers: Select ORDERID, Customer.customeridFrom ORDERS CUSTOMERSWHERE ORDERS.CUSTOMERID = Customers.customEersID One. 2 ALL, DISTINCT, DISTINCTROW, TOP predicate (1) All returns all records that meet the condition of the SQL statement. If this predicate is not specified, the default is ALL. Example: Select All Firstname, Lastname from Employees (2) DistINCT If there are multiple data of multiple recorded selection fields, only one is returned. (3) DistINCTROW If there is a repetitive record, only one (4) TOP display query head is several records. You can also return the percentage of records. This is to use top n percent clauses (where N represents percentage) example: Return 5% Order Single SLECT TOP 5 Percent * from [Order Details] Order by Unitprice * Quantity * (1 -Discount) DESC one. 3 Use the AS clause as a field to nap the name If you want to return a new title, or after the calculation or summary of the field, you have generated a new value, I hope to put it in a new column. The only AS is reserved. Example: Returns the alias FirstName field is NickName SELECT FirstName AS NickName, LastName, City FROM Employees Example: Return of a new display inventory value SELECT ProductName, UnitPrice, UnitsInStock, UnitPrice * UnitsInStock AS valueInStock FROM Products two .WHERE clause specifies the query II. 1 Comparison Operator Comparison Operators Meaning = Parts> Big than
When the comparative character type is data, add a single quotation number '', and the tail fuel is ignored in the comparison. Example: where orderdate> # 96-1-1 # can also be represented as: where orderdate> DateValue ('1/1/96') uses Not expressions. Example: Viewing the order after January 1, 96 WHERE NOT ORDERDATE <= # 1/1/96 # II. 2 Scope (Between and NOTWEEN) BETWEEN ... AND ... The operator specifies a closed interval to search for. Example: Returns the order of January 1996 to February 96. WHERE ORDERDATE BETWEEN # 1/1/96 # AND # 2/1/96 # II .3 List (In, NOT IN) IN operator is used to match any of the lists in the list. The IN clause can replace a series of conditions connected to the OR clause. Example: To find all customers living in London, Paris or Berlin Select Customerid, CompanyName, ContactName, CityFrom Customerswhere City in ('London', 'Paris', 'Berlin') II .4 Mode Match (LIKE) LIKE operator Check if a field value containing string data matches a specified mode. Wildcard wildcard meanings used by the LIKE operator? Any single character * single number between the character # 0 ~ 9 [character list] in the character list [! Character list] Not in any value in the character list - specify the character range, the values on both sides are the upper and lower prices: Return to Postal Coding between (171) 555-9999 customers Select Customerid, CompanyName, City, PhoneFrom Customerswhere Phone Like '(171) 555 - ####' Like operator Some style and meaning styles of styles do not meet the Like 'A *' A and follow the character BC, C255Like'5 [*] '5 * 5 555Like'5? 5' 5 and 5 There is any character 55, 5wer5like'5 ## 5 '5235, 5005 5kd5, 5346like' [AZ] 'AZ, any character 5,% Like' [! 0-9] 'Non 0-9 Any character 0,1like' [[] '1, * 3. Sort by the Order By clause Sage ORDER clause Press one or more (up to 16) fields The resulting query results can be ascending (ASC) or descending (DESC), the default is ascended. The ORDER clause usually places the last of the SQL statement. A plurality of fields are defined in the ORDER clause, order in order in the order of the fields. Example: Select ProductName, UnitPrice, UnitInstockFrom ProductsOrder By UnitInstock Desc, Unitprice Desc, ProductName ORDER BY clause You can use the field in the Selection list instead of the field name, you can mix the field name and location number. Example: The following statement produces the same effect as listed.
Select ProductName, UnitPrice, UnitInstockfrom ProductsOrder by 1 DESC, 2 DESC, 3V. Using connection relationship to implement multi-table quota queen: find out the supplier and customer name in the same city Select Customers.comPanyName, Suppliers.comPany.NameFrom Customers, SuppliersWHERE Customers.City = Suppliers.City Example: find the stock portfolio is greater than a product of the same order number and product orders SELECT ProductName, OrderID, UnitInStock, QuantityFROM products, [order Deails] WHERE Product.productID = [order Details] .ProductidAnd UnitsStock> Quantity Another method is to use Microsof Jet SQL unique JNNER JOIN Syntax: from table1 inner join table2on table1.field1 comparision table2.field2 where Comparision is the comparison operator used by the previous WHERE clause. Select Firstname, LastName, ORDERID, Customerid, ORDERDATEFROM Employeesinner Join ORDERS ON Employees.employeid = Orders.employeei Note: Inner Join cannot connect the MEMO OLECT SINGLE DOUBLE Data Type field. Connecting a plurality of ON JOIN statement clause in the syntax: SELECT fieldsFROM table1 INNER JOIN table2ON table1.field1 compopr table2.field1 ANDON table1.field2 compopr table2.field2 ORON table1.field3 compopr table2.field3 may SELECT fieldsFROM table1 INNER JOIN ( Table2 inner Join [(] Table3 [Inner Joer] on table1.field1 compopr table2.field1 on table1.field2 compoPR Table2.field2 on table1.field3 compopr Table2.field3 external connection Returns more records, In the result, the mismatched record is retained, and all records on the other side are returned regardless of the record that does not satisfy the condition. The From Table [Left | Right] Join Table2on Table1.field1Comparision Table.field2 is used to establish an external connection, The table in the left side of the expression will display all of its data: no matter whether there is no order, return all goods Select ProductName, OrderIDFROM ProductsLeft Join ORDERS ON Products.PrductSid = ORDERS.ProductID The difference between the right connection and the left connection is: no matter the left There is no matching record in the side table, which all returns all records from the left table. Example: If you want to know the customer's information, and count the distribution of customers in each region, you can use one right connection, even if there is no area Customers, also return customer information. Null values do not match each other, can be used to test whether the fields of a table connected have null values.
Select * from Talbe1LOIN TABLE2 on TALBE1LEFT JOIN TABLE2 ON TALBE1.A = Table2.c 4.1 The IIF function is used to display a null value IIF expression in 0 values in the connection query: IIF (Isnull (Amount, 0, Amout) Example: Regardless of whether orchard Less than ¥ 50, you have to return a flag. IIF ([Amount]> 50,? Big Order?,? Small order?) Five packets and summary query results in SQL grammar, Group By and Having clauses are used for data Make a summary.Group By clause indicates which fields to group, and then filter these records with a Having clause with a Having clause. Some of the syntax of the clause Select FidlistFrom Tablewhere Criteria [group by groupfieldlist [haVing groupcripcriteria]] Note: The Microsoft Jet Database JET cannot group memo or OLE object fields. The NULL value in the Group By field is in the packet but cannot be omitted. No null value is calculated in any SQL total. GROUP BY clause can be with up to Ten fields, sort priority is arranged in order from left to right. Example: After in the employee table in the 'WA' area, the number of employees with equal title is greater than 1 title.
Select Title, Count (Title) AS TotalFrom Employeeswhere Region = 'Wa'Group By TitleHaving Count (Title)> 1Jet SQL Meaning Function SUM () summary AVG () average count () expression record Number of count (*) Calculation record Max Max MIN minimum VAR Variance STDEV Standard error FIRST First Value Last Last Value Six Use Parameters Declaration Creating Parameters Query Parameters Declaration Syntax: Parameters Name Dattype [, Name Dattype [, ...]] Where Name is the flag of the parameter, you can reference the parameters through the flag. DataType Description Parameters Data type. Before using the parameters declaration before any other statement. Example: parameters [low price] currency, [beginning date ] DataTimeSelect ORDERID, OrderaMountFrom ORDERE ORDERDERAMOUNT> [Low Price] and ORDERDATE> = [Beginning Date] seven function query so-called function query, it is actually an operational query, which can perform a quick and efficient operation of the database. It is selected as a query Objective To pick up the requirements of the requirements, batch data. Features query includes update queries, delete queries, add queries, and generate table queries. Figure 1 Update query UPDATE clause can change one or more tables Data. It can also change the value of multiple fields at the same time. Update Query Syntax: Update Table Name SET New Value WHERE Criterion Example: British customer's order increased by 5%, increase 3% Update Oedersset ORDERAMOUNT = OrderaMount * 1.1 Freight = Freight * 1.03where shipcountry = 'uk' seven.2 Delete Query Delete clause can use the user to delete a large amount of outdated or redundant data. Note: Deleting the query is the syntax of the entire record. Delete clause: delete [ Table name. *] From Source Table Where Guide Example: To delete all 94 years ago DELETE * From OrderSwhere ORDERDATA <# 94-1-1 # 7.3 Additional Inquiry INSERT clauses can add one or a set of records to the tail of one or more tables. Into clause specifies the table VALUES keyword specified by the new record The new record contains the data value. INSERT clause syntax: INSETR INTO destination table or query (field 1, field 2, ...) VALUES (value 1, value 2, ...) Example: Add a customer insert Into Employees (firstname, LastName, Title) Values ('Harry') Seven .4 Generates a table query to copy all the records of all the conditions to a new table once again. Normally create a record backup or copy or as a report. Basics.Select INTO clauses are used to create a table query syntax: SELECT field 1, field 2, ... INTO new table [IN external database] from Source Database WHERE Criterified Example: Make a Archive Backup for the order Select * INTO ORDERSARCHIVEFROM ORDERS Eight Joint Query Union operations can merge multiple queries to merge the results in a result set. UNION operations General Syntax: [Table] Query 1 Union [all] Query 2 Union ... Example: Return to all suppliers and customers of Brazil and City Select CompanyName, CityFrom Supplerswhere Country = 'Brazil'Union Select CompanyName, City from Customers Where Country =
'Brazil' Note: Underfault, UNION clause does not return repeated records. If all records are wanted, you can add all the ALL units to have the same number of fields. However, the field data type does not have to be the same. Each one. In the query parameter, you can use the Group By clause or the HAVING clause to group. To display the returned data in the specified order, you can use the OREER BY clause at the end of the query. Nine cross-query cross queries can be done The calculation of sum, average, count, or other sum, calculation, the data is packetized by two information: one display on the top of the table, the other is displayed on the top of the table. Microsoft Jet SQL creates a crosstab to query the syntax for the TRANSFROM statement: TRANSFORM AGGFUNCTIONSELECT statement group BY clause Pivot Pivotfield [in (value1 [, value2 [, ...]])] Aggfounction refers to the SQL accumulation function, the SELECT statement selection field, Group By group Description: PivotField Create a column in the query results The field or expression used in the title, limits its value with an optional IN clause. Value represents the fixed value of the creation column header. Example: Displayed in each employee in each employee in 1996 number: TRANSFORM Count (OrderID) SELECT FirstName & '' & LastName AS FullNameFROM Employees INNER JOIN OrdersON Employees.EmployeeID = Orders.EmployeeIDWHERE DatePart ( "yyyy", OrderDate) = '1996'GROUP BY FirstName &' '& LastNameORDER BY FirstName &' '& LastNamePOVOT DatePart ( "Q", orderdate) & 'quarter' 10. Subridden subquery can be understood as a set of queries. Subquerry is a SELECT statement. 10.1 expression of the expression with the single value returned by the subquery Method: Expression Comparist [Any | All | Some] (Subqueries) Description: Any and Some predicates are synonymous with comparative operators (=, <,>, <>, <=,> =). Return a Boolean TRUE or FALSE . An meaning, A series of values returned by the expression and subquery, as long as the True result is generated in one, the ANY test returns the TRUE value (the result of both the WHERE clause), corresponding to the current record of the expression will enter the primary query In the result. AlL test requires the comparison of a series of values returned by the expression to the subquery to generate a true result. Example: The primary query returns a unit price of the product than any discount greater than or equal to 25% All Products of High Products Select * from Productswhere Unitprice> Any (Select Unitprice from [Order Details] WHERE DISCOUNT> 0.25) 10.2 Check that the value of the expression is a value of a set of values returned by the child query: [NOT ] In (sub-query) example: Return the inventory value greater than the product equal to 1000.Select ProductName from Productswhere ProductID in (Select ProdeIndiM] where unitprice * quantity> = 1000) Ten .2 Detection subqueries Returns any record syntax : [NOT] EXISTS: Search with exists Search for the UK Select CompanyName, ContactNameFrom ORDERSWHERE EXISTS (Select * from customerswhere country = 'uk' andcustomers.customerid =
