第5章 关系数据库标准查询语言SQL

1、目 录 上一页 下一页 退 出 2021-7-91 本本 章章 要要 点点 1SQL 概述概述 2SQL 语言的基本概念语言的基本概念 3SQL 表定义功能表定义功能 4SQL 查询功能查询功能 5SQL记录操作功能记录操作功能 思考题 目 录 上一页 下一页 退 出 2021-7-92 SQL语言常用的几条命令，见表8-1 表1 SQL命令动词分类表 SQL功能命令动词 数据定义CREATE，DROP，ALTER 数据操纵INSERT，UPDATE，DELETE 数据查询SELECT 数据控制GRANT，REVOKE 目 录 上一页 下一页 退 出 2021-7-93 数据定义命令用于定义数

2、据库和表结构。 前面介绍了通过表设计器建立表的方法， 在Visual FoxPro中也可以通过SQL 的CREATE TABLE命令建立表结构，指明表名，表中各 字段的名字、类型、精度、比例、是否允 许空值及参照完整性规则。 操作对象：表、索引和视图 三个命令：表的建立（CREATE）、表的删 除（DROP）、表的修改（ALTER） 1 定义（创建）表定义（创建）表 【格式格式】 CREATE TABLE|DBF 表名表名1FREE (字段名字段名1 字段类型字段类型 (字段宽度字段宽度 ,小数位数小数位数) NULL|NOT NULL CHECK ERROR DEFAULT PRIMARY

3、KEY|UNIQUE REFERENCES TAG NOCPTRANS ,字段名字段名2 ,PRIMARY KEY |,UNIQUE TAG ,FOREIGN KEY TAG REFERENCES TAG ,CHECK ERROR ) 【功能功能】定义（也称创建）一个表。定义（也称创建）一个表。 目 录 上一页 下一页 退 出 2021-7-95 【例例1 1】创建一个表创建一个表“学生档案学生档案”，结构如下：，结构如下： 字段名字段名 字段类型字段类型 字段宽度字段宽度 小数点小数点 学号学号 字符型字符型 1010 姓名姓名 字符型字符型 8 8 性别性别 字符型字符型 2 2 出生日期

4、出生日期 日期型日期型 8 8 专业专业 字符型字符型 2020 CREATE TABLE 学生档案学生档案(学号学号 C(10),姓名姓名 C(8),; 性别性别 C(2),出生日期出生日期 D,专业专业 C(20) LIST STRUCTURE MODI STRU 目 录 上一页 下一页 退 出 2021-7-96 注意： 命令中大多数子句使用时需要打开一 个数据库，即在数据库中建立表。如 果没有打开数据库，创建表时会产生 错误。 目 录 上一页 下一页 退 出 2021-7-97 例2建立研究生表，该表不属于任何数据库， 其结构见表8-2。 表2 研究生表 字段名字段类型字段长度小数位数

5、特殊要求 学号C6 姓名C8 性别C2 年龄N30 入学年月D允许空值 目 录 上一页 下一页 退 出 2021-7-98 定义该表的SQL命令为： CREATE TABLE 研究生 FREE(学号 C(6),; 姓名 C(8),性别 C(2),年龄 N(3),; 入学年月 D NULL) 目 录 上一页 下一页 退 出 2021-7-99 例3假设已经建立了XSK数据库,在XSK中建立 学生表,其结构见表3。 表3 学生表 字段名字段类型字段宽度小数位数特殊要求 学号C6主索引 姓名C8不能为空 性别C2 年龄N30 年龄大于10小 于45 是否党员L 入学年月D 默认值为1999 年9月1

6、日 备注M禁止转换 目 录 上一页 下一页 退 出 2021-7-910 OPEN DATABASE XSKOPEN DATABASE XSK CREATE TABLE CREATE TABLE 学生学生( (学号学号 C(6) PRIMARY KEY,;C(6) PRIMARY KEY,; 姓名姓名 C(8) NOT NULL,C(8) NOT NULL,性别性别 C(2),;C(2),; 年龄年龄 N(3) CHECK N(3) CHECK 年龄年龄10 AND 10 AND 年龄年龄45;=75; ORDER BY 成绩 DESC 说明:多个查询条件可以用AND、OR或NOT连接。 SE

7、LECT * TOP 30 PERCENT FROM 选修选修 ORDER BY 成绩成绩 目 录 上一页 下一页 退 出 2021-7-932 使用否定 例22列出非计算机应用专业学生的名单。 SELECT 学号,姓名 FROM 学生; WHERE 专业“计算机应用” 或SELECT 学号,姓名 FROM 学生; WHERE 专业!=“计算机应用” 或SELECT 学号,姓名 FROM 学生; WHERE NOT(专业=“计算机应用”) 目 录 上一页 下一页 退 出 2021-7-933 几个特殊的运算符 1.(NOT)BETWEENAND运算符 在查找中，如果要求某列的数值在某个区间 内

8、，可使用该运算符。 例23查找成绩在7080分之间的学生选课得 分情况。 SELECT * FROM 选修; WHERE 成绩 BETWEEN 70 AND 80 它等价于 SELECT * FROM 选修; WHERE 成绩=70 AND 成绩ANY; (SELECT 成绩 FROM 选修 WHERE 课程号=“C001”) 目 录 上一页 下一页 退 出 2021-7-940 (2)ALL谓词的用法 例29求选修C002号课的学生中,成绩比选 修C001号课的任何学生的成绩都要高的那 些学生的学号及成绩。 SELECT 学号,成绩 FROM 选修; WHERE 课程号=“C002” AND

9、 成绩ALL; (SELECT 成绩 FROM 选修 WHERE 课程号=“C001”) 目 录 上一页 下一页 退 出 2021-7-941 (3)IN谓词的用法 例30求多媒体技术专业所有选修课的设置情况。 SELECT * FROM 选修; WHERE 课程号 IN; (SELECT 课程号 FROM 课程; WHERE 课程名=“多媒体技术”) 说明:IN是属于的意思,等价于“=ANY”,即等于子 查询中任何一值。 目 录 上一页 下一页 退 出 2021-7-942 例31查询没有学习C001课程的学生姓名和性别及专业。 SELECT 姓名,性别,专业 FROM 学生; WHERE

10、学号 NOT IN; (SELECT 学号 FROM 选修 WHERE 课程号=“C001”) 例32查询学习课程号为C001课程的学生学号、姓名、性 别及专业。 SELECT 学号,姓名,性别,专业 FROM 学生; WHERE 学号 IN; (SELECT 学号 FROM 选修 WHERE 课程号=“C001”) 目 录 上一页 下一页 退 出 2021-7-943 1.等值连接 可以按对应列的共同值将一个表中行与另一个表中的行连 接起来。 例33列出选修C001号课的学生姓名及成绩。 SELECT 姓名,成绩 FROM 学生,选修; WHERE 学生.学号=选修.学号 AND 课号=“C

11、001” 或SELECT 姓名,成绩 FROM 学生 S,选修 C; WHERE S.学号=C.学号 AND 课号=“C001” 目 录 上一页 下一页 退 出 2021-7-944 例34求至少选修C001号课和C002号课学生的学号。 SELECT X.学号 FROM 选修 X,选修 Y; WHERE X.学号=Y.学号; AND X.课号=“C001” AND Y.课号=“C002” 说明:连接不仅可以建立在不同的表之间,也可以建立在 同一个表上,即将表与表自身连接,就像两个表一样。 目 录 上一页 下一页 退 出 2021-7-945 4 计算查询 SQL语言支持各种统计汇总函数，即库

12、函数。通过库函 数对满足条件的记录进行最大值、最小值、平均值、总 和等统计、计数运算。常用的库函数有如下5种： COUNT(* )计算元组的个数 COUNT( )对一列中的值计算个数 SUM( )计算数值列的总和 AVG( )计算数值列的平均值 MAX( )求字符、日期、数值列的最大值 MIN( )求字符、日期、数值列的最小值 这些库函数可以用在SELECT短语中对查询结果进行计算。 它们一般从一组值中计算出一个汇总信息，GROUP BY子 句用来定义或者将字段的值划分为多个组，它能控制和 影响查询的结果。 目 录 上一页 下一页 退 出 2021-7-946 例36求男学生的总人数和平均年龄

13、。 SELECT COUNT(*),AVG(YEAR(DATE()-YEAR(出生年月); FROM 学生; WHERE 性别=男 例37统计选修了课程的学生人数。 SELECT COUNT(DISTINCT 学号)FROM 选修 例38查询计算机应用专业的学生人数。 SELECT COUNT(学号) FROM 学生; WHERE 专业=“计算机应用” 说明: COUNT的特殊形式是COUNT(*),统计满足WHERE子句中逻辑表达式 的记录的行数。 目 录 上一页 下一页 退 出 2021-7-947 例39列出平均成绩、最高成绩、最低成绩和选课人数。 SELECT 课程号,AVG(成绩)

14、AS 平均成绩,; MAX(成绩) AS 最高分,MIN(成绩) AS 最低分,; COUNT(学号) AS 选课人数 FROM 选课; GROUP BY 课程号 例40统计每门课程的学生选修人数。 SELECT 课程号,COUNT(学号) FROM 选修 GROUP BY 课程号 5 分组查询 目 录 上一页 下一页 退 出 2021-7-948 例41求每名教师每门课程的学生选修人数,要求显示教 师号、课程号和学生人数。显示时，查询结果按人数升序 排列，教师号相同时再按课程号降序排列。 SELECT 教师号,课程.课程号,COUNT(学号); FROM 课程,选修; WHERE 课程.课程

15、号=选修.课程号; GROUP BY 教师号,课程.课程号; ORDER BY 3,教师号,课程.课程号 DESC 目 录 上一页 下一页 退 出 2021-7-949 例42列出最少选修3门课的学生姓名。 SELECT 姓名 FROM 学生; WHERE 学号 IN； （SELECT 学号 FROM 选修； GROUP BY 学号 HAVING COUNT(*)=3) 例43求出总分大于150的学生的学号、姓名及总成绩。 SELECT 选修.学号,姓名,SUM(成绩) AS 成绩; FROM 学生,选修; WHERE 学生.学号=选修.学号; GROUP BY 选修.学号; HAVING S

16、UM(成绩)150 目 录 上一页 下一页 退 出 2021-7-950 例44求出选修C001号课的成绩最高的学生的学号、姓 名和年龄。 SELECT B.学号,姓名,年龄; FROM 学生 A,选修 B; WHERE A.学号=B.学号 AND 课程号=“C001”; AND 成绩=(SELECT MAX(成绩); FROM 选修 WHERE 课号=“C001”) 目 录 上一页 下一页 退 出 2021-7-951 Visual FoxPro中SQL SELECT的几个特殊选项 介绍几个常用的Visual FoxPro SQL SELECT的特殊选项。 1显示部分结果 例如:只显示前几项

17、记录。 有时只需要满足条件的前几个记录，这时使用TOP nExpr PERCENT短语非常有用，其中nExpr是数字表达 式，当不使用PERCENT时，nExpr是1至32767间的整数， 说明显示前几个记录； 当使用PERCENT时，nExpr是0.01至99.99间的实数，说 明显示结果中前百分之几的记录。需要注意的是TOP短 语要与ORDER BY短语同时使用才有效。 目 录 上一页 下一页 退 出 2021-7-952 【例45】显示选修表中成绩最高的三位学生的信息。 SELECT * TOP 3 FROM 选修; ORDER BY 成绩 DESC 【例46】显示选修表中成绩最低的那3

18、0学生的信息。 SELECT * TOP 30 PERCENT FROM 选修; ORDER BY 成绩 目 录 上一页 下一页 退 出 2021-7-953 将查询结果存放到数组中 可以使用INTO ARRAY ArrayName短语将查询结果存放 到数组中，ArrayName可以是任意的数组变量名。 一般将存放查询结果的数组作为二维数组来使用，每行 一条记录，每列对应于查询结果的一列。 查询结果存放在数组中,可以非常方便地在程序中使用。 如下语句将查询到的学生信息存放在数组tmp中： SELECT * FROM 学生 INTO ARRAY tmp tmp(1,1)存放的是第一条记录的学号字

19、段值，tmp(1,3)存 放的是第一条记录的性别字段值等。 目 录 上一页 下一页 退 出 2021-7-954 将查询结果存放在临时文件中 使用短语INTO CURSOR CursorName可以将查询结果 存放到临时数据库文件中，其中CursorName是临时文 件名，该短语产生的临时文件是一个只读的dbf文件， 当查询结束后该临时文件是当前文件，可以像一般的 dbf文件一样使用，但仅是只读。当关闭文件时该文件 将自动删除。 如下语句将查询到的职工信息存放在临时dbf文件tmp 中； SELECT * FROM 学生 INTO CURSOR tmp 一般利用INTO CURSOR短语存放一

20、些临时结果，比如 一些复杂的汇总可能需要分阶段完成，需要根据几个 中间结果再汇总等，这时利用该短语存放中间结果就 非常合适，当使用完后这些临时文件会自动删除。 目 录 上一页 下一页 退 出 2021-7-955 将查询结果存放到永久表中 使用短语INTO DBF|TABLE TableName可 以将查询结果存放到永久表中(dbf文件)。 比如将查询结果存放在表high中,可以使用 如下语句： SELECT * TOP 3 FROM 学生; INTO TABLE high ORDER BY 年龄 DESC 目 录 上一页 下一页 退 出 2021-7-956 将查询结果存放到文本文件中 使用

21、短语TO FILE FileName ADDITIVE可以将查询结果 存放到文本文件中，其中FileName给出了文本文件名(默 认扩展名是txt)，如果使用ADDITIVE结果将追加在原文 件的尾部，否则将覆盖原有文件。 如下语句将查询结果以文本的形式存储在文本文件 tmp.txt中： SELECT * TOP 3 FROM 学生; TO FILE tmp ORDER BY 年龄 DESC 如果TO短语和INTO短语同时使用，则TO短语将会被忽略。 目 录 上一页 下一页 退 出 2021-7-957 将查询结果直接输出到打印机 使用短语TO PRINTER PROMPT可以直 接将查询结果

22、输出到打印机，如果使用 PROMPT选项，在开始打印之前会打开打 印机设置对话框。 目 录 上一页 下一页 退 出 2021-7-958 数据操纵语言是完成数据操作的命令，一般分为两种类数据操纵语言是完成数据操作的命令，一般分为两种类 型的数据操纵，它们统称为型的数据操纵，它们统称为DML： 数据修改：数据修改：INSERT（插入）、（插入）、 UPDATE（更新）（更新） 、 DELETE（删除）（删除） 数据检索：数据检索： SELETE （查询）（查询） 5 SQL记录操作功能记录操作功能 目 录 上一页 下一页 退 出 2021-7-959 【格式格式1】一次插入一条记录一次插入一条记

23、录 INSERT INTO () VALUES （） 【格式格式2】一次插入多条记录一次插入多条记录 INSERT INTO () VALUES （, , ， ） 【格式格式3】从查询结果插入从查询结果插入 INSERT INTO () 【格式格式4】表的插入表的插入 INSERT INTO () TABLE 功能功能:将表名将表名2的值插入到表名的值插入到表名1中。中。 插入记录插入记录 目 录 上一页 下一页 退 出 2021-7-960 【例例47】在学生表文件的末尾追加一条记录。在学生表文件的末尾追加一条记录。 INSERT INTO 学生学生(学号学号,姓名姓名,性别性别,出生年月出

24、生年月,专业专业); VALUES(05040001,李建国李建国,男男,09/28/86,软软 件件) 目 录 上一页 下一页 退 出 2021-7-961 插入数据 Visual FoxPro支持两种SQL插入命令的格式，第一种格式 是标准格式，第二种格式是Visual FoxPro的特殊格式。 第一种格式： INSERT INTO 表名字段名1,字段名2,)VALUES (表达 式1,表达式2,) 第二种格式： INSERT INTO 表名 FROM ARRAY 数组名|FROM MEMVAR 目 录 上一页 下一页 退 出 2021-7-962 其中： “INSERT INTO 表名”

25、说明向由表名指定的表中插入记 录，当插入的不是完整的记录时，可以用字段名1,字段 名2,指定字段；VALUES (表达式1 ,表达式2,) 给出具体的记录值； “FROM ARRAY 数组名”说明从指定的数组中插入记录值； FROM MEMVAR说明根据同名的内存变量来插入记录值，如 果同名的变量不存在，那么相应的字段为默认值或空值。 目 录 上一页 下一页 退 出 2021-7-963 例 在学生表中插入数据。 INSERT INTO 学生 VALUES(“890163”,“张文青”,“男”,; 20,.T.,CTOD(1999/09/01),三好生) 或者: INSERT INTO 学生(

26、学号,年龄) VALUES(“890239”,20) 目 录 上一页 下一页 退 出 2021-7-964 例定义数组A(6),A中各元素的值分别为: A(1)=980012,A(2)=张,A(3)=女,A(4)=18, A(5)=.F.,A(6)=1999/09/01。 在学生表中插入一条记录: INSERT INTO 学生 FROM ARRAY A 新记录的备注字段为空。 目 录 上一页 下一页 退 出 2021-7-965 例已定义内存变量：学号=960012，姓名=李明，年 龄=20，且保存在MEMVAR.MEM文件中，在学生表中再添加一 条“李明”的记录。 INSERT INTO 学

27、生 FROM MEMVAR 使用FROM MEMVAR添加新记录：将内存变量的值赋给表中指 定记录的同名字段变量。如果同名的内存变量不存在，则 相应的字段为空。 目 录 上一页 下一页 退 出 2021-7-966 DIMENSION DATA8 DATA（1）=011103 DATA（2）=李宁李宁 DATA（3）=女女 DATA（8）=电电0138 DATA（5）=02 DATA（6）=江西九江江西九江 DATA（7）=05/06/85 DATA（8）=.F. INSERT INTO STUD FROM ARRAY DATA *用数组方式追加第二条记录用数组方式追加第二条记录* 目 录 上

28、一页 下一页 退 出 2021-7-967 学号学号=011202 姓名姓名=赵娜赵娜 性别性别=女女 班级名班级名=英英0112 系别代号系别代号=03 地址地址=广西南宁广西南宁 出生日期出生日期=02/21/88 是否团员是否团员=.F. INSERT INTO STUD FROM MEMVAR LIST *用内存变量方式追加第三条记录用内存变量方式追加第三条记录* 目 录 上一页 下一页 退 出 2021-7-968 8.5.2 删除记录删除记录 【格式格式】DELETE FROM WHERE 或 DELETE FROM 数据库名!表名WHERE 条件 【功能功能】这里FROM指定从哪

29、个表中加删除标记，WHERE指定 对满足条件的记录加删除标记，如果不使用WHERE子句，则对 该表中的全部记录加删除标记。 【说明说明】这里的删除是逻辑删除，即在删除的记录前加上一个这里的删除是逻辑删除，即在删除的记录前加上一个 删除标记删除标记“*”。如果要物理删除记录需要继续使用PACK命令。 【例例48】删除学生表中所有性别为男的记录。删除学生表中所有性别为男的记录。 DELETE FROM 学生学生; WHERE 性别性别=“男男” 目 录 上一页 下一页 退 出 2021-7-969 【例例49】对课程表和选修表中的元组进行逻辑删除操作。对课程表和选修表中的元组进行逻辑删除操作。 (1)将课程表中课程号为C002的元组删除。 DELETE FROM 课程; WHERE 课程号=C002 (2)把王小明的多媒体技术成绩从选修表中删除。王小明的 学号为05010001，多媒体技术的课程号为C003。 DELETE FROM 选修; WHERE 学号=05010001 AND 课程号=C003 目 录 上一页 下一页 退 出 2021-7-970 【格式格式】UPDATE 数据库名数据库名! ; SET =，=|ROW=(); WHERE 【功能功能】更新指定表文件中满足更新指定表文件中满足WHERE条件子句的数据。条件子句