关系数据库标准语言SQL(邓).ppt

第5章 关系数据库标准语言SQL,5.1 SQL概述 5.2 SQL的查询功能 5.3 SQL的操作功能 5.4 SQL的定义功能 5.5 SQL的定义视图,SQLStructured Query Language：结构化查询语言，是一种专门访问数据库的语言。 按照美国国家标准协会（ANSI）的规定，SQL被作为关系数据库的标准语言。 几乎所有的关系数据库都支持SQL，因此Visual FoxPro也支持SQL 。,.1 SQL概述,5.1 .1 SQL语言的特点,SQL是Structured Query Language（结构化查询语言）的缩写。 SQL语言的主要特点如下。 一体化语言。 高度非过程化。 语言简洁。下表给出了分类的命令动词。 统一的语法结构对待不同的工作方式。,5.1.2 SQL语言的基本概念,一、SQL语言的模式 SQL语言支持关系数据库三级模式结构，其中外模式对应于视图（View）和部分基本表（Base Table），模式对应于基本表，内模式对应于存储文件。 基本表是本身独立存在的表，在SQL中一个关系就对应一个表。 存储文件的逻辑结构组成了内模式。存储文件的物理结构是任意的。 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 二、SQL语言的使用 SQL语言可以直接以命令方式使用，也可以程序方式使用，采用了后者。,5.2 SQL的查询功能,SQL语法格式 SELECT-SQL的语法格式如下： SELECT 字段列表 FROM 表列表 WHERE 条件表达式 GROUP BY . HAVING 条件表达式 UNION . ORDER BY.,5.2 SQL的查询功能,SQL语法格式 1. SELECT子句 SELECT子句用来指定查询结果中的数据。 2. FROM子句 FROM子句用来指定查询的表与连接类型。 3. WHERE子句 用来指定查询的条件。 4. 用于对查询结果进行分组 5. HAVING:必须跟随GROUP BY短语使用,用来限定分组必须满足的条件 6. ORDER BY:用来对查询结果进行排序.,本书所举的例子均参照图5-1图5-4所示.,图 5-1供应商,图 5-2订购单,图 5-3 职工,图 5-4 仓库,5.2 SQL的查询功能,5.2.1 简单查询 【例5-1】从职工中检索所有职工的工资。 用SQL语言描述为： SELECT 工资 FROM 职工 检索结果为： 2220 2210 2250 2230 2250 如果需要去掉其中的重复值，可以加上DISTINCT短语： SELECT DISTINCT 工资 FROM 职工,5.2 SQL的查询功能,5.2.1 简单查询 【例5-2】检索仓库表中的所有元组。 可以使用通配符*表示所有属性（即字段）： SELECT * FROM 仓库 该命令等价于： SELECT 仓库号 , 面积 , FROM 仓库 检索结果为：,5.2 SQL的查询功能,5.2.1 简单查询 【例5-3】检索工资高于2220元的职工号。 SELECT 职工号 FROM 职工 WHERE 工资 2220 查询结果为：A4 A6 A7 【例5-4】检索哪些仓库有工资高于2220元的职工 SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资2220 查询结果为：SH1 SH2 SH3 【例5-5】 检索在仓库SH1或SH2工作，且工资低于2250元的职工号。 SELECT 职工号 FROM 职工 ; WHERE 工资 2250 AND (仓库号=“SH1“ OR 仓库号=“SH2“) 查询结果为： A1 A3,5.2 SQL的查询功能,5.2.2 简单连接查询 【例5-6】检索工资高于2230元的职工号和他们所在的城市。 SELECT 职工号 , 城市 FROM 职工 , 仓库 ; WHERE ( 工资 2230) AND ( 职工.仓库号 = 仓库.仓库号 ) 查询结果为： A4 上海 A3 北京 【例5-7】查询面积大于1400的仓库职工号以及这些职工所在的城市。 SELECT 职工号 , 城市 FROM 职工, 仓库; WHERE ( 面积 1400) AND ( 职工.仓库号 = 仓库.仓库号 ) 查询结果为： A1 上海 A4 上海,5.2 SQL的查询功能,5.2.3 嵌套查询 【例5-8】查询至少有一个仓库的职工工资为2250元的城市。 SELECT 城市 FROM 仓库 WHERE 仓库号 IN ; ( SELECT 仓库号 FROM 职工 WHERE 工资 = 2250 ) 查询结果为： 北京 上海,5.2 SQL的查询功能,5.2.3 嵌套查询 【例5-9】查询所有职工的工资都高于2210元的仓库信息。 SQL命令为： SELECT * FROM 仓库 WHERE 仓库号 NOT IN ; ( SELECT 仓库号 FROM 职工 WHERE 工资 = 2210 ) 查询结果为： SH2 上海 1500 SH3 广州 1200 SH4 武汉 1400 SQL命令为： SELECT * FROM 仓库 WHERE 仓库号 NOT IN ; ( SELECT 仓库号 FROM 职工 WHERE 工资 = 2210 ) ; AND 仓库号 IN ( SELECT 仓库号 FROM 职工),【例5-10】找出和职工A4挣同样工资的所有工资。 SELECT 职工号 FROM 职工 WHERE 工资=； （SELECT 工资 FROM 职工 WHERE 职工号=“A4”） 结果是： A4 A7,5.2 SQL的查询功能,5.2.4 排序 格式为： ORDER BY 关键字表达式 ASC | DESC ,关键字表达式 ASC | DESC . 【例5-11】按职工的工资升序检索出全部职工信息。 SELECT * FROM 职工 ORDER BY 工资 查询结果为： SH1 A3 2210 SH2 A1 2220 SH3 A6 2230 SH2 A4 2250 SH1 A7 2250 如果需要按降序排序，只要加上DESC选项即可： SELECT * FROM 职工 ORDER BY 工资 DESC,5.2 SQL的查询功能,5.2.4 排序 【例5-12】先按仓库号排序，再按工资升序排序并输出全部职工信息。 SELECT * FROM 职工 ORDER BY 仓库号 , 工资 查询结果为： SH1 A3 2210 SH1 A7 2250 SH2 A1 2220 SH2 A4 2250 SH3 A6 2230,5.2 SQL的查询功能,5.2.5 简单的计算查询 【例5-13】找出供应商所在地的数目。 SELECT COUNT( DISTINCT 地址) FROM 供应商 结果为： 3 命令为： SELECT COUNT ( * ) FROM 供应商 【例5-14】求支付的工资总数。 SELECT SUM ( 工资 ) FROM 职工 结果为： 11160,5.2 SQL的查询功能,5.2.5 简单的计算查询 【例5-16】求所有职工的工资都高于2210元的仓库的平均面积。 该例中，应该排除没有职工的仓库，命令为： SELECT AVG ( 面积 ) FROM 仓库 WHERE 仓库号 NOT IN ; ( SELECT 仓库号 FROM 职工 WHERE 工资 = 2210 ) 结果为： 1366.67 【例5-17】求SH2仓库职工的最高工资。 SELECT MAX ( 工资 ) FROM 职工 WHERE 仓库号=“SH2” 结果为： 2250,5.2 SQL的查询功能,5.2.6 分组与计算查询 GROUP BY子句的格式为： GROUP BY 组表达式,组表达式. HAVING 筛选条件 【例5-18】求每个仓库的职工的平均工资。 在该查询中，首先要按仓库号属性进行分组，然后再计算每个仓库的平均工资。 SELECT 仓库号 , AVG ( 工资 ) FROM 职工 GROUP BY 仓库号 结果为： SH1 2230 SH2 2235 SH3 2230,5.2 SQL的查询功能,5.2.6 分组与计算查询 【例5-19】求至少有两个职工的各个仓库的平均工资。 SELECT 仓库号 , COUNT ( * ) , AVG ( 工资 ) FROM 职工; GROUP BY 仓库号 HAVING COUNT ( * ) =2 结果为： SH1 2 2230 SH2 2 2235,5.2 SQL的查询功能,5.2.7 空值查询 【例5-20】找出尚未确定供应商的订购单 SELECT * FROM 订购单 WHERE 供应商号 IS NULL 结果是： A6 NULL OR77 NULL A1 NULL OR80 NULL A3 NULL OR90 NULL 【例5-21】找出已经确定供应商的订购单信息 SELECT * FROM 订购单 WHERE 供应商号 IS NO NULL,5.2 SQL的查询功能,5.2.8 几个特殊的运算符 BETWEEN AND 【例】查询工资在2220元到2240元范围内的职工信息。 SQL命令为： SELECT * FROM 职工 WHERE 工资 BETWEEN 2220 AND 2240 查询结果为： SH2 A01 2220 SH3 A03 2230 在该命令中，“BETWEEN 2220 AND 2240”等价于： ( 工资 = 2220 ) AND ( 工资 = 2240 ),5.2 SQL的查询功能,5.2.8 几个特殊的运算符 LIKE 【例】从供应商关系中检查全部公司的信息（不要工厂或其他供应商的信息）。 SQL命令为： SELECT * FROM 供应商 WHERE 供应商名 LIKE “*公司“,5.2 SQL的查询功能,5.2.8 几个特殊的运算符 !=，NOT SELECT * FROM 供应商 WHERE 地址 != “北京“ 查询结果为： S3 新悦电子厂 西安 S6 迎郑电子厂 郑州 也可以用NOT运算符写出等价的SQL命令： SELECT * FROM 供应商 WHERE NOT ( 地址 = “郑州“ ),5.2 SQL的查询功能,5.2.8 集合的并运算 SQL支持集合的并运算，可以将两个SELECT语句的查询结果通过并运算合并成一个查询结果。 例如，下面语句的结果是城市为北京和上海的仓库信息。 SELECT * FROM 仓库 WHERE 城市 = “北京“ ; UNION ; SELECT * FROM 仓库 WHERE 城市 = “上海“,5.3 操作功能,数据插入功能 数据更新功能 数据删除功能,数据插入功能,Vfp支持两种插入命令格式 标准格式 特殊格式,标准格式,格式：INSERT INTO ( , ) VALUES ( ,) 例 向“学生表”插入一条新纪录。 INSERT INTO 学生表 (XH,XM,XB) ; VALUES (“0603009”,”章玉婷”,.F.),特殊格式,格式：INSERT INTO FROM ARRAY 数组名|FROM MEMVAR,数据更新功能,格式：UPDATE SET =, = ) WHERE 例 将“学生表”中女生的奖学金增加100元。 UPDATE 学生表 SET JXJ=JXJ+100 WHERE XB=.F.,数据删除功能,格式：DELETE FROM WHERE 例 删除“学生表”中“章玉婷”的记录。 DELETE FROM 学生表 WHERE XM=”章玉婷”,5-4 定义功能,表的定义 表的删除 修改表结构,表的定义,格式：CREATE TABLE （列级完整性约束条件 ,）,表的定义,例：用SQL CREATE 命令建立职工A表 CREATE TABLE 职工A( 仓库号 C(5), 职工号 C(5)PRIMARY KEY, 工资 I CHECK (工资=1000 AND工资=5000 ) ERROR “工资范围在1000-5000！”DEFAULT 2200, FOREIGN KEY 仓库号 TAG 仓库号 REFERENCES 仓库A),表的删除,格式：DROP TABLE 例 删除“学生表” DROP TABLE 学生表,修改表结构,SQL中修改表结构的命令是ALTER TABLE，该命令有3中格式 格式1： ALTER TABLE ADD|ALTER COLUMN(,) NULL|NOT NULL CHECKERROR DEFAULT PRIMARY KEY|UNIQUE PEFERENCES TAG NOCPTRANS,修改表结构,例 为订购单A表增加一个货币类型的总金额字段 ALTER TABLE 订购单A ADD总金额 Y CHECK 总金额0 ERROR “总金额应该大于0！”,修改表结构,格式2：ALEER TABLE ALTER COLUMN NULL|NOT NULL DEFAULT SET CHECKERROR DROP DEFAULT DROP CHECK,修改表结构,例 删除总金额字段的有效性规则 ALTER TABLE 订购单1 ALTER 总金额DROP CHECK,修改表结构,格式3：ALEER TABLE ALTER COLUMN SET CHECKERROR DROP CHECK ADD PRIMARY KEY TAGFOR DROP PRIMARY KEY ADD UNIQE TAGFOR DROP UNIQE TAG ADD FOREIGN KEY TAGFOR REFERENCES TAG ADD FOREIGN KEY TAGSAVE REFERENCES COLUMN TO NOVALIDATE,修改表结构,例 删除订购单A表中的金额字段 ALTER TABLE 订购单A DROP COLUMN 金额,5-5 定义视图,查询和视图都可以进行数据表的检索，而且两者的建立方法也基本相同。但是它们之间也存在差异： (1)对数据库执行查询的结果可以存储成多种数据格式，如数据表、图表、报表等，而根据数据库建立的视图只能是一个虚拟表，但可以当作数据表来使用。 (2)查询的数据仅供查看，并不能修改、回存，而视图的数据则可修改并且回存到数据表中。 (3)查询的数据来源仅限于VFP 的数据表，而视图的数据来源除了VFP 的数据表外，还可以是视图、远程服务器上的数据表、VFP之外的数据表。 (4)视图必须属于某一个数据库，查询则不必。,5-5 定义视图,格式：CREATE VIEW (,) AS WITH CHECK OPTION,从单个表派生出的视图,例：建立视图向用户提供职工号、职工所工作的仓库号的信息。 CREAT VIEW 视图1 AS; SE