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%'