DQL:查询数据

DQL:查询数据

基本语法结构:select 字段名,… from 表名;
1. 如果需要查询表中的所有的记录使用 *

     select * from 表名;

1. 投射(投影)查询:查询表中的某些字段.
   语法: select 字段名,.. from 表名;

   # 查询员工表中所有员工的姓名
   select ename from emp;
   # 查询员工表中所有员工的姓名和工资
   select ename,sal from emp;
   # 查询员工姓名以及员工的月收入(sal+comm)
   select ename,sal+comm from emp; --存在问题 

空值处理

特点:oracle数据库中,没有数据表示空,如果空参与计算结果为空.oracle数据库中任何类型都可以为空.

• nvl(exp1,exp2)
  作用:如果exp1不为空,返回exp1的结果,如果exp1为空,那么返回exp2的结果.

   # 查询员工姓名以及员工的月收入(sal+comm)
   select ename,sal+nvl(comm,0) from emp; 
   -- 为查询的结果定义别名
   select ename,sal+nvl(comm,0) salary from emp;
   -- 使用as关键字
   select ename,sal+nvl(comm,0) as salary_total from emp;
   -- 别名中存在特殊符号
   select ename,sal+nvl(comm,0) "salary total" from emp;

• dual:虚表(伪表),oracle提供一个用于测试函数或者过程的表.

   -- 测试nvl2的功能
   select nvl2(null,1,2) from dual;

• nvl2(exp1,exp2,exp3)
  如果exp1不为null返回exp2的结果,如果exp1为null,那么返回exp3;

   -- 如果该员工有提成,那么工资涨幅0.2,没有提成工资不变,存在comm为0
   select ename,nvl2(comm,sal+sal*0.2,sal) salary from emp;

• nullif(expr1,expr2)
  如果两个表达式相等,那么返回null,否则返回exp1的结果

   select nullif(1,1) from dual;
   -- 如果该员工有提成,那么工资涨幅0.2,没有提成工资不变,存在comm为0
   select ename,nvl2(nullif(comm,0),sal+sal*0.2,sal) salary from emp;

• COALESCE(expr1,…,exprn)
  返回第一个非空的表达式的值

   -- 发奖金,如果工资为null并且提成null发200,如果工资不为空直接发工资,如果提成不为空那么发提成.
   select coalesce(sal,comm,200)  from emp;

• case 表达式:类似于java中的switch语句

  case expr1
    when value1 then 执行语句
    ...
    else 执行语句
  end --表示结束

  -- 如果职位是SALESMAN,工资提高20%,MANAGER工资提高10%,其他提高5%
  select ename,sal old_sal,
   case job
      when 'SALESMAN' then sal*1.2
      when 'MANAGER'  then sal*1.1
      else sal*1.05
   end new_sal 
   from emp;

   -- 创建成绩表
   create table t_grade(
       id number primary key,
       class varchar2(20),
       score number
   );
   -- 准备数据
   insert into t_grade values(1001,'语文',80);
   insert into t_grade values(1002,'java',70);
   insert into t_grade values(1003,'C++',60);

• SIGN(n)
  如果n>0返回1,如果n<0返回-1,如果n=0返回0

  select sign(-1) from dual;
  --获得成绩表中的等级
  select class,
  case sign(score-80)
   when 1 then '优秀' 
   when 0 then '优秀' 
   when -1 then '良好' 
  end lel 
  from t_grade;

  select class,
  case sign(score-80)
   when 1 then '优秀' 
   when 0 then '优秀' 
   when -1 then 
      case sign(score-70)
         when 1 then '良好'
         when 0 then '良好' 
         else '不及格'
      end 
  end lel 
  from t_grade;

• decode(expr1,match1,reustl1,…,default);
  说明:如果expr1的值和match1匹配,返回result1的结果,如果都不匹配返回defualt的值.

   ---- 如果职位是SALESMAN,工资提高20%,MANAGER工资提高10%,其他提高5%
   select ename,sal old_sal,
    decode(job,'SALESMAN',sal*1.2,'MANAGER',sal*1.1,sal*1.05) new_sal
   from emp;
   --获得成绩表中的等级
   select class,
   decode(sign(score-80),
         0,'优秀',
         1,'优秀',
         -1,decode(sign(score-70),0,'良好',1,'良好',
          -1,decode(sign(score-60),-1,'不及格','及格'))) lel
   from t_grade;

字符串函数

1. lower(expr):将表达式的结果转成小写
2. upper(expr):将表达式的结果转成大写.
3. length(str):获得字符串中字符的个数
4. lpad(expr1,n,expr2):将expr2的结果填充在expr1的左边得到长度为n的字符串
5. rpad(expr1,n,expr2):将expr2的结果填充在expr1的右边得到长度为n的字符串
6. substr(str,begin,length):从begin开始截取length个数据,begin从1,开始,如果为负数,从右向左执行.
7. concat(str1,str2):将两个字符串进行拼接,oralce中提供了||作为拼接符
8. trim(math FORM source) 从source中去掉前后的math

  --查询员工姓名
  select lower(ename) low,upper(ename) up,length(ename) len,
  lpad(sal,10,'#') sal
   from emp;

   --- 获得结果为 scott salary is 3000,but he want 5000
   select concat(ename,concat(' salary is ', sal)) info from emp;
   select ename ||' salary is '|| sal  info from emp;

   select trim(' abcd efg ') from dual;--去掉前后的空白
   select trim('a' from 'aaa0123a456aa') from dual;

数字相关的函数

1. round(n,p):四舍五入,n表示需要处理的数据,p表示小数点的位数.
2. floor(n):向下取整
3. ceil(n):向上取整

  -- 12345/ 12345.12/ 12300
  select round(12345.12345,0),round(12345.12345,2),round(12345.12345,-2) from dual;
  select round(-11.5) from dual;

转换函数

1. to_char(date,fmt):将日期转成符合格式的字符串
   
   • yyyy:表示年份
   • mm:表示月份
   • dd:表示几号
   • HH24:24小时制
   • mi:表示分钟
   • ss:表示秒钟

   --2017/07/21
   select sysdate from dual;
   -- 2017-07-21 15:52:35
   select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
   -- 2017-07-21 15:55:38 星期五
   select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss DAY') from dual;
   --2017年07月21日
   select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual;

   select to_char(123456,'L99,999') from dual;
   select to_char(123456,'L0000,000') from dual;

1. to_date(str,fmt):将字符串转成对应的日期格式
2. to_number(str,fmt):将字符串转成对应的数字

  create table t_temp(
     dotime date,
     totla number
  );
  -- 如果提供的字符串符合数字,可以默认的进行转换
  insert into t_temp(totla) values('1001');
  select to_number('￥1234','L99999') from dual;
  insert into t_temp(totla) values(to_number('￥1234','L99999'));

 -- 如果提供的字符串符合默认的日期格式,可以进行默认的转换
  insert into t_temp(dotime) values('21-8月 -17');
  insert into t_temp(dotime) values(to_date('2017/08/21','yyyy/mm/dd'));