一头扎进oracle11g第十三讲源码及-视频笔录

第二节 安装及工具简OracleOracle11g32Oracle11g64 第三节：PLSQLDeveloper安PlsqlPlsqldeveloper 解决办法 32位oracle客户端instantclient-basic-win32-10.2.0.5，地址 /s/1o6sx7n0解plsqldeveloper第三步：环境变量：TNS_ADMIN：oracle根 NLS_LANG：SIMPLIFIEDCHINESE_CHINA.ZHS16GBK第四节：Oracle11g卸OracleOracle第二 Oracle表及表空Java1234_小官网Java1234群创建表空间：createtablespacets1datafileC:\tablespace\ts1.dbfsize自动扩展大小：createtablespacets2datafileC:\tablespace\ts2.dbf'size50Mautoextendonnext设置最大空间：createtablespacets3datafileC:\tablespace\ts3.dbf'size50Mautoextendonnext10Mmaxsize表空间改名：altertablespacets1renameto删除表空间：droptablespacets2includingcontentsand第二节：Oracle第三节：OracledualDualDual表是sys用户下的一张虚表；提供一些运算和日期操作时候用到selectsysdatefrom第三 Oracle序Java1234_小官网Java1234群createsequence序列名称；指定初始值：startwithxx

当前 nextval下一个MinvalueMinvaluemaxvalue11027Incrementby属性序列增长步长默认值1Cycle设置（了解即可，不使用）；Cache20第四 Oracle表操Java1234_小官网Java1234群3、工资等级表名1工资的2此等级的最低工3此等级的最高工4，工资名1雇23雇员的4雇员的给指定给指定 数据：insertintodept(deptno,dname)全部列数据：insertintodept(deptno,dname,loc) 简写insertintodept update语更新指定数据：更新指定数据：updatedeptsetdname='司法部wheredeptno=50;updatedeptsetdname='司法部',loc='china'wheredeptno=50;delete语删除指定数据：删除指定数据：deletefromdeptwhere删除指定条件的数据：deletefromdeptwhereselect语查询所有：查询所有：select*from指定字段查询：selectename,salfromwhere条件：select*fromempwheresal>=800;select*fromempwheresal>=1500andjob='SALESMAN';Distinct去重复记录；Groupby分组查询：selectjob,count(ename)asnumfromEMPtgroupbyHaving过滤分组：selectjob,count(ename)asnumfromEMPtgroupbyjobhavingcount(ename)>=2;Orderby排序：select*fromemporderbysaldesc;子查询：查询出基本工资大于平均工资的员工：select*fromempwheresal>(selectavg(sal)from联合查询select*fromunionallselect*fromselect*fromselect*fromselect*fromempt,deptdwhere类似：select*fromempeinnerjoindeptdone.deptno=d.deptnoinner左外连接：select*fromempeleftjoindeptdon右外连接：select*fromemperightjoindeptdon《一头扎进Oracle11g第五 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,'0fromlower()返回字符串小写：selectlower(userName)fromt_user3;upper()返回字符串大写：selectupper(userName)fromt_user3;initcap()单词首字符大写：selectinitcap(userName)fromt_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(userName)fromt_user3;trim()删除两侧空格:selecttrim(userName)fromt_user3;concat()串联字符串：selectconcat(userName,password)fromt_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=1round()四舍五入：selectround(n1,2)fromt_numberwhereid=1;ceil()向上取整：selectceil(n1)fromt_numberwhereid=2;floor向下取整：selectfloor(n1fromt_numberwhereid=2;Mod()取模：selectmod(5,3)fromdual;Sign()正负性：selectsign(n1fromt_numberwhereid=1;求平方根：selectsqrt(9)fromPower()求乘方：selectpower(2,3)fromdual;To_char()0、9、，、$、FM、L、Cselectto_char(123.45,'0000.000')fromdual;selectto_char(123.45,'9999.999')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;Datetimestamp（时间戳Date包含信century（世纪信息）yearmonthdayhourminutesecond 一般用于日期时间要求非常精确的情况，精确到毫秒insertintot_date下面重点讲date类型的常用selectsysdatefromdual;Add_monthsselectadd_months(d1,2)fromt_datewhereupdatet_datesetd3=to_date('2016-12-2018:31:34','YYYY-MM-DDHH24:MI:SS')whereMonths_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;selectextract(secondfromsystimestamp)fromselectto_char(d1,'YYYY-MM-DD')fromt_datewhereselectto_char(d1,'YYYY-MM-DDHH24:MI:SS')fromt_datewhereMax求最大值：selectmax(sal) Min求最小值：selectmin(sal) fromemp;Avg求平均值：selectavg(sal) fromemp;Sum求和：selectsum(sal) fromemp;Count统计记录数：select fromempNvl空值处理：selectename,nvl(sal,0fromemp;Oracle分页：select*from(selecta.*,rownumrnfrom(select*fromemp)Awhererownum<=10whereOracle中的运算：select2-1fromdual;select2*1fromdual;select2/1fromBetweenandselect*fromempwheresalbetween900and1500;select*fromempwheresal>=900andInselectename,hiredatefromempwhereenamein(selectdistinctenamefromLikeselect*fromempwhereenamelike'%M%'select*fromempwhereenamelike'M%'select*fromempwhereenamelike'_M%'第六 Oracle视Java1234_小官网Java1234群视图是虚表，没有具体物理数据，是通过实体表的一种计视图是虚表，没有具体物理数据，是通过实体表的一种计 逻辑。主要就是为了方便和数据安全CreateCreateviewascreateviewv_emp1asselectename,jobfrom查询：select*from select*from查询：select*from select*fromv_emp1whereenamelike修改：updatev_emp1setjob='销售where添加：insertintov_emp2values('2222','sb2','技术删除：deletefromv_emp2whereCreateCreateviewaswithreadonly第七 Oracle约Ja