Typical SQL statements [Abstract]

1. the SQL insert statement obtains the auto-generated incremental ID value.

Insert into Table1 (Name, des, num) values ('ltp ', 'thisbest', 10 );

Select @ identity as 'id'

2. Implement 1 or 0 to show as a male or female

Select name, Sex =

Case Sex

When '1' then 'male'

When '0' then 'female'

End

From Tablename

3. nested subqueries

Select a, B, c from Table1 where a IN (select a from Table2)

4. display the article, Submitter, and last reply time

Select a. title, a. username, B. adddate

From tablename a, (select max (adddate) adddate from tablename where

Tablename. title = a. title) B

5. Example of randomly extracting records

SQL Server: Select Top 10 * From Tablename Order By NewID ()

Access: Select Top 10 * From Tablename Order By Rnd (ID)

In Rnd (ID), the ID is an automatically numbered field, which can be completed by any other value, for example, UserName)

Select Top 10 * From Table Order BY Rnd (Len (UserName ))

MySql: Select * From Table Order By Rand () Limit 10

6. Find records with the same attributes in the same table

Select UserID from Accounts_Users

Where UserName is not null

Group by UserID

Having count (*)> 1

7. query data of all products of a category

SELECT CategoryName, ProductName

FROM Categories left join Products

ON Categories. CategoryID = Products. CategoryID;

8. query user information between 2 and 5 by range

Select * from UserValue where UserID between 2 and 5

9. 5 minutes in advance of the schedule

Select * From tabschedule where datediff (minute, getdate (), start time) <5

10 . Obtain the maximum number of days in the month of a date.

Select Day (dateadd (DD,-day ('2017-02-13 '), dateadd (mm, 1, '2017-02-13 ')))

As 'daynumber'

11. sort by last name strokes

Select * From tablename order by customername collate chinese_prc_stroke_ci_as

12 . Wildcard usage

1 select * From tablename where column1 like '[A-M] %'

This allows you to select a record with the first letter in the column field between the A-M

2 select * From tablename where column1 like '[ABC] %'

In this way, you can select a record whose first letter is A, B, or C in the column field.

3 select * From tablename where column1 like '[A-CG] %'

In this way, you can select a record with the first letter in the column field between the A-C or G.

4 select * From tablename where column1 like '[^ C] %'

In this way, you can select a record where the first letter in the column field is not C.

Single Character(Keyword: Like _):

You can use the underscore (_) to match any single character.

Select * from tablename where column1 like'm _ crosoft'

Match special characters:( [] _-%)

Put them all in [], for example:

Select * from tablename where column1 like '% [%] %'

13. Copy the table structure (only copy the structure, source table name: a, target table name: B)

Select * into B from a where 1 <> 1

Or

Select top 0 * into [B] from [a]

14. Copy table data (copy data, source table name: a, target table name: B)

Insert into B (Name, des, num) select Name, des, num from Table1;

15. edit a column

Add column:

Alter table Table1 add username varchar (30) not null default''

Modify columns:

Alter table Table1 alter column username varchar (40)

Delete column:

Alter table Table1 drop column username

16. Modify the hour part of the time field

-- Change the data of--1

Update Ad_Browse_20061101

Set browsetime = '2017-11-01 21 '+

SUBSTRING (CONVERT (VARCHAR (30), browsetime, 8), 3, 7)

Where DATEPART (Hour, browsetime) = 23