Oracle Day3

P24 Oracle并集union和union all

union指对两个结果集作并集，重复行只显示一次

union all也是作并集，但是不去重

合并的时候可以指定select哪集列，查询结果会按照你指定的顺序来，只要数据类型相同，列数相同，就可以合并，所以自己一定要注意写明正确的顺序。

 P25 数据库变更

首先先建一张表：

create table myemp as select * from emp;

数据库变更操作包括增删改三个操作。

1.增加Insert

语法：insert into 表名（列1，列2，列3）values（值1，值2，值3）

insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)

values(8000,'kobe','sport',7369,to_date('1978-12-11','yyyy-mm-dd'),1000,100,10);

我们可以看到，字符串格式要用单引号括起来，且区分大小写。日期格式为to_date(‘值’,’格式’)

列名可以省略，此时values就一定要写全（不推荐），实在不想插入的值，写null：

insert into myemp

values(8001,'Lisa','dance',7369,to_date('1997-2-11','yyyy-mm-dd'),1000,100,10);

2.改update

语法：update 表名 set 列名1=xx，列名2=xx where 条件

如果不加条件，每条数据都会被改掉，一般不会出现这种情况，因此记得加where条件

update myemp e set e.ename='LISA',e.job='sing' where e.empno=8001;

批量修改，条件可以写where e.empno in(8001,8002,8003

3.删delete

语法：delete from表名 where 删除条件

from可以省略

delete from myemp where empno=8001;

 P26 Oracle数据库事务

在上述增删改语句执行后，数据实际上并没有发生真正的变化，必须“提交”后才能真正生效，语句为：commit

在提交之前，可以用rollback命令进行回滚。但是提交了之后就不能回滚了。

在plsql developer中，可以直接点击这两个小按钮进行提交和回滚。

 

特别注意：如果有多条update语句对同一条数据进行修改时，第一条语句未提交前，其他语句都是阻塞的，只有第一条提交之后，该条数据才会解锁，这样就解决了并发问题。

如果update操作的是多条数据，那么这多条数据都会被锁住！

带事务的查询语句：在select语句后面加上for update，那么别的查询这张表的操作都会阻塞，直到你点提交之后！

P27 DDL操作（建表删表等）

常用数据类型：varchar2(n)带长度的字符串，number(n)带长度的数值，number(m,n)总长度m小数点后n位，date日期类型，clob大文本数据类型，blob二进制数据

1.创建表create

语法：create table 表名（列名1 数据类型，列名2 数据类型）

 

 

 2.删除表

语法：drop table 表名

3.表的修改

添加列：alter table 表名 add（列名1 数据类型，列名2 数据类型）

修改列：alter table 表名 modify（列名1 数据类型，列名2 数据类型）

字符串长度，可以小改大，不能大改小（不然原来数据放不下怎么办）

4.截断表

使用delete删除表格中的数据时时，会再次提示，并且需要提交事务才能删除成功。但是使用truncate table 表名删除表格中的数据时，会直接删除表格，慎用。

P28 Oracle主键约束非空约束

主键不能重复且不能为空。

加主键的两种方法：

方法1：主键名称不能自定义

 

 方法2：主键名称可以自定义

 

非空约束：

 

 P29 唯一性约束

 

 或者

 

 P30 Oralce检查约束

约束添加的数据在某个范围之内，用check关键字

 

 P31 Oracle外键约束

 首先我们创建两张表：

--drop table orders;
create table orders(
       order_id number(4),
       totalPrice number(7,2),
       order_time date,
       constraint orders_order_id_pk primary key(order_id)
);

--drop table order_detail;
create table order_detail(
       detail_id number(4),
       item_name varchar2(10),
       price number(7,2),
       quantity number(4),
       order_id number(4),
       constraint orders_detail_detial_id_pk primary key(detail_id)
);

给两张表添加外键关联：

drop table orders;
create table orders(
       order_id number(4),
       totalPrice number(7,2),
       order_time date,
       constraint orders_order_id_pk primary key(order_id)
);

drop table order_detail;
create table order_detail(
       detail_id number(4),
       item_name varchar2(10),
       price number(7,2),
       quantity number(4),
       order_id number(4),
       constraint orders_detail_detial_id_pk primary key(detail_id),
       constraint orders_detail_order_id_fk foreign key(order_id) references orders(order_id)
);

下面的表叫做子表，上面的表叫主表。删除表格时，应先删除子表，再删除主表。

在子表添加外键的语句格式为：（若加上on delete cascade，则删除主表数据时，相关联的子表数据会一并删除）

constraint 外键名 foreign key(子表字段) references 主表名(主表字段)

constraint 外键名 foreign key(子表字段) references 主表名(主表字段) on delete cascade

P32 Oracle的rownum和分页

 rownum是给表格添加的一个额外的行编号，一般用于分页，效果如图：

 

需要注意，用rownum查询时，不支持大于号，只支持小于号 

因此，须按照如下格式进行查询：

 

 开始数和结束数的计算：

StartNum=(PageNo-1)*PageSize

EndNum=PageNo*PageSize+1

P33 视图

视图就是封装了一条复杂查询的语句

由于scott用户本身没有创建视图的权限，因此我们需要登录sys用户给scott用户加权限：

grant connect，resource，dba to scott

创建视图的语句格式如下：

create view 视图名称 as 查询语句;

创建之后就可以在view文件夹下看到这个视图：

 

 如何使用视图：

select * from 视图名称

这样相当于把一条复杂的查询语句封装在一个视图内，下次只需要查询这个视图，就可以达到复杂的查询语句的效果了。

我们可以用create or replace（创建或覆盖）来创建视图，可以覆盖原来的视图。

可以用update语句直接操作视图中的数据，原有表中的数据也将被一并修改，如果想让这个视图不能被修改，可以在创建时最后加上with read only。