oracle——笔记——13内容

select * from emp;  --employee 员工表
select * from dept; --department 部门表
select * from salgrade;-- salary grade 工资等级表

---emp
empno    员工编号
ename    员工姓名
job      工作/工种
mgr      manager上级编号
hiredate 入职日期
sal      salary 工资
comm     奖金/津贴
deptno   部门编号

--dept
deptno  部门号
dname   部门名称
loc     所在地

--salgrade
grade 等级
losal lowest salary 最低工资
hisal high salary  最高工资

---------------------------
--数据查询语句
select
from
where
 --查询emp表中，所有员工的姓名、工资、部门号
 select ename,sal,deptno
 from emp;
 
 --查询emp表中，工资大于1500 的员工信息
 select  *
 from emp
 where sal > 1500;
 
 --查询emp表中，上级是7698 的员工姓名和津贴
 select ename,comm
 from emp
 where mgr = 7698;

---列的别名
select ename,sal,deptno from emp;
select ename 姓名,sal 工资,deptno 部门号 from emp;
select ename as 姓名,sal as 工资,deptno as 部门号 from emp;

select ename as e,sal as s,deptno as d from emp;
select ename as "Esc",sal as "alS",deptno as "dDD" from emp;
select ename as "e%",sal as "s#",deptno as "d()" from emp;

---算术运算 + - * /  （）++++++++++++++++++++++++++++++++++++++++++++++++++                    
支持数值型和日期型(只能加减)数据
 --查询每个员工的年薪
 select ename,sal,sal*12
 from emp;
 --给20部门员工，涨薪300之后，工资是多少？
 select ename,sal,sal+300
 from emp
 where deptno = 20;
 
 --查询每名员工的 工资奖金和 
 select sal,comm,sal+comm from emp;
  --运算中如果有空值，那么最后结果为空
  --空值替换 nvl()
  select sal,comm,nvl(comm,0),sal+nvl(comm,0) from emp;
  select * from emp;
  
  insert into emp(empno) values(1122);
  delete from emp where empno = 1122;
  select ename,nvl(ename,'未知') from emp;
---连接符 
  select ename,sal from emp;
  select ename||'的工资是'||sal||'，部门是'||deptno from emp;
   
---去重
  --查询emp中，有多少个部门
  select distinct deptno from emp; 
  --查询emp中，有多少种工作
  select distinct job from emp;
  
---where
 --比较运算符
  --一般的比较运算符 > >= <= < = != <>
  --特殊的比较运算符
   between……and……
   in(……)
   like ……  像  模糊查询
   is null    空值 
   select * from emp
   where comm is null;
   select * from emp
   where comm is not null;
   
   
     
    --查询工资在1500 到 3000 的员工信息
    select *
    from emp
    where sal between 1500 and 3000;  --闭区间
                      下限     上限 
    
    select *
    from emp
    where sal >= 1500 and sal <= 3000;
   
    --查询从事CLERK或SALESMAN 的员工信息
    1）字符串
    2）关于大小写
    
    select * from emp
    where job in('CLERK','SALESMAN');
   
    select * from emp
    where job = 'CLERK' or job = 'SALESMAN' ;
   
    select * from emp where sal in(1500,3000);
    select * from emp
    where sal = 1500
       or sal = 3000;
  --like
  select * from emp
  where ename like 'S%';
   -- %
      --sql里的% 等同于linux的* ，代表零个或多个任意字符
   -- _ 代表一个任意字符

  select * from emp
  where ename like 'S_';
  
  select t.*,t.rowid from emp t;
  
  select emp.*,rowid from emp;
  
  ---查询以S%开头的员工信息

  select * from emp
  where ename like 'S\%%' escape '\';

  select * from emp
  where ename like 'S|%%' escape '|';
  
  select * from emp
  where ename like 'S%\%' escape '\';


 --逻辑运算符 and or not ()
   运算优先级：not > and > or ,()优先级最高
  --查询，20 部门中，从事CLERK 工作的员工
  select * from emp
  where deptno = 20 and job = 'CLERK';
  --查询，30 部门中，奖金为空的员工信息
  select * from emp 
  where deptno = 30 and comm is null;
  --查询，除10 部门之外，工资大于1500 的员工信息
  select * from emp
  where deptno != 10 and sal > 1500;
  --查询，工作是SALESMAN ，或工资不小于3000 的员工
  select * from emp
  where job = 'SALESMAN' or sal >= 3000;
  --查询，工作不是SALESMAN ，也不是CLERK 的员工
  select * from emp where job != 'SALESMAN' and job != 'CLERK';
  
  select * from emp where job in('SALESMAN','CLERK');
  select * from emp where job not in('SALESMAN','CLERK');
  
  --查询，工作是SALESMAN，
          或，工作是PRESIDENT并且工资大于1500的员工信息
  select * from emp
  where job = 'SALESMAN'
     or job = 'PRESIDENT'
    and sal > 1500;
  --查询，工作是SALESMAN或PRESIDNET，并且工资大于1500 的员工信息
  select * from emp
  where (job = 'SALESMAN'
     or job = 'PRESIDENT')
    and sal > 1500;
  select * from emp
  where job in('SALESMAN','PRESIDENT')
    and sal > 1500;
  
---order by 排序 
select
from
where
order by 列名|别名|算术表达式|函数
    
order by 列1,别名,函数
order by 列1,列2 desc;

 --order by的位置：在整个查询语句的最后
 --多次排序 
 select * from emp order by sal;  --升序
 select * from emp order by sal asc;  --升序
 select * from emp order by sal desc; --降序
 
 select ename 姓名,job 工作 
 from emp
 where deptno = 20
 order by 姓名 desc;
 
 ---查询员工信息，结果按照工资奖金和 升序排序
 select emp.*,sal+nvl(comm,0) from emp
 order by sal+nvl(comm,0);
 
 --查询员工信息，结果按照部门号排序
   如果部门号相同，按照工资降序排序
  select * from emp
  order by deptno,sal desc;

---函数
 单组函数          一个值对应一个结果 
 分组函数/聚合函数 多个值对应一个结果  avg() sum()
 --单组函数
  --字符函数
  UPPER() LOWER() INITCAP()  ---字母大小写
  大写     小写    首字母大写
  replace()  substr()  concat() nvl() nvl2()
  替换           截取       连接
  
  select ename,lower(ename),initcap(ename) from emp;
  select t.*,t.rowid from emp t;
  select * from emp where lower(job) = 'clerk';
  
  select ename,replace(ename,'S','s') from emp;
  select ename,substr(ename,2,3) from emp;
                       起始位,长度
  
   --查询工作名称以SALES 开头的员工信息
   select * from emp where job like 'SALES%';
   select * from emp where substr(job,1,5) = 'SALES';
  
   select ename||'的工资是'||sal||deptno from emp;
   select concat(ename,sal) from emp;
   select concat(concat(ename,'的工资是'),sal) from emp;
    ---函数是可以嵌套的
   select substr(concat(ename,'的工资是'),5,5) from emp;
  
   select comm,nvl(comm,0),nvl2(comm,1000,0) from emp;
                                 非空替换,空值替换  
  --数值函数
   round(x[,y])  取整或保留指定小数位，规则：四舍五入
   trunc(x[,y])  取整或保留指定小数位，规则：截断
   mod(x,y) 取模/取余
   
   round(5.72) = 6
   round(5.718,2) = 5.72
   round(04.718,-1) = 0
   
   trunc(5.72) = 5
   trunc(5.718,2) = 5.71
   trunc(05.718,-1) = 0
   
   select round(5.72), round(5.718,2),round(5.718,-1) from dual;
   select trunc(5.718,2),mod(8,4),mod(10,3) from dual;
   --dual表
   作用：语句补全
   select * from dual;
   select sysdate from dual;
   select 12*15,round(1000/23,2) from dual;
   select Sys_Context('userenv','db_name') from dual;--查看当前数据库 名
   select Dbms_Random.random from dual;--获得一个随机数
     select Dbms_Random.value(10,20) from dual;--获得一个随机数
   
  --日期函数
   --使用insert，新增一条记录：
   员工编号：1122
   员工工作：SALESMAN
   入职日期：2018年7月1号
   insert into 表名(列名) values(列对应的值);
   
   insert into emp(empno,job,hiredate) 
   values(1122,'SALESMAN','01-7月-18');
   insert into emp(empno,job,hiredate) 
   values(1122,'SALESMAN','2018-7-1');   
   select * from emp;
   --1)默认日期格式
    select * from nls_session_parameters;
    alter session set NLS_DATE_FORMAT='YYYY-MM-DD';
    alter session set NLS_DATE_FORMAT='DD-MON-RR';
   
   --2) 日期函数 to_date(日期，格式)
   insert into emp(empno,job,hiredate) 
   values(1123,'SALESMAN',to_date('10-1-2018','MM-DD-YYYY')); 
    --查询入职日期早于1981年9 月30 号的员工信息
    select
    from
    where hiredate < to_date()


---多表联合查询/多表连接
 内连接(等值连接、不等值连接)
 外连接(左外、右外、全外)
 自连接
 --等值连接
  --查询员工姓名和员工所在部门的部门名称
   select *
   from emp,dept
   where emp.deptno = dept.deptno; ---连接条件

   select ename,dname
   from emp,dept
   where emp.deptno = dept.deptno;
   
    --查询员工编号、入职日期、部门名称
   select emp.empno,emp.hiredate,dept.dname
   from emp,dept
   where emp.deptno = dept.deptno;
   
   select e.empno,e.hiredate,d.dname
   from emp e,dept d
   where e.deptno = d.deptno;
    --查询 SALES 部门(SALES 是部门名称) 的员工信息
   select e.*
   from emp e,dept d
   where e.deptno = d.deptno
     and d.dname = 'SALES';
    --查询工作类别是ANALYST 的员工的工资、部门号和部门所在地
   select e.sal,d.deptno,d.loc
   from emp e,dept d
   where e.deptno = d.deptno
     and e.job = 'ANALYST';
 
 --不等值连接
  --查询每个员工的工资等级
  select * from emp;
  select * from salgrade;
 
  select *
  from emp e,salgrade s
  where e.sal between s.losal and s.hisal;
 
   --查询工资等级为4级的员工工资、等级、等级区间
   
   --三表连接，需要两个连接条件
    --查询员工姓名、部门名称和工资等级
  select *
  from emp e,dept d,salgrade s
  where e.deptno = d.deptno;
    and e.sal between s.losal and s.hisal;
 
 select * from student;
 select * from class;
 select * from score;
 
 --查询 蔡成功同学的四大神术的成绩
 select 
 from student s,class c,score sc
 where s.ano = sc.ano
   and c.bno = sc.bno

---------------------------
--外连接
 作用：查询不满足连接条件的数据
 select * from emp e,dept d
 where e.deptno = d.deptno;
 select * from dept;
 select * from emp e,dept d
 where e.deptno(+) = d.deptno;    ---右外
 
 insert into emp(empno) values(1122);
 select * from emp e,dept d
 where e.deptno = d.deptno(+);    ---左外
 select * from emp e,dept d
 where e.deptno(+) = d.deptno(+);  ---不存在这种写法 
 ----外连接的另外一种写法：
 select * 
 from emp e left outer join dept d
   on e.deptno = d.deptno;   ---left 显示左边表不满足条件的数据
                             ---outer 可以省略
                             ---on 只能写连接条件，其他条件 写到where里
 select * 
 from emp e right outer join dept d
   on e.deptno = d.deptno;    ---右外
 select *    
 from emp e full outer join dept d
   on e.deptno = d.deptno;     ---全外
   
 select * 
 from emp e right outer join dept d
   on e.deptno = d.deptno;  
 select * 
 from dept d left outer join emp e
   on e.deptno = d.deptno;   
   
  ---查询出没有员工的部门信息
 select d.* 
 from emp e right outer join dept d
   on e.deptno = d.deptno
 where e.empno is null; 

---自连接
 ---查询员工姓名和他的上级姓名
 select * from emp;
 
 员工的mgr = 上级的empno
 
 select *
 from emp worker,emp manager
 where worker.mgr = manager.empno;
 
  select * from emp worker;
  select * from emp manager;
   --查询出入职比上级早的员工
 select *
 from emp worker,emp manager
 where worker.mgr = manager.empno
   and worker.hiredate < manager.hiredate;
 
---分组查询
select
from
where    分组前的条件(不允许出现分组函数)
group by 列1,列2,……
having   分组后的条件(关于分组函数的条件)
order by
 --分组函数 
   avg() sum() max() min() count() wm_concat()
   平均数 求和 最大  最小   统计    列转行
  select avg(sal),sum(sal),max(sal),min(sal),count(sal) from emp;
  select avg(comm),sum(comm),count(comm) from emp;
    ---分组函数不计算空值
  --查询每个部门的平均工资
  select avg(sal) from emp;
  
  select deptno,avg(sal)
  from emp
  group by deptno;

  --查询每种工作的最高工资
  select job,max(sal)
  from emp
  group by job;
  --查询每个部门中每种工作的平均工资
  select deptno,job,avg(sal)
  from emp
  group by deptno,job
  order by deptno;
    --出现在select中的列，必须出现在group by语句里
      select集合包含于group by集合
  
  --查询平均工资大于2000 的部门
  select deptno,avg(sal)
  from emp
  group by deptno
  having avg(sal) > 2000;
  --查询平均工资大于2000 的部门信息（号、名称、所在地）
  select d.*,avg(sal)
  from emp e,dept d
  where e.deptno = d.deptno
  group by d.deptno,d.dname,d.loc
  having avg(sal) > 2000
  order by d.deptno;
  
  select deptno,wm_concat(ename),count(ename)
  from emp
  group by deptno;
  
---子查询
 单行子查询 多行子查询 多列子查询
 
 --查询与SCOTT同部门的员工信息
  1）select deptno from emp where ename = 'SCOTT';
  2）select * from emp where deptno = 20;
  select * from emp 
  where deptno = (select deptno from emp where ename = 'SCOTT');
 --查询与JONES 同上级的员工
 select * from emp
 where mgr = (select mgr from emp where ename = 'JONES');
 
 --查询工资比MILLER 低，奖金比ALLEN 高的员工信息
 select *
 from emp
 where sal < (select sal from emp where ename = 'MILLER')
   and comm > (select comm frpm emp where ename = 'ALLEN');
 --查询与MARTIN 同工作，并且在1981年5 月之前入职的员工
 select *
 from emp
 where job = (select job from emp where ename = 'MARTIN')
   and hiredate < to_date('1981-5-1','YYYY-MM-DD');

 --查询 上级是JONES 的员工信息
 select * from emp
 where mgr = (select empno from emp where ename = 'JONES');
  
 --查询工资比平均工资高的员工
 select * from emp
 where sal > (select avg(sal) from emp);
 
 --查询工资比10 部门平均工资高的员工
  select * from emp
 where sal > (select avg(sal) from emp where deptno = 10);
 
 --使用子查询，查询SALES 部门的员工信息
 select * 
 from emp e,dept d
 where e.deptno = d.deptno
   and d.dname = 'SALES';
   
 select * from emp 
 where deptno = (select deptno from dept where dname = 'SALES');
 
  --使用子查询，查询出蔡成功同学 四大神术的成绩
  select * from score
  where ano = (select ano from student where aname='蔡成功')
    and bno = (select bno from class where bname = '四大神术');
    
 ---多行子查询 >all <all >any <any =any in()
 
  ---查询工资比30 部门所有员工工资都要高的员工信息
  select * from emp
  where sal >all (select sal from emp where deptno = 30);
  select * from emp
  where sal > (select max(sal) from emp where deptno = 30);
  
  >all  大于最大
  <all  小于最小
  
  >any  大于最小
  <any  小于最大
  
  =any
  in()
  
  select * from emp where sal in(select sal from emp where deptno = 30);
  select * from emp where sal in(1600,1250,2850,1500,950)
  --查询哪个部门没有员工
  存在于dept表，但是不存在于emp表
  select * from dept
  where deptno not in(select distinct deptno from emp);
  
  select * from emp;  --deptno 不能有空值
  
  --
  select * from emp
  where (sal,job) = (select sal,job from emp where ename = 'SCOTT' );
  
 ---查询每个部门的部门信息和部门人数(考虑40 部门)
 P237-238 rownum
 ---不使用组函数，查询最高工资
 ---查询工资第二高到第八高的员工信息
 
---DML 数据操作语句：insert  update delete
 --新增 
   insert into 表名 values();
   insert into 表名(列) 子查询; 
   insert into emp94(eid,ename) select 1122,'abc' from dual
                                union
                                select 1123,'abd' from dual
                                union
                                select 1124,'acd' from dual; 
   create table emp94(eid integer,
                      ename varchar(10),
                      birth date,
                      classno number(2));
   select * from emp94;
   insert into emp94(eid,classno) values(1234,11);
   insert into emp94(eid,classno) select empno,deptno from emp;  
   
  --更改 update
  update 表名 set 列名=值;
  update 表名 set 列名=值 where ……;
  update 表名 set 列1=值1,列2=值2,…… where ……;
  update 表名 set 列=子查询 where ……;
  update 表名 set 列1=子查询,列2=子查询,…… where ……;
 
  update emp set sal = 9000,deptno=40 where job = 'CLERK';
 
  ---更改，把emp中，员工的工资翻倍 奖金在原奖金基础上+500 
  update emp set sal=sal*2,comm=nvl(comm,0)+500;
 
  ---更改，把SMITH 的工资改成与KING 一样
  update emp set sal = (select sal from emp where ename = 'KING')
  where ename = 'SMITH';
 
  ---更改，把ALLEN 改成 与CLARK同部门
  update emp set deptno = (select deptno from emp where ename='CLARK')
  where ename = 'ALLEN';
  ---更改，把与BLAKE 同工作的员工的上级，改成SCOTT
  update emp set mgr = SCOTT的empno
  where job = BLAKE的job;
  
  ---更改，把处于平均工资以下的员工，都调到BOSTON (部门的loc)
  update emp set deptno = (select deptno from dept where loc='BOSTON')
  where sal < 平均工资;
  
  ---更改，把WARD 的职位与工资，都调到与KING 相同
  update emp set (job,sal) = KING的job和sal
  where ename = 'WARD';
  
  
    
 update emp set mgr = (select empno from emp where ename = 'SCOTT' ) 

where  empno in (  select  distinct empno from emp  where job = (select job from emp where ename = 'BLAKE') );




select * from emp where sal < (select avg(sal) from emp);

select dept.deptno from dept where dept.loc = 'BOSTON';



update emp set emp.deptno = (select dept.deptno from dept where dept.loc = 'BOSTON') 

where sal in ( select sal from emp where sal < (select avg(sal) from emp));