Oracle SQL常用管理与操作命令

平时工作中常用的SQL语句命令

【常用管理操作】

[X] 连接数据库
sense@sense ~]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 30 09:44:34 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL>

sqlplus本地连接命令语法
sqlplus user/pass@sid [as sysdba]
例如：
SQL>sqlplus userid/pwd@sid [as sysdba]

sqlplus远程连接命令语法
sqlplus user/pass@host:port/sid [as sysdba]
例如：
SQL>sqlplus userid/pwd@database.company.com:1521/sid [as sysdba]

[X] 备份一张表
create table t_areatype0429  as select * from t_areatype

[X] 查看当前session使用状态
select SQL_HASH_VALUE, SQL_ADDRESS from v$session where machine='MSHOME\COMPUTER123';

[X] 查询某个外键属于哪张表：
select * from user_constraints c where c.constraint_type = 'R' and CONSTRAINT_NAME = 'FK_LOG_USERID'

查询数据库中变量绑定情况：
SELECT * FROM V$SQL WHERE PARSING_SCHEMA_NAME IN ('GAMEDEV');

[X] 数据库数据的导入导出相关操作
创建表空间
create tablespace gamedevtblsp logging datafile 'C:\oracle\oradata\orcl\gamedev.dbf' size 32m autoextend on next

32m maxsize 4000m extent management local;
create tablespace WEBGAME logging datafile 'C:\oracle\oradata\orcl\gamedev.dbf' size 32m autoextend on next 32m

maxsize 4000m extent management local;

创建用户指定表空间
create user gamedev identified by gamedev default tablespace gamedevtblsp;

修改用户表空间
alter user gamedev default tablespace webgame;

删除表空间
drop tablespace gamedevtblsp including contents and datafiles;

授予用户权限
grant dba to gamedev;

删除当前用户
drop user username cascade

导出表
exp user/pwd@dbname owner=user file=/oracle/test.dmp

导入表
imp user/pwd@dbname owner=user file=RedBaby_gamedev091127.dmp

查看拥有的所有表空间
select tablespace_name from dba_tablespaces;

查找某用户所有表  注意用户名大写
select owner, table_name from dba_tables where owner='SCOTT';

[X] 查询当前用户所有表格
select *   from   user_objects   where   upper(object_type)='TABLE' order by object_name
select   *   from   user_tables order by table_name
升序再加asc 降序再加desc

[X] 列出表所有字段
select   *   from   syscolumns   a,sysobjects   b   where   a.id=b.id   and   b.name='表名'

[X] Oracle回滚表空间数据文件误删除处理
http://database.ccidnet.com/art/1105/20061113/948643_1.html

【常用数据操作】

[X] 父类子类循环列表查询相关
select * from t_dressing where C_TYPECODE in( select c_typecode from t_dresstype where c_fatherid='01' or

c_fatherid2='01');

[X] inner join, left join, right join用法
select b.i_shopid, b.c_name, b.C_SHOPINTRO, c.i_sysuserid, c.c_nickname, c.c_status
from t_rank_shop a, t_shop b, T_supplyuser c
where a.i_shopid = b.i_shopid and b.c_isdress = '01' and b.c_shopstatus = '02' and b.i_supplyid = 

c.i_sysuserid(+)
order by a.i_Order asc

等价于

select b.i_shopid, b.c_name, b.C_SHOPINTRO, c.i_sysuserid, c.c_nickname, c.c_status from t_rank_shop a
inner join t_shop b on a.i_shopid = b.i_shopid
left join T_supplyuser c on c.i_sysuserid = b.i_supplyid
where b.c_isdress = '01' and b.c_shopstatus = '02'
order by a.i_Order asc

[X] 递归查询(树型查询)
http://www.cnblogs.com/zping/archive/2008/10/05/1303503.html

[X] 日期格式的字段作为查询条件
select * from t_treasureaward t where C_Createtime between to_Date('2010-4-26 00:00:00','yyyy-mm-dd hh24:mi:ss')

and to_Date('2010-4-27 00:00:00','yyyy-mm-dd hh24:mi:ss')

[X] count(distinct ...)用法
distinct指过滤掉重复的字段
http://topic.csdn.net/t/20060929/14/5056938.html

 

[X] 删除一条记录
delete from t_bar where barid = '3'

[X] 删除一个字段
alter   table   XX   drop   column   YY;

[X] 设置一列数据
update T_goods set C_AreaTypeId='001000000001'
update T_goods set C_CVideo ='http://192.168.1.80:8080/redbaby/video/hzpgg.flv'

[X] 设置一列数据中部分内容
update T_goods set C_CVideo = concat('http://192.168.1.96',substr(C_CVideo,20,length(C_CVideo)))

[X] 删除 表 全部 数据
truncate TABLE tableName

[X] oracle模糊查找
http://unix-cd.com/article/ShowArticle2.asp?ArticleID=281
http://www.techonthenet.com/sql/like.php

[X] oracle排名函数
select i_gsorce, rank
  from (select c_gameusername, I_GSORCE, rank() over(ORDER BY I_GSORCE) RANK
          from T_GAMESCORES
         where C_GAMEID = '3'
         ORDER BY I_GSORCE)
 where c_gameusername = 'test2whr'

 

【参考资料】
1. imp/exp 导入/导出命令
http://blog.csdn.net/chfzhb/archive/2008/08/03/2762272.aspx
2. SQL模糊查找
http://zhidao.baidu.com/question/95686600.html
http://hi.baidu.com/jb_0111/blog/item/15586cc60986871e9c163d42.html
http://sourceforge.net/projects/biblesearch/
http://it.china-b.com/zx/mssql/20090609/70408_1.html
http://www.linuxsir.org/bbs/thread46177.html
3. UNION ALL与UNION 区别
http://chengkai5319782.blog.163.com/blog/static/32902301200951714521924/

 本人转载出处：http://blog.sina.com.cn/s/blog_600ce18b0100urzz.html