oracle day02

1、where子句（选择操作）


where子句使用在 select ... from ... 后面，用来选择所需（符合条件的）的记录


例：查询George的工资
   select first_name,salary from s_emp where first_name='George';



where后面跟的是表达式 也就是 XXX=XXX， XXX between X and X  ，XXX in（X，X，X）
like '...' 通配查询


between ... and ... ,表示结果在这之间，between and是一个闭区间，也就相当于... <= ... and ... >= ... 。


!=，<>，^=，这三个都标识不等于，<=，>=，=，这些运算符都可以使用。


... in (va1,val2,...) 判断结果是否在这个集合中存在，等于其中随变一个就行了，可以说in=any
in中集合的数据是有顺序的，概率高的数据放在前面可提高效率

not in<>all 没有顺序
空值会对not in造成影响，也就是不等于任何值，但是空值例外。


like '...' 字符串通配查询，'%'表示0或任意多个字符，'_'，表示任意一个字符。
注意：转义的用法：like ‘S\_%’ escape ‘\’

例：找出表名是以s_开头的表
select table_name from user_tables where table_name like 'S\_%' escape '\';  把\定义为转义字符


... is null 使用来判断值是否为空。is not null 判断值不为空
例：查看哪些人没有提成
   select first_name from s_emp where commission_pct is null;




     ... and ... 表示只有两个条件同时满足
... or ... 表示条件只要满足其中只一就可以
all ... 是要求都满足条件。




注意：Oracle中的字符串是严格区分大小写的。 


（1）注意数据类型，数字类型直接写，字符用'......' ，缺省格式的Date可以用'......'，只有别名才用" "包含。
（2）选择合适的运算符   




2、单行函数（一个输入，一个输出） 
       多行函数又叫组函数（多个输入，一个输出）例平均值


1.字符函数


   字符是大小写敏感的
   转小写 lower(字段名)      ---  其中的参数可以是一个字符串常量或是一个字段名
   转大写 upper(字段名)
   首字母大写 initcap(字段名)
   字符串拼接 concat(字段1, 字段2)
   截取子串 substr(字段名, 起始位置，取字符个数)
返回字符串长度length(字符串)


   dual表，是专门用于函数测试和运算的，他只有一条记录


   可以使用"-"表示从右向左取，取的时候可以从左往右取。
     例：select substr(first_name,-2,2) sub from s_emp;（取后两个）
     select substr(first_name,2,2) sub from s_emp;（取前两个）


nvl是一般函数，支持任何数据类型
     
2.数值函数


   四舍五入 round(数据,保留小数点后几位)
   可以用负数表示小数点前，0，表示小数点后第一位，也就是保留个位，-1表示个位（保留到十位）。
   例：select round(15.36,1) from dual;


   截取数字函数 trunc(数据，保留的位数（小数点后位数）) 截取个位之后补0
   例：select trunc(123.456,1) from dual;  
   
3.日期函数
  
   日期格式，
   全日期格式 世纪信息，年月日，时分秒。
   缺省日期格式，日-月-年 dd-mon-rr
   修改当前会话的日期格式，会按照指定的格式输出日期
   alter session set nls_date_format='yyyy mm dd hh24:mi:ss';


   返回当前日期 sysdate
   例：select sysdate from dual;
       select sysdate+1 from dual;  获得明天的日期，加1，单位是天
    
例: 10分钟以后的时间  select sysdate,sysdate+1/144 from dual;
   
   日期是格式敏感的
   求两个日期间相隔了多少个月 months_between(date1,date2)
   加减指定数量的月份 add_months(date,月数)，月数可以为负，负值就是减去相应的月数。
   从date日期开始的第一个星期五 next_day(date,FriDay)
   返回月末的日期 last_day(date)
round 不能使用缺省的日期格式
例：select round(to_date('02-JAN-07','DD-MM-RR'),'MONTH') from dual;


   截取日期 trunc(date,'年或月或日或时分秒')
   例：select next_day(sysdate,2) from dual;
   例：select trunc(add_months(sysdate,1),'month') from dual;   
   
   练习：
   返回下个月的第一天的日期
   select round(last_day(sysdate),'MONTH') from dual;
   select add_months(trunc(sysdate,'MONTH'),1) from dual;
select trunc(add_months(sysdate,1),'MONTH') from dual;
  
4.不同数据类型间转换函数


   将日期转成字符 tochar(date,'日期格式') 
   日期格式要用有效格式，格式大小写敏感 'yyyy mm dd hh24:mi:ss'（完整的日期可格式）
   year'(全拼的年),'mm'(2位数字表示的月) 'month'(全拼的月)，'dy'(3位字母表示的星期) ，'day'(星期的全拼)，'ddspth' (日期的序数词) 


   例：显示完整的系统日期
   select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;


  查询3号入职的员工
  select first_name,start_date from s_emp where to_char(start_date,'dd')='03';
  select first_name,start_date from s_emp where to_char(start_date,'fmdd')='3';     'fm'是去掉前导0


  向表中插入日期(test是表名)
  insert into test values('01-JAN-07');
  insert into test values(to_date('2007 01 01 10:10:10','yyyy mm dd hh24:mi:ss'));


   将字符转换成数字 to_number('...') ,缺省是按十进制来算，字符串只能是0---9的数字
select to_number('ab','xx') from dual; 把ab转成十六进制
   
   将数字转字符to_char(number，'fmt') fmt是数字格式
select to_char(salary,'$99,999.99') from s_emp;
select to_char(salary,'$00,000.00') from s_emp;    ##########表示越界，大于显示宽度
select to_char(salary,'L00,000.00') from s_emp;


   将字符串转成日期 to_date('...','日期格式')
   例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；




3、多表查询


表连接（关联查寻）


如果多表查询时不加where子句，也就是过滤条件或者是使用了无效的条件，就会产生两表之间记录的相互逐条匹配（组合），产生很多无效的结果（笛卡尔积）。


注意：在使用表连接时，要注意查询的表间的关系信息，表之间的字段所表示的信息的关系


1、等值连接
 
   select [表别名1.字段名1]，[表别名2.字段名2],... 
   from 表1 表别名1 ，表2 表别名2
   where 表别名1.字段名3=表别名2.字段名4;
   表连接时，当表与表之间有同名字段时，可以加上表名或表的别名，加以区分，使用时要用
   表名.字段名或表别名.字段名（列名）。当表的字段名是唯一时，可以不用加上表名或表的别名。


   注意：当为表起了别名，就不能再使用表名.字段名。
  
例：列出所有Sales部门的员工
   select e.first_name,d.name
   from s_emp e,s_dept d
   where e.dept_id=d.id and d.name='Sales';


  列出每个员工的名称，他所在的部门名称，及地区名称
   select e.first_name,d.name,r.name
   from s_emp e,s_dept d,s_region r
   where e.dept_id=d.id and d.region_id=r.id;

 执行脚本
cd $ORACLE_HOME
cd sqlplus
cd demo
sqlplus sd0703/sd0703 @demobld.sql


2、非等值连接


   select [表别名1.字段名1]，[表别名2.字段名2],... 
   from 表1 表别名1 ，表2 表别名2
   where 表别名1.字段名3 ..... 表别名2.字段名4


   ....可以使比较运算符，也可以使其他的除了'='的运算符


   例：列出每个员工的工资和他的工资级别
   select e.ename,e.sal,s.grade 
          from emp e,salgrade s 
          where e.sal between s.losal and s.hisal;      


3、自连接 


   用别名把一张表中的数据分成两部分，然后在使用条件过滤。
   select [表别名1.字段名1]，[表别名2.字段名2],... 
   from 表1 表别名1 ，表1 表别名2
   where 表别名1.字段名3=表别名2.字段名4;


   例：列出每个员工的经理名
   select a.first_name ename,b.first_name cname 
          from s_emp a,s_emp b 
          where a.manager_id=b.id;


   以上所提到的表连接，都叫做内连接，严格匹配两表的记录。          
  
4、外连接（先做连接再做过滤） 


   会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，数据库会模拟出记录去和那些不匹配的记录匹配。


   例：select a.first_name enamei,a.id,b.first_name cname,b.id 
            from s_emp a,s_emp b 
            where a.manager_id=b.id(+);
          即用a表中的数据去匹配b表的，若b表中有null，系统模拟纪录与其匹配


   注意：要把那一方的记录全部都显示出来，还有注意条件(+)跟在要全部选出的对端。               
  
   外连接的应用：


员工都分布在哪些部门
select distinct deptno from emp;


   列出哪个部门没有员工
   select e.deptno,d.deptno
   from emp e,dept d
   where e.deptno(+)=d.deptno
   and e.deptno is null;