数据库系统与应用实验报告

粒粒酥yami

已于 2024-04-30 12:23:26 修改

阅读量808

点赞数 7

文章标签：数据库 oracle

于 2024-04-30 12:16:55 首次发布

本文链接：https://blog.csdn.net/m0_61064631/article/details/138340530

版权

1. 实验一创建数据库

2．实验二数据查询（1）

3. 实验三数据查询（2）

4. 实验四数据查询（3）

数据库实验

设有如下3张表及数据：

学生

学号	姓名	性别	年龄	所在系
1412101	李勇	男	19	计算机系
1412102	刘晨	男	20	计算机系
1412103	王敏	女	20	计算机系
1421101	张立	男	22	信息系
1421102	吴宾	女	21	信息系
1421103	张海	男	20	信息系
1431101	钱小平	女	18	数学系
1431102	王大力	男	19	数学系

课程

课号	课名	学分
c01	计算机思维	3
c02	数据仓库	2
c03	计算机网络	4
c04	数据库基础	6
c05	高等数学	8
c06	数据结构	5

成绩

学号	课号	成绩
1412101	c01	90
1412101	c02	86
1412101	c06	null
1412102	c02	78
1412102	c04	66
1421102	c01	82
1421102	c02	75
1421102	c04	92
1421102	c05	50
1421103	c02	68
1421103	c06
1431101	c01	80
1431101	c05	95
1431102	c05	85

实验一创建数据库

用SQL语句创建数据库：计科210iXXX （i为班号，XXX 为学生姓名）。

（1）初始代码：

在实验一第三个实验内容时，插入学生、课程、成绩信息时会报错1366。

分析错误原因：这是插入汉字报错；

解决方案：可以在sqlyog创建数据库时就设置字符集和校对规则。

（2）修改后：

运行成功，成功创建数据库“计科2103夏妍彦”。

在“计科2103夏妍彦”中，用SQL语句分别创建“学生”、“课程”及“成绩”3张表，要求按以上表格数据及语义定义完整性规则。

由于成绩表中的外码定为学号、课号，即引用学生表的学号，引用课程表的课号，

所以必须先创建学生表课程表，再创建成绩表：

运行成功，成功创建学生表、课程表、成绩表。

3.用任一方法，分别录入“学生”、“课程”及“成绩”3张表中指定的数据,输出各表中的数据。

（教材P.86)

（1）录入学生：

运行成功，成功录入各学生数据。

（2）录入课程：

运行成功，成功录入各课程数据。

（3）录入成绩：

运行失败，报错1136。

分析失败原因：语法错误。

解决方案：正确的语法是insert into 成绩 values(‘1421103’,’c06’,null)或者insert into 成绩(学号,课号) values(‘1421103’,’c06’)。

修改后：

运行成功，成功录入各成绩数据。

输出学生表数据：

运行成功，成功输出学生表数据。

输出课程表数据：

运行成功，成功输出课程表数据。

输出成绩表数据：

运行成功，成功输出成绩表数据。

4.在“学生”表中插入记录：

1421101，于得水，男，23，外语系

（1）实验结果：

运行失败，报错1062。

（2）失败原因分析：主键冲突，学生表里已有一个学号为1421101的学生。

5. 在“成绩”表中插入记录：

（1）1421103，c02,78

实验结果：

运行失败，报错1062。

失败原因分析：主键（主码）冲突。成绩表里已有一个学号为1421103且选课为c02的成绩。

1421103, c07, 90

实验结果：

运行失败，报错1452。

失败原因分析：外键（外码）问题。成绩表中的课号是引用课程表里的课号，而课程表里没有c07这个课号，所以报错。

1421104,c07，88

实验结果：

运行失败，报错1452。

失败原因分析：外键（外码）问题。成绩表中的学号是引用学生表里的学号，而学生表里没有1421104这个学号，所以报错。

6. 在“学生”表中删除记录：

1412101

李勇

男

计算机系

实验结果：

运行失败，报错1451。

失败原因分析：外键（外码）问题。在建表时对学号使用了外键约束也就是FOREIGN KEY，而参照值即学生表中的学号是PRIMARY KEY，所以该位置无法删除为NULL。

实验二数据查询（1）

在查询分析器中，用SQL语句完成如下查询：

统计每门课程的修课人数和最高分。

运行成功，成功统计输出每门课程的修课人数和最高分。

查询修课门数超过2门的学生的平均成绩和选课门数。

运行成功，成功查询输出修课门数超过2门的学生的平均成绩和选课门数。

查询计算机系修了“数据库基础”课程的学生的姓名、性别和成绩。

运行成功，成功查询输出计算机系修了“数据库基础”课程的学生的姓名、性别和成绩。

查询哪些学生合选了一门课程，要求列出所选课程的学生的学号和课程号。

运行成功，成功查询输出合选一门课程的学生的学号和课程号。

查询选修了C01号课程的学生的姓名和所在系。

运行成功，成功查询输出选修了C01号课程的学生的姓名和所在系。

查询‘数据仓库’课程考试成绩前三名的学生的学号、姓名、所在系。

运行成功，成功查询输出‘数据仓库’课程考试成绩前三名的学生的学号、姓名、所在系。

实验中遇到的问题1：先开始的代码是...SELECT TOP 3 学号FROM 成绩...ORDER BY 成绩 DESC)。

分析：在mysql中select top 用法和mysql server有所区别，查询前3条数据时，在mysql中不可使用select top 3 * from表名而应该使用select * from 表名 limit 3（并且order by 要写在where之后，limit之前）。

解决方法：...SELECT 学号FROM 成绩...ORDER BY 成绩 DESC LIMIT 3)。

实验中遇到的问题2：用（1）方法修改后，出现了新问题即代码报错1235，显示This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'。

分析：这是mysql中limit和int不能同时使用的问题。有两种解决方式：第一种是通过使用伪表的方式，进行表连接操作；第二种是将int语句里面的查询再包装一层，绕进去即可。

解决方法：...WHERE学号 IN(SELECT 学生.学号 FROM (SELECT 学号

FROM 成绩

WHERE 课号=(SELECT 课程.课号 FROM 课程 WHERE 课名='数据仓库')

ORDER BY 成绩 DESC LIMIT 3

)AS 学生)。

删除信息系修课成绩小于50分的学生的修课记录。

运行成功，由于并不存在信息系修课成绩小于50的学生，所以0行受影响。

统计‘数据仓库’课程的考试最高分、最低分和平均分。

运行成功，成功统计输出‘数据仓库’课程的考试最高分、最低分和平均分。

9. 查询每个学生的修课总学分，并进行如下处理：如果总学分高于10分，

则显示‘好学生’；如果总学分在6到10分间，则显示‘一般学生’；

如果总学分低于6分，则显示‘加油’。

运行成功，成功查询输出各学生的修课总学分和相应的等级。

10. 查询计算机系男生‘数据仓库’考试成绩小于‘数据仓库’平均分的学生的学号、姓名。

运行成功，由于不存在计算机系男生‘数据仓库’考试成绩小于‘数据仓库’平均分的学生，所以并没有符合条件的数据。

实验三数据查询（2）

在查询分析器中，用SQL语句完成如下查询：

统计每个学生的选课门数，并按选课门数的递增顺序显示学号，选课门数。

运行成功，成功统计输出每个学生的选课门数，并按选课门数的递增顺序显示学号，选课门数。

列出总成绩超过300分的学生，要求列出学号、总成绩。

运行成功，由于总成绩最高只有299分，所以没有符合条件的数据。

3. 查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号，姓名，课程号和考试成绩。

运行成功，成功查询输出学生的选课情况

4. 查询哪些学生的年龄相同，要求列出年龄相同的学生的姓名和年龄。

运行成功，成功查询输出年龄相同的学生的姓名和年龄。

5. 查询数学系成绩80分以上的学生的学号、姓名。

运行成功，成功查询输出数学系成绩80分以上的学生的学号、姓名。

查询计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程名和考试成绩。

运行成功，成功查询输出计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程名和考试成绩。

将计算机系所有选修了计算机网络课程的学生的成绩加10分。

运行成功，由于不存在计算机系选修计算机网络的学生，所以没有符合条件的数据，即0行受影响。

统计‘数据库基础’课程的选课人数。

运行成功，成功统计输出‘数据库基础’课程的选课人数。

统计每个系每个学生的修课门数和平均成绩，如果修课门数大于3并且平均成绩高于90的显示‘优秀’；如果修课门数大于3并且平均成绩在80～90，则显示‘较好’，对于修课门数小于等于3的并且平均成绩高于90的显示‘较好’；如果修课门数小于等于3并且平均成绩在80～90，则显示‘一般’；其他情况显示为‘应努力’。列出系名、学号、原修课门数和平均成绩以及处理后的显示结果。

运行成功，成功统计输出每个系每个学生的修课门数和平均成绩和相应的等级。

10. 查询选修了‘数据仓库’的学生的选课门数以及平均成绩，要求只列出平均成绩大于70且选课门数超过2门的情况。

运行成功，成功查询输出选修了‘数据仓库’并且平均成绩大于70且选课门数超过2门的学生的选课门数以及平均成绩。

实验四数据查询（3）

在查询分析器中，用SQL语句完成如下查询：

1. 统计各系的修课的学生总数和考试的平均成绩。

运行成功，成功统计输出各系的修课的学生总数和考试的平均成绩。

2. 查询选修了c02课程的学生的姓名和所在系。

运行成功，成功查询输出选修c02课程的学生的姓名和所在系。

3. 列出“数据库基础”课程考试成绩前三名的学生的学号，姓名，所在系和考试成绩。

运行成功，成功查询输出“数据库基础”课程考试成绩前三名的学生的学号，姓名，所在系和考试成绩。

4. 查询哪些课程没有人选，要求列出课程号和课程名。

运行成功，成功查询输出没有人选的课程的课号和课名。

5. 查询计算机系学生所选的课程名。

运行成功，成功查询输出计算机系学生所选的课程名。

6. 查询“数据仓库”考试成绩最低的学生的姓名、所在系和“数据仓库”成绩。

运行成功，成功查询输出“数据仓库”考试成绩最低的学生的姓名、所在系和“数据仓库”成绩。

将计算机系所有选修了“计算机网络”课程的学生的成绩加10分。

运行成功，由于没有计算机系且选修了“计算机网络”课程的学生，所以0行受影响。

8. 统计计算机系学生“数据仓库”课程考试的最高分、最低分、平均分。

运行成功，成功统计输出计算机系学生“数据仓库”课程考试的最高分、最低分、平均分。

9. 把'c01'这门课程的成绩用等级来表示.

90-100 优

80-89 良

70-79 中

60-69 及格

60以下不及格

列出学生学号和成绩等级.

运行成功，成功查询输出c01课程的学生学号和成绩相应等级。

10. 查询选课门数超过学生的平均选课门数的学生的姓名。

运行成功，成功查询输出选课门数超过学生的平均选课门数的学生的姓名。

分析：

--------------------------------------------------------

以上的所有代码：

CREATE DATABASE IF NOT EXISTS 计科2103夏妍彦

DEFAULT CHARACTER SET GB2312 DEFAULT COLLATE

gb2312_chinese_ci;

CREATE TABLE 学生

(学号 CHAR(10) PRIMARY KEY,

姓名 CHAR(8),

性别 CHAR(2),

年龄 SMALLINT NULL,

所在系 CHAR(10)

);

CREATE TABLE 课程

(课号 CHAR(8) PRIMARY KEY,

课名 CHAR(16),

学分 NUMERIC(3,1)

);

CREATE TABLE 成绩

(学号 CHAR(10),

课号 CHAR(8),

成绩 SMALLINT NULL,

PRIMARY KEY(学号,课号),

FOREIGN KEY(学号) REFERENCES 学生(学号),

FOREIGN KEY(课号) REFERENCES 课程(课号)

);

INSERT INTO 学生

VALUES('1412101','李勇','男',19,'计算机系'),

('1412102','刘晨','男',20,'计算机系'),

('1412103','王敏','女',20,'计算机系'),

('1421101','张立','男',22,'信息系'),

('1421102','吴宾','女',21,'信息系'),

('1421103','张海','男',20,'信息系'),

('1431101','钱小平','女',18,'数学系'),

('1431102','王大力','男',19,'数学系');

INSERT INTO 课程

VALUES('c01','计算机思维',3),

('c02','数据仓库',2),

('c03','计算机网络',4),

('c04','数据库基础',6),

('c05','高等数学',8),

('c06','数据结构',5);

INSERT INTO 成绩

VALUES('1412101','c01',90),

('1412101','c02',86),

('1412101','c06',NULL),

('1412102','c02',78),

('1412102','c04',66),

('1421102','c01',82),

('1421102','c02',75),

('1421102','c04',92),

('1421102','c05',50),

('1421103','c02',68),

('1421103','c06',NULL),

('1431101','c01',80),

('1431101','c05',95),

('1431102','c05',85);

SELECT * FROM 学生;

SELECT * FROM 课程;

SELECT * FROM 成绩;

INSERT INTO 学生

VALUES('1421101','于得水','男',23,'外语系');

INSERT INTO 成绩

VALUES('1421103','c02',78);

INSERT INTO 成绩

VALUES('1421103','c07',90);

INSERT INTO 成绩

VALUES('1421104','c07',88);

DELETE

FROM

学生

WHERE 学号 = '1412101'

AND 姓名 = '李勇'

AND 性别 = '男'

AND 年龄 = 19

AND 所在系 = '计算机系';

第二次实验

SELECT 课号,COUNT(学号) 修课人数,MAX(成绩) 考试最高分

FROM 成绩

GROUP BY 课号;

SELECT 学号,AVG(成绩) 平均成绩,COUNT(课号) 选课人数

FROM 成绩

GROUP BY 学号

HAVING COUNT(课号)>2;

SELECT 姓名,性别,成绩

FROM 学生,成绩

WHERE 学生.学号=成绩.学号

AND 所在系='计算机系'

AND 课号=(SELECT 课号

FROM 课程

WHERE 课名='数据库基础');

SELECT 学号,课号

FROM 成绩

WHERE 课号 IN (SELECT 课号

FROM 成绩

GROUP BY 课号

HAVING COUNT(学号)>1);

SELECT 姓名,所在系

FROM 学生

WHERE 学号 IN (SELECT 学号

FROM 成绩

WHERE 课号='c01');

（1.）

SELECT 学号,姓名,所在系

FROM 学生

WHERE 学号 IN (SELECT TOP 3 学号

FROM 成绩

WHERE 课号=(SELECT 课号 FROM 课程 WHERE 课名='数据仓库')

ORDER BY 成绩 DESC);

（2.）

SELECT 学号,姓名,所在系

FROM 学生

WHERE 学号 IN (SELECT 学号

FROM 成绩

WHERE 课号=(SELECT 课号 FROM 课程 WHERE 课名='数据仓库')

ORDER BY 成绩 DESC LIMIT 3);

（3.）

SELECT 学号,姓名,所在系

FROM 学生

WHERE 学号 IN(SELECT 学生.学号 FROM (SELECT 学号

FROM 成绩

WHERE 课号=(SELECT 课程.课号 FROM 课程 WHERE 课名='数据仓库')

ORDER BY 成绩 DESC LIMIT 3

)AS 学生);

DELETE FROM 成绩

WHERE 成绩<50

AND 学号 IN

(SELECT 学号 FROM 学生 WHERE 所在系='信息系');

SELECT MAX(成绩) 最高分,MIN(成绩) 最低分,AVG(成绩) 平均分

FROM 成绩

WHERE 课号=(SELECT 课号 FROM 课程 WHERE 课名='数据仓库');

SELECT 学生.学号,姓名,SUM(学分) 总学分,

CASE

WHEN SUM(学分)>10 THEN '优秀'

WHEN SUM(学分) BETWEEN 6 AND 10 THEN '良好'

WHEN SUM(学分)<6 THEN '加油'

END 等级

FROM 学生,成绩,课程

WHERE 学生.学号=成绩.学号

AND 成绩.课号=课程.课号

AND 成绩>60

GROUP BY 学生.学号,姓名

10.

SELECT 学生.学号,姓名
FROM 学生,成绩,课程
WHERE 学生.学号=成绩.学号
   AND 成绩.课号=课程.课号
   AND 所在系='计算机系'
   AND 性别='男'
   AND 课名='数据仓库'
   AND 成绩<(SELECT AVG(成绩)
               FROM 成绩,课程
               WHERE 成绩.课号=课程.课号
                   AND 课名='数据仓库');

第三次实验

SELECT 学号,COUNT(*) 选课门数

FROM 成绩

GROUP BY 学号

ORDER BY COUNT(*);

SELECT 学号,SUM(成绩) 总成绩

FROM 成绩

GROUP BY 学号

HAVING SUM(成绩)>300;

SELECT 学生.学号,姓名,课号,成绩考试成绩

FROM 学生 LEFT JOIN 成绩 ON (学生.学号=成绩.学号);

SELECT 姓名,年龄

FROM 学生

WHERE 年龄 IN(SELECT 年龄

FROM 学生

GROUP BY 年龄

HAVING COUNT(*)>1)

ORDER BY 年龄 ASC;

SELECT 学生.学号,学生.姓名

FROM 学生,成绩

WHERE 学生.学号=成绩.学号 AND 成绩>80 AND 学生.所在系="数学系";

SELECT 姓名,课名,成绩

FROM 学生,课程,成绩

WHERE 学生.学号=成绩.学号

AND 成绩.课号=课程.课号

AND 学生.所在系="计算机系"

AND 成绩>(SELECT AVG(成绩) FROM 成绩,学生

WHERE 成绩.学号=学生.学号 AND 所在系="计算机系");

UPDATE 成绩

SET 成绩=成绩+10

WHERE 学号 IN(

SELECT 学号 FROM 学生

WHERE 所在系="计算机系")

AND 课号 IN(

SELECT 课号 FROM 课程

WHERE 课名="计算机网络");

SELECT COUNT(学号) 数据库基础课程的选课人数

FROM 成绩

GROUP BY 课号

HAVING 课号 IN(SELECT 课程.课号 FROM 课程

WHERE 课名="数据库基础");

SELECT 学生.所在系,学生.学号,COUNT(成绩.课号) 原修课门数,AVG(成绩) 平均成绩,

CASE

WHEN COUNT(成绩.课号)>3 AND AVG(成绩)>90 THEN '优秀'

WHEN COUNT(成绩.课号)>3 AND AVG(成绩) BETWEEN 80 AND 90 THEN '较好'

WHEN COUNT(成绩.课号)<=3 AND AVG(成绩)>90 THEN '较好'

WHEN COUNT(成绩.课号)<=3 AND AVG(成绩)BETWEEN 80 AND 90 THEN '一般'

ELSE '应努力'

END 等级

FROM 学生,课程,成绩

WHERE 成绩.学号=学生.学号

AND 成绩.课号=课程.课号

GROUP BY 学生.学号,学生.姓名;

10.

SELECT 学号,COUNT(成绩.课号) 选课门数,AVG(成绩) 平均成绩

FROM 成绩

WHERE 学号 IN(SELECT 学号 FROM 成绩 WHERE 课号 IN(

SELECT 课号 FROM 课程

WHERE 课名 = "数据仓库"))

GROUP BY 学号

HAVING AVG(成绩.成绩)>70

AND COUNT(成绩.课号)>2;

第四次实验

SELECT 所在系, COUNT(DISTINCT 成绩.学号) 学生总数, AVG(成绩) 平均成绩

FROM 学生 LEFT JOIN 成绩 ON(学生.学号 = 成绩.学号)

GROUP BY 所在系;

SELECT 姓名,所在系

FROM 学生,成绩

WHERE 学生.学号=成绩.学号

AND 成绩.课号="c02";

（1）错：

SELECT 学生.学号,姓名,所在系,成绩

FROM 学生,成绩

WHERE 学生.学号 IN (SELECT TOP 3 学号

FROM 成绩

WHERE 课号=(SELECT 课号 FROM 课程 WHERE 课名='数据库基础')

ORDER BY 成绩 DESC);

错：

SELECT 学生.学号,姓名,所在系,成绩

FROM 学生,成绩

WHERE 学生.学号 IN(SELECT 学生.学号 FROM (SELECT 学号

FROM 成绩

WHERE 课号=(SELECT 课程.课号 FROM 课程 WHERE 课名='数据库基础')

ORDER BY 成绩 DESC LIMIT 3

)AS 学生);

（3）

SELECT 学生.学号,姓名,所在系,成绩

FROM 学生,成绩

WHERE 学生.学号 IN(SELECT 学生.学号 FROM (SELECT 学号

FROM 成绩

WHERE 课号=(SELECT 课程.课号 FROM 课程 WHERE 课名='数据库基础')

ORDER BY 成绩 DESC LIMIT 3

)AS 学生)

AND 学生.学号=成绩.学号

AND 课号=(SELECT 课程.课号 FROM 课程 WHERE 课名='数据库基础');

（4）

SELECT 学生.学号,姓名,所在系,成绩

FROM 学生,成绩

WHERE 学生.学号 IN(SELECT 学生.学号 FROM (SELECT 学号

FROM 成绩

WHERE 课号=(SELECT 课程.课号 FROM 课程 WHERE 课名='数据库基础')

ORDER BY 成绩 DESC LIMIT 3

)AS 学生)

AND 学生.学号=成绩.学号

AND 课号=(SELECT 课程.课号 FROM 课程 WHERE 课名='数据库基础')

ORDER BY 成绩 DESC LIMIT 3;

SELECT 课号,课名

FROM 课程

WHERE NOT EXISTS

( SELECT * FROM 成绩

WHERE 成绩.课号=课程.课号)

SELECT 课名

FROM 课程

WHERE 课号 IN(

SELECT 课号 FROM 成绩

WHERE 学号 IN(

SELECT 学号 FROM 学生 WHERE 所在系 = '计算机系'))

SELECT 姓名,所在系,成绩

FROM 学生 LEFT JOIN 成绩 ON(学生.学号 = 成绩.学号)

LEFT JOIN 课程 ON(课程.课号 = 成绩.课号)

WHERE 成绩 IN (SELECT MIN(成绩) FROM 成绩

WHERE 课号 IN (

SELECT 课号 FROM 课程 WHERE 课名='数据仓库' )

)AND 课名='数据仓库';

UPDATE 成绩 SET 成绩=成绩+10

WHERE 学号 IN (

SELECT 学号 FROM 学生

WHERE 所在系='计算机系')

AND 课号 IN (

SELECT 课号 FROM 课程

WHERE 课名='计算机网络')

SELECT MAX(成绩) 最高分, MIN(成绩) 最低分,AVG(成绩) 平均分

FROM 成绩

WHERE 课号 IN(SELECT 课号 FROM 课程

WHERE 课名="数据仓库")

AND 学号 IN(SELECT 学号 FROM 学生

WHERE 所在系="计算机系");

SELECT 学号,

CASE

WHEN 成绩 BETWEEN 90 AND 100 THEN '优'

WHEN 成绩 BETWEEN 80 AND 89 THEN '良'

WHEN 成绩 BETWEEN 70 AND 79 THEN '中'

WHEN 成绩 BETWEEN 60 AND 69 THEN '及格'

ELSE '不及格'

END 等级

FROM 成绩

WHERE 课号="c01";

10.

SELECT *

FROM (SELECT 学生.姓名,COUNT(成绩.课号)AS 选课人数

FROM 学生,成绩

WHERE 成绩.学号=学生.学号

GROUP BY 学生.学号)table1,

(SELECT AVG( table3.选课人数) AS 平均选课人数

FROM ((SELECT COUNT(成绩.课号) AS 选课人数

FROM 成绩

GROUP BY 成绩.学号

)table3))table2

WHERE table1.选课人数>table2.平均选课人数;

粒粒酥yami

关注

7
点赞
踩
30

收藏

觉得还不错? 一键收藏
1
评论
数据库系统与应用实验报告

分析：在mysql中select top 用法和mysql server有所区别，查询前3条数据时，在mysql中不可使用select top 3 * from表名而应该使用select * from 表名 limit 3（并且order by 要写在where之后，limit之前）。运行成功，成功查询输出计算机系学生考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程名和考试成绩。运行成功，成功查询输出“数据库基础”课程考试成绩前三名的学生的学号，姓名，所在系和考试成绩。
复制链接

扫一扫