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