9
This presentation is the property of its rightful owner.
Sponsored Links
1 / 122

第9章 数据库的使用 PowerPoint PPT Presentation


  • 122 Views
  • Uploaded on
  • Presentation posted in: General

第9章 数据库的使用. 9.1 结构化查询语言 SQL 9.2 用 Access 建立一个数据库文件 9.3 搭建 DSN 桥梁 9.4 访问数据库中的数据 9.5 在客户端访问数据库 9.6 用 Command 对象改善查询 习题. 9.1 结构化查询语言 SQL.

Download Presentation

第9章 数据库的使用

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


9

9

9.1 SQL

9.2 Access

9.3 DSN

9.4

9.5

9.6 Command


9 1 sql

9.1 SQL

  • SQL Structured Query Language SQLSQLDBMSSQL ServerOracleSybaseDB2 SQLDBMSSQL


9

  • SQL CCOBOL FORTRAN SQL IfGotoForDo

    9.1.1 Select

  • SQLSQLSelectSQL

  • [9-3]S24Select


9

  • Select 12... From [[Where ][Order By][Group By|[Having]]]

  • 1 SelectSelectSelect

    (1) FromSQL


9

(2)

(3) SQL

2 From FromSQL

3 WhereSQL

4 Order ByASCDESC()


9

5 Group By

6 HavingSQLGroup ByWhere

  • VBScript


9

  • SQLSQL DBMS DBMS


9

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

[9-5]20001230(Num)(Date)(Amount)AB

Insert into B (Num,Date,Amount) Select Num,Date,Amount From A Where Date<30122000

9.1.3 Update

  • Update Update Set Update WhereUpdate Where Where


9

  • Update Set 1=value1 \[,2 = value2\]... Where

  • value

    [9-6]Update Cslp10ID99

    Update C Set credit=100000.00,id=99 Where name=slp


9

[9-7]CID809010012020021

Update C Set credit=200000.00,state=021 Where ID in (80,90,100,120)

  • Where in ()IDID8090100120

  • SQL Update


9

9.1.4 Delete

  • Delete from Where

  • [9-8]DID99

    Delete from D Where ID=99

    9.1.5 Create table

  • SQL Select Create Drop


9

  • Create table ( 1 \[NOT NULL\],2 \[NOT NULL\], 3 \[ NOT NULL\]...)

  • [9-9]name30, amout8id4B

    Create Table B (name CHAR(30), amout NUMBER(8), id NUMBER(4))

  • Create table B


9

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 access

9.2 Access

  • AccessWindows 2000 Microsoft AccessAccess9.1

  • Microsoft AccessAccess9.29.3


9

9.1


9

9.2


9

9.3


9

  • 9.3mydb9.4

9.4


9

  • mydb19.5

9.5


9

  • 9.61234569.7


9

9.6


9

9.7


9

  • Microsoft Access9.8S1icrosoft Access9.9

  • 9.9ID9.10

  • 9.10S1ID12AccessS1Microsoft AccessMicrosoft AccessS1S1


9

9.8


9

9.9


9

9.10


9 3 dsn

9.3 DSN

  • ODBCODBCODBCODBCAccessODBCAccessASPAccessSQLODBC


9

  • ODBCDSNDSN(Date Source Name)ODBCSQLDSNSQL ServerDSNDSNODBCDSNDSN

  • DSN

  • ODBCODBC


9

  • Windows 95/9832ODBCWindows 2000/NT/DSNDSNDSNDSNDSN

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


9

9.11(a)


9

9.11(b)


9

(2) ODBC32ODBCODBC9.12DSN

(3) 9.129.13Microsoft Access DriveODBC Microsoft Access9.14


9

9.12


9

9.13


9

9.14


9

(4) 9.14DSNQL9.15

  • 9.15C:..My Documentsmydbmydb1.mdb

  • qlC:..my Documentsmydbmydb1.mdbC:..My Documentsmydbmydb1.mdbDSN


9

  • ASPDSNmydb1.mdbS1

9.15


9

9.4

9.4.1 Recordset

  • ADORecordsetRecordset Recordset


9

  • Recordset Recordset Recordset

    [9-11]

    <%

    Set rs=Server.CreateObject(ADODB.Recordset)

    rs.Open Select * from S1,DSN=ql;%>


9

<%=rs()%><p>

<%=rs()%><p>

<%=rs()%>

<%rs.Close

set rs=nothing

%>

  • 9.11.asp9.16


9

9.16


9

  • <%

    Set rs= Server.CreateObject(ADODB.Recordset)

    rs.Open

    =rs()

    ...

    rs.close

    set rs=nothing

    %>

  • Recordset


9

  • rsRecordset

  • RecordsetOpenSQLDNS=;UID=;PWD=

  • =rs()


9

  • Recordsetrs.closeRecordsetcloseRecordsetset rs=nothing

  • S1


9

[9-12]

<%Set rs=Server.CreateObject(ADODB.Recordset)

rs.Open Select * from S1,DSN=ql;%>

<%while not rs.EOF%>

<%=rs()%>

<%=rs()%>

<%=rs()%><p>

<%rs.MoveNext

wend%>

<%rs.Close

set rs=nothing

%>


9

  • 9.12.asp9.17S1

9.17


9

  • while...wendRecordset MoveNext

  • RecordSet

  • rs.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:


9

  • rs.MoveNext

  • rs.MovePrev

  • rs.MoveFirst

  • rs.MoveLast

  • rs.Move [n] n n0

  • rs.Move NumRecords,Start NumRecordsstart

  • rs.Close RecordSet


9

9.4.2 Connection

1.

  • ADOConnectionODBCConnection

  • ConnectionExecuteSQL Insert

    [9-13]

    <%

    Set cn =Server.CreateObject(ADODB.Connection)


9

cn.Open DSN=ql;

strSQL=Insert Into S1 (,,,,,) Values (Jose,34,8000,64455,,1960/03/23)

cn.Execute(strSQL)

%>

<%cn.Close

set cn=nothing%>

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


9

9.18


9

  • mydb1S1

  • <%

    Set cn = Server.CreateObject(ADODB.Connection)

    cn.Open

    strSQL=Insert Into S1 () Values ()

    cn.Execute(strSQL)

    cn.Close

    set cn=nothing

    %>


9

  • Connection

  • RecordsetOpen

  • SOLSQL

  • ConnectionExecute(SQL)Recordset


9

2.

  • SQL Insert SQL Update Delete SQL Update

    [9-14] Update S1Jose

    <%

    Set cn =Server.CreateObject(ADODB.Connection)

    cn.Open DSN=QL;

    cn.Execute(Update S1 Set = Where =Jose )

    %>

    <%cn.Close

    set cn=nothing%>


9

  • 9.14.aspAccessmydb1S1Jose9.19

9.19


9

  • cn.ExecuteSQL

  • Update

    3.

  • SQL Delete

    [9-15]DeleteS1ID5


9

<%

Set cn =Server.CreateObject(ADODB.Connection)

cn.Open DSN=ql;

cn.Execute Delete From S1 Where ID = 5

%>

<%cn.Close

set cn=nothing%>

  • 9.15.aspAccessmydb1S1ID59.20


9

9.20


9

  • SQL Delete Where Delete SQL Where

    4.

  • ConnectionRecordsetRecordset

    [9-16]ConnectionRecordset


9

<%

Set cn =Server.CreateObject(ADODB.Connection)

cn.Open DSN=ql;

cn.Execute Update S1 set = ,=29,=2500 where ID=4

Set rs=Server.CreateObject(ADODB.Recordset)

strSQL = Select ID,,, from S1

rs.Open strSQL, cn

Set ID= rs(ID)

Set xm= rs()

Set nl= rs()

Set gz= rs()%>


9

S1<br> ID <br>

<%do until rs.EOF

Response.Write ID & & xm & & nl & &gz & <BR>

rs.MoveNext

loop

%>

<%cn.Close

set cn=nothing

set rs=nothing

%>

  • 9.16.aspS19.21


9

9.21


9

  • RecordsetcnConnection

    rs.Open strSQL, cn

    5. ConnectionTimeout

  • Connection ConnectionTimeout ConnectionTimeout 20


9

Set cn = Server.CreateObject(ADODB.Connection)

cn.ConnectionTimeout = 20

cn.Open DSN=ql;

  • ConnectionTimeout 30

  • ConnectionTimeout

    6. Connection

  • ConnectionRecordsetConnection


9

[9-17]ConnectionRecordset

<%

sub t(rs)

Response.Write (<table border=1>)

for i=0 to rs.fields.count1

Response.Write(<td> +rs(i).name+</td>)

next

while not rs.EOF

Response.Write(<tr>)


9

for i=0 to rs.fields.count1

v=rs(i)

if isnull(v) then v=

Response.Write(<td valign=top> +CStr(v)+</td>)

next

rs.MoveNext

wend

Response.Write (</table>)

end sub

%>

<%====================================%>


9

<%Set cn = Server.CreateObject(ADODB.Connection)

cn.Open DSN=ql; %>

<%====================================%>

<h3> S1 ,<br></h3>

<%

Set rs1 = Server.CreateObject(ADODB.Recordset)

strSQL=Select ,, From S1

rs1.Open strSQL,cn

t(rs1)

%>

<h3> S1 ,<br></h3>


9

<%

Set rs2=Server.CreateObject(ADODB.Recordset)

strSQL = Select ,, From S1

rs2.Open strSQL, cn

t(rs2)

%>

<%cn.Close

set cn=nothing

set rs1=nothing

set rs2=nothing

%>

  • 9.17.aspS19.22


9

9.22


9

  • t(rs)RecordsetRecordset

  • Connectionql

  • Recordset rs1rs2t(rs)

  • rs.fields.countRecordsetrs(i).nameRecordseti

  • isnullvNULL


9

9.5

9.5.1

[9-18]

<%@ Language=VBScript %>

<html>

<body>

<form Method=post action=9.19.asp>

<input type=text size=6 name=xm>

<input type=text size=2 name=nl value=20><br>


9

<input type=text size=6 name=gz value=2000><br>

<input type=text size=12 name=dh value=11111><br>

<input type=text size=22 name=dz value=><br>

<input type=text size=12 name=rq value=1960/03/23><br>

<input type=submit value=>

<input type=reset value=>

</FORM>

</body>

</html>

  • 9.18.asp9.23


9

9.23


9

[9-19]9-19.asp

<%

sz = & Request.Form(xm) & , & Request.Form(nl) &

, & Request.Form(gz) & , & Request.Form(dh) &

, & Request.Form(dz) & , & Request.Form(rq) &

Set obj = Server.CreateObject(ADODB.Connection)

obj.Open ql

intodb = Insert Into S1 (,,,,, ) Values( &sz & )


9

obj.Execute(intodb)

obj.Close

%>

  • 9.239.19.aspS19.24


9

9.24


9

9.5.2

  • SQL ASPqASP

    [9-20]qq.incasp

    <%

    sub q(inputquery)

    set cn=server.createobject(adodb.connection)


9

cn.open DSN=ql;

set rstemp=cn.execute(inputquery)

howmanyfields=rstemp.fields.count 1

%>

<table border=1><tr>

<%

for i=0 to howmanyfields

%>

<td><b>

<%=rstemp(i).name%></B></TD>

<% next %>


9

</tr>

<%

do while not rstemp.eof

%>

<tr>

<% for i = 0 to howmanyfields

thisvalue=rstemp(i)

if isnull(thisvalue) then

thisvalue=

thisvalue

end if %>

<td valign=top><%=thisvalue%></td>


9

<% next %>

</tr>

<%rstemp.movenext

loop%>

</table>

<%

rstemp.close

set rstemp=nothing

cn.close

set cn=nothing

end sub%>


9

  • qASPSQLq

  • rstemp.fields.countrstemp(i).namerstemp(i)

    [9-21]9-21.asp

    <HTML>

    <body bgColor=pink>

    <form method=post action=9.21.asp>

    <p><input name=name ></p>


9

<p><input type=submit value= name=B1>

<input type=reset value=

name=B2></p>

</form>

<font color=orangered><font size=4><strong>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </strong>

<%

sql=Select * From S1 Where = & Request(name) &

call q(sql)


9

%>

<!-- #include virtual=/asp/q.inc-- ></font></font>

</body>

</html>

  • 9.259.25

  • SQLq.incS1


9

9.25


9

  • & Request(name) & Request(name)& &

    [9-22]

    <head></head><html>

    <body bgColor=pink>

    <form method=post action=9.22.asp>

    <p><input name=name value=abc></p>

    <p><input type=submit value= name=B1>

    <input type=reset value=


9

name=B2></p>

</form>

<font color=orangered><font size=4><strong>

</strong>

<%

sql=Select ,, from S1 Where like &

Request(name) & %%

Call q(sql)

%>

<!-- #include virtual=/asp/q.inc-- ></font></font>

</body>

</html>


9

  • 9.269.26

  • SQL Wherelikelike%%


9

9.26


9

[9-23]9-23.asp

<html>

<head></head>

<body bgColor=pink>

<form method=post action=9.23.asp>

<p><!-- webbot bot=Validation S-Data-Type=Integer

S-Number-Separators=, -- >

<input name=nl value=20 size=20></p>


9

<p>

<input name=dz value=></p>

<p><input type=submit value= name=B1>

<input type=reset value=

name=B2></p>

</form>

<font color=orangered>

<font size=4><strong>


9

</strong>

<%

sql=Select * from S1 Where >= &Request(nl)& and = &Request(dz) &

call q(sql)

%>

<!-- #include virtual=/asp/q.inc-- >

</font>

</font>

</body>

</html>


9

  • 9.2720209.27

  • SQL Where

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


9

9.27


9

[9-24]9-24.asp

<%@ Language=VBScript %>

<html>

<head><title></title></head>

<body bgColor=#CCFFFF>

<p><img border=0 src=.gif>&nbsp;<font color=navy size=5>

</font></p>

<p>

<a href=9.18.asp>


9

</a><a><br>

</a><a href=9.22.asp>

</a><a><br>

</a><a href=9.21.asp>

</a><a href=9.22.asp></a>

<a href=9.21.asp></a><a><br>

</a><a href=9.23.asp>

</a><a href=9.22.asp></a>

<a href=9.23.asp><a><br></p></a>

</body>

</html>


9

9.28


9 6 command

9.6 Command

  • ADORecordsetConnectionCommandRecordset


9

  • CommandConnectionRecordsetCommandSQL

    9.6.1 Command

    [9-25]S29.18.asp(9-25.asp)


9

<%@ Language=VBScript %>

<html>

<body bgColor=darkseagreen><font color=darkred size=5

style=BackGroundColor: #8fbc8f>

</font>

<form method=post ACTION=9.26.asp>

<input size=4 name=nf value=2001 >

<select size=1 name=tx>

<option selected value=1>1.</option>


9

<option value=2>2.</option>

<option value=3>3.</option>

<option value=4>4.</option>

<option value=5>5.</option>

<option value=6>6.</option>

<option value=7>7.</option>

</select><input size=4 name=th value=1 ><br>

<textarea rows=2 name=nr cols=20></textarea>

<p>&nbsp;<textarea rows=2 name=dan cols=20></textarea>


9

</p>

<p>&nbsp;<textarea rows=1 name=jd cols=20></textarea><br>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

<input type=submit value=>&nbsp;&nbsp;&nbsp;

<input type=reset value=>

</p>

</form>

</body>

</html>


9

  • 9.25.asp9.299.299.26.aspS2


9

9.29


9

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

<%

Set cm= Server.CreateObject(ADODB.Command)

Set cn = Server.CreateObject(ADODB.Connection)

cn.Open ql;

Set cm.ActiveConnection =cn

cm.CommandText = Insert Into S2( ,,,,,) Values (?,?,?,?,?,?)


9

cm.CommandType =1

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,,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)


9

cm()=Request(dan)

cm()=Request(jd)

cm.Execute

%>

  • 9.19.aspCommandCommandCommand


9

<%

Set cm= Server.CreateObject(ADODB.Command)

Set cm.ActiveConnection =cn

cm.CommandText =

cm.CommandType =

cm.Parameters.Append cm.CreateParameter(name,type,direction,size,value)

cm(name) =

cm.Execute

%>


9

  • Command

  • 1asp

  • 2cmCommand

  • 3CommandActiveConnectionCommandConnectionConnection9-26

    cn.Open q1;ConnectionOpenql


9

<%Set cm.ActiveConnection = DNS=;UID=;PWD= %>

  • 4cm.CommandText=SQL9-26INSERT INTO S2()VALUES(?,?,?,?,?,?)SQL

  • 5cm.CommandType =41SQL248


9

  • 6cm.Parameters.Append cm.CreateParameter(name,type,direction,size,value)ParameterParameterParameterParameterParameterParameterFieldRecordset

  • name ParameterParameter

  • Type 20013220814129ASCII68;7133yyyymmdd134hhmmss135yyyymmdd hhmmss


9

  • direction Parameter12Command34

  • Size

  • value


9

  • 7cm(name) =Parameter?Param1Param2select*fromwhere=? And =?cm(Param1)=cm(Param2)=?

  • 8cm.Executecm.CommandText

  • Command SQL CommandParameter9.19.aspSQL


9

9.6.2 Command

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

<%

sub t(rs)

Response.Write (<table border=1>)

for i=0 to rs.fields.count1


9

Response.Write(<td> +rs(i).name+</td>)

next

while not rs.EOF

Response.Write(<tr>)

for i=0 to rs.fields.count1

v=rs(i)

if isnull(v) then v=

Response.Write(<td valign=top> +CStr(v)+</td>)

next


9

rs.MoveNext

wend

Response.Write (</table>)

end sub

%>

[9-28]S19.30


9

9.30


9

<html><head></head><body bgColor=pink>

<form method=post action=9.28.asp id=form1 name=form1 value= >

<p><input name=dz ></p>

<p><input type=submit value= name=B1>

<input type=reset value=

name=B2></p>

</form>

<font color=orangered><font size=4><strong>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </strong>

<%


9

if request(dz)<> then

set cn = Server.CreateObject(ADODB.Connection)

cn.Open DSN=ql;

set cm= Server.CreateObject(ADODB.Command)

set cm.ActiveConnection =cn

cm.CommandText=select * from S1 where = ?

cm.Parameters.Append cm.CreateParameter(,200,,255)

cm() =Request(dz)

set rs=cm.Execute


9

call t(rs)

end if

%>

<!-- #include file=9.27.asp-- >

</body>

</html>


9

1. DD()SQL

1

2

3 2000

2. AccesskcglDD()KC


9

3. ASPDD

4. ASP

5. ASPS1ID1000

6. ASPS1 ID1000100


  • Login