数据库实验一

在 stumanage 数据库中，如下图建立表 student： 列名 数据类型 允许空 主键 说明 1 sno Char(8) 否 是 学号 2 sname Varchar(20) 是 否 姓名 3 sex Char(2) 是 否 性别 4 sdept Varchar(20) 是 否 所在系 如下图建立表：course 列名 数据类型 允许空 主键 说明 1 cno Char(6) 否 是 课程号 2 cname Varchar(20) 是 否 课程名 如下图建立表 sc：（注：包括两个外键，sno 和 cno 共同组成主键）2 列名 数据类型 允许空 主键 外键 说明 1 sno Char(8) 否 是 students(sno) 学号 2 cno Char(6) 否 是 course(cno) 课程号 3 grade int 否 否 否 成绩 6、将三条建表语句写入实验报告。 7、在 student 表中插入两条数据： （1）20050101，王飞，男，计算机系 （2）20050102，李丽，女，信息系 8、在 course 表中插入两条数据： （1）01，数据结构 （2）02，数据库原理 9、在 sc 表中插入两条数据： （1）20050101，01，70 （2）20050102，02，90 10、将此八条 SQL 语句写入实验报告。 11、对每一门课，求学生的平均成绩，并把结果以基本表的形式存入数据库。将操 作命令和所用 SQL 语句写入实验报告。 12、修改 sc 表中 sno 为 20050102、cno 为 02 的记录的 grade 属性值为 85，然后将 该条记录删除。将此两条 SQL 语句写入实验报告。 13、修改‘数据库原理’课程的所有学生成绩为 0。将此条 SQL 语句写入实验报告。 14、删除‘李丽’的所有选课情况。将此条 SQL 语句写入实验报告。 15、删除数据库中的三个表中的所有数据，将所用 SQL 语句写入实验报告。 16、删除数据库中的三个表结构。将所用 SQL 语句写入实验报告。 17、删除数据库，将所用语句写入实验报告。    

1、建立表 student：

CREATE TABLE student (

    sno CHAR(8) NOT NULL PRIMARY KEY,  -- 学号，主键，不能为空

    sname VARCHAR(20),                 -- 姓名，可以为空

    sex CHAR(2),                       -- 性别，可以为空

    sdept VARCHAR(20)                  -- 所在系，可以为空

);

2、建立表：course

CREATE TABLE course (

    cno CHAR(6) NOT NULL PRIMARY KEY,  -- 课程号，主键，不能为空

    cname VARCHAR(20)                  -- 课程名，可以为空

);

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),   -- 外键，引用 student 表的 sno 列

    FOREIGN KEY (cno) REFERENCES course(cno)     -- 外键，引用 course 表的 cno 列

);

4、在 student 表中插入两条数据：

INSERT INTO student (sno, sname, sex, sdept) VALUES

('20050101', '王飞', '男', '计算机系'),

('20050102', '李丽', '女', '信息系');

5、在 course 表中插入两条数据：

INSERT INTO course (cno, cname) VALUES

('01', '数据结构'),

('02', '数据库原理');

6、在 sc 表中插入两条数据：

INSERT INTO sc (sno, cno, grade) VALUES

('20050101', '01', 70),

('20050102', '02', 90);

11、首先，创建一个表用于存储每门课程的平均成绩：

CREATE TABLE course_avg_grade (

    cno CHAR(6) PRIMARY KEY,

    avg_grade DECIMAL(5, 2)

);

计算每门课程的平均成绩并插入到 course_avg_grade 表中：

INSERT INTO course_avg_grade (cno, avg_grade)

SELECT cno, AVG(grade) AS avg_grade

FROM sc

GROUP BY cno;

查看存储的平均成绩数据：

SELECT * FROM course_avg_grade;

12、-- 修改成绩为 85

UPDATE sc

SET grade = 85

WHERE sno = '20050102' AND cno = '02';

-- 删除记录

DELETE FROM sc

WHERE sno = '20050102' AND cno = '02';

13、UPDATE sc

SET grade = 0

WHERE cno = '02';

14、DELETE FROM sc

WHERE sno = '20050102';

15、DELETE FROM student;

DELETE FROM course;

DELETE FROM sc;

16、DROP TABLE  student;

DROP TABLE  course;

DROP TABLE  sc;

17、DROP DATABASE  stumanage;