SQL (Basic query)

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)