Oracle 入门到精通系列【第 2章】

最新推荐文章于 2023-03-05 04:16:00 发布

黑石课堂

最新推荐文章于 2023-03-05 04:16:00 发布

阅读量1.5k

点赞数 1

分类专栏： oracle 文章标签： oracle 数据库 linq

转发请留下原链接哈，否则我会不开心的！！！

本文链接：https://blog.csdn.net/imjcoder/article/details/122156555

版权

oracle 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

本文深入介绍了Oracle SQL的各种查询技巧，包括单表查询的简单条件、范围、排序、去重和聚合统计，连接查询的内连接、外连接，子查询的应用，分页查询的实现，以及单行函数、行列转换和集合运算的使用。内容详实，适合SQL初学者和进阶学习。

摘要由CSDN通过智能技术生成

🚌一个人可以走的很快，一群人可以走的很远🇨🇳
🎉点赞➕评论➕收藏 ➕关注== 养成习惯（一键四连）📝
🎉欢迎关注💗一起学习👍一起讨论⭐️一起进步📝
🙏作者水平有限，欢迎各位大佬指点，相互学习进步！😆

一、单表查询

（一）简单条件查询

1.精确查询

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

select * from EMP where empno='7369';

查询结果：

2.模糊查询

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

查询语句：

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

查询结果：

3. and 运算符

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

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

查询结果：

4. or 运算符

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

查询语句：

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

查询结果：

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

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

查询语句：

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

查询结果：

因为 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;

查询结果：

7. 空值查询

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

查询语句：

select * from emp where comm is null;

查询结果：

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

查询语句：

select * from emp where comm is not null;

查询结果：

（二）去掉重复记录

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

查询语句：

select distinct deptno from emp;

查询结果：

（三）排序查询

1.升序排序

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

查询语句：

select * from emp order by sal asc;

查询结果：

2.降序排序

select * from emp order by sal desc;

（四）基于伪列的查询

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

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

1、ROWID

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

查询语句：

select rowID,e.* from emp e;

查询结果：

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

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

2、ROWNUM

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

select rownum,e.* from emp e;

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

（五）聚合统计

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;

3. 分组后条件查询 having

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

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

二、连接查询

（一）多表内连接查询

（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. 单行子查询

只返回一条记录
单行操作符

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. 多行子查询

返回了多条记录
多行操作符

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 )

select * from ( select * from emp )

四、分页查询

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


    @Test
    public void add(){
        UserDao userDao = new UserDao();
        for(long i=409;i<550;i++){
            User user = new User();
            user.setId(i);
            user.setUserName("张三"+i);
            user.setPassword("123456"+i);
            userDao.add(user);
        }

    }

（一）简单分页

需求：分页用户表t_user，每页 10 条记录

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

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

显示结果如下：

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

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

查询结果：

嗯？怎么没有结果？

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

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

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

查询结果如下：

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

（二）基于排序的分页

需求：分页用户表t_user，每页 10 条记录，按ID降序排序。

第一步：先做ID降序排序，查看rownum

select rownum,t.* from T_USER t where t.id<=30 order by id desc;

第二步：根据查询出来的数据分页

第一页数据：select * from (select rownum r,t.* from T_USER t where t.id<=30 and rownum<=30 order by id desc) m where m.r>=21 ;

第二页数据：select * from (select rownum r,t.* from T_USER t where t.id<=20 and rownum<=30 order by id desc) m where m.r>=11 ;

分析：这时候分页和上面的分页逻辑就反了，所以不适合。因为rownum是查询结果的一个值！

第三步：使用三层嵌套分页

先排序好数据，生成固定的rownum，之后基于这个结果再去做查询，生成外层的一个rownum，基于这个rownum再去做正序分页。

第一页数据：select * from (select rownum r,t.* from (select t.* from T_USER t where t.id<=30 order by id desc) t where rownum<=10) where r>0;

第二页数据：select * from (select rownum r,t.* from (select t.* from T_USER t where t.id<=30 order by id desc) t where rownum<=20) where r>10;

五、单行函数

（一）字符函数

常用字符函数讲解：

（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;

（二）数值函数

常用数值函数讲解：

（1）四舍五入函数 ROUND

select round(100.567) from dual

select round(100.567,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

（三）日期函数

我们用 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

（四）转换函数

常用转换函数讲解：

（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 的值）；

（3）条件取值 decode

语法：

decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)

【功能】根据条件返回相应值

（4）case when

（六）、case when

case when：条件判断语句
	(1) 相当于其它语言中的 if else
	(2) 部分情况下，等同于 decode()
case when 表达式用两种形式
    -- 简单 case 函数，要求：when 对象的类型 和 case 对象的类型一致
    -- 此时等同于 decode(sex, '1', '男', '2', '女')
    case sex
      when '1' then '男'
      when '2' then '女'
    else
      '其它'
    end;
   -- case 表达式
   case
     when sex = '1' then '男'
     when sex = '2' then '女'
   else
     '其它'
   end;
注意：when 的执行顺序，当 '第一个' when 满足条件时，便结束查询
	   （不会继续判断其它的 when 条件） 
建议：当 case when 和 decode 等价，且判断语句不超过 10 行时，
        使用 decode（语法简洁）

with t_score as (
  select 90 score, '瑶瑶' name from dual union all
  select 80 score, '倩倩' name from dual union all
  select 70 score, '优优' name from dual 
)
select t.name,
       t.score,
       (case
         when t.score >= 90 then
          '优秀'
         when t.score >= 80 then
          '良好'
         when t.score >= 60 then
          '及格'
         else
          '不及格'
       end) 评分
  from t_score t;

六、行列转换

（1）创建表格、插入测试数据

create table kecheng
(
  id     NUMBER,
  name   VARCHAR2(20),
  course VARCHAR2(20),
  score  NUMBER
);
insert into kecheng (id, name, course, score)
values (1, '张三', '语文', 67);
insert into kecheng (id, name, course, score)
values (1, '张三', '数学', 76);
insert into kecheng (id, name, course, score)
values (1, '张三', '英语', 43);
insert into kecheng (id, name, course, score)
values (1, '张三', '历史', 56);
insert into kecheng (id, name, course, score)
values (1, '张三', '化学', 11);
insert into kecheng (id, name, course, score)
values (2, '李四', '语文', 54);
insert into kecheng (id, name, course, score)
values (2, '李四', '数学', 81);
insert into kecheng (id, name, course, score)
values (2, '李四', '英语', 64);
insert into kecheng (id, name, course, score)
values (2, '李四', '历史', 93);
insert into kecheng (id, name, course, score)
values (2, '李四', '化学', 27);
insert into kecheng (id, name, course, score)
values (3, '王五', '语文', 24);
insert into kecheng (id, name, course, score)
values (3, '王五', '数学', 25);
insert into kecheng (id, name, course, score)
values (3, '王五', '英语', 8);
insert into kecheng (id, name, course, score)
values (3, '王五', '历史', 45);
insert into kecheng (id, name, course, score)
values (3, '王五', '化学', 1);
commit;

（2）查询每个人的每个科目分数

SELECT ID,NAME,
MAX(CASE WHEN course='语文' THEN score ELSE 0 END) 语文,
MAX(CASE WHEN course='数学' THEN score ELSE 0 END) 数学,
MAX(CASE WHEN course='英语' THEN score ELSE 0 END) 英语,
MAX(CASE WHEN course='历史' THEN score ELSE 0 END) 历史,
MAX(CASE WHEN course='化学' THEN score ELSE 0 END) 化学
FROM kecheng
GROUP BY ID ,NAME;