ÈçºÎÌá¸ßaspµÄSQLµÄÖ´ÐÐЧÂÊÌá¸ßÊý¾Ý¿â¶ÁÈ¡ËÙ¶È
·½·¨Ò»¡¢¾¡Á¿Ê¹Óø´ÔÓµÄSQLÀ´´úÌæ¼òµ¥µÄÒ»¶Ñ SQL.
ͬÑùµÄÊÂÎñ£¬Ò»¸ö¸´ÔÓµÄSQLÍê³ÉµÄЧÂʸßÓÚÒ»¶Ñ¼òµ¥SQLÍê³ÉµÄЧÂÊ¡£Óжà¸ö²éѯʱ£¬ÒªÉÆÓÚʹÓÃJOIN¡£
oRs=oConn.Execute("SELECT * from Books")
while not oRs.Eof
strSQL = "SELECT * from Authors WHERE AuthorID="&oRs("AuthorID") oRs2=oConn.Execute(strSQL)
Response.write oRs("Title")&">>"&oRs2("Name")&"<br>&q uot;
oRs.MoveNext()
wend
Òª±ÈÏÂÃæµÄ´úÂëÂý£º
strSQL="SELECT Books.Title,Authors.Name from Books JOIN Authors ON Authors.AuthorID=Books.AuthorID"
oRs=oConn.Execute(strSQL)
while not oRs.Eof
Response.write oRs("Title")&">>"&oRs("Name")&"<br>&qu ot;
oRs.MoveNext()
wend
·½·¨¶þ¡¢¾¡Á¿±ÜÃâʹÓÿɸüРRecordset
oRs=oConn.Execute("SELECT * from Authors WHERE AuthorID=17",3,3)
oRs("Name")="DarkMan"
oRs.Update()
Òª±ÈÏÂÃæµÄ´úÂëÂý£º
strSQL = "UPDATE Authors SET Name='DarkMan' WHERE AuthorID=17"
oConn.Execute strSQL
·½·¨Èý¡¢¸üÐÂÊý¾Ý¿âʱ£¬¾¡Á¿²ÉÓÃÅú´¦ Àí¸üÐÂ
½«ËùÓеÄSQL×é³ÉÒ»¸ö´óµÄÅú´¦ÀíSQL£¬²¢Ò»´ÎÔËÐУ»Õâ±ÈÒ»¸öÒ»¸öµØ¸üÐÂÊý¾ÝÒªÓÐЧÂʵöࡣÕâÑùÒ²¸ü¼ÓÂú×ãÄã½øÐÐÊÂÎñ´¦Àí µÄÐèÒª£º
strSQL=""
strSQL=strSQL&"SET XACT_ABORT ON\n";
strSQL=strSQL&"BEGIN TRANSACTION\n";
strSQL=strSQL&"INSERT INTO Orders(OrdID,CustID,OrdDat) VALUES('9999','1234',GETDATE())\n";
strSQL=strSQL&"INSERT INTO OrderRows(OrdID,OrdRow,Item,Qty) VALUES('9999','01','G4385',5)\n";
strSQL=strSQL&"INSERT INTO OrderRows(OrdID,OrdRow,Item,Qty) VALUES('9999','02','G4726',1)\n";
strSQL=strSQL&"COMMIT TRANSACTION\n";
strSQL=strSQL&"SET XACT_ABORT OFF\n";
oConn.Execute(strSQL);
ÆäÖУ¬SET XACT_ABORT OFF Óï¾ä¸æËßSQL Server£¬Èç¹ûÏÂÃæµÄÊÂÎñ´¦Àí¹ý³ÌÖУ¬Èç¹ûÓöµ½´íÎ󣬾ÍÈ¡ÏûÒѾÍê³ÉµÄÊÂÎñ¡£
·½·¨ËÄ¡¢Êý¾Ý¿âË÷Òý
ÄÇЩ½«ÔÚWhere×Ó¾äÖгöÏÖµÄ×ֶΣ¬ÄãÓ¦¸ÃÊ×ÏÈ¿¼Âǽ¨Á¢Ë÷Òý£»ÄÇЩÐèÒªÅÅÐòµÄ×ֶΣ¬Ò²Ó¦¸ÃÔÚ¿¼ÂÇÖ®ÁÐ ¡£
ÔÚMS AccessÖн¨Á¢Ë÷ÒýµÄ·½·¨£ºÔÚAccessÀïÃæÑ¡ÔñÐèÒªË÷ÒýµÄ±í£¬µã»÷“Éè¼Æ”£¬È»ºóÉèÖÃÏàÓ¦×ֶεÄË÷Òý.
ÔÚMS SQL ServerÖн¨Á¢Ë÷ÒýµÄ·½·¨£ºÔÚSQL Server¹ÜÀíÆ÷ÖУ¬Ñ
Ïà¹ØÎĵµ£º
´æ´¢¹ý³Ì´úÂ룺
--drop procedure p_page
--go
create procedure p_page
(
@Tables varchar(1000), --±íÃûÈçtesttable
@PrimaryKey varchar(100),--±íµÄÖ÷¼ü,±ØÐëΨһÐÔ
@Sort varchar(200) = NULL,--ÅÅÐò×Ö¶ÎÈçf_Name asc»òf_name desc(×¢ÒâÖ»ÄÜÓÐÒ»¸öÅÅÐò×Ö¶Î)
@CurrentPage int = 1,--µ± ......
SQLÃæÊÔÌâ
Sql³£ÓÃÓï·¨
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK) Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨ ......
×¢£º¸ßΣ£¡
ÓÐʱºò£¬¿ª·¢ÈËÔ±ÔÚÓ¦Ó÷þÎñÆ÷ÉÏ£¬ÄÜÄõ½Êý¾Ý¿âµÄÕʺźÍÃÜÂë
Èç¹ûÏëÈÃDBAËÀµô£¬Ì«¼òµ¥ÁË£¨¹þ¹þ¹þ¡«¡«£¬ÓÐÈËÔÚ¼éЦ¡«¡«£¡£©
ËùÒÔDBA°¡£¬µÃ´¦´¦Ð¡ÐÄ¡£¡£
£¨ÓÐÈË˵»°ÁË£ºÄãɵX°É£¬Ó¦ÓóÌÐò·þÎñÆ÷ÔõôÄÜÈÿª·¢ÈËÔ±Ëæ±ãÉÏ£¿£¡£©
ºÙ£¬¾ÍÊÇÉÏÁË£¬ÄãDBAÄÜÕ¦Ñù£¿
Èç¹û¼¼ÊõÉÏʹÊý¾Ý¿âÕʺÅÖ»ÄÜ´Óij¸ö»úÆ÷£¨»òij¸öIPµØ ......
֮ǰÓÐÒ»¸öSQLServerµÄ·ÖÒ³´æ´¢¹ý³Ì µ«ÊÇÐÔÄܲ»ÊÇÊ®·ÖÀíÏë
ÓÖÕÒÁËÒ»¸ö
--SQL2005·ÖÒ³´æ´¢¹ý³Ì
/**
if exists(select * from sysobjects where name='fenye')
drop proc fenye
**/
CREATE procedure fenye
@tableName nvarchar(200) ,
@pageSize int,
@curPage int ,
......