SQL语言多表查询分组统计子查询数据表的更新操作事务处理.docx
- 文档编号:9903571
- 上传时间:2023-02-07
- 格式:DOCX
- 页数:15
- 大小:20.88KB
SQL语言多表查询分组统计子查询数据表的更新操作事务处理.docx
《SQL语言多表查询分组统计子查询数据表的更新操作事务处理.docx》由会员分享,可在线阅读,更多相关《SQL语言多表查询分组统计子查询数据表的更新操作事务处理.docx(15页珍藏版)》请在冰豆网上搜索。
SQL语言多表查询分组统计子查询数据表的更新操作事务处理
SQL语言多表查询、分组统计、子查询、数据表的
更新操作、事务处理
3.1、多表查询
3.1.1、基本语法
但是在多表查询之前首先必须处理一个问题:
例如:
现在求出雇员表中的总记录数(14条记录)
SELECTCOUNT(*)FROMemp;
例如:
现在求出部门表的总记录数(4条记录)
SELECTCOUNT(*)FROMdept;
所谓的多表查询就是直接在FROM语句之后加入若干张表,下面将emp和dept表进行多表查询
SELECT*FROMemp,dept;
以上确实完成了两张表的联合查询,但是查询出来的结果是56条记录。
部门表的记录总数*雇员表的记录总数=56条记录。
那么这样的结果在数据库中就称为笛卡尔积。
对于这样的结果明显不是最终查询者需要返回的结果,应该想办法去掉笛卡尔积。
所以如果要使用多表查
询,则必须按照以下的语句形式进行编写:
SELECT字段
FROM表1,表2
WHERE将两张表的关联字段进行比较,去掉笛卡尔积
以emp和dept表为例
1、雇员表结构:
No.字段名称字段类型字段作用
1EMPNONUMBER(4)表示的是雇员编号,长度为四位的整数
2ENAMEVARCHAR2(10)雇员的姓名,使用字符串表示,字符串的长度最大为10
3JOBVARCHAR2(9)工作,字符串表示,最大长度为9
4MGRNUMBER(4)雇员的直接上级领导编号
5HIREDATEDATE雇佣日期
6SALNUMBER(7,2)工资,工资长度一共是7位,其中整数占5位,小数占2位
7COMMNUMBER(7,2)奖金(佣金)
8DEPTNONUMBER
(2)部门编号
2、部门表结构:
No.字段名称字段类型字段作用
1DEPTNONUMBER
(2)雇员编号
2DNAMEVARCHAR2(14)部门名称
3LOCVARCHAR2(13)部门位置
两张表中都存在deptno字段,一般在数据库建表的时候都会把关联字段的名称统一。
例如:
使用关联字段消除掉之前多表查询的迪卡尔积。
SELECT*FROMemp,deptWHEREemp.deptno=dept.deptno;
但是如果一直按照以上的格式编写的话,对于表名称太长的情况时,如果在访问字段前还是使用表名称的
形式,会很麻烦。
所以一般可以为一张表起一个别名。
修改以上的范例:
SELECT*
FROMempe,deptd
WHEREe.deptno=d.deptno;
例如:
要求查询出雇员的编号、雇员的姓名、工资、部门的名称及位置
SELECTe.empno,e.ename,e.sal,d.dname,d.loc
FROMempe,deptd
WHEREe.deptno=d.deptno;
例如:
要求查询出每个雇员的姓名、工作、雇员工资、领导姓名、领导工资
?
如果要找到领导信息,则肯定需要emp表
?
如果要找到雇员信息,则肯定需要emp表
?
消除笛卡尔积的条件:
雇员的管理者的编号=雇员中的雇员编号
SELECTe.ename雇员姓名,e.job雇员工作,e.sal雇员工资,m.ename领导姓名,m.sal领导工资
FROMempe,empm
WHEREe.mgr=m.empno;
可以发现,本次查询是本表关联本表,那么这样的查询称为自身关联查询。
例如:
要求查询出每个雇员的姓名、工作、雇员工资、部门名称、领导姓名、领导工资
?
如果要找到领导信息,则肯定需要emp表
?
如果要找到雇员信息,则肯定需要emp表
?
如果要找到部门信息,则肯定需要dept表
SELECTe.ename雇员姓名,e.job雇员工作,e.sal雇员工资,m.ename领导姓名,m.sal领导工
资,d.dname部门名称
FROMempe,empm,deptd
WHEREe.mgr=m.empnoANDe.deptno=d.deptno;
注意:
在以上的查询中性能如何?
思考:
现在要求查询出每个雇员的姓名、工资、部门名称,雇员的工资及在公司的工资等级、领导的姓名、领导
的工资及工资在公司的工资等级。
salgrade:
工资等级表
No.字段名称字段类型字段作用
1GRADENUMBER等级编号
2LOSALNUMBER此等级的最低工资
3HISALNUMBER此等级的最高工资
通过sal指定losal和hisal的范围来去除笛卡尔积
?
部门表
?
领导表:
emp
SELECTe.ename,e.sal,d.dname,e.sal,s.grade,m.ename,m.sal,sm.grade
FROMempe,deptd,empm,salgrades,salgradesm
WHEREe.salBETWEENs.losalANDs.hisalANDe.deptno=d.deptnoANDm.salBETWEENsm.losalAND
sm.hisalANDe.mgr=m.empno;
进一步扩展:
在查询完的工资等级进行显示的修改,修改要求如下:
?
1:
“E等工资”
?
2:
“D等工资”
?
3:
“C等工资”
?
4:
“B等工资”
?
5:
“A等工资”
需要使用DECODE函数
SELECTe.ename,e.sal,d.dname,e.sal,
DECODE(s.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资'),
m.ename,m.sal,DECODE(sm.grade,1,'E等工资',2,'D等工资',3,'C等工资',4,'B等工资',5,'A等工资')
FROMempe,deptd,empm,salgrades,salgradesm
WHEREe.salBETWEENs.losalANDs.hisalANDe.deptno=d.deptnoANDm.salBETWEENsm.losalAND
sm.hisalANDe.mgr=m.empno;
3.1.2、SQL:
1999语法(了解)
在SQL语句中提供了另外一套对与表关联查询的支持语法:
SELECTtable1.column,table2.column
FROMtable1[CROSSJOINtable2]|
[NATURALJOINtable2]|
[JOINtable2USING(column_name)]|
[JOINtable2ON(table1.column_name=table2.column_name)]|
[LEFT|RIGHT|FULLOUTERJOINtable2ON(table1.column_name=table2.column_name)];
交叉连接(CROSSJOIN):
专门产生笛卡尔积
SELECT*FROMempCROSSJOINdept;
自然连接(NATUALJOIN):
自动进行关联字段的匹配
SELECT*FROMempNATURALJOINdept;
ON子句;由用户自己指定关联的条件
SELECT*FROMempJOINdeptON(emp.deptno=dept.deptno);
USING子句:
直接指定关联的条件
SELECT*FROMempJOINdeptUSING(deptno);
3.1.3、左、右连接
现在先查询出全部雇员和部门的信息
SELECT*FROMempe,deptdWHEREe.deptno=d.deptno;
查询结果:
以上的结果中显示了三个部门,但是实际上部门有四个
select*fromdept;
那么为什么之前列出的所有部门中没有40部门,因为在雇员表中没有一个雇员属于40部门。
因为匹配的结
果都是以雇员为主。
以等号左边为准,实际上以上的多表查询,可以直接通过以下的形式表现:
SELECT*FROMempe,deptdWHEREe.deptno=d.deptno(+);
也就是说,现在的代码以等号的左边为准,所以此连接称为左连接,如果现在以等号的右边为准呢?
那么
就称为右连接:
SELECT*FROMempe,deptdWHEREe.deptno(+)=d.deptno;
从程序运行结果可以发现,40部门出现了,那么意味着现在的匹配条件以等号右边为准,所以是右连接。
可以发现一个规律:
?
(+)在等号的左边属于右连接
?
(+)在等号的右边属于左连接
同样SQL:
1999语法也对左、右连接有所支持(了解)
例如:
实现左连接
SELECT*FROMempLEFTOUTERJOINdeptON(emp.deptno=dept.deptno);
例如:
实现右连接
SELECT*FROMempRIGHTOUTERJOINdeptON(emp.deptno=dept.deptno);
3.2.2、分组
在SQL语句中如果要想实现对数据的分组统计,则必须使用GROUPBY子句,此时,完整的SQL语法如下:
SELECTcolumn|*
FROMtable1als1,table2als2
[WHEREconditions]
[GROUPBYcolumn]
[ORDERBYcolumn]
例如:
求出每个部门的雇员数量
?
应该按照部门编号进行分组
SELECTdeptno,COUNT(*)FROMempGROUPBYdeptno;
例如:
求出每个部门的平均工资
SELECTdeptno,AVG(sal)FROMempGROUPBYdeptno;
以上已经完成了分组,但是对于分组统计中,是不能出现分组条件之外的字段。
例如:
有以下一段SQL语句
SELECTdeptno,AVG(sal),enameFROMempGROUPBYdeptno;
一旦执行之后出现以下的错误提示:
SELECTdeptno,AVG(sal),ename
第1行出现错误:
ORA-00979:
不是GROUPBY表达式
对于分组函数而言,可以单独使用,如果要连同其他字段一起查询,此字段必须是分组的字段,而且此语
句之中必须存在groupby子句:
SELECTdeptno,COUNT(*)FROMemp;
出现以下错误:
SELECTdeptno,COUNT(*)
第1行出现错误:
ORA-00937:
不是单组分组函数
而如果只写一个分组函数,则不会有任何的问题。
例如:
要求显示出平均工资大于2000的部门编号及平均工资
SELECTdeptno,AVG(sal)FROMempWHEREAVG(sal)>2000GROUPBYdeptno;
语句执行后出现以下的错误:
WHEREAVG(sal)>2000
第3行出现错误:
在WHERE语句之中是不能出现分组函数的。
只要是分组条件且此条件中要使用分组函数,就必须在HAVING
子句之中编写,与WHERE子句的功能一样,只是HAVING必须写在GROUP之后,没有GROUPBY绝对不能出现
HAVING。
SELECTdeptno,AVG(sal)FROMempGROUPBYdeptnoHAVINGAVG(sal)>2000;
完整的语句格式:
SELECTcolumn|*
FROMtable1als1,table2als2
[WHEREconditions]
[GROUPBYcolumn[HAVING分组条件]]
[ORDERBYcolumn]
例如:
显示非销售人员的工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事相同工作雇员
的月工资合计大于5000,输出结果按月工资的合计升序排列。
1、显示全部非销售人员的信息(限定条件,需要在WHERE子句中编写)
SELECT*FROMempWHEREjob<>'SALESMAN';
2、按雇员的工作分组,分组之后可以求出工资的总和
SELECTjob,SUM(sal)FROMempWHEREjob<>'SALESMAN'GROUPBYjob;
3、对分组的条件进行过滤,求出月工资的总和大于5000
SELECTjob,SUM(sal)FROMempWHEREjob<>'SALESMAN'GROUPBYjobHAVINGSUM(sal)>5000;
4、按照工资的总和进行排序
SELECTjob,SUM(sal)suFROMempWHEREjob<>'SALESMAN'
GROUPBYjobHAVINGSUM(sal)>5000
ORDERBYsu;
注意:
分组函数本身是允许嵌套的,但是,嵌套之后是不能出现分组条件的。
例如:
求出平均工资最高的部门
A、错误的代码
SELECTdeptno,MAX(AVG(sal))FROMempGROUPBYdeptno;
实际上以上的代码使用了分组函数的嵌套。
B、正确的代码
SELECTMAX(AVG(sal))FROMempGROUPBYdeptno;
原则:
当列中存在重复的内容时,才可以进行分组。
3.3、子查询
例如:
要求查询出工资比7566雇员的工资要高的全部雇员信息
1、确定出7566雇员的工资
SELECTsalFROMempWHEREempno=7566;
2、求出工资大于7566的雇员
SELECT*FROMempWHEREsal>(SELECTsalFROMempWHEREempno=7566);
需要注意的是,子查询可以在任意的位置上编写:
SELECT、FROM、WHERE、HAVING
子查询语法:
SELECT字段,[SELECTcolumn_listFROM表]别名
FROM表名称,[SELECTcolumn_listFROM表]别名
WHERE条件,[SELECTcolumn_listFROM表]别名
GROUPBY分组条件,[SELECTcolumn_listFROM表]别名
HAVING条件,[SELECTcolumn_listFROM表]别名
对于自查询而言又分为三类:
?
单列子查询:
返回的结果是一行一列
?
单行子查询:
一行多列,例如:
一条完整的记录
?
多行子查询:
返回多条记录
例如:
要求查询出工资比7654高,同时又与7788从事同一个工作的雇员信息
?
查询出7654雇员的工资
SELECTsalFROMempWHEREempno=7654;
?
查出7788的工作
SELECTjobFROMempWHEREempno=7788;
将以上的两条记录作为查询的条件
SELECT*FROMemp
WHEREsal>(SELECTsalFROMempWHEREempno=7654)AND
job=(SELECTjobFROMempWHEREempno=7788);
例如:
要求查询出工资最低的雇员的姓名、工作、工资
求出最低的工作
SELECTMIN(sal)FROMemp;
以此为条件进行查询
SELECT*FROMempWHEREsal=(SELECTMIN(sal)FROMemp);
例如:
查询出工资高于公司平均工资的雇员信息
SELECT*FROMempWHEREsal>(SELECTAVG(sal)FROMemp);
思考:
要求查询出:
部门名称,部门的雇员数,部门的平均工资,部门中最低收入的雇员的姓名。
部门中最低收入、雇员数、平均工资,按部门分组
SELECTdeptno,MIN(sal)fROMempGROUPBYdeptno;
?
多表关联及子查询
SELECTd.dname,e.ename,dm.avg,dm.min,dm.count
FROMdeptd,empe,(SELECTdeptno,AVG(sal)avg,MIN(sal)min,COUNT(empno)countFROMempGROUP
BYdeptno)dm
WHEREd.deptno=e.deptnoANDd.deptno=dm.deptnoANDe.sal=dm.min;
由此可见,子查询可以在任意的位置上出现,如果在FROM语句之后,实际上此查询就是表示出了一张临时
表,临时表的访问要有别名。
例如:
查询平均工资最底的工作名称及其平均工资
SELECTjob,AVG(sal)FROMemp
GROUPBYjobHAVINGAVG(sal)=(SELECTMIN(AVG(sal))FROMempGROUPBYjob);
在子查询中还提供了以下的比较运算符:
?
IN:
指定一个范围的内容
?
ANY
?
ALL
1、IN操作符
例如:
要求查询出各个部门最低工资的雇员信息
?
每个部门的最低工资是多个,之后根据工资查询的时候实际上就是应该指定出一个具体的范围。
SELECT*FROMempWHEREsalIN(SELECTMIN(sal)FROMempGROUPBYdeptno);
2、ANY操作符:
=ANY、>ANY、 ? =ANY: 功能与IN一致 例如: 以上的程序修改为=ANY SELECT*FROMempWHEREsal=ANY(SELECTMIN(sal)FROMempGROUPBYdeptno); ? 比最大的值要小 SELECT*FROMempWHEREsal ? >ANY: 比最小的值要大 SELECT*FROMempWHEREsal>ANY(SELECTMIN(sal)FROMempGROUPBYdeptno); 实际上ANY是要与里面的每一个内容进行比较的。 3、ALL操作符 ? 比最小的值要小 SELECT*FROMempWHEREsal ? >ALL: 比最大的值要大 SELECT*FROMempWHEREsal>ALL(SELECTMIN(sal)FROMempGROUPBYdeptno); 以上实际都是针对于多行子查询的应用。 多列子查询: 一次性返回多个列,如果要想比较,则必须多个列一起比较 SELECT*FROMemp WHERE(sal,NVL(comm,0))IN(SELECTsal,NVL(comm,0)FROMempWHEREdeptno=20); 4、查询练习 ? 求出那个部门的雇员数量 SELECTdeptno,COUNT(empno)FROMempGROUPBYdeptno; ? 求出数量大于1的所有部门 SELECTd.*,dc.c FROMdeptd,(SELECTdeptno,COUNT(empno)cFROMempGROUPBYdeptno)dc WHEREd.deptno=dc.deptno; 2、列出薪金比“SMITH”多的所有员工。 SELECT*FROMempWHEREsal>(SELECTsalFROMempWHEREename='SMITH'); 3、列出所有员工的姓名及其直接上级的姓名。 SELECTe.ename,m.ename FROMempe,empm WHEREe.mgr=m.empno; SELECTename,(SELECTenameFROMempWHEREempno=e.mgr)FROMempe; 4、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称。 ? 列出受雇日期早于其直接上级的所有员工的编号,姓名 SELECTe.empno,e.ename FROMempe,empm WHEREe.mgr=m.empnoANDe.hiredate ? 为其加入部门 SELECTe.empno,e.ename,d.dname FROMempe,empm,deptd WHEREe.mgr=m.empnoANDe.hiredate 5、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。 ? 右连接 SELECT* FROMempe,deptd WHEREe.deptno(+)=d.deptno; 6、列出所有“CLERK”(办事员)的姓名及其部门名称,部门的人数。 ? 列出所有的办事员 SELECTe.ename,d.dname,dc.c FROMempe,deptd,(SELECTdeptno,COUNT(empno)cFROMempGROUPBYdeptno)dc WHEREjob='CLERK'ANDe.deptno=d.deptnoANDd.deptno=dc.deptno; 7、列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数。 ? 求出最低薪金大于1500的各种工作 SELECTjob,MIN(sal) FROMemp GROUPBYjobHAVINGMIN(sal)>1500; ? 雇员的人数 SELECTjob,COUNT(*) FROMemp WHEREjobIN(SELECTjobFROMempGROUPBYjobHAVINGMIN(sal)>1500) GROUPBYjob; 8、列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。 SELECT*FROMempWHEREdeptno=(SELECTdeptnoFROMdeptWHEREdnameLIKE'%SALES%'); 9、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级。 ? 求出工资高于平均工资的雇员姓名,部门编号
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 语言 查询 分组 统计 数据表 更新 操作 事务处理