41，数据库（03）

/*
 达内学习 Oracle day40 2013-10-28
*/
E，多表查询
  执行脚本 ：进入sqlplus  @路径/脚本名
  不写多表查询where 结果是笛卡尔积
  把部门的名称和对应地区名称列出来
  select d.name,r.name from s_dept d,s_region where region_id = r.id;  有别名以后就只能用别名
  from 执行早于 where,where 早于 select
  
  from where 多表查询的另一种写法
  a 表 join b 表 on 连接条件
  select d.name,r.name from s_dept d join s_region r on region_id = r.id;
  select e.dept_id,d.name from s_emp e join s_dept d on e.dept_id = d.id;
  >>> 多表查询的关键就是找到两张表的关系 用等号连接的叫等值连接
  >>> 表达两张表的连接条件 不是用等值 叫非等值连接
  select s_emp.id,salary,salgrade.id from s_emp,salgrade where s_emp.salary >= salgrade.losal and s_emp.salary <= salgrade.hisal;
  >>> 特殊的连接 领导是员工 员工不一定是领导
   一张表中有多层含义的数据，就需要逻辑上把一张表看成多张表。
   使用表的别名
  select distinct m.id,m.first_name from s_emp e,s_emp m where e.manager_id = m.id;
----------------------------------------------------------
等值连接、非等值连接、自连接、这些连接 都符合 符合where条件的数据就被选中，不符合where条件的数据被过滤掉。 这个连接称为内连接

列出所有的部门名 和部门对应的地区名
    s_dept:name,region_id;
     s_region: id,name
     select d.name,r.name from s_dept d,s_region r where r.id = d.region_id;
      新增一个部门
      insert into s_dept values(100,'test',NULL);
      commit;
列出所有部门名 和对应的地区 名 没有地区名的部门 也要显示
 外连接： 外连接的结果集，等于内连接的结果集，加上匹配不上的记录， 一个也不能少
oracle 中使用（+） 把不匹配的记录找回来；显示（+）对面没有匹配的数据。
select d.name,r.name from s_dept d,s_region  where s.region_id = r.id(+);
列出员工的first_name和员工所在的部门名
select e.first_name, d.name from s_emp e,s_dept d where e.dept_id = d.id;

列出部门号不出现在部门表中的员工的名字 部门显示 no dept
select first_name,nvl(name,'no dept') from s_emp e,s_dept d
where dept_id = d.id(+) and d.id is NULL;
------------------------------------------------------
找出所有的领导
select distinct e.first_name ,e.id from s_emp e,s_emp m where e.id = m.manager_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;
----------------------------------------------------
列出 员工的工资 和 工资所在的工资级别
select s_emp.id,salary,salgrade.id from s_emp,salgrade where salary between losal and hisal order by e.id;

update s_emp set salary = 12500 where id = 10 commit;
列出所有员工的工资和工资所在的工资级别
select e.id,salary,s.id from s_emp e,salgrade s where salary between losal(+) and hisal(+) order by e.id;
----------------------------------------------------------
SQL99规定了内外连接的标准
 内连接
 a 表 join b 表 on 连接条件 where 过滤条件
 a 表 inner join b 表 on 连接条件 where 过滤条件
 外连接：
 a 表 left outer join b on 连接条件 where 过滤条件   ;左外连接
 a    right outer join b on 连接条件 where 过滤条件 ；右外连接
 a    full  outer join b on 连接条件 where 过滤条件 ；全连接   
select e.id,salary ,s.id from s_emp e left outer join salgrade
on salary between 
要把哪张表的数据匹配出来，则让哪张发起连接，左边用left 右边用right

全外连接：只是逻辑概念，全外连接的结果等于做外连接的结果集， 加上右外连接的结果集然后减去重复的记录。
oracle不直接支持全连接， 不是通过两端加（+）的方法，而是使用 union 合并两个结果集，并排重，union all 合并两个结果集。
 
F，组函数和分组
 1，组函数的概念，对一组数据处理之后得到一个结果。
 2，常见的组函数 count max min sum avg
 3，统计s_emp表中有多少员工，员工的最高工资
   select count(id),max(salary),min(salary) from s_emp;
 4，统计s_emp 表中工资和工资的平均值
 select sum(salary),sum(distinct salary),avg(salary),avg(distinct salary) from s_emp;
 5，组函数 对 null 值的处理方式
    组函数对 null 的处理方式是 当他不存在
 分组：
  1，   按照一定的标准把数据分成若干组
   where 子句 group by 分组标准 ；位置在where之后
  2，举例
    按照部门号 把员工分组 统计每个部门的人数
 select dept_id ,count(id)  from s_emp where 1= 1 group by dept_id; -- 单独把空值分一组
    按照部门号分组 统计几每个部门的平均工资 和工资的和
  select dept_id,avg(salary),sum(salary) from s_emp group by dept_id;
  3, 按照部门号分组  统计每个部门的平均工资 和 工资的和 要求 工资的和大于3000的
   select dept_id,avg(salary),sum(salary) from s_emp where 1=1 group by dept_id having  sum(salary)>3000 order by sal;
-- 执行顺序
 from
  where
   group by
    having
     select
      order by
  4,按照部门号分组 统计每个部门的人数 列出大于两个人的部门
 select count(id) ,dept_id from s_emp where 1 =1 group by dept_id having count(id) >2;
  5,按照部门号分组 统计每个部门的人数 列出大于2个人的部门，要求显示部门名
   select 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;
    在分组语句中select 后的字段要么是分组标准，要么是经过合适的组函数（max,min）处理过的。
 select deptid,count(d.id),max(name) from s_emp e,s_dept d where e.dept_id = d.id group by dept_id, name having count(e.id) >2;
---------------------------------------------------------------------------------
G，子查询
把一个查询的结果 作为另一个查询的基础
 1，where 后
    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 id not in(select distinct nvl(manager_id,0) from s_emp); --小心null
 2，having 后
      求平均工资大于42部门平均工资的部门 列出部门id,平均工资
  select dept_id ,avg(salary) from s_emp group by dept_id having avg(salary) > (select avg(salary) from s_emp where dept_id = 42);
 3，from 后
 任何一条sql,都可以看成一张内存表
    select id ,first_name,salary from s_emp;
---------------------------------------------------------------------
DDL
  1，添加表   create table tab_name
  (
 eno number(7),
    fname varchar2(10),
    sname char(10) --     最后一个不用逗号
  );
  2，删除表
   drop table tab_name;
  3, 数据类型
    number 数字类型
    number(7) 最大是七位数
    number(7,2)总共七位，小数点占两位，整数最大5位
    number(5,-2) 有效共5位，小树2位，整数7位 1234567 --------->1234500
    char 定长字符串 
    varchar2（10）可变长度
  4，日期类型
     a. date 类型
    和系统语言相关
    NLS_LANG
    系统默认的日期格式  ‘dd-MON-yy’(英语)  ‘dd-n月-yy’(汉语）
     b. select start
 create table order_10019 (
 oid varchar2(30),
   oname varchar2(30),
   omoney number,
   odate date
);
     c,打破时间的默认表现
      to_char(par1,par2)
       par1 要处理的日期类型的数据
       par2 要表现的日期格式字符串
       yyyy四位年  mm两位月 dd两位日 hh时（12）hh24(24) mi分 ss秒
       mon 三位英文月 month月全拼 day星期几 pm上午am 下午pm
       select to_char (odate,'yyyy-mm-dd pm hh24:mi:ss day' from
        系统默认放入的日期 十分秒信息都是0
     d，如何放入带十、分、秒 信息的日期
    sysdate 可以直接获得系统当前时间
      to_date(par1,par2) 把日期字符串转换成日期函数
        par1 要转换的日期字符串，
        par2  根据日期字符串 指定日期格式
           yyyy-mm-dd hh24:mi:ss
    insert into table_name values('2008','test',2800,to_date('2008-9-9 20:09:8','yyyy-mm-dd hh24:mi:ss')) ;
    select to_char (odate,'yyyy-mm-dd hh24:mi:ss pm day'from mytable where oid = 'bj2008' ;
    inser into table_name values('aa','bbb',200,to_date('2012-12-20 23:56:56','yyyy-mm-dd hh24:mi:ss'));
     f，日期的运算
      如何按照天来调整
      select to_char（sysdatem,'yyyy-mm-dd hh24:mi:ss')from dual;
      select to_char (sysdate+1,'yyyy-mm-dd hh24:mi:ss') from dual; --调整到明天
select to_char (sysdate+1/24,'yyyy-mm-dd hh24:mi:ss') from dual;
---向后调整一小时
select to_char (sysdate+1/24/60/60,'yyyy-mm-dd hh24:mi:ss') from dual; --向后调整一秒
     g,特殊调整
        把时间向后调整一个月
      add_months(日期，n) n是正数，向后调 n是负数向前提调    
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(add_months(sysdate+3),'yyyy-mm-dd hh24:mi:ss') from dual;
      last_day(日期） 得到日期的当月的最后一天的日期时间
      next_day(日期，'星期几'） -- 下个星期几的时间点
 select next_day(sysdate,'monday') from dual;
 select to_char(next_day(sysdate,'monday'),'yyyy-mm-dd hh24:mi:ss') from dual;
     h.对日期的一些数学操作
     months_between  两个日期之间相差多少个月
     round  对日期进行四舍五入
  1，默认以天位单位进行四舍五入
          select to_char(round(sysdate),'yyyy-mm-dd hh24:mi:ss') from dual;
        2，指定四舍五入的单位
          select to_char(round(sysdate,'dd'),'yyyy-mm-dd hh24:mi:ss') from dual; --以天为单位，天后面是否进一
     trunc  对日期进行截取 
          select to_char(trunc(sysdate,'mm'),'yyyy-mm-dd hh24:mi:ss') from dual;    --以月为单位，月后面的截断，回到月初

给定一个日期 计算日期对应月的最后一天的最后一秒的时间点
 select to_char(trunc(add_months(sysdate,1),'mm')-1/24/60/60,'yyyy-mm-dd hh24:mi:ss') from dual;
-------------------------------------------------------------------
DML
 account
 create table accountwang
(
  aid number,
  aname varchar2(30),
  apasswd varchar2(30)
);
 1,增加数据
  a. insert into tab_name values(val1,val2）;
  b. 选择一些字段进行插入,必须包含所有的非空字段
      insert into tab_name(字段1，字段3） values(值1，值2)；
   如果一个字段没有被选择，则这个字段的值是null
 2，删除数据
      delete from tab_name where
 3, 更新数据
       update  tab_name set 字段名 = 值1，字段名 = 值2 where 条件