实验五 视图和查询

实验五查询和视图一、实验目的1.掌握SQL语句的定义功能和数据操作功能。

2.掌握SQL语句的查询功能，包括连接查询，内外嵌套查询，带计算的查询等。

3.掌握利用设计器创建和使用查询。

4.掌握利用设计器创建和使用本地视图。

二、实验准备和要求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 成绩<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名的信息。

（在浏览窗口内显示）命令：select * from xsqk join cj on xsqk.学号=cj.学号 where 课程号="1001" top 2 order by 成绩 descselect top 2 * from xsqk join cj on xsqk.学号=cj.学号 where 课程号=”1001”order by 成绩 desc②查询“计算机科学”专业的学生信息并将结果存人临时表ls1中。

（用Top 2短语在浏览窗口内显示）(使用Into Cursor ls1)命令：select * from xsqk where 专业="计算机" into cursor ls1③查询学号为“20070101”的学生的考试成绩信息并将结果存人永久表xhcj中。

（使用Into Table xhcj短语输出）命令：select * from cj where 学号="20070101" into table xhcj4. 用查询设计器创建查询（1）简单查询：用查询设计器创建对1989年出生且入学分数大于570分同学的查询，按入学分数排序。

(保存为“简单查询”)。

SELECT *;FROM 学生管理!xsqk;WHERE YEAR(出生日期) = 1989;AND Xsqk.入学分数 > 570;ORDER BY Xsqk.入学分数运行：do 简单查询.qpr（2）计算查询（字段为表达式）：建立统计各专业同学的人数的查询，字段包括专业、人数(保存为“计算查询”)。