Oracle学习(二)

一、数据排序
1.1 order by   排序标准，永远出现在sql语句最后
按照工资排序(升序 asc 降序 desc) 显示 id first_name salary
select  id,first_name,salary from  s_emp;                
select  id,first_name,salary from  s_emp order by salary;   
select  id,first_name,salary from  s_emp order by salary desc;   
    1.2 排序的语法
order  by 排序标准  排序方式;
排序方式:
升序  asc  默认的顺序
自然顺序   字典顺序
降序  desc             
1.3 多字段排序
select  first_name,salary from s_emp order by  salary;
如果salary的值相同 则启用名字降序排列
select  first_name,salary from s_emp order by  salary,first_name desc; 
    1.4 NULL 值在排序中的处理
     NULL在排序中作为最大值 
     select  id,manager_id from s_emp order by manager_id;
二、单行函数
2.1 针对sql语句影响的每一行  都返回一个结果。sql影响多少行 就返回多少个结果。
select  first_name,upper(first_name) from s_emp;  
select  first_name,upper(first_name) from s_emp  where id=1; 
select  first_name,upper(first_name) from s_emp  where id>1; 
select  first_name,upper(first_name) from s_emp  where id<1;

    2.2 组函数:针对sql语句影响的一组数据做处理,最终返回一个结果。
无论sql影响多少行都返回一个结果。
select count(id) from s_emp where id=1; 
select count(id) from s_emp where id>1; 
select count(id) from s_emp where id<1; 
    2.3 处理字符串的单行函数
upper   字符串变大写
lower   字符串变小写
        select  first_name,lower(first_name) from  s_emp;

测试表   dual
select * from dual; 
select  lower('HELLO') FROM  DUAL;
concat(par1,par2) 连接两个字符串,用的很少  因为有 ||
select  concat('hello ','world') from dual;
select  concat(concat('hello ','world'),' hello') from dual; 
     
length   求字符串长度
select  length('hello')  from dual;
initcap  把每个单词的首字母变大写
select   initcap('one world one dream') from dual; 
substr(par1,par2,par3)
par1要处理的字符串
par2从什么位置开始截取 编号从1开始可以是负数,-1 代表最后一个字符
par3 截取的长度
select  substr('hello',0,2) from dual;
select  substr('hello',1,2) from dual;
select  substr('hello',-1,2) from dual;

列出 s_emp 表中 first_name 以及
first_name的后三个字符
select  first_name,substr(first_name,-3,3)  from  s_emp;
replace  替换
select replace('hello','lo','test') from dual;  
nvl(par1,par2)

2.4 数字处理函数
round  四舍五入函数
select  round(9.57)  from dual;
select  round(9.99)  from dual;

/* 保留小数点后一位 */
select  round(9.57,1)  from dual;
select  round(9.54,1)  from dual;
select  round(9.547,2)  from dual;

/* 对小数点前1位进行四舍五入*/
select  round(12.88,-1) from  dual;
select  round(12.88,-2) from  dual;
trunc  截取  
/* 截掉小数部分 */
select  trunc(9.57)  from dual;
select  trunc(9.99)  from dual;
/* 截取 并保留几位小数 */
select  trunc(9.57,1)  from dual;
select  trunc(9.54,1)  from dual;
select  trunc(9.547,2)  from dual;
/* 截取 对小数前的第几位进行截取 */
select  trunc(12.88,-1) from  dual;
select  trunc(12.88,-2) from  dual;
     
    2.5 格式显示函数
     to_char(par1,par2)
     par1要处理数字数据  
     par2 是格式  可以省略  代表把数字变成
     字符串类型。如果有格式 则按照格式显示
     数字类型的数据。
     格式如下:
     fm   代表格式开头 可以省略
     9    小数点前代表0-9的任意数字
          小数点后1-9的任意数字
     0    小数点前 代表强制显示前导零
          12345  012,345     
          1234   001,234
          小数点后  代表0-9的任意数字 
     $    美元符号
     L    本地货币符号  ￥   RMB
     .    小数点
     ,    分隔符
     select  salary,to_char(salary,
     'fm$099,999.99')  from s_emp; 
     select  salary,to_char(salary,
     'fm$099,999.00')  from s_emp; 
     select  salary,to_char(salary,
     'fmL099,999.00')  from s_emp;
     如何修改数据库的本地语言
     1.远程登录服务器
       telnet   IP
     2.切换shell
       bash
     3.修改配置文件
       vi  .bash_profile
       export  NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
       export  NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'
     4.保存退出
       esc
       shift+zz
     5.source  .bash_profile
     6.重新进入sqlplus 
         
     4.4.5  函数的嵌套
     把一个函数的返回值作为另一个函数的参数。
     把s_emp  表中 first_name 和 first_name 
     的后三个字符显示出来
     结合 substr  和  length
     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;
     列出s_emp 表中 id,first_name,manager_id
     如果manager_id 是NULL 则显示成BOSS
     select  id,first_name,
         nvl(to_char(manager_id),'BOSS') 
         from  s_emp;  
   4.5  多表查询
     4.5.1 为什么?
     需要的数据来自于多张表。
     表的设计决定的。
     4.5.2  表的介绍
     s_emp   员工表
     id       员工编号
     first_name  员工名
     dept_id     部门编号
     s_dept   部门表
     id       部门编号
     name     部门名
     列出每个员工的id  first_name和部门编号
     select  id,first_name,dept_id
         from  s_emp;
     列出每个员工的id  first_name和部门编号
     还要把部门名显示出来。
     字段如果重名则加表名区分
     /* 两张表所有的记录匹配的可能 
        笛卡尔积 */
      select  s_emp.id,first_name,dept_id,
          name
          from  s_emp,s_dept;    
    /* 表达表关系的条件 称之为 表的连接条件
       员工所在的部门编号 是一个部门的编号
       dept_id=s_dept.id */ 
      select  s_emp.id,first_name,dept_id,
          name
          from  s_emp,s_dept
              where dept_id=s_dept.id; 
---------------------------------------                   
   1.下载安装压缩包   解压
   2.找到安装文件位置  安装
     sudo  dpkg -i  gftp*
   3.使用gftp
     gftp    
     code.tarena.com.cn     
--------------------------------------- 






五、笛卡尔积  
    如果两张表 没有任何关联条件则会产生笛卡尔积。
    s_dept  
    id     部门编号
    name   部门名
    region_id   地区编号
    s_region  
    id     地区编号
    name   地区名

    5.1把每个部门的名字和对应的地区名列出
select  s_dept.name,s_region.name 
from  s_dept,s_region
where region_id=s_region.id;

    5.2 使用表的别名
select  d.name,r.name
        from  s_dept d,s_region r
        where region_id=r.id;

    5.3 列出每个员工的first_name 以及部门的名字还有地区的名字。s_emp   s_dept   s_region
select  e.first_name,d.name,r.name
from  s_emp e,s_dept d,s_region r
where  e.dept_id=d.id  and d.region_id=r.id;

    col   字段名  for  a宽度
    col   name    for  a15         
    
    5.4 连接两张表的条件都使用的是等号则这种连接称之为等值连接。
连接两张表的条件使用的是非等号则这种连接称之为非等值连接。
desc  salgrade;
desc  salgrade;
Name
---------------------
GRADE    工资级别
LOSAL    这个级别对应的低工资 
HISAL    这个级别对应的高工资

把每个员工的工资 和 工资对应的工资级别列出来。
select  salary,grade
from  s_emp,salgrade
where  salary between losal and  hisal;

select  salary,grade
        from  s_emp,salgrade
        where  salary>=losal and  salary<=hisal;

    5.5特殊的连接 (自连接)
s_emp   领导    普通员工
        id     fname   manager_id
        2       a      2
        3       c      2
        4       d      2
如果你的id 是另外一个员工的manager_id则你是领导。
select  id,first_name
from  s_emp
        where id=manager_id;

一张表中存储了 两种不同业务含义的数据,找出那些人是领导？
select distinct  m.id,m.first_name 
        from  s_emp  e,s_emp  m
        where e.manager_id=m.id;

25个员工  其中有8个领导 剩下17普通员工

    5.6 等值连接
           非等值连接
           自连接
           都是符合连接条件的数据被选中
           不符合连接条件的被过滤掉。这种连接 统称内连接。           
select distinct  m.id,m.first_name 
        from  s_emp  e,s_emp  m
        where e.manager_id!=m.id;
得到是过滤掉的数据

    外连接: 外连接的结果集等于内连接的结果集加上匹配不上的记录。
    (一个也不能少)   
    

 oracle 使用(+) 来完成外连接,(+) 字段对面的表的数据 全部匹配出来。 
      select distinct  m.id,m.first_name 
      from  s_emp  e,s_emp  m
      where e.manager_id(+)=m.id;
 
     //被找回的数据 是通过NULL 记录匹配的
      select distinct  m.id,m.first_name 
      from  s_emp  e,s_emp  m
      where e.manager_id(+)=m.id
      and e.manager_id is null; 
 
      select distinct  m.id,m.first_name 
      from  s_emp  e,s_emp  m
      where e.manager_id(+)=m.id
      and e.manager_id is not null; 


    7.列出 部门的名字 和 对应的地区名 s_dept    s_region
     select  d.name,r.name
     from  s_dept d,s_region r
     where d.region_id=r.id;
    
6.公司业务扩展 新增了部门
    insert  into  s_dept  values(100,'test',NULL);
    commit;     
    5.列出 每个部门的名字 和 对应的地区名,没有地区的部门名也要显示。
    select d.name,r.name 
from  s_dept d,s_region r
    where d.region_id=r.id(+);    
    
    4.列出每个员工的first_name  和对应的部门名s_emp   s_dept
      select e.first_name,d.name  
      from  s_emp e,s_dept d 
      where dept_id=d.id; 
  
    3.把老板部门号 变成NULL  
    update s_emp  set  dept_id=NULL where  id=1;
    commit; 

    2.列出每个员工的first_name  和对应的部门名,没有部门的员工也要显示 
select e.first_name,d.name  
        from  s_emp e,s_dept d 
        where dept_id=d.id(+);  
  
    1.给老板涨工资 
    update  s_emp  set salary=12500 where id=25;
commit; 
    列出每个员工的id  salary  和工资级别不在工资级别范围内的员工也要显示。 
select  id,salary,grade
        from  s_emp,salgrade 
        where salary  between losal(+)  and  hisal(+)
order  by id;