db oracle scripts

Oracle DOS打开sqlplus
C:\Documents and Settings\Lindows>sqlplus "scott/tiger@orcl"

Oracle SQL文件数据导入

SQL> @'F:\s*****\call service\database\goldapp@gold_192.168.200.72\goldapp@gold_08_08_05.sql'

Oracle DMP文件数据导入
SQL>imp scott/tiger@orcl file=D:\test.dmp

Oracle 日期用法to_date

select * from member m where m.createtime >= to_date('2008-04-01','yyyy-mm-dd');

Oracle joins

http://hi.baidu.com/maogbl/blog/item/9b9d31da34d21360d0164ee8.html

SQL中 inner join、 left join 、right join、 outer join之间的区别

一、inner join
A表(a1,b1,c1) B表(a2,b2)
a1 b1 c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四
03 英语 80 04 王五
select A.*,B.* from A
inner join B on(A.a1=B.a2)
结果是：
a1 b1      c1 a2 b2
01 数学 95 01 张三
02 语文 90 02 李四

二、left outer join
select A.*,B.* from A
left outer join B on(A.a1=B.a2)
结果是:
a1 b1     c1 a2     b2
01 数学 95 01     张三
02 语文 90 02     李四
03 英语 80 NULL NULL

三、right outer join
select A.*,B.* from A
right outer join B on(A.a1=B.a2)
结果是:
a1       b1      c1      a2 b2
01      数学 95    01 张三
02      语文 90      02 李四
NULL NULL NULL 04 王五

四、full outer join
select A.*,B.* from A
full outer join B on(A.a1=B.a2)
结果是:
a1 b1 c1 a2 b2
01      数学 95       01      张三
02      语文 90      02      李四
03      英语 80     NULL NULL
NULL NULL NULL 04      王五

Oracle STATIC SQL vs DYNAMIC SQL

 

http://marshaljlc.spaces.live.com/blog/cns!C1860F2B87B2B191!145.entry?wa=wsignin1.0

今天在AskTom看到一遍文章，关于绑定变量的问题，其实也就是静态sql语句跟动态sql语句的执行效率问题.

SQL>create table t (x int);

Table Created.

SQL>set timing on

SQL>alter session set sql_trace=true;

Session altered.

Elapsed:00:00:00.01

SQL>begin

2      for i in 1..1000

3      loop

4          insert /*STATIC SQL*/ into t values(i);

5      end loop;

6      commit;

7 end;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.04

SQL>begin

2      for i in 1..1000

3      loop

4         execute immediate 'insert /*DYNAMIC SQL*/ into t values(:i)'

5         using i;

6      end loop;

7    commit;

8 end;

9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.03

现在用TKPROF看执行计划log

 

INSERT /*static sql*/ into d values (:b1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.09          0          0          0           0
Execute   1000      0.18       0.17          0          4       1064        1000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1001      0.18       0.26          0          4       1064        1000

 

insert /*dynamic sql*/ into d values (:i)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     1000      0.06       0.06          0          0          0           0
Execute   1000      0.23       0.21          0       1003       1061        1000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2000      0.29       0.28          0       1003       1061        1000

从Parse和Execute来看，对比是明显的。显然都使用了绑定变量但静态sql效率快很多.

Oracle rownum 分页语句

http://kop.iteye.com/blog/253440 

(1)Oracle分页查询： 
SELECT * FROM 
( 
  SELECT A.*,ROWNUM RN 
    FROM (SELECT * FROM TableName) A 
    WHERE ROWNUM<=40 
) 
WHERE RN>=20 
---------------------------------------------------------- 
    SELECT * FROM (SELECT rownum r,* from table_name) A 
    WHERE A.r>m AND A.r<=n 
    结果返回的是第m+1行到第n行的数据集。比如: 
    SELECT * FROM (SELECT rownum r,* from table_name) A 
    WHERE A.r>5 AND A.r<=10　　 
    的意思就是返回包含第6行到第10行的数据结果集。 

 

http://blog.163.com/xiaosanshaoli@126/blog/static/29792163200852510210538/  

1. 标准的rownum分页查询使用方法：
    select *
      from (select c.*, rownum rn from content c)
     where rn >= 1 and rn <= 5

2.  排序的分页

 select *
      from (select rownum rn, t.*
              from (select title, addtime from content order by addtime desc/asc) t)
     where rn >= 1 and rn <= 5

总结：

rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2

，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。

举例：

例如表：student(学生)表，表结构为：
ID　　　     char(6)　　　　　       --学号
name　　 VARCHAR2(10)　　　--姓名
create table student (ID char(6), name VARCHAR2(100));
insert into sale values(''200001'',‘张一’);
insert into sale values(''200002'',‘王二’);
insert into sale values(''200003'',‘李三’);
insert into sale values(''200004'',‘赵四’);
commit;
1.等值查询
如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生

的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判

断是时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。
select rownum,id,name from student where rownum=1;
2.大于某值的查询条件
如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从

1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立（不能对rownum用">"）。可以

使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因

为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。
select * from(select rownum no ,id,name from student) where no>2;
3.小于某值的查询条件
select rownum,id,name from student where rownum <3    （取出前两条数据）
这下就可以解决取出第二条数据的SQl了
select * from (select rownum no,id,name from student where rownum<3 ) where no >=2;
4.rownum和排序
select rownum ,id,name from (select * from student order by name);

注意：oracle取出的数据第一条是1而不是0！

参考文章：http://blog.csdn.net/zhenyucheung/archive/2008/05/15/2449954.aspx

Oracle s***** B2C

/* Formatted on 2008/06/18 12:12 (Formatter Plus v4.8.8) */
/*
database:oracle instance:snpdb id:***** pwd:***** table:ord_mst
E:\s*****\B2C 3\sql\ord_mst.sql
*/

--获取商品目录列表
select * from (select s.spid,g.name,g.memo,s.create_time from specific_product s,general_product g where s.fk_general_product = g.generalid order by s.create_time desc) RSSTABLE where rownum <= 20
select * from ord_mst o where  '2008-06-01'= trim(to_char(o.ord_date,'yyyy-MM-dd'))
--查询2008-06-01与2008-06-15之间订单明细
SELECT o.ord_date, o.ord_no, o.ord_sts, o.ord_typ, o.ttl_amt
  FROM ord_mst o
 WHERE o.ord_date >= TO_DATE ('2008-06-01', 'yyyy-mm-dd')
   AND o.ord_date <= TO_DATE ('2008-06-15', 'yyyy-mm-dd');
--查询2008-06-01与2008-06-15之间订单均价
SELECT AVG (o.ttl_amt)
  FROM ord_mst o
 WHERE o.ord_date >= TO_DATE ('2008-06-01', 'yyyy-mm-dd')
   AND o.ord_date <= TO_DATE ('2008-06-15', 'yyyy-mm-dd');
--查询2008-06-01与2008-06-15之间订单总价
SELECT sum (o.ttl_amt)
  FROM ord_mst o
 WHERE o.ord_date >= TO_DATE ('2008-06-01', 'yyyy-mm-dd')
   AND o.ord_date <= TO_DATE ('2008-06-15', 'yyyy-mm-dd');

-- Create table
create table MEMBER
(
  MEMBERID            NUMBER(19) not null,
  EMAIL               VARCHAR2(255) not null,
  NICKNAME            VARCHAR2(255) not null,
  PASSWORD            VARCHAR2(255),
  NAME                VARCHAR2(255),
  ID_NUMBER           VARCHAR2(255),
  GENDER              VARCHAR2(10),
  TELEPHONE           VARCHAR2(20),
  CELLPHONE           VARCHAR2(20),
  PROVINCE            NUMBER(19),
  CITY                NUMBER(19),
  DISTRICT            NUMBER(19),
  ADDRESS             VARCHAR2(255),
  ZIPCODE             VARCHAR2(255),
  AGE                 VARCHAR2(30),
  ACTIVE_STATUS       VARCHAR2(1),
  OCCUPATION          VARCHAR2(100),
  PASSWORD_PROTECTION VARCHAR2(1),
  FAX                 VARCHAR2(20),
  QUESTION            VARCHAR2(100),
  ANSWER              VARCHAR2(100),
  ACTIVATE_CODE       VARCHAR2(20),
  UPDATE_USER         VARCHAR2(20),
  UPDATE_TIME         TIMESTAMP(6),
  CREATE_USER         VARCHAR2(20),
  CREATE_TIME         TIMESTAMP(6),
  STATUS              VARCHAR2(2),
  FK_LEVEL            NUMBER(19),
  CITY_ID             NUMBER(19),
  BIRTHDAY            TIMESTAMP(6),
  SITE_CODE           VARCHAR2(20),
  SALES_ORG           VARCHAR2(20)
)
tablespace SAP_SAPCE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table MEMBER
  add primary key (MEMBERID)
  using index
  tablespace SAP_SAPCE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table MEMBER
  add unique (EMAIL)
  using index
  tablespace SAP_SAPCE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table MEMBER
  add unique (NICKNAME)
  using index
  tablespace SAP_SAPCE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table MEMBER
  add constraint FK87557E9A47C402E1 foreign key (FK_LEVEL)
  references MEMBER_LEVEL (LEVELID);

--查找会员基本信息

select t.*, t.rowid from member t where t.name='王海军';

--查看当前系统中用户连接数

select   count(currentSession)   from   v$session;

--输出3月26日（即上线之日）以后的所以会员注册信息

select m.nickname 昵称,m.email 邮件,m.name 姓名,m.id_number 身份证, (case m.gender when '1' then '男' when '2' then '女' end) 性别,m.telephone 电话, m.cellphone 手机,m.address 详细地址,c.city_name 注册城市,to_char(m.create_time,'yyyy-MM-dd') 注册日期, m.zipcode 邮政编码,m.age 生日,(case m.occupation when '请选择' then '无' else m.occupation end) 职业,s.sales_organ_name 注册站点, (case m.active_status when '1' then '已激活' else '未激活' end) 激活状态 from member m,city c ,sales_organization s where m.create_time >= to_date('2008-03-26','yyyy-MM-dd') and m.city_id=c.cityid(+) and m.sales_org=s.sales_orga_code(+) order by m.memberid asc;

Oracle s***** CRM

--查询当前用户创建表总数

select count(*) from tabs;
select count(*) from user_tables;
tablespace "snworkorder" enable;
pctincrease 0 freelist 1 freelist group 1 buffer_poll default

--查看用户所有表
select table_name from user_tables;

spool on
spool off
select count(*) from user_tables where username like '%snwork%';
desc user_tables;
desc user_objects;

--赋予"SNWORKORDER” DBA 权限不

GRANT "DBA" TO "SNWORKORDER" WITH ADMIN OPTION;
ALTER USER "SNWORKORDER" DEFAULT ROLE  ALL;

--重新导入供应商数据 goldapp@gold_192.168.200.72

select * from tbl_supplier_exchange t

delete from tbl_supplier_exchange;

toad----database----import----import table data----execute winzard

 

Oracle 提高查询速度方法总结

http://topic.csdn.net/t/20061024/20/5106330.html

主要总结提高查询速度的方法，涉及到减少连接数据库次数、建立索引、优化语句等方面。      
  关于索引，推荐转载的这篇文章  
  http://blog.csdn.net/dutguoyi/archive/2006/01/10/575617.aspx      
  改善SQL语句的效率  
  http://community.csdn.net/Expert/topic/5087/5087396.xml?temp=.345669  
  数据量很大怎样加快索检速度  
  http://community.csdn.net/Expert/topic/5058/5058320.xml?temp=.1229517  
  索引建立方法的区别  
  http://community.csdn.net/Expert/topic/5068/5068154.xml?temp=.3010218  
  频繁插入删除数据需要更新索引  
  http://community.csdn.net/Expert/topic/4937/4937910.xml?temp=.8428614  
  测试了一下sql   server   2005   全文检索  
  http://community.csdn.net/Expert/topic/4878/4878430.xml?temp=.6049311      
  判断一个表的数据不在另一个表中最优秀方法？  
  http://community.csdn.net/Expert/topic/5038/5038742.xml?temp=.4704553  
  删除千万级表中重复记录的办法  
  http://community.csdn.net/Expert/topic/5089/5089261.xml?temp=.7907068      

 

数据库数据查询变得不正常类型问题

大数据量，稳定运行一段时候以后无法得到查询结果。 

http://community.csdn.net/Expert/topic/4810/4810464.xml?temp=9.014529E-02

 

Oracle ORCL instance

http://hi.baidu.com/lidhcn/blog/item/54e987dff2ecb515632798a8.html

使用scott/tiger 用户下的emp 表和dept 表完成下列练习

 

 

Emp 员工表

 

empno	ename	job	Mgr	Hiredate	Sal	Comm	Deptno
员工号	员工姓名	工作	上级编号	受雇日期	薪金	佣金	部门编号

 

Dept 表

 

Deptno	Dname	Loc
部门编号	部门名称	地点

 

1 ．列出至少有一个员工的所有部门。

2 ．列出薪金比“SMITH ”多的所有员工。

3 ．列出所有员工的姓名及其直接上级的姓名。

4 ．列出受雇日期早于其直接上级的所有员工。

5 ．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

6 ．列出所有“CLERK ”（办事员）的姓名及其部门名称。

7 ．列出最低薪金大于1500 的各种工作。

8 ．列出在部门“SALES ”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

9 ．列出薪金高于公司平均薪金的所有员工。

10 ．列出与“SCOTT ”从事相同工作的所有员工。

11 ．列出薪金等于部门30 中员工的薪金的所有员工的姓名和薪金。

12 ．列出薪金高于在部门30 工作的所有员工的薪金的员工姓名和薪金。

13 ．列出在每个部门工作的员工数量、平均工资和平均服务期限。

14 ．列出所有员工的姓名、部门名称和工资。

15 ．列出从事同一种工作但属于不同部门的员工的一种组合。

16 ．列出所有部门的详细信息和部门人数。

17 ．列出各种工作的最低工资。

18 ．列出各个部门的MANAGER （经理）的最低薪金。

19 ．列出所有员工的年工资, 按年薪从低到高排序。

1. select distinct dname from dept where deptno in (select distinct deptno from emp); 要查部门编号如果存在于emp 表中，说明这个部门有员工。

 2. select ename,job,mgr,hiredate,sal from emp where sal> (select sal from emp where ename='SMITH'); 3. select e.ename, e.mgr , w.ename from emp e, emp w where e.mgr=w.empno;

 4. select e.ename, e.mgr , e.hiredate,w.ename,w.hiredate from emp e, emp w where e.mgr=w.empno and e.hiredate< w.hiredate;

 5. select e.ename,e.hiredate, d.dname,d.deptno from emp e, dept d where e.deptno (+)=d.deptno; 本例子使用的是外连接， 也可以用右连接。 select e.ename,e.hiredate, d.dname,d.deptno from emp e right join dept d on e.deptno=d.deptno;

 6. select e.ename,e.hiredate,e.job, d.dname,d.deptno from emp e, dept d where e.deptno =d.deptno and e.job=upper('clerk');

7. select job, min(sal) from emp group by job having min(sal)>1500;

8. select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname=upper('sales'); 或者： select ename from emp where deptno = (select deptno from dept where dname=upper('SALES'));

 9. select ename, job, sal from emp where sal> (select avg(sal) from emp );

10. select ename, job from emp where job = (select job from emp where ename=upper('scott'));

11. select ename, sal,job from emp where sal in (select sal from emp where deptno=30);

12. select ename, sal,job from emp where sal >all (select sal from emp where deptno=30); 或者 select ename ,sal from emp where sal> (select max(sal) from emp where deptno=30);

13. select d.deptno,d.dname, count(e.ename), avg(e.sal), avg(months_between(sysdate,e.hiredate)) from emp e, dept d where e.deptno(+)= d.deptno group by d.deptno,dname; 或者下面的方法，这种方法更好理解，把需要汇总的生成一个视图e。 select e.*,d.dname ， from (select deptno, count(ename) ,avg(sal),avg(months_between(sysdate,hiredate)) from emp group by deptno) e, dept d where e.deptno =d.deptno;

14. select d.dname, e.ename, sal+nvl(comm,0) from emp e, dept d where d.deptno=e.deptno;

15. select e.ename,e.job, e.deptno ,d.job, d.deptno from emp e, emp d where e.job=d.job and e.deptno<>d.deptno;

16. select count(e.ename),d.dname from emp e, dept d where e.deptno(+)= d.deptno group by e.deptno,d.dname; 或者写成 select d.deptno,e.*,d.dname from (select deptno,count(ename) from emp group by deptno) e,dept d where e.deptno(+)= d.deptno;

17. select job,min(sal+nvl(comm,0))from emp group by job;

 18. select deptno,min(sal) from emp where job=upper('manager') group by deptno ;

19. select ename , (sal+nvl(comm,0))*12 yearsal from emp order by yearsal ;

 

Oracle:192.168.116.161 id:sys pwd:li
--create tablespace
CREATE SMALLFILE
    TABLESPACE "SNWORKORDER"
    LOGGING
    DATAFILE
    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SNWORKORDER.ORA' SIZE
    5M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT 
    AUTO
--create user
CREATE USER "SNWORKORDER"  PROFILE "DEFAULT"
    IDENTIFIED BY "SNWORKORDER" DEFAULT TABLESPACE "SNWORKORDER"
    TEMPORARY TABLESPACE "SNWORKORDER"
    ACCOUNT UNLOCK;
GRANT ALTER ANY TABLE TO "SNWORKORDER" WITH ADMIN OPTION
GRANT CREATE ANY TABLE TO "SNWORKORDER" WITH ADMIN OPTION
GRANT CREATE TABLE TO "SNWORKORDER" WITH ADMIN OPTION
GRANT DROP ANY TABLE TO "SNWORKORDER" WITH ADMIN OPTION
GRANT INSERT ANY TABLE TO "SNWORKORDER" WITH ADMIN OPTION
GRANT SELECT ANY TABLE TO "SNWORKORDER" WITH ADMIN OPTION
GRANT SYSDBA TO "SNWORKORDER" WITH ADMIN OPTION
GRANT "CONNECT" TO "SNWORKORDER" WITH ADMIN OPTION;
GRANT "DBA" TO "SNWORKORDER" WITH ADMIN OPTION;
GRANT "OLAP_DBA" TO "SNWORKORDER" WITH ADMIN OPTION;
GRANT "RESOURCE" TO "SNWORKORDER" WITH ADMIN OPTION;

---------------------

end

 

---------------------