第五章关系数据库标准语言SQL

1、数据库技术基础（2课时，笔试4%）Visual FoxPro 6.0初级知识（1课时，笔试4%，上机5%）数据类型与数据运算（4课时，笔试8% ）Visual FoxPro数据库及其操作（8课时，笔试10%，上机10% ）关系数据库标准语言SQL（5课时，笔试20%，上机20% ）查询与视图（6课时，笔试6%，上机20% ） 程序设计语言（8课时，笔试6%，上机10% ）表单设计与应用（8课时，笔试8%，上机20% ）菜单设计与应用（4课时，笔试2%，上机10% ）报表设计（3课时，笔试2%，上机5% ）开发应用程序（1课时，笔试2%）v关系数据库标准语言SQL5.1 SQL概述5.2 数据查

2、询5.3 数据操作5.4 定义功能5.1 SQL概述Visual FoxPro中的命令可以实现对数据库中各种数据对象的操作，但与其相比，使用SQL会更加方便，而且在SQL中可以实现一些Visual FoxPro命令无法实现的功能。SQL的特点： 1.SQL是一体化的语言 SQL可以完成数据库操作要求的所有功能，包括数据查询、数据定义、数据操作和数据控制，是一种全能的数据库语言。 数据查询 SELECT 数据操作 INSERT UPDATE DELETE 数据定义 CREATE DROP ALTER 数据控制 GRANT REVOKE 2.SQL是一种高度非过程化的语言 3.SQL语言非常简洁

3、4.SQL语言可以直接以命令方式交互使用，也可以嵌入到计算机程序语言中使用。5.2 数据查询 数据查询，就是根据给定的条件从关系中 找出符合记录的元组。 SQL的核心是查询，SQL的查询命令也称作SELECT命令，它的基本形式是SELECTFROMWHERE，多个查询可以嵌套。数据查询的常用命令SELECT 说明要查询的数据FROM 说明要查询的数据来自哪个或那些表可以对单个表或多个表进行查询。DISTINCT 去掉查询结果中的重复记录WHERE 说明查询条件GROUP BY 对查询结果进行分组HAVING 必须跟随GROUP BY 短语使用，用来限定分组必须满足的条件。ORDER BY 用来

4、对查询的结果排序演示：建立学生、教师、课程、成绩表。第一节：简单查询 简单查询基于一个关系，即仅对一个表进行查询，可以包括简单的查询条件。格式：selectfromwhere字段名列表指查询结果中包含的字段名，多个字段名之间用半角逗号隔开。查询条件是一个逻辑表达式，它是由多个表达式通过逻辑运算符（not、and、or）连接而成，关系表达式中可以使用关系运算符。运算符 含义= 等于,!=,# 不等于= 精确等于 大于= 大于等于 IS NULL 为空值IN 在一组值的范围内 LIKE 字符串匹配运算%，_Betweenand 在两组之间实例讲解:1.从课程表中检索所有的课程名。 select C

5、name from course2.从教师信息表中检索所有教师的职称，要求去掉重复值。 select distinct prof from teacher3.检索课程表中的所有元组。 select * from Course4.检索学生信息表中性别为女并且班级号为95033的学生记录。 Select * from student ; Where class=“95033” and sex=“女” 注意：SQL语句过长，增加分行符（;）5.检索成绩表中成绩为85、86或88的记录。 Select * from score; where Degree IN (85,86,88)6.检索成绩表中成绩

6、在60至70之间的记录。 Select * from score; where Degree between 60 and 70 7.显示学号前两位为“11”的学生的姓名、课程名和成绩。 Select 姓名,课程名,成绩 from student; where Sno LIKE “11_”注意：在Visual FoxPro中可以用通配符？,*表示一个和任意个字符；而在SQL中有其专用的通配符_和%,表示一个或若干个任意字符,它们必须与LIKE运算符号结合使用Left(学号,2)=“11”第二节 简单的联接查询联接是关系的基本操作之一，连接查询是一种基于多个关系的查询。格式：selectfrom

7、where在联接查询中，当需要对多个表联接时，可以指定查询显示字段；where句中指定连接条件，在from子句中指定要连接的表。实例讲解:1.显示所有学生的姓名、课程号和成绩。Select Sname,Cno,Degree;From student,score;Where student.Sno=Score.sno注意:对于联接的多个表通常存在公共字段，为了区分是哪个表中的字段，在联接条件中通过表名前缀指定连接字段。SQL为了简化输入，允许在查询中使用表的别名，可以在from中为表定义一个临时别名，然后在查询中引用。别名在from短语中的指定。格式: Select Sname,Cno,Degr

8、ee;From student x,score y;Where x.Sno=y.sno第三节 嵌套查询嵌套查询是一类基于多个表的查询，查询的结果是出自一个表中的字段，但是查询的条件要涉及多个表。嵌套查询一般分为两层，内层和外层，被括号括起来的为内层查询，是第一次要进行的查询，外层查询是在内层查询的基础上进行的查询，是第2次要进行的查询。实例讲解: 1.显示任课教师为“王学萍”的学生的成绩。Select 学生学号,课程号,成绩 from score ;Where score.课程号=;(select Course.课程号from Course , teacher where Course.教师号

9、= teacher .教师号 and 教师姓名=“王学萍” )2.显示和学号为“112”的学生同年出生的所有学生的学号、课程号、成绩。 Select Score.Sno,Cno,Degree from; Student ,score student.Sno=Score.Sno; And student.Sno!=“112” and YEAR(birthday)=(select YEAR(birthday) from student where student.Sno=“112”第四节 排序查询 通过在select语句中加入order by可将查询结果排序，可以按升序ASC和降序DESC排列行或

10、列。注意：升序是默认的排列方式，order by 必须是SQL命令的最后一个子句（除指定查询输出去向的子句外）。实例讲解：1.以班级号（class）降序显示学生表中的所有记录。Select * from student order by class desc2.以Cno升序,Degree降序显示Score表的所有记录。 Select * from Score; order by Cno,Degree desc 第五节 计算查询 SQL不仅有一般的检索功能，而且还有计算方法的检索。用于计算检索的函数有:函数名 功能AVG 计算一个数值列表的平均值。COUNT 计算指定列表中选择的项数， coun

11、t(*)统计查询输出的行数。MIN 计算指定列中的最小值。MAX 计算指定列中的最大值。SUM 计算指定列中的数值总和。实例讲解：1.显示班级的个数 select COUNT(DISTINCT class) From student 2.显示班级号为“95031” 的学生人数。 select count (*) from student ; where class=“95031”3.显示成绩表中最低的成绩分数。 select sno,cno min (degree) As “最低分”; 注意：As短语可指定查询结果中显示的新属性名。4.显示课程号为1-108的课程平均分。 select avg

12、 (degree) As “课程平均分”; from score where cno=“1-108” 第六节 分组计算查询通常来说，一个计算函数的范围是满足where子句指定条件的所有记录。当加上group by 子局后，系统会将查询结果按指定列分成集合组。当一个计算函数和一个group by 起子句一起使用时，计算函数的范围变为每组所有的记录。也就是说，一个结果是由组成一组的每个记录集合而成的。例子：统计一个系里各班的人数，就应该把系里的学生按班级分组统计各班的人数。 当where子句、group by 子句、having子句同时出现的时候，执行顺序如下; 执行where子句，从表中符合选择

13、条件的行，由group by子句对选取的行进行分组；执行计算函数；执行having子句选取满足条件的组。实例讲解:1.显示最低分大于60，最高分小于80的学号。 select sno from score; group by sno; having min(degree)60 and max(degree)80 第七节 空值查询SQL支持空值，所以也支持空值查询假设在score中，课程号为3-375的成绩还没有录入，它的学号和成绩均为空。例：将已经登记成绩的信息全部显示出来。 select * from score where degree is not null第八节：自联接查询 一般的查询

14、中，别名并不是必须的，但是在自联接查询中，别名是必不可少的。 SQL不仅可以对多个表实行联接查询，也可以将同一关系与其自身进行联接，这种联接称为自联接，在可以进行这种自连接操作的关系上，实际存在着一种特殊的递归联系，即关系中的一些元组，根据自身统一值域的两个不同的属性，可以与另外一些元组有一种对应关系。假设有一个雇员关系： (雇员号,官员姓名,经理） 其中雇员号和经理两个属性出自同一个值域，同一元组的这两个属性值是上下级关系，即在他们都是雇员的同时，还有一部分人是经理，而经理对于普通雇员有领导关系。雇员号雇员号雇员姓名雇员姓名经理经理E1张朋E2刘学E1E3杨强E1E4冯晓E3例：根据雇员关系

15、列出经理及其领导的职员清单。 select S.雇员姓名AS领导,”管理雇员”AS管理,E.雇员姓名AS雇员； from 雇员 S, 雇员 E ; where S.雇员号=E.经理通过定义别名形成了两个逻辑关系，一个是经理关系S,一个是雇员关系E,结果在关系S和E上的联接实现了检索要求。 第九节：内外层互相关嵌套查询 嵌套查询都是外层查询依赖于内层查询的结果，而内层查询与外层查询无关。事实上，有时也需要内需要外层查询提供值，而外层查询的条件需要内层查询的结果。例子：显示成绩低于该课程平均成绩的学生成绩表。 select sno,cno,degree from score a; where de

16、gree(select avg(degree); from score b where o=o ) 第十节使用量词和谓词的查询 前面已经使用过和嵌套查询有关的IN和NOT IN运算符，除此之外还有两类和子查询有关的运算符，他们有以下两种形式格式1.any/all/some格式2.not exists (子查询）Any和some表示只要子查询中存在符合条件的行，结果就成立；而all只有子查询中的所有的行都符合条件，结果才成立。Exists是谓词，exists或not exists是用来检查在子查询中是否有结果返回，即存在元组或不存在元组。实例讲解;显示所有任课教师的姓名和所属院系。Select

17、tname,depart from teacher a;Where exist (select * from course b; where a.tno=b.tno)第十一节 超链接查询在普通联接即内部联接中是把符合条件的记录包含到运算结果中，除此之外，还可以把不符合条件记录的部分属性也包含到运算结果中，这样的联接称之为超联接。 在一般的SQL中，超联接包括左联接“*=”和右联接“=*”。 左联接，在结果表中包含第1个表中满足条件的所有记录；如果有在联接条件上匹配的元组，则第2个表返回相应值，否则第2个表返回空值。 右联接，在结果表中包含第2个表中满足条件的所有记录；如果有在联接条件上匹配的元

18、组，则第1个表返回相应值，否则第1个表返回空值。完全联接：先按左联接进行运算，再按右联接进行运算。对于元组中不符合联接条件的属性返回空值。格式：Selectfrominner left/right/full;Join on where.实例讲解：左联接，即除满足联接条件的记录出现在查询结果中，第一个表中不满足联接条件的记录也出现在查询结果中。Select teacher.tno, tname, depart, cname from teacher left join course on ; teacher.tno=course.tno第十二节 集合并运算SQL支持集合的并运算（UNION），即

19、可以将两个select 语句的查询结果通过并运算合并成一个查询结果.实例讲解： 显示结果是prof为讲师和助教的教师信息。 select * from teacher where prof=“讲师”; UNION; select * from teacher where prof=“助教” 第十三节 VISUAL FOXPRO中几个特殊选项1.显示部分结果 top注意：top要与order by 一起使用才有效。实例：显示成绩最高的3位同学的成绩。Select * top 3 from score order by degree desc2.将查询结果放在数组中格式：INTO ARRAY 实例

20、：将查询到的教师信息存储在数组tm中。Select * from teacher into array tm(X)3.将查询结果放在临时文件中格式：INTO CURSOR 4.将查询结果放在永久性表中格式：INTO DBF/TABLE 5.将查询结果放在文本文件中格式：TO FILE 6.将查询结果输出到打印机中格式：TO PRINTERPROMPT 5.3 数据操作SQL的操作功能是指对数据库中数据的操作功能，主要包括数据的插入、更新和删除。5.3.1插入 INSERT 命令用于在一个表中添加新纪录，然后给新纪录的字段赋值。Visual FoxPro支持两种插入命令格式，一种是标准格式，另一

21、种是Visual FoxPro6.0的特殊格式。格式1. INSERT INTO添加新纪录的表名 VALUES格式2 INSERT INTO ; FROM ARRAY 实例讲解：给student表插入一条学生记录。 Insert into student; values(“223”,”妞妞”,; ”女”,”1981-12-11”, “95033”)5.3.2 更新UPDATE 命令是用新的值更新表中的记录。格式：UPDATE 修改的表名 SET 被更新字段=新值WHER 限制条件实例讲解：将student表中学号为115的学生的班号修改为95033.Update student set 班级号

22、=“95033”where 学号=“115”5.3.3 删除DELETE命令用于给记录加上删除标记。格式：Delete from 表名 where 限制条件实例:将student表中学号为115的学生记录删除。Delete from student where 学号=“115”5.4 定义功能标准的SQL定义功能非常广泛，一般来说，包括数据库的定义、表的定义、视图的定义、存储过程的定义、索引的定义等。本节将具体介绍表的定义和视图的定义。5.4.1表的定义本身独立存在的表称为基本表，在SQL语言中一个关系唯一对应一个基本表。基本表的定义指建立基本关系模式。格式： create table/dbf

23、 () check逻辑表达式1errordefaultprimary key tagIuniquetag实例讲解：1.建立一个学生情况表std. create table std(;St_class c (8);St_no c (10) primary key not null)5.4.2 表的删除 用以从数据库中删除一个基本表及其全部内容。格式：drop table .表名实例：删除表std Drop table std5.4.3 表结构的修改 alter table 格式1向表中添加新的字段和新的索引。Alter table Addcolumn(checkerrordefaultprimary key I; unique ;add primary key tag foradd unique tag foradd foreign key tag for说明: Add column:向表中添加新的字段。add primary key tag 为该表建立主索引，一个表只能有一个主索引。add unique tag for：为该表建立候选索引，一个表可以有多个候选索引。add foreign key tag for为该表建立外（非主）索引，与指定的父表建立关系，一个表可以有多个外索引。实例讲解：为学生表添加成绩字段C宽度20Alter table 学生 add 成绩