SQL Server 中用于寻址表的正确语法是什么? [英] What is the proper syntax in SQL Server for addressing tables?

问题描述

这似乎是一个相当明显的问题，但我一直无法为我要问的问题想出合适的术语，因此为此提供参考资料很棘手.不过，答案似乎很明显.

This seems like a fairly obvious question, but I haven't been able to think of the proper term for what I am trying to ask, so coming up with reference material for this has been tricky. The answers seem obvious, though.

在检查 SQL Server 的 Pluralsight 培训材料时，他们建议始终在常规"查询(您可能为基本 Web 服务编写的内容)和存储过程中的 SQL 查询中始终引用表，如下所示:

When examining the Pluralsight training material for SQL Server, they recommended always referring to tables in both "regular" queries (something you might write for a basic web service) and for a SQL query in perhaps a stored procedure, like the following:

[databasename].[dbo].[some_table].[sometimesacolumngoeshere]

虽然我发现遇到存储过程等很常见，但只需使用:

Though I have found it very common to come across stored procs, etc., that simply use:

my_column    or    [my_column]

这里的区别显然是一个明确地提供了一个绝对的地址"，而另一个则是隐含的.

The difference here is obviously that one provides an absolute "address" explicitly, whereas the other is implicit.

你怎么知道什么时候使用一个而不是另一个是合适的，你称之为寻址?

How do you know when it is appropriate to use one over the other, and also what do you call this, addressing?

我的偏好是总是明确的，如果你需要节省空间和/或让事情更清楚，你可以别名为一个明确的完整地址"，对吗?

My preference would be to always be explicit, and if you need to save space and/or make things more clear, you could alias to an explicit full "address", correct?

推荐答案

你说得对.基本上，SQL 将尝试在您的 FROM 和 JOIN 部分的所有表中查找您正在寻找的字段my_column".但是，如果您碰巧在表 A 和表 B 中有一个my_column"，那么您需要通过包含表名来明确告诉它您正在查找哪个my_column".如果您在那里也有冲突，那么这会沿着链向上传递到 dbo 和 databasename.

You are correct. Basically SQL will attempt to find the field you are looking for "my_column" in all of the tables in your FROM and JOIN sections. If however you happen to have a "my_column" in table A and in table B then you need to explicitly tell it which "my_column" you are looking for by including the table name. This goes on up the chain to dbo and databasename if you have collisions there as well.

大多数情况下，您会发现人们不会明确调用列所在的表，除非他们加入多个表.

Most of the time you will find that people don't explicitly call out the tables a column is in unless they are joining multiple tables.

例如我这样写查询:

SELECT a.field1, b.field2
FROM tableA AS a
INNER JOIN tableB AS b ON a.id = b.a_id
WHERE a.id = 123

这里我使用 AS 将 tableA 和 tableB 别名为更具可读性的 a 和 b.我可以像这样轻松地编写我的查询:

Here I am using the AS to alias tableA and tableB down to more readable a and b. I could have just as easily written my query like this:

SELECT tableA.field1, tableB.field2
FROM tableA
INNER JOIN tableB ON tableA.id = tableB.a_id
WHERE tableA.id = 123

或者像这样，如果 field1 和 field2 对于那里的表是唯一的，但是这对于每条数据的来源有点混乱.

Or like this, if field1 and field2 are unique to there tables, but this gets a bit confusing as to where each piece of data is coming from.

SELECT field1, field2
FROM tableA
INNER JOIN tableB ON tableA.id = tableB.a_id
WHERE tableA.id = 123

这篇关于SQL Server 中用于寻址表的正确语法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！