Oracle常用命令及Sql

分页selectt2.*from(selectrownumrowno,t1.*from(select*fromAC_CUSTOMER)t1whererownum=10)t2where(rowno0)selectt2.*from(selectrownumrowno,t1.*from()t1whererownum=10)t2where(rowno0)左连接sql的leftjoin命令详解表aaidadate1a12a23a3表bbidbdate1b12b24b4两个表a,b相连接,要取出id相同的字段select*fromainnerjoinbona.aid=b.bid这是仅取出匹配的数据.此时的取出的是:1a1b12a2b2那么leftjoin指:select*fromaleftjoinbona.aid=b.bid首先取出a表中所有数据,然后再加上与a,b匹配的的数据此时的取出的是:1a1b12a2b23a3空字符同样的也有rightjoin指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据此时的取出的是:1a1b12a2b24空字符b4LEFTJOIN或LEFTOUTERJOIN。左向外联接的结果集包括LEFTOUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。三范式第一范式：1）任何给定行的列必须是只包含一个值；2）表中的每一行必须有相同数量的列；3）表中的每一行必须是唯一的即是不相同的；第二范式：1）必须满足第一范式；2）表中的所有非主键必须依赖一整个主键；第三范式：1）必须满足第二范式；2）表中的所有非主键必须相互独立；范式中还有复合主键的用法；查看表SQLselect*fromtab;查看表结构SQLdescribe表名简写以上命令SQLdesc表名系统时间selectto_char(sysdate,'yyyymmddhhss')fromdual;selectto_char(sysdate,'yyyy-mm-dd,hh24:mi:ss')fromdual;导入/导出导入：impcrddev/amc@amcfile=c:\amc.dmp导出：expcrddev/amc@amcfile=c:\amc.dmp如何单独备份一个或多个用户：D:\expscott/tigerfile=导出文件D:\expsystem/managerowner=(用户1,用户2,…,用户n)file=导出文件如何单独备份一个或多个表：D:\exp用户/密码tables=表D:\exp用户/密码tables=(表1,…,表2)D:\expsystem/managertables=(用户1.表)D:\expsystem/managertables=(用户1.表1,…,用户2.表1)如何导入指定表D:\expscott/tigerfile=a.dmpD:\imptest/testfromuser=scotttables=empfile=a.dmpD:\imptest/testtables=deptfile=a.dmp如果导出用户没有DBA权限，则导入用户可以不用指定fromuser、touser参数如果导出用户拥有DBA权限，则导入用户也必须拥有DBA权限）注释SQLcommentontable表is'表注释';注释已创建。SQLcommentoncolumn表.列is'列注释';注释已创建。查询表注释SQLselect*fromuser_tab_commentswherecommentsisnotnull;查询列注释SQLselect*fromuser_col_commentswherecommentsisnotnull;查看表空间查看表空间名字selectdistinctTABLESPACE_NAMEfromtabs;查看几个表空间selectcount(distinctTABLESPACE_NAME)fromtabs;selectb.file_id文件ID,b.tablespace_name表空间,b.file_name物理文件名,b.bytes总字节数,(b.bytes-sum(nvl(a.bytes,0)))已使用,sum(nvl(a.bytes,0))剩余,sum(nvl(a.bytes,0))/(b.bytes)*100剩余百分比fromdba_free_spacea,dba_data_filesbwherea.file_id=b.file_idgroupbyb.tablespace_name,b.file_name,b.file_id,b.bytesorderbyb.tablespace_name--dba_free_space--表空间剩余空间状况--dba_data_files--数据文件空间占用情况查看表空间是否自动扩展selectfile_name,autoextensible,increment_byfromdba_data_files;表空间自动扩展alterdatabasedatafile'path:\datafilename'autoextendonnext1Mmaxsize100M;表空间大小selecttablespace_name,count(*),sum(blocks),sum(bytes)/1024/1024fromdba_data_filesgroupbytablespace_name;使用情况selectdf.tablespace_name表空间名,totalspace总空间M,freespace剩余空间M,round((1-freespace/totalspace)*100,2)使用率%from(selecttablespace_name,round(sum(bytes)/1024/1024)totalspacefromdba_data_filesgroupbytablespace_name)df,(selecttablespace_name,round(sum(bytes)/1024/1024)freespacefromdba_free_spacegroupbytablespace_name)fswheredf.tablespace_name=fs.tablespace_name;使用情况SELECTa.tablespace_name表空间名,total表空间大小,free表空间剩余大小,(total-free)表空间使用大小,ROUND((total-free)/total,4)*100使用率%FROM(SELECTtablespace_name,SUM(bytes)freeFROMDBA_FREE_SPACEGROUPBYtablespace_name)a,(SELECTtablespace_name,SUM(bytes)totalFROMDBA_DATA_FILESGROUPBYtablespace_name)bWHEREa.tablespace_name=b.tablespace_name控制DDL数据定义语言：create、alter、drop、truncate（创建、修改结构、删除、截断）（其他：rename）DML数据操纵语言：insert、delete、select、update（增、删、查、改）DCL数据控制语言：grant、revoke（授权、回收）、setrole事务控制：commit、rollback、savepoint（其他：locktable、setconstraint(s)、settransaction）审计控制：audit、noaudit系统控制：altersystem会话控制：altersession其他语句：comment（添加注释）、explainplan、analyze（收集统计）、validate、call表结构复制SQLcreatetablebasselect*fromawhere1=2;SQLcreatetableb(b1,b2,b3)asselecta1,a2,a3fromawhere1=2;查看回滚段SQLSELECTSEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUSFROMDBA_ROLLBACK_SEGS;查看数据文件放置路径SQLselecttablespace_name,file_id,bytes/1024/1024,file_namefromdba_data_filesorderbyfile_id;查看当前连接用户SQLshowuser计算SQLselect100*20fromdual;连接字符串SQLselect列1||列2from表1;SQLselectconcat(列1,列2)from表1;复制数据用户间复制数据SQLcopyfromuser1touser2createtable2usingselect*fromtable1;视图groupby视图中不能使用orderby，但可用groupby代替来达到排序目的SQLcreateviewaasselectb1,b2frombgroupbyb1,b2;创建用户通过授权的方式来创建用户SQLgrantconnect,resourcetotestidentifiedbytest;备份createtableyhda_bakasselect*fromyhda;分组统计selectsid,count(*)fromyhdaagroupbysidhavingcount(*)1select函数ORDERBY–按照指定列排序返回结果的子句DISTINCT–只返回结果集合内唯一行的关键词COUNT--返回匹配查询的数据行总数数值的函数AVG–该函数返回指定列的平均值SUM–该函数把指定的列中的数字加起来MIN–该函数返回列中最小的非NULL值MAX–该函数返回列中的最大值GROUPBY–按列汇集查询函数结果的子句存储查询结果利用Oracle中的Spool缓冲池技术可以实现Oracle数据导出到文本文件。在OraclePL/SQL中输入缓冲开始命令，并指定输出的文件名：spoold:\output.txt在命令行中随便输入你的SQL查询：selectmobilefromcustomer;selectmobilefromclient;……在命令行中输入缓冲结果命令：spooloff;则系统将缓冲池中的结果都输出到output.txt文件中。以TAB键分隔删除表注意事项在删除一个表中的全部数据时，须使用TRUNCATETABLE表名;因为用DROPTABLE，DELETE*FROM表名时，TABLESPACE表空间该表的占用空间并未释放，反复几次DROP，DELETE操作后，该TABLESPACE上百兆的空间就被耗光了。having用法having子句对groupby子句所确定的行组进行控制,having子句条件中只允许涉及常量,聚组函数或groupby子句中的列.外联接+用法外部联接+按其在=的左边或右边分左联接和右联接.若不带+运算符的表中的一个行不直接匹配于带+预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接+,可以替代效率十分低下的notin运算,大大提高运行速度.例如,下面这条命令执行起来很慢用外联接提高表连接的查询速度在作表连接(常用于视图)时，常使用以下方法来查询数据:SELECTPAY_NO,PROJECT_NAMEFROMAWHEREA.PAY_NONOTIN(SELECTPAY_NOFROMBWHEREVALUE=120000);----但是若表A有10000条记录，表B有10000条记录，则要用掉30分钟才能查完，主要因为NOTIN要进行一条一条的比较，共需要10