Some sets of SQL statements _asp base

1.ASP Connect to Access database:
<% @language =vbscript%>
<%
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.ASP connection with SQL database:
<% @language =vbscript%>
<%
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
%>

To set up a Recordset object:

Setrs=server.createobject ("Adodb.recordset")
Rs.opensql statement, conn,3,2

3.SQL Common Command usage:
(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
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
Recordset Object Methods
Open method
Recordset. Opensource,activeconnection,cursortype,locktype,options
Source
The Recordset object can connect to the command object through the Source property. The source parameter can be a command object name, a section of SQL command, a specified data table name, or a stored
Procedure. If this argument is omitted, the system takes the source property of the Recordset object.

ActiveConnection
The Recordset object can connect connection objects by ActiveConnection properties. The ActiveConnection can be a connection object or a string parameter containing the database connection information (ConnectionString).

CursorType
The CursorType parameter of the Recordset object Open method indicates what cursor type will start the data, including adOpenForwardOnly, adOpenKeyset, adOpenDynamic, and adOpenStatic. It is described as follows:
--------------------------------------------------------------
Constants Constant Numerical description
-------------------------------------------------------------
AdOpenForwardOnly0 the default value, starting a cursor (forwardonly) that moves forward only.
AdOpenKeyset1 launches a keyset type of cursor.
AdOpenDynamic2 launches a dynamic-type cursor.
ADOPENSTATIC3 launches a static type of cursor.
-------------------------------------------------------------
These cursor types will directly affect all properties and methods of the Recordset object, and the following list illustrates the differences between them.
-------------------------------------------------------------
Recordset Properties Adopenforwardonlyadopenkeysetadopendynamicadopenstatic
-------------------------------------------------------------
AbsolutePage does not support read-write writable
AbsolutePosition does not support read-write writable
ActiveConnection can read, write, read, write, read and write
BOF read-only read-only read-only
Bookmark does not support read-write writable
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 and write
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 and write
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 for support
Delete Support support for 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
NextRecordset Support Support
Open Support support supporting support
Requery support Support Support support
Unsupported support support is not supported by Resync
Supports support support
Update support support supporting support
UpdateBatch Support Support
--------------------------------------------------------------
Where the NextRecordset method does not apply to the MicrosoftAccess database.

LockType
The LockType parameter of the Recordset object's Open method represents the lock type to take, and if this argument is omitted, then the system takes the LockType property of the Recordset object as the preset value. The LockType parameters include adLockReadOnly, adlockprssimistic, adLockOptimistic, and adLockBatchOptimistic, as described below:
-------------------------------------------------------------
Constants Constant Numerical description
--------------------------------------------------------------
AdLockReadOnly1 default, the Recordset object starts as read-only and cannot run AddNew, Update, and Delete methods
AdLockPrssimistic2 when the data source is being updated, the system temporarily locks the actions of other users to maintain data consistency.
ADLOCKOPTIMISTIC3 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.
ADLOCKBATCHOPTIMISTIC4 when the data source is being updated, other users must change the CursorLocation property to Adudeclientbatch to increase the data.
Delete, change the operation.
Special sections of SQL Server database ASP calls
<%
RESPONSE.WRITEWS2 (1)
W2=WS2 (2)
Response.Write "a"
Response.Write "B" &ws2 (3) & "C"
Response.Write "D" &w2
%>

Create a table
Createtablefriends (Name1varchar), Phonevarchar (15))


Unlike access, SQL Server's default database can only be read backwards, very strictly.

Copy a table between databases
Select*intocoolhe.dbo.mainfrommain

Delete Database
Dropdatabasecoolhe

Find Replacement function

Updatecommend
Setgif picture =stuff (gif picture, 1,21, "http://192.168.1.2")//Replace GIF picture field 21 characters starting from scratch
Change record
Fields that are automatically added 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 ')

Add a record path from one table to another table
Insertmain
Selecta,b,c,dfromnew

When main has a field that is automatically incremented, the new table cannot select fields that are automatically incremented, rather than writing in the form of the above example

Find all tables in a database

Select*fromsysobjectswherextype= ' u '

Calculates the and of a field

Select ' Downloadcount ' =sum (download times)

Retrieving table definition Information

sp_help table Name

Modify Table Name

Sp_rename ' cool ', ' commend '

Add a table field (if you add more than one field at a time and no bit after)

Altertablemain
Add switch bit

Altertablemainadd Age char (3), name varchar (8), sex char (2)

Modify a table field type
The type of the original last Name field is char (10)
Altertabletablenamealtercolumn name varchar (20)

Modify password
Execsp_passwordnull, ' OK ', ' Victoria ' (when the password is blank) Victoria the username for login
Execsp_password ' OK ', ' coffee ' password changed from OK to coffee

Altertabletable_nameaddcolumncolumn_namedatatype

Description: Adds a field (without deleting a field's syntax.) )

Altertabletable_nameaddprimarykey (column_name)

Description: Change the definition of a table to set a field as the primary key.
　　
Altertabletable_namedropprimarykey (column_name)

Description: Delete the definition of the primary key

Set the default value of one field to 0

Altertabletable_nameaddconstraintdf_test_fieldnamedefault (0) Forfieldname

Df_test_fieldname, don't change.

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 inserted annotations

Setrs=cn.execute (SQL) This statement returns the structure of the execution of an SQL statement, assigning the result to Rs
cn. ExecuteSQL This statement simply executes the SQL statement

Data type +++++++++++++++++++++++++++++++++++++++++++++++++datatypes
smallint
A 16-bit integer.

Interger
A 32-bit integer.

Decimal (P,s)
P exact value and s size of the decimal integer, the exact value P refers to all have a few number (digits) size value, S is a decimal number after a few. If not specifically specified, the system is set to p=5;s=0.

Float
A 32-bit real number.

Double
A 64-bit real number.

CHAR (n)
N-length string, n cannot exceed 254.

VARCHAR (n)
A string whose length is not fixed and its maximum length is n, which cannot exceed 4000.

Graphic (n)
Same as char (n), but its unit is two characters double-bytes,n cannot exceed 127. This form is for fonts that support two character lengths, such as Chinese characters.

Vargraphic (N)
A two-character string with a variable length and a maximum length of n, and N cannot exceed 2000.

Date
Contains the year, month, and date.

Time
Contains the hours, minutes, and seconds.

Timestamp
Includes year, month, day, time, minutes, seconds, 1 per thousand seconds.



A stored procedure that provides this type of recovery in SQL Server.

1.sp_attach_db[@dbname =]dbname,[@filename1 =]filename_n

Add a database to the system, specify the database name in dbname, filename_n the file and log files for the specified database. For example, I have a Voogiya library, stop SQL Server service backup VOOGIYA_DATA.MDF,VOOGIYA_LOG.LDF, start SQL Server, delete the library, and then copy the two files to the SQL Server data directory , execute the following statement in Queryanalyzer:

Execsp_attach_db@dbname=nvoogiya,
@filename1 =nd:\mssql7\data\voogiya_data.mdf, (Success without N)
@filename2 =nd:\mssql7\data\voogiya_log.ldf (Success without N)

The library will be added to the sqlservergroup.
2.sp_attach_single_file_db[@dbname =]dbname,
[@physname =]physical_name

This command, like the one above, allows SQL Server to re-establish the log file as long as the physical file name of the library is written in the physical_name. This stored procedure is run to execute the following stored procedures:

Sp_detach_db@dbname=dbname

Also take the above example:

Execsp_detach_db@dbname=voogiya
Execsp_attach_single_file_db@dbname=voogiya,
@physname =d:\mssql7\data\voogiya_data.mdf

Be aware that users executing the above stored procedures will be in the sysadmin