mysql - answers

作业一

-- 创建Author作者表
CREATE TABLE Author (
    -- 作者ID
    AuthorID int primary key COMMENT '作者ID',
    -- 姓名
    Name varchar(20) COMMENT '姓名',
    -- 邮箱
    Email varchar(20) COMMENT '邮箱'
);

-- 创建Article文章表
CREATE TABLE Article (
    -- 文章ID
    ArticleID int primary key COMMENT '文章ID',
    -- 标题
    Title varchar(100) COMMENT '标题',
    -- 内容
    Content varchar(300) COMMENT '内容',
    -- 发表日期
    PublishDate datetime COMMENT '发表日期'
);

-- 创建Author_Article关联表
CREATE TABLE Author_Article (
    -- 关联ID
    elationID int primary key COMMENT '关联ID',
    -- 文章ID
    ArticleID int COMMENT '文章ID',
    -- 作者ID
    AuthorID int COMMENT '作者ID',
    -- 角色
    Role varchar(20) COMMENT '角色'
);

作业二

-- 向Author作者表中插入数据
INSERT INTO Author (AuthorID, Name, Email)
VALUES (1, 'John Smith', 'john@example.com');

INSERT INTO Author (AuthorID, Name, Email)
VALUES (2, 'Jane Doe', 'jane@example.com');

INSERT INTO Author (AuthorID, Name, Email)
VALUES (3, 'Michael Johnson', 'michael@example.com');

-- 向Article文章表中插入数据
INSERT INTO Article (ArticleID, Title, Content, PublishDate)
VALUES (1, 'Introduction to AI', 'Lorem ipsum dolor sit amet.', '2023-01-15');

INSERT INTO Article (ArticleID, Title, Content, PublishDate)
VALUES (2, 'Machine Learning Basics', 'Lorem ipsum dolor sit amet.', '2023-02-10');

INSERT INTO Article (ArticleID, Title, Content, PublishDate)
VALUES (3, 'Data Science Techniques', 'Lorem ipsum dolor sit amet.', '2023-03-22');

-- 向Author_Article关联表中插入数据
INSERT INTO Author_Article (elationID, AuthorID, ArticleID, Role)
VALUES (1, 1, 1, 'Main Author');

INSERT INTO Author_Article (elationID, AuthorID, ArticleID, Role)
VALUES (2, 2, 1, 'Co-Author');

INSERT INTO Author_Article (elationID, AuthorID, ArticleID, Role)
VALUES (3, 3, 2, 'Main Author');

作业三

-- 修改Author作者表中的数据将John Smith的邮箱改为‘23432@sfd.com’
UPDATE Author
SET Email = '23432@sfd.com'
WHERE Name = 'John Smith';

-- 删除Article文章表中ArticleID为3的数据
DELETE FROM Article
WHERE ArticleID = 3;

作业四

-- 查询文章表（Article）中发表日期（Publish Date）在 2023 年之前的所有文章
SELECT *
FROM Article
WHERE PublishDate < '2024-01-01';

-- 查询作者表（Author）中名字（Name）包含 “Smith” 的作者信息
SELECT *
FROM Author
WHERE Name LIKE '%Smith%';

-- 查询文章表（Article）中标题（Title）以 “Machine Learning” 开头的文章数量
SELECT COUNT(*)
FROM Article
WHERE Title LIKE 'Machine Learning%';

作业五

-- 查询作者表（Author）和文章表（Article），找出每篇文章对应的作者姓名、标题和发表日期
SELECT Author.Name, Article.Title, Article.PublishDate
FROM Author
JOIN Author_Article ON Author.AuthorID = Author_Article.AuthorID
JOIN Article ON Author_Article.ArticleID = Article.ArticleID;

-- 查询作者（Author）表，列出每位作者的姓名、文章标题和发表日期，仅包括担任主作者（role = ‘Main Author’）的文章
SELECT Author.Name, Article.Title, Article.PublishDate
FROM Author
JOIN Author_Article ON Author.AuthorID = Author_Article.AuthorID
JOIN Article ON Author_Article.ArticleID = Article.ArticleID
WHERE Author_Article.Role = 'Main Author';

-- 找出每位作者的姓名以及拥有的文章数量
SELECT Author.Name, COUNT(Article.ArticleID) AS ArticleCount
FROM Author
JOIN Author_Article ON Author.AuthorID = Author_Article.AuthorID
JOIN Article ON Author_Article.ArticleID = Article.ArticleID
GROUP BY Author.Name;

作业六

-- 查询作者（Author）表，找出所有不参与任何文章写作的作者
SELECT Author.*
FROM Author
LEFT JOIN Author_Article ON Author.AuthorID = Author_Article.AuthorID
WHERE Author_Article.AuthorID IS NULL;

-- 找出发表日期最早的文章的标题
SELECT Title
FROM Article
WHERE PublishDate = (
    SELECT MIN(PublishDate)
    FROM Article
);

作业七

-- 创建一个名为"AuthorArticleView"的视图，包含作者（Author）和文章（Article）实体之间的关联信息，显示作者的姓名、邮箱、文章标题和发表日期。要求只包含发表日期在2023年之后的文章
CREATE VIEW AuthorArticleView AS
SELECT Author.Name, Author.Email, Article.Title, Article.PublishDate
FROM Author
JOIN Author_Article ON Author.AuthorID = Author_Article.AuthorID
JOIN Article ON Author_Article.ArticleID = Article.ArticleID
WHERE Article.PublishDate >= '2023-01-01';

-- 创建一个名为"AACountView"的视图，包含作者（Author）实体的姓名和该作者发布的文章数量
CREATE VIEW AACountView AS
SELECT Author.Name, COUNT(Article.ArticleID) AS ArticleCount
FROM Author
JOIN Author_Article ON Author.AuthorID = Author_Article.AuthorID
JOIN Article ON Author_Article.ArticleID = Article.ArticleID
GROUP BY Author.Name;