数据库的一些操作(二)-CSDN博客

本文链接：https://blog.csdn.net/weixin_44882979/article/details/96898665
03-子查询:
--部门名称为 SALES 或 ACCOUNTING 的雇员信息
--查询的数据: 员工信息*
--来源: emp
--条件: 部门名称=SALES 或 ACCOUNTING
--1)先查询出部门名称=SALES 或 ACCOUNTING对应的部门编号
select deptno from dept where dname in('SALES','ACCOUNTING');

--子查询
select *
  from emp
 where deptno in
       (select deptno from dept where dname in ('SALES', 'ACCOUNTING'));

-- 查询工资等级为 2的员工信息
select * from emp;
select * from salgrade;
select * from emp where sal between 1201  and 1400 ;
select *
  from emp
 where sal between (select losal from salgrade where grade = 2) and
       (select hisal from salgrade where grade = 2);

-- 查询 销售部（SALES） 中 工资大于1500的员工信息
--查询的数据: 员工信息*
--来源: emp
--条件: 部门名称=SALES and sal>1500
select *
  from emp
 where sal > 1500
   and deptno = (select deptno from dept where dname = 'SALES');

select *
  from (select *
          from emp
         where deptno = (select deptno from dept where dname = 'SALES'))
 where sal > 1500;

-- 查询工资比SMITH高的同一部门的员工信息
--查询的数据: 员工信息*
--来源: emp
--条件: sal>SMITH的薪资  and deptno = SMITH的部门编号
select *
  from emp
 where sal > (select sal from emp where ename = 'SMITH')
   and deptno = (select deptno from emp where ename = 'SMITH');

--exists 存在即保留,存在即合法
select *
  from emp
 where exists (select deptno from dept where dname = 'SALES');

select *
  from emp
 where exists (select deptno
          from dept
         where dname in ('SALES', 'ACCOUNTING')
            and emp.deptno =dept.deptno);
            
--别名

select *
  from emp e
 where exists (select deptno
          from dept d
         where dname in ('SALES', 'ACCOUNTING')
            and e.deptno =d.deptno);
            
--20部门的员工信息
select *
  from emp e
 where not exists (select deptno
          from dept d
         where dname in ('SALES', 'ACCOUNTING')
            and e.deptno = d.deptno);
            
            
--有奖金的员工信息
select empno, ename, sal,comm
  from emp e1
 where exists (select empno, ename, sal, comm
          from emp e2
         where comm is not null
           and e1.empno = e2.empno);
    
--与有奖金的员工统一部门的所有员工的员工信息     
select empno, ename, sal,deptno
  from emp e1
 where exists (select empno, ename, sal, comm,deptno
          from emp e2
         where comm is not null
           and e1.deptno = e2.deptno);```

04函数
--单行函数:一条数据返回一个结果
--多行函数|组函数|聚合函数:多条数据返回一个结果

-- 当前时间
select sysdate from dual;
select current_date from dual;
select ename,sysdate from emp;

-- 加减日期
-- 2天以后是几号
select sysdate+2 from dual;

-- 所有员工入职的3天前是几号
select ename,hiredate,hiredate-3 from emp;


-- 查询所有员工的试用期期到期（转正的日期） 3个月试用期
select ename,hiredate 入职日期,hiredate+30*3 转正日期 from emp;
--add_months() 月份相加
select ename,hiredate 入职日期,add_months(hiredate,3) 转正日期 from emp;

-- 查询所有员工到目前为止一共工作了几个月
select ename,months_between(sysdate,hiredate) from emp;

-- 查询当前月的最后一天
select last_day(sysdate) from dual;
select last_day(hiredate) from emp;

-- 下一个星期三是几号
select next_day(sysdate,'星期一') from dual;

--日期对象与字符串之间的转问题
-- to_date('字符串','识别日期字符串模板')
-- 设定一个特定的时间（用一个特定的时间字符串转换为日期）
select to_date('2019-07-30 10:11:13','yyyy-mm-dd hh24:mi:ss')+3 from dual;
select to_date('2019年07月30日 10:11:13','yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

-- 将日期转为特定格式的字符串 to_char()
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;


-- decode 判断decode(判定字段，校验字段值1，结果1，校验字段2，结果2。。,默认值)
--给每个部门后后面添加一个伪列,如果10部门,伪列显示为十,二十,三十...
select deptno,dname,decode(deptno,10,'十',20,'二十',30,'三十',40,'四十') 中文名字 from dept;

-- 给20部门的所有员工都涨薪10%，显示出员工的名称， 原来的薪水， 所属部门编号， 涨薪后的薪水
select ename,sal 原薪水,deptno 部门编号,decode(deptno,20,sal*1.1,sal) 涨薪后的薪水 from emp;



-- 10部门涨薪10%， 20涨薪20%，30降薪1% ， 40部门翻倍3倍
select  ename,sal 原薪水,deptno 部门编号,case deptno(when 10 then sal*1.1,when 20 then sal*1.2,when 30 then sal*1.01,end sal) from emp;

-- 组函数: count() sum() max() min() avg()
--对确定的结果集使用函数得结果
--注意: select后 组函数不能和非组函数或分组字段一起使用
--注意: where 不能使用组函数

-- 统计一下一共有多少个员工
--count(*|1|字段)
select 1 from emp;
select count(1) from emp;

-- 统计一共有几个部门 
select count(*) from dept;

-- 统计有员工存在的部门总数
select distinct deptno from emp;
select count(distinct deptno) from emp;
select count(distinct 1) from emp;

-- 统计20部门一共有多少人
select count(1) from emp where deptno=20;

--sum()
-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;

-- 计算20部门每个月的工资花销
select sum(sal) from emp where deptno = 20;

--max()  min()
-- 查询本公司的最高工资和最低工资
select max(sal),min(sal) from emp;


--查看30部门的最高工资和最低工资
select max(sal),min(sal) from emp where deptno=30;


-- 请查询出 20部门的平均工资
select avg(sal) from emp where deptno=20;


--null值不参与运算
-- 计算出所有员工的奖金总和
select sum(comm) from emp;
select sum(comm) from emp where comm is not null;

-- 统计有奖金的员工有几个
select count(1) from emp where comm is not null;

--查询 最高薪水的员工姓名，及薪水
select max(sal) from emp;
select ename, sal from emp where sal = (select max(sal) from emp);

-- 查询工资低于平均工资的员工编号，姓名及工资


--查看 高于本部门平均薪水员工姓名
select ename from emp e1 where exists ( select ename from emp e2 where(sal>(select avg(sal) from emp) and e1.deptno=e2.deptno))
--查询82年入职的员工信息
select * from emp where hiredate like '%82%';
select * from emp where  to_char(hiredate,'yyyy')='1982';

--分组
--select 数据 from 数据源 where 行过滤条件 group by 分组字段 having 组过滤信息 order by 排序字段;
--执行顺序: from --where--group by--having--select--order by

--所有有员工存在的部门编号
select distinct deptno from emp;

select deptno from emp group by deptno;

--10,30,部门的每个部门最高薪资
select max(sal)  from emp group by deptno;
--先过滤再分组
select max(sal) from emp where deptno in(10,30) group by deptno ;
--先分组再过滤
select max(sal),deptno  from emp group by deptno having deptno in(10,30);

--如果有分组,select后只能跟分组字段和组函数

-- 求出平均工资高于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
--按 部门岗位(job) 查询 平均工资
select job,avg(sal) from emp group by  job;
--按 岗位查询 平均工资，且平均工资大于2000的岗位
select job,avg(sal) from emp group by job having avg(sal)>2000; 
--求平均薪资最高的部门编号
select avg(sal),deptno from emp group by deptno having ;
--求没个部门的平均薪资
select deptno,avg(sal) from emp group by deptno;
--求最高部门的平均薪资
--select max(select avg(sal) from emp group by deptno) from emp; 

--where 行过滤 having 组过滤
--from--where--group by --having --
select * from emp where sal>() ;

create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
drop table tb_student cascade constraints;

select *　from tb_student;

--使用一条sql语句,查询出没门课程分数都>80分的学生名字
--查询的数据: 学生名字 name
--数据的来源: tb_student
--条件: 没门分数都要>80 and 每一个人的总课程数=3
select name
  from tb_student
 group by name
having min(score) > 80 and count(1) = (select count(distinct course)
                                         from tb_student);
---------------
select name,score from tb_student group by name having score>80 and (select count(distinct course) from tb_student);                                         
--每个人如果最分数都>80,证明所有成绩都>80
select name from tb_student group by name having min(score)>80;
-----------------
select name from tb_student group by name having min(score)>80;

--一共有几门课程
select count(distinct course) from tb_student;
select name from tb_student group by name having count(1) =3;
----------
select count(distinct course) from tb_student;
select name,count(course) "已考试科目" from tb_student group by name;

--行转列
select name,
       min(decode(course, '语文', score)) "语文",
       max(decode(course, '数学', score)) "数学",
       avg(nvl(decode(course, '英语', score),0)) "英文"
  from tb_student
 group by name;
 -----练习
 select name,min(decode(course,'语文',score)) "语文",max(decode(course,'数学',score)) "数学",max(nvl(decode(course,'英语',score),0)) "英语"from tb_student group by name;
--rowid和rownum都是伪列
--rowid相当于表中每一个条记录的地址,数据插入到表中的时候就已经存在,后续不会改变
select empno,ename,rowid from emp;

--去重,没有主键,没有唯一的字段,可以存在多条数据重复,想要达到去重,可以使用rowid


insert into tb_student values(1,'张三','语文',81);
insert into tb_student values(2,'张三','数学',75);
insert into tb_student values(3,'李四','语文',81);
insert into tb_student values(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);

select * from tb_student;

--查到唯一的不重复的每一条数据,重复的数据只显示一条
select name,course,score,min(rowid) from tb_student group by name,course,score;

select * 　from tb_student where rowid in (select min(rowid)
                                            from tb_student
                                           group by name, course, score);

--查询要删除的数据
select * 　from tb_student where not rowid in (select min(rowid)
                                            from tb_student
                                           group by name, course, score);

--删除这些数据
delete from tb_student
 where not rowid in
        (select min(rowid) from tb_student group by name, course, score);
        
--rownum结果集中数据的序号
select empno,ename,sal,rownum from emp order by sal;
select deptno,dname,rownum from dept;


--分页查询
select empno,ename,sal,rownum from emp where rownum<=5;
--rownum不确定,因为结果集不确定
select empno,ename,sal,rownum from emp where rownum>=6 and rownum<=10;
--如果结果集能确定,对以确定的rownum进行判断,肯定能够判断
select empno,ename,sal,rownum,n from (select empno,ename,sal,rownum n from emp) where n>=6 and n<=10;

--一页显示4条数据 num=4,显示也是  i 
-- i=1  起始数据的rownum=1  结束:rownum=4
-- i=2  起始数据的rownum=5  结束:rownum=8
-- i=3  起始数据的rownum=(i-1)*num+1=9  结束:rownum=i*num=12

--根据薪资进行升序排序,达到分页的效果一页显示4条数据 num=4,显示第二页的数据i=2

select empno,ename,sal,rownum n from emp order by sal; 
--对已经确定的数据,按照执行的要求排序后的,rownum已经确定的,有规律的,从1开始,每次+1
select empno,ename,sal,rownum n from (select empno,ename,sal,rownum from emp order by sal); 

--分页
select empno, ename, sal,n
  from (select empno, ename, sal, rownum n
          from (select empno, ename, sal, rownum from emp order by sal))
 where n >= 5
   and n <= 8;
--查询每一个员工的员工信息,以及所在的部门信息
--数据: 员工信息,部门信息
--来源:emp,dept
--连表查询
--92,99语法

--92语法
--笛卡尔积
select * from emp,dept order by sal;

--等值连接  可以是两个表中的相同字段做连接,可以是不同字段做连接,但是类型要保持一致
select * from emp,dept where emp.deptno=dept.deptno;
select * from emp,dept where emp.ename=dept.dname;

--非等值连接
--查询每个员工的信息以及这个员工的薪资等级
select *　from emp,salgrade where sal between losal and hisal;

--查询2500薪资的等级
select grade from salgrade where 2500 between losal and hisal;

--查询 每一个的员工的员工信息,薪资等级,部门信息
select * from emp,dept,salgrade where emp.deptno=dept.deptno and sal between losal and hisal;
--查询 30部门员工的 每一个的员工的员工信息,薪资等级,部门信息

--92语法中,相同的字段一定要指明字段的出处
select *
  from emp, dept, salgrade
 where dept.deptno = 30
   and emp.deptno = dept.deptno
   and sal between losal and hisal;

--30部门的员工信息及部门信息
--先连接后过滤
select empno,ename,e.deptno,dname from emp e,dept d where e.deptno=d.deptno and e.deptno=30;
--先过滤后连接,效率较高
select empno, ename, e.deptno, dname
  from (select * from emp where deptno = 30) e,
       (select * from dept where deptno = 30) d;
       
--查询所有有上级的员工信息及其上级经理人信息  自连接
--员工表 e1和经理人表 e2
select * from emp e1,emp e2 where e1.mgr=e2.empno;

--查询所有的员工信息及其上级经理人信息 所有员工都应该显示
--想要无论是否满足连接条件,都要显示其中数据的表->主表

--外链接
--做为主表的表中的数据全部显示
--在连接条件的位置,在主表对面的表的连接条件后添加(+)
-- emp e1,emp e2 主表的位置确定,主表在左边叫做左连接,主表在右边,叫做右连接
select * from emp e1,emp e2 where e1.mgr=e2.empno(+); --左连接
select * from emp e2,emp e1 where e1.mgr=e2.empno(+); --右连接