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|DISTINCT FORM 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的比较操作符有：=、=、!（不大于）、! UNION:并查询操作符。操作结果将和的结果合并，即取和的元组， 并在结果中去掉重复行。 MINUS:差查询操作符。操作结果为取在中 且中没有的记录。 INTERSECT:交查询操作符。操作结果为取 和中共有的记录。 SELECT查询命令的使用非常灵活，用它可以构造 各种各样的查询。 5.4.2 简单查询 简单查询是指在查询过程中只涉及一个表的查询 语句，可有简单的查询条件。是最基本的查询语句 。 例如：求计算机系学生的学号和姓名。 SELECT 学号，姓名 FROM 学生 WHERE 所在系= 计算机； 例如：求选修了课程的学生学号，结果去掉重复 值。 SELECT DISTINCT 学号 FROM 选课； 例如：求选修了课程C1的学生学号和成绩，结果按 成绩降序排列，如果成绩相同则按学号升序排列。 SELECT 学号,成绩 FROM 选课 WHERE 课程号= C1 ORDER BY 成绩 DESC，学号 ASC； 例如：求物理系或数学系姓王的学生的信息。 SELECT * FROM 学生 WHERE 所在系 IN (物理, 数学) AND 姓名 LINK 王%； 例如：求缺少成绩的学生的学号和课程号。 SELECT 学号,课程号 FROM 选课 WHERE 成绩 IS NULL； 例如：求选修课程C1且成绩在80-90之间的学生学 号和成绩，并将成绩乘以系数0.8输出。 SELECT 学号,成绩*0.8 FROM 选课 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,课程 B WHERE 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分组子句