Oracle10g数据库基础知识学习笔记.doc

Oracle10g数据库基础知识学习笔记第一课1. 数据库发展概述网状数据库；关系数据库；对象数据库。2. 关系数据库的特点数据完整性，数据安全性。3. 当今流行关系数据库有：Oracle，my sql server，DB2。4. Oracle 10g数据库的安装从Oracle 10g开始，Oracle数据库的安装已经是非常的简单化，安装正常的软件安装步骤即可。5. 实例：指系统中的进程，也指SID。（从网上看到一个比喻可以帮助我们更好的理解，假如一个房间堆满了放文件的大箱子，这些装有文件的大箱子就是数据库，这间装有箱子的房间就是一个实例。）6. 数据库：指文件系统，文件系统包括了：参数文件，控制文件，数据文件，日志文件，其中日志文件又分为：联机重做日志和归档日志。联机重做日志就是不做备份，一直重复的记录；归档日志就是在写满一个日志后，将此日志拷贝到一个固定的目录下保存，在进行一些灾难性的数据恢复时，归档日志可以利用到。7. 使用SQLPLUS登陆：进入到DOS命令集界面，输入：sqlplus回车，再输入“用户名/密码数据库名 as sysdba”。8. 使用sqlplus查看各文件系统结构及状态：（1）.查看控制文件：desc v$controlfile（2）.查看文件状态：select name from v$controlfile （3）.查看数据文件：desc v$datafile（4）.查看日志文件：desc v$logfile注：在sqlplus中，每行命令以“；”作为结束符。9. 体系结构可分为3个类型：物理结构，内存结构，逻辑结构。物理结构图：参数文件控制文件控制文件日志文件日志文件数据文件数据文件内存结构图：SGA（系统全局区）DB buffer数据库高速缓存池大共享区共享池Redo buffer固定SGA其中最重要的是：.DB buffer：数据库高速缓存池：包含了默认缓存池、保持缓存池、再生缓存池。.共享池：包含了：库缓存池、字典缓存池、共享SQL区、PLSQL区。 DB 高速缓存池结构图： 默认缓存池保持缓存池再生缓存池共享池字典缓存区共享SQL区库缓存区PL/SQL区逻辑结构分为：块(block)、盘区(extent)、段(segment)、表空间(tablespace)、数据文件(datafile)。它们的关系是：一个表空间对应多个数据文件；多个段对应一个表空间；一个段对应多个盘区；一个盘区对应多个块；第二课1 Sqlplus介简 Sqlplus是oracle充许用户输入和执行SQL块的工具，8i后可以用Sqlplus启动停止数据库。2 Sqlplus登陆 A管理员登陆模式： sqlplus “sys/密码数据库名 as sysdba” B非管理员登陆模式： sqlplus 用户名/密码数据库名 C实例没打开的情况下登陆 c:sqlplus/nolog Sqlconnect sys/密码 as sysdba3 Oracle命令启动数据库方法（1）startup 正常启动模式；实例，控制文件，数据文件都启动。 （2）Startup mount mount模式；实例，控制文件启动，不启动数据文件。 （3）Starup nomount nomount模式；只启动实例模式，当控制文件丢失时使用。4数据库热备份时不允许进行非归档方式 查看归档方式：sqlarchive log list (1)改归档方式，启用归档日志（只有在mount模式下）：sqlstartup mount Alter datebase archivelog Alter datebase open (2)关闭归档日志 、启动SQL*PLUS以管理身份登录Oracle数据库：SQL connect / as sysdba、关闭数据库实例SQL shutdown immediate、备份数据库：在对数据库做出任何重要的改变之前，建议备份数据库以免出现任何问题。、启动一个新的实例并装载数据库，但不打开数据库：SQL startup mount、禁止自动存档SQL alter system archive log stop;、禁止存档联机重做日志：转换数据库的存档模式。SQL alter database noarchivelog ;、打开数据库：SQL alter database open ;、察看已连接实例的存档信息：SQL archive log list ;数据库日志模式非存档模式自动存档禁用存档终点E:oraclearc最早的联机日志序列50当前日志序列529、更改日志的存档路径; SQL alter system set log_archive_dest_1= location=d:archivelog; 10、重新启动数据库并验证归档是否有效 SQL shutdown immediate SQL startupSQL archive log list; 11、检验数据库归档模式设置是否成功 a)手动切换日志存档，验证数据库日志归档模式及自动存档是否生效SQL alter system switch logfile; b)手动切换日志后，归档目录中会出现日志文件 5Oracle命令关闭数据库方式1.shutdown 当所有用户断开连接后在正常关闭(该方式容易造成挂起) 2.shutdown immediate 迫使每个用户执行完当前的sql语句后立即断开连接 3.shutdown transactional 迫使用户在当前执行完成后断开连接 4.shutdown abort 强性关闭数据库容易造成数据库文件损坏(严重自然灾害下用)关闭数据库最佳选择是：shutdown immediate6查看Sqlplus相关命令help index 所有的sqlplus的基本命令都会显示出来7用&符号可在Sqlplus中设置关量 例子：select * from dept where deptno=&tt;作用：SQL语句放到共享池中进行解析，并缓存，所以用替换变量就可以减轻共享池中库缓存的负担，同时，用替换变量支持多用户同时使用 。8如何在Sqlplus中查看命令的用法,以set命令举例 sql? set9如何在Sqlplus中调用脚本 sql路径文件 举例：sqlc:oraclecreate.sql;10登陆isqlplus方式 浏览器中输入地下：http:/计算机名:5560/isqlplus用用户名登陆，连接标识符是实例名。第三课1 什么是SQL语言 SQL是高级的非过程化编程语言，是沟通数据库服务器和客户端的重要工具，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以，具有完全不同底层结构的不同数据库系统，可以使用相同的SQL语言作为数据输入与管理的 SQL接口。它以记录集合作为操作对象，所有SQL语句接受集合作为输入，返回集合作为输出，这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入，所以SQL语句可以嵌套，这使他具有极大的灵活性和强大的功能，在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的，这也意味着用SQL语言可以写出非常复杂的语句。结构化查询语言(Structured Query Language)最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言，它的前身是SQUARE语言。SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言得到了广泛的应用。如今无论是像Oracle、Sybase、DB2、Informix、SQL Server这些大型的数据库管理系统，还是像Visual Foxpro、PowerBuilder这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。2.SQL语言的分类 SQL语言包含4个部分： 数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句。 数据操作语言(DML)，例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。 数据查询语言(DQL)，例如：SELECT语句。 数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。3SQL函数学习方法 1.巧用PL/SQL的SQL Functions帮助及google、百度等网络工具。 2.在测试数据库上执行各条命令，了解其用法及效果。4需要熟记的常用系统函数(红色部分为需要熟记内容) SQL中的单记录函数Example1.ASCII返回与指定的字符对应的十进制数;SQL select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual; A A ZERO SPACE- - - - 65 97 48 32Example2.CHR给出整数,返回对应的字符;SQL select chr(54740) zhao,chr(65) chr65 from dual;ZH C- -赵 AExample3.CONCAT连接两个字符串;SQL select concat(010-,88888888)|转23 高乾竞电话 from dual;高乾竞电话23Example4.INITCAP返回字符串并将字符串的第一个字母变为大写;SQL select initcap(smith) upp from dual;UPP-SmithExample5.INSTR(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1 被搜索的字符串C2 希望搜索的字符串I 搜索的开始位置,默认为1J 出现的位置,默认为1SQL select instr(oracle traning,ra,1,2) instring from dual; INSTRING- 9Example6.LENGTH返回字符串的长度;SQL select name,length(name),addr,length(addr),sal,length(to_char(sal) from gao.nchar_tst;NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)- - - - - -高乾竞 3 北京市海锭区 6 9999.99 7 Example7.LOWER返回字符串,并将所有的字符小写SQL select lower(AaBbCcDd)AaBbCcDd from dual;AABBCCDD-aabbccddExample8.UPPER返回字符串,并将所有的字符大写SQL select upper(AaBbCcDd) upper from dual;UPPER-AABBCCDD Example9.RPAD和LPAD(粘贴字符)RPAD 在列的右边粘贴字符LPAD 在列的左边粘贴字符SQL select lpad(rpad(gao,10,*),17,*)from dual;LPAD(RPAD(GAO,1-*gao*不够字符则用*来填满Example10.LTRIM和RTRIMLTRIM 删除左边出现的字符串RTRIM 删除右边出现的字符串SQL select ltrim(rtrim( gao qian jing , ), ) from dual;LTRIM(RTRIM(-gao qian jingExample11.SUBSTR(string,start,count)取子字符串,从start开始,取count个SQL select substr3,8) from dual;SUBSTR(-08888888Example12.REPLACE(string,s1,s2)string 希望被替换的字符或变量 s1 被替换的字符串s2 要替换的字符串SQL select replace(he love you,he,i) from dual;REPLACE(H-i love youExample13.SOUNDEX返回一个与给定的字符串读音相同的字符串SQL create table table1(xm varchar(8);SQL insert into table1 values(weather);SQL insert into table1 values(wether);SQL insert into table1 values(gao);SQL select xm from table1 where soundex(xm)=soundex(weather);XM-weatherwetherExample14.TRIM(s from string)LEADING 剪掉前面的字符TRAILING 剪掉后面的字符如果不指定,默认为空格符 Example15.ABS返回指定值的绝对值SQL select abs(100),abs(-100) from dual; ABS(100) ABS(-100)- - 100 100Example16.ACOS给出反余弦的值SQL select acos(-1) from dual; ACOS(-1)-3.1415927Example17.ASIN给出反正弦的值SQL select asin(0.5) from dual;ASIN(0.5)-.52359878Example18.ATAN返回一个数字的反正切值SQL select atan(1) from dual; ATAN(1)-.78539816Example19.CEIL返回大于或等于给出数字的最小整数SQL select ceil(3.1415927) from dual;CEIL(3.1415927)- 4Example20.COS返回一个给定数字的余弦SQL select cos(-3.1415927) from dual;COS(-3.1415927)- -1Example21.COSH返回一个数字反余弦值SQL select cosh(20) from dual; COSH(20)-242582598Example22.EXP返回一个数字e的n次方根SQL select exp(2),exp(1) from dual; EXP(2) EXP(1)- -7.3890561 2.7182818Example23.FLOOR对给定的数字取整数SQL select floor(2345.67) from dual;FLOOR(2345.67)- 2345Example24.LN返回一个数字的对数值SQL select ln(1),ln(2),ln(2.7182818) from dual; LN(1) LN(2) LN(2.7182818)- - - 0 .69314718 .99999999Example25.LOG(n1,n2)返回一个以n1为底n2的对数 SQL select log(2,1),log(2,4) from dual; LOG(2,1) LOG(2,4)- - 0 2Example26.MOD(n1,n2)返回一个n1除以n2的余数SQL select mod(10,3),mod(3,3),mod(2,3) from dual;MOD(10,3) MOD(3,3) MOD(2,3)- - - 1 0 2Example27.POWER返回n1的n2次方根SQL select power(2,10),power(3,3) from dual;POWER(2,10) POWER(3,3)- - 1024 27Example28.ROUND和TRUNC按照指定的精度进行舍入SQL select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)- - - - 56 -55 55 -55Example29.SIGN取数字n的符号,大于0返回1,小于0返回-1,等于0返回0SQL select sign(123),sign(-100),sign(0) from dual;SIGN(123) SIGN(-100) SIGN(0)- - - 1 -1 0Example30.SIN返回一个数字的正弦值SQL select sin(1.57079) from dual;SIN(1.57079)- 1Example31.SIGH返回双曲正弦的值SQL select sin(20),sinh(20) from dual; SIN(20) SINH(20)- -.91294525 242582598Example32.SQRT返回数字n的根SQL select sqrt(64),sqrt(10) from dual; SQRT(64) SQRT(10)- - 8 3.1622777Example33.TAN返回数字的正切值SQL select tan(20),tan(10) from dual; TAN(20) TAN(10)- -2.2371609 .64836083Example34.TANH返回数字n的双曲正切值SQL select tanh(20),tan(20) from dual; TANH(20) TAN(20)- - 1 2.2371609 Example35.TRUNC按照指定的精度截取一个数SQL select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual; TRUNC1 TRUNC(124.16666,2)- - 100 124.16 Example36.ADD_MONTHS增加或减去月份SQL select to_char(add_months(to_date(199912,yyyymm),2),yyyymm) from dual;TO_CHA-200002SQL select to_char(add_months(to_date(199912,yyyymm),-2),yyyymm) from dual;TO_CHA-199910Example37.LAST_DAY返回日期的最后一天SQL select to_char(sysdate,yyyy.mm.dd),to_char(sysdate)+1,yyyy.mm.dd) from dual;TO_CHAR(SY TO_CHAR(S- -2004.05.09 2004.05.10SQL select last_day(sysdate) from dual;LAST_DAY(S-31-5月 -04Example38.MONTHS_BETWEEN(date2,date1)给出date2-date1的月份SQL select months_between(19-12月-1999,19-3月-1999) mon_between from dual;MON_BETWEEN- 9SQLselectmonths_between(to_date(2000.05.20,yyyy.mm.dd),to_date(2005.05.20,yyyy.mm.dd) mon_betw from dual; MON_BETW- -60Example39.NEW_TIME(date,this,that)给出在this时区=other时区的日期和时间SQL select to_char(sysdate,yyyy.mm.dd hh24:mi:ss) bj_time,to_char(new_time 2 (sysdate,PDT,GMT),yyyy.mm.dd hh24:mi:ss) los_angles from dual;BJ_TIME LOS_ANGLES- -2004.05.09 11:05:32 2004.05.09 18:05:32Example40.NEXT_DAY(date,day)给出日期date和星期x之后计算下一个星期的日期SQL select next_day(18-5月-2001,星期五) next_day from dual;NEXT_DAY-25-5月 -01 Example41.SYSDATE用来得到系统的当前日期SQL select to_char(sysdate,dd-mm-yyyy day) from dual;TO_CHAR(SYSDATE,-09-05-2004 星期日trunc(date,fmt)按照给出的要求将日期截断,如果fmt=mi表示保留分,截断秒SQL select to_char(trunc(sysdate,hh),yyyy.mm.dd hh24:mi:ss) hh, 2 to_char(trunc(sysdate,mi),yyyy.mm.dd hh24:mi:ss) hhmm from dual;HH HHMM- -2004.05.09 11:00:00 2004.05.09 11:17:00 Example42.CHARTOROWID将字符数据类型转换为ROWID类型SQL select rowid,rowidtochar(rowid),ename from scott.emp;ROWID ROWIDTOCHAR(ROWID) ENAME- - -AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITHAAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLENAAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARDAAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONESExample43.CONVERT(c,dset,sset)将源字符串 sset从一个语言字符集转换到另一个目的dset字符集SQL select convert(strutz,we8hp,f7dec) conversion from dual;conver-strutzExample44.HEXTORAW将一个十六进制构成的字符串转换为二进制Example45.RAWTOHEXT将一个二进制构成的字符串转换为十六进制 Example46.ROWIDTOCHAR将ROWID数据类型转换为字符类型 Example47.TO_CHAR(date,format)SQL select to_char(sysdate,yyyy/mm/dd hh24:mi:ss) from dual;TO_CHAR(SYSDATE,YY-2004/05/09 21:14:41 Example48.TO_DATE(string,format)将字符串转化为ORACLE中的一个日期Example49.TO_MULTI_BYTE将字符串中的单字节字符转化为多字节字符SQL select to_multi_byte(高) from dual;TO-高Example50.TO_NUMBER将给出的字符转换为数字SQL select to_number(1999) year from dual; YEAR- 1999Example51.BFILENAME(dir,file)指定一个外部二进制文件SQLinsert into file_tb1 values(bfilename(lob_dir1,image1.gif);Example52.CONVERT(x,desc,source)将x字段或变量的源source转换为descSQL select sid,serial#,username,decode(command, 2 0,none, 3 2,insert, 4 3, 5 select, 6 6,update, 7 7,delete, 8 8,drop, 9 other) cmd from v$session where type!=background; SID SERIAL# USERNAME CMD- - - - 1 1 none 2 1 none 3 1 none 4 1 none 5 1 none 6 1 none 7 1275 none 8 1275 none 9 20 GAO select 10 40 GAO noneExample53.DUMP(s,fmt,start,length)DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值SQL col global_name for a30SQL col dump_string for a50SQL set lin 200SQL select global_name,dump(global_name,1017,8,5) dump_string from global_name;GLOBAL_NAME DUMP_STRING- -ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,DExample54.EMPTY_BLOB()和EMPTY_CLOB()这两个函数都是用来对大数据类型字段进行初始化操作的函数Example55.GREATEST返回一组表达式中的最大值,即比较字符的编码大小.SQL select greatest(AA,AB,AC) from dual;GR-ACSQL select greatest(啊,安,天) from dual;GR-天Example56.LEAST返回一组表达式中的最小值SQL select least(啊,安,天) from dual;LE-啊Example57.UID返回标识当前用户的唯一整数SQL show userUSER 为GAOSQL select username,user_id from dba_users where user_id=uid;USERNAME USER_ID- -GAO 25 Example58.USER返回当前用户的名字SQL select user from dual;USER-GAOExample59.USEREVN返回当前用户环境的信息,opt可以是:ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZEISDBA 查看当前用户是否是DBA如果是则返回trueSQL select userenv(isdba) from dual;USEREN-FALSESQL select userenv(isdba) from dual;USEREN-TRUESESSION返回会话标志SQL select userenv(sessionid) from dual;USERENV(SESSIONID)- 152ENTRYID返回会话人口标志SQL select userenv(entryid) from dual;USERENV(ENTRYID)- 0INSTANCE返回当前INSTANCE的标志SQL select userenv(instance) from dual;USERENV(INSTANCE)- 1LANGUAGE返回当前环境变量SQL select userenv(language) from dual;USERENV(LANGUAGE)-SIMPLIFIED CHINESE_CHINA.ZHS16GBKLANG返回当前环境的语言的缩写SQL select userenv(lang) from dual;USERENV(LANG)-ZHSTERMINAL返回用户的终端或机器的标志SQL select userenv(terminal) from dual;USERENV(TERMINA-GAOVSIZE(X)返回X的大小(字节)数SQL select vsize(user),user from dual;VSIZE(USER) USER- - 6 SYSTEM Example60.AVG(DISTINCT|ALL)all表示对所有的值求平均值,distinct只对不同的值求平均值SQLWKS create table table3(xm varchar(8),sal number(7,2);语句已处理。SQLWKS insert into table3 values(gao,1111.11);SQLWKS insert into table3 values(gao,1111.11);SQLWKS insert into table3 values(zhu,5555.55);SQLWKS commit;SQL select avg(distinct sal) from gao.table3;AVG(DISTINCTSAL)- 3333.33SQL select avg(all sal) from gao.table3;AVG(ALLSAL)- 2592.59Example61.MAX(DISTINCT|ALL)求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次SQL select max(distinct sal) from scott.emp;MAX(DISTINCTSAL)- 5000Example62.MIN(DISTINCT|ALL)求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次SQL select min(all sal) from gao.table3;MIN(ALLSAL)- 1111.11Example63.STDDEV(distinct|all)求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差SQL select stddev(sal) from scott.emp;STDDEV(SAL)- 1182.5032SQL select stddev(distinct sal) from scott.emp;STDDEV(DISTINCTSAL)- 1229.951 Example64.VARIANCE(DISTINCT|ALL)求协方差 SQL select variance(sal) from scott.emp;VARIANCE(SAL)- 1398313.9Example65.GROUP BY主要用来对一组数进行统计SQL select deptno,c