数据库系统原理-实验一

一、实验目的：

掌握使用 SQL 语言进行数据定义和数据操纵的方法。

二、实验要求：

建立一个数据库 stumanage，建立三个关系表 student，course，sc。向表中插入 数据，然后对数据进行删除、修改等操作，对关系、数据库进行删除操作。

三、实验步骤：

1、 开始→程序→Microsoft SQL Server→SQL Server Management Stdio。

2、 在“连接到服务器”对话框中，选择“Windows 身份验证”，点击“连接”，进 入SQL Server Management Stdio操作界面。采用如下两种方式之一建立数据库。

3、 在“对象资源管理器”中右击“数据库”，在“新建数据库”对话框中输入数 据库名称 stumanage，设置数据库文件初始大小为 5M，限制文件增长 50M， 日志文件初始大小设为 2M，限制文件增长 5M，并更改文件存储路径。

4、 单击“新建查询”按钮，在 SQL 查询窗口中输入 SQL 语句，建立数据库 stumanage。然后单击工具栏上“执行”按钮（红色叹号）。下部的空白区显示 该语句的运行情况，将建立数据库的 SQL 语句写入实验报告。

以下为实验中的操作以及相应的sql 语句：

（1）在 SQL Server 中创建一个名为 `stumanage` 的数据库，以及三个关系表 `student`、`course` 和 `sc`，可以使用以下 SQL 语句：

-- 创建数据库 stumanage

CREATE DATABASE stumanage;

-- 使用 stumanage 数据库

USE stumanage;

-- 创建 student 表

CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(10) NOT NULL,
age INT NOT NULL,
phone VARCHAR(20),
address VARCHAR(100)
);

-- 创建 course 表

CREATE TABLE course (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
credit INT NOT NULL,
teacher VARCHAR(50) NOT NULL
);

-- 创建 sc 表

CREATE TABLE sc (
id INT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
score INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (course_id) REFERENCES course(id)
);

 `student` 表用于存储学生的信息，包括学生编号、姓名、性别、年龄、联系电话和家庭地址。
 `course` 表用于存储课程的信息，包括课程编号、课程名称、学分和课程教师。
 `sc` 表用于存储学生选课的信息，包括选课记录编号、学生编号、课程编号和成绩，同时建立了 `student_id` 和 `course_id` 两个外键约束，确保选课记录与学生、课程信息的一致性。

（2）在 `stumanage` 数据库中，建立表 `student`、`course` 和 `sc`，可以使用以下 SQL 语句：

1. 建立 `student` 表：

CREATE TABLE student (
sno CHAR(8) NOT NULL PRIMARY KEY,
sname VARCHAR(20) NOT NULL,
sex CHAR(2) NOT NULL,
sdept VARCHAR(20) NOT NULL
);

2. 建立 `course` 表：

CREATE TABLE course (
cno CHAR(6) NOT NULL PRIMARY KEY,
cname VARCHAR(20) NOT NULL
);

3. 建立 `sc` 表：

CREATE TABLE sc (
sno CHAR(8) NOT NULL,
cno CHAR(6) NOT NULL,
grade INT NOT NULL,
PRIMARY KEY (sno, cno),
FOREIGN KEY (sno) REFERENCES student(sno),
FOREIGN KEY (cno) REFERENCES course(cno)
);

 `student` 表中使用学号 `sno` 作为主键，包括姓名 `sname`、性别 `sex`、所在系 `sdept` 等字段。
 `course` 表中使用课程号 `cno` 作为主键，包括课程名称 `cname` 等字段。
 `sc` 表中使用 `(sno, cno)` 作为联合主键，同时设定 `sno` 和 `cno` 两个外键约束，确保选课记录的一致性。`grade` 字段存储成绩信息。

（3）在 `student`、`course` 和 `sc` 表中插入数据，可以使用以下 SQL 语句：

1. 向 `student` 表中插入两条数据：

INSERT INTO student (sno, sname, sex, sdept) VALUES
('20050101', '王飞', '男', '计算机系'),
('20050102', '李丽', '女', '信息系');

2. 向 `course` 表中插入两条数据：

INSERT INTO course (cno, cname) VALUES
('01', '数据结构'),
('02', '数据库原理');

3. 向 `sc` 表中插入两条数据：

INSERT INTO sc (sno, cno, grade) VALUES
('20050101', '01', 70),
('20050102', '02', 90);

使用 `INSERT INTO` 语句向表中插入数据，语法格式为 `INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)`。

（4）按课程求学生平均成绩，并将结果存入基本表

1.针对每一门课，对学生成绩求平均并将结果存入基本表。

SQL 语句：

SELECT cno, AVG(grade) AS avg_grade INTO grade_avg
FROM sc
GROUP BY cno;

- 使用 `SELECT INTO` 语句将查询结果存储到一个新表 `grade_avg` 中。
- `SELECT cno, AVG(grade) AS avg_grade` 表示选出课程号和平均成绩两个列的结果集。
- `AVG()` 是 SQL 中的聚合函数，用于计算某列的平均值。
- `GROUP BY cno` 表示按照课程号进行分组，计算每门课程的平均成绩。
- `INTO grade_avg` 表示将查询结果存储到 `grade_avg` 表中。

2. 修改某条记录的 grade 属性值为 85，并删除该记录

将 sc 表中 sno 为 20050102、cno 为 02 的记录的 grade 属性值改为 85，然后删除该条记录。

SQL 语句：

UPDATE sc SET grade = 85 WHERE sno = '20050102' AND cno = '02';
DELETE FROM sc WHERE sno = '20050102' AND cno = '02';

- `UPDATE sc SET grade = 85 WHERE sno = '20050102' AND cno = '02'` 用于将查询结果中 sno 和 cno 满足条件的记录的 grade 值修改为 85。
- `DELETE FROM sc WHERE sno = '20050102' AND cno = '02'` 用于删除查询结果中 sno 和 cno 满足条件的记录。

3. 修改‘数据库原理’课程的所有学生成绩为 0

SQL 语句：

UPDATE sc SET grade = 0 WHERE cno = '02';

- `UPDATE sc SET grade = 0 WHERE cno = '02'` 用于将查询结果中课程号为 02 的所有记录的 grade 值修改为 0。

4. 删除‘李丽’的所有选课情况。

SQL 语句：

DELETE FROM sc WHERE sno = '20050102';

- `DELETE FROM sc WHERE sno = '20050102'` 用于删除查询结果中 sno 为 20050102 的所有记录。

5. 删除数据库中的三个表中的所有数据

SQL 语句：

DELETE FROM sc;
DELETE FROM course;
DELETE FROM student;

- `DELETE FROM table` 用于删除表中所有数据。

6. 删除数据库中的三个表结构

SQL 语句：

DROP TABLE sc;
DROP TABLE course;
DROP TABLE student;

- `DROP TABLE table` 用于删除指定表的结构。

7. 删除数据库

SQL 语句：

DROP DATABASE stumanage;

- `DROP DATABASE database` 用于删除指定数据库。