A complete collection of MYSQL classic statements - improvement

1. Description: Copy the table (only copy the structure, source table name: a New table name: b) (Access available)
Method 1: SELECT * into b from a where 1<>1 (only for SQlServer)
Method 2: 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 tables between databases (use absolute paths 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: Subquery (table name 1: a table 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: Display 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: outer 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 OUT JOIN b ON a.a = b.c 7. Description: Online view query (Table name 1: a )
　select * from (SELECT a,b,c FROM a) T where t.a > 1;
　8. Description: usage of between, between limits the query data range and includes boundary values, not between does not include
　select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between value1 and value2
9. Description: How to use inselect * from table1 where a [not] in ('value1 ','value 2','value 4','value 6')
　10. Description: Two related tables, delete the information in the main table that is not in the secondary table
　delete from table1 where not exists (select * from table2 where table1.field1=table2.field1)
　11. Description: Four table joint query problem:
　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. Description: Schedule reminder five minutes in advance
　SQL: select * from Schedule where datediff('minute',f start time,getdate())>5
　13. Description: A sql statement Complete 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 = a.primary key field order by a.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: top cannot be followed directly by a variable, so in practical applications Only such special processing is carried out. Rid is an identification column. If there are specific fields after top, this is very beneficial. Because this can avoid the inconsistency in the actual table after the query result if the top field is a logical index (the data in the logical index may be inconsistent with the data table, and if it is in the index during the query, the index will be queried first)
14. Description: Select top 10 * form table1 where range
15. Description: Select all the information of the record with the largest a corresponding to each group of data with the same b value (similar usage can be used for Forum monthly rankings, monthly hot-selling product analysis, ranking by subject scores, etc.)
　select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.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 take out 10 pieces of data
　select top 10 * from tablename order by newid()
　18. Description: Randomly select records
　　select newid()
　19. Description: 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
　Comment: This operation involves the movement of a large amount of data. This approach is not suitable for large-capacity data operations
　3), for example: importing into an external table For some reasons, only a part of the data was imported for the first time, but it is difficult to determine the specific location. In this way, all the data can only be imported next time, which will produce a lot of duplicate fields. How to delete duplicate fields
　　alter table tablename
　--Add one Auto-increment column
　add column_bint identity(1,1)
　delete from tablenamewhere column_b not in(
　select max(column_b) from tablename group by column1,column2,...)
　alter table tablename drop column column_b
　2 0. Description: List all table names in the database
Select name from sysobjects where type='U' // U represents user
21. Description: List all column names in the table
Select name from syscolumns where id=object_id('TableName ')
　　22. Description: List the type, vendor, and pcs fields, arranged by the type field. Case can easily implement multiple selections, similar to the 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 results:
　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 table1
　TRUNCATE TABLE table1

24 , 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

The above is the content of the MYSQL classic statement - improvement chapter, For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!