浙江省计算机三级网络asp复习要点

网络编程

1. 网络应用模式

C/S结构：(Client/Server) 通过将任务合理分配到客户端和服务器端，降低系统的通讯开销，可以充分利用两端硬件环境的优势。

B/S结构（Browser/Server），对C.S结构的一种变化或者改进的结构。在这种结构下，用户界面完全通过WWW浏览器实现，一部分事务逻辑在前端实现，但是主要事务逻辑在服务器端实现，形成所谓3-tier结构。

2.ASP编程基础或JSP编程基础

1. ASP与Access数据库连接：

<%@ language=VBscript%>

<%

dim conn,mdbfile

mdbfile=server.mappath("数据库名称.mdb")

set conn=server.createobject("adodb.connection")

conn.open "driver={microsoft access driver (*.mdb)};uid=admin;pwd=数据库密码;dbq="&mdbfile %>

2. ASP与SQL数据库连接：

<%@ language=VBscript%>

<%

dim conn

set conn=server.createobject("ADODB.connection")

con.open "PROVIDER=SQLOLEDB;DATA SOURCE=SQL服务器名称或IP地址;UID=sa;PWD=数据库密码;DA TABASE=数据库名称

%>

建立记录集对象：

set rs=server.createobject("adodb.recordset")

rs.open SQL语句,conn,3,2

3. SQL常用命令使用方法：

(1) 数据记录筛选：

sql="select * from 数据表where 字段名=字段值order by 字段名"

sql="select * from 数据表where 字段名like ′%字段值%′ order by 字段名"

sql="select top 10 * from 数据表where 字段名order by 字段名"

sql="select * from 数据表where 字段名in (′值1′,′值2′,′值3′)"

sql="select * from 数据表where 字段名between 值1 and 值2"

(2) 更新数据记录：

sql="update 数据表set 字段名=字段值where 条件表达式"

sql="update 数据表set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式" (3) 删除数据记录：

sql="delete from 数据表where 条件表达式"

sql="delete from 数据表" (将数据表所有记录删除)

(4) 添加数据记录：

sql="insert into 数据表(字段1,字段2,字段3 …) values (值1,值2,值3 …)"

sql="insert into 目标数据表select * from 源数据表" (把源数据表的记录添加到目标数据表)

实验三实验四SQL语句

实验三：

1.查询“实验2”中创建的所有记录

select * from student

select * from SC

select * from course

2.查询学生总人数

select count(*) from student

3.检索LIU老师所授课程的课程号和课程

select cno,cname from course where cteacher='liu'

4.检索年龄大于23岁的男生的学号和姓名

select Sno,Sname,Ssex,Sage from Student where (Sage>20) AND (Ssex='男')

5.检索“信息管理”专业的所有女学生的学号和姓名

select sno,sname from student where Sdept='信息管理' and ssex='女'

6.查询年龄最小的学生学号、姓名和年龄

/* 解法一

select sno,sname,sage from student where sage = (select min(sage) from student)

/* 解法二

select top 1 Sno,Sname,Sage from Student order by Sage

7.检索所有学生中年龄最大的前4人，输出其学号、姓名、年龄

/*select top 4 Sno,Sname,Sage from Student order by Sage desc */

8.检索至少选修了2门课程的学生学号

/*解法一

select sno from sc group by sno having count(*)>=2

/*另扩展：

select * from Student where Sno IN (select Sno from SC group by Sno having count(*)>=2 ) 9.在COURSE表中统计开设课程的教师人数、

/* 一种理解：course表中登记的开课教师人数

select count( distinct cteacher)代课老师人数from course

/*另一种理解：根据sc表来查询当前学期开课的老师人数；

select distinct cteacher from course where cno IN (select distinct cno from sc)

10.查询选修了课程的学生人数

select count(distinct sno) from sc

11.查询各个课程号相应的选课人数、

select cno 课程号,count(*) 选课人数from sc group by cno

12.计算C001号课程的学生平均成绩/查看选修了课程3的平均成绩

select '3' 课程号,A VG(grade)平均成绩from SC where Cno=3

13.球每个学生选修课程（已有成绩）的门数和平均成绩

select sno,count(*) 选修课程门数,avg(grade) 平均成绩from sc group by sno

14.统计每个学生选修课程的门数（超过2门课就统计）。要求输出学生学号和选修门数，

查询结果按门数降序排列，若门数相同，按学号升序排列

/* 方法一

select * from (select sno 学号,count(*) 选修门数from sc group by sno having count(*)>2 )as temp_14 order by temp_14.选修门数desc,temp_14.学号asc

/*方法二

select sno 学号,count(*) 选修门数from sc group by sno having count(*)>2 order by 选修门数desc,学号asc

15.检索学号比wang同学大，而年龄比他小的学生姓名/与张飞同学相比

select Sname from Student sx where Sno>(select Sno from Student where Sname = '张飞') and Sage<(select Sage from Student where Sname = '张飞')

16.在表SC中检索成绩为空值的学生学号和课程号

select sno,cno from sc where grade is null

17.检索姓名以“刘”开头的所有学生的姓名和年龄

select sname,sage from student where sname like '刘%'

18.求年龄大于女同学平均年龄的男生姓名和年龄

select sname,sage from student where sage>(select avg(sage) from student where ssex='女') and ssex='男'

19.求年龄大于所有女同学年龄的男生的姓名和年龄

/* 解法一

select sage from student where ssex='女'

select sname,sage from student where sage> all (select sage from student where ssex='女') and ssex='男'

/* 解法二

select sage from student where ssex='女'

select sname,sage from student where sage> (select max(sage) from student where ssex='女') and ssex='男'

20.查询所有学生中平均成绩最差的学生的学号。（按已有成绩，没有成绩的暂不统计）select Sno,Avg(grade) 平均成绩from SC group by Sno order by 平均成绩

/* 方法一

select top 1 Sno from SC group by Sno order by Avg(grade)

/*方法二

select temp_20.sno from (select top 1 sno,avg(grade) avg_grade from sc

where grade is not null group by sno order by avg_grade ) as temp_20

21.查询学生平均成绩最好的课程的课程号和平均成绩；（按已有成绩，没有成绩的暂不统

计）

select top 1 cno,avg(grade) avg_grade from sc where grade is not null group by cno order by avg_grade desc

22.查询与“刘晨”在同一个系学习的学生

select * from Student where Sdept = (select Sdept from Student where Sname = '刘晨')

实验四SQL语言答案

一、复杂查询

1.求选修C004课程的女学生的平均成绩

select avg(grade) 平均成绩from SC,Student where sc.sno=student.sno and student.Ssex='女'and https://www.wendangku.net/doc/7317296101.html,o=4

select avg(grade) 平均成绩from sc where cno=4 and sno in (select sno from student where ssex='女')

2.求选修C004课程的女学生的平均年龄

select avg(Sage) as 平均年龄from SC,Student where sc.sno=student.sno and student.Ssex='女'and https://www.wendangku.net/doc/7317296101.html,o<>4

select avg(sage) 平均年龄from student where Ssex='女' and sno in (select sno from sc where cno=4)

3.检索学号为S0003学生所学课程的课程名与任课教师名

select https://www.wendangku.net/doc/7317296101.html,ame,course.Cteacher from course,SC where https://www.wendangku.net/doc/7317296101.html,o=https://www.wendangku.net/doc/7317296101.html,o and sc.sno='200215121'

select cname,cteacher from course where cno in (select cno from sc where sno='200215121')

4.检索至少选修LIU老师所授课程中一门课程的女学生姓名

select * from student,sc where (student.sno=sc.sno) and student.ssex='女' and (https://www.wendangku.net/doc/7317296101.html,o in (select distinct cno from course where Cteacher='zhang' ))

5.检索刘晨同学不学的课程的课程号

select * from course where cno not in (select distinct https://www.wendangku.net/doc/7317296101.html,o from student,sc where student.sname='刘晨'and student.sno=sc.sno)

6.检索全部学生都选修的课程的课程号和课程名、

select * from course where cno=(select cno from sc group by cno having count(sno)=(select count(sno) from student))

7.检索选修课程包含LIU老师所授课程的学生学号

第7题的一般查询方法，交集的概念,sql2000不能实现intersect、minus、except等方法，2005以后的版本可以*/

select distinct x.sno from sc x where (select count(https://www.wendangku.net/doc/7317296101.html,o) from sc y where y.sno=x.sno and https://www.wendangku.net/doc/7317296101.html,o in (select cno from course where Cteacher='zhang'))= (select count(cno) from course where Cteacher='zhang')

8.查询属于“信息管理”系学生且性别为女性的学生号和姓名

select sno,sname from student where sdept='信息管理' and ssex='女'

9.查询属于“信息管理”系学生或者是女学生的学生号和姓名sql2000只支持union操作select sno,sname,ssex,sdept from student where sdept='信息管理' union select sno,sname,ssex,sdept from student where ssex='女'

10.查询“电子商务：系学生平均成绩大于75分的学生学号

select * from student where sno in (

select student.sno from student,sc where student.sno=sc.sno and student.sdept='信息管理' group by student.sno having avg(grade)<88)

11.查询选修了课程名为“管理信息系统”的学生学号和姓名

select sno from sc where cno in (select cno from course where cname='信息系统')

12.找出选修课程中成绩最高的同学姓名及成绩

select zjltemp.zjl_cno 课程号,student.sno 学生姓名,zjltemp.zjl_grade 成绩from student, (select x.sno zjl_sno,https://www.wendangku.net/doc/7317296101.html,o zjl_cno,x.grade zjl_grade from sc x where x.grade=(select max(grade) from sc y where https://www.wendangku.net/doc/7317296101.html,o=https://www.wendangku.net/doc/7317296101.html,o) )

as zjltemp

where student.sno=zjltemp.zjl_sno

13.查询“电子商务”系选修了两门以上课程的学生学号

*方法一：*/

select sno1 from

(select student.sno sno1,student.sname sname1 from student where student.sdept='信息管理') as zjl_sno

where (select count(https://www.wendangku.net/doc/7317296101.html,o) from sc where sc.sno=zjl_sno.sno1 )>=2

/*方法二：*/

select zjl_one.sno from

(select sno from sc group by sno having count(cno)>=2) as zjl_one

where zjl_one.sno in (select sno from student where sdept='信息管理')

14.查询其他系中比“市场营销”系某一学生年龄小的学生名单

select * from student x where x.sage < any (select sage from student y where y.sdept='信息管理') and x.sdept<>'信息管理'

select * from student x where x.sage < (select max(sage) from student y where y.sdept='信息管理') and x.sdept<>'信息管理'

15.查询其他系中比“市场营销”系所有学生年龄校的学生名单

select * from student x where x.sage < all (select sage from student y where y.sdept='信息管理') and x.sdept<>'信息管理'

16.查询所有选修了C003号课程的学生姓名

select * from student,sc where student.sno=sc.sno and https://www.wendangku.net/doc/7317296101.html,o=3

二、更新操作

1.向关系COURSE中插入一个课程元组（’C010’,’JA V A程序设计’,

2.’BAO’）

select * into course_17 from course

insert into course_17(cno,cname,ccredit,cteacher) values ('8','java程序设计',2,'Zhang')

select * from course_17

drop table course_17

2.将SC表中删除尚无成绩的选课元组

select * into SC_18 from sc

insert into SC_18 (sno,cno) values ('200215124',3)

select * from SC_18

delete from sc_18 where grade is null

select * from SC_18

drop table sc_18

3.把选修LIU老师课程的女学生选课元组全部删除

select * into SC_19 from sc

delete

from sc_19

where cno in (select cno from course where cteacher='Liu') and sno in (select sno from student where ssex='女')

select * from sc where sno in (select sno from student where ssex='女') and cno in (select cno from course where cteacher='Liu')

select * from sc_19

drop table sc_19

4.把“信息系统”课程不及格的成绩全改为60分

select * into SC_20 from sc

update sc_20

set grade=60

where grade<60 and cno=(select cno from course where cname='信息系统')

select * from sc where grade<60 and cno=(select cno from course where cname='信息系统') select * from sc_20 where grade=60 and cno=(select cno from course where cname='信息系统') drop table sc_20

5.把低于总平均成绩的女同学成绩提高5%

select * into SC_21 from sc

select avg(grade) 总平均成绩from sc

update sc_21

set grade = grade*(1.05)

where (grade<(select avg(grade) from sc)) and (sno in (select sno from student where ssex='女')) select * from sc where sc.sno in (select sno from student where ssex='女')

select * from sc_21 where sc_21.sno in (select sno from student where ssex='女')

drop table sc_21

6.在表SC中，当某个选修成绩低于课程的平均成绩时（注意：要针对课程进行分组，与

上题不一样），提高5%

create view cnoavggrade(cno,Gavg) as select cno,avg(grade) from sc group by cno */

/*go*/

/*select * from cnoavggrade

drop view cnoavggrade

select * into SC_22 from sc

update sc_22

set grade = grade*(1+0.05)

where grade<= all (select avg(grade) from sc group by cno)

select * from sc order by grade

select * from sc_22 order by grade

drop table sc_22

7.在表SC中修改C004课程的成绩，当成绩小于等于70分时，提高5%，当成绩大于70

分时提高4%（提示：该题可以参考SQLSERBER2000联机丛书中TRANSACT-SQL一节关于CASE的介绍）

select * into SC_23 from sc

update sc_23

set grade =

case

when (grade<=70) then grade*(1+0.05)

else grade*(1+0.04)

end

where cno=4

select * from sc where cno=4

select * from sc_23 where cno=4

drop table sc_23

8.检索存在所授课程平均成绩大于80分的教师姓名，并把检索到的值送往另一个已存在

的表FACULTY（TNAME）。思考：假设这个表不事先存在，怎么办？

create table faculty (tname char(40),cno int,grade int )

insert into faculty

select course.cteacher,https://www.wendangku.net/doc/7317296101.html,o,temp1.平均成绩from course,

(select cno,avg(grade)平均成绩from sc group by cno having avg(grade)>80) as temp1

where https://www.wendangku.net/doc/7317296101.html,o=https://www.wendangku.net/doc/7317296101.html,o

select * from faculty group by cno,tname,grade