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);