select * from emp;
select * from dept;
select * from salgrade;
select ename || sal from emp;
select distinct deptno from emp;
select distinct deptno, job from emp;
select * from emp where deptno = 10;
select * from emp where deptno <> 10;
select * from emp where sal between 500 and 1500;
select *
from emp
where sal >= 500
and sal <= 1500;
select * from emp where comm is null;
select * from emp where comm is not null;
select * from emp where sal in (800, 1500, 2000);
select * from emp where ename in ('SMITH', 'TURNER', 'CHEN');
select * from emp where ename like '%ALL%';
select * from emp where ename like '%_A%';
select * from emp where ename like '%$%%' escape '$';
select * from emp order by empno asc;
select * from emp order by deptno asc, empno desc;
select substr(ename, 2, 3) from emp;
select ename, sal * 12 from emp;
select ename, sal * 12 annual_salary from emp;
select 2 * 3 from dual;
select sysdate from dual;
select chr(65) from dual;
select ascii('A') from dual;
select round(25.863) from dual;
select round(25.863, 1) from dual;
select round(25.863, -1) from dual;
select to_char(sal, '$99,999.9999') from emp;
select to_char(sal, 'L99,999.9999') from emp;
select to_char(sal, 'L0000.0000') from emp;
select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp;
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
select *
from emp
where hiredate > to_date('1985-6-8 18:30:20', 'YYYY-MM-DD HH24:MI:SS');
select * from emp where sal > to_number('$1,500.00', '$9,999.99');
select ename, sal * 12 + nvl(comm, 0) from emp;
select round(avg(sal), 2) from emp;
select count(*) from emp where deptno = 10;
select count(comm) from emp;
select count(distinct deptno) from emp;
select deptno, avg(sal) from emp group by deptno;
select deptno, job, max(sal) from emp group by deptno, job;
select ename, sal from emp where sal = (select max(sal) from emp);
select deptno, max(sal) from emp group by deptno;
select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;
select deptno, avg(sal)
from emp
where sal > 1200
group by deptno
having avg(sal) > 1500
order by avg(sal) desc;
select ename, sal from emp where sal > (select avg(sal) from emp);
select ename, sal
from emp
join (select max(sal) max_sal, deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;
select ename, dname from emp, dept where emp.deptno = dept.deptno;
select ename, dname from emp join dept on (emp.deptno = dept.deptno);
select ename, dname from emp join dept using (deptno);
--
select ename, grade
from emp e
join salgrade s
on (e.sal between s.losal and s.hisal);
--
select ename, dname, grade
from emp e
join dept d
on (e.deptno = d.deptno)
join salgrade s
on (e.sal between s.losal and s.hisal)
where e.ename not like '_A%';
--
select e1.ename, e2.ename
from emp e1
left join emp e2
on e1.mgr = e2.empno;
--
select e.ename, d.dname
from emp e
right join dept d
on e.deptno = d.deptno;
--
select e.ename, d.dname from emp e full join dept d on e.deptno = d.deptno;
--
select deptno, avg_sal, grade
from (select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s
on (t.avg_sal between s.losal and s.hisal);
--
select deptno, avg(grade)
from (select deptno, grade
from emp
join salgrade s
on (emp.sal between s.losal and s.hisal))
group by deptno;
--
select ename from emp where empno in (select mgr from emp);
select ename from emp where empno in (select distinct mgr from emp);
--
select distinct sal
from emp
where sal not in
(select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);
--
select deptno, avg_sal
from (select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal)
from (select avg(sal) avg_sal from emp group by deptno));
--
select dname
from dept
where deptno =
(select deptno
from (select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal)
from (select avg(sal) avg_sal from emp group by deptno)));
--
select deptno, avg_sal
from (select avg(sal) avg_sal, deptno from emp group by deptno)
where avg_sal = (select max(avg(sal)) from emp group by deptno);
--grant create table,create view to scott;
create view v$_dept_avg_sal as
select deptno, grade, avg_sal
from (select avg(sal) avg_sal, deptno from scott.emp group by deptno) t
join scott.salgrade s
on (t.avg_sal between s.losal and s.hisal);
select dname, t.deptno, t.grade, t.avg_sal
from v$_dept_avg_sal t
join scott.dept
on t.deptno = dept.deptno
where t.grade = (select min(grade) from v$_dept_avg_sal);
--处理空值
select ename
from emp
where empno in (select distinct mgr from emp where mgr is not null)
and sal >
(select max(sal)
from emp
where empno not in
(select distinct mgr from emp where mgr is not null));
--
insert into dept values (50, 'game', 'beijing');
select * from dept;
rollback;
select * from dept;
create table emp2 as
select * from emp;
select * from emp2;
create table dept2 as
select * from dept;
select * from dept2;
insert into dept2 (deptno, dname) values (60, 'game2');
select * from dept2;
insert into dept2
select * from dept;
select * from dept2;
select * from emp2 where rownum <= 5;
select rownum r, ename from emp2;
select r, ename from (select rownum r, ename from emp2) where r > 10;
select ename, sal
from (select ename, sal from emp2 order by sal desc)
where rownum <= 5;
select ename, sal, r
from (select ename, sal, rownum r
from (select ename, sal from emp order by sal desc))
where r >= 6
and r <= 10;
update emp2 set sal=sal*2,ename=ename||'-' where deptno=10;
select sal,ename from emp2 where deptno=10;
delete from dept2 where deptno<25;
rollback;
相关推荐
oracle查询语句精典30题,会了这30题,所以的查询,你都可以搞定了!
Oracle中的select into Oracle中没有select into的用法! 在某些数据库中有select into的用法,用法是: select valueA,valueB into tableB from tableA; 上面这句语句的意思是将tableA表中的valueA和valueB字段的值...
是我自己平时经常用到的总结一下,分享给大家!
但是奇怪的是执行其他的select语句却是可以执行的。 原因和解决方法 这种只有update无法执行其他语句可以执行的其实是因为记录锁导致的,在oracle中,执行了update或者insert语句后,都会要求commit,如果不commit...
ORACLE经典语句汇总 -- 字符串左填充和右填充,默认填充空格 -- 产生1~99行数据,少于一位则补0 -- 刪除相同行 -- 随机数 -- 产生业务流水号 -- 查询某张表中有哪些字段 -- 自循环表中 由叶子节点查父节点 -- 查子...
ORACLE-Select语句执行顺序及如何提高Oracle基本查询效率.pdf
oracle10Gselect语句自学笔记.chmoracle10Gselect语句自学笔记.chm
编写简单的SELECT语句,能够教会你一些简单的select语句的编写。
00587 Oracle公司内部数据库培训资料-Les01基本SQL SELECT语句(PPT 29页).ppt
oracle的SQL语句的一些经验总结,里边有很多大家和自己的东西。
select * from (select a.*,rownum rn from (select * from tablename) a where rownum) where rn>2
shell连接oracle数据库工具脚本:支持select/insert/update/delete 部署位置:/root/sysmonitor db:数据库文件夹 dbconfig.properties:数据库配置文件, dbConnectTest.sh:连接测试文件 dbExecurteSQL.sh:...
介绍可以在SELECT语句中调用DML函数的一个例子
基本select语句总结 oracle数据库基本操作
Oracle数据库 编写基本的SQL SELECT 语句
自动生成表分析sql语句和索引分析语句: 表分析语句 analyTab.sql SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS ;' FROM USER_TABLES; ----------------------------------------------...
从一条select语句看oracle数据库查询原理,浅析了oracle的查询过程。是存储过程的入门。
Oracle性能分析——使用set_autotrace_on和set_timing_on来分析select语句的性能.doc
Oracle公司内部数据库培训资料01基本SQLSELECT语句.ppt
OracleSelect小工具, 不用安装说明插件就可以连上oracle并执行语句,用于验证是否可以正常连接到Oracle数据库