Full access to SQL syntax (5)

SQL Syntax BETWEEN ... And operator

Determines whether a person's numeric value is within a specific range, and this operator can only be used in SQL statements.


Expr[not]between value1 and value2
Expr
Specifies the combination of fields and expressions to be evaluated.
Value1,value2
The range of values specified.
For example:
If you want to check out all employees aged 25-30 years from the staff form, you can use the following procedure.
SELECT name, age BETWEEN and 30
from staff form;


　
Like operand

Used to compare a string to another particular string style (pattern) and filter the records that match the string style.


Expression like "pattern"
Expression
Use in the WHERE Condition clause, the SQL expression.
Pattern
The string style used to compare.
For example:
If you want to check out all the names that are headed by "Li", you can use the following formula.
Like "Li *"
Multiple examples of like operands:
1. Multiple characters:
(1) "A*a"
Filter: "AA", "ABa", "Abbba", Can not be screened: "ABC"
(2) "*ab*"
Filter: "ABC", "AABB", "Xab", Can not be screened: "Azb", "BAC"
2, special characters:
"A" * "a"
Filter: "A*a", Cannot filter: "AAA"
3, single character:
"A?a"
Can be screened: "AAA", "a3a", "ABa", Can not be screened: "Abbba"
4. Single digit:
"A#a"
Can be screened: "a0a", "A1A", "A2A", cannot be screened: "AAA", "a10a"
5, Character range:
"" A-Z ""
Filter: "F", "P", "J", Can not be filtered: "2", "&"
6, the specified character to the external scope:
""!a-z ""
7. Specify non-digit:
""!0-9 ""
Filter: "A", "a", "&", "~", Can not filter: "0", "1", "9"
8. Modular Structure:
"A"!b-m "#"
Filter: "An9", "az0", "A99", Cannot filter: "ABC", "Aj0"



SQL number function

1, AVG: arithmetic average

AVG (expr)

Expr

The name of the field or expression.


For example:

To calculate the average height of a staff member over 165 cm tall, you can use the following SQL statement to complete it.

SELECT AVG (height)

As average height

From staff form WHERE height >165;

2. Count: Count the number of record bars

COUNT (expr)

Expr


The name of the field or expression.

For example:

If you want to count the number of staff in the business unit and check out the name of the employee, you can use the following procedure.

SELECT Count (name) as Employee name

From staff form

WHERE Department name = ' business unit ';

3, First and last: Returns a field of data with the final data.

A (expr)


Last (expr)

Expr

The name of the field or expression.

For example:

If you want to find the first piece of data in the Quantity field and the last piece of data in the Price field, you can use the following query method.

SELECT A (number of items), last (item price)


From order Form

4, Max, and min: Returns the maximum and minimum values for a field.

Use the same as the one and last.

5, sum: Returns the sum value of a particular field or operation.

SUM (expr)

Expr

The name of the field or expression.


For example:

To calculate the total price of a product, you can use the following procedure.


SELECT

Sum (Unit Price * Number of items)

As goods price from order form

Multi-layer SQL query

As the name implies, the multi-level SQL query is: "In one SQL statement can contain another SQL query statement, forming an internal nested query type." ”

comparison[any| all| SOME] (SQLStatement)
Expression[not]in (SQLStatement)
[NOT] EXISTS (SQLStatement)
Comparison
An operation that compares an expression to the result of an inner query.
Expression
An expression that searches for the results of a query on the inner layer.
SQLStatement
The SQL query that is composed of the SELECT statement must be surrounded by ().



For example:

We first query all the units from the order form, then compare the units in the product table with the one by one, and query all the records of the unit price above the order form.

SELECT * from Product form

Where unit price >any (SELECT unit price from order Form WHERE discount >=.25);