Microsoft SQL Server/Table manipulation

維基教科書，自由的教學讀本

創建表[編輯]

使用SQL[1]: 

CREATE TABLE [dbo].[table1] (
	[Nom] [varchar](250) NULL,
	[Prénom] [varchar](250) NULL,
	[identifiant] [int] IDENTITY(1,1) NOT NULL,
    [constraint 约束名] primary key (字段名1, …)

修改表結構[編輯]

創建unique id[編輯]

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

alter table 表名 [add constraint 约束名] primary key(字段名1 ,… )

alter table 表名  add  [列名]  int identity(1,1) primary key

刪除主鍵[編輯]

SQL Server刪除主鍵約束與MySQL不同，需要主鍵的約束名稱才能刪除，SQL Server標準的刪除主鍵的腳本如下： 

ALTER TABLE 表名DROP CONSTRAINT 约束名

當然該語句只能刪除已知約束名的主鍵約束。

增加新列[編輯]

alter table table_name alter column column_name column_type  not null

查看指定表結構[編輯]

exec sp_help Reports

修改表名[編輯]

exec sp_rename 'Reports','Reports2'

刪除數據表[編輯]

不能刪除有外鍵約束的表。

drop table Reports

修改表欄位[編輯]

alter table Reports add NewColumn nchar(5) null --新增字段
alter table Reports alter column NewColumn nvarchar(10) --修改字段属性
exec sp_rename 'Reports.NewColumn','OldColumn'--修改字段名
alter table Reports drop column NewColumn --删除列

修改欄位約束[編輯]

alter table Reports add constraint Name_UQ unique(Name) --新增唯一约束（此非索引）
alter table Reports drop constraint Name_UQ --删除此约束

欄位索引[編輯]

MSSQL默認主鍵是聚集索引。一個表只能有一個聚集索引(Clustered Index)。 

create index NameIndex on Reports(Name) --新增普通索引（非聚集索引）
create unique index Name_UQ  on Reports(Name) --新增唯一索引（非聚集索引）

exec sp_helpindex Reports --查看表的索引
drop index Reports.NameIndex --删除索引

create nonclustered index NameFileIndex on Categories(CategoryName,PictureFile) --创建非聚集索引（组合索引）

當修改表結構時，sql server可能會彈出對話框：

不允許保存更改。您所做的更改要求刪除並重新創建以下表。您對無法重新創建的表進行了更改或者啟用了「阻止保存要求重新創建表的更改」選項。

解決方案：菜單欄->工具->選項->設計器->表設計器和資料庫設計器，右側面板，取消勾選「阻止保存要求重新創建表的更改」。

增刪改查[編輯]

插入表[2]: 

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

複製表[編輯]

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

導入表[編輯]

從Excel 或 Calc、CSV導入表[3] : 

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'

研究所有的表[編輯]

表的研究[編輯]

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%'

研究一個欄位值[編輯]

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. ↑ https://msdn.microsoft.com/en-us/library/ms174979.aspx
  2. ↑ https://msdn.microsoft.com/en-us/library/ms174335.aspx
  3. ↑ https://msdn.microsoft.com/en-us/library/ms188365.aspx
取自「https://zh.wikibooks.org/w/index.php?title=Microsoft_SQL_Server/Table_manipulation&oldid=169424」