SQL Server rookie Takeoff-TOP (1)

The way SQL Server learns to summarize

　　lol bounty hunter : Sail

1,SQL Server query statement

Table:student

Fields: Stuid, Stuname (first name), Stuage (age), Stuscore (score),

General Query

Select *  from

Conditional query where behind and condition

Querying data stuname to Zhang San

Select *  from Where Stunamen=' Zhang San '

Between querying data between two values

Search for data between 20 years old at age 10

Select *  from where between Ten  and  -

Like fuzzy query, query approximate like '% value% '

Query the data of "http://i.cnblogs.com/EditPosts.aspx?opt=1 technique" in Stuname

Select *  from where  like ' % Technical% '

Wildcard characters

%	Instead of one or more characters
—	Represents only one character
"Charlist"	Word columns any single character
"^charlist"	Single character not in character columns

Feel like very magical, very useful, example: "

Inquire about student information of Zhang surname

Select *  from where  like ' % sheet% '

Query name The last word of "lei" classmate information

Select *  from WHERE   like ' % Lei '

Query the last name of the classmate does not exist "Zhang" and "Li" information

Select *  from where  like ' [^ Zhang Li]% '

Query the information of the classmate with the name of two words and surname Zhang

Select *  from where  like ' Zhang _ '

　And,or

　　and used to link SQL conditional statements, which can be understood as "and" means:

This sentence has two conditions, the conditions in the middle with a where link and meaning, query two conditions are set up data

Select *  from  as Where = 1  and = ' Zhang San '　

Or is also used to link SQL statements, which can be understood as "or", for example:

This sentence has two conditions, in the middle with or link, meaning that as long as there is a condition can be found in the data

Select *  from  as Where = 1 OR = ' Zhang San '　

in, not in

　　In contains meaning, not the meaning of the veto, which does not contain a subquery such as:

Select * 　  from Table  whereIdinch(Select Top(3) Stuid fromStudent)--ID is included in the StuidSelect * 　  from Table  whereId not inch(Select Top(3) Stuid fromStudent)--the ID is not included in the STUID data

SELECT *from Student where stuname don't like '% sheet% '--query name without "Zhang" data, not veto 　 

Sort, ORDER BY

Order by sorts the result set after the query he has two identities, (ACS Ascending, DESC flashback), the default is ascending example:

The meaning of this sentence is to enjoy a positive order by student ID

Select *  from Order  by Stuid  // default is a positive sequence, so do not intentionally add ASC unless special interpretation

Select *  from Order  by DESC // result set flashbacks, flashback by student ID

The first sequence is based on the ID, and if more than one age is the same, ascending by ID

Select *  from Order  by desc ASC

　　As (alias)

　　　　As means change in the query process , the name of the result set queried, or the name of the specified table, not changing the name of the table, but only when the query changes such as

When you make a connection to a table,

Select St. Stuid,tab1.tabid from as as  where= Table1. TabID

You can also change the header of a query-out result set, for example

Select  as ' Change the header name '  from

Distinct

　　Ignore query duplicate values when querying, for example:

Select Distinct  from

Max/min

Max queries the maximum value in a column, NULL is not included

Min Query the minimum value in a column, null not included

Both of them can also be used in text columns to get the highest value of text numbers or letters, and the lowest value

Select MAX  from // Find the oldest student Select MIN  from // Check with the youngest student

SUM

Query the sum of a column (numeric INT)

Select SUM  as ' Total Age '  from Student

Avg

Average, returns the average of a column

Select AVG  from

COUNT ()

Returns the specified condition, or the number of rows in a column, such as the following, returning the number of students,

Select COUNT  from Studnet

Top ()

How many columns are returned, for example, querying only the first three columns of a dataset

Select Top (3 from Student

GROUP by

grouping, grouping result sets for example:

Select SUM  as  from Group  by

Disclaimer: This article excerpt, disclaimer, the original address (http://www.cnblogs.com/yank/p/3672478.html);

SQL Server rookie Takeoff-TOP (1)