oracle记录
select * from user_all_tables--查询当前用户下的所有表select rownum,ename from emp where rownum<=10 --rownum是在提取一行后就添加rownumselect rownum,ename from emp where rownum>10--这样写是错误的，rownum是在提取一行后就添加rownum--可以先创建视图，在查询，如下create or replace view w as select rownum id,ename from empselect * from w where id>10--或者也可以这样select * from (select rownum id,ename from emp) a where a.id>10grant create view to scott--给scott用户授予创建视图的权限select rowid from empselect job,sum(sal) from emp group by jobselect 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 deptnoselect 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.deptnoselect e.ename,d.dname from emp e,dept d where e.deptno=d.deptnoselect 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.hisalselect e.ename,g.grade from emp e join salgrade g on e.sal between g.losal and g.hisal--创建序列 sequencecreate 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;--设定每次增长是2create 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;
