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
导入表
[编辑]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