MSSQL之十一数据库高级编程总结-伙伴云

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

网友投稿 806 2022-05-29

视频课 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

/*--备份所有数据库

备份的文件名为数据库名+日期+.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)='' --要备份的数据库名称列表,不指定则备份所有用户数据库

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

二． 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, -- 返回, 非值则返回记录总数

--设置相应的空格

--设置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();

}

四.SQLServer异构数据库之间数据的导入导出

本文讨论了如何通过Transact-SQL以及系统函数OPENDATASOURCE和OPENROWSET在同构和异构数据库之间进行数据的导入导出，并给出了详细的例子以供参考。

1. 在SQL Server数据库之间进行数据导入导出

(1).使用SELECT INTO导出数据

在SQL Server中使用最广泛的就是通过SELECTINTO语句导出数据，SELECT INTO语句同时具备两个功能：根据SELECT后跟的字段以及INTO后面跟的表名建立空表（如果SELECT后是*, 空表的结构和FROM所指的表的结构相同）；将SELECT查出的数据插入到这个空表中。在使用SELECT INTO语句时，INTO后跟的表必须在数据库不存在，否则出错，下面是一个使用SELECT INTO的例子。

假设有一个表table1，字段为f1(int)、f2(varchar(50))。

SELECT * INTO table2 FROM table1

这条SQL语的在建立table2表后，将table1的数据全部插入到table1中的，还可以将*改为f1或f2以便向适当的字段中插入数据。

SELECT INTO不仅可以在同一个数据中建立表，也可以在不同的SQL Server数据库中建立表。

USE db1

SELECT * INTO db2.dbo.table2 FROM table1

以上语句在数据库db2中建立了一个所有者是dbo的表table2，在向db2建表时当前登录的用户必须有在db2建表的权限才能建立table2。使用SELECT INTO要注意的一点是SELECT INTO不可以和COMPUTE一起使用，因为COMPUTE返回的是一组记录集，这将会引起二意性（即不知道根据哪个表建立空表）。

(2).使用INSERTINTO和 UPDATE插入和更新数据

SELECT INTO只能将数据复制到一个空表中，而INSERT INTO可以将一个表或视图中的数据插入到另外一个表中。

INSERT INTO table1 SELECT * FROM table2

或 INSERT INTO db2.dbo.table1 SELECT * FROMtable2

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

但以上的INSERT INTO语句可能会产生一个主键冲突错误（如果table1中的某个字段是主键，恰巧table2中的这个字段有的值和table1的这个字段的值相同）。因此，上面的语句可以修改为

INSERT INTO table1 -- 假设字段f1为主键

SELECT * FROM table2 WHERE NOT EXISTS(SELECTtable1.f1 FROM table1 WHERE table1.f1=table2.f1 )

以上语句的功能是将table2中f1在table1中不存在的记录插入到table1中。

要想更新table1可以使用UPDATE语句

UPDATE table1 SET table1.f1=table2.f1,table1.f2=table2.f2 FROM table2 WHERE table1.f1=table2.f1

将以上两条INSERT INTO和UPDATE语句组合起来在一起运行，就可以实现记录在table1中不存在时插入，存在时更新的功能，但要注意要将UPDATE放在 INSERT INTO前面，否则UPDATE更新的记录数将是table1和table2记录数的总和。

2. 使用OPENDATASOURCE和OPENROWSET在不同类型的数据库之间导入导出数据

在异构的数据库之间进行数据传输，可以使用SQL Server提供的两个系统函数OPENDATASOURCE和OPENROWSET。

OPENDATASOURCE可以打开任何支持OLE DB的数据库，并且可以将OPENDATASOURCE做为SELECT、UPDATE、INSERT和DELETE后所跟的表名。如

SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'DataSource=192.168.18.252;User ID=sa;Password=test').pubs.dbo.authors

这条语句的功能是查询192.168.18.252这台机器中SQL Server数据库pubs中的authors表。从这条语句可以看出，OPENDATASOURCE有两个参数，第一个参数是 provider_name，表示用于访问数据源的 OLE DB 提供程序的 PROGID 的名称。provider_name 的数据类型为 char，没有默认值。第二个参数是连接字符串，根据OLE DB Provider不同而不同（如果不清楚自己所使用的OLE DBProvider的连接字符串，可以使用delphi、visualstudio等开发工具中的ADO控件自动生成相应的连接字符串）。

OPENROWSET函数和OPENDATASOURCE函数类似，只是它可以在打开数据库的同时对数据库中的表进行查询，如以下语句

OPENROWSET('MSDASQL.1', 'Driver=Microsoft VisualFoxPro Driver; SourceDB=c:\db; SourceType=DBF', SELECT * FROM [b.dbf])

最后一个参数查询foxpro表b.dbf，读者可以通过where条件对b.dbf进行过滤。如果将INSERT INTO、SELECT INTO和OPENDATASOURCE或OPENROWSET一起使用，就可以使SQL Server数据库和其它类型的数据库之间进行数据导入导出。下面介绍如何使用这两个函数在SQL Server数据库和其它类型的数据库之间进行数据导入导出。

(1).SQLServer数据库和SQL Server数据库之间的数据导入导出。

导入数据

SELECT * INTOauthors1 FROMOPENDATASOURCE( 'SQLOLEDB', 'Data Source=192.168.18.252;UserID=sa;Password=abc').pubs.dbo.authors

导出数据

INSERT INTO OPENDATASOURCE('SQLOLEDB','DataSource=192.168.18.252;User ID=sa;Password=abc').test.dbo.authors select * frompubs.dbo.authors

在这条语句中OPENDATASOURCE(...)可以理解为SQL Server的一个服务，.pubs.dbo.authors是这个服务管理的一个数据库的一个表authors。使用INSERT INTO时OPENDATASOURCE(...)后跟的表必须存在。

也可以将以上的OPENDATASOURCE换成OPENROWSET

INSERT INTO OPENROWSET('SQLOLEDB','192.168.18.252;sa;abc',select * from test.dbo.kk) SELECT * FROM pubs.dbo.authors

使用OPENROWSET要注意一点，192.168.18.252;sa;abc中间是";"，而不是","。OPENDATASOURCE和OPENROWSET都不接受参数变量。

(2).SQL Server数据库和Access数据库之间的数据导入导出。

导入数据

SELECT * INTO access FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\data.mdb;Persist SecurityInfo=False')...table1

或者使用OPENROWSET

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\data.mdb;admin;',SELECT * FROMtable1)

导出数据

INSERT INTOOPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:\data.mdb;Persist Security Info=False')...table1 SELECT * FROM access

打开access数据库的OLE DBProvider叫Microsoft.Jet.OLEDB.4.0，需要注意的是操作非SQL Server数据库在OPENDATASOURCE(...)后面引用数据库中的表时使用"...”，而不是“.”。

(3).SQL Server数据库和文本文件之间的数据导入导出。

导入数据

SELECT * INTO text1 FROMOPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=c:\')...[data#txt]

导出数据

INSERT INTOOPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=c:\')...[data#txt]SELECT * FROM text1

或者使用OPENROWSET

INSERT INTOOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=c:\, [data#txt]') SELECT *FROM text1

如果要插入部分字段，可使用

INSERT INTOOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=c:\, SELECT aa FROM[data#txt]') SELECT aa FROM text1

这条SQL语句的功能是将c盘根目录的data.txt文件导入到text1表中，在这里文件名中的“.”要使用“#”代替。在向文本导出时，不仅文本文件要存在，而且第一行必须和要导出表的字段一至。

(4).SQL Server数据库和dbase数据库之间的数据导入导出。

导入数据

SELECT * INTO dbase FROMOPENROWSET('MICROSOFT.JET.OLEDB.4.0 ', 'dBase III;HDR=NO;IMEX=2;DATABASE=C:\',SELECT* FROM [b.dbf])

导出数据

INSERT INTO OPENROWSET('MICROSOFT.JET.OLEDB.4.0', dBase III;HDR=NO;IMEX=2;DATABASE=C:\,SELECT * FROM [b.dbf]) SELECT * FROMdbase

OPENROWSET(...)中的b.dbf使用[...]括起来，是为了当dbf文件名有空格等字符时不会出错，如果没有这些特殊字符，可以将[...]去掉

(5).SQL Server数据库和foxpro数据库之间的数据导入导出。

导入数据

SELECT * INTO foxpro FROMOPENROWSET('MSDASQL.1', 'Driver=Microsoft Visual FoxProDriver;SourceDB=c:\; SourceType=DBF, 'SELECT * FROM [a.dbf])

导出数据

INSERT INTO OPENROWSET('MSDASQL.1' ,'Driver=Microsoft Visual FoxPro Driver; SourceDB=c:\db;SourceType=DBF,'SELECT * FROM a.dbf) SELECT * FROM foxpro

在此处a.dbf不能使用[...]括起来，否则出错（这是由driver决定的）。

(6).SQL Server数据库和excel文件之间的数据导入导出

导入数据

SELECT * INTO excel FROMOPENDATASOURCE(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;DATABASE=c:\book1.xls)...[Sheet1$]

导出数据

INSERT INTOOPENDATASOURCE(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;DATABASE=c:\book1.xls)...[Sheet1$] SELECT * FROM excel

在book1.xls的Sheet1中必须有和excel表相对应的字段，否则会出错。

以上讨论了几种常用的数据库和SQL Server数据库之间如何使用Transact-SQL进行数据导入导出。在SQL Server中还提供了将其它类型的数据库注册到SQL Server中的功能，这样就可以和使用SQL Server数据库表一样使用这些被注册数据库中的表了。

EXEC sp_addlinkedserver access,OLE DB Providerfor Jet, Microsoft.Jet.OLEDB.4.0, c:\data.mdb

以上SQL使用存储过程sp_addlinkedserver注册了一个access数据库，我们可以在SQL Server中使用如下语句查询在data.mdb中的table1。

SELECT * FROM access...table1

这样就可很方便地查询access数据库中的表了，如果要导入table1，可以使用SELECT * INTO table2 FROMaccess...table1。如果想删除注册的数据库连接，使用如下语句。

EXEC sp_dropserver access

使用Transact-SQL不仅可以向SQLServer数据库导入导出数据，而且还可以使任意两种类型数据库之间互相导入导出数据。以access和excel为例进行说明。

INSERT INTOOPENDATASOURCE(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;DATABASE=c:\book1.xls)...[Sheet1$] SELECT * FROM OPENROWSET(Microsoft.Jet.OLEDB.4.0,c:\data.mdb;admin;,SELECT * FROM table1)

以上SQL语句将access数据库的table1表的数据插入到excel文件book1.xls中的Sheet1表单中。

使用Transact-SQL进行数据的导入导出，可以很方便地将这些Transact-SQL语句放到客户端程序中（如delphi、c#等），从而可以很容易地编写自已的数据库导入导出工具。

五.无限级分类的数据库设计方案

第一种方案：

表为两张，一张分类表，一张信息表。

表1：

`ID` int(10),

`cID` tinyint(3) ,

`title` varchar(255),

表2：

`cID` tinyint(3) ,

`parentID` tinyint(3)，

`order` tinyint(3) ,

`name` varchar(255),

这样可以根据cID = parentID来判断上一级内容，运用递归至最顶层。

第二种方案：

设置parentID为varchar类型，将父类id都集中在这个字段里，用符号隔开，比如：1,3,6

这样可以比较容易得到各上级分类的ID，而且在查询分类下的信息的时候，可以使用如：Select * From information Where cID Like "1,3%"。不过在添加分类和转移分类的时候操作将非常麻烦。

以上两种方案地址：http://search.phpres.com/phpres-top2007,98552.html

第三种方案：

每级分类递增两位数字，这样，每级分类的数目限定在100个之间，分类方法主要为编码法；

示例：

一级分类：01，02，03

二级分类：0101，0102，0103，0201，0202........

三级分类：010101，010102，010103，010104..........

数据库查询时使用 like '01%'就可得到一级分类01下的所有子分类，非常方便！

如果要列出所有分类的树型结构，只需用一条语句select * from pro_class order bycode，再稍微处理一下就可。（其中，pro_class为产品分类表，code为类别编码）。

设计的数据库结构如下：

id: 类别id，主键

classname: 类名

classcode: 类别编码

parent: 父id

left_child: 最左孩子id（或第一个孩子）

right_sibling: 右兄弟id

layer: 层级（第一级类别为1，第2级类别2，以此类推）

SQL 数据库

分享20个超简单的Excel信息函数应用技巧

806 2022-05-29

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

办公软件三剑客——Excel表格可视化数据，excel表格制作教程入门，及常用公式

在线表格如何查看编辑记录（在线表格可以看编辑记录吗）

分享20个超简单的Excel信息函数应用技巧

推荐文章

企业生产管理是什么，企业生产管理软件

进盘点进销存软件排行榜前十名

进销存系统哪个简单好用？进销存系统优点

工厂生产管理（工厂生产管理流程及制度）

生产管理软件，机械制造业生产管理，制造业生产过程管理软件

进销存软件和ERP有什么区别？进销存与erp软件理解

进销存如何进行库存管理

如何利用excel制作销售订单管理系统？

数据库订单管理系统有哪些功能？数据库订单管理系统怎么设计？

什么是数据库管理系统？

最近发表

热评文章

零代码开发是什么？2022低代码平台排行榜

智能进销存库存管理系统（智慧进销存）

在线文档哪家强？8款在线文档编辑软件推荐

WPS2016怎么绘制简单的价格表?

什么是在线文档？怎么发在线文档

Excel项目进度表模板，简化您的项目进度管理

友情链接

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

推荐文章

最近发表

热评文章

友情链接

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