1.FROM clause
SELECT <*,column [alias],... > from table
SELECT to specify the columns to query
from specifies which table to query from
If you query all columns, you can use the * number after Select
If you are querying only a specific column, you can specify the column name directly after the select and the column names are separated by commas.
2. Using aliases
Using the syntax is the alias of the column followed by the column name, the middle can be added or not add an "as" keyword
If you want to distinguish between uppercase and lowercase characters in an alias, or if the alias contains a character or a space, you must enclose it in double quotation marks
3.WHERE clause
In a SELECT statement, you can use comparison operations in a WHERE clause to limit the results of a query
If the comparison with a number can be caused by single quotation marks, you can also use
If you are comparing data with characters and date types, you must enclose them in single quotation marks
4. Using >,<,>=,<=,!=,<>,=
! = is equivalent to <>
5. Use the And,or keyword
In SQL operations, if you want to return a result that must satisfy more than one condition, you should use the and logical operator to connect the conditions
In SQL operations, if you want to return a result that satisfies one of several conditions, you should use the or logical operator to connect the conditions
6. Using like (fuzzy query)
Comparison operators like to do fuzzy queries
When a user executes a query, the query condition of some information is not fully determined, or only part of the information is known, you can use like to implement
Like requires two wildcard characters:
%: represents 0 to more characters
_: Identifies a single character
These two wildcard characters can be used together to construct flexible matching conditions.
7. Using in and not in
The comparison operator in (list) is used to remove data from the matching list range
The list represents the value lists, and if the column or expression matches any one of the values in the list, the condition is true, and the record is displayed
In can also be understood as a range comparison operator, except that the range is a specified list of values
Not in (list) takes out data records that do not conform to this list
8.BETWEEN ... And ...
Between ... And ... operator to query data that conforms to a range of domain criteria
The most common use is with data ranges of numeric types, but also for data of character types and date type data
9. Applicable is null and is not NULL
A null value of NULL is a special value that cannot be used when comparing the "=" number and must be null otherwise the correct result cannot be obtained.
10. Use the any and all conditions
All and any cannot be used alone and need to be used with single-line comparison operators >, >=, <, <=
>any: Greater than minimum
<any: Less than maximum
>all: Greater than maximum
<all: Less than minimum
>any (3500,4000,4500)
11. Use an expression or function in a query condition
You can use an arithmetic expression on a numeric column (+,-, *,/) when the query requires further evaluation of the selected field
The expression conforms to the default priority of arithmetic, and if you want to change the priority you can use parentheses
Arithmetic operations are mainly for numeric types of data, the date type of data can be added and minus operations, indicating that a date is added or minus a number of days
12. Use distinct to filter duplicates
There are rows in the data table that may store the same data, and when you perform a query operation, all rows are displayed by default, regardless of whether the query results have duplicate data
When data duplication does not make sense, it is often necessary to remove duplicate values and use distinct to implement
13. Using the ORDER BY clause
Sort the data by a certain rule, using the ORDER BY clause
The last clause that must appear in the Select
14.ASC and DESC
ASC: Default item, ascending order;
DESC: Descending order
The null value is the largest, and then in ascending order, in the last, descending order, the first
15. Sorting Multiple columns
When multiple columns are used as sorting criteria, the first column is sorted first, if the first column of data is the same, then the second column is sorted, and so on
When sorting in multiple columns, each column needs to be sorted separately, either in the positive or reverse order
SQL (Basic query)