MSSQLServer数据库的SQL语法以解决方案

数据类型

整数类型：有bit、int、smallint、tinyint 和bigint;

数值类型有decimal、numeric、money、smallmoney 、float 和real；

字符类型：有char、varchar、text、nchar、nvarchar和ntext；

日期时间类型：有datetime、smalldatetime 和timestamp；

二进制类型：image类型。

 

字符串拼接的方式MSSQLServer 中可以直接使用加号“+”来拼接字符串，比如'Hello'+'World'。

 

常用SQL的差异

限制结果集行数
在实现分页检索、排行榜等功能的时候，需要限制检索的结果集行数，不同的数据库系统对此的支持是不同的。
MSSQLServer中提供了TOP关键字用来返回结果集中的前N条记录，比如：
select top 5 * from T_Employee
order by FSalary Desc；

在MSSQLServer2005中还可以使用窗口函数ROW_NUMBER()实现限制结果集行数，比如：
SELECT ROW_NUMBER() OVER(ORDER BY FSalary),
FNumber,FName,FSalary,FAge
FROM T_Employee。

 

删除索引
DROP INDEX T_Person.idx1

 

取元数据信息的差异

取数据库信息
MSSQLServer中也可以通过函数来取得数据库的信息：

APP_NAME()函数返回当前会话的应用程序名称；

CURRENT_USER函数（注意这个函数不能带括号调用）返回当前登陆用户名；

HOST_NAME()函数返回工作站名。
不过，在MSSQLServer中如果要查询当前数据库名，则必须到系统表sysprocesses中查询，SQL语句如下：
select
dbname =
case when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end

from master..sysprocesses
where spid=@@SPID
系统表“master..sysprocesses”中存储了当前数据库系统中的进程信息，而“@@SPID”
则表示当前进程号。

取得所有表
MSSQLServer中的系统表sysobjects中记录了当前系统中定义的对象，其中xtype字段等于U的记录为表定义，因此取得当前数据库中所有表定义的SQL语句如下：
SELECT name FROM sysobjects where xtype='U'

取得指定Schema下的表
MSSQLServer中的系统表sysobjects中记录了当前系统中定义的对象，其中xtype字段等于U的记录为表定义，因此取得当前数据库中所有表定义的SQL语句如下（假设Schema名为demoschema）：
SELECT name FROM demoschema.sysobjects where xtype='U'

取得指定表的字段定义
MYSQLServer中取得指定表的字段定义（假设表名为mytable）：
SELECT syscols.name as COLUMN_NAME,
st.name as DATA_TYPE,
syscomm.text as DATA_DEFAULT,
syscols.isnullable as NULLABLE
FROM syscolumns syscols
left join systypes st on syscols.xusertype=st.xusertype
left join syscomments syscomm on syscols.cdefault=syscomm.id
where syscols.id=OBJECT_ID(N'mytable')
order by syscols.id,syscols.colorder

消除差异性的方案
由于不同数据库系统的语法有差异，所以如果想要开发的系统能够运行于多数据库系统下就必须通过一定的方法来消除这些差异性。消除差异性的主要方法有：为每种数据库编写不同的SQL语句；使用语法交集；使用抽象SQL；使用ORM工具；使用SQL翻译器。

为每种数据库编写不同的SQL语句

if(currentdatabase='MYSQL')
{
executeQuery(' SELECT * FROM T_Person LIMIT 0, 10');
}
else if(currentdatabase='MSSQLServer')
{
executeQuery(' SELECT TOP 10 * FROM T_Person');
} else if(currentdatabase='Oracle')
{
executeQuery(' SELECT * FROM T_Person WHERE ROWNUM <= 10');
}
else if(currentdatabase='DB2')

{
executeQuery(' SELECT * FROM T_Person FETCH FIRST 10 ROWS ONLY');
}

使用语法交集
为了避免多数据库的问题，在开发的时候避免使用各个数据库系统语法的差异部分，只使用所有数据库系统都支持的SQL语句。采用这种方案的时候能够比较好的解决多数据库的
问题，但是由于不能使用一些高级的语法，因此有的功能无法实现或者必须在宿主语言中通过代码来实现，这不仅限制了系统功能的实现而且降低了运行效率

使用SQL实体对象

开发人员不能直接编写SQL语句，只能编写抽象的语法结构，比如下面的代码来实现取得表T_Person中前10行数据的功能：
Query query = new Query();
query.SetColumn("*");
query.SetTableName("T_Person");
query.SetLimit(0,10);
ExecuteQuery(query);

系统框架会将Query翻译成对应数据库系统支持的SQL语句，比如：
MYSQL:
SELECT * FROM T_Person LIMIT 0, 10
MSSQLServer:
SELECT TOP 10 * FROM T_Person
Oracle:
SELECT * FROM T_Person WHERE ROWNUM <= 10
DB2:
SELECT * FROM T_Person FETCH FIRST 10 ROWS ONLY

采用这种方式能最大程度的利用目标数据库的高级特性，而且开发人员甚至不需要对SQL语法有任何了解，其缺点是编写的代码量增加了，同时如果要实现子查询、连接等复杂功能就编写非常冗长且难懂的代码。

使用ORM工具

Java中的Hibernate、EJB以及.Net中的Linq、NHibernate等都是非常优秀的ORM工具，这些ORM工具提供了以面向对象的方式使用数据库，开发人员只要操作实体对象就可以，从而避免了直接编写SQL语句。

Person person = new Person();
person.Name="Tom";
person.Age=22;
ormTool.Save(person);
ORM工具会将其翻译成如下的SQL语句：
INSERT INTO T_Person(FName,FAge) VALUES('Tom',22);

取得人员表中排名前十的人员：
Query query = new Query();
query.SetLimit(0,10);
query.SetEntityName("Person");
ormTool.ExecuteQuery(query);
系统框架会将Query翻译成对应数据库系统支持的SQL语句，比如：
MYSQL:
SELECT * FROM T_Person LIMIT 0, 10
MSSQLServer:
SELECT TOP 10 * FROM T_Person
Oracle:
SELECT * FROM T_Person WHERE ROWNUM <= 10
DB2:
SELECT * FROM T_Person FETCH FIRST 10 ROWS ONLY

使用SQL翻译器
是这样一种翻译器，它接受开发人员编写的SQL，然后会将SQL翻译成目标数据库系统支持的SQL语句。

SELECT TOP 10 * FROM T_Person
SQL翻译器会将其翻译成目标数据库系统支持的SQL语句：
MYSQL:
SELECT * FROM T_Person LIMIT 0, 10
MSSQLServer:
SELECT TOP 10 * FROM T_Person
Oracle:
SELECT * FROM T_Person WHERE ROWNUM <= 10
DB2:
SELECT * FROM T_Person FETCH FIRST 10 ROWS ONLY

SQL翻译器支持完整的SELECT、INSERT、UPDATE、DELETE以及DDL语句语法，而且支持任意复杂度的SQL语句，而且开发人员只要熟悉一种SQL语法就可以了，无需对SQL语句在不同数据库系统中的实现差异性有了解。

目前SQL翻译器产品有三个，分别是SwisSQL、LDBC和CowNewSQL，SwisSQL是一个非开源的商业公司的公开产品，LDBC和CowNewSQL是开源项目。

SwisSQL 支持的数据库非常多，包括DB2、ORACLE、MYSQL、INFORMIX、MSSQLSERVER、SYBASE、POSTGRESQL等

缺点：部分SQL语句解析速度太慢

          一些重要的SQL语句有翻译错误；
          代码可扩展性非常差，

LDBC的缺点如下：
（1）对复杂的SQL语句不支持，比如报表开发中常用的子查询、union等不支持；
（2）支持的函数数量非常少，对DateDiff、DateAdd、Trim、ABS等函数不支持；
（3）可扩展性差，特别是在函数这方面更差，

CowNewSQL的优点：

1，支持较复杂的语法。

2，支持 MYSQL、MSSQLServer、Oracle、DB2 等主流数据库系统。

3，支持的函数比较丰富

4，支持函数的各种常见别名，提高了容错性。

5，解析过程分层明确。

6，易于增加新的数据库支持。

7，易于增加新的函数支持

其缺点如下：对Informix、Sybase、Firbird、Posgtreql等数据库系统还没有提供支持；
目前仅能在Java 平台下使用，不过在其发布的开发计划列表中，已经将对.Net、Python等
语言的支持加入了开发计划中。

CowNewSQL翻译器

CowNewSQL支持如下几种类型的SQL语句：
CreateTable/AlterTable/DropTable/CreateIndex/DropIndex/Select/Insert/Delete/Update/Show；

支持子查询、Join、Union等高级的SQL特性；

支持日期（包括取当前日期、从日期中提取任意部分、计算日期差异、日期前后推算等）、数学（包括取绝对值、取PI值、四舍五入、对数计算、随机数等）、字符串（包括取子字符串、取字符串长度、字符串截断、大小写转换等）、基本数据处理（包括数字字符串互转、日期转字符串、非空判断等）等函数。

CowNewSQL支持的数据类型
整数类型：int、integer、tinyint、smallint。
布尔类型：bit、boolean。
字符类型：varchar、tinytext、longtext、text、longvarchar、char、nchar、nvarchar、clob、nclob。
数值类型：decimal、numeric、real、float、double。
时间日期类型：datetime、date、timestamp、time。
二进制类型：blob、tinyblob、longblob、binary、varbinary、longvarbinary、image。

CowNewSQL采用了特殊的方式来表示字符串的连接，格式如下：
字符串1||字符串2。

Create Table
CREATE TABLE tableName (
columnDefinition [,...]
[,PRIMARY KEY(column [,...])]
[,FOREIGN KEY(column [,...]) REFERENCES tableName ( column [,...])]
[UNIQUE (column [,...])]
)

例句：
（1）标准SQL：
create table T_Person(FId VARCHAR(20),FName varchar(20),FAge int,primary key FId)

MSSQLServer翻译结果：
CREATE TABLE T_Person (FId VARCHAR(20),FName VARCHAR(20),FAge INT,PRIMARY KEY (FId))

（2）标准SQL：
create table T_SaleInvoiceDetails(FId VARCHAR(20),FParentId varchar(20),
FMaterialId varchar(20),FCount int default 0,FPrice decimal(10,2),
primary key FId,
FOREIGN KEY FParentId REFERENCES T_SaleInvoice(FId),
FOREIGN KEY FMaterialId REFERENCES T_Material(FId))
MSSQLServer 的翻译结果:
CREATE TABLE T_SaleInvoiceDetails (FId VARCHAR(20),FParentId
VARCHAR(20),FMaterialId VARCHAR(20),FCount INT DEFAULT 0,FPrice
NUMERIC(10,2),PRIMARY KEY (FId),FOREIGN KEY (FParentId) REFERENCES
T_SaleInvoice(FId),FOREIGN KEY (FMaterialId) REFERENCES T_Material(FId))

DropTable
DROP TABLE tableName

Create Index
CREATE INDEX indexName ON tableName ( columnName [,...] )

Drop Index
DROP INDEX tableName.indexName

标准SQL： DROP INDEX T_Person.idx1
MSSQLServer 的翻译结果：
DROP INDEX T_Person.idx1

SELECT
SELECT [DISTINCT] TOP N { * | selectList }
FROM tableList
[WHERE condition ]
[ GROUP BY columnName [,...] ]
[ HAVING condition ]
[ ORDER BY columnName [{ASC|DESC}] [,...] ]
[UNION [ALL]] SELECT

标准SQL：
select trim(person.FName),bill.FNumber from T_SaleInvoice as bill left join T_Person person on
bill.FSalesPersonId=person.FId
MSSQLServer 的翻译结果：
SELECT LTRIM(RTRIM(person.FName)),bill.FNumber FROM T_SaleInvoice bill LEFT JOIN
T_Person person ON bill.FSalesPersonId = person.FId

Insert
INSERT INTO tableName [ (columnName [,...] ) ] VALUES ( value [,...] )
INSERT INTO tableName [ (columnName [,...] ) ] SELECT ...

标准SQL：
insert into T_SaleInvoice values('sv001','sv001','p001',{'2007-08-8'})
MSSQLServer 的翻译结果：
INSERT INTO T_SaleInvoice VALUES ('sv001', 'sv001', 'p001', '2007-08-8')

Delete
DELETE [*] FROM tableName
[WHERE condition ]

Update
UPDATE tableName
SET columnName=expression [,...]
[WHERE condition ]

Show 语句

主要用来查看系统中的表定义、字段定义、支持的函数等。由于各个数据库系统中取得这些元信息的方式各有不同，经常需要关联查询很多系统表才能得到，为而CowNewSQL创新性的设计了Show系列语句，这样使用非常短的语句就可以实现以前需要编写很复杂的语句才能实现的功能。

Show语句语法列表：
（1）SHOW TABLES：显示系统中默认Schema 下的所有表的表名。
（2）SHOW TABLES SchemaName定Schema 下的所有表的表名。比如：SHOWTABLES DEMO。
（3）SHOW FUNCTIONS：显示系统支持的函数的列表。
（4）SHOW FUNCTIONCOLUMNS：显示系统支持的函数参数以及返回值的详细说明。

（5）SHOW TABLECOLUMNS tablename 列信息。比如：SHOW TABLECOLUMNS table_1。