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 = "select top10 * 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 1 and value 2"
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.
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 %: represents any length of characters _: represents a character

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

(3) delete data records:
SQL = "delete from data table where condition expression"
SQL = "delete from data table"
(Delete all records in the data table)
(4) Add data records:
SQL = "insert into data table (Field 1, field 2, Field 3 ...) Values (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 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

----------------------------------------------------------------------

Connecting to mysql in jsp
(1) load the driver
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
Class. forName ("jdbc. DriverXYZ ");
(2) establish a connection
String Url = "jdbc: odbc: Fred ";
Connection con = DriverManager. getConnection (Url, "myLogin", "myPassword ");
(3) execute SQL statements
ExecuteQuery () is used to execute a single result set statement.
ExecuteUpdate () is used to execute non-select statements such as insert, update, and delete.