编辑代码

-- 创建数据库
CREATE DATABASE IF NOT EXISTS music_rating_system;
USE music_rating_system;

-- 创建歌曲表
CREATE TABLE song (
    SID VARCHAR(11) NOT NULL,
    title VARCHAR(60) NOT NULL,
    type VARCHAR(16) NOT NULL,
    year INT NOT NULL,
    singer VARCHAR(20) NOT NULL,
    composer VARCHAR(20) NOT NULL,
    lyreist VARCHAR(20) NOT NULL,
    PRIMARY KEY (SID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建用户表
CREATE TABLE User (
    UID VARCHAR(11) NOT NULL,
    name VARCHAR(11) NOT NULL,
    sex CHAR(1),
    phone CHAR(11),
    birthday DATE,
    PRIMARY KEY (UID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建评论表
CREATE TABLE Comment (
    SID VARCHAR(11) NOT NULL,
    UID VARCHAR(11) NOT NULL,
    score INT NOT NULL CHECK (score BETWEEN 1 AND 10),
    PRIMARY KEY (SID, UID),
    FOREIGN KEY (SID) REFERENCES song(SID) ON DELETE CASCADE,
    FOREIGN KEY (UID) REFERENCES User(UID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建索引优化查询性能
CREATE INDEX idx_song_title ON song(title);
CREATE INDEX idx_song_singer ON song(singer);
CREATE INDEX idx_song_type ON song(type);
CREATE INDEX idx_comment_score ON Comment(score);