Oracle 11g OCP-051 培训笔记

Oracle11gOCP-051培训笔记测试库基础Ip地址192.168.0.88系统登录root，123123，startxOracle数据库默认密码system用户scott密码scott#su–oracle$lsnrctlstart以sysdba身份登录$sqlplus/assysdba启动oralce数据库SQLstartup连接到scott用户SQLconnscott/scott查看当前用户SQLshowuser;查询当前用户下的所有表SQLSELECT*FROMUSER_TABLES;查看表emp的内容SQLSELECT*FROMEMP；复制表emp至emp1SQLcreatetableemp1asselect*fromemp;第一部分：SQL语言基础第一章：Oracle命令类别：第二章：SQL的基本函数第三章：SQL的数据类型第四章、WHERE子句中常用的运算符第五章：分组函数第六章：数据限定和排序第七章：复杂查询（上）：多表连接技术第八章：复杂查询（下）：子查询第二部分：用户及数据库对象第九章：用户访问控制第十章：Oracle的事务和锁第十一章：索引第十二章：约束第十三章：视图第十四章：同义词第十五章：序列第十六章：外部表第三部分：SQL语言的扩展第十七章：INSERT语句总结第十八章：DML和DDL语句的其他用法第十九章通过GROUPBY产生统计报告第二十章：ORACLE分层查询第二十一章：Oracle的Timezone第二十二章：正则表达式第一部分：SQL语言基础第一章、Oracle命令类别：数据操作语言：DML:select;insert;delete;update;merge.数据定义语言：DDL:create;alter;drop;truncate;rename;comment.事务控制语言：TCL:commit;rollback;savepoint.数据控制语言：DCL:grant;revoke.第二章、SQL的基本函数2.1单行函数与多行函数单行函数：指一行数据输入，返回一个值的函数。所以查询一个表时，对选择的每一行数据都返回一个结果。大写转小写SQLselectempno,lower(ename)fromemp;多行函数：指多行数据输入，返回一个值的函数。所以对表的群组进行操作，并且每组返回一个结果。（典型的是聚合函数）SQLselectsum(sal)fromemp;2.2单行函数的几种类型2.2.1字符函数lower('SQLCourse')-----sqlcourse返回小写upper('sqlcourse')-----SQLCOURSE返回大写initcap('SQLcourse')-----SqlCourse返回首字母大写concat('good','string')----goodstring拼接//只能拼接2个字符串substr('String',1,3)----Str从第1位开始截取3位数instr('t#i#m#r#a#n#','#',3)---从第3位起始找#字符在那个绝对位置length('String')----6长度lpad('first',10,'$')左填充rpad(676768,10,'*')右填充replace('JACKandJUE','J','BL')----BLACKandBLUEtrim('m'from'mmtimranm')----timran2.2.2数值函数round对指定的值做四舍五入,round(p,s)s为正数时，表示小数点后要保留的位数，s也可以为负数，但意义不大。round:按指定精度对十进制数四舍五入,如:round(45.923,1),结果,45.9round(45.923,0),结果,46round(45.923,-1),结果,50trunc对指定的值取整trunc(p,s)trunc:按指定精度截断十进制数,如:trunc(45.923,1),结果,45.9trunc(45.923),结果,45trunc(45.923,-1),结果,40mod返回除法后的余数SQLselectmod(100,12)fromdual;MOD(100,12)-----------42.2.3日期函数因为日期在oracle里是以数字形式存储的，所以可对它进行加减运算，计算是以天为单位。缺省格式：DD-MON-RR.可以表示日期范围：（公元前）4712至（公元）9999时间格式SQLselectto_date('2003-11-0400:00:00','YYYY-MM-DDHH24:MI:SS')FROMdual;SQLselectsysdate+2fromdual;//当前时间+2daySQLselectsysdate+2/24fromdual;//当前时间+2hourSQLselect(sysdate-hiredate)/7weekfromemp;//两个date类型差，结果是以天为整数位的实数。MONTHS_BETWEEN//计算两个日期之间的月数SQLselectmonths_between('1994-04-01','1992-04-01')mmfromdual;查找emp表中参加工作时间30年的员工SQLselect*fromempwheremonths_between(sysdate,hiredate)/1230;考点：很容易认为单行函数返回的数据类型与函数类型一致，对于数值函数类型而言的确如此，但字符和日期函数可以返回任何数据类型的值。比如instr函数是字符型的，months_between函数是日期型的，但它们返回的都是数值。ADD_MONTHS//给日期增加月份SQLselectadd_months('1992-03-01',4)amfromdual;LAST_DAY//日期当前月份的最后一天SQLselectlast_day('1989-03-28')l_dfromdual;NEXT_DAY//NEXT_DAY的第2个参数可以是数字1-7，分别表示周日--周六(考点），比如要取下一个星期六，则应该是：SQLselectnext_day(sysdate,7)FROMDUAL;ROUND(p,s)，TRUNC(p,s)在日期中的应用，如何舍入要看具体情况，s是MONTH按30天计，应该是15舍16入，s是YEAR则按6舍7入计算。SQLSELECTempno,hiredate,round(hiredate,'MONTH')ASround,trunc(hiredate,'MONTH')AStruncFROMempWHEREempno=7788;SQLSELECTempno,hiredate,round(hiredate,'YEAR')ASround,trunc(hiredate,'YEAR')AStruncFROMempWHEREempno=7839;2.2.4几个有用的函数1）decode函数和case表达式：实现sql语句中的条件判断语句，具有类似高级语言中的if语句的功能。decode函数源自oracle,case表达式源自sql标准，实现功能类似，decode语法更简单些。decode函数用法：SQLSELECTjob,sal,DECODE(job,'ANALYST',SAL*1.1,'CLERK',SAL*1.15,'MANAGER',SAL*1.20,SAL)REVISED_SALARYFROMemp/case表达式第一种用法：SQLselectjob,sal,casejobwhen'ANALYST'thenSAL*1.1when'CLERK'thenSAL*1.15when'MANAGER'thenSAL*1.20elsesalendREVISED_SALARYfromemp/case表达式第二种用法：SQLselectjob,sal,casewhenjob='ANALYST'thenSAL*1.1whenjob='CLERK'thenSAL*1.15whenjob='MANAGER'thenSAL*1.20elsesalendREVISED_SALARYfromemp/以上三种写法结果都是一样的：JOBSALREVISED_SALARY---------------------------------CLERK800920SALESMAN16001600SALESMAN12501250MANAGER29753570SALESMAN12501250MANAGER28503420MANAGER24502940ANALYST30003300PRESIDENT50005000SALESMAN15001500CLERK11001265CLERK9501092.5ANALYST30003300CLERK13001495case第二种语法比第一种语法增加了搜索功能。形式上第一种when后跟定值，而第二种还可以使用比较符。看一个例子SQLselectename,sal,casewhensal=3000then'高级'whensal=2000then'中级'else'低级'end级别fromemp/ENAMESAL级别------------------------SMITH800低级ALLEN1600低级WARD1250低级JONES2975中级MARTIN1250低级BLAKE2850中级CLARK2450中级SCOTT3000高级KING5000高级TURNER1500低级ADAMS1100低级JAMES950低级FORD3000高级MILLER1300低级2）DISTINCT(去重)限定词的用法：//distinct貌似多行函数，严格来说它不是函数。SQLselectdistinctjobfromemp;//消除表行重复值。JOB---------CLERKSALESMANPRESIDENTMANAGERANALYSTSQLselectdistinctjob,deptnofromemp;//重复值是后面的字段组合起来考虑的JOBDEPTNO-------------------MANAGER20PRESIDENT10CLERK10SALESMAN30ANALYST20MANAGER30MANAGER10CLERK30CLERK203）CHR()函数和ASCII()函数chr()函数将ASCII码转换为字符：ASCII码–》字符ascii()函数将字符转换为ASCII码：字符–》ASCII码在oracle中chr()函数和ascii()是一对反函数。求字符对应的ASCII值SQLselectASCII('A')FROMdual;ASCII('A')----------65SQLselectchr(65)fromdual;C-A4)sys_context获取环境上下文的函数（很有用）scott远程登录selectSYS_CONTEXT('USERENV','IP_ADDRESS')fromdual;--------------------------------------------------------------------------------192.168.0.136SQLselectsys_context('userenv','sid')fromdual;SYS_CONTEXT('USERENV','SID')--------------------------------------------------------------------------------129SQLselectsys_context('userenv','terminal')fromdual;SYS_CONTEXT('USERENV','TERMINAL')-------------------