完整性约束
范例1:定义member表,其中姓名不允许为空
DROP TABLE member PURGE ; CREATE TABLE member( mid NUMBER , name VARCHAR2(200) NOT NULL ) ; |
范例2:向member表中增加正确的数据
INSERT INTO member(mid,name) VALUES (1,'李兴华') ; |
范例3:向member表中增加错误的数据(两种语句的执行结果一样)
明确设置name字段为null: |
INSERT INTO member(mid,name) VALUES (3,null) ; |
不设置name字段的内容: |
INSERT INTO member(mid) VALUES (3) ; |
范例4:创建member表,在email字段上设置唯一约束
DROP TABLE member PURGE ; CREATE TABLE member( mid NUMBER , name VARCHAR2(200) NOT NULL , email VARCHAR2(50) UNIQUE ) ; |
范例5:向member表中增加正确记录
INSERT INTO member (mid,name,email) VALUES(1,'李兴华','mldnqa@163.com') ; |
范例6:向member表中增加错误的纪录
INSERT INTO member (mid,name,email) VALUES(2,'董鸣楠','mldnqa@163.com') ; |
范例:为唯一约束指定一个名字
DROP TABLE member PURGE ; CREATE TABLE member( mid NUMBER , name VARCHAR2(200) NOT NULL , email VARCHAR2(50) , CONSTRAINT uk_email UNIQUE (email) ) ; |
此时,再次执行两条插入语句,则错误提示将变为以下的内容。
范例:插入两条包含null的记录
INSERT INTO member (mid,name,email) VALUES(10,'魔乐科技',null) ; INSERT INTO member (mid,name,email) VALUES(20,'MLDN',null) ; |
范例:查询此时的member表内容
SELECT * FROM member ; |
范例:设置member表中的mid为主键
DROP TABLE member PURGE ; CREATE TABLE member( mid NUMBER PRIMARY KEY , name VARCHAR2(200) NOT NULL , email VARCHAR2(50) , CONSTRAINT uk_email UNIQUE (email) ) ; |
范例:将mid设置为null
INSERT INTO member (mid,name,email) VALUES(null,'李兴华','mldnqa@163.com') ; |
范例:插入重复的mid
INSERT INTO member (mid,name,email) VALUES(1,'李兴华','mldnqa@163.com') ; INSERT INTO member (mid,name,email) VALUES(1,'董鸣楠','mldnzhaopin@163.com') ; |
范例:指定主键约束的名称
DROP TABLE member PURGE ; CREATE TABLE member( mid NUMBER , name VARCHAR2(200) NOT NULL , email VARCHAR2(50) , CONSTRAINT pk_mid PRIMARY KEY (mid) , CONSTRAINT uk_email UNIQUE (email) ) ; |
范例:将mid和name两个字段同时设置为主键
DROP TABLE member PURGE ; CREATE TABLE member( mid NUMBER , name VARCHAR2(200) NOT NULL , email VARCHAR2(50) , CONSTRAINT pk_mid_name PRIMARY KEY (mid,name) , CONSTRAINT uk_email UNIQUE (email) ) ; |
范例:插入正确数据
INSERT INTO member (mid,name,email) VALUES(1,'李兴华','mldnqa@163.com') ; INSERT INTO member (mid,name,email) VALUES(1,'董鸣楠','mldnzhaopin@163.com') ; |
范例:插入错误的数据 —— mid和name相同
INSERT INTO member (mid,name,email) VALUES(1,'李兴华','mldnhr@163.com') ; |
范例:查看member表数据
SELECT * FROM member ; |
范例:在member表中增加age字段(年龄范围是0~200岁)和sex字段(只能是男或女)
DROP TABLE member PURGE ; CREATE TABLE member( mid NUMBER , name VARCHAR2(200) NOT NULL , email VARCHAR2(50) , age NUMBER CHECK (age BETWEEN 0 AND 200) , sex VARCHAR2(10) , CONSTRAINT pk_mid_name PRIMARY KEY (mid,name) , CONSTRAINT uk_email UNIQUE (email) , CONSTRAINT ck_sex CHECK (sex IN ('男','女')) ) ; |
范例:插入正确的数据
INSERT INTO member (mid,name,email,age,sex) VALUES (1,'李兴华','mldnqa@163.com',30,'男') ; |
范例:插入一条错误的数据,年龄为900岁
INSERT INTO member (mid,name,email,age,sex) VALUES (2,'董鸣楠','mldnzhaopin@163.com',900,'男') ; |
范例:插入错误的数据,性别设置为“无”
INSERT INTO member (mid,name,email,age,sex) VALUES (2,'董鸣楠','mldnzhaopin@163.com',80,'无') ; |
范例:根据给出的数据模型编写数据库创建脚本
DROP TABLE member PURGE ; DROP TABLE advice PURGE ; CREATE TABLE member ( mid NUMBER , name VARCHAR2(200) NOT NULL , CONSTRAINT pk_mid PRIMARY KEY (mid) ) ; CREATE TABLE advice ( adid NUMBER , content CLOB NOT NULL , mid NUMBER , CONSTRAINT pk_adid PRIMARY KEY (adid) ) ; |
范例:插入正确的数据 —— 向member表插入两个会员信息
INSERT INTO member (mid,name) VALUES (1,'李兴华') ; INSERT INTO member (mid,name) VALUES (2,'董鸣楠') ; COMMIT ; |
范例:查询member表数据
SELECT * FROM member ; |
范例:插入正确的数据 —— 向advice表插入五条新记录
INSERT INTO advice (adid,content,mid) VALUES (1,'应该提倡内部沟通机制,设置总裁邮箱',1) ; INSERT INTO advice (adid,content,mid) VALUES (2,'为了使公司内部良性发展,所有的部门领导应该重新应聘上岗',1) ; INSERT INTO advice (adid,content,mid) VALUES (3,'要多开展员工培训活动,让员工更加有归属感',1) ; INSERT INTO advice (adid,content,mid) VALUES (4,'应该开展多元化业务,更加满足市场需求',2) ; INSERT INTO advice (adid,content,mid) VALUES (5,'大力发展技术部门,为本公司设计自己的ERP系统,适应电子化信息发展要求',2) ; COMMIT ; |
范例:查询advice表数据
SELECT * FROM advice ; |
范例:查询出每位成员的完整信息以及所提出的意见数量
SELECT m.mid,m.name,COUNT(a.mid) FROM member m,advice a WHERE m.mid=a.mid GROUP BY m.mid,m.name ; |
范例:在意见表(advice)中增加以下错误的信息
INSERT INTO advice (adid,content,mid) VALUES (6,'岗位职责透明化',99) ; |
范例:查询advice表数据
SELECT * FROM advice ; |
范例:修改表结构,指定主-外键约束
DROP TABLE member PURGE ; DROP TABLE advice PURGE ; CREATE TABLE member ( mid NUMBER , name VARCHAR2(200) NOT NULL , CONSTRAINT pk_mid PRIMARY KEY (mid) ) ; CREATE TABLE advice ( adid NUMBER , content CLOB NOT NULL , mid NUMBER , CONSTRAINT pk_adid PRIMARY KEY (adid) , CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ) ; |
范例:向advice表中插入错误的数据 —— 此时member表中没有mid=99的数据
INSERT INTO advice (adid,content,mid) VALUES (6,'岗位职责透明化',99) ; |
范例:插入正确的数据
INSERT INTO member (mid,name) VALUES (1,'李兴华') ; INSERT INTO member (mid,name) VALUES (2,'董鸣楠') ; INSERT INTO advice (adid,content,mid) VALUES (1,'应该提倡内部沟通机制,设置总裁邮箱',1) ; INSERT INTO advice (adid,content,mid) VALUES (3,'要多开展员工培训活动,让员工更加有归属感',2) ; COMMIT ; |
范例:删除member表中编号为“1”的数据(mid=1),此时没有删除子表(advice)数据
DELETE FROM member WHERE mid=1 ; |
范例:先删除子表(advice)中mid=1的数据,之后再删除父表(member)中mid=1的数据
DELETE FROM advice WHERE mid=1 ; DELETE FROM member WHERE mid=1 ; COMMIT ; |
范例:查询member表中的记录
SELECT * FROM member ; |
范例:修改表创建语法,增加级联删除,同时配置测试数据
DROP TABLE advice PURGE ; DROP TABLE member PURGE ; CREATE TABLE member ( mid NUMBER , name VARCHAR2(200) NOT NULL , CONSTRAINT pk_mid PRIMARY KEY (mid) ) ; CREATE TABLE advice ( adid NUMBER , content CLOB NOT NULL , mid NUMBER , CONSTRAINT pk_adid PRIMARY KEY (adid) , CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE ) ; INSERT INTO member (mid,name) VALUES (1,'李兴华') ; INSERT INTO member (mid,name) VALUES (2,'董鸣楠') ; INSERT INTO advice (adid,content,mid) VALUES (1,'应该提倡内部沟通机制,设置总裁邮箱',1) ; INSERT INTO advice (adid,content,mid) VALUES (3,'要多开展员工培训活动,让员工更加有归属感',2) ; COMMIT ; |
范例:查询member表中的当前数据
SELECT * FROM member ; |
范例:查询advice表中的当前数据
SELECT * FROM advice ; |
范例:删除member表中编号为1的成员信息
DELETE FROM member WHERE mid=1 ; |
范例:查询member表记录
SELECT * FROM member ; |
范例:查询advice表记录
SELECT * FROM advice ; |
范例:修改表的创建语句,增加ON DELETE SET NULL子句
DROP TABLE advice PURGE ; DROP TABLE member PURGE ; CREATE TABLE member ( mid NUMBER , name VARCHAR2(200) NOT NULL , CONSTRAINT pk_mid PRIMARY KEY (mid) ) ; CREATE TABLE advice ( adid NUMBER , content CLOB NOT NULL , mid NUMBER , CONSTRAINT pk_adid PRIMARY KEY (adid) , CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL ) ; INSERT INTO member (mid,name) VALUES (1,'李兴华') ; INSERT INTO member (mid,name) VALUES (2,'董鸣楠') ; INSERT INTO advice (adid,content,mid) VALUES (1,'应该提倡内部沟通机制,设置总裁邮箱',1) ; INSERT INTO advice (adid,content,mid) VALUES (3,'要多开展员工培训活动,让员工更加有归属感',2) ; COMMIT ; |
范例:删除member表中mid为1的记录
DELETE FROM member WHERE mid=1 ; |
范例:查询member表记录
SELECT * FROM member ; |
范例:查询advice表中的记录
SELECT * FROM advice ; |
范例:直接删除父表(member)
DROP TABLE member ; |
范例:先删除子表(advice),再删除父表(member)。
DROP TABLE advice PURGE ; DROP TABLE member PURGE ; |
范例:强制性删除member表
DROP TABLE member CASCADE CONSTRAINT ; |
范例:查看全部的约束名称、类型、约束设置对应的表名称
SELECT constraint_name,constraint_type,table_name FROM user_constraints ; |
范例:查询emp表上的全部约束
SELECT constraint_name,constraint_type,table_name FROM user_constraints WHERE table_name='EMP' ; |
范例:查询user_cons_columns数据字典
SELECT * FROM user_cons_columns ; |
范例:假设有如下的一张表
DROP TABLE member purge ; CREATE TABLE member( mid NUMBER , name VARCHAR2(30) , age NUMBER ) ; |
范例:查看member表中的约束
SELECT constraint_name,constraint_type,table_name FROM user_constraints WHERE table_name='MEMBER' ; |
范例:为member表的mid字段增加主键约束
ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid) ; |
范例:为member表的age增加检查约束
ALTER TABLE member ADD CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 200) ; |
为表中添加完约束之后,下面可以通过数据字典user_constraints查看member表中的全部约束。
范例:查看member表中的约束
SELECT constraint_name,constraint_type,table_name FROM user_constraints WHERE table_name='MEMBER' ; |
范例:为name字段设置非空约束
ALTER TABLE member MODIFY (name VARCHAR2(30) NOT NULL) ; |
范例:给出要操作的数据表
DROP TABLE advice PURGE ; DROP TABLE member PURGE ; CREATE TABLE member ( mid NUMBER , name VARCHAR2(200) NOT NULL , CONSTRAINT pk_mid PRIMARY KEY (mid) ) ; CREATE TABLE advice ( adid NUMBER , content CLOB NOT NULL , mid NUMBER , CONSTRAINT pk_adid PRIMARY KEY (adid) , CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL ) ; INSERT INTO member (mid,name) VALUES (1,'李兴华') ; INSERT INTO member (mid,name) VALUES (2,'董鸣楠') ; INSERT INTO advice (adid,content,mid) VALUES (1,'应该提倡内部沟通机制,设置总裁邮箱',1) ; INSERT INTO advice (adid,content,mid) VALUES (2,'为了使公司内部良性发展,所有的部门领导应该重新应聘上岗',1) ; INSERT INTO advice (adid,content,mid) VALUES (3,'要多开展员工培训活动,让员工更加有归属感',1) ; INSERT INTO advice (adid,content,mid) VALUES (4,'应该开展多元化业务,更加满足市场需求',2) ; INSERT INTO advice (adid,content,mid) VALUES (5,'大力发展技术部门,为本公司设计自己的ERP系统,适应电子化信息发展要求',2) ; COMMIT ; |
范例:禁用advice表中的adid主键约束“pk_adid”
ALTER TABLE advice DISABLE CONSTRAINT pk_adid ; |
范例:查询禁用约束之后advice表中的数据
SELECT * FROM advice ; |
范例:禁用member表中的“pk_mid”约束,此字段在advice表中是外键
ALTER TABLE member DISABLE CONSTRAINT pk_mid ; |
范例:增加两条MID相同的数据
INSERT INTO member(mid,name) VALUES (1,'MLDN') ; INSERT INTO member(mid,name) VALUES (1,'魔乐科技') ; |
范例:查询member表中的记录
SELECT * FROM member ; |
范例:重新启动member表中的主键约束“pk_mid”
ALTER TABLE member ENABLE CONSTRAINT pk_mid ; |
范例:重新启动advice表中的主键约束“pk_adid”
ALTER TABLE advice ENABLE CONSTRAINT pk_adid ; |
范例:删除advice表之中的“pk_adid”约束 —— 无关联外键
ALTER TABLE advice DROP CONSTRAINT pk_adid ; |
范例:删除member表之中的“pk_mid”约束 —— 有关联外键
ALTER TABLE member DROP CONSTRAINT pk_mid CASCADE ; |
范例:定义数据库创建脚本
-- 删除数据表 DROP TABLE grade ; DROP TABLE sporter ; DROP TABLE item ; PURGE RECYCLEBIN ; -- 创建数据表 CREATE TABLE sporter( sporterid NUMBER(4) , name VARCHAR2(30) NOT NULL , sex VARCHAR2(10) , department VARCHAR2(30) NOT NULL , CONSTRAINT pk_sporterid PRIMARY KEY (sporterid) , CONSTRAINT ck_sex CHECK (sex IN ('男','女')) ) ; CREATE TABLE item( itemid VARCHAR2(4) , itemname VARCHAR2(30) NOT NULL , location VARCHAR2(30) NOT NULL , CONSTRAINT pk_itemid PRIMARY KEY (itemid) ) ; CREATE TABLE grade( sporterid NUMBER(4) , itemid VARCHAR2(4) , mark NUMBER(1) , CONSTRAINT fk_sporterid FOREIGN KEY (sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE , CONSTRAINT fk_itemid FOREIGN KEY (itemid) REFERENCES item(itemid) ON DELETE CASCADE , CONSTRAINT ck_mark CHECK (mark IN (6,4,2,0)) ) ; |
范例:查询全部数据表
SELECT * FROM tab WHERE tname IN ('SPORTER','ITEM','GRADE') ; |
范例:查询三张数据表中的约束
SELECT constraint_name,constraint_type,table_name FROM user_constraints WHERE table_name IN ('SPORTER','ITEM','GRADE') ORDER BY table_name; |
INSERT INTO sporter(sporterid,name,sex,department) VALUES (1001,'李明','男','计算机系') ; INSERT INTO sporter(sporterid,name,sex,department) VALUES (1002,'张三','男','数学系') ; INSERT INTO sporter(sporterid,name,sex,department) VALUES (1003,'李四','男','计算机系') ; INSERT INTO sporter(sporterid,name,sex,department) VALUES (1004,'王二','男','物理系') ; INSERT INTO sporter(sporterid,name,sex,department) VALUES (1005,'李娜','女','心理系') ; INSERT INTO sporter(sporterid,name,sex,department) VALUES (1006,'孙丽','女','数学系') ; COMMIT ; |
范例:验证sporter表数据是否已经成功插入
SELECT * FROM sporter ; |
INSERT INTO item(itemid,itemname,location) VALUES ('x001','男子五千米','一操场') ; INSERT INTO item(itemid,itemname,location) VALUES ('x002','男子标枪','一操场') ; INSERT INTO item(itemid,itemname,location) VALUES ('x003','男子跳远','二操场') ; INSERT INTO item(itemid,itemname,location) VALUES ('x004','女子跳高','二操场') ; INSERT INTO item(itemid,itemname,location) VALUES ('x005','女子三千米','三操场') ; COMMIT ; |
范例:验证item表数据是否已经成功插入
SELECT * FROM item ; |
INSERT INTO grade(sporterid,itemid,mark) VALUES (1001, 'x001', 6) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1002, 'x001', 4) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1003, 'x001', 2) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1004, 'x001', 0) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1001, 'x003', 4) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1002, 'x003', 6) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1004, 'x003', 2) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1003, 'x003', 0) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1005, 'x004', 6) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1006, 'x004', 4) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1001, 'x004', 2) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1002, 'x004', 0) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1003, 'x002', 6) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1005, 'x002', 4) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1006, 'x002', 2) ; INSERT INTO grade(sporterid,itemid,mark) VALUES (1001, 'x002', 0) ; COMMIT ; |
范例:验证grade表数据是否已经成功插入
SELECT * FROM grade ; |
【第一题】、求出目前总积分最高的系名及其积分。
SELECT s.department,g.mark FROM sporter s,grade g WHERE s.sporterid=g.sporterid ; |
SELECT s.department,SUM(g.mark) FROM sporter s,grade g WHERE s.sporterid=g.sporterid GROUP BY s.department ; |
SELECT MAX(SUM(g.mark)) FROM sporter s,grade g WHERE s.sporterid=g.sporterid GROUP BY s.department ; |
SELECT s.department,SUM(g.mark) FROM sporter s,grade g WHERE s.sporterid=g.sporterid GROUP BY s.department HAVING SUM(g.mark)=( SELECT MAX(SUM(g.mark)) FROM sporter s,grade g WHERE s.sporterid=g.sporterid GROUP BY s.department) ; |
【第二题】、找出在一操场进行比赛的各项目名称及其冠军的姓名
SELECT i.itemid,g.mark FROM item i,grade g WHERE i.itemid=g.itemid AND i.location='一操场' ; |
SELECT i.itemid,MAX(g.mark) FROM item i,grade g WHERE i.itemid=g.itemid AND i.location='一操场' GROUP BY i.itemid ; |
SELECT i.itemname,s.name,g.mark FROM sporter s,item i,grade g WHERE s.sporterid=g.sporterid AND i.itemid=g.itemid AND i.location='一操场' ; |
SELECT i.itemname,s.name FROM sporter s,item i,grade g,( SELECT i.itemid iid,MAX(g.mark) max FROM item i,grade g WHERE i.itemid=g.itemid AND i.location='一操场' GROUP BY i.itemid ) temp WHERE s.sporterid=g.sporterid AND i.itemid=g.itemid AND i.location='一操场' AND i.itemid=temp.iid AND g.mark=temp.max ; |
【第三题】、找出参加了张三所参加过的项目的其他同学的姓名
SELECT sporterid FROM sporter WHERE name='张三' ; |
SELECT itemid FROM grade WHERE sporterid=( SELECT sporterid FROM sporter WHERE name='张三') ; |
SELECT DISTINCT sporterid FROM grade WHERE itemid IN ( SELECT itemid FROM grade WHERE sporterid=( SELECT sporterid FROM sporter WHERE name='张三')) ; |
SELECT name FROM sporter WHERE sporterid IN( SELECT sporterid FROM grade WHERE itemid IN ( SELECT itemid FROM grade WHERE sporterid=( SELECT sporterid FROM sporter WHERE name='张三'))) AND name<>'张三' ; |
【第四题】、经查张三因为使用了违禁药品,其成绩都记0分,请在数据库中作出相应修改。
UPDATE grade SET mark=0 WHERE sporterid=( SELECT sporterid FROM sporter WHERE name='张三') ; |
SELECT s.name,g.itemid,g.mark FROM sporter s , grade g WHERE s.sporterid=g.sporterid AND s.name='张三' ; |
【第五题】、经组委会协商,需要删除女子跳高比赛项目。
DELETE FROM item WHERE itemname='女子跳高' ; |
范例:查询项目表中是否已经成功删除
SELECT * FROM item WHERE itemname='女子跳高' ; |