分类： 数据库笔记

create table Student  --学生成绩表
(
 id int,  --主键
 Grade int, --班级
 Score int --分数
);

insert into Student values(1,1,88); 
insert into Student values(2,1,66); 
insert into Student values(3,1,75); 
insert into Student values(4,2,30); 
insert into Student values(5,2,70); 
insert into Student values(6,2,80); 
insert into Student values(7,2,60); 
insert into Student values(8,3,90); 
insert into Student values(9,3,70); 
insert into Student values(10,3,80); 
insert into Student values(11,3,80)

select *,row_number() over(order by Score desc) as Sequence from Student

select *,row_number() over(partition by Grade order by Score desc) as Sequence from Student

select * from (
    select *,row_number() over(partition by Grade order by Score desc) as Sequence from Student
  ) T 
where T.Sequence<=1

select *,rank() over(partition by Grade order by Score desc) as Sequence from Student;

select * from (
    select *,rank() over(partition by Grade order by Score desc) as Sequence from Student
  ) T 
where T.Sequence<=2

DECLARE @SqlServerPath NVARCHAR(200) --数据库安装路径（数据文件夹）
DECLARE @BackUpPath NVARCHAR(200) --备份文件路径
DECLARE @BackUpFileName NVARCHAR(200) --备份文件名
DECLARE @DbName NVARCHAR(200) --db名称
DECLARE @LogicalDbName NVARCHAR(200) --逻辑db名称
DECLARE @LogicalLogName NVARCHAR(200) --逻辑log名称
DECLARE @BackUpFullFileName NVARCHAR(260) --备份文件全名
DECLARE @RESTORESQL NVARCHAR(2000) --备份数据库完整语句
SET @BackUpPath = 'D:\db\'; --备份文件路径
SET @SqlServerPath = 'D:\Software\SQL2014\DB\MSSQL12.MSSQLSERVER\MSSQL\DATA\'; --数据库安装路径（数据文件夹）
--add path \
if (@BackUpPath IS NOT NULL) AND LEN(@BackUpPath)>1 AND (RIGHT(@BackUpPath,1)<>'\')
BEGIN
 SET @BackUpPath=@BackUpPath+'\'
END
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp
CREATE TABLE #Temp (id INT IDENTITY,a INT,b INT,c INT)
DECLARE @fpath NVARCHAR(3)
SET @fpath=LEFT(@BackUpPath,3)
INSERT #Temp EXEC master..xp_fileexist @fpath
INSERT #Temp EXEC master..xp_fileexist @BackUpPath
--如果指定盘符有误不存在，则返回错误提示：
IF EXISTS(SELECT 1 FROM #Temp WHERE id=1 AND c=0)
BEGIN
    PRINT CHAR(10) + N'备份文件路径的盘符不存在，请重新输入!'
	GOTO ExitFLag
END
--如果不存在指定的文件夹，则创建:
ELSE IF EXISTS(SELECT 1 FROM #Temp WHERE b=0 AND id=2)
BEGIN
   PRINT CHAR(10) + N'备份文件路径不存在，请重新输入!'
   GOTO ExitFLag
END
IF OBJECT_ID('tempdb..#Dir') IS NOT NULL
    DROP TABLE #Dir
CREATE TABLE #Dir  
(  
     BackDBFileName NVARCHAR(100) ,DEPTH INT ,[File] INT  
)
IF OBJECT_ID('tempdb..#FileListInfo') IS NOT NULL
    DROP TABLE #FileListInfo
CREATE TABLE #FileListInfo
(
	LogicalName              NVARCHAR(128) NULL,
	PhysicalName             NVARCHAR(260) NULL,
	TYPE                     CHAR(1) NULL,
	FileGroupName            NVARCHAR(128) NULL,
	FileSize                 BIGINT NULL,
	MAXSIZE                  BIGINT NULL,
	FileId                   BIGINT,
	CreateLSN                NUMERIC(25, 0),
	DropLSN                  NUMERIC(25, 0) NULL,
	UniqueID                 UNIQUEIDENTIFIER,
	ReadOnlyLSN              NUMERIC(25, 0) NULL,
	ReadWriteLSN             NUMERIC(25, 0) NULL,
	BackupSizeInBytes        BIGINT,
	SourceBlockSize          INT,
	FileGroupID              INT,
	LogGroupGUID             UNIQUEIDENTIFIER NULL,
	DifferentialBaseLSN      NUMERIC(25, 0) NULL,
	DifferentialBaseGUID     UNIQUEIDENTIFIER,
	IsReadOnly               BIT,
	IsPresent                BIT,
	TDEThumbprint            BIT
)
 
INSERT INTO #Dir EXEC master..xp_dirtree @BackUpPath,1,1
 
DELETE FROM #Dir WHERE CHARINDEX('.bak', BackDBFileName)=0
IF NOT EXISTS (SELECT TOP 1 1 FROM #Dir)
BEGIN
   PRINT CHAR(10) + N'在提供的路径下没有找到合符要求的备份文件'    
   Goto ExitFLag
 END
 
 PRINT N'开始还原所有数据库'
DECLARE db_file Cursor Local Static Read_Only Forward_Only
FOR
SELECT BackDBFileName from #Dir
 
OPEN db_file
FETCH NEXT FROM db_file INTO @BackUpFileName
WHILE @@FETCH_STATUS=0
 BEGIN
  --Restore DataBase
  SET @BackUpFullFileName=@BackUpPath+@BackUpFileName
  SET @DbName = REPLACE(@BackUpFileName,'.bak','')
  DELETE FROM #FileListInfo
  INSERT INTO #FileListInfo
    EXEC ('RESTORE FILELISTONLY FROM DISK=''' + @BackUpFullFileName + '''')
	SET @LogicalDbName=''
	SELECT @LogicalDbName = LogicalName FROM   #FileListInfo WHERE [TYPE]= 'D'  --数据库文件  
	SET @LogicalLogName=''
    SELECT @LogicalLogName = LogicalName FROM   #FileListInfo WHERE [TYPE]= 'L' --日志文件
	IF (@LogicalDbName IS NULL OR @LogicalDbName = '')  
   SET @LogicalDbName = @DbName
    IF (@LogicalLogName IS NULL OR @LogicalLogName = '')  
   SET @LogicalLogName = @DbName + '_log'
  SET @RESTORESQL = '  
  IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE [name]='''+@DbName+''')
  BEGIN
  EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'''+@DbName+'''  
  USE [master]  
  DROP DATABASE ['+@DbName +']  
  END
  RESTORE DATABASE '+@DbName+' FROM DISK = '''+@BackUpFullFileName+'''  
  WITH MOVE '''+@LogicalDbName+''' TO '''+@SqlServerPath+@DbName+'.mdf''
 ,MOVE '''+@LogicalLogName+''' TO '''+@SqlServerPath+@DbName+'_log.ldf'' '
   PRINT @RESTORESQL
   EXECUTE  sp_executesql @RESTORESQL
  FETCH NEXT FROM db_file INTO @BackUpFileName
 END
CLOSE db_file
DEALLOCATE db_file
PRINT N'
所有数据库还原完成'
ExitFLag:
----临时表删除--------
IF (OBJECT_ID('tempdb..#Temp') IS NOT NULL)  
  DROP TABLE #Temp  
IF OBJECT_ID('tempdb..#Dir') IS NOT NULL
    DROP TABLE #Dir
IF OBJECT_ID('tempdb..#FileListInfo') IS NOT NULL
    DROP TABLE #FileListInfo

USE [master]
GO
ALTER DATABASE MY_RelationMgmt SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE MY_RelationMgmt SET RECOVERY SIMPLE --先进入简单用户模式
GO
USE [MY_RelationMgmt]
GO
DBCC SHRINKFILE (N'MY_RelationMgmt_Log' , 2, TRUNCATEONLY)
GO

use [MY_RelationMgmt]
IF OBJECT_ID('tempdb..#TablesSizes') IS NOT NULL
    DROP TABLE #TablesSizes
CREATE TABLE #TablesSizes
    (
      TableName sysname ,
      Rows BIGINT ,
      reserved VARCHAR(100) ,
      data VARCHAR(100) ,
      index_size VARCHAR(100) ,
      unused VARCHAR(100)
    )
DECLARE @sql VARCHAR(MAX)
SELECT  @sql = COALESCE(@sql, '') + '
INSERT INTO #TablesSizes execute sp_spaceused ''' + QUOTENAME(TABLE_SCHEMA,
                                                              '[]') + '.'
        + QUOTENAME(Table_Name, '[]') + ''''
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
PRINT ( @SQL )
EXECUTE (@SQL)
SELECT  *
FROM    #TablesSizes
ORDER BY Rows DESC

USE [master]
DECLARE @backupfile NVARCHAR(1024)  
DECLARE @backdesc NVARCHAR(1024)  
DECLARE @filename NVARCHAR(1024)  
DECLARE @path NVARCHAR(1024)  
DECLARE @dbname SYSNAME
DECLARE @extension_name NVARCHAR(50)
SET @path = N'D:\DBBackup\';   -- 此处是服务器的备份文件输出路径
SET @extension_name = N'.bak';
IF (OBJECT_ID('tempdb..#Temp') IS NOT NULL)  
  DROP TABLE #Temp  
CREATE TABLE #Temp (id INT IDENTITY,a INT,b INT,c INT)
DECLARE @fpath NVARCHAR(3)
SET @fpath=LEFT(@path,3)
INSERT #Temp EXEC master..xp_fileexist @fpath
INSERT #Temp EXEC master..xp_fileexist @path
--如果指定盘符有误不存在，则返回错误提示：
IF EXISTS(SELECT 1 FROM #Temp WHERE id=1 AND c=0)
BEGIN
    PRINT CHAR(10) + N'输入的盘符不存在，请重新输入!'
	----临时表删除--------
IF (OBJECT_ID('tempdb..#Temp') IS NOT NULL)  
BEGIN  
  DROP TABLE #Temp  
END
RETURN
END
--如果不存在指定的文件夹，则创建:
ELSE IF EXISTS(SELECT 1 FROM #Temp WHERE b=0 AND id=2)
BEGIN
PRINT CHAR(10) + N'正在创建目录'
EXEC sp_configure 'show advanced options',1
reconfigure
EXEC sp_configure 'xp_cmdshell',1
reconfigure
    DECLARE @mddir NVARCHAR(100)
    SET @mddir='md '+@path
    EXEC master..xp_cmdshell @mddir
	PRINT CHAR(10) + N'创建目录完成'
END
----临时表删除--------
IF (OBJECT_ID('tempdb..#Temp') IS NOT NULL)  
BEGIN  
  DROP TABLE #Temp  
END
--备份
PRINT CHAR(10) + N'开始备份所有数据库'
DECLARE tmp_Cur CURSOR  
FOR  
    SELECT  name  
    FROM    sys.databases  
    WHERE ([name] LIKE '%MGMT' )  -- 此处条件可以自定义，
    ORDER BY Name
 
OPEN tmp_Cur
FETCH NEXT FROM tmp_Cur INTO @dbname;  
WHILE @@FETCH_STATUS = 0    
    BEGIN  
        -- 得到完整目标文件，数据库将备份到这个文件中  
        SET @backupfile = @path + @dbname + @extension_name
        --SELECT  @backupfile  
        SET @backdesc =@dbname + N'-完整 数据库 备份'  
 
        -- 开始备份, COMPRESSION 参数表示压缩，可节省磁盘空间
  PRINT CHAR(10) + N'开始备份 '+@dbname
  PRINT CHAR(10)
        BACKUP DATABASE @dbname TO DISK = @backupfile WITH NOFORMAT, NOINIT,  NAME = @backdesc, SKIP, NOREWIND, NOUNLOAD,  STATS = 10, COMPRESSION  
        PRINT CHAR(10) + @dbname + N' 备份完成 '
        FETCH NEXT FROM tmp_Cur INTO @dbname  
    END  
CLOSE tmp_Cur
DEALLOCATE tmp_Cur
PRINT CHAR(10) + N'所有数据库备份完成'

sp_configure 'show advanced options', 1;
 GO
 RECONFIGURE;
 GO
 sp_configure 'Ad Hoc Distributed Queries', 1;
 GO
 RECONFIGURE;
 GO