Comprehensive exposure to SQL syntax (5)

BETWEEN...AND operator

determines whether a certain value is within a specific range. 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 calculated.
value1,value2
the value range specified.
For example:
If you want to query all employees between the ages of 25-30 from the employee table, you can use the following program.
SELECT name, age BETWEEN 25 AND 30
FROM staff table;



LIKE operand

Used to combine a string with another specific character Compare string patterns (pattern) and filter out records that match the string pattern.


expression LIKE "pattern"
expression
is used in WHERE conditional clauses and SQL expressions.
pattern
String pattern used for comparison.
For example:
If you want to query all the surnames starting with "Li", you can use the following formula.
Like "李*"
Multiple examples of LIKE operands:
1, multiple characters:
(1)"a*a"
Filterable: "aa"," aBa", "aBBBa", cannot be filtered: "aBC"
(2)"*ab*"
can be filtered: "abc", "AABB", "Xab", cannot be filtered: "aZb"," bac"
2, special characters:
"a"*"a"
can be filtered: "a*a", cannot be filtered: "aaa"
3, single character:
" a?a"
can filter: "aaa", "a3a", "aBa", cannot filter: "aBBBa"
4, single number:
"a#a"
can filter: "a0a", "a1a", "a2a", cannot filter: "aaa", "a10a"
5, character range:
""a-z""
can filter: "f", "p ","j", cannot filter: "2","&"
6. Specify the range beyond characters:
""!a-z""
7. Specify non-digits:
"" !0-9””
Can filter: "A", "a", "&", "~", cannot filter: "0", "1", "9"
8, combined structure ：
"a"!b-m"#"
Can filter: "An9", "az0", "a99", cannot filter: "abc", "aj0"



SQL numeric function

1. AVG: arithmetic mean

AVG(expr)

expr

Field name or expression.


For example:

If you want to calculate the average height of employees whose height exceeds 165 cm, you can use the following SQL statement to complete.

　SELECT Avg(height)

　AS average height

　FROM staff table WHERE height>165;

　2. COUNT: Calculate the number of records

　COUNT(expr)

　expr


　Field name or expression.

For example:

If you want to count the number of employees in the business department and query the names of the employees, you can use the following program.

SELECT Count (name) AS Staff name

FROM Staff table

WHERE Department name = 'Business Department';

3. FIRST and LAST: Returns the first and last data of a field.

　FIRST(expr)


　LAST(expr)

　expr

　Field name or expression.

For example:

If you want to find the first data in the product quantity field and the last data in the product price field, you can use the following query method.

SELECT FIRST (item quantity), LAST (item price)


FROM order form

4. MAX, and MIN: return the maximum value and the maximum value of a field minimum value.

Usage is the same as FIRST and LAST.

5. SUM: Returns the sum of a specific field or operation.

　SUM(expr)

　expr

　Field name or expression.


For example:

To calculate the total price of the goods, use the following procedure.


SELECT

Sum (unit price * quantity of goods)

AS total price of goods FROM order form

Multi-layer SQL query

As the name suggests, the purpose of multi-layer SQL query is: "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
The operation of comparing an expression with the result of the inner query.
expression
Expression for searching the results of the inner query.
sqlstatement
is a SQL query composed of a SELECT statement, and the statement must be enclosed in ().



For example:

We first query all the units from the order form, and then compare the units in the product table one by one to query all records with unit prices higher than those in the order form.

SELECT * FROM product form

WHERE unit price>ANY (SELECT unit price FROM order form WHERE discount>=.25);

The above is full contact with SQL Grammar (5) content, for more related content, please pay attention to the PHP Chinese website (www.php.cn)!