var mm=ClassSet.Select(ClassSet.ClassID,ClassSet.ClassName). Where(ClassSet.ClassName.Contains("s").And(ClassSet.ClassID.BiggerThan(9)));
SELECT [Class].[ClassID],[Class].[ClassName] FROM [Class] WHERE [Class].[ClassName] LIKE @p1 AND [Class].[ClassID]>@p2
@p1=%s%
@p2=92.2 MQL的嵌套查询(含有Top查询:支持mysql、oracle、postgreSQL、sqlserver、sqlite)
var qiantao=ScoreSet.SelectAll().Where(
ScoreSet.UserID.In(UserSet.Select(UserSet.UserID).Where(
UserSet.ClassID.In(
ClassSet.Select(ClassSet.ClassID).Where(
ClassSet.ClassName.Equal(c.ClassName).And(ClassSet.ClassID.BiggerThan(0))
)
)
)
)
).Top(1); SELECT TOP 1 [Score].* FROM [Score] WHERE [Score].[UserID] IN (SELECT [User].[UserID] FROM [User] WHERE [User].[ClassID] IN (SELECT [Class].[ClassID] FROM [Class] WHERE [Class].[ClassName]=@p1 AND [Class].[ClassID]>@p2 ) )
@p1=综合测试ClassName2
@p2=0
2.3 MQL的分组查询
var mql=ScoreSet.Select(ScoreSet.ScoreM.Sum().AS("sum"),ScoreSet.TypeName). Where(ScoreSet.ScoreM.BiggerThanOrEqual(100)).
GroupBy(ScoreSet.TypeName).
Having(ScoreSet.ScoreM.Sum().BiggerThan(300));
SELECT SUM([Score].[ScoreM]) AS 'sum',[Score].[TypeName] FROM [Score] WHERE [Score].[ScoreM]>=@p1 GROUP BY [Score].[TypeName] HAVING SUM([Score].[ScoreM])>@p2
@p1=100
@p2=300 2.4 MQL的连接查询
var m1=ClassSet.Select(ClassSet.ClassID,ClassSet.ClassName)
.LeftJoin(
UserSet.Select(UserSet.UserID))
.ON(ClassSet.ClassID.Equal(UserSet.UserID))
.Where(UserSet.UserID.BiggerThan(9));
SELECT [Class].[ClassID],[Class].[ClassName],[User].[UserID] FROM [Class] LEFT JOIN [User] ON [Class].[ClassID]=[User].[UserID] WHERE [User].[UserID]>@p1
@p1=9 2.5 MQL的Union查询
var mql=ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(1))
.Union(ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(2)));
var mql=ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(1))
.UnionAll(ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(2)));
SELECT [Class].* FROM [Class] WHERE [Class].[ClassID]>@p1 UNION SELECT [Class].* FROM [Class] WHERE [Class].[ClassID]>@p2
@p1=1
@p2=2
SELECT [Class].* FROM [Class] WHERE [Class].[ClassID]>@p1 UNION ALL SELECT [Class].* FROM [Class] WHERE [Class].[ClassID]>@p2
@p1=1
@p2=23.MQL查询结果预览
public static void Main(string[] args)
{
using (var db=Db.CreateDefaultDb()) {
db.TransactionEnabled=true;
db.DebugEnabled=true;
Console.WriteLine("---------------嵌套查询---------------------");
var qiantao=ScoreSet.SelectAll().Where(
ScoreSet.UserID.In(UserSet.Select(UserSet.UserID).Where(
UserSet.ClassID.In(
ClassSet.Select(ClassSet.ClassID).Where(
ClassSet.ClassName.Equal(c.ClassName).And(ClassSet.ClassID.BiggerThan(0))
)
)
)
)
).Top(1);
Console.WriteLine("---------------分组查询---------------------");
var mql=ScoreSet.Select(ScoreSet.ScoreM.Sum().AS("sum"),ScoreSet.TypeName).Where(ScoreSet.ScoreM.BiggerThanOrEqual(100)).GroupBy(ScoreSet.TypeName).Having(ScoreSet.ScoreM.Sum().BiggerThan(300));
Console.WriteLine("---------------连接查询---------------------");
var m1=ClassSet.Select(ClassSet.ClassID,ClassSet.ClassName)
.LeftJoin(
UserSet.Select(UserSet.UserID))
.ON(ClassSet.ClassID.Equal(UserSet.UserID))
.Where(UserSet.UserID.BiggerThan(9)); }
Console.WriteLine("---------------Union测试---------------------");
using (var db=Db.CreateDefaultDb()) {
db.TransactionEnabled=true;
db.DebugEnabled=true;
var mql=ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(1))
.Union(ClassSet.SelectAll().Where(ClassSet.ClassID.BiggerThan(2)));