sql 、linq、Lambda 查询语句的格式

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

Lambda表达式的书写格式如下：
集合对象.Where(临时变量=>(条件表达式))
Sql、Linq、Lambda 查询语句示例

1.查询Student表的所有记录

Sql:
    select * from student
Linq:
    from s in Students
    select s
Lambda:    
    Students.Select( s => true) 

2.查询Student表中的所有记录的Sname、Ssex和Class列。

Sql:
     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.查询教师所有的单位即不重复的Depart列。

Sql:
    select distinct depart from teacher
Linq:
    from t in Teachers.Distinct()
    select t.DEPART
Lambda:
    Teachers.Distinct().Select( t => t.DEPART)

4.查询Score表中成绩在60到80之间的所有记录。

Sql:
    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

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

6.查询Student表中”95031”班或性别为”女”的同学记录。

Sql:
    select * from student where class ='95031' 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.以Class降序查询Student表的所有记录。

Sql:
     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（）行数查询

Sql:
     select count(*) from student where class = '95031'
Linq:
     (from s in Students
        where s.CLASS == "95031"
        select s
     ).Count()
Lambda:
     Students.Where( s => s.CLASS == "95031" ) .Select( s => s).Count()

9.查询3-105号课程的平均分。

Sql:
     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)

10.查询Score表中的最高分的学生学号和课程号。

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

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

Sql:
      select avg(degree) from score where cno like '3%' group by Cno having count(*)>=5
Linq:
        from s in Scores
        where s.CNO.StartsWith("3")
        group s by s.CNO
        into cc
        where cc.Count() >= 5
        select cc.Average( c => c.DEGREE)
Lambda:
    Scores.Where( s => s.CNO.StartsWith("3") )
            .GroupBy( s => s.CNO )
            .Where( cc => ( cc.Count() >= 5) )
            .Select( cc => cc.Average( c => c.DEGREE) )
Linq: SqlMethod
like也可以这样写:
    s.CNO.StartsWith("3") or SqlMethods.Like(s.CNO,"%3")

12.多表查询

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