第C#實(shí)現(xiàn)定義一套中間SQL可以跨庫執(zhí)行的SQL語句(案例詳解)目錄hisql查詢樣例單表查詢groupby查詢having查詢join多表查詢分頁hisql實(shí)現(xiàn)參數(shù)化鏈?zhǔn)讲樵僪isql語句和鏈?zhǔn)讲樵兓煊媚壳皵?shù)據(jù)的種類非常多，每種數(shù)據(jù)都支持sql語句，但是大家發(fā)現(xiàn)沒有每種數(shù)據(jù)的SQL都有自己的語法特性，都是SQL語句都沒有一個(gè)特定的語法標(biāo)準(zhǔn)，導(dǎo)致開發(fā)人員在開發(fā)的過程中無法任意選庫（如果用自己不熟的庫都會(huì)要學(xué)習(xí)一遍SQL語法），主要是學(xué)習(xí)成本太高

那么有沒有一種工具能夠統(tǒng)一下sql語法規(guī)則，只要學(xué)一種sql語句就可以跨庫執(zhí)行，讓開發(fā)人員不用學(xué)習(xí)每種庫的SQL語句呢？

下面就給大家介紹一款開源工具h(yuǎn)isql源碼hisql介紹

hisql查詢樣例

單表查詢

根據(jù)hisql語句通過ToSql()方法生成目標(biāo)sql的原生sql

var_sql=sqlClient.HiSql("select*fromHTest01whereCreateTime='2025-02-1709:27:50'andCreateTime='2025-03-2209: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]='2025-02-1709:27:50.000'and[htest01].[CreateTime]='2025-03-2209: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`='2025-02-1709:27:50.000'and`htest01`.`CreateTime`='2025-03-2209: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"='2025-02-1709:27:50.000'and"htest01"."CreateTime"='2025-03-2209:27:50.000'

大家可以會(huì)問明明是select*fromHTest01whereCreateTime=2025-02-1709:27:50andCreateTime=2025-03-2209:27:50用的*查詢生成的sql語句為什么會(huì)直接列出所有字段呢？熟HiSql的網(wǎng)友都應(yīng)該知道這里其實(shí)隱藏了一個(gè)功能就是用戶可以自定義字段排序在系統(tǒng)表Hi_FieldModel中可配置

groupby查詢

hisql默認(rèn)支持的常用函數(shù)max,count,min,sum,avg

stringsql=sqlClient.HiSql("selectFieldName,count(FieldName)asNAME_count,max(FieldType)asFieldType_maxfromHi_FieldModelgroupbyFieldName").ToSql();

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

select[hi_fieldmodel].[FieldName],count(*)asNAME_count,max([hi_fieldmodel].[FieldType])asFieldType_maxfrom[Hi_FieldModel]as[Hi_FieldModel]

groupby[hi_fieldmodel].[FieldName]

hisql生成mysql的sql如下所示

select`hi_fieldmodel`.`FieldName`,count(*)asNAME_count,max(`hi_fieldmodel`.`FieldType`)asFieldType_maxfrom`Hi_FieldModel`as`hi_fieldmodel`

groupby`hi_fieldmodel`.`FieldName`

hisql生成postgresql的sql下所示

select"hi_fieldmodel"."FieldName",count(*)asNAME_count,max("hi_fieldmodel"."FieldType")asFieldType_maxfrom"Hi_FieldModel"as"hi_fieldmodel"

groupby"hi_fieldmodel"."FieldName"

having查詢

上次有網(wǎng)友說having實(shí)現(xiàn)起來很麻煩通過正則表達(dá)式解析很難實(shí)現(xiàn)，我只能說很多網(wǎng)友對(duì)正則表達(dá)式的理解還不是很深，正則表達(dá)式已經(jīng)應(yīng)用于種類型的編譯器中，下面就來演示一下Having

stringsql_having=sqlClient.HiSql("selectFieldName,count(FieldName)asNAME_count,max(FieldType)asFieldType_maxfromHi_FieldModelgroupbyFieldNamehavingcount(FieldName)1").ToSql();

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

select[hi_fieldmodel].[FieldName],count(*)asNAME_count,max([hi_fieldmodel].[FieldType])asFieldType_maxfrom[Hi_FieldModel]as[Hi_FieldModel]

groupby[hi_fieldmodel].[FieldName]

havingcount(*)1

hisql生成mysql的sql如下所示

select`hi_fieldmodel`.`FieldName`,count(*)asNAME_count,max(`hi_fieldmodel`.`FieldType`)asFieldType_maxfrom`Hi_FieldModel`as`hi_fieldmodel`

groupby`hi_fieldmodel`.`FieldName`

havingcount(*)1

hisql生成postgresql的sql下所示

select"hi_fieldmodel"."FieldName",count(*)asNAME_count,max("hi_fieldmodel"."FieldType")asFieldType_maxfrom"Hi_FieldModel"as"hi_fieldmodel"

groupby"hi_fieldmodel"."FieldName"

havingcount(*)1

join多表查詢

hisqlinnerjoin和in操作語法

varsql=sqlClient.HiSql("selecta.tabnamefromhi_fieldmodelasainnerjoinHi_TabModelasbona.tabname=b.tabnameinnerjoinHi_TabModelascona.tabname=c.tabnamewherea.tabname='h_test'anda.FieldTypein(11,41,21)").ToSql();

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

select[a].[TabName]from[Hi_FieldModel]as[a]

innerjoin[Hi_TabModel]as[b]on[a].[TabName]=[b].[TabName]innerjoin[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`

innerjoin`Hi_TabModel`as`b`on`a`.`TabName`=`b`.`TabName`innerjoin`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"

innerjoin"Hi_TabModel"as"b"on"a"."TabName"="b"."TabName"innerjoin"Hi_TabModel"as"c"on"a"."TabName"="c"."TabName"

where"a"."TabName"='h_test'and"a"."FieldType"in(11,41,21)

分頁

hisql分頁只要在日常查詢上增加Take()每頁顯示多少數(shù)據(jù)Skip()顯示第幾頁

varsql=sqlClient.HiSql("selecta.tabnamefromhi_fieldmodelasainnerjoinHi_TabModelasbona.tabname=b.tabnameinnerjoinHi_TabModelascona.tabname=c.tabnamewherea.tabname='h_test'anda.FieldTypein(11,41,21)").Take(2).Skip(2).ToSql();

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

select[TabName]from(

selectROW_NUMBER()OVER(Orderby[a].[FieldType]ASC)AS_hi_rownum_,[a].[TabName]from[Hi_FieldModel]as[a]

innerjoin[Hi_TabModel]as[b]on[a].[TabName]=[b].[TabName]innerjoin[Hi_TabModel]as[c]on[a].[TabName]=[c].[TabName]

where[a].[TabName]='h_test'and[a].[FieldType]in(11,41,21)

)ashi_sql

wherehi_sql._hi_rownum_BETWEEN(2-1)*2+1and2*2orderby_hi_rownum_asc

hisql生成mysql的sql如下所示

select`TabName`from(

selectROW_NUMBER()OVER(Orderby`a`.`FieldType`ASC)AS`_hi_rownum_`,`a`.`TabName`from`Hi_FieldModel`as`a`

innerjoin`Hi_TabModel`as`b`on`a`.`TabName`=`b`.`TabName`innerjoin`Hi_TabModel`as`c`on`a`.`TabName`=`c`.`TabName`

where`a`.`TabName`='h_test'and`a`.`FieldType`in(11,41,21)

)ashi_sql

orderby`_hi_rownum_`asc

limit2,2

hisql生成postgresql的sql下所示

select"TabName"from(

selectROW_NUMBER()OVER(Orderby"a"."FieldType"ASC)AS"_hi_rownum_","a"."TabName"from"Hi_FieldModel"as"a"

innerjoin"Hi_TabModel"as"b"on"a"."TabName"="b"."TabName"innerjoin"Hi_TabModel"as"c"on"a"."TabName"="c"."TabName"

where"a"."TabName"='h_test'and"a"."FieldType"in(11,41,21)

)ashi_sql

orderby"_hi_rownum_"asc

limit2OFFSET2

hisql實(shí)現(xiàn)參數(shù)化

參數(shù)化可以有效的防注入，通過前端可以拼接的hisql語句通過參數(shù)化的方式傳入，這樣就解決了注入問題

stringsql1=sqlClient.HiSql("select*fromhi_tabmodelwheretabname=@tabname",new{TabName="H_test",FieldName="DID"}).ToSql();

stringsql2=sqlClient.HiSql("select*fromhi_tabmodelwheretabname=@tabnameorTabTypein(@TabType)",new{TabName="H_test",TabType=newListint{1,2,3,4}}).ToSql();

stringsql3=sql