实验一 数据库和表的建立、数据操作（1）

标签：Code 数据库 SQLCopy 和表 course 实验 stumanage sc cno

1. 建立数据库 stumanage：

SQLCopy Code

CREATE DATABASE stumanage 
ON (NAME = 'stumanage_data', FILENAME = 'D:\stumanage\stumanage_data.mdf', SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = 'stumanage_log', FILENAME = 'D:\stumanage\stumanage_log.ldf', SIZE = 2MB, MAXSIZE = 5MB, FILEGROWTH = 5MB)

2. 创建表 student：

SQLCopy Code

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

3. 创建表 course：

SQLCopy Code

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

4. 创建表 sc：

SQLCopy Code

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)
)

5. 插入数据：

SQLCopy Code

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

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

INSERT INTO sc VALUES ('20050101', '01', 70)
INSERT INTO sc VALUES ('20050102', '02', 90)

6. 对每一门课求平均成绩并存入基本表：

SQLCopy Code

-- 计算平均成绩
SELECT cno, AVG(grade) AS avg_grade
INTO course_average
FROM sc
GROUP BY cno

-- 将结果存入基本表
ALTER TABLE course ADD avg_grade DECIMAL(5, 2)
UPDATE course SET avg_grade = course_average.avg_grade FROM course_average WHERE course.cno = course_average.cno

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

SQLCopy Code

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

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

SQLCopy Code

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

9. 删除‘李丽’的所有选课情况：

SQLCopy Code

DELETE FROM sc WHERE sno = '20050102'

10. 删除数据库中的所有数据和表：

SQLCopy Code

DROP TABLE sc
DROP TABLE course
DROP TABLE student

11. 删除数据库 stumanage：

SQLCopy Code

DROP DATABASE stumanage

标签：Code,数据库,SQLCopy,和表,course,实验,stumanage,sc,cno
From： https://www.cnblogs.com/yunbianshangdadun/p/17422777.html