一头oracle11g第十二讲源码及笔录

《一头《一头》第一 Oracle简介及安Java1234_小:官网Java1234群Java1234官网Java学习路线图：http: 免费资源汇总：http: Java1234VIP资源汇总 第一节：Oracle简第二节 安装及工具简Oracle11g64 第三节：PLSQLDeveloper安PlsqlPlsqldeveloper 地址 /s/1o6sx7n0解plsqldeveloper_第四节：Oracle11g卸OracleOracle第二 Oracle表及表空Java1234_小:官网Java1234群第一节：Oracle表空'''''表空间改名：altertablespacets1renameto第二节：Oracle数据库第三节：Oracle虚拟表dualselectselectsysdatefrom第三 Oracle序Java1234_小:官网Java1234群第一节：序列简介第二节：创建序列createcreatesequencestartwithnextval第三节：序列属性MinvalueMinvaluemaxvalue11027Incrementby属性序列增长步长默认值1Cycle（了解即可，不使用）；第四 Oracle表操Java1234_小:官网Java1234群第一节 用户默认表介第二节：插入insert语insertintodept第三节：更新update语句更新指定数更新指定数据：updatedeptsetdname='司法部'wheredeptno=50;updatedeptsetdname='司法部',loc=' 'wheredeptno=50;第四节：删除delete语第五节：查询select语查询所有：查询所有：select*fromwhere条件：select*fromempwheresal>=800;select*fromempwheresal>=1500andjob='SALESMAN';Distinct去重复记录；Groupby分组查询：selectjob,count(enameasnumfromEMPtgroupbyHaving过滤分组：selectjob,count(enameasnumfromEMPtgroupbyjobhavingcount(ename)>=2;Orderby排序：select*fromemporderbysaldesc;)select*fromt_user1select*fromselectselect*fromunionselect*fromselect*fromt_user1select*fromselect*fromt_user1select*fromselect*fromempt,deptdwhere类似：select*fromempeinnerjoindeptdone.deptno=d.deptnoinner左外连接：select*fromempeleftjoindeptdon右外连接：select*fromemperightjoindeptdon《一头Oracle 第五 Oracle数据类型及函Java1234_小:官网Java1234群第一节：字符串类型及函数字符类型分3种，char(n)、varchar(n)、varchar2(n)；varchar(n)可变长度字符串，假如长度不足n，右边不会补齐varchar2(n)可变长度字符串 推荐使用，向后兼容性好char(n)VSvarchar2(n) selectlength(userName)ascharlength,length(password)asvarchar2lengthfromt_user3;lpad()向左补全字符串：selectlpad(stuno,6,'0')fromt_user3;rpad()向右补全字符串：selectrpad(stuno,6,'0')fromt_user3;lower()返回字符串小写：selectlower(userName)fromt_user3;upper()返回字符串大写：selectupper(userName)fromt_user3;initcap()单词首字符大写：selectinitcap(userNamefromt_user3;length()返回字符串长度：selectlength(password)fromt_user3;substr()截取字符串：selectsubstr(userName,1,2)fromt_user3;instr()获取字符串出现的位置：selectinstr(password,'23',2,2)fromt_user3;ltrim()删除左侧空格:selectltrim(userName)fromt_user3;rtrim()删除右侧空格:selectrtrim(userNamefromt_user3;trim()删除两侧空格:selecttrim(userName)fromt_user3;concat()串联字符串：selectconcat(userName,passwordfromt_user3;reverse()反转字符串：selectreverse(userName)fromt_user3;第二节：数值类型及函数Precision，scalePrecision代表精度，sacle代表小数位的位数；Precision范围[1,38]scale范围[-84,127]举例：12345.678 Precision是8 scale是3；常用方法abs()求绝对值；selectabs(n1)fromt_numberwhereid=1;round()四舍五入：selectround(n1,2fromt_numberwhereid=1;ceil()向上取整：selectceil(n1)fromt_numberwhereid=2;floor向下取整：selectfloor(n1)fromt_numberwhereid=2;Mod()取模：selectmod(5,3)fromdual;Sign()正负性：selectsign(n1)fromt_numberwhereid=1;Sqrt()求平方根：selectsqrt(9)fromdual;Power()求乘方：selectpower(2,3)fromdual;Trunc()截取：selecttrunc(123.456,2fromdual;To_char()格式化数值：常见的字符匹配有0、9、，、$、FM、L、Cselectto_char(123.45,'0000.000')fromdual;selectto_char(123.45,'9999.999')fromdual;selectto_char(123123,'99,999,999.99')fromdual;selectto_char(123123.3,'FM99,999,999.99')fromdual;selectto_char(123123.3,'$99,999,999.99')fromdual;selectto_char(123123.3,'L99,999,999.99')fromdual;selectto_char(123123.3,'99,999,999.99C')fromdual;第三节：日期类型及函数Date和timestamp（时间戳Datecentury（世纪信息）yearmonthdayhourminutesecond 一般用于日期时间要求非常精确的情况，精确到毫秒级insertintot_date下面重点讲date类型的常用函数selectsysdatefromdual;selectsystimestampfromdual;Add_monthsselectadd_months(d1,2)fromt_datewhereLast_dayselectlast_day(d1)fromt_datewhereMonths_betweenselectmonths_between(d1,d3fromt_datewhereid=1;next_day返回特定日期之后的一周内的日期：selectnext_day(d1,2)fromt_datewhereid=1;Truncselecttrunc(d1,'YYYY')fromt_datewhereid=1;selecttrunc(d1,'MM')fromt_datewhereid=1;selecttrunc(d1,'DD')fromt_datewhereid=1;selecttrunc(d1,'HH')fromt_datewhereid=1;selecttrunc(d1,'MI')fromt_datewhereid=1;Extractselectextract(yearfromsysdate)fromdual;selectextract(monthfromsysdate)fromdual;selectextract(dayfromsysdate)fromdual;selectextract(Hourfromsystimestamp)fromdual;selectextract(minutefromsystimestamp)fromdual;第四节：其他常用处理函数MaxMax求最大值：selectmax(sal)fromempMin求最小值：selectmin(sal)fromempAvg求平均值：selectavg(sal)fromemp;Sum求和：selectsum(sal)fromemp;Count统计记录数：selectcount(ename)fromempNvl空值处理：selectename,nvl(sal,0fromemp;Oracle分页：select*fromselecta.*,rownumrnfromselect*fromempAwhererownum<=10whereOracle中的运算：select2+1fromdual;select2-1fromdual;select2*1fromselect2/1fromBetweenandselect*fromempwheresalbetween900and1500;select*fromempwheresal>=900andsal<=1500;Inselectename,hiredatefromempwhereenamein(selectdistinctenamefromLikeselect*fromempwhereenamelikeselect*fromempwhereenamelike'M%'select*fromempwhereenamelike'_M%'《一头《一头》第六 Oracle视Java1234_小:官网Java1234群第一节：视图简介第二节：创建视图CreateCreateviewascreateviewv_emp1asselectename,jobfrom查询：select*from select*fromv_emp1whereename