Oracle 笔记02

显示first_name 的后三个字符
hello
12345
54321
select first_name,substr(first_name,-3,3) 
from s_emp;


函数嵌套：
确定一个字符串的倒数第三个字符
select first_name,
substr(first_name,length(first_name)-2,3) 
from s_emp;


数字的单行函数：
round    ------四舍五入
select round(49.9)  from dual;//50
select round(49.9,0)  from dual;//50
select round(49.77,1)  from dual;//49.8
select round(49.774,2)  from dual;//49.77
select round(49.77,-1)  from dual;//50
select round(49.774,-2)  from dual;//0
select round(59.774,-2)  from dual;//100
trunc    ------截取
select trunc(49.9)  from dual;//49
select trunc(49.9,0)  from dual;//49
select trunc(49.77,1)  from dual;//49.7


格式显示函数
to_char(参数1，参数2) 
参数1 要处理的数据
参数2 格式字符串（不是必须的）
如果没有参数2 则把任何类型转成字符串
有参数2
fm   格式说明符 这个不是必须的
9    代表任意数字
0    强制显示前导零 9   009
L    本地货币符号   ￥   RMB
$    美元符号
.    小数点
，   国际货币分隔符号 10000  10,000 


select salary,to_char(salary,'fmL099,999.00')
from s_emp;


select to_char(2500.88,'L099,999.99')  from dual;
select to_char(2500.88,'L099,999.00')  from dual;


验证你的语言环境
select userenv('lang')  from dual;


1.退出sqlplus
2.如果系统的shell不是bash  则切换shell  
   bash
3.回到主目录  
   cd
4.编辑配置文件  
  vi  .bash_profile
  NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
  export NLS_LANG
5.保存退出
6.source  .bash_profile
7.重新进入sqlplus 
8.验证语言环境 
  SQL>select userenv('lang')  from dual;
  SQL>ZHS 


如果想切换成英文
  NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'
  export NLS_LANG


-----------------------------------------
函数嵌套
列出first_name ,manager_id   
如果manager_id 是NULL 则显示成'BOSS'
select  first_name,
nvl(to_char(manager_id),'''BOSS''')
from s_emp;


--------------------------------------------
F.多表查询（最重要的内容）
列出first_name,dept_id 
select first_name,dept_id  from s_emp;


s_emp   ,s_dept
 Name
 --------------
 ID          部门编号
 NAME        部门名称
 REGION_ID   地区编号 
列出first_name,dept_id ，部门名称


s_emp  25
s_dept 12
select first_name,dept_id,name  
from s_emp,s_dept;


笛卡尔积


产生一个合理的集合 
两张表中如果有相同的字段名  
select first_name,dept_id,name  
from s_emp,s_dept
where dept_id=s_dept.id;


select first_name,dept_id,s_dept.id,name  
from s_emp,s_dept
where dept_id=s_dept.id;


表连接的种类：
等值连接：用等号做为连接符号
介绍另一张表  s_region(地区表)
 s_dept   部门表
 Name
 --------------
 ID          部门编号
 NAME        部门名称
 REGION_ID   地区编号 


 s_region  地区表
 Name
 -------------
 ID      地区编号
 NAME    地区名称
 列出部门名称   和每个部门属于的地区名称
  select s_dept.name,s_region.name
     from  s_dept,s_region
         where  region_id=s_region.id;


select s_dept.name,region_id,s_region.id,
s_region.name
     from  s_dept,s_region
         where  region_id=s_region.id;


col name for  a15
设置某一列的宽度(查询语句之前使用)


非等值连接:不用等号做连接符号
SQL> select * from salgrade;


     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999
列出s_emp表中每个人 first_name的工资级别
select first_name,salary,grade
    from  s_emp,salgrade
    where salary  between losal and hisal; 


等值连接和非等值连接 都是内连接
内连接:符合连接连接条件就被选中 不符合
连接条件就被过滤掉
内连接中的有种特殊的连接------自连接
s_emp 
id   first_name   manager_id        
 1    Carmen      
 2    test        1
 3    hello       1
 4    world       2
 
 3号和4号不是别人的manager_id
 领导----这个员工id是别人的manager_id 
 物理上只有一张表  
  
 列出所有领导的first_name (8个)
 select distinct manager_id from s_emp 
     order by manager_id;
补充:排序中把NULL值做为最大值处理
 select first_name,manager_id from s_emp
     where  id=manager_id;
     //上面的是找自己管自己的员工
 逻辑上考虑成两张表  解决树状关系   
   select distinct m.first_name,m.id
        from   s_emp m,s_emp e 
   where  m.id=e.manager_id
   order by m.id;
  
内连接：等值 
        非等值
自连接 


一共有25个员工  找出了8个领导  
写一条sql把17个普通员工列出来 
列出first_name  id  manager_id


select distinct m.first_name,m.id
        from   s_emp m,s_emp e 
   where  m.id!=e.manager_id;
查出了8个领导  -----内连接
 select distinct m.first_name,m.id
        from   s_emp m,s_emp e 
   where  m.id=e.manager_id;


外连接的结果集=内连接的结果集+过滤掉的数据
select distinct m.first_name,m.id
        from   s_emp m,s_emp e 
   where  m.id=e.manager_id(+);
需要领导表中所有的数据
(+)对面的表的数据全部匹配出来
要普通员工
select distinct m.first_name,m.id
        from   s_emp m,s_emp e 
   where  m.id=e.manager_id(+)
   and  e.manager_id is null;
要领导
select distinct m.first_name,m.id
        from   s_emp m,s_emp e 
   where  m.id=e.manager_id(+)
   and  e.manager_id is not null;


列出s_emp表中每个人 first_name的工资级别
s_emp   salgrade
 select first_name,salary,grade
    from  s_emp,salgrade
        where salary between  losal and hisal;
update  s_emp  set salary=12500 where id=1;
commit;
你要所有员工  还是所有的工资级别？
 select first_name,salary,grade
    from  s_emp,salgrade
        where salary between  losal(+) and hisal(+);
 要那些没有工资级别的人
 select first_name,salary,grade
    from  s_emp,salgrade
        where salary between  losal(+) and hisal(+)
and  losal is null and  hisal is null;


列出所有的部门名称 对应的地区名称
s_dept     s_region
关系
region_id=s_region.id
select s_dept.name,s_region.name   
   from  s_dept,s_region
       where region_id=s_region.id;
//12 row被选中  
公司成立了新的部门  编号100
insert into s_dept values(100,'test',NULL);
commit;
列出所有的部门名称 对应的地区名称
我们需要部门表中所有的数据  你要把(+)加在那
一端？
select s_dept.name,s_region.name   
   from  s_dept,s_region
       where region_id=s_region.id(+);
select s_dept.name,s_region.name   
   from  s_dept,s_region
       where s_region.id(+)=region_id;
SQL99标准
左外连接  ----left  outer join  
右外连接  ----right outer join
全外连接  ----full  outer join




inner  join      简写为  join
查询部门名称和它对应的地区名称
select s_dept.name,s_region.name   
    from  s_dept,s_region
        where region_id=s_region.id;
了解下面的写法：
select s_dept.name,s_region.name   
    from  s_dept join s_region
        on region_id=s_region.id;
select s_dept.name,s_region.name   
    from  s_dept inner join s_region
        on region_id=s_region.id;
列出所有的部门名称 和对应的地区名称
我们需要部门表中所有的数据  
要考虑让那张表
发起连接  如果那张表发起外连接则那张表的数据
全部被显示出来  
select s_dept.name,s_region.name   
    from  s_dept left outer join s_region
        on region_id=s_region.id;
select s_dept.name,s_region.name   
    from  s_region right outer join s_dept
        on region_id=s_region.id;
如果要过滤数据 需要使用where条件
select s_dept.name,s_region.name   
    from  s_region right outer join s_dept
        on region_id=s_region.id
where  s_region.id is null;


全外连接=左外连接+右外连接 去掉重复的数据


oracle如何实现全外连接：
不是两端都加(+)  而是使用uinon  和 union all
实现


uinon      合并两个结果集然后排重
select  id  from s_emp   union
select  id  from s_emp;//25
union all  合并两个结果集不排重
select  id  from s_emp   union all
select  id  from s_emp;//50


连接-----内连接----等值 非等值  自连接
    -----外连接----等值 非等值  自连接
           
-----------------------------------------
G 组函数和分组
  常见的组函数
  count()  ------统计个数
  max()    ------求最大值
  min()    ------求最小值      
  avg()    ------求平均值
  sum()    ------求和


对一组数据处理之后 得到一个结果
select   max(salary)  from s_emp;


统计s_emp表中提成的平均值？
select avg(commission_pct)  from s_emp;
组函数对NULL  忽略


select avg(distinct commission_pct)  from s_emp;


按照部门号  分组  统计每个组的员工数
group by  分组标准
select   dept_id,count(id)  
    from s_emp  
        group by  dept_id;


按照部门号  分组  统计每个组的平均工资
把部门名称显示出来
select   dept_id,avg(salary),name  
    from s_emp,s_dept
        where dept_id=s_dept.id
        group by  dept_id,name;
同一个部门id 肯定对应同一个部门名称
select   dept_id,avg(salary),name  
    from s_emp,s_dept
        where dept_id=s_dept.id
        group by  dept_id;
在分组的语句中  出现在select后的字段
要么是分组标准  要么要经过组函数处理 
select   dept_id,avg(salary),min(name)  
    from s_emp,s_dept
        where dept_id=s_dept.id
        group by  dept_id;
思考：如何只显示平均工资大于1500的
对分组后的数据进行过滤 要采用having
having 出现group by 之后
select   dept_id,avg(salary),min(name)  
    from s_emp,s_dept
        where dept_id=s_dept.id 
        group by  dept_id
having  avg(salary)>1500;


select   dept_id,avg(salary) asal,min(name)  
    from s_emp,s_dept
        where dept_id=s_dept.id 
        group by  dept_id
having  avg(salary)>1500
order by  asal;


执行顺序：
    from
    where 
    group by 
    having 
    select
    order by


group  by  分组标准
对组数据过滤  使用having
select 后的字段 要么是分组标准
要么使用组函数进行处理
sql的执行顺序是什么
---------------------------------------
G.子查询
  把一个sql的查询结果 
  作为另一个sql的查询基础


  可以使用子查询的位置
  where后
  谁是领导？8
  //得到所有领导的id  和NULL
  select distinct manager_id from s_emp;


  select first_name,id from s_emp  
  where id in (select distinct 
  manager_id from s_emp);
    select first_name,id from s_emp  
  where id in (1,2,3,6,7,8,9,10,NULL);
  使用子查询查出所有的普通员工？
  
  如果子查询返回多个结果 则不能使用等号
  找出和id是1的 职位相同的员工？




  from 后
  from (子查询)
  这时的子查询实际上是一张内存表
  任何sql语句都是一张内存表
  select  dept_id,max(salary) sal from s_emp 
  group by  dept_id;
  select  * from (select  dept_id,max(salary) sal from s_emp 
  group by  dept_id) where sal>1500;
  having 后
  查询50部门的平均工资


  求平均工资大于50部门平均工资的部门名称
  select  avg(salary) from s_emp 
  where dept_id=50;


  select dept_id ,avg(salary)  from s_emp 
      group by dept_id
      having avg(salary)>(select  avg(salary) from s_emp 
  where dept_id=50);