浅谈sql 、linq、lambda 查询语句的区别

浅谈sql 、linq、lambda 查询语句的区别

LINQ的书写格式如下:  
 from 临时变量 in 集合对象或数据库对象  
 where 条件表达式   
[order by条件]   
select 临时变量中被查询的值  
 [group by 条件]

Lambda表达式的书写格式如下:

(参数列表) => 表达式或者语句块

其中: 参数个数:可以有多个参数,一个参数,或者无参数。

参数类型:可以隐式或者显式定义。

表达式或者语句块:这部分就是我们平常写函数的实现部分(函数体)。

1.查询全部

实例 Code
查询Student表的所有记录。
 select * from student
Linq:
from s in Students
select s
Lambda:
Students.Select( s => s)

2 按条件查询全部:

实例 Code
 查询Student表中的所有记录的Sname、Ssex和Class列。
  select sname,ssex,class from student
Linq:
from s in Students
select new {
s.SNAME,
s.SSEX,
s.CLASS
}
Lambda:
Students.Select( s =>new {
SNAME = s.SNAME,SSEX = s.SSEX,CLASS = s.CLASS
})

3.distinct 去掉重复的

实例 Code
查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher
Linq:
from t in Teachers.Distinct()
select t.DEPART
Lambda:
Teachers.Distinct().Select( t => t.DEPART)

  

4.连接查询 between and

实例 Code
查询Score表中成绩在60到80之间的所有记录。
 select * from score where degree between 60 and 80
Linq:
from s in Scores
where s.DEGREE >=60&& s.DEGREE <80
select s
Lambda:
Scores.Where(
s => (
s.DEGREE >=60&& s.DEGREE <80
)
)

5.在范围内筛选 In

实例 Code
 
 select * from score where degree in (85,86,88)
Linq:
from s in Scores
where (
newdecimal[]{85,86,88}
).Contains(s.DEGREE)
select s
Lambda:
Scores.Where( s =>new Decimal[] {85,86,88}.Contains(s.DEGREE))
 

6.or 条件过滤

实例 Code
 
查询Student表中"95031"班或性别为"女"的同学记录。
 select * from student whereclass='' or ssex= N'女'
Linq:
from s in Students
where s.CLASS =="95031"
|| s.CLASS =="女"
select s
Lambda:
Students.Where(s => ( s.CLASS =="95031"|| s.CLASS =="女"))
 

7.排序

实例 Code
以Class降序查询Student表的所有记录。
 select * from student order by Class DESC
Linq:
from s in Students
orderby s.CLASS descending
select s
Lambda:
Students.OrderByDescending(s => s.CLASS)

8.count()行数查询

实例 Code
 select count(*) from student whereclass=''
Linq:
( from s in Students
where s.CLASS ==""
select s
).Count()
Lambda:
Students.Where( s => s.CLASS =="" )
.Select( s => s)
.Count()

10.avg()平均

实例 Code
查询'3-105'号课程的平均分。
 select avg(degree) from score where cno ='3-105'
Linq:
(
from s in Scores
where s.CNO =="3-105"
select s.DEGREE
).Average()
Lambda:
Scores.Where( s => s.CNO =="3-105")
.Select( s => s.DEGREE)

11.子查询

实例 Code
查询Score表中的最高分的学生学号和课程号。
  select distinct s.Sno,c.Cno from student as s,course as c ,score as sc
where s.sno=(select sno from score where degree = (select max(degree) from score))
and c.cno = (select cno from score where degree = (select max(degree) from score))
Linq:
(
from s in Students
from c in Courses
from sc in Scores
let maxDegree = (from sss in Scores
select sss.DEGREE
).Max()
let sno = (from ss in Scores
where ss.DEGREE == maxDegree
select ss.SNO).Single().ToString()
let cno = (from ssss in Scores
where ssss.DEGREE == maxDegree
select ssss.CNO).Single().ToString()
where s.SNO == sno && c.CNO == cno
select new {
s.SNO,
c.CNO
}
).Distinct()

12.分组 过滤

实例 Code
查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(degree) from score where cno like '3%' group by Cno having count(*)>=
Linq:
from s in Scores
where s.CNO.StartsWith("")
group s by s.CNO
into cc
where cc.Count() >=
select cc.Average( c => c.DEGREE)
Lambda:
Scores.Where( s => s.CNO.StartsWith("") )
.GroupBy( s => s.CNO )
.Where( cc => ( cc.Count() >=) )
.Select( cc => cc.Average( c => c.DEGREE) )
Linq: SqlMethod
like也可以这样写:
s.CNO.StartsWith("") or SqlMethods.Like(s.CNO,"%3")

13.分组

实例 Code
查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
 select avg(degree) from score where cno like '3%' group by Cno having count(*)>=
Linq:
from s in Scores
where s.CNO.StartsWith("")
group s by s.CNO
into cc
where cc.Count() >=
select cc.Average( c => c.DEGREE)
Lambda:
Scores.Where( s => s.CNO.StartsWith("") )
.GroupBy( s => s.CNO )
.Where( cc => ( cc.Count() >=) )
.Select( cc => cc.Average( c => c.DEGREE) )
Linq: SqlMethod
like也可以这样写:
s.CNO.StartsWith("") or SqlMethods.Like(s.CNO,"%3")

14. 多表查询

实例 Code

 select sc.sno,c.cname,sc.degree from course as c,score as sc where c.cno = sc.cno
Linq:
from c in Courses
join sc in Scores
on c.CNO equals sc.CNO
select new
{
sc.SNO,c.CNAME,sc.DEGREE
}
Lambda:
Courses.Join ( Scores, c => c.CNO,
sc => sc.CNO,
(c, sc) =>new
{
SNO = sc.SNO,
CNAME = c.CNAME,
DEGREE = sc.DEGREE
})
.Average()
上一篇:Hive 遇到 Class path contains multiple SLF4J bindings


下一篇:spring cloud启动zipkin,报错maven依赖jar包冲突 Class path contains multiple SLF4J bindings