SQLServer常用语句(手记)

概述****** 整理的有点乱，将就看下 ******* 创建数据库 CREATE DATABASE [Credit] ON  PRIMARY ( NAME = N'Credit', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Credit.mdf' , SIZE = 1024

**** 整理的有点乱，将就看下 *******

创建数据库

CREATE DATABASE [Credit] ON  PRIMARY

( name = N'Credit',filename = N'D:\Program files\Microsoft sql Server\MSsql10_50.MSsqlSERVER\MSsql\DATA\Credit.mdf',SIZE = 10240KB,MAXSIZE = UNliMITED,fileGROWTH = 1024KB )

fileGROUP [FG1]--多个文件组的时候

( name = N'Credit1',filename = N'D:\Program files\Microsoft sql Server\MSsql10_50.MSsqlSERVER\MSsql\DATA\Credit1.mdf',SIZE = 3072KB,fileGROWTH = 1024KB )

LOG ON  

( name = N'Credit_log',filename = N'D:\Program files\Microsoft sql Server\MSsql10_50.MSsqlSERVER\MSsql\DATA\Credit_1.ldf',MAXSIZE = 2048GB,simsun;">GO

创建表

CREATE table tmp01(

ID int not null,--primary key

name nvarchar(20) null,

CONSTRAINT PK_tmp01 PRIMARY KEYCLUSTERED

(ID ASC

))

检查数据库备份是否损坏

RESTORE VERIFYONLY from disk='f:\database\Sfis_DW_init.bak'

修改Tempdb路径

sp_helpdb Tempdb    (查看路径)

ALTER DATABASE TEMPDB MODIFY file

(name='TEMPDEV',filename='NEWPATH\tempdb.mdf',SIZE=500MB)

ALTER DATABASE TEMPDB MODIFY file

(name='TEMPLOG',filename='NEWPATH\templog.ldf',simsun;color:#E36C09;">重启sql Server

各种备份区别

完整备份，备份整个数据库，恢复模式必须为完整

事务备份，备份事务交易记录，恢复模式必须为完整

差异备份，恢复时不需要依备份顺序逐个恢复（如需恢复到星期三，先还原星期天的完整备份，再直接恢复星期三的差异备份）

增量备份，恢复时必须依备份顺序逐个恢复（如需恢复到星期三，先还原星期天的完整备份，然后还原星期一/二的备份，最后才恢复星期三的增量备份）

三种范式

第一范式：实体中的某个属性不能有多个值或者不能有重复的属性

第二范式：实体的属性完全依赖于主关键字(非主属性非部分依赖于主关键字)

第三范式：每个非关键字列都独立于其他非关键字列，并依赖于关键字，第三范式指数据库中不能存在传递函数依赖关系

数据库的备份与恢复(备份和恢复时，可以用with pasword or with mediapassword 选项来设置密码)

BACKUP DATABASE [Credit] TO  disK = N'F:\Credit.bak' WITH NOFORMAT,NOINIT, name = N'Credit-完整 数据库 备份',SKIP,norEWIND,NOUNLOAD, STATS = 10    --完整备份

GO

BACKUP LOG [Credit] TO  disK = N'F:\Credit.trn' WITH NOFORMAT, name = N'Credit-事务日志  备份', STATS = 10    --事务日志备份

RESTORE DATABASE [Credit] FROM  disK = N'F:\Credit.bak' WITH  file = 1, norECOVERY, NOUNLOAD, REPLACE, STATS = 10--可以还原事务日记 WITH norECOVERY

RESTORE DATABASE [Credit] FROM  disK = N'F:\Credit_backup_2012_07_06_011501_0146598.trn' WITH norECOVERY

RESTORE DATABASE [Credit] FROM  disK = N'F:\Credit_backup_2012_07_06_021503_5289873.trn' WITH RECOVERY,StopAT='2012-07-06 02:00:00'    --锁定到哪一点

还原没有数据库的前提，需要还原的时候移动文件

MOVE 'Credit' TO 'D:\Program file\...\Credit.mdf',simsun;">MOVE 'Credit_Log' TO 'D:\Program file\...\Credit_1.ldf',simsun;">norECOVERY, STATS = 10

***********************************************************************************************************************

数据库快照

Create Database Data_snapshot_Credit

ON

( name = 'Credit',   --这里的名称必须为实际数据库名称,simsun;Font-size:12px;color:#ff0000;">查出所对应的name名称(exec sp_helpdb 'Credit')

 filename = N'D:\Program files\Microsoft sql Server\MSsql10_50.MSsqlSERVER\MSsql\DATA\Credit_snapshot.snap'

AS Snapshot of Credit

DROP DATABASE Data_snapshot_Credit    --删除快照

RESTORE DATABASE Credit FROM Database_snapshot = 'Data_snapshot_Credit'    --利用快照恢复数据库

创建用户名，授权角色

USE [master]

CREATE LOGIN [CreditUser] WITH PASSWORD=N'biostarsfis',DEFAulT_DATABASE=[Credit],CHECK_EXPIRATION=OFF,CHECK_POliCY=OFF

USE [Credit]

CREATE USER [CreditUser] FOR LOGIN [CreditUser]

EXEC sp_addrolemember N'db_owner',N'CreditUser'

创建一个角色，并授权

CREATE RolE TestRole

Grant Create table,create procedure,create vIEw to TestRole

Grant select,insert,execute,alter to TestRole

Create LOGIN TestLogin With password='samis'

Create USER TestLogin for TestLogin    --默认用户同登录名

EXEC sp_addrolemember 'TestRole','TestLogin'

创建外键

ALTER table [dbo].[T_asuspacksnandliaohao]  WITH CHECK ADD  CONSTRAINT [FK_T_asuspacksnandliaohao_T_asuspackliaohao] FOREIGN KEY([liaohao])

REFERENCES [dbo].[T_asuspackliaohao] ([code])

ON UPDATE CASCADE    --级联更新&删除

ON DELETE CASCADE

修改数据库名字

sp_renamedb 'old_name','new_name'

表维护相关

ALTER table T_asuspackliaohao ALTER ColUMN liaohao nvarchar(50)    --修改列长度

ALTER table tmp01 ADD name nvarchar(20)    --增加列

EXEC sp_rename 'tmp01.name','name1','column'    --修改列名字(修改表名：EXEC sp_rename 'tmp01','tmp02')

ALTER table tmp01 DROP ColUMN name     ----删除列

Alter table tmp01 add primary key(ID)     --添加主键,删除：Alter table tmp01 drop primary key(ID)

ALTER table tmp01 ADD CONSTRAINT CK_tmp01 CHECK (ID between and 100)--CK_Persion CHECK (SEX IN ('男','女'))

ALTER table tmp01 nocheck all    --删除所有Check约束

查询所有用户表

select name from sysobjects where xtype='u' and status>=0

查询表的所有字段名

select name from syscolumns where ID=object_ID('表名')

select name from syscolumns where ID in (select ID from sysobjects where type = 'u' and name = '表名')

查询所有存储过程

select name as 存储过程名称 from sysobjects where xtype='P'

quotename('aa') 生成的有效的标识符为 [aa]

启用自动统计信息创建功能

ALTER  DATABASE[Sfis] SET auto_CREATE_STATISTICS ON

ALTER DATABASE[Sfis] SET auto_UPDATE_STATISTICS ON/OFF (自动更新统计信息开关)

dbcc show_statistics('Sfis.dbo.t_productarrage',pk_t_productarrage) 查看统计信息

更新统计信息

update statistics Sfis.dbo.t_productarrage

链接服务器

exec sp_addlinkedserver   'ITSV ',' ','sqlolEDB ','远程服务器名或ip地址 '

exec sp_addlinkedsrvlogin  'ITSV ','false ',null,'用户名 ','密码 '

exec sp_dropserver  'ITSV ','droplogins ' --删除链接服务器

*****************************************************************************************************************

sql函数

CHARINDEX('-',@sql,1)--返回第1个'-'出现的位置

ROUND(123.4545,2)--返回123.45

CAST(@var AS int)--转换类型

REPLACE(@sql,':','-')--把@sql中的冒号转为-

DATEPART(MM,str)--返回整数

DATEname(MM,str)--返回字符

lower和upper函数--参数大小写转化

select CHAR(65)--返回A字母

LTRIM()--把字符串头部的空格去掉

RTRIM() --把字符串尾部的空格去掉

left() --取左边的字符串

RIGHT() --取左边的字符串

SUBSTRING()--返回从字符串左边第N个开始的M长度

SELECT CONVERT(nvarchar(20),GETDATE(),120)--指定格式返回当前日期，CONVERT()为转换格式

这个月第一天是哪一天

Select DATEADD(mm,DATEDIFF(mm,getdate()),0)

本周星期一是哪一天

Select DATEADD(wk,DATEDIFF(wk,simsun;">一年中的第一天

Select DATEADD(yy,DATEDIFF(yy,simsun;">季度中的第一天

Select DATEADD(qq,DATEDIFF(qq,simsun;">当天的半夜

Select DATEADD(dd,DATEDIFF(dd,simsun;">取上个星期天

select DATEADD(WEEK,DATEDIFF(WEEK,-8)

上个月的最后一天

select dateadd(dd,-1,DATEADD(mm,0))

这个月最后一天

SQL语句放在with as 里面，取一个别名，后面的查询就可以用它

with a as (select * from test)

创建非聚集索引

CREATE NONCLUSTERED INDEX [IX_Scanjob] ON [dbo].[Scanjob]

(

[test] ASC,simsun;">[yzm] ASC

INCLUDE ( [scandate])

唯一性非聚集索引

CREATE UNIQUE INDEX AK_Scanjob ON Scanjob(ID);

EXCEPT 与 INTERSECT

EXCEPT 去掉2个或多个结果集的重复值返回第一个结果集(查询A的数据并返回B中是否存在，如存在则去掉)

INTERSECT 查询2表的并集，2表都存在的数据

PIVOT 与 UNPIVOT 关系预算符将表值表达式更改为另外一个表

PIVOT 将表达式某一列中的唯一值转为输出的列，必要时对输出列进行聚集预算

UNPIVOT 与PIVOT相反，将表达式中的列转为值

INSERT INTO dbo.Stg_scanfinishstateunpvt

SELECT gdID,workstate,qty

FROM

(SELECT gdID,workstatefinishsl1,workstatefinishsl2

FROM dbo.Stg_scanfinishstate) p

UNPIVOT

(qty FOR workstate IN (

workstatefinishsl1,simsun;">) AS unpvt

创建规则

Create rule score

AS

@value Between 0 and 100

sp_blindrule score,'成绩信息.分数'--将规则绑定到，成绩表，分数栏位

同义词

CREATE SYNONYM tmp1 for tmp01

更改数据库&Server名字(reset)

SP_DROPSERVER 'My_Server'

SP_ADDSERVER 'MyServer',LOCAL

SELECT @@Servername

触发器(2个重要临时表，deleted,inserted)

CREATE TRIGGER tr_student

ON student

for update

if update

begin

 update borrowrecord set br.student=i.studentID

 from borrowrecord br,delete d,insert i

 where br.studentID=d.studentID

end

存储过程

CREATE PROCEDURE [dbo].[usp_CalculateallData]

BEGIN

DECLARE @StartDatenvarchar(20)

DECLARE @EndDatenvarchar(20)

DECLARE @ProcessDatenvarchar(20)

SET @StartDate = N'2010-10-01'

SET @EndDate = left(CONVERT(nvarchar(20),GETDATE()),120),10)

SET @ProcessDate = @StartDate

WHILE @ProcessDate <= @EndDate

PRINT N'EXECUTE dbo.usp_importMainLoop ''' + @ProcessDate + N''',''' + @ProcessDate + N''''

EXECUTE dbo.usp_importMainLoop @ProcessDate,@ProcessDate

SET @ProcessDate = left(CONVERT(nvarchar(20),CONVERT(datetime,@ProcessDate)),10)

END

标量值函数

CREATE FUNCTION [dbo].[fn_GetParameter]

@Parameter_name nvarchar(128)

RETURNS sql_variant

DECLARE @ReturnVal sql_variant

SELECT @ReturnVal = value FROM dbo.T_Parameters WHERE name = @Parameter_name

RETURN @ReturnVal

标值函数

CREATE FUNCTION [dbo].[fn_Data_EngDailyByPhase]

@strPhase nvarchar(10) = N''

RETURNS @Rettable table

factorycodenvarchar(10) NulL,simsun;">pdatenvarchar(10) NulL

BEGIN

SET @strPhase = UPPER(@strPhase)

IF @strPhase <> N'SMT' AND @strPhase <> N'DIP' AND @strPhase <> N'TEST'

RETURN--如果不满足条件，返回

INSERT INTO @RettableSELECT... FROM XXX

RETURN

游标

DECLARE cursor1 CURSOR LOCAL FOR

SELECT UPPER(FactoryCode),DBname FROM dbo.t_factory WHERE IsActive = 1ORDER BY Seq

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @FactoryCode,@DBname

WHILE @@FETCH_STATUS = 0

...

END

CLOSE cursor1

DEALLOCATE cursor1

显式事务

BEGIN TRANSACTION T1;

BEGIN TRY

DELETE FROM ...

INSERT INTO ...

COMMIT TRANSACTION T1;

END TRY

BEGIN CATCH

RolLBACK TRANSACTION T1;

END CATCH

DBCC SHOWCONfig('t_product')--显示资料表的存储情况

DBCC CHECKDB('Credit')--检查数据库

DBCC SHRINKDATABASE('Credit',50)--压缩数据库50%

DBCC SHRINKfile(1,100)--file_ID为1的压缩至100M，SELECT * FROM SYS.database_files

DBCC INDEXDEFRAG('0','tmp01','PK_tmp01')--重组当前表索引碎片整理

DBCC CLEANtable('0','tmp01')--回收可变长度空间

DBCC DBREINDEX('tmp01','PK_tmp01')--重新生成索引

DBCC CHECKtable('tmp01')--检查指定数据表

DBCC CHECKALLOC('Credit')--检查数据库的磁盘分配结构一致性

sp_who2

sp_who 'Credit'--查看用户连接状态

sp_monitor--查看与上一次执行的状态

sp_spaceused--查看数据库占用空间

sp_lock--查询锁

总结

以上是内存溢出为你收集整理的SQLServer常用语句(手记)全部内容，希望文章能够帮你解决SQLServer常用语句(手记)所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。