oracle记录

select * from user_all_tables--查询当前用户下的所有表
select rownum,ename from emp where rownum<=10 
--rownum是在提取一行后就添加rownum
select rownum,ename from emp where rownum>10
--这样写是错误的，rownum是在提取一行后就添加rownum
--可以先创建视图，在查询，如下
create or replace view w as select rownum id,ename from emp
select * from w where id>10
--或者也可以这样
select * from (select rownum id,ename from emp) a where a.id>10

grant create view to scott--给scott用户授予创建视图的权限
select rowid from emp
select job,sum(sal) from emp group by job
select avg(sal),deptno from emp group by deptno having deptno in (10,20) 
select avg(sal),deptno from emp where deptno in (10,20) group by deptno
select avg(sal),deptno from emp where deptno in (10,20) group by deptno having avg(sal)<2500
--where的作用是行的过滤，having的作用是组的过滤

--分组查询中，在列列表中出现的列名，必须出现在分组条件中，或者是聚合函数

select e.ename,d.dname from emp e join dept d using(deptno)--等值连接中的列名必须是相同的
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno
select ename,dname from emp cross join dept--产生笛卡尔积
select e.ename,g.grade from emp e,salgrade g where e.sal between g.losal and g.hisal
select e.ename,g.grade from emp e join salgrade g on e.sal between g.losal and g.hisal

--创建序列 sequence
create sequence myseq;
create table testseq (
       nextseq number,
       currseq number
);
insert into testseq values(myseq.nextval,myseq.currval);
select * from testseq;
drop sequence myseq;

create sequence myseq increment by 2;--设定每次增长是2

create sequence myseq increment by 2 start with 10;--序列默认是从1开始的，可以使用startwith 来设定从几开始

select * from tab;--查询当前用户下有哪些表
select sysdate from dual;
create user test_user identified by abcd--创建用户 只有管理员有权限创建用户
grant create session to test_user  --给用户授予创建session的权限
select * from scott.emp;

方便以后学习 ,附有图解资料（看视频的时候剪的）


~~^-^~~