数据库系统SSD7 实验2 《数据表查询与更新》(MySQL)

数据库系统SSD7 实验2 《数据表查询与更新》(MySQL) 一、实验目的

1、熟悉和掌握对数据表中数据的查询 *** 作和SQL命令的使用，学会灵活熟练的使用SQL 语句的各种形式，加深理解关系运算的各种 *** 作（尤其是关系的选择，投影，连接和除运算）；

2、熟悉和掌握数据表中数据的插入、修改、删除 *** 作和命令的使用（熟悉使用UPDATE/INSERT/DELETE语句进行表 *** 作）；加深理解表的定义对数据更新的作用。

二、实验内容

（一）在表S，C，SC上完成以下查询：

1． 查询学生的基本信息；

2． 查询“CS”系学生的基本信息；

3． 查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；

4． 找出“CS”系年龄最大的学生，显示其学号、姓名；

5． 找出各系年龄最大的学生，显示其学号、姓名；

6． 统计“CS”系学生的人数；

7． 统计各系学生的人数，结果按升序排列；

8． 按系统计各系学生的平均年龄，结果按降序排列；

9． 查询无先修课的课程的课程名和学分数；

10．统计每位学生选修课程的门数、学分及其平均成绩；

11．统计选修每门课程的学生人数及各门课程的平均成绩；

12．找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列；

13．查询选修了“1”或“2”号课程的学生学号和姓名；

14．查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；

15．查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；

16．查询没有选修课程的学生的基本信息；

17．查询选修了3门以上课程的学生学号；

18．查询选修课程成绩至少有一门在80分以上的学生学号；

19．查询选修课程成绩均在80分以上的学生学号；

（二）在表S、C、SC中完成下列更新：

1． 将数据分别插入表S、C、SC；

2． 将表S、C、SC中的数据保存在磁盘上。

3． 在表S、C、SC上练习数据的插入、修改、删除 *** 作。（比较在表上定义/未定义主码（Primary Key）或外码（Foreign Key）时的情况）

4． 将表S、C、SC中的数据全部删除，再利用磁盘上备份的数据来恢复数据。

5． 如果要在表SC中插入某个学生的选课信息（如：学号为“2007001005”，课程号为“c123”，成绩待定），应如何进行？

6． 求各系学生的平均成绩，并把结果存入数据库；

7． 将“CS”系全体学生的成绩置零；

8． 删除“CS”系全体学生的选课记录；

9． 删除学号为“S1”的相关信息；

10．将学号为“S1”的学生的学号修改为“S001”；

11．把平均成绩大于80分的男同学的学号和平均成绩存入另一个表S——GRADE（SNO，AVG_GRADE）；

12．把选修了课程名为“数据结构”的学生的成绩提高10%；

13．把选修了“C2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉。

三、实验方法与实验步骤

大部分情况根据实验要求写出相应的SQL语句，新建查询并运行、调试即可(也可以直接上MySQL控制台)，小部分情况比如备份与恢复可以使用Navicat提供的备份与恢复功能实现。

四、实验结果

以下为相应的SQL语句。

（一）在表S，C，SC上完成以下查询：

1． 查询学生的基本信息；

SELECT * FROM s

2． 查询“CS”系学生的基本信息；

SELECT * FROM s WHERE sdept='CS'

3． 查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；

SELECT Sno, Sname FROM s WHERE sdept='CS' AND Sage<19 OR Sage>21

4． 找出“CS”系年龄最大的学生，显示其学号、姓名；

SELECT Sno, Sname FROM s WHERE sdept='CS' ORDER BY Sage DESC LIMIT 1

5． 找出各系年龄最大的学生，显示其学号、姓名；

SELECT Sno, Sname FROM (SELECT * FROM s stu ORDER BY Sage DESC ) temp GROUP BY sdept

6． 统计“CS”系学生的人数；

SELECT COUNT(*) FROM s WHERE sdept='CS'

7． 统计各系学生的人数，结果按升序排列；

SELECT sdept, COUNT(*) cou FROM s GROUP BY sdept ORDER BY cou

8． 按系统计各系学生的平均年龄，结果按降序排列；

SELECT sdept, AVG(Sage) average FROM s GROUP BY sdept ORDER BY average DESC

9． 查询无先修课的课程的课程名和学分数；

SELECT Cname, ccredit FROM c WHERE Cpno IS NULL

10．统计每位学生选修课程的门数、学分及其平均成绩；

SELECT 
	sc.Sno, COUNT(sc.Cno) cnum, SUM(c.ccredit) cre_sum, AVG(sc.grade) gra_avg
FROM sc, c
WHERE sc.Cno=c.Cno
GROUP BY sc.Sno

11．统计选修每门课程的学生人数及各门课程的平均成绩；

SELECT c.Cname, COUNT(sc.Sno) snum, AVG(sc.grade) gra_avg
FROM sc, c
WHERE sc.Cno=c.Cno
GROUP BY sc.Cno

12．找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列；

SELECT * FROM
	(	
		SELECT sc.Sno, s.sdept, AVG(sc.grade) gra_avg
		FROM sc, c, s
		WHERE sc.Cno=c.Cno AND sc.Sno=s.Sno
		GROUP BY sc.Sno
		HAVING gra_avg>85
		ORDER BY gra_avg
	) temp
GROUP BY temp.sdept

13．查询选修了“2-1”或“4-2”号课程的学生学号和姓名；

SELECT s.Sno, s.Sname FROM s, sc 
WHERE (s.Sno=sc.Sno) AND (sc.Cno='2-1' OR sc.Cno='4-2')

14．查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；

SELECT s.Sno, s.Sname, sc.grade FROM s, sc, c
WHERE s.Sno=sc.Sno 
	AND sc.Cno=c.Cno 
	AND sc.grade<60 
	AND c.Cname='数据库系统'

15．查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；

SELECT s.Sno, s.Sname, c.Cno, c.Cname, sc.grade 
FROM s, sc, c
WHERE s.Sno=sc.Sno AND sc.Cno=c.Cno

16．查询没有选修课程的学生的基本信息；

SELECT * FROM s WHERE s.Sno NOT IN (SELECT Sno FROM sc)

17．查询选修了3门以上课程的学生学号；

SELECT sc.Sno, COUNT(sc.Cno) cnum FROM sc GROUP BY sc.Sno HAVING cnum>=3

18．查询选修课程成绩至少有一门在80分以上的学生学号；

SELECT sc.Sno FROM sc GROUP BY sc.Sno HAVING MAX(sc.grade>80)

19．查询选修课程成绩均在80分以上的学生学号；

SELECT sc.Sno FROM sc GROUP BY sc.Sno HAVING MIN(sc.grade>80)

（二）在表S、C、SC中完成下列更新：

1． 将数据分别插入表S、C、SC；

INSERT INTO S(Sno, Sname, Ssex, Sage, sdept)
VALUES
	('1234567890', '李华', '男', 19, '英语系'),
	('1234567891', '王五', '男', 21, '土木工程系'),
	('1234567892', '赵秋明', '女', 20, '城乡规划系'),
	('1234567893', '刘畅', '男', 19, '软件工程系');
INSERT INTO C(Cno, Cname, Cpno, ccredit)
VALUES
	('2-1', '数据结构', '线性代数', 2),
	('5-6', '编译原理', '汇编语言', 3),
	('1-3', ' *** 作系统', '离散数学', 5),
	('8-5', '计算机网络原理', '高等数学', 3);
INSERT INTO SC(Sno, Cno, grade)
VALUES
	('1234567890', '2-1', 98),
	('1234567891', '8-5', 95),
	('1234567892', '5-6', 93),
	('1234567893', '1-3', 100);

2． 将表S、C、SC中的数据保存在磁盘上。
通过Navicat内置的备份功能完成数据另存：
点击新建备份，选择需要备份的三个表S、C、SC：

点击开始，完成备份：

结果：

3． 在表S、C、SC上练习数据的插入、修改、删除 *** 作。（比较在表上定义/未定义主码（Primary Key）或外码（Foreign Key）时的情况）

INSERT INTO C(Cno, Cname, Cpno, ccredit,Cperiod)
VALUES
	('T1','测试数据','',1,12);
INSERT INTO S (Sno, Sname, Ssex, Sage, sdept)
VALUES
	(1111111111,'测试','男',11,'TEST');
INSERT INTO SC(Sno, Cno, grade)
VALUES
	(1111111111,'T1',88);

4． 将表S、C、SC中的数据全部删除，再利用磁盘上备份的数据来恢复数据。
先设置外键失效，删除三个表的数据之后再设置外键生效：

SET foreign_key_checks = 0;
TRUNCATE TABLE S;
TRUNCATE TABLE C;
TRUNCATE TABLE SC;
SET foreign_key_checks = 1;

恢复备份的数据：
使用Navicat备份的数据进行恢复，打开备份区，选取上述备份，点击还原备份：

点击开始还原，等到还原完成即可：

5． 如果要在表SC中插入某个学生的选课信息（如：学号为“2007001005”，课程号为“c123”，成绩待定），应如何进行？

INSERT INTO SC(Sno, Cno)
VALUES
	('2007001005', ' c123');

6． 求各系学生的平均成绩，并把结果存入数据库；

SELECT 
sc.Sno, AVG(sc.grade) gra_avg
FROM sc, c
WHERE sc.Cno=c.Cno
GROUP BY sc.Sno

7． 将“CS”系全体学生的成绩置零；

UPDATE sc SET grade=0 
WHERE Sno IN
(SELECT Sno FROM s WHERE sdept='CS')

8． 删除“CS”系全体学生的选课记录；

SET foreign_key_checks = 0;
DELETE sc FROM sc, s WHERE sc.Sno=s.Sno AND s.sdept='CS';
SET foreign_key_checks = 1;

9． 删除学号为“S1”的相关信息；

SET foreign_key_checks = 0;
DELETE s, sc FROM s, sc 
WHERE s.Sno='S1' AND s.Sno=sc.Sno;
SET foreign_key_checks = 1;

10．将学号为“S1”的学生的学号修改为“S001”；

SET foreign_key_checks = 0;
UPDATE s, sc SET s.Sno='S001', sc.Sno='S001'
WHERE s.Sno='1234567890' AND s.Sno=sc.Sno;
SET foreign_key_checks = 1;

11．把平均成绩大于80分的男同学的学号和平均成绩存入另一个表S——GRADE（SNO，AVG_GRADE）；

CREATE TABLE S_GRADE(
	Sno VARCHAR(10),
	avg_grade FLOAT(8),
	PRIMARY KEY(Sno)
);
INSERT INTO S_GRADE(Sno, avg_grade)
SELECT 
	sc.Sno, AVG(sc.grade) 
FROM sc, c
WHERE sc.Cno=c.Cno
GROUP BY sc.Sno;

12．把选修了课程名为“数据结构”的学生的成绩提高10%；

UPDATE sc, c SET sc.grade = sc.grade*1.1
WHERE sc.Cno=c.Cno AND c.Cname='数据结构'

13．把选修了“C2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉。

SET foreign_key_checks = 0;
DELETE sc FROM sc, c, (
	SELECT sc.Cno, AVG(sc.grade) gra_avg
	FROM sc, c
	WHERE sc.Cno=c.Cno
	GROUP BY sc.Cno
) temp_avg
WHERE sc.Cno=c.Cno 
	AND c.Cname='C2 ' 
	AND sc.Cno=temp_avg.Cno 
	AND sc.grade<temp_avg.gra_avg;
SET foreign_key_checks = 1;

五、实验小结

主要是熟悉了表的增删改查语句及其基本语法，还有一些关键字如WHERE、ORDER BY、GROUP BY、SET、SELECT关键字的基本使用，还有TRUNCATE语句的使用。因为前一个实验设置了外键，因此在做一些删除、更新 *** 作的时候需要使用：

SET foreign_key_checks = 0;

语句来暂时设置外键失效来完成目标。

还学习到了以下几点：
SQL具有数据定义、数据 *** 纵和数据控制的功能。
1、SQL数据定义功能：能够定义数据库的三级模式结构，即外模式、全局模式和内模式结构。在SQL中，外模式又叫做视图（View），全局模式简称模式（Schema），内模式由系统根据数据库模式自动实现，一般无需用户过问。
2、SQL数据 *** 纵功能：包括对基本表和视图的数据插入、删除和修改，特别是具有很强的数据查询功能。
3、SQL的数据控制功能：主要是对用户的访问权限加以控制，以保证系统的安全性。