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数据库工具脚本:支持select/insert/update/delete 部署位置:/root/sysmonitor db:数据库文件夹 dbconfig.properties:数据库配置文件, dbConnectTest.sh:连接测试文件 dbExecurteSQL.sh:...
oracle笔记,入门体验篇,基本的SQL-SELECT语句,简单入门,有具体的案例代码!
set verify(ver)on/off:是否显示替换变量被替换前后的语句(用在sql语句中有替换变量或以& &&开头的时候); v_ename emp.ename%type; --v_ename是定义的变量;emp是查选的表ename是emp表中的字段,然后%type就是...
4 学会在oracle中编写简单的select语句 第1讲:基础语法 内容介绍: 1.为什么学习oracle 2.介绍oracle及其公司的背景 3.学会安装、启动、卸载oracle 4.oracle开发工具 5.Sql*plus的常用命令 6.oracle用户管理 ...
这是我自己学习oracle的时候,写的代码案例和笔记,基本上每一个知识点都写的很清楚!...基本的sql_Select语句 运算符,多表联查,排序,组函数,序列,索引,同义词, 约束,创建和管理表,单行函数,过滤数据等等
oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距今五十年前。简单来说是本身可视...
oracle知识点笔记,语法,触发器,存储过程,存储函数,流程控制,游标,异常处理,记录类型,视图,控制用户权限,高级子查询,set运算符,基本的sql_Select语句等等
内容导航1、定义2、函数列表3、案例代码 1、定义 聚合函数(Aggregate Functions)根据行组而不是单行...如果SELECT语句不包含GROUP BY子句,查询的结果集只有一个分组,Oracle将SELECT列表中的聚合函数应用于结果集
视图是存储在数据字典里的一条select语句。通过创建视图可以提取数据的逻辑上的集合或组合。 视图的优点: 1.对数据库的访问,因为视图可以有选择性的选取数据库里的一部分。 2.用户通过简单的查询可以从复杂...
word 文档,各种案例.
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) 一、基础 1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_...
经典SQL语句大全 一、基础 1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建 备份数据的 device USE master EXEC sp_addumpdevice ...
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;
format( " SELECT * FROM table1 " ) RDBMS不可知,可与Oracle,MS SQL Server,Postgres,H2等配合使用。 已针对数百种复杂,真实SQL语句进行了测试待办事项/计划中添加对CREATE,ALTER,DELETE,SELECT INTO语句...
内容导航1、定义2、代码案例 1、定义 简单表达式是由指定列、伪列、常数、序列或null独立构成。 除了用户的Schema之外,Schema还可以是“PUBLIC”(需要双引号),在这种情况下,它必须限定表、视图或物化视图的公共...
本书特色:主要介绍SQL的语法规则及在实际开发中的应用,并且对SQL在MySQL、MS SQL Server、Oracle和DB2中的差异进行了分析;详细讲解数据库对增、删、改、查等SQL的支持并给出了相应的SQL应用案例;透彻分析函数...
第11章 案例讲解 11.1 报表制作 11.1.1 显示制单人详细信息 11.1.2 显示销售单的详细信息 11.1.3 计算收益 11.1.4 产品销售额统计 11.1.5 统计销售记录的份额 11.1.6 为采购单分级 11.1.7 检索所有...
第11章 案例讲解 11.1 报表制作 11.1.1 显示制单人详细信息 11.1.2 显示销售单的详细信息 11.1.3 计算收益 11.1.4 产品销售额统计 11.1.5 统计销售记录的份额 11.1.6 为采购单分级 11.1.7 检索所有...