实验八.docx
《实验八.docx》由会员分享，可在线阅读，更多相关《实验八.docx（14页珍藏版）》请在冰豆网上搜索。
实验八
实验八SQL语言
【实验目的】
1.掌握SQL语言的数据定义功能：
表的定义，表结构修改，删除，视图的定义
2.熟练掌握SQL语言的数据操作功能：
数据的插入，更新，删除。
3.熟练掌握数据的简单查询、连接查询、带特殊运算符的条件查询、排序查询、分组与计算查询、别名与多表查询、嵌套查询。
4.掌握查询结果的处理。
【实验内容】
1.数据定义。
（1）创建一个数据库rsgl（人事管理）
在命令窗口输入：
Createdatabasersgl
执行命令，创建一个regl数据库，如图8-1所示。
图8-1
（2）创建一个表bmsz（部门设置表），组成字段：
部门名称：
（C,20）、部门岗位人数（N,2）和部门实际人数（N,2）。
在命令窗口输入：
CREATETABLEbmsz（部门名称c（20）primarykey,部门岗位人数N
（2）,部门实际人数N
（2））
LISTSTRUCTURE
执行上面两条命令，VisualFoxPro系统主窗口显示如图8-2所示
图8-2
以上命令用primarykey建立了主关键字。
用MODIFYDATABASErsgl命令打开数据库设计器，可以看到显示结果如图8-3所示。
图8-3数据库设计器中的bmsz表
注意：
在书写VisualFoxPro命令式所有的标点符号、括号、运算符等都必须在英文状态下输入，否则将显示“语法错误”。
（3）创建一个表zgqk（职工信息表）组成字段：
编号（C,10）、姓名（C,8）、性别（C,2）、学历（C,6）、部门（C,20）、住址（C,50）、出生日期（D）、简历（M）和照片（G）。
在命令窗口输入：
CREATETABLEzgqk（编号C（10）PRIMARYKEY,姓名C（8）,学历C（10）,性别C
（2）CHECK（性别="男"or性别="女"）ERROR"性别只能是男或女"DEFAULT"男";
部门名称C（20）,FOREIGNKEY部门名称TAG部门名称REFERENCESbmsz,住址C（50）,出生日期D,简历M,照片G）
这将在当前打开的数据库（Rsgl）中建立zgqk表，其中用Check为性别字段建立了有效性规则（性别="男"or性别="女"）；用Error为该有效性设置了出错提示星星“性别只能是男或女”；用DEFAUIT子句为性别字段设置了默认值是“男”；另外还用子句“FOREIGNKEY部门名称tag部门名称REFERENCESbmsz”建立了zgqk表与bmsz表的联系，“FOREIGNKEY部门名称tag部门名称”在部门名称字段上建立一个普通索引，同时该字段也是连接字段；“REFERENCESbmsz”通过引用bmsz表的主索引“部门名称”与bmsz表建立关系。
如图8-4所示。
图8-4“数据库设计器”窗口
2.数据操作
（1）打开教师提供的zggz数据表。
在命令窗口中输入：
USEd:
\vf6\vfp98\cs\zggz.dbfEXCLUSIVE
执行以上命令后，右击数据库设计器中的zggz表，选择“浏览”选项，显示如图8-9所示。
图8-9插入的记录数据
注意：
命令中的文件位置“d:
\vf6\vfp98\cs\”为范例，实际操作中请根据实际情况进行设置。
（2）将zggz表中的所有记录的交通补足字段值更新为150.
在命令窗口中输入：
UPDATEzggzSET交通补足=150
执行命令后浏览该表，如图8-10所示。
图8-10修改后的记录数据
（3）为zggz表中基本工资小于1500的职工增加10%的基本工资。
UPDATEzggzSET基本工资=基本工资*1.1WHERE基本工资<1500
图8-11带条件修改
说明：
一般用WHERE子句指定条件，以更新满足条件的一些记录的字段值，并且一次可以更新多个字段；如果不适用WHERE子句，则更新全部记录。
（4）删除zggz表中编号为“10003”的记录。
在命令窗口中输入：
DELEFROMzggzWHERE编号="10003"
执行命令后浏览该表，如图8-12所示。
图8-12删除记录
注意：
在VisualFoxPro中SQLDELETE命令是逻辑删除记录。
如要物理删除记录，继续使用PACK命令。
4.单表查询
依照图8-13为表zgqk输入数据：
（1）显示zgqk（职工信息表）中的所有记录。
在命令窗口中输入：
SELECT*FROMzgqk
命令中的“*”表示输出显示所有字段，数据来源是zgqk表，表中的内容以浏览方式显示。
（2）显示zgqk（职工情况表）中的部门。
SELECTDISTINCT部门名称FROMzgqk
显示如图8-14所示。
图8-14显示无重复记录
其中DISTINCT表示显示无重复结果。
（3）显示zggz（职工工资表）中的所有记录，并将奖金一项加50元。
在命令窗口中输入：
SELECT编号,基本工资,津贴,奖金+50AS奖金,交通补足FROMzggz
执行命令后，屏幕显示如图8-15所示。
图8-15显示调整后的记录
（4）显示zgqk表中所有男职工的编号、姓名和性别字段值。
在命令窗口中输入：
SELECT编号,姓名,性别FROMzgqkWHERE性别="男"
执行命令后，屏幕显示如图8-16所示。
图8-16显示所有男职工记录
（5）显示zgqk表中出生日期在1970年1月1日和1979年12月31日之前的编号、姓名、出生日期。
在命令窗口中输入：
SELECT编号,姓名,出生日期FROMzgqk;
WHERE出生日期BETWEEN{1970-01-01}AND{1979-12-31}
执行命令后，屏幕显示如图8-17所示。
图8-17显示20世纪70年代的员工记录
注意：
在操作此命令前，需把“工具”菜单的“选项”命令中“常规”选项卡的“严格的日期格式”设置为0—关闭，否则讲出现报错信息，如图8-18所示。
图8-18提示设置严格日期格式
（6）显示zgqk表中姓李的职工的编号，姓名，部门名称，出生日期。
在命令窗口中输入：
SELECT编号,姓名,部门名称,出生日期FROMzgqk;
WHERE姓名LIKE"李%"
执行命令后，屏幕显示如图8-19所示。
图8-19显示姓李职工记录
说明：
LIKE为字符串匹配运算符，通配符“%”表示0个或多个字符，另外还有一个通配符“_”下划线表示一个字符。
（7）按出生日期降序显示zgqk表中的编号，姓名，出生日期。
在命令窗口中输入：
SELECT编号,姓名,出生日期FROMzgqk;
ORDERBY出生日期DESC
执行命令后，屏幕显示如图8-20所示。
图8-20降序排序查询
（8）按基本工资的升序显示zggz表中的编号，基本工资，津贴。
在命令窗口中输入：
SELECT编号,基本工资,津贴FROMzggzORDERBY基本工资ASC
执行命令后，屏幕显示如图8-21所示。
图8-21升序排序查询
（9）计算所有员工的基本工资总和。
（新加的不涉及分组的简单统计）
在命令窗口中输入：
SELECTSUM（基本工资）AS基本工资FROMzggz
执行命令后，屏幕显示如图8-22所示。
（10）统计各部门的人数
在命令窗口中输入：
SELECT部门名称,COUNT（部门名称）AS部门人数FROMzgqk;
GROUPBY部门名称
执行命令后，屏幕显示如图8-23所示。
图8-23统计查询
2多表查询
（1）查询并显示每个职工的编号，姓名，职称，基本工资。
在命令窗口中输入：
SELECTzgqk.编号,zgqk.姓名,zgqk.职称,zgqk.出生日期,zggz.基本工资;
FROMzgqk,zggz;
WHEREzgqk.编号=zggz.编号
执行命令后，屏幕显示如图8-24所示。
图8-24连接查询职工工资
说明：
在联接操作中，要使用别名做前缀。
如在减少命令的FROM之后有两个以上关系，那么这几个关系之间肯定有一种联系，否则无法构成检索表达式，当FROM之后的多个关系中含有相同的属性名时，其相同属性名的值应相等，并且必须用关系前缀直接指明属性所属的关系，如zgqk.编号，“.”前面是关系名，后面是属性名。
（2）查询技术部门的所有职工的姓名、职称，部门名称，基本工资。
在命令窗口中输入：
SELECTa.编号,姓名,职称,部门名称,基本工资;
FROMzgqka,zggzb;
WHEREa.编号=b.编号ANDa.部门名称="技术部"
执行命令后，屏幕显示如图8-25所示。
图8-25连接查询技术部门的职工基本工资情况
说明：
SQL语序在FROM短语中为关系名定义别名来简化长表名，格式为:
<关系名><别名>，当属性名不同时可以省略关系名。
（3）显示技术部门的男职工的基本工资
在命令窗口中输入：
SELECT姓名,性别,部门名称,基本工资;
FROMzgqk,zggz;
WHEREzgqk.编号=zggz.编号AND部门名称="技术部"
执行命令后，屏幕显示如图8-26所示。
图8-26连接筛选查询
（4）显示基本工资在1300至1600之间的职工的姓名，职称和基本工资。
在命令窗口中输入：
SELECT姓名,职称,基本工资;
FROMzgqk,zggz;
WHEREzgqk.编号=zggz.编号AND基本工资BETWEEN1300AND1600
执行命令后，屏幕显示如图8-27所示。
图8-27BETWEEN在查询中的应用
【实验练习】
1.打开实验八下的“学生管理”数据库，要求如下：
（数据库会发给学生）
1）插入一条记录到学生选课表中，学号为“99222”，姓名为“张三”，出生日期为1991年1月2日。
2）将“99003”选修“102”课程的成绩修改为85分。
3）删除学时数低于50的课程。
4）查询“学生选课”表的全部记录。
5）查询“学生情况”表所有记录的学号、姓名、性别。
6）查询在1980年1月1日以后出生的女生信息。
7）查询学生选修课程的学号、姓名、课程号、成绩。
8）查询选修大学物理的学号、成绩。
9）查询所有成绩的平均分、最高分、最低分。
10）查询每个人的总分。
11）查询被两个人以上选修的课程的平均分。
2.数据定义（选做）
1）创建一个zggl（职工管理系统）的数据库，在其中创建以下数据表。
2）创建一个表zggz（职工工资表），组成字段：
编号（C，10）、基本工资（N，6）、津贴（N，4）、奖金（N,4）和纳税（N,4）。
3）创建一个表rcxq（人才需求表），组成字段：
部门名称（C,20）、人数（N,2）、学历（C,6）和岗位（C,10）。
4）创建一个表zgyj（职工业绩表），组成字段：
序号（C,6）、姓名（C,8）、项目名称（C,20）、类别（C,10）、量化（N,2）和项目简介（M）。
5）将zggz表中的“纳税”字段改为“交通补足”。
6）为rcxq表增加一个“年龄”字段（数值类型），同时为之定义字段级有效性规则：
年龄大于30，小于50。
7）修改或定义rcxq表“年龄”字段的有效性规则
8）删除表rcxq表“年龄”字段的有效性规则。
9）删除rcxq表的“年龄”字段
10）删除rcxq表
【实验思考】
1.SQL的9个命令动词是哪些，其功能分别是什么？
2.插入，更新和删除记录如何用SQL实现？
升级会员 