SQLserver 语句总结

修改数据库定序：

?? 语法：ALTER DATABASE "&Database Name&"SET SINGLE_USER WITH ROLLBACK IMMEDIATE
?????? ALTER DATABASE "&Database Name&" COLLATE "&定序名称&"
?????? ALTER DATABASE "&Database Name&"SET MULTI_USER WITH ROLLBACK IMMEDIATE

? 如：ALTER DATABASE SODDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
???? ALTER DATABASE SODDB COLLATE Chinese_Taiwan_Stroke_CI_AI
???? ALTER DATABASE SODDB SET MULTI_USER WITH ROLLBACK IMMEDIATE
修改资料库栏位定序：

?? 语法：Alter Table "&TableName &"Alter Column "&ColumnName&" "&ColumnType&"

?? 如：Alter Table SODM87 Alter Column COMP_ID char(10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL

变动资料库中的栏位:

???? 添加栏位

??????? 语法： ALTER TABLE "&TableName&" ADD "&ColumnName&" DataType

??????? 如： ALTER TABLE SODM87 ADD CLASSNO_SDATE CHAR(9) NOT NULL? DEFAULT(' ')

?????修改栏位类型

??????? 语法：ALTER TABLE "&TableName&" ALTER COLUMN? "&ColumnName&" NewDataType、

????????如：ALTER TABLE SODM87 ALTER COLUMN APPLY_NAME?Varchar(10)? NULL

????修改栏位名称

?????? 语法：EXEC SP_RENAME 'TableName.ColumnName','NewColumnName','COLUMN'

?????? 如：将SODM87的栏位名称APPLY_NAME修改为APPLY_ID,

????? 语句为：EXEC SP_RENAME SODM87.APPLY_NAME','APPLY_ID','COLUMN'

??? 修改栏位名称和类型

????? 如果同时修改栏位名称和类型，则需要分两步进行，同时使用如上3、4两步即可。

????删除栏位

????? 语法：ALTER TABLE "&TableName&" DROP COLUMN "&ColumnName&"

??????如：ALTER TABLE SODM87 DROP COLUMN CLASSNO_SDATE

??? 添加索引

?????? 语法：CREATE? INDEX [索引名称] ON "&TableName&" (索引建) ON [PRIMARY]

?????? 如：CREATE? INDEX [IDX_MAIN_CNO_CODE] ON [dbo].[ SODM87]([MAIN_CNO_CODE]) ON [PRIMARY]

??? 添加主键

?????? 语法：ALTER Table "&TableName&" ADD CONSTRAINT"&PK Name&" PRIMARY KEY CLUSTERED (构成主键栏位组合 ) ON [PRIMARY]

?????? 如：ALTER TABLE [PM048TB] ADD? CONSTRAINT [PK_PM048TB] PRIMARY KEY CLUSTERED
???????????? (
?????????????? [USER_CODE] ASC,
?????????????? [LOG_DATE] ASC,
?????????????? [PROJ_CODE] ASC,
???????????????[TASK_KIND] ASC,
???????????????[TASK_CODE] ASC
?????????????) ON [PRIMARY]

??? 删除主键

?????? 语法：ALTER TABLE "&Table Name&"DROP CONSTRAINT "&PK Name&"

?????? 如：IF? EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[PM048TB]') AND name = N'PK_PM048TB')
??????? ALTER TABLE [dbo].[PM048TB] DROP CONSTRAINT [PK_PM048TB]

??? 修改table的主键

??????? 先删除当前主键，再添加主键

??? 数据库还原

?????? 语法：USE MASTER
??????????????? RESTORE DATABASE?数据库名称 FROM DISK=档案位置

??????????????? with replace;

????? ?如：USE MASTER
???????????? RESTORE DATABASE APWCDB FROM DISK='C:APWCDB.BAK'
???????????? with replace;

?????数据库重命名

????????? 语法：EXEC sp_dboption 'DatabaseName','Single User','TRUE'
???????????????? ? EXEC sp_renamedb 'DatabaseName','NewDatabaseName'
???????????????????EXEC sp_dboption 'NewDatabaseName','FALSE'

????????? 如：将MCTA改名为MCTADB
????????????????? EXEC sp_dboption 'MCTA','TRUE'
???????????????? ?EXEC sp_renamedb 'MCTA','MCTADB'
????????????????? EXEC sp_dboption 'MCTADB','FALSE'

????? 多table的删除

??????????? 语法：DELETE FROM TableA
??????????????????? FROM TableA TA INNER JOIN TableB TB
??????????????????? ON TA.KEY_1=TB.KEY_1 AND TA.KEY_2=TB.KEY_2 AND TA.KEY_3=TB.KEY_3

?????? 用TableA中资料更新TableB中资料

?????????? UPDATE TableA SET TEMP_A=B.TEMP_B,NOTE_A=B.NOTE_B
?????????? FROM TableB TB?
???????????WHERE TableA.KEY_1=TB.KEY_1 AND TableA.KEY_2=TB.KEY_2 AND TableA.KEY_3=TB.KEY_3
?????????? AND ……(其它条件)

???????链接两台数据库

????????? 语法：EXEC master.dbo.addlinkedserver @server=N'DB別名',@provider=N'SQLNCLI',@provstr=N'DRIVER={SQL Server};

?????????????????? SERVER=远端db机器名称或者IPP;UID=访问ID;PWD=访问密码;'

????????? 如：EXEC master.dbo.addlinkedserver @server=N'DB_20',@provstr=N'DRIVER={SQL Server};SERVER=192.168.1.30;UID=sa;PWD=sa;'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!