MSSQL之十一 数据库高级编程总结（上）

视频课https://edu.csdn.net/course/play/7940

一.  SQL Server数据库命名规范

数据库命名规范：

1. 数据库名：

      1.1）用产品或项目的名字命名；

      1.2）Pascal Case，如AdventureWork;

      1.3）避免使用特殊字符，如数字，下划线，空格之类；

      1.4）避免使用缩写

2. 表名

      2.1）使用复数，Pascal Case，而复数只加在最后一个单词上如：Products，Users，UserRoles

      2.2）避免使用特殊字符，如数字，下划线，空格之类；

      2.3）避免使用缩写

3. 列名

      3.1） 使用Pascal Case

      3.2） 避免和表名重复，避免数据类型前缀如: Int

      3.3） 避免使用缩写或者特殊字符

4. 存储过程

      4.1）用动词加表名描述操作类型

      4.2）使用前缀：sp+{“Insert”, “Update”, “Delete”,“Get”, “Validate”,...}

5.  视图

          5.1）参考表名规则

          5.2）用"vw"做前缀

6. 触发器

          6.1）使用"trg"前缀

          6.2） 使用操作类型+表名，如：trg_ProductsInsert

7. 索引

          7.1）使用格式如：idx_{表名}_{索引列名}_{Unique/NonUnique}_{Cluster/NonCluster}

8. 主键

        8.1） 使用格式如：pk_{表名}_{主键列名}

9. 外键

        9.1） 使用格式如：fk_{主表名}_{主表的列名}_{引用表名}_{引用表的列名}

10. default

    10.1） 使用格式如：df_{表名}_{列名}

11. 约束

    11.1） 使用格式如：ck_{表名}_{列名}

12. 变量

    12.1） 参照列名规则

二. 数据库备份

备份处理的存储过程  

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 /*--备份所有数据库  

 备份的文件名为数据库名+日期+.bak  

 将所有的用户数据库(或指定的数据库列表)  

 备分到指定的目录下.  

 /*--调用示例  

 --备份所有用户数据库  

 exec   p_backupdb  @bkpath='D:\',@dbname=''  

 --备份指定数据库  

 exec   p_backupdb   @bkpath=D:\',@dbname='数据库名称'  

 --*/  

create  proc   [dbo].[p_backupdb]  

 @bkpath   nvarchar(260)='D:\', --备份文件的存放目录,不指定则使用SQL默认的备份目录  

@dbname   nvarchar(4000)='' --要备份的数据库名称列表,不指定则备份所有用户数据库  

as

declare   @sql   varchar(8000)

DECLARE @strdate NVARCHAR(200)

set @strdate = convert(NVARCHAR(10),getdate(),120)

set @strdate = REPLACE(@strdate, '-' , '')

 --检查参数  

 if   isnull(@bkpath,'')=''  

 begin  

 select   @bkpath=rtrim(reverse(filename))  from   master..sysfiles   where  name='master'  

 select  @bkpath=substring(@bkpath,charindex('\',@bkpath)+1,4000)  

,@bkpath=reverse(substring(@bkpath,charindex('\',@bkpath),4000))+'BACKUP\'  

 end  

 else   if   right(@bkpath,1)<>'\'  set   @bkpath=@bkpath+'\'  

 --得到要备份的数据库列表  

 if   isnull(@dbname,'')=''  

 declare   tb   cursor   local  for  

 select   name   from  master..sysdatabases   where   name  not   in('master','tempdb','model','msdb')  

 else  

 declare   tb   cursor   local  for  

 select   name   from  master..sysdatabases  

 where   name   not  in('master','tempdb','model','msdb')  and(name  like '%'+@dbname+'%')

 --备份处理  

 open   tb  

 fetch   next   from   tb  into   @dbname  

 while   @@fetch_status=0  

 begin  

 set   @sql='backup   database  '+@dbname  

 +'   to   disk='''+@bkpath+@dbname+'_'+@strdate

 +'.bak''   with   format'  

 exec(@sql)  

 fetch   next   from   tb  into   @dbname  

 end  

 close   tb  

 deallocate   tb  

 go

二．      Sql Server 2005的分页存储过程

CREATEPROCEDURE     [dbo].[TopPageList]

   @strTable      varchar(200),   --表名 ("@strTable", "myUser");

   @strColumn     varchar(50),    --按该列来进行分页("@strColumn", "UserId");

   @strOrderColumn  varchar(50),    --排序字段order by XXX desc

   @intOrder     int,--排序的顺序 0 升序 1降序

   @strColumnlist  varchar(150) , --要查询出的字段列表,*表示全部字段 cmd.Parameters.Add("@strColumnlist", "*");  

   @strWhere      varchar(800)='',--查询条件cmd.Parameters.Add("@strWhere", "");

   @intPageSize    int, --每页记录数  cmd.Parameters.Add("@intPageSize", 15);

   @intPageNum    int,           --指定页  cmd.Parameters.Add("@intPageNum", 5);

 --  @intPageCount   int   OUTPUT  , --总页数  SqlParameter paramPageCount =cmd.Parameters.Add("@intPageCount", SqlDbType.Int);

  -- paramPageCount.Direction = ParameterDirection.Output;

   @itemCount      int  OUTPUT

--   @doCount bit = 0, -- 返回, 非值则返回记录总数

 AS

--设置相应的空格

--设置DESC ASC

 if @intOrder=0  --0升序

    set @strOrderColumn=' order by '+@strOrderColumn

 else           --降序

    set @strOrderColumn=' order by '+@strOrderColumn +' desc '

 DECLARE   @sql    nvarchar(2000) --用于构造SQL语句

 DECLARE   @where1 varchar(800)   --构造条件语句

 DECLARE   @where2 varchar(800)   --构造条件语句

 IF   @strWhere   is   null  or   rtrim(@strWhere)=''  

 -- 为了避免SQL关键字与字段、表名等连在一起，首先为传入的变量添加空格

 BEGIN  --没有查询条件

     SET   @where1=' WHERE '  

     SET   @where2=' '  

 END  

 ELSE  

 BEGIN  --有查询条件

     SET   @where1=' WHERE  ('+@strWhere+')  AND '

     SET   @where2=' WHERE  ('+@strWhere+') '  

 END

 ------构造SQL语句，计算总页数。计算公式为总页数= Ceiling ( 记录个数/ 页大小)

  --计算总项数

 SET   @sql='SELECT   @itemCount=COUNT('+@strColumn+') from  '+@strTable +@where2

print(@sql)

 EXEC sp_executesql  @sql,N'@itemCount  int  OUTPUT',@itemCount   OUTPUT

  --   1：直接计算    2：自己写个分页控件里面设置一下也可以~！

-- set @intPageCount  =floor(cast(@itemCount as float)/@intPageSize)

 --   if @intPageCount

 --   set @intPageCount =@intPageCount +1

--

 --执行SQL语句，计算总页数，并将其放入@intPageCount变量中

 --将总页数放到查询返回记录集的第一个字段前，此语句可省略

 SET  @strColumnlist=' '+ Cast(@itemCount as varchar(30)) + ' asitemCount,' +' '+ @strColumnlist  

--+ Cast(@intPageCount as varchar(30)) + ' as PageCount,'

 SET @sql='SELECT TOP '+ CAST(@intPageSize   AS  varchar)  +  @strColumnlist +  

              ' FROM ' + @strTable + @where1 + '  '+

              @strColumn + ' not in  '+  

              '  (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1)  AS varchar) + ' ' +

              @strColumn + ' FROM '+ @strTable+@where2+@strOrderColumn+')  ' +@strOrderColumn

 print(@sql)

--ELSE

--     begin   --构造降序的SQL---针对2个表的时候会出现聚合函数的异常--适合单个表格的数据库分页操作

--     SET @sql='SELECT TOP '+CAST(@intPageSize   AS   varchar)  + @strColumnlist +  

--              ' FROM ' + @strTable + @where1 + '  '+

--             @strColumn + '<(SELECT MIN('+@strColumn+') '+  

--             ' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1)  AS varchar) + ' ' +

--             @strColumn + ' FROM '+ @strTable+@where2+@strOrderColumn+')  as tblTmp)' +@strOrderColumn

 --    print(@sql)

--     end

 IF   @intPageNum=1--第一页

     SET   @sql='SELECT  TOP   '+CAST(@intPageSize   AS   varchar) +@strColumnlist + ' FROM'+@strTable+  

               @where2+@strOrderColumn

 --END  

--PRINT   @sql  

print(@sql)

exec(@sql)

public static void BindingContent(string strTable, string strColumn, stringstrOrderColumn, int intOrder, string strColumnlist, string strWhere,IChangePageStored changePage)
        {
           SqlParameter[] paras=new SqlParameter[9];
            paras[0] =new SqlParameter("@strTable" ,SqlDbType.VarChar);
           paras[0].Value = strTable;
            paras[1] =new SqlParameter("@strColumn", SqlDbType.VarChar);
           paras[1].Value = strColumn;
            paras[2] =new SqlParameter("@strOrderColumn", SqlDbType.VarChar);
           paras[2].Value = strOrderColumn;
            paras[3] =new SqlParameter("@strColumnlist", SqlDbType.VarChar);
           paras[3].Value = strColumnlist;
            paras[4] =new SqlParameter("@intOrder", SqlDbType.Int);
           paras[4].Value = intOrder;
            paras[5] =new SqlParameter("@strWhere", SqlDbType.VarChar);
           paras[5].Value = strWhere;
            paras[6] =new SqlParameter("@intPageSize", SqlDbType.Int);
           paras[6].Value = changePage.PageSize;
            paras[7] =new SqlParameter("@intPageNum", SqlDbType.Int);
           paras[7].Value = changePage.CurrentPage  ;
         //   paras[8] = newSqlParameter("@intPageCount", SqlDbType.Int);
         //  paras[8].Direction = ParameterDirection.Output;
            paras[8] =new SqlParameter("@itemCount", SqlDbType.Int);
           paras[8].Direction = ParameterDirection.Output;
            DataSet ds =DBTool.ExecuteDataset(CommandType.StoredProcedure, "TopPageList",paras);
          /* @intPageCount   int   OUTPUT  , --总页数  SqlParameterparamPageCount = cmd.Parameters.Add("@intPageCount", SqlDbType.Int);
   -- paramPageCount.Direction = ParameterDirection.Output;
               @strTable = N'zhq_in_content c  INNER JOIN zhp_in_columns  m ONc.columns_id=m.columns_id',
  @strColumn = N'c.content_id',
  @strOrderColumn = N'c.createdate',
  @intOrder = 1,
  @strColumnlist = N'*',
  @strWhere = N'c.status=0 AND c.del=0',
  @intPageSize = 20,
  @intPageNum =100,*/
           changePage.DataSource = ds.Tables[0].DefaultView;// 设置分页控件的数据
            if (ds !=null && ds.Tables[0].Rows.Count > 0)
            {
               changePage.RecordCount =int.Parse(ds.Tables[0].Rows[0]["itemCount"].ToString());
              // changePage.PageCount =int.Parse(ds.Tables[0].Rows[0]["PageCount"].ToString());
            }
            if(changePage.DataUI.GetType().BaseType.Name == "BaseDataList")
            {
               changePage.DataUI.DataSource = changePage.DataSource;//设置数据源控件的数据
               changePage.DataUI.DataBind();
            }
       }