Today, the editor will share with you a collection of SQL statements. Interested friends, let’s learn about it with the editor!
1. Connect ASP to Access database:
dimconn,mdbfile
mdbfile=server.mappath("database name.mdb")
setconn=server.createobject("adodb.connection")
conn.open"driver={microsoftaccessdriver
(*.mdb)};uid=admin;pwd=database password;dbq="&mdbfile
%>
2. Connect ASP to SQL database:
dimconn
setconn=server.createobject("ADODB.connection")
con.open"PROVIDER=SQLOLEDB;DATA
SOURCE=SQL server name or IP address;UID=sa;PWD=database password;DATABASE=database name
%>
Create a record set object:
setrs=server.createobject("adodb.recordset")
rs.openSQL statement,conn,3,2
3. Common SQL command usage methods:
(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="selecttop10*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"
(2) Update data records:
sql="update data table set field name = field value where conditional expression"
sql="update data table set field 1=value 1, field 2=value 2...field n=value nwhere condition expression"
(3) Delete data records:
sql="deletefrom data table where conditional expression"
sql="deletefrom data table" (delete all records in the data table)
(4) Add data records:
sql="insertinto data table (field 1, field 2, field 3…) values (value 1, value 2, value 3…)"
sql="insertinto target data table select* from source data table" (add the source data table record to the target data table)
(5) Data record statistics function:
AVG (field name) gets an average value of a table column
COUNT (*|field name) statistics on the number of data rows or statistics on the number of data rows with values in a certain 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 column
Reference the above function method:
sql="selectsum(field name) as alias from data table where conditional expression"
setrs=conn.excute(sql)
Use rs ("alias") to obtain the statistical calculation value, and other functions are used the same as above.
(5) Establishment and deletion of 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. Methods of recording set objects:
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 of the data table
rs.absoluteposition=N Move the record pointer to row N of the data table
rs.absolutepage=N Move the record pointer to the first line of page N
rs.pagesize=N Set each page to N records
rs.pagecount returns the total number of pages according to the pagesize setting
rs.recordcount returns the total number of records
rs.bof returns whether the record pointer exceeds the head of the data table. True means yes, false is no
rs.eof returns whether the record pointer exceeds the end of the data table. True means yes, false is no
rs.delete deletes the current record, but the record pointer does not move downward
rs.addnew add record to the end of the data table
rs.update updates data table records
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－�
Recordset object method
Open method
recordset.OpenSource,ActiveConnection,CursorType,LockType,Options
Source
The Recordset object can be connected to the Command object through the Source property. The Source parameter can be a Command object name, a SQL command, a specified data table name, or a Stored
Procedure. If this parameter is omitted, the system uses the Source property of the Recordset object.
ActiveConnection
The Recordset object can be connected to the Connection object through the ActiveConnection property. The ActiveConnection here can be a Connection object or a string parameter containing database connection information (ConnectionString).
CursorType
The CursorType parameter of the Open method of the Recordset object indicates what cursor type to start the data, including adOpenForwardOnly, adOpenKeyset, adOpenDynamic and adOpenStatic, which are described as follows:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Constant constant value description
--------------------------------------------------------------------------------------------------------------------------------
adOpenForwardOnly0 default value, starts a cursor that can only move forward (ForwardOnly).
adOpenKeyset1 starts a Keyset type cursor.
adOpenDynamic2 starts a Dynamic type cursor.
adOpenStatic3 starts a Static type cursor.
--------------------------------------------------------------------------------------------------------------------------------
The above cursor types will directly affect all properties and methods of the Recordset object. The following list explains the difference between them.
--------------------------------------------------------------------------------------------------------------------------------
Recordset property adOpenForwardOnlyadOpenKeysetadOpenDynamicadOpenStatic
--------------------------------------------------------------------------------------------------------------------------------
AbsolutePage does not support readable, writeable, readable, writeable
AbsolutePosition does not support readable, writeable, readable, writeable
ActiveConnection readable, writeable, readable, writeable, readable, writeable, readable, writeable
BOF read only read only read only read only read only read only
Bookmark does not support readable, writeable, readable, writeable
CacheSize can be read, write, read, write, read, write, read, write, read, write
CursorLocation readable, writeable, readable, writeable, readable, writeable, readable, writeable
CursorType can be read, write, read, write, read, write, read, write, read, write
EditMode Read-only Read-only Read-only
Read only
EOF read only read only read only read only
Read only
Filter can be readable, writeable, readable, writeable, readable, writeable, readable, writeable
LockType can be read, write, read, write, read, write, read, write, read, write
MarshalOptions readable, writeable, readable, writeable, readable, writeable, readable, writeable
MaxRecords readable, writeable, readable, writeable, readable, writeable, readable, writeable
PageCount does not support read-only read-only
PageSize can read, write, read, write, read, write, read, write, read, write
RecordCount does not support read-only read-only
Source can be readable, writeable, readable, writeable, readable, writeable, readable, writeable
State read only read only read only read only read only read only read only
Status read only read only read only read only read only read only
AddNew Support Support Support Support
CancelBatch Support Support Support Support
CancelUpdate Support Support Support Support
Clone does not support it or not
Close Support Support Support Support
Delete Support Support Support Support
GetRows Support Support Support
Move does not support support support support support
MoveFirst Support Support Support Support
MoveLast does not support support support support support
MoveNext Support Support Support Support
MovePrevious does not support support support support support
NextRecordset Support Support Support Support
Open Support Support Support Support
Requery Support Support Support Support
Resync does not support support support support
Supports Support Support
Update Support Support Support Support Support
UpdateBatch Support Support Support Support Support
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The NextRecordset method is not applicable to Microsoft Access database.
LockType
The LockType parameter of the Open method of the Recordset object indicates the Lock type to be adopted. If this parameter is ignored, the system will use the LockType property of the Recordset object as the preset value. The LockType parameters include adLockReadOnly, adLockPrssimistic, adLockOptimistic and adLockBatchOptimistic, etc., and are described as follows:
--------------------------------------------------------------------------------------------------------------------------------
Constant constant value description
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The default value of adLockReadOnly1, the Recordset object is started in read-only mode, and the AddNew, Update, Delete and other methods cannot be run.
adLockPrssimistic2 When the data source is being updated, the system temporarily locks other users' actions to maintain data consistency.
adLockOptimistic3 When the data source is being updated, the system will not lock other users' actions. Other users can add, delete and modify the data.
adLockBatchOptimistic4 When the data source is being updated, other users must change the CursorLocation property to adUdeClientBatch to increase the data.
Delete and modify operations.
Special part of Sqlserver database asp call
response.writews2(1)
w2=ws2(2)
response.write"a"
response.write"b"&ws2(3)&"c"
response.write"d"&w2
%>
Create a table
createtablefriends(name1varchar(10),phonevarchar(15))
Unlike access, the default database of SQLServer can only be read backwards, which is very strict.
Copy the copy table between databases
select*intocoolhe.dbo.mainfrommain
Delete the database
dropdatabasecoolhe
Find replacement functions
updatecommend
setGIF picture = stuff(GIF picture, 1, 21,"http://192.168.1.2")//Replace the 21 characters starting from the beginning of the GIF picture field
Change record
Automatically added fields cannot be changed
The solution is as follows
CREATETABLEnew_employees
(
id_numintIDENTITY(1,1),
fnamevarchar(20),
minitchar(1),
lnamevarchar(30)
)
INSERTnew_employees
(fname,minit,lname)
valueS
('Karin','F','Josephs')
Modify a record
updatemain
setGIF picture='new'whereid=4331
Add a record
Insertarticles
(category, title, link, updatetime)
values
('CPU','AMDAthlonXP processor technology and architecture','20011024/01.asp','10-24-2001')
Adding a record track from one table and another table
insertmain
selecta,b,c,dfromnew
When main has a field that is automatically added, the new table cannot select the automatically added field, and avoid writing the field in the above example.
Find all tables in the database
select*fromysobjectswhereextype='u'
Calculate the sum of a field
select'downloadcount'=sum(number of downloads)
Search table definition information
sp_help table name
Modify the table name
sp_rename'cool','commend'
Add table fields (if you add multiple fields at a time, no bit will be added)
ALTERTABLEmain
ADD switch bit
altertablemainadd age char(3), name varchar(8), gender char(2)
Modify table field type
The original name field type is char(10)
altertabletablenamealtercolumnNamevarchar(20)
Modify password
EXECsp_passwordNULL,'ok','Victoria' (when the password is empty) The username of Victoria is login
EXECsp_password'ok','coffee' password changed from ok to coffee
altertabletable_nameaddcolumncolumn_namedatatype
Description: Add a field (the syntax for deleting a field is not syntax.)
altertabletable_nameaddprimarykey(column_name)
Description: Change the definition of the table and set a field as the primary key.
altertabletable_namedropprimarykey(column_name)
Description: Delete the primary key definition
Set the default value of a field to 0
ALTERTABLEtable_nameADDCONSTRAINTDF_Test_FieldNameDEFAULT(0)FORFieldName
Don't change DF_Test_FieldName
Change field name
sp_rename'tablename.fieldname','newname','column';
column do not change
Comments
/*select*fromnews*/or --select*fromnews-
Where horizontal lines are used for insertion annotation
setrs=cn.execute(sql) This statement returns the structure after execution of a SQL statement and assigns the result to RS
cn.Executesql This statement just executes SQL statement
Data Type++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ datatypes
Smallint
16-bit integer.
interger
32-bit integer.
decimal(p,s)
The exact value of p and the decimal integer of the size of s. The exact value of p refers to the size values of all numbers (digits), and s refers to the number of digits after the decimal. If not specified, the system will be set to p=5;s=0.
float
A real number of 32 bits.
double
A real number of 64 bits.
char(n)
A string of n length cannot exceed 254.
varchar(n)
For strings whose length is not fixed and whose maximum length is n, n cannot exceed 4000.
graphic(n)
Like char(n), but its unit is two characters double-bytes, n cannot exceed 127. This form is used to support two character lengths, such as Chinese characters.
vargraphic(n)
A double-character string with a variable length and its maximum length is n, n cannot exceed 2000.
date
Includes year, month, and date.
time
Includes hours, minutes, and seconds.
timestamp
It includes year, month, day, hour, minute, second, and one thousandth of a second.
This type of stored procedure for recovery is provided in SQLServer.
1.sp_attach_db[@dbname=]dbname,[@filename1=]filename_n
Add a database to the system, specify the database name in dbname, and filename_n specifies the database file and log files. For example, I have a voogiya library. Stop the SQLServer service to backup voogiya_data.mdf, voogiya_log.ldf, start SQLserver, delete the library, and then copy the two files to the SQLserver DATA directory. Execute the following statement in QueryAnalyzer:
EXECsp_attach_db@dbname=Nvoogiya,
@filename1=Nd:/mssql7/data/voogiya_data.mdf, (successful if N is not added)
@filename2=Nd:/mssql7/data/voogiya_log.ldf (successful if N is not added)
This library will be added to the SQLServerGroup.
2.sp_attach_single_file_db[@dbname=]dbname,
[@physname=]physical_name
This command is the same as the above function. In physical_name, just write the physical file name of the database, and the log file SQLserver will be re-established. The following stored procedure must be executed first:
sp_detach_db@dbname=dbname
The same as above is an example:
EXECsp_detach_db@dbname=voogiya
EXECsp_attach_single_file_db@dbname=voogiya,
@physname=d:/mssql7/data/voogiya_data.mdf
It should be noted that users who execute the above stored procedures must be in sysadmin
The above is an introduction to the collection sharing of SQL statements. I hope you can like it. For more content, please continue to pay attention to the website of the Wuxin Technology Channel!