Oracle9DML学习笔记

Author：SeanGao

 

连接查询

 

在连接查询中select  emp.empno  ···比select empno效率更高，因为不用再查询字段位于哪个表当中

 

非等值连接

select empno,ename,sal,grade,losal,hisal

from emp e,salgrade s

where sal>=losal and sal <=hisal;    

查询工资的等级，从emp表中取出sal字段，再和salgrade表中的lowsal和hisal进行比较

 

外连接实例：

Select ---- from  --- where  a.x=b.x (+)   左外连接，显示包括符合条件和a中不符合条件的

Select ---- from  --- where  a.x(+)=b.x   右外连接，显示包括符合条件和b中不符合条件的

 

自连接范例

select a.empno,a.ename,a.mgr,b.ename

from emp a ,emp b

where a.mgr=b.empno;         查找员工的上司

 

交叉连接cross Join

等同于在两个表进行连接时未使用where 子句限定条件

 

 

自然连接 natural join

基于两个表中的全部同名列建立连接，不允许在参照列上使用表明或别名前缀

Using 不希望被连接表的所有同名列都进行连接

Select ----from emp join dept using（deptno,u,```）;

 

左外连接left join，右right join 满外连接  full outer join

满外连接，除了返回满足条件的所有行，还返回两个表中不满足条件的行

<>表不等于

 

子查询

 

如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行运算符

多行记录比较运算符 IN,ANY,ALL

Any和子查询中的任意一个比较，>any即表示大于最小值即可

All  和所有记录比较,IN表示等于列表中的任何一个

 

在Oracle中通常采用子查询的方式来实现topN查询

Select * from (select * from emp order by sal desc) where rownum<=5

不允许使用where rownum>=5 and rownum<=10,这样返回空数据

 

select * from

(select rownum rno, a.* from (select * from emp order by sal desc)a)

where rno>=5 and rno<=10;

返回第5到第10行记录

 

 

DML

 

在insert语句中使用子查询，实现表间数据拷贝

例：insert into dept1（id，name）select deptno，dname from dept；

要求子查询的值列表与insert子句中的字段列表相对应

 

Delete =delete from tablename

 

数据合并，例，将test1合并到test2表中

merge into test2

  using test1

  on(test1.eid=test.eid)

  when matched then         

  update set name=``        【update后不能跟表明，默认是test2的列】

  when not matched then

  insert (````) values()        【insert后不能跟表明，默认是test2的列】

 

 

事务必须满足的ACID属性

·原子性（Atomicity）

·一致性（Consistency）

·隔离性（Isolation）

·持久性（Durability）

 

Show autocommit;  显示自动提交设置

未自动提交的，在插入数据等时只对当前此次会话有效

Set autocommit on/off 设置

 

当前事务中DML语句中所涉及的行被锁定，其他用户（会话）不能对其进行修改操作

Savepoint  p1;

Rollback  to  p1;保存点

 

DDL

Select table_name from user_tables;

使用子查询创建表

Create table table_name (col1,col2,col)

 As select empno,ename,sal from emp;

 

Create table table_name (col1,col2,col)

 As select empno,ename,sal*12 aa from emp; 使用表达式的时候必须使用别名

 

Alter table t1

Add(  grade number(3),phone varchar2(20)

);

 

Alter table t1

Modify( grage number(4),phone varchar2(15) default ‘111111’);

修改操作会受到当前表已有数据的影响，如果类型不兼容则修改失败

修改缺省值只对之后的数据有效

 

Alart table drop(grade,phone);

 

Truncate table table_name清空表中的数据，释放表的存储空间，一经执行不可撤消

 

重命名表  rename t1 to t2

 

Create table student

(   sid number(3) not null,  --使用数据库自定的约束名字

       Sname varhcar2(8),

       Birthday date constraint cc_ss  not null  --使用自定义的约束名

)

Select * from user_constraints;

Unique(sid,subname)两字段联合不能重复

Primary key(col1,col2)联合主键,两个字段都不能为空,只能定义为表级约束

 

外键约束

子表外键列的值必须在主表参照列值的范围内，或者为空

外键参照的必须是主表的主键或者唯一键

主表外键值被子表参照时，主表相应记录不允许被删除

 

 

使用cascade子句将删除相关的约束

Alter table empinfo drop empinfo_eid_ok cascade;

将删除与empinfo_eid_ok这个约束相关的约束

 

Alert table record drop (sid) cascade constraints;在删除字段的时候一并删除约束

Alert table student disable constraint name;禁用约束   启用为enable

 

Create or replace view 创建或替换视图

 

可更新的视图 ，视图定义中不能使用分组函数，group by，字段的定义不能为表达式

由两个以上基表中导出的视图不可更新

 

加上with read only子句，视图不可进行DML操作

 

同义词，相当于数据库的别名

Create [public] synonym gt for emp; 使用public其他用户也可以使用