实验五-视图及查询.doc

实验五 查询和视图
一、 实验目的

1. 掌握SQL语句的定义功能和数据操作功能。
2. 掌握SQL语句的查询功能，包括连接查询，内外嵌套查询，带计算的查询等。
3. 掌握利用设计器创建和使用查询。
4. 掌握利用设计器创建和使用本地视图。

二、实验准备和要求

1. 将老师发给你们的“学生管理(自由表)”文件夹复制到在D盘并改名为自己的学号后两位和姓名，并选择“工具/选项/文件位置”,把该文件夹设为VFP的默认工作目录。
2. 作业提交要求：SQL命令直接在实验指导文档中写出，用查询和视图设计器做的查询和视图和实验指导文档一同上传。

三、实验内容与步骤

1.SQL的定义功能
（1） 用SQL的定义命令创建一个职工表（文件名为：zg.dbf）,表结构如下所示。
字段名 类型 宽度 小数点
职工号 C 4
姓名 C 8
性别 C 2
出生日期 d
工资 N 7 2
命令：CREAT TABLE zg("职工号" C(4),"姓名" C(8),"性别" C(2),"出生日期" D,"工资" N(7,2))

（2） 修改上题创建的zg表。
① 增加“职称”字段，类型为字符型，宽度为6。
命令：
ALTER TABLE zg ADD COLUMN "职称" C(6)
② 删除“出生日期”字段。
命令：
alter table zg drop 出生日期
③ 把“性别”字段的类型改为逻辑型。
命令：
alter table zg alter 性别 l
④ 把“工资”字段改名为“实发工资”。
命令：
alter table zg rename "工资" to "实发工资"
2.SQL的操作功能
① 给课程表（kc）插入一条记录（"2001","英语精读",2," "）。
命令：
insert into kc (课程号,课程名,学分) values (“2001”,”英语精读”,2)
② 给女同学的入学分数增加10分。（提示：用Update 命令）。
命令：
update xsqk set 入学分数=入学分数+10 where 性别=”女”
③ 把课程号为“1003”的学分改为4分。（提示：用Update 命令）。
命令：
update kc set 学分=4 where 课程号="1003"
④ 给成绩表（cj）中不及格成绩的数据加上删除标记。查看是否执行，再去掉删除标记。
命令：
delete from cj where "成绩"<"60"
Recall all
3. SQL语言的查询功能
（1）简单查询
① 查询所有男同学的个人记录。
命令：
select * from xsqk where 性别="男"
② 查询所有同学的学号、姓名、性别、专业、年龄和入学分数。
命令：
select 学号,姓名,性别,专业,2012-year(出生日期) as 年龄,入学分数 from xsqk
③ 查询成绩表中不重复同学的学号（即去掉重复的学号）。（提示：用Distinct短语）
命令：
select distinct 学号 from cj
（2）条件查询
① 查询“计算机科学”专业男同学的信息。
命令：
select * from xsqk where 专业="计算机科学" and 性别="男"
② 查询1988年出生的“工业工程”专业同学的学号、姓名、性别、入学成绩和出生日期信息。
命令：
select 学号,姓名,性别,入学分数,出生日期 from xsqk where year(出生日期)=1988 and 专业="工业工程"
（3）对查询结果进行排序。
① 查询“市场营销”专业入学分数前3名同学的情况。
命令：
select * from xsqk where 专业="市场营销" top 3 order by 入学分数
② 查询所有男同学，并且是1988年出生的学生情况，要求查询结果按专业升序、入学分数降序排列。
命令：
select * from xsqk where 性别="男" and year(出生日期)=1988 order by 专业 asc,入学分数 desc
（4）使用分组统计函数
① 统计输出每个同学各门课程的成绩最高分、最低分和平均分，并按平均分降序排序。 （提示：按“学号”分组）
命令：
select 学号,max(成绩) as 最高分,min(成绩) as 最低分, avg(成绩) as 平均分 from cj order by 平均分 desc group by 学号
② 统计输出各门课程的人数和期末考试各门课程成绩的最高分、最低分和平均分并按最高分升序排序。 （提示：按“课程号”分组排序）
命令：
select 课程号,count (*) as 人数,max (成绩) as 最高分, min(成绩) as 最低分,avg(成绩) as 平均分 from cj order by 最高分 asc group by 课程号
（5）联结查询
① 查询“市场营销”专业的同学的所有课程成绩。包括同学的学号、姓名、专业、课程号、学期和成绩。（提示：对xsqk、cj两表连接）
命令：
select xsqk.学号,xsqk.姓名,xsqk.专业,cj.学号,cj.课程号,cj.学期,cj.成绩 from xsqk full join cj on xsqk.学号=cj.学号 where 专业="市场营销"
② 列出所有不及格同学的学号、姓名、专业、课程号、课程名和成绩。（提示：对xsqk、cj、kc三表连接）
命令：
select xsqk.学号,xsqk.姓名,xsqk.专业,kc.课程号,kc.课程名,cj.成绩 from xsqk join cj join kc on cj.课程号=kc.课程号 on xsqk.学号=cj.学号 where 成绩<60
③ 为“市场营销”专业建立一个包括每个同学学号、姓名、专业和各课程成绩平均分的查询，按平均分降序排列。（提示：对xsqk、cj两表连接，并按cj表中的学号分组）
命令：
select xsqk.学号,姓名,专业,avg(cj.成绩) as 平均分 from xsqk join cj on xsqk.学号=cj.学号 where 专业="市场营销" order by 平均分 desc group by cj.学号

（6）嵌套查询
① 列出“市场营销”专业同学的所有成绩表中的记录，包括学号、课程号、学期、成绩。（提示：学号作为内外查询连接的条件）
命令：
select *from cj where 学号 in (select 学号 from xsqk where 专业=”市场营销”)
② 查询成绩表（cj）中至今没有一门课程成绩的同学的信息。包括学号、姓名、性别、专业、出生日期和入学分数等。（提示：学号作为内外查询连接的条件）
命令：
select * from xsqk where 学号 not in (select 学号 from cj)
③ 查询学分最高的课程信息。（ 提示：先用Max函数在kc表中求出最高学分是多少）
命令：
select *from kc where 学分=(select max(学分) from kc)
（7）查询结果的不同去向
① 查询“1001”号课程考试分数前2名的信息。（用Top 2短语在浏览窗口内显示）
命令：

② 查询“计算机科学”专业的学生信息并将结果存人临时表ls1中。 (使用Into Cursor ls1)
命令：

③ 查询学号为“20070101”的学生的考试成绩信息并将结果存人永久表xhcj中。（使用Into Table xhcj短语输出）
命令：

4. 用查询设计器创建查询
（1）简单查询： 用查询设计器创建对1989年出生且入学分数大于570分同学的查询，按入学分数排序。(保存为“简单查询”)。

（2）计算查询（字段为表达式）： 建立统计各专业同学的人数的查询，字段包括专业、人数(保存为“计算查询”)。
提示：
① 需要用xsqk一个表。
② 人数用表达式 Count(*) as 人数
③ 分组依据按专业分组。

（3）多表联结查询：建立一个包括每个同学学号、姓名、专业和各课程平均分、总分以及最高分数、最低分数的查询。按学号排序。(保存为“多表查询”)
提示：
① 需要用xsqk和cj二个表！
② 平均分、总分以及最高分数、最低分数选用AVG()、Sum()、Max()函数。
③ 要按学号分组才能得到各个同学的平均分、最高分等。

5. 用视图设计器创建本地视图
（做视图设计器的准备工作：先建立数据库xsgl.dbc，将老师发给你们的表添加到数据库中；然后将每个表做索引，做完索引后在进行各表连接。）
（1）单表视图：用视图设计器创建包括所有人学分数在550分至570分的同学的学号、姓名、性别、专业和入学分数字段的视图并按入学分数从高到低排列。将入学分数设置为可更新字段。存盘，保存为“单表视图”后打开视图，显示其中女同学的记录并把某个同学的成绩更改分数。
关闭视图，选择学生表，看是否能够把修改结果传递回表中？
提示：入学分数600至700的表示方法可以有两种：
① 入学分数>=600 .and. 入学分数<=700
② 入学分数 between 600,700

（2）多表联结视图：用视图设计器创建包括同学学号、姓名、性别、专业、课程号、课程名、学分、学期和成绩字段的视图，将成绩设置为课更新字段，且按照课程号及学号排序。保存为“多表视图”。
提示：
① 需要用xsqk、kc和cj三个表！
② 关键字段必须从主关键字表中选取。
存盘后打开视图，用命令进行下列操作（视图的操作和数据表操作命令一样）：
① 列出“市场营销”专业同学的所有成绩表
② 列出“张晓伟”同学的成绩单，并在视图中修改张晓伟同学的成绩。
③ 打开成绩表，查看张晓伟同学的成绩修改情况。