Orcale的一些基本查询和中高级查询

Orale数据库

Oracle数据库在之前介绍过,在这里总结一些相对进阶的一些内容,作为以后分析的基石。

1.Orcale的用户与表空间

    启用      alter user scott account unlock;
    表空间tablespace
            查询表空间--  select * from user_tablespaces;
            设置表空间--  alter user system  default tablespace  system;   默认普通用户没有改变表空间的权限
            创建表空间--  create
            修改表空间--  修改表空间状态  以及修改表数据文件alter tablespace  xxx  drop datafile xxx
            删除表空间--  drop tablespace
            一个数据库可以由多个表空间
                    永久表空间--表,视图等     -------------------- users  system sysaux
                    临时表空间--中间执行过程,执行结束后自动释放----------- temp
                    undo表空间--用于保存事务被修改之前的数据-用于数据回滚 ----------undotbs1
        数据字典
            dba_tablespace
            user_tablespace
    表是基本的存储单位,存在表空间中。
    查看脚本 get   d:\temp\test.sql
    执行脚本 @d:\temp\test.sql
    按行查询

Orcale中的基本查询

基本查询-在之前对Orcale以及SQL的介绍中有所涉及,在这里就不做详细的介绍了。
条件查询
范围查询
模糊查询--通配符 %  _
占位符  ?
给字段设置别名--以及给表设置别名
column_name AS new_name

2.Orcale中高级查询

在Orcale中,基本的查询,在之前有所介绍。而在数据分析中,仅仅这些查询是不够的,在此需要学习
rowid就是唯一标志记录物理位置的一个id
SQL语句一般情况下需使用分号;来结尾
针对列
    一张表的查询---一个列和多个列
    多张表的查询
针对行
    集合运算

针对列的查询

单表查询
    分组查询--group by
            --having  ------order by 
        常用的组函数group_function
            min max sum avg count  wm_concat<行转列>
                            variance stddev
            注意--组函数会自动忽略空值--count(*) count(no)
             count(*)  ==   count(nvl(no,0))
        组函数的嵌套使用
            select max(avg(sal)) 
            from emp 
            group by deptno;
        单列和多列分组
            select deptno,avg(sal) 
            from emp 
            group by deptno;
            select deptno, job,avg(sal) 
            from emp 
            group by deptno,job;
            select deptno, job,avg(sal) 
            from emp    
            group by deptno;
            group by 增强型
                select deptno, job,sum(sal) 
                from emp    
                group by deptno,job;
                select deptno,sum(sal) 
                from emp    
                group by deptno;
                select sum(sal) from emp;
                    === 
                select deptno,job,sum(sal) 
                from emp 
                group by rollup(deptno,job);

                    break on deptno skip 2
                    set pagesize  30
                单列和多列分组--过滤分组
                    select deptno, job,avg(sal) from emp    group by deptno,job having avg(sal)>2000;
                    where子句中 不能使用组函数--having可以使用组函数
                     这个语句是错误的--- select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno;--这个语句是错误的
                   Where的效率高一些
                  select deptno,avg(sal) 
                  from emp 
                  where deptno= 10 
                  group by deptno; 先过滤再分组
                  select deptno,avg(sal) 
                  from emp 
                  group by deptno 
                  having depto=10;  先分组,再过滤
    子查询-
        问题不能一步求解--实质是-select 语句的嵌套
        书写风格要便于查看
        必须有小括号( )-select from where having  --不可以使用的地方group by
            select * 
            from emp 
            where  sal > (select sal from emp where ename='SCOTT');

            select  deptno,avg(sal) from emp group   by deptno having avg(sal) > (select max(sal)
            from emp                    
            where deptno=30 );
            单行子查询--单行操作符--空值问题--根据返回值
            >  >=          子查询不返回任何行    
            多行子查询--多行操作符--空值问题
            in any all

            伪行号--行号只能使用小于以及小于等于,默认的顺序
             select rownum,empno,ename,sal
             from emp  (select * from emp order by sal desc)
             where rownum <=3;
            单表将查询到结果作为临时表
自连接操作
    --通过表的别名,将同一张表视为多张表,本质是多表查询,结果直观
    --会产生笛卡尔积,不适合操作大表
             select  e.name 员工姓名, b.name 老板姓名 from emp e,emp b
                 where e.mgr=b.mgr;                     
            层次查询 --单表查询--结果不直观
        select  level ,empno,ename,sal,mgr
        from  emp
        connect by   prior empno = mgr
        start with  empno =7839
        order by 1;

    level  伪字段
    start with mgr is null;根节点

多张表的查询
    笛卡尔积

    01.交叉连接--笛卡尔积
        显式的和隐式的

    02.内连接又包括等值连接,非等值连接。
        连接查询==等值连接和不等值连接
            ==根据连接条件
            ==
            between  and --小值在前,大值在后
        显式的内连接,一般称为内连接,有INNER JOIN,形成的中间表为两个表经过ON条件过滤后的笛卡尔积。

    03.外连接
        --核心是-可以把对于连接不成立的记录,仍然包含在最后的结果中
        --外连接又分为左外连接(left join)与右外连接(right join)
    子查询
            select  e.* 
            from emp e,dept d 
            where e.deptno=d.deptno and d.dnem='SALES'
            主查询和子查询可以不是同一张表
                select *
               from emp 
                where deptno=(select deptno from dept where dname='SALES');

集合运算

针对行-

blogroll

social