目录
直接上图!!!
一、修改表结构
基础
检查表结构
DESC 表名;
准备:
- 首先创建一个用于演示的表
- 增加测试数据
CREATE TABLE MEMBER(
MID NUMBER,
NAME VARCHAR2(50)
);
INSERT INTO MEMBER(MID,NAME) VALUES (1,'ZHANGSAN');
INSERT INTO MEMBER(MID,NAME) VALUES (2,'LISI');
INSERT INTO MEMBER(MID,NAME) VALUES (3,'WANGWU');
- 检查member表的结构
SQL>desc member;
删除表
举例:
- 删除member这个表
SQL>DROP TABLE MEMBER PURGE;
向表中增加字段
语法:
ALTER TABLE 表名称 ADD(列名称 数据类型 [DEFAULT 默认值], 列名称 数据类型 [DEFAULT 默认值],...);
举例:
- 为member表增加字段age、birthday(为默认的SYSDATE)
ALTER TABLE MEMBER ADD(AGE NUMBER(3),BIRTHDAY DATE DEFAULT SYSDATE);
注意:
如果增加的数据列没有默认值,则所有已有的数据的列的内容都是null,而如果增加的列指定了DEFAULT默认值的话,则所有已有的数据列都是设置的默认值
修改表中存在的字段的默认值
语法:
ALTER TABLE 表名称 MODIFY(列名称 数据类型 [DEFAULT 默认值],
举例:
- 将name字段的默认值定义为“wuming”
SQL>ALTER TABLE MEMBER MODIFY(NAME VARCHAR2(20) DEFAULT 'WUMING');
此时,新添加的mid为4的数据项name字段就是默认的WUMING
- 删除表中BIRTHDAY字段的默认值
注意:删除默认值时,无论默认值是以何种方式添加的,一律用MODIFY去删除
SQL>ALTER TABLE MEMBER MODIFY BIRTHDAY DEFAULT NULL;
可见,新添加的MID为6的用户的默认的出生日期是没有值的
- 将表中NAME字段的默认值也删除
SQL>ALTER TABLE MEMBER MODIFY NAME DEFAULT NULL;
可见,新添加的MID为7的用户的默认的名字是没有值的
修改字段名
语法:
ALTER TABLE 表名称 RENAME COLUMN 原列名称 TO 新列名称;
举例:
- 将member表的mid字段名修改为id
SQL>ALTER TABLE MEMEMBER RENAME COLUMN MID TO ID;
可见,将MID修改为了ID
删除列
举例:
- 删除age列字段
SQL>ALTER TABLE MEMBER DROP COLUMN AGE;
可见,age列被删除了
二、约束
概述
表建立完成后,并不能检查表中的数据是否合法,如果想要针对表中的数据做一些过滤的话,则可以通过约束完成,约束的主要功能是保证表中的数据合法性
分类
非空约束(/NOT NULL/NK)
概述:不能为空的数据项;例如,姓名、年龄、性别等
举例:将MEMBER表的NAME字段设置为非空约束
DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(
MID NUMBER,
NAME VARCHAR2(20) NOT NULL
);
INSERT INTO MEMBER(MID,NAME) VALUES(1,'ZHANGSAN');
INSERT INTO MEMBER(MID,NAME) VALUES(NULL,'LISI');
INSERT INTO MEMBER(NAME) VALUES('ZHANGSAN');
测试:
创建完成后,查看member表 由于设置的非空约束是NAME,则MID为空并不会出错
如果插入的数据又NAME字段为空的,系统就会提示用户的MEMBER表的NAME字段不能以NULL插入
唯一约束(/UNIQUE/UK)
概述:唯一的数据项;即某一个列上的数据是不允许重复的,例如,邮件地址
举例:
将MEMBER表的EMAIL字段设置为唯一约束
建表+添加测试数据
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);
查看:利用col将列字段所表示的范围缩小
此时若插入重复的email字段就会出错,提示的是唯一约束用户的xx被违反,显然这里的错误信息并不太详细,没有明确说明违反了哪个字段的唯一性约束,可以给这个约束起个名字,后面的约束类似
注意:要先把之前的表删除
建表+添加测试数据
DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER(
MID NUMBER,
NAME VARCHAR2(50) NOT NULL,
EMAIL VARCHAR2(50),
CONSTRAINT UK_EMAIL UNIQUE(EMAIL) 给这个约束起个名字 叫做UK_EMAIL
);
INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(1,'ZHANGSAN','ZHANGSAN@163.COM');
INSERT INTO MEMBER(MID,NAME,EMAIL) VALUES(2,'LISI',NULL);
添加错误的信息后,可见系统就提示了明确的错误出处
主键约束(/PRIMARY KEY/PK)
概述:键值对 一个键对应一个值 同时实现非空和唯一;在之前设置唯一约束的时候发现可以设置为null,而如果使用了主键约束之后则不能为null,而主键一般作为数据的唯一的一个标记出现。例如人员的编号。
举例:
将MEMBER表的MID字段设置为主键约束
注意:这里为了后期的精确提示输入错误,直接给约束起个名字
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');
此时,若输入重复的MID数据字段时,系统就会精确提示错误位置
复合主键
概述: 从开发角度来说,一张表一般都只有一个主键,但是从SQL语法的规定来说,一张表可以设置多个主键,此种做法叫做复合主键,
举例:
将MEMBER表的MID、NAME字段设置为主键约束,即复合主键
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');
注意:当表的结构为复合主键时,即MEMBER表的MID与NAME都是主键时,只有新添加的两个字段都与表中原有的数据重复时,才会提示错误
缺点:在复合主键的使用之中,只有两个字段的内容都一样的情况下,才被称为重复数据。
注意:正常开发情况下,一张表只设置一个主键。
检查约束(/CHECK KEY/CK)
概述:
检查约束指为表中的数据增加一些过滤条件 如:
- 将数据格式进行检查
- 对数据内容进行检查 比如age数据项 不能写汉字,不能为复数或100以上
- 性别不能为男/女以外
举例:
将MEMBER表的SEX、AGE字段设置为检查约束(SEX不能为男女以外的数据,AGE不能超过100)
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 ('MALE','FEMAL')),
CONSTRAINT CK_AGE CHECK (AGE BETWEEN 0 AND 100) );
SQL> insert into member(MID,NAME,SEX,AGE) values(1,'Eichi','MALE',22);
当插入的性别不是检查约束要求的以外,系统就会提示输入错误
外键约束(FOREIGN KEY/FK)
概述:
字段与字段产生的关联 而且是跨表的;之前的约束都是在单张表中进行的,而外键约束是在两张表中进行的,这两张表是存在父子关系的,即子表中的某个字段的取值范围是由父表所决定。
说明:如果MEMBER表中的MID没有X,那么就不能在BOOK中添加关于X的书籍
举例:
产生原因,如果不将book表的mid字段与memnber表的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)
);
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);
此时,如果添加如下数据,即添加两本书进BOOK表中,但是其是属于member中不存在的用户的,这当然与实际不符,即member表中并没有这两个用户,原则上是不能进行添加的,但是由于这里还没有添加外键约束,则是可以添加的
INSERT INTO BOOK(BID,TITLE,MID) VALUES(108,'PS',3);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(109,'C',9);
- 将BOOK表的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);
此时如果向book表中添加member中不存在的mid信息,系统将会报错,提示主键没有找到,就是说member中没有这个MID,所以不能在子表中直接添加
INSERT INTO BOOK(BID,TITLE,MID) VALUES(108,'PS',3);
INSERT INTO BOOK(BID,TITLE,MID) VALUES(109,'C',9);
两张表关联后后的删除问题(字段删除)
概述:
想要删除member表中mid为1的数据,那么系统会提示,此表存在子记录,应该先删除子记录,然后才能删除这个表的这条数据记录,但是去BOOK表删除MID为1的数据的话,连带书也删除了,这与实际不符
演示:
- 删除member表中的mid=1的数据项
delete from member where mid=1;
系统提示,由于找到了子表的记录,所以不能直接删除,应该先删除子表的关联项,才能在父表中删除
解决:
1.先删除子表中的数据项
DELETE FROM BOOK WHERE MID=1;
DELETE FROM MEMBER WHERE MID=1;这样就把MID为1的数据字段行全部删除了,即书也被删了 与实际不符
总结:
此方案虽然可以成功删除了MEMBER表的mid=1的数据项,但是BOOK表中的书也被删除了,影响巨大
如下所示:
2.使用级联删除
重新建表
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);
查看效果
虽然,可以直接删除主表的MID=1的数据字段,但是此级联删除的操作逻辑还是先删除自子表的数据项,在删除主表的数据项,只不过不需要用户去手动删除子表的数据项,但是带来的影响是相同的,即子表中的与MID=1的书也被删除了
3. 删除数据的时候,让子表中对应的数据设置为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;
可见,达到需求
删除整个表
问题:
存在关联的表,如果想要删除主表,那么需要先删除子表;但是这样很麻烦,对于未知的数据库,如果要按照这样的顺序来执行,必须知道表之间的父子关系。
解决:在oracle中,提供了一个强制性删除表的操作,不再关心约束
DROP TABLE MEMBER CASCADE CONSTRAINT PURGE;
更好的做法:在以后进行数据表删除的时候,最好是先删除子表,再删除父表。
修改约束
概述:约束本身也属于数据库对象,那么也肯定可以进行修改操作,而且只要是修改都使用ALTER指令
分类
- 为表增加约束
ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(字段);
- 删除表中的约束
ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;
建议:跟表结构一样,约束最好不要修改,在表建立的同时一定要将约束定义好,后期使用中建议不要修改。
查询约束名称
查询部分字段
SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME FROM USER_CONSTRAINTS;
查询另一张数据字典表
SELECT * FROM USER_CONS_COLUMNS;
再次进行查询该表的其他字段 可以看到到底定义的是哪个列
SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS;
可以利用col 列名称 for a长度 来更改输出的内容以便于查看