Oracle架构基础知识


Oracle Oracle Oracle Oracle 突击训练营第一天 基础知识篇 一、Oracle Oracle Oracle Oracle 架构基础知识 111 1 、物理结构((( ( 由以下666 6 部分组成))) ) (1)(1)(1) (1) 控制文件 SQL>SQL>SQL> SQL> selectselectselect select *** * fromfromfrom from v$controlfile;v$controlfile;v$controlfile; v$controlfile; 控制文件是一个二进制文件,用来描述数据库的物理结构,一个数据库只需要*** * 一个控制文件,控制文件的内容包括: 数据库名及数据库唯一标识 数据文件和日志文件标识 数据库恢复所需的同步信息,即检查点号 (2)(2)(2) (2) 数据文件SQL>SQL>SQL> SQL> selectselectselect select *** * fromfromfrom from v$datafile;v$datafile;v$datafile; v$datafile; *数据文件用来存储数据库的数据,如表、索引等。读取数据时,系统首先从 数据库文件中读取数据,并存储到SGASGASGA SGA 的数据缓冲区中。这是为了减少I/OI/OI/O I/O ,如 果读取数据时,缓冲区中已经有要读取的数据,就不需要再从磁盘中读取了。 存储数据时也是一样,事务提交时改变的数据先存储到内存缓冲区中,再由 oracleoracleoracle oracle 后台进程DBWRDBWRDBWR DBWR 决定如何将其写入到数据文件中。 (3)(3)(3) (3) 日志文件SQL>SQL>SQL> SQL> selectselectselect select *** * fromfromfrom from v$logfile;v$logfile;v$logfile; v$logfile; (4)(4)(4) (4) 参数文件 (5)(5)(5) (5) 归档文件 (6)(6)(6) (6) 口令文件组成 222 2 、逻辑结构(表空间、段、区、块组成) 【表空间】是数据库中的基本逻辑结构,一系列数据文件的集合。 表空间(tablespace)(tablespace)(tablespace) (tablespace) 是数据库的逻辑划分,,, , 每个数据库至少有一个表空间,,, , 叫 做系统表空间(system(system(system (system 表空间).).). ). 一个表空间只能属于一个数据库。 表空间的特性::: : (1)(1)(1) (1) 控制数据库数据磁盘分配 (2)(2)(2) (2) 限制用户在表空间中可以使用的磁盘空间大小 (3)(3)(3) (3) 表空间具有 online,online,online, online, offline,offline,offline, offline, readonly,readonly,readonly, readonly, readwritereadwritereadwrite readwrite 属性 【段】是对象在数据库中占用的空间... . 【区】是为数据一次性预留的一个较大的存储空间... . 【块】ORACLEORACLEORACLE ORACLE 最基本的存储单位,在建立数据库的时候指定 二、OracleOracleOracle Oracle 常用函数: 1 字符函数: (1)ascii(char) 返回字符所对应的ascii 值如果是字符串就返回首字母的ascii 的值 (2)chr(数字[using nchar_cs])返回ascii 所对应的字符值 (3)concat(string,string)字符串拼接 (4)substr(string,n[,m])返回字符串的一部分,n 从第几个开始取,m 取几个 select substr('ABCDEFG',3,4) "Substring" from dual; select substr('ABCDEFG',-5,4) "Substring" from dual; (5)initcap(string)将字符串中每个单词首字母改为大写 (6)instr(string1,string2[,n[,m]])查子串,在string1 中搜索string2 并返回开始位置 n 指定从shtring1 中哪个位置开始查找,默认为1,负数从右边开始找 m 指定想要找到该字符串第几次出现位置,默认为1 select instr('abcddef','d',3,2) from dual; select instr('CORPORATE FLOOR','OR',-3, 2) from dual; (7)length(string)返回字符串中的字符数 (8)lower(string)将所有字符转换成小写 (9)upper(string)将所有的字符串转为大写 (10)replace(string1,string2[,string3])搜索字符串并用一个子字符串替代另一个字符 串,也可以用于删除出现子字符串 select replace('JACK and JUE','J','BL')"Changes" from dual; (11)lpad(string1,n,[,string2])在string1 前边加空格直到string1 长度为n 为止, 可以指定填充内容string2 就是要指定内容 select lpad(first_name,10,'*') from s_emp; (12)rpad(string1,n,[,string2])同lpad 相反 (13)trim(string)去字符串两边空格 (14)ltrim(string1[,string2])从字符串左边删除指定字符,string2 默认为单个空格 select ltrim(' dd ') from dual; select ltrim('*dd*','*') from dual; (15)ltrim(string1[,string2])从字符串右边删除指定字符,string2 默认为单个空格 (16)fm 压缩空格和前导0 2.数值函数:对数值作四舍五入 round(m,n) trunc(m,n) mod(m,n) 取余如果n 为0,返回m; ROUND(45.923, 2) 45.92 ROUND(45.923, 0) 46 ROUND(45.923, -1) 50 TRUNC(45.923, 2) 45.92 TRUNC(45.923) 45 TRUNC(45.923, -1) 40 mod(11,3) 2 mod(11,0) 11 5 日期函数:日期类型: 一个日期一定包含世纪,年,月日,时,分,秒日期是格式敏感的 (1)sysdate 函数: 获得系统日期默认日期格式:'DD-MON-RR' alter session set nls_date_format='YYYY mm dd hh24:mi:ss'; (2)months_between(d1,d2)返回两个日期之间的月数 months_between('01-SEP-95','11-JAN-94') 隐式类型转换 (3)add_months(d,n)在日期d 上添加几个月,n 为负数就是减几个月 add_months('11-JAN-94',6) (4)next_day(d,string)计算下一次出现的特定一周的某一天 d 指定日期值,string 一周中的某一天的名字 next_day('12-DEC-07','FRIDAY') 从这个日期开始,第一个星期五 (5)last_day(d)返回日期d 所在的月份中对应最后一天的日期 (6)round(d,[,fmt])将日期/时间值取舍为最近的日期/时间单位,d 指定的日期,fmt 指定 日期格式元素 三... . 常用的数据库字段类型如下: OracleOracleOracle Oracle 数据库字段 字段类型 中文说明 限制条件 其它说明 CHAR 固定长度字符串 最大长度2000 bytes VARCHAR2 可变长度的字符串 最大长度4000 bytes 可做索引的最大长度749 NCHAR 根据字符集而定的固定长度 字符串 最大长度2000 bytes NVARCHAR2 根据字符集而定的可变长度 字符串 最大长度4000 bytes DATE 日期(日-月-年) DD-MM-YY(HH-MI-SS) 经过严格测试,无千虫问题 LONG 超长字符串 最大长度2G(231-1) 足够存储大部头著作 RAW 固定长度的二进制数据 最大长度2000 bytes 可存放多媒体图象声音等 LONGRAW 可变长度的二进制数据 最大长度2G 同上 BLOB 二进制数据 最大长度4G CLOB 字符数据 最大长度4G NCLOB 根据字符集而定的字符数据 最大长度4G BFILE 存放在数据库外的二进制数 据 最大长度4G ROWID 数据表中记录的唯一行号 10 bytes ********.****.****格式,*为0或1 四、常用SQLSQLSQL SQL 语句 SQLSQLSQL SQL 分类: DDLDDLDDL DDL ——— — 数据定义语言(CREATE(CREATE(CREATE (CREATE ,ALTERALTERALTER ALTER ,DROPDROPDROP DROP ,DECLARE)DECLARE)DECLARE) DECLARE) DMLDMLDML DML ——— — 数据操纵语言(SELECT(SELECT(SELECT (SELECT ,DELETEDELETEDELETE DELETE ,UPDATEUPDATEUPDATE UPDATE ,INSERT)INSERT)INSERT) INSERT) DCLDCLDCL DCL ——— — 数据控制语言(GRANT(GRANT(GRANT (GRANT ,REVOKEREVOKEREVOKE REVOKE ,COMMITCOMMITCOMMIT COMMIT ,ROLLBACK)ROLLBACK)ROLLBACK) ROLLBACK) 1.1.1. 1. 说明:创建数据库 CREATECREATECREATE CREATE DATABASEDATABASEDATABASE DATABASE database-namedatabase-namedatabase-name database-name 222 2 、说明:删除数据库 dropdropdrop drop databasedatabasedatabase database dbnamedbnamedbname dbname 333 3 、说明:备份sqlsqlsql sql serverserverserver server --------- --- 创建 备份数据的 devicedevicedevice device USEUSEUSE USE mastermastermaster master EXECEXECEXEC EXEC sp_addumpdevicesp_addumpdevicesp_addumpdevice sp_addumpdevice 'disk','disk','disk', 'disk', 'testBack','testBack','testBack', 'testBack', 'c:\mssql7backup\MyNwind_1.dat''c:\mssql7backup\MyNwind_1.dat''c:\mssql7backup\MyNwind_1.dat' 'c:\mssql7backup\MyNwind_1.dat' --------- --- 开始 备份 BACKUPBACKUPBACKUP BACKUP DATABASEDATABASEDATABASE DATABASE pubspubspubs pubs TOTOTO TO testBacktestBacktestBack testBack 444 4 、说明:创建新表 createcreatecreate create tabletabletable table tabname(col1tabname(col1tabname(col1 tabname(col1 type1type1type1 type1 [not[not[not [not null]null]null] null] [primary[primary[primary [primary key],col2key],col2key],col2 key],col2 type2type2type2 type2 [not[not[not [not null],..)null],..)null],..) null],..) 根据已有的表创建新表: AAA A :createcreatecreate create tabletabletable table tab_newtab_newtab_new tab_new likelikelike like tab_oldtab_oldtab_old tab_old ((( ( 使用旧表创建新表))) ) BBB B :createcreatecreate create tabletabletable table tab_newtab_newtab_new tab_new asasas as selectselectselect select col1,col2col1,col2col1,col2 col1,col2 ……… … fromfromfrom from tab_oldtab_oldtab_old tab_old definitiondefinitiondefinition definition onlyonlyonly only 555 5 、说明:删除新表dropdropdrop drop tabletabletable table tabnametabnametabname tabname 666 6 、说明:增加一个列 AlterAlterAlter Alter tabletabletable table tabnametabnametabname tabname addaddadd add columncolumncolumn column colcolcol col typetypetype type 注:列增加后将不能删除。DB2DB2DB2 DB2 中列加上后数据类型也不能改变,唯一能改变的 是增加varcharvarcharvarchar varchar 类型的长度。 777 7 、说明:添加主键: AlterAlterAlter Alter tabletabletable table tabnametabnametabname tabname addaddadd add primaryprimaryprimary primary key(col)key(col)key(col) key(col) 说明:删除主键: AlterAlterAlter Alter tabletabletable table tabnametabnametabname tabname dropdropdrop drop primaryprimaryprimary primary key(col)key(col)key(col) key(col) 888 8 、说明:创建索引:createcreatecreate create [unique][unique][unique] [unique] indexindexindex index idxnameidxnameidxname idxname ononon on tabname(coltabname(coltabname(col tabname(col ……… … .).).) .) 删除索引:dropdropdrop drop indexindexindex index idxnameidxnameidxname idxname 注:索引是不可更改的,想更改必须删除重新建。 999 9 、说明:创建视图:createcreatecreate create viewviewview view viewnameviewnameviewname viewname asasas as selectselectselect select statementstatementstatement statement 删除视图:dropdropdrop drop viewviewview view viewnameviewnameviewname viewname 101010 10 、说明:几个简单的基本的sqlsqlsql sql 语句 NROWID 二进制数据表中记录的唯一 行号 最大长度4000 bytes NUMBER(P,S) 数字类型 P为整数位,S为小数位 DECIMAL(P,S) 数字类型 P为整数位,S为小数位 INTEGER 整数类型 小的整数 FLOAT 浮点数类型 NUMBER(38),双精度 REAL 实数类型 NUMBER(63),精度 选择:selectselectselect select *** * fromfromfrom from table1table1table1 table1 wherewherewhere where 范围 插入:insertinsertinsert insert intointointo into table1(field1,field2)table1(field1,field2)table1(field1,field2) table1(field1,field2) values(value1,value2)values(value1,value2)values(value1,value2) values(value1,value2) 删除:deletedeletedelete delete fromfromfrom from table1table1table1 table1 wherewherewhere where 范围 更新:updateupdateupdate update table1table1table1 table1 setsetset set field1=value1field1=value1field1=value1 field1=value1 wherewherewhere where 范围 查找:selectselectselect select *** * fromfromfrom from table1table1table1 table1 wherewherewhere where field1field1field1 field1 likelikelike like ’’’ ’ %value1%%value1%%value1% %value1% ’’’ ’ ---like---like---like ---like 的语法很精妙,查 资料!!! ! 排序:selectselectselect select *** * fromfromfrom from table1table1table1 table1 orderorderorder order bybyby by field1,field2field1,field2field1,field2 field1,field2 [desc][desc][desc] [desc] 总数:selectselectselect select countcountcount count *** * asasas as totalcounttotalcounttotalcount totalcount fromfromfrom from table1table1table1 table1 求和:selectselectselect select sum(field1)sum(field1)sum(field1) sum(field1) asasas as sumvaluesumvaluesumvalue sumvalue fromfromfrom from table1table1table1 table1 平均:selectselectselect select avg(field1)avg(field1)avg(field1) avg(field1) asasas as avgvalueavgvalueavgvalue avgvalue fromfromfrom from table1table1table1 table1 最大:selectselectselect select max(field1)max(field1)max(field1) max(field1) asasas as maxvaluemaxvaluemaxvalue maxvalue fromfromfrom from table1table1table1 table1 最小:selectselectselect select min(field1)min(field1)min(field1) min(field1) asasas as minvalueminvalueminvalue minvalue fromfromfrom from table1table1table1 table1 111111 11 、说明:随机取出101010 10 条数据 selectselectselect select toptoptop top 101010 10 *** * fromfromfrom from tablenametablenametablename tablename orderorderorder order bybyby by newid()newid()newid() newid() 121212 12 、说明:随机选择记录 selectselectselect select newid()newid()newid() newid() 131313 13 、说明:删除重复记录 DeleteDeleteDelete Delete fromfromfrom from tablenametablenametablename tablename wherewherewhere where ididid id notnotnot not ininin in (select(select(select (select max(id)max(id)max(id) max(id) fromfromfrom from tablenametablenametablename tablename groupgroupgroup group bybyby by col1,col2,...)col1,col2,...)col1,col2,...) col1,col2,...) 141414 14 、说明:列出数据库里所有的表名 selectselectselect select namenamename name fromfromfrom from sysobjectssysobjectssysobjects sysobjects wherewherewhere where type='U'type='U'type='U' type='U' 151515 15 、说明:列出表里的所有的 selectselectselect select namenamename name fromfromfrom from syscolumnssyscolumnssyscolumns syscolumns wherewherewhere where id=object_id('TableName')id=object_id('TableName')id=object_id('TableName') id=object_id('TableName') 五、数据字典 用户表:由用户创建,包含用户的内容 数据字典:由系统建立,包含数据库的信息 前缀::: : USER_USER_USER_ USER_ :由用户创建,,, , 显示用户拥有的所有对象。 ALL_ALL_ALL_ ALL_ :由受权的用户访问,,, , 用户可以访问的对象名。 DBA_DBA_DBA_ DBA_ :由受了DBADBADBA DBA 权限的人访问,显示数据库的所有对象。 V$V$V$ V$ :由受了DBADBADBA DBA 权限的人访问,显示数据字典数据库服务器性能信息。通常是DBADBADBA DBA 用于显示系统的统计表和动态性能表。 数据字典 DICTIONARYDICTIONARYDICTIONARY DICTIONARY 数据字典提供用户可以访问的数据字典表和视图的描述。 USER_OBJECTSUSER_OBJECTSUSER_OBJECTS USER_OBJECTS 显示用户拥有的对象。 USER_VIEWSUSER_VIEWSUSER_VIEWS USER_VIEWS 显示用户拥有的视图。 USER_TABLESUSER_TABLESUSER_TABLES USER_TABLES 显示用户拥有的表。 ALL_TABLESALL_TABLESALL_TABLES ALL_TABLES 显示用户可以访问的表。 ALL_VIEWSALL_VIEWSALL_VIEWS ALL_VIEWS 显示用户可以访问的视图。 USER_CATALOGUSER_CATALOGUSER_CATALOG USER_CATALOG 显示用户拥有的所有表,视图,同义词和序列。 USER_CONS_COLUMNSUSER_CONS_COLUMNSUSER_CONS_COLUMNS USER_CONS_COLUMNS 显示带约束的列。 DBA_CONS_COLUMNSDBA_CONS_COLUMNSDBA_CONS_COLUMNS DBA_CONS_COLUMNS 显示数据库里的所有表,视图和同义词。 USER_TAB_PRIVS_MADEUSER_TAB_PRIVS_MADEUSER_TAB_PRIVS_MADE USER_TAB_PRIVS_MADE :本用户赋给别的用户赋予权限的表 USER_TAB_PRIVS_RECDUSER_TAB_PRIVS_RECDUSER_TAB_PRIVS_RECD USER_TAB_PRIVS_RECD :其他用户给本用户赋予权限的表 USER_COL_PRIVS_MADEUSER_COL_PRIVS_MADEUSER_COL_PRIVS_MADE USER_COL_PRIVS_MADE :本用户赋给别的用户赋予权限的字段 USER_COL_PRIVS_RECDUSER_COL_PRIVS_RECDUSER_COL_PRIVS_RECD USER_COL_PRIVS_RECD :其他用户给本用户赋予权限的字段 ROLE_SYS_PRIVS:ROLE_SYS_PRIVS:ROLE_SYS_PRIVS: ROLE_SYS_PRIVS: 有什么系统权限赋给rolerolerole role ROLE_TAB_PRIVS:ROLE_TAB_PRIVS:ROLE_TAB_PRIVS: ROLE_TAB_PRIVS: 有什么关于表的权限赋给rolerolerole role USER_ROLE_PRIVS:roleUSER_ROLE_PRIVS:roleUSER_ROLE_PRIVS:role USER_ROLE_PRIVS:role 和用户的对应表
还剩5页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 15 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

linjy222

贡献于2011-08-08

下载需要 15 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf