类中的代码如下
1 class Program 2 { 3 private static int CountMark(int wirtten, int lab) 4 { 5 return wirtten + lab; 6 } 7 static void Main() 8 { 9 StudentDBDataContext db = new StudentDBDataContext("server=.;uid=sa;pwd=123456;database=studentdb;"); 10 db.Log = Console.Out; 11 12 #region 关系语句（where条件）查询 13 //var sql1 = from stu in db.stuInfo 14 // where stu.stuSex == "男" && stu.stuAge > 30 15 // select stu; 16 //var sql1 = db.stuInfo.Where(s => s.stuSex == "男" && s.stuAge > 30); 17 //foreach (var item in sql1) 18 //{ 19 // Console.WriteLine(item.GetType()); 20 // Console.WriteLine(item.stuNo + "\t" + item.stuName + "\t" + item.stuSex + "\t" + item.stuAge); 21 //} 22 #endregion 23 24 #region 匿名类型语句查询 25 //var sql2 = from s in db.stuInfo 26 // select new 27 // { 28 // 学号=s.stuNo, 29 // 姓名=s.stuName, 30 // 性别=s.stuSex 31 // }; 32 33 //foreach (var item in sql2) 34 //{ 35 // Console.WriteLine(item.GetType()); 36 // Console.WriteLine(item.学号 + "\t" + item.姓名 + "\t" + item.性别 + "\t" ); 37 //} 38 39 #endregion 40 41 42 #region First语句查询:第一条数据 Last:最后一条,从集合中取第一条或最后一条.(FirstOrDefault,LastOrDefault) 43 //var sql3 = from stu in db.stuInfo 44 // where stu.stuSex == "男" && stu.stuAge > 30 45 // select stu; 46 //var firstObj = sql3.First(); 47 //Console.WriteLine("First:" + firstObj.stuNo + "\t" + firstObj.stuName); 48 //var lastObj = sql3.ToList().Last(); 49 //Console.WriteLine("Last:" + lastObj.stuNo + "\t" + lastObj.stuName); 50 51 #endregion 52 53 #region Single语句查询:查询的结果只返回一条 SingleOrDefault() 54 //var sql3 = from stu in db.stuInfo 55 // where stu.stuNo == "s25301" 56 // select stu; 57 //var single = sql3.SingleOrDefault(); 58 //Console.WriteLine("First:" + single.stuNo + "\t" + single.stuName); 59 #endregion 60 61 #region 调用外部方法查询,与匿名类型结合使用 62 //var sql5 = from mark in db.stuMarks select mark; 63 64 //foreach (var item in sql5) 65 //{ 66 // Console.WriteLine(item.stuNo+"\t"+item.writtenExam+"\t"+item.LabExam+"\t总分:"+(item.LabExam+item.writtenExam)); 67 //} 68 69 //var sql5 = from mark in db.stuMarks 70 // select new 71 // { 72 // 学号 = mark.stuNo, 73 // 笔试 = mark.writtenExam, 74 // 机试 = mark.LabExam, 75 // 总分 = CountMark(mark.writtenExam, mark.LabExam) 76 // }; 77 78 //foreach (var item in sql5) 79 //{ 80 // Console.WriteLine(item.学号 + "\t" + item.笔试 + "\t" + item.机试 + "\t" + item.总分); 81 //} 82 #endregion 83 84 #region Group By分组查询 85 //根据性别分组，查询学生姓名 86 //var sql6 = from stu in db.stuInfo 87 // group stu by stu.stuSex 88 // into gs 89 // select gs; //gs:全局变量，分组后的数据 90 91 //foreach (var item in sql6) 92 //{ 93 // Console.WriteLine(item.Key); 94 // foreach (var item1 in item) 95 // { 96 // Console.WriteLine(item1.stuName+item1.stuSex); 97 // } 98 //} 99 //根据班级分组，获得班级总人数 100 //var sql6 = from stu in db.stuInfo 101 // group stu by stu.classID into gs 102 // select new 103 // { 104 // 班级=gs.Key, 105 // 人数=gs.Count() 106 // }; 107 //foreach (var item in sql6) 108 //{ 109 // Console.WriteLine(item.班级+"\t"+item.人数); 110 //} 111 112 //根据性别分组，查询男女生比例 113 114 115 //根据班级号和性别分组，查询男女比例(多列分组(用匿名类型)) 116 //var sql6 = from stu in db.stuInfo 117 // group stu by new 118 // { 119 // stu.classID, 120 // stu.stuSex 121 // } 122 // into gs 123 // select new 124 // { 125 // 班级 = gs.Key.classID, 126 // 性别=gs.Key.stuSex, 127 // 人数 = gs.Count() 128 // }; 129 130 //foreach (var item in sql6) 131 //{ 132 // Console.WriteLine(item.班级+"\t"+item.性别+"\t"+item.人数); 133 //} 134 // //select gs; 135 ////foreach (var item in sql6) 136 ////{ 137 //// Console.WriteLine(item.Key); 138 //// foreach (var item1 in item) 139 //// { 140 //// Console.WriteLine(item1.classID+"\t"+item1.stuSex+"\t"+item1.stuName); 141 //// } 142 ////} 143 #endregion 144 145 #region OrderBy排序查询 146 //var sql7 = from stu in db.stuInfo orderby stu.stuAge descending select stu; 147 //foreach (var item in sql7) 148 //{ 149 // Console.WriteLine(item.stuNo+"\t"+item.stuName+"\t"+item.stuAge); 150 //} 151 #endregion 152 153 #region 聚合函数查询 154 //聚合函数(count,sum,avg) 155 //var count = (from s in db.stuMarks select s).Count(); 156 //Console.WriteLine(count); 157 158 159 ////var avg = (from m in db.stuMarks select m.LabExam).Average(m => m); 160 //var avg = db.stuMarks.Average(m=>m.LabExam); 161 //Console.WriteLine(avg); 162 163 //var sum = db.stuMarks.Sum(m=>m.LabExam); 164 //Console.WriteLine(sum); 165 #endregion 166 167 #region Union/Intersect/Except 168 //Union:连接不同的集合，自动过滤相同项；延迟。即是将两个集合进行合并操作，过滤相同的项 169 //var sql8 = (from stu in db.stuInfo select stu.stuNo).Union(from sm in db.stuMarks select sm.stuNo); 170 //foreach (var item in sql8) 171 //{ 172 // Console.WriteLine(item); 173 //} 174 175 //Intersect（相交）:取相交项；延迟。即是获取不同集合的相同项（交集）。即先遍历第一个集合，找出所有唯一的元素，然后遍历第二个集合，并将每个元素与前面找出的元素作对比，返回所有在两个集合内都出现的元素。 176 //var sql8 = (from stu in db.stuInfo select stu.stuNo).Intersect(from sm in db.stuMarks select sm.stuNo); 177 //foreach (var item in sql8) 178 //{ 179 // Console.WriteLine(item); 180 //} 181 182 //Except（与非）:排除相交项；延迟。即是从某集合中删除与另一个集合中相同的项。先遍历第一个集合，找出所有唯一的元素，然后再遍历第二个集合，返回第二个集合中所有未出现在前面所得元素集合中的元素。 183 var sql8 = (from stu in db.stuInfo select stu.stuNo).Except(from sm in db.stuMarks select sm.stuNo); 184 foreach (var item in sql8) 185 { 186 Console.WriteLine(item); 187 } 188 #endregion 189 190 //去掉重复行 191 var sql = (from stu in db.stuInfo 192 select stu.stuSex).Distinct(); 193 foreach (var item in sql) 194 { 195 Console.WriteLine(item); 196 } 197 198 } 199 }
数据库的代码如下
1 create database StudentDB 2 go 3 use StudentDB 4 go 5 --班级表 6 create table classic 7 ( 8 classID int identity(1,1) primary key, 9 className varchar(20) not null 10 ) 11 go 12 insert into classic values(‘T102‘); 13 insert into classic values(‘T104‘); 14 insert into classic values(‘T110‘); 15 go 16 --学生表 17 CREATE TABLE stuInfo 18 ( 19 stuName NVARCHAR(20) NOT NULL, 20 stuNo NCHAR(6) NOT NULL, 21 stuSex NCHAR(4) NOT NULL, 22 stuAge int NOT NULL, 23 stuSeat int IDENTITY(1,1), 24 classID int foreign key references classic(classID) 25 ) 26 GO 27 INSERT INTO stuInfo VALUES(‘张秋丽‘,‘s25301‘,‘女‘,18,1) 28 INSERT INTO stuInfo VALUES(‘李斯文‘,‘s25303‘,‘男‘,22,2) 29 INSERT INTO stuInfo VALUES(‘李文才‘,‘s25302‘,‘男‘,31,3) 30 INSERT INTO stuInfo VALUES(‘马英‘,‘s25304‘,‘女‘,25,2) 31 INSERT INTO stuInfo VALUES(‘孙红雷‘,‘s25305‘,‘男‘,32,3) 32 INSERT INTO stuInfo VALUES(‘欧阳俊雄‘,‘s25306‘,‘男‘,28,1) 33 INSERT INTO stuInfo VALUES(‘江琳‘,‘s25307‘,‘女‘,23,1) 34 go 35 --课程表 36 CREATE TABLE stuMarks 37 (ExamNo CHAR(7) primary key NOT NULL, 38 stuNo NCHAR(6) NOT NULL, 39 writtenExam SMALLINT NOT NULL, 40 LabExam SMALLINT NOT NULL) 41 GO 42 INSERT INTO stuMarks VALUES(‘S271811‘,‘s25301‘,87,88) 43 INSERT INTO stuMarks VALUES(‘S271812‘,‘s25302‘,67,52) 44 INSERT INTO stuMarks VALUES(‘S271813‘,‘s25303‘,65,62) 45 INSERT INTO stuMarks VALUES(‘S271814‘,‘s25304‘,80,58) 46 INSERT INTO stuMarks VALUES(‘S271815‘,‘s25305‘,50,90) 47 INSERT INTO stuMarks VALUES(‘S271816‘,‘s25306‘,77,82) 48 go 49 alter table stuInfo 50 add constraint PK_stuNo primary key(stuNo) 51 alter table stuMarks 52 add constraint FK_stuno foreign key (stuNo) references stuinfo(stuno) 53 go 54 select * from classic 55 select * from stuInfo 56 select * from stuMarks
在项目中还要添加StudentDB.dbml文件。也就是linq to sql的那个文件。把数据库中的表都拖放在那个文件中就OK 了