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