Microsoft SQL Server入門

Download sql-server eBook

備註

這是一組突出顯示SQL Server基本用法的示例。

版本

發布日期
SQL Server 2016 2016年6月1日
SQL Server 2014 2014年3月18日
SQL Server 2012 2011-10-11
SQL Server 2008 R2 2010-04-01
SQL Server 2008 2008-08-06
SQL Server 2005 2005-11-01
SQL Server 2000 2000-11-01

代碼中的評論

Transact-SQL支持兩種形式的註釋編寫。數據庫引擎會忽略註釋,供人們閱讀。

註釋前面帶有-- 並且在遇到新行之前被忽略:

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;
 

斜杠明星評論/* 開頭,以*/ 結尾。這些分隔符之間的所有文本都被視為註釋塊。

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/
 

如果SQL語句丟失了新的行字符,則斜杠明星註釋的優點是可以保留註釋。在故障排除期間捕獲SQL時會發生這種情況。

斜線星形註釋可以嵌套,並且斜線星形註釋中的起始/* 需要以*/ 結束才能生效。以下代碼將導致錯誤

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/  */
 

即使在報價內部,斜線明星也被視為評論的開始。因此,它需要以另一個收盤明星斜線結束。正確的方法是

/* This is
a multi-line
comment block. */
SELECT Id = 1, [Message] = 'First row'
UNION ALL
SELECT 2, 'Second row'
/* This is a one liner */
SELECT 'More';
 

創建新表並從舊表中插入記錄

SELECT * INTO NewTable FROM OldTable
 

創建一個包含舊表結構的新表,並將所有行插入到新表中。

一些限制

  1. 您不能將表變量或表值參數指定為新表。
  2. 即使源表已分區,也無法使用SELECT ... INTO創建分區表。 SELECT ... INTO不使用源表的分區方案;相反,新表是在默認文件組中創建的。要將行插入分區表,必須首先創建分區表,然後使用INSERT INTO ... SELECT FROM語句。
  3. 源表中定義的索引,約束和触發器不會傳輸到新表,也不能在SELECT ... INTO語句中指定。如果需要這些對象,則可以在執行SELECT ... INTO語句後創建它們。
  4. 指定ORDER BY子句不保證按指定順序插入行。當稀疏列包含在選擇列表中時,稀疏列屬性不會傳輸到新表中的列。如果新表中需要此屬性,請在執行SELECT ... INTO語句後更改列定義以包含此屬性。
  5. 當計算列包含在選擇列表中時,新表中的相應列不是計算列。新列中的值是SELECT ... INTO執行時計算的值。

[ sic ]

刪除所有行

DELETE
FROM Helloworlds
 

這將刪除表中的所有數據。運行此代碼後,該表將不包含任何行。與DROP TABLE 不同,這會保留表本身及其結構,您可以繼續在該表中插入新行。

刪除表中所有行的另一種方法是截斷它,如下所示:

TRUNCATE TABLE HelloWords
 

與DELETE操作的區別有幾個:

  1. 截斷操作不存儲在事務日誌文件中
  2. 如果存在IDENTITY 字段,則將重置此值
  3. TRUNCATE可以應用於整個表,而不是部分應用(而使用DELETE 命令可以關聯WHERE 子句)

TRUNCATE的限制

  1. 如果存在FOREIGN KEY 引用,則無法對錶進行TRUNCATE
  2. 如果表格參與了INDEXED VIEW
  3. 如果使用TRANSACTIONAL REPLICATIONMERGE REPLICATION 發布該表
  4. 它不會觸發表中定義的任何TRIGGER

[原文如此]

獲得表行計數

如果table_name 替換為您要查詢的表,則可以使用以下示例查找數據庫中特定表的總行數:

SELECT COUNT(*) AS [TotalRowCount] FROM table_name;
 

通過使用以下腳本基於表的HEAP(index_id = 0)或集群聚簇索引(index_id = 1)連接回表的分區,還可以獲取所有表的行計數:

SELECT  [Tables].name                AS [TableName],
        SUM( [Partitions].[rows] )    AS [TotalRowCount]
FROM    sys.tables AS [Tables]
JOIN    sys.partitions AS [Partitions]
    ON  [Tables].[object_id]    =    [Partitions].[object_id]
    AND [Partitions].index_id IN ( 0, 1 )
--WHERE    [Tables].name = N'table name' /* uncomment to look for a specific table */
GROUP BY    [Tables].name;
 

這是可能的,因為每個表本質上都是一個分區表,除非向其添加額外的分區。該腳本還具有不干擾對錶行的讀/寫操作的好處。

INSERT / SELECT / UPDATE / DELETE:數據操作語言的基礎知識

D ata M anipulation L anguage(簡稱DML)包括INSERTUPDATEDELETE

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

在這個腳本中,我們創建了一個表來演示一些基本查詢。

以下示例顯示瞭如何查詢表:

SELECT TOP 10 * FROM Northwind.dbo.Customers 
ORDER BY CompanyName

SELECT TOP 10 * FROM Pubs.dbo.Authors
ORDER BY City
 

將選擇Customer 表的前10條記錄,這些記錄由Northwind 數據庫中的CompanyName 列排序(這是Microsoft的示例數據庫之一,可以從此處下載):

Northwind數據庫查詢

注意 Use Northwind; 更改所有後續查詢的默認數據庫。您仍然可以使用[Database]形式的完全限定語法來引用數據庫。[Schema]。[Table]:

-- descending order
SELECT TOP 10 [Date] FROM dbo.MyLogTable
ORDER BY [Date] DESC
 

如果您要查詢來自不同數據庫的數據,這將非常有用。請注意,在“之間”指定的dbo 稱為模式,需要在使用完全限定語法時指定。您可以將其視為數據庫中的文件夾。 dbo 是默認架構。可以省略默認模式。需要指定所有其他用戶定義的模式。

如果數據庫表包含名為保留字的列,例如Date ,則需要將列名括在括號中,如下所示:

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
order by "Date" desc 
 

如果列名在其名稱中包含空格(不建議這樣做),則同樣適用。另一種語法是使用雙引號而不是方括號,例如:

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
where UserId='johndoe'
order by "Date" desc 
 

相當但不常用。注意雙引號和單引號之間的區別:單引號用於字符串,即

SELECT TOP 10 * FROM Northwind.dbo.Customers 
WHERE CompanyName LIKE N'AL%'
ORDER BY CompanyName
 

是一種有效的語法。請注意,T-SQL具有NChar和NVarchar數據類型的N 前綴,例如

SELECT TOP 5 Territories.*, 
    Regions.RegionDescription 
FROM Territories 
INNER JOIN Region 
    ON Territories.RegionID=Region.RegionID
ORDER BY TerritoryDescription
 

返回所有公司名稱以AL 開頭的公司( % 是外卡,使用它,就像在DOS命令行中使用星號一樣,例如DIR AL* )。對於LIKE ,有幾個可用的通配符,請查看此處以了解更多詳細信息。

加盟

如果要查詢一個表中不存在但在多個表中不存在的字段,則聯接很有用。例如:您想查詢Northwind 數據庫中Region 表中的所有列。但是您注意到還需要RegionDescription ,它存儲在另一個表Region 。但是,有一個公共密鑰RgionID 可用於在單個查詢中組合此信息,如下所示( Top 5 只返回前5行,省略它以獲取所有行):

<TableName> [as] <alias>
 

將顯示Territories 所有列以及RegionRegionDescription 列。結果按TerritoryDescription 排序。

表別名

當您的查詢需要引用兩個或更多表時,您可能會發現使用表別名很有用。表別名是對可用於代替完整表名的表的簡寫引用,並且可以減少鍵入和編輯。使用別名的語法是:

SELECT TOP 5 t.*, 
    r.RegionDescription 
FROM Territories t
INNER JOIN Region r 
    ON t.RegionID = r.RegionID
ORDER BY TerritoryDescription
 

as 是一個可選關鍵字。例如,以前的查詢可以重寫為:

SELECT e.*, 
    s.Name as SupervisorName -- Rename the field for output
FROM Employee e
INNER JOIN Employee s
    ON e.SupervisorId = s.EmployeeId
WHERE e.EmployeeId = 111
 

對於查詢中的所有表,別名必須是唯一的,即使您使用同一個表兩次。例如,如果您的Employee表包含SupervisorId字段,則可以使用此查詢返回員工及其主管的姓名:

SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees
UNION
SELECT ContactName, Address, City FROM Customers
 

工會

正如我們之前看到的,Join會添加來自不同表源的列。但是,如果要組合來自不同來源的行,該怎麼辦?在這種情況下,您可以使用UNION。假設您正計劃參加一個聚會,並且不僅希望邀請員工,還希望邀請客戶。然後你可以運行這個查詢來做到這一點:

DECLARE @Region TABLE
(
  RegionID int, 
  RegionDescription NChar(50)
)
 

它將在一個表中返回員工和客戶的姓名,地址和城市。請注意,重複的行(如果應該有的話)會自動消除(如果您不想這樣做,請改用UNION ALL )。列號,列名,順序和數據類型必須匹配所有屬於聯合的select語句 - 這就是第一個SELECT將Employee中的FirstNameLastName 組合成ContactName

表變量

如果您需要處理臨時數據(特別是在存儲過程中),使用表變量可能很有用:“真實”表和表變量之間的區別在於它只存在於內存中以進行臨時處理。

例:

INSERT INTO @Region values(3,'Northern')
INSERT INTO @Region values(4,'Southern')
 

在內存中創建一個表。在這種情況下, @ 前綴是必需的,因為它是一個變量。您可以執行上面提到的所有DML操作來插入,刪除和選擇行,例如

INSERT INTO @Region
SELECT * FROM dbo.Region WHERE RegionID>2;
 

但通常情況下,你會根據真實的表格來填充它

SELECT * FROM Territories t
JOIN @Region r on t.RegionID=r.RegionID
 

這將讀取真實表dbo.Region 的過濾值並將其插入到內存表@Region - 它可以用於進一步處理。例如,您可以在類似的連接中使用它

USE Northwind;
GO
SELECT TOP 10 * FROM Customers 
ORDER BY CompanyName
 

在這種情況下,將返回所有NorthernSouthern 地區。更多詳細信息可以在這裡找到。如果您有興趣閱讀有關該主題的更多信息,請在此處討論臨時表。

注意:如果表變量中的數據行數小於100,Microsoft僅建議使用表變量。如果要處理大量數據,請使用臨時表或臨時表。

打印

向輸出控制台顯示消息。使用SQL Server Management Studio,它將顯示在消息選項卡中,而不是結果選項卡中:

PRINT 'Hello World!';
 

檢索基本服務器信息

SELECT @@VERSION
 

返回在實例上運行的MS SQL Server的版本。

SELECT @@SERVICENAME
 

返回MS SQL Server實例的名稱。

SELECT serverproperty('ComputerNamePhysicalNetBIOS');
 

返回MS SQL Server正在運行的Windows服務的名稱。

SELECT * FROM fn_virtualservernodes();
 

返回運行SQL Server的計算機的物理名稱。用於標識故障轉移群集中的節點。

SELECT @@SERVERNAME
 

在故障轉移群集中,返回可運行SQL Server的每個節點。如果不是集群,它什麼也不返回。

從表中選擇所有行和列

句法:

SELECT *
FROM table_name
 

使用星號運算符* 可用作選擇表中所有列的快捷方式。還將選擇所有行,因為此SELECT 語句沒有WHERE 子句,以指定任何過濾條件。

如果您向表中添加別名,這也會以相同的方式工作,例如在這種情況下為e

SELECT e.*, d.DepartmentName
FROM Employees AS e
    INNER JOIN Department AS d 
        ON e.DepartmentID = d.DepartmentID
 

或者,如果要從特定表中選擇全部,可以使用別名+“。*”:

SELECT * FROM [server_name].[database_name].[schema_name].[table_name]
 

也可以使用完全限定名稱訪問數據庫對象:

SELECT col1, col2, col3
FROM table_name
 

不一定建議這樣做,因為更改服務器和/或數據庫名稱會導致使用完全限定名稱的查詢由於無效的對象名稱而不再執行。

請注意,如果查詢分別在單個服務器,數據庫和模式上執行,則在許多情況下可以省略table_name 之前的字段。但是,數據庫通常具有多個模式,在這些情況下,在可能的情況下不應省略模式名稱。

警告:在生產代碼或存儲過程中使用SELECT * 可能會導致以後出現問題(因為新列被添加到表中,或者如果列重新排列在表中),特別是如果您的代碼對列的順序做出簡單的假設,或返回的列數。因此,總是在SELECT語句中為生產代碼顯式指定列名更安全。

SELECT *
FROM Employees AS e
 

選擇與條件匹配的行

通常,語法是:

SELECT <column names>
FROM <table name>
WHERE <condition>
 

例如:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith' AND (City = 'New York' OR City = 'Los Angeles')
 

條件可能很複雜:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith'
 

TRUNCATE TABLE

TRUNCATE TABLE Helloworlds 
 

此代碼將刪除表Helloworlds中的所有數據。截斷表幾乎類似於Delete from Table 代碼。區別在於你不能在帶有Truncate的where子句中使用。 Truncate表被認為比刪除更好,因為它使用較少的事務日誌空間。

請注意,如果存在標識列,則會將其重置為初始種子值(例如,自動遞增的ID將從1重新啟動)。如果標識列用作另一個表中的外鍵,則可能導致不一致。

更新所有行

一種簡單的更新形式是遞增表格給定字段中的所有值。為此,我們需要定義字段和增量值

以下是將Score 字段增加1(在所有行中)的示例:

UPDATE Scores
SET score = score + 1  
 

這可能很危險,因為如果您不小心對錶中的所有行的UPDATE進行特定行的更新,則可能會損壞您的數據。

更新特定行

UPDATE HelloWorlds
SET HelloWorld = 'HELLO WORLD!!!'
WHERE Id = 5
 

上面的代碼用“HELLO WORLD !!!”更新字段“HelloWorld”的值對於HelloWorlds表中“Id = 5”的記錄。

注意:在更新語句中,建議使用“where”子句以避免更新整個表,除非並且直到您的要求不同。

使用事務安全地更改數據

無論何時更改數據,都可以在數據操作語言(DML)命令中將更改包裝在事務中。 DML包括UPDATETRUNCATEINSERTDELETE 。您可以確保更改正確數據的方法之一是使用事務。

DML更改將鎖定受影響的行。當您開始交易時,您必須結束交易,否則DML中所有正在更改的對象將由開始交易的人保持鎖定狀態。您可以使用ROLLBACKCOMMIT 結束事務。 ROLLBACK 將事務中的所有內容返回到其原始狀態。 COMMIT 將數據置於最終狀態,在沒有其他DML語句的情況下,您無法撤消更改。

例:

--Create a test table

USE [your database]
GO
CREATE TABLE test_transaction (column_1 varchar(10))
GO

INSERT INTO 
 dbo.test_transaction
        ( column_1 )
VALUES
        ( 'a' )

BEGIN TRANSACTION --This is the beginning of your transaction

UPDATE dbo.test_transaction
SET column_1 = 'B'
OUTPUT INSERTED.*
WHERE column_1 = 'A'
  

ROLLBACK TRANSACTION  --Rollback will undo your changes
           --Alternatively, use COMMIT to save your results

SELECT * FROM dbo.test_transaction   --View the table after your changes have been run

DROP TABLE dbo.test_transaction
 

筆記:

  • 這是一個簡化的示例 ,不包括錯誤處理。但任何數據庫操作都可能失敗,因此拋出異常。 下面是一個示例,說明這種必需的錯誤處理方式。不應該在沒有 錯誤處理程序的情況下使用事務,否則可能會使事務處於未知狀態。
  • 根據隔離級別 ,事務會對要查詢或更改的數據進行鎖定。您需要確保事務長時間不運行,因為它們將鎖定數據庫中的記錄,並可能導致與其他並行運行事務的死鎖 。保持封裝在事務中的操作盡可能短,並最大限度地減少對鎖定的數據量的影響。

Stats

2257 Contributors: 63
Sunday, August 6, 2017
許可下: CC-BY-SA

不隸屬於 Stack Overflow
Rip Tutorial: info@zzzprojects.com

下載電子書