选择：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 ;