Shulou(Shulou.com)06/01 Report--

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

Law 1: select into b from a where 11 (for SQlServer only)

Method 2: select top 0 into b from a

2. Description: copy table (copy data, source table name: a target table name: B) (available for Access)

Insert into b (a, b, c) select d from a

Select * into b from a

3. Description: copy of tables across databases (use absolute path for specific data) (Access is available)

Insert into b (a, b, c) select djiggy e from an in 'specific database' where condition

Example:.. from an in'"& Server.MapPath (". ") &"\ data.mdb "&" 'where.. "

4. Description: sub-query (table name 1VLA, table name 2RB)

Select a dagger bjorc from a where an IN (select d from b) or: select a recital b recital c from a where an IN (1m 2pm 3)

5. Description: displays the article, author and last reply time

Select a.title adddate from table where table.title=a.title a.username from table a b.adddate, (select max (title) adddate from table where table.title=a.title) b

6. Description: external join query (table name 1VR a, table name 2RB)

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 1pora)

Select * from (SELECT a FROM c FROM a) T where t.a > 1

8. Description: the use of between. Boundary values are included when between limits the scope of query data, while not between does not include

Select * from table1 where time between time1 and time2

Select a dint bpenc, from table1 where a not between numeric 1 and numeric 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 to delete information that has not been found in the secondary table in the primary table

Delete from table1 where not exists (select * from table2 where table1.field1=table2.field1)

11. Description: joint check of the four tables:

Select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where.

12. Note: five minutes in advance of the schedule

SQL: select * from scheduling where datediff ('minute',f start time, getdate ()) > 5

13. Description: a sql statement completes database paging

Select top 10 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

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: you can't follow a variable directly after top, so this is the only way to deal with it in practice. Rid is an identity column, which is very beneficial if there are specific fields after top. Because this can avoid the inconsistency in the actual table after the result of the query if the field of top is logically indexed (the data in the logical index may be inconsistent with that in the data table, and if the field in the query is in the index, the index will be queried first)

14. Description: the first 10 records

Select top 10 * form table1 where range

15. Description: select records from 10 to 15

Select top 5 from (select top 15 from table order by id asc) table_ alias order by id desc

Select from (select top 5 from (select top 15 * from table order by id asc) table_ alias order by id desc) table_ alias order by id asc

16. Description: 10 pieces of data are randomly selected

Select top 10 * from tablename order by newid ()

17. Description: randomly select records

Select newid ()

Generate multiple Guid:

-CREATE TABLE # GuidList (Id int,Gid uniqueidentifier)

-- GO

-- cycle execution times

-INSERT INTO # GuidList (Gid) SELECT newid ()

-- GO 100

-- select * from # GuidList

18. Record search:

Start to N records

Select Top N * From table N to M records (with primary index ID)

Select Top Mmurn * From Where ID in (Select Top M ID From table) Order by ID Desc

N to end record

Select Top N * From table Order by ID Desc

Case

For example, 1: a table has more than 10, 000 records, and the first field of the table, RecID, is a self-growing field. Write a SQL statement to find the 31st to 40th records of the table.

Select top 10 recid from A where recid not in (select top 30 recid from A)

Analysis: some problems will arise if you write like this, if recid has a logical index in the table.

Select top 10 recid from A where... Is to look up from the index, while the following select top 30 recid from A looks in the data table, so because the order in the index may not be consistent with that in the data table, the query does not get the data you want to get.

Solution

1, with order by select top 30 recid from An order by ricid, if the field is not self-growing, there will be a problem

2, add a condition to that subquery: select top 30 recid from A where recid >-1

Example 2: querying the last entry in the table, we do not know how much data there is in the table and the structure of the table.

Set @ s = 'select top 1 * from T where pid not in (select top' + str (@ count-1) + 'pid from T)'

Print @ s exec sp_executesql @ s

19. Description: select all the information corresponding to the largest record of an in each set of data with the same b value (similar usage can be used for forum monthly rankings, monthly best-selling product analysis, ranking by subject scores, etc.).

Select a dint bjorn c from tablename ta where a = (select max (a) from tablename tb where tb.b=ta.b)

20, description: list type, vender, pcs fields, arranged in type fields, case can easily achieve multiple selection, 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 typ

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.