oracle基础（2）

 

--分页
select * from emp;

select a.empno from (select * from emp) a;

select * from emp e where e.deptno in (select d.deptno from dept d where d.dname like '%A%')

--第一页
select * from (select rownum count,emp.* from emp) a where a.count>=1 and a.count<=3;
--第二页
select * from (select rownum count,emp.* from emp) a where a.count>=4 and a.count<=6;

--给指定用户创建私有同义词的权限
GRANT CONNECT , CREATE SYNONYM TO yhy;

select * from scott.emp;

--创建私有同义词别名（谁创建谁用）
create synonym e for scott.emp;

select * from e

--创建共有同义词
create public synonym pub_emp for scott.emp;

select * from pub_emp;

--创建序列
create sequence jseq
start with 1
increment by 1

select * from java0723

delete from java0723

insert into java0723 values(jseq.nextval,'熊大','35723992','男'，'淄博',to_date('2019-09-23','yyyy-mm-dd'),10);

insert into java0723 values(jseq.nextval,'熊二','357239924','男'，'淄博',to_date('2019-09-23','yyyy-mm-dd'),10);

select jseq.currval from dual;

--赋值给指定用户权限
grant create view to scott;

--创建视图
create view empinfo
as
select empno,ename,job,mgr,hiredate,sal,comm,emp.deptno from scott.emp join scott.dept on emp.deptno=dept.deptno

select * from empinfo

--索引

--创建测试表
create table t_testseq
(
id number,
name varchar2(10)
);
--创建序列
create sequence seq_value
start with 1
increment by 1;

--普通查询
Select * from t_testseq where id = 99999;

--创建索引
create index testIndex on t_testseq(id);
--删除索引
drop index testIndex