oracle11g如何查找表,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) summax

FROM sporter s,

(SELECT g.sporterid,SUM(mark) sm FROM grade g GROUP BY g.sporterid ORDER BY g.sporterid)sums

WHERE s.sporterid=sums.sporterid

GROUP BY s.department

ORDER BY summax DESC)

WHERE ROWNUM=1

;

--1.2

SELECT *

FROM (SELECT s.department sd,SUM(sums.sm) summax

FROM sporter s,

(SELECT g.sporterid,SUM(mark) sm FROM grade g GROUP BY g.sporterid ORDER BY g.sporterid)sums

WHERE s.sporterid=sums.sporterid

GROUP BY s.department

ORDER BY summax DESC)temp

WHERE temp.summax = (SELECT MAX(SUM(sums.sm)) summax

FROM sporter s,

(SELECT g.sporterid,SUM(mark) sm FROM grade g GROUP BY g.sporterid ORDER BY g.sporterid)sums

WHERE s.sporterid=sums.sporterid

GROUP BY s.department

--ORDER BY summax DESC

)

;

第二天续

--2

SELECT name,tmpname.itemid,i.itemname

FROM sporter s,item i,

(SELECT sporterid,itemid FROM grade WHERE itemid IN (SELECT itemid FROM item WHERE location IN '一操场') AND mark=6)tmpname

WHERE s.sporterid=tmpname.sporterid AND tmpname.itemid=i.itemid

;

--3

SELECT name

FROM sporter s,

(SELECT DISTINCT sporterid FROM grade WHERE itemid IN (SELECT itemid FROM grade WHERE sporterid IN(SELECT sporterid FROM sporter WHERE name IN ('张三'))))spid

WHERE s.sporterid=spid.sporterid AND name NOT IN ('张三')

;

--4

UPDATE grade

SET mark = 0

WHERE sporterid IN (SELECT sporterid FROM sporter WHERE name IN ('张三'))

;

--5

DELETE FROM item

WHERE itemname IN ('女子跳高')

;

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

--创建序列

CREATE SEQUENCE studentPKSequence START WITH 1 INCREMENT BY 1;

--序列增长

SELECT studentPKSequence.nextVal AS studentid FROM dual;

--删除序列

DROP SEQUENCE studentPKSequence;