第七章SQL的应用.ppt

《第七章SQL的应用.ppt》由会员分享，可在线阅读，更多相关《第七章SQL的应用.ppt（51页珍藏版）》请在三一文库上搜索。

1、,第七章 SQL的应用,本章要点,SQL概述 SQL的数据定义功能 SQL的数据查询功能 SQL的数据操纵功能,7.1 SQL概述,SQL Structured Query Language，结构化查询语言。 集数据的定义、查询、操纵和控制功能于一体的语言。 SQL语句最主要的功能就是查询功能。 SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。 功能丰富，使用灵活，语言简捷易学。,7.1 SQL概述,SQL 的语言动词,7.2 SQL的数据定义,SQL的数据定义功能包括定义表、定义视图和定义索引。,7.2.1 创建数据表,1创建表的基本的命令 命令：CREATE

2、 TABLEDBF FREE ( (宽度 ,小数位数), (宽度 ,小数位数 )NOT NULLNULL 功能：创建一个以为表的名字、以指定的字段属性定义的数据表。,7.2.1 创建数据表,1创建表的基本的命令 【例7.1】 利用SQL命名创建一个名为“通讯录”的自由表，含有姓名、手机号码、Emai地址、生日4个字段。 定义此表结构的SQL命令如下： CREATE TABLE 通讯录 FREE(姓名 C（8）,手机号码 C(11),Email地址 C(30),生日 D),7.2.1 创建数据表,2创建表的同时定义完整性规则 格式：CREATE TABLEDBF ( (宽度 ,小数点位数) CH

3、ECKERROR DEFAULT PRIMARY KEYUNIQUE REFERENCESTAG , ,PRIMARY KEY TAG ,UNIQUE TAG ,FOREIGN KEY TAG REFERENCES TAG ,CHECK ERROR ) FROM ARRAY,7.2.1 创建数据表,2创建表的同时定义完整性规则 【例7.2】 创建一个名为Student的数据库，再在其中创建一个“学生信息表”，包含学号、姓名、出生日期、少数民族否、籍贯、入学成绩、简历、照片8个字段，并以“学号”字段为关键字创建一个主索引。 【例7.3】 在“Student”数据库中创建一个“选课”表，包含学号、

4、课程号、成绩3个字段，为“成绩”字段建立一个取值范围，并在超出该取值范围是，显示提示出错信息“成绩值的范围应为0100！”，并为其设定一个默认值，再以上面已经创建的“学生信息表”为父表通过共有的“学号”字段为关键字建立两表之间的永久关系。,7.2.2 修改表的结构,1增加字段 格式：ALTER TABLE ; ADD COLUMN (宽度 ,小数点位数) ; ADD COLUMN (宽度 ,小数点位数) 功能：在表中增加新字段，并定义字段的属性。,7.2.2 修改表的结构,2修改字段 格式：ALTER TABLE ; ALTER COLUMN (宽度 ,小数点位数) ; ALTER COLUM

5、N (宽度 ,小数点位数) 功能：修改表中字段的属性。 【例7.4】 为例7-1创建的“通讯录”表添加一个宽度为16的“家庭住址”字段，并将其“姓名”字段的宽度改为10。 ALTER TABLE 通讯录 ADD 家庭住址 C(16) ALTER TABLE 通讯录 ALTER 姓名 C(10),7.2.2 修改表的结构,3删除字段 格式：ALTER TABLE ; DROP COLUMN ; DROP COLUMN 功能：删除表中指定的字段,7.2.2 修改表的结构,4修改字段名 格式：ALTER TABLE RENAME; COLUMN TO 功能：将表中的名字修改为。 【例7.5】 在例7

6、-1创建的“通讯录”表中，删除“家庭住址”字段，并将其“手机号码”字段更名为“移动电话”。,7.2.2 修改表的结构,5定义或修改数据完整性 （1）在增加字段的时候定义数据完整性 命令：ALTER TABLE ADD COLUMN ; NOT NULL|NULLPRIMARY KEY ; DEFAULT 表达式 CHECK 逻辑表达式 ; ERROR 字符串表达式 功能：在表中增加新的字段，并且定义新字段的完整性规则。 【例7.6】 为例7-1创建的“学生信息表”增加一个整数类型的年龄字段。,7.2.2 修改表的结构,5定义或修改数据完整性 （2）在修改字段的时候定义数据完整性 命令：ALTE

7、R TABLE ALTER COLUMN ; NOT NULL|NULLPRIMARY KEY ; SET DEFAULT 表达式 ; SET CHECK 逻辑表达式 ; ERROR 字符串表达式 功能：在表中修改字段的数据完整性规则。,【例7.7】 设置“学生信息表”中“少数民族否”字段的默认值为.F.。,7.2.3 删除数据表,命令：DROP TABLE 表名 功能：直接从磁盘上删除指定的表文件。如果删除的是数据库表，则需要打开相应的数据库，然后使用DROP TABLE命令删除数据库表。 【例7.8】 从磁盘上删除表“选课.dbf”。 OPEN DATABASE Student DROP

8、TABLE 选课,7.2.4 定义/删除视图,1定义视图 命令：CREATE SQL VIEW (字段名1,字段名2) AS SELECT 功能：根据SELECT查询语句查询的结果，定义一个视图。,7.2.4 定义/删除视图,2删除视图 命令：DROP VIEW 视图名 功能：删除数据库中指定的视图。,7.3 SQL的数据查询,数据查询是对数据库中的数据按指定条件和顺序进行检索输出。数据查询是数据库的核心操作。 虽然SQL语言的数据查询只有一条SELECT语句，但是该语句却是用途最广泛的一条语句，具有灵活的使用方法和丰富的功能。,7.3.1 SELECT语句的格式,命令： SELECT ALL

9、|DISTINCT ; TOP PERCENT. ; AS ,AS ; FROM , ; INNER|LEFT|RIGHT|FULL JOIN ; ON ; INTO TABLE|TO FILE |TO PRINTER|TO SCREEN ; WHERE AND ; AND|ORAND|OR ; GROUP BY ,HAVING ; ORDER BY ASC|DESC,ASC|DESC,7.3.2 投影查询,投影查询是指从表中查询全部列或部分列。 1查询部分字段 如果用户只需要查询表的部分字段，可以在SELECT之后列出需要查询的字段名，字段名之间以英文逗号“,”分隔。 【例7.12】 从数据

10、库表“学生信息表.dbf”查询学生的学号、姓名和入学成绩。,7.3.2 投影查询,2查询全部字段 如果用户需要查询表的全部字段，可在SELECT之后列出表中所有字段，也可在SELECT之后直接用星号“*”来表示表中所有字段，而不必逐一列出。 【例7.13】 查询“课程表.dbf”的全部数据。,7.3.2 投影查询,3取消重复记录 在SELECT语句中，可以使用DISTINCT来取消查询结果中重复的记录。 【例7.14】 查询学生信息表中有哪些籍贯的学生（不显示重复的籍贯）。 SELECT DISTINCT 籍贯 FROM 学生信息表,7.3.2 投影查询,4查询经过计算的表达式 在SELECT

11、语句中，查询的列，可以是字段，也可以是计算表达式。 【例7.15】 从成绩表中查询学生学号、姓名和相应的三科总成绩。 SELECT 学号,姓名,高数+英语+计算机 AS 总成绩 FROM 成绩表 说明：AS用来修改查询结果中指定列的列名。AS可以省略。,7.3.3 条件查询,若要在数据表中找出满足某些条件的行时，则需使用WHERE子句来指定查询条件。,WHERE子句中的条件运算符,7.3.3 条件查询,1比较大小 【例7.16】 从学生信息表中查询少数民族学生信息。 【例7.17】 从学生信息表中查询籍贯为重庆的学生学号、姓名、籍贯和入学成绩。 【例7.18】 从学生信息表中查询入学成绩高于6

12、00的学生的学号、姓名、籍贯、入学成绩和简历。,7.3.3 条件查询,2多重条件查询 当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND和OR将其连接成复合逻辑表达式，AND的运算优先级高于OR。用户可使用括号改变优先级。 【例7.19】 从学生信息表中查询入学成绩在560分到600分之间的学生信息。 【例7.20】 从学生信息表中查询北京籍和上海籍的学生信息。,7.3.3 条件查询,3确定范围 确定范围的子句是： BETWEEN 下界表达式 AND 上界表达式 其含义是“在下界表达式和上界表达式之间，且包含上界表达式的值和下界表达式的值”。 确定不在某个范围的子句是：

13、NOT BETWEEN 下界表达式 AND 上界表达式 其含义是“不在下界表达式和上界表达式之间的值”。,7.3.3 条件查询,4确定集合 利用IN操作可以查询字段值属于指定集合的记录，利用NOT IN操作可以查询字段值不属于指定集合的记录。 【例7.23】 从学生信息表中查询学号为001，003，005的学生信息。 SELECT * FROM 学生信息表; WHERE 学号 IN(001,003,005) 等价于下列SELECT语句： SELECT * FROM 学生信息表; WHERE 学号=001 OR 学号=002 OR 学号=003,7.3.3 条件查询,5部分匹配查询 当用户不知道

14、完全精确的查询条件的时候，可以使用LIKE或NOT LIKE进行字符串匹配查询（也称模糊查询）。 LIKE定义的一般格式为： LIKE 说明： 字段类型必须为字符型。字符串常量的字符可以包含如下2个特殊符号： “%”表示任意长度的字符串。 “_”表示任意一个字符。注意：在Visual FoxPro中，一个汉字用一个字符“_”表示。,7.3.3 条件查询,6涉及空值查询 在SELECT语句中，使用IS NULL和IS NOT NULL来查询某个字段的值是否为空值。这里，IS不能用用等号“=”代替。 【例7.26】 从成绩表中查询成绩不为空的记录。 SELECT * FROM 选课表 WHERE

15、成绩 IS NULL,7.3.4 统计查询,在实际应用中，往往不仅要求将表中的记录查询出来，还需要在原有数据的基础上，通过计算来输出统计结果。SQL提供了许多统计函数，增强了检索功能，一些常用函数如表7.5所示。 在这些函数中，可以使用DISTINCT或ALL。如果指定了DISTINCT，在计算时取消指定列中的重复值；如果不指定DISTINCT或ALL，则取默认值ALL，不取消重复值。,7.3.4 统计查询,SQL中的统计函数,7.3.4 统计查询,【例7.27】 计算学生信息表中所有学生入学成绩的最高分、最低分和平均分。 【例7.28】 从课程表中，统计课程的门数。 SELECT COUNT

16、(*) AS 课程门数 FROM 课程表 注意：COUNT(*)用来统计记录的个数，不消除重复行，不允许使用DISTINCT。 【例7.29】 统计选课表中已选课程的门数。 SELECT COUNT(DISTINCT 课程号) AS 选课门数 FROM 选课表 思考：这里的DISTINCT为什么不能省略？,7.3.5 分组查询,1分组查询 通过GROUP BY子句进行分组查询 【例7.30】 从学生信息表中分别统计少数民族学生和非少数民族学生的人数 SELECT 少数民族否,COUNT(少数民族否); FROM 学生信息表; GROUP BY 少数民族否,7.3.5 分组查询,2限定分组查询

17、如果查询要求分组满足某些条件，则需要使用HAVING子句来限定分组。HAVING子句总是在GROUP BY子句之后，不可以单独使用。 【例7.31】 从选课表中统计成绩平均分小于80分的课程号。 SELECT 课程号,AVG(成绩) FROM 选课表; GROUP BY 课程号 HAVING AVG(成绩)70,7.3.6 查询的排序,1单列排序 使用ORDER BY子句可对查询结果按一个查询列进行排序。 【例7.32】 从学生信息表中查询入学成绩大于570的学生学号、姓名、籍贯和入学成绩，将查询结果按成绩降序排列。 SELECT 学号,姓名,籍贯,入学成绩; FROM 学生信息表; WHER

18、E 入学成绩570; ORDER BY 入学成绩 DESC,7.3.6 查询的排序,2多列排序 多列排序的格式如下： ORDER BY 列名1 ASC|DESC，列名2 ASC|DESC 【例7.32】 对学生信息表，按籍贯顺利列出学生的学号、姓名、出生日期、籍贯及入学成绩，籍贯相同的再先按出生日期后按成绩由高到低排序。,7.3.6 查询的排序,3查询前面部分记录 在排序的基础上，可以使用TOP N PERCENT子句查询满足条件的前面部分记录。 【例7.33】从学生信息表中查询年龄最小的3名学生的信息。 SELECT * TOP 3 FROM 学生信息表; ORDER BY 出生日期 DES

19、C 【例7.34】从学生信息表中查询入学成绩在后10%的学生信息。 SELECT * TOP 10 PERCENT FROM 学生信息表; ORDER BY 入学成绩,7.3.7 内连接查询,SELECT 查询列FROM 表1，表2 WHERE 连接条件 AND 查询条件 SELECT 查询列FROM 表1 INNER JOIN 表2 ON 连接条件 WHERE 查询条件 【例7.35】 从学生信息表和选课表中，查询各学生所选课程号为“1001”的课程的成绩情况，要求显示学号、姓名、课程号和成绩。 分析：查询中涉及学生信息表和选课表，两个表之间通过公共字段“学号”建立连接。,7.3.8 自连接

20、查询,SQL还支持将同一个表与其自身进行连接，这种连接查询称为自连接查询。在自连接查询中，必须将查询涉及的表名定义为别名。在查询涉及的字段前面，用别名加以限定。 定义表的别名的语法是：.。 【例7.37】 查询选修计算机的学生中成绩大于学号为“007”的学生该门课成绩的那些学生的学号及成绩。,7.3.9 修改查询去向,将查询结果存放到永久表中 使用子句INTO DBF | TABLE 将查询结果存放在临时文件中 使用子句INTO CURSOR 将查询结果存放到文本文件中 使用子句TO FILE ADDITIVE 将查询结果存放到数组中 可以使用子句INTO ARRAY 将查询结果直接输出到打印

21、机 使用子句TO PRINTER PROMPT,7.3.10 嵌套查询,在SELECT语句中，一个SELECT-FROM-WHERE语句称为一个查询块。一个查询块（子查询）嵌套在另一个查询块（父查询）中的WHERE条件或HAVING子句的查询称为嵌套查询。 系统在处理嵌套查询时，首先查询出子查询的结果，然后将子查询的结果用于父查询的查询条件中。,7.3.10 嵌套查询,1带比较运算符号的子查询 在嵌套查询中，当子查询的结果是一个单值（只有一个记录，一个字段值），可以用、=、等比较运算符来生成父查询的查询条件。 【例7.41】 查询入学成绩大于学号为“007”的学生的学号，姓名和入学成绩。 SE

22、LECT 学号,姓名,入学成绩 FROM 学生信息表 WHERE 入学成绩; (SELECT 入学成绩 FROM 学生信息表 WHERE 学号=“006“) 说明：因为学生信息表中，学号没有重复，因此子查询的结果是一个单值，可以使用比较运算符号。,7.3.10 嵌套查询,2IN谓词子查询 在嵌套查询中，子查询的结果一般是一个集合，因此在外层查询中，可以用IN谓词来作为查询条件。IN谓词的使用格式是： 父查询 WHERE 字段 IN (子查询) 【例7.42】 查询选修了课程号为“1010“的所有学生的学号。,7.3.10 嵌套查询,3带有ANY、ALL或SOME量词的子查询,SQL中的量词运算

23、,7.3.11 集合查询,通过UNION进行集合查询。 参加UNION操作的各个查询的结果的字段数目必须相同，对应的数据类型也必须相同。 【例7.45】 查询入学成绩大于90或小于70的学生信息。,7.4 SQL的数据操纵功能,SQL的数据操纵也称为数据更新，主要包括： 插入数据 修改数据 删除数据,7.4.1 插入记录,命令：INSERT INTO ; (,) ; VALUES(,) 功能：将新记录插入到指定的表中，分别用值1、值2等为字段名1、字段名2等赋值。 说明：指定要插入新记录的表；是可选项，指定待添加数据的列；VALUES子句指定待插入记录的各个字段的值。,7.4.2 更新记录,命令：UPDATE ; SET = ; ,= WHERE 功能：对表中的一个或多个记录的某些字段值进行修改。 说明：指定要修改的表；SET子句给出要修改的字段及其修改以后的值；WHERE子句指定需要修改的记录应当满足的条件，WHERE子句省略时，则修改表中所有记录。,7.4.3 删除记录,命令：DELETE FROM WHERE 功能：逻辑删除表中的一个或多个记录。 说明： 指定要删除数据的表。WHERE子句指定待删除的记录应当满足的条件。WHERE子句省略时，则删除表中的所有记录。,