ORACLE笔记.docx
《ORACLE笔记.docx》由会员分享，可在线阅读，更多相关《ORACLE笔记.docx（23页珍藏版）》请在冰点文库上搜索。
ORACLE笔记
1.Oracle工具:
sqlplus
Sqlplus/assysdba
Shutdownimmediate（关闭数据库）
Startup（启动数据库）
注意：
数据库开启才可以进行操作
Selectusername,account_statusfromdba_users;（查询数据库中所有的用户名称与用户状态）
Alteruserscottidentifiedbytigeraccountunlock;（scott用户解锁）
Sqlplusscott/tiger（使用SCOTT密码为tiger登陆ORACLE数据库）
Connectscott/tiger（在SQL>中直接使用SCOTT用户连接数据库）
Showall;（看所有变量）
Setsqlprompt“_user>”（设置sqlplus环境下面的提示符用自身用户显示）
注意：
如要变量下次重启生效必须把变量写入
/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sql中
Host（回到系统）
Helpindex（查看所有命令）
Help+命令（类似于LINUX中的MAN,查看命令的格式与用法）
Disconnect（关闭SQLPLUS工具但是不离开数据库）
Describeemp（查看EMP表）
Define（定义变量,常用与写脚本用）
如：
DEFINE_EDITOR=”vi”（CHAR）（定义VI编辑器用于保存最后一条执行的SQL语句）
Save/u01/app/oracle/aa.sql（保存SQL语句）
Get/u01/app/oracle/aa.sql（调用保存的SQL语句）
2.select（select查询语句是ORACLE中最常用的语句）
DML语句包括（insert写入，update改变，delect删除，merge两张表同步）
注意：
merge常用在ETL（数据仓库）底下
DDL数据定义语句包括（create创建对象，alter修改，drop删除对象，rename改名，truncate删除整个表）
TCL事务处理语句（commit提交立刻生效，rollback回滚，savepoint保存点，savepoint（保存点）
DCL权限语句（grant赋予权限，revoke（收回权限）
ORACLE的表称之为堆表（keeptable）
SELECT
（1）writingbasicSQLselectstatemanes
1.selectingallcolumns
SCOTT>select*fromdept;
SCOTT>selectdname,deptno,locfromdept;
BasicSELECTStatement
SELECT*|{[DISTINCT]column|expression[alias],...}
FROMtable;
2.selectingspecificcolumns
SCOTT>selectdeptno,dnamefromdept;
SCOTT>selectename,salfromemp;
3.writingSQLstatements
SQLstatementsarenotcasesensitive.
SQLstatementscanbeononeormorelines.
Keywordscannotbeabbreviatedorsplitacrosslines.
Clausesareusuallyplacedonseparatelines.
Indentsareusedtoenhancereadability.
select*FROMEMP;
SELECT*
FROMemp;
4.ArithmeticExpressions
Anarithmeticexpressioncancontaincolumnnames,constantnumericvalues,andthearithmeticoperators.
+-*/
arithmeticoperatorsinanyclauseofaSQLstatementexceptintheFROMclause
selectename,sal+300fromemp;
OperatorPrecedence
Multiplication（*）anddivision（/）takepriorityoveraddition（+）andsubtraction（-）.
Operatorsofthesamepriorityareevaluatedfromlefttoright.
Parentheses（...）areusedtoforceprioritizedevaluationandtoclarifystatements.
selectename,sal,12*sal+100fromemp;
selectename,sal,12*（sal+100）fromemp;
5.defingaNULLvalue
Anullisavaluethatisunavailable,unassigned,unknown,orinapplicable.
Anullisnotthesameaszeroorablankspace.
SCOTT>selectename,sal,commfromemp;
SCOTT>selectename,sal,12*sal*commfromemp;
Arithmeticexpressionscontaininganullvalueevaluatetonull.（空值和任何数字计算等于空值）
6.definingaColumnalias
Renamesacolumnheading.
Isusefulwithcalculations.
Immediatelyfollowsthecolumnname-therecanalsobetheoptionalASkeywordbetweenthecolumnnameandalias.
Requiresdoublequotationmarksifitcontainsspacesorspecialcharactersoriscasesensitive.（备注名）
SCOTT>selectenamename,sal,12*sal*commfromemp;
SCOTT>selectenamename,sal,12*sal*commas"AnnualSalary"fromemp;
7.ConcatenationOperator
Concatenatescolumnsorcharacterstringstoothercolumns.
Isrepresentedbytwoverticalbars（||）.
Createsaresultantcolumnthatisacharacterexpression.
SCOTT>selectename||jobas"Employees"fromemp;
SCOTT>selectename||'isa'||jobas"Employees"fromemp;
（||是连接2个列之间的符号）
8.LiteralCharacterStrings
Aliteralisacharacter,anumber,oradateincludedintheSELECTlist.
Dateandcharacterliteralvaluesmustbeenclosedwithinsinglequotationmarks.
Eachcharacterstringisoutputonceforeachrowreturned.
SCOTT>selectename||'isa'||jobas"EmployeeDetails"fromemp;
9.DuplicateRows
SCOTT>selectdeptnofromdept;
SCOTT>selectdistinctdeptnofromemp;（取唯一）
（2）Restrictngandsortingdata
SELECT*|{[DISTINCT]column|expression[alias],...}
FROMtable
[WHEREcondition（s）];
a）WHERECLAUSE
RestricttherowsreturnedbyusingtheWHEREclause.
TheWHEREclausefollowstheFROMclause.
*where:
resttrictsthequerytorowsthatmeetacondition.
*condition:
columnname,expression,constants,andacomparisonoperator.
Characterstringsanddatevaluesareenclosedinsinglequotationmarks.
Charactervaluesarecasesensitive,anddatevaluesareformatsensitive.
ThedefaultdateformatisDD-MON-RR
1.LimitingRowsusingaSelection
SCOTT>select*fromempwheredeptno=30;
SCOTT>select*fromempwhereename='KING';
2.Comparisonconditions
=,>,>=,<,<=,<>
SCOTT>select*fromempwheresal>2000;
3.OtherComprisonconditions
between...and...,in（set）,like,isnull
a）usingtheBETWEENcondition
SCOTT>select*fromempwheresalbetween2000and2500;
b）usingtheINcondition
SCOTT>select*fromempwheremgrin（7698,7839,7788）;
c）usingtheLIKEcondition
%denoteszeroormanycharacters.
_denotesonecharacter.
SCOTT>select*fromempwhereenamelike'A%';（开头是A）
SCOTT>select*fromempwhereenamelike'_A%';（开头第2个是A的）
connhr/hr
HR>selectlast_name,job_idfromemployees;
SCOTT>SELECTlast_name,job_id
FROMemployees
WHEREjob_idLIKE'%SA\_%'ESCAPE'\';（查找SA_开头的\是转义字符，因为_在ORACLE中是属于字符类型的，所有要转义）
d）usingtheNULLconditions
SCOTT>select*fromempwheremgrisnull;（取空值要用IS）
4.logicalconditions
and,or,not
a）usingtheANDoperator
SCOTT>select*fromempwheresal>1000andjoblike'%MAN%';
b）usingtheORoperator
SCOTT>select*fromempwheresal>1000orjoblike'%MAN%';
c）usingtheNOToperator
SCOTT>select*fromempwherejobnotin（'SALESMAN','CLERK'）;
5.RulesofPrecedence
1Arithmeticoperators
2Concatenationoperator
3Comparisonconditions
4IS[NOT]NULL,LIKE,[NOT]IN
5[NOT]BETWEEN
6NOTlogicalcondition
7ANDlogicalcondition
8ORlogicalcondition
（and>or>not）
SCOTT>select*fromempwherejob='SALESMAN'orjob='CLERK'andsal>=1300;
SCOTT>select*fromempwhere（job='SALESMAN'orjob='CLERK'）andsal>=1300;
b）ORDERBYclause（排序）
SELECT*|{[DISTINCT]column|expression[alias],...}
FROMtable
[WHEREcondition（s）]
[ORDERBYcolumn|expression];
sortrows
ASC:
ascendingorder,default
DESC:
descendingorder
TheORDERBYclausecomeslastintheSELECTstatement.
1.SCOTT>select*fromemporderbyhiredate;（按照日期排序，日期是从老到新）
2.sortingindescendingorder
SCOTT>select*fromemporderbyhiredatedesc;（加DESC是升序）
3.sortingbycolumnalias
SCOTT>selectename,sal,12*salannsalfromemporderbyannsal;
4.sortingbymultiplecolumns
SCOTT>selectename,deptno,salannsalfromemporderbydeptno,saldesc;
YoucansortbyacolumnthatisnotintheSELECTlist.
SCOTT>selectename,deptnoannsalfromemporderbydeptno,saldesc;
（3）single-rowfunctions
SQLfunctionsarebuiltintoOracleDatabaseandareavailableforuseinvariousappropriateSQLstatements.
sqlfunctiontype
Manipulatedataitems.
Acceptargumentsandreturnonevalue.
Actoneachrowreturned.
Returnoneresultperrow.
Maymodifythedatatype.
Canbenested.
Acceptargumentswhichcanbeacolumnoranexpression.
function_name[（arg1,arg2,...）]
1.characterfunctions（字符函数）
selectename,lower（ename）fromemp;（把ename列里面的大写转换为小写）
selectupper（'Sqlcourse'）fromdual;（转换大写）
selectinitcap（'sqlcourse'）fromdual;（字段开头大写）
dualtable
DUALisatableautomaticallycreatedbyOracleDatabasealongwiththedatadictionary.
DUALisintheschemaoftheuserSYSbutisaccessiblebythenameDUALtoallusers.
Ithasonecolumn,DUMMY,definedtobeVARCHAR2
（1）,andcontainsonerowwithavalueX.
select*fromempwhereename='smith';
norowsselected
select*fromempwherelower（ename）='smith';（查询EMP条件是ENAME里面小写是smith）
selectconcat（'Hello','World'）fromdual;（连接2个字符串函数）
selectsubstr（'HelloWorld',1,5）fromdual;（取字符第一个算起到第5个）
selectlength（'HelloWorld'）fromdual;（统计字符数量）
selectinstr（'HelloWorld','W'）fromdual;（统计W在字符串中的排位）
selectlpad（sal,10,'*'）fromemp;（在EMP表中取10位，10位不满的位数用*代替）
selectrpad（sal,10,'*'）fromemp;（在EMP表中取10位，10位不满的后面位数用*代替）
selecttrim（'H'from'HelloWorld'）fromdual;（删除字符串2边的字符包括空格）
2.Numberfunctions
selectround（45.926,2）,round（45.923,0）,round（45.923,-1）fromdual;（四舍五入）
selecttrunc（45.926,2）,trunc（45.923,0）,trunc（45.923,-1）fromdual;（取数比如后面是2就取45.92）
selectmod（1600,300）fromdual;
3.Datefunctions
ArithmeticwithDates
Addorsubtractanumbertoorfromadateforaresultantdatevalue.
Subtracttwodatestofindthenumberofdaysbetweenthosedates.
Addhourstoadatebydividingthenumberofhoursby24.
selectsysdatefromdual;
selectsysdate+1fromdual;
selectsysdate-1fromdual;
selectename,hiredate,（sysdate-hiredate）/365fromemp;
selectsysdate+24/24,sysdate+33/24fromdual;
date+datenotallowed
DateFunctions
months_between（date1,date2）=（date1-date2）
selectmonths_between（'01-SEP-95','11-JAN-94'）fromdual;
add_months（date,number）=（date.month+number）
selectsysdateastoday,add_months（sysdate,1）asnext_month,
add_months（sysdate,-1）aslast_monthfromdual;
next_day（date,'day'）
selectnext_day（'01-SEP-95','FRIDAY'）fromdual;
last_day（date）
selectlast_day（'01-FEB-95'）fromdual;
round（date,'format'）
selectround（sysdate,'month'）fromdual;
selectround（sysdate,'year'）fromdual;
selecttrunc（sysdate,'month'）fromdual;
selecttrunc（sysdate,'year'）fromdual;
4.conversionfunctions
Datatypeconversion
Implicitdatatypeconversion.
select*fromdeptwheredeptno（typenumber）='10';
selectenamefromempwhereempno='7788';
Explicitdatatypeconversion
a）to_char（date,'format_model'）
format_model:
Mustbeenclosedinsinglequotationmarksandiscasesensitive.
Canincludeanyvaliddateformatelement.
Hasanfmelementtoremovepadd
升级会员 