Test the 9 Basic SQL statements you need to know

1. Testing needs to learn simple basic SQL statements
Selection: Select From table1 where range
Insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2)
Delete: Delete from table1 where range
Updated: Update table1 set field1=value1 where range
Find: SelectFrom table1 where field1 like '%value1% '---the syntax of like is very subtle, check the information!
Sort by: Select From table1 ORDER by FIELD1,FIELD2 [DESC]
Total: Select count as TotalCount from table1
Sum: Select SUM (field1) as Sumvalue from table1
Average: Select AVG (field1) as Avgvalue from table1
Maximum: Select Max (field1) as MaxValue from table1
Min: select min (field1) as MinValue from table1
2. Description: Copy table (copy structure only, source table name: A new table name: B) (Access available)
Law One: Selectinto 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: 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

6, 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)

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

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

9, Description: List all the table names in the database
Select name from sysobjects where type= ' u '//U on behalf of user

Test the 9 Basic SQL statements you need to know