oracle数据库基础：DQL语言基础（下）

已于 2022-03-09 09:12:40 修改
阅读量800 收藏 1
点赞数 1
分类专栏： 大杂烩 文章标签： oracle database
于 2021-11-22 19:09:23 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lalala_dxf/article/details/121473671
版权

DQL语言基础（下）

5、分组查询

group by

语法:

select 分组函数，列（要求出现在group by的后面）
from 表名
group by 分组的列表
order by 子句

在使用分组函数的时候：
如果还使用了group by分组，那么就表示先分组，然后对每一个小组使用聚合函数。
如果没有使用group by分组，那么就表示全部数据是一个默认小组，然后对这个全部数据使用聚合函数。

例一：查询s_emp表中所有员工的平均工资（没有使用group by进行分组）

select avg(salary) from s_emp;

例二：查询s_emp表中不同部门的员工的平均工资

select dept_id,avg(salary) from s_emp group by dept_id order by dept_id;

例三：查询s_emp表中每个部门员工的最高工资

select dept_id,max(salary) from s_emp group by dept_id;

group by …having…

having用来添加筛选条件：
语法：

select 分组函数，列（要求出现在group by的后面）
from 表名
group by 分组的列表
having 筛选条件
order by 子句

例题：查询s_emp表中部门的平均工资大于等于1400的部门

select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>=1400;

？？思考？？：where和having同样是用来筛选条件的，有什么区别
答：
（1）where和having都是做条件筛选的
（2）where执行的时间比having要早
（3）where后面不能出现组函数
（4）having后面可以出现组函数
（5）where语句要紧跟from后面
（6）having语句要紧跟group by后面

案例：
例一：查询s_emp表中，部门的平均工资大于等于1400的部门，并且显示出这些部门的名字，同时按照部门编号进行排序。
步骤：
（1）查询出基本需求

select  dept_id,avg(salary)
from  s_emp
group by  dept_id
having  avg(salary)>=1400
order by  dept_id;

（2）加入多表查询，并且分别给表起别名

select  se.dept_id,avg(se.salary)
from  s_emp se,s_dept sd
group by  se.dept_id
having  
avg(se.salary)>=1400
order by 
se.dept_id;

（3）查询出s_dept表中的部门名称，并且进行等值连接

select  se.dept_id,avg(se.salary),sd.name
from  s_emp se,s_dept sd
where  se.dept_id = sd.id
group by  se.dept_id
having  avg(se.salary)>=1400
order by  se.dept_id;

（4）select语句后出现了组函数，那么没有被组函数修饰的列，需要放到group by分组后面

select  se.dept_id,avg(se.salary),sd.name
from  s_emp se,s_dept sd
where  se.dept_id = sd.id
group by  se.dept_id,sd.name
having  avg(se.salary)>=1400
order by  se.dept_id;

例二：查询s_emp表每个部门的最大工资数，并且显示出这个最大工资的员工的名字，以及该部门的名字、该部门所属区域，并且使用部门编号进行排序
步骤：
（1）查询每个部门的最大工资，排序

select  max(salary),dept_id
from  s_emp
group by  dept_id
order by  dept_id;

（2）显示出这个最大工资的员工名字

select  max(s1.salary) ,s1.dept_id,s2.last_name
from  s_emp s1,s_emp s2
where  s2.dept_id=s1.dept_id 
group by  s1.dept_id,s2.last_name,s2.salary
having   max(s1.salary)=s2.salary
order by  s1.dept_id;

（3）显示出这个最大工资的员工名字，显示部门名称、部门所在地区

select  max(s1.salary) ,s1.dept_id,s2.last_name,d.name,r.name 
from  s_emp s1,s_emp s2,s_dept d, s_region r 
where  s2.dept_id=s1.dept_id  
and s1.dept_id=d.id 
and d.region_id=r.id 
group by  s1.dept_id,s2.last_name,s2.salary,d.name,r.name
having   max(s1.salary)=s2.salary
order by  s1.dept_id;

完整select语句执行顺序

现在一个select查询语句，由以下几部分组成：

select 字段1,字段2
from 表
where 条件
group by 分组条件
having 分组筛选条件
order by 排序条件

select语句执行顺序：

  1. from子句，组装来自表的数据，有可能是多张表
  2. where子句，基于指定的条件对记录行进行筛选
  3. group by子句，将数据划分为多个分组
  4. 使用聚合函数对没个小组中的数据进行计算
  5. having子句，进行条件筛选，这里可以使用聚合函数的计算结果
  6. 计算所有的运算表达式，主要是select部分7. order by子句，对结果集进行排序

聚合函数能够出现的位置

  1. select后面
  2. having后面
  3. order by后面

注意：
1、where后面一定【不能】出现组函数
2、如果select、having语句后面出现了组函数，那么select、having后面没有被组函数修饰的列，就必须出现在group by 后面 （非常重要）

group by和having的关系

  1. group by可以单独存在，后面可以不出现having语句
  2. having不能单独存在，如果需要出现，那么就必须出现在group by后面

6、连接查询（多表查询）

多表查询，又称表联合查询，即一条sql语句涉及到的表有多张，表中的数据通过特定的连接，进行联合显示。

分类

1、等值连接

利用一张表中某列的值，和另一张表中某列的值相等的关系，把俩张表连接起来，满足条件的数据才会组合。

例如：查询员工的名字、部门编号、部门名字

select last_name,dept_id,s_dept.id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id;

为了好区分，可以为每张表起别名

select se.last_name,se.dept_id,sd.id,sd.name 
from s_emp se,s_dept sd
where se.dept_id=sd.id;

2、不等值连接

假设数据库中有一张工资等级表 salgrade
内容如下：
id salgrade losal hisal
1 初级程序员 2000 4000
2 中级程序员 4000 6000

其中
gradeName 列表示等级名称
losal 列表示这个级别的最低工资数
hisal 列表示这个级别的最高工资数

让你查询出员工的名字、职位、工资、工资等级名称

select e.last_name, e.title, e.salray, s.gradeName
from   s_emp e, salgrade s
where   e.salray between s.losal and s.hisal

3、外连接

先分别在俩s_emp和s_dept表中插入新的数据
如下：

insert into s_emp(id,last_name) values(26,'tom');
insert into s_dept(id,name) values(60,'st');
commit;

注意，新员工tom不在任何部门中，新增部门st下面没有任何员工

此时使用等值连接查询结果，如下：

col last_name for a15
col name for a15

select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id;

在这里插入图片描述
新增加的员工tom和新增的部门st都没被查询出来，
原本应该查处27条数据呀，怎们会数据会少呢？
因为此时
新增的员工tom和部门表中的数据连接不上，
新增的部门st也和员工表中的数据连接不上，
那么这俩条数据都是在等值连接中查询不出来的。
由此需要使用外连接了

语法：

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组条件】
【having 筛选条件】
【order by 排序列表】

outer可以省去不写

3.1 左外连接

语法：
select 查询列表
from 表1 别名 【left outer】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组条件】
【having 筛选条件】
【order by 排序列表】

例如：查询所有员工，以及对应的部门的名字，没有部门的员工也要显示出来

select last_name,dept_id,name 
from s_emp left outer join s_dept
on s_emp.dept_id=s_dept.id;

在这里插入图片描述
在oracle中，左外连接，还可以简写成：

select last_name,dept_id,name 
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+);

如若想查出一个表中的数据，就在另一张表上添加‘+’，是不是好记了点

3.2 右外连接

语法：
select 查询列表
from 表1 别名 【right outer】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组条件】
【having 筛选条件】
【order by 排序列表】

右外连接和左外连接一样使用

select last_name,dept_id,name 
from s_emp right outer join s_dept
on s_emp.dept_id=s_dept.id;

在这里插入图片描述
在oracle中，左外连接，也可以简写成：

select last_name,dept_id,name 
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;
3.3 全连接

语法：
select 查询列表
from 表1 别名 【full】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组条件】
【having 筛选条件】
【order by 排序列表】

例如，查询所有员工 以及对应的部门的名字，没有任何员工的部门也要显示出来，没有部门的员工也要显示出来

select last_name,dept_id,name 
from s_emp full outer join s_dept 
on s_emp.dept_id=s_dept.id;

结果是左右俩边的表中，新增的数据tom和st，原来等值连接不上，现在也全都被查询出来了。

4、自连接

自连接就是一张表，自己和自己连接后进行查询

例如，查询每个员工的名字以及员工对应的管理者的名字

select s1.last_name,s2.last_name manager_name
from s_emp s1,s_emp s2
where s1.manager_id = s2.id;

7、子查询

子查询，也称嵌套查询，即一个select语句中嵌套了另外的一个或者多个select语句

子查询的思路就是，把第一个sql语句的查询结果，在第二个sql语句中使用，这时候第一条sql语句的结果，在第二条sql中就可以充当一个where条件中的一个值，或者充当一张虚拟的表。

例如，查询工资比Smith工资高的员工信息
步骤：
（1）查询Smith的工资数

select salary
from s_emp
where last_name='Smith'; 

//运行结果：  
 SALARY
 ----------       
 940

（2）查询工资比940高的员工信息

select last_name,salary
from s_emp
where salary>940;

（3）把第二步中的数字940替换成第一步中的sql语句即可，可以使用小括号包裹子查询

select last_name,salary
from s_empwhere salary>( 
	select salary 
	from s_emp 
	where last_name='Smith'
);

8、分页查询

因为表中数据过多，我们并不能一次全都查询出来交给前端去展示，而是需要每次按照提前设置好的要求，查询一部分数据给用户，当用户点击下一页的时候，再查出下一部分数据给用户。
实现分页的方式有很多，并且不同数据库，实现分页查询的方式也会不同，那么在Oracle中，可以使用伪列 rownum，再结合子查询来进行简单的实现。

8.1 伪列rownum

Oracle中，有一个特殊的关键字rownum，被称为：伪列。
rownum只有Oracle数据中才有。
rownum（伪列），就像表中的列一样，但是在表中并不存在。
伪列只能用于查询,不能像其他列一样随便操作。
rownum表示为一个查询的结果的行号，从1开始，连续不断。
例如：

select rownum,last_name
from s_emp;

结果：
在这里插入图片描述

伪列，可以根据查询结果的条数自动生成并且一定是从1开始连续不断的数字伪列
rownum的本质就是给查询的一行行结果标上行号

当伪列用在where条件中时，只能有以下操作：
rownum=1；表示查询第一行的内容

select last_name
from s_emp
where rownum=1

rownum>0;表示查询所有行的内容

select last_name
from s_emp
where rownum>0

rownum<任意数n；表示查询小于前n行的内容

select last_name
from s_emp
where rownum<7

注意：rownum要么等于1，要么大于0，要么小于某个数

8.2 分页查询的使用

例一：例如，查询s_emp表中，前10条数据

select rownum rn,id,last_name,dept_id 
from s_emp 
where rownum<=10

例二：查询s_emp表中，第3~7条数据

select t.id,t.last_name,t.dept_id
from ( 	
	select rownum rn,id,last_name,dept_id  
	from s_emp 
	where rownum<=7) t
where t.rn>=3;

这sql中，给rownum起一个别名rn，然后把这个sql语句作为第二条sql语句的子查询，并且把它当做一张虚拟的表，那么这时候，这个虚拟表中的rn是可以当做普通的字段进行操作了

也可以通过操作结果集的方式求：

where last_name,id
from s_emp
where rownum<=10
minus
where last_name,id
from s_emp
where rownum<=6

9、操作结果集

union：取两个结果集的并集
union all：把两个结果集合在一起显示出来
minus：第一个结果集除去与第二个结果集中相同的部分
intersect：求两个结果集的交集

前提条件是，俩个结果集中查询的列要完全一致（名称和类型）

例如，取俩个结果集的并集

select last_name,dept_id,name 
from s_emp,s_deptwhere s_emp.dept_id=s_dept.id(+)
union
select last_name,dept_id,name 
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;

例如，把俩个结果集合在一起显示出来

select last_name,dept_id,name 
from s_emp,s_deptwhere s_emp.dept_id=s_dept.id(+)
union all
select last_name,dept_id,name 
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;

例如，第一个结果集除去第二个结果集和它相同的部分

select last_name,dept_id,name 
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+)
minus
select last_name,dept_id,name 
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;

例如，求俩个结果集的交集

select last_name,dept_id,name 
from s_emp,s_dept
where s_emp.dept_id=s_dept.id(+)
intersect
select last_name,dept_id,name 
from s_emp,s_dept
where s_emp.dept_id(+)=s_dept.id;

10、千年虫

在早期的计算机的程序中，规定了的年份仅用两位数来表示。假如是1971年，在计算机里就会被表示为71。但是到了2000年的时候这个情况就出现了问题，计算机读取出之前存的一个日期88，结果就是2088年。这样的话，计算机内部对年份的计算就会出现问题，这个事情当时被称为千年虫。

为了处理这个问题，后面就有了一种新的日期格式：rr
如果在存储日期数据的时候，年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定，其规则如下：

规则1 :
如果指定年在00~ 49之间，并且当前年份在00~ 49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015

规则2:
如果指定年在50~ 99之间，并且当前年份在00~ 49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975

规则3:
如果指定年在00~ 49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115

规则4：
如果指定年在5099之间，并且当前年份在5099之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055

在这里插入图片描述

注意：rr格式并没有完全的解决俩位数年份保存的问题

持久的棒棒君
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle数据库基础学习
08-17
Oracle数据库基础学习是数据库学习的基础部分，本节课程将从DOS命令、SQL命令行、数据库简述、SQL语言简述、表等几个方面对Oracle数据库进行详细的介绍。 一、DOS命令 DOS命令是Disk Operating System的缩写，即...
Oracle数据操作和控制语言详解
03-05
北京　SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。其中用于定义数据的结构，比如创建、修改或者删除数据库；DCL用于定义数据库用户的权限；在这篇文章中我将详细...
五、Oracle学习笔记：DQL数据查询语句
bigdodo的博客
08-04 381
一、DQL:数据查询语言（select:挑选，选择意思） --格式： select colName,...,... from tname;   or   select colName as nickName,... from tname;(as 可以省略不写)             二、虚表(dual)：oracle中提供的一个没有数据的表名，是给一些基础操作（如计算等）提供的基础 ...
oracle数据库基础：DQL语言基础（上）
lalala_dxf的博客
11-22 656
1、基础查询 操作结果集 union：取两个结果集的并集 union all：把两个结果集合在一起显示出来 minus：第一个结果集除去与第二个结果集中相同的部分 intersect：求两个结果集的并集 2、条件查询 3、排序查询 4、函数 4.1 单行函数 4.2 聚合函数 4.3 转换函数 5、分组查询 6、连接查询（多表查询） 7、子查询 8、分页查询 伪列 ...
oracle之DQL
weixin_30929295的博客
07-05 86
一、单表查询 语法：select * from table where 条件 group by 分组 having 过滤分组 order by 排序 --查询平均工资低于2000的部门的最大工资和平均工资 select deptno, max(sal), avg(sal) from emp group by deptno having avg(sal) < 2000 ...
快速学习Oracle-pl/sql 基本语法
逍遥云恋
12-18 1459
什么是 PL/SQL? PL/SQL（Procedure Language/SQL）PLSQL 是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。 范例 1：为职工涨工资，每人涨 ...
Oracle怎么用dql查,Oracle学习(四) --- DQL语法
weixin_39540178的博客
04-12 95
标准DQL语法select distinct * | 列名 as 别名, 列表2 as 别名2... | 聚合函数from 表名 as 别名, 表名2 as 别名2 ,....where 查询条件group by 分组字段 having 分组条件order by 排序字段 asc | desc,....1、查询 -- 单表查询1.1、简单查询练习-- 1 查询水表编号为30408的业主记录sele...
Oracle第13章SQL语言基础.pptx
09-22
Oracle第13章SQL语言基础 本资源为Oracle第13章SQL语言基础的教学资料，涵盖了SQL语言的基础知识，包括SQL语言概述、数据查询、数据操纵、事务控制、SQL函数等内容。 SQL语言概述 SQL（Structured Query Language...
Oracle数据库表的基本操作以及空值问题的解决
12-14
文章目录1.Oracle数据库语言分类：2.区分delete,drop,truncate？（面试题）3.常见命令：4.Oracle数据库的数据类型：5.null:6.nvl(字段1,值1): 1.Oracle数据库语言分类： 1）DDL：数据定义语言，对数据结构起作用 ...
最全、最具体的ORACLE查询语法（DQL）
weixin_45011505的博客
06-25 903
基本语法： select [dinstinct|all]/[col_name] from [table_name] where [conditions] group by [col_name] --对查询结果进行分组 having [conditions] --分组后的筛选条件，需要基于groupby order by [col_name] --指定查询结果的排序规则 一、简单查询显示： 1.字段加别名： select [col_name] as [要叫的名字] from [table_nam
ORACLE数据库SQL语句基础知识总结三：数据操作语言（DQL）
GodofNight的博客
02-20 330
数据查询语言（DQL） 查询结构 SELECT *|列名 FROM 表1 JOIN 表2 ON 表1.字段=表2.字段 WHERE 条件 GROUP BY 列名 HAVING 条件 ORDER BY 列名[ASC/DESC] 查询结构执行顺序 SELECT *|列名 --执行
Oracle怎么用dql查,Oracle-DQL语句：查
weixin_39606019的博客
04-12 136
导航我们为什么需要DQL语句？DQL的关键词简单查询子查询通配符ORDER BY排序DUAL表GROUP BY子句HAVING子句多表联合查询1.内连接2.外连接3.交叉连接4.自然连接EXISTSUNION 与 UNION ALLINTERSECT 与 MINUS我们为什么需要DQL语句？当我们只需要知道数据内容，但并不对数据进行操作的时候，我们就需要使用DQL语句来进行数据的查询读取(只读)。...
Oracle数据库DQL(查询基本语句),查询语句
qq_45252842的博客
09-01 1415
Oracle数据库DQL(查询基本语句) DQL (Data Query Language) 数据查询语⾔，⽤于检索数据库中的数据，主要是 SELECT 语句，它在操作数据库时较为频繁 select 查当前用户下所有的表:select table_name from user_tables; 一.基本查询 1.查询表中的所有内容 select * from 表名; 2.查询表中的某一个字段 select 字段名 from 表名; 3.查询表中多个字段名 select 字段1名,字段2名
Oracle怎么用dql查,Oracle常用SQL语句之三：数据查询(DQL)语句
weixin_42350544的博客
04-12 95
SELECT语句功能投影操作：结果集是源表中的部分列。选择操作：结果集是源表中的部分行。连接操作：将两张表里的行按某种条件组合成一条长长的行放入结果集。最基本的用法：SELECT ……FROM……SELECT子句用于指定字段名，多个字段名用逗号隔开，*代表所有列。SELECT后面可以跟列，字符，表达式，DISTINCT关键字(去掉重复行)，函数，常量，组标识。完成对数据的处理。FROM子句用于指定...
Oracle-DQL（数据查询）笔记
lurenyi168的专栏
05-24 807
Oracle基础，SQL查询语句
Oracle SQL 内置函数大全（一）
xiang37的专栏
05-23 95
it's from http://www.gissky.com/Database/ShowArticle.asp?Sid=42&amp;ID=1590&amp;Page=1 SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL&gt; Select Ascii('A') A,Ascii('A') A,Ascii('0') Zero,Ascii(' ') Space...
【Oracle 数据库】奶妈式教程day03 排序查询
山西没老板
06-09 817
排序分组
Oracle -复杂查询总结-1
C++++ ++
03-07 551
一 表的复杂查询： 在实际应用过程中需要执行复杂的数据统计，经常需要显示多张表的数据，进行联合查询获取结果。 1：数据分组关键字： max,min,sum,avg,count。聚合函数，函数中可以是（字段）和（表达式）max, min, sum, avg, count。聚合函数，函数中可以是 （字段）和（表达式）max,min,sum,avg,count。聚合函数，函数中可以是（字段）和（表达式...
【Hive】第6章 查询
啥也不会~
05-09 318
查询
oracle数据库基本语言
最新发布
09-08
Oracle数据库的基本语言包括数据定义语言（DDL）、数据库操作语言（DML）、数据查询语言（DQL）、事务控制语言（TCL）、数据控制语言（DCL）等。其中，DDL用于创建、修改和删除数据库对象，如表、视图、索引等；DML...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值