2016/3/13 Seven types of queries (General query criteria Query Sort query Fuzzy query Statistics Query Group query query)

In a nutshell, SQL, as a Structured Query Language , is the standard language for standard relational databases .

T -SQL is a language that is used in SQL Server extensions that are based on SQL

1, normal query
#查询Info表中的所有列
SELECT * FROM Info
#查询Info表中的Name和Code列
Select Name,code from Info

2, conditional query keyword where
#查询Info表的左右列 scope column is named P001
SELECT * from Info where column name = "P001"
#查询条件之间并且的关系
SELECT * from Info where nation= ' n001 ' and sex=true
#查询条件之间或的关系
SELECT * from Info where nation= ' n001 ' or sex=true

3, ambiguous query keyword where like
Query all the columns in the table and blur the query in the AreaName column
% can have any number of characters before and after
SELECT * from Info where areaname like name= '% in% '
_ represents only one character before or after
SELECT * from Info where areaname like name= ' _ '

4, sort query keyword Order by ASC default Ascending desc Descending
SELECT * from Info ORDER by Code ASC
SELECT * from Info ORDER BY Code Desc
SELECT * from Info ORDER by Brand,powers #按照两个列排序 Brand is the primary Powers

5, Statistical query (aggregate function) keyword count min max sum avg
Select COUNT (*) from Car #查询总条数 count () is * time-consuming cost source
Select COUNT (1) from Car
Select COUNT (Code) from Car
Maximum value of the Price column for select Max from Car #查询最大值
Select min (Price) from Car #查询最小值 The minimum value of the price column
Select AVG (price) from Car #查询平均值
Select SUM (Price) from Car #查询总和

Select COUNT (*) and select COUNT (1) both return the same result
If the table does not have a primary key (Primary key), then count (1) is faster than COUNT (*),
If there is a primary key, the primary key is the fastest of count (primary key) as the condition of count
If your table has only one field, then count (*) is the fastest
Count (*), like the result of Count (1), includes the statistics for NULL,
and Count (column) is not included n Statistics of ULL

6, group query keyword: group by has
#根据系列分组查看每组数据条数 pay attention to code Brand behind the number
Select Code Brand,count (*) from Car GROUP by Brand
#在sql server is followed by brand in front of
#查询分组之后数据条数大于2的
SELECT * FROM Car GROUP by Brand have Count (*) >2

7, paged query keyword: limit paging rule (N-1)
#跳过零条数据, fetch five data
SELECT * FROM Car limit 0,5
SELECT * FROM Car limit 5,5
SQL Server inside is skap 0 tick 5 skip 0 strip Take 5 bar

2016/3/13 Seven types of queries (General query criteria Query Sort query Fuzzy query Statistics Query Group query query)