`

Oracle select 语句 案例大全

 
阅读更多

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;

分享到:
评论

相关推荐

    shell连接oracle数据库执行SQL工具脚本-支持select+inset+update

    shell连接oracle数据库工具脚本:支持select/insert/update/delete 部署位置:/root/sysmonitor db:数据库文件夹 dbconfig.properties:数据库配置文件, dbConnectTest.sh:连接测试文件 dbExecurteSQL.sh:...

    oracle笔记,入门体验篇,基本的SQL-SELECT语句

    oracle笔记,入门体验篇,基本的SQL-SELECT语句,简单入门,有具体的案例代码!

    Oracle上课PPT和案例

    set verify(ver)on/off:是否显示替换变量被替换前后的语句(用在sql语句中有替换变量或以& &&开头的时候); v_ename emp.ename%type; --v_ename是定义的变量;emp是查选的表ename是emp表中的字段,然后%type就是...

    韩顺平oracle学习笔记

    4 学会在oracle中编写简单的select语句 第1讲:基础语法 内容介绍: 1.为什么学习oracle 2.介绍oracle及其公司的背景 3.学会安装、启动、卸载oracle 4.oracle开发工具 5.Sql*plus的常用命令 6.oracle用户管理 ...

    oracle所有知识点笔记(全)

    这是我自己学习oracle的时候,写的代码案例和笔记,基本上每一个知识点都写的很清楚!...基本的sql_Select语句 运算符,多表联查,排序,组函数,序列,索引,同义词, 约束,创建和管理表,单行函数,过滤数据等等

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距今五十年前。简单来说是本身可视...

    oracle知识点笔记(语法案例等等)

    oracle知识点笔记,语法,触发器,存储过程,存储函数,流程控制,游标,异常处理,记录类型,视图,控制用户权限,高级子查询,set运算符,基本的sql_Select语句等等

    《Oracle Database编程指南》之15:聚合函数(Aggregate Functions)

    内容导航1、定义2、函数列表3、案例代码 1、定义 聚合函数(Aggregate Functions)根据行组而不是单行...如果SELECT语句不包含GROUP BY子句,查询的结果集只有一个分组,Oracle将SELECT列表中的聚合函数应用于结果集

    Oracle创建视图(View)

    视图是存储在数据字典里的一条select语句。通过创建视图可以提取数据的逻辑上的集合或组合。 视图的优点: 1.对数据库的访问,因为视图可以有选择性的选取数据库里的一部分。 2.用户通过简单的查询可以从复杂...

    ORACLE数据库恢复实例

    word 文档,各种案例.

    经典SQL语句大全

    select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3) 5、说明:显示文章、提交人和最后回复时间 select a.title,a.username,b.adddate from table a,(select max...

    数据库操作语句大全(sql)

    数据库操作语句大全(sql) 一、基础 1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_...

    sql经典语句一部分

    经典SQL语句大全 一、基础 1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice ...

    OraclePLSQL编程经典例子

    PL/SQL编程 pl/sql(procedural language/sql)是Oracle在...--案例 create or replace procedure xxc_pro3(newname in varchar2,newsal in number) is begin update emp set sal=newsal where ename=newname; end;

    jsqlformatter:Java SQL格式化程序,美化程序和漂亮打印机

    format( " SELECT * FROM table1 " ) RDBMS不可知,可与Oracle,MS SQL Server,Postgres,H2等配合使用。 已针对数百种复杂,真实SQL语句进行了测试待办事项/计划中添加对CREATE,ALTER,DELETE,SELECT INTO语句...

    《Oracle Database编程指南》14-01:简单表达式(Simple Expressions)

    内容导航1、定义2、代码案例 1、定义 简单表达式是由指定列、伪列、常数、序列或null独立构成。 除了用户的Schema之外,Schema还可以是“PUBLIC”(需要双引号),在这种情况下,它必须限定表、视图或物化视图的公共...

    程序员的SQL金典.rar

     本书特色:主要介绍SQL的语法规则及在实际开发中的应用,并且对SQL在MySQL、MS SQL Server、Oracle和DB2中的差异进行了分析;详细讲解数据库对增、删、改、查等SQL的支持并给出了相应的SQL应用案例;透彻分析函数...

    程序员的SQL金典6-8

    第11章 案例讲解  11.1 报表制作  11.1.1 显示制单人详细信息  11.1.2 显示销售单的详细信息  11.1.3 计算收益  11.1.4 产品销售额统计  11.1.5 统计销售记录的份额  11.1.6 为采购单分级  11.1.7 检索所有...

    程序员的SQL金典7-8

    第11章 案例讲解  11.1 报表制作  11.1.1 显示制单人详细信息  11.1.2 显示销售单的详细信息  11.1.3 计算收益  11.1.4 产品销售额统计  11.1.5 统计销售记录的份额  11.1.6 为采购单分级  11.1.7 检索所有...

Global site tag (gtag.js) - Google Analytics