sql 、linq、lambda 查询语句的区别-白红宇

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

阅读量：4660 次

发布时间：2019-06-09

本文共 5655 字，大约阅读时间需要 18 分钟。

LINQ的书写格式如下：　

　from 临时变量 in 集合对象或数据库对象　

　where 条件表达式　　

[order by条件] 　　

select 临时变量中被查询的值　

　[group by 条件]

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

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

其中：参数个数：可以有多个参数，一个参数，或者无参数。

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

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

1.查询全部

1 查询Student表的所有记录。 2 select * from student 3 Linq: 4     from s in Students 5     select s 6 Lambda: 7     Students.Select( s => s)

2 按条件查询全部：

1  查询Student表中的所有记录的Sname、Ssex和Class列。  2 select sname,ssex,class from student  3 Linq:  4     from s in Students  5     select new {
       6         s.SNAME,  7         s.SSEX,  8         s.CLASS  9     } 10 Lambda: 11     Students.Select( s => new {
      12         SNAME = s.SNAME,SSEX = s.SSEX,CLASS = s.CLASS 13     })

3.distinct 去掉重复的

1 查询教师所有的单位即不重复的Depart列。 2 select distinct depart from teacher 3 Linq: 4     from t in Teachers.Distinct() 5     select t.DEPART 6 Lambda: 7     Teachers.Distinct().Select( t => t.DEPART)

4.连接查询 between and

1 查询Score表中成绩在60到80之间的所有记录。  2 select * from score where degree between 60 and 80  3 Linq:  4     from s in Scores  5     where s.DEGREE >= 60 && s.DEGREE < 80  6     select s  7 Lambda:  8     Scores.Where(  9         s => ( 10                 s.DEGREE >= 60 && s.DEGREE < 80 11              ) 12     )

5.在范围内筛选 In

1 select * from score where degree in (85,86,88) 2 Linq: 3     from s in Scores 4     where ( 5             new decimal[]{
      85,86,88} 6           ).Contains(s.DEGREE) 7     select s 8 Lambda: 9     Scores.Where( s => new Decimal[] {
      85,86,88}.Contains(s.DEGREE))

6.or 条件过滤

1 查询Student表中"95031"班或性别为"女"的同学记录。 2 select * from student where class ='95031' or ssex= N'女' 3 Linq: 4     from s in Students 5     where s.CLASS == "95031" 6        || s.CLASS == "女" 7     select s 8 Lambda: 9     Students.Where(s => ( s.CLASS == "95031" || s.CLASS == "女"))

7.排序

1 以Class降序查询Student表的所有记录。 2 select * from student order by Class DESC 3 Linq: 4     from s in Students 5     orderby s.CLASS descending 6     select s 7 Lambda: 8     Students.OrderByDescending(s => s.CLASS)

8.count（）行数查询

1 select count(*) from student where class = '95031'  2 Linq:  3     (    from s in Students  4         where s.CLASS == "95031"  5         select s  6     ).Count()  7 Lambda:  8     Students.Where( s => s.CLASS == "95031" )  9                 .Select( s => s) 10                     .Count()

10.avg()平均

1 查询'3-105'号课程的平均分。  2 select avg(degree) from score where cno = '3-105'  3 Linq:  4     (  5         from s in Scores  6         where s.CNO == "3-105"  7         select s.DEGREE  8     ).Average()  9 Lambda: 10     Scores.Where( s => s.CNO == "3-105") 11             .Select( s => s.DEGREE)

11.子查询

1 查询Score表中的最高分的学生学号和课程号。  2 select distinct s.Sno,c.Cno from student as s,course as c ,score as sc  3 where s.sno=(select sno from score where degree = (select max(degree) from score))  4 and c.cno = (select cno from score where degree = (select max(degree) from score))  5 Linq:  6     (  7         from s in Students  8         from c in Courses  9         from sc in Scores 10         let maxDegree = (from sss in Scores 11                         select sss.DEGREE 12                         ).Max() 13         let sno = (from ss in Scores 14                 where ss.DEGREE == maxDegree 15                 select ss.SNO).Single().ToString() 16         let cno = (from ssss in Scores 17                 where ssss.DEGREE == maxDegree 18                 select ssss.CNO).Single().ToString() 19         where s.SNO == sno && c.CNO == cno 20         select new {
      21             s.SNO, 22             c.CNO 23         } 24     ).Distinct()

12.分组过滤

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

13.分组

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

14. 多表查询

1 select sc.sno,c.cname,sc.degree from course as c,score as sc where c.cno = sc.cno  2 Linq:  3     from c in Courses  4     join sc in Scores  5     on c.CNO equals sc.CNO  6     select new  7     {
       8         sc.SNO,c.CNAME,sc.DEGREE  9     } 10 Lambda: 11     Courses.Join ( Scores, c => c.CNO, 12                              sc => sc.CNO, 13                              (c, sc) => new 14                                         {
      15                                             SNO = sc.SNO, 16                                             CNAME = c.CNAME, 17                                             DEGREE = sc.DEGREE 18                                         }) 19                 .Average()

（在此，特别感谢网友“王者之舞”所提供的实例）

转载于:https://www.cnblogs.com/wybshyy/p/5847979.html

你可能感兴趣的文章