SQL syntax-common SQL commands

(1) Data Record Filtering:

SQL = "select * from data table where field name = Field Value Order by field name [DESC]"

SQL = "select * from data table where field name like/'% Field Value %/'order by field name [DESC]"

SQL = "select top 10 * from data table where field name order by field name [DESC]"

SQL = "select * from data table where field name in (/'value 1/',/'value 2/',/'value 3 /')"

SQL = "select * from data table where field name between value 1 and value 2 ″

(2) update data records:

SQL = "update data table set field name = field value where condition expression"

SQL = "update data table set field 1 = value 1, Field 2 = value 2 ...... Field n = value n where condition expression"

(3) Delete data records:

SQL = "delete from data table where condition expression"

SQL = "delete from data table" (delete all data table Records)

(4) add data records:

SQL = "insert into data table (Field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...)"

SQL = "insert into target data table select * from source data table" (add records of source data table to target data table)

(5) statistical functions of data records:

AVG (field name) returns the average value of a table column
Count (* | field name) statistics on the number of data rows or the number of data rows with values in a column
Max (field name) obtains the maximum value of a table column.
Min (field name) obtains the minimum value of a table column.
Sum (field name) adds the values in the data column

The method for referencing the above functions:

SQL = "select sum (field name) as Alias from data table where conditional expression"

 

Bytes ----------------------------------------------------------------------------------------------

The complete syntax of the 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]
Note:
The Section enclosed by braces ([]) is optional. The section enclosed by braces ({}) indicates that one of them must be selected.
1 From clause
The from clause specifies the field source in the SELECT statement. The from clause is followed by one or more expressions (separated by commas ), the expression can be a single table name, saved query, or compound result obtained by inner join, left join, or right join. If a table or query is stored in an external database, specify its complete path after the in clause.
For example, the following SQL statement returns all customers with orders:
Select orderid, customer. mermerid
From orders MERs
Where orders. customerid = customers. customeersid

2 All, distinct, distinctrow, and top predicates
(1) All returns all records that meet the SQL statement conditions. If this predicate is not specified, the default value is all.
Example: Select All firstname, lastname
From employees
(2) distinct if the selected fields of multiple records have the same data, only one is returned.
(3) If distinctrow has repeated records, only one
(4) Top displays several records at the beginning and end of the query. You can also return the percentage of records. This is to use the top N percent clause (where n represents the percentage)
For example, the maximum order amount of 5% is returned.
Select top 5 percent *
From [Order Details]
Order by unitprice * quantity * (1-discount) DESC

3. Use the as clause to get the alias for the Field
If you want to get a new title for the returned column, or after calculating or summarizing the field, a new value is generated and you want to display it in a new column, it is retained with.
For example, if the returned firstname field is named nick name
Select firstname as nickname, lastname, City
From employees
For example, return a new column to show the inventory value.
Select productname, unitprice, unitsinstock, unitprice * unitsinstock as valueinstock
From Products

II. The WHERE clause specifies the query conditions.

1 comparison operator
Comparison operator meaning
= Equal
> Greater
<Less
> = Greater than or equal
<= Less than or equal
<> Not equal
!> Not greater
! <Not less
For example, return the order from January 1, January.
Select orderid, customerid, orderdate
From orders
Where orderdate> #1/1/96 # And orderdate <#1/30/96 #
Note:
In mcirosoft jet SQL, dates are bounded. The date can also be replaced by the datevalue () function. When comparing character data, you must add single quotation marks ''. Trailing spaces are ignored during comparison.
Example:
Where orderdate> #96-1-1 #
It can also be expressed:
Where orderdate> datevalue ('2014/1/96 ')
Use the not expression to reverse the request.
For example, view the orders placed after January 1, January 1.
Where not orderdate <=# 1/1/96 #
Range 2 (between and not)
Between... And... The operator specifies a closed interval to be searched.
For example, an order from January 1, January to January 31, February is returned.
Where orderdate between #1/1/96 # And #2/1/96 #
3 list (in, not in)
The in operator is used to match any value in the list. An in clause can replace a series of conditions connected by an or clause.
For example, you need to find all customers who live in London, Paris, or Berlin.
Select customerid, companyName, contactname, City
From MERs
Where city in ('London ', 'Paris', 'berlin ')
4. pattern matching (like)
The like operator checks whether the value of a field containing string data matches a specified pattern.
Wildcard characters used in the like Operator
Wildcard characters
? Any single character
* Any length of Characters
#0 ~ A single number between 9
[Character List] any value in the Character List
[! Character List] any value not in the Character List
-Specify the character range. The upper and lower limits are the values on both sides.
Example: return the customer whose zip code is between (171) 555-0000 and (171) 555-9999.
Select customerid, companyName, city, phone
From MERs
Where phone like '( 171) 555 -####'
Style and meaning of the like Operator
Invalid style meaning
Like 'a * 'a followed by any length of characters BC, c255
Like '5
'5*5 555
Like '5? Any character between 5 and 5 is 55, 5wer5
Like '5 # 5' 5235,5005 5kd5, 5346
Like '[A-Z]' any character between a-Z 5, %
Like '[! 0-9] 'any character other than 0-9, 0, 1
Like '[[] '1 ,*
3. Sort the results using the order by clause
The Order clause sorts the query results by one or more (up to 16) fields, either ascending or descending. The default value is ascending. The Order clause is usually placed at the end of an SQL statement.
Multiple fields are defined in the order clause.
Example:
Select productname, unitprice, unitinstock
From Products
Order by unitinstock DESC, unitprice DESC, productname
In the order by clause, you can replace the field name with the position number in the field selection list. You can mix the field name and position number.
For example, the following statement produces the same effect as the preceding one.
Select productname, unitprice, unitinstock
From Products
Order by 1 DESC, 2 DESC, 3
4. Multi-Table query using connection relationships
Example: Find the names of suppliers and customers in the same city
Select customers. companyName, suppliers. Company. Name
From MERs, suppliers
Where customers. City = suppliers. City
For example, find out the products and orders with the product inventory greater than the order quantity of the same product.
Select productname, orderid, unitinstock, quantity
From products, [order deails]
Where product. productid = [Order Details]. productid
And unitsinstock> quantity
Another method is to use the jnner join exclusive to microsof jet SQL.
Syntax:
From Table1 inner join Table2
On table1.field1 comparision table2.field2
Comparision is the comparison operator used by the WHERE clause.
Select firstname, lastname, orderid, customerid, orderdate
From employees
Inner join orders on employees. employeeid = orders. employeeid
Note:
Inner join cannot be used to connect memo OLE object single double data type fields.
Join multiple on clauses in a join statement
Syntax:
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
Yes.
Select fields
From Table1 inner join
(Table2 inner join [(] table3
[Inner joer] [(] tablex [inner join]
On table1.field1 compopr table2.field1
On table1.field2 compopr table2.field2
On table1.field3 compopr table2.field3
External connections return more records and keep unmatched records in the results. No matter there are no records that meet the conditions, all records on the other side are returned.
From table [left | right] Join Table2
On table1.field1comparision table. field2
Use the left join to create an external join. All the data in the table on the left of the expression is displayed.
For example, all products are returned regardless of the order quantity.
Select productname, orderid
From Products
Left join orders on products. prductsid = orders. productid
The difference between the right join and the left join is that, no matter whether there are matching records in the left table, it returns all records from the left table.
For example, if you want to know the customer information and calculate the customer distribution in each region, you can use a right connection to return the customer information even if there are no customers in a region.
NULL values do not match each other. You can use an external connection to test whether the fields in a joined table have null values.
Select *
From talbe1
Left join Table2 on table1.a = table2.c
1. Use the IIF function in connection query to display null values with 0 values
IIF expression: IIF (isnull (amount, 0, amout)
For example, whether the order is greater than or less than ￥50, a flag is returned.
IIF ([amount]> 50 ,? Big order ?,? Small order ?)
5. Grouping and summarizing query results
In SQL syntax, the group by and having clauses are used to summarize data. The group by clause specifies which fields are grouped. After grouping records, the having clause is used to filter these records.
Group by clause syntax
Select fidldlist
From table
Where criteria
[Group by groupfieldlist [having groupcriteria]
Note: Microsoft Jet Database cannot group remarks or OLE object fields.
The Null Value in the group by field is used for grouping, but cannot be ignored.
No null value is calculated in any SQL aggregate function.
The group by clause can contain up to ten fields, and the sorting priority is arranged from left to right.
For example, After grouping by title in the employee table of the 'wa 'region, find all titles with the same title with more than 1 employee.
Select title, count (title) as total
From employees
Where region = 'wa'
Group by title
Having count (title)> 1
Accumulation functions in Jet SQL
Significance of Aggregate functions
Sum () summation
AVG () Average Value
Number of records in the count () Expression
Count (*) calculates the number of records
Max
Min minimum
VaR variance
STDev standard error
First Value
Last Value
6. Use parameters to declare and create a parameter query
Syntax declared by parameters:
Parameters name datatype [, name datatype [,…]
Here, name is the identifier of the parameter. You can use the identifier to reference the parameter.
Datatype indicates the Data Type of the parameter.
You must place the parameters declaration before any other statements.
Example:
Parameters [low price] currency, [Beginning date] datatime
Select orderid, orderamount
From orders
Where orderamount> [low price]
And orderdate> = [Beginning date]
VII. Function Query
The so-called function query is actually an operation query, which can perform fast and efficient operations on the database. it selects matching data for the purpose of selecting a query and then processes the data in batches. function query includes update query, delete query, add query, and generate Table query.
1. Update Query
The update clause can change the data in one or more tables at the same time. It can also change the values of multiple fields at the same time.
Update query syntax:
Update table name
Set new value
Where Criterion
For example, the order volume of UK customers increased by 5% and the cargo volume increased by 3%.
Update oeders
Set orderamount = orderamount * 1.1
Freight = freight * 1.03
Where shipcountry = 'U'
2. Delete Query
The delete clause allows you to delete a large amount of outdated or redundant data.
Note: The query object is deleted as a whole record.
Syntax of the delete clause:
Delete [Table name. *]
From source table
Where Criterion
Example: delete all Orders 94 years ago
Delete *
From orders
Where orderdata <#94-1-1 #
3 append Query
The insert clause can append one or more records to the end of one or more tables.
The into clause specifies the table that accepts the new record.
The values keyword specifies the data value included in the new record.
Syntax of the insert clause:
Insetr into target table or query (Field 1, Field 2 ,...)
Values (value 1, value 2 ,...)
Example: Add a customer
Insert into employees (firstname, lastname, title)
Values ('Harry ', 'Washington', 'trainee ')
4. Generate a table Query
All matching records can be copied to a new table at a time. A backup or copy of the records is usually created or serves as the basis of the report.
The select into clause is used to create the query syntax for the generated table:
Select Field 1, Field 2 ,...
Into new table [in external database]
From source database
Where Criterion
For example, create an archive backup for the order
Select *
Into ordersarchive
From orders
8. Joint Query
The union operation combines the results of multiple queries into a single result set for display.
General Syntax of Union operations:
[Table] query 1 Union [all] query 2 Union...
Example: return the names and cities of all suppliers and customers in Brazil
Select companyName, City
From suppliers
Where Country = 'Brazil'
Union
Select companyName, City
From MERs
Where Country = 'Brazil'
Note:
By default, the union clause does not return repeated records. to display all records, add the all option.
The union operation requires that the query has the same number of fields. However, the field data types do not have to be the same.
You can use the group by clause or having clause to group each query parameter. to display the returned data in the specified order, you can use the oreer by clause at the end of the last query.
9. Cross Query
Cross-query can calculate the sum, average, Count, or other sum of data. The data is grouped by two types of information: one is displayed on the left of the table, the other is displayed on the top of the table.
Microsoft Jet SQL uses the transfrom statement to create the cross tabulation query syntax:
Transform aggfunction
Select statement
Group by clause
Struct tfield [IN (value1 [, value2 [,…]) ]
Aggfounction refers to the SQL accumulation function,
Select the field as the title in the SELECT statement,
Group by group
Note:
The field or expression used to create a column title in the query result set. The optional in clause is used to limit its value.
Value indicates the fixed value of the created column title.
For example, the number of orders received by each employee in each quarter of 1996 is displayed:
Transform count (orderid)
Select firstname & ''& lastname as fullname
From employees inner join orders
On employees. employeeid = orders. employeeid
Where datepart ("yyyy", orderdate) = '201312'
Group by firstname & ''& lastname
Order by firstname & ''& lastname
Povot datepart ("Q", orderdate) & 'quarterly'
10. subquery
A subquery can be understood as a set of queries. A subquery is a SELECT statement.
1. Compare the expression value with the single value returned by the subquery.
Syntax:
Expression comparision [any | all | some] (subquery)
Note:
Any and some predicates are synonyms and are used together with comparison operators (=, <, >,< =, >=. returns a Boolean value of true or false. any means that the expression is compared with a series of values returned by the subquery one by one. If a comparison produces a true result, any tests return a true value (both the result of the WHERE clause ), the current record corresponding to this expression will be included in the results of the primary query. the all test requires that the expression and a series of values returned by the subquery produce the true result before returning the true value.
For example, the unit price returned by the primary query is higher than the unit price of any product whose discount is greater than or equal to 25%.
Select * from products
Where unitprice> Any
(Select unitprice from [Order Details] Where discount> 0.25)

2. Check whether the expression value matches a value of a group returned by the subquery.
Syntax:
[Not] In (subquery)
For example, products whose inventory value is greater than or equal to 1000 are returned.
Select productname from products
Where productid in
(Select prdoctid from [Order Details]
Where unitprice * quantity & gt; = 1000)

3. Check whether the subquery returns any records.
Syntax:
[Not] exists (subquery)
Example: Search UK customers with exists
Select companyName, contactname
From orders
Where exists
(Select *
From MERs
Where Country = 'uk 'and
Customers. customerid = orders. customerid)