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”