Oracle基础语法总结

超、凢脫俗 2024-04-19 11:45 120阅读 0赞

在此的总结肯定不全面，因为以下记载都是我不太熟悉的或者模棱两可的。

**1.*求平均值是需要注意空值处理** ***

select sum(comm),avg(nvl(comm,0)),count(ename) from emp;

注：**nvl(字段名,0)语法**

如果你某个[字段][Link 1]为空，但是你想让这个字段显示0， nvl([字段名][Link 2],0)，就是当你选出来的时候，这个字段虽然为空，但是显示的是  0，当然这个0也可以换成其他东西，如：1，2，3……

**2.对于分组函数 **

select deptno ,max(sal),min(sal),round(avg(sal)) avg_sal
    
       from emp
    
       group by deptno
    
       order by avg_sal;

注：**如果****select****后面出现了组函数**,**那么其他内容必须出现在** group by **子句中**

**3.将一个表结构复制到另一个表中**

*①将**emp**表的结构复制到**t\_emp**表中*

create table t_emp
    
      as
    
      select * from emp
    
      --提供一个否定条件
    
      where 1=0;

②*将**emp**表中的数据复制到**t\_emp**表中*

create table t_emp
    
     as
    
     select * from emp;

③只复制一部分数据，*将**emp**表中部门**2**的员工的数据复制到**t\_emp**表中*

create table t_emp
    
      as
    
      select * from emp
    
      where deptno=2;

④将表中的部分字段复制到另外一张新表中，并且如果有函数统计的字段需要起别名

create table t_emp
    
      as
      --如果字段中出现函数或者计算需要提供别名
      select ename,sal,sal*12 year_sal 
    
      from emp;

注：所有的复制表都只会复制表结构或者数据，但不会复制表中的约束

**4.关于伪列**

①**rowid**:Oracle独有的.每一条记录的rowid的记录是唯一的

②**rownum**:表示行号

*显示**t\_emp**表中第**3**条到第**9**条记录*

--这种是查不出来结果的  
    select rownum ,e.* from t_emp e
    
    where rownum>=3 and rownum<=9;

--正确写法，即能查出正确结果的脚本 
    select t.*
    
       from
    
      (select rownum r,e.* from t_emp e ) t
    
       where t.r>=3 and t.r<=9;

**5.关于视图**

**特点:**简化复制查询的操作，隐藏表中的字段、视图非物理表即是虚表，基本数据的映射

** 简单视图介绍:视图中的数据就是基表中的子集**

①先删除表

drop table t_emp;

②再复制表（或者表数据及表结构）

create  table t_emp
    
      as
    
      select * from emp where deptno in (10,20);

③使用t\_emp作为基表创建视图

create view v_emp
    
      as
    
      select ename,empno,deptno from t_emp where deptno=20;

④开始查询视图

select * from v_emp;

⑤查询视图的机构

desc v_emp;

⑥更新基表中的数据，那么视图中对应的数据也会相应的有变化

update t_emp set ename='yves' where empno=7369;

⑦更新视图中的数据，简单视图在默认情况下可以通过修改视图影响基表中的数据

update v_emp set ename='yves' where empno=7369;

⑧简单视图防止修改视图影响基表在创建视图时后缀使用

with read only

比如：

--先删除视图
    
      drop view v_emp;
    
      --创建只读的简单视图
    
      create view v_emp
    
      as
    
      select ename,empno,deptno from t_emp where deptno=20 with read only;

**复杂视图：数据是通过基表中的数据计算获得，特点只读**

**关联视图：视图中的数据通过多张表联合查询获得**

--创建一个试图,显示每个部门有多少员工
    
      create view v_emp_count
    
      as
    
      select deptno,count(ename) emp_num from t_emp group by deptno;

其它情况：

①向基表中插入数据，视图中的数据会发生变化

insert into t_emp(ename,empno,deptno) values('yves',1001,10);

②修改视图：

delete from v_emp_count where deptno=10;

③创建视图可以使用create or replace 进行创建，没有就创建，有就覆盖

create or replace view v_emp_count
    
      as
    
      select max(sal) max_sal,avg(nvl(sal,0)) avg_sal from t_emp;

④通过数据字典查询视图

--视图对应的sql语句 
    select text from user_views where view_name='V_EMP_COUNT';

6.关于索引

①手动创建索引

create index 自定义索引名称 on 表名(字段名);

②删除索引

drop index 自定义名称;

**7.关于优化SQL方面的总结：**

①**在****select****语句中避免使用** \*

②**减少数据库的访问次数**

③ **删除重复记录即注重去重**

④**使用****where****替换****having**

⑤**多使用Oracle自带函数提高****sql****语句效率**

⑥**尽量多使用****commit**

⑦**养成起别名的好习惯**

⑧**使用****exists****替换****in**,**使用** not **exists****替换****not** in

⑨**要学会利用索引提高查询效率**

⑩**避免在索引类上使用****not**,**oracle****遇到****not****就使用全表扫**描

①①**使用**>=**替换**>

①②**使用****in****替换****or**

①③**尽量使用****where****替换****group** by

①④**避免使用消耗资源的操作****.****如** union

[Link 1]: http://www.so.com/s?q=%E5%AD%97%E6%AE%B5&ie=utf-8&src=internal_wenda_recommend_textn
[Link 2]: http://www.so.com/s?q=%E5%AD%97%E6%AE%B5%E5%90%8D&ie=utf-8&src=internal_wenda_recommend_textn