SQL basic statement

SQL basic statement

1. Data record filtering:

Sql= "select * from data Table where field name = field value order by field name [desc]" (sorted by a field value in descending order. Default ascending ASC)

Sql= "select * from data Table where Field name like'% Field value% 'order by Field name [desc]"

Sql= "select top 10 * from data Table where Field name = Field value order by Field name [desc]"

Sql= "select top 10 * from data Table order by Field name [desc]"

Sql= "select * from data Table where field name in ('value 1') 'value 2')"

Sql= "select * from data Table where Field name between value 1 and value 2"

two。 Update data records:

Sql= "update data Table set Field name = Field value where conditional expression"

Sql= "update data Table set field 1 = value 1, field 2 = value 2... field n = value n where conditional expression"

3. Delete data records:

Sql= "delete from data Table where conditional expression"

Sql= "delete from data Table" (delete all records of the data table)

4. Add a data record:

Sql= "insert into data Table (Field 1, Field 2, Field 3...) values (value 1, value 2, value 3...)"

Sql= "insert into destination data Table select * from Source data Table" (add records from the source data table to the target data table)

5. Data record statistics function:

AVG (field name) to get a table column average

COUNT (*; field name) statistics on the number of rows of data or the number of rows of data with value for a column

MAX (field name) gets the maximum value of a table column

MIN (field name) gets the minimum value of a table column

SUM (field name) adds the values of the data column

The method of referencing the above function:

Sql= "select sum (field name) as alias from datasheet where conditional expression"

Set rs=conn.excute (sql)

Use rs ("alias") to get the statistical value, other functions use the same as above.

Duplicate value removed by query: select distinct* from table1

6. Creation and deletion of data tables:

CREATE TABLE datasheet name (field 1 type 1 (length), field 2 type 2 (length)... )

7. Single column summation:

SELECT SUM (field name) FROM data sheet