Collect classic SQL statements that are commonly used in work

As a programmer to deal with the database is inevitable, and now collects the work of common SQL statements, hoping to bring you some help, of course, not comprehensive, welcome to add!

1. Execute the INSERT statement to get an automatically generated increment ID value

INSERT  into VALUES (' Super admin ',' description ') SELECT @ @identity  as ' ID '

2. Find records of the same property in the same data table

SELECT  from GROUP  by  having COUNT (ID) > 1

3. Random extraction of records

SELECT  from ORDER  by NEWID ()

4. Sort by last name stroke

SELECT  from ORDER  by RoleName COLLATE Chinese_prc_stroke_ci_as

5. Get the maximum number of days for the month in which a date is

SELECT  Day (DATEADD(DD,-Day ('2017-5-12'),DATEADD(mm,1 ,'2017-5-12' as'daynumber'

6, realize with 0 or an explicit male or female

Select name, sex= case sexwhile'0'then  ' male 'when'1' then  ' female 'end from Sys_user

7. Explicit article, author and last reply time

Select a.title,a.username,b.adddate  from  as a,select(max from tablenamewhere tablename.title= A.title) b

8. Nested subqueries

Select  from where inch (Select from Table2)

9. Duplicate table structure (source table name: A, target table name: b)

Select *  into  from where 1 <> 1 or Select Top 0 *  into [b]  from [a]

10. Copy table data (source table name: A, target table name: b)

Insert  into B (name,descasselect name,desc from as a

11. Use of wildcard characters

Select *  fromTable1whereName like 'a-l%'--Filter The record of the first letter of the name column between A~lSelect *  fromTable1whereName like'[abcd]%'--Filter The first letter of the name column is a, B, C, or D recordSelect *  fromTable1whereName like '[a-dh]%'--Filter The first letter of the name column in the a~d or H recordSelect *  fromTable1whereName like '[^d]%'--filter for records with the first letter of the name column not DSelect *  fromTable1whereName like 'xiao_ming'--_ Match any single character

12. Operation Column

Alter TableTable1Add desc nvarchar( -) not NULL default "' --Add a new columnAlter TableTable1Alter column  desc  nvarchar( -)--Modifying ColumnsAlter TableTable1DropColmndesc --Delete Column

13. Stored Procedures

--sample stored procedure for inserting dataCREATE procedure [dbo].[Proc_createuser]@username nvarchar( $),@password nvarchar( $),@truename nvarchar( $),@role int asInsert  intoUsers (username,password,truename,role,createdate)VALUES(@username,@password,@truename,@role,GETDATE())--Update Data SampleCREATE procedure [dbo].[Proc_updateuser]@id int,@username nvarchar( $) asUpdateUsersSetUserName=@usernamewhereId=@id--Delete DataCREATE procedure [dbo].[Proc_deluser]@id int asDeleteUserswhereId=@id

14. Example of view chart

Create View   view_userasselectfromusergo

Welcome to my public number (Sync update article): Donet technology sharing platform

Collect classic SQL statements that are commonly used in work