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