Day39_组函数、分组、DML、事务控制、时间格式

一.单行函数
  1.处理字符串的单行函数
    upper(par1)
    lower(par1)
    initcap(par1)
    concat(par1,par2)
    length(par1)
    substr(par1,par2,par3)
    nvl(par1,par2)
  2.处理数字的单行函数
    round
    trunc
  3.处理数字格式化显示
    to_char(par1)
    to_char(par1,par2)
    fm
    $
    L
    9
    0
    ,
    .
  4.函数的嵌套
    把一个函数的返回值 作为另一个函数的参数
二.多表查询
  1.内连接
    a.等值连接
      使用等号作为连接符号
      把每个员工的名字和对应的部门的名字显示出来
      把部门的名字和对应的地区名显示出来
    b.非等值连接
      不使用等号 作为连接符号
      把每个员工的工资和对应的工资级别显示出来
    c.自连接
      逻辑上 把一张物理表 看成多张表
      查询谁是领导？
  2.外链接
    c.自连接   查询谁是领导
      等值连接  显示所有的部门名称
      非等值连接的外链接  把每个员工的工资 和 对应的工资级别显示出来（给老板涨工资）
  3.sgl99 中规定的内外连接
    a.内连接
      from a表 join/inner b表 on 连接条件；
      select first_name,name from s_emp join s_dept on dept_id=s_dept.id;
      把每个员工的名字和对应的部门名显示出来
      select first_name,name from s_emp,s_dept where dept_id=s_dept.id;
    b.外链接
      a表 left outer join b表 on 连接条件；
      a表 right outer join b表 on 连接条件；
      a表 full outer join b表 on 连接条件；
      
      谁是普通员工？
      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;
      a表 left outer join 的意思是a表发起连接，则把a表中所有的数据匹配出来
      select distinct m.id,m.first_name from s_emp m left outer join s_emp e on e.manager_id=m.id where e.manager_id is null;
      select distinct m.id,m.first_name from s_emp e right outer join s_emp m on e.manager_id=m.id where e.manager_id is null;
    c.oracle中的全外连接
      全外连接：全外连接的结果集 等于左外连接的记过集加上右外连接的结果集 减去重复的记录
      oracle 支持full outer join
      oracle 不支持两端都加（+）
      union 合并两个结果集 然后排重
      union all 合并两个结果集
      
      select id from s_emp union select id from s_emp;
      select id from s_emp union all select id from s_emp;
三.组函数和分组
 a.组函数
  1.常见的组函数
    count    统计一组数据的个数
    max      统计一组数据的最大值
    min      统计一组数据的最小值
    sum      统计一组数据的和
    avg      统计一组数据的平均值
    
    select count(salary),max(salary),min(salary),sum(salary),avg(salary) from s_emp;
  2.组函数中可以使用distinct
    统计工资的个数  工资的和  工资的平均值
    select count(salary),sum(salary),avg(salary) from s_emp;
    select count(distinct salary),sum(distinct salary),avg(distinct salary) from s_emp;
  3.组函数 对NULL的处理方式（忽略）
    统计提成的个数  提成的和
    select count(sommission_pct),sum(commission_pct) from s_emp;
 b.分组
   1.分组的概念  和  关键字
     按照一定的标准 把数据分成若干部分
     group by 分组标准
   2.举例
     按照部门号分组  统计每个部门的人数
     select dept_id,count(id) from s_emp where 1=1 group by dept_id;
   3.按照部门号  分组  统计每个部门的平均工资
     要显示平均工资大于1400的部门？
     select dept_id,avg(salary) avgg from s_emp where 1=1 group by dept_id order by avgg;
     这句话虽然没有解决问题，但是证明了执行顺序：from ,where, group by,select,order by
     如何对组数据进行过滤？使用having
     select dept_id,avg(salary) avgg from s_emp where 1=1 group by dept_id having avg(salary)>1400;
     执行顺序：from,where,group by,having,select,order by
   4.按照部门号分组 统计每个部门的人数
     要显示人数大于等于2的部门 还要显示部门名
     select e.dept_id,count(e.id),max(name) from s_emp e,s_dept d where e.dept_id=d.id group by dept_id having count(e.id)>=2;
     在分组语句中，select后的字段，要么是分组标准，要么是经过合适的组函数处理过的
     select e.dept_id,count(e.id),name from s_emp e,s_dept d where e.dept_id=d.id group by dept_id,name having count(e.id)>=2;
     
四.子查询
  把一个查询的结果 作为另一个查询的基础
  1.where 之后
    /* 查询除了所有的领导id */
    select distinct manager_id from s_emp;
    /* 让员工id出现在领导id中 */
    select id,first_name from s_emp where id in(select distinct manager_id from s_emp);
    /* 使用子查询 找出所有的普通员工 */
    select id,first_name from s_emp where not id in(select distinct manager_id from s_emp where manager_id is not null);
    commission_pct 等于10
    select id,first_name,commission_pct from s_emp where commission_pct=10;
    select id,first_name,commission_pct from s_emp where commission_pct!=10;
  2.having 之后
    按照部门号 分组 求每个部门的平均工资
    显示平均工资大于42部门平均工资的部门
    desc s_emp;
    select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>(select avg(salary) dept_id from s_emp where dept_id = 42);
  3.from 之后
    任何一条合法select语句 都会在内存中构建一张表
    select id,first_name name,salary from s_emp;
    select id,name from (select id,first_name name,salary from s_emp) where id>10;
    
五.建表语句 和 删表语句
  1.建立表
    create table 表名(字段名  类型，字段名  类型，字段名  类型...);
    类型  number       数字类型
          varchar2(n)  变长字符串类型
          char         定长字符串
          date         日期类型
    建立一张表
     id      数字类型
     fname   变长字符串  长度是10
     sname   定长字符串  长度是10
     create table ljf(id number,name varchar2(10),qq number);
     create table fuckzhou(name varchar2(10),sname varchar2(10));
     insert into fuckzhou values('FUCK','ZHOUJUN');
   2.删除表
     drop table 表名;
     drop table qnmlgb;
     --insert into qingw values(999,'hate','zhoujun')

六.DML 语句
  1.插入语句 insert语句
    a.insert into 表名 values(值1，值2，值3)；
      insert into ljf values(1,'FUCK',88468560);
      commit;
    b.选择一些字段进行插入值
      insert into 表名(字段名1，字段名2) values(值1，值2);
      insert into qingw(id,fname,sname) values(999,'hate','zhoujun');
      没有被选中字段的值 插入的是NULL值
      如果某个字段做了非空的限制 则必须包含这个字段
  2.删除语句 delete语句
    delete from 表名 where 条件;
    delete from ljf where id=1;
  3.更新语句 update语句
    update 表名 set 字段名=值,字段名2=值2 ...;

七.事务控制语句
  1.事务控制语句的含义
    commit; 确认事务的操作
    rollback; 回滚事务的操作
    savepoint n; 保存点
  2.事务的四大特性
    a.原子性  事务中的语句是一个不可分割的整体
              转帐
              update account set money=money-20000 where ano='A';
              a
              update account set money=money+20000 where ano='B';
              b
              if(a&&b)
              {
                  commit;
              }else{
                  rollback;
              }
              要么一起成功  要么一起失败
              
    b.隔离性
              一个事务中的语句 在没有提交以前 这个语句引起的数据的变化 对另一个事务而言是不可见的
              create table testtransation(id number primary key,name varchar2(30),money number);
              insert into testtransation values(100,'test',10000);
              select * from testtransation;
    c.一致性
    d.持久性
  3.有些时候 做到 部分成功部分失败
    insert into testtransation value(212,'test2',10000);
    savepoint a;
    insert into testtransation value(312,'test3',10000);
    savepoint b;
    insert into testtransation value(412,'test4',10000);
    rollback to b;
    commit;
八.日期类型
  1.日期类型的默认表现
    DD-MON-YY
    25-NOV-13
    25-11月-13
    desc s_emp;
    select start_data from s_emp;
    select start_date,salary from s_emp;
    按照入职日期排序 显示start_date salary
    select start_date,salary from s_emp order by start_date;
  2.改变默认的显示格式
    to_char(日期数据,'日期格式');
    yyyy    四位年
    mm      两位月
    dd      两位天
    
    hh      12小时制
    hh24    24小时制
    mi      分钟
    ss      秒
    
    day     星期几
    mon     英文月缩写
    month   英文月的全写
    pm      表达上午am下午pm
    
    select to_char(start_date,'yyyy-mm-dd hh24:mi:ss'),salary from s_emp order by start_date;
  3.如何存入日期
    建立一张标  订单表
    id  number
    oname varchar2(50)
    omoney number;
    odate  date;
    
    create table oddb(id number primary key,oname varchar2(50),omoney number,odate date);
    a./* 默认方式只能放入 年月日 时分秒都是零 */
      insert into oddb values(1,'testdj',1888,'25-NOV-13');
    
      select id,to_char(odate,'yyyy-mm-dd hh24:mi:ss') from oddb where id=1;
    b.使用sysdate放入日期数据
      insert into oddb values(2,'testdj',1888,sysdate);
    c.指定一个具体的时间放入数据库
      '2008-08-08 20:08:08'
      to_date(日期字符串，'日期格式') 根据日期字符串的格式把一个日期字符串 转换成日期
      insert into oddb values(3,'testdj',1888,to_date('2008-08-08 20:08:08','yyyy-mm-dd hh24:mi:ss'));
  4.日期的调整
    sysdate
    按照天为单位调整
    select to_char(sysdate+1,'yyyy-mm-dd hh24:mi:ss') from dual;
    按照小时，分钟，秒调整
    select to_char(sysdate+20/(24*60),'yyyy-mm-dd hh24:mi:ss') from dual; -- 加了二十分钟