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

本文实例讲述了oracle基本查询操作子查询用法。分享给大家供大家参考，具体如下：

一、子查询语法

1. SELECT select_list
2. FROM table
3. WHERE expr operator (SELECT select_list FROM table);

子查询在主查询之前一次执行完成。
子查询的结果被主查询使用。

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

(*注意：子查询要包含在括号内，将子查询放在比较条件的右侧。单行操作符对应单行子查询，多行操作符对应多行子查询。)

单行子查询，只返回一行，使用单行比较符(> = < >= <= != <>)

1. --子查询中使用组函数
2. select ename,sal from emp where sal=(select min(sal) from emp);
3. --子查询中的having子句
4. --首先执行子查询
5. --向主查询中的having子句返回结果
6. select deptno, min(sal)
7. from emp
8. group by deptno
9. having min(sal) > (select min(sal) from emp);

多行子查询，返回多行，使用多行比较符(IN ANY ALL)

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

二、集合运算

并集 
UNION运算符返回两个集合去掉重复元素后的所有记录。
UNION ALL 返回两个集合的所有记录，包括重复的。
交集
INTERSECT 运算符返回同时属于两个集合的记录

1. --返回工资在500-1000和900-1200的员工信息
2. select ename, sal
3. from emp
4. where sal between 500 and 1000
5. intersect
6. select ename, sal
7. from emp
8. where sal between 900 and 1200;

差集

MINUS 返回属于第一个集合，但不属于第二个集合的记录。

1. --返回工资属于500-1000，但不属于900-1200的员工信息
2. select ename, sal
3. from emp
4. where sal between 500 and 1000
5. minus
6. select ename, sal
7. from emp
8. where sal between 900 and 1200;

集合使用的注意事项

1、select语句中参数类型和个数保持一致。
2、可以使用括号改变集合执行的顺序。
3、如果有order by，必须放到最后一句查询语句后。
4、集合运算采用第一个语句的表头作为表头。

三、数据操作语言

插入数据

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

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

从其他表中拷贝数据

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

更新数据

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

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

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

删除数据

1. DELETE [FROM] table
2. [WHERE condition];

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

delete和truncate

1、都是删除表中的数据。
2、delete操作可以rollback，可以闪回。
3、delete可能产生碎片，并且不释放空间。
4、truncate清空表。

四、数据库事务

数据库事务由以下的部分组成：
1、一个或多个DML语句
2、一个DDL数据定义语句
3、一个DCL数据控制语句

以第一个DML语句的执行作为开始
以下面的其中之一作为结束：
显示结束：commit rollback
隐式结束(自动提交)：DDL语句，DCL语句，exit(事务正常退出)
隐式回滚(系统异常终了)：关闭窗口，死机，掉电

commit和rollback语句的优点
1、确保数据完整性。
2、数据改变被提交之前预览。
3、将逻辑上相关的操作分组。

回滚到保留点
使用savepoint语句在当前事务中创建保存点。
使用rollback to savepoint语句回滚到创建的保存点。

1. update emp set sal=sal+100 where empno=7369;
2. savepoint update_empno7369;
3. delete from emp where empno=7369;
4. rollback to update_empno7369;

五、创建和管理表

常见的数据库对象
如下：
表        基本的数据存储集合，由行和列组成。
视图     从表中抽出的逻辑上相关的数据集合。
序列     提供有规律的数值。
索引     提高查询的效率。
同义词  给对象起别名。

创建表

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

1. create table test(
2. id number(12),
3. name varchar2(32));

通过子查询创建表

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

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

修改表

1. --添加列
2. ALTER TABLE table
3. ADD (column datatype [DEFAULT expr] [, column datatype] ...);
4.  
5. --添加info列
6. alter table test add (info varchar2(256) default '');
7.  
8. --修改列
9. ALTER TABLE table
10. MODIFY (column datatype [DEFAULT expr] [, column datatype] ...);
11.  
12. --修改info列
13. alter table test modify (info varchar2(64) default '');
14.  
15. --删除列
16. ALTER TABLE table
17. DROP column (column);
18.  
19. --删除info列
20. alter table test drop column info;
21.  
22. --修改列名
23. ALTER TABLE table
24. rename column old_column_name to new_column_name;
25.  
26. --修改name列名
27. alter table test rename column name to name2;

删除表

1、数据和结构都被删除
2、所有正在运行的相关事物被提交
3、所有相关索引被删除
4、DROP TABLE语句不能回滚，但是可以闪回。

1. drop table test;

改变对象的名称

1. rename dept to newDept;

清空表
1、删除表中所有数据。
2、释放表的存储空间。
3、truncate不能回滚。

1. truncate table test;

更多关于Oracle相关内容感兴趣的读者可查看本站专题：《Oracle常用函数汇总》、《Oracle日期与时间操作技巧总结》及《php+Oracle数据库程序设计技巧总结》

希望本文所述对大家Oracle数据库程序设计有所帮助。