如何完成这个作业SQL查询? [英] How to finish this homework SQL query?

问题描述

这是我的作业问题:

编写一个 SQL 查询，该查询从 2013 年创建的所有文章的 user_id、用户名、来自表user"的电子邮件、文章"的标题、评论"的内容返回.

Write an SQL query that returns from user_id, username, email from table 'user', title of 'article', content of 'comments' for all the articles are created in 2013.

这是我目前的解决方案:

This is my solution so far:

Select user_id, users_username, users_email, articles_title, articles_created, comments_content
from comments
inner join users
   on comments.user_id = users.id
inner join users
   on comments.articles_id = articles.id
where created > = '2013/01/01' AND '2014/01/01';

这是 ERD:

推荐答案

此查询应返回指定的结果集:

This query should return the specified resultset:

SELECT u.id AS user_id
     , u.username
     , u.email
     , a.title
     , c.created
     , c.content
  FROM comments c
  JOIN users u
    ON u.id = c.user_id
  JOIN articles a
    ON a.id = c.articles_id
 WHERE c.created >= '2013-01-01' 
   AND c.created <  '2014-01-01'
 ORDER BY u.id, c.created

<小时>

如果您想了解 SQL，解决问题并了解查询文本的解释方式很重要.

---

If you want to understand SQL, it's important that you work through the problems, and understand how the query text is being interpreted.

一些注意事项:

• 不要在 >= 运算符中的两个字符之间放置空格(我很确定这是无效的；即使是，也不要这样做.)
• AND 是一个逻辑运算符，"foo >= x AND y" 不是将 "foo" 与 "y" 进行比较>，它将 "foo >= x" 评估为布尔值(返回 true、false 或 null)，它将 "y" 评估为布尔值(返回 true、false, 或 null)，然后 "AND" 对两个布尔值进行逻辑 AND 运算.这是有效的语法，但不是您想要的操作.
• MySQL 中的日期文字应始终采用 'YYYY-MM-DD' 形式；后面的 MySQL 版本对分隔符和月和日的两个位置比较宽松，但标准是四横二横二格式.
• 最佳实践是使用表名或表别名来限定列引用，点字符将表名/别名与列分开
• 查询中的 "users_username" 引用是无效标识符.查询引用的任何表中都没有该名称的任何列.看起来您的意思是从 "users" 表中指定 "username" 列，该列将被引用为 "users.user_name"，使用点字符分隔两个标识符.SELECT 列表中的其他列引用也是如此
• "INNER" 关键字是多余的，没有必要，在查询中没有区别；在 MySQL 中 "JOIN" 和 "INNER JOIN" 是同义词.(它也是 "CROSS JOIN" 的同义词，尽管我们通常会在没有连接谓词时包含 "CROSS" 关键字，即使它不是必需的.这主要是为未来的读者提供文档帮助，他们将寻找 JOIN 谓词，"CROSS" 关键字为他们提供了一个线索，即缺少"的 JOIN 谓词是有意的，而不仅仅是错误或疏忽.

• Don't put spaces between the two characters in the >= operator (I'm fairly certain that's not valid; even if it is, don't do it.)
• The AND is a logical operator, "foo >= x AND y" isn't comparing "foo" to "y", it's evaluating "foo >= x" as a boolean (returning true, false or null), it's evaluating "y" as a boolean (returning true, false, or null), and then "AND" is doing a logical AND operation on the two booleans. That's valid syntax, but it's not the operations you intended.
• Date literals in MySQL should always in the form 'YYYY-MM-DD'; later versions of MySQL are more lenient about the separator character and the two positions for the month and day, but the four-dash-two-dash-two format is the standard.
• Best practice is to qualify column references with table name or table alias, the dot character separates the table name/alias from the column
• The "users_username" reference in your query is an invalid identifier. There isn't any column by that name in any table referenced by the query. It looks like you meant that to specify the "username" column from the "users" table, which would be referenced as "users.user_name", using the dot character to separate the the two identifiers. Same for the other column references in your SELECT list
• The "INNER" keyword is redundant, it's unnecessary and makes no difference in the query; in MySQL "JOIN" and "INNER JOIN" are synonymous. (It's also synonymous with "CROSS JOIN", though we typically do include the "CROSS" keyword when there are no join predicates, even though it's not required. This is mostly a documentation aid for the future reader, who will be looking for JOIN predicates, the "CROSS" keyword gives them a clue that the "missing" JOIN predicates are intentional, and not just a mistake or oversight.

另请注意:SQL 语句的 READ 次数多于 WRITTEN 的次数；因此，请格式化 SQL，以便读者能够轻松解读和理解语句的作用.

Also note: a SQL statement is READ many more times than it is WRITTEN; so, format the SQL so it's easy for a reader to decipher and comprehend what the statement is doing.

例如，将上面的查询与等价的查询进行比较:

As an example, compare the query above, to the equivalent:

select users.id as user_id,users.username as username,users.email as email,articles.title,
comments.created, comments.content from comments inner join users on comments.user_id=
users.id join articles on comments.articles_id=articles.id where comments.created >=
'2013-01-01' and comments.created < '2014-01-01' order by users.id, comments.created

然后回答以下问题，查看 SQL:

Then answer the following questions, looking at the SQL:

• 此查询返回多少列?
• 评论表返回哪些列?
• 涉及多少张桌子?
• 是否有任何外连接操作?
• 选择列表中是否有任何聚合?
• 等

SQL 的格式是对未来读者的一种帮助.真正的好处是通过更复杂的 SQL(涉及多个表、聚合、内联视图、复杂表达式、相关子查询等)来体现.

The formatting of the SQL is an aid to the future reader. The real benefit reveals itself with more complicated SQL (involving multiple tables, aggregates, inline views, complex expressions, correlated subqueries, and so on.)

这篇关于如何完成这个作业SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！