100801SQL02.docx
- 文档编号:24396404
- 上传时间:2023-05-27
- 格式:DOCX
- 页数:24
- 大小:110.33KB
100801SQL02.docx
《100801SQL02.docx》由会员分享,可在线阅读,更多相关《100801SQL02.docx(24页珍藏版)》请在冰豆网上搜索。
100801SQL02
1、课程名称:
SQL语句
多表查询、分组统计、子查询、数据表的更新操作、事务处理、查询练习
2、知识点
2.1、上次课程的主要知识点
1、基本的SQL查询语句结构:
SELECT{若干字段若干别名|*}
FROM表名称
[WHERE多个查询条件][ORDERBY排序条件{ASC|DESC}]
2、函数:
substr、to_char、to_date、nvl、decode
2.2、本次预计讲解的知识点
1、多表查询的作用及笛卡尔积的产生
2、分组函数的使用及分组统计的应用
3、子查询的作用,并结合多表查询、子查询,完成各种复杂的查询案例
4、使用SQL语句进行数据的增加、修改、删除操作
5、Oracle中的事务处理及死锁
3、具体内容
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.
字段名称
字段类型
字段作用
1
EMPNO
NUMBER(4)
表示的是雇员编号,长度为四位的整数
2
ENAME
VARCHAR2(10)
雇员的姓名,使用字符串表示,字符串的长度最大为10
3
JOB
VARCHAR2(9)
工作,字符串表示,最大长度为9
4
MGR
NUMBER(4)
雇员的直接上级领导编号
5
HIREDATE
DATE
雇佣日期
6
SAL
NUMBER(7,2)
工资,工资长度一共是7位,其中整数占5位,小数占2位
7
COMM
NUMBER(7,2)
奖金(佣金)
8
DEPTNO
NUMBER
(2)
部门编号
2、部门表结构:
No.
字段名称
字段类型
字段作用
1
DEPTNO
NUMBER
(2)
雇员编号
2
DNAME
VARCHAR2(14)
部门名称
3
LOC
VARCHAR2(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;
注意:
在以上的查询中性能如何?
例如:
求出以上三张表的笛卡尔积的数量
SELECTCOUNT(*)FROMempe,empm,deptd;
可以发现以上的查询产生了784条的记录。
思考:
现在要求查询出每个雇员的姓名、工资、部门名称,雇员的工资及在公司的工资等级、领导的姓名、领导的工资及工资在公司的工资等级。
·salgrade:
工资等级表
No.
字段名称
字段类型
字段作用
1
GRADE
NUMBER
等级编号
2
LOSAL
NUMBER
此等级的最低工资
3
HISAL
NUMBER
此等级的最高工资
|-通过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.losalANDsm.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.losalANDsm.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,deptd
WHEREe.deptno=d.deptno;
查询结果:
以上的结果中显示了三个部门,但是实际上部门有四个
select*fromdept;
那么为什么之前列出的所有部门中没有40部门,因为在雇员表中没有一个雇员属于40部门。
因为匹配的结果都是以雇员为主。
以等号左边为准,实际上以上的多表查询,可以直接通过以下的形式表现:
SELECT*
FROMempe,deptd
WHEREe.deptno=d.deptno(+);
也就是说,现在的代码以等号的左边为准,所以此连接称为左连接,如果现在以等号的右边为准呢?
那么就称为右连接:
SELECT*
FROMempe,deptd
WHEREe.deptno(+)=d.deptno;
从程序运行结果可以发现,40部门出现了,那么意味着现在的匹配条件以等号右边为准,所以是右连接。
可以发现一个规律:
·(+)在等号的左边属于右连接
·(+)在等号的右边属于左连接
同样SQL:
1999语法也对左、右连接有所支持(了解)
例如:
实现左连接
SELECT*FROMempLEFTOUTERJOINdeptON(emp.deptno=dept.deptno);
例如:
实现右连接
SELECT*FROMempRIGHTOUTERJOINdeptON(emp.deptno=dept.deptno);
3.2、分组统计
分组统计在一般的开发中使用较多,例如:
求出某一类工作的所有雇员数,那么这就是一种分组。
3.2.1、分组函数
是一些统计函数,之前讲解的统计一张表的全部记录数实际上就是一个分组函数,分组函数有以下几种:
·COUNT:
求出记录总数
·SUM:
求和
·AVG:
求平均值
·MAX:
求出最大值
·MIN:
求出最小值
例如:
验证以上的函数
SELECTCOUNT(*),SUM(sal),AVG(sal),MAX(sal),MIN(sal)FROMemp;
3.2.2、分组
在SQL语句中如果要想实现对数据的分组统计,则必须使用GROUPBY子句,此时,完整的SQL语法如下:
SELECTcolumn|*
FROMtable1als1,table2als2
[WHEREconditions]
[GROUPBYcolumn]
[ORDERBYcolumn]
例如:
求出每个部门的雇员数量
·应该按照部门编号进行分组
SELECTdeptno,COUNT(*)
FROMemp
GROUPBYdeptno;
例如:
求出每个部门的平均工资
SELECTdeptno,AVG(sal)
FROMemp
GROUPBYdeptno;
以上已经完成了分组,但是对于分组统计中,是不能出现分组条件之外的字段。
例如:
有以下一段SQL语句
SELECTdeptno,AVG(sal),ename
FROMemp
GROUPBYdeptno;
一旦执行之后出现以下的错误提示:
SELECTdeptno,AVG(sal),ename
*
第1行出现错误:
ORA-00979:
不是GROUPBY表达式
对于分组函数而言,可以单独使用,如果要连同其他字段一起查询,此字段必须是分组的字段,而且此语句之中必须存在groupby子句:
SELECTdeptno,COUNT(*)FROMemp;
出现以下错误:
SELECTdeptno,COUNT(*)
*
第1行出现错误:
ORA-00937:
不是单组分组函数
而如果只写一个分组函数,则不会有任何的问题。
例如:
要求显示出平均工资大于2000的部门编号及平均工资
SELECTdeptno,AVG(sal)
FROMemp
WHEREAVG(sal)>2000
GROUPBYdeptno;
语句执行后出现以下的错误:
WHEREAVG(sal)>2000
*
第3行出现错误:
在WHERE语句之中是不能出现分组函数的。
只要是分组条件且此条件中要使用分组函数,就必须在HAVING子句之中编写,与WHERE子句的功能一样,只是HAVING必须写在GROUP之后,没有GROUPBY绝对不能出现HAVING。
SELECTdeptno,AVG(sal)
FROMemp
GROUPBYdeptnoHAVINGAVG(sal)>2000;
完整的语句格式:
SELECTcolumn|*
FROMtable1als1,table2als2
[WHEREconditions]
[GROUPBYcolumn[HAVING分组条件]]
[ORDERBYcolumn]
例如:
显示非销售人员的工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事相同工作雇员的月工资合计大于5000,输出结果按月工资的合计升序排列。
1、显示全部非销售人员的信息(限定条件,需要在WHERE子句中编写)
SELECT*
FROMemp
WHEREjob<>'SALESMAN';
2、按雇员的工作分组,分组之后可以求出工资的总和
SELECTjob,SUM(sal)
FROMemp
WHEREjob<>'SALESMAN'
GROUPBYjob;
3、对分组的条件进行过滤,求出月工资的总和大于5000
SELECTjob,SUM(sal)
FROMemp
WHEREjob<>'SALESMAN'
GROUPBYjobHAVINGSUM(sal)>5000;
4、按照工资的总和进行排序
SELECTjob,SUM(sal)su
FROMemp
WHEREjob<>'SALESMAN'
GROUPBYjobHAVINGSUM(sal)>5000
ORDERBYsu;
注意:
分组函数本身是允许嵌套的,但是,嵌套之后是不能出现分组条件的。
例如:
求出平均工资最高的部门
A、错误的代码
SELECTdeptno,MAX(AVG(sal))
FROMemp
GROUPBYdeptno;
实际上以上的代码使用了分组函数的嵌套。
B、正确的代码
SELECTMAX(AVG(sal))
FROMemp
GROUPBYdeptno;
原则:
当列中存在重复的内容时,才可以进行分组。
3.3、子查询
在一个查询中包含了另外一个查询,此查询称为子查询。
例如:
要求查询出工资比7566雇员的工资要高的全部雇员信息
1、确定出7566雇员的工资
SELECTsalFROMempWHEREempno=7566;
2、求出工资大于7566的雇员
SELECT*FROMemp
WHEREsal>(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)
FROMemp
GROUPBYdeptno;
·多表关联及子查询
SELECTd.dname,e.ename,dm.avg,dm.min,dm.count
FROMdeptd,empe,(SELECTdeptno,AVG(sal)avg,MIN(sal)min,COUNT(empno)countFROMempGROUPBYdeptno)dm
WHEREd.deptno=e.deptnoANDd.deptno=dm.deptnoANDe.sal=dm.min;
由此可见,子查询可以在任意的位置上出现,如果在FROM语句之后,实际上此查询就是表示出了一张临时表,临时表的访问要有别名。
例如:
查询平均工资最底的工作名称及其平均工资
SELECTjob,AVG(sal)
FROMempGROUPBYjobHAVINGAVG(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); 3.4、数据操作 之前所讲解的全部都属于SQL的查询语法,在SQL语句之中也明确的定义出了关于数据的增加、修改、删除等操作。 但是为了保证emp表中的数据不被破坏。 在操作之前将表进行复制: CREATETABLEmyempASSELECT*FROMemp; 提示: 此语法只在Oracle中有用。 3.4.1、数据的增加操作 指的是向数据库表中增加一条完整的记录,增加的SQL语法: INSERTINTO表名称[(字段1,字段2,字段n…)]VALUES(值1,值2,值n,…) 但是需要注意的是,对于字符串的数据必须使用“'”括起来,而对于数字不需要单引号。 现在使用此语法向myemp表中增加数据 范例: 增加记录 ·使用最标准的做法 INSERTINTOmyemp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES(8888,'张三','清洁工',7369,sysdate,9000,100,40); 执行之后,会向数据库表中增加一条对应的记录。 ·如果在执行插入语句的时候没有明确的写出插入
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 100801 SQL02