第5章关系数据库标准语言SQL

第5章关系数据库标准语言SQL（16课时）一、【教学目标】1.了解SQL语言的作用和主要特点。2.理解SQL语言的数据定义功能，掌握用CREATE命令建立表结构、用ALTER命令修改表结构、用DROP命令删除表。3.理解SQL语言的数据操作功能，掌握用INSERT命令插入记录、用DELETE命令删除记录、用UPDATE命令更新记录。4.理解SQL语言的数据查询功能，掌握用SELECT命令进行简单查询、条件查询、多表查询、嵌套查询、统计查询、分组查询等。二、【重点和难点】重点：１．SQL定义、修改表结构的规则与方法。２．SQL数据修改方法与规则。３．SQL数据查询功能的语句规则和特点。４．综合应用SQL数据查询功能完成实际问题。难点：1.使用嵌套子查询的方法和规则。2.处理数据表自身连接和复合条件查询的方法。3.综合应用SQL数据查询功能完成实际问题的能力。三、【学法指导】1.SQL语言是数据库标准语言，包含数据定义、数据操纵、数据控制等，VFP对SQL语言的支持使VFP更加完善。2.所有关系数据库都支持SQL语言，具有通用性。四、【教学要点】1．SQL概述SQL是结构化查询语言StructuredQueryLanguage的缩写，查询是SQL的重要组成部分，SQL还包括数据定义、数据操纵和数据控制功能等部分，SQL已经成为关系数据库的标准数据语言，该语言具有如下特点：①SQL是一种一体化的语言，它包括了数据定义、数据查询、数据操纵和数据控制等方面的功能，它可以完成数据库活动的全部工作，②SQL是一种高度非过程化的语言，用户只需要描述“做什么”，SQL语言就可以将要求交给系统，系统自动完成全部工作。③SQL语言非常简洁，很接近自然语言（英语），因此易学，易掌握。④SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。2．查询功能SQL的核心是查询，查询命令是SELECT。常用语法格式如下：SELECT……FROM……[WHERE……][GROUPBY……][HAVING……][UNION[ALL]……][ORDERBY……]说明：①SELECT说明要查询的数据。②FROM说明要查询的数据来自那个或那些表可以对单个表或多个表进行查询。③WHERE说明查询条件即选择元组的条件。④GROUPBY短语用于对查询结果进行分组，可以利用它进行分组汇总。⑤HAVING必须跟随GROUPBY使用，用来限定分组必须满足的条件。⑥ORDERBY用来对查询的结果进行排序。（1）简单查询①无条件查询：由SELECT和FROM短语构成②条件查询：由SELECT、FROM和WHERE短语构成③[DISTINCT]短语,去掉结果中的重复值例1：从职工关系检索仓库WH2的所有工资值SELECT工资FROM职工WHERE仓库号=“WH2”例2：检索仓库中的所有元组SELECT*FROM仓库等同于select仓库号，城市，面积from仓库（2）简单的联接查询联接是关系的基本操作之一，联接查询是基于多个关系的查询。例：找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市select职工号，城市from仓库，职工；where(面积400)and(职工.仓库号=仓库.仓库号)说明:①在VF的SQL命令中分号是续行符号。②当from后的多个关系中有同名字段时,须用前缀指明字段所属的表。③多表连接查询时要指明查询联接条件．．．．．．．．．．．．．．．．。（3）嵌套查询（有两个以上查询条件，且在不同关系中）当查询所要求的结果出自一个关系，但相关的条件却涉及多个关系，则使用嵌套查询。即当检索关系X中的元组时，它的条件依赖于相关的关系Y中的元组属性值，此时用嵌套查询比较方便。外层查询的条件依赖于内层查询的结果．．．．．．．．．．．．．．．．．。注：记录字段值区分大小写．．．．．．．．．．．．例1：哪些城市至少有一个仓库的职工工资为1250元？Select城市from仓库WHERE仓库号IN；（SELECT仓库号FROM职工WHERE工资=1250）例2：找出和职工E4挣同样工资的所有职工select职工号from职工where工资=；(select工资from职工where职工号=“E4”)例3：查询所有职工的工资都多于1210元的仓库的信息分析：①查询范围来源于职工表，而结果在仓库表，故用嵌套查询。②若条件设为工资1210,则比如WHI中工资有满足条件的,也没不满足条件的,故无法适应此情况应该设为:仓库号notin……工资=1210③如果某仓库还没有职工,则也满足②的条件notin职工，故应添加条件,保证该仓库至少有一名职工。解:select*from仓库where仓库号notin;(select仓库号from职工where工资=1210)and仓库号in(select仓库号from职工)（4）几个特殊运算符①BETWEEN……AND……在……和……之间例：检索出工资在1220元到1240范围内的职工信息。Select*from职工where工资between1220and1240说明：①此条件等价于（工资=1220）and(工资=1240)，但更简洁。②“不在……之间”可用notbetween……and……select*from职工where工资notbetween1220and1240②LIKE%——表示0个或多个字符LIKE_——表示一个字符例：检索出供应商是某公司的信息。select*from供应商where供应商名Like“%公司”③不等于“！=”可用否定运算符NOT表示。例：找出不在北京的供应商信息。Select*from供应商wherenot(地址=“北京”)（5）排序使用排序短语ORDERBY可以查询结果排序。格式：ORDERBY字段名1[ASC|DESC][，字段名2[ASC|DESC]…]说明：①可以指定排序方式为升序或降序②允许按一列或多列排序例：先按仓库号升序排序，再按工资降序排序检索出全部职工信息。Selet*from职工orderby仓库号ASC，工资desc（6）简单的计算查询SQL语句可以直接对检索结果进行计算，有5个计算函数：①COUNT——计数②SUM——求和③AVG——计算平均值④MAX——就最大值⑤MIN——求最小值例：求WH2仓库的职工人数、工资总数、平均工资、最高工资和最低工资。Selectcount(*)as人数，avg(工资)as平均工资，;Sum(工资)as工资合计,max（工资）as最高工资，；min(工资)as最低工资from职工Where仓库号=“WH2”（7）分组与计算查询短语：GROUPBY组名[，组名……][HAVING条件]功能：先按属性分组，再进行查询，还可根据多个属性分组。当需要分组满足某条件时才检索，可用Having子句来限定分组。例1：求每个仓库的职工的平均工资。select仓库号，AVG（工资）from职工groupby仓库号例2：求至少有两个职工的每个仓库的平均工资。select仓库号，count（*）from职工;groupby仓库号havingcount(*)=2（8）利用空值查询ISNULLISNOTNULL例1：找出尚未确定供应商的定单信息。Select*from订购单where供应商号ISNULL注：空值查询不能用“=NULL”，因为空值不是一个确定的值，不能用“=”运算符比较例题：已确定了供应商。Select*from订购单where供应商号ISNOTNULL（9）别名与自连接查询①别名定义：为了简化关系名.格式；关系名别名例:从定货管理的四张表中找出仓库和供应商都在北京的供应商.Select供应商名from供应商S,订购单P,职工E,仓库W；where地址=“北京”and城市=“北京”；ands.供应商号=p.供应商号；andp.职工号=E.职工号；ande.仓库号=w.仓库号②自联接查询自联接：将同一关系与其自身进行联接称自联接。在此关系上，存在着一种递归联系，既关系中的一些元组，据出自同一值域的两个不同属性，可以与另外一些元组有一种对应关系（一对多的联系）。例：雇员（雇员号，雇员姓名，经理）雇员号和经理两个属性同一值域，且同一元组的这两个属性值是“上、下级”关系根据雇员关系例出上一级经理及所领导的职员清单。SelectS.雇员姓名，“领导”，E.雇员姓名from雇员S,雇员E;whereS.雇员号=E.经理（10）内外层互相关联嵌套查询内外层互相关的查询：指内层查询需要外层查询提供值，而外层查询的条件的需要内层查询的结果。例：例出每个职工经手的具有最高总金额的订购单信息。Selectout.职工号，out.供应商号，out.订购单号,out.总金额;from订购单outwhere总金额=；（selectmax（总金额）from订购单innerl;whereout.职工号=innerl.职工号）（11）使用量词和谓词的查询在嵌套查询中还可以有如下两种形式：1、表达式比较运算符[ANY|SOME]（子查询）2、[NOT]EXISTS（子查询）说明：①any、all、some是量词，其中any和some是同义词，比较时只要子查询中有一行能使结果为真，则结果就为真；而all要求子查询中的所有行都使结果为真时，结果才为真。②exists或notexists用来检查在子查询中是否有结果返回，即存在元组或不存在元组。例1：检索那些仓库中还没有职工的仓库的信息。Select*from仓库wherenotexists;(Select*from职工where仓库号=仓库.仓库号)例2：检索有职工的工资大于且等于“WH1”仓库中任何一名职工的工资的仓库号。Selectdistinct仓库号from职工where工资=any;(select工资from职工where仓库号=“WH1”)例3：检索有职工的工资大于或等于“WH1”仓库中所有职工的工资的仓库号。Selectdistinct仓库号from职工where工资=all;(select工资from职工where仓库号=“WH1”)（12）超联接查询超联接：首先保证一个表中满足条件的元组都在结果表中；然后将满足联接条件的元组与另一个表的元组进行联接，不满足联接条件的则将应来自另一表的属置为空值。格式：SELECT……FROMTableINNER|KEFT|RIGHT|FULLJoinTableONJoinConditionWHERE……说明：①INERJOIN普通联接,称内部联接②LEFTJOIN为左联接③RIGHTJOIN为右联接④FULLJOIN以称为全联接，即两个表中的条件不管是否满足联接条件将都在目标表或查询结果中出现，不满足联接条件的记录对应部分为NULL.⑤ONJoinCondicion联接条件例1：内部联接．．．．___只有满足联接条件的记录才出现在查询结果中。SELECT仓库.仓库号，城市，面积，职工号，工资；From仓库innerjoin职工on仓库.仓库号=职工.仓库号例2：左联接．．．___即除满足是联接条件的记录出现在查询结果中外，第一个表中不满足联接条件的记录也出现在查询结果中。SELECT仓库.仓库号，城市，面积，职工号，工资；From仓库leftjoin职工on仓库.仓库号=职工.仓库号例3：右联接．．．___即除满足联接条件的记录出现在查询结果中外，第二个表中不满足联接条件的记录也出现在查询结果中。SELECT仓库.仓库号，城市，面积，职工号，工资；From仓库rightjoin职工on仓库.仓库号=职工.仓库号例4：全联接．．．___除满足条件的记录出现，两表中不满足联系条件的记录也出现在查询结果中。SELECT仓库.仓库号，城市，面积，职工号，工资；From仓库fulljoin职工on仓库.仓库号=职工.仓库号（13）集合的合并运算SQL可以将两个select语句的查询结果通过合并运算union合并成一个查询结果，要求：两查询结果具有