Database Operations improvements

1. Description: copy a table (only copy structure, source table name: a new table name: B) (access available)

Method 1:Select * into B from a where 1 <> 1 (for sqlserver only)
Method 2:Select top 0 * into B from

2. Description: copy a 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: Table Copying across databases (absolute path for specific data) (access is 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: subquery (table name 1: Table A name 2: B)
Select a, B, c from a where a in (select D from B) or: select a, B, c from a where a in (1, 2, 3)

5. Description: displays the article, Submitter, and last reply time.
Select a. Title, A. username, B. adddate from Table A, (select max (adddate) adddate from table where table. Title = A. Title) B

6. Description: External join query (table name 1: Table A name 2: B)
Select a. a, a. B, A. C, B. C, B. D, B. F from a left out join B on A. A = B. C

7. Description: Online View query (table name 1:)
Select * from (select a, B, c from a) t where T. A> 1;

8. Description: between usage. When between restricts the Data Query range, it includes the boundary value. Not between does not include
Select * From Table1 where time between time1 and time2
Select a, B, c, from Table1 where a not between value 1 and value 2

9. Description: How to Use in
Select * From Table1 where a [not] In ('value 1', 'value 2', 'value 4', 'value 6 ')

10. Description: two associated tables are used to delete information that is not in the secondary table.
Delete from Table1 where not exists (select * From Table2 where table1.field1 = table2.field1)

11. Notes: four table join query problems:
Select * from a left inner join B on. A = B. B right inner join C on. A = C. C inner join D on. A = D. d Where .....

12. Note: Five minutes ahead of schedule reminder
SQL: Select * from Schedule where datediff ('minute ', F Start Time, getdate ()> 5

13. Note: One SQL statement is used to handle database paging.
Select top 10 B. * From (select top 20 primary key field, sorting field from table name order by sorting field DESC) A, table name B where B. primary key field =. primary key field order by. sorting Field
Specific implementation:
About database paging:

Declare @ start int, @ end int

@ SQL nvarchar (600)

Set @ SQL = 'select top '+ STR (@ end-@ start + 1) +' + from t where RID not in (select top '+ STR (@ str-1) + 'rid from t where RID>-1 )'

Exec sp_executesql @ SQL
Note: A variable cannot be directly followed after top, so this is the only special method in actual application. RID is an identifier column. If there are specific fields after top, this is very beneficial. This avoids the inconsistency in the actual table after the query results of the top field if it is a logical index. (the data in the logical index may be inconsistent with that in the data table, if the query is indexed, the index is first queried)

14. Note: The first 10 records
Select top 10 * Form Table1 where range

15. Note: select all the information of the largest record of a corresponding to the data with the same B value in each group (similar usage can be used for the monthly ranking of the forum and Analysis of popular products each month, rank by subject score, etc .)
Select a, B, c from tablename Ta where a = (select max (a) from tablename TB where TB. B = TA. B)

16. Description: includes all rows in tablea but not in tableb and tablec and removes all repeated rows to derive a result table.
(Select a from tablea) Before t (select a from tableb) Before t (select a from tablec)

17. Description: 10 data records are randomly taken out.
Select top 10 * From tablename orderNewid ()

18. Description: randomly selected records
Select newid ()

19. Note: delete duplicate records
1 ),Delete from tablename where id not in (select max (ID) from tablename group by col1, col2 ,...)
2), Select distinct * into temp from tablename
Delete from tablename
Insert into tablename select * from temp
Rating: this operation involves the movement of a large amount of data, which is not suitable for large-capacity but data operations.

3) For example, if you import data to an external table for the first time for some reasons, but it is difficult to determine the specific position, so that only the next import is complete, in this way, many repeated fields are generated. How to delete repeated fields?

Alter table tablename
-Add an auto-incrementing Column
Add column_ B int identity (1, 1)
Delete from tablename where column_ B not in (
Select max (column_ B) from tablename group by column1, column2 ,...)
Alter table tablename drop column column_ B

20. Description: Lists All table names in the database.
Select name from sysobjects where type = 'U' // U indicates the user

21. Description: list all column names in the table.
Select name from syscolumns where id = object_id ('tablename ')

22. Description: lists the fields of type, vender, and PCs, which are arranged by the Type field. case can be easily selected, similar to case in select.
Select Type, sum (Case vender when 'a then PCs else 0 end), sum (Case vender when 'C' then PCs else 0 end ), sum (Case vender when 'B' then PCs else 0 end) from tablename group by type
Display result:
Type vender PCs
Computer A 1
Computer A 1
Cd B 2
Cd a 2
Mobile phone B 3
Mobile phone C 3

23. Description: Initialize table 1.

Truncate table Table1

24. Description: select a record from 10 to 15.
Select top 5 * from (select top 15 * from Table order by id asc) Table _ alias order by ID DESC

Address: http://ourzone.info/2011/09/%E6%95%B0%E6%8D% AE %E5%BA%93%E6%93%8D%E4%BD%9C%E4%B9%8B%E6%8F%90%E5%8D%87%E7%AF%87/#more-99