关系数据库标准语言SQL2.ppt

第六章 关系数据库标准语言SQL,6.1 SQL语言的概述 SQL是结构化查询语言，它包括： 查询 数据定义 数据操纵 数据控制 SQL已经成为关系数据库的标准数据语言，掌握SQL语法可以很方便地对数据进行管理。,SQL查询的特点： 一体化语言。SQL提供的功能可以实现数据库生命周期中的全部活动，包括： 定义数据库和表的结构 对表中的数据的录入、修改、删除、查询和维护 数据库重构、数据库安全性控制 2）高度非过程化。 3）语言简洁。 4）统一的语法结构对待不同的工作方式。 命令窗口以人机交互方式使用， 程序设计中以程序方式使用。,6.3 SQL的查询功能,常用的命令格式 SELECT DISTINCTTOP INTO TBALE WHERE AND GROUP BY HAVING ORDER BY DESCASC (ZW 12.3),说明: 基本子句：SELECT-FROM-WHERE 操作子句：ORDER、GROUP等子句 SELECT子句：指定查询结果中的数据。 FROM子句：指定查询的表。 WHERE子句：指定查询的条件。,6.3.1 基本查询,所谓基本查询：指无条件查询。 由SELECT、FROM构成无条件查询. 1、检索所有值。 例：显示职工表中的所有信息供应商表.dbf 职工表.dbf SELE * FROM 职工 “*”是通配符，表示所有属性，即字段。这里的命令等同于： SELECT 职工号，仓库号，工资 FROM 职工 表,2、去掉重复值的检索 使用DISTINCT短语:去掉重值 例1：显示职工表中的所有工资信息，要求不显示重复值。 SELE DISTINCT 工资 FROM 职工表 例2：列出所有学生表中学生的姓名与年龄。 使用 SELE 姓名，YEAR(DATE()-YEAR(出生日期) AS 年龄 from student 注：AS（列名）用于指定输出时使用的列标 题，可以不同于字段名。,6.3.2 带条件的查询,1、使用WHERE短语的简单查询 查询基于单个表，可有简单的查询条件 由SELECT、FROM和WHERE短语构成条件查询。 例1：查询职工表中所有仓库号为“WH1”的信息。 SELE * FROM 职工表 WHERE 仓库号=“WH1”,例2：给出在仓库“WH1”或“WH2”工作，并且工资少于1250元的职工号。 SELECT 职工号 FROM 职工表 WHERE 工资1250 AND ； (仓库号=“WH1” OR 仓库号=“WH2”) “;”,表示续行号. 小结：上面的例子在FROM之后只指定了一个关系。12.1,2、使用WHERE短语的简单联接查询 联接查询是基于多个关系的查询，查询所要求的结果出自多个关系。在语句中要指定联接的条件，即多个关系的相同属性: 表1.字段名 =表2.字段名 例3：找出工资多于1230元的职工号和他们所在的城市。 SELE 职工号，城市 FROM 职工表，仓库表； WHERE （工资1230） AND (职工表.仓库号=仓库表.仓库号) 例4：找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市。 SELECT 职工号，城市 FROM 职工表，仓库表 WHERE ； 面积400 AND (职工表.仓库号=仓库表.仓库号),注意： 1、这里的“职工.仓库号=仓库.仓库号”是联接条件。 2、当FROM之后的多个关系中含有相同的属性时，这时必须用关系前缀“.”直接指明属性所属的关系。,6.3.3、嵌套查询 嵌套查询：指在SELE-FROM-WHERE查询块内部再嵌入另 一个查询块，称为子查询。 嵌套查询的运算：先计算子查询，然后将子查询的结果传给外层查询使用。 查询所要求的结果：出自一个关系,但相关的条件却涉及多个 关系。 常用内层与外层嵌套的语句有: IN语句、ALL语句、ANY 语句 IN语句：IN（属于）或NOT IN（不属于） 即查询字段所需的值属于子查询中的结果。 用法： IN (),比如:查询职工E3所在仓库的城市. 分析:此查询确定的内容：涉及两个表，即职工表和仓库表 子查询为：在职工表中找出职工E3所在的仓库号（内层查询） 查询的条件为:职工号=“E3” 查询的结果为:仓库号. wh1 命令：SELE 仓库号 FROM 职工表 WHERE 职工号=“E3” 外层查询：在仓库表中找出仓库号属于子查询仓库号所对的城市。 内外层的所属关系: 仓库号 IN 命令：SELE 城市 FROM 仓库表 WHERE 仓库号 IN 此查询的完整的命令： SELE 城市 FROM 仓库表 WHERE 仓库号 IN； （SELE 仓库号 FROM 职工表 WHERE 职工号=“E3”）,例4：哪些城市至少一个仓库的职工工资为1250元？ 分析：此题为先在内查询中找出工资为1250元的仓库号，然后在以此结果到仓库表中找出对应的城市。 内层查询： 查询的结果为：职工表中的仓库号 查询的条件为：工资=1250 （WH1、WH2） 命令：SELE 仓库号 FROM 职工表 WHERE 工资=1250 外层查询： 查询的结果为：仓库表的城市 查询的条件为：仓库号属于内层查询的所给的仓库号 命令：SELE 城市 FROM 仓库表 WHERE 仓库号 IN,此查询的完整的命令： SELECT 城市 FROM 仓库表 WHERE 仓库号 IN； （SELE 仓库号 FROM 职工表 WHERE 工资=1250） （北京、上海）,ALL语句： 功能:满足子查询结果的所有值 用法: ALL() ALL的语义为:大于子查询结果中的所有值（最高值）. 例5：检索有职工的工资大于或等于WH1仓库中所有职工工资的仓库号。 分析：查询涉及的表只有职工表. 内层查询：从职工表中查询出仓库号为WH1的所有工资 查询的信息：工资,数据出于职工表. 查询的条件：仓库号为WH1 命令:SELE 工资 FROM 职工表 WHERE 仓库号=“WH1” (1210、1250）,外层查询：查询出高于内层查询所提供的工资的所有仓库号， 用量词ALL,且写为：工资=ALL 同时要去掉重复的仓库号. 命令: SELE DISTINCT 仓库号 FROM 职工 WHERE 工资=ALL 此查询的完整命令: SELE DISTINCT 仓库号 FROM f:vfp讲义职工表； WHERE 工资=ALL ； (SELE 工资 FROM f:vfp讲义职工 WHERE 仓库号=”WH1”) 注：这里只取工资大于或等于1250的仓库号,ANY语句 功能：满足子查询结果的任何一个值 用法: ANY() ANY的语义为:大于子查询结果中的某个值(最低值）. 例: 检索所有职工的工资大于或等于WH1仓库中任何一名职工工资的仓库号 分析：查询涉及的表为职工表 查询的信息: 职工表中仓库号 内层查询: 从职工表中找出仓库号为WH1的职工工资 外层查询: 找出大于或等于WH1中任何一工资值的仓库号 即工资=ANY SELE DISTINCT 仓库号 FROM 职工表 WHERE 工资=ANY; (SELE 工资 FROM 职工表 WHERE 仓库号=”WH1”),6.3.4 查询结果处理 1、排序输出（ORDER BY） 使用ORDER BY短语，升序用ASC（可省略）、降序用DESC 例：按职工的工资值升序检索出全部职工信息. SELE * FROM 职工表 ORDER BY 工资 如果降序,则加上DESC SELE * FROM 职工表 ORDER BY 工资 DESC,2、分组与计算查询 使用GROUP BY HAVING Having是分组条件，一般放在GROUP BY之后。 GROUP BY子句:将查询结果按一列或多列值分组,列值相等的 为一组. HAVING短语:按一定条件对分组后的数据进行筛选. 例1:求每个仓库的职工的平均工资.(按仓库分组) SELE 仓库号,AVG(工资) FROM 职工表 GROUP BY 仓库号 例2:求至少有两个职工的每个仓库的平均工资 SELE 仓库号,AVG(工资) AS 平均工资 FROM 职工表 GROUP BY 仓库号 HAVING COUNT(*)=2 注意：限制条件时一般用HAVING COUNT（*）=N表示.(12.8),3、重定向输出 INTO或TO 子句,表示查询结果可以重定输出方向. 1) 使用短语INTO DBF/TABLE存入永久表. 例:将查询到的每个仓库的职工的平均工资存放到表BB中 SELE 仓库号,AVG(工资) AS “平均工资” FROM 职工表 GROUP BY 仓库号 HAVING COUNT(*)=2; INTO TABLE BB 2) 使用短语INTO CURSOR存入临时表. 例:将查询到的职工存放在临时表DBF文件TMP中 SELE * FROM 职工表 INTO CURSOR TMP 3) 使用短语TO FILE存入文本文件 SELE * FROM 职工表 TO FILE ZKB,6.3.5 显示部分结果 1、只显示前几项记录 使用TOP短语 格式：TOP 例：显示工资最高的三位职工的信息 SELE * TOP 3 FROM 职工表 ORDER BY 工资 DESC 注意：显示前几个数，应按要显示的字段排序。 2、显示工资最低的那30%职工的信息 使用PERCENT短语 格式: TOP PERCENT SELE * TOP 30 PERCENT FROM 职工表 ORDER BY 工资,复习上节课内容: 1基本查询: SELE FROM 2带条件查询:SELE FROM WHERE 3连接查询:SELE FROM ; WHERE AND 表1.字段名=表2.字段名 4嵌套查询: 所属值的查询: WHERE IN ALL语句: 满足子查询的所有值的查询 ANY语句:满足子查询的任何一个值的查询 5排序查询:ORDER BY DESC ASC 6分组查询:GROUP BY HAVING 7前几名的查询: TOP 后带ORDER BY子句进行排序 8显示百分的查询:PERCENT 9查询的去向: 存于表中:INTO TABLE /DBF 存于临时表中:INTO CURSOR 存于文本文件:TO FILE 下面介绍利用SQL对表中数据进行运算的操作.,6.3.6 几个特殊运算符 特殊运算符： BETWEENAND.表示值在什么范围。 LIKE表示字符串相匹配，后面使用如下通配符: “*“或“%“表示0个或多个字符， “？”表示任何一个字符。 使用BETWEENAND. 例1:检索出工资在1220元到1240元范围内的职工信息. SELECT * FROM 职工表 WHERE 工资 BETWEEN； 1220 AND 1240,例2：找出工资不在1220元和1240元之间的全部职工信息，用命令： SELECT * FROM 职工表 WHERE 工资NOT BETWEEN 1220 AND 1240 使用LIKE 例3：从供应商关系中检索出全部公司的信息，不要工厂或其他供应商的信息。 分析：这是一个检索全部公司的查询，应使用LIKE运算符。 SELECT * FROM 供应商表 WHERE 供应商名 LIKE “%公司”,利用空值查询 空值是指不确定的值，SQL支持空值。 不确定：用IS NULL（空值），而不能用= NULL。 确定值：用IS NOT NULL（不空值）。 例1：找出尚未确定供应商的订购单。 SELE * FROM 订购单表WHERE 供应商号 IS NULL 例2：列出已经确定了供应商的订购单信息。 SELE * FROM 订购单表WHERE供应商号IS NOT NULL,6.3.7 简单的计算查询 常用的短语有: COUNT-计数 SUM-求和 AVG-计算平均值 MAX-求最大值 MIN-求最小值,1)计数: 使用COUNT（） (1)统计职工表中所有仓库号的数目 SELE COUNT(DIST 仓库号) AS “仓库数” FROM 职工表 (2)统计职工表中所有人数 SELE COUNT(*) AS “所有人数” FROM 职工表 2)求和：使用SUM（） 例:求支付的工资总数 SELE SUM(工资) AS “工资总额” FROM 职工表 3)计算平均值：使用AVG（） 例:求所有仓库的平均面积. SELE AVG(面积) AS “平均面积” FROM 仓库表,4)求最大值：使用MAX（） 例:求在WH2仓库工作的职工的最高工资值 SELE MAX(工资) AS “最大工资” FROM 职工表 WHERE; 仓库号=”WH2” 5)求最小值：使用MIN（） 例:求在WH2仓库工作的职工的最低工资值 SELE MIN(工资) FROM 职工表 WHERE 仓库号=”WH2”,6.3 数据定义,建立(CREATE)数据对象 修改(ALTER)数据对象 删除(DROP)数据对象,6.2.1 建立表结构,命令格式: Crea table/dbffree ( (,) null/not null check error defaultprimary key /unique referencestag ,例题1: 建立一个自由表:人事档案(编号,姓名,性别,基本工资,出生年月),其中允许出生年月为空值. Crea table 人事档案 free (学号 C(7),姓名 C(8),性别 C(2),基本工资 N(7,2),出生年月 D NULL) 例题2: 建立父表:学生表.包括字段有:学号(C,5),姓名 C(8),入学成绩 N(5,1).其中学号为主关键字,成绩字段有效性规则为:入学成绩0,其出错信息为“成绩应该大于0” OPEN DATA 学生管理 CREA DBF 学生表 (学号 C(7) PRIMARY KEY ,姓名 C(8);入学成绩 N(5,1) CHECK(入学成绩0) ERROR “成绩应该大于0”),例题3:建立一个候选表: CREA TABL 选课(学号 C(5),课程号 C(5),; 成绩 I CHECK (成绩=0 AND 成绩=100); ERROR “成绩值的范围0100!” DEFAULT 60,; FORIGN KEY 学号 TAG 学号 REFERENCES 学生),6.2.2 删除表,命令格式: DROP TABLE 功能:直接从磁盘上删除所指定的表文件. 说明:如果是数据库表,删除时数据库应处于打开状态.,6.2.3 修改表结构,1修改字段: Alter table alter 如：Alter table 仓库表 alter 仓库号 C(7) 2、添加字段： Alter table add 如：Alter table 职工表 alter 联系方式 C(12),3、修改字段、有效性规则及默认值。 Alter table alter ； set default set check error 如: Alter table 学生表 alter 入学成绩 set default 0 set check 入学成绩400 ; error “入学成绩在400以上”,4、删除字段、有效性规则、默认值、主索引、候选索引 Alter table alter drop check drop default drop primary key drop foreign key drop column 如：删除字段 Alter table 学生表 drop column 入学成绩 删除有效性 Alter table 学生表 alter 入学成绩 drop check,6. 4 数据操纵,分为三部分： 插入（INSERT） 删除（DELETE） 更新（UPDATE）,6.4.1 插入记录: INSERT INTO VALUES 如:INSERT INTO 学生表 VALUES (“20002”,”李鹏“,450) 6.4.2 删除记录 DELE FROM WHERE 删除学生表中入学成绩在450以上的记录 DELE FROM 学生表 WHER 入学成绩450 6.4.3 更新记录 UPDA SET = WHERE 将入学成绩在450以下的记录加上20分 UPDA 学生表 SET 入学成绩=入学成绩+20 WHERE 入学成绩450,小结(12.10),SQL的核心SELECT语句比VFP命令更加强大功能.掌握该语句的要点是理解各子句的含义和使用规则.如FROM、WHERE、GROUP BY、HAVING、ORDER BY等各自的含义和使用特点。 连接查询是经常用到的SELECT形式，它可以从多个相互关联的表中查询感趣的数据。 等值连接可以用等号（=）表示。 涉及两个表的嵌套查询即外层表查询的条件需要内层表查询提供值时，需要认真阅读例题，理解其逻辑关系。 SELE查询结果的去向可能有多种，在设计查询程序（表单）时多使用INTO CUSOR短语,作为”表格”数据源. 如存入到表中,一般用INTO DBF/TABLE. SQL定义和操作语句可以嵌入在程序中直接定义、修改表的结构和插入、更新记录，不必进行交互操作，给编写程序带来很大灵活性。尤其INSET、UPDATE和DELETE三条语句因比VFP的命令更简洁和方便在程序设计广泛使用。,作业：P114 2 1列出在北京的供应商的名称。 SELE 供应商名 FROM 供应商表 WHERE 地址=“北京” 2列出发给供应商S6的订购单号 sele 订购单号 FROM 订购单表 WHERE 供应商号=“S6“ 3列出职工E6发给供应商S6的订购单信息。 SELE 订购单号 FROM 订购单表 WHERE 职工号=“E6” AND 供应商号=“S6“ 或 SELE 订购单号 FROM 订购单表 WHERE 职工号 IN (SELE 职工号 FROM 订购单表 WHERE 供应商号=“S6” 4列出向供应商S3发过订购单的职工的职工号和仓库号 sele 职工号,仓库号 FROM 职工表 WHERE 职工号 IN (SELE 职工号 FROM 订购单表 WHERE 供应商号=“S3“) 5、列出和职工E1、E3都有联系的北京的供应商信息。 SELE * FROM 供应商表 WHERE 地址=“北京“ AND 供应商号 IN (SELE 供应商号 FROM