SQLServer T-SQL 部分查詢語句歸納

  • 2019 年 12 月 4 日
  • 筆記

數據庫/表操作

獲取所有用戶名:

Select name FROM Sysusers where status='2' and islogin='1'
  • islogin=』1』 :表示帳戶
  • islogin=』0』 :表示角色
  • status=』2』 :表示用戶帳戶
  • status=』0』 :表示糸統帳戶

獲取所有數據庫名:

Select Name FROM Master..SysDatabases orDER BY Name

獲取所有表名:

Select Name FROM DatabaseName..SysObjects Where XType='U' orDER BY Name  SELECT TABLE_NAME FROM dbName.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';  -- dbName 數據庫名稱
  • XType=』U』 :表示所有用戶表;
  • XType=』S』 :表示所有系統表;

獲取所有字段名:

Select Name FROM SysColumns Where id=Object_Id('表名')

查詢表的字段定義:

select * from information_schema.columns where table_name = '表名';

獲取數據庫所有類型:

select name from systypes

獲取主鍵字段:

Select  name FROM SysColumns Where id=Object_Id('表名') and colid=(select top 1 keyno from sysindexkeys where id=Object_Id('表名'))

數據庫多表查詢

Join連接

SQL JOIN 用於把來自兩個或多個表的行結合起來。

SQL JOIN 類型：

  • INNER JOIN：如果表中有至少一個匹配，則返回行
  • LEFT JOIN：即使右表中沒有匹配，也從左表返回所有的行
  • RIGHT JOIN：即使左表中沒有匹配，也從右表返回所有的行
  • FULL JOIN：只要其中一個表中存在匹配，則返回行

INNER JOIN 內連接

SELECT testOrders.OrderID, Customers.CustomerName, testOrders.OrderDate  FROM testOrders  INNER JOIN Customers  ON testOrders.CustomerID=Customers.CustomerID;

通過某一約束條件 （ON table.XXX = table2.XXX） 進行關聯，如果表中有至少一個匹配，則返回行，輸出查詢的字段。

SQL左鏈接

LEFT JOIN關鍵字返回左表（表1）中的所有行，即使在右表（表2）中沒有匹配。如果在正確的表中沒有匹配，結果是NULL。

SELECT Customers.CustomerName, testOrders.OrderID  FROM Customers  LEFT JOIN testOrders  ON Customers.CustomerID = testOrders.CustomerID

LEFT JOIN 關鍵字通過某一條件 （ON Customers.CustomerID = testOrders.CustomerID）返回左表（Customers）中的所有行，即使在右邊表（testOrders）中沒有匹配。

RIGHT JOIN 右連接

SQL右鏈接 RIGHT JOIN 關鍵字返回右表（table2）的所有行，即使在左表（table1）上沒有匹配。如果左表沒有匹配，則結果為NULL。

SELECT testOrders.OrderId, testOrders.OrderDate, Customers.CustomerName  FROM testOrders  RIGHT JOIN Customers  ON Customers.CustomerID = testOrders.CustomerID

RIGHT JOIN 關鍵字返回右表（Employees）的所有行，即使在左表（Orders）中沒有匹配。

FULL OUTER JOIN 完整外部連接

當左（表1）或右（表2）表記錄匹配時，FULL OUTER JOIN關鍵字將返回所有記錄。

SELECT * FROM testOrders;  SELECT * FROM Customers;    SELECT Customers.CustomerName, testOrders.OrderID  FROM Customers  FULL OUTER JOIN testOrders ON Customers.CustomerID=testOrders.CustomerID  ORDER BY Customers.CustomerName;

FULL OUTER JOIN關鍵字返回左表（Customers）中的所有行，以及右表（testOrders）中的所有行。如果 「Customers」中的行中沒有」testOrders」中的匹配項，或者」testOrders」中的行中沒有 「Customers」中的匹配項，那麼這些行也會列出(沒有的字段為NULL)。

Self JOIN自連接

自聯接是一種常規聯接，但表本身是連接的。

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.Country  FROM Customers A, Customers B  WHERE A.CustomerID <> B.CustomerID  AND A.Country = B.Country  ORDER BY A.Country;

匹配出同一個國家的公司。

SQL 索引

索引是一種特殊的查詢表，可以被數據庫搜索引擎用來加速數據的檢索。 索引能夠提高 SELECT 查詢和 WHERE 子句的速度，但是卻降低了包含 UPDATE 語句或 INSERT 語句的數據輸入過程的速度**。 索引的創建與刪除不會對表中的數據產生影響。

CREATE INDEX 命令：

CREATE INDEX命令的基本語法如下：

CREATE INDEX index_name ON table_name;

單列索引：

單列索引基於單一的字段創建，其基本語法如下所示：

CREATE INDEX index_name  ON table_name (column_name);

唯一索引：

唯一索引不止用於提升查詢性能，還用於保證數據完整性。唯一索引不允許向表中插入任何重複值。其基本語法如下所示：

CREATE UNIQUE INDEX index_name  on table_name (column_name);

聚簇索引：

聚簇索引在表中兩個或更多的列的基礎上建立。其基本語法如下所示：

CREATE INDEX index_name  on table_name (column1, column2);

創建單列索引還是聚簇索引，要看每次查詢中，哪些列在作為過濾條件的 WHERE 子句中最常出現。

如果只需要一列，那麼就應當創建單列索引。如果作為過濾條件的 WHERE 子句用到了兩個或者更多的列，那麼聚簇索引就是最好的選擇。

隱式索引：

隱式索引由數據庫服務器在創建某些對象的時候自動生成。例如，對於主鍵約束和唯一約束，數據庫服務器就會自動創建索引。

DROP INDEX 命令：

索引可以用 SQL DROP 命令刪除。刪除索引時應當特別小心，數據庫的性能可能會因此而降低或者提高。

其基本語法如下：

DROP INDEX table_name.index_name;

什麼時候應當避免使用索引？

儘管創建索引的目的是提升數據庫的性能，但是還是有一些情況應當避免使用索引。下面幾條指導原則給出了何時應當重新考慮是否使用索引：

  • 小的數據表不應當使用索引；
  • 需要頻繁進行大批量的更新或者插入操作的表；
  • 如果列中包含大數或者 NULL 值，不宜創建索引；
  • 頻繁操作的列不宜創建索引。

行轉列： PIVOT

在數據庫操作中，有些時候我們遇到需要實現「行轉列」的需求，例如:

  SELECT [Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]  FROM WEEK_INCOME  PIVOT  (      SUM(INCOME) for [week] in([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])  )TBL
  • SUM(INCOME) for [week] in([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])是PIVOT第一步驟，也是核心的地方，進行行轉列操作。聚合函數SUM表示你需要怎樣處理轉換後的列的值，是總和(sum)，還是平均(avg)還是min,max等等。例如如果week_income表中有兩條數據並且其week都是「星期一」，其中一條的income是1000,另一條income是500，那麼在這裡使用sum，行轉列後「星期一」這個列的值當然是1500了。後面的for [week] in([星期一],[星期二]…)中 for [week]就是說將week列的值分別轉換成一個個列，也就是「以值變列」。但是需要轉換成列的值有可能有很多，我們只想取其中幾個值轉換成列，那麼怎樣取呢？就是在in裏面了，比如我此刻只想看工作日的收入，在in裏面就只寫「星期一」至「星期五」（注意，in裏面是原來week列的值,」以值變列」）。總的來說，SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])這句的意思如果直譯出來，就是說：將列[week]值為」星期一」,」星期二」,」星期三」,」星期四」,」星期五」,」星期六」,」星期日」分別轉換成列，這些列的值取income的總和。
  • FROM WEEK_INCOME這裡是PIVOT第二步驟(準備原始的查詢結果，因為PIVOT是對一個原始的查詢結果集進行轉換操作，所以先查詢一個結果集出來)這裡可以是一個select子查詢，但為子查詢時候要指定別名，否則語法錯誤
  • SELECT [Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]這裡是PIVOT第三步（選擇行轉列後的結果集的列）這裡可以用「*」表示選擇所有列，也可以只選擇某些列(也就是某些天)
  • TBL別名 不能缺省

行轉列也可以使用 CONVERT 來實現，兩種方法均可以參考：重溫SQL——行轉列，列轉行

數據庫事務

事務是單個工作單元。 如果某一事務成功，則在該事務中進行的所有數據修改均會提交，成為數據庫中的永久組成部分。 如果事務遇到錯誤且必須取消或回滾，則所有數據修改均被清除。

SQL Server 以下列事務模式運行：

  • 自動提交事務,每條單獨的語句都是一個事務。
  • 顯式事務,每個事務均以 BEGIN TRANSACTION 語句顯式開始，以 COMMIT 或 ROLLBACK 語句顯式結束。
  • 隱式事務,在前一個事務完成時新事務隱式啟動，但每個事務仍以 COMMIT 或 ROLLBACK 語句顯式完成。
  • 批處理級事務,只能應用於多個活動結果集 (MARS)，在 MARS 會話中啟動的 Transact-SQL 顯式或隱式事務變為批處理級事務。 當批處理完成時沒有提交或回滾的批處理級事務自動由 SQL Server 進行回滾。

一個數據庫事務主要有三部分組成：

  • 開始事物：begin transaction
  • 提交事物：commit transaction
  • 回滾事物：rollback transaction

示例：

begin transaction    declare @errorSum int      --定義局部變量    set @errorSum=0  --初始化臨時變量    update bank set currentMoneycurrentMoney= currentMoney-1000 where customerName='張三'    set @errorSum=@errorSum+@@error    --累計是否有錯誤    update bank set currentMoneycurrentMoney= currentMoney+1000 where customerName='李四'    set @errorSum=@errorSum+@@error    --累計是否有錯誤    if @errorSum<>0     --如果有錯誤    begin    rollback transaction    end    else    begin    commit  transaction    end    go

SQL SERVER的鎖

VirMach 便宜 VPS

QNews

QNews