结构化查询语言(SQL)

结构化查询语言（SQL）SQL概述SQL的内容在笔试和上机考试中均占到大约30%的比例，此外它还是查询和视图的基础，因此是学习的重点也是难点。SQL是结构化查询语言StructureQueryLanguage的缩写。SQL包含了查询功能、数据定义、数据操纵和数据控制功能，在VFP中没有提供数据控制功能。SQL主要特点1．SQL是一种一体化语言。2．SQL是一种高度非过程化的语言。3．SQL语言非常简洁。4．SQL语言可直接以命令方式交互使用，也可嵌入到程序设计语言中以程序方式使用。查询功能SELECT命令的特点：1．可以自动打开数据库、表文件加以查询，而不需要事先用OPENDATABASE或USE命令打开。2．可以直接选取数据表中的数据，而不需要事先用SETRELATION命令建立关联。3．当需要的索引文件不存在时，会自动建立暂存索引文件，以支持快速搜索技术（Rushmore）来查询。4．其查询结果可输出到文件、表、屏幕或报表上，还可以转换成统计图表。命令格式：SELECT——FROM——WHERE可与LISTFIELDS——FOR——对照学习。关系操作：投影，选择，联接。说明：功能强大，语法灵活；要处理的数据表无须事先打开，通过FROM子句指明并打开。1．SELECT短语：说明要查询的数据；对应的关系操作为投影，类似于FIELDS子句。2．FROM短语：说明要查询的数据来自哪个或哪些表，可对单个表或多个表进行查询；3．WHERE短语：说明查询条件；对应的关系操作为选择，类似于FOR子句。如是多表查询还可能过该子句指明联接条件，进行联接。4．GROUPBY短语：用于对查询结果进行分组，可利用它进行分组汇总；类似于TOTAL命令。5．HAVING短语：跟随GROUPBY使用，它用来限定分组必须满足的条件；6．ORDERBY短语：用于对查询的结果进行排序；类似于SORT命令。示例数据库：学生管理，包括三个表：学生，课程，选课（通过该表反映“学生”与“课程”之间“多对多”的联系。问题：请同学们分别指出三个表的主关键字。一、简单查询简单查询基于单个表。例：1．查询学生信息SELE*FROM学生&&“*”号代表所有列。类似于：USE学生LIST2．查询男生学生的学号、姓名及生日SELE学号,姓名,生日FROM学生WHERE性别=”男”类似于USE学生LIST学号,姓名,生日FOR性别=”男”3.查询学分大于5的课程的信息SELE*FROM课程WHERE学分5类似于USE学生LISTFOR学分54、查询学分不在6到9之间的Sele*from课程where学分notbetween6and9二、简单联接查询联接是关系的基本操作之一，联接查询基于多个关系的查询。例：查询男生学生的选课信息，包括姓名，学号及成绩。分析：本例的查询结果包括两个表“学生”与“选课”的属性，适用于联接查询。SELE姓名,选课.学号,成绩;FROM学生,选课;WHERE学生.学号=选课.学号AND性别=”男”说明：如果命令太长一行写不下可在行末加分号“：”表续行选课.学号：因为两个表中均有“学号”字段，所以必须指明所属表。学生.学号=选课.学号：连接条件。还可使用如下SQL语句：SELE姓名,选课.学号,成绩;FROM学生JOIN选课ON学生.学号=选课.学号;WHERE性别=”男”说明：JOIN……ON：建立表与表之间的联接。自连接：seleS.雇员姓名,领导,E.雇员姓名from雇员S,雇员EwhereS.雇员号=E.经理三、嵌套查询嵌套查询是另一类基于多个关系的查询，此类查询所要求的结果出自一个关系，但相关的条件却涉及多个关系。1、例：查询女生学生选课信息，包括学号，课程号，成绩。SELE*;FROM选课;WHERE学号IN(SELE学号FROM学生WHERE性别=”女”)说明：IN：相当于集合运算符∈。还可使用如下SQL语句：简单联接查询SELE选课.学号,课程号,成绩;FROM选课JOIN学生ON选课.学号=学生.学号;WHERE性别=”女”2、查询有“硬盘”订购需求的订购单明细记录：这个例子要求查询订购单明细表中的记录，而查询条件是产品标的名称，Sele*from订购单明细where产品号IN；（sele产品号from产品where名称=“硬盘”）3、排除法查询的例子：查询所有订购数量（即订购单中每个订购项目的数量）都在50以上的订购单的信息Sele*from订购单where订单号notin；（sele订单号from订购单明细where数量〈=50）Sele*from订购单where订单号notin；（sele订单号from订购单明细where数量〈=50）and订单号in（sele订单号from订购单明细）4、找出和某某产品同等价位的所有产品信息：内外层查询的同一个表，内层查询查处单价，外层查询根据这个单价查询产品信息Sele*from产品where单价=；（sele单价from产品where规格说明=“某某产品”）5、内外层互相查询嵌套查询四、特殊运算符1．BETWEEN…AND…意为“…和…之间”例：查询成绩在80分到90分之间的选课信息。SELE*FROM选课WHERE成绩BETWEEN80AND90等价于：SELE*FROM选课WHERE成绩=80AND成绩=90例：查询成绩不在80分到90分之间的选课信息。SELE*FROM选课WHERE成绩NOTBETWEEN80AND902．LIKE是字符串匹配运算符，通配符“%”表示0个或多个字符，“_”表示一个字符。注意：不是“*”和“？”，不正确！例：从学生关系中查询姓“黄”的学生信息。SELE*FROM学生WHERE姓名LIKE“黄%”不能写成：SELE*FROM学生WHERE姓名=黄%例：从学生关系中查询不姓“黄”的学生信息。SELE*FROM学生WHERE姓名NOTLIKE“黄%”例：SELE*FROM学生WHERE姓名LIKE黄_SELE*FROM学生WHERE姓名LIKE黄__五、排序SQLSELECT可将查询结果排序。排序的短语格式如下：ORDERBYOrder_Item[ASC|DESC][,Order_Item[ASC|DESC]…]说明：可按升序或降序排序，允许按一列或多列排序。例：按学分进行升序查询课程信息。SELE*FROM课程ORDERBY学分ASC例：先按学分进行升序排序，学分相同的再按课程名进行降序排序并输出全部课程信息。SELE*FROM课程ORDERBY学分,课程名DESC注：ORDERBY对最终的查询（外查询）结果进行排序，不能在子查询（内查询）中使用此短语。六、简单计算查询SQL不但具有一般的检索能力，而且还有计算方式的检索。用于计算检索的函数有：1．COUNT（*）：计数2．SUM（）：求和3．AVG（）：算平均值4．MAX（）：求最大值自变量通常为数值型5．MIN（）：求最小值上面的函数可用在SELECT短语中对查询结果进行计算。例：查询学号为“03”的学生选修课程的考试成绩总分和平均分。SELESUM(成绩),AVG(成绩)FROM选课WHERE学号=”03”SELESUM(成绩)总分,AVG(成绩)平均分FROM选课WHERE学号=”03”SELE姓名,SUM(成绩)AS总分,AVG(成绩)AS平均分;FROM学生JOIN选课ON学生.学号=选课.学号;WHERE学生.学号=”03”七、分组与计算查询利用GROUPBY子句进行分组计算查询使用得更加广泛。GROUPBY短语格式如下：GROUPBYGroupColumn[,GroupColunm…][HAVINGFilterCondition]说明：1．可按一列或多列分组，还可以用HAVING进一步限定分组的条件。2．GROUPBY子句一般跟在WHERE子句之后，没有WHERE子句时，跟在FROM子句之后；HAVING子句必须跟在GROUPBY之后，不能单独使用。在查询中是先用WHERE子句限定元组，然后进行分组，最后再用HAVING子句限定分组。例：求每个学生选课的考试成绩平均分。SELE学号,AVG(成绩)FROM选课GROUPBY学号说明：在此查询中，选按学号属性进行分组，然后再计算每个学号的平均成绩。例：在选课表中求每个选课门数为4门的学生的总分和平均分。SELE学号,SUM(成绩)AS总分,AVG(成绩)AS平均分;FROM选课;GROUPBY学号HAVINGCOUNT(*)=4SELE姓名,SUM(成绩)AS总分,AVG(成绩)AS平均分;FROM学生JOIN选课ON学生.学号=选课.学号;WHERE性别=”女”;GROUPBY选课.学号HAVINGCOUNT(*)=4例：求平均成绩在80分以上的各课程的课程号与平均成绩。SELE课程号,AVG(成绩)FROM选课;GROUPBY课程号HAVINGAVG(成绩)80八、使用空值进行查询空值的概念：空值就是缺值或还没有确定值，不能把它理解为任何意义的数据。比如表示价格的一个字段值，空值表示没有定价，而数值0可能表示免费。空值与空（或空白）字符串、数值0等具有不同的含义。例：假设在选课中有些学生某门课程还没有考试，则成绩为空。试找出尚未考试的选课信息。SELE*FROM选课WHERE成绩ISNULL注：不能写成“=NULL”例：试找出成绩不为空的选课信息。SELE*FROM选课WHERE成绩ISNOTNULL九、别名与自联接查询在联接操作中，要使用关系名作前缀，为简单起见，SQL允许在FROM短语中为关系名定义别名。格式为：关系名别名例：查询选课信息中的姓名，课程名，成绩。SELE姓名,课程名,成绩;FROM学生S,课程C,选课SC;WHERES.学号=SC.学号ANDC.课程号=SC.课程号说明：在上面的例子中，别名并不是必须的，但是在关系的自联接操作中，别名则是必不可少的。SQL不仅可以对多个关系实行联接操作，也可将同一关系与其自身进行联接，这种联接就称为自联接。在这种自联接操作关系上，本质上存在着一种特殊的递归联系，也就是关系中的一些元组，根据出自同一值域的两个不同的属性，可以与另外一些元组有一种对应关系（一对多的联系）。注：元组即记录。例：试查询先修课的课程名在本例中，先修课号与课程号出自同一值域，会涉及自联接查询。SELEDISTINCTC2.先修课号C1.课程名;FROM课程C1,课程C2;WHEREC1.课程号=C2.先修课号十、内外层相关嵌套查询前面讨论的嵌套查询是外层查询依赖于内层查询的结果，而内层查询与外层查询无关。但有时也需要内、外层互相关的查询，这时内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果。例：查询只有一个学生选修的课程的学号，课程号及成绩。SELE*;FROM选课SC1;WHERESC1.课程号NOTIN；(SELE课程号FROM选课SC2WHERESC1.学号SC2.学号)SELE*FROM选课SC1;WHERENOTEXIST(SELE*FROM选课SC2;WHERESC1.学号SC2.学号ANDSC2.课程号=SC1.课程号)十一、用量词和谓词的查询前面已学过[NOT]IN运算符，此处还有两种与子查询相关的运算符。格式1：表达式比较运算符[ANY|ALL|SOME](子查询)格式2：[NOT]EXISTS(子查询)说明：1．ANY、ALL和SOME为量词，ANY和SOME是同义词，在进行比较运算时只要子查询中有一行能使结果为真，则结果为真；ALL则要求子查询中的所有行都使结果为真时，结果才为真。2．EXISTS为谓词，EXISTS和NOTEXISTS是用来检查在子查询中是否有结果返回，也就是存在元组或不存在元组。例：查询选修有课程的学生的学号，姓名，性别及生日。（看懂）SELE*FROM学生WHEREEXIST(SELE*FROM选课WHERE学生.学号=选课.学号)注：本例中内层查询引用了外层查询的表，只有这样使用谓词EXISTS或NOTEXIS