第4章 关系数据库标准语言SQL.ppt

1、第4章 关系数据库标准语言SQL,14:18:31,4.1 SQL概述,SQLStructured Query Language （结构查询语言） 是一个功能强大的数据库语言。现在已成为关系数据库管理系统的标准语言。,4.1.1 SQL概念,14:18:31,SQL发展历程,1976年诞生在IBM公司 1986年出现标准，ANSI公布 1987年成为国际标准，ISO公布 SQL89 SQL92 SQL3,14:18:31,SQL的特点,是一种一体化语言，包含了数据定义、数据操纵、数据控制等功能 高度的非过程化，不需要告诉计算机“怎样做”，只要说明“做什么” 语言简洁，只有很少几条命令，接近自然

2、语言 可交互式用，又可嵌入程序设计语言中,14:18:31,SQL语言的基本命令 （表4-1）,SELECT：实现查询功能 CREATE、DROP、ALTER：数据定义 INSERT、UPDATE、DELETE：数据操作 GRANT、REVOKE：数据控制,14:18:31,4.1.2 VFP对SQL的支持,不同的数据库管理系统所支持的SQL不尽相同： VFP支持SQL的数据定义、数据查询、数据操作等功能，不支持数据控制功能。,在VFP中不能使用命令GRANT、REVOKE,14:18:31,4.2 SQL的定义功能,4.2.1 表的定义与删除,14:18:31,1. 定义数据表,命令格式：

3、CREATE TABLE | DBF 表名 NAME 长表名FREE (字段名 类型 (宽度)NULL|NOT NULLCHECK 表达式ERROR 错误信息DEFAULT 表达式PRIMARY KEY | UNIQUE ,字段名) | FROM ARRAY 数组名,14:18:31,关键词说明：,NAME：指定长表名 FREE：指明建立自由表 NULL：字段可否为空 UNIQUE：建立候选索引 PRIMARY KEY：建立主索引 FROM ARRAY：根据指定数组内容建立表,14:18:31,范例： CREATE TABLE 学生档案 ( 学号 c(6) PRIMARY KEY, 姓名 c(

4、8) NOT NULL, 年龄 n(2) CHECK (年龄0) ERROR “年龄应大于0!” DEFAULT 20 ),14:18:31,2. 表的删除,格式： DROP TABLE 表名 DROP VIEW 视图名 说明：删除数据库表时应在打开数据库后进行，该命令是直接删除表文件。 删除表还可以用 DELETE FILE 表名,14:18:31,4.2.2 数据表结构的修改,格式：ALTER TABLE 表名 ADD|DROP| ALTER COLUMN 字段名 类型(宽度) NULL|NOT NULLCHECK 表达式ERROR 错误信息 DEFAULT 表达式PRIMARY KEY

5、| UNIQUE 功能：增加、删除或修改字段及数据完整性规则。,14:18:31,例1：为学生成绩表增加一物理成绩字段,并对用户输入作有效性检查。 ALTER TABLE 学生成绩; ADD 物理 n(6,2) SET CHECK 物理0,例2：对上例删除物理字段的有效性检查。 ALTER TABLE 学生成绩; ALTER 物理 DROP CHECK,14:18:31,例3：对上例，删除物理字段。 ALTER TABLE 学生成绩; DROP COLUMN 物理,例4：删除学生成绩表中的主索引。 ALTER TABLE 学生成绩; DROP PRIMARY KEY,14:18:31,4.3

6、SQL的操作功能,4.3.1 数据记录的插入,命令格式： INSERT INTO 表名(字段名表) VALUES (表达式表),指明操作的表,指明插入的字段,指明插入对应字段的值,14:18:31,范例：向学生档案表中加入记录： （020179,王蕾,女,工商会计,85） INSERT INTO 学生成绩(学号,姓名,性别,专业,综合考评); VALUES (“020179”, “王蕾”, “女”, “工商会计”, “85”),14:18:31,VFP中插入命令的特殊格式：,INSERT INTO 表名; FROM ARRAY 数组名； | FROM MEMVAR,说明：从指定数组或内存变量中

7、向指定数据表插入数据记录。,14:18:31,4.3.2 数据更新,命令格式： UPDATE 表名; SET 字段名=表达式 字段名2=表达式2; WHERE 条件表达式 功能：修改指定表中满足条件的记录的某些字段值。,14:18:31,范例：将学生档案表中所有女生的综合考评加1分。 UPDATE 学生档案; SET 综合考评=综合考评+1; WHERE 性别=女,14:18:31,命令格式： DELETE FROM 表名 WHERE 条件 功能：删除指定表中满足条件的记录。 如不加限定条件则删除表中所有记录。,4.3.3 数据删除,14:18:31,范例：删除学生成绩表中2000130101

8、号学生的记录。 DELETE FROM 学生成绩 WHERE 学号=“2000130101”,注意：在VFP中本命令只是逻辑删除记录，要物理删除记录还要使用其它命令。,14:18:31,4.4 SQL的数据查询功能,查询语句的一般格式：,SELECT ALL | DISTINCT field1,field2,. FROM table1,table2,. WHERE conditions GROUP BY field HAVING conditions ORDER BY field ASC | DESC ,14:18:31, 各子句的作用说明：,FROM是唯一必须的子句，指定查询来源； WHER

9、E可选子句限定查询条件； GROUP BY子句对查询结果进行分组； HAVING子句只能用于GROUP BY之后，为每个分组指定条件； ORDER BY指定查询结果的显示顺序。,14:18:31,4.4.1 简单查询,1. 从表中查询全部记录 SELECT * FROM 学生档案 说明：* 代表所有字段 “学生档案”指明查询结果来自的数据表,14:18:31,查询结果的过滤,例： SELECT DISTINCT 生源地 FROM 学生档案,DISTINCT 表示把查询结果中某一字段重复的记录过滤掉，相同的只留一条记录。,14:18:31,2. 查询满足条件的指定输出字段,SELECT 字段名表

10、 FROM 表名 WHERE 条件表达式 功能：从指定表中查询满足条件的记录，显示出指定字段,14:18:31,条件表达式的说明：,表达式可以使用以下关系（比较）运算符： = 、 、= 、 、 LIKE 、IN 、 BETWEEN,逻辑运算符： NOT AND OR,14:18:31,例1：=与AND的使用,SELECT 姓名，性别，专业; FROM 学生档案; WHERE 出生日期=1980-02-01; AND 性别=“女”,注意：不同数据类型数据实例的写法。 日期型：1980-02-01,14:18:31,1. IN的使用 例如：,SELSCT * FROM 学生档案; WHERE 专业

11、 IN (市场营销,工商会计) 专业= 市场营销 OR 专业= 工商会计 NOT 专业=信息管理,在只有三个专业的表中查询，以上条件等价。,4.4.2 查询中的特殊运算符,14:18:31,例：,SELECT * FROM 学生档案; WHERE 姓名 LIKE 王_ OR； 姓名 LIKE 周%,说明：_ 替代一个字符，%替代多个字符。,2. LIKE的使用,14:18:31,例：,SELECT * FROM 学生档案; WHERE 综合考评 BETWEEN 70 AND 90 综合考评=70 AND 综合考评=90 以上两个条件等价,3. BETWEEN的使用,14:18:31,4.4.3

12、 连接查询,将两个或两个以上的表连接起来，从中进行查询，生成查询结果。,例： SELECT 学生档案.学号,姓名,英语 ; FROM 学生档案,学生成绩 ; WHERE 学生成绩.学号=学生档案.学号,14:18:31,两表相同字段名前应加表名前缀； 使用连接条件构造连接； 使用关系运算符； 为表指定别名：例；,连接查询的说明,14:18:31,例2： SELECT D.学号,姓名,英语 ; FROM 学生档案 D, 学生成绩 C ; WHERE D.学号=C.学号,14:18:31,为使查询结果更加明确，可以为每个字段加上别名。 例3： SELECT NO 学号，NAME 姓名； FROM

13、XSDA,关于为字段选择别名,14:18:31,4.4.4 嵌套查询,在一个查询中可以嵌套一个或多个查询，从而完成复杂的查询任务。 被嵌套的内层查询我们习惯上称之为子查询。 子查询一般作为外层查询的查询条件。,14:18:31,例1（10-11）：在学生档案表中查询与“张文”在一个专业的学生。,SELECT 姓名,性别,专业； FROM 学生档案 WHERE; 专业=（SELECT 专业 FROM 学生档案； WHERE 姓名=张文）,14:18:31,例2（10-13）：查询与张文同专业的学生的成绩。,SELECT D.学号,姓名,专业,高数,英语； FROM 学生档案 D，学生成绩 C；

14、WHERE 专业=（SELECT 专业； FROM 学生档案 WHERE 姓名=张文）； AND D.学号=C.学号,14:18:31,例： SELECT * FROM 学生档案 ORDER BY 学号 DESC ASC升序、DESC降序,ORDER BY 指定查询结果的显示顺序。,4.4.5 查询排序,14:18:31,4.4.6 分组汇总查询,使用GROUP BY子句进行查询分组。 在分组的基础上使用函数或表达式可以完成一些统计汇总工作。,14:18:31,AVG( ) 、 SUM( ) 、ROUND(N,d) 、DTOC( ) 、 CTOD( ) 、 LOWER( ) 、 UPPER(

15、) 、 MAX( ) 、 MIN( ) 、COUNT( ),常用统计函数,14:18:31,应用示例 例：分别求出学生成绩表中高数、英语、计算机三科成绩的平均分。 SELECT AVG(高数),AVG(英语),AVG(计算机) FROM 学生成绩,14:18:31,例：分别求出学生成绩表中高数、英语、计算机三科的总分。 SELECT SUM(高数)高数总分, ；SUM(英语) 英语总分,SUM(计算机) 计;算机总分 FROM 学生成绩,14:18:31,例：查询出学生档案表中学生所属专业的个数。 SELECT COUNT(DISTINCT 专业) ; FROM 学生档案,例：查询出学生成绩表

16、中高数最高分。 SELECT MAX(高数) FROM 学生成绩,14:18:31,HAVING 短语,SQL通过GROUP BY 子句 实现分组，通过HAVING 短语对分组后的查询结果进行限定。,14:18:31,例：在学生档案、学生成绩两表中专业分别求男女生的人数、高数平均分，并只列出高数平均分高于80分的专业。,SELECT 专业,性别,COUNT(*) 人数, ; AVG(高数) 高数平均分 ; FROM 学生档案 D, 学生成绩 C; WHERE D.学号=C.学号; GROUP BY 专业，性别; HAVING AVG(高数)=80,14:18:31,4.4.7 空值查询,利用N

17、ULL值进行查询操作，使用IS作判断动词。,例：查询高数缺考的学生。 SELECT 学号 FROM 学生成绩 ; WHERE 高数 IS NULL,14:18:31,4.4.8 自连接查询,将同一个表作为两个表关联起来查询。 利用表的别名指定同一个表为两个表。,例5.42 ：查询至少选修了课程号为C01 和C02的学生的学号。,14:18:31,SELECT a.学号 FROM 选课登记 a,选课登记 b WHERE a.学号 = b.学号 ; AND a.课程号 =C01 ; AND b.课程号 =C02,14:18:31,4.4.9 内外层互相关嵌套查询,外层查询条件使用内层查询结果值，内

18、层查询条件需要外层查询提供值，这种查询成为内外层互相关嵌套查询。,14:18:31,SELECT * FROM 选课登记 A WHERE 成绩=(SELECT MAX(成绩) FROM 选课登记 B WHERE B.学号=A.学号),例：查询每位学生所选修的课程中成绩最高的课程成绩。,14:18:31,4.4.10 使用量词和谓词的查询,在含有子查询的查询语句中，常要用到一些特殊的运算符：,1、查询中的量词使用格式： ANY |ALL | SOME （子查询）,量词运算符,14:18:31,ANY、SOME是同义词，在作比较运 算时只要子查询中有一行能使结果为真，则结果就为真； ALL要求子查

19、询中的所有行都使结果为真时，结果才为真。,14:18:31,例1：有两个表分别存放1、2班的学生档案信息，查询1班里比2班所有学生出生都早的学生的信息。 SELECT * FROM XSDA1; WHERE 出生年月 ALL (SELECT; 出生年月 FROM XSDA2),14:18:31,SELECT * FROM XSDA1; WHERE 出生年月 (SELECT; MAX(出生年月) FROM XSDA2) 以上两查询效果相同。,14:18:31,例2：查询1班里比2班最小的学生出生早的学生的信息。 SELECT * FROM XSDA1; WHERE 出生年月 ANY (SELEC

20、T; 出生年月 FROM XSDA2),或SOME,14:18:31,SELECT * FROM XSDA1; WHERE 出生年月 (SELECT; MIN(出生年月) FROM XSDA2) 以上两查询效果相同。,14:18:31,2、查询中的谓词使用格式：,NOT EXISTS (子查询) EXISTS是谓词，他用来检查在子查询中是否有结果返回，即子查询结果中是否有记录。,14:18:31,例：查询学生档案表中高数不及格的学生信息。 SELECT * FROM 学生档案 A ; WHERE EXISTS (SELECT * ; FROM 学生成绩 WHERE 学号=A.学号; AND 高

21、数60),14:18:31,上面查询也可以用IN作条件判断: SELECT * FROM 学生档案 A ; WHERE 学号 IN (SELECT 学号; FROM 学生成绩 WHERE 高数60),14:18:31,4.4.11 超联接查询,等值联接和自然联接实现的多表查询中只有满足联接条件的记录才会出现在查询结果中，超联接运算符则不同。 标准的SQL语言中的超联接运算符为： 左联接运算符： “*= ” 右联接运算符： “ =*”,14:18:31,联接符的作用： *=：结果中返回满足条件的记录和运算符左侧表中的所有记录。 =*：结果中返回满足条件的记录和运算符右侧表中的所有记录。,14:1

22、8:31,在VFP中不支持以上两个运算符，与之对应的联接命令为： 左联接 LEFT JOIN 右联接 RIGHT JOIN,VFP还定义了两个特别的联接命令： 普通联接 INNER JOIN (或JOIN) 全联接 FULL JOIN,14:18:31,VFP中联接命令的使用格式：,SELECT FROM table1 INNER | LEFT | RIGHT | FULL JION table2 ON jioncondition WHERE ,指定联接条件,14:18:31,例：查询学生单科成绩，结果显示出每人所属的专业。 SELECT D.学号,姓名,专业,高数,英语; FROM 学生档案

23、 D INNER JOIN 学生;成绩 C ON D.学号=C.学号,等价与： SELECT D.学号,姓名,专业,高数,英语; FROM 学生档案 D, 学生成绩 C ; WHERE D.学号=C.学号,14:18:31,上例中，若要求结果显示两表中的匹配记录并显示出学生档案（或学生成绩）表中的所有记录。 SELECT D.学号,姓名,专业,高数,英语; FROM 学生档案 D; LEFT （RIGHT）JOIN 学生成绩 C ; ON D.学号=C.学号,14:18:31,上例中，若要求结果显示两表中的匹配记录和两表中的所有不匹配记录。 SELECT D.学号,姓名,专业,高数,英语; F

24、ROM 学生档案 D; FULL JOIN 学生成绩 C ; ON D.学号=C.学号,14:18:31,4.4.12 合并查询结果,SQL支持集合的并运算，即可以将俩和查询的结果合并到一起。 要求被合并的记录字段个数相同且对应字段值要出自同一值域。 并运算符 UNION,14:18:31,例如：如下语句是查询的属于两个专业的学生信息。 SELECT * FROM 学生档案; WHERE 专业=工商会计; UNION ; SELECT * FROM 学生档案; WHERE 专业=信息管理,14:18:31,4.4.13 VFP中SQL的特殊选项,1、显示部分结果 TOP PERCENT 功能：选取结果的前几条（使用PERCENT时为前百分之几）的记录。,注:TOP短语必须与ORDER BY同时使用。,14:1