第七章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 TABL

2、EDBF 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(宽度,小数点位数)CHECKERRORDEFAULTPRIMARY

3、KEYUNIQUEREFERENCESTAG,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”数据库中创建一个“选课”表，包含学号、课程号、成绩3个字段，为“成绩”字段建立一个取值范围，并在超出该取值范围是，

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

5、家庭住址”字段，并将其“姓名”字段的宽度改为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-1创建的“通讯录”表中，删除“家庭住址”字段，并将其“手机号码”字段更名为“移动电话”。,7.2.2 修改表的结构,5定义或修改数据完整性（

6、1）在增加字段的时候定义数据完整性命令：ALTER TABLE ADD COLUMN;NOT NULL|NULLPRIMARY KEY;DEFAULT 表达式 CHECK 逻辑表达式;ERROR 字符串表达式功能：在表中增加新的字段，并且定义新字段的完整性规则。【例7.6】为例7-1创建的“学生信息表”增加一个整数类型的年龄字段。,7.2.2 修改表的结构,5定义或修改数据完整性（2）在修改字段的时候定义数据完整性命令：ALTER TABLE ALTER COLUMN;NOT NULL|NULLPRIMARY KEY;SET DEFAULT 表达式;SET CHECK 逻辑表达式;ERROR

7、字符串表达式功能：在表中修改字段的数据完整性规则。,【例7.7】设置“学生信息表”中“少数民族否”字段的默认值为.F.。,7.2.3 删除数据表,命令：DROP TABLE 表名功能：直接从磁盘上删除指定的表文件。如果删除的是数据库表，则需要打开相应的数据库，然后使用DROP TABLE命令删除数据库表。【例7.8】从磁盘上删除表“选课.dbf”。OPEN DATABASE StudentDROP TABLE 选课,7.2.4 定义/删除视图,1定义视图命令：CREATE SQL VIEW(字段名1,字段名2)AS SELECT 功能：根据SELECT查询语句查询的结果，定义一个视图。,7.2

8、.4 定义/删除视图,2删除视图命令：DROP VIEW 视图名功能：删除数据库中指定的视图。,7.3 SQL的数据查询,数据查询是对数据库中的数据按指定条件和顺序进行检索输出。数据查询是数据库的核心操作。虽然SQL语言的数据查询只有一条SELECT语句，但是该语句却是用途最广泛的一条语句，具有灵活的使用方法和丰富的功能。,7.3.1 SELECT语句的格式,命令：SELECT ALL|DISTINCT;TOP PERCENT.;AS,AS;FROM,;INNER|LEFT|RIGHT|FULL JOIN;ON;INTO TABLE|TO FILE|TO PRINTER|TO SCREEN;W

9、HERE AND;AND|ORAND|OR;GROUP BY,HAVING;ORDER BY ASC|DESC,ASC|DESC,7.3.2 投影查询,投影查询是指从表中查询全部列或部分列。1查询部分字段如果用户只需要查询表的部分字段，可以在SELECT之后列出需要查询的字段名，字段名之间以英文逗号“,”分隔。【例7.12】从数据库表“学生信息表.dbf”查询学生的学号、姓名和入学成绩。,7.3.2 投影查询,2查询全部字段如果用户需要查询表的全部字段，可在SELECT之后列出表中所有字段，也可在SELECT之后直接用星号“*”来表示表中所有字段，而不必逐一列出。【例7.13】查询“课程表.d

10、bf”的全部数据。,7.3.2 投影查询,3取消重复记录在SELECT语句中，可以使用DISTINCT来取消查询结果中重复的记录。【例7.14】查询学生信息表中有哪些籍贯的学生（不显示重复的籍贯）。SELECT DISTINCT 籍贯 FROM 学生信息表,7.3.2 投影查询,4查询经过计算的表达式在SELECT语句中，查询的列，可以是字段，也可以是计算表达式。【例7.15】从成绩表中查询学生学号、姓名和相应的三科总成绩。SELECT 学号,姓名,高数+英语+计算机 AS 总成绩 FROM 成绩表说明：AS用来修改查询结果中指定列的列名。AS可以省略。,7.3.3 条件查询,若要在数据表中找

11、出满足某些条件的行时，则需使用WHERE子句来指定查询条件。,WHERE子句中的条件运算符,7.3.3 条件查询,1比较大小【例7.16】从学生信息表中查询少数民族学生信息。【例7.17】从学生信息表中查询籍贯为重庆的学生学号、姓名、籍贯和入学成绩。【例7.18】从学生信息表中查询入学成绩高于600的学生的学号、姓名、籍贯、入学成绩和简历。,7.3.3 条件查询,2多重条件查询当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND和OR将其连接成复合逻辑表达式，AND的运算优先级高于OR。用户可使用括号改变优先级。【例7.19】从学生信息表中查询入学成绩在560分到600分之

12、间的学生信息。【例7.20】从学生信息表中查询北京籍和上海籍的学生信息。,7.3.3 条件查询,3确定范围确定范围的子句是：BETWEEN 下界表达式 AND 上界表达式其含义是“在下界表达式和上界表达式之间，且包含上界表达式的值和下界表达式的值”。确定不在某个范围的子句是：NOT BETWEEN 下界表达式 AND 上界表达式其含义是“不在下界表达式和上界表达式之间的值”。,7.3.3 条件查询,4确定集合利用IN操作可以查询字段值属于指定集合的记录，利用NOT IN操作可以查询字段值不属于指定集合的记录。【例7.23】从学生信息表中查询学号为001，003，005的学生信息。SELECT*

13、FROM 学生信息表;WHERE 学号 IN(001,003,005)等价于下列SELECT语句：SELECT*FROM 学生信息表;WHERE 学号=001 OR 学号=002 OR 学号=003,7.3.3 条件查询,5部分匹配查询当用户不知道完全精确的查询条件的时候，可以使用LIKE或NOT LIKE进行字符串匹配查询（也称模糊查询）。LIKE定义的一般格式为：LIKE 说明：字段类型必须为字符型。字符串常量的字符可以包含如下2个特殊符号：“%”表示任意长度的字符串。“_”表示任意一个字符。注意：在Visual FoxPro中，一个汉字用一个字符“_”表示。,7.3.3 条件查询,6涉及

14、空值查询在SELECT语句中，使用IS NULL和IS NOT NULL来查询某个字段的值是否为空值。这里，IS不能用用等号“=”代替。【例7.26】从成绩表中查询成绩不为空的记录。SELECT*FROM 选课表 WHERE 成绩 IS NULL,7.3.4 统计查询,在实际应用中，往往不仅要求将表中的记录查询出来，还需要在原有数据的基础上，通过计算来输出统计结果。SQL提供了许多统计函数，增强了检索功能，一些常用函数如表7.5所示。在这些函数中，可以使用DISTINCT或ALL。如果指定了DISTINCT，在计算时取消指定列中的重复值；如果不指定DISTINCT或ALL，则取默认值ALL，不

15、取消重复值。,7.3.4 统计查询,SQL中的统计函数,7.3.4 统计查询,【例7.27】计算学生信息表中所有学生入学成绩的最高分、最低分和平均分。【例7.28】从课程表中，统计课程的门数。SELECT COUNT(*)AS 课程门数 FROM 课程表注意：COUNT(*)用来统计记录的个数，不消除重复行，不允许使用DISTINCT。【例7.29】统计选课表中已选课程的门数。SELECT COUNT(DISTINCT 课程号)AS 选课门数 FROM 选课表思考：这里的DISTINCT为什么不能省略？,7.3.5 分组查询,1分组查询通过GROUP BY子句进行分组查询【例7.30】从学生信

16、息表中分别统计少数民族学生和非少数民族学生的人数SELECT 少数民族否,COUNT(少数民族否);FROM 学生信息表;GROUP BY 少数民族否,7.3.5 分组查询,2限定分组查询如果查询要求分组满足某些条件，则需要使用HAVING子句来限定分组。HAVING子句总是在GROUP BY子句之后，不可以单独使用。【例7.31】从选课表中统计成绩平均分小于80分的课程号。SELECT 课程号,AVG(成绩)FROM 选课表;GROUP BY 课程号 HAVING AVG(成绩)70,7.3.6 查询的排序,1单列排序使用ORDER BY子句可对查询结果按一个查询列进行排序。【例7.32】从

17、学生信息表中查询入学成绩大于570的学生学号、姓名、籍贯和入学成绩，将查询结果按成绩降序排列。SELECT 学号,姓名,籍贯,入学成绩;FROM 学生信息表;WHERE 入学成绩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子句查询满足条件的前面部分记

18、录。【例7.33】从学生信息表中查询年龄最小的3名学生的信息。SELECT*TOP 3 FROM 学生信息表;ORDER BY 出生日期 DESC【例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”的课程的成绩情况，要求

19、显示学号、姓名、课程号和成绩。分析：查询中涉及学生信息表和选课表，两个表之间通过公共字段“学号”建立连接。,7.3.8 自连接查询,SQL还支持将同一个表与其自身进行连接，这种连接查询称为自连接查询。在自连接查询中，必须将查询涉及的表名定义为别名。在查询涉及的字段前面，用别名加以限定。定义表的别名的语法是：.。【例7.37】查询选修计算机的学生中成绩大于学号为“007”的学生该门课成绩的那些学生的学号及成绩。,7.3.9 修改查询去向,将查询结果存放到永久表中使用子句INTO DBF|TABLE 将查询结果存放在临时文件中使用子句INTO CURSOR 将查询结果存放到文本文件中使用子句TO

20、FILE ADDITIVE将查询结果存放到数组中可以使用子句INTO ARRAY 将查询结果直接输出到打印机使用子句TO PRINTER PROMPT,7.3.10 嵌套查询,在SELECT语句中，一个SELECT-FROM-WHERE语句称为一个查询块。一个查询块（子查询）嵌套在另一个查询块（父查询）中的WHERE条件或HAVING子句的查询称为嵌套查询。系统在处理嵌套查询时，首先查询出子查询的结果，然后将子查询的结果用于父查询的查询条件中。,7.3.10 嵌套查询,1带比较运算符号的子查询在嵌套查询中，当子查询的结果是一个单值（只有一个记录，一个字段值），可以用、=、等比较运算符来生成父查

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

22、0 嵌套查询,3带有ANY、ALL或SOME量词的子查询,SQL中的量词运算,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子句省略时，则删除表中的所有记录。,