计算机二级 第5章 关系数据库标准语言SQL.ppt

1、第5章 关系数据库标准语言SQL,SQL（Structured Query Language-结构化查询语言）,前言,当表中的记录较少，如几条、几十条记录时，通过浏览窗口或LOCATE、SEEK查找命令可以很快查到符合条件的记录。但当表较大，有几百条、几千条甚至上万条记录时，再使用浏览窗口就非常困难，而通过SEEK或LOCATE命令只能显示本次查找结果，不能保留。建立数据库的目的不仅仅是为了存储数据，更重要的是利用数据库技术来处理这些数据，以得到这些数据所包含的信息。表的查询是数据处理的重要工作之一，在很多时候，用户所需要的只是大量数据中的一部分而已。本章主要介绍关系数据库标准语言SQL。,5

2、.1 SQL概述,SQL标准是1986年10月由美国ANSI公布的，ISO于1987年6月正式采纳为国际标准SQL89SQL92 SQL语言的特点 1.SQL是一种一体化的语言，包含数据定义、数据查询、数据操纵和数据控制等功能，可以完成数据库活动中的全部工作 2.SQL是一种高度非过程化的语言 3.SQL语言非常简洁 4.SQL语言可直接以命令方式交互使用，也可嵌入到程序设计语言中,SQL 命令动词,5.2 查询功能,SQL语言的核心是查询。SQL语言的查询命令也称作SELECT命令，它的基本形式由SELECTFROMWHERE查询块组成，多个查询块可嵌套执行。 SELECT ALL|DIST

3、INCT AS 列名 字段名 AS 列名， FROM INNER | LEFT OUTER | RIGHTOUTER | FULL OUTER JOIN ON WHERE AND GROUP BY ， ， HAVING ORDER BY ASC|DESC INTO | TO FILE ADDITIVE | TO PRINTER PROMPT | TO SCREEN,SELECT说明要查询的数据； FROM说明要查询的数据来自哪些表，可基于单表也可基于多表进行查询 WHERE说明查询条件，即选择元组的条件 GROUP BY用于对查询结果进行分组 HAVING跟随GROUP BY语句使用，用于限定

4、分组必须满足的条件 ORDER BY用于对查询的结果进行排序,示例表：xj、cj、kc表，包含如下字段,5.2.1 简单查询 例5.1： 从学籍表中查找姓名字段 SELECT 姓名 FROM XJ 从成绩表中查找出课程号字段 SELECT 课程号 FROM CJ SELECT DISTINCT 课程号 FROM CJ DISTINCT用于去掉查询结果中的重复值,例5.2 ： 检索学籍表中所有元组和字段 SELECT * FROM XJ 例5.3： 检索成绩表中成绩大于70的学生学号和成绩 SELECT 学号,成绩 FROM CJ WHERE 成绩70,例5.4：查询XJ表中，出生日期在1988

5、-1-1之前的学生的姓名和家庭住址 select 姓名,家庭住址 from xj where 出生日期=1988-01-01 例5.5： 查询XJ表中，家庭住址在三台并且政治面貌为团员的学生姓名，性别和出生日期 select 姓名,性别,出生日期 from xj where 家庭住址=三台 and 政治面貌=团员,5.2.2 简单的连接查询,连接是关系的基本操作之一，连接查询是一种基于多个关系的查询。 例5.6：查找CJ表和XJ表中的学号，姓名，课程号和成绩 SELECT xj.学号,姓名,课程号,成绩 from xj,cj where (xj.学号=cj.学号) 例5.7：查找CJ表中成绩大

6、于等于70分的学生的学号，姓名，课程号和成绩 SELECT xj.学号,姓名,课程号,成绩 from xj,cj where (xj.学号=cj.学号) and (成绩=70),注意：当FROM之后的多个关系中含有相同的属性名时，这时必须用关系前缀指明属性所属的关系，如xj.学号，cj.课程号等。点号前面是关系名（表名），后面是属性名（表中的字段名）。,5.2.3 嵌套查询,这类查询所要求的结果出自一个关系，但相关的条件却涉及多个关系。例如，当检索一个关系中的元组时，它的条件依赖于相关的另一个关系中的元组的属性值。 例5.8：查询成绩不及格同学的家庭住址 SELECT distinct 家庭住

7、址 from xj where 学号 in (select 学号 from cj where 成绩60) 例5.9：查询成绩及格同学的姓名和家庭住址 SELECT 学号, 姓名,家庭住址 from xj where 学号 not in (select 学号 from cj where 成绩60),在这个命令中含有两个SELECT-FROM-WHERE查询块，即内层查询块和外层查询块，内层查询块检索到的结果是外层查询块的条件 例5.10:查找课程号为001,并且成绩为62.5的同学的学号,姓名和家庭住址 SELECT 学号,姓名,家庭住址 from xj where 学号=(select 学号

8、from cj where (课程号=001) and (成绩=62.5),5.2.4 几个特殊运算符,例4.11 查询出成绩在 65到75范围内的信息。 SELECT * FROM CJ WHERE 成绩 BETWEEN 65 AND 75 注：BETWEEN AND等价于 （成绩=65） AND （成绩=75）,例5.12 查询BYMC表中通讯地址以”绵阳”开头的学生的姓名,性别,通讯地址和专业信息 SELECT 姓名,性别,通讯地址,专业 FROM BYMC WHERE 通讯地址 LIKE 绵阳% “ 这里的LIKE是字符串匹配运算符，通配符“%”表示0个或多个字符,另外还有一个通配符”

9、_”表示一个字符,如”刘%”或“刘_”。,例5.13 查询BYMC表中专业不是”护理”专业的学生的姓名,性别,通讯地址和专业信息 SELECT 姓名,性别,通讯地址,专业 FROM BYMC WHERE 专业!=护理 例5.131 查询成绩表中成绩不在65至75之间的学生信息。 SELECT * FROM CJ WHERE 成绩 NOT BETWEEN 65 AND 75,5.2.5 排序,例5.14 查询成绩表，并以成绩从高到低排序。 SELECT * FROM CJ ORDER BY 成绩 DESCENDING 例5.15 查询BYMC表中的序号,姓名,性别,专业学生信息,并以序号进行升序

10、排列. SELECT 序号,姓名,性别,通讯地址,专业 FROM BYMC ORDER BY 序号 注意：ORDER BY是对最终的查询结果进行排序，不可以在子查询中使用该短语。,5.2.6 简单的计算查询,例5.16 找出BYMC表中专业的个数. SELECT COUNT(专业) FROM BYMC SELECT COUNT(DISTINCT 专业) FROM BYMC 例5.17 求CJ表中学号为2003010101同学的所有科目的总和与平均. SELECT SUM(成绩),AVG(成绩) FROM CJ WHERE 学号=2003010101,例5.18 查询家庭住址为绵阳同学的各科成绩

11、总和. SELECT SUM(成绩) FROM CJ WHERE 学号 IN (SELECT 学号 FROM XJ WHERE 家庭住址=绵阳) 例5.19 求学号为2003010106同学成绩的最高分和最低分. SELECT MAX(成绩),MIN(成绩) FROM CJ WHERE 学号=2003010106,5.2.7 分组与计算查询,例5.21 求CJ表中每个同学的平均成绩。 SELECT 学号,AVG(成绩) FROM CJ GROUP BY 学号 例5.22 求CJ表中平均成绩及格的信息。 SELECT 学号,AVG(成绩) FROM CJ GROUP BY 学号 HAVING A

12、VG(成绩)=60 Having子句总是跟在Group By子句之后，而不可以单独使用。它与Where子句不矛盾，在查询中最生用Where子句限定元组，最后再利用Having子句限定分组。,5.2.8 利用空值查询,例5.23 在CJ表中找出没有输入课程号的学生学号。 SELECT * FROM CJ WHERE 课程号 IS NULL SELECT * FROM CJ WHERE 课程号 IS NOT NULL 查询空值时要使用IS NULL，而=NULL是无效的，因为空值不是一个确定的值，所以不能用=这样的运算符进行比较。,5.2.9 别名与自连接查询,例:SELECT XJ.学号,XJ.

13、姓名,XJ.性别,KC.课程名,KC.任课教师,CJ.成绩 FROM XJ,CJ,KC WHERE XJ.学号=CJ.学号 AND CJ.课程号=KC.课程号 或:SELECT A.学号,A.姓名,A.性别,C.课程名,C.任课教师,B.成绩 FROM XJ A,CJ B,KC C WHERE A.学号=B.学号 AND B.课程号=C.课程号,自连接:是指同一关系与其自身进行连接。在可以进行这种自连接操作的关系上，实际存在着一种特殊的递归联系，即关系中的一些元组，根据出自同一值域的两个不同的属性，可以与另外一些元组有一种对应关系。 例4.25：查询 bymc表中与龙海丽同学同年同月同日出生的

14、同学的姓名和出生日期。 select a.姓名,a.出生日期 from bymc a,bymc b where a.出生日期=b.出生日期 and b.姓名=龙海丽,5.2.10 内外层互相关嵌套查询,在前面的嵌套查询中都是外层查询依赖内层查询结果，而内层查询与外层查询无关。但有时也需要内、外层互相关的查询，这时内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果。 例5.26 列出每位同学有最高成绩的信息。 SELECT O.学号,O.课程号,O.成绩 FROM CJ O WHERE 成绩=(SELECT MAX(成绩) FROM CJ I WHERE O.学号=I.学号),

15、5.2.11 使用量词和谓词的查询, ANY|ALL|SOME(子查询) NOT EXISTS (子查询)，用来检查在子查询中是否有结果返回（即存在元组和不存在元组）。 例5.27 查找还没有成绩的学生信息 SELECT * FROM XJ WHERE NOT EXISTS (SELECT * FROM CJ WHERE 学号=XJ.学号) 注：这里的内层查询引用了外层查询的表，只有这样使用谓词EXISTS和NOT EXISTS才有意义。所以这类查询也都是内、外层互相关嵌套查询。,例5.29 查找出成绩大于学号为2003010101同学任一科目成绩的学生成绩信息。 SELECT * FROM

16、CJ WHERE 成绩 =ANY(SELECT 成绩 FROM CJ WHERE 学号=“2003010101”) 例5.30 查找出成绩大于学号为2003010101同学所有科目成绩的学生成绩信息。 FROM CJ WHERE 成绩 = ALL (SELECT 成绩 FROM CJ WHERE 学号=“2003010101”),5.2.12 超连接查询,在新的SQL查询中支持两个新的关系连接运算符，原来的连接是只有满足连接条件，相应的结果才会出现在结果表中；而这两个新的连接运算是，首先保证一个表中满足条件的元组都在结果表中，然后将满足连接的元组与另一个表的元组进行连接，不满足连接条件的则应将

17、来自另一表的属性值为空值。 超连接分左连接和右连接，左连接是在结果表中包含第一个表中满足条件的所有记录；如果结果表是在连接条件上匹配的元组，则第二个表返回相应值，否则第二个表返回空值；右连接是在结果表中包含第二个表中满足条件的所有记录；如果结果表是在连接条件上匹配的元组，则第一个表返回相应值，否则第一表返回空值。,VF中超连接的语法格式为： SELECT FROM 表名 INNER|LEFT|RIGHT|FULL JOIN 表名 ON 连接条件 WHERE 例5.31 查询相应学号同学的课程名，任课教师和科目成绩。 SELECT CJ.学号,KC.课程名,KC.任课教师,CJ.成绩 FROM

18、CJ JOIN KC ON CJ.课程号=KC.课程号,例5.32 左连接（查询相应学号同学的课程名，任课教师和科目成绩。 ） SELECT CJ.学号,KC.课程名,KC.任课教师,CJ.成绩 FROM CJ LEFT JOIN KC ON CJ.课程号=KC.课程号 例5.33 右连接（查询相应学号同学的课程名，任课教师和科目成绩。 ） SELECT CJ.学号,KC.课程名,KC.任课教师,CJ.成绩 FROM CJ RIGHT JOIN KC ON CJ.课程号=KC.课程号,例5.34 全连接（查询相应学号同学的课程名，任课教师和科目成绩。 ） SELECT CJ.学号,KC.课程名

19、,KC.任课教师,CJ.成绩 FROM CJ FULL JOIN KC ON CJ.课程号=KC.课程号,5.2.13 集合的并运算,SQL支持集合的并（UNION）运算，即可以将两个SELECT语句的查询结果通过并运算合并成一个查询结果。但是要求两个查询结果具有相同的字段个数，并且对应字段的值要出自同一个值域（相同的数据类型和取值范围）,例如：查询学号为2003010101和2003010110两位同学的信息。 SELECT * FROM CJ WHERE 学号=2003010101 UNION SELECT * FROM CJ WHERE 学号=2003010110,5.2.14 VFP中

20、SQL SELECT的几个特殊选项,1.只显示前几项记录 TOP 数值表达式 PERCENT ORDER BY 例5.35 查询CJ表中课程号001前3名的学生成绩信息 SELECT * TOP 3 FROM CJ WHERE 课程号=001 ORDER BY 成绩 DESC 例5.36 显示成绩最高的那50%学生的成绩信息 SELECT * TOP 50 PERCENT FROM CJ ORDER BY 成绩 DESC,2.将查询结果存放到数组中 使用INTO ARRAY 数据名 例：将学号为”2003010101”同学的成绩信息放到SZ数组中 SELECT * FROM CJ INTO A

21、RRAY SZ WHERE 学号=2003010101“ 3.将查询结果存放在临时文件中 使用INTO CURSOR 例：将学号为”2003010101”同学的成绩信息放到临时文件aaa中 SELECT * FROM CJ INTO CURSOR aaa WHERE 学号=2003010101“,4.将查询结果存放到永久表中 INTO DBF|TABLE 表名 将学号为”2003010101”同学的成绩信息放到表bbb中 SELECT * FROM CJ INTO TABLE bbb WHERE 学号=2003010101“ 5.将查询结果存放到文本文件中 TO FILE 文件名 ADDITI

22、VE 6.将查询结果直接输出到打印机 TO PRINTER PROMPT,5.3 操作功能,操作功能是指对数据库中数据的操作功能，主要包括数据的插入、更新和删除3方面的内容。 5.3.1 插入数据 格式1:INSERT INTO 表名(字段名列表) VALUES(表达式列表) 格式2:INSERT INTO 表名 FROM ARRAY 数组名 FROM MEMVAR 注：在表的末尾插入数据同APPEND命令,5.3.2 更新数据 格式为：UPDATE 表名 SET 字段名=表达式 WHERE 条件 例：将CJ表中课程号为001的成绩减去5分。 UPDATE CJ SET 成绩=成绩-5 WHE

23、RE 课程号=001 5.3.3 删除数据 格式为：DELECT FROM 表名WHERE 条件 注：该删除为逻辑删除。,5.4 定义功能,定义功能包括数据库的定义、表的定义、视图的定义、存储过程的定义、规则的定义和索引等若干部分。 5.4.1 表的定义 格式为：CREATE TABLE|DBF 表名 (字段名 类型(宽度),)NULLNOT NULL CHECK 逻辑表达式 ERROR 信息提示DEFAULT 表达式PRIMARY KEY|UNIQUE,5.4.2 表的删除 格式为：DROP TABLE 表名 5.4.3 表结构的修改 格式1：ALTER TABLE 表名 ADDALTER COLUMN 字段名 类型(宽度) 格式2： ALTER TABLE 表名 ALTER COLUMN 字段名 NULLNOT NULLSET DEFAULT 表达式SET CHECK 逻辑表达式 ERROR 信息提示DROP DEFAU