Oracle知识点总结

1Oracle知识点总结1、Oracle数据库的安装和配置⑴OracleInternet级数据库SQLServer中小企业级数据库Access桌面级数据库⑵Oracle的安装注意：来源和目标的目录不允许有中文或空格⑶Oracle数据库启动时必需开启的后台服务①OracleOrahome90TNSListener使第三方的软件或语言访问②OracleServiceETCOracle的实例CRUD增删改查注意：②中的ETC是你起的实例的名字⑷Oracle的开发工具①DOS界面的开发平台-运行-sqlplus②Oracle本身基于DOS的平台-运行-sqlplusw③OracleEnterpriseManagerConsole④PL/SQLDeveloper7.1.5⑸创建一个表的完整流程①创建一个数数库例子：创建一个数据库ETC,物理文件放在F:\，初始化1m,自增长开启createtablespaceetcdatafile'f:\etc.dbf'2size1mautoextendon;删除表空间droptablespace数据库名称;②为该数据库创建一个帐号和密码语法：createuser用户名称identifiedby密码defaulttablespace数据库名称注意：1、在Oracle中账号和数据库one2one绑定2、Oracle的密码不允许为纯数字或空3、需要在system账号完成动作修改Oracle的账号密码语法：alteruser用户identifiedby新密码③用grant权限to用户为该帐户授权语法：grant权限名称to用户;撤销权限语法：revoke权限名称from用户名;④在该帐号下创建表⑹Oracle中的事务处理3Transacation事务特点：整体提交(commit)整体回滚(rollback)事务的四个特性①原子性不可分割②持久性-当数据整整写入到数据库物理文件中后，该数据被持久化③隔离性-事务之间相互独立互不干扰④一致性-数据安全⑺Oracle中的保留点(还原点)关键字：savepoint使用方法：savepoint名称;如进行保留点回滚rollbacktomark2⑻Oracle备份数据表数据①createtable备份表名称as查询语句;②Oracle备份表结构createtableemp_bakasselect*fromempwhere1=2;2、Oracle的函数⑴function（系统预定义函数）函数和存储过程相比，在于函数必须有返回值，而存储过程只有输出参数。语法：createorreplacefunction函数名称return返回类型4asPL/SQL语句块例子：输入一个员工工号，返回该员工所在部门平均工资createorreplacefunctionfun_demo1(enoemp.empno%type)returnemp.sal%typeasavgsalemp.sal%type;beginselectavg(sal)intoavgsalfromempwhereempno=eno;returnavgsal;end;调用：selectfun_demo1(7788)fromdual;publicintadd(intnum1,intnum2){intres=0;res=num1+num2;returnres;}⑵单行函数(单值函数)：函数有且只有返回1个值|-字符函数substrinstr①字符串连接函数：concat5concat(字符串1,字符串2)Oracle中字符连接操作符号||②字符串对齐函数：lpad-leftpadding左填充/rpad语法：lpad(字符串,预留位置,填充字符)SQL:selectlpad(ename,10,'')fromemp;③字符串截取函数：substr-substring语法：substr(字符串,截取的起始位置,截取个数)substr(字符串,截取的起始位置)SQL:selectsubstr(ename,1,3)fromeemp;④字符查找函数：instr-innerstring语法：instr(字符串,待找的字符,查找的起始位置,出现次数)SQL:selectename,instr(ename,'T',1,1)fromemp;注意：当返回值为0时，字符不存在⑤函数：initcap-initialcaptor语法：initcap(字符串)SQL:selectinitcap(ename)fromemp;⑥函数：length语法：length(字符串)SQL:select*fromempwherelength(ename)=5;⑦函数：lower/upper语法：lower(字符串)SQL:selectlower(ename)fromemp;6|-日期函数sysdateadd_months①函数：sysdateSQL:selectsysdatefromdual;dual-无实际意义，函数测试或其他测试使用②月份差：months_between语法：months_between(日期1,日期2)SQL:select*fromempwheremonths_between(sysdate,hiredate)=144③函数：last_day语法：last_day(日期数据)SQL:select*fromempwherelast_day(hiredate)-2=hiredate;④函数：add_months语法：add_months(时间,添加月份)SQL：selectadd_months(sysdate,3)fromdual;|-转换函数to_charto_date①转换函数to_char语法：to_char(时间数据,制定格式)SQL:selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ssddddday')fromdual;yyyy-mm-dd年月日7hh24:mi:ss时分秒(24)d一周第几天ddd一年的第几天day星期几②to_date:将文本类型数据按照制定格式转换成时间类型数据语法：to_date('具体的时间字符数据','时间字符串格式')to_date('2010-01-19','yyyy-mm-dd')注意：Oracle中的date字段类型及其特殊|-数学函数absceilfloorabs(n)绝对值ceil(n)向上取值ceilling天花板ceil(1.1)-2floor(n)向下取值floor地板floor(2.9)-2mod(m,n)求模取余mod(5,2)-1power(m,n)m的n次方power(2,8)-256round(m,n)四舍五入m待处理数据n精度round(3.1415,2)-3.14trunc(m)整数截取trunc(3.1415)=3sign(m)符号sqrt(m)平方根|-混合函数usernvl①user:返回当前登录账号SQLselectuserfromdual;8②nvl:替空函数语法：nvl(字符串,替换后的数据)注意：替换后的数据必须与该字段类型保持一致SQLselectename,nvl(comm,0)fromemp;⑶分析函数：数据分析和挖掘功能DataMiningrankdense_rank作用：主要用于排名使用函数：rank①语法：rank()over(orderby字段名称)SQL:selectrank()over(orderbysaldesc),ename,salfromemp;若排名相同下一个将跳转SQL:selectdense_rank()over(orderbysaldesc),ename,salfromemp;连续②语法：rank(参数1，参数2)withingroup(orderby字段1，字段2)SQL:selectename,rank(2850,'BLAKE')withingroup(orderbysal,ename)fromemp;⑷分组函数：类似于SQLServer中的聚合函数sumavgcountgroupby分组关键字having条件3、Oracle数据库的查询⑴制定区间查询Oracle中伪列：Oracle为每张数据表(包括用户自定义创建的数据表)都自动创建两个位列分别是：rowid、rownum9通过命令：SQLdesc表名;--查看表结构rowid:由18个字符组成唯一标识每一行rownum:行号⑵介绍问题：使用Oracle分层查询以数据结构显示emp表中的人员关系语法：selectlpad(ename,level*5,'')fromempconnectbypriorempno=mgrstartwithmgrisnull;⑶并集：查询部门10的办事员和部门20的经理关键字：unionallselect*fromempwheredeptno=10andjob='CLERK'unionallselect*fromempwheredeptno=20andjob='MANAGER';注意：unionall不会消除查询结果中的重复数据,union会消除查询结果中的重复记录⑷交集：查询部门10和部门20都有的工作类型关键字：intersectselectjobfromempwheredeptno=10intersectselectjobfromempwheredeptno=20;⑸差集：查询部门30中有，而部门10中没有的工作类型关键字：minusselectjobfromempwheredeptno=3010minusselectjobfromempwheredeptno=10;4、Oracle数据库的数据对象Oracle的数据库对象：用户、表、约束、序列、视图、同义词和索引定义：但凡使用create开头创建的对象称之为数据库对象。⑴锁定/解除用户语法：alteruser用户名称accountlock;注意：该指令一般在system账号下输入解除用户锁定语法：alteruser用户名称accountunlock;SQLalteruserscottaccountunlock;注意：Oracle11g中scott账号是默认锁定的，需要进行解锁处理。⑵用户授权/撤销grant权限名称to用户名称;撤销用户权限revoke权限名称from用户名;⑶表数据字段的类型①数字类型：number语法：number(长度,精度)number(5,2)数字长度为3，精度为2范围：-999.99~999.9911number(5)数字长度为5，精度为0范围：-99999~99999number精度默认为0范围：-32767~32768②字符类型：varchar2可变长度char字符类型语法：varchar2(长度)③日期类型：date④long数据类型：该字段最大存储空间为2GB，该字段不允许添加索引LOB数据类型：该字段最大存储空间为4GB，该字段不允许添加索引CLOB：大字符类型-文章(小说，cnki)BLOB：大二进制类型-图片、音频、视频.....多媒体文件FLOB：文件定位器-内存指针⑷创建数据表的语法结构：createtable表名(字段名称1类型[约束],字段名称1类型[约束],……字段名称n类型[约束])①使用desc表名查看表结构②给表添加字段address和telephone语法：altertable表名add(字段名称类型);12③删除表字段address语法：altertable表名dropcolumn字段名称;④修改表中已有字段的类型语法：altertable表名modify(字段名称新类型);⑤修改表名称语法：rename原表名to新表名;约束完整性约束=准确性+一致性⑸约束的四大分类：①实体完整性约束(行约束)-尽量减少数据表中数据的冗余(重复的数据)技术实施：主键约束、唯一约束语法：altertable表名addprimarykey