oracle数据库从入门到精通之二

重点关注表的创建与操作语法（如何创建与设计表是后话）
常用数据类型
表本质上是数据的集合操作

数据类型：
不管扩展多少种数据类型，常用的就以下几种
字符串    varchar2(其它数据库是varchar)来描述,200个字以类的都用此类型。
数值    number来描述，小数用number(m,n),其中n为小数位，m-n为整数位。
        数据库也考虑了程序人员的习惯：
        整数用INT来表示
        小数用FLOAT来表示
日期    date
大文本数据    clob来描述，最多可以保存4G的文本。
大对象数据    blob来描述，图片，音乐，视频，文本，最多可以保存4G，
            虽然能存，但效率低下，数据库更多的是用来存地址，费了
            半天劲，保存进去了，还得费半天劲读出来，把数据库就累死了。
从实际的开发来讲，掌握这四个就够了，varchar2,number,date,clob.

#########################
表创建：
create table 表名(
    列名    数据类型    [default 默认值],
    列名    数据类型    [default 默认值],
    列名    数据类型    [default 默认值],
    列名    数据类型    [default 默认值],
    ...
    列名    数据类型    [default 默认值]
);

create table member(
    mid number,
    name varchar2(20) default 'noname',
    birthday date default sysdate,
    note clob
insert into member(mid,name,birthday,note) values(1,'aa',to_date('1980-09-08','yyyy-mm-dd'),'aaaaaaaaaaaaaa');
insert into member(mid,name,birthday,note) values(2,'bb',to_date('1980-10-08','yyyy-mm-dd'),'bbbbbbbbbbbbbb');
insert into member(mid) values(3);
insert into member(mid,name) values(4,null);
如果指定了null，就会覆盖默认值。

############################

表复制

将子查询的结果保存为数据表。
create table 表名 as 子查询;
简单查询
create table emp30 as select * from emp where deptno=30;
复杂查询
create table deptstat
    as
select d.deptno,d.dname,temp.count,temp.avg
from dept d, (
    select deptno dno,count(*) count,avg(sal) avg
    from emp
    group by deptno) temp
where d.deptno=temp.dno(+);

除了复制表数据之外，还可以只复制表结构。
只需要设置一个绝对不可能满足的条件即可。
create table empnull
    as
select * from emp where 1=2;

 

################################

截断表(oracle特色，了解就行)
事务处理本身是保护数据完整性的一个手段。
使用事务处理的过程中，未提交之前，又发生了ddl操作，那么所有的事务会自动提交
事务只对dml起作用，不对ddl起作用。

select * from emp30;
delete from emp30;
rollback;
可以回退，但未提交之前，又发生了ddl操作，那么所有的事务会自动提交
delete from emp30;
create table a(a int);
rollback;无用

delete * from emp30;
并不会立刻将数据删除，同时这些数据所占用的资源不会立刻释放，
所以截断表概念就是删除数据，并释放资源，并且不能用事务去恢复

truncate table emp30;
#####################################

表重命名(oracle特色)

ddl主要功能是创建对象，
当用户进行对象操作的时候，oracle提供有一个数据字典，用于记录所有对象的状态
CRUD是指在做计算处理时的增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写。主要被用在描述软件系统中数据库或者持久层的基本操作功能。
用户不能直接操作数据字典的crud,只能通过命令完成。

开发大多数情况下使用tab就可以了
select * from tab;
下面这个表记录了资源占用情况
select * from user_tables;

表重命名的过程是更新数据字典的过程。
作为oracle自己的特点，知道就行了，尽量别用。
比如项目完了，运行正常，突然要重命名一个表名，想想都是可怕的。
rename emp30 to emp90;

 

################################

表的删除
删除数据表属于数据库对象的操作
drop table 表名;
从10g开始，删除表不是直接删除，而是先放到回收站里。
用于恢复，或是彻底删除，这叫做闪回技术（flashback）。
在任何数据库里，都不可能提供有批量删除数据表的操作。

闪回技术（理解），回收站，(oracle特色)
用户具备查看，恢复，彻底删除，清空的操作
查看两种方式：
show recyclebin;
select * from user_recyclebin;
闪回恢复
flashback table mydept to before drop;
彻底删除
drop table mydept purge;
删除回收站中的一张表
purge table emp10;
清空
purge recyclebin;
回收站特性是oracle特色。

############################

修改表结构（了解）
在开发中，发现表中少了列，多了列，或列设计不合理，这时有两种做法：
1.修改表结构。一部分人支持这个，这个应该提供，但有的不提供，如db2.
2.删除重建。一部分人支持这个
为了方便数据库的使用，往往设计人员会给出一个数据库脚本，
用于快速恢复数据库，脚本包含以下几个内容：
1.删除原有的数据表，
2.重新创建新的数据表，
3.创建测试数据，
4.进行事务提交。
脚本样例：
--删除数据表
drop table member purge;
purge recyclebin;
--创建数据表
create table member(
    mid number,
    name varchar2(20)
);
--测试数据
insert into member(mid,name) values(1,'aa');
insert into member(mid,name) values(2,'bb');
--提交事务
commit;

修改已有列
下面就基于这个脚本实现数据表的修改操作：
需求
要为name字段设置默认值，如下新插入数据没有name,就为空。
insert into member(mid) values(3);
实现,一行语句只改一个列。
alter table member modify(name varchar2(30) default '11111');
insert into member(mid) values(4);这下就有默认值了。
为表增加列
alter table member add(loc varchar2(10));
删除列,删除操作知道就行了，能不用就别用。
alter table member drop column 列名;

总结
1.常用的数据类型，varchar2,number,date,clob
2.ddl对象的操作语法：
    创建对象    create 对象类型 对象名称...
    删除对象    drop 对象类型 对象名称...
    修改对象    alter 对象类型 对象名称...

#################################

五种约束的使用
约束的管理

数据表本身只支持数据的存储操作
但是在数据库上为了保证数据表中数据的完整性，
即：需要满足若干条件之后才可以进行操作
例如：某些数据不能重复，如身份证，手机号等。
本质上来讲有六种约束，
数据类型（也算一种，如数字类型不能保存字符串。）
非空约束
唯一约束
主键约束
检查约束
外键约束（重点所在）
约束是一把双刃剑，它的确可以保证数据合法后才可以保存，
但如果在一张表中设置了过多的约束，更新的速度就会很慢。
所以在开发之中，某一些验证的操作还是强烈建议交给程序逻辑来处理。

非空约束（not null）
表中的某一个字段内容不允许为空。
列的后面加上not null就可以了。

--删除数据表
drop table member purge;
--创建数据表
create table member(
    mid number,
    name varchar2(20) not null
);
表示name这个列上的数据不能为设为空。

下面是正确的
insert into member(mid,name) values(1,'aa');
下面两个是错误的
insert into member(mid,name) values(2,null);
insert into member(mid) values(3);
在设置了非空约束后，如果出现了违反非空约束的操作，
那么会自动地准确地定位到模式.对象.列

###############################################

唯一约束（unique,UK）
唯一约束的特点是在某一列上的内容不允许出现重复

--删除数据表
drop table member purge;
--创建数据表
create table member(
    mid number,
    name varchar2(20) not null,
    email varchar2(30) unique
);

下面是正确的
insert into member(mid,name,email) values(1,'aa','[e-mail]');
insert into member(mid,name,email) values(2,'bb','[e-mail]');

下面是保存重复的内容
insert into member(mid,name,email) values(3,'cc','[e-mail]');

SQL> insert into member(mid,name,email) values(3,'cc','[e-mail]');
insert into member(mid,name,email) values(3,'cc','[e-mail]')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0012119) violated

约束也称为一个对象，只要设置了约束，oracle会自动创建对象，既然是对象，那么
所有的对象会在数据字典中保存。
select * from user_constraints;
col owner for a20;
col constraint_name for a20;
col table_name for a20;
select owner,constraint_name,constraint_type,table_name from user_constraints;
只是告诉用户约束是在哪一张表上的，并没有说明是在哪一个列上的。此时要用到另一个字典：
select * from SYS.USER_CONS_COLUMNS;


发现唯一约束并不像非空约束那样，可以很明确的告诉用户是哪个列上出现问题，所以为了解决这个问题，
可以在约束创建的时候指定名字

约束简写_字段名
uk_email的来做为此约束的名字

创建唯一约束，同时设置约束名称。
--删除数据表
drop table member purge;
--创建数据表
create table member(
    mid number,
    name varchar2(20) not null,
    email varchar2(30),
    constraint uk_email unique(email)
);

SQL> insert into member(mid,name,email) values(3,'cc','[e-mail]');
insert into member(mid,name,email) values(3,'cc','[e-mail]')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_EMAIL) violated

因此，只要进行数据表创建时，约束一定要设置名字，约束的名字不能重复。
现在，设置了唯一约束，但保存的数据为null.下面的可以正常插入多次。
insert into member(mid,name,email) values(3,'cc',null);
null不受唯一约束的影响。不在唯一约束的判断范畴之内。

################################################

主键约束(primary key,pk)
主键约束＝非空约束＋唯一约束 组合
也就是说设置为主键的列，不能够为空，不能重复。

--删除数据表
drop table member purge;
--创建数据表
create table member(
--  mid number primary key,
    mid number,
    name varchar2(20) not null,
    constraint pk_mid primary key(mid)
);

增加正确的数据
insert into member(mid,name) values(1,'aa');
增加错误的数据
insert into member(mid,name) values(null,'aa');
ORA-01400: cannot insert NULL into ("SCOTT"."MEMBER"."MID")
insert into member(mid,name) values(1,'bb');
ORA-00001: unique constraint (SCOTT.PK_MID) violated
两个错误说明主键是非空与唯一的组合。
在99％的情况下一张表只定义一个主键信息，但从sql语法的角度来讲是允许定义多个列为主键的。
这样的操作往往叫做复合主键。
复合主键多数不用，只是看看，知道就行。当两个字段完全重复时才表示违反约束。
--删除数据表
drop table member purge;
--创建数据表
create table member(
--  mid number primary key,
    mid number,
    name varchar2(20),
    constraint pk_mid primary key(mid,name)
);

增加正确数据
insert into member(mid,name) values(1,'aa');
insert into member(mid,name) values(1,'bb');
insert into member(mid,name) values(2,'aa');
增加错误数据
insert into member(mid,name) values(1,'aa');

数据库设计第一原则：不要使用复合主键，即一张表就一张主键。

############################################

检查约束（check,ck）
检查约束指的是在数据列上设置一些过滤条件，当过滤条件满足的时候才可以进行保存，如果不满足
则出现错误。

--删除数据表
drop table member purge;
--创建数据表
create table member(
--  mid number primary key,
    mid number,
    name varchar2(20),
--  age number(3) check,
    age number(3),
    constraint pk_mid primary key(mid),
    constraint ck_age check(age between 0 and 350)
);

--增加正确数据
insert into member(mid,name,age) values(1,'aa',40);
--增加错误数据
insert into member(mid,name,age) values(2,'aa',400);

从实际开发来讲，外键与非空用的最多。检查约束往往不会设置，而检查都会通过程序来完成。

###############################

外键约束（foreign key fk）
外键约束主要是在父子表关系中体现的一种约束操作。
即子表中某一个字段的内容由父表来决定其使用的数据范围
例如：一个人有多本书，需要设计两张表，
初期设计－－不使用外键

--删除数据表
drop table member purge;
drop table book purge;

--创建数据表
create table member(
--  mid number primary key,
    mid number,
    name varchar2(20),
    constraint pk_mid primary key(mid)
);
create table book (
    bid number,
    bname varchar2(30),
    mid number
);

--增加正确数据
insert into member(mid,name) values(1,'aa');
insert into member(mid,name) values(2,'bb');
insert into book(bid,bname,mid) values(10,'java',1);
insert into book(bid,bname,mid) values(20,'c',1);
insert into book(bid,bname,mid) values(30,'python',2);
insert into book(bid,bname,mid) values(40,'oracle',2);
--增加一条数据
insert into book(bid,bname,mid) values(50,'linux',8);
但这条数据不符合逻辑，因为编号为8的成员不存在，但是也能插入成功，就是因为没有外键约束。

book表中的mid列的内容取值应该由member表中的mid列来决定，所以可以利用外键约束来解决这个问题。
下面重新创建表及增加数据。
--删除数据表
drop table member purge;
drop table book purge;

--创建数据表
create table member(
--  mid number primary key,
    mid number,
    name varchar2(20),
    constraint pk_mid primary key(mid)
);
create table book (
    bid number,
    bname varchar2(30),
    mid number,
    constraint fk_mid foreign key(mid) references member(mid)
);

book中的外键mid列参考member表中的mid列，也就是说book.mid列取值由member.mid来决定。
SQL> insert into book(bid,bname,mid) values(50,'linux',8);
insert into book(bid,bname,mid) values(50,'linux',8)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_MID) violated - parent key not found


对外键而言，最麻烦的是它存在有一堆的限制。
限制1：在删除父表之前需要先删除掉它所对应的全部子表后才可以删除。
但有些时候，一些不合理的设计者，将A表作为B表的父表，又将B表作为A表的父表，即相互依赖，所以删不了
为此，oracle专门提供了一个强制删除父表的操作，删除之后不关心子表

drop table member purge;不行
drop table member cascade constraint;可以
强制删除尽量不使用，尽量还是按照先后顺序删除。
限制2：子表外键的父表列，必须设置为唯一或主键约束

限制1与限制2本质上是对表结构的限制
限制3：如果父表中的某一行数据有对应的子表数据，那么必须先删除子表中的全部数据。


--删除数据表
drop table book purge;
drop table member purge;

--创建数据表
create table member(
--  mid number primary key,
    mid number,
    name varchar2(20),
    constraint pk_mid primary key(mid)
);
create table book (
    bid number,
    bname varchar2(30),
    mid number,
    constraint fk_mid foreign key(mid) references member(mid)
);

--增加正确数据
insert into member(mid,name) values(1,'aa');
insert into member(mid,name) values(2,'bb');
insert into book(bid,bname,mid) values(10,'java',1);
insert into book(bid,bname,mid) values(20,'c',1);
insert into book(bid,bname,mid) values(30,'python',2);
insert into book(bid,bname,mid) values(40,'oracle',2);

SQL> delete from member where mid=1;
delete from member where mid=1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_MID) violated - child record found
如果不想受子表的困扰，那么可以使用级联的操作关系。
级联删除与级联更新
级联删除，在定义时，加入 on delete cascade
当年删除父表数据时，子表数据会同时删除
--删除数据表
drop table book purge;
drop table member purge;

--创建数据表
create table member(
--  mid number primary key,
    mid number,
    name varchar2(20),
    constraint pk_mid primary key(mid)
);
create table book (
    bid number,
    bname varchar2(30),
    mid number,
    constraint fk_mid foreign key(mid) references member(mid) on delete cascade
);

--增加正确数据
insert into member(mid,name) values(1,'aa');
insert into member(mid,name) values(2,'bb');
insert into book(bid,bname,mid) values(10,'java',1);
insert into book(bid,bname,mid) values(20,'c',1);
insert into book(bid,bname,mid) values(30,'python',2);
insert into book(bid,bname,mid) values(40,'oracle',2);
此时就可以删除了
SQL> delete from member where mid=1;

1 row deleted.

级联更新
删除父表数据的时候，对应的子表数据的外键列就设置为null.使用 on delete set null

--删除数据表
drop table book purge;
drop table member purge;

--创建数据表
create table member(
--  mid number primary key,
    mid number,
    name varchar2(20),
    constraint pk_mid primary key(mid)
);
create table book (
    bid number,
    bname varchar2(30),
    mid number,
    constraint fk_mid foreign key(mid) references member(mid) on delete set null
);

--增加正确数据
insert into member(mid,name) values(1,'aa');
insert into member(mid,name) values(2,'bb');
insert into book(bid,bname,mid) values(10,'java',1);
insert into book(bid,bname,mid) values(20,'c',1);
insert into book(bid,bname,mid) values(30,'python',2);
insert into book(bid,bname,mid) values(40,'oracle',2);

delete from member where mid=1;

设计时根据需要选择级联删除与级联更新。

#########################################

修改约束（了解）
在表结构的修改还在可以容忍的范畴之内，那么约束的修改是绝对100%禁止的。
所有的约束都要在表定义的时候就设置完整。不要产生修改的操作。
就好比生了个智障孩子，后天再怎么治，也没啥用，那开始就不要生，一定要三思而后生。


create table member(
    mid number,
    name varchar2(20)
);

insert into member(mid,name) values(1,'aa');
insert into member(mid,name) values(1,'bb');
insert into member(mid,name) values(2,null);

alter table member add constraint pk_mid primary key(mid);
SQL> alter table member add constraint pk_mid primary key(mid);
alter table member add constraint pk_mid primary key(mid)
                                  *
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.PK_MID) - primary key violated
因为此时表中已经存在相同编号的数据了，此时是不可能创建约束成功的。
解决办法是删除重复数据，再创建约束就可以，再添加重复数据就是不可能的
delete from member where mid=1;

alter table member add constraint pk_mid primary key(mid);
这样的语法是不能添加非空约束的，其它四种是可以的。
如果想要为字段增加非空约束，那么只能依靠修改表结构的方式完成。
alter table member modify(name varchar2(20) not null);
但是得保证表中数据没有空的数据，否则添加不成功。
SQL> alter table member modify(name varchar2(20) not null);
alter table member modify(name varchar2(20) not null)
*
ERROR at line 1:
ORA-02296: cannot enable (SCOTT.) - null values found
只能先删除空数据，再添加非空约束即可。
delete from member where name is null;
所以基本上是没有意义的，数据都不见了，再修改有什么用呢

删除约束
alter table member drop constraint pk_mid;
此时再添加重复数据，就能加进去了。
insert into member(mid,name) values(1,'aa');
insert into member(mid,name) values(1,'bb');

创建表，约束要一起完成。

############################################