選擇：select * from table1 where 范圍
插入：insert into table1(field1,field2) values(value1,value2)
刪除：delete from table1 where 范圍
更新：update table1 set field1=value1 where 范圍
查找：select * from table1 where field1 like 』%value1%』 （所有包含『value1』這個模式的字元串）---like的語法很精妙，查資料!
排序：select * from table1 order by field1,field2 [desc]
2. sql 防注入代碼
<%
dim sql_injdata
SQL_injdata = "'|and|exec|insert|select|delete|update|count|*|%|chr|mid|master|truncate|char|declare"
SQL_inj = split(SQL_Injdata,"|")
If Request.QueryString<>"" Then
For Each SQL_Get In Request.QueryString
For SQL_Data=0 To Ubound(SQL_inj)
if instr(Request.QueryString(SQL_Get),Sql_Inj(Sql_DATA))>0 Then
Response.Write "<Script Language=javascript>alert('注意：請不要提交非法請求！');history.back(-1)</Script>"
Response.end
end if
next
Next
End If
If Request.Form<>"" Then
For Each Sql_Post In Request.Form
For SQL_Data=0 To Ubound(SQL_inj)
if instr(Request.Form(Sql_Post),Sql_Inj(Sql_DATA))>0 Then
Response.Write "<Script Language=javascript>alert('注意：請不要提交非法請求！');history.back(-1)</Script>"
Response.end
end if
next
next
end if
%>
寫在資料庫連接文件中
3. SQL查詢語句中，這個什麼意思 JOIN dim.city dc USING (cityid)
意思就是多表聯合查詢，使用cityid進行兩個表連接。
JOIN
dim.city dc USING (cityid)
inner join dim.deal dl on dl.cityid=dc.cityid
4. 關於SQL語句的簡單問題
如果有現成的表就可以
insert into table1(A,B,C)
select A,B,C
from table2
如果沒有的話手工用
insert into table1
select 數據A,數據B,數據C union
.......
這樣又不太現實,
如果你是把EXCEL之類的裡面的數據插進去,當然最好是用程序來解決了,具體怎麼寫,就要看用的是啥語言.
=================================
這是我以前用VBSCRIPT寫的
Call test
Sub test()
Dim xlapp
Dim ExcelSheet
Dim ExcelBook
Dim xlfilecontrol
Dim ForReading
Dim rs
Dim i
Dim j
Dim count
Dim fso
Dim daterecord
Dim dr
Dim strSQL
Dim strSQLS
Dim strCon
Dim iCon
Dim rscompare
Dim com
Dim countryc '插入表的相關變數
Dim countryna
Dim note
Dim biexcl
Dim statu
Dim fileName
Dim dateDis
Dim getDate
Dim getDate2
Dim daytest
Dim monthtest
Dim getTime
Dim hour
Dim second
Dim minute
Dim Timedis
Dim dateTime
Dim count2
Dim count3
Dim judge
Dim verdict
Dim verdictrs
Dim verdict2
Dim verdict2rs
count3=0
count2=0
'fileName = "ctydrug_20080101101010"
rs = ""
ForReading = 1
i=2
j=1
count=0
dr=0
judge=0
verdict=0
verdict2=0
set iCon = CreateObject("Adodb.connection")
set rs = CreateObject("Adodb.Recordset")
set xlapp = CreateObject("Excel.Application")
set ExcelBook = xlapp.workbooks.open("G:\vbscript\testexcel.xls")
set fso = createobject("Scripting.FileSystemObject")
'daterecord = cstr(fso.GetFile("D:\2007-11\20080306job\ctydrug_20080101101010.xls").DateLastModified)
'msgbox daterecord
strCon = "UID=sa;PWD=;Database=yxlovemoney01;" & _
"Driver={SQL Server};" & _
"Server=.;" & _
"Provider=SQLOLEDB.1;"
iCon.Open strCon
strSQLS = "select * from ctyDrug"
'set rscompare = iCon.Execute(strSQLS)
rs.Open strSQLS,iCon,adOpenForwardOnly,1,adCmdText ' 打開結果集
xlapp.visible = true
'得到文件日期,時間============================
'set xlfilecontrol = xlapp.workbooks.open("D:\2007-11\20080306job\testExcel.xls")
'=====================================================================
set ExcelSheet = ExcelBook.ActiveSheet
'xlfilecontrol.worksheets("Sheet1").Activate
rs.MoveFirst
'rs = ExcelBook.worksheets("Sheet1").cells(i,1).value
do while count<>2
if j=1 then '捉取不同列的欄位
countryc = ExcelBook.worksheets("Sheet1").cells(i,j).value
'=============檢查資料庫中是否有相同內容==================
if trim(rs.Fields("CountryCode"))=trim(countryc) then
' msgbox "這有執行？"
verdict=verdict+1
verdictrs=rs(1)
end if
loop
'rscompare.movefirst
'=========================================================
'==============檢查是否有帶 ' 的字元串====================
if countryc="" then
count =count + 1
end if
elseif j=2 then
countryn = ExcelBook.worksheets("Sheet1").cells(i,j).value
'=============檢查資料庫中是否有相同內容==================
' do while not rs.EOF
if trim(rs.Fields("CountryName"))<>trim(countryn) then
' msgbox "這有執行？"
'msgbox "unlikeness!"
msgbox countryn
verdict2=verdict2+1
end if
rs.movenext
'loop
'rscompare.movefirst
'=========================================================
'==============檢查是否有帶 ' 的字元串====================
if Instr(countryn,"'")>0 then
countryn = mid(countryn,1,Instr(countryn,"'")-1)&"''"&mid(countryn,Instr(countryn,"'")+1)
end if
'==========================================================
if countryn="" then
count =count + 1
end if
end if
j = j+1
if j>2 Then
rs.movenext
j=1
i = i + 1
'MsgBox ("Error #:2 " & CStr(Err.Number) & " " & Err.Description)
if verdict2=1 then
count3=count3+1
'msgbox count3
'msgbox "this is update"
'MsgBox ("Error #:2 " & CStr(Err.Number) & " " & Err.Description)
strSQL = "update ctyDrug set CountryName='"&countryn&"',lastupdate='"&dateTime&"',bitDelete='"&status&"',bitExclusion='"&biexcl&"',UpdateDate=getdate() where CountryCode='"&verdictrs&"'"
iCon.Execute strSQL
'MsgBox ("Error #:2 " & CStr(Err.Number) & " " & Err.Description)
elseif note="NULL" and verdict=0 then
' msgbox "null"
count2 = count2 + 1
strSQL = "insert into ctyDrug(CountryCode,CountryName,lastupdate,bitDelete,bitExclusion,"& _
"UpdateDate) values('"&countryc&"','"&countryn&"','"&dateTime&"','"&status&"','"&biexcl&"',getdate())"
iCon.Execute strSQL
else'if note<>"NULL" and verdict2=0 and verdict=0 then
count2 = count2 + 1
strSQL = "insert into ctyDrug(CountryCode,CountryName,Notes,lastupdate,bitDelete,bitExclusion,"& _
"UpdateDate) values('"&countryc&"','"&countryn&"','"¬e&"','"&dateTime&"','"&status&"','"&biexcl&"',getdate())"
iCon.Execute strSQL
end if
'msgbox "insert ok!"
countryc = ""
countryn = ""
note = ""
biexcl = ""
count = 0
verdict=0
verdict2=0
end if
loop
Set ExcelSheet = Nothing
ExcelBook.close
set ExcelBook = Nothing
xlapp.quit
set xlapp = Nothing
set rs = Nothing
msgbox "update"&count3
msgbox "ok!"&count2
end sub
5. dim定義的問題
如果你的程序有這一句的話,不需要對變數先定義再使用,否則,必須得先定義:
<%Option Explicit%>
先定義再使用變數是編程的一個好習慣,這樣的影響是多方面的阿,你真正寫程序,調試的時候就明白了.
所以建議樓主最好還是先定義再使用.
6. 請教SQL基本語句
SQL SELECT 語句
SELECT 語句用於從表中選取數據。表格式的結構被存儲在一個結果表中（稱為結果集）。
語法：SELECT 列名稱 FROM 表名稱
注釋：SQL 語句對大小寫不敏感。SELECT 等效於 select。
Update 語句
Update 語句用於修改表中的數據。
語法：UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值
DELETE 語句
DELETE 語句用於刪除表中的行。
語法：DELETE FROM 表名稱 WHERE 列名稱 = 值
7. 還是SQL語句的問題。
不好意思，當中沒注意，寫漏了點，Sorry!
下面是新的,本人測試過了，絕對好用！
感謝支持！！！
<!--#include file="admin.asp"-->
<!--#include file="conn.asp"-->
<%
Sql = "select distinct email into [#aaa] from [email]"
conn.execute(sql)
Sql = "drop table [email]"
conn.execute(sql)
Sql = "select * into [email] from [#aaa]"
conn.execute(sql)
Sql = "drop table [#aaa]"
conn.execute(sql)
%>
8. 這兩個SQL語句的區別
沒什麼區別 後面是給t_dim_excel 取個別名t而已
一張表查詢取不取別名都沒關系的。
多表查詢，特別是多表中都有某欄位比如 id之類的時候 就要取別名了。
例如：
select t1.*,t2.* from t_dim_excel t1,t_dim_excel2 t2
where t1.id = t2.id ;