ORACLE数据库的SQL语句之约束——基础篇

2018-01-11 08:11:44来源:cnblogs.com作者:923583281人点击

分享

/*修改用户密码:
1.输入cmd打开dos窗口
2.sqlplus/nolog
3.conn/as sysdba
4.alter user system identified by orcl;
*/

/**
数据完整性指存储在数据库中的所有数据值均正确的状态。
如果数据库中存储有不正确的数据值,则该数据库称为已丧失数据完整性
*/

CREATE TABLE tb_student(
NAME VARCHAR2(18),
sex CHAR(3),
age INT,
address VARCHAR2(100),
phone VARCHAR2(30)
)

SELECT * FROM tb_student;

--不正确状态数据
INSERT INTO tb_clazz(sex,age,address,phone)VALUES('女','19','广州','1390000115');
INSERT INTO tb_clazz(NAME,sex,age,address,phone)VALUES('alice','妖','19','广州','1390000115');
INSERT INTO tb_clazz(NAME,sex,age,address,phone)VALUES('alice','女','19','广州','1390000115');

/**
约束是在表上强制执行的数据校验规则
约束主要用于保证数据库的完整性
大部分数据库支持下面五类完整性约束
UNIQUE Key唯一键
CHECK检查(NOT NULL非空)
*/

/**重点:
PRIMARY KEY主键
作用:主键是表中唯一确定一行数据的字段,主键从功能上看相当于非空且唯一
1.一个表中只允许一个主键
联合主键,主键字段可以是单字段或者是多字段的组合
2.现代数据库建模,建议一张表一定要有主键,并且主键应该和业务数据无关,建议使用自动增长的自然数。

重点:
FOREIGN KEY外键
格式FOREIGN KEY (外键列名) REFERENCES 主表(参照列)
作用:外键是构建于一个表的两个字段或者两个字段之间的关系,解决数据冗余问题。
课堂示例:通过clazz_id字段将tb_clazz表和tb_student表关联起来。
*/

/**外键要注意的问题:
1.子(从)表[tb_student]外键列[clazz_id]的值必须在父(主)表[tb_clazz]参照列[id]的范围内。
或者为空(也可以加非空约束,强制不允许为空)
2.外键[clazz_id]参照的只能是主表[tb_clazz]主键或者唯一键,保证子表记录可以准确定位到被参照的记录。
3.当主表[tb_clazz]的记录被子表[tb_student]参照时,主表记录不允许被删除。
解决方案1:先删除关联的子表数据,再删除主表数据
*/
SELECT * FROM tb_student;
ALTER

--先删除主表的ID
DELETE FROM tb_student WHERE clazz_id = 1;
--再删除从表的ID
DELETE FROM tb_clazz WHERE ID = 1;

--解决方案2:先将关联的子表数据修改,再删除主表数据
UPDATE tb_student SET clazz_id = 1 WHERE CLAZZ_ID = 3;
DELETE FROM tb_clazz WHERE ID = 3;

SELECT * FROM tb_student;
SELECT * FROM tb_clazz;

--解决方案3:先将关联的子表数据设置为null,再删除主表数据
UPDATE tb_student SET clazz_id = NULL WHERE clazz_id = 2;
DELETE FROM tb_clazz WHERE ID = 2;

4.建表时可以增加以下设置:
--层叠效果删除CASCADE
(1)ON DELETE CASCADE:当父表中的行被删除的时候,同时删除再子表中依靠的行
--把B表中依靠外键A表的值转换为空值
(2)ON DELETE SET NULL:将依靠的外键值转换为空值,该外键设置为空

/*在建表时直接在列后面创建约束称为列级约束,数据库会默认给约束增加一个唯一
的名称SYS_C007217用于管理**/
DROP TABLE tb_student;

SELECT * FROM tb_student;

CREATE TABLE tb_student(
ID INT PRIMARY KEY,--主键约束
NAME VARCHAR2(30) NOT NULL,--非空约束
sex CHAR(3) DEFAULT '男' CHECK(sex='男' OR sex='女'),--检查约束
age INT CHECK(age >6 AND age<149),--检查约束
phone VARCHAR2(11) UNIQUE --唯一约束UNIQUE
);

--测试非空约束
INSERT INTO tb_student(sex,age,phone)VALUES('男',23,'15913115996');

--测试检查约束
--插入性别为妖,手机号为重复的数据,年龄200
INSERT INTO tb_student(ID,name,sex,age,phone)VALUES(1,'admin','妖',23,'15913115996');
INSERT INTO tb_student(ID,name,sex,age,phone)VALUES(2,'admin','男',2000,'15913115996');

--测试唯一约束
INSERT INTO tb_student(ID,name,sex,age,phone)VALUES(1,'admin','男','20','15913115996');
INSERT INTO tb_student(ID,name,sex,age,phone)VALUES(2,'admin','男','20','15913115996');

--为什么要有主键约束?观察插入重复数据
--主键用来保证数据的完整性和唯一性
INSERT INTO tb_student(name,sex,age,phone)VALUES('admin','男','20','15913115996');
INSERT INTO tb_student(name,sex,age,phone)VALUES('admin','男','20','15913115996');

--为什么要有外键?
DROP TABLE tb_student;
DROP TABLE tb_clazz;

SELECT * FROM tb_clazz;

CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR2(18) NOT NULL,
sex CHAR(3) DEFAULT '男' CHECK(sex='男' OR sex='女'),
age INT CHECK(age > 15 AND age <60),
address VARCHAR2(30) UNIQUE,
phone VARCHAR2(30) UNIQUE,
--班级名字,班级编码,班主任
clazz_name VARCHAR2(30),
CODE VARCHAR2(30),
bzr VARCHAR2(30)
);

SELECT * FROM tb_student;
SELECT * FROM tb_clazz;
--这样插入数据会出现数据冗余
--插入数据
INSERT INTO tb_student(ID,NAME,SEX,AGE,ADDRESS,PHONE,CLAZZ_NAME,CODE,bzr)
VALUES(1,'admin','男',23,'广州','15913115996','j1707','java就业班','谢老师');

--建立外键关联两个表


--主表 班级表
CREATE TABLE tb_clazz(
ID INT PRIMARY KEY,--如果要设置外键,那么主表必须有一个主键
CODE VARCHAR2(18),
NAME VARCHAR2(18),
bzr VARCHAR2(18)
);

INSERT INTO tb_clazz(id,CODE,NAME,bzr)VALUES(1,'j1709','java就业班','谢老师');
INSERT INTO tb_clazz(id,CODE,NAME,bzr)VALUES(2,'j1710','java就业班','李老师');
INSERT INTO tb_clazz(id,CODE,NAME,bzr)VALUES(3,'j1711','java基础班','黄老师');

--列级约束
CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR2(18) NOT NULL,
sex CHAR(3) DEFAULT '男' CHECK(sex='男' OR sex='女'),
age INT CHECK(age >15 AND age <60),
address VARCHAR2(100),
phone VARCHAR2(30) UNIQUE,
--班级外键
clazz_id INT,
--外键 (外键名) 引用 主表(主键)
FOREIGN KEY (clazz_id) REFERENCES tb_clazz(ID)
)

INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(1,'admin','男',23,'广州','15913115996',1);
INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(2,'admin2','男',23,'广州','15913115997',2);
INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(3,'admin3','男',23,'广州','15913115998',3);

--插入数据
INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(1,'admin','男',23,'广州','15913115996',1);
INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(2,'rose','女',21,'深圳','15913115997',2);

--测试:1.子(从)表[tb_student]外键列[clazz_id]的值必须在父(主)表[tb_clazz]参照列[id]值的范围内
--错误信息:未找到父项关键字,因为现在tb_clazz当中没有id为9的班级
INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(3,'rose','女',21,'深圳','15913115998',9);

--测试:2.外键[clazz_id]参照的只能是主表[tb_clazz]主键或者唯一键,
--保证子表记录可以准确定位到被参照的记录。
CREATE TABLE tb_clazz1(
ID INT,
CODE VARCHAR2(18),
NAME VARCHAR2(18),
bzr VARCHAR2(18)
);

INSERT INTO tb_clazz1(id,CODE,NAME,bzr)VALUES(1,'j1509','java就业班','谢老师');
INSERT INTO tb_clazz1(id,CODE,NAME,bzr)VALUES(1,'j1508','java就业班','李老师');

SELECT * FROM tb_clazz;
SELECT * FROM tb_student;

--测试:3.当主表[tb_clazz]的记录被子表[tb_student]参照时,主表记录不允许被删除。
--错误信息:已找到子记录
DELETE FROM tb_clazz WHERE ID = 1;

-- 解决方案2:先将关联的子表数据修改,再删除主表数据
--子表修改的CLAZZ_ID值必须要在主表中的ID值范围内
UPDATE tb_student SET clazz_id = 3 WHERE ID =1;
DELETE FROM tb_clazz WHERE ID = 2;

--(1)ON DELETE CASCADE:当父表中的行被删除的时候,同时删除在子表中依靠的行
--(2)ON DELETE SET NULL:将依靠的外键值转换为空值
--建表时增加

DROP TABLE tb_student;

CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR2(18) NOT NULL,
sex CHAR(3) DEFAULT '男' CHECK(sex='男' OR sex='女'),
age INT CHECK(age >15 AND age <60),
address VARCHAR2(100),
phone VARCHAR2(30) UNIQUE,
--外键 (外键名) 引用 主表(主键)
FOREIGN KEY (clazz_id) REFERENCES tb_clazz(ID),
--当删除主表数据时要把子表关联的数据设置为空
clazz_id INT REFERENCES tb_clazz(ID) ON DELETE SET NULL
);

DELETE FROM tb_clazz WHERE ID = 1;

SELECT * FROM tb_clazz;
SELECT * FROM tb_student;

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台