Nine basic sql statements you need to know for testing

1. To test, you need to learn simple basic sql statements

Select: select from table1 where ran

Insert: insert into table1 (field1,field2) values (value1,value2)

Deleting: delete from table1 where ran

Updating: update table1 set field1=value1 where ran

Look up: select from table1 where field1 like'% value1%'-like syntax is very exquisite, look up the information!

Sort: select from table1 order by field1,field2 [desc]

Total: select count as totalcount from table1

Summation: select sum (field1) as sumvalue from table1

Average: select avg (field1) as avgvalue from table1

Maximum: select max (field1) as maxvalue from table1

Minimum: select min (field1) as minvalue from table1

2. 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 b

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 b in 'specific database' where condition

Example:.. from b 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: 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

6. Description: two associated tables to delete the information in the primary table that is not in the secondary table

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

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

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

9. Description: list all the table names in the database

Select name from sysobjects where type='U' / / U represents the user