Oracle常用SQL操作技巧.docx
《Oracle常用SQL操作技巧.docx》由会员分享，可在线阅读，更多相关《Oracle常用SQL操作技巧.docx（11页珍藏版）》请在冰豆网上搜索。
Oracle常用SQL操作技巧
Oracle常用SQL操作技巧
1.以管理员身份连接到数据库实例
sqlplus"/assysdba"
2.查看当前连接数据库实例名称、状态、是否启用归档
selectname,open_mode,log_modefromv$database;
3.查看当前数据库版本
select*fromv$version;
4.查看指定数据表的字段结构
descv$database;
5.本机安装有多个数据库实例
查看已安装好的实例名称，例如下图中的DBSVR001
（如果本机安装多个数据库实例，直接执行sqlplus"/assysdba"会连接到最后安装的一个实例，那我们想连接到指定的实例怎么办呢？
例如要连接的实例为：
DBSVR001）
setoracle_sid=DBSVR001
6.以指定用户连接到数据库
sqlplussdzy_mis/cont@lb_dbsvr001
7.查看当前连接用户有什么角色、什么权限
select*fromuser_role_privs;
select*fromsession_privs;
8.查看当前用户的系统权限和表级权限
select*fromuser_sys_privs;
select*fromuser_tab_privs;
9.获取当前连接Oracle的用户信息
SELECTS.USERNAME,S.OSUSER,S.MACHINE,S.PROGRAM,S.LOGON_TIME
FROMV$SESSIONS
WHERES.SID=（SELECTDISTINCTSIDFROMV$MYSTAT）;
10.查看表数据
例如：
表名SYS_META_CTVALUE；存在列：
CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER
SELECTCT_ID,KEY_CODE,KEY_NAME,KEY_ORDER
FROMSYS_META_CTVALUE
WHERECT_IDIN（'SYS_SYSTEMTYPE','SYS_OPERATOR'）
ORDERBYCT_ID,KEY_ORDER;
11.创建一个临时表drop01
例如：
原表名SYS_META_CTVALUE；存在列：
CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER
新表名：
DROP01；新列名：
CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2
其中列KEY_ORDER2为原值扩展为2位，不足2位前补0
CREATETABLEDROP01AS
SELECTCT_ID,KEY_CODE,KEY_NAME,KEY_ORDER
LPAD（TO_CHAR（KEY_ORDER）,3,'0'）ASKEY_ORDER2
FROMSYS_META_CTVALUE
WHERECT_IDIN（'SYS_SYSTEMTYPE','SYS_OPERATOR'）
ORDERBYCT_ID,KEY_ORDER;
注意：
此种生成的新表无主键。
12.在已存在数据表中生成一个索引列
SELECTROW_NUMBER（）OVER（ORDERBYCT_ID,KEY_ORDER）ASTMP01
CT_ID,KEY_ORDER,KEY_CODE,KEY_NAME,KEY_ORDER2
FROMDROP01;
13.在已存在数据表中生成一个索引列、一个分类索引列
SELECTROW_NUMBER（）OVER（ORDERBYCT_ID,KEY_ORDER）ASTMP01
ROW_NUMBER（）OVER（PARTITIONBYCT_IDORDERBYCT_ID,KEY_ORDER）ASTMP02
CT_ID,KEY_ORDER,KEY_CODE,KEY_NAME,KEY_ORDER2
FROMDROP01;
14.在已存在数据表中生成一个类似主键列（值不唯一列）
SELECTROW_NUMBER（）OVER（ORDERBYCT_ID,KEY_ORDER）ASTMP01
ROW_NUMBER（）OVER（PARTITIONBYCT_IDORDERBYCT_ID,KEY_ORDER）ASTMP02
SYS_GUID（）ASTMP03
CT_ID,KEY_ORDER,KEY_CODE,KEY_NAME,KEY_ORDER2
FROMDROP01;
15.生成两个数据表，制作测试数据
表名：
DROP11,DROP12
列名：
POID,T_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2
值列要求：
POID列值不能重复
--创建临时表drop11
CREATETABLEDROP11AS
SELECTSYS_GUID（）ASPOID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2
FROMDROP01;
--创建临时表drop12
CREATETABLEDROP12AS
SELECTPOID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2
FROMDROP11;
--11:
13,12:
13SUM11:
13+13=26
INSERTINTODROP11（POID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2）
（SELECTPOID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2
FROMDROP12）;
--11:
26,12:
13SUM11:
26+13=39
INSERTINTODROP12（POID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2）
（SELECTPOID,CT_ID,KEY_CODE,KEY_NAME,KEY_ORDER,KEY_ORDER2
FROMDROP11）;
--保存数据
COMMIT;
--查看两个表的数据行数
SELECT
（SELECTCOUNT
（1）FROMDROP11）ASDROP11_COUNT
（SELECTCOUNT
（1）FROMDROP12）ASDROP12_COUNT
FROMDUAL;
15.给数据表加主键
altertableDROP11
addconstraintDROP11_POIDprimarykey（POID）;
16.快速启动网络配置向导
netca
升级会员 