SQL statement Sample _mssql

SQL means structured Query language, whose main function is to communicate with various databases. A query refers to a request for data stored in SQL. The task that the query completes is to provide the result set of the Select statement to the user. The Select statement retrieves data from SQL and returns it to the user in the form of one or more result sets.
==========================================================
Select BASIC Syntax structure
==========================================================
select[predicate]{*|table.*|[ Table.]] field [, [table.] field2[,...]}
[As ALIAS1 [, alias2[,...]]]
[Into New_table_name]
From Tableexpression [, ...]
[Where ...]
[GROUP by ...]
[Order BY ...] [ASC | DESC]]

predicate--> Specifies the number of returned records (rows), optionally: All,top
*---------> Specify all Fields (columns) in the table.
Table-----> Specify the name of the table.
Field-----> Specify the names of the fields (columns) in the table
[As alias]-Aliases the actual field (column) name in the representation.
[Into new_table_name]--> creates a new table and name.
Tableexpression----The name of the > table.
[GROUP by ...] Represents a group of values in this field
[Order BY ...] The expression is in ascending order, descending to select DESC;
------------------------------------------------------------
1 Select columns
------------------------------------------------------------
How SQL statements are entered in Access
(1) Select "Query"--> new--> Default Design view--> Click OK
(2) Close the Show Table dialog box
(3) In the menu bar select "View"--->sql view, you can enter the SQL statement


Example 1_1_ Select all fields
Select *
From Useres;
Example 1_2_ Select a partial field
Select user_name,real_name,submit_date
From Useres;
Example 1_3 query for fields in two tables
Select Book information table. Book Bar code, library Information table. Book Barcode
From book information table, library information table;

Sample interpretation:
With the simple example above, we realize that
(1) SELECT clause selects a list that indicates the fields (columns) and their attributes contained in the query result set, and selects all columns with a wildcard *, separated by commas when selecting partial columns
(2) The FROM clause indicates the table name of the query, separated by commas when specifying multiple tables
------------------------------------------------------------
2 top Specifies the number of records returned
------------------------------------------------------------
Example 1_4_ returns the number of records
Select Top 3 *
From Useres;
-------------------------------------------------------------
3 as derived new field
-------------------------------------------------------------
Example 1_5_ derive a new field
Select user_name, (submit_date+30) as New_date
From Useres;
------------------------------------------------------------
4 Where to specify criteria for filtering
------------------------------------------------------------
Example 1_6 equals sign find specified record
Select *
From Useres
Where useres.real_name= "Red Red";
Example 1_7 who is older than 30
Select *
From Useres
Where age>30
As you can see from the above, operators are used when filtering according to criteria, and the common operators are as follows:
1 comparison operators
= equals
<> Not equal to
> Greater than
< less than
<= less than or equal
>= is greater than or equal to
2 logical operators
All returns true if all conditions are true
Returns true if both and two conditions are true
Returns True if OR has a condition of true
Not pair value counter
Any of all conditions returns True if one is true
BETWEEN returns True if the operand is within the specified range
Returns true if the operand equals one of the expressions
Like if the operand matches the pattern, returns true
SOME returns true in a series of comparisons, some of which are true
Example 1_8_ registered User a day ago
Select *
From Useres
Where submit_date< #2004 -12-30#
Example 1_9_ a period of time to register a user
Select *
From Useres
Where submit_date BETWEEN #2004 -1-1# and #2005 -5-1#
Example 1_10_ Search by keyword
Select *
From Useres
Where useres.real_name like "* Lee *"
------------------
In and or differences
-----------------
Example _in a record in a filtered field
Select real_name,submit_date
From Useres
Where real_name in ("Xiao Li", "Xiao Zhang")
Example _or a record in a filtered field
Select real_name,submit_date
From Useres
Where real_name= "Xiao Li" or real_name= "Xiao Zhang"
------------------------------------------------------------
5 GROUP BY grouped result sets
------------------------------------------------------------
Example 1_12_groupby grouping result sets
Select sex sum of sum (age) as age
From Useres
GROUP by Useres.sex
Order by SUM (age) DESC;
Sample interpretation:
Group the new field "Age sum" by the record under field "sex".
ORDER BY ... Desc used to specify descending order
The sum in this example is the aggregate function in SQL (manipulating a set of values to return a single total value), and the following are several common aggregate functions:
1 Sum sum function
Format:
SUM ([all| DISTINCT] expression)
Parameters:
All to sum all values, default to all
DISTINCT to exclude duplicates when sum is obtained
expression values or expressions, can be variables, fields, functions, etc.
2 AVG value function
Format:
AVG ([all| DISTINCT] expression)
Parameters:
All averages for all values, default to all
DISTINCT to exclude duplicates when seeking an average
expression values or expressions, can be variables, fields, functions, etc.
The 3 min and Max functions are for minimum and maximum values, and are similar in format to the above.
4 Count Line counting function
Format:
COUNT ({[all| DISTINCT] expression|*})
All represents the calculation of items other than NULL, which is the default option
DISTINCT indicates that count returns the number of unique non-null values
Expression is an expression and cannot be a txte,image,ntxt and uniqueidentifier type of data.
Example 1_13_avg averaging function
Select Sex, avg (age) as the AVG
From Useres
GROUP by Useres.sex
Order by AVG (age) DESC;
Example 1_14_count returns the number of records
Select COUNT (*)
From Useres
Example 1_15_ the number of records by sex
Select Sex, COUNT (*)
From Useres
GROUP by sex;
------------------------------------------------------------
6 distinct to remove duplicate records from tail
------------------------------------------------------------
Select DISTINCT Real_name
From Useres
------------------------------------------------------------
7 Combination Query
------------------------------------------------------------
When you need to query from more than one table, you can use a combination query
Select Useres.real_name, Logtime.log_time
From Useres, LogTime
Where ((useres.real_name) =[logtime].[ Real_name]));