ORACLE 第 2 章

旧城等待， 2024-03-25 20:09 68阅读 0赞

**ORACLE 数据库**

第 2 章

ORACLE 查询

## 一、单表查询 ##

### （一）简单条件查询 ###

#### 1.精确查询 ####

需求：查询员工号为7369的员工

select * from EMP where empno='7369';

查询结果：

![5cccc54dcc4544adafd1d309a3c980c4.png][]

#### 2.模糊查询 ####

需求：查询员工名包含“S”的业主记录

查询语句：

select * from EMP where  ename like '%S%';

查询结果：

![d70883eaef664c26b7f9a3d058a20844.png][]

#### 3. and 运算符 ####

需求：查询员工名包含“S”并且工作包含“C”的记录

select * from EMP where ename like '%S%' and job like '%C%';

查询结果：

![241e427fdae14a28a74a21fd998fe27d.png][]

#### 4. or 运算符 ####

需求：查询员工名包含“S”或者工作包含“C”的记录

查询语句：

select * from EMP where ename like '%S%' or job like '%C%';

查询结果：

![5acebe8dfef64755ad7bc5d3e0d7ee7d.png][]

#### 5. and 与 or 运算符混合使用 ####

需求：查询员工名包含“S”或者工作包含“C”的记录，并且员工号为7369的员工记录。

查询语句：

select * from EMP where (ename like '%S%' or job like '%C%') and empno='7369';

查询结果：

![05ab8a936d9c45b080cca24af0f5f5a2.png][]

因为 and 的优先级比 or 大，所以我们需要用 ( ) 来改变优先级。

#### 6. 范围查询 ####

需求：查询员工薪水大于1000，并且小于3000的记录我们可以用>= 和<=来实现

查询语句：

select * from emp where sal <=3000 and sal >=1000;

我们也可以用 between .. and ..来实现

select * from emp where sal between  1000 and 3000;

查询结果：

![902b8152fd8d43d8b06bf5806f205310.png][]

#### 7. 空值查询 ####

需求：查询 emp 表中 comm 为空的记录

查询语句：

select * from emp where comm is null;

查询结果：

![f156ea7a66b642d8b717794b2d66c660.png][]

需求：查询 emp 表中 comm 不为空的记录

查询语句：

select * from emp where comm is not null;

查询结果：

![90a44f13088f44a3a51461d40b2b2189.png][]

### （二）去掉重复记录 ###

需求：查询emp 表中的 deptno,不重复显示

查询语句：

select distinct deptno from emp;

查询结果：

![b4cf217710d9443eabfb425c83e55e16.png][]

### （三）排序查询 ###

#### 1.升序排序 ####

需求：对 emp 表按薪水进行升序排序

查询语句：

select * from emp order by sal asc;

查询结果：

![3283ddaf96bc4073b4ae6512720afb9a.png][]

1.  #### 降序排序 ####

select * from emp order by sal desc;

### （四）基于伪列的查询 ###

在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。

接下来学习两个伪列：ROWID 和 ROWNUM。

#### 1、ROWID ####

表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地址。**使用 ROWID 可以快速的定位表中的某一行**。ROWID 值可以唯一的标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可以显示行是如何存储的。

查询语句：

select rowID,e.* from emp e;

查询结果：

![0f42f036e22c4199a76011b60ab9b0a0.png][]

我们可以通过指定 ROWID 来查询记录

select rowID,e.* from emp e where ROWID='AAASNYAAGAAAACWAAB';

![0383898b2cbe43d6bd626b7bd2f30af5.png][]

#### 2、ROWNUM ####

在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回 1，第二行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数。

select rownum,e.* from emp e;

![08d5d453602145418f9dd9aa4421a2da.png][]

**我们的分页查询需要用到此伪列，在本章第四小节详细讲解。**

### （五）聚合统计 ###

ORACLE 的聚合统计是通过分组函数来实现的，与 MYSQL 一致。

#### 1. 聚合函数 ####

（1）求和 sum

需求：统计所有员工的薪水之和

select sum(sal) from emp;

（2）求平均 avg

需求：统计所有员工的平均薪水

select avg(sal) from emp;

（3）求最大值 max

需求：统计所有员工的最高薪水

select max(sal) from emp;

（4）求最小值 min

需求：统计所有员工的最低薪水

select min(sal) from emp;

（5）统计记录个数 count

需求：统计所有用工数

select count(1) from emp;
    select count(*) from emp;

#### 2. 分组聚合 Group by ####

需求：按部门分组统计薪水总和

select deptno,sum(sal) from emp group by deptno;

![6bf197edad654ca281e330d912f95b04.png][]

#### 3. 分组后条件查询 having ####

需求：按部门分组统计薪水总和，并且部门薪水总和大于1万的

select deptno,sum(sal) as sumSal from emp group by deptno having sum(sal)>10000;

分组：

select后面的列可以是：

①、分组列，group by 后面的列

②、聚合函数

having 筛选条件：类似于where 可以是group by 后面的列，也可以是聚合函数

## 二、连接查询 ##

#### （一）多表内连接查询 ####

（1）需求：查询员工部门名称

select * from emp e,dept d where e.deptno = d.deptno;

（2） 查询各部门的平均薪水及部门编号，部门名称。

SELECT AVG(SAL),DNAME,EMP.DEPTNO FROM EMP,DEPT where EMP.DEPTNO = DEPT.DEPTNO GROUP BY DNAME,EMP.DEPTNO;

#### （二）左外连接查询 ####

查询员工姓名及领导者姓名

select a.ename AS 员工姓名 ,b.ename AS 领导姓名
    from emp a LEFT JOIN emp b on a.mgr = b.empno;

其他例子：

用户表、商品表：根据用户id查询用户所属商品

商品表、图片表：根据商品ID查询商品所属图片

#### （三）右外连接查询 ####

通上类似，right join

## 三、子查询 ##

#### （一）where 子句中的子查询 ####

##### 1. 单行子查询 #####

*  只返回一条记录

*  单行操作符

![898e2d52cd7c49fb97e09e2e3931539f.png][]

1.  查询薪水的最高值

select sal from emp where sal =  (select max(sal) from emp);

2、 查询‘SMITH’的领导姓名

SELECT ENAME FROM EMP WHERE EMPNO = (SELECT MGR FROM EMP WHERE ENAME = 'SMITH');

3、 查询部门名称是‘SALES’的员工信息

SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');

##### 2. 多行子查询 #####

1.  返回了多条记录

1.  多行操作符

![5db6682199a644fd9450585e22940e7f.png][]

**in 运算符**

**1、** 查询各部门中最高薪水的员工编号，姓名…

select empno , ename,DEPTNO from emp WHERE SAL in (select MAX(SAL) from EMP GROUP BY DEPTNO);

2、 查询各部门最高薪水的员工信息（有坑）（薪水和部门编号都需要匹配）

select * from EMP where (deptno,SAL) in (select deptno,MAX(sal) from emp GROUP BY DEPTNO);

3、 查询各部门最高薪水的员工信息

select * from EMP where (deptno,SAL) in (select deptno,MAX(sal) from emp GROUP BY DEPTNO);

### （二）from 子句中的子查询 ###

select * from ( select * from emp )

## 四、分页查询 ##

需要准备t\_user进行批量插入数据，我们这里循环插入一些数据，使用前面讲解的JDBC进行插入数据

### （一）简单分页 ###

需求：分页用户表t\_user，每页 10 条记录

分析：我们在 ORACLE 进行分页查询，需要用到伪列 ROWNUM 和嵌套查询我们首先显示前 10 条记录，语句如下：

select rownum,tt.* from T_USER tt where rownum<=10;

显示结果如下：

![3b5cb270a1194743b2dcd916b298342a.png][]

那么我们显示第 11 条到第 20 条的记录呢？编写语句：

select rownum,t.* from T_USER t where rownum>10 and rownum<=20;

查询结果：

![eca044182b4e4d82be96fa8518128f8c.png][]

嗯？怎么没有结果？

这是因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于” 符号，只能使用“小于”或“小于等于” ，只用“等于”也不行。

那怎么办呢？我们可以使用子查询来实现

select * from (select rownum r,t.* from T_USER t where rownum<=20) where r>10;

查询结果如下：

![96d9b6411f6744b0a228ab5e2a55950e.png][]

这样分页也可以

select * from (select rownum r,t.* from T_USER t ) where r <=20 and r>10;

![defcf6a34dd0425b92722c260f62a4e8.png][]

**注：rownum是虚拟列，是得出结果后，再进行计算的。所以，只能是小于的，无法大于。**

**用子查询分页也能满足需求，但是如果是放到开发中，每次最里面的SQL都要带一个rownum不方便，所以最里面的SQL可以不写rownum，最外面再加一层。这样我们开发直接写SQL即可，不需要关注rownum！！！**

### （二）改造分页 ###

如下两种分页都是类似，都能满足分页需求

select * from (select rownum r,t.* from T_USER t where rownum<=20) where r>10;
    
    select * from (select rownum r,t.* from T_USER t ) where r <=20 and r>10;

但是开发中，每次都要自己带一个rownum？？？？那岂不是比较累，改造如下：

select * from ( select rownum r,t.* from ( select * from t_user t  ) t where rownum <=20 )  where r >10;
    或者
    select * from ( select rownum r,t.* from ( select * from t_user t  ) t )  where r <= 20 and r > 10;

这时候最内层的SQL就是开发人员自己的实际需求，写成子查询、分页、分组等都是可以，不需要关心rownum。

三层分页，查询条件、结果集，符合我们的理解，类似于mysql中的limit

**总结：**

rownum是Oracle引入的虚列。

在物理上这个虚列并不存在，只是在查询时才构造出来。伪列通常是自由分配的，用户无法执行修改等操作。

关于rownum有以下主要特点：

1）rownum不属于任何表。

2）rownum存在的前提，先有结果表。

3）rownum总是从1开始。

4）rownum一般只和<(<=)一起用。

5）使用rownum进行分页查询需要把rownum转化为实列，并针对rownum查询。

## 五、单行函数 ##

### （一）字符函数 ###

![a96ef8c51150479296b450efb9a79f1f.png][]

![4398d1572a2f45dfb06802897c290a57.png][]

![7134843c02314fe784367020536731a5.png][]

dual：oracle提供的一个虚拟表，用来做临时计算等等。

#### 常用字符函数讲解： ####

（1）求字符串长度 LENGTH

select length('ABCD') from dual;

（2）求字符串的子串 SUBSTR

select substr('ABCD',2,2) from dual;

（3）字符串拼接 CONCAT

select concat('ABC','D') from dual;
    select 'ABC'||'D' from dual;

### （二）数值函数 ###

![5412180412044b1b9eec5333e8dd0a9a.png][]

![3a4c238a337640a090eb2c7559cd7692.png][]

#### 常用数值函数讲解： ####

（1）四舍五入函数 ROUND

select round(100.567) from dual;
    select round(100.467,2) from dual;

（2）截取函数 TRUNC

select trunc(100.567) from dual ;
    select trunc(100.567,2) from dual;

（3）取模 MOD

select mod(10,3) from dual ;

### （三）日期函数 ###

![9ca40d0f0dd748e4a3d0c62ca3b99cbb.png][]

![2a28bccc466f41cd81dbe0ab684e35a8.png][]

我们用 sysdate 这个系统变量来获取当前日期和时间

select sysdate from dual;

#### 常用日期函数讲解： ####

（1）加月函数 ADD\_MONTHS ：在当前日期基础上加指定的月

select add_months(sysdate,2) from dual;

（2）求所在月最后一天 LAST\_DAY

select last_day(sysdate) from dual;

（3）日期截取 TRUNC

select TRUNC(sysdate) from dual
    select TRUNC(sysdate,'yyyy') from dual 
    select TRUNC(sysdate,'mm') from dual

### （四）转换函数 ###

![8b828d44879849a9995aa13e0c5eda2b.png][]

![6af1bf45f3bd4d14917e8776b6904803.png][]

#### 常用转换函数讲解： ####

（1）数字转字符串 TO\_CHAR

select TO_CHAR(1024) from dual;

（2）日期转字符串 TO\_CHAR

select TO_CHAR(sysdate,'yyyy-mm-dd') from dual;
    select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

（3）字符串转日期 TO\_DATE

select TO_DATE('2017-01-01','yyyy-mm-dd') from dual;

（4）字符串转数字 TO\_NUMBER

`select to_number('100') from dual;

### （五）其它函数 ###

（1）空值处理函数 NVL

select NVL(NULL,0) from dual;

（2）空值处理函数 NVL2

用法：

NVL2（检测的值，如果不为 null 的值，如果为 null 的值）；

## 六、集合运算 ##

### （一）什么是集合运算 ###

集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：

··UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。

··UNION(并集)，返回各个查询的所有记录，不包括重复记录。

### （二）并集运算 ###

UNION ALL 不去掉重复记录

select * from t_user where id<=7 union all select * from t_user where id>=5;

UNION 去掉重复记录

select * from t_user where id<=7 union select * from t_user where id>=5;

[5cccc54dcc4544adafd1d309a3c980c4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/d148ef64b44745f7bf35c240ead24fd4.png
[d70883eaef664c26b7f9a3d058a20844.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/74dbf80b7fd747ffacb5593938c78870.png
[241e427fdae14a28a74a21fd998fe27d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/df67e5c7d05144a187f1d23e6d379aaa.png
[5acebe8dfef64755ad7bc5d3e0d7ee7d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/f35cf7a6fe834c4c9142728e57ad5571.png
[05ab8a936d9c45b080cca24af0f5f5a2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/ec450c8a117a4c37980c7632ef8a42f0.png
[902b8152fd8d43d8b06bf5806f205310.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/027cbc806d48484381ef84cf7549834d.png
[f156ea7a66b642d8b717794b2d66c660.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/e65ca172a16e4481b58ef4b445d9b40e.png
[90a44f13088f44a3a51461d40b2b2189.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/62094385d6f14b2fbb5d387f04aeb3d2.png
[b4cf217710d9443eabfb425c83e55e16.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/a5485724b9f94fb39f2a84ab9b0d668d.png
[3283ddaf96bc4073b4ae6512720afb9a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/83fdfa5594e04284b28eae578b079a5a.png
[0f42f036e22c4199a76011b60ab9b0a0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/a31bdc6a46c04408aa9355163b9049b5.png
[0383898b2cbe43d6bd626b7bd2f30af5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/465339da32cc48a3bc1ffd866c3c79f7.png
[08d5d453602145418f9dd9aa4421a2da.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/b7b6c068b0c940e0a64eff83a3f684ce.png
[6bf197edad654ca281e330d912f95b04.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/6ed2dbe1678b428ba1d1716a6f2c2ac6.png
[898e2d52cd7c49fb97e09e2e3931539f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/9e1e2f65b4f64dbe9532e2d1a14d0e71.png
[5db6682199a644fd9450585e22940e7f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/2c0aa74eca5d42d8b09542ec7cf65705.png
[3b5cb270a1194743b2dcd916b298342a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/e9915a67a2a24f65a2081e9f4a41b8d3.png
[eca044182b4e4d82be96fa8518128f8c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/fd1f7256568d47da8df7c96e6b1ff3cb.png
[96d9b6411f6744b0a228ab5e2a55950e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/e3f8d18eb38a4e968d64e4bc66514fc1.png
[defcf6a34dd0425b92722c260f62a4e8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/17f89e0511344f6b917aa45fd2fde707.png
[a96ef8c51150479296b450efb9a79f1f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/609fdfbe65ec4630a22ead260f6ff39d.png
[4398d1572a2f45dfb06802897c290a57.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/03a515102aff4afea251bdfee46e9b36.png
[7134843c02314fe784367020536731a5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5e6ea34eb535468dbc2349b0f612d3a3.png
[5412180412044b1b9eec5333e8dd0a9a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/56efbcfb078d42f29826593723cdd262.png
[3a4c238a337640a090eb2c7559cd7692.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/48746b393d504383af7eeeb13113d3fb.png
[9ca40d0f0dd748e4a3d0c62ca3b99cbb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/436177de34f940738d779fde80c9666d.png
[2a28bccc466f41cd81dbe0ab684e35a8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/b5259ef3328f4682874c806eae8ce1af.png
[8b828d44879849a9995aa13e0c5eda2b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/d5f60947143745f585d176969f414f98.png
[6af1bf45f3bd4d14917e8776b6904803.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/f9dcbde8307e48cab1e1a0c52cf1c6c9.png