发表于 2009-1-5 19:48
| 
SQL璇硶澶у叏
鍙戝竷: 2008-7-01 14:14 | 浣滆: 缃戠粶杞浇 | 鏉ユ簮: 缃戠粶杞浇 | 鏌ョ湅: 429娆
SQL璇硶澶у叏
SQL璇硶澶у叏
1. ASP涓嶢ccess鏁版嵁搴撹繛鎺ワ細
dim conn,mdbfile
mdbfile=server.mappath("鏁版嵁搴撳悕绉.mdb")
set conn=server.createobject("adodb.connection")
conn.open "driver={microsoft access driver (*.mdb)};uid=admin;pwd=鏁版嵁搴撳瘑鐮;dbq="&mdbfile
2. ASP涓嶴QL鏁版嵁搴撹繛鎺ワ細
dim conn
set conn=server.createobject("ADODB.connection")
con.open "PROVIDER=SQLOLEDB;DATA SOURCE=SQL鏈嶅姟鍣ㄥ悕绉版垨IP鍦板潃;UID=sa;PWD=鏁版嵁搴撳瘑鐮;DATABASE=鏁版嵁搴撳悕绉
寤虹珛璁板綍闆嗗璞★細
set rs=server.createobject("adodb.recordset")
rs.open SQL璇彞,conn,3,2
3. SQL甯哥敤鍛戒护浣跨敤鏂规硶锛
(1) 鏁版嵁璁板綍绛涢夛細
sql="select * from 鏁版嵁琛 where 瀛楁鍚=瀛楁鍊 order by 瀛楁鍚 [desc]"
sql="select * from 鏁版嵁琛 where 瀛楁鍚 like \'%瀛楁鍊%\' order by 瀛楁鍚 [desc]"
sql="select top 10 * from 鏁版嵁琛 where 瀛楁鍚 order by 瀛楁鍚 [desc]"
sql="select * from 鏁版嵁琛 where 瀛楁鍚 in (\'鍊1\',\'鍊2\',\'鍊3\')"
sql="select * from 鏁版嵁琛 where 瀛楁鍚 between 鍊1 and 鍊2"
(2) 鏇存柊鏁版嵁璁板綍锛
sql="update 鏁版嵁琛 set 瀛楁鍚=瀛楁鍊 where 鏉′欢琛ㄨ揪寮"
sql="update 鏁版嵁琛 set 瀛楁1=鍊1,瀛楁2=鍊2 鈥︹ 瀛楁n=鍊糿 where 鏉′欢琛ㄨ揪寮"
(3) 鍒犻櫎鏁版嵁璁板綍锛
sql="delete from 鏁版嵁琛 where 鏉′欢琛ㄨ揪寮"
sql="delete from 鏁版嵁琛" (灏嗘暟鎹〃鎵鏈夎褰曞垹闄)
(4) 娣诲姞鏁版嵁璁板綍锛
sql="insert into 鏁版嵁琛 (瀛楁1,瀛楁2,瀛楁3 鈥) values (鍊1,鍊2,鍊3 鈥)"
sql="insert into 鐩爣鏁版嵁琛 select * from 婧愭暟鎹〃" (鎶婃簮鏁版嵁琛ㄧ殑璁板綍娣诲姞鍒扮洰鏍囨暟鎹〃)
(5) 鏁版嵁璁板綍缁熻鍑芥暟锛
AVG(瀛楁鍚) 寰楀嚭涓涓〃鏍兼爮骞冲潎鍊
COUNT(*|瀛楁鍚) 瀵规暟鎹鏁扮殑缁熻鎴栧鏌愪竴鏍忔湁鍊肩殑鏁版嵁琛屾暟缁熻
MAX(瀛楁鍚) 鍙栧緱涓涓〃鏍兼爮鏈澶х殑鍊
MIN(瀛楁鍚) 鍙栧緱涓涓〃鏍兼爮鏈灏忕殑鍊
SUM(瀛楁鍚) 鎶婃暟鎹爮鐨勫肩浉鍔
寮曠敤浠ヤ笂鍑芥暟鐨勬柟娉曪細
sql="select sum(瀛楁鍚) as 鍒悕 from 鏁版嵁琛 where 鏉′欢琛ㄨ揪寮"
set rs=conn.excute(sql)
鐢 rs("鍒悕") 鑾峰彇缁熺殑璁″硷紝鍏跺畠鍑芥暟杩愮敤鍚屼笂銆
(5) 鏁版嵁琛ㄧ殑寤虹珛鍜屽垹闄わ細
CREATE TABLE 鏁版嵁琛ㄥ悕绉(瀛楁1 绫诲瀷1(闀垮害),瀛楁2 绫诲瀷2(闀垮害) 鈥︹ )
渚嬶細CREATE TABLE tab01(name varchar(50),datetime default now())
DROP TABLE 鏁版嵁琛ㄥ悕绉 (姘镐箙鎬у垹闄や竴涓暟鎹〃)
4. 璁板綍闆嗗璞＄殑鏂规硶锛
rs.movenext 灏嗚褰曟寚閽堜粠褰撳墠鐨勪綅缃悜涓嬬Щ涓琛
rs.moveprevious 灏嗚褰曟寚閽堜粠褰撳墠鐨勪綅缃悜涓婄Щ涓琛
rs.movefirst 灏嗚褰曟寚閽堢Щ鍒版暟鎹〃绗竴琛
rs.movelast 灏嗚褰曟寚閽堢Щ鍒版暟鎹〃鏈鍚庝竴琛
rs.absoluteposition=N 灏嗚褰曟寚閽堢Щ鍒版暟鎹〃绗琋琛
rs.absolutepage=N 灏嗚褰曟寚閽堢Щ鍒扮N椤电殑绗竴琛
rs.pagesize=N 璁剧疆姣忛〉涓篘鏉¤褰
rs.pagecount 鏍规嵁 pagesize 鐨勮缃繑鍥炴婚〉鏁
rs.recordcount 杩斿洖璁板綍鎬绘暟
rs.bof 杩斿洖璁板綍鎸囬拡鏄惁瓒呭嚭鏁版嵁琛ㄩ绔紝true琛ㄧず鏄紝false涓哄惁
rs.eof 杩斿洖璁板綍鎸囬拡鏄惁瓒呭嚭鏁版嵁琛ㄦ湯绔紝true琛ㄧず鏄紝false涓哄惁
rs.delete 鍒犻櫎褰撳墠璁板綍锛屼絾璁板綍鎸囬拡涓嶄細鍚戜笅绉诲姩
rs.addnew 娣诲姞璁板綍鍒版暟鎹〃鏈
rs.update 鏇存柊鏁版嵁琛ㄨ褰
[8] [9] [10] [11] [12]
