欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle学习3.0(数据库对象)

程序员文章站 2022-06-02 15:42:08
...

数据库对象

查看数据库中的对象和种类

select object_type,count(object_type)
from dba_objects
group by object_type
order by object_type;

几个视图
dba_objects
all_objects
user_objests

CDB_ 12C 新增的视图前缀

表 堆表,行列形式存储数据
视图 存储SELECT语句,可以像表一样引用
索引 可以提供一个指向该行的确切位置的指针,可以减少访问表的次数
约束 限定插入表的数据
同义词 表或视图的别名
序列 一种生成唯一数字的结构

用户和模式

用户 是一个可以连接数据库的人,有用户名和密码 账户
模式 是包含用户所拥有对象的容器
创建用户时,模式就被创建,模式是用户拥有的对象,最初,模式是空的
有一些模式总是空的,用户永远不会创建任何对象,这种用户可以通过直接授权和通过角色获得权限,以访问其他用户的模式中的数据,而有一些用户可能相反,他们拥有很多对象 ,但从来不会登陆数据库,甚至可能没有创建会话的权限,所以账户是禁用的
模式对象是拥有者的对象。特定类型的对象的唯一标识符不是对象名称,而是在该名称前加上所属的模式的名称,比如 hehe.emp 和 scott.emp

数据库创建时,会创建很多用户,最重要的是SYS和SYSTEM ,用户SYS拥有数据字典,数据字典不能被DML修改,否则会带来灾难性后果
system模式用来存储用于管理和监控的附加对象

模式对象归用户所有,命名规则为
名称可以是1-30个字符长度
保留字不能用作对象名称
所有名称必须以A-Z的字母开头
名称中的字符只能是字母 ,数字,下划线,$或者#
小写字母自动转换为大写

通过将名称带上双引号,除长度外 ,所有规则都可以无视,但在使用时,也需双引号引用。
同样的限制也适用于列名称。

SQL> create table lower(c1 date);

Table created.

SQL>

SQL> create table “lower”(coll varchar2(2));

Table created.

SQL> select table_name from user_tables where lower(table_name) = ‘lower’;

TABLE_NAME

LOWER
lower

所以不建议使用小写字母和空格等特殊字符

对象名称空间
名称空间定义了一组对象类型,在这个组中,所有名称都必须由模式和名称唯一标识
不同名称空间的对象可以共享相同的名称
以下对象类型共享相同的名称空间

视图
序列
私有同义词
独立的过程
独立的存储函数
程序包
物化视图
用户定义类型和运算符

以下对象都有自己的名称空间
约束
群集
数据库触发器
私有数据库连接
维度

所以 ,即使是在同样的模式中,所有的名称也可以和表名称相同,但是强烈建议不要这么做。

查看用户模式中各种类型的对象

select object_type,count(*)
from user_objects
group by object_type;

OBJECT_TYPE COUNT(*)


TABLE 7
INDEX 2

查看用户有权访问的对象的总数

select object_type,count(*)
from all_objects
group by object_type;

谁拥有scott能看见的对象

select distinict ower
from all_objects;

表的创建及管理

对于数据库而言实际上每一张表都表示是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表,视图,索引,序列,约束等等,都属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为以下三类语法:
创建对象:CREATE 对象名称…;
删除对象:DROP 对象名称…;
修改对象:ALTER 对象名称…;

常用的数据字段
每一张数据表实际上都是由若干个字段组成,而每一个字段都会有其对应的数据类型,在oracle中,常用的数据类型有以下几种:
字符串 VARCHAR2(n) n表示的是字符串所能保存的最大长度,基本上保存200个左右的内容
整数 NUMBER(n) 表示最多为n位的整数,有时候可以使用INT代替
小数 NUMBER(n,m) 其中m为小数位,n-m为整数位,有时候可以用FLOAT代替
日期 DATE 存放日期时间
大文本 CLOB 可以存储海量文字(4Gb),例如存储小说等
大对象 BLOB 存放二进制,例如:电影,MP3,图片,文字
LOB
一般在开发之中使用最多的是VARCHAR2()、NUMBER、DATE、CLOB,而对于BLOB数据类型一般使用较少,一是因为会导致数据库过于庞大,一是读取不方便。

使用列规范来创建表

语法:
CREATE TABLE 表名称(
字段1 数据类型 [DEFAULT 默认值],
字段2 数据类型 [DEFAULT 默认值],
…..
字段3 数据类型 [DEFAULT 默认值]
);

创建一张成员表(member),保存以下信息:姓名、年龄、生日、个人简介。

CREATE TABLE MEMBER(
name VARCHAR2(50) 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语法。

使用子查询创建表

语法:
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支持,其他数据库可能存在不同的方法。

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

修改表名称
RENAME 旧的表名称 TO 新的表名称;
如:

修改member表为person

RENAME MEMBER TO PERSON;

确认修改完成

SELECT * FROM TAB;

查询表中的数据

SELECT * FROM PERSON;
这种方法是oracle数据库所独有,了解即可。

截断表
在之前讲过的删除表数据的操作,使用的是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; (没有任何数据)
注意:这种语法是oracle所独有。

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

删除person表

DROP TABLE PERSON;

查询该表会出现错误提示

SELECT * FROM PERSON;

删除其他之前创建的表

DROP TABLE EMP20;
DROP TABLE EMPNULL;
DROP TABLE MEMBER;
DROP TABLE MYEMP;

查看其他的表

SELECT * FROM TAB;

疑问?查询出来的其他的东西是什么?

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

查看回收站

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

恢复MYEMP表

FLASHBACK TABLE MYEMP TO BEFORE DROP;

查看MYEMP表

当然,也可以直接删除回收站中的一些数据表,语法如下:
PURGE TABLE 表名称;

删除回收站中的member表

PURGE TABLE MEMBER2;

查看回收站中的表

SHOW RECYCLEBIN;

清空回收站

PURGE RECYCLEBIN;

查询所有表

SELECT * FROM TAB;

如果想在删除表的时候进行直接删除,而不进入回收站,可以在删除的时候增加PURGE关键字。

删除MYEMP表不进入回收站

DROP TABLE MYEMP PURGE;

查询所有的表

SELECT * FROM TAB;

这种技术是在oracle10g以后才有的。

问题?在回收站中存在一张tab1表,而后又建立了一张tab1表,那么如果从回收站中恢复的话,可以吗?

修改表结构
如果一张建立好的数据表,发现其初期的结构已经不满足后期的使用要求,则可以进行表的修改操作,而表的修改操作实际上就是数据库对象的修改操作,使用ALTER指令完成,例如,有以下一张表:
CREATE TABLE MEMBER(
MID NUMBER,
NAME VARCHAR2(20)
);

检查表结构

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(30) DEFAULT ‘WUMING’);

修改完成后,再进行增加数据:

INSERT INTO MEMBER(MID) VALUES (46);

注意查看结果

SELECT * FROM MEMBER;

删除表中字段的默认值

ALTER TABLE MEMEBER MODIFY NAME DEFAULT NULL;
注意:虽然在SQL语法和oracle中都支持修改表结构的操作,但是这种操作有一定的风险,尽可能避免。

重命名列(无数据)

ALTER TABLE 表名称 RENAME COLUMN 原列名称 TO 新列名称;

SQL> alter table member rename column name to mingzi;

Table altered.

SQL> select * from member;

   MID MINGZI                AGE BIRTHDAY

 1 ZHANGSAN                  22-JAN-18
 2 LISI                      22-JAN-18
 3 WANGWU                    22-JAN-18
46 wuming                    22-JAN-18

删除列

alter table member drop column age;

SQL> alter table member drop column age;

Table altered.

SQL>
SQL>
SQL>
SQL> select * from member;

   MID MINGZI             BIRTHDAY

 1 ZHANGSAN           22-JAN-18
 2 LISI               22-JAN-18
 3 WANGWU             22-JAN-18
46 wuming             22-JAN-18

将列标记为未使用

alter table emp
set unused column job_id;

删除所有未使用的列

alter table emp
drop unused columns;

SQL> alter table member drop column age;

Table altered.

SQL>
SQL>
SQL>
SQL> select * from member;

   MID MINGZI             BIRTHDAY

 1 ZHANGSAN           22-JAN-18
 2 LISI               22-JAN-18
 3 WANGWU             22-JAN-18
46 wuming             22-JAN-18

SQL>
SQL>
SQL>
SQL> alter table member set unused column birthday;

Table altered.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> alter table member drop unused columns;

Table altered.

SQL>
SQL>
SQL> select * from member;

   MID MINGZI

 1 ZHANGSAN
 2 LISI
 3 WANGWU
46 wuming

将表标记为只读

alter table emp
read only;

SQL> insert into member(mid,mingzi) values(22,zhangmaomao);
insert into member(mid,mingzi) values(22,zhangmaomao)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “C##SCOTT”.”MEMBER”

临时表

创建临时表

语法:
CREATE GLOBAL TEMPORARY TABLE temp_tab_name
(column datatype [,column datatype])
[ON COMMIT {DELTE | PRESERVE} ROWS];

列定义和普通表没有差别,但确实可以通过子查询来提供,末尾的可选子句确定插入的任何行的生命周期
默认方式是:在插入行的事务完成之时,删除相应的行,但也可以更改此行为,以便将它们保留到插入行的会话结束为止
无论哪个选项,数据都专门用于每个会话,不同用户可将自己的行插入自己的表的副本中,谁都无法看到其他人的行。

临时表的数据是临时的,专用于相应的会话,针对其执行的所有SQL命令的速度远快于对永久表
一是临时表不是永久表空间中的段,临时表空间不使用数据库缓冲区
二是针对临时表不生成重做数据

创建临时表

SQL> create global temporary table tmp_emp (dept number,sal number);

Table created.

SQL> insert into tmp_emp(select deptno,sal from emp);

14 rows created.

SQL> update tmp_emp set sal=sal*1.1;

14 rows updated.

SQL> select sum(sal) from tmp_emp;

SUM(SAL)

31927.5

SQL> commit;

Commit complete.

SQL> select sum(sal) from tmp_emp;

SUM(SAL)

查看不同会话的临时表

会话1
SQL> create global temporary table tmp_emp on commit preserve rows as select * from emp where 1=2;

Table created.

SQL> insert into tmp_emp select * from emp where deptno=30;

6 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_emp;

COUNT(*)

 6

会话2

SQL> select count(*) from tmp_emp;

COUNT(*)

 0

SQL> insert into tmp_emp select * from emp where deptno=20;

5 rows created.

SQL> commit;

Commit complete.

SQL> select * from tmp_emp;
SQL> select count(*) from tmp_emp;

COUNT(*)

 5

会话1
SQL> truncate table tmp_emp;

Table truncated.

SQL> select * from tmp_emp;

no rows selected

会话2
SQL> select * from tmp_emp;
SQL> select count(*) from tmp_emp;

COUNT(*)

 5

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 21 13:28:39 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn c##scott/oracle;
Connected.
SQL> select * from tmp_emp;

no rows selected

清理环境

drop table tmp_emp;

约束

表建立完成后,并不能检查表中的数据是否合法,如果想要针对表中的数据做一些过滤的话,则可以通过约束完成,约束的主要功能是保证表中的数据合法性,按照约束的分类,一共有五种约束:
非空约束
唯一约束
主键约束
检查约束
外键约束

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

定义一张数据表

DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(
MID NUMBER,
NAME VARCHAR2(50) 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 (“SCOTT”.”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’,’[email protected]’);
INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(2,’LISI’,NULL);

插入错误的数据:

INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(3,’WANGWU’,’[email protected]’);
插入上面的数据后,提示如下错误:
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’,’[email protected]’);
INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(2,’LISI’,NULL);

插入错误的数据:

INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(3,’WANGWU’,’[email protected]’);
插入上面的数据后,提示如下错误:
ORA-00001: unique constraint (SCOTT.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 (“SCOTT”.”MEMBER”.”MID”)

插入错误的数据:主键重复:

INSERT INTO MEMBER(MID,NAME) VALUES(1,’LISI’);
出现如下错误提示:
ORA-00001: unique constraint (SCOTT.SYS_C0011342) 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 (SCOTT.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 (SCOTT.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 (SCOTT.CK_SEX) violated

增加错误的数据,年龄

INSERT INTO MEMBER(MID,NAME,SEX,AGE) VALUES (3,’WANGWU’,’NV’,’500’);
出现以下错误提示;
ORA-02290: check constraint (SCOTT.CK_AGE) violated

检查的操作就是对输入的数据进行一个过滤。

外键约束

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

select * from emp;
select * from dept; 主 按deptno字段

例如,多个人有多本书,创建数据表

创建两张表,如下

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 (SCOTT.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 (SCOTT.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 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 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表

SELECT * FROM BOOK;
已无编号为1的人员的相关信息。

此时由于存在级联删除的操作,所以主表中的数据删除之后,对应的子表中的数据也都会被同时删除。

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);

再次执行删除操作

DELETE FROM MEMBER WHERE MID=1;

查看MEMBER表

SELECT * FROM MEMBER;

查看BOOK表

SELECT * FROM BOOK;

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 约束名称;
如果要维护约束,肯定需要一个正确的名字才可以。(注意非空约束)

创建一张新表,不含约束。

DROP TABLE MEMBER CASCADE CONSTRAINT PURGE;
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 (SCOTT.CK_AGE) - check constraint violated
因为表中已经存在了违反约束的数据,所以无法添加约束。

删除mid为2的数据

SQL> 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);

删除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;

scott用户没有创建视图的权限,如果直接创建视图,会返回以下错误提示:

ORA-01031: insufficient privileges

使用以下命令放权

GRANT CREATE VIEW TO c##SCOTT;

执行下面语句查询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;