9 9.1 SQL9.2 Access9.3 DSN9.4 9.5 9.6 Command

9.1 SQLSQL Structured Query Language SQLSQLDBMSSQL ServerOracleSybaseDB2 SQLDBMSSQL

SQL CCOBOL FORTRAN SQL IfGotoForDo9.1.1 SelectSQLSQLSelectSQL[9-3]S24Select

Select 12... From [[Where ][Order By][Group By|[Having]]] 1 SelectSelectSelect(1) FromSQL

(2) (3) SQL2 From FromSQL 3 WhereSQL4 Order ByASCDESC()

5 Group By6 HavingSQLGroup ByWhere VBScript

SQLSQL DBMS DBMS

9.1.2 Insert Into Insert Into(col1, col2...) Values (value1, value2...) [9-4]S

Insert Into S( xm,xb,nl,gz,dh,dz) Values (,,28,4500,8888,)Insert Insert

[9-5]20001230(Num)(Date)(Amount)ABInsert into B (Num,Date,Amount) Select Num,Date,Amount From A Where Date

Update Set 1=value1 \[,2 = value2\]... Where value[9-6]Update Cslp10ID99Update C Set credit=100000.00,id=99 Where name=slp

[9-7]CID809010012020021Update C Set credit=200000.00,state=021 Where ID in (80,90,100,120)Where in ()IDID8090100120SQL Update

9.1.4 Delete Delete from Where [9-8]DID99Delete from D Where ID=99 9.1.5 Create tableSQL Select Create Drop

Create table ( 1 \[NOT NULL\],2 \[NOT NULL\], 3 \[ NOT NULL\]...) [9-9]name30, amout8id4BCreate Table B (name CHAR(30), amout NUMBER(8), id NUMBER(4))Create table B

Insert Into B(name,amout,id) Values(zhangchi,100,1) 9.1.6 Drop table Drop table Drop table [9-10]B

Drop table B

9.2 AccessAccessWindows 2000 Microsoft AccessAccess9.1Microsoft AccessAccess9.29.3

9.1

9.2

9.3

9.3mydb9.49.4

mydb19.59.5

9.61234569.7

9.6

9.7

Microsoft Access9.8S1icrosoft Access9.99.9ID9.109.10S1ID12AccessS1Microsoft AccessMicrosoft AccessS1S1

9.8

9.9

9.10

9.3 DSNODBCODBCODBCODBCAccessODBCAccessASPAccessSQLODBC

ODBCDSNDSN(Date Source Name)ODBCSQLDSNSQL ServerDSNDSNODBCDSNDSNDSN ODBCODBC

Windows 95/9832ODBCWindows 2000/NT/DSNDSNDSNDSNDSN(1) Windows 95/98 9.11(a)Windows 2000/NT 9.11(b)

9.11(a)

9.11(b)

(2) ODBC32ODBCODBC9.12DSN(3) 9.129.13Microsoft Access DriveODBC Microsoft Access9.14

9.12

9.13

9.14

(4) 9.14DSNQL9.159.15C:..My Documentsmydbmydb1.mdbqlC:..my Documentsmydbmydb1.mdbC:..My Documentsmydbmydb1.mdbDSN

9.15ASPDSNmydb1.mdbS1

9.4 9.4.1 RecordsetADORecordsetRecordset Recordset

Recordset Recordset Recordset [9-11]

9.11.asp9.16

9.16

Recordset

rsRecordsetRecordsetOpenSQLDNS=;UID=;PWD==rs()

Recordsetrs.closeRecordsetcloseRecordsetset rs=nothing S1

[9-12]

9.179.12.asp9.17S1

while...wendRecordset MoveNext RecordSetrs.EOF rs.BOF rs.Fields.Count: RecordSet rs(i).Name: i i 0 rs.Fields.Count1 rs(i): i i 0 rs.Fields.Count1 rs( ): rs.RecordCount:

rs.MoveNext rs.MovePrev rs.MoveFirst rs.MoveLast rs.Move [n] n n0rs.Move NumRecords,Start NumRecordsstartrs.Close RecordSet

9.4.2 Connection1. ADOConnectionODBCConnectionConnectionExecuteSQL Insert[9-13]

cn.Open DSN=ql;strSQL=Insert Into S1 (,,,,,) Values (Jose,34,8000,64455,,1960/03/23)cn.Execute(strSQL)%>

9.13.aspAccessmydb1S19.13.asp9.13.aspS1S19.18

9.18

mydb1S1

Connection RecordsetOpenSOLSQLConnectionExecute(SQL)Recordset

2. SQL Insert SQL Update Delete SQL Update [9-14] Update S1Jose

9.199.14.aspAccessmydb1S1Jose9.19

cn.ExecuteSQLUpdate3. SQL Delete [9-15]DeleteS1ID5

9.15.aspAccessmydb1S1ID59.20

9.20

SQL Delete Where Delete SQL Where 4. ConnectionRecordsetRecordset[9-16]ConnectionRecordset

S1 ID

9.16.aspS19.21

9.21

RecordsetcnConnection rs.Open strSQL, cn5. ConnectionTimeout Connection ConnectionTimeout ConnectionTimeout 20

Set cn = Server.CreateObject(ADODB.Connection)cn.ConnectionTimeout = 20cn.Open DSN=ql; ConnectionTimeout 30 ConnectionTimeout 6. ConnectionConnectionRecordsetConnection

[9-17]ConnectionRecordset

for i=0 to rs.fields.count1v=rs(i)if isnull(v) then v=Response.Write( +CStr(v)+)nextrs.MoveNextwendResponse.Write ()end sub%>

S1 ,

S1 ,

9.17.aspS19.22

9.22

t(rs)RecordsetRecordsetConnectionqlRecordset rs1rs2t(rs) rs.fields.countRecordsetrs(i).nameRecordsetiisnullvNULL

9.59.5.1 [9-18]

9.18.asp9.23

9.23

[9-19]9-19.asp

obj.Execute(intodb)obj.Close%>9.239.19.aspS19.24

9.24

9.5.2 SQL ASPqASP[9-20]qq.incasp

cn.open DSN=ql;set rstemp=cn.execute(inputquery)howmanyfields=rstemp.fields.count 1%>

qASPSQLqrstemp.fields.countrstemp(i).namerstemp(i)[9-21]9-21.asp

%>

9.25

& Request(name) & Request(name)& & [9-22]

name=B2>

9.269.26SQL Wherelikelike%%

9.26

[9-23]9-23.asp

= &Request(nl)& and = &Request(dz) & call q(sql)%>

9.2720209.27SQL Where

>= & Request(nl) & and = & Request(dz) &

9.27

[9-24]9-24.asp

9.28

9.6 CommandADORecordsetConnectionCommandRecordset

CommandConnectionRecordsetCommandSQL9.6.1 Command[9-25]S29.18.asp(9-25.asp)

1.

2.3.4.5.6.7.

9.25.asp9.299.299.26.aspS2

9.29

[9-26]Command9.299-26.asp

cm.CommandType =1cm.Parameters.Append cm.CreateParameter(,200,,25)cm.Parameters.Append cm.CreateParameter(,200,,25)cm.Parameters.Append cm.CreateParameter(,200,,25)cm.Parameters.Append cm.CreateParameter(,200,,25)cm.Parameters.Append cm.CreateParameter(,200,,255)cm.Parameters.Append cm.CreateParameter(,200,,255)cm()=Request(nf)cm()=Request(tx)cm()=Request(th)cm()=Request(nr)

cm()=Request(dan)cm()=Request(jd)cm.Execute%>

9.19.aspCommandCommandCommand

Command1asp2cmCommand3CommandActiveConnectionCommandConnectionConnection9-26cn.Open q1;ConnectionOpenql

4cm.CommandText=SQL9-26INSERT INTO S2()VALUES(?,?,?,?,?,?)SQL5cm.CommandType =41SQL248

6cm.Parameters.Append cm.CreateParameter(name,type,direction,size,value)ParameterParameterParameterParameterParameterParameterFieldRecordsetname ParameterParameterType 20013220814129ASCII68;7133yyyymmdd134hhmmss135yyyymmdd hhmmss

direction Parameter12Command34Size value

7cm(name) =Parameter?Param1Param2select*fromwhere=? And =?cm(Param1)=cm(Param2)=?8cm.Executecm.CommandText Command SQL CommandParameter9.19.aspSQL

9.6.2 Command[9-27]tASPcall t(rs)tRecordSet9-28.asp

Response.Write( +rs(i).name+)nextwhile not rs.EOFResponse.Write()for i=0 to rs.fields.count1v=rs(i)if isnull(v) then v=Response.Write( +CStr(v)+)next

rs.MoveNextwendResponse.Write ()end sub%>[9-28]S19.30

9.30

if request(dz) thenset cn = Server.CreateObject(ADODB.Connection)cn.Open DSN=ql;set cm= Server.CreateObject(ADODB.Command)set cm.ActiveConnection =cncm.CommandText=select * from S1 where = ? cm.Parameters.Append cm.CreateParameter(,200,,255)cm() =Request(dz)set rs=cm.Execute

call t(rs)end if%>

1. DD()SQL1 2 3 20002. AccesskcglDD()KC

3. ASPDD4. ASP5. ASPS1ID10006. ASPS1 ID1000100