oracle(day2).txt

讲了一些关于oracle的基本配置

一、单行函数
1.字符函数

   字符是大小写敏感的
   转小写 lower(字段名)      ---  其中的参数可以是一个字符串常量或是一个字段名
   转大写 upper(字段名)
   首字母大写 initcap(字段名)
   字符串拼接 concat(字段1, 字段2)
   截取子串 substr(字段名, 起始位置，取字符个数)
   dual表，是专门用于函数测试和运算的，他只有一条记录	
   字符串拼接 concat(...,....)
   求指定子串 substr(...,起始位置，取字符个数)
   可以使用"-"表示从右向左取，取的时候可以从左往友取。
    例：select substr(first_name,-2,2) sub from s_emp;（取后两个）
	      select substr(first_name,2,2) sub from s_emp;（取前两个）
	      
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，单位是天
    
   日期是格式敏感的
   求两个日期间相隔了多少个月 months_between(date1,date2)
   加减指定数量的月份 add_months(date,月数)，月数可以为负，负值就是减去相应的月数。
   从date日期开始的第一个星期五 next_day(date,FriDay)
   返回月末的日期 last_day(date)
   截取日期 trunc(date,'年或月或日或时分秒')
   例：select next_day(sysdate,2) from dual;
   例：select trunc(add_months(sysdate,1),'month') from dual;   
   ROUND('25-MAY-95','MONTH')		01-JUN-95
   ROUND('25-MAY-95 ','YEAR')		01-JAN-95
   TRUNC('25-MAY-95 ','MONTH')	01-MAY-95
   TRUNC('25-MAY-95 ','YEAR')		01-JAN-95
   
   练习：
   返回下个月的第一天的日期
   select round(last_day(sysdate),'MONTH') from dual;
   select add_months(trunc(sysdate,'MONTH'),1);
   
4，不同数据类型间转换函数

   将日期转成字符 tochar(date,'日期格式') 
   日期格式要用有效格式，格式大小写敏感 'yyyy mm dd hh24:mi:ss',
   'year'(全拼的年),'mm'(数字表示的月) 'month'(全拼的月)，'day'(星期的全拼)，'ddspth' (日期的全拼) 'yy mm dd'
   例：select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;

   将字符转换成数字 to_number('...')
   
   将数字转字符to_char(number，'fmt') fmt是数字格式

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

二、多表查询
表连接（关联查寻）

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

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

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

  注意：当为表起了别名，就不能再使用表名.字段名。

  例：select a.first_name,a.last_name,b.name 
      from s_emp a,s_dept b 
      where a.dept_id=b.id;     

2、非等值连接

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

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

  例：select e.ename, d.grade,e.sal 
      from emp e,salgrade d 
      where e.sal between d.losal and d.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 e.deptno,d.deptno
  from emp e,dept d
  where e.deptno(+)=d.deptno
  and e.deptno is null;
  
  标准sql中的外连接：
     select e.last_name,c.name
     from s_emp e right join s_customer c
     on e.id=c.sales_rep_id;
   注意:a)right join-->把join右边的表全部显示出来，另外一方                          没有用null来补齐
           left join--->把join左边的表全部显示出来
           join ------->没有left/right是内连接(都非空才连接起来，严格匹                  配两表的记录)
           full join---->满连接(把左右两边的都显示出来)
         b)join 一定放在from中
         c)条件用on
  
三、组函数

group 组 
group by 分组子句，按指定的分组规则分组 ，这个group by 子句可以跟在 select 语句后或是 having后面。
group by子句也会出发排序操作，会按分组字段排序。

select [组函数或分组的字段名] ，... from 表名 group by [字段名1],[字段名2],.....；

例：select avg(salary) from s_emp group by dept_id;

注意：组函数可以处理一组数据，返回一个值。
      组函数会忽略空值。

avg(..),求平均值，sum(..),求和 这两个函数的参数只能是number型的。

以下所提到的函数可以使用任意类型做参数。
count(..)，用来统计记录数，可以使用排重命令。count(...)默认使用的是all。
max(..),min(..)求最大值和最小值，
count(*),统计表中记录数。

例：select max(b.name),avg(a.salary), max(c.name) 
    from s_emp a,s_dept b,s_region c 
    where a.dept_id=b.id and b.region_id=c.id 
    group by b.dept_id;

注意：只要写了group by子句，
      ***   select后就只能用group by后的字段或者是组函数。  ***
      where子句只能够过滤记录，放单行函数。


having子句可以过滤组函数结果或是分组的信息，且写在group by子句后。

例:
 select max(b.name),avg(a.salary), max(c.name) 
 from s_emp a,s_dept b,s_region c 
 where a.dept_id=b.id and b.region_id=c.id 
 group by b.id 
 having sum(a.salary)>4000;

column 也可以定义有别名的列的格式。
column "别名" 格式定义

注意：要先过滤掉不需要的记录，然后再进行分组操作，提高效率。