第五章sql语言

第五章关系数据库标准语言SQL一、SQL概述SQL是结构化查询语言即StructuredQueryLanguage1、特点：①是一种一体化语言，包括了数据定义、数据查询、数据操纵和数据控制等，可以完成数据库活动中的全部工作。②是一种高度非过程化的语言③语言非常简洁④可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。2、功能：①数据定义：CreateDropAlter②数据查询：Select③数据操纵：InsertUpdateDelete④数据控制：GrantRevoke二、查询功能1、简单查询基本格式Select···From···where···；GroupBy···OrderBy···Select数据对象（目标即字段名，表达式等）From数据来源（表名）Where查询条件GroupBy分组OrderBy排序例：1、从职工关系中检索所有的工资值Select(distinct)工资From职工Distinct：去掉查询结果中的重复值。2、检索仓库关系中的所有元组Select*From仓库*：是通配符，表示所有属性，即字段3、检索工资多于1230元的职工号Select职工号From职工where工资12304、给出在仓库“WH1”或“WH2”工作，并且工资少于1250元的职工号。Select职工号From职工where工资1250and(仓库号=“WH1”or仓库号=”WH2”)或Select职工号From职工；where工资1250and(仓库号=“WH1”or仓库号=”WH2”)；是续行符2、简单联接查询关系的三种基本操作：选择、投影、联接select相当于投影操作，where相当于选择操作例1、找出工资多于1230元的职工号和他们所在的城市select职工号,城市from职工，仓库；where(工资1230)and(职工.仓库号=仓库.仓库号)如果在from之后有两个关系，那么这两个关系之间肯定有一种关系，职工.仓库号=仓库.仓库号是联接条件，仓库号是职工表的外部关键字，通过公共字段实现联接练习：1、检索在北京的供应商的名称2、检索仓库表中的城市和面积3、检索出向供应商S3发过订购单的职工的职工号和仓库号3、嵌套查询当检索关系X中的元组时，它的条件依赖于相关的关系Y在的元组属性值，这是使用SQL的嵌套查询功能。例1、哪些城市至少有一个仓库的职工工资为1250元？Select城市from仓库where仓库号in;(select仓库号from职工where工资=1250)2、查询所有职工的工资都多于1210元的仓库的信息即没有一个职工的工资少于或等于1210元的仓库的信息select*from仓库where仓库号notin;(select仓库号from职工where工资=1210)注意区别：select*from仓库where仓库号in;(select仓库号from职工where工资1210)select*from仓库where仓库号notin;(select仓库号from职工where工资=1210);and仓库号in(select仓库号from职工)3、找出和E4挣同样工资的所有职工select职工号from职工where工资=；（select工资from职工where职工号=”E4”）练习：1、检索出向供应商S3发过订购单的职工的职工号和仓库号2、检索出和职工E1、E3都有联系的北京的供应商信息4、几个特殊运算符between····and····在···之间like字符串匹配通配符“%”表示0个或多个字符“—”表示一个字符!=不等于例：1、检索出工资在1220元到1240元范围内的职工信息select*from职工where工资between1220and12402、从供应商关系中检索出公司的信息，不要工厂或其他供应商的信息select*from供应商where供应商名like“%公司”3、找出不在北京的全部供应商信息select*from供应商where地址!=”北京”或select*from供应商wherenot(地址=”北京”)5、排序基本格式：orderby字段名[asc][desc]多列排序：orderby字段名1，字段名2例1、按职工的工资值升序检索出全部职工信息select*from职工orderby工资2、先按仓库号排序，再按工资排序并输出全部职工信息select*from职工orderby仓库号，工资6、简单的计算查询用于计算查询的常用函数：count计数sum求和avg计算平均值max求最大值min求最小值例1、求北京和上海的仓库职工的的总和selectsum(工资)from职工where仓库号in;(select仓库号from仓库where城市=”北京”or“城市=上海”)例2、求所有职工的工资都多于1210元的仓库的平均面积selectAVG(面积)from仓库where仓库号notin;(select仓库号from职工where工资=1210)7、分组与计算查询基本格式：groupby字段名1[，字段名2][having条件]where用于限定元组条件，having用于限定分组条件例1、求每个仓库的职工的平均工资select仓库号，AVG（工资）from职工groupby仓库号例2、求至少有两个职工的每个仓库的平均工资select仓库号，count(*),AVG(工资)from职工;groupby仓库号havingcount(*)=28、利用空值查询如果字段变量尚未取值，把这个信息存入数据库，则这个属性为空值。（NULL）空值：null在使用时用isnull，空值不是一个确定的值因此不能用=null例1、找出尚未确定供应商的订购单sele*from订购单where供应商号isnull例2、列出已经确定了供应商的订购单信息sele*from订购单where供应商号isnotnull9、别名与自联接查询在联接操作中，经常要使用关系名作前缀，有时这样显得很麻烦，因此，SQL允许在FROM短语中为关系名定义别名格式：关系名别名如：SELECT供应商名FROM供应商，订购单，职工，仓库；WHERE地址=“北京”AND城市=“北京”；AND供应商.供应商号=订购单.供应商号；AND订购单.职工号=职工.职工号；AND职工.仓库号=仓库.仓库号使用别名改成：SELECT供应商名FROM供应商S，订购单P，职工E，仓库W；WHERE地址“北京”AND城市=“北京”；ANDS.供应商号=P.供应商号；ANDP.职工号=E职工号；ANDE.仓库号=W.仓库号自连接：将同一关系与其自身进行连接在可以进行自连接操作的关系上，存在着一中特殊的递归关系，即关系中的一些，元组根据出自同一值域的两个不同的属性，可以与另外一些元组有一种对应关系，这种关系为一对多的联系。雇员关系：雇员雇员姓名经理E3赵涌E4钱潮E3E6孙洁E3E8李绿E6根据雇员关系列出上一级经理及其所领导的职员清单。SELECTS.雇员姓名，“领导”，E.雇员姓名FROM雇员S，雇员E；WHERES.雇员号=E.经理S为经理关系，E是雇员关系10、内外层互相关嵌套查询建立订购单表：（职工号，供应商号，订购单号，订购日期，总金额）例：列出每个职工经手的具有最高总金额的订购单信息。SELECTout.职工号，out.供应商号，out.订购单号，out.订购日期，out.总金额；FROM订购单outWHERE总金额=；（SELECTMAX（总金额）FROM订购单innerl;WHEREout.职工号=innerl.职工号）外层查询和内层查询使用同一个关系，给他们分别指定别名out和innerl。Out关系中每个元组的职工号值给内层查询使用；内层查询利用这个职工号值，确定该职工经手的具有最高总金额的订购单的总金额；接着外层查询再根据out关系的同一元组的总金额值与该总金额值进行比较，如果相等，则该元组被选择。（本题也可用表别名做）11、使用量词和谓词的查询和子查询有关的运算符：表达式比较运算符[ANY/ALL/SOME][NOT]EXISTSANY、ALL和SOME是量词，其中ANY和SOME是同义词，在进行比较运算时只要子查询中有一行能使结果为真，则结果就为真；而ALL则要求子查询中的所有行都使结果为真时，结果才为真。EXISTS是谓词，EXISTS或NOTEXISTS是用来检查在子查询中是否有结果返回，即存在元组或不存在元组。例：1、查询那些仓库中还没有职工的仓库的信息。SELECT*FROM仓库WHERENOTEXISTS；（SELECT*FROM职工WHERE仓库号=仓库.仓库号）2、检索那些仓库中至少已经有一个职工的仓库的信息。SELECT*FROM仓库WHEREEXISTS；（SELECT*FROM职工WHERE仓库号=仓库.仓库号）3、检索职工的工资大于或等于WH1仓库中任何一名职工工资的仓库号SELECTDIDTINCT仓库号FROM职工WHERE工资=ANY;(SELECT工资FROM职工WHERE仓库号=“WH1”)4、检索有职工的工资大于或等于WH1仓库中所有职工工资的仓库号SELECTDISTINCT仓库号FROM职工WHERE工资=ALL(SELECT工资FROM职工WHERE仓库号=“WH1”)12、超连接查询在SQL中超连接运算符是：左连接“*=”和右连接“=*”左连接：在结果表中包含第一个表中满组条件的所有记录；如果有在连接条件上匹配的元组，则第二个表返回相应值，否则第二个表返回空值。右连接：在结果表中包含第二个表中满足条件的所有记录；如果有在联接条件上匹配的元组，则第一个表返回相应值，否则第一个表返回空值。格式：SELECT……FROM表INNER/LEFT/RIGHT/FULLJOIN表ON连接条件INNERJOIN：普通连接，在VFP中称为内部联接。LEFTJOIN：左连接RIGHTJOIN：右连接FULLJOIN：全联接1、内部联接，即只有满足联接条件的记录才出现在查询结果中SELECT仓库.仓库号，城市，面积，职工号，工资；FROM仓库JOIN职工；ON仓库.仓库号=职工.仓库号SELECT仓库.仓库号，城市，面积，职工号，工资；FROM仓库INNERJOIN职工；ON仓库.仓库号=职工.仓库号SELECT仓库.仓库号，城市，面积，职工号，工资；FROM仓库，职工WHERE仓库.仓库号=职工.仓库号2、左联接，即消除满足联接条件的记录出现在出查询结果中外，第一个表总不满足联接条件的记录也出现在查询结果中。SELECT仓库.仓库号，城市，面积，职工号，工资；FROM仓库LEFTJION职工；ON仓库.仓库号=职工.仓库号3、右连接，除满足联接条件的记录出现在查询结果中外，第二个表中不满足联接条的记录也出现在查询结果中。SELECT仓库.仓库号，城市，面积，职工号，工资；FROM仓库RIGHTJOIN职工；ON仓库.仓库号=职工.仓库号4、全联接，除满足连接条件的记录出现在查询结果中外，两个表中不满足联接条件的记录也出现在查询结果中。SELECT仓库.仓库号，城市，面积，职工号，工资；FROM仓库FULLJOIN职工；ON仓库.仓库号=职工.仓库号注：JOIN连接格式在连接多个表时的书写方法要特别注意，在这种格式中JOIN的顺序和ON的顺序是很重要的，特别要注意JOIN的顺序要和ON的顺序正好相反。13、集合的并运算集合的并运算（UNION），可以将两个SELECT语句的查询结果通过并运算并成一个查询结果。条件：两个查询结果具有相同的字段个数，并且对应字段的值要出自同一个值域。语句的结果是城市为北京和上海的的仓库信息SELECT*FROM仓库WHERE城市=“北京”；UNION；SELECT*FROM仓库WHERE城市=“上海”14、几个特殊选项1、显示只显示前几项记录TOP数字表达式[PERCENT]如果不使用PERCENT时，数字表达式的取值是1到3