MLDN_oracle课堂笔记(全)

1Oracle22.112.21Oracle2Oracle3sqlplusw1MLDN010-5128334633.1OracleOracleOracleOracleOracleIBMOracleLarryEllisonOracleBruceScottOraclePointBase2MLDN010-512833463.2OracleOracle10gOracleOracle8Oracle8iiInternetOracleOracle9iOracle9i8iOracle10ggbaiduOracle10ginstallsetup.exeOracleOracleD3MLDN010-51283346mldnoracleadmin4MLDN010-512833465MLDN010-51283346scotttigersystemmanagersyschange_on_installEMOracleOracle6MLDN010-51283346OracleOraDb10g_home1TNSListenerOracleServiceMLDNOracleServiceOracleSQLPlus3.3SQLPlusSQLPlusOracleSqlplussqlplussqlplusw7MLDN010-51283346SELECT*FROMemp;emp8MLDN010-51283346sqlpluswsetlinesize;oraclesetpagesize;9MLDN010-51283346sqpluswsqlpluswsqlpluswed@[e-mail]@*.sql*.sqlsqlpluswsyssystemconn/[ASSYSDBA|SYSOPER]sysASSYSDBAconnsys/change_on_installassysdba;SQLSELECT*FROMemp;SELECT*FROMemp*1:ORA-00942:empempscottsysempscott.emp10MLDN010-51283346showuser;SELECT*FROMtab;descNUMBER44VARCHAR21010DATENUMBER(7,2)257sqlplusw/11MLDN010-5128334612MLDN010-5128334641Oracle2Oracle3Oraclesqlplusw1SQL2scott2.1emp1MLDN010-512833462MLDN010-51283346EMPNo.1EMPNONUMBER(4)2ENAMEVARCHAR2(10)3JOBVARCHAR2(9)4MGRNUMBER(4)5HIREDATEDATE6SALNUMBER(7,2)7COMMNUMBER(7,2)8DEPTNONUMBER(2)2.2deptdeptNo.1DEPTNONUMBER(2)2DNAMEVARCHAR2(14)3LOCVARCHAR2(13)2.3SALGRADESALGRADENo.1GRADENUMBER2LOSALNUMBER3HISALNUMBER2.4BONUS3MLDN010-51283346BONUSNo.1ENAMEVARCHAR2(10)2JOBVARCHAR2(9)3SALNUMBER4COMMNUMBER33.11Oracle2sqlplusw3.212DISTINCT345Oracle4SQLSQL4.1SQLSQLStructuredQueryLanguageSQLANSISQLSQLDMLDataManipulationLanguageDDLDataDefinitionLanguageDCLDataControlLanguage4.2SELECT*|FROM;SELECT*FROMemp;SELECTempno,ename,jobFROMemp;4MLDN010-51283346SELECTempno,ename,jobFROMemp;jobSELECTjobFROMemp;5MLDN010-51283346DISTINCTSELECT{DISTINCT}*|FROM;SELECTDISTINCTempno,jobFROMemp;7369SMITHCLERK6MLDN010-51283346Oracle||'SELECT''||empno||''||ename||''||jobFROMemp;SELECTename,sal*12FROMemp;sal*12SELECTename,sal*12incomeFROMemp;+-*/7MLDN010-512833464.3WHERESELECT{DISTINCT}*|FROM{WHERE(s)}1500sal1500SELECT*FROMempWHEREsal1500;commnullISNOTNULLSELECT*FROMempWHEREcommISNOTNULL;8MLDN010-51283346commnullISNULLSELECT*FROMempWHEREcommISNULL;1500ANDSELECT*FROMempWHEREsal1500ANDcommISNOTNULL;1500ORSELECT*FROMempWHEREsal1500ORcommISNOTNULL;9MLDN010-51283346NOT1500SELECT*FROMempWHERENOT(sal1500ANDcommISNOTNULL);15003000SELECT*FROMempWHEREsal1500ANDsal3000;sal1500sal3000SQLBETWEEN…AND….BETWEENANDSELECT*FROMempWHEREsalBETWEEN1500AND3000;BETWEEN…AND10MLDN010-51283346BETWEEN…ANDsal=1500ANDsal=30001981198111~19811231'SELECT*FROMempWHEREhiredateBETWEEN'1-1-81'AND'31-12-81';BETWEEN…ANDsmithename='smith'SELECT*FROMempWHEREename='smith';smithOracleSELECT*FROMempWHEREename='SMITH';11MLDN010-51283346736974997521OR|-empno=7369ORempno=7499ORempno=7521SELECT*FROMempWHEREempno=7369ORempno=7499ORempno=7521;SQLININ(1,2,…..,n)NOTINNOTIN(1,2,…..,n)SELECT*FROMempWHEREempnoIN(7369,7499,7521);12MLDN010-51283346736974997521SELECT*FROMempWHEREempnoNOTIN(7369,7499,7521);INSMITHALLENKINGSELECT*FROMempWHEREenameIN('SMITH','ALLEN','KING');SELECT*FROMempWHEREenameIN('SMITH','ALLEN','KING','#$#$$##$');SQLLIKELIKELIKE13MLDN010-51283346%_MSELECT*FROMempWHEREenameLIKE'_M%';MMSELECT*FROMempWHEREenameLIKE'%M%';LIKESELECT*FROMempWHEREenameLIKE'%%';14MLDN010-51283346LIKE1981SELECT*FROMempWHEREhiredateLIKE'%81%';6SELECT*FROMempWHEREsalLIKE'%5%';15MLDN010-51283346===SQL!=7369SELECT*FROMempWHEREempno7369;!=SELECT*FROMempWHEREempno!=7369;16MLDN010-512833464.4ORDERBYSQLORDERBYSELECT*FROMemp;ORDERBYSELECT{DISTINCT}*|FROM{WHERE(s)}{ORDERBY1,2ASC|DESC}ASCDESCSELECT*FROMempORDERBYsal;17MLDN010-51283346SELECT*FROMempORDERBYsalASC;DESCSELECT*FROMempORDERBYsalDESC;1018MLDN010-51283346SELECT*FROMempWHEREdeptno=10ORDERBYsalDESC,hiredateASC;SQL4.5function_name(column|expression,[arg1,arg2,…])function_namecolumnexpressionarg1arg2NVLDECODE4.5.119MLDN010-51283346SELECTUPPER('smith')FROMDUAL;upperSELECT*FROMempWHEREename=UPPER('Smith');lower()SELECTLOWER('HELLOWORLD')FROMdual;initcap()SELECTINITCAP('HELLOWORLD')FROMdual;20MLDN010-51283346SELECTINITCAP(ename)FROMemp;||CONCAT()SELECTCONCAT('hello','world')FROMDUAL;21MLDN010-51283346||substr()length()replace()SELECTsubstr('hello',1,3),length('hello'),replace('hello','l','x')FROMDUAL;substr()Oraclesubstr()01|-01Oracle2SELECTename,SUBSTR(ename,LENGTH(ename)-2)FROMemp;substr()SELECTename,SUBSTR(ename,-3,3)FROMemp;22MLDN010-512833464.5.2ROUND()TRUNC()MODSELECTROUND(789.536)FROMdual;ROUND()SELECTROUND(789.536,2)FROMdual;ROUND()SELECTROUND(789.536,-2)FROMdual;23MLDN010-51283346TRUNC()ROUND()TRUNC()TRUNC()SELECTTRUNC(789.536)FROMDUAL;TRUNC()SELECTTRUNC(789.536,2)FROMDUAL;SELECTTRUNC(789.536,-2)FROMDUAL;24MLDN010-51283346MOD()SELECTMOD(10,3)FROMDUAL;4.5.3Oracle–=+=–=10OraclesysdateSELECTSYSDATEFROMDUAL;–=/7=SELECTempno,ename,ROUND((SYSDATE-hiredate)/7)FROMemp;25MLDN010-51283346OracleMONTHS_BETWEEN()ADD_MONTHS()NEXT_DAY()LAST_DAY()MONTHS_BETWEEN()SELECTempno,ename,MONTHS_BETWEEN(sysdate,hiredate)FROMemp;ROUND()26MLDN010-51283346ADD_MONTHS()SELECTADD