SQL select statements we used in those years

We all know that there are nine SQL statements in the world of SQL, and the most complex of these statements is our SELECT statement, which is the most used statement in our database, therefore, if we have mastered the SELECT statement, we will have at least half of the SQL language. So please follow our introduction to learn the SELECT statement.

When we look for something, the first strategy we adopt is to know where it is. When we think about where it is, it is very easy to find it. The same principle is that when we perform data retrieval, we must know where the required data is stored. Of course, our SELECT statement can contain multiple query statements and can be nested with select statements.

First, let's take a look at the search that the SELECT statement can be used:

SQL Available queries	All rows and columns;
	All rows and specific columns
	Rows with a limited range
	Hide rows with repeated values
	The row that matches a group of values.
	Rows retrieved Based on unknown values
	Rows retrieved based on multiple search criteria

Next we will begin to understand the basic structure of the SELECT statement:

Select [All | distinct] select_list [into [new_table_name] from {table_name | view_name} [[, {table_name2 | view_name2 }[..., {Table_name16}] [where search_conditions] [group bygroup_by_list] [having search_conditons] [order
By order_list [ASC | DESC]

This is the simplified select structure, which is not the standard structure in SQL statements. The simplified structure is what we often use. But how can we understand so many English names? Don't worry. Let me explain it one by one.

Select statement -- sentence

The Select clause specifies the columns that need to be returned from the same multi-query table. Its syntax is: select [All | distinct] [Top N] <select_list>
All	Indicates that columns with the same value can be displayed in the query structure. It is the default value of the system.	Differences between all and distinct It means that all can be displayed the same And it is a system Default, but distinct does not The same value is displayed. Only show one of them.
Distinct	Indicates that if a column with the same value exists in the query result, only one column is displayed. For the distinct option, the null value is considered to be the same value;
Top N [percent]	Specify the first n rows of data to be returned. If the percent keyword is specified, Returns the first n rows of data in the query result.
Select_list	List of fields to be queried. Field names are separated by commas (,). A table can contain both the field names in the data source table or view and other expressions. If "*" is used, the system returns all fields in the data table.

Of course, we have other clauses. Next we will introduce each clause by category.

Into clause:

Into clause: used to store query results in a new table.
Syntax: into new_table	New_table specifies the name of the new table. Columns specified in the SELECT statement
	The data rows in the new table are specified by the WHERE clause if The Select clause specifies the calculation column, which corresponds Is not a calculation column, but a data in the column actually stored in the table is calculated by the select_into syntax.

Union clause:

Union clause: combines two or more query results into a result set. It is different from the use of join queries to merge columns in two tables, Merging query results using the Union operator requires two basic rules: the tree and sequence of columns must be consistent in all queries; Data Type must be compatible

Syntax: Select statement Union [all] Select statement

Note: ① The Field Names of the result set obtained using the Union clause are the fields in the SELECT statement result set before the Union operator. If the names are the same, the field names of select and statement result sets after the Union operator will be ignored; ② By default, the Union operator will delete duplicate records from the final set. If you want the final result to be retained Some records must use the all keyword; ③ When the Union operator is used, a separate SELECT statement cannot contain its own order by or compute clause, Only one order by or compute clause can be used after the last SELECT statement. This clause applies In the final combination result set; ④ If you want to group the query results and filter the results using the having clause After grouping, you must Specify the group by and having clauses in a single SELECT statement.

Where clause:

Where clause: specifies the conditions to be retrieved to restrict the returned data rows.
Query Conditions	Comparison operators: <, <=,>, >=, =,>, and ,! = ,! <,!>
	Scope Description: between A and B, not between A and B
	List of optional values: in and not in
	Pattern Matching: Like, not like
	Null: Is null, is not null
	Logical combination of the preceding conditions: And, or, not

Having clause: Specifies the grouping search conditions, which are often used with the group by clause. Data Types of text, ntext, and image cannot be used in having clauses. Syntax: Having <search_condition>

From clause;

From clause: Specifies the table to be queried.
Syntax: from {<table_source> }[,... N]	Table_source: Specifies the tables, views, and other data sources used by the SELECT statement. The data tables and names in the list are separated by commas.
	The from clause can also specify the join types between data tables or views. These types depend on the join conditions specified in the on clause.

Group by clause:

Group by clause: Specifies the grouping conditions for query results.
Syntax: Group by [all] <Group_by_expression> [,... N] [With {cube | rollup}]	Group_by_expression: Specifies the grouping conditions. Usually The column name, but not the column alias. The data type is text, Ntext, image, or bit Columns cannot be grouped.
	Cube: Except for the columns specified by the Group by clause, Returns the rows by group. The returned results are first grouped by the first Condition columns are sorted, and so on. The statistical line includes Statistics on various combinations of columns specified by the Group by clause.
	Rollup: Unlike cube, this option applies to group The column order in the by clause is sensitive. Only the first group entry is returned. The statistical row of the specified column. The number of result lines varies.

Order by clause:

Order by clause: Specify the arrangement of query results
Syntax: Order {Order_by _ Expression [ASC | DESC]} [,... N]	Order_by_expression: Specifies the sorting rule order_by_expression. It can be the name or alias of a table or View column. If the distinct option or union operator is used It can contain column names or aliases not in the select list. Order by clause Text, ntext, and image data types cannot be used.
	ASC: indicates that the query results are sorted in ascending order. This is the default value.
	Desc: indicates that the query results are sorted in descending order.

Compute clause:

Compute clause: generate a summary data row at the end of the query result
Syntax: Compute {AVG | count | Max | min | sum} (Expression )} [,... N] [by expression [,... N]	Expression: Specifies the name of the column to be counted. This column must be included in Select list, and aliases cannot be used. The compute clause cannot be used either. Use text, ntext, and image data types.
	By expression: generate the rows of classification statistics in the query results. If If this option is used, the order by clause must be used at the same time. Expression is in the corresponding order by clause A subset or complete set of order_by_expression.
	AVG | count | max | min | sum: The preceding parameter corresponds to a letter. The number has the same meaning. All these functions ignore the null value and the distinct Option cannot be used here.

The Application of select statements is nothing more than the application of its clauses, functions and several queries. Here we mainly introduce the clauses of select statements. In the next blog, we will mainly introduce SQL query commands, if something is wrong, please point it out.