第七讲 Visual FoxPro SQL高级查询语句.ppt

1、第七讲 SQL高级查询语句,西南林业大学:鲁 莹 学时：2节,教学内容,7.1 特殊查询语句 7.2 联接查询 7.2.1 Where联接 7.2.2 JOIN联接 7.2.3 自联接 7.3 嵌套查询 7.4 查询结果输出 7.5 数据定义语言 7.6 综合应用示例,教学目标及重点难点,教学目标 掌握特殊查询语句的使用方法 掌握联接查询的使用方法 掌握嵌套查询中IN关键字的使用方法 了解数据表创表语句 掌握视图的创建和使用方法 能灵活应用SQL语句解决实际问题 重点难点 联接查询、子查询的理解及应用 创表语句灵活使用,7.1.1 空值查询,空值判断查询条件常用来搜索某一字段值为空值的记录，可

2、以使用IS NULL或IS NOT NULL关键字来指定这种查询条件。 注：NULL值表示字段的数据值未知或不可用，它并不表示零（数字值或二进制值）、零长度的字符串或空白（字符值）。 Select * From ClassInfo Where ClassDesc is null Select * From ClassInfo Where ClassDesc is Not null,7.1.2 集合的并运算(Union),UNION 运算符将两个或多个 SELECT 语句的结果组合成一个结果集。使用 UNION 组合的结果集都必须满足下列条件： 具有相同的结构 字段数目相同 结果集中相应字段的数

3、据类型必须兼容 UNION运算符的指定格式如下： SELECT 语句 UNION ALL SELECT 语句,UNION示例,-Union联接多个结果集 Select * From StudScoreInfo Where StudScore=60 And StudScore=90 And StudScore=90 And StudScore=60 And StudScore=70,学生各分数段等级人数,Select 优秀,Count(*) From StudScoreInfo; Where StudNo=99070470 And StudScore=90; Union All; Select

4、良好,Count(*) From StudScoreInfo; Where StudNo=99070470 And StudScore=80 And StudScore=70 And StudScore80; Union All; Select 不及格,Count(*) From StudScoreInfo; Where StudNo=99070470 And StudScore60,7.2 联接查询,当一个查询同时涉及两个以上的表时，称为联接查询。 联接查询是通过各个表之间共同属性列的关联来查询数据，数据表之间的联系是通过表的字段值来体现的 联接操作的目的就是通过加在联接字段上的条件将多个表

5、联接起来，以便从多个表中查询数据。 联接查询是关系数据库中最主要的查询，包括等值与非等值联接查询、自身联接查询、外联接查询等。 表的联接方法有两种： 方法1：表之间满足一定条件的行进行联接，此时FROM子句中指明进行联接的表名，WHERE子句指明联接的列名及其联接条件。 方法2：利用关键字JOIN进行联接。,7.2.1 Where联接,Select StudInfo.StudNo,StudInfo.StudName,StudInfo.ClassID,ClassInfo.ClassName From StudInfo,ClassInfo Where StudInfo.ClassID=ClassI

6、nfo.ClassID And StudInfo.StudNo in(99070470,99070401) StudInfo.StudNo in(99070470,99070401)为查询条件，而“StudInfo.ClassID=ClassInfo.ClassID为联接条件，“ClassID”为联接字段。 联接条件的一般格式为： . . 其中,比较运算符主要有：、！。 引用两个表中的列名相同的字段时必须用表名前缀限制字段来自于哪个表,Where联接,Select S.Studno,S.StudName,C.ClassName,SI.CourseID,SI.StudScore From Stu

7、dInfo S,ClassInfo C,StudScoreInfo SI Where S.Studno=SI.StudNo And C.ClassID=S.ClassID And S.StudNo=99070470,7.2.2 JOIN联接,具体分为以下几种： INNER JOIN ：显示符合条件的记录，此为默认值； LEFT JOIN：显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以NULL来显示，此称为左联接； RIGHT JOIN：显示符合条件的数据行以及右边表中不符合条件的数据行，此时左边数据行会以NULL来显示，此称为右联接； FULL JOIN：显示符合条件的

8、数据行以及左边表和右边表中不符合条件的数据行，此时缺乏数据的数据行会以NULL来显示； 当将JOIN 关键词放于FROM子句中时，应有关键词ON与之相对应，以表明联接的条件。,Join联接,Select StudInfo.StudNo,StudInfo.StudName,StudInfo.ClassID,ClassInfo.ClassName From StudInfo Inner Join ClassInfo On StudInfo.ClassID=ClassInfo.ClassID Classinfo.classname, Courseinfo.courseid, Courseinfo.c

9、oursename, Studscoreinfo.studscore; FROM classinfo INNER JOIN StudInfo; INNER JOIN studscoreinfo INNER JOIN courseinfo ; ON Courseinfo.courseid = Studscoreinfo.courseid ; ON Studinfo.studno = Studscoreinfo.studno ; ON Classinfo.classid = Studinfo.classid And StudInfo.StudNo=99070470,Join联接,Select St

10、udInfo.StudNo,StudInfo.StudName,StudSex,StudScoreInfo.CourseID,StudScore From StudInfo left join StudScoreInfo On StudInfo.Studno=StudScoreinfo.StudNo Select StudInfo.StudNo,StudInfo.StudName,StudSex,StudScoreInfo.CourseID,StudScore From StudInfo Right join StudScoreInfo On StudInfo.Studno=StudScore

11、info.StudNo Select StudInfo.StudNo,StudInfo.StudName,StudSex,StudScoreInfo.CourseID,StudScore From StudInfo Full join StudScoreInfo On StudInfo.Studno=StudScoreinfo.StudNo,7.2.3 自联接,当一个表与其自已进行联接操作时，称为表的自联接。表的自身联接主要用于同一个表中数据的比较。 Select Si.StudNo,Si.StudName,Si.StudSex From StudInfo S,StudInfo SI Wher

12、e S.StudNo=99070478 And SI.StudNameS.StudName Select Si.StudNo,Si.StudName,Si.StudSex From StudInfo S,StudInfo SI Where S.StudNo=99070478 And S.StudSex=SI.StudSex,自联接,select * from studinfoback Select A.StudNo,A.StudName,A.StudSex,B.StudNo,B.StudName,B.Manager From StudInfoBack A,StudInfoBack B Wher

13、e A.StudName=B.Manager,7.3 嵌套查询,在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为嵌套查询或子查询，包含子查询的语句称为父查询或外部查询，子查询的嵌套层次最多可达到255层 嵌套查询在执行时由里向外处理，即先执行子查询再执行父查询，父查询要用到子查询的结果。 1. 返回一个值的子查询 当子查询的返回值只有一个时，可以使用比较运算符（=, , =, (Select Avg(StudScore) From StudScoreInfo Where StudNo=99070470),2.返回一组值的子查询,如果子查询的返回值不止一个，

14、而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY或ALL。其具体含义详见以下各例。 （1）使用ANY， 含义为任意一个 Select * From StudScoreInfo Where StudScore= Any(Select StudScore From StudScoreInfo Where StudNo=99070470) ( StudNo C (15) PRIMARY KEY NOT NULL; StudName C (20) NOT NULL,; StudSex C (2) DEFAULT 男 CHECK StudSex=男 OR StudSex=

15、女 ERROR StudSEX必须是男或女 ,; StudBirthDay D CHECK（StudBirthDay=1970-01-01,; StudScore N (5,1) ,; Level L.; StudDesc M,; Photo G ) 为续行号，字段之间用逗号隔开,7.5.2 修改数据表,SQL语言使用ALTER TABLE命令修改基本表的结构，增加、删除和修改字段，还可以增加、删除和修改如主索引、侯选索引、有效性规则等结构 主要有两种格式 语法一： ALTER TABLE ADD /ALTERCOLUMN ，小数位 NULL/NOT NULL CHECK ERROR DEFA

16、ULT PRIMARY KEY/UNIQUE 功能：修改基本表的结构。 ADD用于增加新列。 ALTER用于修改某列。,修改表结构命令2,ALTER TABLE DROPCOLUMN SET DEFAULT DROP DEFAULT SET CHECK ERROR DROP CHECK ADD PRIMARY KEY DROP PRIMARY KEY ADD UNIQUE DROP UNIQUE TAG RENAME COLUMN TO 说明 DROP用于删除列、默认值、有效性规则、主键、候选索引。 SET 用于为已有的字段设置默认值和有效性规则。 ADD 用于添加主索引和候选索引。 RENA

17、ME 用于修改表中的一个字段名。,修改表示例,ALTER TABLE StudInfo1; ALTER COLUMN LastStudScore N(3) CHECK(LastStudScore =500) ERROR “入校总分必须大于500” ALTER TABLE StudInfo1； RENAME COLUMN LastStudScore TO 高考成绩,7.5.3 删除数据表,当某个数据表无用时，可将其删除。删除后，该表中的数据和在此表上所建的索引都被删除。 语法 DROP TABLE 功能 删除数据表 示例 DROP TABLE StudInfo1,7.6 综合应用示例,假设图书管

18、理数据库中有3个表，图书.dbf、读者.dbf和借阅.dbf。它们的结构分别如下： 图书（总编号C(6)，书名C（16），作者C（6），出版单位C（20），单价N（6，2） 读者（借书证号C（4），姓名C（6），性别C（2），地址C（20） 借阅（借书证号C（4），总编号C（6），借书日期D（8） 在上述图书管理数据库中，图书的主索引是总编号，读者的主索引是借书证号，借阅的主索引是借书证号和总编号。,题目内容,1.使用SQL语句创建图书管理数据库和数据表 2.使用数据库设计器建立图书管理数据表间关系和数据参照完整性 3.写出向图书表中添加一条记录的SQL语句，总编号为A001，书名为二级Visual FoxPro，作者为李明，出版单位是清华大学，单价是33。,综合应用示例,4写出查询姓名、书名、借书日期的的SQL语句 SELECT 读者.姓名，图书.书名，借阅.借书日期； FROM 图书管理!读者，图书管理!借阅，图书管理!图书； WHERE读者.借书证号=借阅.借书证号 .And. 图书.