1. from in子句:指定查询操作的数据源和变量范围
//创建LinqDB数据库上下文实例DataClasses1DataContext db = new DataClasses1DataContext();//使用lINQ查询表达式查询数据var query = from stu in db.studentjoin m in db.mark on stu.sid equals m.sidselect new { 姓名=stu.sname,科目=m.subject,成绩=m.score };//设置数据源并绑定数据GridView1.DataSource = query;GridView1.DataBind();
2. select子句:指定查询结果的类型和表现形式
3. where子句:删选元素的逻辑条件,一般由逻辑运算符组成
4. groupby子句:对查询进行分组
DataClasses1DataContext db = new DataClasses1DataContext(); var query = from m in db.markgroup m by m.mid into gorderby g.Keyselect new { g.Key, 成绩 = g.Max(c => c.score) };GridView1.DataSource = query; GridView1.DataBind();
5. orderby子句:对查询结果排序,可以为升序或降序
降序:
DataClasses1DataContext db = new DataClasses1DataContext();var q = db.mark.OrderByDescending(P => P.score ).ToList();GridView1.DataSource = q;GridView1.DataBind();
或者:
升序:
//创建LinqDB数据库上下文实例DataClasses1DataContext db = new DataClasses1DataContext();//使用lINQ查询表达式查询数据var query = from m in db.markjoin stu in db.student on m.sid equals stu.sidorderby m.score ascendingselect new { 姓名 =stu.sname, 科目 = m.subject, 成绩 = m.score };//设置数据源并绑定数据GridView1.DataSource = query;GridView1.DataBind();
或者
DataClasses1DataContext db = new DataClasses1DataContext();var q = db.mark.OrderBy(P => P.score ).ToList();GridView1.DataSource = q;GridView1.DataBind();
6. join子句:连接多个查询操作的数据源
//创建LinqDB数据库上下文实例DataClasses1DataContext db = new DataClasses1DataContext();//使用lINQ查询表达式查询数据var query = from stu in db.studentjoin m in db.mark on stu.sid equals m.sidselect new { 姓名=stu.sname,科目=m.subject,成绩=m.score };//设置数据源并绑定数据GridView1.DataSource = query;GridView1.DataBind();
7. let子句:引入用于储存查询表达式中的子表达式结果的范围变量
DataClasses1DataContext db = new DataClasses1DataContext();var q = from m in db.marklet n = m.score where n >90select m ;GridView1.DataSource = q;GridView1.DataBind();
8. into子句:提供一个临时标志符,充当对group,select,join,子句的结果
用于select:
DataClasses1DataContext db = new DataClasses1DataContext();var q = from stu in db.studentwhere stu.sid > 2select new { 学号 = stu.sid, 姓名 = stu.sname }into sorderby s.学号select s;GridView1.DataSource = q;GridView1.DataBind();
附数据库代码:
use mastergoif exists(select * from sys.databases where name='Student')drop database Studentcreate database Studentgouse Studentif exists(select * from sys.tables where name='student')drop database studentcreate table student(sid int primary key identity(1,1) ,sname varchar(10) not null)if exists(select * from sys.tables where name='mark')drop database markcreate table mark(mid int primary key identity(1,1) ,subject varchar(10) not null,score int not null,sid int references student(sid))insert into student values ('林彦俊')insert into student values ('灵超')insert into student values ('尤长靖')insert into student values ('木子洋')insert into mark values ('html',100,1)insert into mark values ('sql',90,1)insert into mark values ('html',10,2)insert into mark values ('sql',60,2)insert into mark values ('html',99,3)insert into mark values ('sql',91,4)select *from studentselect * from mark
数据库运行结果: