oracle_cmd_common

一、表空间：
 create tablespace school datafile 'E:\Oracle\data\school.dbf' size 10m autoextend off;
  3.删除表空间:
 drop tablespace school including contents and datafiles;
 
二、用户：
 create user david identified by davidpass default tablespace school temporary tablespace student
  4.为用户授权：
 grant select on user_b.table_c to user_a;
 grant delete on my_test to an with grant option;
 grant connect,dba,resource to username;
 
三、表操作
  1.查询
  select * from tab;
 select id "ID号",name "姓名" from my_test;
 select distinct * from my_test;
 select * from aaa.my_test;
 select name,sum(sub1 + sub2 + sub3) from students group by name;
 select roll_no,name,class,sub2,rank()over(partition by class order by sub2 desc) rank from
 students;
  2.创建表
  create table account(acct varchar2(20));
 create table you_test as select * from my_test where 1=2;
  3.插入表
 insert into accout values('00001');
  insert into you_test select * from my_test where id = '1';
  4.删除表
 truncate table you_test;
 drop table you_test;
  5.更新表
  update my_test set name = 'yaoming' where id = '00002';
  刚开始我在网上搜索的用的下面这种方法：
update tbl1 a
　　set (a.col1, a.col2) = (select b.col1, b.col2
　　from tbl2 b
　　where a.key = b.key)
然后，使用后把我坑惨了。我直接在服务器上做的操作，做完后，居然登录不上系统了。后来才发现，有一个字段的值因为这个语句更新为空了，而登录时是用了视图的。这个字段正好是视图连接表时要用到的。好多用户反应也登录不上了，真是悲惨啊！
后来百度才知道原来是这个原因，如果 tbl1.key 的值在 tbl2.key 中没有此值时，这个更新的两个字段 tbl1.col1 和 tbl1.col2 字段会被更新为空值(null)。
速度借鉴大神的写法：
update tbl1 a
　　set (a.col1, a.col2) = (select b.col1, b.col2
　　from tbl2 b
　　where a.key = b.key)
　　where a.key in(select key from tbl2)//这一句是相当的关键啊。
oracle动态更新（将A表中的某个字段的值赋给B表中的某个字段）
通过Update语句将A表的某列值修改为B表中的指定列的值时，所用的sql语句在Oracle和SQL Server中是不一样的，
代码如下：
Oracle中的语句：
UPDATE   A 
SET A.COL1=(SELECT B.COL1 from B where  A.KEY=B.KEY [and 其它限制(可选)]  ) 
where 其它条件
SQL Server中更新语句：
update  A 
SET A.COL1=B.COL1 
FROM  A,B  where  A.KEY=B.KEY where 其它条件限制
若关联条件为多个，where后面用and 连接指定条件即可

五、运行外部文件
 @'c:\学生表.sql'
 
六、锁
 select * from employee where empno = 'E006' for update;
 select * from employee where empno = 'E006' for update wait 5;
 lock table employee in share mode [nowait];
 lock table employee in exclusive mode [nowait];

七、表分区
  partition by range(dateofjoining)
  (
      partition p1 values less than (to_date('01/04/2001','dd/mm/yyyy')) tablespace t1,
      partition p2 values less than (to_date('01/09/2005','dd/mm/yyyy')) tablespace t2,
       partition p3 values less than (maxvalue) tablespace t3
  )
 partition by hash(product_id)
 (
     partition p1,
     partition p2,
     partition p3
 )
 partition by hash(product_id)
 partitions 4 [store in (t1,t2,t3)]
 select table_name,partition_name from user_tab_partitions;
    3.复合分区
 create table Annual
 (
     product_id varchar2(5),
     sales_date date
 )
 partition by range(sales_date)
 subpartition by hash(product_id)
 subpartitions 8
 (
     partition p1 values less than (to_date('01/09/2004','dd/mm/yyyy'))
  )
 partition by list(department)
 (
     partition p1 values ('Accounts'),
     partition p2 values ('Management')
 )
 insert into employee values ('001','aaa','31-8月 2004');
 select * from employee_aaa partition (p2);
 delete from employee_aaa partition (p2);

八、分区维护
 alter table employee_aaa add partition p4 values less than(to_date('01/04/2006','dd/mm/yyyy'));
 alter table employee_aaa drop partition p4;
 alter table employee_aaa merge partitions p21,p22 into partition p2;
 alter table employee_aaa move partition p3 tablespace work;

九、同义词
 create public synonym pub_tick for aa.ticket_header;
    3.删除同义词
 drop synonym tick

十、序列
 create sequence new_seq
 increment by 2
 start with 25
 maxvalue 75
 minvalue 25
 cycle
 nocache;
 select new_seq.nextval from dual;
 select new_seq.currval from dual;
 alter sequence new_seq increment by 5;
 drop sequence new_seq;

十一、视图
 create view fleet as select * from fleet_header order by day
 update fleet set name = 'aa';
 drop view fleet;
   user_views

十二、索引
 create index aud_index on ticket_header(adults);
 create unique index place_ind on place_header(place_name);
 create index comp_index on route_header(route_id,route_no);
 create index rev_ind on route_header(route_no) reverse;
 alter index rev_index rebuild noreverse;
 create bitmap index bit_ind on route_header(cat_code);
 create index ucase_name_ind on fleet_header (upper(name));
 select * from fleet_header where upper(name) = 'SMITH';
 create index myind on order_mast(orderno) local;
 create index myind on order_mast(orderno) global;
 user_indexes
 user_ind_partitions
 user_ind_columns

十三、异常
 access_into_null
 case_not_found
 collection_is_null
//建立一个可执行的java控制台程序
 import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
public class calljdbc {
 /**
  * @param args
  */
 public static void main(String[] args) {
  // TODO Auto-generated method stub
  try{
  String driver_class = "oracle.jdbc.driver.OracleDriver";
  String connect_string ="jdbc:oracle:thin:@127.0.0.1:1521:orcl";
  Connection conn;
  Class.forName(driver_class);
  conn = DriverManager.getConnection(connect_string, "huoying", "huoying");
  cstmt.registerOutParameter(1,OracleTypes.CURSOR);
  cstmt.execute();
  ResultSet rset = (ResultSet)cstmt.getObject(1);
  while (rset.next ())
  {
   System.out.println( rset.getString("MID") );
   System.out.println( rset.getString("MNAME") );
  }
  cstmt.close();
  }catch(Exception e){
   e.printStackTrace();
  }
 }
}
}