第五章SQL语言、查询与视图(新教材)

1、1目 录上一页下一页退 出目 录上一页下一页退 出2本本 章章 要要 点点5.1SQL语言语言 5.1.1 数据定义语言数据定义语言 5.1.2 数据操纵语言数据操纵语言 5.1.3 SQL查询查询 5.1.4 SQL查询结果的输出与处理查询结果的输出与处理5.2 使用查询设计器使用查询设计器5.3 视图视图目 录上一页下一页退 出3 结构化查询语言（结构化查询语言（Structured Query Language，SQL） 1综合统一综合统一 SQL语言集数据定义（DDL）、数据操纵（DML）、数据控制（DCL）的功能于一体，语言风格统一，可以独立完成数据库的全部操作，包括定义关系模式、录

2、入数据及建立数据库、查询、更新、维护数据、数据库的重新构造、数据库安全性等一系列操作的要求，为数据库应用系统开发者提供了良好的环境。 2高度非过程化高度非过程化 用户只需提出做什么，而不必指明怎么做。 3面向集合的操作方式面向集合的操作方式 查找结果、插入、删除、更新都可以是记录的集合。4以同一种语法结构提供两种使用方式以同一种语法结构提供两种使用方式 自含式语言（交互方式），嵌入式语言，嵌入到高级语言（如C、FORTRAN、PL/I）程序中 5语言简洁，易学易用语言简洁，易学易用5.1 SQL语言语言目 录上一页下一页退 出4数据定义语言DDL用于执行数据定义的操作，如创建或删除表、索引和视

3、图之类的对象。由CREATE、DROP、ALTER命令组成，完成数据库对象的建立（CREATE）、删除（DROP）和修改（ALTER）。1. 定义（创建）表定义（创建）表【格式】CREATE TABLE （，）NULL | NOT NULL，）【功能】定义（也称创建）一个表。5.1.1 数据定义语言数据定义语言【例】使用命令建立数据表。【例】使用命令建立数据表。 CREATE TABLE 进货信息 ( 订单编号 C(10),商品编号 C(6),进货时间 D,进货单价 N(8,2),数量 N(5),到货标志 L,经办人 C(8) )目 录上一页下一页退 出52修改表结构修改表结构 【格式】【格式

4、】ALTER TABLE ADD （，）NULL | NOT NULLDROP 【功能】修改表结构。【说明】ADD子句用于增加指定表的字段变量名、数据类型、宽度；DROP子句用于删除指定的字段。【例】为“进货信息”表添加“经销商”字段。ALTER TABLE 进货信息 ADD 经销商 C(20)【例】将“进货信息”表中“经销商”字段的宽度改为28。ALTER TABLE 进货信息 ALTER 经销商 C(28)【例】删除“进货信息”表中“经销商”字段。ALTER TABLE 进货信息 DROP 经销商目 录上一页下一页退 出6【格式】DELETE DATABASE 【功能】删除指定数据库的结构

5、，但保留原库中的表，其变成自由表。4. 删除表删除表【格式】DROP TALBE 【功能】删除指定表的结构和内容（包括在此表上建立的索引）。【说明】如果只是想删除一个表中的所有记录，则应使用DELETE语句。3. 删除数据库删除数据库目 录上一页下一页退 出7 数据操纵语言一般由INSERT（插入）、DELETE（删除）、UPDATE（更新）等组成。5.1.2 数据操纵语言数据操纵语言1插入记录命令插入记录命令格式：INSERT INTO(,) VALUES (,)功能：在指定的表尾添加一条新记录，其值为VALUES后面表达式的值。【例】向“进货信息”表中添加记录。INSERT INTO 进货

6、信息 VALUES (0707000101,160007,2007/07/02, 2.6,4,.T., admin)目 录上一页下一页退 出82删除记录命令删除记录命令格式：DELETE FROM WHERE 功能：从指定表中，根据指定的条件逻辑删除记录。【例】将“进货信息信息”表中所有商品编号为“160007”的记录逻辑删除。DELETE FROM 进货信息 WHERE 商品编号=160007“3更新记录命令更新记录命令格式：UPDATE SET=,= WHERE功能：用指定的新值更新记录。【例】将“进货信息”表中所有2007年以后进货的商品的单价提高5%。UPDATE 进货信息 SET 进

7、货单价=进货单价*(1+0.05) WHERE 进货时间=2007/01/01目 录上一页下一页退 出95.1.3 SQL中的数据查询语句中的数据查询语句 数据查询是数据库的核心操作。在SQL语言中，查询语言中有一条查询命令，即SELECT语句。（使用SELECT命令事先不必打开表文件）格式：格式：SELECT ALL|DISTINCT . AS ,. AS FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC, ASC|DESC 说明：（1）ALL：返回查询结果的所有行。（2）DISTINCT：去除查询结果中重复的行。（3）别名：当选择多个数据库表中的

8、字段时，可使用别名来区分不同的数据表。（4）：字段名、表达式或函数。（5）AS ：在输出结果中，如果不希望使用字段名，可以根据要求设置一个名称。（6）FROM ：要查询的表文件名，可以包含多个表，各表间用逗号分开。（7）WHERE ：指定多个表的连接条件和查询条件。（8）GROUP BY 指定分组查询的表达式。（9）HAVING ：指定分组筛选的条件，总是跟在GROUP BY子句之后，不可以单独使用。（10）ORDER BY ：指定查询结果按哪个字段排序。（11）ASC：指定的排序项按升序排列。 （12）DESC：指定的排序项按降序排列。目 录上一页下一页退 出101简单查询（简单查询（无条件

9、查询） 由SELECT和FROM短语构成无条件查询。【格式】SELECT FROM 【例 】查询spxx.dbf中所有商品信息。 SELECT * FROM spxx【例】查询spxx.dbf中所有商品的名称、类别、零售价。 SELECT 商品名称,类别,零售价 FROM spxx可以用函数 sum()、avg()、cnt()、max()、min()【例】查询jhxx.dbf中商品的进货总数、平均价格。 SELECT SUM(进货数量) AS 进货总数,AVG(进货单价) AS 平均价格 FROM jhxx目 录上一页下一页退 出112. 带条件（带条件（WHERE）的查询）的查询【格式】SE

10、LECT FROM WHERE 【功能】从一个表中查询满足条件的数据。【说明】由一系列用AND 或 OR 连接的条件表达式组成.SQL支持的关系运算符如下：、！、。目 录上一页下一页退 出12ALL 满足子查询中所有值的记录用法：ALL()ANY 字段中的内容满足一个条件就为真 用法：ANY() BETWEEN 的内容在指定范围内 用法：BETWEENAND EXISTS 测试子查询中查询结果是否为空。若为空，则返回.F. 用法：EXISTS() IN 字段内容是结果集合或者子查询中的 用法：IN或者IN() LIKE 对字符型数据进行字符串比较，提供两种通配符，即下画线“_”和“”用法：LI

11、KESOME 满足集合中的某一个值，功能与用法等同于ANY 用法： SOME() 谓词说明目 录上一页下一页退 出13【例】查询spxx.dbf中日用品的商品名称。 SELECT 商品名称 FROM spxx WHERE 类别=日用品【例】查询spxx.dbf中非办公用品类商品的名称、厂商编号、零售价。 SELECT 商品名称,厂商编号,零售价 FROM spxx WHERE 类别办公用品“【例】查询jhxx.dbf中进货数量在1020之间的商品的进货单号、商品编号、进货数量。 SELECT 进货单号,商品编号,进货数量 FROM jhxx WHERE 进货数量 BETWEEN 10 AND

12、20目 录上一页下一页退 出14【例】列出办公用品和日用品的商品名称、厂商编号、零售价和类别。 SELECT 商品名称,厂商编号,零售价,类别 FROM spxx WHERE 类别 IN (办公用品,日用品)【例】列出所有联系人姓张的供应商的信息。 SELECT * FROM gys WHERE 联系人 LIKE 张%“目 录上一页下一页退 出153嵌套查询嵌套查询 在SQL语句中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。【例】列出大连宏华经贸有限公司的商品名称。 SELECT 商

13、品名称 FROM spxx WHERE 厂商编号=(SELECT 厂商编号 FROM gys WHERE 厂商名称=大连宏华经贸有限公司)【例】列出饮料类厂商的名称。 SELECT 厂商名称 FROM gys WHERE 厂商编号 IN (SELECT 厂商编号 FROM spxx WHERE 类别=饮料)目 录上一页下一页退 出16【例】列出办公用品类中零售价比饮料类的最低零售价高的商品的信息。 SELECT * FROM spxx WHERE 类别=办公用品 AND 零售价 ANY (SELECT 零售价 FROM spxx WHERE 类别=饮料)【例】列出日用品类中零售价比饮料类和调味

14、品类中的最高零售价还要高的商品的信息。 SELECT * FROM spxx WHERE 类别=日用品 AND 零售价 ALL (SELECT 零售价 FROM spxx WHERE 类别=饮料 OR 类别=调味品)目 录上一页下一页退 出174多表查询多表查询数据库是由多个相关联的表组成的，经常需要从多个表中提取信息。SELECT命令允许对多表进行连接操作，连接的条件要在WHERE子句中指出。【例】查询已到货的所有厂商所进商品的商品名称和进货时间。 SELECT 厂商名称,商品名称,进货时间 FROM spxx,jhxx,gys WHERE spxx.商品编号=jhxx.商品编号 AND s

15、pxx.厂商编号=gys.厂商编号AND 到货标志=.T.目 录上一页下一页退 出181排序输出排序输出配合子句: ORDER BYASC | DESC，ASC | DESC说明：排序选项可以是字段名，也可以是数字。字段名必须是主SELECT子句的选项，数字是表的列序号，第1列为1。【例】查询所有商品的销售额、厂商名称，并按销售额由高到低排列。 SELECT 商品名称,销售数量*销售单价 AS 销售额排行榜,厂商名称 FROM spxx,chmx,gys WHERE spxx.商品编号=chmx.商品编号 AND spxx.厂商编号=gys.厂商编号 ORDER BY 销售额排行榜 DESC

16、【例】查询所有商品的商品编号、商品名称和销售数量，并按销售数量由低到高排列。 SELECT spxx.商品编号,商品名称,销售数量 FROM chmx,spxx WHERE spxx.商品编号=chmx.商品编号 ORDER BY 35.1.4 SQL查询结果的输出与处理目 录上一页下一页退 出192分组统计与筛选分组统计与筛选配合子句: GROUP BY , HAVING 说明：HAVING子句与GROUP BY子句连用【例】查询各类商品的平均零售价。SELECT 类别,avg(零售价) FROM spxx GROUP BY 类别【例】查询各商品的生产厂家、总销售量。SELECT 厂商名称,

17、sum(销售数量) as 总销售量 FROM spxx,chmx,gys WHERE spxx.厂商编号=gys.厂商编号 AND spxx.商品编号=chmx.商品编号 GROUP BY gys.厂商编号【例】查询至少有两种商品的每个厂商的商品平均零售价。SELECT 厂商编号,CNT(*),AVG(零售价) FROM spxx GROUP BY 厂商编号 HAVING COUNT(*)=2目 录上一页下一页退 出203显示部分结果 有时只需要满足条件的前几个记录，使用 TOP ;数值表达式 PRECENT,说明显示结果中前百分之几的记录。 【例】查询零售价最高的5种商品的信息。SELECT

18、 * TOP 5 FROM spxx ORDER BY 零售价 DESCSELECT * TOP 5 PERCENT FROM spxx ORDER BY 零售价 DESC目 录上一页下一页退 出214重定向输出配合子句: INTOTO FILEADDITIVE|TO PRINTER包含以下包含以下3部分内容部分内容:ARRAY：将查询结果存到指定数组名的内存变量数组中；CURSOR：将输出结果存到一个临时表，该文件为只读，一旦被关闭就被删除；DBF| TABLE：将结果存到一个表。【例】查询销售量达到或超过10个以上的商品的相关信息，并将结果存放在临时文件mytmp中。SELECT 商品名称

19、,类别,零售价,销售单价,销售数量 FROM spxx,chmx WHERE spxx.商品编号=chmx.商品编号 AND 销售数量=10 INTO CURSOR mytmp 该命令执行后，会产生临时表mytmp，若再执行如下命令：SELECT * FROM mytmp目 录上一页下一页退 出22【例】将查询结果存放在表文件中。SELECT * TOP 5 FROM spxx ORDER BY 零售价 DESC INTO TABLE high5 SELECT * FROM high5【例】将查询结果存放在表文件中。SELECT 商品名称,销售数量,厂商编号 FROM spxx,chmx WH

20、ERE spxx.商品编号=chmx.商品编号 INTO CURSOR testSELECT 厂商名称,商品名称,SUM(销售数量) as 总销售量 FROM test,gys WHERE test.厂商编号=gys.厂商编号 GROUP BY gys.厂商编号 INTO TABLE test27SELECT * FROM test27目 录上一页下一页退 出235.2 使用查询设计器 “查询设计器”实际上就是SELECT命令程序的交互式设计操作窗口。可以用扩展名为qpr的文件保存在磁盘上, 在命令窗口输入： DO 文件.qpr 执行查询。5.2.1 查询设计器简介1“查询设计器”的启动 从“

21、项目管理器”或“文件”菜单中，都可以启动“查询设计器”。 2“查询设计器”中的选项卡5.2.2 查询结果输出目 录上一页下一页退 出245.3 视 图5.3.1 视图的概念视图（View）是在数据库表的基础上创建的一种虚拟表,其数据是从已有的数据库表或其他视图中提取的，这些数据在数据库中并不实际存储，仅在数据词典中存储视图的定义。视图有本地视图和远程视图两种类型。视图有如下特点: （1）视图可以提供附加的安全层（2）视图可以隐蔽数据的复杂性 （3）视图有助于命名简洁（4）视图带来更改灵活性（5）通过视图更新数据库目 录上一页下一页退 出255.3.2 视图设计器概述 利用 “视图设计器” 可以可视化地创建视图。其操作同查询设计器 , 区别是视图设计器增加了“更新条件”选项卡。 “更新条件更新条件”选项卡中包括以下几个部分。选项卡中包括以下几个部分。（1）表表示视图所基于的表。（2）关键字表示当前视图的关键字字段，单击复选框，出现“”符号，表示选中。（3）更