Oracle示例.docx

Oracle示例.docx

《Oracle示例.docx》由会员分享，可在线阅读，更多相关《Oracle示例.docx（20页珍藏版）》请在冰豆网上搜索。

Oracle示例

ORACLE

基本体系结构

创建用户

createuserxifuidentifiedbyxifu;

授权

grantconnect,resource,dbatoxifu;--授予测试、开发、DBA权限

grantunlimitedtablespacetoxifu;--授予不限制的表空间

grantselectanytabletoxifu;--授予查询任何表

grantselectanydictionarytoxifu;--授予查询任何字典

删除用户

DROPUSERxifuCASCADE

创建表空间

CREATETABLESPACEdata01

DATAFILE'F:

//oracle//DATA01.dbf'SIZE5M

UNIFORMSIZE128k;#指定区尺寸为128k,如不指定，区尺寸默认为64k

修改表空间属性

alterdatabasedatafile'F:

//oracle//DATA01.dbf'resize10M

删除表空间

droptablespacedata01includingcontentsanddatafiles;

建回滚段表空间

CREATEUNDOTABLESPACEUNDOTBS02

DATAFILE'F:

//oracle//UNDOTBS02.dbf'SIZE5M

建临时表空间

CREATETEMPORARYTABLESPACEtemp_data

TEMPFILE'F:

//oracle//TEMP_DATA.dbf'SIZE5M

表空间增加文件

ALTERTABLESPACEdata01ADDDATAFILE'F:

//oracle//DATA02.dbf'SIZE5M;

设定数据文件自动扩展

ALTERDATABASEDATAFILE'F:

//oracle//DATA02.dbf'AUTOEXTENDONNEXT5MMAXSIZE10M;

将数据从一个表空间移动到另一个表空间

CREATETABLESPACEdata03DATAFILE'F:

//oracle//DATA02.dbf'SIZE5M

UNIFORMSIZE128k;

altertableT_TESTmovetablespacedata03;

DDL语句

创建表

createtableT_STUDENT（

STUDENT_IDnumbernotnullconstraintPK_STUDENTIDprimarykey,

STUDENT_NAMEvarchar（50）notnull,

STUDENT_SEXchar（10）notnull,

STUDENT_AGEnumbernotnull,

STUDENT_ADDRESSvarchar（100）null,

UPDATEDATEdatenotnull

）tablespacedata01;

createtableT_COURSE（

Course_IDnumbernotnullconstraintPK_COURSEIDprimarykey,

Course_NAMEvarchar（50）notnull,

Course_TEACHERvarchar（50）default（'傅'）notnull

）tablespacedata01;

createtableT_COUR_STU_RELATION（

Course_IDnumbernotnull,

STUDENT_IDnumbernotnull,

constraintPK_CIDSIDprimarykey（Course_ID,STUDENT_ID）

）tablespacedata01;

修改表

altertableT_STUDENTaddSTUDENT_HEIGHTnumber（2,2）notnull

altertableT_STUDENTmodifySTUDENT_HEIGHTnumbernull

（如果表是由sys用户创建的则无法删除列）

altertableT_STUDENTdropcolumnSTUDENT_HEIGHT

altertableT_STUDENTsetunused（"STUDENT_HEIGHT"）cascadeconstraints

altertabledropunusedcolumns

增加约束

altertableT_STUDENTaddconstraintUQ_STUDENTNAMEunique（STUDENT_NAME）

altertableT_COUR_STU_RELATIONaddconstraintFK_STUDENTIDforeignkey（STUDENT_ID）referencesT_STUDENT（STUDENT_ID）ondeletecascade

（deletesetnull将所有相关记录的外部码字段值设置为NULL）（deletenoaction不做任何操作）

altertableT_STUDENTaddconstraintCK_STUDENTSEXcheck（STUDENT_SEX=0orSTUDENT_SEX=1）

删除约束

altertableT_STUDENTdropunique（STUDENT_NAME）

altertableT_STUDENTdropconstraintUQ_STUDENTNAME

删除约束保留索引

createtablea（aidnumbernotnullprimarykey）tablespacedata01;

altertableadropprimarykeykeepindex

删除级联约束

altertableT_STUDENTdropprimarykeycascade

禁用/激活约束

altertableT_STUDENTdisableunique（STUDENT_NAME）

altertableT_STUDENTenableunique（STUDENT_NAME）

altertableT_STUDENTdisableconstraintUQ_STUDENTNAME

altertableT_STUDENTenableconstraintUQ_STUDENTNAME

altertableT_STUDENTmodifyconstraintUQ_STUDENTNAMEdisable

altertableT_STUDENTmodifyconstraintUQ_STUDENTNAMEenable

清除所有行

truncatetableT_STUDENT

在内存中，用delete删除数据，表空间中其被删除数据的表占用的空间还在，除非重新启动系统（OS或者RDBMS）。

用truncate清除数据，内存中表空间中其被删除数据的表占用的空间会被立即释放

DML语句

insertintoT_STUDENTvalues（My_SEQ.Nextval,'Lily',0,18,'江西理工',sysdate）;

insertintoT_STUDENTvalues（My_SEQ.Nextval,'Meggie',0,18,'华东交大',sysdate）;

insertintoT_STUDENTvalues（My_SEQ.Nextval,'Kiven',1,21,'南昌理工',sysdate）;

insertintoT_STUDENTvalues（My_SEQ.Nextval,'Anne',0,19,'江西财大',sysdate）;

insertintoT_STUDENTvalues（My_SEQ.Nextval,'Gigi',0,20,'江西理工',sysdate）;

insertintoT_STUDENTvalues（My_SEQ.Nextval,'Johe',1,18,'南昌理工',sysdate）;

insertintoT_STUDENTvalues（My_SEQ.Nextval,'Tom',1,20,'江西财大',sysdate）;

insertintoT_STUDENTvalues（My_SEQ.Nextval,'Rose',0,19,'江西师大',sysdate）;

insertintoT_STUDENTvalues（My_SEQ.Nextval,'Peter',1,18,'江西科大',sysdate）;

insertintoT_STUDENTvalues（My_SEQ.Nextval,'Jession',1,18,null,sysdate）;

insertintoT_COURSE（COURSE_ID,COURSE_NAME,COURSE_TEACHER）values（1,'英语','王老师'）

insertintoT_COURSE（COURSE_ID,COURSE_NAME,COURSE_TEACHER）values（2,'数学','李老师'）

insertintoT_COURSE（COURSE_ID,COURSE_NAME,COURSE_TEACHER）values（3,'数据结构','刘老师'）

insertintoT_COURSE（COURSE_ID,COURSE_NAME,COURSE_TEACHER）values（4,'C语言','朱老师'）

insertintoT_COURSE（COURSE_ID,COURSE_NAME,COURSE_TEACHER）values（5,'数据库','钱老师'）

insertintoT_COUR_STU_RELATIONvalues（4,3）;

insertintoT_COUR_STU_RELATIONvalues（1,5）;

insertintoT_COUR_STU_RELATIONvalues（2,9）;

insertintoT_COUR_STU_RELATIONvalues（5,1）;

insertintoT_COUR_STU_RELATIONvalues（3,3）;

insertintoT_COUR_STU_RELATIONvalues（4,10）;

简单select

selectSTUDENT_NAMEname,STUDENT_SEXassexfromT_STUDENT;

selectdistinctSTUDENT_AGEfromT_STUDENT;

selectcount（*）fromT_STUDENT;

selectsum（distinctStudent_AGE）fromT_STUDENT;

不支持top语句，可用rownum代替

select*fromT_STUDENTwhererownum<=4orderbyStudent_ID

select*fromT_STUDENTwhereSTUDENT_ID=1;

select*fromT_STUDENTwhereSTUDENT_ADDRESSisnull;

select*fromT_STUDENTwhereSTUDENT_NAME='Peter'andSTUDENT_AGE=18;

日期的查询方式要用装换函数转成日期或字符

select*fromT_STUDENTwhereupdatedate=to_date（'2008-12-1320:

35:

29','yyyy-mm-ddhh24:

mi:

ss'）;

select*fromT_STUDENTwhereto_char（updatedate,'FMyyyy-mm-ddHH24:

mi:

ss'）='2008-12-1320:

35:

29';

select*fromT_STUDENTwhereSTUDENT_IDbetween2and5;

select*fromT_STUDENTwhereSTUDENT_NAMElike'%R%';

Like操作符加ESCAPE表示查询包含“%”和“_”

select*fromT_STUDENTwhereexists（select*fromT_STUDENTwhereSTUDENT_ID=1）;

select*fromT_STUDENTwherenotexists（select*fromT_STUDENTwhereSTUDENT_ID=1）;

select*fromT_STUDENTwhereSTUDENT_IDin（selectSTUDENT_IDfromT_STUDENTwhereSTUDENT_IDbetween2and5）;

select*fromT_STUDENTwhereSTUDENT_IDnotin（selectSTUDENT_IDfromT_STUDENTwhereSTUDENT_IDbetween2and5）;

select*fromT_STUDENTwhereSTUDENT_IDin（selectSTUDENT_IDfromT_STUDENTwhereSTUDENT_IDbetween2and5）;

select*fromT_STUDENTwhereSTUDENT_ID=any（selectSTUDENT_IDfromT_STUDENTwhereSTUDENT_IDbetween2and5）;

select*fromT_STUDENTwhereSTUDENT_IDnotin（selectSTUDENT_IDfromT_STUDENTwhereSTUDENT_IDbetween2and5）;

select*fromT_STUDENTwhereSTUDENT_ID<>all（selectSTUDENT_IDfromT_STUDENTwhereSTUDENT_IDbetween2and5）;

分组

selectSTUDENT_ADDRESSfromT_STUDENTgroupbySTUDENT_ADDRESS;

selectSTUDENT_ADDRESSfromT_STUDENTgroupbySTUDENT_ADDRESShavingSTUDENT_ADDRESSisnotnull;

分组中的cube操作符

createtableInventory

（

Itemvarchar（50）notnull,

Colorvarchar（50）notnull,

Quantitynumbernotnull,

pricenumber（5,2）null

）tablespacedata01;

insertintoInventoryvalues（'Table','Blue',124,150.0）;

insertintoInventoryvalues（'Table','Red',223,156.0）;

insertintoInventoryvalues（'Chair','Blue',101,89.0）;

insertintoInventoryvalues（'Chair','Red',210,93.0）;

SELECTItem,Color,SUM（Quantity）ASQtySumFROMInventoryGROUPBYCUBE（Item,Color）;

SELECTCASEWHEN（GROUPING（Item）=1）THEN'ALL'

ELSEnvl（Item,'UNKNOWN'）

ENDASItem,

CASEWHEN（GROUPING（Color）=1）THEN'ALL'

ELSEnvl（Color,'UNKNOWN'）

ENDASColor,

SUM（Quantity）ASQtySum

FROMInventory

GROUPBYCUBE（Item,Color）;

rollup操作符

SELECTCASEWHEN（GROUPING（Item）=1）THEN'ALL'

ELSEnvl（Item,'UNKNOWN'）

ENDASItem,

CASEWHEN（GROUPING（Color）=1）THEN'ALL'

ELSEnvl（Color,'UNKNOWN'）

ENDASColor,

SUM（Quantity）ASQtySum

FROMInventory

GROUPBYROLLUP（Item,Color）;

排序

select*fromT_STUDENTorderbySTUDENT_AGEasc;

select*fromT_STUDENTorderbySTUDENT_AGEdesc;

内连接

select*fromT_STUDENTainnerjoinT_COUR_STU_RELATIONbona.STUDENT_ID=b.STUDENT_ID;

select*fromT_STUDENTa,T_COUR_STU_RELATIONbwherea.STUDENT_ID（+）=b.STUDENT_ID;

select*fromT_STUDENTa,T_COUR_STU_RELATIONbwherea.STUDENT_ID=b.STUDENT_ID;

外连接

select*fromT_STUDENTaleftouterjoinT_COUR_STU_RELATIONbona.STUDENT_ID=b.STUDENT_ID;

select*fromT_STUDENTarightouterjoinT_COUR_STU_RELATIONbona.STUDENT_ID=b.STUDENT_ID;

Union

selectSTUDENT_ID,STUDENT_NAME,STUDENT_ADDRESSfromT_STUDENT

union

select*fromT_COURSE;

Unionall

selectSTUDENT_IDfromT_STUDENT

unionall

selectSTUDENT_IDfromT_COUR_STU_RELATION;

MINUS

selectSTUDENT_IDfromT_STUDENT

MINUS

selectSTUDENT_IDfromT_COUR_STU_RELATION;

INTERSECT

selectSTUDENT_IDfromT_STUDENT

INTERSECT

selectSTUDENT_IDfromT_COUR_STU_RELATION;

INTO子句

declare

namevarchar（10）;

begin

selectSTUDENT_NAMEintonamefromT_STUDENTwhereSTUDENT_ID=2;

end;

createtabletest

as

（

select*fromT_STUDENT

）;

insertintoxifu.tabselectSTUDENT_ID,STUDENT_NAMEfromT_STUDENT;

commit;

数据类型

selectsysdatefromdual;

selectsystimestampfromdual;

selectrowidfromT_STUDENT;

selectrownumfromT_STUDENT;

函数

日期函数

selectround（sysdate,'month'）fromdual

selectnext_day（sysdate,'星期一'）fromdual

selectextract（monthfromsysdate）fromdual

字符串函数

selectltrim（'li'）fromdual

selectconcat（'Lily','Kiven'）fromdual

selecttranslate（'hello','ll','ii'）fromdual

selectreplace（'jackandjue','j','bl'）fromdual

select*fromT_STUDENTwhereINSTR（STUDENT_NAME,'J'）>0

转换函数

selectto_char（sysdate,'FMyyyy-mm-ddHH:

mi:

ss'）fromdual

selectto_char（12.3,'99'）fromdual

selectto_char（0.23）fromdual

selectto_char（0.9999,'FM90.0999'）fromdual

selectto_date（'2008-01-2314:

55:

20','yyyy-mm-ddhh24:

mi:

ss'）fromdual

selectto_number（'20'）fromdual

其他函数

selectnvl（null,'hello'）fromdual

selectnvl（12,53）fromdual

分析函数

select*fromscott.emp

selectrownumfromscott.emp

selectename,sal,deptno,row_number（）over（partitionbydeptnoorderbysal）fromscott.emporderbydeptno

selectename,sal,deptno,rank（）over（partitionbydeptnoorderbysal）fromscott.emporderbydeptno

selectename,sal,deptno,dense_rank（）over（partitionbydeptnoorderbysal）fromscott.emporderbydeptno

selectdeptno,ename,sal,lag（ename,1,nu