第四章 关系数据库标准语言SQL 2.docx
《第四章 关系数据库标准语言SQL 2.docx》由会员分享，可在线阅读，更多相关《第四章 关系数据库标准语言SQL 2.docx（23页珍藏版）》请在冰豆网上搜索。
第四章关系数据库标准语言SQL2
第四章关系数据库标准语言SQL
4.1SQL概述
SQL是structuredquerylanguage结构化查询语言的缩写
SQL包含数据定义、数据操纵、数据控制功能。
SQL语言的特点：
1.一体化语言，包括数据定义、数据查询、数据操纵、数据控制功能。
2.一种高度非过程化的语言，写出命令要求，系统自动完成工作。
3.语言简洁。
接近自然语言，易学。
4.可以直接以命令方式嵌入程序设计中交互使用。
SQL命令动词
SQL功能
命令动词
数据查询（选择）
SELECT
数据定义（搭建框架结构）
CREATE、DROP、ALTER
数据操纵（记录操作）
INSERTUPDATEDELETE
数据控制（远程）
GRANTREVOKE
4.2查询功能
SQL语言的核心是查询.
SQL语言的查询命令也称SELECT命令,它的基本形式select---from---where---查询块。
即：
select字段1,字段2,…字段nfrom表1,表2,…,表nwhere等值条件
使用SQL语句时步骤：
1.找字段（select）
2.确定表（from）
3.找条件（where）
4.是否需要分组（groupby）
5.是否需要排序（orderby）
6.是否有输出（tofile/intotable/intoarray/intocursor）
等值条件
什么是等值条件?
1.字段名=字段值
2.用到两个或两个以上的表时,必须写表1.字段名=表2.字段名等等.
多个查询块可以嵌套.
SQL语句中各个命令短语的含义
Select说明要查询的数据（必须的）
From说明要查询的数据来自哪些表（可以是一个表也可以是多个表）（必须的）
Where说明查询的条件。
（这个可以有也可以没有）
Groupby用于对查询结果进行分组，可以利用它进行分组汇总。
（这个可以有也可以没有）
Having必须跟随GROUPBY短语之后，它用来限定分组必须满足的条件
Orderby用来对查询结果进行排序。
若有多个排序则用逗号分隔。
注意：
要对数据库中的表记录进行操作，应该要求DBA对表的结构有清晰的认识。
如何打开表查看表结构或表记录：
（1）在数据库设计器中选择表->右击->浏览
（2）用数据工作期（窗口->数据工作期->选择相应的表->浏览）
4.2.1简单查询
基于一个表的无条件查询，要学会判定查找的目标。
例4.1从职工关系中检索所有工资值。
目标：
所有工资值
来自：
职工表
条件：
无
Select工资from职工
结果中有重复的值，若去掉重复值用DISTINCT短语（笔试常考）
Selectdistinct工资from职工
例4.2检索仓库关系中的所有元组
目标：
所有字段
来自：
仓库表
条件：
无
正常情况下，我们应该打开那个表看看里面有哪些字段。
Select仓库号,城市,面积from仓库
用“*”通配符表示所有的字段
Select*from仓库
例4.3检索工资多于1230元的职工号
目标：
职工号
来自：
职工表
条件：
工资多于1230
Select职工号from职工where工资>1230
注意：
在SQL中用where指定条件,在VF中用FOR或WHILE指定条件。
例4.4检索哪些仓库有工资多于1210元的职工
目标：
仓库号
来自：
职工表
条件：
工资多于1230
Select仓库号from职工where工资>1210
去掉重复的仓库号:
Selectdistinct仓库号from职工where工资>1210
例4.5给出在仓库”WH1”或”WH2”工作并且工资少于1250元的职工号
目标：
职工号
来自：
”职工”表
条件：
在”WH1”工作且工资多于1250或在”WH2”工作且工资多于1250
select职工号from职工where仓库号="WH1"AND工资<1250or仓库号="WH2"AND工资<1250（优先级：
and>or）
或者：
select职工号from职工where工资<1250and（仓库号="WH1"or仓库号="WH2"）
注意：
WH1不能写成wh1
4.2.2简单的连接查询
当要查找的字段分别在两个或两个以上的表中存放时，我们需要进行连接查询。
注意：
1.如果要查找的目标字段在多表中都有时，要加表名前缀加以限定。
2.如果FROM中有2个或多于2个表时，这两个表之间肯定有一种联系（公共字段），否则构不成检索表达式，这样必须在WHERE之后加等值连接条件。
例4.6找出工资多于1230元的职工号和他们所在的城市。
目标：
职工号,城市
来自：
”职工”表,“仓库”表
条件:
工资多于1230
Select职工号,城市from职工,仓库where工资>1230and职工.仓库号=仓库.仓库号
例4.7找出工作面积大于400的仓库的职工号以及这些职工工作的城市。
目标：
职工号,城市
来自：
”职工”表,“仓库”表
条件:
面积大于400
Select职工号,城市from职工,仓库where面积>400and职工.仓库号=仓库.仓库号
4.2.3嵌套查询
所要查询的结果在一个表中，但相关的条件却在另外的一个表中，这时用嵌套查询很方便。
注意：
在嵌套时，内层查询的目标字段一定是外层查询的条件并且括号不能少。
例4.8哪些城市至少有一个仓库的职工的工资为1250元
select城市from仓库,职工where工资=1250and仓库.仓库号=职工.仓库号
用嵌套查询：
select城市from仓库where仓库号in（select仓库号from职工where工资=1250）
例4.9查询所有职工的工资都多于1210元的仓库的信息
目标：
仓库的所有信息
来自：
职工,仓库
条件：
工资都多于1210
select*from仓库where仓库号in（select仓库号from职工where工资>1210）
这条语句结果:
WH1北京370
WH2上海500
WH3广州200可以发现在WH1中包含1210的也检索出来了.
select*from仓库where仓库号notin（select仓库号from职工where工资<=1210）
结果:
WH2上海500
WH3广州200
WH4武汉400
但是在WH4仓库中没有职工.
select*from仓库where仓库号notin（select仓库号from职工where工资<=1210）and仓库号in（select仓库号from职工）
结果:
WH2上海500
WH3广州200
说明:
本例是一个逆向思维的特例,以后凡是遇到….都…..的就用notin
用in没有语法错误，但是逻辑上有问题。
例4.10找出和职工E4挣同样工资的所有职工
Select职工号from职工where工资=（select工资from职工where职工号=”E4”）
注意：
当内层查询结果为唯一值时用“=”
4.2.4几个特殊运算符
1.between…..and…..（在…范围内，…和…之间）
例子4.11检索出工资在1220元到1240元范围内的职工信息。
目标：
职工的全部信息
来自：
职工表
条件：
工资>=1220and工资<=1240
Select*from职工where工资>=1220and工资<=1240
用between….And….写
Select*from职工where工资between1220and1240
2.like运算符
like运算符是SQL语句中字符串匹配运算符，用到的通配符是%和_
%表示0个或多个字符
_表示任意一个字符
要注意区别like（串1，串2）字符串匹配函数。
在这个函数中串1中可以含有通配符*和？
，若串2中含有通配符则返回.F.。
*表示0个或多个字符
?
表示任意一个字符
小结：
%和_只能用在SQL语句的WHERE条件后
*和?
只能用在变量名和函数的参数中
例4.12在供应商关系中检索出全部公司的信息（不要工厂或其他供应商信息）
目标：
…公司的全部信息
来自：
供应商
条件：
只要带有”公司”字样
Select*from供应商where供应商名like“%公司”
等价的两条语句：
select*from供应商where"公司"$供应商名
select*from供应商whereat（"公司",供应商名）<>0（!
=0）
否定运算符!
=不等于，notin，notbetween
例4.13找出不在北京的全部供应商信息。
Select*from供应商where地址!
=”北京”
Select*from供应商wherenot（地址=”北京”）
例4.14找出工资不在1220元和1240元之间的全部职工信息。
select*from职工where工资notbetween1220and1240
4.2.5排序
SQL语句可以将查询的结果按照一定的顺序排列，用orderby语句排序。
Orderby并不是条件，它总是放在查询语句的最后，且不能嵌套。
升序：
默认或指定ASC关键字
降序：
指定DESC关键字，若有多个降序则用逗号隔开，都应指定DESC
例4.14按职工的工资值升序检索出全部职工信息
Select*from职工orderby工资
降序：
Select*from职工orderby工资desc
例4.15先按仓库号排序，再按工资排序输出全部职工信息
Select*from职工orderby仓库号,工资
先按仓库号升序排序，若仓库号相同再按工资降序：
按仓库号和工资降序排列：
Select*from职工orderby仓库号desc,工资desc
4.2.6简单的计算查询
SQL不仅有一般的检索功能，还具有自动计算的功能。
用于计算的5个函数：
COUNT（）计算函数，SUM（）求和函数，AVG（）求平均值函数，MAX（）求最大值函数，MIN（）求最小值函数
虚字段：
本来表中没有这个字段，指定虚字段可有助我们了解表结构
例4.16找出供应商所在地的数目
Selectcount（*）from供应商
虚字段：
用AS或空格表示
Selectcount（*）as供应商所在地数目from供应商
Selectcount（*）供应商所在地数目from供应商
例4.17求支付的工资总数
Selectsum（工资）from职工
Selectsum（工资）as总工资from职工
注意：
若用distinct过滤那就得不到正确的结果了。
例4.18求北京和上海的仓库职工的工资总和
目标：
工资总和
来自：
职工表
条件：
北京和上海的仓库职工
Selectsum（工资）from职工,仓库where职工.仓库号=仓库.仓库号and（城市=”北京”or城市=”上海”）
嵌套查询：
（尽量不用嵌套，不容易理解）
Selectsum（工资）from职工where仓库号in（select仓库号from仓库where城市=”北京”or城市=”上海”）
注意：
这里为什么用OR而不用AND?
（课本第60页）
因为一个职工要么在北京工作，要么在上海工作，不可能同时在北京和上海工作。
例4.19求所有职工的工资都多于1210元的仓库的平均面积
对于求：
“所有的…..都….的“这种题型用notin
Selectavg（面积）from仓库where仓库号notin（select仓库号from职工where工资<=1210）and仓库号in（select仓库号from职工）
注意：
要排除没有职工的仓库WH4
例4.20求在WH2仓库工作的职工的最高工资值
Selectmax（工资）from职工where仓库号=”WH2”
Selectmin（工资）from职工where仓库号=”WH2”
4.2.7分组与计算查询（一定一定要领会掌握）
分组是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。
分组用GROUPBY子句,进一步限定分组的条件用HAVING子句
GroupBy语句从英文的字面意义上理解就是“根据（by）一定的规则进行分组（Group）”。
什么时候用到分组?
1.当用到那5个计算函数时99%需要分组:
count（）,sum（）,avg（）,max（）,min（）
2.每个、每组、每人、每种、各个，各组、各公司、之类的，这种带"每"或是带"各"的都需要分组,如按每（各）组人的工资排序：
groupby组号orderby工资。
3.在需要对某个有重复值的字段进行分组汇总数据，
比如说：
有个学生表，数据如下：
学生表
姓名课程名成绩
张三语文90
李四数学95
张三数学88
李四语文85
现在如果要统计每个学生的总分，那么就需要用到groupby
select姓名,sum（成绩）as总成绩from学生groupby姓名
得到的结果如下：
姓名总成绩
张三178
李四180
分组时的依据是SELECT语句中的目标字段
只有那1%的情况不需要分组:
求（所有的…）时候不需要分组.
例4.21求每个仓库的职工的平均工资.
目标:
平均工资
来自:
职工
条件:
每个仓库的职工的
Select仓库号,avg（工资）from职工groupby仓库号
说明:
GROUPBY子句一般跟在WHERE子句之后,没有WHERE子句时,跟在FROM子句之后.可以根据多个属性进行分组.
例4.22求至少有两个职工的每个仓库的平均工资
Select仓库号,count（*）,avg（工资）from职工groupby仓库号havingcount（*）>=2
说明:
having子句总是跟在groupby之后,不可以单独使用.
4.2.8利用空值查询
在SQL语句中查询空值时用ISNULL而不能用=NULL
空值用null表示
例4.23找出尚未确定供应商的订购单
Select*from订购单where供应商号isnull
例4.24列出已经确定了供应商的订购单信息
Select*from订购单where供应商号isnotnull
4.2.9别名与自连接查询
1.别名
别名就是对表名另外取名
有时用表名作前缀，表名很长麻烦，用别名就会简单些。
别名不是必须的。
SQL语句允许在FROM短语中定义别名。
格式：
表名别名
比如学生数据管理aa就是把aa当作学生数据管理表来使用
Select供应商名from供应商,订购单,职工,仓库where地址=”北京”and城市=”北京”And供应商.供应商号=订购单.供应商号and订购单.职工号=职工.职工号and职工.仓库号=仓库.仓库号
Select供应商名from供应商s,订购单p,职工e,仓库wwhere地址=”北京”and城市=”北京”Ands.供应商号=p.供应商号andp.职工号=e.职工号ande.仓库号=w.仓库号
2.自连接
将同一关系与其自身进行连接，就称自连接。
如何理解自连接？
（递归联系）
出自同一值域的两个不同的属性，可以与本关系中另外一些元组有一种对应关系。
雇员（雇员号，雇员姓名，经理）
在雇员这个关系中，雇员号和经理出自同一值域，但他们是一种领导与被领导的关系
雇员号
雇员姓名
经理
E3
赵涌
E4
钱潮
E3
E6
孙洁
E3
E8
李渌
E6
如何实现自连接？
利用别名来实现，通过定义别名，可以把一个表虚拟成两个表来用。
在关系的自连接中，别名是必须得。
例子4.25根据雇员关系列出上一级经理及其职员的清单（难！
）
select雇员姓名,"领导",雇员姓名from雇员where雇员.雇员号=雇员.经理
输出结果：
空（计算机不理解这句SQL语句的含义）
SelectS.雇员姓名,“领导”,E.雇员姓名from雇员S,雇员EwhereS.雇员号=E.经理
注意：
在SELECT字段中可以加常量，比如“领导”。
这里用S.雇员号=E.经理而不用S.雇员号=E.雇员号（因为这是递归关系）
4.2.10内外层互相关嵌套查询
这种嵌套题型：
外层查询的条件需要内存查询的结果
例4.26列出每个职工经手的具有最高总金额的订购单信息。
（难！
）
select*from订购单outwhere总金额=（selectmax（总金额）from订购单inwhereout.职工号=in.职工号）
如何理解每个职工经手？
每一个职工都有唯一的职工号，每一个职工的订单不止一个（E3经手了OR67，OR79，OR90，OR91；E1经手了OR80，OR73；条件每个职工经手能表示成订购单.职工号=订购单.订购单号吗？
），每一个订单都有唯一的订单号，在这一个表中不好体现职工号和订购单号的关系，这样我们把这一个订单表虚拟成两个表，这两个表中职工号和订购单号有一定关联的关系。
找出最高总金额。
4.2.11使用量词和谓词的查询
在嵌套的子查询中还用到一些运算符：
1.量词
ANY只要子查询中有一行能使结果为真，则结果就为真（同SOME）
SOME只要子查询中有一行能使结果为真，则结果就为真（同ANY）
ALL要求子查询中所有行都使结果为真才为真
这三个量词的使用形式：
<表达式>比较运算符ANY|SOME|ALL
例4.29检索有职工的工资大于或等于WH1仓库中任何一名职工的工资的仓库号
SELECTdistinct仓库号from职工where工资>=any（select工资from职工where仓库号=”WH1”）
分析：
内层查询是找职工表中的工资，外层找仓库号，外层依据的条件只要内层中有一名职工的工资能让外层职工的工资大于或等于就可以
或者：
SELECTdistinct仓库号from职工where工资>=some（select工资from职工where仓库号=”WH1”）
以上两条语句等价于：
distinct仓库号from职工where工资>=（selectmin（工资）from职工where仓库号=”WH1”）
说明：
any,some和min（）等价，因为只要在内层中找一个最小值能满足存在一个的条件，就可以了。
（重要切记！
！
！
）
例4.30检索有职工工资大于或等于WH1仓库中所有职工的工资的仓库号
Selectdistinct仓库号from职工where工资>=all（select工资from职工where仓库号=”WH1”）
分析说明：
内层查询是找WH1仓库中的所有职工的工资，外层查询是在职工表中找仓库号，依据的条件是只要工资大于或等于WH1仓库中所有职工的工资
等价于：
Selectdistinct仓库号from职工where工资>=（selectmax（工资）from职工where仓库号=”WH1”）
分析说明：
外层查询职工工资只要比WH1仓库中职工工资的最大值都大，那么它就大于或等于WH1仓库中所有职工的工资
ALL和max（）等价
2.谓词
Exists判断检查在子查询中存在元组
Notexists判断检查在子查询中不存在元组
注意：
这两个谓词只是用来判断子查询中是否有结果返回，本身没有任何运算或比较。
例4.27检索那些仓库中还没有职工的仓库的信息。
分析：
仓库表和职工表是通过“仓库号”建立起一定关联的，一个职工只对应于一个仓库，如果仓库表中的仓库号不在职工表中，那么这个仓库中就没有职工。
（只要职工表中没有仓库号，就说明对应的仓库中没有职工）
select仓库号from仓库wherenotexists（select仓库号from职工where仓库.仓库号=职工.仓库号）
select仓库号from仓库where仓库号notin（select仓库号from职工）
完善一下：
select*from仓库wherenotexists（select*from职工where仓库.仓库号=职工.仓库号）
select*from仓库where仓库号notin（select仓库号from职工）
例4.28检索那些仓库中至少已经有一个职工的仓库的信息
Select*from仓库whereexists（select*from职工where仓库.仓库号=职工.仓库号）
等价于：
Select*from仓库where仓库号in（select*from职工where仓库.仓库号=职工.仓库号）
4.2.12超连接查询
原来的连接都是只有满足连接条件，相应的结果才会出现在结果表中。
普通连接：
只有满足连接条件的记录才出现查询结果中
超连接：
首先保证一个表中满足条件的元组都在结果表中，然后将满足连接条件的元组与另一个表的元组进行连接，不满足连接条件的则应该将来自另一个表的属性值设置为空值。
连接运算的格式：
Select…from表1inner|left|right|full
join表2on连接条件where…
关键短语：
Innerjoin等价于join,为普通连接
Leftjoin左连接
Rightjoin右连接
Fulljoin全连接
On指定连接条件
Innerjoin等价于join
例：
普通连接：
只有满足连接条件的记录才出现查询结果中
select仓库.仓库号,城市,面积,职工号,工资from仓库join职工on仓库.仓库号=职工.仓库号
等价的语句：
Select仓库.仓库号,城市,面积,职工号,工资from仓库，职工w
升级会员 