oracle开发常用命令

1、创建表空间

create tablespace aaa
logging  
datafile 'D:\oracle10\product\10.2.0\oradata\orcl\aaa.dbf' 
size 1024m  
autoextend on  
next 50m maxsize 20480m  
extent management local

注：创建表空间：aaa

2、创建用户

create user aaa identified by aaa default tablespace aaa

注：创建用户aaa，密码为aaa，表空间为aaa

3、授权

grant dba,resource,connect to aaa
grant select any table to aaa

 注：授权分为角色授权和功能授权，上一句将dba，resouce，connect三个角色授权给用户aaa，下一句将select any table功能授权给用户aaa

4、数据库导出

exp aaa/aaa@orcl file=d:/aaa.dump owner=aaa

注：将用户aaa用例导出，文件名为aaa.dump

5、数据库导入

imp aaa/aaa@orcl file=d:/aaa.dump fromuser=aaa touser=aaa

注：将aaa.dump文件导入到用户名为aaa用户下

6、创建表

create table test(id varchar2(32),name varchar2(128))

7、删除表

drop table test

8、修改表名称

rename test to test1

9、修改表字段

alter table test modify ID VARCHAR2(322)
alter table test rename column ID to IDs
alter table test add (age varchar2(32))
alter table test drop(id)

注：修改字段类型，修改字段名称，添加字段，删除字段

10、合并列字段

select  account_id,wmsys.wm_concat(address) from c_customer_address group by account_id

注将统一账号的多个住址合并显示

11、将一张表中的数据插入到另一张表中

insert into test1(id,name) select id,name from test2

12、导出、导入一张表

exp aaa/aaa@orcl file=d:/1.dump tables=(test)

imp aaa/aaa@orcl file=d:/1.dump tables=(test)

13、case when的使用

select  case a.state when '1' then '试用' when '2' then '转正' else '离职' end "state" from user a

14、decode的使用

select decode(a.state,'1','试用','2','转正','离职')  "state" from user a

15、排序

select row_number() over(partition by u.department order by u.staff_id) rn,
       u.staff_id,
       u.department
  from sc_login_user u

注：将数据按department分组后，按staff_id从小到大排序

select t.staff_id, rownum
  from (select u.staff_id from sc_login_user u order by u.staff_id) t

注：将数据按staff_id从小到大排序

16、分页

select *
  from (select u.*, rownum rn
          from (select l.staff_id, l.department
                  from sc_login_user l) u
         where rownum > 0)
 where rn < 11

17、取整数部分

select round(1.2) from dual