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;
相关推荐
在数据库管理中,UPDATE语句用于修改已存在的数据记录,而SELECT语句则用于...Oracle、MySQL和Informix都支持标准SQL,可以直接执行转换后的SELECT语句。了解这些转换技巧,可以帮助开发者更好地理解和调试数据库操作。
以上总结了Oracle SQL语句中除了SELECT语句之外的常用操作,包括DML、DDL和DCL的使用方法。熟练掌握这些语句对于进行有效的数据库管理和维护至关重要。通过这些操作,数据库管理员可以确保数据的安全性、完整性和...
Oracle 数据库支持多种角色,包括 CONNECT、DBA、SELECT_CATALOG_ROLE、DELETE_CATALOG_ROLE、EXECUTE_CATALOG_ROLE 等。 四.事务控制语言(TCL) 事务控制语言(TCL)用于控制数据库的事务,包括提交事务、回滚...
Oracle公司内部数据库培训资料-Les01基本SQL SELECT语句 本资源涉及基本的SQL SELECT语句,主要涵盖了SELECT语句的基本功能、语法、操作符优先级、空值定义等方面的内容。 一、SELECT语句的基本功能 SELECT语句是...
Oracle 常用查询语句 本文总结了十个常用的 Oracle 查询语句,涵盖了查看表空间、回滚段、控制文件、日志文件、表空间使用情况、数据库对象、数据库版本、数据库创建日期、归档方式、长时间运行的 SQL 语句和数据表...
Oracle 查询语句大全-精华版 Oracle 查询语句大全是 Oracle 数据库管理系统中最基本也是最重要的组成部分,用于管理和维护数据库。本文将详细介绍 Oracle 查询语句的使用方法和实践操作。 一、创建用户和授权 在 ...
根据提供的文件信息,以下是从标题、描述、标签和部分内容中提取的关键知识点,这些知识点主要围绕Oracle数据库中的查询语句和基本操作展开。 ### Oracle 查询语句大全 #### 1. 查询表信息 - **查询所有表**: - `...
其中,SELECT语句是SQL中最常用的命令之一,它用于从数据库中检索数据。而iSQL*Plus是Oracle提供的一个命令行界面工具,用于执行SQL语句,它在Oracle9i产品中首次引入,并在Oracle10g中得到了增强。尽管iSQL*Plus...
Oracle中的select into Oracle中没有select into的用法! 在某些数据库中有select into的用法,用法是: select valueA,valueB into tableB from tableA; 上面这句语句的意思是将tableA表中的valueA和valueB字段的值...
这种技巧主要依靠Oracle SELECT语句的强大组合功能,其中每个SELECT语句可以作为另一个的查询结果的数据源。在文章中,作者通过一个邮政编码表排序查询的例子来演示如何使用嵌套的SELECT语句来实现排序后行号显示的...
Oracle 常用 SQL 语句大全 本文档总结了 Oracle 中常用的 SQL 语句,包括数据库的创建、删除、备份、表的创建、删除、修改、索引的创建、视图的创建等基本操作,以及一些高级查询运算符的使用。 一、数据库操作 ...
ORACLE-Select语句执行顺序及如何提高Oracle基本查询效率 在Oracle中,SQL语句的执行顺序是非常重要的。了解了SQL语句的执行顺序,我们可以更好地优化SQL语句,提高查询效率。下面我们将详细介绍ORACLE-Select语句...
### Oracle CASE 语句介绍及应用案例 #### 一、Oracle CASE 语句概述 在 Oracle 数据库中,CASE 语句是一种非常实用且灵活的条件判断表达式,它可以用于根据不同的条件返回不同的值。CASE 语句可以分为两种类型:...
1. **SELECT语句**:这是最基础的查询语句,用于从表中检索数据。例如,`SELECT * FROM table_name;` 会返回表table_name中的所有列。若需选择特定列,可以指定列名,如 `SELECT column1, column2 FROM table_name;`...
根据提供的文件信息,我们可以深入探讨Oracle数据库中的SELECT语句及其用法。在Oracle数据库中,SELECT语句是最常用的SQL查询语言之一,用于从一个或多个表中检索数据。本篇文章将详细解析两个示例中的SELECT语句,...
1. **SELECT**:用于从数据库中检索数据,是最常用的SQL语句。可以指定字段、筛选条件、排序方式、分组规则等。 2. **INSERT**:用于向表中插入新的记录。 3. **UPDATE**:用于更新已存在的表记录。 4. **DELETE**:...
"Oracle常用语句-总结文档汇总"提供了丰富的资源,涵盖了SQL语句的基础到进阶应用,包括列行转换、SQL性能优化等多个关键知识点。 首先,列行转换是数据处理中常见的需求,Oracle提供了多种方法来实现这一操作。...
### SELECT语句详解 `SELECT`语句是SQL中最常用也是最强大的语句之一,用于从一个或多个表中检索数据。基本语法为: ```sql SELECT column_name(s) FROM table_name WHERE condition(s) GROUP BY column_name(s) ...