SQL statement example

SQL refers to the structured query language. Its main function is to establish connections with various databases for communication. query refers to requests for data stored in SQL. The query task is to provide the result set of the Select statement to the user. The Select statement retrieves data from the SQL statement 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). Optional: ALL, TOP
* ---------> Specify all fields (columns) in the table ).
Table -----> specifies the table name.
Field -----> specifies the name of the field (column) in the table.
[AS alias]-replace the alias of the actual field (column) name in the table.
[INTO new_table_name] --> Create a new table and its name.
Tableexpression ----> table name.
[Group by...] indicates grouping with the value of this field
[Order by...] indicates sorting in ascending ORDER, and selecting DESC in descending ORDER;
------------------------------------------------------------
1 select Columns
------------------------------------------------------------
SQL statement input in access
(1) Select "query" --> New --> default design view --> click OK
(2) Close the "display table dialog box"
(3) Select "View"> "SQL View" from the menu bar to enter an SQL statement.


Example 1_1 _ select all fields
Select *
FROM useres;
Example 1_2 _ select some fields
Select user_name, real_name, submit_date
FROM useres;
Example 1_3 query fields in two tables
Select information table of books. Bar Code of books, borrowing information table. Bar Code of books
FROM book info table, borrow book info table;

Example:
Through the simple example above, we can see that
(1) Select clause selection list, which indicates the fields (columns) and their attributes in the query result set. When selecting all columns, use wildcard functions *. When selecting some columns, use commas to separate them.
(2) The FROM clause specifies the name of the table to be queried. Multiple tables must be separated by commas (,).
------------------------------------------------------------
2 TOP: number of returned records
------------------------------------------------------------
Example 1_4 _ number of returned records
Select TOP 3 *
FROM useres;
-------------------------------------------------------------
3 AS-derived new field
-------------------------------------------------------------
Example ‑ 5_derived New Field
Select user_name, (submit_date + 30) AS new_date
FROM useres;
------------------------------------------------------------
4 Where: specify conditions for filtering
------------------------------------------------------------
In the example, wait 6 to find the specified record
Select *
FROM useres
Where useres. real_name = "red ";
Example 7: persons older than 30
Select *
FROM useres
Where age> 30
We can see from the above that operators are used for filtering based on conditions. Common operators are as follows:
1 comparison operator
= Equal
<> Not equal
> Greater
<Less
<= Less than or equal
> = Greater than or equal
2 logical operators
If ALL conditions are true, true is returned.
AND returns true if both conditions are true.
OR returns true if one of the conditions is true.
NOT logarithm
Returns true if one of all ANY conditions is true.
BETWEEN returns true if the operand is within the specified range.
Returns true if the operand is equal to one of the expressions.
LIKE returns true if the operand matches the pattern.
SOME returns true if SOME are true in a series of comparisons.
Example: register a user before October 8
Select *
FROM useres
Where submit_date <#2004-12-30 #
Example 9_user registration in a certain period of time
Select *
FROM useres
Where submit_date BETWEEN #2004-1-1 # AND #2005-5-1 #
Example comment 10_search by keyword
Select *
FROM useres
Where useres. real_name LIKE "* li *"
------------------
Difference between IN and OR
-----------------
Record IN the sample _ IN Filter field
Select real_name, submit_date
FROM useres
Where real_name In ("Xiao Li", "Xiao Zhang ")
Example _ OR filter records in the field
Select real_name, submit_date
FROM useres
Where real_name = "Xiao Li" or real_name = "Xiao Zhang"
------------------------------------------------------------
5 group by grouping result set
------------------------------------------------------------
Example of 201712_groupby grouping result set
Select sex, SUM (age) AS age SUM
FROM useres
Group by useres. sex
Order by sum (age) DESC;
Example:
Group the new "field" age SUM "by the record under field" sex.
Order by... DESC is used to specify the ORDER in descending ORDER
In this example, sum is an aggregate function in SQL (operate on a group of values and return a single aggregate value). Below are some commonly used Aggregate functions:
1 SUM function
Format:
SUM ([ALL | DISTINCT] expression)
Parameters:
ALL evaluates the sum of ALL values. The default value is ALL.
Duplicate items are excluded when DISTINCT calculates the sum.
Expression value or expression, which can be a variable, field, function, etc.
2 AVG average Function
Format:
AVG ([ALL | DISTINCT] expression)
Parameters:
ALL calculates the mean for ALL values. The default value is ALL.
Duplicate items are excluded when DISTINCT calculates the average.
Expression value or expression, which can be a variable, field, function, etc.
The 3 MIN and MAX functions calculate the minimum and maximum values respectively. The format is similar to the above.
4 COUNT row counting function
Format:
COUNT ({[ALL | DISTINCT] expression | *})
ALL indicates that ALL items except NULL are calculated, which is the default option.
DISTINCT indicates the number of unique non-null values returned by COUNT.
Expression is an expression and cannot be txte, image, ntxt, or uniqueidentifier data.
Example 13_avg evaluate the average Function
Select sex, AVG (age) AS age AVG
FROM useres
Group by useres. sex
Order by avg (age) DESC;
Example 14_count: number of returned records
Select COUNT (*)
FROM useres
Example 15_number of records grouped by gender
Select sex, COUNT (*)
FROM useres
Group by sex;
------------------------------------------------------------
6 DISTINCT remove duplicate records from tail
------------------------------------------------------------
Select DISTINCT real_name
FROM useres
------------------------------------------------------------
7. Combined Query
------------------------------------------------------------
When you need to query from multiple tables, you can use the Combined Query
Select useres. real_name, logtime. log_time
FROM useres, logtime
Where (useres. real_name) = [logtime]. [real_name]);