数据库概论实验

电玩女神 2022-03-20 15:56 269阅读 0赞

查看表结构：DESC STUDENT ;  
查看所有表：SELECT \* FROM TAB;  
ORACLE查看所有用户：SELECT \* FROM ALL\_USERS;

例1-1： (建立数据库表) 建立教学数据库的四个数据库表，其中Student表中不包含SSEX(C,2) 字段，Sname 字段为Sname(C,8)且可为空。  
学生情况表：  
CREATE TABLE STUDENT  
(SNO CHAR(5) CONSTRAINT PK\_S PRIMARY KEY,  
SNAME VARCHAR2(8),  
SDEPT CHAR(2) NOT NULL,  
SCLASS CHAR(2) NOT NULL,  
SAGE NUMBER(2) CHECK (SAGE BETWEEN 5 AND 50)  
);  
课程名称表：  
CREATE TABLE COURSE  
(CNO CHAR(3),  
CNAME VARCHAR2(16)NOT NULL UNIQUE,  
CTIME NUMBER(3),  
CONSTRAINT PK\_C PRIMARY KEY(CNO)) ;  
教师授课表：  
CREATE TABLE TEACH  
(TNAME VARCHAR2(8) ,  
TSEX CHAR(2) CHECK(TSEX IN('男','女')),  
CNO CHAR(3) NOT NULL,  
TDATE DATE NOT NULL,  
TDEPT CHAR(2) NOT NULL,  
CONSTRAINT PK\_T PRIMARY KEY (TNAME,CNO,TDEPT),  
CONSTRAINT FK\_T\_C FOREIGN KEY (CNO) REFERENCES COURSE(CNO)  
);  
成绩表：  
CREATE TABLE SCORE  
(SNO CHAR(5),  
CNO CHAR(3),  
SCORE NUMBER(5,2),  
CONSTRAINT PK\_SC PRIMARY KEY (SNO,CNO),  
CONSTRAINT FK\_SC\_S FOREIGN KEY (SNO)  
REFERENCES STUDENT(SNO) ON DELETE CASCADE,  
CONSTRAINT FK\_SC\_C FOREIGN KEY (CNO) REFERENCES COURSE(CNO));

例1-2： (修改数据库表) 在Student表中增加SEX(C,2) 字段。  
ALTER TABLE STUDENT ADD SEX CHAR(2);

例1-3： (修改列名) 将Student表中列名SEX修改为SSEX。  
ALTER TABLE STUDENT RENAME COLUMN SEX TO SSEX;

例1-4： (修改数据库表) 将Student表中把Sname 字段修改为Sname(C,10)且为非空。  
ALTER TABLE STUDENT MODIFY (SNAME VARCHAR2(10));  
ALTER TABLE STUDENT MODIFY SNAME NOT NULL;

例1-5： (建立索引) 为Score表按课程号升序、分数降序建立索引，索引名为SC\_GRADE。  
CREATE INDEX SC\_GRADE ON SCORE(CNO ASC,SCORE DESC);  
例1-6： (删除索引) 删除索引SC\_GRADE。  
DROP INDEX SC\_GRADE;  
例1-7： (建立数据库表) 建立数据库表S1(SNO,SNAME,SD,SA)，其字段类型定义与Student表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。  
CREATE TABLE S1  
(SNO CHAR(5) CONSTRAINT PK\_S1 PRIMARY KEY,  
SNAME VARCHAR2(8),  
SD CHAR(2) NOT NULL,  
SA NUMBER(2) CHECK (SA BETWEEN 5 AND 50));  
例1-8： (修改数据库表) 删除成绩表Score的参照完整性约束关系。  
ALTER TABLE SCORE DROP CONSTRAINT FK\_SC\_C;  
ALTER TABLE SCORE DROP CONSTRAINT FK\_SC\_S;

例1-9： (修改数据库表) 添加成绩表Score的参照完整性约束关系。  
ALTER TABLE SCORE ADD CONSTRAINT FK\_SC\_C FOREIGN KEY (CNO)  
REFERENCES COURSE (CNO);

ALTER TABLE SCORE ADD CONSTRAINT FK\_SC\_S FOREIGN KEY (SNO)  
REFERENCES STUDENT(SNO) ON DELETE CASCADE,

例1-10： (修改数据库表名) 将数据库表S1改名为Student\_Temp。  
RENAME S1 TO STUDENT\_TEMP;

2． SQL数据操纵语句：  
例2-1： (插入数据) 按前面各表中的数据分别插入到教学数据库的四个数据库表中。  
学生情况表：  
INSERT INTO STUDENT VALUES('96001','马小燕','CS','01',21,'女');  
INSERT INTO STUDENT VALUES('96002','黎明','CS','01',18,'男');  
INSERT INTO STUDENT VALUES('96003','刘东明','MA','01',18,'男');  
INSERT INTO STUDENT VALUES('96004','赵志勇','IS','02',20,'男');  
INSERT INTO STUDENT VALUES('97001','马蓉','MA','02',19,'女');  
INSERT INTO STUDENT VALUES('97002','李成功','CS','01',20,'男');  
INSERT INTO STUDENT VALUES('97003','黎明','IS','03',19,'女');  
INSERT INTO STUDENT VALUES('97004','李丽','CS','02',19,'女');  
INSERT INTO STUDENT VALUES('96005','司马志明','CS','02',18,'男');  
课程名称表：  
INSERT INTO COURSE VALUES('001','数序分析','144');  
INSERT INTO COURSE VALUES('002','普通物理',144);  
INSERT INTO Course VALUES('003','微机原理',72);  
INSERT INTO COURSE VALUES('004','数据结构',72);  
INSERT INTO COURSE VALUES('005','操作系统',64);  
INSERT INTO COURSE VALUES('006','数据库原理',64);  
INSERT INTO COURSE VALUES('007','DB\_Design',48);  
INSERT INTO COURSE VALUES('008','程序设计',56);  
教师授课表：  
INSERT INTO TEACH VALUES('王成刚','男','004',TO\_DATE( '1999-09-05', 'YYYY-MM-DD'),'CS');  
INSERT INTO TEACH VALUES('李正科','男','003',TO\_DATE( '1999-09-05', 'YYYY-MM-DD'),'CS');  
INSERT INTO TEACH VALUES('严敏','女','001',TO\_DATE( '1999-09-05', 'YYYY-MM-DD'),'MA');  
INSERT INTO TEACH VALUES('赵高','男','004',TO\_DATE( '1999-09-05', 'YYYY-MM-DD'),'IS');  
INSERT INTO TEACH VALUES('李正科','男','003',TO\_DATE( '2000-02-23', 'YYYY-MM-DD'),'MA');  
INSERT INTO TEACH VALUES('刘玉兰','女','006',TO\_DATE( '2000-02-23', 'YYYY-MM-DD'),'CS');  
INSERT INTO TEACH VALUES('王成刚','男','004',TO\_DATE( '2000-02-23', 'YYYY-MM-DD'),'IS');  
INSERT INTO TEACH VALUES('马悦','女','008',TO\_DATE( '2000-09-06', 'YYYY-MM-DD'),'CS');  
INSERT INTO TEACH VALUES('王成刚','男','007',TO\_DATE( '1999-09-05', 'YYYY-MM-DD'),'CS');  
成绩表：  
INSERT INTO SCORE VALUES('96001','001',77.5);  
INSERT INTO SCORE VALUES('96001','003',89);  
INSERT INTO SCORE VALUES('96001','004',86);  
INSERT INTO SCORE VALUES('96001','005',82);  
INSERT INTO SCORE VALUES('96002','001',88);  
INSERT INTO SCORE VALUES('96002','003',92.5);  
INSERT INTO SCORE VALUES('96002','006',90);  
INSERT INTO SCORE VALUES('96005','004',92);  
INSERT INTO SCORE VALUES('96005','005',90);  
INSERT INTO SCORE VALUES('96005','006',89);  
INSERT INTO SCORE VALUES('96005','007',76);  
INSERT INTO SCORE VALUES('96003','001',69);  
INSERT INTO SCORE VALUES('97001','001',96);  
INSERT INTO SCORE VALUES('97001','008',95);  
INSERT INTO SCORE VALUES('96004','001',87);  
INSERT INTO SCORE VALUES('96003','003',91);  
INSERT INTO SCORE VALUES('97002','003',91);  
INSERT INTO SCORE VALUES('97002','004','');  
INSERT INTO SCORE VALUES('97002','006',92);  
INSERT INTO SCORE VALUES('97004','005',90);  
INSERT INTO SCORE VALUES('97004','006',85);  
INSERT INTO SCORE VALUES('97004','008',75);  
INSERT INTO SCORE VALUES('97003','001',59);  
INSERT INTO SCORE VALUES('97003','003',58);

例2-2：(多行插入) 将表Student表中计算机系(‘CS’)的学生数据插入到表S1中。  
（以上操作中，注意用COMMIT提交数据，将数据保存到数据库服务器）  
INSERT INTO STUDENT\_TEMP(SNO,SNAME,SD,SA)  
SELECT SNO,SNAME,SDEPT,SAGE FROM STUDENT  
WHERE SDEPT='CS';

例2-3：(利用查询来实现表的定义与数据插入) 求每一个学生的平均成绩，把结果存入数据库表Student\_Gr中  
CREATE TABLE STUDENT\_GR  
(SNO CHAR(8) NOT NULL,  
SAVG NUMBER(5,2)  
);  
INSERT INTO STUDENT\_GR(SNO,SAVG)  
SELECT SNO, AVG(SCORE)  
FROM SCORE GROUP BY SNO;

例2-4： (修改数据) 将Student\_Temp表中所有学生的年龄加2。  
UPDATE STUDENT\_TEMP SET SA=SA+2;

例2-5： (修改数据) 将Course表中‘程序设计’课时数修改成与‘数据结构’的课时数相同。  
UPDATE COURSE SET CTIME = ( SELECT CTIME FROM COURSE  
WHERE CNAME = '数据结构' );

例2-6： (插入数据) 向Score表中插入数据（‘98001’, ‘001’, 95），根据返回信息解释其原因。  
INSERT INTO SCORE VALUES (‘'98001','001',95 );  
原因分析：违反完整约束条件 (U109074104.FK\_SC\_S),因为SCORE表中的SNO这个字段是根据STUDENT中的SNO创建的，但STUDENT表中还没有’98001’这个学号。

例2-7： (插入数据) 向Score表中插入数据（‘97001’, ‘010’, 80），根据返回信息解释其原因。  
INSERT INTO SCORE VALUES ('97001','010',80);  
原因分析：违反完整约束条件 (U109074104.FK\_SC\_C);因为SCORE表中的CNO这个字段是根据COURSE中的CNO字段创建的，但COURSE表中还没有’010’这个课程号；

例2-8： (删除数据) 删除Student表中学号为‘96001’的学生信息，根据返回信息解释其原因。  
DELETE FROM STUDENT WHERE SNO = '96001';  
原因分析：我删除成功了，因为在建表时没有说明具体的约束关系，我在建SCORE时加了ON DELETE CASCADE 的约束，所以能正常删除。

例2-9： (删除数据) 删除Course表中课程号为‘003’ 的课程信息，根据返回信息解释其原因。  
DELETE FROM COURSE WHERE CNO = '003';  
原因分析：因为违反完整约束条件 (U109074104.FK\_SC\_C),COURSE中的CNO和SCORE表中的CNO有约束关系，不能直接删除。

例2-10： (删除数据) 删除学生表Student\_Temp中学号以96打头的学生信息。  
（此操作后，注意用ROLLBACK回退可能更新的数据）  
DELETE FROM STUDENT\_TEMP WHERE SNO LIKE '96%';

例2-11： (删除数据) 删除数据库表Student\_Temp中所有学生的数据。  
DELETE FROM STUDENT\_TEMP;

例2-12：(删除表) 删除数据库表Student\_Temp和Student\_Gr。  
DROP TABLE STUDENT\_TEMP;  
DROP TABLE STUDENT\_GR;

3． SQL数据查询语句：  
例3-1： (选择表中的若干列) 求全体学生的学号、姓名、性别和年龄。  
SELECT SNO,SNAME,SSEX,SAGE FROM STUDENT;

例3-2： (不选择重复行) 求选修了课程的学生学号。  
SELECT DISTINCT SNO FROM SCORE WHERE CNO IS NOT NULL;

例3-3： (选择表中的所有列) 求全体学生的详细信息。  
SELECT \* FROM STUDENT;

例3-4： (使用表达式) 求全体学生的学号、姓名和出生年份。  
SELECT SNO,SNAME,2012-SAGE AS "BIRTH" FROM STUDENT;

例3-5： (使用列的别名) 求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。  
SELECT SNO AS "学号",2012-SAGE AS "出生年份" FROM STUDENT;

例3-6： (比较大小条件) 求年龄大于19岁的学生的姓名和年龄。  
SELECT SNAME,SAGE FROM STUDENT  
WHERE SAGE > 19;

例3-7： (比较大小条件) 求计算机系或[信息系][Link 1]年龄大于18岁的学生的姓名、系和年龄。  
SELECT SNAME,SDEPT,SAGE FROM STUDENT  
WHERE ( SDEPT = 'CS' OR SDEPT = 'IS' )  
AND SAGE > 18

SELECT SNAME,SDEPT,SAGE FROM STUDENT  
WHERE SDEPT IN ( 'CS', 'IS' )  
AND SAGE > 18;

例3-8： (确定范围条件) 求年龄在19岁与22岁(含20岁和22岁)之间的学生的学号和年龄。  
SELECT SNO,SAGE FROM STUDENT  
WHERE SAGE BETWEEN 19 AND 22;

例3-9： (确定范围条件) 求年龄不在19岁与22岁之间的学生的学号和年龄。  
SELECT SNO,SAGE FROM STUDENT  
WHERE SAGE NOT BETWEEN 19 AND 22

例3-10：(确定集合条件) 求在下列各系的学生信息：数学系、计算机系。  
SELECT \* FROM STUDENT  
WHERE SDEPT IN ( 'MA', 'CS' );

例3-11：(确定集合条件) 求不是数学系、计算机系的学生信息。  
SELECT \* FROM STUDENT  
WHERE SDEPT NOT IN ( 'MA', 'CS' )

例3-12：(匹配查询) 求姓名是以“李”打头的学生。  
SELECT \* FROM STUDENT  
WHERE SNAME LIKE '李%';

例3-13：(匹配查询) 求姓名中含有“志”的学生。  
SELECT \* FROM STUDENT  
WHERE SNAME LIKE '%志%';

例3-14：(匹配查询) 求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。  
SELECT \* FROM STUDENT  
WHERE SNAME LIKE '%马\_\_';

例3-15：(匹配查询) 求选修课程001或003，成绩在80至90之间，学号为96xxx的学生的学号、课程号和成绩。  
SELECT SNO,CNO,SCORE FROM SCORE  
WHERE CNO IN ( '001', '003' )  
AND SCORE BETWEEN 80 AND 90  
AND SNO LIKE '96\_\_\_';

例3-16：(匹配查询) 求课程名中包含 ’\_’ 字符的课程号、课程名和学时数。  
SELECT CNO,CNAME,CTIME FROM COURSE  
WHERE CNAME LIKE '%\\\_%' ESCAPE '\\'

例3-17：(涉及空值查询) 求缺少学习成绩的学生的学号和课程号。  
SELECT SNO,CNO FROM SCORE  
WHERE SCORE IS NULL;

例3-18：(控制行的显示顺序) 求选修003课程或004课程的学生的学号、课程号和分数，要求按课程号升序、分数降序的顺序显示结果。(空值当最大处理)  
SELECT SNO,CNO,SCORE FROM SCORE  
WHERE CNO IN ( '003', '004' )  
ORDER BY CNO ASC,SCORE DESC;

例3-19：(组函数) 求学生总人数。  
SELECT COUNT(\*) FROM STUDENT;

例3-20：(组函数) 求选修了课程的学生人数。  
SELECT COUNT ( DISTINCT SNO ) FROM SCORE  
WHERE SCORE IS NOT NULL;

例3-21：(组函数) 求计算机系学生的平均年龄。  
SELECT AVG ( SAGE ) FROM STUDENT  
WHERE SDEPT = 'CS';

例3-22：(组函数) 求选修了课程001的最高、最低与平均成绩。  
SELECT MAX ( SCORE ), MIN ( SCORE ), AVG ( SCORE )  
WHERE CNO = '001';

例3-23：(分组查询) 求各门课程的平均成绩与总成绩。(不需要where语句)  
SELECT AVG ( SCORE ), SUM ( SCORE ) FROM SCORE  
GROUP BY CNO

例3-24：(分组查询) 求各系、各班级的人数和平均年龄。  
SELECT COUNT ( SNO ), AVG ( SAGE ) FROM STUDENT  
GROUP BY SDEPT,SCLASS;

例3-25：(分组查询) 输入以下查询语句并执行，观察出现的其结果并分析其原因。  
SELECT SNAME，SDEPT，COUNT（\*）FROM STUDENT  
WHERE SDEPT=’CS’ GROUP BY SDEPT;  
出错提示：ORA-00979: 不是 GROUP BY 表达式  
原因分析：在包含GROUP BY子句的查询语句中，SELECT子句后面的所有字段列表（除组函数外），均应该包含在GROUP BY 子句中，即所选项与分组的一致性。

例3-26：(分组查询) 分析以下语句为什么会出现错误。并给出正确的查询语句。  
SELECT SAGE FROM STUDENT GROUP BY SNO;  
出错提示：ORA-00979: 不是 GROUP BY 表达式  
原因分析：在包含GROUP BY子句的查询语句中，SELECT子句后面的所有字段列表（除组函数外），均应该包含在GROUP BY 子句中，即所选项与分组的一致性。  
正确语句：SELECT SAGE FROM STUDENT;

例3-27：(分组查询) 求学生人数不足3人的系及其相应的学生数。  
SELECT SDEPT, COUNT ( SNO ) FROM STUDENT  
GROUP BY SDEPT  
HAVING COUNT ( SNO ) < 3;

例3-28：(分组查询) 求各系中除01班之外的各班的学生人数。  
SELECT COUNT ( SNO ) FROM STUDENT  
WHERE SCLASS != '01'  
GROUP BY SDEPT, SCLASS;

例3-29：(涉及空值的查询) 分别观察各组函数、行的显示顺序以及分组查询与空值的关系。  
  
例3-30：(连接查询) 求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。  
SELECT SNAME,CNAME,SCORE FROM STUDENT,SCORE,COURSE  
WHERE SCORE NOT BETWEEN 70 AND 90  
AND SCORE.CNO = '001'  
AND STUDENT.SNO = SCORE.SNO  
AND COURSE.CNO = SCORE.CNO;

例3-31：(连接查询与表的别名) 求选修了课程的学生的学生姓名、课程号和成绩。(怎么消除重复行？？)  
SELECT SNAME,CNO,SCORE FROM STUDENT S,SCORE SC  
WHERE S.SNO = SC.SNO;

例3-32：(自身连接查询) 求年龄大于 ’李丽’ 的所有学生的姓名、系和年龄。  
SELECT S1.SNAME,S1.SDEPT,S1.SAGE FROM STUDENT S1, STUDENT S2  
WHERE S1.SAGE > S2.SAGE  
AND S2.SNAME = '李丽';

例3-33：(外部连接查询) 求选修了课程002或003的学生的学号、课程号、课程名和成绩，要求必须将002和003课程的相关信息显示出来。  
SELECT SC.SNO,SC.CNO,C.CNAME,SC.SCORE FROM SCORE SC,COURSE C  
WHERE SC.CNO IN ( '002','003' )  
AND SC.CNO = C.CNO(+);  
  
例3-34：(子查询) 求与 ‘黎明’ 年龄相同的学生的姓名和系。(IN不能换成等号)  
SELECT SNAME,SDEPT FROM STUDENT S  
WHERE S.SAGE IN ( SELECT SAGE FROM STUDENT  
WHERE SNAME = '黎明' );

例3-35：(子查询) 求选修了课程名为 ’数据结构’ 的学生的学号和姓名。  
SELECT S.SNO,S.SNAME FROM STUDENT S,SCORE SC,COURSE C  
WHERE S.SNO = SC.SNO  
AND SC.CNO = C.CNO  
AND C.CNAME = '数据结构';

或者:  
SELECT SNO,SNAME FROM STUDENT  
WHERE SNO IN(  
SELECT SNO FROM SCORE  
WHERE CNO=(  
SELECT CNO FROM COURSE  
WHERE CNAME='数据结构') ) ;

例3-36：(子查询ANY) 求比数学系中某一学生年龄大的学生的姓名和系。  
SELECT SNAME,SDEPT FROM STUDENT  
WHERE SAGE > ANY ( SELECT SAGE FROM STUDENT  
WHERE SDEPT = 'CS' )  
AND SDEPT <> 'CS';

或者:  
SELECT SNAME,SDEPT FROM STUDENT  
WHERE SAGE > ( SELECT MIN(SAGE) FROM STUDENT  
WHERE SDEPT = 'CS' )  
AND SDEPT <> 'CS';

例3-37：(子查询ALL) 求比数学系中全体学生年龄大的学生的姓名和系。  
SELECT SNAME,SDEPT FROM STUDENT  
WHERE SAGE > ALL ( SELECT SAGE FROM STUDENT  
WHERE SDEPT = 'MA' )  
AND SDEPT <> 'MA';  
或者:  
SELECT SNAME,SDEPT FROM STUDENT  
WHERE SAGE > ( SELECT MAX(SAGE) FROM STUDENT  
WHERE SDEPT = 'MA' )  
AND SDEPT <> 'MA'

例3-38：(子查询EXISTS) 求选修了课程004的学生的姓名和系。  
SELECT SNAME,SDEPT FROM STUDENT  
WHERE EXISTS ( SELECT \* FROM SCORE  
WHERE SNO = STUDENT.SNO  
AND CNO = '004' );

例3-39：(返回多列的子查询) 求与 ‘李丽’ 同系且同龄的学生的姓名和系。  
SELECT SNAME,SDEPT FROM STUDENT  
WHERE SDEPT = ( SELECT SDEPT FROM STUDENT  
WHERE SNAME = '李丽' )  
AND SAGE = ( SELECT SAGE FROM STUDENT  
WHERE SNAME = '李丽' );

例3-40：(多个子查询) 求与 ‘‘黎明’ 同系，且年龄大于 ‘李丽’ 的学生的信息。  
SELECT \* FROM STUDENT  
WHERE SDEPT IN ( SELECT SDEPT FROM STUDENT  
WHERE SNAME = '黎明' )  
AND SAGE > ( SELECT SAGE FROM STUDENT  
WHERE SNAME = '李丽')

例3-41：(子查询中使用表连接) 求数学系中年龄相同的学生的姓名和年龄。  
SELECT STU1.SNAME,STU1.SAGE FROM STUDENT STU1,STUDENT STU2  
WHERE STU1.SDEPT = 'MA' AND STU2.SDEPT = 'MA'  
AND STU1.SAGE = STU2.SAGE  
AND STU1.SNO <> STU2.SNO；  
  
例3-42：(连接或嵌套查询) 检索至少选修王成刚老师所授课程中一门课程的女学生姓名。  
SELECT SNAME FROM STUDENT S,SCORE SC  
WHERE SSEX = '女'  
AND S.SNO = SC.SNO  
AND CNO IN ( SELECT CNO FROM TEACH  
WHERE TNAME = '王成刚' ;  
  
例3-43：(嵌套与分组查询) 检索选修某课程的学生人数多于3人的教师姓名。(有问题)  
SELECT DISTINCT TNAME FROM TEACH  
WHERE CNO IN ( SELECT CNO FROM SCORE  
GROUP BY CNO  
HAVING COUNT ( CNO ) > 3 );

例3-44：(集合查询) 列出所有教师和同学的姓名和性别。  
SELECT SNAME AS "NAME",SSEX AS "SEX" FROM STUDENT  
UNION  
SELECT TNAME,TSEX FROM TEACH;

例3-45：(相关子查询) 求未选修课程004的学生的姓名。  
SELECT SNAME FROM STUDENT S1  
WHERE NOT EXISTS  
( SELECT \* FROM SCORE  
WHERE SNO = S1.SNO  
AND CNO = '004' );

例3-46：(相关子查询) 求选修了全部课程的学生的姓名。  
SELECT SNAME FROM STUDENT S  
WHERE NOT EXISTS  
( SELECT \* FROM COURSE C  
WHERE NOT EXISTS  
( SELECT \* FROM SCORE  
WHERE SNO = S.SNO  
AND CNO = C.CNO ) );

例3-47：(相关子查询) 求至少选修了学生 ‘96002’ 所选修的全部课程的学生的学号。  
SELECT DISTINCT SNO FROM SCORE SC1  
WHERE NOT EXISTS  
( SELECT \* FROM SCORE SC2  
WHERE SC2.SNO = '96002'  
AND NOT EXISTS  
( SELECT \* FROM SCORE SC3  
WHERE SNO = SC1.SNO  
AND CNO = SC2.CNO ) )

例3-48：(相关子查询) 求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。  
SELECT SNO,CNO,SCORE FROM SCORE SC  
WHERE EXISTS  
(SELECT \* FROM SCORE  
GROUP BY CNO  
HAVING SC.SCORE > AVG ( SCORE )  
AND CNO = SC.CNO );

例3-49：(相关子查询) 查询被一个以上的学生选修的课程号。  
SELECT CNO FROM COURSE C  
WHERE EXISTS  
( SELECT \* FROM SCORE  
GROUP BY CNO  
HAVING COUNT ( SNO ) > 1  
AND C.CNO = CNO );

例3-50：(相关子查询) 查询所有未选课程的学生姓名和所在系。  
SELECT SNAME,SDEPT FROM STUDENT S  
WHERE NOT EXISTS  
( SELECT \* FROM SCORE  
WHERE S.SNO = SNO );

4． SQL视图的定义与操纵：  
例4-1： (建立视图) 建立计算机系的学生的视图STUDENT\_CS。  
CREATE VIEW STUDENT\_CS AS  
SELECT \* FROM STUDENT  
WHERE SDEPT = 'CS';

例4-2： (建立视图) 建立由学号和平均成绩两个字段的视图STUDENT\_GR。  
CREATE VIEW STUDENT\_GR ( SNO,SCAVG ) AS  
SELECT SNO,AVG ( SCORE ) FROM SCORE  
GROUP BY SNO;

例4-3： (视图查询) 利用视图STUDENT\_CS，求年龄大于19岁的学生的全部信息。  
SELECT \* FROM STUDENT\_CS  
WHERE SAGE > 19;

例4-4： (视图查询) 利用视图STUDENT\_GR，求平均成绩为88分以上的学生的学号和平均成绩。  
SELECT SNO,SCAVG FROM STUDENT\_GR  
WHERE SCAVG > 88;

例4-5： (视图更新) 利用视图STUDENT\_CS，增加学生( ‘96006’，‘张然’，‘CS’，‘02’，‘男’，19 )。  
INSERT INTO STUDENT\_CS VALUES ( '96006','张然','CS','02',19,'男' );

例4-6： (视图更新) 利用视图STUDENT\_CS，将学生年龄增加1岁。观察其运行结果并分析原因。  
UPDATE STUDENT\_CS SET SAGE = SAGE + 1;  
运行结果：对视图的改变改变了基本表。  
原因分析：对视图的修改实际是对基本表的修改

例4-7： (视图更新) 利用视图STUDENT\_GR，将平均成绩增加2分。观察其运行结果并分析原因。  
UPDATE STUDENT\_GR SET SCAVG = SCAVG + 2；  
原因分析：不能对含有聚合函数的视图进行更新操作，因为更新的结果是会反映到基本表上的，而更新聚合函数无法得知更新基本表的哪个原件。

例4-8： (视图更新) 删除视图STUDENT\_CS中学号为 ‘96006’ 的学生的全部数据。  
DELETE FROM STUDENT\_CS WHERE SNO = '96006';

例4-9： (视图更新) 删除视图STUDENT\_GR的全部数据。  
DELETE FROM STUDENT\_GR;  
错误提示：ORA-01732: 此视图的数据操纵操作非法  
原因分析：若视图的字段来自聚集函数，则视图不可更新。

例4-10：(删除视图) 删除视图STUDENT\_CS和STUDENT\_GR。  
DROP VIEW STUDENT\_GR;  
DROP VIEW STUDENT\_CS;

5． SQL数据控制语句：  
例5-1： (授权) 给左右邻近同学（用户）授予在表Student上的SELECT权限，并使这两个用户具有给其他用户授予相同权限的权限。  
GRANT SELECT ON STUDENT TO U109074103  
WITH GRANT OPTION;

例5-2： (授权) 给邻近同学（用户）授予Teach表上的所有权限。  
GRANT ALL ON TEACH TO U109074103;

例5-3： (授权) 给所有用户授予Score表上的SELECT权限。  
GRANT SELECT ON SCORE TO PUBLIC;

例5-4： (授权验证) 观察左右邻近同学查询你所授权的表中的内容。  
SELECT \* FROM USER\_TAB\_PRIVS;

例5-5： (收回授权) 收回上面例子中的所有授予的权限。  
REVOKE SELECT ON STUDENT FROM U109074103;  
REVOKE ALL ON TEACH FROM U109074103;  
REVOKE SELECT ON SCORE FROM PUBLIC;

6． SQL事务处理：  
例6-1： (事务回退) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、‘微机原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,取消所有修改(ROLLBACK)。再次查询全部课程的总学时数。注意比较分析两次查询的结果。  
UPDATE COURSE SET CTIME = 80  
WHERE CNAME = '程序设计';  
UPDATE COURSE SET CTIME = 70  
WHERE CNAME = '微机原理';  
SELECT \* FROM COURSE;  
  
ROLLBACK;  
SELECT \* FROM COURSE;  
  
例6-2： (事务提交) 将课程名称表中的 ‘程序设计’ 课程学时数修改为80、‘微机原理’ 课程学时数修改为70学时，查询全部课程的总学时数后,确认所有修改(COMMIT)。再次查询全部课程的总学时数。注意比较分析两次查询的结果  
UPDATE COURSE SET CTIME = 80  
WHERE CNAME = '程序设计';  
UPDATE COURSE SET CTIME = 70  
WHERE CNAME = '微机原理';  
SELECT \* FROM COURSE;  
  
COMMIT;  
SELECT \* FROM COURSE;  
  
7． 存储过程与触发器：  
例7-1： (存储过程) 创建一个显示学生总人数的存储过程  
CREATE OR REPLACE PROCEDURE STU\_COUNT (SUM OUT NUMBER)  
AS  
BEGIN  
SELECT COUNT(\*) INTO SUM FROM STUDENT;  
END;  
/  
SET SERVEROUTPUT ON

DECLARE  
SUM1 NUMBER;  
BEGIN  
STU\_COUNT (SUM1);  
DBMS\_OUTPUT.PUT\_LINE('学生总数：'||SUM1);  
END;  
/

例7-2： (存储过程) 创建显示学生信息的存储过程STUDENT\_LIST，并引用STU\_COUNT存储过程。  
CREATE OR REPLACE PROCEDURE STUDENT\_LIST IS  
CURSOR C IS  
SELECT SNO,SNAME,SDEPT,SCLASS,SAGE,SSEX FROM STUDENT;  
P1 STUDENT.SNO%TYPE;  
P2 STUDENT.SNAME%TYPE;  
P3 STUDENT.SDEPT%TYPE;  
P4 STUDENT.SCLASS%TYPE;  
P5 STUDENT.SAGE%TYPE;  
P6 STUDENT.SSEX%TYPE;  
NUM1 NUMBER;  
I NUMBER;  
BEGIN  
STU\_COUNT(NUM1);  
OPEN C;  
FOR I IN 1 .. NUM1 LOOP  
FETCH C INTO P1,P2,P3,P4,P5,P6;  
DBMS\_OUTPUT.PUT\_LINE('学号:'||P1||' 姓名:'||P2||' 专业:'||  
P3||' 班级:'||P4||' 性别:'||P5||' 年龄:'||P6);  
END LOOP;  
CLOSE C;  
END STUDENT\_LIST ;  
/  
EXECUTE STUDENT\_LIST;

例7-3： (存储过程) 创建一个显示学生平均成绩的存储过程。  
CREATE OR REPLACE PROCEDURE SAVG(S\_SNO IN STUDENT.SNO%TYPE)  
AS  
S\_NAME STUDENT.SNAME%TYPE;  
S\_AVG NUMBER(5,2);  
BEGIN  
SELECT SNAME INTO S\_NAME FROM STUDENT WHERE SNO = S\_SNO;  
SELECT AVG(SCORE) INTO S\_AVG FROM SCORE WHERE SNO = S\_SNO;  
DBMS\_OUTPUT.PUT\_LINE('SNAME:'||S\_NAME||' AVG\_SCORE:'||S\_AVG);  
END;  
/  
EXECUTE SAVG('96001');  
例7-4： (存储过程) 创建显示所有学生平均成绩的存储过程。  
CREATE OR REPLACE PROCEDURE SC\_AVG  
AS  
S\_AVG NUMBER(5,2);  
BEGIN  
SELECT AVG(SCORE) INTO S\_AVG FROM SCORE;  
DBMS\_OUTPUT.PUT\_LINE('S\_AVG:'||S\_AVG);  
END;  
/  
SET SERVEROUTPUT ON;  
EXECUTE SC\_AVG;

例7-5： (修改数据库表) 在Student表中增加SAVG(N,6,2) 字段。  
ALTER TABLE STUDENT ADD SAVG NUMBER(6,2);

例7-6： (存储过程) 创建存储过程，计算每个学生的平均成绩保存到学生表SAVG字段中。  
CREATE OR REPLACE PROCEDURE SAVE\_AVG  
AS  
S\_SNO STUDENT.SNO%TYPE;  
NUM NUMBER(2);  
I NUMBER;  
CURSOR C\_ADD IS SELECT SNO FROM STUDENT;  
BEGIN  
STU\_COUNT(NUM);  
OPEN C\_ADD;  
FOR I IN 1..NUM LOOP  
FETCH C\_ADD INTO S\_SNO;  
UPDATE STUDENT SET SAVG =  
( SELECT AVG(SCORE) FROM SCORE  
GROUP BY SNO  
HAVING SNO = S\_SNO )  
WHERE SNO = S\_SNO ;  
END LOOP;  
CLOSE C\_ADD;  
END SAVE\_AVG;  
/  
EXECUTE SAVE\_AVG;

例7-7： (触发器) 当更新学生成绩表SCORE中的学生成绩时，自动计算该学生的平均成绩保存到学生表SAVG字段中。  
CREATE OR REPLACE TRIGGER SC\_UP AFTER UPDATE ON SCORE  
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW  
BEGIN  
IF UPDATING THEN  
SAVE\_AVG;  
END IF;  
END;  
/

例7-8： (触发器) 创建包含插入、删除、修改多种触发事件的触发器DML\_LOG，对SCORE表的操作进行记录。用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。  
先创建事件[记录表][Link 2]LOGS，该表用来对操作进行记录。该表的字段含义解释如下：  
LOG\_ID：操作记录的编号，数值型，它是该表的主键，自动增1，[可由][Link 3]序列自动生成。  
LOG\_TABLE：进行操作的表名，字符型，非空，该表设计成可以由多个触发器共享使用。比如我们可以为Student表创建类似的触发器，同样将操作记录到该表。  
LOG\_DML：操作的动作，即INSERT、DELETE或UPDATE三种之一。  
LOG\_KEY\_ID：操作时表的主键值，数值型。之所以记录表的主键，是因为主键是表的记录的惟一标识，可以识别是对哪一条记录进行了操作。对于Score表，主键是由SNO\_CNO构成。  
LOG\_DATE：操作的日期，日期型，取当前的系统时间。  
LOG\_USER：操作者，字符型，取当时的操作者账户名。比如登录SCOTT账户进行操作，在该字段中，记录账户名为SCOTT。

CREATE TABLE LOGS(  
LOG\_ID NUMBER(10) PRIMARY KEY,  
LOG\_TABLE VARCHAR2(10) NOT NULL,  
LOG\_DML VARCHAR2(10),  
LOG\_KEY\_ID NUMBER(10),  
LOG\_DATE DATE,  
LOG\_USER VARCHAR2(15));

CREATE SEQUENCE LOGS\_ID\_SQU INCREMENT BY 1  
START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;

CREATE OR REPLACE TRIGGER DML\_LOG  
BEFORE DELETE OR INSERT OR UPDATE  
ON SCORE  
FOR EACH ROW -- 行级触发器  
BEGIN  
IF INSERTING THEN  
INSERT INTO LOGS VALUES(LOGS\_ID\_SQU.NEXTVAL,'SCORE','INSERT',:new.SNO,SYSDATE,USER);

ELSIF DELETING THEN  
INSERT INTO LOGS VALUES(LOGS\_ID\_SQU.NEXTVAL, 'SCORE','DELETE',:old.SNO,SYSDATE,USER);

ELSE  
INSERT INTO LOGS VALUES(LOGS\_ID\_SQU.NEXTVAL, 'SCORE','UPDATE',:new.SNO,SYSDATE,USER);  
END IF;  
END;  
/

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！[https://blog.csdn.net/jiangjunshow][https_blog.csdn.net_jiangjunshow]

[Link 1]: https://www.baidu.com/s?wd=%E4%BF%A1%E6%81%AF%E7%B3%BB&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[Link 2]: https://www.baidu.com/s?wd=%E8%AE%B0%E5%BD%95%E8%A1%A8&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[Link 3]: https://www.baidu.com/s?wd=%E5%8F%AF%E7%94%B1&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[https_blog.csdn.net_jiangjunshow]: https://blog.csdn.net/jiangjunshow/article/details/77338485