Database development and application: How to use SQL common commands

Data | database

How to use SQL Common commands:

(1) Data record filtering:

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]"

Sql= "SELECT top * from data table where field name order 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 1 and 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 n Where Condition expression "

(3) Delete data records:

Sql= "Delete from data table where condition expression"

Sql= "Delete from data table" (delete all records from datasheet)

(4) Adding data records:

sql= INSERT into Data table (field 1, Field 2, Field 3 ...) valuess (value 1, value 2, value 3 ...)

Sql= INSERT INTO Target datasheet SELECT * from source datasheet (add a record from the source datasheet 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:

1, set Rs=conn.excute (SQL)

2, Rs. Open sql,conn,1,3 (This is my personal preferred method in Web development/asp)

'***********************************************************
' Rs.Open statement detailed description
' Rs. Open [first argument], [second parameter], [third parameter], [fourth parameter], (fifth parameter)
' Rs. Open [datasheet name or SQL Directive], [specify current connection], [instruct CursorType], [instruct LockType], (Adcmdtable/adcmdtext)
'-----------------------------------------------------------
' First parameter
' Can be a table name, or it can be an SQL statement
'-----------------------------------------------------------
' Second parameter
' Specifies the current connection.
'-----------------------------------------------------------
' Third parameter
' Instructs Cursortype,cursortype to indicate what cursor type will start the data.
' including adOpenForwardOnly, adOpenKeyset, adopendynamic and adOpenStatic,
' is divided into the following:
' third parameter constant numerical description
' adOpenForwardOnly 0 defaults to start a cursor that can only be moved forward (Forward only).
' adOpenKeyset 1 launches a keyset type of cursor.
' adOpenDynamic 2 launches a dynamic-type cursor.
' adOpenStatic 3 launches a static type of cursor.
'----------------------------------------------------
' Several of these cursor types will directly affect all properties and methods of the Recordset object, and the following list illustrates the differences between them.
' Recordset attribute adopenforwardonly (0) adOpenKeyset (1) adopendynamic (2) adopenstatic (3)
' AbsolutePage does not support read-write readable
' AbsolutePosition does not support read-write readable
' ActiveConnection can read, write, read, write, read and write
' BOF read-only read-only read-only
' Bookmark does not support read-write readable
' CacheSize can read, write, read, write, read and write
' CursorLocation can read, write, read, write, read and write
' CursorType can read, write, read, write, read and write
' EditMode read-only read-only read-only
' EOF read-only read-only read-only
' Filter can read, write, read-write, read-write, readable
' LockType can read, write, read, write, read and write
' MarshalOptions can read, write, read, write, read and write
' MaxRecords can read, write, read, write, read and write
' PageCount does not support read-only read-only
' PageSize can read, write, read, write, read and write
' RecordCount does not support read-only read-only
' Source can read, write, read-write, read-write, readable
' State read-only read-only read-only
' Status read-only read-only read-only
' AddNew support Support
' CancelBatch support Support
' CancelUpdate support Support
' Clone does not support unsupported
' Close support support ' support
' Delete support supporting support support
' GetRows support Support
' Move does not support support support
' MoveFirst support Support
' MoveLast support support support is not supported
' MoveNext support Support
' MovePrevious support support support is not supported
' Open support support supporting support
' Requery Support Support support support
' Resync does not support unsupported support support
' Supports support Support
' Update support support supporting support
' UpdateBatch support Support
' NextRecordset support Support
The ' NextRecordset method does not apply to Microsoft Access databases.

'------------------------------------------------------------
' Fourth parameter
' Instructs LockType to determine the type of lock (concurrency) that the provider should use when opening the Recordset.
' is divided into the following:
' Fourth parameter constants constant Value description
' adLockReadOnly 1 defaults, the Recordset object starts read-only and cannot run AddNew, Update, and Delete methods
' Adlockprssimistic 2 when the data source is being updated, the system temporarily locks the actions of other users to maintain data consistency.
' adLockOptimistic 3 When the data source is being updated, the system does not lock other users ' actions, other users can add, delete, and change the operation of the data.
' adLockBatchOptimistic 4 When the data source is being updated, other users must change the CursorLocation property to Adudeclientbatch to add, delete, and modify the data

'----------------------------------------------------------------
' Fifth parameter
' is associated with the first parameter,
' If the first argument is a table name, then the fifth argument uses adCmdTable,
' If the first argument is an SQL statement, then the parameter is adCmdText
' Fifth parameter can be omitted without filling
'*****************************************************************

(Note: In this editor, display code formatting is not aligned, such as copy code in the DW Code View Code format will be aligned)