oracle 基础知识

1．Oracle体系结构1．逻辑结构表空间、表、索引、视图回滚段、角色、包、存储过程序列、权限等2．物理结构控制文件(ControlFile)数据文件(DataFile)日志文件(RedoLogFile)参数文件3．内存结构(SGA)sharedpool(共享池)，databasebuffercache(数据缓冲区),redologbuffer(重做日志缓冲区)SGA=share_pool_size+db_block_size*db_block_buffers+log_buffers4．transaction(一组动作的集合)交易事务ACID5．后台进程PMON，LCLN，RECO，SMON，DBWR，LGWR，CKPT，ARCHPMON做程序的清洁工作，处理一些不正常退出的事件．SMON做系统的清洁工作，执行系统出错后自动恢复工作．LCKNOracle系统表级或行级加锁的进程.RECO恢复进程．DBWR数据写进程LGWR日志文件写的进程CKPT检测点ARCH归档方式备份进程6．分析一个SQL语句是怎样在Oracle内部工作的。A、用户发出SQL请求，打开游标；B、把SQL语句语法分析，执行计划，数据字典等信息存入内存中共享池内；C、从数据文件中把相关数据块读入数据缓冲区；D、做相应操作，若做修改，先加上行级锁，经确认后，把改过前后记录内容存入重做日志缓冲区内；E、返回结果给用户，关闭游标。7.存储结构1、Oracle数据存储单位a、block数据块：2k最小的I-O单位，伴随database产生而产生，不可变b、extent一组连续的数据块：是用户所能分配存储的最小单位c、segment段：有共同结构的一个或几个区域(extent)d、tablespace表空间：一组物理数据的逻辑组合。e、file文件：属于某个表空间的物理文件f、database数据库：一组表空间所构成的逻辑的可共享的数据。2．存取规则1、在对象级的存储参数设置值覆盖表空间级的设置2、未在对象级设置存储参数，由表空间级数设置决定3、未在表空间级设置存储参数，由Oracle数据库级参数设置决定4、若存储参数改变后，新的选项只针对未分配的extents有效。默认用户internal/oraclesys/change_on_installsystem/managerscott/tigersysman/oem_temp默认表空间CWMLITE用于联机分析处理（OLAP）DRSYS用于存放与工作空间设置有关的信息EXAMPLE实例表空间，存放实例信息INDEX索引表空间，存放数据库索引信息SYSTEM系统表空间，存放表空间名称、所含数据文件等管理信息TEMP临时表空间，存储临时表TOOLS工具表空间，存放数据库工具软件所需的数据库对象UNDOTBS回滚表空间，存放数据库恢复信息USERS用户表空间，存放用户私有信息备份逻辑备份:exp/imp物理备份:热备份/冷备份2．常用Sql1SQL语言基础1.1两种SQL语言1.1.1DDLaltertable增加表列、重定义表列、更改存储分配analyze收集数据库对象的性能统计值并送入基于代价的优化器altertableaddconstraint在已有的表上增加约束createtable创建表createindex创建索引adropindex删除索引droptable删除表grant将权限或角色授予用户或其他角色truncate删除表中所有行revoke从用户或数据库角色回收权限createtablespacets_testdatafile'X:\ORA8\DATABASE\ts_test_1.dat'size500Mdefaultstorage(initial10knext128kminextents2maxextents121pctincrease10)online;droptablespaceTS_TESTincludingcontents;altertablespacets_testadddatafile'd:\oracle8\oradata\ts_test_02.dbf'size200M;修改数据文件的大小alterdatabasedatafile'd:\oracle8\oradata\ts_test_02.dbf'resize500M;1.1.2DMLinsert增加数据行到表delete从表中删除数据行update更改表中数据select从表或视图中检索数据行commitwork把当前事务所做的更改永久化(写入磁盘)rollback作废上次提交以来的所有更改1.2SQL语句入门1.2.1创建语句createtableAA10(AAA100VARCHAR2(20)notnull,AAA101VARCHAR2(100),AAA102VARCHAR2(20)notnull,AAA103VARCHAR2(120),AAA104CHAR(1),AAA105VARCHAR2(50),AAA106VARCHAR2(50),AAA107VARCHAR2(50),BAE003DATE,AAA108NUMBER(5))tablespaceSYSTEM；数据类型说明char(size)存放定长字符数据，最长2000个字符nchar(size)与char型类似，不过其最大长度由数据库所用的字符集(如AmericanEnglish)决定varchar2(size)存放可变长字符数据，最大长度为4000字符nvarchar(size)类似于varchar2Varchar目前等同于charnumber(l,d)存放数值型数据，l代表总位数，d代表小数点后位数(4,2)的最大值是99.99。Blob二进制大对象，其最大长度为4GBraw(size)纯(Raw)二进制数据，最大长度为2000字节Date存放日期，范围从公元前4712年的1月1日到公元后9999年的12月31日Long存放可变长字符数据，最大为2GB(gigabytes)1.2.2描述语句describeaa10--得到表aa10的相关信息在SQL*Plus中，只有describe命令可简写为desc。1.2.3插入语句insertinto-----------Oracle数据库响应1rowcreated.1.2.4查询语句select*fromaa10where--------1.2.5where子句操作符作用样例=相等;!=不相等^=同!=;同!=小于;大于=小于或等于;=大于或等于;In等于括号内任一成员select*fromAA10whereAAA100in(’’,’’)；notin不等于括号内任一成员select*fromAA10whereAAA100notin(’’,’’)；betweenAandB大于等于A与小于等于Bselect*fromAA10whereAAA100between1and50；notbetweenAandB不大于等于A与小于等于Bselect*fromaa10whereaaa100notbetween1and50；like’%A%’包括给定字串select*fromaa10whereaaa100like’%A%’；1.2.6Orderby子句降序：select*fromAA10orderby---desc;升序：select*fromAA10orderby---;在orderby子句中未指定升序或降序时，Oracle按升序排列1.3数值型数据SQL语句中，若无法指定具体的数据表用于该语句，就可以使用dual表。函数返回值样例显示ceil(n)大于等于数值n的最小整数selectceil(10.6)fromdual；11floor(n)小于等于数值n的最大整数selectfloor(10.6)fromdual；10mod(m,n)m除以n的余数，若n=0，则返回mselectmod(7,5)fromdual；2power(m,n)m的n次方selectpower(3,2)fromdual；9round(n,m)将n四舍五入，保留小数点后m位Selectround(14.5678,2)fromdual；14.57sign(n)n=0返回0;n0返回1;n0返回-1selectsign(12)fromdual；1sqrt(n)n的平方根selectsqrt(25)fromdual；5SQL*Plus的帮助实用工具存放在OracleDocumentation内。SQLhelpfunctions1.4字符型数据表7-8常用字符函数函数返回值样例显示initcap(char)把每个字符串的第一个字符换成大写Selectinitcap(’mr.teplow’)fromdual；Mr.Teplowlower(char)整个字符串换成小写Selectlower(’Mr.FrankTownson’)fromdual；mr.terrybeaker;大写UPPERreplace(char,str1,str2)字符串中所有str1换成str2selectreplace(’Scott’,’S’,’Boy’)fromdual；Boycottsoundex(char)字符串的语音表示，可查找发音相似拼写不同的字符串substr(char,m,n)取出从m字符开始的n个字符的子串selectsubstr(’ABCDEF’,2,1)fromdual；CDlength(char)求字符串的长度selectlength(’Anderson’)fromdual；8用两竖线“||”表示并置运算符。select’ABC’||’DEF’fromdual;，返回文本ABCDEF。1.5日期型数据1.5.1日期型函数函数返回值样例显示Sysdate当前日期和时间Selectsysdatefromdual；Last_day本月最后一天Selectlast_day(sysdate)fromdual；Add_months(d,n)当前日期d后推n个月Selectadd_months(sysdate,2)fromdual；Months_between(f,s)日期f和s间相差月数Next_day(d,day)d后第一周指定的day日期Selectnext_day(sysdate,’Monday’)fromdual；1.5.2特殊格式的日期型数据格式返回值样例显示Q季度，1～3月为第一季度Selectto_char(sysdate,’Q’)fromdual；2表示第二季度①MM月份数Selectto_char(sysdate,’MM’)fromdual；12表示12月RM月份的罗马表示Selectto_char(sysdate,’RM’)fromdual；IV表示4月Month用9个字符长度表示的月份名Selectto_char(sysdate,’Month’)fromdual；May后跟6个空格表示5月WW当年第几周Selectto_char(sysdate,’WW’)fromdual；W本月第几周Selectto_char(sysdate,’W’)fromdual；2002年10月1日为第1周DDD当年第几天Selectto_char(sysdate,’DDD’)fromdual；DD当月第几天Selectto_char(sysdate,’DD’)fromdual；D周内第几天Selectto_char(sysdate,’D’)fromdual；DY周内第几天缩写Selectto_char(sysdate,’DY’)fromdual；HH或HH1212进制小时数Selectto_char(sysdate,’HH’)fromdual；HH2424小时制Selectto_char(sysdate,’HH24’)fromdual；MI分钟数(0～59)Selectto_char(sysdate,’MI’)fromdual；SS秒数(0～59)Sel