Oracle12cR2学习记录2
数据库3年前 (2020-02-09)
数据更新
#数据更新包括数据的增加、修改、删除。 #为了保险起见，先使用以下指令将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);
#简便型：不写列名称 #错误：insert into myemp values (8889,'lisi',SYSDATE,3000,7369,'daza',30); #正确：需要按照列名称的顺序来写，必须符合字段的要求，一般开发中不使用 insert into myemp values (8890,'wangwu','daza',7369,sysdate,3000,null,30);
数据的修改
语法：
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;
#所有员工的工资统一改为7500 #如果更新的时候不加上更新条件，则意味着更新全部数据，一定要注意，当数据量达到一定程度时，这种方式并不可取 update myemp set sal=7500;
#为了后续实验，先回滚数据表 rollback;
数据的删除
语法：
DELETE FROM 表名称 [WHERE 删除条件(s)];
#删除1987年入职的员工的信息 #如果删除的时候没有相应匹配条件的时候，则更新记录为0，更新操作也一样。 delete from myemp where TO_CHAR(hiredate,'yyyy')=1987;
#删除表中的所有数据 #对于删除操作，尽可能少使用，因为删除操作对于查询操作要危险许多 #对于删除操作，推荐在开发时对于所有的删除操作之前先给出一个提示框，以防止误删除 delete from myemp;
事务处理
对于数据表的操作，查询要比更新操作更安全，因为更新操作有可能会出现错误，导致没有按照既定的要求正确的完成更新操作。
在很多时候更新可能由多条语句共同完成，如银行转账：
#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查询会发现数据并未被删除
#在会话1中回滚之前的更新操作，再次删除数据，然后提交，再在两个会话中查询数据，在会话1中进行回滚发现已经无法回滚了。 会话1 delete from myemp where empno=7369; 会话1 commit; 会话1 select * from myemp; 会话2 select * from myemp; #会话1提交后，会话2查询发现数据已被删除
#这种事务控制会出现一些问题，例如，某一个会话在更新数据表的时候还没有提交事务，其他会话是无法进行更新的，必须等待之前的会话提交后才可以。 #死锁,会话1执行更新后，在没有提交之前，会话2进行更新会出现等待提示 会话1 update myemp set sal=9000 where empno=7839; 会话2 update myemp set sal=8000 where empno=7839; #这种问题从大的方面来讲可以称作死锁，但是在oracle之中死锁有很多种类，所有的数据更新一定都会受到事务的控制
会话1 commit; 会话2 提示已更新数据
数据伪列
ROWNUM
ROWNUM为每一个显示的记录都会自动的随着查询生成的行号
#查询emp表的rownum，empno,ename,job,hiredate和sal #该行号不是永久的，会随着查询的显示变化而变化 select rownum,empno,ename,job,hiredate,sal from emp;
#查询emp表的rownum，empno,ename,job,hiredate和sal select rownum,empno,ename,job,hiredate,sal from emp where deptno=30;
#查询前5条记录 select rownum,empno,ename,job,hiredate,sal from emp where rownum<=5;
#查询第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;
ROWID
ROWID表示的是每一行数据保存的物理地址的编号
#查看ROWID select rowid,deptno,dname,loc from dept;
每一条记录的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 );
#该表创建好后，插入几条数据，把以下命令执行三次来插入三条相同的数据
INSERT INTO MEMBER(name,age,birthday,content) VALUES ('ZHANGSAN',20,TO_DATE('1990-08-12','yyyy-mm-dd'),'superman');
#执行以下语句来查看插入数据时默认值的情况 INSERT INTO MEMBER(age,content) VALUES (20,'superman');
表的复制
语法：
CREATE TABLE 复制表名称 AS 子查询
#复制一张只包含20部门的雇员信息的表 CREATE TABLE EMP20 AS SELECT * FROM EMP WHERE DEPTNO=20; #查看该表 SELECT * FROM TAB;
#复制emp表的表结构，不要数据 CREATE TABLE EMPNULL AS SELECT * FROM EMP WHERE 1=2; #查询该表的内容 SELECT * FROM EMPNULL;
表重命名
#在oracle数据库中，所有的数据实际上都是通过数据字典保存的，如： SELECT * FROM TAB; #在oracle中，提供了四种类型的数据字典，最常用的是：cdb_、dba_、user_、all_ SELECT * FROM USER_TABLES; #oracle中的所有数据都是按照文件保存的，那么所有的内容都会在数据字典中注册，所谓的修改表名称实际上就是修改一条数据
RENAME 旧的表名称 TO 新的表名称; #修改member表为person RENAME MEMBER TO PERSON; #确认修改完成 SELECT * FROM TAB; #查询表中的数据 SELECT * FROM PERSON;
截断表
#在之前删除表数据的操作使用的是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;
表的删除
表的删除操作指的是数据库对象的删除，使用DROP语句，语法：DROP TABLE 表名称
#删除person表 DROP TABLE PERSON; #查询该表会出现错误提示 SELECT * FROM PERSON;
#删除其他之前创建的表 DROP TABLE EMP20; DROP TABLE EMPNULL; DROP TABLE MYEMP; #查看其他的表 SELECT * FROM TAB;
闪回
在oracle10g之后为了预防用户的误删除表操作，专门提供了回收站的功能，用户所删除的表默认情况下会在一个回收站之中保存，而用户也可以通过回收站进行表的恢复，所以此技术称为闪回FLASHBACK
#查看回收站 SHOW RECYCLEBIN;
#所有已经删除的表都在回收站之中保存，那么下面就可以使用如下的语法进行表的恢复；FLASHBACK TABLE 表名称 TO BEFORE DROP; #恢复EMP20表 FLASHBACK TABLE EMP20 TO BEFORE DROP;
#直接删除回收站中的数据表，语法：PURGE TABLE 表名称; #删除回收站中的PERSON;表 PURGE TABLE PERSON; #查看回收站中的表 SHOW RECYCLEBIN;
#清空回收站 PURGE RECYCLEBIN; #查询所有表 SELECT * FROM TAB;
#删除EMP20P表不进入回收站 DROP TABLE EMP20 PURGE; #查询所有的表 SELECT * FROM TAB;
修改表结构
如果一张建立好的数据表，发现初期的结构已经不满足后期的使用要求，则可以进行表的修改操作，而表的修改操作实际上就是数据库对象的修改操作，使用ALTER指令完成
#先创建一个表 CREATE TABLE MEMBER( MID NUMBER, NAME VARCHAR2(50) );
#检查表结构 DESC MEMBER;
#增加测试数据 INSERT INTO MEMBER(MID,NAME) VALUES (1,'ZHANGSAN'); INSERT INTO MEMBER(MID,NAME) VALUES (2,'LISI'); INSERT INTO MEMBER(MID,NAME) VALUES (3,'WANGWU');
#现在向表中增加字段，所以此时可以采用如下的语法完成：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;
#修改字段名(无数据) #ALTER TABLE 表名称 RENAME COLUMN 原列名称 TO 新列名称; #删除表中字段的默认值 #注意：虽然SQL语法和oracle中都支持修改表结构的操作，但是这种操作有一定的风险 #ALTER TABLE MEMEBER MODIFY NAME DEFAULT NULL;为错误语句 alter table member drop column age;
约束
表建立完成后，并不能检查表中的数据是否合法，如果想要针对表中的数据做一些过滤的话，则可以通过约束完成，约束的主要功能是保证表中的数据合法性
按照约束的分类，一共有五种约束：非空约束、唯一约束、主键约束、检查约束、外键约束
非空约束/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;
#错误的语句：
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 #因为错误提示信息并不太详细，没有明确说明违反了哪个唯一性约束，所以需要通过添加约束
#添加约束条件， 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 约束有了自定义的名字，会有很明确的提示
主键约束/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
#这个错误信息就是唯一约束的错误信息，但是信息不明确，因为没有约束名称
#添加约束，并设置约束名称 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
复合主键
从开发角度来说，一张表一般都只有一个主键，但是从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 #正常开发情况下，一张表只设置一个主键
检查约束/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
#检查的操作就是对输入的数据进行一个过滤
外键约束
之前的约束都是在单张表中进行的，而外键约束是在两张表中进行的，这两张表是存在父子关系的，即子表中的某个字段的取值范围是由父表所决定
#多个人有多本书，创建数据表 #创建两张表，如下 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);
#现在增加了两条数据，也可以保存在数据库中，但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如果数据有错误，则无法执行更新操作
#使用外键最大好处是控制了子表中某些数据的取值范围，但是同样带来了不少的问题 #问题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 #所以需要先删除子表，再删除父表
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; #此时由于存在级联删除的操作，所以主表中的数据删除之后，对应的子表中的数据也都会被同时删除
#问题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);
#问题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; 但是这样很麻烦，对于未知的数据库，如果要按照这样的顺序来执行，必须知道表之间的父子关系
#在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 #因为表中已经存在了违反约束的数据，所以无法添加约束
#删除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;
#删除之前自己创建的所有的表： 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;
视图
在所有的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 #现在已经无法更新视图的创建条件
选项二：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
#如果视图中的查询语句是统计操作，则根本就不可能更新 #创建一个视图： 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 #该信息是统计而来的，根本就不可能更新。项目中，视图的数量可能多于表的数量
#查看视图的字段 desc myview #查看视图具体的定义 SELECT * FROM USER_VIEWS;
本站所有文章均可随意转载，转载时请保留原文链接及作者。
，想必也是非常愉悦的事！
