oracle基础学习

作者:课课家教育更新于： 2019-04-29 10:15:14

　　1.desctable_name可以查询表的结构

　　2.怎么获取有哪些用户在使用数据库

　　selectusernamefromv$session;

　　3.如何在Oracle服务器上通过SQLPLUS查看本机IP地址?

　　selectsys_context('userenv','ip_address')fromdual;

　　如果是登陆本机数据库，只能返回127.0.0.1

　　4.如何给表、列加注释？

　　SQL>commentontable表is'表注释';

　　注释已创建

　　SQL>commentoncolumn表.列is'列注释';

　　注释已创建。

　　SQL>select*fromuser_tab_commentswherecommentsisnotnull;

　　5.如何在ORACLE中取毫秒?

　　selectsystimestampfromdual;

　　6.如何在字符串里加回车？

　　添加一个||chr(10)

　　select'Welcometovisit'||chr(10)||'www.CSDN.NET'fromdual;

　　7.怎样修改oracel数据库的默认日期?

　　altersessionsetnls_date_format='yyyymmddhh24miss';

　　8.怎么可以看到数据库有多少个tablespace?

　　select*fromdba_tablespaces;

　　9.如何显示当前连接用户?

　　SHOWUSER

　　10.如何测试SQL语句执行所用的时间?

　　SQL>settimingon;

　　11.怎么把select出来的结果导到一个文本文件中？

　　SQL>SPOOLF:\\ABCD.TXT;

　　SQL>select*fromtable;

　　SQL>spooloff;

　　12.如何在sqlplus下改变字段大小?

　　altertabletable_namemodify(field_namevarchar2(100));

　　改大行，改小不行（除非都是空的）

　　13.如果修改表名?

　　altertableold_table_namerenametonew_table_name;

　　14.如何搜索出前N条记录？(desc降序)

　　SELECT*FROMTablenameWHEREROWNUM

　　ORDERBYcolumn;

　　15.如何在给现有的日期加上2年？

　　selectadd_months(sysdate,24)fromdual;

　　16.Connectstring是指什么?

　　应该是tnsnames.ora中的服务名后面的内容

　　17.返回大于等于N的最小整数值?

　　SELECTCEIL(-10.102)FROMDUAL;

　　18.返回小于等于N的最大整数值?

　　SELECTFLOOR(2.3)FROMDUAL;

　　19.返回行的物理地址

　　SELECTROWID,enameFROMtablenameWHEREdeptno=20;

　　20.将N秒转换为时分秒格式？

　　setserverouton

　　declare

　　Nnumber:=1000000;

　　retvarchar2(100);

　　begin

　　ret:=trunc(n/3600)||'小时'||to_char(to_date(mod(n,3600),'sssss'),'fmmi"分"ss"秒"');

　　dbms_output.put_line(ret);

　　end;

　　21.如何监控当前数据库谁在运行什么SQL语句？

　　SELECTosuser,username,sql_textfromv$sessiona,v$sqltextb

　　wherea.sql_address=b.addressorderbyaddress,piece;

　　22.如何知道当前用户的ID号?

　　SQL>SHOWUSER;

　　OR

　　SQL>selectuserfromdual;

　　23.如何知道使用CPU多的用户session?

　　11是cpuusedbythissession

　　selecta.sid,spid,status,substr(a.program,1,40)prog,a.terminal,osuser,value/60/100value

　　fromv$sessiona,v$processb,v$sesstatc

　　wherec.statistic#=11andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;

　　24.Oracle建立表空间和用户

　　建立表空间和用户的步骤：

　　用户

　　建立：createuser用户名identifiedby"密码";

　　授权：grantcreatesessionto用户名;

　　grantcreatetableto用户名;

　　grantcreatetablespaceto用户名;

　　grantcreateviewto用户名;

　　表空间

　　建立表空间(一般建N个存数据的表空间和一个索引空间)：

　　createtablespace表空间名

　　datafile'路径(要先建好路径)\\***.dbf'size*M

　　tempfile'路径\\***.dbf'size*M

　　autoextendon--自动增长

　　--还有一些定义大小的命令，看需要

　　defaultstorage(

　　initial100K,

　　next100k,

　　);

　　用户权限

　　授予用户使用表空间的权限：

　　alteruser用户名quotaunlimitedon表空间;

　　或alteruser用户名quota*Mon表空间;

　　createtablespacezqdatafile'D:\\zq\\zw.dbf'SIZE1000MAUTOALLOCATE;

　　修改用户的默认表空间

　　alteruserusernamedefaulttablespacetablespacename;

　　25.在sqlplus中清屏命令：clearsrcclearscreen;clscr;

　　怎样用语句查询表空间里面表的内容?

　　selecttable_namefromall_tableswheretablespace_name='zq';

　　selecttable_namefromuser_tableswheretablespace_name='xx'

　　26.如何查询表在哪个表空间中？(单引号里面的要大写)

　　SELECTtablespace_nameFROMUSER_TABLESWHEREtable_name='YOUR_TABLENAME'

　　查一下，这个表是哪个用户下的，如果是本用户则可以用上面的sql

　　如果是别的用户的表你就用

　　SELECTtablespace_nameFROMDBA_TABLESWHEREtable_name='YOUR_TABLENAME'andowner='表的OWNER'

　　还有你要确定你查的确实是一个表而不是view或SYNONYM

　　而且在引号里面的表名和owner都要用大写字母

　　27.表的创建

　　createtableaa

　　(avarchar2(10),

　　bnumber(8,2),

　　cdate

　　)tablespaceusers;

　　如果在创建用户时没有指定默认表空间，系统默认表空间为System，在创建表时必须指定tablespace;

　　28.如何查询一个表空间下的所有表(单引号里面的要大写)

　　selecttable_namefromuser_tableswheretablespace_name='表空间名';

　　29.更改计算机名后会出现OracleORA-12541:TNS:nolistener错误解决方法

　　D:\\oracle\\product\\10.2.0\\db_1\\NETWORK\\ADMIN\\listener.ora

　　修改为现在的计算机名，再次启动OracleOraHome90TNSListener服务成功

　　30.创建表时默认表空间是SYSAUX

　　31.oracle10gemDatabaseControl的启动问题修复

　　打开http://localhost:1158/em/显示数据库状态没有启动，提示用户登录错误ORA-28000:theaccountislocked，使用PL/SQL或SQL*plus

　　连接是正常的。到网上搜索一番，没有找到确切的原因。其中一个可能的原因是用户DBSNMP的密码和sys用户的密码不一致，导致

　　OracleDBConsoleSID服务错误，网上很多朋友说使用emca（EMConfigurationAssistant）工具进行修复。

　　在命令行里键入下面两个命令就可以修复数据库的em

　　emca-reposrecreate

　　emca-configdbcontroldb

　　另外，网上很多朋友给出“emca-r”这样的命令，我试了之后发现10g版本的命令格式已经有所变化，具体的命令格式可以通过命令

　　的帮助获得。在命令行中键入

　　emcahelp=y

　　可以查看详细的命令格式。

　　常用的命令语法：

　　emca-reposcreate创建一个EM资料库

　　emca-reposrecreate重建一个EM资料库

　　emca-reposdrop删除一个EM资料库

　　emca-configdbcontroldb配置数据库的DatabaseControl

　　emca-deconfigdbcontroldb删除数据库的DatabaseControl配置

　　emca-reconfigports重新配置dbcontrol的端口，默认端口在1158

　　emctlstartconsole启动EMconsole服务，使用前需要先设置ORACLE_SID环境变量

　　emctlstopconsole停止EMconsole服务，使用前需要先设置ORACLE_SID环境变量

　　32.解决启动Oracle9i的OEM或OMS的常见问题(VTK-1000)

　　能否正常启动OEM或OMS关键有以下两点：

　　第一．Oracle的系统服务是否开启；

　　第二．登录时用的用户名和口令是否正确。

　　那么先针对第一点谈谈Oracle的系统服务。在完全安装的情况下，Oracle的系统服务共有11项：

　　1．OracleOLAP9.0.1.0.1

　　2．OracleOLAPAgent

　　3．OracleOraHome90Agent

　　4．OracleOraHome90ClientCache

　　5．OracleOraHome90HTTPServer

　　6．OracleOraHome90ManagementServer(0.5M)

　　7．OracleOraHome90PagingServer

　　8．OracleOraHome90SNMPPeerEncapsulator

　　9．OracleOraHome90SNMPPeerMasterAgent

　　10．OracleOraHome90TNSListener(5.2M)

　　11．OracleServiceORACLE(70M)

　　(注：OraHome90是可以在安装时改变的Oracle的主目录名称，是安装时的默认值)

　　其中最重要的服务有3个，分别是OracleOraHome90ManagementServer、OracleOraHome90TNSListener与

　　OracleServiceORACLE。下面就来看一下有哪些启动错误与它们有关。

　　1．Oracle系统提示：Ora-12541:TNS:没有监听器；

　　2．操作系统提示：在本地计算机无法启动OMS服务

　　错误：1053：服务并未及时响应来控制请求附带；

　　以上两种错误提示大都是由OracleOraHome90TNSListener监听服务引起的。

　　解决方法：控制面版->管理工具->服务->右键单击“OracleOraHome90TNSListener”，再单击“启动”。

　　3．Oracle系统提示：Ora-12500:TNS:监听程序无法启动专用服务器进程；

　　该错误是由OracleServiceORACLE专用服务器进程引起的。

　　解决方法：控制面版->管理工具->服务->右键单击“OracleServiceORACLE”，再单击“启动”。

　　4．Oracle系统提示：VTK-1000：无法连接到ManagementServer。

　　请验证您已输入OracleManagementServer的正确主机名和状态。

　　该错误引起的原因有两种，一是OracleOraHome90ManagementServer还没启动；二是没有输入主机名。

　　解决方法：控制面版->管理工具->服务->右键单击“OracleOraHome90ManagementServer”，

　　再单击“启动”，

　　或是输入您这台计算机的完整名称。

　　接着针对第二点谈谈登录时用的用户名和口令。

　　在安装结束后，系统提供了两个默认的数据库系统管理员，其用户名和口令分别是SYS/change_on_install和SYSTEM/manager，同时系统还

　　提供了登录OMS的用户名和口令：sysman/oem_temp。这里容易出现错误的是在登录OMS是用SYS或SYSTEM作为用户名进行登录，那么Oracle系统

　　就回有“登录身份证明不正确”的提示。

　　小结：这三个服务的启动或关闭还有先后的顺序。一般来讲，启动时必须先启动OracleOraHome90TNSListener再启动

　　OracleOraHome90ManagementServer或OracleServiceORACLE，在启动OracleOraHome90ManagementServer时，同时也启动了

　　OracleServiceORACLE。而关闭时必须先关闭OracleOraHome90ManagementServer再关闭OracleOraHome90TNSListener或OracleServiceORACLE，

　　关闭OracleOraHome90ManagementServer时，若有提示输入用户名和口令，请输入sysman的用户名和口令，以确保成功的执行。有些其他提示如

　　：资源已被占用，I/O重复，端口已被使用等等之类的话，那最好与系统管理员联系，再寻求解决办法。

　　1)查询数据库名：

　　SQL>selectnamefromv$database;

　　(2)查询数据库实例名：

　　SQL>selectinstance_namefromv$instance;

　　(3)查询数据库服务名：

　　SQL>selectvaluefromv$parameterwherename='service_names';//(小写)

　　(4)查询全局数据库名(sys用户)：

　　SQL>selectvalue$fromprops$wherename='GLOBAL_DB_NAME';//字符串区分大小写

　　监视用户会话：

　　SQL>selectusername,sid,serial#,machinefromv$session;

　　删除用户会话：

　　SQL>altersystemkillsession'sid,serail#';

　　//使用下面的方法可以重复执行上一条SQL语句（在SQL*Plus中）

　　SQL>l//小写字母L，显示上一条SQL语句

　　1*selectusername,sid,serial#,machinefromv$session

　　SQL>///正斜杠：重复执行上一条SQL语句

　　SQL>setlinesize1000//将SQL*Plus中显示行宽设成1000个字符。

　　SQL>connectsystem/manager@orasjz//在SQL*Plus中直接连接到另一台机器上的数据库，@字符后是另

　　一台机器的tnsname

　　4.手工配置Oracle网络连接:主要是配置"tnsnames.ora"文件。

　　A.手工配置Oracle网络连接配置文件：tnsnames.ora

　　B.手工配置Oracle监听进程配置文件：listener.ora

　　UNIX下启动进程命令：

　　$lsnrctlstart//启动监听进程

　　$lsnrctlstatus//显示监听进程状态

　　$lsnrctlstop//停止监听进程

　　启动进程的命令与UNIX相同。

　　LISTENER=

　　(DESCRIPTION_LIST=

　　(DESCRIPTION=

　　(ADDRESS_LIST=

　　(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))

　　)

　　(ADDRESS_LIST=

　　(ADDRESS=(PROTOCOL=TCP)(HOST=ORA54)(PORT=1521))

　　//注意：上面一行的HOST必须是你的主机名，否则监听会出问题，也可以使用你的机器的IP地址

　　)

　　)

　　(DESCRIPTION=

　　(PROTOCOL_STACK=

　　(PRESENTATION=GIOP)

　　(SESSION=RAW)

　　)

　　(ADDRESS=(PROTOCOL=TCP)(HOST=ORA54)(PORT=2481))

　　)

　　)

　　SID_LIST_LISTENER=

　　(SID_LIST=

　　(SID_DESC=

　　(SID_NAME=PLSExtProc)

　　(ORACLE_HOME=..........)

　　(PROGRAM=extproc)

　　)

　　(SID_DESC=

　　(GLOBAL_DBNAME=ora54)

　　(ORACLE_HOME=..........)

　　(SID_NAME=ora54)//注意：ora54为数据库的SID名称，不能更改，否则监听出问题

　　)

　　)

　　5.将主机字符串(HostString)写入注册表(简化SQL*Plus的登录)

　　regedit.exe->HKLM->Software->Oracle->home0

　　增加关键字:local(字符串)，键值：主机字符串名。

　　/*将下面的内容复制到一个.reg文件中，在Windows2000中双击执行，即可实现增加或者修改local键值的

　　作用

　　WindowsRegistryEditorVersion5.00

　　[HKEY_LOCAL_MACHINE\\SOFTWARE\\ORACLE\\HOME0]

　　"local"="ora54"

　　*/

　　[SVRMGRL:ServerManager的使用方法]

　　D:\\>svrmgrl

　　SVRMGR>connectinternal//使用svrmgrl，进入后使用的第一个命令必须是这个命令。

　　口令://如果要求输入口令，请输入oracle

　　连接成功。

　　SVRMGR>selectnamefromv$database;//检查当前使用的数据库名

　　NAME

　　---------

　　ORA44

　　已选择1行。

　　SVRMGR>shutdownimmediate//关闭当前使用的数据库

　　已关闭数据库。

　　已卸下数据库。

　　已关闭ORACLE实例。

　　SVRMGR>startup//启动当前使用的数据库，如果无效，请使用startupforce

　　已启动ORACLE实例。

　　系统全局区域合计有24433932个字节

　　FixedSize70924个字节

　　VariableSize7507968个字节

　　DatabaseBuffers16777216个字节

　　RedoBuffers77824个字节

　　ORA-00205:?????????????????????

　　SVRMGR>

　　[另一个启动oracle数据库的例子]

　　d:\\>sqlplusinternal/oracle

　　SQL>startupforce//强行重新启动数据库。

　　[修改口令字]

　　SQL>grantconnecttosystemidentifiedbyNewPassword;//如果用数字作口令，需要使用双引号括起

　　来

　　SQL>grantconnecttosysidentifiedbyNewPassword;

　　SQL>alterusersystemidentifiedbyNewPassword;

　　SQL>alterusersysidentifiedbyNewPassword;

　　SQL>password//需要输入原口令

　　//注：以上修改口令的方法等价；sys与system用户可以互相修改口令；如果sys与system用户的口令都忘

　　记了，使用如下方法：

　　D:\\>svrmgrl

　　SVRMGR>connectinternal/oracle

　　连接成功。

　　SVRMGR>grantconnecttosystemidentifiedbymanager;

　　语句已处理。

　　SVRMGR>exit

　　服务器管理程序结束。

　　、Oracle产品组成

　　查询数据库选件产品：

　　SQL>select*fromv$option;

　　一般都是True,如果是False,可以双击激活。

　　/*cartridges(小产品的)插件，(大产品的)选件(options)*/

　　[SYS用户是Oracle数据库中权限最大的用户。]

　　[SQL*Plus登录方法]

　　1.c:\\>sqlplus"/assysdba"

　　2.c:\\>sqlplusinternal

　　//注意：以上两种方法可以类似的使用于SQL*Plus的图形登录界面中

　　//用这种登录方法登录进去，所使用的用户均为SYS。

　　[之所以会出现这种登录方法，是因为在NT的用户组中存在一个ORA_DBA的本地组，凡在此组中的用户使用

　　操作系统认证，即在此组中的用户登录数据库时不需要密码。]

　　[orapwd命令:修改internal用户的口令字]

　　/*orapwd的命令行参数

　　D:\\>orapwd

　　Usage:orapwdfile=password=entries=

　　where

　　file-nameofpasswordfile(mand),

　　password-passwordforSYSandINTERNAL(mand),

　　entries-maximumnumberofdistinctDBAandOPERs(opt),

　　Therearenospacesaroundtheequal-to(=)character.*/

　　修改internal口令字(internal默认口令为oracle)认证方法(AB两个步骤)：

　　A.修改Oracle登录认证方法：

　　修改文件SQLNET.ORA文件。

　　SQLNET.AUTHENTICATION_SERVICES=(NTS)//将这一行前面加上#号注释掉，即可将Oracle的认证方法由操作

　　系统认证改为Oracle认证

　　SQLNET.ORA文件的位置：

　　UNIX:/u01/app/oracle/product/8.1.6/network/admin/sqlnet.ora

　　WindowsNT/2000:d:\\oracle\\ora81\\network\\admin\\sqlnet.ora

　　B.修改Internal口令字：

　　WindowsNT/2000:

　　C:\\>ORAPWDfile=d:\\oracle\\ora81\\database\\pwdora8i.ora

　　password=YourPassword//YourPassword为你要设置的密码

　　entries=30

　　/*ORAPWDfile=d:\\oracle\\ora81\\database\\pwdora54.orapassword=qeventries=30*/

　　UNIX:

　　$orapwdfile=$ORACLE_HOME/dbs/orapwSID

　　password=YourPassword

　　entries=30

　　然后重新启动Oracle服务(服务－＞OracleServiceHOSTNAME)。

　　/*在执行上述命令之前，需要先将相应目录的pwdora8i.ora(或orapwSID)文件删除或者改名，因为口令字

　　文件不能重名。*/

　　[tkprof跟踪文件整理工具]

　　$tkprofx.trcx.txt

　　Windows2000/NT:

　　d:\\oracle\\admin\\db_name\\udump\\*.trc

　　UNIX:/u01/app/oracle/amdin/db_name/udump/*.trc//*/

　　Oracle数据分区技术：8.0以后开始使用

　　(一)LOB(LargeObject)大对象类型数据：

　　1.BLOB:存储二进制数据，如图象、视频、声音等，用于代替Longraw类型(Oracle7.0以前的数据类型，今

　　后不再支持)

　　2.CLOB:存储大字符，如：个人简历，用于代替long字段。

　　3.NCLOB:其它民族语言的支持

　　(1)数据库字符集NLS:

　　Server端：NLS_CHARACTERSET:(如果为以下的字符集，Oracle的数据库可以用来存储汉字)

　　ZHS16GBK(Oraclei8,Oracle8)

　　ZHS16CGB231280(Oracle7.3,8,8i)

　　此参数位置在数据字典中，查询核心字符集(语言、日期、货币等)：

　　SQL>select*fromnls_database_parameters;

　　Client端：NLS_LANG=SimplifiedChinese_CHINA.ZHS16GBK(如果没有设置，默认是英文)

　　regedit.exe

　　UNIXClient中：

　　$NLS_LANG="simplifiedchinese"_china.zhs16gbk

　　$exportNLS_LANG

　　//一般将上述环境变量放入.profile文件中。

　　/***********************************************插入内容

　　********************************************************

　　(1)sys是一种用户，internal是一种方式，用来启动关闭数据库，9.0以后不再使用internal，全部是sys

　　。

　　(2)SQL>setcomv7//将8i版本暂时退回到7版

　　(3)SQL>altersystemsuspend;//使用internal用户执行，冻结数据

　　SQL>altersystemresume;//取消冻结，恢复正常

　　**********************************************************************************************

　　******************/

　　(2)Oracle数据库核心字符集修改方法：

　　[*]修改数据字典(使用SYS用户)：

　　SQL>updateprops$setvalue$='ZHS16GBK'wherename='NLS_CHARACTERSET';

　　SQL>commit;

　　然后重新启动数据库。

　　4.BFILE:外部文件存储，将数据存储在服务器硬盘

　　5、SQL关键字（命令）

　　SQL关键字（以下命令为SQL命令，以分号结束）

　　1.Alter：修改表结构

　　2.Audit:审计，NoAudit(取消审计)

　　3.Commit:数据提交，相反的命令：Rollback(回退)

　　4.Comment:将oracle的注释写入数据字典

　　5.Create:建表、数组、索引、视图等，相反的命令：Drop

　　6.delete:删除表中的数据，Drop是把表中的数据包括结构全部删除

　　7.Grant:授权，Revoke:收权(权限回收)

　　8.Insert:在表中插入新行

　　9.Lock:将表强行锁住

　　10.Rename:修改表名

　　11.Select:数据查询

　　12.Update:数据更新，修改某一个列

　　13.Validate:校验，对数据进行校验。

　　6、SQL命令的输入：

　　在SQL>提示符后输入命令，可以输入多行，以分号结束

　　7、SQL*Plus关键字：以回车结束

　　1.@:执行外部命令，格式：@路径\\文件名

　　2.#:注释

　　3./:执行上一条命令

　　4.Accept:接收键盘输入的命令

　　5.Append（简化为a):在行尾增加字符串

　　SQL>at

　　1*select*fromdept

　　SQL>/

　　6.Break:分组，Syntax:breakon分组列skipn//n为每两组之间间隔的空行

　　/*例：

　　SQL>breakonjob

　　SQL>select*fromemporderbyjob;//此处务必按分组列排序

　　SQL>breakonjobskip1

　　SQL>/

　　*/

　　7.Btitle,Ttitle:设置表尾、表头

　　SQL>Ttitle'表头'

　　SQL>Btitle'表尾'

　　SQL>Ttitleoff//失效

　　8.Change:写错了改正,Syntax:C/old/new

　　/*例：

　　SQL>select*fromdetp;

　　select*fromdetp

　　SQL>c/tp/pt

　　1*select*fromdept

　　SQL>/

　　9.Clear:清除

　　SQL>clearbreak//清除前面的break设置

　　SQL>clearbuff//清除SQL*Plus缓冲区中的SQL语句

　　10.Column:列定义，用于定义列的显示格式

　　语法：SQL>column列名format格式

　　例：SQL>Columnsalformat$99.999999

　　11.computer:统计计算

　　12.connect:从一个用户退出，直接进入另一个用户

　　13.disconnect:中断当前用户的连接

　　14.copy:远程复制（用的较少，一般数据链路)

　　15.define:定义

　　16.undefine:取消定义

　　17.del:删除当前行

　　18.describe（简写desc):显示表结构

　　例：SQL>descemp

　　19.document:文档注释

　　20.edit(简化ed):调入外部编辑器编辑缓冲区中的SQL语句，保存后，可以直接通过“/”运行编辑过的SQL

　　语句

　　21.get:将外部文件调入不执行，只供显示

　　例：SQL>getc:\\sqlplus.ora

　　22.host:执行操作系统命令

　　例：SQL>hostdir

　　23.input(简化为I):行插入，在当前行的后面插入。

　　24.List(简化为L):列示

　　25.Pause:设置屏幕暂停

　　SQL>setpauseon//暂停

　　SQL>setpauseoff//不暂停

　　SQL>set'more'pauseon[???]

　　26.Quit:退出

　　27.Remark:注释

　　28.Run(R):类似“/”

　　29.Save:将已经运行过的SQL命令保存到磁盘

　　例：SQL>savec:\\run.sqlreplace//覆盖保存

　　append//追加

　　30.set:设置

　　31.show:显示环境变量的值

　　32.Spool:显示跟踪

　　例：SQL>spoolc:\\文件名

　　SQL>spooloff//停止记录

　　33.Start:类似@，执行外部的SQL文件

　　33.Timing:服务器端某一命令的执行时间跟踪

　　数据字典：描述系统信息的表、视图、同义词等，由系统自动维护。

　　例：SQL>select*fromtabs;

　　1.显示当前所登录的用户：select*fromall_users;

　　2.user_xxx:描述用户创建的对象，如：user_tables，user_views，user_indexes

　　3.dba_XXX:数据库管理员(sys,system)专用数据字典

　　4.all_XXX:描述用户创建的对象，其他用户授权可以存取的对象。

　　all_objects:全部的数据字典

　　1.查询全表的数据：

　　SQL>select*fromemp;

　　2.查询某（几）个列：SQL>selectename,salfromemp;

　　3.DISTINCT标识：只显示不相同的列

　　例：

　　SQL>selectjobfromemp;//显示结果中有相同的记录

　　SQL>selectdistinctjobfromemp;//只显示不相同的职业

　　4.使用orderby将显示结果排序：ASC升序（默认），DESC降序

　　5.使用where指出查询条件：

　　SQL>select*fromempwheresal>2000;

　　[字符型或日期型用单引号括起来，并区分大小写]

　　6.设置日期显示格式：

　　(1)确定日历格式：设置参数nls_calendar。

　　SQL>altersessionsetnls_calendar='JapaneseImperial';//设置为日本日历

　　SQL>altersessionsetnls_calendar='ROCofficial';//设置为台湾日历

　　SQL>altersessionsetnls_calendar='Gregorian';//设置为格林尼治日历，这是我们要设置成的日历。

　　SQL>selectsysdatefromdual;//查询系统时间

　　(2)确定日期格式：参数nls_date_format

　　常用的日期格式：

　　yyyy.mm.dd

　　yyyy-mm-dd

　　yyyy/mm/dd

　　yyyy"年"mm"月"dd"日"//如果要显示汉字，请用双引号括起来

　　yyyy"年"mm"月"dd"日"dy//dy表示星期

　　命令格式：

　　SQL>altersessionsetnls_date_format='格式';

　　[此命令只修改前端的显示，退出后即失效，要想永久有效，要修改注册表，在注册表中

　　HKLM->Software->Oracle->Home0增加字符串关键字nls_date_format，键值为日期格式，此处的格式不需要

　　用单引号括起来。]

　　例：SQL>altersessionsetnls_date_format='yyyy"年"mm"月"dd"日"dy';

　　7.Oracle登录自动执行文件glogin.sql:每次用户登录时自动执行该文件，可以将一些环境变量（如

　　linesize）的设置命令或者其它命令放入该文件中。

　　glogin.sql文件路径：

　　Windows下：d:\\oracle\\ora81\\sqlplus\\admin\\glogin.sql

　　UNIX:/u01/app/oracle/product/8.1.6/sqlplus/admin/glogin.sql

　　二、运算符与谓词

　　1、算术运算符：+,-,*,/

　　SQL>selectsal,sal*12fromemp;//计算工资及年薪。

　　SQL>selectsal,sal+commfromemp;//错误语句，因为comm中存在空值，会使运算结果也会出现空值。

　　SQL>selectsal,sal+NVL(comm,0)fromemp;//正确语句，NVL为空值运算函数，当comm为空值时，

　　该函数返回第二个值0。

　　2、逻辑运算符：NOT,AND,OR，三个运算符的优先级别依次降低。

　　3、比较运算符：>,<,=,>=,<=,!=

　　4、谓词：

　　(1)IN(或NOTIN):等于（不等于）列表中的任意值。

　　SQL>select*fromempwherejobin('MANAGER','CLERK')

　　(2)(NOT)BETWEENAND:表示从小到大的一个范围。[必须是从小到大]

　　SQL>select*fromempwheresalnotbetween2000and3000;

　　(3)LIKE:模式匹配

　　SQL>select*fromempwhereenamelike'S%';//寻找ename为S打头的记录

　　%:表示任意字符串

　　_(下划线):表示一个任意字符

　　SQL>select*fromempwhereenamelike'李%';//可以使用中文

　　SQL>select*fromempwhereenamelike'S_I%';//寻找ename为S打头，第三个字母为I的记录

　　SQL>selectobject_namefromall_objectswhereobject_namelike'DBA%';//搜索数据字典中与dba

　　有关系的对象

　　(4)(NOT)NULL(空值):

　　SQL>select*fromempwherecommisNULL;//查询哪些人没有资金。

　　5.伪列：

　　(1)rowid:唯一行标识，行被删除之后，rowid不变。

　　(2)rownum:行号，一行被删除之后，后面的行号会随之改变

　　SQL>selectempno,ename,rowid,rownumfromemp;

　　[查询SQL语句的相关数据字典]

　　（1）SQL>selectsql_textfromv$sqlarea;//查询以前使用过的SQL语句

　　（2）查询当前连接用户的SQL语句：

　　SQL>selectuser_name,sql_textfromv$open_cursor;//需要用sys或system用户执行

　　三、列名别名

　　SQL>selectename,salasSalaryfromemp;

　　SQL>selectename职工姓名,sal工资fromemp;

　　*#/\\select都不允许做别名，如果一定要用，用双引号括起来。

　　数据操纵语言(DML)

　　一、数据插入：

　　1.对于表中全部列插入

　　语法：SQL>insertinto表名values(值表达式);

　　例：SQL>Insertintodeptvalues(51,'软件开发部','北京');

　　[*]用desc显示表结构、数据类型、顺序SQL>descdept//注意，不要将linesize设置的太大，否则看不

　　到表结构，设置成100即可。

　　[*]所插数据必须与目标列一致。

　　[*]字符与日期数据使用单引号

　　2.对于表中部分列插入

　　语法：SQL>insertinto表名（列名1，列名2...)values(值表达式);

　　[*]对于表中的非空列必须插入数据

　　SQL>insertintoemp(empno,ename,job,hiredate,deptno)values(1234,'李大力','工程师',

　　sysdate-30,10);

　　/*sys或system用户访问其它用户的表的方法

　　SQL>connectsystem/ab@ora44

　　已连接。

　　SQL>select*fromuser01.emp*/

　　/********************commit及Rollback用法:下例是Rollback的一个例子。

　　SQL>deletedeptwheredeptno=51;

　　已删除1行。

　　SQL>select*fromdept;

　　SQL>rollback;

　　SQL>select*fromdept;

　　DEPTNODNAMELOC

　　-------------------------------------

　　10ACCOUNTINGNEWYORK

　　20RESEARCHDALLAS

　　30SALESCHICAGO

　　40OPERATIONSBOSTON

　　50COMPUTERBEIJING

　　51软件开发部北京

　　已选择6行。

　　*************************************************/

　　3.使用参数(变量)，临时输入值

　　SQL>insertinto表名(列名1,列名2,...)values(&x1,&x2,...);

　　SQL>insertdept(deptno,dname,loc)values(&x1,'&x2','&x3')

　　SQL>insertintodeptvalues(&x1,'&x2','&x3');//在命令行中给字符或日期型参数加上单引号，则

　　下面输入时不再需要输入单引号

　　输入x1的值:53

　　输入x2的值:dd

　　输入x3的值:ee

　　原值1:insertintodeptvalues(&x1,'&x2','&x3')

　　新值1:insertintodeptvalues(53,'dd','ee')

　　//definex=7788

　　//也可以这样用：select&x1,&x2fromemp;

　　4.使用子查询从另一个表中复制数据

　　语法：Insertinto表名select子句;

　　SQL>inserttoemp(empno,hiredate,deptno)

　　selectdeptno+7300,sysdate,deptnofromdept;

　　SQL>inserttoemp(empno,hiredate,deptno)

　　selectdeptno+7300,sysdate,deptnofromdept@数据库链路名;//表示dept这个表在网络上的其它服务

　　器中，注意：@之后不是连接串。

　　SQL>createtablepayasselectename,salfromemp;//用复制的方法创建表

　　SQL>insertintopayselect*frompay;//自己复制自己，可以使一个表飞速扩大。

　　二、数据更新

　　语法：SQL>Update表名set列名=值表达式where条件;

　　例：

　　SQL>updateempsetsal=sal+100wheresal<2000;

　　SQL>updateempsetsal=5000,job='MANAGER'whereename='SMITH';

　　SQL>updateempsetcomm=100wherecommisnull;

　　三、数据删除

　　语法：SQL>Deletefrom表名where条件;//删除数据保留结构，可以回退

　　SQL>Truncatetable表名;//删除数据保留结构，不可以回退，效率高

　　SQL>Droptable表名;//删除数据及结构，不可以回退

　　数据控制语言(DCL)

　　一、事务提交：对于数据的插入、更新、删除，只有提交后，数据才真正改变，在提交之前，只有修改了数

　　据的用户才可以看到数据的改变，而其他用户看不到数据的改变。

　　1.显式数据提交语法：SQL>commit;

　　/*某一个用户对表进行更新等写操作之后，如果没有commit，那么其它用户不能对表进行更新操作，否则

　　就会死掉。

　　查锁方法：

　　SQL>selectusername,sid,serial#fromv$session;

　　解锁方法：

　　SQL>altersystemkillsession'sid,serial#';

　　**********************************************************************************************

　　*******/

　　2.隐式数据提交：

　　下列命令是隐式提交命令：

　　Create,Alter,Drop,Connect,Disconnect,Grant,Revoke,Rename,Exit,Quit,Audit,NoAudit

　　3.自动数据提交：

　　SQL>setautocommiton//打开自动数据提交开关

　　SQL>setautocommitoff//关闭自动数据提交开关（默认）

　　二、事务回退

　　语法：SQL>Rollback;//使数据库回退到最近一次提交后状态，如果一次也没有提交过，回到最原始状态

　　SQL>Rollbac;

　　SQL>Rollba;

　　SQL>Rollb;

　　SQL>Roll;

　　//以上的用法都是正确的。

　　三、设置保存点：

　　SQL>SavePointa;

　　回退到保存点：

　　SQL>Rollbacktoa;//此处的Rollback不能再简写。

　　数据定义语言(DDL)

　　包括：创建基表、视图、同义词、索引、数据库链路、序列等

　　一、创建基表

　　语法：

　　SQL>CreateTable表名(

　　列名1数据类型,

　　列名2数据类型,

　　...);//最多可以到1000个字段

　　例：SQL>CreateTableproduct(

　　p_name,varchar2(20),

　　p_id_numbernumber(7),

　　p_datedate);

　　1.数据类型

　　(1)字符型

　　char(n):n<=2000，固定长度，如果实际长度不够，前面用空格补齐。

　　varchar2(n):n<=4000，可变长度，不用空格补齐。

　　(2)数字型number(n):整数，number(n,d):小数

　　(3)日期型

　　(4)二进制raw(8i以前的，现在不支持了）

　　(5)大字符long：建议不要使用，现在不支持了

　　(6)blob:存储二进制

　　(7)CLOB:存储大字符

　　SQL>Createtableemployee(

　　namevarchar2(30),

　　salarynumber(7,2),

　　b_datedate,

　　photeblob,

　　resumeclob);

　　[*]修改数据库兼容性参数（如果在执行上面的SQL语句时，提示clob字段“默认字符集具有不同的宽度”，

　　则需要修改数据库兼容性。）:

　　D:\\oracle\\amdin\\db_name\\pfile\\init.ora

　　compatible=8.0.5=改为=>8.1.0或8.1.5

　　改完后重新启动数据库使修改生效。

　　/**********************************************

　　修改前：

　　SQL>Createtableemployee(

　　2namevarchar2(30),

　　3salarynumber(7,2),

　　4b_datedate,

　　5photeblob,

　　6resumeclob);

　　resumeclob)

　　*

　　ERROR位于第6行:

　　ORA-22866:默认字符集具有不同的宽度

　　修改后：

　　SQL>Createtableemployee(

　　2namevarchar2(30),

　　3salarynumber(7,2),

　　4b_datedate,

　　5photeblob,

　　6resumeclob);

　　表已创建。

　　************************************************/

　　2.约束条件：数据完整性约束条件(DataIntegrityConstaints)

　　Oracle常用约束条件：

　　(1)非空约束：NOTNULL

　　(2)唯一性约束：UNIQUE

　　(3)主键：PRIMARYKEY，主键同时具有上面两个约束条件，一个表中只允许有一个主键。

　　(4)外键：FOREIGNKEY，这一列值从其它表中取出，允许重复，但不允许修改。

　　(5)检查：CHECK(e.g.check(sal>200)

　　(6)引用（参考）：REFERENCES，只能用其它表或者本表的某一列列值，不能随便修改。

　　(7)缺省值：DEFAULT，如果没有输入，自动使用DEFAULT值。

　　3.约束条件的定义方法：

　　可以定义为列的一部分，也可以定义为表的一部分。

　　(1)定义为列的一部分：

　　SQL>CreateTableproduct(

　　p_namevarchar2(20)unique,

　　p_idnumber(7)primarykey,

　　p_datedatenotnull);

　　[*]Check、Default约束条件：

　　SQL>Createtableemployee(

　　namevarchar2(20),

　　idnumber(7)primarykey,

　　salnumber(11)check(sal>200andsal<2000),

　　h_datedatedefaultsysdate);

　　一个列具有多个约束条件的写法：

　　SQL>CreateTableproduct(

　　p_namevarchar2(20)unique,

　　p_idnumber(7)primarykey,

　　check(p_id>=111andp_id<=999),

　　p_datedatenotnull);

　　例：

　　SQL>insertintoemployee(name,id,sal)values('Smith',2,201)；

　　SQL>select*fromemployee;

　　(2)定义为表的一部分(不能用在Default和NotNUll的定义上)：

　　SQL>CreateTableProduct(

　　p_namevarchar2(20),

　　p_idnumber(7),

　　p_datedatenotnull,

　　constraintp_id_pkprimarykey(p_id),

　　constraintp_name_ukunique(p_name));

　　[*]查询约束条件：

　　SQL>select*fromuser_constraintswheretable_name='PRODUCT';

　　(3)另一种约束定义方法：

　　SQL>CreateTableProduct(

　　p_namevarchar2(20),

　　p_idnumber(7)constraintpk_p_idprimarykey,

　　p_datedateconstraintfk_p_datenotnull);

　　4.使用外键创建主从基表

　　(1)创建主表(定义主表):

　　SQL>CreateTableproduct(

　　p_namevarchar2(20),

　　p_idnumber(7)primarykey,

　　p_datedatenotnull);

　　(2)创建子表，定义外键

　　SQL>createtablesales_list(

　　sales_namevarchar2(20),

　　sales_idnumber(7)primarykey,

　　p_idnumber(7),

　　constraintp_id_fkforeignkey(p_id)referencesproduct(p_id));

　　5.数据完整性约束条件的修改

　　(1)删除约束条件：

　　A.删除主键约束：

　　SQL>Altertableproductdropprimarykey;

　　SQL>Altertableproductdropconstraintp_id_pk;

　　//以上两种方法等价

　　B.删除唯一性约束：

　　SQL>Altertableproductdropunique(p_name);

　　SQL>Altertableproductdropconstraintp_id_uk;

　　C.删除非空约束

　　SQL>Altertableproductmodify(p_dateNULL);

　　D.删除缺省值：

　　SQL>Altertableproductmodify(p_datedefaultnull);

　　(2)增加约束条件

　　A.增加主键约束：

　　SQL>Altertableproductaddprimarykey(p_id);

　　SQL>Altertableproductaddconstraintp_id_pkprimarykey(p_id);

　　B.增加非空约束

　　SQL>altertableproductmodify(p_datenotnull);

　　C.增加缺省值

　　SQL>Altertableproductmodify(p_datedefaultsysdate-1);

　　[*]查询缺省值：

　　SQL>selecttable_name,column_name,data_defaultfromuser_tab_columns;

　　二、修改表结构

　　1.在表中增加新列：

　　SQL>Altertableproductadd(p_listnumber(7),p_locvarchar2(20));

　　2.删除一个列(只适用于Oracle8i以后的版本）：

　　SQL>Altertableproductdrop(p_list,p_loc);//删除多个列

　　SQL>Altertableproductdropcolumnp_list;//删除一个列

　　3.修改列宽：

　　SQL>Altertableproductmodify(p_namevarchar2(40));

　　//增加列宽没有约束，但减小列宽要求列中数据为空。使用此命令也可以修改列的数据类型。

　　三、视图(View):视图是虚表。

　　[*]视图不存储数据

　　[*]数据来源于基表

　　[*]不是数据的复制

　　[*]在同一个表上可以创建多个视图

　　1.创建视图的语法：

　　SQL>CreateorReplaceview视图名

　　asselect语句;

　　例：SQL>createviewmanager

　　as

　　select*fromempwherejob='MANAGER';//select*fromtab;检查

　　SQL>select*frommanager;//查询视图如果查询表

　　//第二次创建视图，可以使用orreplace参数，不需要再删除而直接覆盖同名视图。

　　SQL>createorreplaceviewmanager

　　as

　　select*fromempwherejob='MANAGER';

　　[几点说明:]

　　(1)在创建视图时，不得使用orderby排序。

　　(2)在视图中插入数据，则数据被插入到基表中，所以，如果要向视图插入数据，则创建视图时，必须包含

　　表中全部非空列。

　　(3)用户视图数据字典：

　　SQL>selectview_name,textfromuser_views;

　　2.视图列别名：

　　错误语句：

　　SQL>createviewpaymentas

　　selectsal,sal*12,nul(comm,0)/salfromemp;//错误原因：视图可以视同为表，所以列名也要符合

　　规定，而sal*12则是不符合规矩的列名。

　　正确语句：

　　SQL>createviewpayment(c1,c2,c3)as//c1,c2,c3即为视图列别名

　　selectsal,sal*12,nvl(comm,0)/salfromemp;

　　3.创建视图时增加约束条件：WITHCHECKOPTION

　　SQL>Createorreplaceviewdeptno20as

　　selectempno,ename,deptnofromempwheredeptno=20;

　　SQL>Insertintodeptno20values(1236,'李力',30);

　　SQL>select*fromdeptno20;

　　//上面的语句会出现能够通过视图入基表中插入数据，但却不能通过视图看到插入的数据的问题，解决办

　　法：

　　SQL>Createorreplaceviewdeptno20as

　　selectempno,ename,deptnofromempwheredeptno=20

　　WITHCHECKOPTION;

　　4.创建Oracle8i的实体化视图(MaterializedView):视图不依赖于基表，基表被删除后，视图仍然正常。

　　一般用于两个远程数据库之间的访问，通过数据链路来实现。

　　(1)以DBA用户登录，为用户授予创建实体化视图的权限：

　　SQL>GrantCreateMaterializedViewto用户名;

　　(2)以获权用户登录，创建实体化视图：

　　SQL>CreateMaterializedViewmanageras

　　select*fromempwherejob='MANAGER';

　　(3)删除实体化视图：

　　SQL>DropMaterializedViewmanager;

　　删除基表：

　　SQL>droptable表名;

　　删除视图：

　　SQL>dropview视图名;

　　三、创建数据库链路(Databaselink):

　　数据库链路：用于数据库之间的远程数据复制。

　　DB1(UNIX)<-------DB2(NT)

　　若要将DB2中的数据复制到DB1，则需要在DB1中创建数据库链路指向DB2数据库。

　　创建数据库链路的步骤：

　　(1)创建好连接串。

　　(2)创建数据库链路。

　　1.创建数据库链路的语法：

　　SQL>Createdatabaselink数据库链路名

　　connectto用户名identifiedby口令

　　using'主机字符串';

　　[*]数据库链路名必须与远程数据库的全局数据库名(数据库名.域名，若没有数据库名，就是数据库名)相同

　　[*]用户名及口令为远程数据库的用户名及口令

　　[*]主机字符串为本机tnsnames.ora中网络连接串。

　　SQL>Createdatabaselinkora31connecttouser30identifiedbyuser30using'ora31';

　　2.使用数据库链路：

　　SQL>select*fromproduct@ora31;

　　SQL>insertintoproduct@ora31values(...);

　　SQL>Createtableproductasselect*fromproduct@ora31;

　　3.删除数据库链路：

　　SQL>Dropdatabaselinkora31;

　　(一）创建数据库触发器实现两个数据库之间实时数据传输。

　　DB1(UNIX)<-------DB2(NT)

　　若要将DB2中的数据复制到DB1，则需要在DB1中创建数据库链路指向DB2数据库。

　　[*]在对方的数据库(DB2)上建立触发器，即数据发送方的数据库上建立触发器。

　　[*]在数据发送方建立指向DB1的数据库链路。

　　[操作步骤（假设db1为ora31,db2为ora8i)：]

　　(1)在DB1上建立用来复制远程数据的表：

　　SQL>Createtableproduct

　　asselect*fromproduct@ora31;

　　(2)在DB2上建立到DB1的数据库链路：

　　SQL>Createdatabaselinkora31connecttouser30identifiedbyuser30using'ora31';

　　(3)在DB2上建立触发器：

　　SQL>CreateorReplacetriggerinsert_productbeforeinsertonproduct

　　foreachrow

　　Begin

　　Insertintoproduct@ora8i

　　values(:new.p_id,:new.p_name);

　　Endinsert_product;

　　//上述创建数据库触发器的语句，请以.号结束，然后以/执行。

　　/*查看SQL语句执行的错误信息：

　　SQL>showerrors

　　*/

　　(4)在DB2上测试数据的自动复制是否成功：

　　SQL>Insertintoproductvalues(1005,'测试商品');//在db2的表中插入一条数据

　　SQL>select*fromproduct;//检查数据是否正确插入本地表

　　SQL>select*fromproduct@ora31;//检查数据是否复制到了db1的表中

　　查询数据库链路信息：

　　SQL>selectusername,passwordfromuser_db_links;

　　数据更新：

　　SQL>CreateorReplacetriggerupdate_product

　　beforeupdateonproduct

　　foreachrow

　　Begin

　　updateproduct@ora8i

　　setp_id=:new.p_id,p_name=new.p_namewherep_id=:old.p_id

　　Endupdate_product;

　　数据删除：

　　SQL>CreateorReplacetriggerdelete_product

　　beforedeleteonproduct

　　foreachrow

　　Begin

　　deletefromproduct@ora8iwherep_id=:old.pid

　　Enddelete_product

　　（二）.创建快照（实体化视图）实现两个数据库之间定时数据库传输：

　　快照：要求主副站点数据库的用户名相同

　　(1)在主节点创建快照日志

　　语法：SQL>Createsnapshotlogon主节点表名;//主节点基表必须含有主键

　　(2)在副节点创建快照

　　语法：

　　SQL>Createsnapshot快照名

　　refresh刷新方式

　　next时间间隔

　　withprimarykey

　　forupdate

　　asselect*from主节点表名@数据库链路名;

　　[*]刷新方式：

　　Compelete:完全刷新

　　Force:强制刷新(建议使用，强制刷新自动先fast刷新，然后再force刷新)

　　Fast:快速刷新

　　[*]时间间隔：以天为单位。

　　sysdate+1/4//六个小时刷新一次

　　sysdate+1/1440//一分钟刷新一次

　　SQL>showuser

　　USER为"STUD29"

　　SQL>createsnapshotlogondept;

　　实体化视图日志已创建。

　　SQL>showuser

　　USER为"STUD29"

　　SQL>select*fromdept;

　　SQL>insertintodeptvalues(60,'testsnap','snapshot');

　　[2]副节点

　　SQL>connectsystem/ab

　　已连接。

　　SQL>createuserstud29identifiedbystud29;

　　用户已创建

　　SQL>grantconnect,resourcetostud29;

　　授权成功。

　　SQL>grantcreatesnapshottostud29;

　　授权成功。

　　SQL>connectstud29/stud29;

　　已连接。

　　SQL>connectsystem/ab

　　已连接。

　　SQL>grantcreatedatabaselinktostud29;

　　授权成功。

　　SQL>connectstud29/stud29

　　已连接。

　　SQL>createdatabaselinkora8iconnecttostud29identifiedbystud29using'tea';

　　数据库链接已创建。

　　SQL>createsnapshotdept

　　实体化视图已创建。

　　*******************************************************************************************/

　　创建索引(indexes):

　　语法：SQL>createindex索引名on表名(列名);

　　例：SQL>createindexindex_dept_dnameondept(dname);

　　索引数据字典：

　　SQL>selectindex_name,table_owner,table_name,fromuser_indexes;

　　五、创建序列(Sequences):

　　语法：SQL>Createsequence序列名

　　startwith起始编码

　　incrementby步长

　　maxvalue终止编码;

　　SQL>createsequenceid_code

　　startwith2

　　incrementby2

　　maxvalue999;

　　序列使用方法：

　　id_code.nextval//下一个值

　　id_code.currval//当前值

　　第一次要使用nextval，然后以后每次使用currval。

　　insertintostudentvalues(id_code.nextval,'姓名');

　　[*]||(双竖线)在oracle中是连接符号，将两个字符串连成一个，如：'A'||'B'='AB'

　　数据库分区技术

　　一、什么是数据分区？

　　数据分区是指把一个表划分成若干小块。在创建表的结构时应考虑好分区方案，选择表中某一列或多列

　　数据作为分区关键字，该关键字决定哪些数据分到哪些区。Oracle对分区进行管理，新插入数据自动存储到

　　相应的分区。

　　二、创建分区表：

　　SQL>Createtableemployee(

　　idnumber(7),

　　namevarchar2(20),

　　salnumber(7,2))

　　Partitionbyrange(sal)

　　(Partitionp1valueslessthan(500)tablespaceusers,

　　Partitionp2valueslessthan(800)tablespacetools,

　　partitionp3valueslessthan(1000)tablespacesystem);

　　//p1,p2,p3是三个分区的名字，users,tools,system是三个表空间的名字。lessthan是小于（不包含

　　）。

　　几点说明：

　　[.]所插数据不得大于LESSTHAN中的最大值

　　[.]可以使用MAXVALUE（如上面的语句中，要求工资不能大于1000，如果出现这种情况，则应该改成下面的

　　语句：

　　SQL>Createtableemployee(

　　idnumber(7),

　　namevarchar2(20),

　　salnumber(7,2))

　　Partitionbyrange(sal)

　　(Partitionp1valueslessthan(500)tablespaceusers,

　　Partitionp2valueslessthan(800)tablespacetools,

　　partitionp3valueslessthan(1000)tablespacesystem,

　　partitionp4valueslessthen(maxvale)tablespaceusers);

　　[.]不指定表空间时，则该区使用该用户的缺省表空间。

　　*查询每个用户的用户缺省表空间：

　　SQL>selectusername,default_tablespacefromdba_users;//使用dba用户查询

　　[*]查询可以使用的表空间名字：

　　SQL>selecttablespace_name,file_namefromdba_data_files;//使用dba（sys或system）来执行

　　SQL>selectdba_users.username,dba_users.default_tablespace,dba_data_files.file_name

　　fromdba_users,dba_data_files

　　wheredba_users.default_tablespace=dba_data_files.tablespace_name;

　　[temp表空间不能用于数据分区。]

　　三、分区表的查询方法：

　　SQL>select*fromemployee;//按没有分区的方法查询

　　SQL>select*fromemployeepartition(p1);//只查询p1分区的数据。

　　SQL>createtablepart3asselect*fromemployeepartition(p3);

　　四、分区表的修改：

　　1、增加分区：

　　SQL>AltertableemployeeADD

　　partitionp4valueslessthan(1500)tablespaceusers;

　　[*]分区数据字典：

　　SQL>selectpartition_name,high_value,tablespace_name

　　fromuser_tab_partitions

　　wheretable_name='EMPLOYEE';

　　2、删除分区:

　　SQL>AltertableemployeeDROPpartitionp4;//结构数据全部删除（相应分区及数据全部被删除)

　　SQL>AltertableemployeeTRUNCATEpartitionp4;//保留结构(即区还存在)，数据删除

　　3、修改区名：

　　SQL>AltertableemployeeRENAMEpartitionp4top5;

　　4、分区数据移动：将分区数据从一个表空间移动到另一个表空间

　　SQL>AltertableemployeeMOVEpartitionp4tablespacesystem;

　　5、分区的拆分：

　　SQL>AltertableemployeeSPLIT

　　partitionp3at(900)

　　into(partitionp31,partitionp32);

　　/*关于数据字典的几点说明：

　　v$打头的数据字典，后面不会以s结尾，例如：V$database,v$session;

　　user打头的，后面都会以s结尾(复数),如：user_tab_partitions,user_tables

　　dba打头的，有的以s结尾，有的不。

　　***************************************************************************/

　　6、分区的合并：

　　SQL>AltertableemployeeMERGEpartitionsp31,p32intopartitionp3;

　　SQL*Plus报表功能

　　/**********************插入内容：数据字典的一些说明*****************************

　　(1)user_XXX：用户，例如：user_tables

　　(2)dba_XXX:DBA专用

　　(3)all_XXX:本用户建的，或者其它用户创建本用户可以查询的（需要其它用户的授权）

　　(4)v$XXX:动态数据字典，如：v$database,v$instance,v$session，这些数据字典在oracle不启动时也

　　能查询

　　***********************************************************************************/

　　一、定义表头与表尾

　　SQL>ttitle'表头'

　　SQL>btitile'表尾'

　　失效：SQL>ttitleoff

　　SQL>btittleoff

　　二、定义列名

　　语法：SQL>column列名heading别名//别名不区分大小写

　　三、定义列格式：

　　SQL>column列名Format格式

　　常用列格式：An:A为字符，n为最大字符宽度。

　　$99.9999.99

　　9.99eeee

　　例：SQL>Columnsalformat$99.9999.99

　　SQL>ColumncommlikeSal

　　四、分组命令：

　　语法：SQL>breakon列名skipn

　　例：SQL>breakondeptnoskip2

　　SQL>select*fromemporderbydeptno;

　　五、统计计算：

　　语法：SQL>compute函数of统计列onskipn//可以使用的函数有：sum,max,min,avg,count,var(

　　斜方差),std(标准差)

　　例：SQL>computesumofsalondeptno

　　清除命令：SQL>clearcompute

　　SQL>clearbreak

　　SQL>clearcolumn

　　增加报表级统计：

　　SQL>breakondeptnoonREPORT

　　SQL>computesumofsalonreport

　　//整个报表出一个结果，上面两行都要运行，那么整个报表会根据你的设置出一个sumofsal的总计结果

　　。

　　函数

　　一、日期格式转换函数：to_char(日期变量,'格式')//格式要用单引号括起来

　　(1)日期格式构成方法:

　　年月日时分秒

　　yymmddhh(12小时制）miss

　　yyyymondy(星期)hh24（24小时制）

　　monthday

　　A.yy.mm.dd,yy/mm/dd,yy-mm-dd,yyyy.mm.dd,...加中文也可以，中文要用又引号括起来

　　SQL>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;//dual是一个虚拟表，任何用户

　　都可以使用。

　　二、聚组函数：从一组中返回汇总信息

　　聚组函数有：Sum,count,countdistinct,max,min,avg,stddev(标准差)

　　例：SQL>selectmin(sal),max(sal),avg(sal),sum(sal)formemp;

　　SQL>selectename,job,salfromempwheresal=(slectmax(sal)fromemp);

　　SQL>selectcount(*)fromemp;

　　复杂查询

　　一、连接查询：

　　问：Smith在哪里工作？

　　答：SQL>selectlocfromdept,empwhereename='SMITH'andemp.deptno=dept.deptno;

　　二、集合查询[请参考PowerPiont教程：SQL讲稿.pptP29,30,31]

　　集合操作是将多个基表的查询结果作UNION运算。

　　交操作:Intersect

　　差操作：MINUS

　　三、子查询(Subqueries):

　　子查询是在where子句中包含的查询语句，是由系列简单构成的复杂查询。

　　问：谁与smith在同一部门工作？

　　答：SQL>selectdeptnofromempwhereename='SMITH';

　　SQL>selectenamefromempwheredeptno=20;

　　将两个语句合起来：Selectenamefromempwheredeptno=(selectdeptnofromempwhereename=

　　'SMITH');

　　四、同义词(Synonym):

　　1、创建私有同义词：

　　语法：SQL>createsynonym同义词名for代替项;

　　user01:

　　SQL>grantselect,upateonproducttouser02;

　　user02:

　　SQL>Createsynonymproductforuser01.product;

　　SQL>select*fromproduct;//这里的product即user01.product。

　　同义词数据字典：

　　SQL>selectsynonym_name,owner,table_namefromall_synonyms;

　　SQL>selectsynonym_name,table_namefromuser_synonyms;

　　2、DBA可以创建公共同义词(PublicSynonym)：公共同义词全体用户可以存取

　　语法：SQL>createpublicsynonym公共同义词名for代替项;

　　SCOTT:

　　SQL>grantselectonpaymenttopublic;

　　SYSTEM:

　　SQL>createpublicsynonympaymentforscott.payment;

　　3.删除同义词：

　　User:SQL>dropsynonym私有同义词名;

　　DBA:SQL>droppublicsynonym公共同义词名;

　　找Oracle视频教程学oracle，上课课家教育IT培训专家。