oracle学习笔记(三)

组函数

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

例：求所有人的提成的平均值
   select avg(nvl(commission_pct,0)) from s_emp;
    求有多少人有提成
   select count(commission_pct) from s_emp;
    求员工都分布在多少个不同的部门
   select count(distinct(dept_id)) from s_emp;

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

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

count(*),统计表中记录数，不忽略空值。

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

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

例：找出各个部门的平均工资
    select dept_id,avg(salary)
    from s_emp;
    group by dept_id;

   在没有group by时，select后不能把普通字段和组函数同时使用

   求各个部门不同职位有多少人
   select dept_id,title,count(*)
   from s_emp
   group by dept_id,title;

   求平均工资大于2000的部门
   select dept_id,avg(salary)
   from s_emp
   group by dept_id
   having avg(salary)>2000;

   求除了42部门以外的各个部门的平均工资
   select dept_id,avg(salary)
   from s_emp
   where dept_id<>42
   group by dept_id;

  求各个部门的平均工资
  select max(d.name),max(r.name),avg(salary)
  from s_emp e,s_dept d,s_region r
  where e.dept_id=d.id and d.region_id=r.id
  group by dept_id;
 
注意：只要写了group by子句，select后就只能用group by后的字段或者是组函数。
      where子句只能够过滤记录，放单行函数。

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

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


四、子查询

子查询，就是可以嵌在任何的sql语句中的select语句,子查询也会触发排序

在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边。

注意：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值，in）。
配合使用子查询返回的结果必须符合运算符的用法。

例:
   求谁的工资是最低的
   select first_name,salary
   from s_emp
   where salary =(select min(salary)
                  from s_emp
                 );

  求谁和Smith是做一样工作的
  select first_name,title
  from s_emp
  where title in (select title
  from s_emp
                  where last_name='Smith')
 
  求哪些人是普通员工
   select first_name from s_emp
   where id not in (select manager_id from s_emp
                    where manager_id is not null)

  求哪些人是经理
  select first_name from s_emp
  where id in (select manager_id from s_emp)

from后面也可以用子查询

例：找出哪个人的平均工资比本部门的平均工资高
  select e.first_name,e.salary,a.avgsal
  from s_emp e,(select dept_id,avg(salary) avgsal from s_emp group by dept_id) a
  where e.dept_id=a.dept_id and e.salary>a.avgsal;

重点掌握的三种形式：

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

2、求各部门的平均工资
  select max(d.name),max(r.name),avg(salary)
  from s_emp e,s_dept d,s_region r
  where e.dept_id=d.id and d.region_id=r.id
  group by dept_id;

3、找出哪个人的平均工资比本部门的平均工资高
  select e.first_name,e.salary,a.avgsal
  from s_emp e,(select dept_id,avg(salary) avgsal from s_emp group by dept_id) a
  where e.dept_id=a.dept_id and e.salary>a.avgsal;


将业务需求转换成可操作的表

一: 需求分析
二: 画E-R图（E entity 实体, R relation 关系）
三: 转换成表关系
四: 割接（新老系统交接）
五: 生产,试运行

E-R工具：Erwin

E-R图属性:
* 为强制的非空属性
o 可选属性(可以有值也可以没有)
#* 表示此属性唯一且非空 
# 表示唯一的

实体关系:
mastbean maybean

数量关系:   一对一关系  
    一对多关系  
    多对一关系   
    多对多关系

第一范式，所有的属性都必须是单值，也就是属性只表示单一的意义。（记录可以重复，没有任何限制）
第二范式，属性要求唯一且非空，（记录不可重复，但是数据可能会出现冗余）。
第三范式，非主属性只能依赖于主属性，不能依赖于其他非主属性。（解决数据冗余问题）


六、约束

约束是针对表中的字段进行定义的。

primary key （主键约束 PK）保证实体的完整性，保证记录的唯一
主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，只有两个字段放在一起唯一标识记录，叫做联合主键。

foreign key （外建约束 FK）保证引用的完整性，
外键约束，外键的取值是受另外一张表中的主键或唯一值得约束，不能够取其他值，只能够引用主键会唯一键的值，被引用的表，叫做parent table（父表），引用方的表叫做child table（子表），要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。

unuque key（唯一键，可以为空 UK），值为唯一

index（索引）是数据库特有的一类对象，view（视图）

典型的一对多 class 对应多个学生。

student table                      class table
______________________________     _________________________
| id | name | address| class_id|   | id |class_desc|class_num|
|(PK)|______|________|___(FK)__|   |(pk)|__________|_________|
|    |      |        |         |   |    |          |         |


一对一

student tabel             shenfenzheng table
____________________     _________________________________
| id | name | address|   |  s_id  |shenfen_desc|shenfen_num|
|(PK)|______|________|   |(PK，FK)|____________|___________|
|    |      |        |   |        |            |           |

多对多

student tabel             zhongjian table                      kecheng table
____________________     _________________________________    __________________
| id | name | address|   |  s_id  |shenfen_desc|shenfen_num|  | kid | kechengname|
|(PK)|______|________|   |(FK，FK)|____________|___________|  | (PK)|____________|
|    |      |        |   |联合主键|            |           |  |     |            |

引用对方表的主键,当作本身的主键,所以这个表的主键,既是主键又是外建


建表和其他相关操作

DDL语句

创建表：
   create    table  表名   (    字段名1    类型(数据长度)(default ...)   约束条件，   字段名2    类型(数据长度)    约束条件 );

Oracle数据库中的数据类型

varchar(长度)，可变长字符串，按照实际长度存储   char(长度) 定长字符串存储
number(..,..),number 表示浮点数，或者是整数

LOB（large object) 大对象   不能直接用insert插入  
CLOB 字符的大对象，存大文本
BLOB 二进制的大对象，也是以相当于指针的形式存放的。

data block数据块

create table test(c1 number default 1,c2 number);  没有插入的时候会用默认值

primary key约束：
主键约束的定义：
第一种定义形式：
create table   test(c  number  primary key  );     列级约束
第二种定义形式：
create table  test(c  number , primary key(c) )  ; 表级约束
create table   test( c1  number  constraints   pkc1  primary key );   此约束有名字:  pkc1
create table   test(c number , c2  number ,  primary key (c ,c1) )  ; 用表级约束可以实现联合主键

处理错误
（1）!oerr ora [错误号] ，系统可以显示错误的原因和如何修改。如果命令错误输入可以使用edit或ed来修改输入错误。
     实际上是在编辑缓存文件中的最后一条sql语句。
     也可以使用 (change) c /错误字段/正确字段，来进行替换操作进行修改。
     只有在Linux平台使用
     ! 相当于 host ，没有断连接，只是切换了一下，执行shell命令
（2）edit命令来编辑最后一条sql语句。