查询语句及函数

约 1980 字大约 7 分钟

2025-04-10

一、DQL用法

1. 别名要用双引号括起来单引号为列

2. 去重关键字 distinct

3. 四则运算

空值问题 : 当我要计算（两个字段）工资加奖金，有一字段为空，计算结果就变成空
解决 : 见下方函数

4. 连接字符串

|| 而不是 +

示例：select job || ‘：’|| name from emp

结果：职位：姓名

5. 查询有奖金的雇员

select * from emp where bouns is not null  and bouns <> 0 ;
--简化为：
select * from emp where bouns > 0;

6. 范围查询

between and 包括边界值。

--范围查询时间类型  
between to_date('1981-01-01','YYYY-MM-DD')  and  to_date('1981-12-31','YYYY-MM-DD');

7. 查询字段值的大小写问题

见下方函数解决

8. in 关键字。

select * from EMP where empno in (7521,7232,7213);

9. 排序查询

null值放前面和后面 NULLS FIRST 和 NULLS LAST

select * from emp order by bouns nulls  last

10. 排序前先过滤数据提高效率。

二、单行函数

1. 字符类型

1.1.大小写转换。

select lower(ename)  from emp;
select upper(ename) from emp;

1.2. 连接字符串

select concat('A','B') from dual;

1.3. 截取字符串。

select  substr('hello',1,3) from  dual;  (索引 0  和 1  一样)

1.4. 获取字符长度。

select length('hello')  from  dual;

1.5. 字符串替换。

select replace ('hello,Oracle','l','x') FROM DUAL;

2. 数值类型

2.1. 四舍五入

select round (123.456,2) from dual; 2--->留几位小数

2.2. 不四舍五入

select trunc(123.456,2) from dual;

2.3. 取余数

select mod(5,2) from dual;

3. 日期函数

3.1. 查询员工入职的周数。

select round((sysdate - hiredate)/7 ,0) c1 from emp;  c1--->第一列

3.2. 查询入职月数(计算两个时间段中间的月数)。

select round( months _between(Sysdate,hiredate) ,0) c1 from emp;

3.3. 查询三个月后的时间。

select add_months(sysdate,3) from dual;

4. 转换函数

4.1. 数字转字符 to_char

--9 代表数字   0 代表0
select to_char(sal,'L999,999,999.00') from emp;

4.2. 日期转字符 to_char

select to_char(sysdate,'yyyy-MM-dd  hh24:mm:ss') from dual;

4.3. 字符转数字或时间 to_number to_date

select to_number('123.456') from dual;
select to_date ('2017-09-14','yyyy-MM-dd') from dual; //注意单引号

4.4. 解决空值问题。 nvl 如果为空就选后面的值

select ename,sal*12+nvl(comm,0) from emp;

NVL2 如果目标不为 NULL 取第一个为 NULL 取第二个(类似三目)

SELECT NVL2(E.COMM,E.COMM,0) FROM EMP E;

NULLIF 如果两个值相等返回一个 NULL

SELECT NULLIF(E.COMM,E.COMM) FROM EMP E;

COALESCE 配置多个表达式返回一个为空的如果都为空则返回空

SELECT COALESCE(NULL,1,NULL,2) FROM DUAL;

5. 条件表达式

5.1. 计算员工的工资税率等级

select 
  ename,
  sal,
  decode(trunc(sal/2000,0), 0, 0.00, 1, 0.09, 2, 0.20, 0.3)
from emp;
SELECT E.ENAME 姓名,E.SAL 薪资,
CASE 
WHEN E.SAL >= 500 AND E.SAL < 1500 THEN 1 
WHEN E.SAL >= 1500 AND E.SAL < 2000 THEN 2 
WHEN E.SAL >= 2000 AND E.SAL < 2500 THEN 3 
WHEN E.SAL >= 3000 AND E.SAL < 3500 THEN 4 
ELSE 5 
END 薪资等级 
FROM EMP E;

三、多行函数

1. 也可以叫聚合函数

2. 平均

select round(avg(sal),2) from emp;

3. count（面试题）

count(*) count(1) count(主键) 三种方式哪个快?

count(主键)最快，索引机制。数据已经按照主键形成了一份记录（目录）,

count(1) 居中

4. 分组

4.1. 查询每个部门平均工资。

select deptno,round(avg(sal),2) from emp group by deptno;

4.2. 分组函数 select 之后只能写分组条件的列和聚合函数，不能写其他的列

4.3. 对分组数据进行过滤 having 对分组数据进行过滤，having后面只能接受分组条件和聚合函数

4.4. 性能 having的性能不如where，能先用where进行筛选的时候一定要先筛选

四、连接查询

1. 内连接

1.1. 隐式内连接

表少的时候使用

1.2. 显式内连接

表多的时候使用

1.3. 特点

两个表关联字段其中有一个为空值的数据，就不会查出来

2. 外连接

2.1. 左/右外连接

左外连接以左边为主表，右外连接以右表为主表

2.2. 特殊写法

select * from emp e , dept d  where e.deptno=d.deptno(+);
--谁加 +  谁是从表

2.3. 性能

左边的一列会被当成索引，所以当右外连接的时候第一列有空值时，索引会被破坏掉，性能就会下降

3. 自连接

3.1. 等值自连接

select E.ename 员工姓名,me.ename 领导名称 from emp E ,emp me  where w.mgr = me.empno;

3.2. 内连接自连接

SELECT * FROM EMP e INNER JOIN emp me ON e.mgr = me.empno;

3.3. 外连接自连接

SELECT e.ename 员工名称,me.ename 领导名称 FROM emp e LEFT JOIN emp me ON e.mgr = me.empno;

4. 示例

4.1. 查询出员工的姓名，工资和工资等级并命名为一级二级...

SELECT 
    E.ENAME,
    E.SAL,
    decode(G.GRADE, 1,'一级',2,'二级',3,'三级',4,'四级',5,'五级','高级') C1
FROM EMP E LEFT JOIN SALGRADE G 
ON 
    E.SAL BETWEEN G.LOSAL AND G.HISAL;

4.2. 查询员工的信息，一级领导的信息

SELECT 
    E.EMPNO,
    E.ENAME,E.SAL,
    decode(G1.GRADE,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级','高级') C1,
    ME.ENAME,
    ME.SAL,
    decode(G2.GRADE,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级','高级') C2,
    D.DNAME
FROM EMP E
LEFT JOIN EMP ME ON E.MGR = ME.EMPNO 
LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO 
LEFT JOIN SALGRADE G1 ON E.SAL BETWEEN G1.LOSAL AND G1.HISAL 
LEFT JOIN SALGRADE G2 ON ME.SAL BETWEEN G2.LOSAL AND G2.HISAL;

五、子查询

1. 概述

把一个查询结果当成条件或者新表

2. in

in 后面的值可以为空

not in 后面不能有空值

3. exists

只返回 true 和 false

在 ddl 语句使用的时候用这个关键字

4. 找到工资的最高的前三个员工

伪列：rownum rowid

oracle 中独特的定义，不存在于表中，但是能使用

--rownum 将查询结果从1 开始编号，但是发生在排序之前
select  rownum,e.* from emp  e;

这个时候用子查询

select
 rownum,
 a.*
from 
 (select * from emp  order by sal desc ) a 
 where rownum < 4;

5. 查询工资在5-8名的员工信息

新的问题: rownum 每次都会从1给你排序你的新查询结果，也就是筛选rownum 时必须包括 1

select  
    b.*
from 
    (
        select  rownum  rn ,a.* from (select * from emp  order by sal desc ) a 
    )b
where b.rn between 5 and  8 ;

6. 统计每年入职的员工个数并统计总人数

6.1. 先查1981年入职的员工

select count(*) from emp  where hiredate between date 'XXX' and  date 'XXX'

6.2. 再查有多少个年份

6.3. 假如按年份分组，就查不了总人数

6.4. 用 decode 年代正确了就是1 不正确就是0 在相加就求出来了这个年代的人数

SELECT 
COUNT(empno) total,
sum(DECODE(TO_CHAR(HIREDATE,'yyyy'),'1980',1,0)) "1980",
sum(DECODE(TO_CHAR(HIREDATE,'yyyy'),'1981',1,0)) "1981",
sum(DECODE(TO_CHAR(HIREDATE,'yyyy'),'1982',1,0)) "1982",
sum(DECODE(TO_CHAR(HIREDATE,'yyyy'),'1987',1,0)) "1987"
FROM EMP;

六、分页查询

1. oracle 分页必须用到伪列且必须从1开始

2. oracle 分页公式

数据层伪列层分页层

3. 数据层

只提供分页数据排序分组，一定在这层完成

select * from  emp  order by sal desc;

4. 伪列层

这一层就获取伪列

select A.* ,rownum rn from( 数据层)  A

5. 分页层

获取分页参数

select * from ( 伪列层 ) B  where b.rn between (1-1) * 3+1  and 1*3

七、集合

1. 并集

1.1. union

去重，自然排序，查询效率很低

把两个毫无关系结果放在一起，且破坏了本来的索引机制以及其他的优化查询机制

这种情况下又要排序，又要去重

1.2. union all

不去重也不排序

2. 交集 intersect

select  * from emp where sal > 1500
intersect
select * from emp where deptno=20;

3. 差集 minus

在第一个集合但是不在第二个集合中

4. 注意

集合运算两边查询的字段类型，数量，顺序必须一致

所有的集合查询都可以用连接查询。