实验四 数据库的操作.doc

实验四 数据库的操作二、实验内容1.创建学生管理数据库（xsgl.dbc）。 在D盘创建“学生管理”文件夹，选择“工具/选项/文件位置”,把该文件夹设为VFP的默认工作目录。 选择“文件/新建/数据库/新建文件”，在“创建”对话框中的数据库名文本框输入“xsgl”,单击“保存”按钮。进入图1所示的数据库设计器。图1 数据库设计器2.把做好的自由表（xsqk.dbf,kc.dbf,cj.dbf）添加到刚建立好的数据库中。【注意：可以在网络教室“教学案例”节点下载“学生管理自由表”，里面有已做好的三个自由表。】然后在该数据库中建立一新表（ls.dbf）,有两个字段：编号（C,8），平均成绩（N,5,1）。 选择“数据库/添加表”，在“打开”对话框中查找并选择“xsqk.dbf”，单击“确定”按钮，将其添加到数据库中； 选择“数据库/添加表”，在“打开”对话框中查找并选择“kc.dbf”，单击“确定”按钮，将其添加到数据库中； 选择“数据库/添加表”，在“打开”对话框中查找并选择“cj.dbf”，单击“确定”按钮，将其添加到数据库中； 选择“数据库/新建表”，在“新建表”窗口中单击“新建表”按钮，然后在“创建”对话框中的输入表名文本框输入“ls”,单击“保存”按钮； 在弹出的表设计器中输入并设置好编号和平均成绩两个字段；然后单击“确定”按钮，在弹出的“是否输入记录”的对话框中选“否”，完成表的创建。 3.打开xsqk表，进行以下设置：（1）把学号字段的显示标题改为“学生证编号”，显示格式为删除前导空格和尾部空格，掩码规定只能输入8位数字字符。 在数据库设计器窗口中，单击选中“xsqk”表，然后选择菜单“数据库/修改”，打开表设计器。 选中“学号”字段，在标题文本框输入“学生证编号”。 在格式文本框中输入“T”。 在输入掩码文本框中输入“99999999”。（2）把团员字段的默认值设置为“.T.”。 选中“团员”字段。 在“默认值”文本框中输入“.T.”。（3）性别设置为“男”、“女”两个选项，掩码规定只能输入1个汉字，字段有效性规则是“性别=男.OR. 性别=女”，否则给出提示信息“输入错误，请重新输入性别！”，默认性别为“男”。 选中“性别”字段； 在“输入掩码”文本框中输入“XX”； 在“规则”文本框中输入“性别=男.OR. 性别=女”； 在“信息”文本框中输入“性别只能是男或女”；在“默认值”文本框中输入“男”。（4）入学分数必须0，否则给出提示信息“输入分数必须大于0！”,默认分数为500； 选中“入学分数”字段； 在“规则”文本框中输入“入学分数0”； 在“信息”文本框中输入“输入分数必须大于0！”； 在“默认值”文本框中输入“500”,单击“确定”按钮。（5）在表的末尾添加2条新记录（内容自己定）。 在数据库设计器窗口中，单击选中“xsqk”表，然后选择菜单“数据库/浏览”，打开表浏览器； 选择菜单“表/追加新纪录”,在表文件末尾新添加的记录中输入相应内容。 检查以上设置的有效性。4在学生情况表（xsqk.dbf）和成绩表（cj.dbf）之间建立一对多关联，在课程表（kc.dbf）和成绩表（cj.dbf）之间建立一对多关联。 对学生情况表（xsqk.dbf）以学号字段建立主索引。 对成绩表（cj.dbf）以学号字段和课程号字段分别建立普通索引。 对课程表（kc.dbf）以课程号字段建立主索引。 将鼠标移到xsqk表的主索引学号上，拖动它到cj表的索引关键字学号上，松开鼠标左键，此时可以看到在主表与子表的索引学号间产生了一条连线，即表示两表建立了永久性关系。用同样的方法，在kc表的主索引课程号和cj表的索引关键字课程号之间也建立一条连线，如图4所示。图2 建立表之间永久关联 至此学生情况表（xsqk.dbf）和成绩表（cj.dbf）之间的一对多关联和课程表（kc.dbf）和成绩表（cj.dbf）之间的一对多关联建立完毕。 5. 在学生情况表（xsqk.dbf）和成绩表（cj.dbf）之间以学号为关键字，编辑参照完整性，规定两个表之间的“删除规则”、“更新规则”和“插入规则”。Xsqk表与cj表之间的删除规则为级联，更新规则为级联，插入规则为限制。 打开数据库设计器； 选定xsqk与cj表之间的关系线，单击鼠标右键，在快捷菜单中选择 “编辑参照完整性”命令，系统会显示 “编辑参照完整性”对话框，在此对话框中设置xsqk表与cj表间的删除规则为级联，更新规则为级联，插入规则为限制，如图3所示。图3 编辑参照完整性 打开xsqk表,浏览记录，对某条记录做删除标记，关闭浏览器，观察cj表记录。并做出解释：表中与之对应的学号也作上了删除记录原因：因为xsqk与cj的参照完整性删除规则是级联，删除父表的同时会删除子表的记录 打开xsqk表,浏览记录，对某条记录做修改，关闭浏览器，观察cj表记录。并做出解释：Cj表也做出相同的修改原因：因为更新规则也设置了级联，所以父表改变子表也会改变 打开 cj表,插入一条记录，观察现象。并做出解释：Cj表中无法插入，产生触发器失败原因：因为插入规则设置了限制，即父表没有相关记录，子表不能添加实验五 查询和视图二、实验准备和要求1. 本实验要用到实验四建好的学生管理数据库（xsgl.dbc）中的学生情况表、课程表和成绩表（xsqk.dbf,kc.dbf,cj.dbf），可以从天空教室下载，下载完毕把该数据库所在的文件夹设为默认目录。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)答案：create 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 add 职称 c(6) 删除“出生日期”字段。命令：alter table zg drop 出生日期答案： alter table zg drop 出生日期 把“性别”字段的类型改为逻辑型。命令:ALTER TABLE zg ALTER 性别 L 答案： alter table zg alter 性别 l 把“工资”字段改名为“实发工资”。命令：alter table zg rename 工资 to 实发工资答案： 去掉引号2.SQL的操作功能 给课程表（kc）插入一条记录（2001,英语精读,2, ）。命令：insert into kc (课程号,课程名,学分,先修课) values (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 all3. 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 专业=工业工程或select 学号,姓名,性别,入学分数,出生日期 from xsqk where 出生日期 between 1988-01-01 and 1988-12-31 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 专业=市场营销或 select xsqk.学号,姓名,专业,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或者：Select xsqk.学号,xsqk.姓名,xsqk.专业,kc.课程号,kc.课程名,cj.成绩 from xsqk join cj on xsqk.学号=cj.学号 join kc on cj.课程号=kc.课程号 where 成绩 570; ORDER BY Xsqk.入学分数运行：do 简单查询.qpr（2）计算查询（字段为表达式）： 建立统计各专业同学的人数的查询，字段包括专业、人数(保存为“计算查询”)。提示： 需要用xsqk一个表。 人数用表达式 Count(*) as 人数 分组依据按专业分组。SELECT Xsqk.专业, COUNT(*) AS 人数; FROM 学生管理!xsqk; GROUP BY Xsqk.专业DO 计算查询.qpr（3）多表联结查询：建立一个包括每个同学学号、姓名、专业和各课程平均分、总分以及最高分数、最低分数的查询。按学号排序。(保存为“多表查询”)提示： 需要用xsqk和cj二个表！ 平均分、总分以及最高分数、最低分数选用AVG()、Sum()、Max()函数。 要按学号分组才能得到各个同学的平均分、最高分等。SELECT Xsqk.学号, Xsqk.姓名, Xsqk.专业, SUM(Cj.成绩) AS 总分,; AVG(Cj.成绩) AS 平均分, MAX(Cj.成绩) AS 最高分, MIN(Cj.成绩) AS 最低分; FROM 学生管理!xsqk FULL JOIN 学生管理!cj ; ON Xsqk.学号 = Cj.学号; GROUP BY Xsqk.学号; ORDER BY Xsqk.学号DO 多表查询.qpr5. 用视图设计器创建本地视图（1）单表视图：用视图设计器创建包括所有人学分数在550分至570分的同学的学号、姓名、性别、专业和入学分数字段的视图并按入学分数从高到低排列。将入学分数设置为可更新字段。存盘，保存为“单表视图”后打开视图，显示其中女同学的记录并把某个同学的成绩更改分数。关闭视图，选择学生表，看是否能够把修改结果传递回表中？提示：入学分数600至700的表示方法可以有两种： 入学分数=600 .and. 入学分数5000 sjks=ks*0.80 case ks3000 sjks=ks*0.85 case ks2000 sjks=ks*0.90 case ks1000 sjks=ks*0.95 otherwise sjks=ksendcase?”实际购物款数”,sjks7.输入一个成绩，判断其所在档次，档次划分为：90分以上优秀，8090分为良好，7080分为中等，6070为合格，60分以下为不合格。写出程序代码:clearinput 成绩: to cj do case case cj=90 dc=”优秀” case cj=80 dc=”良好” case cj=70 dc=”中等” case cj=60 dc=”合格” otherwise dc=”不合格”endcase?”档次”,dc 8.用DoWhile循环结构：统计sxqk表中入学分数超过570分的人数。写出程序代码:clearuse xsqk rs=0do while not eof()if 入学分数570 rs=rs+1endifskipenddo?”入学分数超过570的人数:”,rsUse9.用FOR循环结构：统计sxqk表中入学分数超过570分的人数。写出程序代码:clearuse xsqkn=reccount()rs=0for i=1 to n if 入学分数570 rs=rs+1 endif skip endfor?超过570人数共：,rsuse10.用Scran指针循环结构：统计sxqk表中入学分数超过570分的人数。写出程序代码:clear use xsqkrs=0scan for 入学分数570 rs=rs+1endsca