编辑代码

-- 1. 创建数据库
CREATE DATABASE StudentGradeDB
ON (
    NAME = StudentGradeDB_data,
    FILENAME = 'C:\Data\StudentGradeDB.mdf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 1MB
)
LOG ON (
    NAME = StudentGradeDB_log,
    FILENAME = 'C:\Data\StudentGradeDB.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 10%
)
COLLATE Chinese_PRC_CI_AS;

-- 2. 创建表结构
USE StudentGradeDB;

-- 学生表
CREATE TABLE Student (
    StudentID VARCHAR(10) PRIMARY KEY,
    StudentName VARCHAR(20) NOT NULL,
    Gender VARCHAR(2) NOT NULL,
    Class VARCHAR(20) NOT NULL,
    EnrollmentYear DATE NOT NULL
);

-- 课程表
CREATE TABLE Course (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL,
    Credit INT NOT NULL,
    Teacher VARCHAR(20) NOT NULL
);

-- 成绩表
CREATE TABLE Grade (
    GradeID INT IDENTITY(1,1) PRIMARY KEY,
    StudentID VARCHAR(10) NOT NULL,
    CourseID VARCHAR(10) NOT NULL,
    Score INT NOT NULL CHECK(Score BETWEEN 0 AND 100),
    ExamDate DATE NOT NULL,
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

-- 3. 插入测试数据
INSERT INTO Student (StudentID, StudentName, Gender, Class, EnrollmentYear)
VALUES 
('S001', '张三', '男', '计算机2023级1班', '2023-09-01'),
('S002', '李四', '女', '计算机2023级1班', '2023-09-01'),
('S003', '王五', '男', '计算机2023级2班', '2023-09-01');

INSERT INTO Course (CourseID, CourseName, Credit, Teacher)
VALUES 
('C001', '数据库原理', 4, '赵老师'),
('C002', '数据结构', 4, '钱老师'),
('C003', '操作系统', 3, '孙老师');

INSERT INTO Grade (StudentID, CourseID, Score, ExamDate)
VALUES 
('S001', 'C001', 85, '2024-01-10'),
('S001', 'C002', 90, '2024-01-15'),
('S002', 'C001', 78, '2024-01-10'),
('S002', 'C002', 82, '2024-01-15'),
('S003', 'C001', 92, '2024-01-10'),
('S003', 'C003', 88, '2024-01-20');

-- 4. 测试SQL（部分示例）
-- 更新学生姓名
UPDATE Student SET StudentName = '张大三' WHERE StudentID = 'S001';

-- 删除学生记录
DELETE FROM Student WHERE StudentID = 'S003';

-- 查询所有学生
SELECT * FROM Student;

-- 条件查询（成绩≥85）
SELECT * FROM Grade WHERE Score >= 85;

-- 模糊查询（课程名含"数据"）
SELECT * FROM Course WHERE CourseName LIKE '%数据%';

-- 多表查询（学生-课程-成绩）
SELECT s.StudentName, c.CourseName, g.Score
FROM Student s
JOIN Grade g ON s.StudentID = g.StudentID
JOIN Course c ON g.CourseID = c.CourseID;

-- 创建视图
CREATE VIEW StudentGradeView AS
SELECT s.StudentName, c.CourseName, g.Score
FROM Student s
JOIN Grade g ON s.StudentID = g.StudentID
JOIN Course c ON g.CourseID = c.CourseID;

-- 创建索引
CREATE INDEX idx_StudentName ON Student(StudentName);
CREATE INDEX idx_CourseScore ON Grade(CourseID, Score);