visualfoxpro程序设计案例教程(蔡莉)vf按章.ppt

5.3 SQL的数据更新功能,数据更新是指对数据的操作功能，主要包括数据的插入、更新、删除三个方面的操作。5.3.1 数据插入SQL的数据插入语句有两种使用形式：一种是使用常量，一次插入一条记录；另一种是插入子查询的结果，一次插入多条记录。1、使用常量插入一条记录2、在表中插入子查询的结果,1、使用常量插入一条记录,语句格式：INSERT INTO(,)VALUES(常量1),常量2，)语句功能：将新记录插入指定表中。说 明：（1）新记录的值为的值。（2）如果INTO子句中有属性列选项，则没有出现在子句中的属性将取空值。（3）如果INTO子句中没有指明任何列名，则新插入的记录必须在每个列上均有值。,例如：将一新生记录(学号:20050110,姓名:张亮,年龄:20，所在系:数学)插入到学生表中。INSERT INTO 学生 VALUES(20050110,张亮,20,数学)；例如：在选课表中插入一条记录(学号:20030211,课程号:C10,成绩不详)。INSERT INTO 选课 VALUES(20030211,C10)；,2、在表中插入子查询的结果,语句格式：INSERT INTO(,)；例如：求每个系学生的平均年龄，并把结果存入数据表中。CREATE TABLE 系平均年龄(系名 CHAR(20),平均年龄 SMALLINT)；INSERT INTO 系平均年龄SELECT 所在系,AVG(ALL 年龄)FROM 学生GROUP BY 所在系；,5.3.2 数据更新,语句格式：UPDATE表名 SET=,=WHERE；语句功能：将表中符合条件的记录的某些列用表达式的值替代。说 明：(1)指定要更新数据的记录所在的表名及该表所在的数据库名。(2)SET=指定被更新的字段及该字段的新值。(3)WHERE条件表达式指明将要更新符合条件表达式的记录。并且一次可以更新多个字段;如果不使用WHERE子句，则更新全部记录。,例如：将学生表中全部学生的年龄加上2岁。UPDATE 学生 SET 年龄=年龄+2例如：将选课表中的VB课程的成绩乘以1.4。UPDATE 选课 SET 成绩成绩*1.4 WHERE 课程号=(SELECT 课程号 FROM 课程 WHERE 课程名=VB)；,5.3.3 数据删除,语句格式：DELETE FROM表名 WHERE 条件表达式1；说 明：(1)指定要删除数据的表名。(2)WHERE选项指明只对满足条件的记录删除。如果不使用WHERE子句，则删除该表中的全部记录。例如：删除数学系学生的选课记录。DELETE FROM 选课 WHERE 学号IN(SELECT 学号 FROM 学生 WHERE 所在系=数学)；,5.4 SQL的数据查询功能,5.4.1 SELECT语句介绍1、SELECT语句格式SELECTALL|DISTINCTFORM WHERE GROUP BY,HAVING ORDER BYASC|DESC,ASC|DESC语句功能：从指定的表中选择满足条件的记录。,SELECT-SQL命令的格式包括三个基本子句：SELECT子句、FROM子句、WHERE子句，还包括操作子句：ORDER子句、GROUP子句以及其他一些选项。说 明：（1）SELECT子句用来指定查询结果中的目标列，目标列组用一个*号来表示时，指所有的字段。（2）ALL选项：表示选出的记录中包括重复记录，这是缺省值；DISTINCT则表示选出的记录中不包括重复记录。（3）FROM子句用于指明查询的数据源。定义表别名的格式为：。,（4）WHERE子句通过条件表达式描述表中记录的筛选条件，将不满足条件的元组筛选掉。（5）GROUP BY子句对记录按分组列的值分组，分组可以使同组的元组集中在一起，常用于数据分组统计。（6）GROUP BY子句后可带上HAVING子句作为组筛选条件，组选择条件为带有函数的条件表达式，它决定着整个组记录的取舍条件。（7）ORDER BY子句指定查询结查中记录按排序列排序，默认升序。选项ASC表示升序，DESE表示降序。,2、SELECT 子句的操作符,SELECT语句中使用的操作符包括算术操作符、比较操作符、逻辑操作符、组合查询操作符。（1）算术操作符算术操作符在SQL语句中表达数学运算操作。SQL的算术操作符有：+、-、*、/四种。（2）比较操作符比较操作符用于测试两个数据的关系。SQL的比较操作符有：=、=、!（不大于）、!（不小于）九种。（3）逻辑操作符P94表5-3列出了SQL的主要逻辑操作符的语义。,（4）组合查询操作符组合查询操作符的使用格式为：UNION:并查询操作符。操作结果将和的结果合并，即取和的元组，并在结果中去掉重复行。MINUS:差查询操作符。操作结果为取在中且中没有的记录。INTERSECT:交查询操作符。操作结果为取和中共有的记录。SELECT查询命令的使用非常灵活，用它可以构造各种各样的查询。,5.4.2 简单查询,简单查询是指在查询过程中只涉及一个表的查询语句，可有简单的查询条件。是最基本的查询语句。例如：求计算机系学生的学号和姓名。SELECT 学号，姓名 FROM 学生WHERE 所在系=计算机；例如：求选修了课程的学生学号，结果去掉重复值。SELECT DISTINCT 学号 FROM 选课；,例如：求选修了课程C1的学生学号和成绩，结果按成绩降序排列，如果成绩相同则按学号升序排列。SELECT 学号,成绩 FROM 选课WHERE 课程号=C1ORDER BY 成绩 DESC，学号 ASC；例如：求物理系或数学系姓王的学生的信息。SELECT*FROM 学生WHERE 所在系 IN(物理,数学)AND 姓名 LINK 王%；,例如：求缺少成绩的学生的学号和课程号。SELECT 学号,课程号FROM 选课WHERE 成绩 IS NULL；例如：求选修课程C1且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。SELECT 学号,成绩*0.8FROM 选课WHERE 课程号=C1 AND 成绩 BETWEEN 80 AND 90;,5.4.3 连接查询,若查询涉及到两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询功能。1、等值连接和非等值连接连接查询中，用来连接两个表的条件称为连接条件或连接谓词。连接条件的一般格式为：.连接谓词中的列名称为连接字段；连接条件中，连接字段类型必须是可比的，但连接字段不一定是同名的。,当连接运算符为“=”时，该连接操作称为等值连接；否则，使用其他运算符的连接运算称非等值连接。当等值连接中的连接字段相同，并且在SELECT子句中去除了重复字段时，则该连接操作为自然连接。例如：查询每个学生的情况以及他所选修的课程。SELECT 学生.*，选课.*FROM 学生,选课 WHERE 学生.学号=选课.学号；,例如：查询选修C1课程且成绩为90分以上的学生学号、姓名及成绩。SELECT 学生.学号,姓名,成绩FROM 学生，选课WHERE 学生.学号=选课.学号 AND 课程号=C1 AND 成绩90；例如:查询学生的学号、姓名、选修的课程名及成绩。SELECT 学生.学号,姓名,课程名,成绩FROM 学生,课程,选课WHERE 学生.学号=选课.学号 AND 课程.课程号=选课.课程号；,2、自身连接,连接操作不只是在两个表之间进行，一个表内还可以进行自身连接操作。表自身的连接操作称为自身连接。例如：查询每一门课的间接先行课（即先行课的先行课）。SELECT A.课程号，A.课程名,B.先行课FROM 课程 A,课程 BWHERE A.先行课=B.课程号；,说 明：（1）同一查询语句中，当一个表有两个作用时，需要对表起别名，应用中使用表的别名。上例中的A和B分别是课程的别名，A和B分别作为独立表使用。（2）该题的连接条件是A表中的先行课与B表中的课程号等值连接，其结果集中“B.先行课”为“A.课程号”的间接先行课字段。由于A和B都是课程的别名，所以该例是自身连接操作。,5.4.4 嵌套查询,将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。1、使用IN操作符的嵌套查询当IN操作符后的数据集需要通过查询得到时，就需要使用IN嵌套查询。例如：查询选修了高等数学的学生学号和姓名。SELECT 学号，姓名FROM 学生WHERE 学号 IN(SELECT 学号 FROM 课程 WHERE 课程号=高等数学)；,2、使用比较符的嵌套查询,例如：查询选修了课程C1的成绩高于张亮的学生学号和成绩。SELECT 学号，成绩FROM 选课WHERE 课程号C1 AND 成绩(SELECT 成绩 FROM 选课 WHERE 课程号=C1 AND 学号(SELECT 学号 FROM 学生 WHERE 姓名张亮)；,3、使用ANY或ALL操作符的嵌套查询,使用ANY或ALL操作符时必须与比较符配合使用，其格式为：ANY|ALL 例如：查询其他系中比数学系某一学生年龄小的学生（即查询年龄小于数学系最大者的学生）。SELECT*FROM 学生WHERE 年龄 数学；,例如：查询其他系中比数学系学生年龄都小的学生。SELECT*FROM 学生WHERE 年龄 数学；,4、使用EXISTS操作符的嵌套查询,EXISTS代表存在量词。EXISTS操作符后子查询的结果集中如果不为空，则产生逻辑真值“TRUE”，否则产生假值“FALSE”。例如：查询选修了C2课程的学生姓名。SELECT 姓名FROM 学生WHERE EXISTS(SELECT*FROM 选课 WHERE 学生.学号=学号 AND 课程号=C2)；,例如：查询没有选修C2课程的学生姓名。SELECT 姓名FROM 学生WHERE NOT EXISTS(SELECT*FROM 选课 WHERE 学生.学号=学号 AND 课程号=C2)；,例如：查询选修了全部课程的学生的姓名。SELECT 姓名FROM 学生WHERE NOT EXISTS(SELECT*FROM 课程 WHERE NOT EXISTS(SELECT*FROM 选课 WHERE 学生.学号=学号 AND 课程号=课程号)；,例如：查询至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。SELECT 学号，姓名FROM 学生WHERE NOT EXISTS(SELECT*FROM 选课 选课1 WHERE 选课1.学号=S2 AND NOT EXISTS(SELECT*FROM 选课 选课2 WHERE 学生.学号=选课2.学号AND 选课2.课程号=选课1.课程号)；,5.4.5 组合查询,将SELECT语句的查询结果集再进行集合运算就构成了SQL的组合查询。SQL的组合查询操作符有UNION(并操作)、INTERSECT(交操作)、MINUS(差操作)3种。例如：查询选修了C1课程或选修了C2课程的学生学号。SELECT 学号 FROM 选课 WHERE 课程号=C1 UNION SELECT 学号 FROM 选课 WHERE 课程号=C2；,例如：查询选修C1课程，并且也选修C2课程的学生学号。SELECT 学号 FROM 选课 WHERE 课程号=C1 INTERSECT SELECT 学号 FROM 选课 WHERE 课程号=C2；,例如：查询选修了C1课程但没有选修C2课程的学生学号。SELECT 学号 FROM 选课 WHERE 课程号=C1 MINUS SELECT 学号 FROM 选课 WHERE 课程号=C2；,5.4.6 使用分组和SQL函数查询,SQL函数是综合信息的统计函数，包括计数、求最大值、最小值、平均值、和等。SQL函数可作为列标识符出现在SELECT子句的目标列或HAVING子句的条件中。在SQL查询语句中，如果有GROUP BY分组子句，则语句中的函数为分组统计函数；如果没有GROUP BY分组子句，则语句中的函数为全部结果集的统计函数。,例如：求学生的总人数。SELECT COUNT(*)FROM 学生；例如：求选修了课程的学生人数。SELECT COUNT(DISTINCT 学号)FROM 选课；,例如：求课程和选修该课程的人数。SELECT 课程号，COUNT(学号)FROM 选课 GROUP BY 课程号；例如：求选修课程超过3门课的学生学号。SELECT 学号 FROM 选课 GROUP BY 学号 HAVING COUNT(*)3；,