1. SQL INSERT statement gets automatically generated increment ID value
Copy Code code as follows:
Insert into Table1 (name,des,num) VALUES (' LTP ', ' thisisbest ', 10);
SELECT @ @identity as ' Id '
2. Implementation is 1 or 0 want to show as male or female
Copy Code code as follows:
Select name,sex= (Case Sex when ' 1 ' Then ' man ' when ' 0 ' then ' female ') from TableName
3. Nested subqueries
Copy Code code as follows:
Select A,b,c from Table1 where a in (select a from Table2)
4. Show article, submitter, and last reply time
Copy Code code as follows:
Select A.title,a.username,b.adddate from TableName A, (select Max (adddate) adddate from TableName where tablename.title=a . title) b
5. Examples of random extraction of strip records
Copy Code code as follows:
SQL Server:select Top * out tablename ORDER by NewID ()
Access:select Top * FROM tablename ORDER by Rnd (ID)
Rnd (ID) The ID is an AutoNumber field, which can be done using any number of other values, such as the name of a segment username
Select Top * FROM Table order by Rnd (Len (UserName))
Mysql:select * FROM Table order by Rand () Limit 10
6. Find a record of the same property in the same table
Copy Code code as follows:
Select UserID from accounts_users where UserName isn't null GROUP by UserID have Count (*) >1
7. Query category all product corresponding data
Copy Code code as follows:
SELECT Categoryname,productname from Categories left JOIN the products on categories.categoryid = Products.CategoryID;
8. Query for user information numbered from 2 to 5 by scope
Copy Code code as follows:
SELECT * from Uservalue where UserID between 2 and 5;
9. Schedule 5 minutes Advance reminder
Copy Code code as follows:
Select * from Tabschedule where DateDiff (Minute,getdate (), start time) <5
10. Arrive at the maximum number of days of the month in which a date
Copy Code code as follows:
SELECT Day (DATEADD (DD,-day (' 2008-02-13 '), DATEADD (mm, 1, ' 2008-02-13 '))) as ' Daynumber '
11. Sort by last name stroke
Copy Code code as follows:
Select * FROM TableName ORDER by CustomerName Collate Chinese_prc_stroke_ci_as
12. Some uses of wildcard characters
Copy Code code as follows:
1.select * FROM TableName where column1 like ' [a-m]% ' to select the record of the first letter in the column field between A-M
2.select * FROM TableName where column1 like ' [abc]% ' so you can select a record with the first letter A or B or C in the column field
3.select * FROM TableName where column1 like ' [a-cg]% ' can select the record of the first letter in the column field between A-c or G
4.select * FROM TableName where column1 like ' [^c]% ' so you can select a record with the first letter not C in the column field
13. Copy table structure (copy structure only, source table name: A, target table name: b)
Copy Code code as follows:
SELECT * into B from a where 1<>1 or select top 0 * to [b] from [a]
14. Copy table data (copy data, source table name: A, destination table name: b)
Copy Code code as follows:
INSERT into B (name,des,num) select Name,des,num from Table1;
