1. 创建数据库并选择：
```
CREATE DATABASE example;
USE example;
```
2. 创建表：
```
-- 表1：用户信息
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
gender VARCHAR(10),
email VARCHAR(100)
);
-- 表2：文章信息
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(100),
content TEXT,
author_id INT,
FOREIGN KEY (author_id) REFERENCES users(id)
);
-- 表3：评论信息
CREATE TABLE comments (
id INT PRIMARY KEY,
content TEXT,
article_id INT,
user_id INT,
FOREIGN KEY (article_id) REFERENCES articles(id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- 表4：标签信息
CREATE TABLE tags (
id INT PRIMARY KEY,
name VARCHAR(50)
);
-- 表5：文章和标签关系
CREATE TABLE article_tags (
article_id INT,
tag_id INT,
PRIMARY KEY (article_id, tag_id),
FOREIGN KEY (article_id) REFERENCES articles(id),
FOREIGN KEY (tag_id) REFERENCES tags(id)
);
-- 插入数据
INSERT INTO users VALUES
(1, 'Tom', 20, 'male', 'tom@example.com'),
(2, 'Jerry', 22, 'male', 'jerry@example.com'),
(3, 'Lucy', 21, 'female', 'lucy@example.com'),
(4, 'Lily', 23, 'female', 'lily@example.com'),
(5, 'Bob', 25, 'male', 'bob@example.com'),
(6, 'Alice', 24, 'female', 'alice@example.com'),
(7, 'John', 26, 'male', 'john@example.com'),
(8, 'David', 27, 'male', 'david@example.com'),
(9, 'Kate', 24, 'female', 'kate@example.com'),
(10, 'Amy', 23, 'female', 'amy@example.com');
INSERT INTO articles VALUES
(1, 'MySQL Tutorial', 'This is a MySQL tutorial.', 1),
(2, 'Python Tutorial', 'This is a Python tutorial.', 2),
(3, 'Java Tutorial', 'This is a Java tutorial.', 3),
(4, 'PHP Tutorial', 'This is a PHP tutorial.', 4),
(5, 'C# Tutorial', 'This is a C# tutorial.', 5),
(6, 'Ruby Tutorial', 'This is a Ruby tutorial.', 6),
(7, 'Swift Tutorial', 'This is a Swift tutorial.', 7),
(8, 'Objective-C Tutorial', 'This is an Objective-C tutorial.', 8),
(9, 'JavaScript Tutorial', 'This is a JavaScript tutorial.', 9),
(10, 'CSS Tutorial', 'This is a CSS tutorial.', 10);
INSERT INTO comments VALUES
(1, 'Great tutorial!', 1, 2),
(2, 'Thanks for sharing.', 1, 3),
(3, 'I learned a lot from this tutorial.', 1, 4),
(4, 'Nice job!', 2, 1),
(5, 'This tutorial is very helpful.', 2, 3),
(6, 'I will recommend this tutorial to my friends.', 2, 5),
(7, 'I have a question about this tutorial.', 3, 2),
(8, 'Can you explain this concept in more detail?', 3, 4),
(9, 'I like your writing style.', 4, 3),
(10, 'This tutorial is easy to follow.', 5, 1),
(11, 'I wish there were more examples.', 5, 2),
(12, 'This tutorial helped me solve a problem.', 6, 4),
(13, 'Excellent tutorial!', 7, 5),
(14, 'Thank you for your effort.', 8, 3),
(15, 'This tutorial is perfect for beginners.', 9, 1),
(16, 'I will come back to this tutorial for reference.', 10, 2);
INSERT INTO tags VALUES
(1, 'MySQL'),
(2, 'Python'),
(3, 'Java'),
(4, 'PHP'),
(5, 'C#'),
(6, 'Ruby'),
(7, 'Swift'),
(8, 'Objective-C'),
(9, 'JavaScript'),
(10, 'CSS');
INSERT INTO article_tags VALUES
(1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5),
(6, 6),
(7, 7),
(8, 8),
(9, 9),
(10, 10);
```
3. 使用模式匹配查询：
```
-- 查询以 'T' 开头的姓名
SELECT * FROM users WHERE name LIKE 'T%';
-- 查询标题中包含 'Tutorial' 的文章
SELECT * FROM articles WHERE title LIKE '%Tutorial%';
```
4. 使用 TOP 查询：
```
-- 查询前 3 条评论
SELECT TOP 3 * FROM comments;
```
5. 使用 IN 查询：
```
-- 查询标签为 MySQL 或 Python 的文章
SELECT * FROM articles WHERE id IN (SELECT article_id FROM article_tags WHERE tag_id IN (1, 2));
```
6. 使用降序查询：
```
-- 查询年龄最大的用户
SELECT * FROM users ORDER BY age DESC LIMIT 1;
```
7. 使用 COUNT 集合函数查询：
```
-- 查询每个作者发表的文章数量
SELECT author_id, COUNT(*) AS num_articles FROM articles GROUP BY author_id;
```
8. 分组统计查询：
```
-- 查询每个标签下的文章数量
SELECT tag_id, COUNT(*) AS num_articles FROM article_tags GROUP BY tag_id;
```
9. 使用连接条件的多表查询：
```
-- 查询每篇文章下的评论数量
SELECT articles.id, COUNT(*) AS num_comments FROM articles LEFT JOIN comments ON articles.id = comments.article_id GROUP BY articles.id;
```
10. 比较运算符的子查询：
```
-- 查询年龄大于平均年龄的用户
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);
```
11. 使用 IN 的子查询：
```
-- 查询评论过文章标题中包含 'Python' 的用户
SELECT * FROM users WHERE id IN (SELECT user_id FROM comments WHERE article_id IN (SELECT id FROM articles WHERE title LIKE '%Python%'));
```
12. 创建视图并使用：
```
-- 创建视图，查询文章和标签的关系
CREATE VIEW article_tag_relations AS
SELECT articles.id AS article_id, tags.id AS tag_id, tags.name AS tag_name
FROM articles LEFT JOIN article_tags ON articles.id = article_tags.article_id
LEFT JOIN tags ON article_tags.tag_id = tags.id;
-- 使用视图查询
SELECT * FROM article_tag_relations;
```
13. 创建索引：
```
-- 创建索引，加速查询
CREATE INDEX idx_author_id ON articles (author_id);
```
14. 创建唯一性约束，检查约束，默认值约束，外键约束：
```
-- 在 users 表的 email 列上创建唯一性约束
ALTER TABLE users ADD CONSTRAINT uc_email UNIQUE (email);
-- 在 articles 表的 author_id 列上创建外键约束
ALTER TABLE articles ADD CONSTRAINT fk_author_id FOREIGN KEY (author_id) REFERENCES users(id);
-- 在 comments 表的 content 列上创建检查约束，限制长度不超过 200 字符
ALTER TABLE comments ADD CONSTRAINT chk_content CHECK (LEN(content) <= 200);
-- 在 articles 表的 created_at 列上创建默认值约束，设置默认值为当前时间
ALTER TABLE articles ADD CONSTRAINT df_created_at DEFAULT (GETDATE()) FOR created_at;
```
15. 创建存储过程并使用：
```
-- 创建存储过程，查询指定作者的文章数量
CREATE PROCEDURE get_num_articles_by_author (@author_name VARCHAR(50))
AS
BEGIN
SELECT COUNT(*) FROM articles WHERE author_id = (SELECT id FROM users WHERE name = @author_name);
END;
-- 使用存储过程查询
EXEC get_num_articles_by_author 'Tom';
```
16. 创建插入和更新的 DML 触发器：
```
-- 创建插入触发器，自动设置文章的创建时间为当前时间
CREATE TRIGGER tr_insert_article
ON articles
AFTER INSERT
AS
BEGIN
UPDATE articles SET created_at = GETDATE() WHERE id = inserted.id;
END;
-- 创建更新触发器，自动更新文章的修改时间为当前时间
CREATE TRIGGER tr_update_article
ON articles
AFTER UPDATE
AS
BEGIN
UPDATE articles SET updated_at = GETDATE() WHERE id = inserted.id;
END;
```
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
