2020-11-09

/**sql的分类
数据查询语言DQL
   --DQL语句 select 
 
数据操作语言DML
  --DML语句 insert delete update merge 
  --作用操作行
数据定义语言DDL
   --DDL语句create drop alter truncate  rename comment
   --作用 操作数据库对象（表、视图等）
数据控制语言DCL
    --DCL语句  grant revoke（允许客户访问 、撤销客户访问）
    --作用 操作对数据和数据库对象的访问
事务控制语言TCL
    DCL语句 commit rollback savepoint
    --作用对事务进行控制

**/

/**sql的分类
数据查询语言DQL
   --DQL语句 select
   所有算数表达式有一个为空，就为空
   比较：>,>=,<,<=,=,  后面都是不等于<>,!=,^=
   特殊运算符:
   between....and 条件之间的
   like 模糊
   in 范围
   is null 判断空
   多条件可以使用not ,and ,or链接
   
   单行函数：
   函数的概念：具有某种功能的程序块,通常包括输入端和输出端
               分类:单行函数：接收一个数据，输出一个数据
                    多行函数:聚合函数  分行函数  接受一组数据  输出一组数据
   常见字符函数:
       大小写相关: lower ,upper inincap(小写 大写 首子母大写）
       其他:trim ,ltrim rtrim ,lpad,
       instr 查找字符串位置返回索引 从1开始
       ,substr 截取字符串
       length 字符串长度
       concat 链接字符串 拼接
       peplace 替换的
       
   常见数值函数:某种功能的程序块
         round 四设五入
         trunc 去尾
         mod:求摸（求余数）
   常键日期函数:
       sysdate :获取当前系统时间
       months_between:获取两个日期相差月数
       add_month:获取几个月后的日期
       last_day:获取本月最后一天的日期
       extract:从日期中获取某一部分
   常见转换函数
      to_char:将日期或数值转换为指定格式的字符
      to_date:将指定格式字符串转换为日期
      to_number:将指定格式字符转换为数值
   其他函数
   
   **/
   select ename,job,sal+1000 ff,deptno from emp ; 
    
   select concat(ename,job) from emp;
   
   select lower('HELLO'),upper('hello'),initcap('hello') from dual;--字母小写   字母大写   首字母大写
   
   
    select trim('H' from 'HelloHH'),ltrim('HelloHH','H'),rtrim('HelloHH','H') from dual; --去掉所有带H的   去掉右边H  去掉左边的H
    select 'ss ' from dual;
    select lpad(1000,5,'*'),rpad(1000,5,'*') from dual;  --左补齐  右补齐
    select inset('hello tom','tom') from dual; --查早字符串在字符中的位置 从索引一开始的
    
    select substr('hello',3) from dual;  --截取从第3位开始截取之后的，可以n-n个三个参数
    
    select concat('aa','bb'), length('tom') from dual; --concat链接字符串  ，length求长度
    
    select replace('hello','h','t') from dual;  --替换
    --数字函数
    select round(123.456,2),round('123.456',-2),round(123.456,0),round(123.456) from dual; --四舍五入
    select trunc(123.456,2),trunc('123.456',-2),trunc(123.956,0),trunc(123.956) from dual; --去尾

    select mod(12,5) from dual;
    --日期函数
    
    select sysdate+2 from dual;
    
    select add_months(sysdate,3) from dual;  --三个月后
    
    
   select ename, months_between(sysdate,hiredate) from emp; --入职时间到今天又多少个月
   
   
   select  last_day(sysdate) from dual;  --当前月的最后一天
   
   select  extract(year from sysdate) from dual; --当前日期年
   select  extract(month from sysdate) from dual; --当前日期月
   select  extract(day from sysdate) from dual; --当前日期日
   
   --转换函数
   --to_char:将日期或数值转换为指定格式字符
   --to_date:将指定格式字符转换为日期
   --to_number:将指定格式字符转换为数值

    select to_char(sysdate,'yyyy-mm-dd') from dual;
    select to_char(123.45,'L9,999,999.00') from dual;
   
    select to_date('1980-01-01','yyyy-MM-DD') from dual;
 
    select to_number('$222','$999') from dual;
    
    --空值转换函数 nvl,nvl2,nullif
    
    select ename,comm,nvl(comm,0) from emp;
     select ename,comm,nvl(to_char(comm),'char') from emp;
     --给各个部门涨工资
     select ename,deptno,sal,
     decode(deptno ,
      10 ,sal+100,
      20, sal+1000,
      sal ) --其他)
       from emp;
       
       select ename,deptno,sal
          ,case deptno
          when 10 then sal+100
          when 20 then sal+200
          else deptno end alll
            from emp;
            
          select ename,deptno,sal
          ,case 
          when deptno=10 then sal+100
          when deptno=20 then sal+100
          when deptno=30 then sal+100
          else sal end
            from emp;
    --聚合函数 和分组函数
    --聚合函数:
       --max:求最大值 
       --min:求最小值
       --sum:计算和
       --avg: 计算平均值
       --count:统计行数
       
       --max,min  可以应用在字符、数值和日期类型数据上
       --sum,avg: 可以应用在数值类型上
       --count:统计行数、值不为空的行数 
       
       select max(HIREDATE),min(HIREDATE),max(sal),min(sal) from emp;        
        select count(*) from emp;
        
         select count(comm) from emp;
         
           select * from emp;
           --分组 group by：根据指定的列进行分组统计
           --having 限定显示分组后的数据
           
           --求每个部门的最高工资
           
           select deptno,max(sal),min(sal) 
           from emp
           group by deptno
           having min(sal)>20;
           
           --在select子句中出现的非分组列，必须出现在group by 子句中
           select deptno,job,avg(sal) from emp
           group by deptno,job
           order by deptno desc;
   
 /***
数据操作语言DML
  --DML语句 insert delete update merge 
  --作用操作行
数据定义语言DDL
   --DDL语句create drop alter truncate  rename comment
   --作用 操作数据库对象（表、视图等）
数据控制语言DCL
    --DCL语句  grant revoke（允许客户访问 、撤销客户访问）
    --作用 操作对数据和数据库对象的访问
事务控制语言TCL
    DCL语句 commit rollback savepoint
    --作用对事务进行控制

**/

--第二套
--1.多表查询
--2.子查询
--3.相关子查询
--第一部分
 --表连接概述
 --使用相等和不等连接
 --使用自连接
 --使用外连接
 --连接更多的表
 
 --1）相等连接
    --from table1 natural join table2
    --from table1 join table2 using(column)
    --from table1 join table2 on table1.col=table2.col
    
    select ename,deptno,dname from emp natural join dept; --等值自然连接
    select ename,deptno,dname from emp join dept using(deptno)
    select ename,emp.deptno,dname from emp join dept on emp.deptno=dept.deptno
    
    
    select * from salgrade; --工资等级
    
    select ename,sal,grade from 
    emp join salgrade on sal between losal and hisal;
   
   --自连接：将一张表作为两张表看待进行的连接
   --格式from table t1 join table t2 on t1.col=t2.col 
    
   select  w.ename,n.ename from emp w join emp n on w.mgr=n.empno ;
   
   select ename,d.deptno,dname from emp join dept d on emp.deptno=d.deptno;
    --子查询 :查询中嵌套查询
    select * from emp;
    select sal from emp where ename='SMITH1';
    select * from emp where sal>1100
    
    --多行子查询
    select * from emp where sal>(select avg(sal) from emp);
    
    select * from emp where sal>all(select avg(sal) from emp  group by deptno) ;--大于部门平均工资的
        select * from emp where sal>any(select avg(sal) from emp  group by deptno) ;--大于任意部门平均工资的
        
        
       --3
       --相关子查询：定义 在子查询中需要使用主查询中行的查询
       --相关子查询的执行流程
           1）首先从主查询中获取一行
           2）利用主查询中获取行执行子查询
           3）在执行一次主查询
           
           --普通相关子查询
          select ename,sal,deptno from emp e where 
          sal>(select avg(sal) from emp where deptno=e.deptno);
           
       --exiats   查询管理者
       select ename,sal,deptno from emp e where exists(select 1 from emp where mgr=e.empno )
       
    select * from emp for update;
    
    
    select avg(sal) from emp  group by deptno