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