Linq to SQL八大子句
查询数据库中的数据
| from… in子句 |
指定查询操作的数据源和范围变量 |
| select子句 |
指定查询结果的类型和表现形式 |
| where子句 |
筛选元素的逻辑条件，一般由逻辑运算符组成 |
| group… by子句 |
对查询进行分组 |
| order by子句 |
对查询结果进行排序，可以为“升序”或“降序” |
| join子句 |
连接多个查询操作的数据源 |
| let子句 |
引入用于存储查询表达式中的子表达式结果的范围变量 |
| into子句 |
提供一个临时标示符，充当对join、group或select子句的结果 |
一、from… in子句
以下例子均使用此数据库
USE master -- 设置当前数据库为master，以解除对Students的占用
GO
IF EXISTS(SELECT * FROM sys.databases WHERE name='Students')
DROP DATABASE Students
--创建一个Students数据库
CREATE DATABASE Students
GO
GO
IF EXISTS(SELECT * FROM sys.sysobjects WHERE xtype = 'U' AND name='StuInfo')
DROP TABLE StuInfo
USE Students -- 设置当前数据库为Students
CREATE TABLE StuInfo
(
-- 创建学生信息表
StuID int NOT NULL PRIMARY KEY, -- 学生学号，主键
StuName varchar(10) NOT NULL, -- 学生姓名，非空
StuSex char(2) NOT NULL DEFAULT('男'), -- 学生性别，非空
CHECK (StuSex ='男' or StuSex='女' )
)
GO
GO
IF EXISTS(SELECT * FROM sys.sysobjects WHERE xtype = 'U' AND name='StuMarks')
DROP TABLE StuMarks
USE Students -- 设置当前数据库为Students
CREATE TABLE StuMarks
(
-- 创建学生成绩表
ExamNo int identity(1,1) primary key, -- 考试编号，标识
StuID int NOT NULL references StuInfo(StuID), -- 考生学号，外键
Course varchar(10) not null, -- 考试科目
Score int default(0), -- 考试成绩
)
GO
GO
USE Students -- 设置当前数据库为Students
INSERT INTO StuInfo (StuID,StuName,StuSex)
SELECT 1,'张三','男' UNION
SELECT 2,'李四','男' UNION
SELECT 3,'钱七','女' UNION
SELECT 4,'王五','女' UNION
SELECT 5,'赵六','男'
GO
GO
USE Students -- 设置当前数据库为Students
INSERT INTO StuMarks (StuID,Course,Score)
SELECT 1,'语文',70 UNION
SELECT 1,'数学',89 UNION
SELECT 2,'语文',33 UNION
SELECT 2,'数学',50 UNION
SELECT 3,'语文',79 UNION
SELECT 3,'数学',90 UNION
SELECT 4,'语文',88 UNION
SELECT 4,'数学',74 UNION
SELECT 5,'语文',64 UNION
SELECT 5,'数学',92
GO
GO
SELECT * FROM StuInfo;
SELECT * FROM StuMarks;FROM...IN子句
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataClasses1DataContext dc = new DataClasses1DataContext();
Response.Write("<br/>查询数据库<br/>");
var stus1 = from stu in dc.StuInfos
select stu;
foreach (var stu in stus1)
{
Response.Write(stu.StuID + "、" + stu.StuName + "、" + stu.StuSex + "<br/>");
}
Response.Write("<br/>查询对象<br/>");
List<int> datas = new List<int> { 1, 3, 5, 7, 9 };
var stus2 = from data in datas
select data;
foreach (var stu in stus2)
{
Response.Write(stu + "<br/>");
}
}
}运行的结果如下：
二、select子句
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataClasses1DataContext dc = new DataClasses1DataContext();
Response.Write("<br/>分组查询所有男学生和女同学的信息<br/>");
var stus1 = from stu in dc.StuInfos
group stu by stu.StuSex;
foreach (var groups in stus1)
{
Response.Write("<br/>分组名称：" + groups.Key + "<br/>");
foreach (var stu in groups)
{
Response.Write(stu.StuID + "、" + stu.StuName + "<br/>");
}
}
Response.Write("<br/>分组查询所有男学生的信息<br/>");
var stus2 = (from stu in dc.StuInfos
group stu by stu.StuSex).ToList();
foreach (var stu in stus2.ElementAt(0))
{
Response.Write(stu.StuID + "、" + stu.StuName + "<br/>");
}
}
}运行的结果：
三、where子句
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataClasses1DataContext dc = new DataClasses1DataContext();
Response.Write("<br/>查询所有男学生信息<br/>");
var stus1 = from stu in dc.StuInfos
where stu.StuSex == "男"
select stu;
foreach (var stu in stus1)
{
Response.Write(stu.StuID + "、" + stu.StuName + "<br/>");
}
Response.Write("<br/>查询所有姓张的男学生信息<br/>");
var stus2 = from stu in dc.StuInfos
where stu.StuSex == "男" && stu.StuName.StartsWith("张")
select stu;
foreach (var stu in stus2)
{
Response.Write(stu.StuID + "、" + stu.StuName + "<br/>");
}
}
}运行的结果：
四、group … by子句
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataClasses1DataContext dc = new DataClasses1DataContext();
Response.Write("<br/>分组查询所有男学生和女同学的信息<br/>");
var stus1 = from stu in dc.StuInfos
group stu by stu.StuSex;
foreach (var groups in stus1)
{
Response.Write("<br/>分组名称：" + groups.Key + "<br/>");
foreach (var stu in groups)
{
Response.Write(stu.StuID + "、" + stu.StuName + "<br/>");
}
}
Response.Write("<br/>分组查询所有男学生的信息<br/>");
var stus2 = (from stu in dc.StuInfos
group stu by stu.StuSex).ToList();
foreach (var stu in stus2.ElementAt(0))
{
Response.Write(stu.StuID + "、" + stu.StuName + "<br/>");
}
}
}运行结果为：
五、orderby子句
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataClasses1DataContext dc = new DataClasses1DataContext();
Response.Write("<br/>降序查询所有学生的成绩信息<br/>");
var marks1 = from mark in dc.StuMarks
orderby mark.Score descending
select mark;
foreach (var mark in marks1)
{
Response.Write(mark.StuID + "、" + mark.Course + "、" + mark.Score + "<br/>");
}
Response.Write("<br/>降序查询所有学生的数学成绩信息<br/>");
var marks2 = from mark in dc.StuMarks
where mark.Course=="数学"
orderby mark.Score descending
select mark;
foreach (var mark in marks2)
{
Response.Write(mark.StuID + "、" + mark.Score + "<br/>");
}
}
}运行结果为：
六、join子句
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataClasses1DataContext dc = new DataClasses1DataContext();
Response.Write("<br/>查询所有学生的姓名及考试信息<br/>");
var res1 = from stu in dc.StuInfos
join mark in dc.StuMarks
on stu.StuID equals mark.StuID
select new { stu.StuName,mark.Course,mark.Score};
foreach (var stu in res1)
{
Response.Write(stu.StuName + "、" + stu.Course + "、" + stu.Score + "<br/>");
}
Response.Write("<br/>查询所有学生的姓名及考试信息<br/>");
var res2 = from stu in dc.StuInfos
from mark in dc.StuMarks
where stu.StuID == mark.StuID
select new { stu.StuName, mark.Course, mark.Score };
foreach (var stu in res2)
{
Response.Write(stu.StuName + "、" + stu.Course + "、" + stu.Score + "<br/>");
}
}
}运行结果为：
七、let子句
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataClasses1DataContext dc = new DataClasses1DataContext();
Response.Write("<br/>查询所有姓张的男学生信息<br/>");
var stus1 = from stu in dc.StuInfos
let start = stu.StuName.StartsWith("张")
where stu.StuSex == "男" && start //== true
select stu;
foreach (var stu in stus1)
{
Response.Write(stu.StuID + "、" + stu.StuName + "<br/>");
}
}
}运行结果为：
八、into子句
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataClasses1DataContext dc = new DataClasses1DataContext();
Response.Write("<br/>分组查询所有男学生和女同学的信息<br/>");
var stus1 = from stu in dc.StuInfos
group stu by stu.StuSex into res
select res;
foreach (var groups in stus1)
{
Response.Write("<br/>分组名称：" + groups.Key + "<br/>");
foreach (var stu in groups)
{
Response.Write(stu.StuID + "、" + stu.StuName + "<br/>");
}
}
}
}运行结果为：
