何时使用USE开始T-SQL查询？

I'm a T-SQl and database newbie, and a little confused. In the T-SQL book I'm reading it says that a USE statement is written to set the database context of the session. Does that mean that there can be more than one database in an instance of SQL-server and the USE statement tells SQL server which database the query will be on?

我是T-SQl和数据库新手,有点困惑。在我正在阅读的T-SQL书中,它说写了一个USE语句来设置会话的数据库上下文。这是否意味着SQL服务器实例中可以有多个数据库,USE语句告诉SQL服务器查询将在哪个数据库上?

7 个解决方案

#1

It's not quite as simple as that. Yes, a server can have multiple databases, but I would caution against using a USE statement unless all of your queries are that way.

它并不那么简单。是的,服务器可以有多个数据库,但我会提醒您不要使用USE语句,除非您的所有查询都是这样的。

Every connection to SQL server has a "current database". That means that any query run over that connection will use that database (though it's possible to access information from other databases by explicitly indicating this with dot notation). The USE statement changes the current database for the connection, so if one statement expects to run in the MyDatabase database (but doesn't specify it with a USE MyDatabase statement) and another query executes Use OtherDatabase, then the first query will fail when run.

与SQL Server的每个连接都有一个“当前数据库”。这意味着在该连接上运行的任何查询都将使用该数据库(尽管可以通过使用点表示法明确指示来访问其他数据库中的信息)。 USE语句更改连接的当前数据库,因此如果一个语句期望在MyDatabase数据库中运行(但未使用USE MyDatabase语句指定它)而另一个查询执行Use OtherDatabase,则第一个查询将在运行时失败。

For instance:

Connect to SQL Server to the MyDatabase database

连接到SQL Server到MyDatabase数据库

Execute the following query:

执行以下查询:

select * from sometable

从某个表中选择*

Execute the next query:

执行下一个查询:

use OtherDatabase

select * from othertable

从othertable中选择*

If we try to execute the first query again it will fail (assuming that sometable doesn't also exist in OtherDatabase) because we've changed the context of the connection with the USE statement. The upshot here is that the USE statement is connection-wide, not specific to an individual query.

如果我们再次尝试执行第一个查询,它将失败(假设在OtherDatabase中也不存在某个表),因为我们已经使用USE语句更改了连接的上下文。这里的结果是USE语句是连接范围的,不是特定于单个查询。

In software development, the more common approach is to maintain a connection to the server for each database you plan to query against. That means that the query against MyDatabase will use a different connection than the one used by the query for OtherDatabase. This helps mitigate the possibility of a change in query context causing queries to fail.

在软件开发中,更常见的方法是为计划查询的每个数据库维护与服务器的连接。这意味着针对MyDatabase的查询将使用与查询用于OtherDatabase的连接不同的连接。这有助于减少查询上下文更改导致查询失败的可能性。

#2

Yes, there can be more than one database on a SQL Server instance.

是的,SQL Server实例上可以有多个数据库。

#3

Yes, an instance of SQL Server can maintain many databases. You can also specify your database in the connection string in an applciation.

是的,SQL Server的一个实例可以维护许多数据库。您还可以在applciation中的连接字符串中指定数据库。

In Management Studio, when you change the database in the drop down menu it automatically runs the USE command for the currently selected window.

在Management Studio中,当您在下拉菜单中更改数据库时,它会自动为当前选定的窗口运行USE命令。

#4

Yes you are correct. There may be more than one DB per server instance.

是的,你是对的。每个服务器实例可能有多个数据库。

Using "USE", lets the server know which database you are refering to by default. You may however make reference to any database (of that instance), by explicitly specifying the database.

使用“USE”,让服务器默认知道您要引用的数据库。但是,您可以通过显式指定数据库来引用任何数据库(该实例)。

The syntax is database.schema.sqlobject (sqlobject is table, view, SP etc.)

语法是database.schema.sqlobject(sqlobject是table,view,SP等)

For example:

SELECT * FROM myDbOfBirds.dbo.MigratoryTable

or even

USE myDbOfBirds
GO
SELECT * 
-- note how we don't explicity qualify MigratoryTable table (can but do not have to)
-- but that we do so for the Country table  (have to)
FROM MigratoryTable  MT
JOIN MyOtherDB.dbo.Country C ON C.CountryCode = MT.CountryCode
WHERE MigrationMonth = 11

It is important to understand that, like many other settings, USE is a connection-wide setting, which affects all pieces of logic sharing the same connection. For this reason, USE's usage is either reserved to ad-hoc types of queries, or when there is a single user of the connection (or when several users of said connections are known to exclusively reference a single database)

重要的是要理解,与许多其他设置一样,USE是一种连接范围的设置,它会影响共享同一连接的所有逻辑部分。出于这个原因,USE的用法要么保留给特定类型的查询,要么只有连接的单个用户(或者已知所述连接的多个用户专门引用单个数据库)

#5

Most often your DB is specified via the connection (or connection string); you can use the USE statement to specify a change in the query context.

通常,您的数据库是通过连接(或连接字符串)指定的;您可以使用USE语句指定查询上下文中的更改。

#6

Yes, a SQL Server can host many databases. The USE statement instructs SQL Server to execute following statements in the context of the specified database.

是的,SQL Server可以托管许多数据库。 USE语句指示SQL Server在指定数据库的上下文中执行以下语句。

The USE statement is often used to help prevent queries from "accidentally" running in the wrong database. For example, before running a script that creates or deletes tables, you might want to add a USE statement at the top to mitigate the risk of accidentally running the script in the master database.

USE语句通常用于帮助防止查询“意外”在错误的数据库中运行。例如,在运行创建或删除表的脚本之前,您可能希望在顶部添加USE语句,以降低在master数据库中意外运行脚本的风险。

That said, in most scenarios a USE statement is not necessary, because the connection string used to connect to the server will also include the database.

也就是说,在大多数情况下,不需要USE语句,因为用于连接服务器的连接字符串也将包括数据库。

#7

you typically do not need to use the "use" statement, because the database name is usually included in the connection string. you can use that if you want to switch databases tho.

您通常不需要使用“use”语句,因为数据库名称通常包含在连接字符串中。如果你想切换数据库,你可以使用它。

sql server can support many databases accessible through the same server. the use statement tells the server what database context your connection will be using.

sql server可以支持许多可通过同一服务器访问的数据库。 use语句告诉服务器您的连接将使用哪个数据库上下文。

the maximum number of databases per instance of SQL Server is 32,767.

每个SQL Server实例的最大数据库数为32,767。

#1