个人中心
文章发布
退出
作者热门文章
- VisualStudio2022插件的安装及使用-编程手把手系列文章
- pprof-在现网场景怎么用
- C#实现的下拉多选框，下拉多选树，多级节点
- 【学习笔记】基础数据结构：猫树
56
4
hi 大家好，我是三合，在过往的岁月中，我曾经想过要写以下这些工具 。
但是无一例外，都失败了，因为要实现以上这些需求，都需要一个核心的类库，即sql解析引擎，遗憾的是，我没有找到合适的，这是我当初寻找的轨迹 。
JsonConvert.SerializeObject(statements.First(), Formatting.Indented)
// Elided for readability
{
"Query": {
"Body": {
"Select": {
"Projection": [
{
"Expression": {
"Ident": {
"Value": "a",
"QuoteStyle": null
}
}
}
...
额，怎么说呢，这语法树也太丑了点吧，同时非常难以理解，跟我想象中的完全不一样啊，于是也只能pass.
接下来我又发现了另外一些基于antlr来解析sql的类库，比如SQLParser,因为代码是antlr自动生成的，比较难以进行手动优化，所以还是pass.
最后我还发现了另外一个gsp的sqlparser,但它是收费的，而且巨贵无比，也pass.
找了一圈下来，我发现符合我要求的类库并不存在，所以我上面的那些想法，也一度搁浅了，但每一次的搁浅，都会使我内心的不甘加重一分，终于有一天，我下定决心，自己动手，丰衣足食，所以最近花了大概3个月时间，从头开始写了一个sql解析引擎，包括词法解析器到语法分析器，不依赖任何第三方组件，纯c#代码，在通过了156个各种各样场景的单元测试以及各种真实的业务环境验证后，今天它SqlParser.Net1.0.0正式发布了，本项目基于MIT协议开源，有以下优点， 。
SqlParser.Net是一个免费，功能全面且高性能的sql解析引擎类库，它可以帮助你简单快速高效的解析和处理sql.
接下来，我将介绍SqlParser.Net的用法 。
你可以运行以下命令在你的项目中安装 SqlParser.Net .
PM> Install-Package SqlParser.Net 。
netstandard2.0 。
让我们一起看一个最简单的select语句是如何解析的 。
var sql = "select * from test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
}
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test"
}
}
}
};
以上面为例子，抽象语法树的所有叶子节点均为sqlExpression的子类，且各种sqlExpression节点可以互相嵌套，组成一颗复杂无比的树，其他sql解析引擎还分为statement和expression，我认为过于复杂，所以全部统一为sqlExpression，顶级的sqlExpression总共分为4种， 。
这4种顶级语句中，我认为最复杂的是查询语句，因为查询组合非常多，要兼容各种各样的情况，其他3种反而非常简单。现阶段，sqlExpression的子类一共有38种，我将在下面的演示中，结合例子给大家讲解一下.
如上例子，SqlSelectExpression代表一个查询语句，SqlSelectQueryExpression则是真正具体的查询语句，他包括了 。
其中Columns是一个列表，他的每一个子项都是一个SqlSelectItemExpression，他的body代表一个逻辑子句，逻辑子句的值，可以为以下这些 。
包括order by，partition by，group by,between,in,case when后面跟着的都是逻辑子句，这个稍后会演示，在这个例子中，因为是要查询所有列，所以仅有一个SqlSelectItemExpression，他的body是SqlAllColumnExpression（代表所有列），From代表要查询的数据源，在这里仅单表查询，所以From的值为SqlTableExpression（代表单表），表名是一个SqlIdentifierExpression，即标识符表达式，表示这是一个标识符，在SQL中，标识符（Identifier）是用于命名数据库对象的名称。这些对象可以包括表、列、索引、视图、模式、数据库等。标识符使得我们能够引用和操作这些对象，在这里，标识符的值为test，表示表名为test.
var sql = "select id AS bid,t.NAME testName from test t";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "id",
},
Alias = new SqlIdentifierExpression()
{
Value = "bid",
},
},
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "testName",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
在上面这个例子中，我们指定了要查询2个字段，分别是id和t.NAME，此时Columns列表里有2个值， 第一个SqlSelectItemExpression包含了 。
第二个SqlSelectItemExpression的body里是一个SqlPropertyExpression，代表这是一个属性表达式，SqlPropertyExpression它包含了 。
合起来则代表t表的name字段，而第二个SqlSelectItemExpression也有列别名，即testName，这个查询也是单表查询，但SqlTableExpression他多了一个Alias别名字段，即表示，表别名为t.
var sql = "select 1+2 from test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlBinaryExpression()
{
Left = new SqlNumberExpression()
{
Value = 1M,
},
Operator = SqlBinaryOperator.Add,
Right = new SqlNumberExpression()
{
Value = 2M,
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
在这个例子中，要查询的字段的值为一个二元表达式SqlBinaryExpression，他包含了 。
这个例子证明了，SqlSelectItemExpression代表一个逻辑子句，而不仅仅是某个字段.
var sql = "select ''' ''',3,true FROM test";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlStringExpression()
{
Value = "' '"
},
},
new SqlSelectItemExpression()
{
Body = new SqlNumberExpression()
{
Value = 3M,
},
},
new SqlSelectItemExpression()
{
Body = new SqlBoolExpression()
{
Value = true
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
在这个例子中，要查询的3个字段为字符串，数字和布尔值，字符串表达式即SqlStringExpression，body里即字符串的值' '，数字表达式即SqlNumberExpression，值为3，布尔表达式即SqlBoolExpression，值为true； 。
var sql = "select LOWER(name) FROM test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "LOWER",
},
Arguments = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name",
},
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
在这个例子中，要查询的表达式是一个函数调用，函数调用表达式即SqlFunctionCallExpression，它包含了 。
var sql = "SELECT t.*, ROW_NUMBER() OVER ( PARTITION BY t.ID ORDER BY t.NAME,t.ID) as rnum FROM TEST t";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "*",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
new SqlSelectItemExpression()
{
Body = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "ROW_NUMBER",
},
Over = new SqlOverExpression()
{
PartitionBy = new SqlPartitionByExpression()
{
Items = new List<SqlExpression>()
{
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "ID",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "ID",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "rnum",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
在这个例子中，SqlFunctionCallExpression它除了常规字段外，还包含了Over子句，具体有以下这些 。
var sql = "select name,PERCENTILE_CONT(0.5) within group(order by \"number\") from TEST5 group by name";
var sqlAst = DbUtils.Parse(sql, DbType.Pgsql);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name",
},
},
new SqlSelectItemExpression()
{
Body = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "PERCENTILE_CONT",
},
WithinGroup = new SqlWithinGroupExpression()
{
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "number",
LeftQualifiers = "\"",
RightQualifiers = "\"",
},
},
},
},
},
Arguments = new List<SqlExpression>()
{
new SqlNumberExpression()
{
Value = 0.5M,
},
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST5",
},
},
GroupBy = new SqlGroupByExpression()
{
Items = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name",
},
},
},
},
};
在这个例子中，SqlFunctionCallExpression它除了常规字段外，还包含了within group子句，具体有以下这些 。
var sql = "select c.*, (select a.name as province_name from portal_area a where a.id = c.province_id) as province_name, (select a.name as city_name from portal_area a where a.id = c.city_id) as city_name, (CASE WHEN c.area_id IS NULL THEN NULL ELSE (select a.name as area_name from portal_area a where a.id = c.area_id) END )as area_name from portal.portal_company c";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "*",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
new SqlSelectItemExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "province_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_area",
},
Alias = new SqlIdentifierExpression()
{
Value = "a",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "province_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "province_name",
},
},
new SqlSelectItemExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "city_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_area",
},
Alias = new SqlIdentifierExpression()
{
Value = "a",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "city_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "city_name",
},
},
new SqlSelectItemExpression()
{
Body = new SqlCaseExpression()
{
Items = new List<SqlCaseItemExpression>()
{
new SqlCaseItemExpression()
{
Condition = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "area_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
Operator = SqlBinaryOperator.Is,
Right = new SqlNullExpression()
},
Value = new SqlNullExpression()
},
},
Else = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Alias = new SqlIdentifierExpression()
{
Value = "area_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_area",
},
Alias = new SqlIdentifierExpression()
{
Value = "a",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "a",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "area_id",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
},
},
},
Alias = new SqlIdentifierExpression()
{
Value = "area_name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "portal_company",
},
Schema = new SqlIdentifierExpression()
{
Value = "portal",
},
Alias = new SqlIdentifierExpression()
{
Value = "c",
},
},
},
};
在这个例子中，要查询的列值为一个SqlSelectExpression表达式，即要查询的列是一个子查询 。
var sql = "SELECT * FROM test WHERE ID =1";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "ID",
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlNumberExpression()
{
Value = 1M,
},
},
},
};
在这个例子中，where字段的值是一个二元表达式SqlBinaryExpression，他包含了 。
二元表达式的两边可以非常灵活，可以是各种其他表达式，同时也可以自我嵌套另一个二元表达式，组成一个非常复杂的二元表达式 。
var sql = "SELECT * FROM test WHERE ID BETWEEN 1 AND 2";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
Where = new SqlBetweenAndExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "ID",
},
Begin = new SqlNumberExpression()
{
Value = 1M,
},
End = new SqlNumberExpression()
{
Value = 2M,
},
},
},
};
between子句包含了 。
var sql = "select * from test rd where rd.name is null";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "rd",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "rd",
},
},
Operator = SqlBinaryOperator.Is,
Right = new SqlNullExpression()
},
},
};
is null/is not null子句主要体现在二元表达式里，Operator字段为Is/IsNot，right字段为SqlNullExpression，即null表达式，代表值为null 。
var sql = "select * from TEST t where EXISTS(select * from TEST2 t2)";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlExistsExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST2",
},
Alias = new SqlIdentifierExpression()
{
Value = "t2",
},
},
},
},
},
},
};
exists/not exists子句,主要体现为SqlExistsExpression表达式， 。
var sql = "SELECT * from TEST t WHERE name LIKE '%a%'";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name",
},
Operator = SqlBinaryOperator.Like,
Right = new SqlStringExpression()
{
Value = "%a%"
},
},
},
};
like子句,主要体现在二元表达式里，Operator字段为Like/NotLike，本例子中right字段为字符串表达式，即SqlStringExpression表达式，值为%a%.
var sql = "select * from customer c where c.Age >all(select o.Quantity from orderdetail o)";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "customer",
},
Alias = new SqlIdentifierExpression()
{
Value = "c",
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Age",
},
Table = new SqlIdentifierExpression()
{
Value = "c",
},
},
Operator = SqlBinaryOperator.GreaterThen,
Right = new SqlAllExpression()
{
Body = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Quantity",
},
Table = new SqlIdentifierExpression()
{
Value = "o",
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "orderdetail",
},
Alias = new SqlIdentifierExpression()
{
Value = "o",
},
},
},
},
},
},
},
};
all/any子句,主要体现在SqlAllExpression/SqlAnyExpression表达式，它的body里是另一个SqlSelectExpression表达式 。
var sql = "SELECT * from TEST t WHERE t.NAME IN ('a','b','c')";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlInExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
TargetList = new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a"
},
new SqlStringExpression()
{
Value = "b"
},
new SqlStringExpression()
{
Value = "c"
},
},
},
},
};
in/not in子句,主要体现在SqlInExpression表达式，它包含了 。
当然了，in也有另一种子查询的类型，即 。
var sql = "select * from TEST5 WHERE NAME IN (SELECT NAME FROM TEST3)";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST5",
},
},
Where = new SqlInExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "NAME",
},
SubQuery = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "NAME",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST3",
},
},
},
},
},
},
};
在这里的SqlInExpression表达式中，它包含了 。
var sql = "SELECT CASE WHEN t.name='1' THEN 'a' WHEN t.name='2' THEN 'b' ELSE 'c' END AS v from TEST t";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlCaseExpression()
{
Items = new List<SqlCaseItemExpression>()
{
new SqlCaseItemExpression()
{
Condition = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "1"
},
},
Value = new SqlStringExpression()
{
Value = "a"
},
},
new SqlCaseItemExpression()
{
Condition = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "2"
},
},
Value = new SqlStringExpression()
{
Value = "b"
},
},
},
Else = new SqlStringExpression()
{
Value = "c"
},
},
Alias = new SqlIdentifierExpression()
{
Value = "v",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
case when子句,主要体现在SqlCaseExpression表达式里，他包含了 。
case when还有另外一种句式，如下:
var sql = "select case t.name when 'a' then 1 else 2 end from test t ";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlCaseExpression()
{
Items = new List<SqlCaseItemExpression>()
{
new SqlCaseItemExpression()
{
Condition = new SqlStringExpression()
{
Value = "a"
},
Value = new SqlNumberExpression()
{
Value = 1M,
},
},
},
Else = new SqlNumberExpression()
{
Value = 2M,
},
Value = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
};
在这种SqlCaseExpression表达式里，他包含了 。
var sql = "select * from TEST t WHERE not t.NAME ='abc'";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlNotExpression()
{
Body = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "abc"
},
},
},
},
};
not子句,主要体现在SqlNotExpression表达式里，它只有一个body字段，即代表否定的部分 。
var sql = "select * from TEST t WHERE not t.NAME =:name";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Where = new SqlNotExpression()
{
Body = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "NAME",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlVariableExpression()
{
Name = "name",
Prefix = ":",
},
},
},
},
};
变量子句,主要体现在SqlVariableExpression表达式里，它包括以下部分
在sql中，From关键字后面有多种形式来指定数据源。主要有以下几种 。
select * from test
这个解析结果上面已经演示了.
var sql = "select * from (select * from test) t";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlSelectExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
},
},
};
在这个例子中，数据源From的值为一个SqlSelectExpression，即SqlSelectExpression中可以嵌套SqlSelectExpression，同时我们注意到内部的SqlSelectExpression有一个表别名的字段Alias，标识符的值为t，表示表别名为t； 。
var sql = "select t1.id from test t1 left join test2 t2 on t1.id=t2.id right join test3 t3 on t2.id=t3.id";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t1",
},
},
},
},
From = new SqlJoinTableExpression()
{
Left = new SqlJoinTableExpression()
{
Left = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t1",
},
},
JoinType = SqlJoinType.LeftJoin,
Right = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test2",
},
Alias = new SqlIdentifierExpression()
{
Value = "t2",
},
},
Conditions = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t1",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t2",
},
},
},
},
JoinType = SqlJoinType.RightJoin,
Right = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test3",
},
Alias = new SqlIdentifierExpression()
{
Value = "t3",
},
},
Conditions = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t2",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "id",
},
Table = new SqlIdentifierExpression()
{
Value = "t3",
},
},
},
},
},
};
在上面这个例子中，我们演示了连表查询是如何解析的，From字段的值为一个SqlJoinTableExpression，即连表查询表达式，他包含了 。
在这个例子中，总共3张表联查，SqlJoinTableExpression中得left字段又是一个SqlJoinTableExpression，即SqlJoinTableExpression中可以嵌套SqlJoinTableExpression，无限套娃.
var sql = "with c1 as (select name from test t) , c2(name) AS (SELECT name FROM TEST2 t3 ) select *from c1 JOIN c2 ON c1.name=c2.name";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
WithSubQuerys = new List<SqlWithSubQueryExpression>()
{
new SqlWithSubQueryExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "c1",
},
FromSelect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
new SqlWithSubQueryExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "c2",
},
FromSelect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST2",
},
Alias = new SqlIdentifierExpression()
{
Value = "t3",
},
},
},
},
Columns = new List<SqlIdentifierExpression>()
{
new SqlIdentifierExpression()
{
Value = "name",
},
},
},
},
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlJoinTableExpression()
{
Left = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "c1",
},
},
JoinType = SqlJoinType.InnerJoin,
Right = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "c2",
},
},
Conditions = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "c1",
},
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "c2",
},
},
},
},
},
};
公用表表达式（CTE），主要体现在SqlSelectQueryExpression的WithSubQuerys字段，他是一个SqlWithSubQueryExpression表达式列表，即公用表列表，它里面的每一个元素都是SqlWithSubQueryExpression表达式，此表达式，包含了 。
特定数据库支持从返回结果集的函数中查询，比如oracle中添加一个自定义函数splitstr，他的作用是将一个字符串根据;号进行分割，返回多行数据 。
var sql = "SELECT * FROM TABLE(splitstr('a;b',';'))";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlReferenceTableExpression()
{
FunctionCall = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TABLE",
},
Arguments = new List<SqlExpression>()
{
new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "splitstr",
},
Arguments = new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a;b"
},
new SqlStringExpression()
{
Value = ";"
},
},
},
},
},
}
},
};
函数返回的结果集主要体现在SqlReferenceTableExpression表达式，他的内部包含了一个FunctionCall字段，值为SqlFunctionCallExpression表达式，代表从函数调用的结果集中进行查询.
var sql = "select fa.FlowId from FlowActivity fa order by fa.FlowId desc,fa.Id asc";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowActivity"
},
Alias = new SqlIdentifierExpression()
{
Value = "fa"
},
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body =
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
OrderByType = SqlOrderByType.Desc
},
new SqlOrderByItemExpression()
{
Body =
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Id"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
OrderByType = SqlOrderByType.Asc
},
},
},
},
};
OrderBy排序子句，值为SqlOrderByExpression表达式，表达式的内容也非常简单，只有一个Items，即一个排序子项表达式的列表，列表里的值为SqlOrderByItemExpression，即排序子项表达式，排序子项表达式里又包含了以下内容 。
select * from TEST5 t order by t.NAME desc nulls FIRST,t.AGE ASC NULLS last
那么我们的NullsType字段，他的值有SqlOrderByNullsType.First和SqlOrderByNullsType.Last,与之对应.
var sql = "select fa.FlowId from FlowActivity fa group by fa.FlowId,fa.Id HAVING count(fa.Id)>1";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowActivity"
},
Alias = new SqlIdentifierExpression()
{
Value = "fa"
},
},
GroupBy = new SqlGroupByExpression()
{
Items = new List<SqlExpression>()
{
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "FlowId"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Id"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
Having = new SqlBinaryExpression()
{
Left = new SqlFunctionCallExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "count"
},
Arguments = new List<SqlExpression>()
{
new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "Id"
},
Table = new SqlIdentifierExpression()
{
Value = "fa"
},
},
},
},
Operator = SqlBinaryOperator.GreaterThen,
Right = new SqlNumberExpression()
{
Value = 1M
},
},
},
},
};
GroupBy分组语句，值为SqlGroupByExpression表达式，他的内容如下 。
var sql = "select * from test t limit 1,5";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
Limit = new SqlLimitExpression()
{
Offset = new SqlNumberExpression()
{
Value = 1M,
},
RowCount = new SqlNumberExpression()
{
Value = 5M,
},
},
},
};
Limit分页子句，值为SqlLimitExpression表达式，他的内容如下 。
var sql = "SELECT * FROM TEST3 t ORDER BY t.NAME DESC FETCH FIRST 2 rows ONLY";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
}
},
From = new SqlTableExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
Name = new SqlIdentifierExpression()
{
Value = "TEST3"
}
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
OrderByType = SqlOrderByType.Desc,
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression() { Value = "NAME" },
Table = new SqlIdentifierExpression()
{
Value = "t"
}
}
}
}
},
Limit = new SqlLimitExpression()
{
RowCount = new SqlNumberExpression()
{
Value = 2
}
}
}
};
var sql = "select * from test5 t order by t.name limit 1 offset 10;";
var sqlAst = DbUtils.Parse(sql, DbType.Pgsql);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test5",
},
Alias = new SqlIdentifierExpression()
{
Value = "t",
},
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "name",
},
Table = new SqlIdentifierExpression()
{
Value = "t",
},
},
},
},
},
Limit = new SqlLimitExpression()
{
Offset = new SqlNumberExpression()
{
Value = 10M,
},
RowCount = new SqlNumberExpression()
{
Value = 1M,
},
},
},
};
var sql = "select * from test t order by t.name OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
}
},
From = new SqlTableExpression()
{
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
Name = new SqlIdentifierExpression()
{
Value = "test"
}
},
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlPropertyExpression()
{
Name = new SqlIdentifierExpression() { Value = "name" },
Table = new SqlIdentifierExpression()
{
Value = "t"
}
}
}
}
},
Limit = new SqlLimitExpression()
{
Offset = new SqlNumberExpression()
{
Value = 5
},
RowCount = new SqlNumberExpression()
{
Value = 10
}
}
}
};
var sql = "SELECT EMPLOYEEID , MANAGERID , LEVEL FROM EMPLOYEE e START WITH MANAGERID IS NULL CONNECT BY NOCYCLE PRIOR EMPLOYEEID = MANAGERID ORDER SIBLINGS BY EMPLOYEEID ";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "EMPLOYEEID",
},
},
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "MANAGERID",
},
},
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "LEVEL",
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "EMPLOYEE",
},
Alias = new SqlIdentifierExpression()
{
Value = "e",
},
},
ConnectBy = new SqlConnectByExpression()
{
StartWith = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "MANAGERID",
},
Operator = SqlBinaryOperator.Is,
Right = new SqlNullExpression()
},
Body = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "EMPLOYEEID",
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlIdentifierExpression()
{
Value = "MANAGERID",
},
},
IsNocycle = true,
IsPrior = true,
OrderBy = new SqlOrderByExpression()
{
Items = new List<SqlOrderByItemExpression>()
{
new SqlOrderByItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "EMPLOYEEID",
},
},
},
IsSiblings = true,
},
},
},
};
ConnectBy层次查询子句，值为SqlConnectByExpression表达式，他的内容如下 。
var sql = "SELECT name into test14 from TEST as t ";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name"
},
},
},
Into = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test14"
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST"
},
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
},
},
};
into子句，在本例子中值为SqlTableExpression，即into到某张表里.
var sql = "insert into test11(name,id) values('a1','a2')";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下:
var expect = new SqlInsertExpression()
{
Columns = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name"
},
new SqlIdentifierExpression()
{
Value = "id"
},
},
ValuesList = new List<List<SqlExpression>>()
{
new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a1"
},
new SqlStringExpression()
{
Value = "a2"
},
},
},
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test11"
},
},
};
如上例子，插入语句表现为一个SqlInsertExpression，他包含了 。
为什么ValuesList字段是列表里嵌套列表呢？主要是因为可以插入多个值列表，让我们继续往下看 。
var sql = "insert into test11(name,id) values('a1','a2'),('a3','a4')";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下:
var expect = new SqlInsertExpression()
{
Columns = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name"
},
new SqlIdentifierExpression()
{
Value = "id"
},
},
ValuesList = new List<List<SqlExpression>>()
{
new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a1"
},
new SqlStringExpression()
{
Value = "a2"
},
},
new List<SqlExpression>()
{
new SqlStringExpression()
{
Value = "a3"
},
new SqlStringExpression()
{
Value = "a4"
},
},
},
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test11"
},
},
};
在本例子中，ValuesList字段中有2个子元素，即2个List 列表，代表插入2组数据，值分别为('a1','a2')和('a3','a4') 。
var sql = "INSERT INTO TEST2(name) SELECT name AS name2 FROM TEST t";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下:
var expect = new SqlInsertExpression()
{
Columns = new List<SqlExpression>()
{
new SqlIdentifierExpression()
{
Value = "name"
},
},
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST2"
},
},
FromSelect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlIdentifierExpression()
{
Value = "name"
},
Alias = new SqlIdentifierExpression()
{
Value = "name2"
},
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "TEST"
},
Alias = new SqlIdentifierExpression()
{
Value = "t"
},
},
},
},
};
如上例子，插入语句表现为一个SqlInsertExpression，他包含了 。
var sql = "update test set name ='4',d='2024-11-22 08:19:47.243' where name ='1'";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下:
var expect = new SqlUpdateExpression()
{
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test"
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name"
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "1"
},
},
Items = new List<SqlExpression>()
{
new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name"
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "4"
},
},
new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "d"
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "2024-11-22 08:19:47.243"
},
},
},
};
如上例子，更新语句表现为一个SqlUpdateExpression，他包含了 。
var sql = "delete from test where name=4";
var sqlAst = DbUtils.Parse(sql, DbType.MySql);
解析结果如下:
var expect = new SqlDeleteExpression()
{
Table = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test"
},
},
Where = new SqlBinaryExpression()
{
Left = new SqlIdentifierExpression()
{
Value = "name"
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlNumberExpression()
{
Value = 4M
},
},
};
如上例子，删除语句表现为一个SqlDeleteExpression，他包含了 。
var sql = @"select *--abc from test lbu WHERE a ='1'--aaaaaa
FROM test";
var sqlAst = DbUtils.Parse(sql, DbType.SqlServer);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
如上例子，单行注释被正确忽视，解析正确.
var sql = @"/*这
是
顶部*/
select *--abc
FROM test/*这
是
底部*/";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析结果如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
如上例子，多行注释被正确忽视，解析正确.
当我们通过 。
var sql = @"select * from test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析sql获取到抽象语法树以后，我们就要对这颗抽象语法树进行解析，获取我们想要的数据，此时就要用上访问者模式(visitor) . 。
访问者模式最大的特点就是结构与算法分离，结合本项目理解，就是ast抽象语法树这个结构已经解析出来了，你可以根据自己的需要写算法去任意解析这颗语法树。这是一个1-N的操作，即一个抽象语法树，可以对应N个解析算法，当我们要自定义算法去解析抽象语法树时，我们需要自定义一个Visitor类，并且实现IAstVisitor接口 。
public class CustomVisitor : IAcceptVisitor
{
}
但是实现这个接口要实现接口里的很多个方法，并且有些数据并不是我们关心的，所以我提供了一个实现了IAcceptVisitor接口的抽象类BaseAstVisitor用来简化操作，我们只需要继承这个抽象类，然后重写我们感兴趣的方法即可 。
public class CustomVisitor : BaseAstVisitor
{
}
在本项目中，我提供了2个基本的vistor供大家使用，UnitTestAstVisitor和SqlGenerationAstVisitor，大家可以参考这2个visitor去写自己的算法来解析抽象语法树。接下来，我将介绍这2个visitor的用法.
当我们通过 。
var sql = @"select * from test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
解析sql获取到抽象语法树之后，sqlAst其实还是一个数据结构，我们可以通过vs监视这个变量来查看内部的结构，但是如果是非常复杂的sql，那这颗树会巨大无比，要靠我们手动去慢慢点开查看得累死，没错！写单元测试的时候，我刚开始都是用手写的结果去对比引擎解析出来的结果，后来我就被累死了，该说不说，这活真不是人干的，所以痛定思痛之后我就写了这个UnitTestAstVisitor来替我生成ast的结构字符串，接下来让我们看看用法 。
var sql = @"select * from test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
var unitTestAstVisitor = new UnitTestAstVisitor();
sqlAst.Accept(unitTestAstVisitor);
var result = unitTestAstVisitor.GetResult();
其中的result就是解析抽象语法树生成的字符串，如下:
var expect = new SqlSelectExpression()
{
Query = new SqlSelectQueryExpression()
{
Columns = new List<SqlSelectItemExpression>()
{
new SqlSelectItemExpression()
{
Body = new SqlAllColumnExpression()
},
},
From = new SqlTableExpression()
{
Name = new SqlIdentifierExpression()
{
Value = "test",
},
},
},
};
然后把这个生成的字符串黏贴到vs里去和引擎生成的结果进行对比， 。
Assert.True(sqlAst.Equals(expect));
至此，我写单元测试的工作量大大减轻，同时对于生成的sqlAst语法树的结构也更加一目了然了.
我们通过解析sql生成了抽象语法树之后，如果我们想要给这颗抽象语法树添加一个where条件,比如添加test.name ='a' 。
var sql = @"select * from test";
var sqlAst = DbUtils.Parse(sql, DbType.Oracle);
if (sqlAst is SqlSelectExpression sqlSelectExpression && sqlSelectExpression.Query is SqlSelectQueryExpression sqlSelectQueryExpression)
{
sqlSelectQueryExpression.Where = new SqlBinaryExpression()
{
Left = new SqlPropertyExpression()
{
Table = new SqlIdentifierExpression()
{
Value = "test"
},
Name = new SqlIdentifierExpression()
{
Value = "name"
}
},
Operator = SqlBinaryOperator.EqualTo,
Right = new SqlStringExpression()
{
Value = "a"
}
};
}
好了，现在我们添加完了，接下来我们肯定是想着把抽象语法树转化为sql语句，此时，就需要用上SqlGenerationAstVisitor了，他就是负责把抽象语法树转化为sql的 。
var sqlGenerationAstVisitor = new SqlGenerationAstVisitor(DbType.Oracle);
sqlAst.Accept(sqlGenerationAstVisitor);
var newSql = sqlGenerationAstVisitor.GetResult();
我们获取到的newSql就是新的sql了，他的值为 。
select * from test where(test.name = 'a')
至此，我们的目的就达到了.
sql之所以能被我们解析出来，主要是因为sql是一种形式语言，自然语言和形式语言的一个重要区别是，自然语言的一个语句，可能有多重含义，而形式语言的一个语句，只能有一个语义;形式语言的语法是人为规定的，有了一定的语法规则，语法解析器就能根据语法规则，解析出一个语句的唯一含义.
本项目基于MIT协议开源，地址为 https://github.com/TripleView/SqlParser.Net 。
同时感谢以下项目 。
如果各位靓仔觉得这个项目不错，欢迎一键三连（推荐，star，关注）,同时欢迎加入三合的开源交流群，QQ群号：799648362 。
最后此篇关于他又又来了，c#开源sql解析引擎类库【SqlParser.Net1.0】正式发布，它可以帮助你简单快速高效的解析和处理sql的文章就讲到这里了,如果你想了解更多关于他又又来了，c#开源sql解析引擎类库【SqlParser.Net1.0】正式发布，它可以帮助你简单快速高效的解析和处理sql的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。
56
4
0
Java 库和 android 库有什么区别，各自有什么优点/缺点？ 最佳答案 您可以在 Android 应用程序中包含标准 Java .jar 文件库。它们在 .apk 构建时被翻译成 Dalvik
所以，我现在的代码就像从 Java 层加载库(比如 liba.so)，并在内部 liba.so 加载 libb.so。因此，如果我必须将所有库打包到 APK 中并将其安装在没有 root 访问权限的设
我想在我的系统中设置 LEDA 库。 我已经从以下链接下载了 LEDA 库 http://www.algorithmic-solutions.info/free/d5.php Instruct
我想用 autoconf 创建一个共享库。但是，我希望共享库具有“.so”扩展名，而不是以“lib”开头。基本上，我想制作一个加载 dlopen 的插件。 .是否有捷径可寻？ 当我尝试使用 autoc
我需要在 Apps 脚本应用程序上修改 PDF。为此，我想使用 JS 库:PDF-LIB 我的代码: eval(UrlFetchApp.fetch("https://unpkg.com/pdf-lib
我正在构建一个使用以下 Boost header 的程序(我使用的是 Microsoft Visual C++ 10)， #include #include #include #include
当我通过 cygwin 在 hadoop 上运行此命令时: $bin/hadoop jar hadoop-examples-*.jar grep input output 'dfs[a-z.]+' 我
我已经通过 vcpgk 成功安装了一个 C++ 库，名为:lmdb:x64-windows 我还安装了lmdb通过 Cabal 安装的 Haskell 绑定(bind)包 在尝试测试 lmdb 包时:
我该如何解决这个问题？ 我刚刚将 javacv jar 文件复制到我的项目 Lib 文件夹下，但出现了这个错误! 我可以找到这个thread来自谷歌，但不幸的是，由于我国的谷歌限制政策，该页面无法打开
我有一个 Android 库项目 FooLib。 FooLib 引用 Android Context 之类的东西，但不需要任何资源文件(res/ 中的东西)所以我目前将其打包为供我的应用使用的 JAR
我正在开发一个 Android 应用程序(使用 Android Studio)，它能够通过手势识别算法了解您正在进行的 Activity 。对于我使用 nickgillian ithub 帐户上可用的
关于从 .NET Framework 项目中引用 .NET Standard 类库的问题有很多类似的问题，其中 netstandard 库中的 NuGet 包依赖项不会流向 netframework
我已经从互联网上下载了 jna-4.2.2.jar，现在想将这个 jar 导入到我的项目中。但是当我试图将这个 jar 导入我的项目时，出现以下错误。 [2016-06-20 09:35:01 - F
我正在尝试通过编译在 Mac 上安装 rsync 3.2.3。但是，我想安装所有功能。为此，它需要一些库，此处 ( https://download.samba.org/pub/rsync/INSTA
进入 Web 开发有点困难。过去 5 年我一直致力于 winforms 工作。所以我正在努力从一种切换到另一种。前段时间，我使用过 JavaScript，但现在还没有大量的 JavaScript 库
很难说出这里要问什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或夸夸其谈，无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开，visit the help center . 关闭 1
我正在寻找一个用Python编写的与logstash(ruby + java)类似的工具/库。 我的目标是: 从 syslog 中解析所有系统日志 解析应用程序特定日志(apache、django、m
就目前情况而言，这个问题不太适合我们的问答形式。我们希望答案得到事实、引用资料或专业知识的支持，但这个问题可能会引发辩论、争论、民意调查或扩展讨论。如果您觉得这个问题可以改进并可能重新开放，visit
我花了几天时间试图寻找用于 JavaPOS 实现的 .jar 库，但我找不到任何可以工作的东西。我找到了很多像这样的文档:http://jpos.1045706.n5.nabble.com/file/
这个问题在这里已经有了答案: Merge multiple .so shared libraries (2 个答案) 关闭 9 年前。 我有我在代码中使用的第三方库的源代码和对象。该库附带有关如何使
我是一名优秀的程序员,十分优秀！