编辑代码

-- 音乐图书馆数据库设计
-- 适用于小型音乐收藏管理系统

-- 创建数据库
CREATE DATABASE MusicLibraryDB;
GO
USE MusicLibraryDB;

-- 创建流派表
CREATE TABLE Genre (
    GenreID INT PRIMARY KEY IDENTITY(1,1),
    GenreName VARCHAR(50) NOT NULL,
    Description VARCHAR(200)
);

-- 创建艺术家表
CREATE TABLE Artist (
    ArtistID INT PRIMARY KEY IDENTITY(1,1),
    ArtistName VARCHAR(100) NOT NULL,
    Country VARCHAR(50),
    Description VARCHAR(500),
    Established DATE
);

-- 创建专辑表
CREATE TABLE Album (
    AlbumID INT PRIMARY KEY IDENTITY(1,1),
    AlbumTitle VARCHAR(100) NOT NULL,
    ArtistID INT NOT NULL,
    ReleaseDate DATE,
    GenreID INT,
    CoverImageURL VARCHAR(200),
    Rating DECIMAL(2,1), -- 优化为0.0-5.0评分
    TrackCount INT, -- 专辑歌曲数量
    RecordLabel VARCHAR(100), -- 唱片公司
    FOREIGN KEY (ArtistID) REFERENCES Artist(ArtistID),
    FOREIGN KEY (GenreID) REFERENCES Genre(GenreID)
);

-- 创建歌曲表
CREATE TABLE Song (
    SongID INT PRIMARY KEY IDENTITY(1,1),
    SongTitle VARCHAR(100) NOT NULL,
    AlbumID INT,
    Duration INT NOT NULL, -- 以秒为单位
    TrackNumber INT,
    Lyrics TEXT,
    FilePath VARCHAR(200),
    FileSize BIGINT, -- 文件大小（字节）
    GenreID INT,
    Rating DECIMAL(2,1), -- 优化为0.0-5.0评分
    PlayCount INT DEFAULT 0, -- 播放次数
    IsFavorite BIT DEFAULT 0, -- 是否收藏
    FOREIGN KEY (AlbumID) REFERENCES Album(AlbumID),
    FOREIGN KEY (GenreID) REFERENCES Genre(GenreID)
);

-- 创建歌曲-艺术家关联表（多对多关系）
CREATE TABLE SongArtist (
    SongID INT NOT NULL,
    ArtistID INT NOT NULL,
    PRIMARY KEY (SongID, ArtistID),
    FOREIGN KEY (SongID) REFERENCES Song(SongID),
    FOREIGN KEY (ArtistID) REFERENCES Artist(ArtistID)
);

-- 创建用户表（修复保留字冲突）
CREATE TABLE [User] ( -- 使用方括号避免与关键字冲突
    UserID INT PRIMARY KEY IDENTITY(1,1),
    Username VARCHAR(50) UNIQUE NOT NULL,
    PasswordHash VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    RegistrationDate DATE NOT NULL,
    LastLoginDate DATE,
    Role VARCHAR(20) CHECK (Role IN ('普通用户', '管理员')) DEFAULT '普通用户',
    AvatarURL VARCHAR(200), -- 用户头像地址
    Theme VARCHAR(20) DEFAULT 'light' -- 界面主题
);

-- 创建播放列表表
CREATE TABLE Playlist (
    PlaylistID INT PRIMARY KEY IDENTITY(1,1),
    PlaylistName VARCHAR(100) NOT NULL,
    UserID INT NOT NULL,
    CreationDate DATE NOT NULL,
    Description VARCHAR(200),
    IsPublic BIT DEFAULT 0, -- 是否公开（0=私有，1=公开）
    LastUpdateDate DATE, -- 最后更新时间
    FOREIGN KEY (UserID) REFERENCES [User](UserID)
);

-- 创建播放列表-歌曲关联表（多对多关系）
CREATE TABLE PlaylistSong (
    PlaylistID INT NOT NULL,
    SongID INT NOT NULL,
    AddedDate DATE DEFAULT GETDATE(), -- 添加到播放列表的时间
    PlayOrder INT, -- 播放顺序
    PRIMARY KEY (PlaylistID, SongID),
    FOREIGN KEY (PlaylistID) REFERENCES Playlist(PlaylistID),
    FOREIGN KEY (SongID) REFERENCES Song(SongID)
);