Collation of common SQL statements--sql Server 2008 (query two-)

Built-in functions in sql:

--aggregate function count--the number of data in the query table

Select  from
Select COUNT (*) from the person where age in (18,19,20)--all functions that can be spoken with the previous section you want

--min,max,avg,sum

Select  from
Select Max (age) from person
Select AVG (age) from person
Select SUM (age) from person

--Data grouping---grouped according to age, then the grouped data is removed

Select  from person GROUP by age

Note: If the field used does not appear behind group BY, it cannot be used with a SELECT statement (but the aggregate function is possible), let's take a look at two examples

Select age,count (*) as from the person group by age
Error usage: Select Age,count (*) as number, username from
---Error: the column ' Person.username ' in the select list is not valid because the column is not contained in an aggregate function or a GROUP by clause.

Having is the filtering of the post-grouping information, the columns that can be used and the columns that can be used in the select are the same, the difference between having and where is not in depth here, but to understand that there is no substitute for where

Select age,count (*) as from the person group by age have age>15
---Group statistics according to age, and select groups older than 15

--Removing duplicate data

Select  from person

--federated result set (combining query results into 1 query results) Note: The top and bottom two query statement fields must be consistent (name, type, number must be the same)

Select  from personunion Select  from Student

Note: The difference between Union and union all. The former merges duplicate data, the latter is not merged, because the Union needs to scan and contrast the data, so inefficient, so if not to merge data, it is recommended to use UNION ALL

--Query each person's name and age, and calculate the age synthesis, then put in the last column

Select  from Person Union all Select ' Total ', SUM (age) from the person

Collation of common SQL statements--sql Server 2008 (query two-)