oracle经典sql语句(表空间,表结构)

****************************************************************表空间,用户的创建

--创建临时表空间
create temporary tablespace test_temp
tempfile 'E:\oracle\product\10.2.0\oradata\orcl\test_temp01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

--创建数据表空间
create tablespace test_data
logging
datafile 'E:\oracle\product\10.2.0\oradata\orcl\test_data01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

--创建用户并指定表空间
create user xmh identified by xmh
default tablespace test_data
temporary tablespace test_temp;


（1）创建用户
Create user 用户名 identified by 密码；（如果是纯数字则要加双引号”654321”，如果是字母就不用）

（2）授权给某个用户
Grant connect,resource to 用户名；(只有用户有了connect 和 resource后才能操作其他表)

（3）授DBA 权限化
Grant dba to 用户名；

（4）给用户创建会话的权限：

grant create session to DB_USER

（5）撤权：
revoke 权限... from 用户名;

（6）删除用户：
drop user username cascade （cascade 保证彻底删除）




****************************************************************表增加约束,表结构更改
select count(*),min(id),username from test group by username;
select * from t where id in (select a1.id from test a1,test a2 where a1.id>a2.id and https://www.360docs.net/doc/ba17465191.html,ername = https://www.360docs.net/doc/ba17465191.html,ername)
select a2.*,a1.* from test a1,test a2 where https://www.360docs.net/doc/ba17465191.html,ername = https://www.360docs.net/doc/ba17465191.html,ername;--自连接
select * from test t1 left join test_f t2 on t1.fid = t2.id where t2.id is null--左外连接

alter table customer_classify_info add classify varchar2(32) --添加列
alter table test add (mobile varchar2(20),address varchar2(20))
alter table customer_classify_info drop (sell_channel) --删除列
alter table mytest drop column mobile;
alter table customer_accessory_info modify auth_papers_file varchar2(256) -- 修改列类型
alter table syn_user rename column IFUSERLEVEL to LFUSERLEVEL --修改列名

alter table test add constraint pk primary key (id)--追加主键约束
alter table test add constraint uk unique (password)--追加唯一性约束
alter table test add constraint fk foreign key (fid) REFERENCE test_f(id);--追加外键
alter table test add constraint ck check(age between 20 and 33)--追加check约束
alter table test add check(password <> '1')
create index myindex on test(username) --追加索引
alter table test drop constraint SYS_C008562 --删除各种约束
drop index myindex --删除索引
alter table test add primary key (id) using index
alter table test modify password unique

--查看约束名 主键约束、唯一性约束
select cu.* from user_cons_columns cu, user_constraints au where
cu.constraint_name = au.constraint_name and au.constraint_type = 'C' and au.table_name = 'TEST'
select * from user_cons_columns
select * from us

er_constraints



select * from all_tables where table_name like '%USER%';--查询所有table
--拼接sql语句
select 'drop table '||TABLE_NAME||';' from all_tables where last_analyzed > to_date('2014/1/17 00:00:00','yyyy/mm/dd hh24:mi:ss')
--取差集的形式 找出排序后的指定某一项
select * from (select * from emp order by empno desc) where rownum<=3
minus
select * from (select * from emp order by empno desc) where rownum<=2


SELECT * FROM ALL_TABLES where owner = 'IAM20130726' AND table_name like '%E/_USER%' escape '/'

select * from org_subacc where login_name='xmh'
union all
select * from org_subacc where login_name='Guest'//union all
select sum(empno) from EMP ;//sum函数
select * from emp where LOWER(ename) = lower('XmH3');//lower函数

alter table E_ElecProcessRes add primary key (UUID) using index//不知道约束名

select app.resnum,acc.login_name,acc.accountid from e_oper_authz_resacc_rel oAccRel
left join org_subacc acc
on acc.subacc_uuid = oAccRel.resaccuuid
left join e_appaccount ea
on ea.subacc_uuid = acc.subacc_uuid
left join e_appresource app
on app.resuuid = acc.resuuid
left join org_person p
on oAccRel.Personuuid = p.person_uuid
WHERE p.login_name = 'xmh'

select SYN_TIME from (select SYN_TIME from syn_info order by SYN_TIME desc) where rownum = 1
select * from sms_orderbill where createtime between to_date('2013-01-03','yyyy-mm-dd') and to_date('2013-01-04','yyyy-mm-dd');
select * from sms_orderbill where userid > '2013-01-05 02:00:06'
select * from test where ( username is null or username like '% %' ) and id = '3'

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。




//查主键
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'TEST'


1、查找表的所有索引（包括索引名，类型，构成列）：

select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表

2、查找表的主键（包括名称，构成列）：

select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查询的表

3、查找表的唯一性约束（包括名称，构成列）：

select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'U' and au.table_name = 要查询的表

4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）：

select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查询的表

查询外键约束的列名：

select * from user_cons_columns cl where cl.constraint_name = 外键名称

查询引用表的键

的列名：

select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名

5、查询表的所有列及其属性

select t.*,https://www.360docs.net/doc/ba17465191.html,MENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = 要查询的表