SQLServer常用语句(手记)
|
**** 整理的有点乱，将就看下 ******* 创建数据库 CREATE DATABASE [Credit] ON ?PRIMARY ( NAME = N'Credit',FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATACredit.mdf',SIZE = 10240KB,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB ) FILEGROUP [FG1]--多个文件组的时候 ( NAME = N'Credit1',FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATACredit1.mdf',SIZE = 3072KB,FILEGROWTH = 1024KB ) LOG ON ? ( NAME = N'Credit_log',FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATACredit_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:databaseSfis_DW_init.bak' 修改Tempdb路径 sp_helpdb Tempdb ? ?(查看路径) ALTER DATABASE TEMPDB MODIFY FILE (NAME='TEMPDEV',FILENAME='NEWPATHtempdb.mdf',SIZE=500MB)
ALTER DATABASE TEMPDB MODIFY FILE (NAME='TEMPLOG',FILENAME='NEWPATHtemplog.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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATACredit_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--查询锁 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |