仓酷云

标题: MYSQL教程之Oracle履历技能集 [打印本页]

作者: 飘灵儿 时间: 2015-1-16 22:43
标题: MYSQL教程之Oracle履历技能集
不管怎么样，市场的结果已经证明MySQL具有性价比高、灵活、MySQL学习教程广为使用和具有良好支持的特点。oracle|技能Oracle履历技能集1．删除表空间
DROPTABLESPACETableSpaceName[INCLUDINGCONTENTS[ANDDATAFILES]]
2．删除用户
DROPUSERUser_NameCASCADE
3．删除表的注重事项
在删除一个表中的全体数据时，须利用TRUNCATETABLE表名;由于用DROPTABLE，DELETE*FROM表名时，TABLESPACE表空间该表的占用空间并未开释，重复几回DROP，DELETE操纵后，该TABLESPACE上百兆的空间就被耗光了。
4．having子句的用法
having子句对groupby子句所断定的行组举行把持，having子句前提中只同意触及常量,聚组函数或groupby子句中的列。
5．内部连接"+"的用法
内部连接"+"按其在"="的右边或右侧分左连接和右连接.若不带"+"运算符的表中的一个行不间接婚配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相婚配并被前往.若两者均不带’+’,则两者中没法婚配的均被前往.使用内部连接"+",能够替换效力非常低下的notin运算,年夜年夜进步运转速率.比方,上面这条命令实行起来很慢

用外连接进步表毗连的查询速率

在作表毗连(经常使用于视图)时，常利用以下办法来查询数据:

SELECTPAY_NO,PROJECT_NAME

FROMA

WHEREA.PAY_NONOTIN(SELECTPAY_

NOFROMBWHEREVALUE>=120000);

----可是若表A有10000笔记录，表B有10000笔记录，则要用失落30分钟才干查完，次要由于NOTIN要进行一条一条的对照，共必要10000*10000次对照后，才干失掉了局。该用外连接后，能够延长到1分摆布的工夫:

SELECTPAY_NO,PROJECT_NAME

FROMA,B

WHEREA.PAY_NO=B.PAY_NO(+)

ANDB.PAY_NOISNULL

ANDB.VALUE>=12000;
6．settransaction命令的用法
在实行年夜事件时,偶然oracle会报出以下的毛病:

ORA-01555:snapshottooold(rollbacksegmenttoosmall)

　　这申明oracle给此事件随机分派的回滚段太小了,这时候能够为它指定一个充足年夜的回滚段,以确保这个事件的乐成实行.比方

settransactionuserollbacksegmentroll_abc;

deletefromtable_namewhere...

commit;

　　回滚段roll_abc被指定给这个delete事件,commit命令则在事件停止以后作废了回滚段的指定.
7．数据库重修应注重的成绩

　　在使用import举行数据库重修过程当中,有些视图大概会带来成绩,由于布局输出的按次大概形成视图的输出先于它低条理表的输出,如许创建视图就会失利.要办理这一成绩,可接纳分两步走的办法:起首输出布局,然后输出数据.命令举比方下(uesrname:jfcl,password:hfjf,hoststing:ora1,数据文件:expdata.dmp):

impjfcl/hfjf@ora1file=empdata.dmprows=N

impjfcl/hfjf@ora1file=empdata.dmpfull=Ybuffer=64000

commit=Yignore=Y

　　第一条命令输出一切数据库布局,但无纪录.第二次输出布局和数据,64000字节提交一次.ignore=Y选项包管第二次输出既使工具存在的情形下也能乐成.

selecta.empnofromempawherea.empnonotin

(selectempnofromemp1wherejob=’SALE’);

　　倘使使用内部连接,改写命令以下:

selecta.empnofromempa,emp1b

wherea.empno=b.empno(+)

andb.empnoisnull

andb.job=’SALE’;

　　能够发明,运转速率分明进步.
8．从已知表新建另外一个表：
CREATETABLEb

ASSELECT*(能够是表a中的几列)

FROMa

WHEREa.column=...;
9．查找、删除反复纪录:
法一:用Groupby语句此查找很快的

selectcount(num),max(name)fromstudent--查找表中num列反复的，列出反复的纪录数，并列出他的name属性

groupbynum

havingcount(num)>1--按num分组后找出表中num列反复，即呈现次数年夜于一次

deletefromstudent(下面Select的)

如许的话就把一切反复的都删除。-----稳重

法二:当表对照年夜(比方10万条以上)时,这个办法的效力之差使人没法忍耐,必要另想举措:

----实行上面SQL语句后就能够显现一切DRAWING和DSNO不异且反复的纪录

SELECT*FROMEM5_PIPE_PREFAB

WHEREROWID!=(SELECTMAX(ROWID)FROMEM5_PIPE_PREFABD--D相称于First,Second

WHEREEM5_PIPE_PREFAB.DRAWING=D.DRAWINGAND

EM5_PIPE_PREFAB.DSNO=D.DSNO);

----实行上面SQL语句后就能够h除一切DRAWING和DSNO不异且反复的纪录

DELETEFROMEM5_PIPE_PREFAB

WHEREROWID!=(SELECTMAX(ROWID)FROMEM5_PIPE_PREFABD

WHEREEM5_PIPE_PREFAB.DRAWING=D.DRAWINGAND

EM5_PIPE_PREFAB.DSNO=D.DSNO);

10．前往表中[N，M]笔记录：

获得某列中第N年夜的行

selectcolumn_namefrom

(selecttable_name.*,dense_rank()over(orderbycolumndesc)rankfromtable_name)

whererank=&N；

　假设要前往前5笔记录：

　　select*fromtablenamewhererownum<6;(或是rownum<=5或是rownum!=6)

假设要前往第5-9笔记录：

select*fromtablename

where…

andrownum<10

minus

select*fromtablename

where…

andrownum<５

orderbyname

　选出了局后用name排序显现了局。(先选再排序)

注重：只能用以上标记(<、<=、!=)。

select*fromtablenamewhererownum!=10;前往的是前９笔记录。

不克不及用：>,>=,=,Between...and。因为rownum是一个老是从1入手下手的伪列，Oracle以为这类前提不建立，查不到纪录.

别的，这个办法更快：

select*from(

selectrownumr,afromyourtable

whererownum<=20

orderbyname)

wherer>10

如许掏出第11-20笔记录!(先选再排序再选)

要先排序再选则须用select嵌套：内层排序外层选。

rownum是跟着了局集天生的，一旦天生，就不会变更了；同时,天生的了局是顺次递减的，没有1就永久不会有2!

rownum是在查询汇合发生的过程当中发生的伪列，而且假如where前提中存在rownum前提的话，则:

1：假设判断前提是常量，则：

只能rownum=1,<=年夜于1的天然数，=年夜于1的数是没有了局的，年夜于一个数也是没有了局的

即当呈现一个rownum不满意前提的时分则查询停止　　thisisstopkey!

2:当判断值不是常量的时分

若前提是=var,则只要当var为1的时分才满意前提，这个时分不存在stopkey,必需举行fullscan,对每一个满意其他where前提的数据举行判断

选出一行后才干往选rownum=2的行……

11．疾速编译一切视图

----当在把数据库倒进到新的服务器上后(数据库重修)，必要将视图从头编译一遍，由于该表空间视图到别的表空间的表的毗连会呈现成绩，能够使用PL/SQL的言语特征，疾速编译。

SQL>SPOOLON.SQL

SQL>SELECT‘ALTERVIEW‘||TNAME||’

COMPILE;’FROMTAB;

SQL>SPOOLOFF

然后实行ON.SQL便可。

SQL>@ON.SQL

固然，受权和创立同义词也能够疾速举行，如：

SQL>SELECT‘GRANTSELECTON’

||TNAME||’TOUSERNAME;’FROMTAB;

SQL>SELECT‘CREATESYNONYM

‘||TNAME||’FORUSERNAME.’||TNAME||’;’FROMTAB;

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

让你的天空只要甜和美

忘记———该怎样堕泪

文章选项：

Lunatic

(stranger)

06/13/0311:33

[精髓]Re:Oracle经常使用命令[re:Lunatic]

12．读写文本型操纵体系文件
----在PL/SQL3.3以上的版本中，UTL_FILE包同意用户经由过程PL/SQL读写操纵体系文件。以下：

DECALRE

FILE_HANDLEUTL_FILE.FILE_TYPE;

BEGIN

FILE_HANDLE:=UTL_FILE.FOPEN(

‘C:’,’TEST.TXT’,’A’);

UTL_FILE.PUT_LINE(FILE_HANDLE,’

HELLO,IT’SATESTTXTFILE’);

UTL_FILE.FCLOSE(FILE_HANDLE);

END;

13．在数据库触发器中利用列的新值与旧值
----在数据库触发器中几近老是要利用触发器基表的列值，假如某条语句必要某列修正前的值，利用:OLD就能够了，利用某列修正后的新值，用:NEW就能够了。如:OLD.DEPT_NO,:NEW.DEPT_NO。

14．数据库文件的挪动办法
当想将数据库文件挪动到别的一个目次下时，能够用ALTERDATABASE命令来挪动(比ALTERTABLESPACE合用性强)：

1.利用SERVERMANAGER封闭实例.

SVRMGR>connectinternal;

SVRMGR>shutdown;

SVRMGR>exit;

2.利用操纵体系命令来挪动数据库文件地位(假定这里操纵体系为SOLARIS2.6).在UNIX顶用mv命令能够把文件挪动到新的地位，

#mv/ora13/orarun/document.dbf/ora12/orarun

3.装载数据库并用alterdatabase命令来改动数据库中的文件名.

SVRMGR>connectinternal;

SVRMGR>startupmountRUN73;

SVRMGR>alterdatabaserenamefile

>‘/ora13/orarun/document.dbf’

>‘/ora12/orarun/document.dbf’;

4.启动实例.

SVRMGR>alterdatabaseopen;

15．毗连查询了局：
表a列a1a2

纪录1a

1b

2x

2y

2z

用select能选成以下了局:

1ab

2xyz

上面有两个例子：

1.利用pl/sql代码完成，但请求你组合后的长度不克不及超越oraclevarchar2长度的限定

createorreplacetypestrings_tableistableofvarchar2(20);

/

createorreplacefunctionmerge(pvinstrings_table)returnvarchar2

is

lsvarchar2(4000);

begin

foriin1..pv.countloop

ls:=ls||pv(i);

endloop;

returnls;

end;

/

createtablet(idnumber,namevarchar2(10));

insertintotvalues(1,Joan);

insertintotvalues(1,Jack);

insertintotvalues(1,Tom);

insertintotvalues(2,Rose);

insertintotvalues(2,Jenny);

columnnamesformata80;

selectt0.id,merge(cast(multiset(selectnamefromtwheret.id=t0.id)asstrings_table))names

from(selectdistinctidfromt)t0;

droptypestrings_table;

dropfunctionmerge;

droptablet;

2.地道用sql：

表dept,emp

要失掉以下了局

deptno,dname,employees

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

10,accounting,clark;king;miller

20,research,smith;adams;ford;scott;jones

30,sales,allen;blake;martin;james;turners

每一个dept的employee串起来作为一笔记录前往

Thisexampleusesamaxof6,andwouldneedmorecutnpastingtodomorethanthat：

SQL>selectdeptno,dname,emps

2from(

3selectd.deptno,d.dname,rtrim(e.ename||,||

4lead(e.ename,1)over(partitionbyd.deptno

5orderbye.ename)||,||

6lead(e.ename,2)over(partitionbyd.deptno

7orderbye.ename)||,||

8lead(e.ename,3)over(partitionbyd.deptno

9orderbye.ename)||,||

10lead(e.ename,4)over(partitionbyd.deptno

11orderbye.ename)||,||

12lead(e.ename,5)over(partitionbyd.deptno

13orderbye.ename),,)emps,

14row_number()over(partitionbyd.deptno

15orderbye.ename)x

16fromempe,deptd

17whered.deptno=e.deptno

18)

19wherex=1

20/

DEPTNODNAMEEMPS

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

10ACCOUNTINGCLARK,KING,MILLER

20RESEARCHADAMS,FORD,JONES,ROONEY,SCOTT,SMITH

30SALESALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

16．在Oracle中建一个编号会主动增添的字段,以利于查询

1、创建序列：

CREATESEQUENCEcheckup_no_seq

NOCYCLE

MAXVALUE9999999999

STARTWITH2;

2、创建触发器：

CREATEORREPLACETRIGGERset_checkup_no

BEFOREINSERTONcheckup_history

FOREACHROW

DECLARE

next_checkup_noNUMBER;

BEGIN

--Getthenextcheckupnumberfromthesequence

SELECTcheckup_no_seq.NEXTVAL

INTOnext_checkup_no

FROMdual;

--usethesequencenumberastheprimarykey

--fortherecordbeinginserted

:new.checkup_no:=next_checkup_no;

END;

17．检察工具的依附干系(好比视图与表的援用)

检察视图：dba_dependencies纪录了相干的依附干系

查器材不晓得要检察哪一个视图时，能够在DBA_Objects里看，

selectobject_namefromdba_objectswhereobject_namelike%ROLE%(假设检察ROLE相干)

然后DESC一下就大致上晓得了。

18．要找到某月中一切周五的详细日期
selectto_char(t.d,YY-MM-DD)from(

selecttrunc(sysdate,MM)+rownum-1asd

fromdba_objects

whererownum<32)t

whereto_char(t.d,MM)=to_char(sysdate,MM)--找出以后月份的周五的日期

andtrim(to_char(t.d,Day))=礼拜五

--------

03-05-02

03-05-09

03-05-16

03-05-23

03-05-30

假如把whereto_char(t.d,MM)=to_char(sysdate,MM)改成sysdate-90，即为查找以后

月份的前三个月中的每周五的日期。
“通过有能力的DBA的一个简单计划，MySQL可以达到令人难以想像的运行速度，”一位资深DBA如此表示。MySQL中没有多余的功能来拖累CPU或占用内存。

作者: 金色的骷髅 时间: 2015-1-19 21:48
一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

作者: admin 时间: 2015-2-2 17:08
如果是将来做数据库的开发设计，就应该详细学习T-SQL的各种细节，包括T-SQL的程序设计、存储过程、触发器以及具体使用某个开发语言来访问数据库。

作者: 若天明 时间: 2015-2-8 02:24
所以你总能得到相应的升级版本，来满足你的需求。

作者: 乐观 时间: 2015-2-24 02:09
对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。

作者: 深爱那片海 时间: 2015-3-7 10:29
再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。

作者: 小妖女 时间: 2015-3-14 22:49
where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

作者: 精灵巫婆 时间: 2015-3-21 14:52
另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);

欢迎光临仓酷云 (http://ckuyun.com/)