ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [转]sql语句-003

[转]sql语句-003

原创 Linux操作系统 作者:31597359 时间:2019-07-13 08:21:05 0 删除 编辑
03

24、树形查询
create table zj(
bm number(8),
bmmc varchar2(20),
sjbm number(8)
)

insert into zj values(1,'aaa',0)
insert into zj values(11,'aaa1',1)
insert into zj values(12,'aaa2',1)
insert into zj values(111,'aaa11',11)
insert into zj values(112,'aaa12',11)
insert into zj values(113,'aaa13',11)
insert into zj values(121,'aaa21',12)
insert into zj values(122,'aaa22',12)
insert into zj values(123,'aaa23',12)
--
select bm,bmmc,sjbm,level
from zj
start with sjbm=0
connect by prior bm = sjbm
或者

select bm,bmmc,sjbm,level
from zj
start with sjbm=0
connect by sjbm = prior bm


25、快照
create snapshot SNAPSHOT_NAME
[storage (storage parameter)]
[tablespace TABLESPACE_NAME]
[refresh [fastcompleteforce]
[start with START_DATE next NEXT_DATE]
as QUERY;

create snapshot snapshot_to_study as select * from TABLE_NAME@to_study;
创建角色
create role aa identified by aaa;
授权 grant create snapshot,alter snapshot to aaa;
grant aaa to emp;
create snapshot SNAPSHOT_TO_HTML refresh complete start with sysdate next
sysdate+5/(24*60*60) as select * from a@to_html;
删除 drop snapshot snap_to_html
手工刷新快照,(调用DBMS_SNAPSHOT包中的refresh过程)DBMS_SNAPSHOT.refresh(snapshot_name,refresh_type);
begin
DBMS_SNAPSHOT.REFRESH('snap_to_html','c');
end;
对所有快照进行刷新
begin
DBMS_SNAPSHOT.REFRESH_ALL;
end;
怎样执行远程的内嵌过程
begin
otherdbpro@to_html(参数);
end;
26、用户管理
create a user: database authentication
sql> create user juncky identified by oracle default tablespace users
sql> temporary tablespace temp quota 10m on data password expire
sql> [account lock|unlock] [profile profilename|default];

<1>.查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;
<2>生成用户时指定缺省表空间
create user 用户名 identified by 口令 default tablespace 表空间名;

<3>重新指定用户的缺省表空间
alter user 用户名 default tablespace 表空间名
<4>查看当前用户的角色
SQL>select * from user_role_privs;
<5>查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
<6>查看用户下所有的表
SQL>select * from user_tables;
<7> alter user语句的quota子句限制用户的磁盘空间
如:alter user jf quota 10M on system;


27、查看放在ORACLE的内存区里的表
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

28、约束条件
create table employee
(empno number(10) primary key,
name varchar2(40) not null,
deptno number(2) default 10,
salary number(7,2) check salary<10000,
birth_date date,
soc_see_num char(9) unique,
foreign key(deptno) references dept.deptno)
tablespace users;

关键字(primary key)必须是非空,表中记录的唯一性
not null 非空约束
default 缺省值约束
check 检查约束,使列的值符合一定的标准范围
unqiue 唯一性约束
foreign key 外部键约束

29、查看创建视图的select语句
SQL>set view_name,text_length from user_views;
SQL>set long 2000; 说明:可以根据视图的text_length值设定set long 的大小
SQL>select text from user_views where view_name=upper('&view_name');

30、查看同义词的名称
SQL>select * from user_synonyms;

31、用Sql语句实现查找一列中第N大值
select * from
(select t.*,dense_rank() over (order by sal) rank from employee)
where rank = N;

32 虚拟自段
<1>. CURRVAL 和 nextval
为表创建序列
CREATE SEQUENCE EMPSEQ ... ;
SELECT empseq.currval FROM DUAL ;
自动插入序列的数值
INSERT INTO emp
VALUES (empseq.nextval, 'LEWIS', 'CLERK',
7902, SYSDATE, 1200, NULL, 20) ;

<2>. ROWNUM
按设定排序的行的序号
SELECT * FROM emp WHERE ROWNUM < 10 ;

<3>. ROWID
返回行的物理地址
SELECT ROWID, ename FROM emp WHERE deptno = 20 ;

33、对CLOB字段进行全文检索
SELECT * FROM A WHERE dbms_lob.instr(a.a,'K',1,1)>0;

34. 特殊字符的插入,比如"&"
insert into a values (translate ('at{&}t','at{}','at'));

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8000572/viewspace-62277/,如需转载,请注明出处,否则将追究法律责任。

上一篇: [转]sql语句-002
下一篇: [转]sql语句-004
请登录后发表评论 登录
全部评论

注册时间:2006-10-07

  • 博文量
    120
  • 访问量
    77810