MySQL basic SQL statement Encyclopedia (Promotion of the term article)

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 where a 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

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 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 (select A,b,c from a) T where t.a > 1;

　　8, Description: Between usage, between limits the query data range 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 is not between value 1 and value 2

　9. Description: How to use

SELECT * FROM table1 where 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 where NOT EXISTS (SELECT * from table2 where table1.field1=table2.field1)

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

SELECT * from a left inner join B in 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 five minutes before the reminder

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

　13, 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 rid not 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 * form table1 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,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 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 TableName where ID not in (the Select Max (ID) from tablename GROUP by Col1,col2,...)

2), select DISTINCT * to temp from tablename

Delete from 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 where Column_b isn't 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 from sysobjects where type= ' u '//U on behalf of user

　21. Description: Lists all column names in the table

Select name from syscolumns 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 (case vender if ' A ' then the PCs else 0 end), sum (case vender if ' C ' then PCs else 0 end), sum (case vender WH En ' 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

MySQL basic SQL statement Encyclopedia (Promotion of the term article)