Common Summary of SQL statements

Add, delete, and modify operations by running the DB. Execute (SQL) command
Certificate -------------------- Certificate
☆Data record filtering ☆
Certificate -------------------- Certificate
Note: The usage of single double quotation marks may be incorrect (no test formula is available)

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 ')"

Certificate -------------------- Certificate
☆Add data record ☆
Certificate -------------------- Certificate
SQL = "insert into data table (Field 1, Field 2, Field 3 ...) Valuess (value 1, value 2, value 3 ...) "

SQL = "insert into data table valuess (value 1, value 2, value 3 ...) "
If the field name is not specified, it indicates that the field in the data table is added in sequence.

SQL = "insert into target data table select * from source data table"
Add records of the source data table to the target data table

Certificate -------------------- Certificate
☆Update data records ☆
Certificate -------------------- Certificate
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 n where condition expression"

SQL = "update data table set field 1 = value 1, Field 2 = value 2 ...... Field n = value N"
If no conditions exist, update the specified field value in the entire data table.

Certificate -------------------- Certificate
☆Delete data records ☆
Certificate -------------------- Certificate
SQL = "delete from data table where condition expression"

SQL = "delete from data table"
All records in the data table will be deleted if no conditions exist)

Certificate -------------------------- Certificate
☆Data record statistics function ☆
Certificate -------------------------- Certificate
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 = "select sum (field name) as Alias from data table where condition expression"
Set rs = conn. excute (SQL)
Use RS ("alias") to obtain the calculation value. Use the same method for other functions.

Certificate ----------------------------- Certificate
☆Create and delete a data table ☆
Certificate ----------------------------- Certificate
Create Table data table name (Field 1 type 1 (length), Field 2 type 2 (length )...... )
Example: Create Table tab01 (name varchar (50), datetime default now ())
Drop table data table name (permanently delete a data table)

Certificate -------------------------- Certificate
☆Method of record set object ☆
Certificate -------------------------- Certificate
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 update data table records