Oracle基础语法总结

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

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

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

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

  如果你某个字段为空，但是你想让这个字段显示0， nvl(字段名,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