第8章 SQL语言、查询与视图.ppt

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

2、以从数据库中提取出目前用户所需要的数据，并保存起来，以便日后使用。而借助SQL语言可以更深入地操作数据库，建立更高效与复杂的查询与视图。,第八章 SQL语言、查询与视图,8.1 结构化查询语言SQL SQL(Structured Query Language)语言是1974年由Boyce和Chamberlin提出的，在IBM公司的San Jose Research Laboratory研制的System R 上实现了这种语言。 Visual FoxPro不支持全部的SQL语言，仅支持SELECT、CREAT、ALTER、INSERT、DELETE、UPDATE等几种命令，其功能如下：,第八章

3、SQL语言、查询与视图,8.1.1 SQL的数据定义 1、定义表 命令格式： CREAT TABLE ( ( ， ) ， ( ， ) 例8.1 用SQL创建学生表CREAT TABLE 学生(学号 C(6)，姓名 C(8)，性别 L，出生日期 D，奖学金 N(6,2) ) 说明：新表将在最低的可用工作区打开，并可通过它的别名来访问。新表以独占方式打开，与SET EXCLUSIVE的设置无关。,第八章 SQL语言、查询与视图,2、修改表 命令格式： ALTER TABLE ADD|ALTER COLUMN 字段名1 类型 (字段宽度 ，小数位数 ) 参数说明： ADD COLUMN 字段名1：指

4、定要添加的字段名，一个单独的表可包含最多255个字段，如果一个或更多的字段允许空值，该限制数降至254上字段。 ADD/ALTER COLUMN 字段名1：指定要修改的字段名。 例8.2 向学生表添加年龄字段 ALTER TABLE 学生 ADD 年龄 N(2),第八章 SQL语言、查询与视图,8.1.2 SQL的数据修改 Visual FoxPro提供了三条命令进行数据操作。 1、INSERT命令 格式1： INSERT INTO 表名 (字段名1 ，字段名2，) VALUES (表达式1 ，表达式2，),第八章 SQL语言、查询与视图,格式2： INSERT INTO 表名 FROM AR

5、RAY 数组名|FROM MEMVAR 说明： 数组名，指定输入到新记录的字段值。如果存在字段名参数表，则数组各个元素的值依次赋予指定的字段名；否则，数组各个元素的值依次赋予表结构定义的字價。如果数组元素的个数多于字段的个数，则忽略多余的数组元素；如果数组元素的个数少于字段的个数，则多余的字段要么为表设计中定义的默认值，要么为空值。 例8.3 向学生表中插入一条记录。 INSERT INTO 学生 (学号，姓名，性别) VALUES (“990203”，“杨阳”，.T.),第八章 SQL语言、查询与视图,2、DELETE命令DELETE命令给表中的记录加上删除标记。 格式： DELETE FR

6、OM 数据库名! 表名 WHERE 过滤条件1 AND|OR 过滤条件2 例8.4 删除学生表中的李华 DELETE FROM 学生 WHERE 姓名=“李华”,第八章 SQL语言、查询与视图,3、UPDATE命令 UPDATE命令用新值替换表中现有记录的值。 格式： UPDATE 数据库名! 表名 SET 字段名1=表达式1 ，字段名2=表达式2 WHERE过滤条件1 AND|OR 过滤条件2 例8.5 将学生表中林虹的奖学金增加100元。 UPDATE 学生 SET 奖学金=奖学金+100 WHERE 姓名=“林虹”,第八章 SQL语言、查询与视图,8.1.3 SQL的数据查询 格式： S

7、ELECT ALL|DISTINCT 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,第八章 SQL语言、查询与视图,说明： SELECT 后紧跟的是将要查询的数据； FROM 子句指出查询数据的来源，即属于哪个表； WHERE子句表示查询的数据满足的条件； GROUP 子句说明查询结果的

8、分组依据； HAVING子句限定分组所需满足的条件； ORDER BY 子句用来对查询的结果进行排序。,第八章 SQL语言、查询与视图,1、简单查询 (1) 只有FROM子句 查询指定列 例8.6 查询学生表中的学生的年龄 SELCET 姓名，年龄 FROM 学生 查询全部列列出表中的所有字段，一是在SELECT后列出所有字段名，一是使用“*”。(注意，有的数据库管理系统不支持“*”) 例8.7 查询学生表中全体学生的所有信息。 SELCECT * FROM 学生 或 SELECT 学号，姓名，性别，出生日期，简历,第八章 SQL语言、查询与视图,(2) WHERE子句 说明： 关键字LIKE

9、 可以用来进行字符串的匹配，语法格式如下： NOT LIKE ESCAPE ” 在匹配串中可以使用通配符“&”，“_”。 % 代表任意长度(长度可为0)的字符串 _(下划线) 代表任意单个字符,第八章 SQL语言、查询与视图,例8.8 查询9901班的全体学生 SELECT * FORM 学生 WHERE SUBSTR(学号,1,4)=“9901“ 例8.9 查询成绩在70到80之间的学生 SELECT 学号，成绩 FROM 成绩 WHERE 成绩 BETWEEN 70 AND 80 例8.10 查询20 岁和22 岁的学生名单 SELECT 姓名 FROM 学生 WHERE 年龄 IN (2

10、0,22) 例8.11 查询姓“王”的学生 SELECT 姓名 FROM 学生 WHERE 姓名 LIKE“王%”,第八章 SQL语言、查询与视图,(3) 空值查询 例8.12 查询缺考学生的学号和课程号 SELECT 学号，课程号 FROM 成绩 WHERE 成绩 IS NULL (4) GROUP BY 子句 将查询结果按某一列或多列值分组，值相等的为一组。 例8.13 将学生表中的学生按年龄进行分组，并统计各组的人数 SELECT 年龄，COUNT(姓名) FROM 学生 GROUP BY 年龄,第八章 SQL语言、查询与视图,(5) ORDER BY 子句 例8.14 查询获得奖学金的

11、学生姓名和奖学金，查询结果按奖学金降序排序。 SELECT 姓名，奖学金 FROM 学生 WHERE 奖学金0 的ORDER BY 奖学金 DESC (6) 计算列 SELECT关键字后不仅可以跟字段名，还可以是表达式。 例8.15 查询全体学生的姓名和年龄 SELECT 姓名，YEAR(DATE()-YEAR(出生日期) AS 年龄 FROM 学生,第八章 SQL语言、查询与视图,2、嵌套查询 例8.16 查询选修了“0001”课程的学生名单SELECT 姓名 FROM 学生 WHERE 学号 IN SELECT 学号 FROM 成绩 WHERE 课程号=“0001”,第八章 SQL语言、查

12、询与视图,3、联接查询 一个查询同时涉及两个以上的表，则称为联接查询。联接有4 种类型，在FROM子句指明： INNER JOIN ：内部联接，结果集中包含联接条件左右的两个表中满足联接条件的记录。 LEFT JOIN ：左联接，结果集中包含联接条件左边的表中所有记录和联接条件右边的表中满足联接条件的记录。 RIGHT JOIN ：右联接，结果集中包含联接条件右边的表中所有记录和联接条件左边的表中满足联接条件的记录。 FULL JOIN ：完全联接，结果集中包含两个表中的所有记录，不论是否满足联接条件。 缺省为内部联接，除了四种连接外，还可以用WHERE条件实现查询。,第八章 SQL语言、查询

13、与视图,例8.17 查询每个学生的选课成绩 (1) 内部联接 SELECT 学生.*,成绩.* FROM 学生 INNER JOIN 成绩; ON 学生.学号=成绩.学号 (2) 左联接，除满足条件的记录外，左表中不满足条件的记录也出现在结果中 SELECT 学生.*,成绩.* FROM 学生 LEFT JOIN 成绩; ON 学生.学号=成绩.学号 (3) 右联接，除满足条件的记录外，右表中不满足条件的记录也出现在结果中 SELECT 学生.*,成绩.* FROM 学生 RIGHT JOIN 成绩; ON 学生.学号=成绩.学号,第八章 SQL语言、查询与视图,(4) 完全联接，结果包括两个

14、表中的所有记录 SELECT 学生.*,成绩.* FROM 学生 FULL JOIN 成绩; ON 学生.学号=成绩.学号 (5) 用WHERE子句实现，结果同内部联接。 SELECT 学生.*，成绩.* FROM 学生，成绩 WHERE 学生.学号=成绩.学号,第八章 SQL语言、查询与视图,4、使用列函数 SQL的列函数：注意： 列函数中可以使用DISTINCT，表示计算时取消指定列中的重复值。 在COUNT()函数中，“*”与DISTINCT不能同时使用。,第八章 SQL语言、查询与视图,5、查询结果的输出定向：,第八章 SQL语言、查询与视图,8.2 建立查询 利用SQL命令可以方便、

15、迅速地建立查询，但是对初学者来说，还是复杂了些。在Visual FoxPro中，还可以使用查询向导和查询设计器来生成查询文件。 8.2.1 查询向导 根据第七章建立的数据库，使用查询向导建立如下查询：显示“学生”表的学号、姓名及选修课程的成绩，按“学号”升序排序。,第八章 SQL语言、查询与视图,1、打开查询向导 在“文件”菜单中选择“新建”，在弹出的新建对话框中，在文件类型中选择查询，选择向导按钮，弹出对话框，其中列出了3种向导供用户选择，查询向导、交叉表向导、图形向导。选择查询向导，按“确定”按钮，进入查询向导第一步。 查询向导第一步：选择要查询的表及字段。 查询向导第二步：为表建立关系

16、查询向导第三步： 设置联接条件 查询向导第四步：筛选记录,第八章 SQL语言、查询与视图,查询向导第五步：记录排序 查询向导第六步：限制记录查询向导第七步：完成 保存查询：仅保存建立的查询 保存并运行查询：保存查询后立即运行查询 保存查询并在“查询设计器”中修改最后，运行查询,第八章 SQL语言、查询与视图,8.2.2 查询设计器 使用查询设计器的步骤如下： 打开查询设计器 打开(或添加)数据库或自由表 选择字段 设置联接 筛选 排序依据 分组依据 杂项,第八章 SQL语言、查询与视图,1、打开查询设计器 在选择“文件”菜单中选择“新建”，在弹出的“新建”对话框的文件类型中选择“查询”，单击“

17、新建文件”按钮，打开查询设计器，同时还为查询选择表或者视图，单击“添加”按钮添加到查询设计器中。 每添加一个新表，如果与查询设计器中已有的表有相同字段，则系统提示在两表中设置联接条件，建立联接的两表间的联接字段有直线相连。如果还想添加自由表，单击“其他”按钮。 所有的表或视图添加完毕，则可以通过选项卡来定义输出结果，查询设计器中包括字段、联接、筛选、排序依据、分组依据、杂项6个选项卡。 在查询设计器中，可以在建立查询的过程中查看查询结果，单击常用工具栏上的！按钮或在“查询”菜单中选择“运行查询”命令即可。,第八章 SQL语言、查询与视图,2、字段 在该选项卡除了选择源表中的各字段外，还可以为查

18、询添加统计项。 3、联接 如果在创建查询前已经在表之间建立了关系，Visual FoxPro将自动创建相关的联接，默认为内部联接。如果没有，在选择表或视图时，系统会提示建立联接，联接条件有4种类型，建立好联接后，在联接选项卡中显示当前已经建立的联接。 在联接选项卡中可以修改已建立的联接、建立新的联接及删除联接。,第八章 SQL语言、查询与视图,4、筛选 在筛选选项卡可以设置查询条件 。 5、排序依据 在该选项中设置查询结果的输出顺序。 6、分组依据 所谓分组即是将一组类似的记录压缩成一个记录，这样就可以完成基于一组记录的计算。分组还可以与其它函数联合使用，如SUM、AVG、COUNT等。,第八

19、章 SQL语言、查询与视图,8.3 视图 视图是一个定制的虚拟表，其数据可以来源于一个表或多个表，或其他视图。 视图本身并不保存任何数据。 视图可分为本地视图和远程视图。 视图是可更新的，修改视图中的数据时，实际上是直接修改了其数据源中的数据。,第八章 SQL语言、查询与视图,第八章 SQL语言、查询与视图,8.3.1 创建视图 创建视图也有两种方式：视图向导和视图设计器，另外，与查询一样还可以用SQL SELECT语言来创建视图。 一、视图设计器的使用步骤： 打开数据库 打开视图设计器 选择字段 设置联接 筛选 排序依据 分组依据 更新条件 杂项,第八章 SQL语言、查询与视图,二、更新条件

20、选项卡 视图设计器同查询设计器很类似，只是多了一个“更新条件”选项卡，其它选项卡的使用同查询设计器。 1、表 指定视图中可以更新的表，系统默认是“全部表” 。 2、“字段名”列表框 显示可标志为关键字字段或可更新字段的字段名。,第八章 SQL语言、查询与视图,在“字段名”列表框中列出了选定表的所有选定字段，在这些字段名的左面的两列是标记列，其中钥匙列用来确定关键字字段，铅笔列确定可更新字段。 关键字段：若钥匙列中显示“”标记，则表明该字段是关键字段，用鼠标单击字段前的小方块按钮进行设定。 可更新字段：在铅笔列中显示“”标记，则表明该字段是可更新的，也可以通过鼠标单击字段前的小方块按钮进行设定。

21、 重置关键字：单击该按钮，系统自动使用每个表的主索引作为关键字，在钥匙列加上“”标记。 全部更新：选择除了关键字字段以外的所有选定字段进行更新，并对“字段名”列表框中铅笔列有“”标记的字段发送SQL更新。 系统通过关键字使视图和源表相联系。只有指定关键字后，系统才允许选择通过视图来更新源表的字段。源表的主索引字段，在钥匙列中显示“”标记。,第八章 SQL语言、查询与视图,3、发送SQL更新 指定是否将视图中记录的修改传送给源表 4、SQL WHERE 子句 如果建立的系统将在多用户环境中使用，则需设置该选项 关键字段：若源表关键字段已被修改，禁止当前用户更新 关键字和可更新字段：若源表关键字或

22、可更新字段已被修改，禁止当前用户更新 关键字和已修改字段：若源表关键字可当前用户修改的字段，已被其他用户修改，禁止当前用户更新 关键字和时间戳：若源表关键字和时间戳已被其他用户修改，禁止当前用户更新,第八章 SQL语言、查询与视图,5、使用更新 控制视图的更新方式： SQL DELETE 然后INSERT：表示系统先删除源表数据，再插入视图的更新数据。 SQL UPDATE：默认选项，直接用视图的更新数据替换源表数据。,第八章 SQL语言、查询与视图,8.3.2 使用视图 视图类似于表，可以用以下几种方式进行浏览： 使用USE命令打开视图，在浏览窗口中显示视图记录 打开数据库，选择视图并浏览 在“数据工作期“窗口中显示打开的视图 例8.18 打开视图v1 USE V1 & 系统提示找不到表v1.dbf OPEN DATABASE STUDENT &打开数据库STUDENT USE V1 & 此时，可以使用LIST命令显示视图V1的内容，也可 & 以通过“显示”菜单的浏览命令来显示 BROWSE & 打开浏览窗口浏览视图V1,第八章结束,