下载.jpg


数据更新

#数据更新包括数据的增加、修改、删除。
#为了保险起见,先使用以下指令将emp表复制一份
create table myemp as select * from emp;

数据的增加

语法:

INSERT INTO 表名称 [(字段1,字段2,字段3,...)] VALUES(值1,值2,值3,...)

注意:如果需要进行增加数据的话,则以下的几种数据类型要分别处理:

增加数字:直接编写数字,如:123;

增加字符串:字符串应该使用"'"声明;

增加DATE数据:

第一种:可以按照已有的字符串的格式编写字符串,如:‘20-6月-06’;

第二种:利用TO_DATE函数将字符串变为DATE型数据;

第三种:如果设置的时间为当前系统时间,则使用SYSDATE;

#对于数据的增加有两种操作格式:
#完整型
insert into myemp (empno,ename,hiredate,sal,mgr,job,comm)
values (8888,'zhangsan',TO_DATE('1960-08-17','yyyy-mm-dd'),8000,7369,'daza',1000);
#或者
insert into myemp (empno,ename,hiredate,sal,mgr,job,deptno)
values (8889,'lisi',SYSDATE,3000,7369,'daza',30);

2020-02-09_215344.jpg

#简便型:不写列名称
#错误:insert into myemp values (8889,'lisi',SYSDATE,3000,7369,'daza',30);
#正确:需要按照列名称的顺序来写,必须符合字段的要求,一般开发中不使用
insert into myemp values (8890,'wangwu','daza',7369,sysdate,3000,null,30);

2020-02-09_215521.jpg


数据的修改

语法:

UPDATE 表名称 SET 更新字段1=更新值1,更新字段2=更新值2,...[WHERE 更新条件(s)];

#更新员工编号为7369的员工的工资为5000,奖金为2000,职位升级为'MANAGER',职位更新时间为当前时间
update myemp set sal=5000,comm=2000,job='MANAGER',hiredate=sysdate where empno=7369;

2020-02-09_215706.jpg


#所有员工的工资统一改为7500
#如果更新的时候不加上更新条件,则意味着更新全部数据,一定要注意,当数据量达到一定程度时,这种方式并不可取
update myemp set sal=7500;

2020-02-09_215823.jpg

#为了后续实验,先回滚数据表
rollback;

2020-02-09_215958.jpg


数据的删除

语法:

DELETE FROM 表名称 [WHERE 删除条件(s)];

#删除1987年入职的员工的信息
#如果删除的时候没有相应匹配条件的时候,则更新记录为0,更新操作也一样。
delete from myemp where TO_CHAR(hiredate,'yyyy')=1987;

2020-02-09_220456.jpg


#删除表中的所有数据
#对于删除操作,尽可能少使用,因为删除操作对于查询操作要危险许多
#对于删除操作,推荐在开发时对于所有的删除操作之前先给出一个提示框,以防止误删除
delete from myemp;

2020-02-09_220602.jpg


事务处理

对于数据表的操作,查询要比更新操作更安全,因为更新操作有可能会出现错误,导致没有按照既定的要求正确的完成更新操作。

在很多时候更新可能由多条语句共同完成,如银行转账:

#1判断A的账户上是否有5000W     
select yue+shouxufei>5000+sxf from zhanghu where id=a
#2判断B的账户状态是否正常      
select id,status from zhanghu where id=b
#3从A的账户上移走5000W         
update zhanghu set yue-5000 where id=a
#4向B的账户上增加5000W		  
update zhanghu set yue+5000 where  id=b
#5向银行支付手续费5W           
update zhanghu set yue+shouxufei where id=yinhang

以上的数据操作是一个整体,可以理解为一个完整的业务,如果有操作出现错误,那么其他操作应该不能再继续执行,并且都回归到最原始的状态,而这一个流程的操作实际上就是事务的操作

所有的事务处理都是针对每一个会话进行的,在Oracle中,把每一个连接到数据库的用户都称为一个会话,每一个会话之间彼此独立,互不通信,每一个会话独享自己的事务控制,而事务控制之中主要使用两个命令:

事务的回滚:ROLLBACK,更新操作回到原点

事务的提交:COMMIT,真正的发出更新请求,一旦提交后无法回滚

#在会话1中删除一条数据,从另一个会话来查询数据
会话1  delete from myemp where empno=7369;
会话1  select * from myemp;
#会话1查询会发现数据并未被删除

2020-02-09_222108.jpg


#在会话1中回滚之前的更新操作,再次删除数据,然后提交,再在两个会话中查询数据,在会话1中进行回滚发现已经无法回滚了。
会话1  delete from myemp where empno=7369;
会话1  commit;
会话1  select * from myemp;
会话2  select * from myemp;
#会话1提交后,会话2查询发现数据已被删除

2020-02-09_222416.jpg


#这种事务控制会出现一些问题,例如,某一个会话在更新数据表的时候还没有提交事务,其他会话是无法进行更新的,必须等待之前的会话提交后才可以。
#死锁,会话1执行更新后,在没有提交之前,会话2进行更新会出现等待提示
会话1  update myemp set sal=9000 where empno=7839;
会话2  update myemp set sal=8000 where empno=7839;
#这种问题从大的方面来讲可以称作死锁,但是在oracle之中死锁有很多种类,所有的数据更新一定都会受到事务的控制

2020-02-09_222600.jpg

会话1 commit;
会话2 提示已更新数据

2020-02-09_222710.jpg


数据伪列

ROWNUM

ROWNUM为每一个显示的记录都会自动的随着查询生成的行号

#查询emp表的rownum,empno,ename,job,hiredate和sal
#该行号不是永久的,会随着查询的显示变化而变化
select rownum,empno,ename,job,hiredate,sal from emp;

2020-02-09_223017.jpg


#查询emp表的rownum,empno,ename,job,hiredate和sal
select rownum,empno,ename,job,hiredate,sal from emp where deptno=30;

2020-02-09_223105.jpg


#查询前5条记录
select rownum,empno,ename,job,hiredate,sal from emp where rownum<=5;

2020-02-09_223144.jpg


#查询第6至10行记录
#错误做法:select rownum,empno,ename,job,hiredate,sal from emp where rownum between 6 and 10;
#执行完成后并没有返回任何数据,因为rownum不是真实列,正确的思路是先查询前10条记录,然后再查询后5条记录,需要使用子查询
select * from (
select rownum rn,empno,ename,job,hiredate,sal from emp
where rownum<=10) temp
where temp.rn>5;

2020-02-09_223320.jpg


ROWID

ROWID表示的是每一行数据保存的物理地址的编号

#查看ROWID
select rowid,deptno,dname,loc from dept;

2020-02-09_223423.jpg

每一条记录的ROWID都不会重复,所以即使所有列的内容重复,ROWID也不会重复.

格式为:AAAL+XAAEAAAAANAAA

其中:数据对象号:AAAL+X   相对文件号:AAE   数据块号:AAAAAN   数据行号:AAA


表的创建及管理

对于数据库而言实际上每一张表都表示是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表,视图,索引,序列,约束等等,都属于对象的操作,所以表的建立就是对象的建立

对象的操作主要分为以下三类语法:

创建对象:CREATE 对象名称;

删除对象:DROP 对象名称;

修改对象:ALTER 对象名称;

常用的数据字段

 每一张数据表实际上都是由若干个字段组成,而每一个字段都会有其对应的数据类型,在Oracle中,常用的数据类型有以下几种:

一般在开发之中使用最多的是VARCHAR2()、NUMBER、DATE、CLOB,而对于BLOB数据类型一般使用较少,一是因为会导致数据库过于庞大,一是读取不方便

字符串 VARCHAR2(n) n表示的是字符串所能保存的最大长度,基本上保存200个左右的内容
整数 NUMBER(n) 表示最多为n位的整数,有时候可以使用INT代替
小数 NUMBER(n,m) 其中m为小数位,n-m为整数位,有时候可以用FLOAT代替   7,2   * * * * * .* * 
日期 DATE  存放日期时间
大文本 CLOB  可以存储海量文字(4Gb),例如存储小说等
大对象 BLOB  存放二进制,例如:电影,MP3,图片,文字


表的创建

语法:

CREATE TABLE 表名称(

 字段1 数据类型 [DEFAULT 默认值],

 字段2 数据类型 [DEFAULT 默认值],

   .....

 字段3 数据类型 [DEFAULT 默认值]);

#创建一张成员表(member),保存以下信息:姓名、年龄、生日、个人简介
#表的创建是数据库对象的创建,使用的是CREATE语法
CREATE TABLE  MEMBER(
 name  VARCHAR2(20) DEFAULT 'WUMING',
 age   NUMBER(3),
 birthday DATE DEFAULT SYSDATE,
 content  CLOB
);

2020-02-09_224135.jpg

#该表创建好后,插入几条数据,把以下命令执行三次来插入三条相同的数据
INSERT INTO MEMBER(name,age,birthday,content) VALUES ('ZHANGSAN',20,TO_DATE('1990-08-12','yyyy-mm-dd'),'superman');

2020-02-09_224302.jpg

#执行以下语句来查看插入数据时默认值的情况
INSERT INTO MEMBER(age,content) VALUES (20,'superman');

2020-02-09_224410.jpg


表的复制

语法:

CREATE TABLE 复制表名称 AS 子查询

#复制一张只包含20部门的雇员信息的表
CREATE TABLE EMP20 AS SELECT * FROM EMP WHERE DEPTNO=20;
#查看该表
SELECT * FROM TAB;

2020-02-09_224706.jpg

#复制emp表的表结构,不要数据
CREATE TABLE EMPNULL AS SELECT * FROM EMP WHERE 1=2;
#查询该表的内容
SELECT * FROM EMPNULL;

2020-02-09_225259.jpg


表重命名

#在oracle数据库中,所有的数据实际上都是通过数据字典保存的,如:
SELECT * FROM TAB;
#在oracle中,提供了四种类型的数据字典,最常用的是:cdb_、dba_、user_、all_
SELECT * FROM USER_TABLES;
#oracle中的所有数据都是按照文件保存的,那么所有的内容都会在数据字典中注册,所谓的修改表名称实际上就是修改一条数据

2020-02-09_230742.jpg

RENAME 旧的表名称 TO 新的表名称;
#修改member表为person
RENAME MEMBER TO PERSON;
#确认修改完成
SELECT * FROM TAB;
#查询表中的数据
SELECT * FROM PERSON;

2020-02-09_231239.jpg


截断表

#在之前删除表数据的操作使用的是DELETE操作,但是这种删除操作本身有一个特点就是可以进行事务的回滚,也就是说删除之后并不会立即释放数据的资源
#查看person表的内容
SELECT * FROM PERSON;
#删除表中的数据
DELETE FROM PERSON;
#再次查询表中的内容
SELECT * FROM PERSON;
#回滚事务
ROLLBACK;
#再次查询表中数据
SELECT * FROM PERSON;
#如果希望彻底释放一张表所占用的全部资源(表空间,索引等等)就可以使用截断表的语法,语法如下:TRUNCATE TABLE 表名称
#截断PERSON表
TRUNCATE TABLE PERSON;
#回滚事务
ROLLBACK;
#再次查询表中数据
SELECT * FROM PERSON;

2020-02-09_231916.jpg


表的删除

 表的删除操作指的是数据库对象的删除,使用DROP语句,语法:DROP TABLE 表名称

#删除person表
DROP TABLE PERSON;
#查询该表会出现错误提示
SELECT * FROM PERSON;

2020-02-09_232059.jpg

#删除其他之前创建的表
DROP TABLE EMP20;
DROP TABLE EMPNULL;
DROP TABLE MYEMP;
#查看其他的表
SELECT * FROM TAB;

2020-02-09_232224.jpg


闪回

在oracle10g之后为了预防用户的误删除表操作,专门提供了回收站的功能,用户所删除的表默认情况下会在一个回收站之中保存,而用户也可以通过回收站进行表的恢复,所以此技术称为闪回FLASHBACK

#查看回收站
SHOW RECYCLEBIN;

2020-02-09_232516.jpg

#所有已经删除的表都在回收站之中保存,那么下面就可以使用如下的语法进行表的恢复;FLASHBACK TABLE 表名称 TO BEFORE DROP;
#恢复EMP20表
FLASHBACK TABLE EMP20 TO BEFORE DROP;

2020-02-09_232706.jpg

#直接删除回收站中的数据表,语法:PURGE TABLE 表名称;
#删除回收站中的PERSON;表
PURGE TABLE PERSON;
#查看回收站中的表
SHOW RECYCLEBIN;

2020-02-09_233106.jpg

#清空回收站
PURGE RECYCLEBIN;
#查询所有表
SELECT * FROM TAB;

2020-02-09_233219.jpg

#删除EMP20P表不进入回收站
DROP TABLE EMP20 PURGE;
#查询所有的表
SELECT * FROM TAB;

2020-02-09_233319.jpg


修改表结构

如果一张建立好的数据表,发现初期的结构已经不满足后期的使用要求,则可以进行表的修改操作,而表的修改操作实际上就是数据库对象的修改操作,使用ALTER指令完成

#先创建一个表
CREATE TABLE MEMBER(
 MID  NUMBER,
 NAME VARCHAR2(50)
);

2020-02-14_203931.jpg

#检查表结构
DESC MEMBER;

2020-02-14_203951.jpg

#增加测试数据
INSERT INTO MEMBER(MID,NAME) VALUES (1,'ZHANGSAN');
INSERT INTO MEMBER(MID,NAME) VALUES (2,'LISI');
INSERT INTO MEMBER(MID,NAME) VALUES (3,'WANGWU');

2020-02-14_204250.jpg


#现在向表中增加字段,所以此时可以采用如下的语法完成:ALTER TABLE 表名称 ADD(列名称 数据类型 [DEFAULT 默认值],列名称 数据类型 [DEFAULT 默认值],...);
#为member表增加字段
ALTER TABLE MEMBER ADD(AGE NUMBER(3),BIRTHDAY DATE DEFAULT SYSDATE);
 
#如果增加的数据列没有默认值,则所有已有的数据的列的内容都是null,而如果增加的列指定了DEFAULT默认值的话,则所有已有的数据列都是设置的默认值
#也可以修改已有的表结构,此时的语法如下:
#ALTER TABLE 表名称 MODIFY(列名称 数据类型 [DEFAULT 默认值],列名称 数据类型 [DEFAULT 默认值],...);
#将name字段的默认值定义为“wuming”
ALTER TABLE MEMBER MODIFY(NAME VARCHAR2(20) DEFAULT 'WUMING');

#修改完成后,再进行增加数据:
INSERT INTO MEMBER(MID) VALUES (46);

#查看结果
SELECT * FROM MEMBER;

2020-02-14_204504.jpg

#修改字段名(无数据)
#ALTER TABLE 表名称 RENAME COLUMN 原列名称 TO 新列名称;
#删除表中字段的默认值
#注意:虽然SQL语法和oracle中都支持修改表结构的操作,但是这种操作有一定的风险
#ALTER TABLE MEMEBER MODIFY NAME DEFAULT NULL;为错误语句
alter table member drop column age;

2020-02-14_204943.jpg


约束

表建立完成后,并不能检查表中的数据是否合法,如果想要针对表中的数据做一些过滤的话,则可以通过约束完成,约束的主要功能是保证表中的数据合法性

按照约束的分类,一共有五种约束:非空约束、唯一约束、主键约束、检查约束、外键约束


非空约束/NOT NULL/NK

当数据表中的某个字段上的内容不希望设置为null的话,则可以使用NOT NULL进行指定

#定义一张数据表
DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(MID NUMBER,NAME VARCHAR2(20) NOT NULL);
#因为存在了“NOT NULL”约束,所以下面插入两组数据;
#正确的数据:
INSERT INTO MEMBER(MID,NAME) VALUES(1,'ZHANGSAN');
INSERT INTO MEMBER(MID,NAME) VALUES(NULL,'LISI');
INSERT INTO MEMBER(NAME) VALUES('ZHANGSAN');
#完成测试数据的插入后,查询表的内容
SELECT * FROM MEMBER;

2020-02-14_205310.jpg

#错误的语句:
INSERT INTO MEMBER(MID,NAME) VALUES(9,NULL);
INSERT INTO MEMBER(MID) VALUES(10);
#执行上面的语句后,提示如下报错:ORA-01400: cannot insert NULL into ("VOS"."MEMBER"."NAME")
#因为非空约束保证数据不为空


唯一约束/UNIQUE/UK

唯一约束指的是某一个列上的数据是不允许重复的,比如邮箱地址

#定义一张数据表
DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(MID NUMBER,NAME VARCHAR2(50) NOT NULL,EMAIL VARCHAR2(50) UNIQUE);

#插入正确的数据:
INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(1,'ZHANGSAN','ZHANGSAN@163.COM');
INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(2,'LISI',NULL);

#插入错误的数据:
INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(3,'WANGWU','ZHANGSAN@163.COM');
插入上面的数据后,提示如下错误:
ORA-00001: unique constraint (SCOTT.SYS_C0011338) violated
#因为错误提示信息并不太详细,没有明确说明违反了哪个唯一性约束,所以需要通过添加约束

2020-02-14_205949.jpg

#添加约束条件,
DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(MID NUMBER,NAME VARCHAR2(50) NOT NULL,EMAIL VARCHAR2(50),CONSTRAINT UK_EMAIL UNIQUE(EMAIL));

#插入正确的数据:
INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(1,'ZHANGSAN','ZHANGSAN@163.COM');
INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(2,'LISI',NULL);

#插入错误的数据:
INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(3,'WANGWU','ZHANGSAN@163.COM');
#插入上面的数据后,提示如下错误:ORA-00001: unique constraint (VOS.UK_EMAIL) violated
约束有了自定义的名字,会有很明确的提示

2020-02-14_210501.jpg


主键约束/PRIMARY KEY/PK

主键约束=非空约束+唯一约束。在之前设置唯一约束的时候发现可以设置为null,而如果使用了主键约束之后则不能为null,而主键一般作为数据的唯一的一个标记出现,例如人员的编号

#建立主键约束
DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(MID NUMBER PRIMARY KEY,NAME VARCHAR2(50) NOT NULL);

#增加正确的数据
INSERT INTO MEMBER(MID,NAME) VALUES(1,'LISI');

#插入错误的数据:主键为null
INSERT INTO MEMBER(MID,NAME) VALUES(null,'LISI');
出现如下错误提示:ORA-01400: cannot insert NULL into ("VOS"."MEMBER"."MID")

#插入错误的数据:主键重复:
INSERT INTO MEMBER(MID,NAME) VALUES(1,'LISI');
#出现如下错误提示:ORA-00001: unique constraint (VOS.SYS_C007931) violated
#这个错误信息就是唯一约束的错误信息,但是信息不明确,因为没有约束名称

2020-02-14_210738.jpg

#添加约束,并设置约束名称
DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(MID NUMBER,NAME VARCHAR2(50) NOT NULL,CONSTRAINT PK_MID PRIMARY KEY (MID));
#插入正确数据
INSERT INTO MEMBER(MID,NAME) VALUES(1,'LISI');

#插入重复数据
INSERT INTO MEMBER(MID,NAME) VALUES(1,'LISI');
#出现错误提示,可以看到很明显的错误提示ORA-00001: unique constraint (VOS.PK_MID) violated

2020-02-14_210941.jpg


复合主键

从开发角度来说,一张表一般都只有一个主键,但是从SQL语法的规定来说,一张表可以设置多个主键,此种做法叫做复合主键

#创建复合主键
DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(MID NUMBER,NAME VARCHAR2(50) NOT NULL,CONSTRAINT PK_MID_NAME PRIMARY KEY (MID,NAME));
#在复合主键的使用之中,只有两个字段的内容都一样的情况下,才被称为重复数据

#插入正确的数据
INSERT INTO MEMBER(MID,NAME) VALUES(1,'ZHANGSAN');
INSERT INTO MEMBER(MID,NAME) VALUES(1,'LISI');
INSERT INTO MEMBER(MID,NAME) VALUES(2,'LISI');

#插入错误数据
INSERT INTO MEMBER(MID,NAME) VALUES(1,'ZHANGSAN');
#出现错误提示ORA-00001: unique constraint (VOS.PK_MID_NAME) violated
#正常开发情况下,一张表只设置一个主键

2020-02-14_211554.jpg


检查约束/CHECK/CK

 检查约束指为表中的数据增加一些过滤条件,如:设置年龄的时候范围为0-250,设置性别的时候为男、女和其他

#设置检查约束
DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(MID NUMBER,NAME VARCHAR2(50) NOT NULL,SEX VARCHAR2(10) NOT NULL,AGE NUMBER(3),CONSTRAINT PK_MID PRIMARY KEY (MID),CONSTRAINT CK_SEX CHECK (SEX IN ('NAN','NV','QITA')),CONSTRAINT CK_AGE CHECK (AGE BETWEEN 0 AND 250));

#增加正确的数据
INSERT INTO MEMBER(MID,NAME,SEX,AGE) VALUES (1,'ZHANGSAN','NAN','200');

#增加错误的数据,性别
INSERT INTO MEMBER(MID,NAME,SEX,AGE) VALUES (2,'LISI','RENYAO','200');
#出现错误提示:ORA-02290: check constraint (VOS.CK_SEX) violated

#增加错误的数据,年龄
INSERT INTO MEMBER(MID,NAME,SEX,AGE) VALUES (3,'WANGWU','NV','500');
#出现错误提示;ORA-02290: check constraint (VOS.CK_AGE) violated
#检查的操作就是对输入的数据进行一个过滤

2020-02-14_211907.jpg


外键约束

之前的约束都是在单张表中进行的,而外键约束是在两张表中进行的,这两张表是存在父子关系的,即子表中的某个字段的取值范围是由父表所决定

#多个人有多本书,创建数据表
#创建两张表,如下
DROP TABLE MEMBER PURGE;
DROP TABLE BOOK PURGE;
CREATE TABLE MEMBER(MID NUMBER,NAME VARCHAR2(50) NOT NULL,CONSTRAINT PK_MID PRIMARY KEY (MID));
CREATE TABLE BOOK(BID NUMBER,TITLE VARCHAR2(50) NOT NULL,MID NUMBER,CONSTRAINT PK_BID PRIMARY KEY (BID));

#插入几条数据
INSERT INTO MEMBER(MID,NAME) VALUES(1,'ZHANGSAN');
INSERT INTO MEMBER(MID,NAME) VALUES(2,'LISI');
INSERT INTO BOOK(BID,TITLE,MID) VALUES(101,'JAVA',1);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(102,'ANDROID',2);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(103,'PYTHON',2);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(104,'EJB',1);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(105,'AJAX',1);

#为了验证这个数据是否有意义,最简单的做法就是写查询语句来查看数据
#统计每个人拥有书的数量
SELECT M.MID,M.NAME,COUNT(B.BID) FROM MEMBER M,BOOK B WHERE M.MID=B.MID GROUP BY M.MID,M.NAME;

#查询出每个人的编号,姓名和用户有书的名称
SELECT M.MID,M.NAME,B.TITLE FROM MEMBER M,BOOK B WHERE M.MID=B.MID;

#现在的book.mid字段应该是与member.mid字段相关联的,但是由于没有设置约束,所以以下的数据也是可以增加的
INSERT INTO BOOK(BID,TITLE,MID) VALUES(108,'PS',3);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(109,'C',9);

2020-02-14_222444.jpg

#现在增加了两条数据,也可以保存在数据库中,但member表中却没有book.mid中的3和9,需要使用外键约束来解决
#让book.mid的字段的取值由member.mid所决定,如果member.mid的数据真实存在,则表示为正常数据,可以更新
DROP TABLE MEMBER PURGE;
DROP TABLE BOOK PURGE;
CREATE TABLE MEMBER(MID NUMBER,NAME VARCHAR2(50) NOT NULL,CONSTRAINT PK_MID PRIMARY KEY (MID));
CREATE TABLE BOOK(BID NUMBER,TITLE VARCHAR2(50) NOT NULL,MID NUMBER,CONSTRAINT PK_BID PRIMARY KEY (BID),CONSTRAINT FK_MID FOREIGN KEY (MID) REFERENCES MEMBER(MID));

#该表较之前的多添加了约束,插入数据。
INSERT INTO MEMBER(MID,NAME) VALUES(1,'ZHANGSAN');
INSERT INTO MEMBER(MID,NAME) VALUES(2,'LISI');
INSERT INTO BOOK(BID,TITLE,MID) VALUES(101,'JAVA',1);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(102,'ANDROID',2);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(103,'PYTHON',2);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(104,'EJB',1);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(105,'AJAX',1);

#输入以下的数据则会报错
INSERT INTO BOOK(BID,TITLE,MID) VALUES(108,'PS',3);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(109,'C',9);
#提示报错:ORA-02291: integrity constraint (VOS.FK_MID) violated - parent key not found
#因为member.mid没有指定的数据,所以book.mid如果数据有错误,则无法执行更新操作

2020-02-14_222830.jpg


#使用外键最大好处是控制了子表中某些数据的取值范围,但是同样带来了不少的问题 
#问题1
#删除member表中mid为1的数据
DELETE FROM MEMBER WHERE MID=1;
#提示错误信息ORA-02292: integrity constraint (VOS.FK_MID) violated - child record found
#此时只能删除子表记录之后再删除父表记录,如:
DELETE FROM BOOK WHERE MID=1;
DELETE FROM MEMBER WHERE MID=1;
#但是这种操作明显不方便,如果说想在主表数据删除之后,子表中对应的数据也可以删除的话,则可以在建立外键的时候指定一个级联删除的功能,修改数据库创建脚本;
#先删除父表会提示以下错误信息ORA-02449: unique/primary keys in table referenced by foreign keys
#所以需要先删除子表,再删除父表

2020-02-14_223109.jpg

DROP TABLE MEMBER PURGE;
DROP TABLE BOOK PURGE;
CREATE TABLE MEMBER(MID NUMBER,NAME VARCHAR2(50) NOT NULL,CONSTRAINT PK_MID PRIMARY KEY (MID));
CREATE TABLE BOOK(BID NUMBER,TITLE VARCHAR2(50) NOT NULL,MID NUMBER,CONSTRAINT PK_BID PRIMARY KEY (BID),CONSTRAINT FK_MID FOREIGN KEY (MID) REFERENCES MEMBER(MID) ON DELETE CASCADE);
 
#执行完成之后插入数据;
INSERT INTO MEMBER(MID,NAME) VALUES(1,'ZHANGSAN');
INSERT INTO MEMBER(MID,NAME) VALUES(2,'LISI');
INSERT INTO BOOK(BID,TITLE,MID) VALUES(101,'JAVA',1);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(102,'ANDROID',2);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(103,'PYTHON',2);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(104,'EJB',1);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(105,'AJAX',1);
 
#删除主表
DELETE FROM MEMBER WHERE MID=1;

#该表中的数据删除成功,然后查看booK表,已无编号为1的人员的相关信息
SELECT * FROM BOOK;
#此时由于存在级联删除的操作,所以主表中的数据删除之后,对应的子表中的数据也都会被同时删除

2020-02-14_223327.jpg


#问题2
#删除数据的时候,让子表中对应的数据设置为null,当主表中的数据删除后,对应的子表中的数据相关项希望设置为null,而不是删除
DROP TABLE BOOK PURGE;
DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(MID NUMBER,NAME VARCHAR2(50) NOT NULL,CONSTRAINT PK_MID PRIMARY KEY (MID));
CREATE TABLE BOOK(BID NUMBER,TITLE VARCHAR2(50) NOT NULL,MID NUMBER,CONSTRAINT PK_BID PRIMARY KEY (BID),CONSTRAINT FK_MID FOREIGN KEY (MID) REFERENCES MEMBER(MID) ON DELETE SET NULL);
INSERT INTO MEMBER(MID,NAME) VALUES(1,'ZHANGSAN');
INSERT INTO MEMBER(MID,NAME) VALUES(2,'LISI');
INSERT INTO BOOK(BID,TITLE,MID) VALUES(101,'JAVA',1);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(102,'ANDROID',2);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(103,'PYTHON',2);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(104,'EJB',1);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(105,'AJAX',1);

2020-02-14_223647.jpg


#问题3
#删除父表之前必须先删除对应的子表,否则无法删除
#直接删除member表
DROP TABLE MEMBER PURGE;
#提示错误提示ORA-02449: unique/primary keys in table referenced by foreign keys

#只有先删除子表,再删除父表才可以
DROP TABLE BOOK PURGE;
DROP TABLE MEMBER PURGE;
但是这样很麻烦,对于未知的数据库,如果要按照这样的顺序来执行,必须知道表之间的父子关系

2020-02-14_223858.jpg

#在oracle中提供了一个强制性删除表的操作,不再关心约束
#强制删除表,不再关心约束
DROP TABLE MEMBER CASCADE CONSTRAINT PURGE;
#这样删除表之后,子表还在
#删除完member表之后,再删除book表
DROP TABLE BOOK CASCADE CONSTRAINT PURGE;
#更好的做法为最好是先删除子表,再删除父表


修改约束

约束本身也属于数据库对象,那么也肯定可以进行修改操作,而且只要是修改都使用ALTER指令,约束的修改主要指的是以下两种操作:

为表增加约束:ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(字段);

删除表中的约束ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;

如果要维护约束,肯定需要一个正确的名字才可以。(注意非空约束)

#创建一张新表,不含约束
CREATE TABLE MEMBER(MID NUMBER,NAME VARCHAR2(50) NOT NULL,AGE NUMBER(3));

#为表增加主键约束
ALTER TABLE MEMBER ADD CONSTRAINT PK_MID PRIMARY KEY(MID);

#增加测试数据
INSERT INTO MEMBER(MID,NAME,AGE) VALUES(1,'ZHANGSAN',30);
INSERT INTO MEMBER(MID,NAME,AGE) VALUES(2,'LISI',300);

#增加年龄约束
ALTER TABLE MEMBER ADD CONSTRAINT CK_AGE CHECK(AGE BETWEEN 0 AND 250);
#执行后出现错误提示:ORA-02293: cannot validate (VOS.CK_AGE) - check constraint violated
#因为表中已经存在了违反约束的数据,所以无法添加约束

2020-02-14_224311.jpg


#删除mid为2的数据
delete from member where mid=2; 
 
#增加年龄约束
ALTER TABLE MEMBER ADD CONSTRAINT CK_AGE CHECK(AGE BETWEEN 0 AND 250);  

#增加测试数据
INSERT INTO MEMBER(MID,NAME,AGE) VALUES(2,'LISI',300);
#提示错误ORA-02290: check constraint (VOS.CK_AGE) violated

#删除member表中的mid的主键约束
ALTER TABLE MEMBER DROP CONSTRAINT PK_MID;

#增加重复的数据
INSERT INTO MEMBER(MID,NAME,AGE) VALUES(1,'ZHANGSAN',30);
#该语句执行成功
#跟表结构一样,约束最好不要修改,在表建立的同时一定要将约束定义好,后期使用中建议不要修改

#查询约束
SELECT CONSTRAINT_NAME,SEARCH_CONDITION,TABLE_NAME FROM USER_CONSTRAINTS;

2020-02-14_224552.jpg

#删除之前自己创建的所有的表:
DROP TABLE MEMBER PURGE;
DROP TABLE NATION PURGE;
DROP TABLE BOOK PURGE;

#查询部分字段
SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME FROM USER_CONSTRAINTS;
#查看查询结果只有约束的名字,而没有约束的是哪个字段

#查询另一张数据字典表
SELECT * FROM USER_CONS_COLUMNS;
#查看COLUMN_NAME字段,该字段就是约束的字段名称

#再次进行查询该表的其他字段
SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS;

#该查询结果显示不方便查看,使用格式化来调整
COL OWNER FOR A15;
COL CONSTRAINT_NAME FOR A15;
COL TABLE_NAME FOR A15;
COL COLUMN_NAME FOR A15;
SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS;

2020-02-14_224829.jpg


视图

 在所有的SQL语法之中,查询操作是最麻烦的,为了更好更快地使用数据库,我们需要创建视图,视图中包含了一些复杂的SQL语句

视图创建语法:

CREATE [OR REPLACE] VIEW 视图名称 AS 子查询

#创建一张视图
CREATE VIEW MYVIEW AS SELECT D.DEPTNO,D.DNAME,D.LOC,COUNT(E.EMPNO) COUNT,NVL(AVG(E.SAL),0) AVG FROM EMP E,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO GROUP BY D.DEPTNO,D.DNAME,D.LOC;

#vos用户没有创建视图的权限,如果直接创建视图,会返回错误提示ORA-01031: insufficient privileges
#返回sys用户使用命令放权
GRANT CREATE VIEW TO vos;
 
#执行下面语句查询myview
SELECT * FROM MYVIEW;
#通过一个简单的视图查询操作,就可以完成之前的复杂SQL语句的功能,所以视图就是包装了的SQL查询操作

#创建一张包含简单查询语句的视图
CREATE VIEW MYVIEW AS SELECT * FROM EMP WHERE DEPTNO=20;
执行上面语句会返回错误提示ORA-00955: name is already used by an existing object

#drop掉之前创建的视图myview
DROP VIEW MYVIEW;
#然后再次执行
CREATE VIEW MYVIEW AS SELECT * FROM EMP WHERE DEPTNO=20;
#语句执行成功,上面的操作实际上是属于一个视图的替换操作,所以此时可以使用另外一种语法:

CREATE OR REPLACE VIEW MYVIEW AS SELECT * FROM EMP WHERE DEPTNO=20;
#表示如果视图存在则替换,不存在则创建


创建视图有两个选项:

选项一:WITH CHECK OPTION

以上创建的视图,是存在一个创建条件的,如“WHERE DEPTNO=20”

#更新条件
UPDATE MYVIEW SET DEPTNO=30 WHERE EMPNO=7369;
#查询该视图,发现SMITH用户的信息已不存在,该用户的部门信息已经更改。
SELECT * FROM MYVIEW;
#此时更新的是一张视图,但是视图本身并不是一个具体的数据表,而且现在更新的操作又是视图的创建条件,这样的做法不可取,为了解决这个问题,可以加入WITH CHECK OPTION

#回滚数据表:
ROLLBACK;

#创建一个视图
CREATE OR REPLACE VIEW MYVIEW AS SELECT * FROM EMP WHERE DEPTNO=20 WITH CHECK OPTION;
 
#再次更新视图的更新操作:
UPDATE MYVIEW SET DEPTNO=30 WHERE EMPNO=7369;
UPDATE MYVIEW SET sal=5000 WHERE EMPNO=7369;

#出现以下错误提示ORA-01402: view WITH CHECK OPTION where-clause violation
#现在已经无法更新视图的创建条件

2020-02-14_225920.jpg


选项二:WITH READ ONLY

虽然使用WITH CHECK OPTION可以保证视图的创建条件不被更新,但是其他的字段却允许更新

#更新一个数据
UPDATE MYVIEW SET SAL=9000 WHERE EMPNO=7369;

#查询视图,发现SMITH的SAL值已经更改,和上一个问题一样,视图本身不是具体的真实数据,而是一些查询语句,所以这样更新并不合理,据此,可以在创建视图的时候建议设置为只读视图
CREATE OR REPLACE VIEW MYVIEW AS SELECT * FROM EMP WHERE DEPTNO=20 WITH READ ONLY;

#再次进行更新操作
UPDATE MYVIEW SET SAL=9000 WHERE EMPNO=7369;
#返回错误提示ORA-42399: cannot perform a DML operation on a read-only view

2020-02-14_225955.jpg


#如果视图中的查询语句是统计操作,则根本就不可能更新
#创建一个视图:
CREATE OR REPLACE VIEW MYVIEW AS SELECT D.DEPTNO,D.DNAME,D.LOC,COUNT(E.EMPNO) COUNT,AVG(E.SAL) AVG FROM EMP E,DEPT D WHERE E.DEPTNO(+)=D.DEPTNO GROUP BY D.DEPTNO,D.DNAME,D.LOC;

#查看视图查询结果
SELECT * FROM MYVIEW;

#执行更新操作;
UPDATE MYVIEW SET COUNT=30 WHERE DEPTNO=10;
#执行之后,返回错误提示ORA-01732: data manipulation operation not legal on this view
#该信息是统计而来的,根本就不可能更新。项目中,视图的数量可能多于表的数量

2020-02-14_230153.jpg


#查看视图的字段
desc myview

#查看视图具体的定义
SELECT * FROM USER_VIEWS;

2020-02-14_230249.jpg


更新日期: 2020年02月09日
文章标签: Oracle
文章链接: https://www.vos.cn/db/399.html  
版权说明:如非注明,本站文章均为vOS原创,转载请注明出处和附带本文链接。