数据库实验二:数据高级查询与更新实验

1、实验目的

(1)熟悉数据库的数据更新操作,包括插入、修改、删除
(2)掌握SQL嵌套查询等各种高级查询的设计方法等
(3)熟练掌握常用SQL语句的基本语法,能够通过SQL对数据库进行操作。

2、实验平台

利用实验一中安装的RDBMS系统及其交互查询工具来操作SQL语言。

3、实验内容及要求

(1)针对TPCH数据库,正确用户查询要求一设计各种嵌套查询;
(2)针对TPCH数据库设计单元组插入、批量数据插入、修改数据和删除数据等SQL语句。
(3)理解和掌握INSERT、UPDATE和DELETE语法结构的各个组成成分,结合嵌套SQL子查询,分别设计几种不同形式的插入、修改和删除数据的语句,并调试成功。

参考实验用例:

根据实验一建立的学生选课数据库,练习对数据库的更新操作和高级查询操作。

学生选课数据库ST,包括三个基本表,其中Student表保存学生基本信息,Course表保存课程信息,SC表保存学生选课信息,其结构如下表:

表1. Student表结构

列名称 用途 类型 长度 约束 备注
Sno 学号 char 9 主键
Sname 姓名 char 20 唯一
Ssex 性别 char 2
Sage 年龄 smallint
Sdept 所在系 char 20

表2. Course表结构

列名称 用途 类型 长度 约束 备注
Cno 课程号 char 4 主键
Cname 课程名 char 40
Cpno 先行课 char 4 外键
Ccredit 学分 smallint

表3. SC表结构

学生选课表SC,由以下属性组成:学号Sno(char型,长度为9),课程号Cno(char型,长度为4),成绩Grade(smallint),其中Sno和Cno构成主码。
列名称 用途 类型 长度 约束 备注
Sno 学号 char 9 外键
Cno 课程号 char 4
Grade 成绩 smallint

列名称 用途 类型 长度 约束 备注
Sno 学号 char 9 外键
Cno 课程号 char 4
Grade 成绩 smallint

一、更新操作

1、 插入数据

(1)将一个新学生元祖(学号:200215128;姓名:陈冬;性别:男;所在系:IS;年龄:18岁)插入到Student表中。
INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES(‘200215128’,‘陈冬’,‘男’,‘IS’,18)
(2)将学生张成民的信息插入到Student表中。
INSERT INTO Student VALUES(‘200215126’,‘张成民’,‘男’,18,‘IS’);/要补上入学日期才能插入成功/
(3)插入一条选课记录:(‘200215128’,‘1’)。
INSERT INTO SC(Sno,Cno) VALUES(‘200215128’,‘1’);

2、 修改数据

(1)将学生200215121的年龄改为22岁。
UPDATE student SET Sage = 22 WHERE Sno=‘200215121’;
(2)将所有学生的年龄增加一岁。
UPDATE Student SET Sage = Sage +1
(3)将计算机科学系全体学生的成绩置零。
UPDATE sc SET Grade= 0
WHERE ‘CS’=(select Sdept from student where student.Sno=sc.Sno);
(4)检查数据是否修改

3、 删除数据

(1)删除学号为200215128的学生记录。

DELETE FROM Student WHERE Sno=‘200215128’

(2)删除所有学生的选课记录。

DELETE FROM SC

(3)删除计算机科学系所有学生的选课记录。

DELETE FROM SC WHERE ‘CS’=(select Sdept from student where student.Sno=SC.Sno );

(4)检查数据是否删除

二、高级查询操作

1、 嵌套查询

(1)查询与“刘晨”在同一个系学习的学生。

SELECT Sno,Sname,Sdept FROM Student

WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname= ‘刘晨’);

(2)查询选修了课程名为“信息系统”的学生学号和姓名。

SELECT Sno,Sname FROM Student

WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM course WHERE Cname= ‘信息系统’));

(3)找出每个学生超过他选修课程平均成绩的课程号。

SELECT Sno, Cno FROM SC x

WHERE Grade >= ( SELECT AVG(Grade) FROM SC y

             WHERE y.Sno=x.Sno);

(4)查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄。

SELECT Sname,Sage FROM Student

WHERE Sage < ANY ( SELECT Sage FROM Student WHERE Sdept= ‘CS’)

   AND  Sdept <> 'CS' ;

(5)查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄。

SELECT Sname,Sage FROM Student

WHERE Sage < ALL ( SELECT Sage FROM Student WHERE Sdept= ‘CS’)

   AND  Sdept <> 'CS' ;

(6)查询选修了1号课程的学生姓名。

SELECT Sname FROM Student

 WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '1');

(7)查询没有选修1号课程的学生姓名。

SELECT Sname FROM Student

 WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '1');

(8)查询选修了全部课程的学生姓名。

SELECT Sname FROM Student

 WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC 	  WHERE Sno=Student.Sno AND Cno= 
 Course.CnoSno));

(9)查询至少选修了学生200215121选修的全部课程的学生号码。

SELECT DISTINCT Sno FROM SC scx

 WHERE NOT EXISTS (SELECT * FROM SC scy WHERE scy.Sno='200215122' AND NOT EXISTS (SELECT * 	FROM SC scz WHERE 
 scz.Sno=scx.Sno AND scz.Cno= scx.Cno));
2、集合查询

(1)查询计算机科学系的学生及年龄不大于19岁的学生。

SELECT * FROM Student WHERE Sdept= ‘CS’

UNION

SELECT * FROM Student WHERE Sage<=19;

(2)查询选修了课程1或课程2的学生。

SELECT Sno FROM SC WHERE Cno= ‘1’

UNION

SELECT Sno FROM SC WHERE cno= ‘2’;

(3)查询计算机科学系的学生与年龄不大于19岁的学生的交集。

SELECT * FROM Student WHERE Sdept=‘CS’

INTERSECT

SELECT * FROM Student WHERE Sage<=19;

(4)查询既选修了课程1又选修了课程2的学生。就是查询选修课程1的学生集合与选修课程2的学生集合的交集。

SELECT Sno FROM SC WHERE Cno= ‘1’

INTERSECT

SELECT Sno FROM SC WHERE cno= ‘2’;

(5)查询计算机科学系的学生与年龄不大于19岁的学生的差集。

SELECT * FROM Student WHERE Sdept=‘CS’

EXCEPT

SELECT * FROM Student WHERE Sage <=19;

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值