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;