喝Coffee学Java

--测试数据
if exists(select [id] from sysobjects where name='student')
	drop table student
go

create table student
(
	sname varchar(20) not null,
	sclass varchar(20) not null
)
go

alter table student add constraint pk_student primary key(sname,sclass);
go

if exists(select [id] from sysobjects where name='score')
	drop table score
go

create table score
(
	scoreid int not null primary key identity(1,1),
	sname varchar(20) not null,
	sclass varchar(20) not null,
	score float
)
go

alter table score add constraint fk_score foreign key(sname,sclass) references student(sname,sclass);
go

insert into student values('aaa','1');
insert into student values('ccc','2');
insert into student values('bbb','2');
insert into student values('ddd','2');
insert into student values('ccc','3');
insert into student values('ddd','4');

insert into score(sname,sclass,score) values('aaa',1,66);
insert into score(sname,sclass,score) values('bbb',2,77);
insert into score(sname,sclass,score) values('ccc',2,88);
insert into score(sname,sclass,score) values('ddd',4,99);

/*
--外键插入null值
insert into score(score) values(66);
insert into score(score) values(77);
insert into score(score) values(77);
*/

select * from student;
select * from score;

--复合主键内连接
select * from student t inner join score s on t.sname = s.sname and t.sclass = s.sclass;

--复合主键左外连接
select t.sname,t.sclass from student t left outer join score s on t.sname = s.sname and t.sclass = s.sclass where s.scoreid is null;

测试过程中发现= null和is null的区别，特引用下面一篇文章加以说明：
SQL Server Null的比较运算（转）

今天我在写sql的时候发现用

UPDATE dbo.tblInvTransaction
SET Area_Type = 'Gross'，
WHERE (Area_Type = null)

结果为零。


然后查到下面这个文章
改成

UPDATE dbo.tblInvTransaction
SET Area_Type = 'Gross'
WHERE (Area_Type is NULL )
就ok了



前几天写一个数据库查询程序，碰到的一个问题，是关于SQL Server的Null值的比较运算的。一般情况下我们查询空值或者非空值的时候，用的是is null/is not null，而很少用=/<>。但是在我的这个程序中，没有用is这样的关键字，而是用=/<>这样的比较元算符号，这就碰到了一些问题。
问题起源于一个Web查询页面，因为问题比较复杂的，所以简化一下来说明。
在页面上用户可以自由选择数据表的某些字段，填写该字段的查询条件，先是选择比较运算符号（=，<>等），然后填写值。提交之后，就需要创建一个SQL语句，查询条件的各部分由不同的程序模块创建。这里涉及两个程序模块，一个模块根据提交创建比较运算符号，一个模块负责创建比较值模块。在创建值模块中有这样一个规则，“如果提交的值是空的，把该值设为Null”。
但是我发现，如果比价值为Null的时候，同样一个SQL查询语句放在存储过程里边查询和通过应用程序直接查询的结果是不一样的。
查了查SQL Server文档，发现Null值的比较运算，存在两种规则：
在SQL2000中Null值的比较运算有两种规则。一种是ANSI SQL（SQL-92）规定的Null值的比较取值结果都为False，既Null=Null取值也是False。另一种不准循ANSI SQL标准，即Null=Null为True。
以一张表T的查询为例。

表T存在下面的数据：
RowId Data
--------------
1 'test'
2 Null
3 'test1'

按照ANSI SQL标准，下面的两个查询都不返回任何行：
Query1: select * from T where Data=null
Query2: select * from T where Data<>null
而按照非ANSI SQL标准，查询1将返回第二行，查询2返回1、3行。
ANSI SQL标准中取得Null值的行需要用下面的查询：
select * from T where Data is null
反之则用is not null。由此可见非ANSI SQL标准中Data=Null等同于Data Is Null，Data<>Null等同于Data Is Not Null。

而控制采用那一种规则，需要使用命令SET ANSI_NULLS [ON/OFF]。ON值采用ANSI SQL标准，OFF值采用非标准模式。另外SET ANSI_DEFAULTS [ON/OFF]命令也可以实现标准的切换，只是这个命令控制的是一组符合SQL-92标准的设置，其中就包括Null值的标准。

默认情况下，数据库管理程序（DB-Library）是SET ANSI_NULLS为OFF的。但是我们的大多数应用程序，都是通过ODBC或者OLEDB来访问数据库的，作为一种开放兼容的数据库访问程序，或许是兼容性的考虑，SET ANSI_NULLS值设置为ON。这样一来带来的一些问题是需要注意的。像存储过程或者自定义函数这样的应用程序都是基于DB-Library的，默认情况下，SET ANSI_NULLS为OFF，并且在这样的程序中，不能使用SET ANSI_NULLS在一个环境中修改规则，只能修改数据库配置参数。

考虑下面这种情况。
你的应用程序使用ADODB来访问数据库，采用OleDb或者ODBC数据提供程序。对于前面的查询1：
select * from T where Data=null
你可以直接发送命令取得结果集，也可以把它放到存储过程当中。但是他们的查询结果是不一样的。如果直接使用查询命令，什么结果也没有，而如果访问存储过程，你获得第2行的数据。

我写了一个.Net程序来验证这一点。同时也为了验证.Net SqlClient的SET ANSI_NULLS的设置，由于SqlClient不是通过OleDb或者ODBC这些数据提供程序来访问SQL Server，而是直接对SQL Server进行访问，本来我以为它会采用SQL Server默认的设置，但是结果恰恰相反，它的默认设置和OleDb、ODBC一样。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
public class AnsiNullsTest{
public static void Main(String[] args){
IDbConnection conn;
String connType = "SqlClient";
if(args.Length>0)connType = args[0];
if(connType.ToUpper()=="OLEDB"){
Console.WriteLine("Connection Type:OLEDB");
conn = new OleDbConnection("Provider=SQLOLEDB.1;User ID=sa;PWD=test;Initial Catalog=TEST;Data Source=TEST");
}else if(connType.ToUpper()=="ODBC"){
Console.WriteLine("Connection Type:ODBC");
conn = new OdbcConnection("Driver={SQL Server};UID=sa;PWD=test;Database=TEST;Server=TEST");
}else{
Console.WriteLine("Connection Type:SQLClient");
conn = new SqlConnection("Server=TEST;Database=TEST;User ID=sa;PWD=test");
}
Test(conn);
}
public static void Test(IDbConnection conn){
String query1 = "select 'Test' where null=null";
String query2 = "exec p_Test"; //存储过程中是一样的SQL语句
IDbCommand cmd;
IDataReader reader;
Console.WriteLine("print 'Test' set ansi_nulls off");
try{
cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = query1;
reader = cmd.ExecuteReader();
Console.WriteLine("command:" + query1);
while(reader.Read()){
Console.WriteLine("result:" + reader[0].ToString());
}
reader.Close();
cmd.CommandText = query2;
reader = cmd.ExecuteReader();
Console.WriteLine("command:" + query2);
while(reader.Read()){
Console.WriteLine("result:" + reader[0].ToString());
}
reader.Close();
}
catch(Exception ex){
Console.WriteLine(ex.Message);
}
finally{
conn.Close();
}

}
}

它有一个参数，根据参数采用不同的参数值采用不同的数据库访问程序。命令对象作了两次查询，一次是SQL查询命令，一次是调用存储过程。语句都是一样，但是结果不一样。