Basic syntax for SQL statements _mssql

Basic syntax for SQL statements
Yin Hong Write, really endure do not paste here as a view of the information.
Because the original text is written in Word, the paragraph is a bit messy.


the complete syntax for a 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
A partial representation enclosed in brackets ([]) is optional, and the part enclosed in braces ({}) is a representation of the one that must be selected from.

A. 1 FROM clause
The FROM clause specifies the source of the fields in the SELECT statement. The FROM clause is followed by one or more expressions, separated by commas, where the expression can be a single table name, a saved query, or a compound result from a INNER join, a left join, or a right join. If a table or query is stored in an external database, indicate its full path after the IN clause.
Example: The following SQL statement returns all customers with orders:
Select Orderid,customer.customerid
From Orders Customers
Where Orders.customerid=customers.customeersid

one. 2 All, DISTINCT, Distinctrow, top predicates
(1) All returns all records that satisfy the conditions of the SQL statement. If this verb is not specified, all is the default.
Example: Select all Firstname,lastname
From Employees
(2) DISTINCT If there are multiple records with the same data in the selection field, only one is returned.
(3) Distinctrow If there is a duplicate record, only return a
(4) Top display Query the tail of several records. You can also return the percentage of the record by using the top N percent clause (where N represents a percentage)
Example: Returns 5% orders with the largest order amount
Select Top 5 percent*
from [Order Details]
Order by unitprice*quantity* (1-discount) DESC

one. 3 Alias a field with the AS clause
If you want to get a new caption for the returned column, or after you calculate or summarize a field, a new value is generated that you want to put in a new column display, then keep it as.
Example: Returns the FirstName field with an alias of nickname
Select FirstName as nickname, LastName, City
From Employees
Example: Returns a new column showing inventory value
Select ProductName, UnitPrice, UnitsInStock, Unitprice*unitsinstock as Valueinstock
From Products

two. Where clause specify query criteria

Two. 1 comparison operators
comparison operator meaning
= equals
> Greater than
< less than
>= is greater than or equal to
<= less than or equal
<> Not equal to
!> not greater than
!< not less than
Example: Return to January 96 order
Select OrderID, CustomerID, OrderDate
From Orders
Where orderdate> #1/1/96# and orderdate< #1/30/96#
Attention:
Mcirosoft JET SQL, the date is bounded by ' # '. Dates can also be replaced with the DateValue () function. When comparing character data, add single quotes ', trailing spaces are ignored in the comparison.
Cases:
Where orderdate> #96 -1-1#
Can also be expressed as:
Where orderdate>datevalue (' 1/1/96 ')
Negates using the not expression.
For example: View orders after January 1, 96
Where not orderdate<= #1/1/96#

Two. 2 Range (BETWEEN and not BETWEEN)
BETWEEN ... And ... operator specifies a closed interval to search for.
Example: Return the order from January 96 to February 96.
Where OrderDate Between #1/1/96# and #2/1/96#

Two. 3 list (in, not in)
The in operator is used to match any one of the values in the list. The IN clause can replace a series of conditions that are joined by an OR clause.
To find all the customers who live in London, Paris or Berlin
Select CustomerID, CompanyName, ContactName, city
From Customers
Where City in (' London ', ' Paris ', ' Berlin ')

Two. 4 pattern matching (like)
The like operator verifies that a field value that contains string data matches a specified pattern.
Wildcard characters used in the LIKE operator
Wildcard meaning
？ Any single character
* Characters of any length
# 0~9 a single digit between
[Character List] any of the values in the character lists
[！ Character List] is not in any of the values in the character lists
-Specify a range of characters, with values on both sides being their upper and lower bounds
Example: Returning a customer with a postal code between (171) 555-0000 to (171) 555-9999
Select CustomerID, Companyname,city,phone
From Customers
Where Phone like ' (171) 555-#### '
Some styles and meanings of the LIKE operator
Style meaning does not match
Like ' A * ' followed by any length of character bc,c255
Like ' 5
' 5*5 555
There is any character between like ' 5?5 ' 5 and 5 55,5wer5
Like ' 5# #5 ' 5235,5005 5kd5,5346
Any character between like ' [a A-z] ' A-Z 5,%
Any of the characters like ' [!0-9] ' not 0-9 0,1
Like ' [[] ' 1,*

Three. Sort results with an ORDER BY clause
The order clause sorts query results by one or more (up to 16) fields, either ascending (ASC) or Descending (DESC), and the default is ascending. The order clause is usually placed at the end of the SQL statement.
Multiple fields are defined in the order clause, sorted by field.
Cases:
Select Productname,unitprice, Unitinstock
From Products
ORDER by Unitinstock DESC, UnitPrice DESC, ProductName
You can mix field names and location numbers by using the position number of the field in the select list instead of the field name in the ORDER BY clause.
Example: The following statement produces the same effect as the above.
Select Productname,unitprice, Unitinstock
From Products
ORDER by 1 DESC, 2 desc,3

Four. Using connection relation to realize multi-table query
Example: Find out the names of suppliers and customers in the same city
Select Customers.companyname, Suppliers.ComPany.Name
From Customers, suppliers
Where customers.city=suppliers.city
For example: Find products and orders that have a product inventory larger than the order number of the same product
Select Productname,orderid, Unitinstock, Quantity
From products, [order Deails]
Where Product.productid=[order Details]. ProductID
and unitsinstock>quantity
Another approach is to use the Jnner JOIN that is unique to Microsof JET SQL
Grammar:
From table1 INNER JOIN table2
On Table1.field1 comparision table2.field2
Where comparision is the comparison operator used in the previous where clause.
Select firstname,lastname,orderid,customerid,orderdate
From Employees
INNER JOIN Orders on Employees.employeeid=orders.employeeid
Attention:
INNER join cannot connect to the memo OLE Object single Double data Type field.
To concatenate multiple on clauses in a join statement
Grammar:
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
can also
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 joins return more records, leaving mismatched records in the results, regardless of whether a record that satisfies the condition does not exist to return all records on the other side.
from table [left| Right]join table2
On Table1.field1comparision table.field2
To establish an external connection with left JOIN, the table on the left side of the expression shows all of its data
For example: Return all merchandise, regardless of order volume
Select ProductName, OrderID
From Products
Left JOIN Orders on Products.prductsid=orders.productid
The difference between a right and a left connection is that it returns all records from the table on the left, regardless of whether there is a matching record in the left.
For example: If you want to know the customer's information and count the distribution of customers in each region, you can use a right connection, even if there is no customer in a region, to return customer information.
Null values do not match each other, and you can use an outer join to test whether the fields of one of the connected tables have null values.
Select *
From Talbe1
Left JOIN table2 on table1.a=table2.c

Four. 1 Connection queries use the IIF function to display null values in 0 values
IIf expression: IIf (IsNull (amount,0,amout)
For example: if the order is greater than or less than ￥50, return a flag.
Iif ([Amount]>50,? Big Order?,? Small order?)

Five grouping and summarizing query results
In the syntax of SQL, GROUP by and having clauses are used to summarize data. The GROUP BY clause indicates which fields to group by, and the records are filtered by the HAVING clause after grouping them.
The syntax of the GROUP by clause
Select fidldlist
From table
Where criteria
[GROUP by Groupfieldlist [GroupCriteria]]
Note: Microsoft Jet database jet cannot group notes or OLE Object fields.
The null value in the Group by field is for grouping but cannot be omitted.
Null values are not computed in any SQL aggregate function.
The GROUP by clause can have up to 10 fields, sorted in order of precedence, from left to right.
For example: After grouping by title in the employee table in the ' WA ' region, find all titles with an equal title of more than 1 employees.
Select Title, Count (Title) as Total
From Employees
Where Region = ' WA '
GROUP by Title
Having Count (Title) >1
Accumulating functions in JET SQL
The meaning of aggregate function
SUM () sum
AVG () Average
Number of records in COUNT () expression
COUNT (*) calculates the number of records
Max Max value
Min min value
var variance
STDEV standard Error
First value
Last value

VI Create a parameter query with parameters declaration
Syntax for parameters declarations:
PARAMETERS name Datatype[,name datatype[, ...]
Where name is the identifier for the parameter, you can reference the parameter by a marker.
DataType describes the data type of the parameter.
Use to place the parameters declaration before any other statement.
Cases:
Parameters[low Price] Currency,[beginning date]datatime
Select OrderID, OrderAmount
From Orders
Where Orderamount>[low Price]
and orderdate>=[beginning Date]

Seven function query
The so-called function query, in fact, is a kind of operation query, it can be a fast and efficient operation of the database. It selects the eligible data and then batches the data in order to select the query. Feature queries include update queries, delete queries, add queries, and make-table queries.
Seven. 1 Update query
The UPDATE clause can change 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 criteria
Example: British customers increased the volume of orders by 5%, freight volume increased by 3%
Update oeders
SET OrderAmount = OrderAmount *1.1
, Freight = freight*1.03
Where ShipCountry = ' UK '

Seven. 2 delete query
The DELETE clause allows the user to delete a large number of obsolete or redundant data.
Note: The object that deletes the query is the entire record.
Syntax for DELETE clause:
Delete [table name. *]
From source table
Where criteria
To delete all orders that were 94 years old
Delete *
From Orders
Where orderdata< #94 -1-1#

Seven. 3 Append Query
An INSERT clause can append one or a group of records to the tail of one or more tables.
INTO clause specifies the table to accept the new record
The values keyword specifies the data value that the new record contains.
Syntax for INSERT clause:
Insetr into destination table or query (field 1, Field 2,...)
Values (value 1, value 2,...)
Example: Adding a Client
Insert into Employees (firstname,lastname,title)
ValueS (' Harry ', ' Washington ', ' trainee ')

Seven. 4 Generate Table Query
You can copy all the records that meet the criteria at once to a new table. Usually make a backup or copy of a record or a basis for a report.
The Select into clause is used to create a build table query syntax:
Select Field 1, Field 2,...
into a new table [in external database]
From source database
Where criteria
Example: Make an archive backup for an order
Select *
Into ordersarchive
From Orders

Eight joint enquiries
The Union operation can combine the results of multiple queries into a single result set.
General syntax for union operations:
[Table] Query 1 union [all] Query 2 union ...
Returns the names and cities of all suppliers and customers in Brazil
Select companyname,city
From suppliers
Where Country = ' Brazil '
UNION
Select companyname,city
From Customers
Where Country = ' Brazil '
Note:
By default, the Union clause does not return duplicate records. If you want to show all the records, you can add the All option
The Union operation requires that the query have the same number of fields. However, the field data types do not have to be the same.
You can group by using either the GROUP BY clause or the HAVING clause in each query parameter. To display the returned data in a specified order, you can use the Oreer by clause at the end of the last query.

Nine-handed * enquiry
A * * query can calculate the sum, average, count, or other sum of data, grouped by two types of information: one displayed at the left of the table and the other at the top of the table.
Microsoft Jet SQL creates the cross-table query syntax with the TRANSFROM statement:
TRANSFORM aggfunction
Select statement
GROUP BY clause
PIVOT pivotfield[in (value1 [, value2[,...]])]
Aggfounction refers to the SQL accumulation function,
The SELECT statement selects the field as the caption,
GROUP BY group
Description
Pivotfield the field or expression used to create a column header in a query result set, limiting its value with an optional in clause.
Value represents a fixed value for creating a column header.
Example: shows the number of orders received by each employee in each quarter in 1996 years:
TRANSFORM Count (OrderID)
Select firstname& ' &lastname as FullName
From Employees INNER JOIN Orders
On employees.employeeid = Orders.EmployeeID
Where DatePart ("yyyy", OrderDate) = ' 1996 '
GROUP by firstname& ' &lastname
ORDER by firstname& ' &lastname
Povot DatePart ("Q", OrderDate) & ' quarter '

Ten. Child query
Subqueries can be interpreted as nested queries. A subquery is a SELECT statement.

10.1 The value of the expression is compared with the single value returned by the subquery
Grammar:
Expression comparision [any| all| SOME] (subquery)
Description
The any and some predicates are synonyms and are used with comparison operators (=,<,>,<>,<=,>=). Returns a Boolean value of true or false.any meaning that the expression is compared to a series of values returned by the subquery. Whenever one of the comparisons yields a true result, the any test returns a true value (both the result of the WHERE clause) and the current record of the expression is entered in the result of the main query. The all test requires the expression to have a true result from a comparison of a series of values returned by the subquery, returning a true value.
Example: The main query returns all products with a unit price higher than the price of any product with a discount greater than or equal to 25%
Select * FROM Products
Where Unitprice>any
(Select UnitPrice from[order Details] Where discount>0.25)

10.2 checks whether the value of an expression matches a value of a set of values returned by a subquery
Grammar:
[NOT] In (subquery)
Example: Returns a product with an inventory value greater than or equal to 1000.
Select ProductName from
Where ProductID in
(Select prdoctid from [order DEtails]
Where unitprice*quantity>= 1000)

10.2 Detect whether subqueries return any records
Grammar:
[NOT] EXISTS (subquery)
Example: Using exists to retrieve British customers
Select Companyname,contactname
From Orders
Where EXISTS
(Select *
From Customers
Where Country = ' UK ' and
Customers.customerid= Orders.CustomerID)