sql 随记

将查询结果，组合成一个字符串返回

declare @s varchar(100)

select @s=isnull(@s,'')+','+ rtrim(moduleid) from dbo.ModuleInfo where modeType=2

print @s

select stuff(@s,1,1,'') 

删除重复记录(只留下一条)

需要有一个主键ID

delete from a
where
 id not in (
select max(t1.id) from a as  t1 group by t1.name
)

查询去除重复后的所有数据

select DISTINCT name,* From [tablename]

查询所有重复数据

Select [name] From [tablename] group by name having count(*)>1

查询部分字段重复的数据

SELECT b.*
FROM (SELECT age, sex
        FROM test
        GROUP BY age, sex
        HAVING (COUNT(*) > 1)) a INNER JOIN
      test b ON a.age = b.age AND a.sex = b.sex

删除所有数据，重建表结构

Truncate table tablename

自增列恢复

USE   DBName  
  GO  
  DBCC   CHECKIDENT   (tblName,   RESEED,   1)  
  GO 

查询是否存在列，并增加列

if not exists (select   *   from   examgrade.dbo.syscolumns  
where   id   =  object_id('examgrade.dbo.QuestionTitleInfo')   and   name   =   'TitleinfoGId' )
alter table examgrade.dbo.QuestionTitleInfo add TitleinfoGId uniqueidentifier

使用SQLDMO还原数据库

        public void Restroe(string servername, string uid, string pwd, string dbname, string path)
        {
            SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
            try
            {
                SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
                oSQLServer.LoginSecure = false;
                oSQLServer.Connect(servername.Trim(), uid.Trim(), pwd);
                //oSQLServer.Connect("master", uid.Trim(), pwd);
                oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;

                SQLDMO.RestoreSink_PercentCompleteEventHandler pceh = new SQLDMO.RestoreSink_PercentCompleteEventHandler(Step);
                oRestore.PercentComplete += pceh;

                oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
                oRestore.Database = dbname.Trim();
                oRestore.Files = "["+path.Trim()+"]";
                oRestore.FileNumber = 1;
                oRestore.ReplaceDatabase = true;
                string strfile = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"TempFolder\DataBase");
                if (!System.IO.Directory.Exists(strfile))
                {
                    System.IO.Directory.CreateDirectory(strfile);
                }
                strfile = System.IO.Path.Combine(strfile, dbname);
                SQLDMO.QueryResults qr = oRestore.ReadFileList(oSQLServer);

                oRestore.RelocateFiles = qr.GetColumnString(1, 1) + ",[" + strfile + ".mdf]," + qr.GetColumnString(2, 1) + ",[" + strfile + ".ldf]";

                oRestore.SQLRestore(oSQLServer);
            }
            catch (Exception ex)
            {
                throw new Exception("还原数据库时失败!" + ex.Message);
                Log.LogErr("还原数据库时失败!" + ex.Message);
            }
            finally
            {
                oSQLServer.DisConnect();
            }
           
        }

        /**//// <summary>
        /// 清除数据库连接
        /// </summary>
        /// <param name="strDBName"></param>
        void DisposeConnection(string strDBName)
        {
            Log.LogNote("清除数据库连接");
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("  declare   hcforeach   cursor   global   for   select   'kill   '+rtrim(spid)   from   sysprocesses   where   dbid=db_id(N'" + strDBName + "')");
                sb.Append("exec   sp_msforeach_worker   '?'");
                SqlConnection connSql = new SqlConnection(GetConntectionStringByDDName("master"));
                SqlCommand cmd = new SqlCommand(sb.ToString(), connSql);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (System.Exception e)
            {
                Log.LogErr("清除数据库连接");
            }

        }
        /**//// <summary>
        /// 删除数据库
        /// </summary>
        /// <param name="strDBName"></param>
        void DeleteDataBase(string strDBName)
        {
            Log.LogNote("删除数据库");
            try
            {
                DisposeConnection(strDBName);
                StringBuilder sb = new StringBuilder();
                sb.Append("DROP DATABASE ");
                sb.Append(strDBName);
                SqlConnection connSql = new SqlConnection(GetConntectionStringByDDName("master"));
                SqlCommand cmd = new SqlCommand(sb.ToString(), connSql);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (Exception ex)
            {
                Log.LogErr("删除数据库" + ex.Message);
            }

        }