Oracle数据库的一些操作

如何备份Oracle数据库：

MD F:/DB_BAK/%date:~0,10%
EXP userid=bszlhr/bszlhr@ORCL_14 file=F:/DB_BAK/%date:~0,10%/bszlhr%date:~0,10%.dmp log=f:/DB_BAK/%date:~0,10%/log_bszlhr_%date:~0,10%.log
  buffer=64000 owner=bszlhr

 

-----创建信用信息的表----------

create table GCCredit(
 itemID number(10,0),
 EmpID number(10,0),
 CreditCots varchar2(100),
 Notes varchar(100)
)

select * from GCCredit

GCV_Credit

-------创建信用信息的视图------
 CREATE OR REPLACE VIEW GCV_Credit (
  filepath,
   unitid,
   deptno,
   empno,
   empname,
   position,
   itemid,
   empid,
   creditcots,
   notes
  )
AS
Select  emp.FilePath ,emp.UnitID, emp.DeptNo, emp.EmpNo,      emp.EmpName,emp.position,
 c.itemid,c.empid,c.creditcots,c.notes
From GCCredit c, GCEmployee emp
WHERE emp.EmpID = c.EmpID

select * from GCV_Credit

----1.用来查询所需要的应用程序------------------
select * from gc_appobjects where obj_class like '%Credit%';
-----2.用来查询所需要的数据对象-----------------
select * from gc_dbobjects where obj_class like '%Credit%';

------3.用来查询数据对象中所包含的字段--------
select * from gc_columns where obj_id in (
select obj_id from gc_dbobjects where obj_class like '%Credit%');

 

 

 

 

 

 

 

1.       在oracle中如何获取前10条的记录：

select   *   from   tablename   where   rownum<11   order   by   colname   desc

2.       在oracle中的多表连接查询，

 

 

Oracle多表连接与子查询

Posted on 2009-07-02 13:17 仲宏伟（anmo） 阅读(456) 评论(0)  编辑  收藏

1：等值连接
     迪卡尔集连接
                  select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部门
                  from emp a, dept b
 
     等值连接
                  select ename, a.deptno as a_deptno,b.deptno as b_deptno ,b.dname as 部门
                  from emp a, dept b
                  where a.deptno = b.deptno;

2：非等值连接，在emp表和salgrade表中查找员工的姓名，工资，等级，工资上线，工资下线
                 select ename as 姓名, sal as 工资, grade as 工资等级,losal as 工资上线，hisal as 工资下线
                 from emp, salgrade
                 where sal between losal and hisal;

3:外连
       右外连
                 select e.ename, d.dname, e.deptno
                 from emp e,dept d
                 where e.deptno = d.deptno(+);

        左外连与右外连相反

     以hr登陆查询表employees 和departments 表
                 select  first_name as 姓名, department_name as 部门名称,  d.department_id as 部门编号 
                 from employees e, departments d
                 where e.department_id= d.department_id(+) ;

4:自连：在同一个表中查询每个员工及上司的工号和姓名
                 select a.empno  as 员工编号, a.ename as 员工姓名, a.mgr as 上司的员工编号, b.ename as 上司姓名
                  from emp a, emp b
                 where a.mgr = b.empno;

 

5：sql99
         交叉连接 cross join------相当于迪卡尔集
                  select e.ename,d.dname
                  from emp e cross join dept d;

         自然连接 natural join------相当等值连接
                  select e.ename, d.dname
                  from emp e natural join dept d;

         using子句-----使用同名列查询
              select e.ename, d.dname
              from emp e join dept d
              using (deptno);

         on子句 ------当列名不同时用on子句
              用on查询两张表
                   select e.ename, d.dname
                    from emp e join dept d
                   on e.deptno = d.deptno;

      用on查询多张表
               select e.ename, d.dname
               from emp e join dept d
               on e.deptno = d.deptno
               join 第三个表
               on 列1 = 列2;

     内连接（Inner join）-------内连接只返回满足连接条件的数据
              select employee_id, last_name, salary, department_id, department_name
              from employees inner join departments using(department_id);

     左外连
              select employee_id, last_name, salary, department_id, department_name
              from employees left join departments using(department_id);
 
     右外连
              select employee_id, last_name, salary, department_id, department_name
              from employees right join departments using(department_id);

     满外连
              select employee_id, last_name, salary, department_id, department_name
              from employees full outer join departments using(department_id);

    
    子查询------------
             查找出工资比scott高的人
              select ename, sal from emp
              where sal>
              (select sal from emp where ename='SCOTT');
 
     查找那些人和scott相同职位的人
              select ename, job from emp
              where job=
               (select job from emp where ename='SCOTT')  
               and ename <> 'SCOTT';

     any的用法  < any意味着小于最大、> any大于最小
              select empno, ename, sal, job
              from emp 
              where sal<any(select sal from emp where job='SALESMAN');

     all的用法 < all：小于所有，即小于最小、> all：大于所有，即大于最大
              select empno, ename, sal, job
              from emp
              where sal<all(select sal from emp where job='SALESMAN');