SQL Server database basic query

 

SQL ServerBasic syntax

 

--View data tables

Select * from student

 

--UseSQLQuery data

 

-- 1Query all the class capacities in the table

Select * From tablename

 

-- 2Query the specified field category in the table

Select columnname ,..., From tablename

 

Select stuname, stuno, stusex from stuinfo

 

-- 3, TapeWhereCondition Query

Select * | columnname from tablename where Condition

 

Select * From stuinfo where stusex ='Female'

 

-- 4, Sorted Query(Order by columnname)

--Syntax: Select * | columnname from tablename order by columnname DESC | ASC

 

Select * From stuinfo order by stuage, stuseat DESC

 

-- 5Select a specified number of records,Usually works togetherOrderUse

--Syntax: Select top num * | columnname from tablename

--Syntax: Select top num * | columnname from tablename order by DESC | ASC

Select Top 2 * From stuinfo order by stuage DESC

 

-- 6Group QueryGroup

--The columns contained in the grouping query must be included in the aggregate function orGroupClause

Select * From stuinfo

Select stusex, max (stuage)'Average age'From stuinfo group by stusex

 

-- 7And filter the grouped results.

-- Having (EquivalentWhere)

Select * From stuinfo

Select stusex, AVG (stuage)'Average age'From stuinfo group by stusex having AVG (stuage)> 20

 

-- 8,GroupCooperationWhereUse

Select * From stuinfo

Select stusex, AVG (stuage)'Average age'From stuinfo where stuage> 18 group by stusex having AVG (stuage)> 20

 

 

-----------------------------------------------------------

--UseSQLInsert data

-- 1Insert data without specifying Columns

Syntax: Insert into tablename values (Value List)

Insert into stuinfo values ('Xiaoba', 'S25311 ','Male', 24 ,'Beijing')

 

Select * From stuinfo

 

-- 2, Specify the column name to insert data

--Syntax: Insert into tablename (Column Name List) Values (Value List)

--Note: You can specify the order of the column names, but the order of the Value List should be the same as that of the column names.

 

Insert into stuinfo (stuname, stuno, stusex, stuaddress, stuage) values ('Jiujiu', 'S25312 ','Male','Shanghai', 25)

 

Select * From stuinfo

 

-- 3Insert multiple records at a time

-- 1) insert into tablename (Column name and category table) Select...FromInsert to an existing table

--Note::The data type of the column name. The number must be the same.

 

Insert into stuinfocopy (stuname, stuno, stusex, stuage, stuseat, stuaddress)

Select * From stuinfo

 

Select * From stuinfocopy

 

-- 3Insert multiple records at a time

-- 2) SelectColumn Name ListIntoNew table nameFrom sourcetableInsert to an existing table

--Note::The data type of the column name. The number must be the same.,The new table must not exist.

Select Identity (INT, 1, 1) as 'id', stuname, stuno, stusex, stuage, stuaddress

Into # temp

From stuinfo

 

Select * from # temp

 

-- 3Insert multiple rows at a time

-- 3)UseUnionMerge data rows

Insert # temp (stuname, stuno, stusex, stuage, stuaddress)

Select'Baby', 'S25318 ','Male', 22 ,'Hubei Province'Union

Select'Baby2', 's25318 ','Female', 23 ,'Hunan'

 

Select * from # temp

 

 

-- 4, Change data

--Syntax: Update tablename set columnname =ValueWhere condititon

 

Update # temp set stuname ='Baby3 'where stuname ='Baby'

 

Select * from # temp

 

-- 5Delete data

--Syntax: Delete from tablename where Condition

Delete from # temp where id = 8

 

Select * from # temp

 

-- 5Delete data

--Syntax:Truncate table tablename (When deleting all data in a tableDeleteHigh efficiency, but not

--Delete a table containing foreign key constraints

 

Truncate table stumarks

 

 

-- WhereCondition Type

-- 1,ColumnnameLow ValueAndHigh Value

Select * From stuinfo where stuage between 20 and 25

 

-- 2,And or not (And, or, not)

 

-- 3,In (Value List)

Select * From stuinfo where stuage in (225-25)

 

-- 4,Like (Fuzzy search)

-- %Represents any number of characters_One character[]A RANGE[^]Not in a certain range

Select * From stuinfo where stuname like'Small%'