范例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='女子跳高' ; |