SQL Server script statements

first, the grammatical structure

Select select_list[]from table_source[][][][  

The main parameters in the Select query statement are described below

select_list: A comma-delimited list of columns or expressions that are queried. New_table: The new table name. Table_source: The table to query. If there are multiple tables, separate them with commas. Search_condition: Query criteria. Group_by_expression: Grouping expressions. Order_by_expression: Sort an expression. ASC: Sort in ascending order. Desc: Sort in descending order.

List of functions for select query statement

Clause	Key Features	is required
Select	Specify the columns returned by the query	Is
From	Specify the table to query	Is
Into	Create a new table and insert the result row into a new table	Whether
where	Query criteria	Whether
GROUP BY	Grouping Query Results	Whether
ORDER BY	Sort the results of a query	Whether
Having	Filter the results of a query	Whether

Second, select the list

Select list to define columns in the result set of a SELECT statement

　　1. * Query All columns:

From person

* Is the result set, which represents all columns in the query person table.

　　2, distinct remove duplicate data:

　　　Distinct is the function of all columns, that is, all columns are the same to be counted as duplicate data.

From person

　　3. Queries that contain functions:

For example:

Count (from person

third, from clause

The FROM clause is actually a comma-delimited list of table names, view names, and join words. Use the FROM clause to achieve the following functions:

1. Lists the tables and views of the selection list and the columns referenced by the WHERE clause. You can use the AS clause to specify aliases for tables and views.

2. Type of connection. These types are qualified by the join condition specified in the ON clause.

You can use the following form when assigning table names

• • TABLE_NAME as Table alias
  • TABLE_NAME as Table_alias

It is important to note that if you assign an alias to a table, all display references to that table in the T-SQL statement must use aliases instead of aliases.

iv. WHERE clause

The WHERE clause can filter rows in the source table of the result set. The structure of a SELECT statement with a WHERE clause is as follows:

 　　< Field list > < table name > where< conditional expression >     

Where the conditional expression is composed of various fields, constants, expressions, relational operators, logical operators, and special operators.

Operators in the WHERE clause:

1. Relational operators

A relational operator is used to represent a comparison relationship between two expressions.

Relational operators	Meaning
=	Equals
<	Less than
>	Greater than
! = (or <>)	Not equal to
>=	Greater than or equal
<=	Less than or equal
!>	No greater than
!<	Not less than

2. Logical Operators

A logical operator is used to represent a logical relationship between two expressions:

logical operators	Meaning
Not	Non (NO)
and	And
Or	Or

3. Special operators

The

special operator	meaning
%	wildcard character, usually used in conjunction with like,
_	wildcard character, which represents a strict character. Where name like ' _xxx ' will find all 4-letter names ending with xxx (sxxx,dxxx, etc.)
[]	specified range ([a-f]) or collection ([ABCDEFG]) of any single character. where name like ' [A-f]xxxx ', the character that will be abcdef to start with the end of XXXX.
[^]	Any single character that does not belong to the specified range ([a-f]) or collection ([ABCDEFG]).
between	defines a range of values to use and separate. Between the start value with the and end value.
like	string match
in	the value of a field is within a defined set of values
exists	subquery has result set returned (then subquery returns True)
NOT exists	subquery No result set returned (then subquery Returns True)
is null	field is null
was not null	field not Null

Using exists in the WHERE clause, if used properly, can greatly improve performance. Because exists is used, SQL Server stops immediately whenever a record is found that matches the condition. Assuming there is a table with 1 million records, and a matching record is found in the third record, using the EXISTS option avoids reading 999,997 records! Not exists works the same way.

v. GROUP BY clause

In order to explain the problem in the simplest way, I have specially designed a table like this one.

　　

first, GROUP by single-value rules

Rule 1: Single-value rules, followed by the list after select, must be returned and only one value returned for each grouping.

The typical performance is the column following the select, which must appear after the GROUP BY clause if no aggregate function is used.

As the following query error:

　　

Because after grouping by department, there are 3 numbers in the technical part group, there are 2 numbers in the Sales section group, which do you let the database show?

If you assume that you use the aggregate function after count (number), for each department group, there is only one value-the number of people under that department:

　　

Under the actual situation below, we hope to find out the name, department, and salary of each department, the highest wage of the person.

　　

Shit, a frustrated. The first combat is wrong, let's analyze it.

Obviously, the name column above does not conform to the single-value rule. Our wishful thinking is that after MAX (payroll), SQL Server will automatically help us return ' names ' that do not conform to the single-value rule. Unfortunately, SQL Server did not do so. The reasons are as follows:

1. If two people pay the same wages, then which person should be the name returned?
2. If we are not using the max () aggregate function, but the aggregate functions such as SUM, AVG, and so on (there is no wage matching it), which is the name returned?
3. If you use two aggregate functions in a query statement, such as Max (), MIN (). So should I return the name of Max's salary or the name of min salary?

In summary, the database is not likely to be able to be based on our input of an aggregate function, it helps us to judge and show that does not conform to the single-valued rules of the column.

For MySQL, when there is a column that does not conform to a single-value rule, the default is the first record that returns this set of results. And SQLite is returning to the last article.

Therefore, for the above query, we need to find a different solution.

　　Solution 1: Associate Subqueries

As T1EXISTS (> T1. Salary)  

The output is as follows:

　　

Fully meet the requirements. For the associated subquery above, it can be understood as:

Traverse all records of the payroll table to find records that do not have the same salary as the current record department.

　　Although the syntax for correlating subqueries is simple, performance is not good. Because the subquery is executed once for each record.

　　Solution 2: Derived tables

The idea of using a derived table is to execute a subquery first, get a temporary result set, and then use the temporary result set and the original table for the inner JOIN operation. Information about the person who can get the highest wage.

　　

When I first wrote this SQL statement, I thought it was wonderful, and I thought it was wonderful after understanding it.

 select name, T1. Department, salary as T1 join (select Department, max (Salary) as highest --execute query, first record two fields department-maximum wage group by department) as T2 --derivative table T2on T1. Department = T2. Department = highest        

　　The way the derived table performs better than the associated subquery, because the way the derived table executes only one subquery at a time. But it needs a temporary table to store the temporary records. Therefore, this solution is not the best solution.

　　Solution 3: Use Join + is NULL

This is a better solution, when we use an outer join to match the record, when the matching record does not exist, it will replace the corresponding column with NULL.

Let's start by looking at a very simple SQL statement:

　　

What do you see from it? Null is returned when there is no record in the T2 table that is higher than the salary in the T1 table.

So, then, is it null to solve the problem?

　　

Good, wonderful method, let a person simply astounding use outer JOIN.

The join solution works for large data queries and scalable comparisons. It is always better suited to variable data volumes than a subquery-based solution.

　　Solution 4: Use aggregate functions for additional columns

We know that when GROUP by, the select list must return a single value, can we use an aggregate function to get this column to return a single value? The answer is yes.

　　

In fact, the returned data is problematic, and when the salary is the same, it returns the first name arranged by name from the big to the small. In other words, when the salary is the same, it can only return a record.

Let's change the aggregation function to a min look.

　　

　　Solution 5:row_number () + Over

As (as part     , score, Name, createtime from xxx) 1     　　

The output is as follows:

　　

second, having the understanding

The difference between where and having:

• Where (pre-group filtering): where the aggregate function column cannot be filtered because the grouping has not been executed while executing where, and the aggregate function has not been executed.
• Having (post-grouping filtering): Mainly used to filter the aggregate function columns, because there is an actual grouping followed by execution. The HAVING clause can only be used with the GROUP BY clause. You cannot use having when there is no GROUP BY clause.

Example of error using where:

　　

Use the Where and have example correctly:

　　

In order to explain the problem in the simplest way, I have specially designed a table like this one.

　　

first, GROUP by single-value rules

Rule 1: Single-value rules, followed by the list after select, must be returned and only one value returned for each grouping.

The typical performance is the column following the select, which must appear after the GROUP BY clause if no aggregate function is used.

As the following query error:

　　

Because after grouping by department, there are 3 numbers in the technical part group, there are 2 numbers in the Sales section group, which do you let the database show?

If you assume that you use the aggregate function after count (number), for each department group, there is only one value-the number of people under that department:

　　

Under the actual situation below, we hope to find out the name, department, and salary of each department, the highest wage of the person.

　　

Shit, a frustrated. The first combat is wrong, let's analyze it.

Obviously, the name column above does not conform to the single-value rule. Our wishful thinking is that after MAX (payroll), SQL Server will automatically help us return ' names ' that do not conform to the single-value rule. Unfortunately, SQL Server did not do so. The reasons are as follows:

1. If two people pay the same wages, then which person should be the name returned?
2. If we are not using the max () aggregate function, but the aggregate functions such as SUM, AVG, and so on (there is no wage matching it), which is the name returned?
3. If you use two aggregate functions in a query statement, such as Max (), MIN (). So should I return the name of Max's salary or the name of min salary?

In summary, the database is not likely to be able to be based on our input of an aggregate function, it helps us to judge and show that does not conform to the single-valued rules of the column.

For MySQL, when there is a column that does not conform to a single-value rule, the default is the first record that returns this set of results. And SQLite is returning to the last article.

Therefore, for the above query, we need to find a different solution.

　　Solution 1: Associate Subqueries

As T1EXISTS (> T1. Salary)  

The output is as follows:

　　

Fully meet the requirements. For the associated subquery above, it can be understood as:

Traverse all records of the payroll table to find records that do not have the same salary as the current record department.

　　Although the syntax for correlating subqueries is simple, performance is not good. Because the subquery is executed once for each record.

　　Solution 2: Derived tables

The idea of using a derived table is to execute a subquery first, get a temporary result set, and then use the temporary result set and the original table for the inner JOIN operation. Information about the person who can get the highest wage.

　　

When I first wrote this SQL statement, I thought it was wonderful, and I thought it was wonderful after understanding it.

 select name, T1. Department, salary as T1 join (select Department, max (Salary) as highest --execute query, first record two fields department-maximum wage group by department) as T2 --derivative table T2on T1. Department = T2. Department = highest        

　　The way the derived table performs better than the associated subquery, because the way the derived table executes only one subquery at a time. But it needs a temporary table to store the temporary records. Therefore, this solution is not the best solution.

　　Solution 3: Use Join + is NULL

This is a better solution, when we use an outer join to match the record, when the matching record does not exist, it will replace the corresponding column with NULL.

Let's start by looking at a very simple SQL statement:

　　

What do you see from it? Null is returned when there is no record in the T2 table that is higher than the salary in the T1 table.

So, then, is it null to solve the problem?

　　

Good, wonderful method, let a person simply astounding use outer JOIN.

The join solution works for large data queries and scalable comparisons. It is always better suited to variable data volumes than a subquery-based solution.

　　Solution 4: Use aggregate functions for additional columns

We know that when GROUP by, the select list must return a single value, can we use an aggregate function to get this column to return a single value? The answer is yes.

　　

In fact, the returned data is problematic, and when the salary is the same, it returns the first name arranged by name from the big to the small. In other words, when the salary is the same, it can only return a record.

Let's change the aggregation function to a min look.

　　

　　Solution 5:row_number () + Over

As (as part     , score, Name, createtime from xxx) 1     　　

The output is as follows:

　　

second, having the understanding

The difference between where and having:

• Where (pre-group filtering): where the aggregate function column cannot be filtered because the grouping has not been executed while executing where, and the aggregate function has not been executed.
• Having (post-grouping filtering): Mainly used to filter the aggregate function columns, because there is an actual grouping followed by execution. The HAVING clause can only be used with the GROUP BY clause. You cannot use having when there is no GROUP BY clause.

Example of error using where:

　　

Use the Where and have example correctly:

　　

vi. ORDER BY clause

The ORDER BY clause is used to specify the ordering of the result set

1. Grammatical structure:

　　　　< column list > from  database table name [where < conditional expression >] [order by[< field name or expression > [asc|de SC],...]]        

The ORDER BY clause can be paired with a WHERE clause, or it can be used with SELECT, FROMD, without requiring a WHERE clause.

The syntax for the ORDER BY clause is as follows:

    [ ORDER BY {order_by_expression [ASC | desc[]]  

The main parameters are described below:

Order_by_espression: Specifies the column to sort, the alias of the column, an expression, or a negative integer that is specified as the location of the name, alias, or expression within the selection list.

ASC: Sorts the values in the specified column in ascending order.

Desc: Sorts the values in the specified column in descending order.

Seven, having a filter query

See address:http://www.cnblogs.com/kissdodog/p/3365789.html

eight, into query

The INTO clause generates a new table for the query results, and the structure of the new table consists of a list of query fields. You can also send the results of a query to a temporary table in the tempdb database so that the temporary table is automatically deleted after you shut down the server.

The syntax structure of the INTO query:

　　< list of field names > [] from  database table name []      

SQL Server script statements