[独孤九剑]Oracle知识点梳理（四）SQL语句之DML和DDL

浏览数：23 / 时间：2015年06月12日

本系列链接导航：

[独孤九剑]Oracle知识点梳理（一）表空间、用户

[独孤九剑]Oracle知识点梳理（二）数据库的连接

[独孤九剑]Oracle知识点梳理（三）导入、导出

[独孤九剑]Oracle知识点梳理（四）SQL语句之DML和DDL

[独孤九剑]Oracle知识点梳理（五）数据库常用对象之Table、View

[独孤九剑]Oracle知识点梳理（六）数据库常用对象之Procedure、function、Sequence

[独孤九剑]Oracle知识点梳理（七）数据库常用对象之Cursor

[独孤九剑]Oracle知识点梳理（八）常见Exception

[独孤九剑]Oracle知识点梳理（九）数据库常用对象之package

[独孤九剑]Oracle知识点梳理（十）%type与%rowtype及常用函数

4、SQL语句

　　Oracle 将sql语句分为一下几类：

　　a) 数据操作语言语句[Data manipulation language，DML]，如select、insert、update、delete、merge、lock、fetch等

　　b) 数据定义语言语句[Data definition language，DDL]，如create、alter、drop、rename、truncate、grant、revoke、audit、noaudit、comment等

　　c) 事务控制语句[transaction control statement]，如commit、rollback、savepoint、set transaction等

　　d) 会话控制语句[session control statement]
　　　　d.1 执行特定操作，修改当前会话，例如启用或禁用 SQL 跟踪功能[SQL trace facility](ALTER SESSION)；
　　　　d.2 为当前会话启用或禁用角色[role](即一组权限的集合)(SET ROLE)

　　e) 系统控制语句[system control statement]，alter system 是唯一的系统控制语句

　　f) 嵌入 SQL 语句[embedded SQL statement]
　　　　f.1 cursor的定义(declare cursor)、打开(open)、关闭(close)，
　　　　f.2 选择一个oracle数据库并进行连接 declare database,connect
　　　　f.3 分配变量名 declare statement
　　　　f.4 初始化描述符[descriptor](DESCRIBE)
　　　　f.5 设定如何处理错误及警告(WHENEVER)
　　　　f.6 解析并执行 SQL 语句(PREPARE，EXECUTE，EXECUTE IMMEDIATE)
　　　　f.7 从数据库中取回数据(FETCH)

我操作最多的是a,b,c和f中的部分。

4.1、DML

4.1.1、增删改查的语法使用标准sql语句即可：

1 select * from table1;
2 insert into table1(f1,f2,f3) values(v1,v2,v3);
3 update table1 t set t.f1=v1 where t.f2=v2;
4 delete table1 t where t.f2=v2;

　　rownum,是oracle中特有的一个关键字，当select一张表时，oracle会对查询结果进行标记，rownum是number类型的，每次都是从1开始，如果查询出10行数据，那么rownum就会从1到10，按查询结果的顺序标记。

　　rownum是不可以跳跃的，即可以使用where rownum=1,但不可以使用where rownum=2; 可以使用rownum <5,但不可以使用rownum>5，而where rownum=0可以得到表结构。

4.1.2、分页查询：

select tt.* from (select t.*,rownum rn from table1 t where rownum<=10) tt where tt.rn>=1; --这就是使用rownum的最好例子

4.1.3、联合查询

where方式关联：

1 select t1.*,t2.* from table1 t1,table2 t2 where t1.f1=t2.f2 --同inner join
2 select t1.*,t2.* from table1 t1,table2 t2 where t1.f1(+)=t2.f2 --同left join
3 select t1.*,t2.* from table1 t1,table2 t2 where t1.f1=t2.f2(+) --同right join

join方式关联：

1 select t1.* from table1 t1
2 join table2 t2
3 on t1.f1=t2.f2
4 where t1.fx>10 and t2.fy>100

　　left join 、right join、outer join 方式与标准sql一致，略。

4.1.4、merge 合并

　　该命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据.

1 　　/*涉及到两个表关联的例子*/
2 merge into toTable a    --需要更新的表
3 　　using fromTable b    --关联表(数据源表)
4 　　on (a.id=b.id)    --关联条件
5 　　when metched then    --匹配关联条件，做更新处理
6 　　　　update set a.f1=b.f1, a.f2=b.f2+1
7 　　when not metched then    --不匹配关联条件，做插入处理
8 　　　　insert values(b.f1,b.f2,b.f3...);

1 /*涉及到多个表关联的例子，有3个表，table1 是目标表，table2和table3需要关联后，作为table1的数据源；并且只做更新操作*/
2 merge into table1 a
3 　　using (select t2.id,t2.f1,t2.f2,t3.f1 from table t2 join table2 t3 on t2.id=t3.id) b
4　　 on (a.id=b.id)
5 　　when matched then
6 　　　　update set a.f1=b.f1,a.f2=b.f2+1;

注意：

使用merge时，update或insert的字段，不能是 on中用于匹配的字段
对于update和insert语句，都可以使用where条件；where中可针对a表，也可针对b表进行过滤

4.2、DDL

　　create用于创建table、view、procedure、function、sequence等。
　　alter用于修改
　　drop用于删除table、view、procedure、function、sequence等。
　　grant、revoke用于给用户赋权限和取下权限
　　audit、noaudit用于审计功能
　　comment用于添加备注信息
　　truncate用于截断表，不能回滚
　　rename用于重命名

第5节，在介绍table、view、procedure、function、sequence时，使用的都是DDL语句