Common Classic SQL Statement Daquan (Ascension)

Second, promote

1. Description: Copy table (copy structure only, source table name: A new table name: B) (Access available)

Law One:select * into B from a where 1<>1 (SQL Server only)

Law II:SELECT top 0 * into B from a

2. Description: Copy table (copy data, source table name: A target table name: b) (Access available)

Insert into B (A, B, c) select d,e,f from B;

3. Description: Copy of table across databases (use absolute path for specific data) (Access available)

Insert into B (A, B, c) Select d,e,f from B in ' specific database ' where condition

Example:.. From B in ' "&server.mappath (". ") & "\data.mdb" & "' Where."

4, Description: Sub-query (table name 1:a table name 2:b)

Select A,b,c from a Wherea in (select D from B)

Or:

Select A,b,c from a where a in (all-in-a-

5, Description: Display the article, the author and the last reply time

Selecta.title,a.username,b.adddate from Table A, (select Max (adddate) adddate fromtable where table.title=a.title) b

6, Description: External connection query (table name 1:a table name 2:b)

Select A.a, A.B, A.C,B.C, B.D, B.f from a left off JOIN b on a.a = B.C

7, Description: Online view query (table name 1:a)

SELECT * FROM (Selecta,b,c from a) T where t.a > 1;

8, Description: The use of between,

Between includes boundary values when querying data ranges, not between does not include

SELECT * from Table1where time between time1 and time2 select A,b,c, from table1 where a is not between value 1 and value 2

9. Description: How to use

SELECT * from Table1where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

10, Description: Two related tables, delete the main table is already in the secondary table does not have information

Delete from table1 wherenot exists (SELECT * from table2 where table1.field1=table2.field1)

11, Description: Four table linked to check the problem:

SELECT * from a leftinner join B on a.a=b.b right inner join C on A.A=C.C inner join D on a.a=d.dwhere ....

12, Description: Schedule five minutes before the reminder

Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

- , Description: A SQL statement to take care of database paging

Select Top b.* from (select Top 20 primary key field, sort field from table name order by sort field desc) A, table name B where B. primary key field = A. primary key field order by a. Sort field

Specific implementation:

About Database paging:

declare @start int, @end int

@sql nvarchar (600)

Set @sql = ' Select Top ' +str (@[email protected]+1) + ' +from t where Ridnot in (select Top ' +str (@str-1) + ' RIDs from T where rid> -1) '

EXEC sp_executesql @sql

Note: You cannot follow a variable directly after top, so there is only such special handling in the actual application. The RID is an identity column, which is beneficial if there are specific fields after top. Because this avoids the top field if it is a logical index, the result of the query is inconsistent in the actual table (the data in the logical index is likely to be inconsistent with the data table, and the index is queried first if it is in the index)

14, Description: The first 10 records

Select Top Ten * formtable1 where range

15, Description: Select in each group B value the same data corresponding to a maximum record of all information (similar to the usage can be used in the forum monthly leaderboard, monthly hot product analysis, ranked by the subject score, and so on.)

Select A,b,cfrom tablename ta where a= (select Max (a) from TableName TB wheretb.b=ta.b)

16. Description: Include all rows in TableA but not in TableB and TableC and eliminate all duplicate rows to derive a result table

(select a from TableA) except (select a from TableB) except (select a from TableC)

17, Description: Randomly remove 10 data

Select Top * FROM tablename ORDER by newid ()

18, Description: Random selection of records

Select NEWID ()

19. Description: Delete duplicate records

1),Delete from Tablenamewhere id ' not ' in (select Max (ID) from tablename GROUP by Col1,col2,...)

2), selectdistinct * to temp from tablename Delete to tablename INSERT INTO TableName Select *from Temp

Evaluation: This operation is implicated in the movement of large amounts of data, which is not suitable for large capacity but data manipulation

3), For example: import data in an external table, for some reason, for the first time only part of the import, but it is difficult to determine the exact location, so that only the next time all imports, so that will produce a lot of duplicate fields, how to delete duplicate fields

ALTER TABLE TableName

--Add a self-increment column

Add Column_b int identity (+)

Delete from TableName Wherecolumn_b not in (

Select Max (Column_b) from tablename GROUP by Column1,column2,...)

ALTER TABLE tablename DROP column Column_b

20, Description: List all the table names in the database

Select name Froms ysobjects where type= ' u '//U on behalf of user

21. Description: Lists all column names in the table

Select name Fromsyscolumns where id=object_id (' TableName ')

22, Description: List the type, Vender, PCs fields, arranged in the Type field, case can easily implement multiple choices, similar to case in select.

Select Type,sum (casevender when ' A ' and PCs else 0 end), sum (case vender if ' C ' then PCs else 0end), sum (case vender when ' B ' then PCs else 0 end] from tablename GROUP By type

Show Results:

Type Vender pcs

PC A 1

PC A 1

Disc B 2

Disc A 2

Mobile B 3

Mobile C 3

23. Description: Initialize table table1

TRUNCATE TABLE table1

24. Description: Select records from 10 to 15

Select Top 5 * FROM (select top [from table] ORDER by ID ASC) Table_ alias ORDER by id DESC

Common Classic SQL Statement Daquan (Ascension)