*************************************
** oracle 学习笔记第二天 **
** author Ice Xu (XuBin) **
** date 2006-10-31 **
*************************************
where 条件一定是根据某个字段来进行过滤操作.
多表连接操作：
两表没有任何关联时会产生迪卡尔机：
select first_name , name from s_emp , s_dept;
等值连接：
练习一:查看员工的姓名和员工部门号:(要考虑到表中实际数据中空值的影响）
select first_name , name from s_emp, s_dept where s_emp.dept_id=s_dept.id;
练习二：每个员工所在的部门和部门所在的地区
select first_name , name from s_emp, s_dept, s_region where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id;
非等值连接：
练习三:找出每个员工和每个员工的工资级别
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal between b.losal and b.hisal;
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal>=b.losal and a.sal<=b.hisal;
自连接：
select first_name , manager_id from s_emp;
查出所有员工的部门领导的名称：( 这种sql会少一条记录，总经理没有被配置上）
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id;
外连接：(防止空值时，用（+）的一方会模拟一条记录配置另一方)这就称为外连接,一个记录都不能少；
select e.first_name , m.first_name from s_emp e , s_emp m where e.manager_id=m.id(+);
查看员工分部的部门：
select distinct(deptno) from emp ;
找出没有员工的部门：(很经典的一个例子,用外连接来解决的标准做法,这是一种方式）
第一步：
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno;
第二步：
select e.deptno , d.deptno from emp e , dept d where e.deptno(+)=d.deptno and e.deptno is null;
组函数（group function)：
group by 分组子句 对分组后的子句进行过滤还可以用having 条件 对分组后的条件进行过滤 where 是对记录进行过滤
avg(distinct | all )求平均值
count(distinct | all )统计
max(distinct | all ） 求最大值
min(distinct | all )求最小值
sum(distinct | all ) 求和
(所有组函数会忽略空值 , avg sum只能作用于数字类型）
求有提成员工的提成的平均值；
select avg(nvl(commission_pct ,0 ) ) from s_emp;
有多少人有提成：
select count( commission_pct ) from s_emp ;
count(*) 用于统计记录数：
select sum(commission_pct)/ count(*) from s_emp;
员工分部在多少个不同的部门：count 默认为作all的动作
select count(dept_id) from s_emp;
select count(distinct dept_id) from s_emp;
求各个部门的平均工资：group by 子句也会触发排序
select dept_id , avg(salary) aa from s_emp group by dept_id order by aa ;
select dept_id , avg(salary) aa from s_emp group by dept_id ;
注意：group by 子句后面跟有条件只能是查询的结果中的字段,所以我们会人为在结果要加入一些group by 要用的字段
select region_id , count(*) from s_dept 此句会有错
select max(region_id) , count(*) from s_dept; (强制语法上可以正确,但是不能保证结果也会正确）
求各个部门不同工种的平均工资：
select dept_id , title, avg(salary) from s_emp group by dept_id , title ;
哪些部门的平均工资比2000高:
select dept_id, avg(salary) aa from s_emp group by (dept_id) having avg(salary)>2000;
除了42部门以外的部门的平均工资:
select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id!=42;
select dept_id , avg(salary) from s_emp where dept_id!=42 group by (dept_id ) ;(此种sql效率要高，先过滤再计算）
where 单行函数。
having 组函数。
求各个部门的平均工资:
// 这样统计不详细
select max(d.name) , avg (s.salary) from s_emp s, s_dept d where s.dept_id=d.id group by d.name;
//****这问题很经典，为了过 oracle sql 语法关而写max(d.name) ***
select max(d.name) , avg(e.salary) , max(r.name) from s_emp e, s_dept d , s_region r where e.dept_id = d.id and d.region_id=r.id group by d.id ;
下午：
关于子查询: Subqueries
找出所有员工中，工资最低的那个员工：( 利用子查询 ）
select first_name, salary from s_emp where salary = ( select min(salary) from s_emp) ;
//这样写会出错姓名和工资不一致
select max(first_name), min(salary) from s_emp;
子查询运行的顺序： 先运行子查询再运行主查询 子查询一般出现在运算符的右边
单值运算符：运算后面只能跟一个值
多值运算符：可以对两个以上的值进行操作
查询谁跟Smith干一样的活：
select last_name from s_emp where last_name='Smith';
//下种写法可能还存在bug，没有考虑到数据的全面性,有潜在性问题
select last_name , title from s_emp where title =( select title from s_emp where last_name='Smith' ) and last_name <> 'Smith' ;
//这种写法才考虑的比较全面
select last_name , title from s_emp where title in ( select title from s_emp where last_name='Smith' ) and last_name <> 'Smith' ;
使用子查询时应注意： 单行子查询返回多个结果时会有错误 single-row subquery returns more than one value
查出哪些员工的工资比平均工资低：
select * from s_emp where salary < ( select avg(salary) from s_emp) ;
哪些部门的平均工资比32部门的平均工资要低：
第一步先查出各个部门的平均工资：
select min(avg(salary ) ) from s_emp group by dept_id;
第二步再查出哪个部门的工资是最低的:
select dept_id, avg(salary) from s_emp group by dept_id having avg(salary) = (select min(avg(salary) ) from s_emp group by dept_id ) ;
哪个部门里没有员工：
select deptno from dept where deptno not in ( select deptno from emp );
哪些人是普通员工：（用子查询形式来做）
select * from s_emp where id not in ( select manager_id from s_emp);
E--R图 实体关系图entity relation
开发流程先进行需求分析，进行系统设计，建表，再进行开发编码,测试最终产品上线试运行。
把软件设计模型转化为数据中的表，设计时要考虑性能的设计
第一范式：最简单的一种建方式，一张表只有一个主键。
第二范式：表的自连接存在原因，一张表，学生表中也有班级的信息。
第三范式：表连接存在的原因，两张表，其中一张表引用其它一张表。
约束：
为了保证数据的一致性，
primary key (pk) 主键约束 不允许有重复和空值(唯一且非空）
foregin key (fk) 外键约束 两张表parent table child table
unique key (uk) 唯一可以为空
not null
数据库设计时的注意：
索引： 为了提高效率而设计的一种与业务无关的
考虑表点用的物理空间：
考虑表之间的关系：
一对多关系： 利用FK+PK实现，多的一方引用外键
一对一关系： 可以利用FK+UK实现，
多对多关系： 通过中间增加一个附加表来实现，附加表利用联合主键来实现，联合起来的主键唯一。
DDL语句：数据库定义语句：
table (表）
view(示图）
sequence(序列号）
index(索引）
创建表语句：
create table [schema].表名 ( 字段名， 字段类型 约束条件); schema 默认就是当前用户,严格来访问表名完整的写法是schema.tablename
数据类型：
表名的命令规则： 首字母为字母，不得超过30个字符
char(size) 定长 不管是否达到最大宽度，都会点最大的宽度。
varchar2(size) 可变长 按实际的字节占用空间
number 所有的数字类型都称为number
number(n, m ) n------n位宽度 m-----小数点后的宽度
number(2,4)小数点后4 位，有效位2位 values(0.0099) 这样可以 values(0.01)这样出错
LONG 2GB 大文本一个表最我只允许定义一个LONG类型(不建议使用）
CLOB 大对象形式存放(在表里只存一个指针)
BLOB 存二进制大对象(声音，图像之类）
default 作用演示：
create table test(c1 number default 10, c2 number);
约束的演示：
主键约束的定义：
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) ) ; 用表级约束可以实现联合主键
外键约束的定义：(先定义父表，再定义子表）
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
on delete cascade (及联删除，删除父表时子表也跟着删除）
on delete set null (及联删除父表时子表中引用的字段为null）