Oracle11g简单建表（约束）、查询、修改脚本

记录一下简单的创建和约束脚本，还有几个一般查询的语句

等以后熟练了再来改进

题目源于“MLDN魔乐科技_Oracle课堂20_完整练习.rar”,大家应该能电驴到

发的东西和课程里讲的答案不一样，自己瞎写的 不过功能无误 

DROP TABLE grade;DROP TABLE sporter;DROP TABLE item;CREATE TABLE sporter(sporterid NUMBER(9),name VARCHAR2(50) NOT NULL,sex VARCHAR2(3) NOT NULL,department VARCHAR2(30) NOT NULL,CONSTRAINT sporter_sporterid_pk PRIMARY KEY(sporterid),CONSTRAINT sporter_sex_ck CHECK(sex IN ('男','女','中')));CREATE TABLE item(itemid VARCHAR2(9),itemname VARCHAR2(50) NOT NULL,location VARCHAR2(50) NOT NULL,CONSTRAINT item_itemid_pk PRIMARY KEY(itemid));CREATE TABLE grade(sporterid NUMBER(9),itemid VARCHAR2(9),mark NUMBER(9) NOT NULL,--CONSTRAINT grade_sporterid_pk PRIMARY KEY(sporterid),CONSTRAINT grade_mark_ck CHECK(mark IN(6,4,2,0)),CONSTRAINT sporter_grade_sportid_fk FOREIGN KEY(sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE,CONSTRAINT item_grade_itemid_fk FOREIGN KEY(itemid) REFERENCES item(itemid) ON DELETE CASCADE);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,'孙俪','女','数学系');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','女子三千米','三操场');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(1005,'x004','6');INSERT INTO grade(sporterid,itemid,mark) VALUES(1006,'x004','4');--查询--1.1 分数总和最高的系名和对应分数SELECT *FROM (SELECT s.department sd,SUM(sums.sm) summaxFROM sporter s,(SELECT g.sporterid,SUM(mark) sm FROM grade g GROUP BY g.sporterid ORDER BY g.sporterid)sumsWHERE s.sporterid=sums.sporteridGROUP BY s.departmentORDER BY summax DESC)WHERE ROWNUM=1;--1.2SELECT *FROM (SELECT s.department sd,SUM(sums.sm) summaxFROM sporter s,(SELECT g.sporterid,SUM(mark) sm FROM grade g GROUP BY g.sporterid ORDER BY g.sporterid)sumsWHERE s.sporterid=sums.sporteridGROUP BY s.departmentORDER BY summax DESC)tempWHERE temp.summax = (SELECT MAX(SUM(sums.sm)) summaxFROM sporter s,(SELECT g.sporterid,SUM(mark) sm FROM grade g GROUP BY g.sporterid ORDER BY g.sporterid)sumsWHERE s.sporterid=sums.sporteridGROUP BY s.department--ORDER BY summax DESC);

第二天续

--2SELECT name,tmpname.itemid,i.itemnameFROM sporter s,item i,(SELECT sporterid,itemid FROM grade WHERE itemid IN (SELECT itemid FROM item WHERE location IN '一操场') AND mark=6)tmpnameWHERE s.sporterid=tmpname.sporterid AND tmpname.itemid=i.itemid;--3SELECT nameFROM sporter s,(SELECT DISTINCT sporterid FROM grade WHERE itemid IN (SELECT itemid FROM grade WHERE sporterid IN（SELECT sporterid FROM sporter WHERE name IN ('张三')）))spidWHERE s.sporterid=spid.sporterid AND name NOT IN ('张三');--4UPDATE grade SET mark = 0WHERE sporterid IN (SELECT sporterid FROM sporter WHERE name IN ('张三'));--5DELETE FROM itemWHERE itemname IN ('女子跳高');

补充个序列的创建、查询、删除：

--创建序列CREATE SEQUENCE studentPKSequence START WITH 1 INCREMENT BY 1;--序列增长SELECT studentPKSequence.nextVal AS studentid FROM dual;--删除序列DROP SEQUENCE studentPKSequence;