Microsoft SQL Server/表操作

介紹

資料定義語言 (DDL) 和資料操縱語言 (DML) 遵守 SQL-86 標準。但是，除了 `SELECT`、`UPDATE`、`INSERT` 請求外，我們還發現自 2008 版本開始出現 `MERGE` ^[1]。

建立表

在 SSMS 中，右鍵單擊資料庫的 "表" 資料夾，可以新增一個表。

右鍵單擊特定表，可以選擇以下操作：

修改表結構（新增列，修改型別）。
選擇其前 1000 條記錄 ( `TOP` )，或最後 1000 條記錄 ( `ORDER BY id DESC` )。
編輯其前 200 條記錄。

否則，在 SQL 中，您需要輸入以下內容^[2]

CREATE TABLE [dbo].[table1] (
	[Nom] [varchar](250) NULL,
	[Prénom] [varchar](250) NULL,
	[identifiant] [int] IDENTITY(1,1) NOT NULL)

填充前幾列^[3]

INSERT INTO table1 VALUES ('Doe', 'Jane', 1), ('Doe', 'John', 2)

要針對其他列，必須明確指定欄位。例如，在填充了名字後，姓氏將為空

INSERT INTO table1 (First_name, id) VALUES ('Jane', 3)

從另一個表

INSERT INTO table1 (First_name, id)
SELECT First_name, ID FROM table2

更新

UPDATE table1
SET First_name = 'Janet'
WHERE ID = 3

UPDATE table1
SET First_name = t2.First_name, Last_name = t2.Last_name
FROM table1 t1
INNER JOIN table2 t2 on t1.ID = t2.ID_t1

建立索引

軟體中的 PK 縮寫代表 "主鍵"。

要建立外部索引鍵，請下拉表，在 "鍵" 選單中右鍵單擊，選擇 "新建外部索引鍵..."，所有表的外部索引鍵列表將顯示在一個小視窗中（預設情況下命名為 "FK_..."，代表 "外部索引鍵"）。

在 "常規" 中，"表和列規範" 中，單擊 "..." 選擇要連結的表及其欄位。

新增唯一標識

通常每個表應該至少擁有一個唯一標識（主鍵）。但是，無法修改現有列以賦予該鍵所需的 `AUTOINCREMENT` 屬性。

因此，要新增一個唯一標識

ALTER TABLE table1 ADD id int NOT NULL IDENTITY (1,1) PRIMARY KEY

複製表

下面的選擇將克隆一個具有相同欄位大小的表

SELECT * INTO table2 FROM table1

考慮到系統資料庫 `master` 中的 `spt_values` 表包含一個連續的 `number` 欄位，因此可以使用此計數器生成表

SELECT DISTINCT number 
FROM master.dbo.spt_values
WHERE number BETWEEN 2 AND 10

其中

SELECT DISTINCT 'Line ' + convert(varchar, number, 112) as N into #BlankTable
FROM master.dbo.spt_values
WHERE number BETWEEN 2 AND 10

SELECT * from #BlankTable

N
Line 10
Line 2
Line 3
Line 4
Line 5
Line 6
Line 7
Line 8
Line 9

匯入表

從陣列（例如，以 PC DOS 編碼的 CSV 形式轉換的 Excel 或 Calc）匯入資料作為新表 ^[4]

CREATE TABLE Array_to_Table (
  [Champ1] [varchar](500) NULL,
  [Champ2] [varchar](500) NULL,
  [Champ3] [varchar](500) NULL
)
GO
BULK INSERT Array_to_Table
FROM 'C:\Users\superadmin\Desktop\Array1.csv'
WITH (
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n'
)
GO
-- Displays the result
SELECT * from Array_to_Table
GO

刪除表

要刪除整個表（資料和結構）

DROP TABLE table1

要截斷表，即僅保留表頭和列型別，刪除所有記錄

TRUNCATE TABLE table1
--or
DELETE table1

要從表中刪除特定行

DELETE table1 WHERE Condition

注意：在 `WHERE` 之前新增 `OUTPUT deleted.*` 可以獲得已刪除的內容，而不是已刪除的行數。}}

查詢表

要查詢我們知道確切名稱的表，在伺服器的所有資料庫中

sp_MSforeachdb 'USE ?
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[MyTable]'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
  PRINT ''Table found in the database: ?''
END'

查詢所有表

SSMS 10 沒有提供任何查詢功能，就像您在例如 MySQL 的 phpMyAdmin 中找到的那樣。

表查詢

此指令碼遍歷每個資料庫，返回名稱包含指定字串（在結尾）的表。

ALTER Proc FindTable
@TableName nVarchar(50)
As
/*
Purpose : Search for a Table in all databases
Author : Sandesh Segu
Date : 17th July 2009
Version : 1.0
More Scripts  : http://sanssql.blogspot.com
*/
ALTER Table #temp (DatabaseName varchar(50),SchemaName varchar(50),TableName varchar(50))

Declare @SQL Varchar(500)
Set @SQL='Use [?] ;
if exists(Select name from sys.tables where name like '''+@TableName+''') 
insert into #temp 
Select ''?'' AS DatabaseName ,SS.Name AS SchemaName ,ST.Name AS TableName from sys.tables as ST , sys.schemas SS 
where ST.Schema_ID=SS.Schema_ID and ST.name like '''+@TableName+''''

EXEC sp_msforeachdb @SQL

Select * from #temp

Drop table #temp
GO

/* 
Usage: If the exact table name is known then specify the table name else include the wild cards
EXEC FindTable 'Employee'*/
EXEC FindTable '%String of characters to research%'

查詢值

在所有表中查詢欄位值需要一些時間^[5]

CREATE TABLE #result(
  id      INT IDENTITY,
  tblName VARCHAR(255),
  colName VARCHAR(255),
  qtRows  INT
)
go

DECLARE @toLookFor VARCHAR(255)
SET @toLookFor = '%String of characters%'

DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
  '[' + usr.name + '].[' + tbl.name + ']' AS tblName,
  '[' + col.name + ']' AS colName,
  LOWER(typ.name) AS typName
FROM
  sysobjects tbl
    INNER JOIN(
      syscolumns col
        INNER JOIN systypes typ
        ON typ.xtype = col.xtype
    )
    ON col.id = tbl.id
    --
    LEFT OUTER JOIN sysusers usr
    ON usr.uid = tbl.uid

WHERE tbl.xtype = 'U'
  AND LOWER(typ.name) IN(
        'char', 'nchar',
        'varchar', 'nvarchar',
        'text', 'ntext'
      )
ORDER BY tbl.name, col.colorder
--
DECLARE @tblName VARCHAR(255)
DECLARE @colName VARCHAR(255)
DECLARE @typName VARCHAR(255)

DECLARE @sql  NVARCHAR(4000)
DECLARE @crlf CHAR(2)

SET @crlf = CHAR(13) + CHAR(10)

OPEN cCursor
FETCH cCursor
INTO @tblName, @colName, @typName

WHILE @@fetch_status = 0
BEGIN
  IF @typName IN('text', 'ntext')
  BEGIN
    SET @sql = ''
    SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
    SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
    SET @sql = @sql + 'FROM ' + @tblName + @crlf
    SET @sql = @sql + 'WHERE PATINDEX(''%'' + @toLookFor + ''%'', ' + @colName + ') > 0' + @crlf
  END
  ELSE
  BEGIN
    SET @sql = ''
    SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
    SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
    SET @sql = @sql + 'FROM ' + @tblName + @crlf
    SET @sql = @sql + 'WHERE ' + @colName + ' LIKE ''%'' + @toLookFor + ''%''' + @crlf
  END

  EXECUTE sp_executesql
            @sql,
            N'@tblName varchar(255), @colName varchar(255), @toLookFor varchar(255)',
            @tblName, @colName, @toLookFor

  FETCH cCursor
  INTO @tblName, @colName, @typName
END

SELECT *
FROM #result
WHERE qtRows > 0
ORDER BY id
GO

DROP TABLE #result
go

參考資料

[1] ttps://msdn.microsoft.com/en-us/library/bb510625.aspx

[2] ttps://msdn.microsoft.com/en-us/library/ms174979.aspx

[3] ttps://msdn.microsoft.com/en-us/library/ms174335.aspx

[4] ttps://msdn.microsoft.com/en-us/library/ms188365.aspx

[5] ttp://stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-db

[1]

[2]

[3]

[4]

[5]