SQL、LINQ、Lambda 三种用法

SQL LinqToSql Lambda

1、查询Student表中的所有记录的Sname、Ssex和Class列。selectsname,ssex,classfromstudentLinq:fromsinStudentsselectnew{s.SNAME,s.SSEX,s.CLASS}Lambda:Students.Select(s=>new{SNAME=s.SNAME,SSEX=s.SSEX,CLASS=s.CLASS})2、查询教师所有的单位即不重复的Depart列。selectdistinctdepartfromteacherLinq:fromtinTeachers.Distinct()selectt.DEPARTLambda:Teachers.Distinct().Select(t=>t.DEPART)3、查询Student表的所有记录。select*fromstudentLinq:fromsinStudentsselectsLambda:Students.Select(s=>s)4、查询Score表中成绩在60到80之间的所有记录。select*fromscorewheredegreebetween60and80Linq:fromsinScoreswheres.DEGREE>=60&&s.DEGREE<80selectsLambda:Scores.Where(s=>(s.DEGREE>=60&&s.DEGREE<80))5、查询Score表中成绩为85，86或88的记录。select*fromscorewheredegreein(85,86,88)Linq:InfromsinScoreswhere(newdecimal[]{85,86,88}).Contains(s.DEGREE)selectsLambda:Scores.Where(s=>newDecimal[]{85,86,88}.Contains(s.DEGREE))NotinfromsinScoreswhere!(newdecimal[]{85,86,88}).Contains(s.DEGREE)selectsLambda:Scores.Where(s=>!(newDecimal[]{85,86,88}.Contains(s.DEGREE)))Any()应用：双表进行Any时，必须是主键为(String)CustomerDemographicsCustomerTypeID（String）CustomerCustomerDemos(CustomerIDCustomerTypeID)(String)一个主键与二个主建进行Any(或者是一对一关键进行Any)不可，以二个主键于与一个主键进行AnyfromeinCustomerDemographicswhere!e.CustomerCustomerDemos.Any()selectefromcinCategorieswhere!c.PRoducts.Any()selectc6、查询Student表中"95031"班或性别为"女"的同学记录。select*fromstudentwhereclass='95031'orssex=N'女'Linq:fromsinStudentswheres.CLASS=="95031"||s.CLASS=="女"selectsLambda:Students.Where(s=>(s.CLASS=="95031"||s.CLASS=="女"))7、以Class降序查询Student表的所有记录。select*fromstudentorderbyClassDESCLinq:fromsinStudentsorderbys.CLASSdescendingselectsLambda:Students.OrderByDescending(s=>s.CLASS)8、以Cno升序、Degree降序查询Score表的所有记录。select*fromscoreorderbyCnoASC,DegreeDESCLinq:(这里CnoASC在linq中要写在最外面)fromsinScoresorderbys.DEGREEdescendingorderbys.CNOascendingselectsLambda:Scores.OrderByDescending(s=>s.DEGREE).OrderBy(s=>s.CNO)9、查询"95031"班的学生人数。selectcount(*)fromstudentwhereclass='95031'Linq:(fromsinStudentswheres.CLASS=="95031"selects).Count()Lambda:Students.Where(s=>s.CLASS=="95031").Select(s=>s).Count()10、查询Score表中的最高分的学生学号和课程号。selectdistincts.Sno,c.Cnofromstudentass,courseasc,scoreasscwheres.sno=(selectsnofromscorewheredegree=(selectmax(degree)fromscore))andc.cno=(selectcnofromscorewheredegree=(selectmax(degree)fromscore))Linq:(fromsinStudentsfromcinCoursesfromscinScoresletmaxDegree=(fromsssinScoresselectsss.DEGREE).Max()letsno=(fromssinScoreswheress.DEGREE==maxDegreeselectss.SNO).Single().ToString()letcno=(fromssssinScoreswheressss.DEGREE==maxDegreeselectssss.CNO).Single().ToString()wheres.SNO==sno&&c.CNO==cnoselectnew{s.SNO,c.CNO}).Distinct()操作时问题？执行时报错：wheres.SNO==sno（这行报出来的）运算符"=="无法应用于"string"和"System.Linq.IQueryable<string>"类型的操作数解决：原：letsno=(fromssinScoreswheress.DEGREE==maxDegreeselectss.SNO).ToString()Queryable().Single()返回序列的唯一元素；如果该序列并非恰好包含一个元素，则会引发异常。解：letsno=(fromssinScoreswheress.DEGREE==maxDegreeselectss.SNO).Single().ToString()11、查询'3-105'号课程的平均分。selectavg(degree)fromscorewherecno='3-105'Linq:(fromsinScoreswheres.CNO=="3-105"selects.DEGREE).Average()Lambda:Scores.Where(s=>s.CNO=="3-105").Select(s=>s.DEGREE).Average()12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。selectavg(degree)fromscorewherecnolike'3%'groupbyCnohavingcount(*)>=5Linq:fromsinScoreswheres.CNO.StartsWith("3")groupsbys.CNOintoccwherecc.Count()>=5selectcc.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:SqlMethodlike也可以这样写:s.CNO.StartsWith("3")orSqlMethods.Like(s.CNO,"%3")13、查询最低分大于70，最高分小于90的Sno列。selectsnofromscoregroupbysnohavingmin(degree)>70andmax(degree)<90Linq:fromsinScoresgroupsbys.SNOintosswheress.Min(cc=>cc.DEGREE)>70&&ss.Max(cc=>cc.DEGREE)<90selectnew{sno=ss.Key}Lambda:Scores.GroupBy(s=>s.SNO).Where(ss=>((ss.Min(cc=>cc.DEGREE)>70)&&(ss.Max(cc=>cc.DEGREE)<90))).Select(ss=>new{sno=ss.Key})14、查询所有学生的Sname、Cno和Degree列。selects.sname,sc.cno,sc.degreefromstudentass,scoreasscwheres.sno=sc.snoLinq:fromsinStudentsjoinscinScoresons.SNOequalssc.SNOselectnew{s.SNAME,sc.CNO,sc.DEGREE}Lambda:Students.Join(Scores,s=>s.SNO,sc=>sc.SNO,(s,sc)=>new{SNAME=s.SNAME,CNO=sc.CNO,DEGREE=sc.DEGREE})15、查询所有学生的Sno、Cname和Degree列。selectsc.sno,c.cname,sc.degreefromcourseasc,scoreasscwherec.cno=sc.cnoLinq:fromcinCoursesjoinscinScoresonc.CNOequalssc.CNOselectnew{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})16、查询所有学生的Sname、Cname和Degree列。selects.sname,c.cname,sc.degreefromstudentass,courseasc,scoreasscwheres.sno=sc.snoandc.cno=sc.cnoLinq:fromsinStudentsfromcinCoursesfromscinScoreswheres.SNO==sc.SNO&&c.CNO==sc.CNOselectnew{s.SNAME,c.CNAME,sc.DEGREE}