How to add, delete, and modify SQL statements-Basic operations

Add, remove, and modify using DB. Execute operation (SQL) command

╔----------------╗☆ data record filtering ☆╚----------------╝ Note: The use of single and double quotes may be incorrect (not tested) SQL = "Select Distinct field name from data table" Distinct function, The query data in the inventory table does not duplicate records in sql = "Select Count (*) from data table where field name 1> #18:0:0# and field name 1< #19:00#" Count function, query the number of records in the library table, "field name 1" refers to the same One field example: 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 20 in the field with the Date class value 2003-8-10 19:55:08 03-8-10 to 2003-8-12 all records, regardless of a few minutes. SELECT * from Tb_name where datetime between #2003 -8-10# and #2003 the data format in the -8-12# field is: 2003-8-10 19:55:08, all records from 2003-8-10 to 2003-8-12 are identified by SQL, regardless of a few minutes. 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 datasheet where field name order by field name [desc] "Find database Top 10 records sql=" SELECT top N * form data table ORDER by newid () "The method of randomly fetching several records in a database top n,n is the number of records to be fetched sql=" SELECT * from data table where field name in (' Value 1 ', ' Value 2 ', ' Value 3 ') "
╔----------------╗☆ Add data record ☆╚----------------╝sql= insert INTO datasheet (field 1, Field 2, Field 3 ...) valuess (value 1, value 2, value 3 ...) "sql=" insert INTO Datasheet valuess (value 1, value 2, value 3 ...) "Do not specify a field name to indicate that the field in the data table will be added sql=" INSERT into Target datasheet SELECT * From source data table "to add records from the source data table to the Target data table
╔----------------╗☆ Update data record ☆╚----------------╝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 "No condition updates the specified field value in the entire data table
╔----------------╗☆ Delete data records ☆╚----------------╝sql= "Delete from data table where condition expression" sql= "Delete from data table" No condition will delete all records in datasheet)
╔--------------------╗☆ data record statistic function ☆╚--------------------╝avg (field name) to derive a table bar average count (*| field name) statistics on the number of rows of data or data rows with values for a column statistics 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) Add the value of the data bar to the method that references the above function: sql= "Select SUM (field name) as Alias from data table where conditional expression" Set RS =conn.excute (SQL) uses RS ("Alias") to obtain the value of the system, the other functions are used as above.
╔----------------------╗☆ data table creation and deletion ☆╚----------------------╝create table data table name (field 1 type 1 (length), Field 2 type 2 (length) ...) Example: CREATE Table TAB01 (name varchar (), datetime default Now ()) DROP table name (permanently delete a data table)
╔--------------------╗☆ The Recordset object ☆╚--------------------╝rs.movenext moves the record pointer down one line from the current position rs.moveprevious Moves the record pointer up one line from the current position Rs.movefirst moves the record pointer to the first row of the data table Rs.movelast moves the record pointer to the last row in the datasheet rs.absoluteposition=n move the record pointer to row N 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 returns the total number of pages based on the pagesize settings Rs.recordcount return records RS.BOF Returns whether the pointer is past the first end of the data table, True indicates Yes, False is no rs.eof return the record pointer over the end of the data table, True indicates yes, false to no rs.delete delete the current record, but the record pointer does not move down rs.addnew Add record to data table End rs.update Update datasheet record


Report:SQL Basic Operation statement and classical statement complete

How to add, delete, and modify SQL statements-Basic operations