Oracle学习笔记（二）
7、表链接的应用
????????????
//求部门平均薪水的等级select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal); //求部门平均的薪水等级select deptno,avg(grade) from (select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t group by deptno;//雇员中有哪些人是经理人select ename from emp where empno in (select distinict mgr from emp);//不用组函数求薪水的最高值select disinict sal from emp where sal not in (select distinict e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal)) //平均薪水最高的部门的部门编号select deptno ,avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal=(select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno)) //平均薪水最高的部门的部门名称select dname from dept where deptno= (select deptno from (select avg(sal) avg_sal,deptno from emp group by deptno) where avg_sal= (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) ) ) //求普通员工的最高薪水还要高的经理人名称 select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal> (select max(sal) from emp empno not in (select distinct mgr from emp where mgr is not null ) )
?
六、Oracle中的分页
??????
# 薪水最高的第六个人到第十个人select ename,sal from ( select enmae,sal,rownum r from ( select ename,sal from emp order by sal desc; ) ) where r>=6 and r<=10;
?
七、其他
??7.1创建新用户
??????????1、backup scott
?????????????? 进入到一个目录下(cd temp)，将目录里的文件全部删掉(del *.*),
???????????????然后敲exp,输入用户名和密码? scott/tiger? 回车 回车 U yes yes yes?? 成功导出
????????????????在temp里有一个expdat.dmp????????????????
??????????2、create user
?????????????? create user liuchao identified by liuchao default tablespace users quota 10M on users;
?????????????? (users表空间上分配10兆空间)
???????????????grant create session,create table,create view to liuchao???
??????????3、import the data
????????????????????? imp 回车 liuchao/liuchao?? scott(只导入scott用户的东西)
??????????4、验证：
?????????????????????? conn liuchao/liuchao
? 7.2DML语句
???????????????DML语句就是insert delete update语句，实例略。
? 7.3一些术语
??????????? 1、主键：可以唯一标识整条记录的，(非空唯一),逻辑意义代表不同的记录
????????????2、外键：一张表的两个字段，两张表的两个字段上,被参考的字段必须是主键
??????????? 3、事务：一系列的操作，要么同时完成，要么都不完成，碰到rollback,(commit，DDL语句，正常断开连接)
??????????????????????? ?事务结束
????????????4、索引：相当字典里的索引，读起来更快。索引的用途是访问量特别大，因此不要轻易的建立索引（耗费
???????????????????????? 的空间大）
??????????? 5、视图：视图就是子查询,视图的优点是简化查询，保护私有数据。缺点是表结构改了，视图得跟着改，
???????????????????????? ?增加了维护的支出。
??????????? 6、三范式：数据库设计的规则
?????????????????? (1)不存在冗余数据 主键? , 设计任何表都要有主键，（要有主键，列不可分，不能重复）
?????????????????? (2)多对多设计时所要遵循的东西，当一张表有多个字段作为主键的时候，(非主键的不能依赖于
????????????????????????部分主键，不能存在部分依赖。)??????????????????
?????????????????? (3)存在传递依赖?。
??????? ??????????????
? 7.4DDL语句
?????????????? DDL语句就是create、alert语句。
????????????????
#create语句create table stu ( id number(6), name varchar2(20) constraint stu_namenn not null, sex number(1), age number(3), sdate date, grade number(2) default 1 class number(4) references class(id), email varchar2(50), constraint stu_class_fk foreign key (class) references class(id) constraint stu_id_pk primary key (id), constraint stu_name_email_uni unique(email,name) ); #alert语句alter table stu add(addr varchar2(100));alter table stu drop (addr);alter table stu add(addr varchar2(100)) ;alter table stu modify (addr varchar2(50));alter table stu drop constraint stu_class_fk;//一般不需要修改alter table stu add constraint stu_class_fk foreign key (class) references class(id);
?
?
?
