Sqlsever Second class, the main learning content for top query before how many lines, distinct, order by sort, group by group, the most important sub-query

Create DATABASE Xuesheng

Drop Database Xusheng

Use Xuesheng

Go

CREATE TABLE Xueshengxinxi

(

Code int primary key identity (a) is not NULL,

Name varchar (NOT NULL),

The age int is not NULL, and sex varchar (not null),

hight int NOT NULL, [weight] int is not NULL,

Dizhi varchar () NOT NULL,

)

Go

Select *from Xueshengxinxi

Go

INSERT into Xueshengxinxi values (' Zhang San ', 23, ' Male ', 175, 65, ' Zibo ')

INSERT into Xueshengxinxi values (' John Doe ', 24, ' Male ', 170, 50, ' Weifang ')

INSERT into Xueshengxinxi values (' Harry ', 25, ' Male ', 165, 62, ' Linyi ')

INSERT into Xueshengxinxi values (' Zhao Liu ', 26, ' female ', 162, 60, ' Jinan ')

INSERT into Xueshengxinxi values (' Tianqi ', 27, ' female ', 172, 63, ' Texas ')

INSERT into Xueshengxinxi values (' Tianqi ', 20, ' female ', 160, 58, ' Linyi ')

Go

Update Xueshengxinxi set name= ' Hello ' where code=1--change the first line name to "Hello"

Update Xueshengxinxi set [weight]=50 where code=9

Go

Delete from Xueshengxinxi--delete all data in the table

Delete from Xueshengxinxi where age=4--Deletes the fourth row of data in the table

Go select *from xueshengxinxi where code =4--queries the fourth row of data in the table

Select Name,age from Xueshengxinxi where code between 1 and 3--query table first row to third row name,age Two column-specific information

Go Delete from table Xueshengxinxi--Delete all the information in the table

Delete from table where code=3--Deletes the third row of information in the table

drop table Xueshengxinxi

--top keywords

Select top 3*from xueshengxinxi--query top three rows

Select Top 3 Name,code from Xueshengxinxi where age >=22--query for the first three rows of the name column and the code column for older than 22 years

--distinct to Heavy

Select distinct name from xueshengxinxi--remove the duplicate name

Select distinct sex from xueshengxinxi--sex to weight, select disrinct sex= ' Male ' from xushengxinxi--put sex male to heavy, no longer show female information

--order by

Select *from Xueshengxinxi ORDER BY age ASC--ascending, chronological order

Select *from Xueshengxinxi ORDER by hight desc--Descending, sorted by height

Select *from Xueshengxinxi where age<25 order by age asc--queries older than 25 come out and sort

Select *from Xueshengxinxi ORDER by age Asc,code desc--If there are two of the same age, first beat the old and then follow the code in reverse

Select *from Xueshengxinxi ORDER BY [weight] Asc,code asc--If there is a weight equal, first by weight in the right order-and then by the number of positive sequence.

Order by+ Column name +ASC is the sequence, order by+ column name +desc is an inverted

Go

--Grouping

Select age from Xueshengxinxi GROUP by age-Groups a column, which is equivalent to re-display, showing only one column

Select Hight from Xueshengxinxi GROUP by hight--automatically sorted after grouping

Go

--sub-query!!!!!!!!!!!!! --Use Query statements to query, one by one columns of data out, and then use as parameters in the query criteria of other queries

--in indicates what parameters are within

Select *from Xueshengxinxi where age in (23,24)-equivalent to age=23 or age=24. Find information about 23, 24 years old in student information

Select *from Xueshengxinxi where age isn't in (23,24)--Query student information is not 23, 24 years old information

Go

--Check the height range of people who are not 23 years of age:

Select *from Xueshengxinxi where hight not in (select Hight from Xueshengxinxi where age=23)--Check the 23-year-old first and then remove the 23-year-old information.

--the person whose name is Tianqi is older than Code=20 's John Doe is 4 years younger than the man's information:

Select *from xueshengxinxi where age+4= (select age from Xueshengxinxi where code=20) and name= ' Tianqi '

--the person whose name is Tianqi in the age of three years older than the surname Lee

: Select *from xueshengxinxi where age-3= (select age from Xueshengxinxi where name like ' Li% ') and name= ' Tianqi '

--Information for people taller than Zhang San select *from xueshengxinxi where hight< (select Hight from xueshengxinxi where name = ' Zhang San ')

--people who are taller than code=24 are shorter than the third-highest information:

Select*from Xueshengxinxi where hight< (select Hight from Xueshengxinxi where Name= ' Zhang San ') and hight> (select Hight from Xueshengxinxi where code=24)--a woman whose height is 170 heavier than the person who weighs 8 kilograms of information:

Select*from Xueshengxinxi WHERE [weight]-8= (select [weight] from Xueshengxinxi where hight=170) and sex= ' women '

--a girl who is younger than the age of 26:

Select *from xueshengxinxi where age< (select age from Xueshengxinxi where age=26) and sex= ' women '

--Find information about students who are 170 and 175 of their height:

Select *from Xueshengxinxi where hight in (170,175)

Sqlsever Second class, the main learning content for top query before how many lines, distinct, order by sort, group by group, the most important sub-query