您的问题似乎不完整，您是想询问关于C语言编程的某个具体问题吗？比如C语言的语法、编程技巧、项目开发等。请提供更具体的信息，这样我才能给出更准确的回答。

本文共计1616个文字，预计阅读时间需要7分钟。

目录- hisql查询示例- 单表查询- group by查询- having查询- join多表查询- 分页查询- hisql实现参数化- 链式查询- hisql语句和链式查询混合使用- 前置数据的种类非常多，每种数据都支持SQL语句

目录

• hisql查询样例
• 单表查询
• group by查询
• having 查询
• join 多表查询
• 分页
• hisql 实现参数化
• 链式查询
• hisql语句和链式查询混用

目前数据的种类非常多，每种数据都支持sql语句，但是大家发现没有每种数据的SQL都有自己的语法特性，都是SQL语句都没有一个特定的语法标准，导致开发人员在开发的过程中无法任意选库（如果用自己不熟的库都会要学习一遍SQL语法），主要是学习成本太高

那么有没有一种工具能够统一下sql语法规则，只要学一种sql语句就可以跨库执行，让开发人员不用学习每种库的SQL语句呢？

下面就给大家介绍一款开源工具hisql源码hisql介绍

hisql查询样例

单表查询

根据hisql语句通过ToSql()方法生成目标sql的原生sql

var _sql = sqlClient.HiSql("select * from HTest01 where CreateTime>='2022-02-17 09:27:50' and CreateTime<='2022-03-22 09:27:50'").ToSql();

hisql生成的sqlserver 的sql 如下的所示

select [htest01].[SID],[htest01].[UName],[htest01].[Age],[htest01].[Salary],[htest01].[Descript],[htest01].[CreateTime],[htest01].[CreateName],[htest01].[ModiTime],[htest01].[ModiName] from [HTest01] as [HTest01] where [htest01].[CreateTime] >= '2022-02-17 09:27:50.000' and [htest01].[CreateTime] <= '2022-03-22 09:27:50.000'

hisql生成mysql的sql如下所示

select `htest01`.`SID`,`htest01`.`UName`,`htest01`.`Age`,`htest01`.`Salary`,`htest01`.`Descript`,`htest01`.`CreateTime`,`htest01`.`CreateName`,`htest01`.`ModiTime`,`htest01`.`ModiName` from `htest01` as `htest01` where `htest01`.`CreateTime` >= '2022-02-17 09:27:50.000' and `htest01`.`CreateTime` <= '2022-03-22 09:27:50.000'

hisql生成postgresql 的sql下所示

select "htest01"."SID","htest01"."UName","htest01"."Age","htest01"."Salary","htest01"."Descript","htest01"."CreateTime","htest01"."CreateName","htest01"."ModiTime","htest01"."ModiName" from "HTest01" as "htest01" where "htest01"."CreateTime" >= '2022-02-17 09:27:50.000' and "htest01"."CreateTime" <= '2022-03-22 09:27:50.000'

大家可以会问 明明是"select * from HTest01 where CreateTime>='2022-02-17 09:27:50' and CreateTime<='2022-03-22 09:27:50'"用的*查询 生成的sql语句为什么会直接列出所有字段呢？ 熟HiSql的网友都应该知道这里其实隐藏了一个功能就是用户可以自定义字段排序在系统表Hi_FieldModel中可配置

group by查询

hisql 默认支持的常用函数max,count,min,sum,avg

string sql = sqlClient.HiSql("select FieldName, count(FieldName) as NAME_count,max(FieldType) as FieldType_max from Hi_FieldModel group by FieldName").ToSql();

hisql生成的sqlserver 的sql 如下的所示

select [hi_fieldmodel].[FieldName],count(*) as NAME_count,max([hi_fieldmodel].[FieldType]) as FieldType_max from [Hi_FieldModel] as [Hi_FieldModel] group by [hi_fieldmodel].[FieldName]

hisql生成mysql的sql如下所示

select `hi_fieldmodel`.`FieldName`,count(*) as NAME_count,max(`hi_fieldmodel`.`FieldType`) as FieldType_max from `Hi_FieldModel` as `hi_fieldmodel` group by `hi_fieldmodel`.`FieldName`

hisql生成postgresql 的sql下所示

select "hi_fieldmodel"."FieldName",count(*) as NAME_count,max("hi_fieldmodel"."FieldType") as FieldType_max from "Hi_FieldModel" as "hi_fieldmodel" group by "hi_fieldmodel"."FieldName"

having 查询

上次有网友说having 实现起来很麻烦通过正则表达式解析很难实现，我只能说很多网友对正则表达式的理解还不是很深，正则表达式已经应用于种类型的编译器中，下面就来演示一下Having

string sql_having = sqlClient.HiSql("select FieldName, count(FieldName) as NAME_count,max(FieldType) as FieldType_max from Hi_FieldModel group by FieldName having count(FieldName) > 1").ToSql();

hisql生成的sqlserver 的sql 如下的所示

select [hi_fieldmodel].[FieldName],count(*) as NAME_count,max([hi_fieldmodel].[FieldType]) as FieldType_max from [Hi_FieldModel] as [Hi_FieldModel] group by [hi_fieldmodel].[FieldName] having count(*) > 1

hisql生成mysql的sql如下所示

select `hi_fieldmodel`.`FieldName`,count(*) as NAME_count,max(`hi_fieldmodel`.`FieldType`) as FieldType_max from `Hi_FieldModel` as `hi_fieldmodel` group by `hi_fieldmodel`.`FieldName` having count(*) > 1

hisql生成postgresql 的sql下所示

select "hi_fieldmodel"."FieldName",count(*) as NAME_count,max("hi_fieldmodel"."FieldType") as FieldType_max from "Hi_FieldModel" as "hi_fieldmodel" group by "hi_fieldmodel"."FieldName" having count(*) > 1

join 多表查询

hisql inner join 和 in 操作语法

var sql = sqlClient.HiSql("select a.tabname from hi_fieldmodel as a inner join Hi_TabModel as b on a.tabname =b.tabname inner join Hi_TabModel as c on a.tabname=c.tabname where a.tabname='h_test' and a.FieldType in (11,41,21) ").ToSql();

hisql生成的sqlserver 的sql 如下的所示

select [a].[TabName] from [Hi_FieldModel] as [a] inner join [Hi_TabModel] as [b] on [a].[TabName] = [b].[TabName] inner join [Hi_TabModel] as [c] on [a].[TabName] = [c].[TabName] where [a].[TabName] = 'h_test' and [a].[FieldType] in (11,41,21)

hisql生成mysql的sql如下所示

select `a`.`TabName` from `Hi_FieldModel` as `a` inner join `Hi_TabModel` as `b` on `a`.`TabName` = `b`.`TabName` inner join `Hi_TabModel` as `c` on `a`.`TabName` = `c`.`TabName` where `a`.`TabName` = 'h_test' and `a`.`FieldType` in (11,41,21)

hisql生成postgresql 的sql下所示

select "a"."TabName" from "Hi_FieldModel" as "a" inner join "Hi_TabModel" as "b" on "a"."TabName" = "b"."TabName" inner join "Hi_TabModel" as "c" on "a"."TabName" = "c"."TabName" where "a"."TabName" = 'h_test' and "a"."FieldType" in (11,41,21)

分页

hisql 分页 只要在日常查询上增加Take()每页显示多少数据Skip()显示第几页

var sql = sqlClient.HiSql("select a.tabname from hi_fieldmodel as a inner join Hi_TabModel as b on a.tabname =b.tabname inner join Hi_TabModel as c on a.tabname=c.tabname where a.tabname='h_test' and a.FieldType in (11,41,21) ").Take(2).Skip(2).ToSql();

hisql生成的sqlserver 的sql 如下的所示

select [TabName] from ( select ROW_NUMBER() OVER(Order by [a].[FieldType] ASC) AS _hi_rownum_, [a].[TabName] from [Hi_FieldModel] as [a] inner join [Hi_TabModel] as [b] on [a].[TabName] = [b].[TabName] inner join [Hi_TabModel] as [c] on [a].[TabName] = [c].[TabName] where [a].[TabName] = 'h_test' and [a].[FieldType] in (11,41,21) ) as hi_sql where hi_sql._hi_rownum_ BETWEEN (2-1)*2+1 and 2*2 order by _hi_rownum_ asc

hisql生成mysql的sql如下所示

select `TabName` from ( select ROW_NUMBER() OVER(Order by `a`.`FieldType` ASC) AS `_hi_rownum_`, `a`.`TabName` from `Hi_FieldModel` as `a` inner join `Hi_TabModel` as `b` on `a`.`TabName` = `b`.`TabName` inner join `Hi_TabModel` as `c` on `a`.`TabName` = `c`.`TabName` where `a`.`TabName` = 'h_test' and `a`.`FieldType` in (11,41,21) ) as hi_sql order by `_hi_rownum_` asc limit 2,2

hisql生成postgresql 的sql下所示

select "TabName" from ( select ROW_NUMBER() OVER(Order by "a"."FieldType" ASC) AS "_hi_rownum_", "a"."TabName" from "Hi_FieldModel" as "a" inner join "Hi_TabModel" as "b" on "a"."TabName" = "b"."TabName" inner join "Hi_TabModel" as "c" on "a"."TabName" = "c"."TabName" where "a"."TabName" = 'h_test' and "a"."FieldType" in (11,41,21) ) as hi_sql order by "_hi_rownum_" asc limit 2 OFFSET 2

hisql 实现参数化

参数化可以有效的防注入，通过前端可以拼接的hisql语句通过参数化的方式传入， 这样就解决了注入问题

string sql1= sqlClient.HiSql("select * from hi_tabmodel where tabname=@tabname ", new { TabName="H_test" ,FieldName="DID"}).ToSql(); string sql2= sqlClient.HiSql("select * from hi_tabmodel where tabname=@tabname or TabType in( @TabType)", new { TabName="H_test" , TabType =new List<int> { 1,2,3,4} }).ToSql(); string sql3 = sqlClient.HiSql("select * from hi_tabmodel where tabname=@tabname ", new Dictionary<string, object> { { "TabName", "H_test" } }).ToSql();

链式查询

hisql除了提供hisql语法外还提供链式查询方法如下所示

string sql = sqlClient.Query("Hi_FieldModel").As("A").Field("A.FieldType") .Join("Hi_TabModel").As("B").On(new HiSql.JoinOn() { { "A.TabName", "B.TabName" } }) .Where("A.TabName='GD_UniqueCodeInfo'").Group(new GroupBy { { "A.FieldType" } }) .Sort("A.FieldType asc", "A.TabName asc") .Take(2).Skip(2) .ToSql();

hisql语句和链式查询混用

string sql = sqlClient.HiSql("select A.FieldType from Hi_FieldModel as A ") .Where("A.TabName='GD_UniqueCodeInfo'").Group(new GroupBy { { "A.FieldType" } }) .Sort("A.FieldType asc", "A.TabName asc") .Take(2).Skip(2) .ToSql();

到此这篇关于C#实现定义一套中间SQL可以跨库执行的SQL语句的文章就介绍到这了,更多相关C#跨库执行SQL语句内容请搜索自由互联以前的文章或继续浏览下面的相关文章希望大家以后多多支持自由互联！