数据库作业记录

xampp安装与启动

安装

下载：https://www.apachefriends.org/

安装教程：【小白必看的php入门教程，第二节课，软件xampp的安装与使用】https://www.bilibili.com/video/BV1e7411u7qY?vd_source=8c8fc2009f4a41d7a064a07f284a69af

注意

• 安装路径直接设置为D盘
• 不要安装在program file下

启动

1. 启动软件xampp\xampp-control.exe
2. 点击对应功能的start启用服务
3. 点explorer进入软件文件夹
4. xampp\htdocs文件夹内写php文件

使用

软件使用教程参考https://blog.csdn.net/qq_36595013/article/details/80373597

• 开启Mysql连接
• 点phpmyadmin进入
• utf-8_general_ci

数据库设计

1) 数据库设计

设计一组表，用于存储音乐活动数据的关系数据库。这些表应该表示有关音乐家、乐队、活动、门票销售、商品、版税和其他相关实体的信息。

解释每个表包含的数据

2) 实体-关系（ER）图

解释表之间的关系

在你的报告中提供一个ER图，展示你设计的表之间的关系。包括以下细节：

• 每个表的表名在每个表的顶部。
• 每个表中的字段。
• 用“PK”标记主键字段。
• 用“FK”标记外键字段。
• 在连接线的两端标记每个关系的基数。
• 用虚线或圆圈符号表示可选性。

3) 数据库设计的理由（字数限制200字）

为什么在表中放那一项，特别是关系表

在你的报告中写一个对你的设计的理由，讨论在讲座中讨论的考虑。

ER图设计

参考教程：https://blog.csdn.net/wct040923/article/details/130656641

实体关系表设计（MySQL中的SQL表创建）

• 同一个成员在不同乐队中使用的乐器、税率都可能不同，因此写在关系表

• event存在venue_id和event_date都相同的情况，生成 EventPerformances表时需要注意

列名

Musician ID, Musician Name, Birthday, Instrument, Experience, Royalty Rate,
Band Name, Genre, Formation Year, Manager Phone, Manager Email,
Venue, Event Date, Theme, Audience Size, Merchandise Sales, Ticket Revenue, Headliner

注意

具体设计的文件在桌面，是依据聊天记录拍的图片

实体表

-- 1. Musicians (音乐家表)
CREATE TABLE Musicians (
    musician_id INT PRIMARY KEY,-- int整数型
    musician_name VARCHAR(100) NOT NULL,-- varchar(字符串) -- not null非空
    birthday DATE NOT NULL,-- date日期
    experience VARCHAR(20) NOT NULL
);

-- 2. Bands (乐队表)
CREATE TABLE Bands (
    band_id INT PRIMARY KEY AUTO_INCREMENT,-- AUTO_INCREMENT自动递增
    band_name VARCHAR(100) NOT NULL,
    genre VARCHAR(50) NOT NULL,
    formation_year INT NOT NULL,
    manager_phone VARCHAR(20),
    manager_email VARCHAR(100)
);

-- 3. Venues (场馆表)
CREATE TABLE Venues (
    venue_id INT PRIMARY KEY AUTO_INCREMENT,
    venue_name VARCHAR(100) NOT NULL UNIQUE
);

-- 4. Events (活动表)
CREATE TABLE Events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    venue_id INT NOT NULL,
    event_date DATE NOT NULL,
    theme VARCHAR(100),
    audience_size INT,
    merchandise_sales DECIMAL(10,2),-- decimal2位保留小数
    ticket_revenue DECIMAL(10,2),
    FOREIGN KEY (venue_id) REFERENCES Venues(venue_id)-- 外键
);

关系表

-- 1. BandMembers (乐队成员关系表)
CREATE TABLE BandMembers (
    band_id INT,
    musician_id INT,
    instrument VARCHAR(50) NOT NULL,
    royalty_rate DECIMAL(5,2) NOT NULL,
    PRIMARY KEY (band_id, musician_id, instrument),-- 主键
    FOREIGN KEY (band_id) REFERENCES Bands(band_id),-- 外键，其他表中的主键
    FOREIGN KEY (musician_id) REFERENCES Musicians(musician_id)
);

-- 2. EventPerformances (活动演出关系表)
CREATE TABLE EventPerformances (
    event_id INT,
    band_id INT,
    headliner NOT NULL,
    PRIMARY KEY (event_id, band_id),
    FOREIGN KEY (event_id) REFERENCES Events(event_id),
    FOREIGN KEY (band_id) REFERENCES Bands(band_id)
);

数据导入与查询

导入数据(从CSV文件导入数据)

• 格式选择：CSV
• 字符集：选择 utf-8
• 列分隔符：选择逗号（,）
• 勾选”第一行为列名”（如果你的CSV第一行是标题）

列名解释

1. Musician ID: 音乐家的唯一标识符，用于区分不同的音乐家。

2. Musician Name: 音乐家的姓名，通常包括名字和姓氏。

3. Birthday: 音乐家的出生日期，用于计算年龄或经验。

4. Instrument: 音乐家演奏的乐器，例如萨克斯、鼓、曼陀林等。

5. Experience: 音乐家的经验水平，可能是“业余”或“专业”。

6. Royalty Rate: 音乐家在乐队中获得的版税率，表示为小数。

7. Band Name: 乐队的名称，音乐家所属的乐队。

8. Genre: 乐队的音乐风格或类型，例如爵士、摇滚等。

9. Formation Year: 乐队成立的年份。

10. Manager Phone: 乐队经理的联系电话。

11. Manager Email: 乐队经理的电子邮件地址。

12. Venue: 活动举办的场馆名称。

13. Event Date: 活动的日期。

14. Theme: 活动的主题，例如“爵士之夜”。

15. Audience Size: 活动的观众人数。

16. Merchandise Sales: 活动期间商品销售的总收入。

17. Ticket Revenue: 活动门票销售的总收入。

18. Headliner: 指示乐队是否是活动的主乐队，通常为布尔值（True/False）。

数据导入到表（数据转移SQL）

-- 1. 导入Musicians表
INSERT INTO Musicians (musician_id, musician_name, birthday, experience)
SELECT DISTINCT 
    `Musician ID`,
    `Musician Name`,
    `Birthday`,
    `Experience`
FROM rawdata;

-- 2. 导入Bands表
INSERT INTO Bands (band_name, genre, formation_year, manager_phone, manager_email)
SELECT DISTINCT 
    `Band Name`,
    `Genre`,
    `Formation Year`,
    `Manager Phone`,
    `Manager Email`
FROM rawdata;

-- 3. 导入Venues表
INSERT INTO Venues (venue_name)
SELECT DISTINCT `Venue`
FROM rawdata;

-- 4. 导入Events表
INSERT INTO Events (venue_id, event_date, theme, audience_size, merchandise_sales, ticket_revenue)
SELECT DISTINCT 
    v.venue_id,
    r.`Event Date`,
    r.`Theme`,
    r.`Audience Size`,
    r.`Merchandise Sales`,
    r.`Ticket Revenue`
FROM rawdata r
JOIN Venues v ON v.venue_name = r.`Venue`;

-- 5. 导入BandMembers表
INSERT INTO BandMembers (band_id, musician_id, instrument, royalty_rate)
SELECT DISTINCT 
    b.band_id,
    r.`Musician ID`,
    r.`Instrument`,
    r.`Royalty Rate`
FROM rawdata r
JOIN Bands b ON b.band_name = r.`Band Name`;

-- 6. 导入EventPerformances表
-- 使用GROUP BY确保每个活动-乐队组合只插入一次
INSERT INTO EventPerformances (event_id, band_id, headliner)
SELECT 
    e.event_id,
    b.band_id,
    r.`Headliner`
FROM rawdata r
JOIN Venues v ON v.venue_name = r.`Venue`
JOIN Events e ON e.event_date = r.`Event Date` 
    AND e.venue_id = v.venue_id
    AND e.theme = r.`Theme`
JOIN Bands b ON b.band_name = r.`Band Name`
GROUP BY e.event_id, b.band_id;  -- 对活动和乐队进行分组

注意

导入EventPerformances表需要注意，利用了group by分组去除了结果中（只有event_id、band_id、headline）的重复的组合（也就是每个乐队的每个人的数据都占了一行，一个乐队有4个人就是重复了4次），不然会报错

查询语句（SQL查询和结果）

a. 列出每位在1997年1月1日至2012年12月31日期间出生的音乐家的姓名和出生日期。

SELECT musician_name, birthday
FROM Musicians
WHERE birthday BETWEEN '1997-01-01' AND '2012-12-31';

b. 列出每个乐队的总演出次数。包括乐队名称。

SELECT 
    b.band_name,
    COUNT(ep.event_id) as performance_count -- as重命名
FROM Bands b
-- join：连接
LEFT JOIN EventPerformances ep ON b.band_id = ep.band_id
-- 分组，去除重复的数据
GROUP BY b.band_id, b.band_name
-- order by对查找的数据排序
ORDER BY performance_count DESC;

c. 列出每个乐队的门票销售总收入和商品销售总收入。包括乐队名称、风格和成立年份。

SELECT 
    b.band_name,
    b.genre,
    b.formation_year,
    SUM(CASE 
        WHEN ep.headliner = TRUE THEN e.ticket_revenue * 0.8 * 0.6
        ELSE (e.ticket_revenue * 0.8 * 0.4) / 
            (SELECT COUNT(*) FROM EventPerformances ep2 
             WHERE ep2.event_id = e.event_id AND ep2.headliner = FALSE)
    END) as total_ticket_revenue,
    SUM(CASE 
        WHEN ep.headliner = TRUE THEN e.merchandise_sales * 0.6
        ELSE (e.merchandise_sales * 0.4) / 
            (SELECT COUNT(*) FROM EventPerformances ep2 
             WHERE ep2.event_id = e.event_id AND ep2.headliner = FALSE)
    END) as total_merchandise_revenue
FROM Bands b
LEFT JOIN EventPerformances ep ON b.band_id = ep.band_id
LEFT JOIN Events e ON ep.event_id = e.event_id
GROUP BY b.band_id, b.band_name, b.genre, b.formation_year
ORDER BY total_ticket_revenue DESC;

d. 列出所有在2023年3月期间，除了“The Waves”或“Groove Squad”之外的所有乐队都演出的场馆名称。

SELECT DISTINCT v.venue_name
FROM Venues v
JOIN Events e ON v.venue_id = e.venue_id
JOIN EventPerformances ep ON e.event_id = ep.event_id
JOIN Bands b ON ep.band_id = b.band_id
WHERE e.event_date BETWEEN '2023-03-01' AND '2023-03-31'
GROUP BY v.venue_name
HAVING COUNT(DISTINCT b.band_name) = 
    (SELECT COUNT(DISTINCT b2.band_name)
     FROM Bands b2
     JOIN EventPerformances ep2 ON b2.band_id = ep2.band_id
     JOIN Events e2 ON ep2.event_id = e2.event_id
     WHERE e2.venue_id = v.venue_id
     AND e2.event_date BETWEEN '2023-03-01' AND '2023-03-31'
     AND b2.band_name NOT IN ('The Waves', 'Groove Squad'));

# 使用HAVING子句确保场馆中演出的乐队不包括“The Waves”和“Groove Squad”。

更改后

SELECT v.venue_name
FROM Venues v
JOIN Events e ON v.venue_id = e.venue_id
JOIN EventPerformances ep ON e.event_id = ep.event_id
JOIN Bands b ON ep.band_id = b.band_id
WHERE e.event_date BETWEEN '2023-03-01' AND '2023-03-31'
GROUP BY v.venue_name
HAVING COUNT(DISTINCT CASE 
    WHEN b.band_name NOT IN ('The Waves', 'Groove Squad') 
    THEN b.band_name END) = (
        SELECT COUNT(DISTINCT band_name) 
        FROM Bands 
        WHERE band_name NOT IN ('The Waves', 'Groove Squad')
    );

e. 编写一个查询，为每个场馆列出年末总结，显示场馆名称、举办的活动数量、总门票收入、总商品销售额和每个活动的平均观众规模。

SELECT 
    v.venue_name,
    COUNT(e.event_id) as total_events,
    SUM(e.ticket_revenue) as total_ticket_revenue,
    SUM(e.merchandise_sales) as total_merchandise_sales,
    ROUND(AVG(e.audience_size), 0) as avg_audience_size
FROM Venues v
LEFT JOIN Events e ON v.venue_id = e.venue_id
GROUP BY v.venue_id, v.venue_name
ORDER BY total_events DESC;

# ROUND(..., 0): 将平均观众数四舍五入到整数

f. 列出那些是在他们出生日期之前成立的乐队的音乐家。

SELECT DISTINCT
    m.musician_name,
    m.birthday,
    b.band_name,
    b.formation_year
FROM Musicians m
JOIN BandMembers bm ON m.musician_id = bm.musician_id
JOIN Bands b ON bm.band_id = b.band_id
WHERE YEAR(m.birthday) > b.formation_year
ORDER BY m.musician_name;

g. 选择一个特定的地点（场馆），列出所有在任何爵士主题活动（与爵士音乐相关的活动）中演出的乐队。如果一个乐队演出了多次，只包括第一次演出的日期。

SELECT 
    b.band_name,
    MIN(e.event_date) as first_performance_date
FROM Venues v
JOIN Events e ON v.venue_id = e.venue_id
JOIN EventPerformances ep ON e.event_id = ep.event_id
JOIN Bands b ON ep.band_id = b.band_id
WHERE v.venue_name = 'The Pavilion'  -- 这里可以更换成你想查询的场馆名称
AND e.theme LIKE '%Jazz%'  -- 匹配包含'Jazz'的主题
GROUP BY b.band_id, b.band_name
ORDER BY first_performance_date;

h. 找出观众规模最大的活动（如果有多个活动）。列出场馆、日期和演出的乐队。

SELECT 
    v.venue_name,
    e.event_date,
    e.audience_size,
    b.band_name,
    CASE 
        WHEN ep.headliner = TRUE THEN 'Headliner'
        ELSE 'Support'
    END as band_role
FROM Events e
JOIN Venues v ON e.venue_id = v.venue_id
JOIN EventPerformances ep ON e.event_id = ep.event_id
JOIN Bands b ON ep.band_id = b.band_id
WHERE e.audience_size = (
    SELECT MAX(audience_size) 
    FROM Events
)
ORDER BY 
    e.event_date;

i. 找出在2023年3月期间版税收入最高的音乐家（如果有多位音乐家）。包括他们的姓名、他们演奏的所有乐器和总版税收入。

# 先创建了一个新表
WITH MusicianRevenue AS (
    SELECT 
        m.musician_id,
        m.musician_name,
        GROUP_CONCAT(DISTINCT bm.instrument) as instruments, # 合并所有的乐器
        SUM(
            CASE 
                WHEN ep.headliner = TRUE THEN 
                    e.ticket_revenue * 0.8 * 0.6 * bm.royalty_rate
                ELSE 
                    (e.ticket_revenue * 0.8 * 0.4 / 
                        (SELECT COUNT(*) FROM EventPerformances ep2 
                         WHERE ep2.event_id = e.event_id AND ep2.headliner = FALSE)
                    ) * bm.royalty_rate
            END
        ) as total_royalty
    FROM Musicians m
    JOIN BandMembers bm ON m.musician_id = bm.musician_id
    JOIN EventPerformances ep ON bm.band_id = ep.band_id
    JOIN Events e ON ep.event_id = e.event_id
    WHERE e.event_date BETWEEN '2023-03-01' AND '2023-03-31'
    GROUP BY m.musician_id, m.musician_name
)
SELECT 
    musician_name,
    instruments,
    total_royalty
FROM MusicianRevenue
WHERE total_royalty = (
    SELECT MAX(total_royalty) 
    FROM MusicianRevenue
)
ORDER BY musician_name;

简化版本：只能查询主乐队的成员的总税收

SELECT 
	m.musician_id,
	m.musician_name,
	SUM(e.ticket_revenue * 0.8 * 0.6 * bm.royalty_rate) as total_royalty
FROM Musicians m
JOIN BandMembers bm ON m.musician_id = bm.musician_id
JOIN EventPerformances ep ON bm.band_id = ep.band_id
JOIN Events e ON ep.event_id = e.event_id
WHERE e.event_date BETWEEN '2023-03-01' AND '2023-03-31'
AND ep.headliner = 'True'
GROUP BY m.musician_id, m.musician_name

j. 识别任何在乐队中也是独奏艺术家的音乐家。此外，确定这些独奏艺术家在演出时是否演奏了多种乐器，并列出他们与乐队一起演出的日期。

-- 先找出独奏艺术家（假设独奏艺术家就是自己组成的乐队）
WITH SoloArtists AS (
    SELECT DISTINCT m.musician_id, m.musician_name
    FROM Musicians m
    JOIN BandMembers bm ON m.musician_id = bm.musician_id
    JOIN Bands b ON bm.band_id = b.band_id
    GROUP BY b.band_id, m.musician_id, m.musician_name
    HAVING COUNT(bm.musician_id) = 1
),
-- 找出多人乐队的成员
BandArtists AS (
    SELECT DISTINCT m.musician_id, m.musician_name
    FROM Musicians m
    JOIN BandMembers bm ON m.musician_id = bm.musician_id
    JOIN Bands b ON bm.band_id = b.band_id
    GROUP BY b.band_id, m.musician_id, m.musician_name
    HAVING COUNT(bm.musician_id) > 1
)
-- 主查询
SELECT 
    m.musician_name,
    e.event_date,
    b.band_name
FROM Musicians m
JOIN BandMembers bm ON m.musician_id = bm.musician_id
JOIN Bands b ON bm.band_id = b.band_id
JOIN EventPerformances ep ON b.band_id = ep.band_id
JOIN Events e ON ep.event_id = e.event_id
WHERE m.musician_id IN (
    SELECT s.musician_id 
    FROM SoloArtists s
    JOIN BandArtists ba ON s.musician_id = ba.musician_id
)
GROUP BY m.musician_name, e.event_date, b.band_name
ORDER BY m.musician_name, e.event_date;