The most commonly used SQL statement _mssql

(1) Data record filtering:
sql= "select*from data table where field name = field value by field name [desc]"
sql= "select*from data table where field name like '% field value% ' By field name [desc]"
sql= "selecttop10*from data table where field name by 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 conditional expression "

(3) Delete data records:
sql= "deletefrom data table where condition expression"
sql= "Deletefrom data table" (delete all records in datasheet)

(4) Adding data records:
Sql= "Insertinto Datasheet (field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...)
Sql= "Insertinto target datasheet select*from source data Table" (Add a record from the source data table to the destination datasheet)

(5) Data record statistic function:
AVG (field name) to derive a table column average
Count (*| field name) statistics on the number of data rows or the number of data rows that have a value for a column
Max (field name) gets the maximum value of a table column
Min (field name) gets the smallest value of a table column
sum (field name) adds the value of the data bar
How to reference the above function:
Sql= "selectsum (field name) as Alias from data table where condition expression"
Setrs=conn.excute (SQL)
Using RS ("Alias") to obtain the value of the unified, other functions using the same.

(5) The establishment and deletion of the data table:
CreateTable Datasheet Name (field 1 type 1 (length), Field 2 type 2 (length) ...)
Example: CREATETABLEtab01 (Namevarchar), Datetimedefaultnow ())
Droptable data table name (permanently deletes a datasheet)

4. Method of Recordset object:
Rs.movenext moves the record pointer down one row from the current position
Rs.moveprevious move the record pointer up one row from the current position
Rs.movefirst move the record pointer to the first row of the datasheet
Rs.movelast move the record pointer to the last row of the datasheet
Rs.absoluteposition=n move the record pointer to the nth row of the datasheet
Rs.absolutepage=n move the record pointer to the first row of page n
Rs.pagesize=n set every page to N records
Rs.pagecount returns the total number of pages based on pagesize settings
Rs.recordcount returns the total number of records
RS.BOF returns whether the record pointer is over the first end of the datasheet, True indicates Yes, FALSE is no
Rs.eof returns whether the record pointer is over the end of the datasheet, True indicates Yes, FALSE is no
Rs.delete deletes the current record, but the record pointer does not move down
Rs.addnew add records to the end of the datasheet
Rs.update Update datasheet Record
SQL = "Select Distinct field name from data table"
Distinct function to query for records that are not duplicated in the data inventory table
SQL = "Select Count (*) from data table where field name 1> #18:0:0# and field name 1< #19:00#"
Count function, query how many records are in the library table, and "field name 1" refers to the same field
Cases:
Set Rs=conn.execute ("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#"
Look for all the records from 2003-8-10 to 2003-8-12 in the field with the date class value of 2003-8-10 19:55:08, no matter how few.
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, all records from 2003-8-10 to 2003-8-12 are detected through SQL, 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 query
Sql= "SELECT top * from data table where field name order by field name [desc]"
Find the top 10 records in a database
Sql= "SELECT top N * Form datasheet ORDER by NEWID ()"
Methods of randomly extracting several records from a database
Top N,n is the number of records to take out
Sql= "SELECT * from data table where field name in (' Value 1 ', ' Value 2 ', ' Value 3 ')"

Delete a column of a table
ALTER TABLE bankbill drop column ZSL
ALTER TABLE XX alter/add/drop column XX
Copy the table of the database
SELECT * into a new table name from the table name of the tables to be replicated where 1=2

To be completely replicated, where 1=2 goes.