oracle学习笔记(二)

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;

评论

1 楼 quningstudy 2008-12-17  

很好，谢谢