oracle基本查询操作子查询用法实例分析

SELECT select_list
FROM table
WHERE expr operator (SELECT select_list FROM table);

select ename from emp where sal > (select sal from emp where ename='SCOTT');

--子查询中使用组函数
select ename,sal from emp where sal=(select min(sal) from emp);
--子查询中的having子句
--首先执行子查询
--向主查询中的having子句返回结果
select deptno, min(sal)
 from emp
 group by deptno
having min(sal) > (select min(sal) from emp);

--查询比部门10里任意一个人工资高的员工信息
select ename, sal
 from emp
 where sal > any (select sal from emp where deptno = 10);
--查询比部门20里所有人工资高的员工信息
select ename, sal
 from emp
 where sal > all (select sal from emp where deptno = 20);
--查询不是老板的员工信息
select ename from emp where empno not in(select mgr from emp);

--返回工资在500-1000和900-1200的员工信息
select ename, sal
from emp
where sal between 500 and 1000
intersect
select ename, sal
from emp
where sal between 900 and 1200;

--返回工资属于500-1000，但不属于900-1200的员工信息
select ename, sal
from emp
where sal between 500 and 1000
minus
select ename, sal
from emp
where sal between 900 and 1200;

INSERT INTO table [(column [,column...])]
VALUES (value [,value...]);

insert into dept(deptno,dname,loc) values(50,'test','test');

insert into dept(deptno, dname, loc)
select 60, dname, loc from dept where deptno = 10;

UPDATE table
SET column=value [, column=value, ...]
[WHERE codition]

--更新一条数据
update emp set sal=sal+100 where empno=7369;

--update使用子查询
update emp
set sal = (select max(sal) from emp)
where empno = (select empno from emp where sal = (select min(sal) from emp));

DELETE [FROM] table
[WHERE condition];

--删除一条数据
delete from dept where deptno=60;

update emp set sal=sal+100 where empno=7369;
savepoint update_empno7369;
delete from emp where empno=7369;
rollback to update_empno7369;

CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, ...]);

create table test(
id number(12),
name varchar2(32));

CREATE TABLE table [(column, column...)]
AS subquery;

create table test2 as select empno,ename from emp where sal>1000;

--添加列
ALTER TABLE table
ADD (column datatype [DEFAULT expr] [, column datatype] ...);

--添加info列
alter table test add (info varchar2(256) default '');

--修改列
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr] [, column datatype] ...);

--修改info列
alter table test modify (info varchar2(64) default '');

--删除列
ALTER TABLE table
DROP column (column);

--删除info列
alter table test drop column info;

--修改列名
ALTER TABLE table
rename column old_column_name to new_column_name;

--修改name列名
alter table test rename column name to name2;

drop table test;

rename dept to newDept;

truncate table test;