萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP.NET 程式設計

站長必備:ASP 與 SQL 常用技巧速查手冊 (增強版)

SQL 常用指令速查

1. 資料篩選查詢

-- 精確匹配
SELECT * FROM table WHERE field = 'value' ORDER BY sort_field [DESC]

-- 模糊查詢
SELECT * FROM table WHERE field LIKE '%keyword%' ORDER BY sort_field [DESC]

-- 取前 N 筆 (SQL Server/Access)
SELECT TOP 10 * FROM table WHERE field = 'value' ORDER BY sort_field DESC

-- 多值匹配
SELECT * FROM table WHERE field IN ('val1','val2','val3')

-- 範圍查詢
SELECT * FROM table WHERE field BETWEEN val1 AND val2

-- 多條件組合
SELECT * FROM table WHERE (field1 = 'a' OR field1 = 'b') AND field2 > 100

2. 資料異動 (CUD)

-- 更新單一欄位
UPDATE table SET field = 'new_value' WHERE condition

-- 更新多欄位
UPDATE table SET field1 = 'v1', field2 = 'v2', field3 = 'v3' WHERE condition

-- 刪除條件刪除
DELETE FROM table WHERE condition

-- 刪除全表 (慎用!)
DELETE FROM table

-- 新增單筆 (指定欄位)
INSERT INTO table (field1, field2, field3) VALUES ('v1', 'v2', 'v3')

-- 新增查詢結果 (表複製)
INSERT INTO target_table SELECT * FROM source_table WHERE condition

3. 統計彙總函數

SELECT 
    COUNT(*) AS total_rows,
    COUNT(field) AS non_null_count,
    SUM(amount) AS total_amount,
    AVG(price) AS avg_price,
    MAX(score) AS max_score,
    MIN(score) AS min_score
FROM table 
WHERE condition

-- 分組統計
SELECT category, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 10

4. 資料表結構 (DDL)

-- 建立資料表
CREATE TABLE tab01 (
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50) NOT NULL,
    created_at DATETIME DEFAULT GETDATE()
)

-- 刪除資料表 (永久)
DROP TABLE tab01

-- 複製表結構與資料 (去重範例)
SELECT DISTINCT * INTO temp_table FROM source_table
DROP TABLE source_table
SELECT * INTO source_table FROM temp_table
DROP TABLE temp_table

ASP 連接字串標準庫

SQL Server (OLE DB - 推薦)

Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLNCLI11;Data Source=SERVER_IP;Initial Catalog=DB_NAME;User ID=uid;Password=pwd;"

Access (Jet/ACE OLEDB)

Dim conn, connStr
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/data/db.mdb") & ";"
' ACCDB 用: Provider=Microsoft.ACE.OLEDB.12.0;
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr

ASP 常用操作模式

1. 讀取並顯示 (唯讀、最高效能)

Set rs = conn.Execute("SELECT id, title FROM news WHERE is_pub=1 ORDER BY id DESC")
Do While Not rs.EOF
    Response.Write "" & rs("title") & "
" rs.MoveNext Loop rs.Close: Set rs = Nothing

2. 修改單一欄位 (原子操作)

conn.Execute "UPDATE articles SET view_count = view_count + 1 WHERE id = " & CLng(id)

3. 刪除記錄 + 實體檔案 (圖片)

Set rs = conn.Execute("SELECT pic_path FROM articles WHERE id = " & CLng(id))
If Not rs.EOF Then
    Dim fso, fullPath
    Set fso = Server.CreateObject("Scripting.FileSystemObject")
    fullPath = Server.MapPath(rs("pic_path"))
    If fso.FileExists(fullPath) Then fso.DeleteFile fullPath, True
    rs.Close
    
    conn.Execute "DELETE FROM articles WHERE id = " & CLng(id)
End If
Set fso = Nothing
Response.Redirect Request.ServerVariables("HTTP_REFERER")

ASP 分頁經典實作 (通用版)

<%
Dim pageSize, pageNum, totalRows, totalPages, startRow, endRow
pageSize = 20
pageNum = CInt(Request.QueryString("page"))
If pageNum < 1 Then pageNum = 1

' 1. 取得總筆數
Set rs = conn.Execute("SELECT COUNT(*) AS cnt FROM articles WHERE is_pub=1")
totalRows = rs("cnt")
rs.Close

totalPages = Int((totalRows + pageSize - 1) / pageSize)
If pageNum > totalPages Then pageNum = totalPages

' 2. 計算分頁範圍 (SQL Server 2012+ OFFSET/FETCH 或通用子查詢)
startRow = (pageNum - 1) * pageSize + 1
endRow = pageNum * pageSize

sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY id DESC) AS rn, * FROM articles WHERE is_pub=1) t WHERE rn BETWEEN " & startRow & " AND " & endRow
Set rs = conn.Execute(sql)

' 3. 顯示資料...
Do While Not rs.EOF
    ' ...
    rs.MoveNext
Loop
rs.Close

' 4. 分頁導航列
Response.Write ""
%>

安全與效能鐵律

  1. 參數化查詢:所有用戶輸入進 SQL 必須用 Command 參數化
  2. 最小權限:網站資料庫帳號僅擁有必要 CRUD 權限
  3. 錯誤不外露:生產環境關閉詳細錯誤,記錄日誌供開發查閱
  4. 連線池善用:盡早 Close、設 Nothing,勿長連線
  5. 索引優化:WHERE/JOIN/ORDER BY 欄位建立索引,避免 SELECT *

這份速查手冊涵蓋 ASP 開發 90% 的資料庫操作場景,建議列印備查或整合至專案代碼片段庫。

copyright © 萬盛學電腦網 all rights reserved