The most common SQL statements

(1) Data Record Filtering:
SQL = "select * from data table where field name = field value orderby field name [desc]"
SQL = "select * from data table where field name like '% Field Value %' orderby field name [desc]"
SQL = "selecttop10 * from data table where field name orderby field name [desc]"
SQL = "select * from data table where field name in ('value 1', 'value 2', 'value 3 ')"
SQL = "select * from data table where field name between value 1and value 2"

(2) update data records:
SQL = "update data table set field name = field value where condition expression"
SQL = "update data table set field 1 = value 1, Field 2 = value 2 ...... Field n = value nwhere condition expression"

(3) Delete data records:
SQL = "where efrom data table where condition expression"
SQL = "deletefrom data table" (delete all records of the data table)

(4) add data records:
SQL = "insertinto data table (Field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...) "
SQL = "insertinto target data table select * from source data table" (add records of source data table to target data table)

(5) statistical functions of data records:
AVG (field name) returns the average value of a table column
COUNT (* | field name) statistics on the number of data rows or the number of data rows with values in a column
MAX (field name) obtains the maximum value of a table column.
MIN (field name) obtains the minimum value of a table column.
SUM (field name) adds the values in the data column
The method for referencing the above functions:
SQL = "selectsum (field name) as Alias from data table where condition expression"
Setrs = conn. excute (SQL)
Use rs ("alias") to obtain the calculation value. Use the same method for other functions.

(5) Create and delete data tables:
CREATETABLE data table name (Field 1 type 1 (length), Field 2 type 2 (length )......)
Example: CREATETABLEtab01 (namevarchar (50), datetimedefaultnow ())
DROPTABLE data table name (permanently delete a data table)

4. Method of record set object:
Rs. movenext moves the record pointer down a row from the current position
Rs. moveprevious transfers the record pointer from the current position to a row up
Rs. movefirst move the record pointer to the first row of the data table
Rs. movelast moves the record pointer to the last row of the data table
Rs. absoluteposition = N move the record pointer to the nth row of the data table
Rs. absolutepage = N move the record pointer to the first row of page N
Rs. pagesize = N set N records per page
Rs. pagecount the total number of pages returned Based on pagesize settings
Rs. recordcount total number of returned records
Rs. bof indicates whether the record pointer exceeds the first end of the data table. true indicates yes, and false indicates no.
Rs. eof indicates whether the returned record pointer exceeds the end Of the data table. true indicates yes, and false indicates no.
Rs. delete deletes the current record, but the record pointer does not move down
Rs. addnew add record to end of data table
Rs. update data table records
SQL = "Select Distinct field name From data table"
The Distinct function is used to query records that are not repeated in the data inventory table.
SQL = "Select Count (*) From data table where field name 1 >#0:0 # and field name 1 <#19:00 #"
The count function is used to query the number of records in a database table. Field name 1 indicates the same field.
Example:
Set rs1_conn.exe cute ("select count (id) as idnum from news ")
Response. write rs ("idnum ")
SQL = "select * from data table where field name between value 1 and value 2"
SQL = "select * from data table where field name between #2003-8-10 # and #2003-8-12 #"
In the field with a date value of-8-10 19:55:08, search for all records from-8-10 to-8-12, regardless of the time.
Select * from tb_name where datetime between #2003-8-10 # and #2003-8-12 #
The data format in the field is 2003-8-10 19:55:08. You can use SQL to find all records from 2003-8-10 to 2003-8-12, regardless of the time.

SQL = "select * from data table where field name = Field Value order by field name [desc]"
SQL = "select * from data table where field name like '% Field Value %' order by field name [desc]"
Fuzzy search
SQL = "select top 10 * from data table where field name order by field name [desc]"
Search for the first 10 records in the database
SQL = "select top n * form data table order by newid ()"
How to randomly retrieve several records from a database
Top n, n is the number of records to be retrieved
SQL = "select * from data table where field name in ('value 1', 'value 2', 'value 3 ')"

Deletes a column from a table.
Alter table bankbill drop column zsl
Alter table xx alter/add/drop column xx
Copy a database table
Select * into new table name from table name to be copied where 1 = 2

You can simply copy where 1 = 2.