Simple SQL query

Using C #, PHP, Java, and other development tools, I feel that SQL is very important. No matter whether it is functional or performance, we need to keep improving on the database, at this time, programmers are particularly important in understanding SQL statements. Many times, if they do not know some keyword queries, they only know simple select and connection queries. Maybe when doing something, only one multi-table join query is required to solve the problem. It is very cumbersome to write a part of the query first, and then create a loop, after n SQL statements are generated based on the query and N result sets are queried, or to avoid repeating all queries, the repeated records are eliminated in related languages. If you are still at this level, I suggest you study SQL in depth.

This week I read the SQL full manual and read about the first eight chapters. Many of them are commonly used and have no technical content, but some details are ignored. I have sorted out some details, but they are not very detailed for future queries. I also hope to help others on the blog.

　　

Duplicate record (distinct)

Select distinct field from table_name can avoid repeated field fields.

It can also be the default select all from...

 

Search Condition (where)

Comparison Test: =, <>, <,>, <=,> =

In SQL's 3-value logic, a search condition can generate true, false, or null values. Only those records whose search conditions generate true are included in the query results.

 

Range Test ():

Select field from table_name (not) between... and...

A between B and C = (A> = B) and (A <= C)

Between testing is a simple method when search criteria are considered for the range of values.

Group member testing (in)

It tests whether a data value matches one of a set of target values.

Eg: Select field from table_name where field (not) in (value1, value2, value3)

Like the between test, the in test does not increase the SQL expression capability:

X in (a, B, c) = (x = A) or (x = B) or (x = C)

Pattern Matching Test (like ):

Wildcard characters:

1.% matches 0 or more characters in any order

2. _ match any single character

Escape characters:

Sometimes the condition to be queried contains the SQL built-in pattern matching characters, which need to be expressed using the transfer character. In this case, you need to use escape to customize the escape character:

Select * From table_name where field like 'A $ % BC % 'escape '$'

 

Null Value Test (is null ):

Select * From table_name where field is null;

Compound search conditions (and, or, and not)

Order ):

Select * From table_name order by field1, field2...

The first sort item (field1) is the primary sort key, followed by the secondary sort key. When the two query results have the same value at the primary sort key, sort by secondary sort key.

Specify ascending or descending order:

Desc ascending (default)

ASC sort in descending order

Select field1, field2, (field3-field2) from table_name order by field1 ASC, 3 DESC

// The third field is sorted in descending order, and the first field (field1) is sorted in ascending order.

 

Union)

You need to combine two or more query results into a query result table.

Select * From table_name where condition1 Union select * From table_name2 where condition2

There are several important restrictions on a table composed of a union operation:

1. The two tables must contain the same number of fields.

2. The data type in the first table must be the same as that in the second table.

3. Neither of the two tables can be ordered by the order by clause. However, the combined query results can be sorted.

The union operation eliminates repeated records. You can specify the all keyword after the Union keyword.

The 'ORDER BY' clause cannot appear in two select statements combined by the union operation. You can use 'ORDER BY' after the second SELECT statement. However, the fields generated by the union operation are not named. The order by clause must be sorted by Field Numbers.

Select * From table_name where condition1 Union all select * From table_name2 where condition2 order by 1, 2

You can use multiple union:

A union (B Union C)

A (Union B) Union C

(A union c) Union B