Oracle基础

--查询表名和表空间
select t.TABLE_NAME,t.TABLESPACE_NAME from user_tables t

--查询表主键，外键
--P -Primary Key -Object
--R -Referential AKA Foreign Key -Column
--U -Unique Key -Column

select au.constraint_type,au.constraint_name,au.* from user_constraints au;
select cu.* from user_cons_columns cu;


--查询索引
select d.index_name,d.table_name,d.index_type,d.uniqueness from user_indexes d;
select c.INDEX_NAME,c.TABLE_NAME,c.COLUMN_NAME from user_ind_columns c;

--建立表空间
select name from v$datafile;
create  tablespace  bruce_temp  
        datafile   'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\bruce_temp.DBF'    
        size   300m;
        
drop tablespace bruce_temp including contents and datafiles cascade constraints;
--including contents 删除表空间中的内容，如果删除表空间之前表空间中有内容，而未加此参数，表空间删不掉，所以习惯性的加此参数
--including datafiles 删除表空间中的数据文件 
--cascade constraints 同时删除tablespace中表的外键参照        


--建立索引，如表空间使用br_temp
drop index tem_index;
create index tem_index_c1 on testIndex (c1) tablespace br_temp; 
create index tem_index_c2 on testIndex (c1) tablespace br_temp; 

--merge into 用法
/*
语法如下：
　　MERGE INTO table_name alias1 
　　USING (table|view|sub_query) alias2 
　　ON (join condition) 
　　WHEN MATCHED THEN 
　　UPDATE table_name 
　　SET col1 = col_val1, 
　　col2 = col2_val 
　　WHEN NOT MATCHED THEN 
　　INSERT (column_list) VALUES (column_values);
*/
merge into TestCount c   
using (select distinct p.lisno, p.pcode   
         from TestCount p   
        where p.pcode like '10%')  p 
on (p.lisno = c.lisno)   
when matched then  
  update set c.pcode = p.pcode where c.pcode not like '10%';  
commit;