HTZProject项目中用到的SQL总结

下面的SQL语句只针对SQL Server:

1.删除某个视图

一般使用如下SQL语句：

" if exists (select name   from  sysobjects where name  = '" + viewName  + "' and   xtype = 'V') drop view " + viewName;

 但推荐使用如下语句，能提高效率：

" if exists(select  TABLE_NAME from INFORMATION_SCHEMA.VIEWS where TABLE_NAME = '"  + viewName + "') drop view " + viewName;

    要擅于使用SQL Server给我们提供的INFORMATION_SCHEMA视图，对于INFORMATION_SCHEMA视图的详情，请查看SQL Server的联机帮助文档。

2。使用JDBC创建视图

public void generateView(String dropViewSql, String createViewSql) {
		// 因为'CREATE VIEW'
		// 必须是查询批次中的第一个语句,所以只能用这种方式执行,而不能用addBatch()方式执行;否则会报错
		jdbcTemplate.execute(dropViewSql);
		jdbcTemplate.execute(createViewSql);
	}

 使用jdbc创建视图时，不能使用批处理的方式执行，如果使用批处理的方式，它会提示“'CREATE VIEW' 必须是查询批次中的第一个语句”这样的错误。

3。使用Bulk insert导入数据

数据库中的存储过程如下所示，其中@fn变量是一个文件路径字符串，不能超过200个字符。同时导入的文件的行分隔符是\n，数据分隔符是逗号(,),这些你都可以自己改变，有关bulk insert详细语法的说明，请参阅SQL Server BULK INSERT (Transact-SQL)。

create  proc [dbo].[importDataFile] @tableName nvarchar(30), @fn
	 nvarchar(200) as exec(' bulk INSERT '+@tableName+' FROM '''+@fn+''' WITH (
	  FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' )')

BULK INSERT 将数据从数据文件加载到表中。此功能类似于 bcp 命令的 in 选项，但是数据文件是由 SQL Server 进程读取的。使用SQL Server自带的bulk insert来导入数据，能极大的提高效率，因为它直接将本地系统文件中的数据导入到数据表中。我这里使用的是txt文件进行导入，先将文件上传到服务器上再进行导入。使用bulk insert导入数据文件时一定要注意以下几点：

1).bulk insert是导入本地文件系统中的数据，所以如果你的应用程序和你的数据库不是部署在同一台机器上时，你的程序千万不要使用bulk insert。建议还是用批处理。

2).使用bulk insert时,使用此数据库的用户的服务器角色必须是sysadmin或buikadmin.

 

调用此存储过程，方法1：

public void bulkInsert(final String tableName, final String filePath) {

		String sql = "{call dbo.importDataFile(?,?)}";
		jdbcTemplate.execute(sql, new PreparedStatementCallback<String>() {
			
			public String doInPreparedStatement(PreparedStatement pstmt)
			                throws SQLException, DataAccessException {
				pstmt.setString(1, tableName);
				pstmt.setString(2, filePath);
				pstmt.execute();
				return null;
			}
		});
	}

 方法2:

public void bulkInsert( String tableName, String filePath) {

	 String sql = "{call dbo.importDataFile( '" + tableName + "' ,'" + filePath + "')}";// 调用的sql
	 jdbcTemplate.getJdbcOperations().execute(sql);
}

 4.使用Pivot进行列转置生成视图

有关Pivot与UnPivot的详细信息请参考：http://technet.microsoft.com/zh-cn/library/ms177410.aspx

PIVOT:通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。

在表B201中的列production_code中有01,02,03...等生产指标值，monthly_amount列中存储的是每一个生产指标所对应的值.SQL代码如下所示：

create view B201_view_m_2008 as SELECT     TOP (100) PERCENT company_code, [01] AS [1], [02] AS [2], [03] AS [3], [04] AS [4], [05] AS [5], [06] AS [6], year, month
FROM         (SELECT     company_code, production_code, monthly_amount, year, month
 FROM  B201 WHERE   year = '2008') AS sourcetalbe PIVOT (max(monthly_amount) FOR production_code IN ([01], [02], [03], [04], [05], [06])) AS prvottalbe
ORDER BY month

 

 此SQL中pivot的语法为：

＜pivot_clause＞ ::=( aggregate_function ( value_column ) 
　FOR pivot_column 
　　IN ( ＜column_list＞ ) 
) 

 aggregate_function
  系统或用户定义的聚合函数。注意：不允许使用COUNT(*)系统聚合函数。
value_column
    PIVOT运算符用于进行计算的值列。与UNPIVOT一起使用时，value_column不能是输入table_source中的现有列的名称。
FOR pivot_column
    PIVOT运算符的透视列。pivot_column必须是可隐式或显式转换为nvarchar()的类型。
　使用UNPIVOT时，pivot_column是从table_source中提取输出的列名称，table_source中不能有该名称的现有列。
IN ( column_list )
　　在PIVOT子句中，column_list列出pivot_column中将成为输出表的列名的值。
　　在UNPIVOT子句中，column_list列出table_source中将被提取到单个pivot_column中的所有列名。
table_alias
　　输出表的别名

     在上面的创建视图的SQL语句中，如果没有Top(100) percent这句话，是不能在后面加上order by字句的，它会弹出出错信息对话框。

注意，该错误并不是说ORDER BY完全被禁止，而是说只有在两种情况下可以使用该子句即指定TOP或FOR XML。TOP和FOR
XML都是T-SQL的扩展，不是标准的SQL元素。TOP和ORDER BY以及ORDER BY和FOR XML是结果集规范（result set
specification）的一部分，但单独的ORDER BY并不是。因此，TOP和ORDER BY以及ORDER BY和FOR XML可以出现在视图定义中，而单独的ORDER BY却不行。

   其实在我上面的创建视图的SQL语句中，那条order by语句是没有意义的，虽然没有报错，但它并没有对我生成的视图的数据进行排序，而如果你改成top(10000)，把perscent去掉，也就是具体 前10000条数据，此时视图中的数据就会经过排序了，所以如果你一定要对你生成的视图进行排序的话，你可以将top（记录数）中的记录数的值大于你返回 的记录数就ok了，但不推荐这样子做。下面是对这一现象的解释：下面的解释来自文章：http://zonghe.17xie.com/book/10288517/39313.html

 

如果你需要把存储的数据返回客户端，可以在视图的外部查询中指定ORDER BY子句。

SELECT *
FROM dbo.B201_view_m_2008
ORDER BY month;

      查询视图时允许使用ORDER BY子句是有道理的，因为客户端希望得到一个物理对象——行集。而且客户端有理由期望得到经过排序的数据。

    在外部查询中使用TOP选项时，ORDER BY子句有两个用处：一个是确定要选择哪些行；另一个是确定结果集中行的顺序。但是，当在表表达式中（如在视图的查询中）使用TOP选项时，ORDER BY子句只有一个功能——确定要选择的行。这种情况下，视图还是表示一个有效的表。查询视图时，不保证行会按某个特定的顺序返回，除非在视图的外部查询中也包含ORDER
BY子句。当同时指定了TOP时，ORDER BY子句允许出现在视图中（或其他的表表达式中），因为它只具有逻辑功能，不具备物理功能。理解这些细节可以帮助你开发正确的代码并避免以错误的方式使用表表达式。
例如，创建排序视图的企图本身就是错误的，因为视图表示一个表，而表是不会对行排序的。众所周知，在SQL Server
2000中追求排序视图的开发人员会利用貌似系统漏洞的一个技巧。利用这个漏洞可以创建一个非常可笑的视图，在该视图中，指定TOP 100 PERCENT以及一个ORDER BY子句，就像下面的语句一样：

create VIEW dbo.VcustsWithOrders
AS
SELECT TOP (100) PERCENT
  Country, CustomerID, CompanyName, ContactName, ContactTitle,
  Address, City, Region, PostalCode, Phone, Fax
FROM Customers AS C
WHERE EXISTS
  (SELECT * FROM dbo.Orders AS O
   WHERE O.CustomerID = C.CustomerID)
ORDER BY Country;
GO

 注意   上面的代码假定你正在使用SQL Server 2005。因此，它使用分号来结束ALTER VIEW语句，并在TOP选项中使用括号。如果你要在SQL Server 2000中测试这段代码，需要删除括号和分号。

     视图中的ORDER BY子句意味着什么呢？它的意义不是很明确，因为TOP选项不是ANSI的标准语句。但如果你从集合的角度思考一下，就会认为ORDER BY子句没有什么意义了，因为你已经选择了所有符合筛选表达式（filter expression）的行。查询视图时，SQL Server不保证输出的顺序，除非在外部查询包含ORDER BY子句。SQL Server 2005联机丛书有一段对该行为的描述：“在视图、内联函数、派生表或子查询的定义中使用 ORDER BY 时，子句只用于确定 TOP 子句返回的行。ORDER BY 不保证在查询这些构造时得到有序结果，除非在查询本身中也指定了 ORDER BY。”即使优化器不忽略ORDER BY子句并返回排序的数据，你也不应该依赖于这种行为。有意思的是，当我在SQL Server
2000中运行下面的代码时，可以得到经过排序的数据:

SELECT Country, CustomerID, CompanyName
FROM dbo.VcustsWithOrders;

 而，当我在SQL Server 2005中运行下面的查询时，却得到未排序的输出。

通过分析两个版本的执行计划可以解释这种差异。erver 2000中的执行计划使用了一个排序运算符，按Country对数据排序。而SQL Server 2005的优化器则完全忽略了TOP (100) PERCENT 和 ORDER BY的组合。优化器知道TOP和ORDER
BY在这里是没意义的。因此，它并没有对数据排序。