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

1第五章目录上一页下一页退出关系数据库标准语言SQL2本章要点1SQL概述2查询功能3操纵功能4定义功能31、SQL语言的特点综合统一SQL语言集数据定义、数据查询、数据操纵和数据控值的功能于一体，语言风格统一，可以独立完成数据库活动中的全部操作，包括定义关系模式、录入数据及建立数据库、查询、更新、维护数据、数据库的重新构造、数据库安全性等一系列操作的要求，为数据库应用系统开发者提供了良好的环境。高度非过程化语言简洁，易学易用以同一种语法结构提供两种使用方式：交互方式和程序方式5.1SQL概述45.2查询功能数据库中的数据很多时侯是为了查询的，因此，数据查询是数据库的核心操作。而在SQL语言中，查询语言中有一条查询命令，即SELECT语句。5.2.1简单查询1、无条件查询【格式】SELECT[ALL|DISTINCT]字段列表FROM表文件名【功能】无条件查询。【说明】ALL：表示显示全部查询记录，包括重复记录。（可省略）DISTINCT：表示显示无重复结果的记录。本节重点5例1、显示STUD（学生信息数据表）中的所有记录。SELECT*FROMSTUD说明：命令中的*表示输出表中所有字段，FROM后面的表文件名指明数据的来源，表中的内容以浏览方式显示。例2、显示职工表中的所有工资值。SELECT工资FROM职工表例3、显示职工表中的所有工资值，同时能去除重复值。SELECTDISTINCT工资FROM职工表例4、显示成绩表中的所有记录，并将成绩一项乘以0.7。SELECT学号，课程号，成绩*0.7AS成绩；FROM成绩本节重点62带条件（WHERE）的查询【格式】SELECT[ALL|DISTINCT]字段列表FROM表文件名[WHERE条件]【功能】从一个表中查询满足条件的记录。【说明】WHERE条件子句用来查询满足条件的记录。例5、显示STUD表中所有男生记录的学号，姓名和性别字段值。SELECT学号，姓名，性别;FROMSTUDWHERE性别=男本节重点7例6、显示STUD表中出生日期在85年之间的学生的学号，姓名，出生日期。SELECT学号,姓名,出生日期FROMSTUD;WHERE(出生日期={01/01/85})AND(出生日期={12/31/85})例7、显示职工表中在仓库“WH1”或“WH2”工作，并且工资少于1250元的职工号。SELECT职工号FROM职工表;WHERE工资1250AND(仓库号=“WH1”OR;仓库号=“WH2”)本节重点85.2.2简单的连接查询【说明】在一个数据库中的多个表之间一般都存在着某些联系，在一个查询语句中同时涉及到两个或两个以上的表时，这种查询称之为连接查询（也称为多表查询）。在多表之间查询必须处理表与表之间的连接关系。【格式】SELECT[ALL|DISTINCT]字段列表FROM表文件名1[，表文件名2…..]WHERE条件本节重点9例8、查询并显示各个学生的学号，姓名，课程名称及成绩。SELECT学生.学号，姓名，课程名称，成绩;FROM学生，课程，成绩;WHERE学生.学号=成绩.学号AND成绩.课程号=课程.课程号例9、查询工资多于1230元的职工号（职工表）和他们所在的城市（仓库表）。SELECT职工号，城市FROM职工表，仓库表;WHERE(工资1230).AND.职工表.仓库号=仓库表.仓库号例10、找出工作在面积大于400的仓库的职工号（职工表）以及这些职工工作所在的城市（仓库表）。SELECT职工号，城市FROM职工表，仓库表;WHERE(面积400).AND.职工表.仓库号=仓库表.仓库号本节重点105.2.3嵌套查询在SQL语句中，一个SELECT－FROM－WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。例11、在STUD.DBF表中显示“李宁”所在班级的学生名单。SELECT学号，姓名，班级名FROMSTUD;WHERE班级名=（SELECT班级名FROMSTUD;WHERE姓名=“李宁”）例12、哪些城市至少有一个仓库的职工工资为1250元？SELECT城市FROM仓库表;WHERE仓库号IN（SELECT仓库号FROM职工表;WHERE工资=1250）练习：找出和职工E4中同样工资的所有职工。本节重点115.2.4几个特殊的运算符（1）字段名[NOT]BETWEEN起始值AND终止值（2）字段名[NOT]IN(值表)（3）字段名[NOT]LIKE字符表达式(通配符:_%)例13、检索出工资在1220元到1240元范围内的职工信息SELECT*FROM职工表;WHERE工资BETWEEN1220AND1240例14、从供应商关系中检索出全部公司的信息，不要工厂或其他供应商的信息。SELECT*FROM供应商表;WHERE供应商名LIKE“%公司”练习、找出地址在北京的供应商的全部信息本节重点125.2.5排序——将查询的结果排序【格式】SELECT[ALL|DISTINCT]字段列表FROM表文件名[WHERE条件][ORDERBY排序项1[ASC|DESC][,排序项2[ASC|DESC]…]]【说明】利用ORDERBY排序项子句将查询的结果进行排序例15、按出生日期降序显示STUD表中的学号，姓名，出生日期。SELECT学号，姓名，出生日期FROMSTUD;ORDERBY出生日期DESC本节重点13例16、按成绩升序显示成绩表中的学号，课程号，成绩。SELECT学号，课程号，成绩FROM成绩;ORDERBY成绩ASC例17、先按仓库号排序，在按工资排序，并输出全部职工的信息。SELECT*FROM职工表ORDERBY仓库号,工资例18、按成绩降序显示成绩表中03级同学的学号，姓名，课程名称，成绩。SELECT学号，姓名，课程名称，成绩;FROM成绩，学生，课程;WHERELEFT(学生.学号,2)=“03”AND学生.学号=成绩.;学号AND成绩.课程号=课程.课程号ORDERBY成绩DESC练习:先按课程名称，再按成绩升序显示同学的学号，姓名，课程名称，成绩。本节重点145.2.6简单的计算查询SQL不仅具有一般的检索能力，而且还具有计算方式的检索。比如：检索某门课程的最高分数、或检索职工的平均工资等。用于计算检索的函数有：①COUNT——计数②SUM——求和③AVG——计算平均值④MAX——求最大值⑤MIN——求最小值例19、找出供应商所在地的数目。SELECTCOUNT（DISTINCT地址）FROM供应商表SELECTCOUNT（*）FROM供应商表——功能？本节重点15例20、求应支付给职工的工资总额。SELECTSUM（工资）FROM职工表例21、求北京和上海的仓库职工的工资总和。SELECTSUM(工资)FROM职工表WHERE仓库号IN(SELECT仓库号FROM仓库表;WHERE城市=“上海”OR城市=北京)例22、求所有职工的工资都多于1210元的仓库的平均面积。SELECTAVG(面积)FROM仓库表WHERE仓库号;NOTIN(SELECT仓库号FROM职工表WHERE;工资=1210)AND仓库号IN(SELECT仓库号;FROM职工表)练习：求在WH2仓库工作的职工的最高工资。SELECTmax(工资)FROM职工表WHERE仓库号=WH2本节重点165.2.7分组与计算查询【格式】SELECT[ALL|DISTINCT]字段列表FROM表文件名[WHERE条件][GROUPBY分类字段列表…][HAVING过滤条件][ORDERBY排序项[ASC|DESC]]【说明】利用GROUPBY分类字段子句进行分组计算利用HAVING过滤条件子句进一步限定分组的条件。注意：HAVING子句必须跟随GROUPBY使用。本节重点17例23、求每个仓库的职工的平均工资。SELECT仓库号,AVG(工资)FROM职工表;GROUPBY仓库号例24、求至少有两个职工的仓库的平均工资。SELECT仓库号,COUNT(*),AVG(工资);FROM职工表;GROUPBY仓库号HAVINGCOUNT(*)=2练习：求STUD表中的各班的总人数。SELECT班级名,COUNT(班级名)AS总人数;FROMSTUD;GROUPBY班级名本节重点185.2.8利用空值查询例25、找出尚未确定供应商的订购单。SELECT*FROM定购单表;WHERE供应商号ISNULL例26、列出已经确定了供应商的订购单信息。SELECT*FROM定购单表;WHERE供应商号ISNOTNULL5.2.9别名与自连接查询例27、查询并显示各个学生的学号，姓名，课程名称及成绩。SELECTa.学号，a.姓名，b.课程名称，c.成绩;FROM学生a，课程b，成绩c;WHEREa.学号=c.学号ANDb.课程号=c.课程号本节重点195.2.10内外层互相关嵌套查询（略）5.2.11使用谓词和量词的查询在SQL语句的嵌套查询或子查询中，还可以使用下列两类运算符：量词：ANY、ALL、SOMEANY、SOME—在进行比较运算时，只要子查询中有一行能使结果为真，则结果就为真；ALL—要求子查询中的所有行都使结果为真时，结果才为真。谓词：EXISTS、NOTEXISTSEXISTS——用来检查在子查询中是否存在元组（记录）格式：SELECT字段列表FROM表WHERE表达式比较运算符[ANY|ALL|SOME]（子查询）或：WHERE[NOT]EXISTS（子查询）本节重点20例28、检索那些仓库中还没有职工的仓库的信息。SELECT*FROM仓库表WHERENOTEXISTS;（SELECT*FROM职工表WHERE仓库号=仓库表.仓库号）或：SELECT*FROM仓库表WHERENOTIN;（SELECT仓库号FROM职工表）例29、检索有职工的工资大于或等于WH1仓库中任何一名职工工资的仓库号。SELECTDISTINCT仓库号FROM职工表WHERE工资=;ANY(SELECT工资FROM职工表WHERE仓库号=“WH1”)或：SELECTDISTINCT仓库号FROM职工表WHERE工资=;（SELECTMIN(工资)FROM职工表WHERE仓库号=“WH1”）练习：检索那些仓库中至少有一名职工的仓库的信息。SELECT*FROM仓库表WHEREEXISTS;（SELECT*FROM职工表WHERE仓库号=仓库.仓库号）或：SELECT*FROM仓库表WHEREIN;（SELECT仓库号FROM职工表）本节重点215.2.12超联接查询在SQL语句中，在FROM子句中提供了一种称之为连接的子句，连接分为内连接和外连接，外连接又可分为左外连接、右外连接和全外连接。格式：SELECT字段列表FROM表1INNER|LEFT|RIGHT|FULLJOIN表2ON条件——指定连接条件[WHERE条件]（1）内连接内连接是指只有符合条件的记录，才出现在查询结果中。INNERJOIN——等价于JOIN，普通连接。（内连接）本节重点22例30、查询并显示各个学生的学号，所学课程名称及成绩。SELECT学号，课程名称，成绩FROM课程，成绩;WHERE成绩.课程号=课程.课程号如果采用内连接方式，则命令为：SELECT学号，课程名称，成绩;FROM课程aJOIN成绩bONa.课程号=b.课程号（2）外连接：外连接是指把两个表分为左右两个表。LEFTJOIN——左外连接：是指除满足连接条件的记录出现在查询结果中外，左侧表中不满足条件的记录也出现在查询结果中。RIGHTJOIN——右外连接：…，右侧表中不满足条件的记录也出现在查询结果中。FULLJOIN——全外连接：…，两个表中不满足条件的记录也出