Day38_数据库、内连、外连

2013年11月22日 星期五 10时07分41秒 

    2.测试单行函数
      dual 单行单列的标
      a.字符串处理函数
        upper  变大写
        lower  变小写
        select lower("HELLO") from dual;
        initcap 每个单词的首字母大写
          select initcap('one world one dream') from dual;
        concat(par1,par2)  连接两个字符串，用的很少，因为有||
          select concat('Liu','Jianfan') from dual;
          select 'hello'||'world'||'hello' from dual;
          select concat('hello',concat('word','hello')) from dual;
        length(par1)
          select length('hello') from dual;
        substr(par1,par2,par3)
          par1 要处理的字符串 或者 字段
          par2 是从什么位置开始处理 编号从1开始，也可以是负数，-1 代表最后一个字符
          par3 截取多少个字符
          select substr('hello',1,2) from dual;
          把S_开头的表名的前三个字符
          select table_name,substr(table_name,1,3) from user_tables where table_name like 'S\_%' escape'\';     '
          select table_name,substr(table_name,-3,3) from user_tables where table_name like 'S\_%' escape'\';    '
        b.处理数字的函数
          round 四舍五入
            select round(9.57) from dual;        --10
            select round(9.47) from dual;        --9
            select round(9.57,1) from dual;      --9.6
            select round(9.494,2) from dual;     --9.49
            select round(19.57,-1) from dual;    --20
            select round(12.57,-1) from dual;    --10
            select round(12.57,-2) from dual;    --0
          trunc 截取
            select trunc(12.57,1) from dual;     --12.5
            select trunc(9.494,2) from dual;     --9.49
            select trunc(9.497,2) from dual;     --9.49
        c.数字格式化显示函数
          to_char(par1,par2)
            par1 是要处理的数字或者字段
            par2 是格式  可以省略  代表把一个数字类型数据转换成字符串类型，格式以fm开头
            9    小数点前代表0-9任意数字 小数点后代表1-9的任意数字
            0    0 用在小数点前代表强制显示前导零
                 12345  012,345
                 0 用在小数点后代表0-9的任意数字
            $    美元符号
            L    本地货币符号
            ,    分隔符号
            .    小数点
            
            显示 s_emp 表员工的工资 按照格式 'fm$099,999,99'
            select to_char(salary,'fm$099,999.999') from s_emp;    --$001,200.
            select to_char(salary,'fm$099,999.000') from s_emp;    --$001,200.000
            select to_char(salary,'fmL099,999.000') from s_emp;    --L根据本地语言环境判断
            如何修改本地的语言环境
            1.切换shell
              bash
            2.打开配置文件
              vi .bash_profile
            3.写入配置数据
              export NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
              export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'
            4.保存退出文件
              shift+zz
            5.让环境变量生效
              source .bash_profile
            6.重新进入sqlplus
            
        d.函数嵌套
          把一个函数的返回值 作为另一个函数的参数
          把s_emp 表中 first_name 后三个字符列出来
           select first_name,substr(first_name,-3,3) from s_emp;
           length 结合substr完成
           select first_name,substr(first_name,length(first_name)-2,3) from s_emp;
           
           显示s_emp 表中 id,first_name,manager_id
           select id,first_name,manager_id from s_emp;
           select id,first_name,nvl(to_char(manager_id),'BOSS') from s_emp;

E.多表查询
  1.把每个员工的id，first_name，dept_id
    select if,first_name,dept_id from s_emp;
  2.查询的数据来自于俩张表
          s_dept(部门表)
          SQL> desc s_dept;(笛卡尔乘积)
               select s_emp.id,first_name,dept_id,name from s_emp,s_dept where dept_id=s_dept.id;
       
            NAME
       -------------------------------
          ID             部门编号
          NAME           部门名字
          REGION_ID      地区编号
        SQL>  desc   s_region
           ID             地区编号
           NAME           地区名
     把每个部门的名字 和 对应的地区名字列举出来
         select s_dept.name,s_region.name from s_dept,s_region where s_dept.region_id = s_region.id;
       col xxx for a15-- 调整显示的格式
     4.表的别名
          select d.id,d.name,r.name from s_dept d,s_region r where d.region_id=r.id;
           在from后面给表取名子后，整个语句中必须使用取的别名；不然就不识别；
     5.连接两张表的条件的符号 的都是等号 所以这种连接称之为等值连接；
       连接两张表的条件符号   不是等号，这种连接称之为非等值连接
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRADE						    NUMBER  工资级别
 LOSAL						    NUMBER   同等级的对应的最低工资
 HISAL						    NUMBER   同等级的对应的最高工资
       s_emp 表中  first_name salary 以及工资对应的工资级别
            select s_emp.first_name,s_emp.salary,salgrade.grade from s_emp, salgrade where s_emp.salary between losal and hisal;
 6. 自连接
       领导是员工
       不是领导的员工 也是员工
     找出所有的领导；
         id  manager_id
       select  distinct m.id,m.first_name from s_emp e,s_emp m where e.manager_id=m.id; 
       
----------------------------------------------------------------------------------
连接：
  等值连接
  非等值连接
  自连接
  这三种连接都有一个共同的特点 符合连接条件就选中数据 不符合条件就过滤掉数据

找出所有的普通员工？
  select distinct m.id,m.first_name from s_emp e,s_emp m where m.id=e.manager_id;
外连接：外连接的结果集 等于 内连接的结果集 加上 匹配不上的记录
        一个也不能少
  select distinct m.id,m.first_name from s_emp e,s_emp m where e.manager_id(+)=m.id;
  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;
9.列出所有部门的部门名 和 部门名对应的地区名
  s_dept    部门表
  id
  name
  region_id
  
  s_region  地区表
  id
  name
  select distinct d.id,d.name,r.name from s_dept d,s_region r where d.region_id=r.id;
  新增加了一个没有地区的部门
  insert into s_dept values(999,'T_T',NULL);
  commit;
  --显示所有的部门名 和 对应的地区名
  select distinct d.id,d.name,r.name from s_dept d,s_region r where d.region_id=r.id(+);
  --把没有地区编号的部门显示出来
  select distinct d.id,d.name,r.name from s_dept d,s_region r where d.region_id=r.id(+) and r.id is not null;
10.列出每个员工的first_name salary 和工资对应的工资级别
   salgrade
   s_emp
     select e.first_name,e.salary,g.grade from s_emp e,salgrade g where e.salary between losal and hisal;
   给老板涨工资
   update s_emp set salary=22500 where id=1;
   commit;
   select e.first_name,e.salary,g.grade from s_emp e,salgrade g where e.salary between losal(+) and hisal(+) order by id;