-- 精確匹配
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
-- 更新單一欄位
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
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
-- 建立資料表
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
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;"
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
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
conn.Execute "UPDATE articles SET view_count = view_count + 1 WHERE id = " & CLng(id)
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")
<%
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 ""
%>
這份速查手冊涵蓋 ASP 開發 90% 的資料庫操作場景,建議列印備查或整合至專案代碼片段庫。