Visual FoxPro 6.0学习笔记九

第九章SQL 语言应用

一、利用SELECT语句进行查询

1.基于单表的SQL简单查询
（1）查询STUDENT表中全部记录的全部字段信息
SELECT * FROM STUDENT&&查询STUDENT表中的全部字段信息

运行结果为：

（2）查询STUDENT表中全部记录的“学号”、“姓名”、“性别”、“专业”等部分字段信息
SELECT 学号,姓名,性别,专业 FROM STUDENT

运行结果为：

（3）查询各专业学生来源（去掉重复记录）
SELECT DISTINCT 专业,籍贯 FROM STUDENT&&这里是去掉了专业和籍贯两列数据均相同的记录。

运行结果为：

补充：如果是对单字段进行去重，得到的结果为：

（a）对专业字段去重：

（b）对籍贯字段去重：

（4）查询所有学生的“学号”、“姓名”、“专业”、“入学成绩”，并将“入学成绩”字段指定显示列名为“高考成绩”
SELECT 学号,姓名,专业,入学成绩 AS 高考成绩 FROM STUDENT

运行结果为：

（5）查询1978年以前出生的男同学
SELECT * FROM STUDENT WHERE 出生日期>{^1978/01/01} AND 性别="男"

运行结果为：

（6）查询姓“张”的同学的相关信息（用LIKE指定查询条件）
SELECT * FROM STUDENT WHERE 姓名 LIKE “张%”&&这里的%代表任意多个字符

运行结果为：
（7）利用SELECT查询计算STUDENT表中的“入学成绩”的平均值
SELECT AVG(入学成绩) AS “入学的平均分数” FROM STUDENT

运行结果为：
（8）查询结果先按专业排序，相同时按入学时的成绩降序排序
SELECT * FROM STUDENT ORDER BY 专业 ASC, 专业DEC

运行结果为：
（9）按专业分组查询，统计查询各个专业“入学成绩”的平均分数
SELECT 专业,AVG(入学成绩) AS “专业平均成绩” FROM STUDENT GROUP BY 专业

运行结果为：
（10）查询入学成绩排在前3名的同学信息
SELECT TOP 3 * FROM STUDENT ORDER BY 入学成绩

运行结果为：
（11）查询入学成绩在530分到590分之间的学生记录信息并按降序输出
SELECT * FROM STUDENT WHERE 入学成绩 BETWEEN 530 AND 590；
ORDER BY 入学成绩 DESC

运行结果为：

（12）查询“山东”或“福建”籍的同学记录信息
SELECT * FROM STUDENT WHERE 籍贯 IN ("山东","福建")

运行结果为：
（13）统计STUDENT 表中各专业学生的人数
SELECT 专业,count(*) AS 人数 FROM STUDENT GROUP BY 专业

运行结果为：
注意：当命令执行后，会自动打开“浏览”窗口，显示查询的结果。
2.基于多表的SQL查询
基于多个表的查询中，由于查询的数据取自多个表，因此进行多表查询首先要考虑表之间的连接关系，最简单的解决方法是取两个表中的公共字段作为两个表的连接条件。
首先创建一个“Rsgl.DBC”数据库，数据库中包含“员工.DBF”和“工资.DBF”两个表。
SELECT 员工.编号,员工.姓名,员工.部门代码,工资.实发工资;

FORM 员工,工资WHERE 员工.编号=工资.编号

运行结果为：
3.嵌套查询

若一个SELECT查询（父查询）的WHERE字句中包含另一个SELECT查询（子查询），则称这种查询为嵌套查询。执行嵌套查询时，首先查询出子查询的结果，然后将子查询的结果用于父查询的条件。
“Xsgl.DBC”数据库中，除了包含STUDENT表，还含有“COURSE.DBF”和“SCORE.DBF”两个表，根据COURSE表和SCORE两个表，利用嵌套查询查询“李天明”老师所授课的学生学号、课程编号与成绩信息。
SELECT 学号,课程编号,成绩 FROM SCORE WHERE 课程编号 IN;
(SELECT 课程编号 FROM COURSE WHERE 任课教师=”李天明”)

运行结果为：
4.连接查询
利用内部连接查询，对员工.DBF和工资.DBF两个表进行多表查询，实现部门代码为“01”的每位员工的编号、姓名、部门代码和实发工资等字段信息的查询输出。
SELECT a.编号,a.姓名,a.部门代码,b.实发工资;
FROM 员工 a INNER JOIN 工资 b ON a.编号=b.编号;
WHERE 部门代码=”01”

运行结果为：

5.查询结果重定向
VFP中，SELECT语句的查询结果默认在“浏览”窗口中显示，用户根据需要可以将其保存在数组、临时表、一个新的表文件中或从打印机上打印输出。利用SELECT语句完成如下的数据查询，根据查询任务填写相应的SELECT语句。
（1）查询部门代码为“01”的人员信息，并将查询结果输出到临时表LS中
SELECT * FROM 员工 WHERE 编号=”01”INTO CURSOR LS&&INTO CURSOR将创建临时表

使用BROWSE命令即可浏览临时表

运行结果为：

（2）查询部门代码为“02”的人员相关信息，并将查询结果输出到永久表NT中
SELECT 编号,姓名,姓名,部门代码 FROM 员工 WHERE 部门代码=”02”;
INTO DBF NT&&INTO DBF是创建永久表

运行结果为：

这个永久表是可以被使用的：

但临时表不可以：

（3）将查询结果输出到数组SALARY中
SELECT * FROM 工资 INTO ARRAY SALARY

查看SALARY数组的长度

?len(salary)

运行结果为：

这里请注意salary是一个二维数组，LEN()函数查看的一共有几行，如果要分别查看数组的行数和列数，需要使用一下命令：

?alen(salary,1)&&一维长度
?alen(salary,2)&&二维长度

运行结果为：

二、SQL语言的数据更新

1.数据更新
（1）利用UPDATE 命令实现全部员工的基本工资增加10%
UPDATE 员工 SET 基本工资=基本工资+基本工资*0.1

运行结果为：

UPDATE 员工 SET 基本工资=基本工资+120, 部门代码=”03”WHERE 部门代码=”11”

运行结果为：
2.插入记录
INSERT INTO STUDENT("学号","姓名","性别") VALUE(“0101506”,”韩菲儿”,”女”)

运行结果为：

三、SQL语言的数据定义

利用SQL语言的数据定义命令，创建如下所示的数据库。

数据库文件名——教学管理.DBC，包含的三个数据表名依次为STUDENT1.DBF、COURSE1.DBF、SCORE1.DBF，结构如下：

STUDENT1(学号C(7),姓名C(8),性别C(2),专业C(10),出生日期D,是否为党员L,入学成绩N(7,2),备注M)

COURSE1(课程编号C(5),课程名称C(24),性别任课教师C(8))

SCORE1(学号C(7),课程编号C(5),成绩N(7,2))

（1）利用CREATE TABLE语句建立STUDENT1.DBF表并指定学号为主关键字，由此建立主索引，且给“入学成绩”字段指定有效性规则。

CREATE DATABASE 教学管理.DBC

CREATE TABLE STUDENT1(学号 C(7) PRIMARY KEY, 姓名 C(8),性别 C(2),;
 专业 C(10),出生日期 D ,是否党员 L,;
 入学成绩 N(7,2) CHECK(入学成绩>0) ERROR "入学成绩必须大于 0!",;
 备注 M )

（2）创建COURSE1.DBF表并指定课程编号为主关键字由此建立索引。

CREATE TABLE COURSE1(课程编号 C(5) PRIMARY KEY,;
课程名称 C(24),任课教师 C(8))

（3）建立SCORE1.DBF表，以“学号+课程编号”为主关键字建立索引标记名为“学生号课程编号”的主索引，并指定其与STUDENT1.DBF和COURSE1.DBF建立一对多的永久联系。

CREATE TABLE SCORE1 (学号 C(7),课程编号 C(5),成绩 N(7,2),;
 PRIMARY KEY 学号+课程编号 TAG 学课编号,;
 FOREIG KEY 学号 TAG 学号 REFERENCE STUDENT1,;
 FOREIG KEY 课程编号 TAG 课程编号 REFERENCE COURSE1)

运行结果为：

思考与练习

2.若要建立“员工管理.DBC”数据库，其中包含RSDA和SALARY两个表，并且两个表通过公共字段“编号”可以建立起一对一的关系，两个表的结构如下：

RSDA(编号 C(4),姓名 C(8),性别 C(2),婚否 L,出生日期 D,工作时间 D,部门 C(8))

SALARY(编号 C(4),基本工资 N(7,2),奖金 N(4,0),所得税 N(7,2),实发工资 N(7,2))

写出完成下列任务的SQL操作语句：

（1）创建RSDA.DBF表并指定“编号”为主关键字由此建立索引。

CREATE DATABASE 员工管理.DBC
CREATE TABLE RSDA(编号 C(4) PRIMARY KEY,姓名 C(8),性别 C(2),;
婚否 L,出生日期 D,工作时间 D,部门 C(8))
（2）建立SALARY.DBF表，指定主关键字为“编号”并于RSDA.DBF表建立一对一的永久联系。
CREATE TABLE SALARY(编号 C(4) PRIMARY KEY,基本工资 N(7,2),;
奖金 N(4,0),所得税 N(7,2),实发工资 N(7,2),FOREIG KEY 编号 TAG 编号 REFERENCE RSDA)

打开数据库设计器进行查看，运行结果为：

（3）查询出生日期在1970年以前的员工的全部信息。

SELECT * FROM RSDA WHERE 出生日期>{^1970/01/01}

（4）查询RSDA表中女职工的“姓名”、“部门”、“工作时间”等字段的信息，且查询结果按“部门”字段排序，并在“浏览”窗口中显示。

SELECT 姓名,部门,工作时间 FROM RSDA WHERE 性别="女" ORDER BY 部门

（5）查询RSDA表中姓“李”的职工的编号、姓名、部门信息。

SELECT 编号,姓名,部门信息 FROM RSDA WHERE 姓名 LIKE "李%"

（6）按“实发工资=基本工资+奖金-所得税”更新SALARY表中所有记录的“实发工资”字段。

UPDATE SALARY SET 实发工资=基本工资+奖金-所得税

（7）由 RSDA 表创建仅包含部门为“销售部”人员记录的表 XSB.DBF。

SELECT * FROM RSDA WHERE 部门="销售部" INTO DBF XSB

（8）基于 RSDA 和 SALARY 两个表，建立一个包含各位员工相应“编号”、“姓名”、

“部门”和“实发工资”的新表 SFGZ.DBF，并按“部门”排序。

SELECT RSDA.编号,RSDA.姓名,RSDA.部门,SALARY.实发工资 FROM RSDA;

INNER JOIN SALARY ON RSDA.编号=SALARY.编号;

ORDER BY 部门

（9）统计各部门人员的实发工资的总和

SELECT SUM(实发工资) FROM SALARY GROUP BY 部门

（10）修改SALARY的结构，增加一个津贴（N(4,0)）字段。

ALTER TABLE SALARY ADD 津贴 N(4,0)