ASP中使用SQL语句教程.doc

1. SELECT SQLSELECT SQLSELECT what FROM whichTable WHERE criteriaASPASPSELECT SQL = SELECT what FROM whichTable WHERE criteriaASPSQLSQLProducts SQL =SELECT * FROM ProductsSQLp_name * SQL =SELECT p_name FROM ProductsProducts p_name 2. WHERErecordsetp_name wWHERE SQL =SELECT p_name FROM Products WHERE p_name LIKE W%WHERE w p_name %w west willow Products SELECT recordset SQLSELECT SELECT WHEREWHERE = =SELECT * FROM Products WHERE p_price = 199.95SELECT * FROM Products WHERE p_price 19.95SELECT * FROM Products WHERE p_version = 4 4 4 SELECT recordset3. LIKE NOT LIKE BETWEENwLIKELIKE5SKU15_%SQL = SELECT * FROM Products WHERE p_sku LIKE 1_51 _ _ _ 551_ _ _ 5SKULIKENOTBETWEENBETWEEN 1 10BETWEENWHERE ID BETWEEN 1 AND 10WHERE ID = 1 AND ID = 104. SQLrecordsetAND ORNOTSQLSQL =SELECT c_firstname, c_lastname, c_email FROM customers WHERE c_email ISNOT NULL AND c_purchase = 1 OR c_purchase = 2 AND c_lastname LIKEA%SQLSQLSQLSQL = SELECT c_firstname, c_lastname, c_emailaddress, c_phoneSQL = SQL & FROM customersSQL = SQL & WHERE c_firstname LIKE A% and c_emailaddress NOT NULLSQL = SQL & ORDER BY c_lastname, c_firstnameSQLSELECT SELECT c_firstname, c_lastname, c_emailaddress, c_phone FROM customersWHERE c_firstname LIKE A% and c_emailaddress NO NULL ORDER BY c_lastname,c_firstname5. SELECTASPSQLSQL ASP recordsetSQLrecordsetrecordsetDim objRecSet objRec = Server.CreateObject (ADODB.Recordset)objRec.Open customers, objConn, 0, 1, 2ASPcustomersrecordsetSQLASPrecordsetDim objRecSet objRec = Server.CreateObject (ADODB.Recordset)objRec.Open SQL, objConn, 0, 1, 2objRec.Open,SQL0, 1 ,2 SQLSQLrecordsetDim objRecset objRec = objConn.Execute(SQL)SQLSQL SELECT SQLrecordset objRecrecordsetrecordsetSQLrecordset ASPrecordset if-then SQLASPSQLResponse.WriteyourVariable SQLASP6. SQLSQLHTMLActiveCommand ASPSet objSQ = Server.CreateObject (ADODB.Command)objSQ.ActiveConnection = databaseNameobjSQ.CommandText = storedQueryNameobjSQ.CommandType = adCmdStoredProcset objRec = objSQ.ExecuteadCmdStoredProc adovbs.inc AccessadCmdStoredProc 7. ORDER BYAccessAccessSort ByASPrecordsetORDER BY SELECTORDER BYCustomersSQL = SELECT c_lastname, c_firstname, c_email FROM Customers ORDER BY c_lastnamerecordset8. ASPRecordCount recordsetSELECTcount(*) SQL = SELECT count(*) FROM CustomersSQL = SELECT count(*) FROM Customers WHERE c_lastname LIKE A%SQL = SELECT c_firstname, c_la