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语句。

 

replace：

update tableName set valueName=replace(valueName,'oldStr','newStr')