Set of SQL statements

1. asp and Access database connection:
<% @ 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 and SQL database connection:
<% @ 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
%>

Create a record set object:

Setrs = server. Createobject ("ADODB. recordset ")
Rs. opensql statement, Conn, 3, 2

3. Common SQL commands:
(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 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 condition expression"

(3) Delete data records:
SQL = "where efrom data table where condition expression"
SQL = "deletefrom data table" (delete all records of 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 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 update 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 attribute. The source parameter can be a command object name, an SQL command, a specified data table name, or a stored
Procedure. If this parameter is omitted, the system uses the source attribute of the recordset object.

Activeconnection
The recordset object can be connected to the connection object through the activeconnection attribute. Activeconnection can be a connection object or a string containing the database connection information (connectionstring.

Cursortype
The cursortype parameter of the open method of the recordset object indicates the type of cursor to start data, including adopenforwardonly, adopenkeyset, adopendynamic, and adopenstatic, which are described as follows:
--------------------------------------------------------------
Constant Value description
-------------------------------------------------------------
The default value of adopenforwardonly0 is to start a forward cursor (forwardonly ).
Adopenkeyset1 starts a keyset-type cursor.
Adopendynamic2 starts a dynamic type cursor.
Adopenstatic3 starts a static cursor.
-------------------------------------------------------------
The preceding cursor types directly affect all attributes and methods of the recordset object. The following lists the differences between them.
-------------------------------------------------------------
Recordset attribute adopenforwardonlyadopenkeysetadopendynamicadopenstatic
-------------------------------------------------------------
Absolutepage does not support read/write.
Absoluteposition does not support read/write.
Activeconnection can be read/written or read/write
Bof read-only
Bookmark does not support read/write.
Cachesize: readable/writable
Cursorlocation: readable/writable
Cursortype can be read/written or read/write
Editmode read-only
Read-Only
EOF read-only
Read-Only
The filter can read and write, and can read and write.
Locktype: readable/writable
Marshaloptions: readable/writable
Maxrecords
Pagecount does not support read-only and read-only
Pagesize: readable/writable
Recordcount does not support read-only and read-only
Source: readable/writable
State read-only
Status read-only
Addnew support
Support for cancelbatch
Supported cancelupdate
Clone is not supported.
Support for close
Support for Delete
Getrows support
Move is not supported.
Supported by movefirst
Supported by movelast
Supported by movenext
Supported by moveprevious
Supported by nextrecordset
Open Support
Requery support
Not supported by Resync
Supports support
Update support
Supported by updatebatch
--------------------------------------------------------------
The nextrecordset method is not applicable to Microsoft Access databases.

Locktype
The locktype parameter of the open method of the recordset object indicates the lock type to be used. If this parameter is ignored, the system uses the locktype attribute of the recordset object as the default value. The locktype parameters include adlockreadonly, adlockprssimistic, adlockoptimistic, and adlockbatchoptimistic, which are described as follows:
-------------------------------------------------------------
Constant Value description
--------------------------------------------------------------
Adlockreadonly1 is the default value. The recordset object is started in read-only mode and cannot run addnew, update, delete, and other 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 the actions of other users. Other users can add, delete, and modify data.
Adlockbatchoptimistic4 when the data source is being updated, other users must change the cursorlocation attribute to adudeclientbatch to add,
Delete and modify operations.
Special part of SQL Server database ASP call
<%
Response. writews2 (1)
W2 = WS2 (2)
Response. Write ""
Response. Write "B" & WS2 (3) & "C"
Response. Write "D" & W2
%>

Create a table
Createtablefriends (name1varchar (10), phonevarchar (15 ))

Unlike access, sqlserver's default database can only be read backward, which is very strict.

Copy a table between databases
Select * distinct coolhe. DBO. mainfrommain

Delete Database
Dropdatabasecoolhe

Search and replace

Updatecommend
Setgif image = stuff (GIF image, "http: // 192.168.1.2") // Replace the 21 characters starting from the beginning of the GIF image field with

Change Record
The automatically added field 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', 'Joseph s ')

Modify a record
Updatemain
Setgif image = 'new' whereid = 4331

Add a record
Insertarticles
(Category, title, Link, updatetime)
Values
('Cpu ', 'amdathlonxp processor technology and architecture', '2017. asp ', '10-24-2001 ')

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

When there is a field in main that is automatically added, the newly added field cannot be selected for the new table, and the field is not written in the form of the above example.

Search all tables in the database

Select * fromsysobjectswherextype = 'U'

Calculate the sum of a field

Select 'downloadcount' = sum (downloads)

Retrieve table definition Information

Sp_help table name

Modify Table Name

Sp_rename 'cool ', 'commend'

Add Table fields (if BIT is not added after adding multiple fields at a time)

Altertablemain
Add switch bit

Altertablemainadd age char (3), name varchar (8), Gender char (2)

Modify Table Field Type
The original name field is of the char (10) type)
Altertabletablenamealtercolumn name varchar (20)

Change Password
Execsp_passwordnull, 'OK', 'victoria '(when the password is blank). Victoria is the login user name.
Execsp_password 'OK', 'coffee 'password changed from OK to coffee

Altertabletable_nameaddcolumncolumn_namedatatype

Note: Add a column (the syntax of a column is not deleted .)

Altertabletable_nameaddprimarykey (column_name)

Note: You can change the table definition to set a column as a primary key.

Altertabletable_namedropprimarykey (column_name)

Delete the definition of the primary key

Set the default value of a field to 0.

Altertabletable_nameaddconstraintdf_test_fieldnamedefault (0) forfieldname

Do not change the df_test_fieldname.

Change Field name
Sp_rename 'tablename. fieldname', 'newname', 'column ';
Do not change column

Note
/* Select * fromnews */or -- select * fromnews --
The horizontal line is used for inserting comments.

Setrsw.cn.exe cute (SQL) This statement returns the structure after an SQL statement is executed and assigns the result to Rs.
CN. executesql this statement only executes SQL statements

Data Type ++ +++ ++ PES ypes
Smallint
An integer of 16 bits.

Interger
An integer of 32 bits.

Decimal (P, S)
The exact value of P and the decimal integer of S. The exact value of P refers to the number of digits after a decimal number (digits. If not specified, the system is set to P = 5; S = 0.

Float
32-bit real number.

Double
The real number of 64-bit RMB.

Char (N)
A string of N length. N cannot exceed 254.

Varchar (N)
A string with an unfixed length and a maximum length of N. N cannot exceed 4000.

Graphic (N)
The Unit is the same as char (N), but it is a double-bytes character. N cannot exceed 127. This form is a font supporting two character lengths, such as Chinese characters.

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

Date
Contains the year, month, and date.

Time
Contains hours, minutes, and seconds.

Timestamp
Including year, month, day, hour, minute, second, 1‰ seconds.

This recovery method is provided in SQL Server.

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

Add a database to the system, specify the database name in dbname, and specify the database file and log file in filename_n. For example, if I have a voogiya database, stop the sqlserver service to back up voogiya_data.mdf, voogiya_log.ldf, start sqlserver, delete the database, and then copy the files to the sqlserver data directory, execute the following statement in queryanalyzer:

Execsp_attach_db @ dbname = nvoogiya,
@ Filename1 = Nd: \ MSSQL7 \ data \ voogiya_data.mdf (success if n is not added)
@ Filename2 = Nd: \ MSSQL7 \ data \ voogiya_log.ldf (success if n is not added)

This library will be added to sqlservergroup.
2. sp_attach_single_file_db [@ dbname =] dbname,
[@ Physname =] physical_name

This command is similar to the above function. In physical_name, you only need to write the physical file name of the database, and the log file sqlserver will be re-created. To run this stored procedure, you must first execute the following stored procedure:

Sp_detach_db @ dbname = dbname

Take the preceding example as an example:

Execsp_detach_db @ dbname = voogiya
Execsp_attach_single_file_db @ dbname = voogiya,
@ Physname = D: \ MSSQL7 \ data \ voogiya_data.mdf

Note that the user executing the above stored procedure should be in SysAdmin