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

    本文实例讲述了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);
    

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

    
    --查询比部门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);
    

    二、集合运算

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

    
    --返回工资在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;
    

    差集

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

    
    --返回工资属于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;
    

    集合使用的注意事项

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

    三、数据操作语言

    插入数据

    
    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;
    

    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语句回滚到创建的保存点。

    
    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;
    

    删除表

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

    
    drop table test;
    

    改变对象的名称

    
    rename dept to newDept;
    

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

    
    truncate table test;
    

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

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