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]