用mysql和workbench实现一个数据库设计系统使用model建表不少于5张表有主键外键索引函数视图触发器

以下是一个使用MySQL和Workbench实现的数据库设计系统，包含5张表、主键、外键、索引、函数、视图和触发器：

1. 用户表

CREATE TABLE users ( user_id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, password VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

2. 文章表

CREATE TABLE articles ( article_id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(100) NOT NULL, body TEXT NOT NULL, author_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (author_id) REFERENCES users(user_id) ON DELETE CASCADE );

3. 评论表

CREATE TABLE comments ( comment_id INT PRIMARY KEY AUTO_INCREMENT, body TEXT NOT NULL, author_id INT NOT NULL, article_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (author_id) REFERENCES users(user_id) ON DELETE CASCADE, FOREIGN KEY (article_id) REFERENCES articles(article_id) ON DELETE CASCADE );

4. 标签表

CREATE TABLE tags ( tag_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

5. 文章标签表

CREATE TABLE article_tags ( article_id INT NOT NULL, tag_id INT NOT NULL, FOREIGN KEY (article_id) REFERENCES articles(article_id) ON DELETE CASCADE, FOREIGN KEY (tag_id) REFERENCES tags(tag_id) ON DELETE CASCADE, PRIMARY KEY (article_id, tag_id) );

-- 创建索引 CREATE INDEX idx_author_id ON articles(author_id); CREATE INDEX idx_article_id ON comments(article_id);

-- 创建函数 DELIMITER $$ CREATE FUNCTION fn_calculate_age(dob DATE) RETURNS INT BEGIN DECLARE age INT; SELECT FLOOR(DATEDIFF(CURRENT_DATE(), dob)/365.25) INTO age; RETURN age; END$$ DELIMITER ;

-- 创建视图 CREATE VIEW article_comments AS SELECT articles.title, COUNT(comments.comment_id) AS total_comments FROM articles LEFT JOIN comments ON articles.article_id = comments.article_id GROUP BY articles.article_id;

-- 创建触发器 DELIMITER $$ CREATE TRIGGER tr_user_created_at BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.created_at = CURRENT_TIMESTAMP; END$$ DELIMITER