Oracle_sql 基础_查询语句

1.基本查询

create table users
( id number(6,0),
username varchar2(20),
salary varchar2(20)
);
insert into users values(1,'aaa','5000');
insert into users values(2,'bbb','1800');
insert into users values(3,'ccc','15000');
insert into users values(4,'ddd','20000');

(1) 为表中查询字段设置别名：

select id as 编号,username as 用户名, salary as 工资 from users;

(2) 运算符和表达式：

    ① 算术运算符（+，-，*，/）

//说明：使用算数运算符并不会真正的修改数据库的数据，只是在展示的时候改变；
select id,username, salary+200 from users;

    ② 比较运算符（>,>=,<,<=,<>）

select id,username, salary from users where salary >10000;

    ③ 逻辑运算符（and,or,not）

select id,username, salary from users where not (username='aaa');

(3) 模糊查询(LIKE)：

//字符匹配操作可以使用通配符 “%” 和 “_”：  %：表示任意个字符，包括零个；  _：表示一个任意字符；
select * from users where username like '_a%';

(4) 范围查询(between...and，in的使用)：

select * from users where salary not between 800 and 2000;
select * from users where username not  in('aaa','bbb');

(5) 对查询结果进行排序(ORDER BY)： desc:降序；asc：升序

select * from users order by id desc,salary asc;

(6) case...when...then和decode函数的使用 ：

    ① case...when...then：根据不同的查询结果显示不同的值

select username , case when username='aaa' then '计算机部门' 
                       when username='bbb' then '市场部门' 
                       else '其他部门' 
                       end as 部门
from users;

select username,case username when 'aaa' then '计算机部门'
                              when 'bbb' then '市场部门' 
                              else '其他部门' 
                              end as 部门
from users;

    ② decode函数的使用：

select username,decode(username,'aaa','计算机部门','bbb','市场部门','其他部门') as 部门 from users;

(7) union all和union的使用

    ① 概念：

    如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。

    union(或称为联合)的作用是将多个结果合并在一起显示出来。

    ② union和union all的区别是：

    Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

    Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

    ③ 示例：

select * from emp where deptno >= 20
union
select * from emp where deptno <= 30

(8) 递归查询：start with...connect by子句的用法

    ① 作用： oracle 提供了start with connect by 语法结构可以实现递归查询。

    ② 示例1：

    我们拿scott用户下的emp表做实验.  Emp表有个字段 一个是empno(员工编号)  另一个是mgr(上级经理编号)

    【1】向下递归：

select * from emp  connect by  mgr= prior empno start with empno=7698;

    得到的结果是 empno=7698 的数据,以及会得到 mgr=7698的数据.  它是向下递归的, 即我们从empno=7698开始遍历, 去找出mgr=7698的所有数据S(我们用S代表查出的所有数据.), 然后在从S中的empno的值去匹配查找是否还有满足 mgr  in (s.empno)的数据. 一直遍历进去到没有数据为止。

    【2】 向上递归

select * from emp  connect by prior mgr=  empno start with empno=7844;

    这个和上面的找相反, 这个是向上递归遍历，直到没有想匹配的数据为止。

    ③ 示例2:

SQL> select *  from test;
BILL_MONTH          DAY_NUMBER   MSISDN
-------------------- ---------- --------------------
200803                        1                 13800
200803                        3                 13800
200803                        2                 13800
200803                        2                 13801
200803                        4                 13804
200803                        5                 13804
200803                        7                 13804
200803                        8                 13804
200803                        6                 13802
200803                        6                 13801
200803                        7                 13801
200803                        8                 13801
12 rows selected
SQL>
SQL> select * from test
  2       start with day_number=1
  3       connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  4      ;
BILL_MONTH           DAY_NUMBER MSISDN
-------------------- ---------- --------------------
200803                        1 13800
200803                        2 13800
200803                        3 13800
SQL>

     上面的语句查找出了从1开始，并且day_number 逐渐+1 递增的，并且 msisdn 相同的哪些个数据.

    ④ start with  connect by 语法结构

    如上面说看到的 例子， 其语法结构为  start with condition  connect by  condition （含 prior 关键字)

    start with conditon 给出的seed 数据的范围, connect by  后面给出了递归查询的条件,prior 关键字表示父数据，prior 条件表示子数据需要满足父数据的什么条件。

    在下面的这个start with connect by 结构中，就表示查找出了从1开始，父数据的day_number等于子数据的day_number-1而且父数据的msisdn=子数据的msisdn.

select * from test
         start with day_number=1
         connect by  prior day_number=day_number-1 and prior msisdn= msisdn
        ;

2.分组查询：

(1) 分组函数：分组函数作用于一组函数，并对一组数据返回一个值。

AVG,SUM,MIN,MAX,COUNT,WM_CONCAT

select avg(sal),sum(sal) from emp;
select max(sal),min(sal) from emp;
select count(*) from emp;

(2) distinct：去处重复数据

select count(distinct deptno) from emp;

(3) 行转列：WM_CONCAT

select deptno 部门号,wm_concat(ename) 部门中员工的姓名 from emp group by deptno;

(4) 分组函数 NVL 的使用 ：nvl(comm,0)，里面两个参数的意思分别是：如果第一个参数为null，就返回第二个参数，否侧返回第一个参数。

//求出员工的平均奖金
select count(*),count(nvl(comm,0)) from emp;

(5) group by 分组数据：

     在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中；

select deptno,job,sum(sal) from emp group by deptno,job;
+
select deptno,sum(sal) from emp group by deptno;
+
select sum(sal) from emp;
--或者
select deptno,job,sum(sal) from emp group by rollup(deptno,job);

    说明：以上的多列分组中:group by rollup(a,b) 等价于 group by a,b+group by a +group by null

(6) having 过滤分组

select deptno,avg(sal)
from emp
group by deptno
having avg(sal) > 2000；
--不能在WHERE子句中使用组函数，可以在HAVING子句中使用组函数。
--从SQL 优化的角度上看，尽量使用where，因为：having 先分组,后过滤； where 先过滤,后分组。

(7) Order by 对分组函数进行排序，默认升序

select deptno,avg(sal) 平均工资
from emp
group by deptno
order by 2

(8) 分组函数的嵌套

select max(avg(sal))
from emp
group by deptno;

3.多表查询

(1) 笛卡尔积

     为了避免笛卡尔集,可以在WHERE加入有效的连接条件,在实际运行环境下，应避免使用笛卡尔全集。

(2) 等值连接

select e.empno,e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno;

(3) 不等值连接

select e.empno,e.ename,e.sal,s.grade
from emp e,salgrade s
--对于between...and必须小值在前 大值在后
where e.sal between s.losal and s.hisal;

(4) 外连接

    通过外连接，把对于连接条件不成立的记录，仍然包含在最后的结果中。

    左外链接：当连接条件不成立的时候，等号左边的表仍然被包含。

    右外链接：当连接条件不成立的时候，等号右边的表仍然被包含。

select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
from emp e, dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname; 

      
select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
from dept d 
left join emp e
on  e.deptno=d.deptno
group by d.deptno,d.dname;

(5) 内连接(自连接)：通过别名，将同一张表视为多张表

//查询员工姓名和员工老板的姓名
select e.ename 员工姓名,b.ename 老板姓名
from emp e, emp b
where e.mgr=b.empno;

     说明：上面这样做，将会产生笛卡尔积。可以使用下面的 层次查询 解决该问题，层次查询是单表查询。

(6) 层次查询

//查询员工姓名和员工老板的姓名
//--connect by 上一层的员工号=老板号
//-- level oracle提供的伪列，在层次查询中使用
select level,empno,ename,sal,mgr
from emp
connect by prior empno=mgr
--start with empno=7839
start with mgr is null
order by level;

4.子查询:可以解决不能一步求解得问题。

(1) With as 的使用

    ① with as 简介：

    with as其实就是一个子查询， 使用它可以增强sql的可读性，同时因为该查询只执行一次，并将结果存储在用户临时表空间中，可以多次引用，增强性能。

    ② with as 用法：

        with as学名为公用表表达式 - CTE, 使用它需要注意以下几点：

    【1】cte后面必须直接跟使用cte的sql语句， 如：select、insert、update，否则cte将失效

with temp as (  
     select * from xxx  
)  
select * from temp;  

    【2】cte后面可以跟其他的cte，但只能使用一个with，多个cte中间使用逗号分隔：

with temp1 as (    
     select * from xxx    
),  
temp2 as(  
     select * from yyy  
)  
select x.* from temp1 x, temp2 y where x.id = y.id   

     【3】with as 实战：

            查询出部门的总薪水大于所有部门平均总薪水的部门：部门表dept, 员工表emp;

-- step1:查询出部门名和部门的总薪水
with dept_costs as(
　　select dname,sum(sal) dept_total
　　   from
　　   dept a,emp b
　　   where a.deptno=b.deptno
　　   group by dname
),
-- step2:利用上一个with查询的结果，计算部门的平均总薪水
avg_costs as(
　　select sum(dept_total)/count(*) dept_avg
　　from dept_costs
)
-- step3:从两个with查询中比较并且输出查询结果
select dname,dept_total
   from dept_costs
where
dept_total > (
  select dept_avg from avg_costs
)
order by dname;

(2) 子查询需要注意的几个问题：

    ① 子查询中小()问题：子查询必须有小()。

    ② 子查询的书写风格问题：合理 规范。

    ③ 可以使用子查询的位置：where，select，having，from(select后面的子查询必须返回单行数据)。

select empno,ename,job,mgr,hiredate,sal,comm,(select deptno from emp where empno=7369) 部门 from emp where empno=7369;

    ④ 不可以使用子查询的位置：group by 。

    ⑤ 强调：from后面的子查询。

//查询员工的ID，姓名，月薪，年薪
select * from (select empno,ename,sal,sal*12 annsal from emp);

    ⑥ 主查询和子查询可以不是同一张表。

    ⑦ 一般不在子查询只使用排序，但在Top_N分析问题中，必须对子查询排序。

//找到员工表中工资最高的前三名
//行号需要注意的两个问题：
//① 行号永远按照默认的顺序生成
//② 行号只能使用<,< =;不能使用>,> =
select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<=3;

   ⑧ 一般先执行子查询，在执行住查询，但相关子查询列外。

//找到员工表中薪水大于本部门平均薪水的员工
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno);

    ⑨ 单行子查询只能只用单行操作符，多行子查询只能使用多行操作符。

        单行操作符：=,>,>=,<,<=,<>.

        多行操作符:IN,ANY,ALL

   【1】查询员工信息,要求:职位与7566员工一样,薪水大于7782员工的薪水.

select *
from emp
where job = (select job from emp where empno=7566) and
          sal > (select sal from emp where empno=7782);

   【2】查询部门名称是SALES和ACCOUNTING的员工信息

select *
from emp
where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');

select e.*
from emp e, dept d
where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');

    【3】查询最低工资大于20号部门最低工资的部门号和部门的最低工资

select deptno, min(sal)
  from emp
 group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);

    【4】查询工资比30号部门任意一个员工高的员工信息

select *
from emp
where sal > any (select sal from emp where deptno=30);

    ⑩ 子查询是 nul 值得问题。

//查询不是老板的员工
select *
from emp
where empno not in (select mgr from emp where mgr is not null);

5.综合示例

(1) 分页查询显示员工信息 :显示员工号，姓名，月薪。

    要求：每页显示四条记录，显示第二页的员工，按照月薪降序排列(在Oracle数据库中分页是通过嵌套子查询来实现 )。

select r,empno,ename,sal
from (select rownum r,empno,ename,sal
     from (select rownum, empno,ename,sal from emp order by sal desc) e1
     where rownum<=8) e2
where r>=5;

(2) 找到员工表中薪水大于本部门平均薪水的员工

//相关子查询
select e. empno,e. ename,e.sal,d.avgsal
from emp e, (select deptno,avg(sal) avgsal from emp  group by deptno) d 
where e.deptno=d.deptno and e.sal>d.avgsal;
//多表子查询
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno)

    为什么说上面的相关子查询要比下面的多表子查询效率高，通过sql语句的执行计划来判断，判断实例如下：

--执行执行计划
explain plan for
select e. empno,e. ename,e.sal,d.avgsal
from emp e, (select deptno,avg(sal) avgsal from emp  group by deptno) d
where e.deptno=d.deptno and e.sal>d.avgsal;
--查看执行计划
select * from table(dbms_xplan.display);

(3) 按部门统计员工入职的年份的人数和总人数：

select count(*) 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;
或者：
select
   (select count(*) from emp) Total,
   (select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",
   (select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",
   (select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",
   (select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"
from dual;