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

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

 

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

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

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

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

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

1.查询全部

 

1 查询Student表的所有记录。

 select * from student

 Linq:

     from s in Students

     select s

 Lambda:

     Students.Select( s => s)

 

 按条件查询全部:

 

   查询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

     })

 

.distinct 去掉重复的

 

 查询教师所有的单位即不重复的Depart列。

 select distinct depart from teacher

 Linq:

     from t in Teachers.Distinct()

     select t.DEPART

 Lambda:

     Teachers.Distinct().Select( t => t.DEPART)

 

.连接查询 between and

  查询Score表中成绩在0到0之间的所有记录。

  select * from score where degree between 0 and 0

  Linq:

      from s in Scores

      where s.DEGREE >= 0 && s.DEGREE < 0

      select s

  Lambda:

      Scores.Where(

          s => (

                 s.DEGREE >= 0 && s.DEGREE < 0

              )

     )

 

.在范围内筛选 In

 select * from score where degree in (,,)

 Linq:

     from s in Scores

     where (

             new decimal[]{,,}

           ).Contains(s.DEGREE)

     select s

 Lambda:

     Scores.Where( s => new Decimal[] {,,}.Contains(s.DEGREE))

 

.or 条件过滤

 

 查询Student表中"0"班或性别为"女"的同学记录。

 select * from student where class ='0' or ssex= N'女'

 Linq:

     from s in Students

     where s.CLASS == "0"

        || s.CLASS == "女"

     select s

 Lambda:

     Students.Where(s => ( s.CLASS == "0" || s.CLASS == "女"))

 

.排序

 

 以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)

 

.count()行数查询

 

  select count(*) from student where class = '0'

  Linq:

      (    from s in Students

          where s.CLASS == "0"

          select s

      ).Count()

  Lambda:

      Students.Where( s => s.CLASS == "0" )

                  .Select( s => s)

                     .Count()

 

.avg()平均

  查询'-'号课程的平均分。

  select avg(degree) from score where cno = '-'

  Linq:

      (

          from s in Scores

          where s.CNO == "-"

          select s.DEGREE

      ).Average()

  Lambda:

     Scores.Where( s => s.CNO == "-")

             .Select( s => s.degree)

或者:Scores.Average(p => p.degree);

 

.子查询

  查询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

0         select new {

             s.SNO,

             c.CNO

         }

     ).Distinct()

 

.分组 过滤

  查询Score表中至少有名学生选修的并以开头的课程的平均分数。

  select avg(degree) from score where cno like '%' 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,"%")

 

.分组

  查询Score表中至少有名学生选修的并以开头的课程的平均分数。

  select avg(degree) from score where cno like '%' 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,"%")

 

. 多表查询

  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()

 

 

 

posted @ 2016-07-29 14:02  离。  阅读(18)  评论(0编辑  收藏  举报