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