Linq to Sql ------------------------复习（一）

类中的代码如下

class Program
    {
        private static int CountMark(int wirtten, int lab)
        {
            return wirtten + lab;
        }
        static void Main()
        {
            StudentDBDataContext db = new StudentDBDataContext("server=.;uid=sa;pwd=123456;database=studentdb;");
            db.Log = Console.Out;

            #region 关系语句（where条件）查询
            //var sql1 = from stu in db.stuInfo
            //           where stu.stuSex == "男" && stu.stuAge > 30
            //           select stu;
            //var sql1 = db.stuInfo.Where(s => s.stuSex == "男" && s.stuAge > 30);
            //foreach (var item in sql1)
            //{
            //    Console.WriteLine(item.GetType());
            //    Console.WriteLine(item.stuNo + "\t" + item.stuName + "\t" + item.stuSex + "\t" + item.stuAge);
            //}
            #endregion

            #region 匿名类型语句查询
            //var sql2 = from s in db.stuInfo
            //           select new
            //           {
            //               学号=s.stuNo,
            //               姓名=s.stuName,
            //               性别=s.stuSex
            //           };

            //foreach (var item in sql2)
            //{
            //    Console.WriteLine(item.GetType());
            //    Console.WriteLine(item.学号 + "\t" + item.姓名 + "\t" + item.性别 + "\t" );
            //}

            #endregion


            #region First语句查询:第一条数据  Last:最后一条,从集合中取第一条或最后一条.(FirstOrDefault,LastOrDefault)
            //var sql3 = from stu in db.stuInfo
            //           where stu.stuSex == "男" && stu.stuAge > 30
            //           select stu;
            //var firstObj = sql3.First();
            //Console.WriteLine("First:" + firstObj.stuNo + "\t" + firstObj.stuName);
            //var lastObj = sql3.ToList().Last();
            //Console.WriteLine("Last:" + lastObj.stuNo + "\t" + lastObj.stuName);

            #endregion

            #region Single语句查询:查询的结果只返回一条 SingleOrDefault()
            //var sql3 = from stu in db.stuInfo
            //           where stu.stuNo == "s25301"
            //           select stu;
            //var single = sql3.SingleOrDefault();
            //Console.WriteLine("First:" + single.stuNo + "\t" + single.stuName);
            #endregion

            #region 调用外部方法查询,与匿名类型结合使用
            //var sql5 = from mark in db.stuMarks select mark;

            //foreach (var item in sql5)
            //{
            //    Console.WriteLine(item.stuNo+"\t"+item.writtenExam+"\t"+item.LabExam+"\t总分:"+(item.LabExam+item.writtenExam));
            //}

            //var sql5 = from mark in db.stuMarks
            //           select new
            //               {
            //                   学号 = mark.stuNo,
            //                   笔试 = mark.writtenExam,
            //                   机试 = mark.LabExam,
            //                   总分 = CountMark(mark.writtenExam, mark.LabExam)
            //               };

            //foreach (var item in sql5)
            //{
            //    Console.WriteLine(item.学号 + "\t" + item.笔试 + "\t" + item.机试 + "\t" + item.总分);
            //}
            #endregion

            #region Group By分组查询
            //根据性别分组，查询学生姓名
            //var sql6 = from stu in db.stuInfo
            //           group stu by stu.stuSex
            //               into gs
            //               select gs; //gs:全局变量，分组后的数据

            //foreach (var item in sql6)
            //{
            //    Console.WriteLine(item.Key);
            //    foreach (var item1  in item)
            //    {
            //        Console.WriteLine(item1.stuName+item1.stuSex);
            //    }
            //}
            //根据班级分组，获得班级总人数
            //var sql6 = from stu in db.stuInfo
            //           group stu by stu.classID into gs
            //           select new 
            //           {
            //               班级=gs.Key,
            //               人数=gs.Count()
            //           };
            //foreach (var item in sql6)
            //{
            //    Console.WriteLine(item.班级+"\t"+item.人数);
            //}

            //根据性别分组，查询男女生比例


            //根据班级号和性别分组，查询男女比例(多列分组(用匿名类型))
            //var sql6 = from stu in db.stuInfo
            //           group stu by new
            //           {
            //               stu.classID,
            //               stu.stuSex
            //           }
            //               into gs
            //               select new
            //               {
            //                   班级 = gs.Key.classID,
            //                   性别=gs.Key.stuSex,
            //                   人数 = gs.Count()
            //               };

            //foreach (var item in sql6)
            //{
            //    Console.WriteLine(item.班级+"\t"+item.性别+"\t"+item.人数);
            //}
            //           //select gs;
            ////foreach (var item in sql6)
            ////{
            ////    Console.WriteLine(item.Key);
            ////    foreach (var item1 in item)
            ////    {
            ////        Console.WriteLine(item1.classID+"\t"+item1.stuSex+"\t"+item1.stuName);
            ////    }
            ////}
            #endregion

            #region OrderBy排序查询
            //var sql7 = from stu in db.stuInfo orderby stu.stuAge descending select stu;
            //foreach (var item in sql7)
            //{
            //    Console.WriteLine(item.stuNo+"\t"+item.stuName+"\t"+item.stuAge);
            //}
            #endregion

            #region 聚合函数查询
            //聚合函数(count,sum,avg)
            //var count = (from s in db.stuMarks select s).Count();
            //Console.WriteLine(count);


            ////var avg = (from m in db.stuMarks select m.LabExam).Average(m => m); 
            //var avg = db.stuMarks.Average(m=>m.LabExam);
            //Console.WriteLine(avg);

            //var sum = db.stuMarks.Sum(m=>m.LabExam);
            //Console.WriteLine(sum);
            #endregion

            #region Union/Intersect/Except
            //Union:连接不同的集合，自动过滤相同项；延迟。即是将两个集合进行合并操作，过滤相同的项
            //var sql8 = (from stu in db.stuInfo select stu.stuNo).Union(from sm in db.stuMarks select sm.stuNo);
            //foreach (var item in sql8)
            //{
            //    Console.WriteLine(item);
            //}

            //Intersect（相交）:取相交项；延迟。即是获取不同集合的相同项（交集）。即先遍历第一个集合，找出所有唯一的元素，然后遍历第二个集合，并将每个元素与前面找出的元素作对比，返回所有在两个集合内都出现的元素。
            //var sql8 = (from stu in db.stuInfo select stu.stuNo).Intersect(from sm in db.stuMarks select sm.stuNo);
            //foreach (var item in sql8)
            //{
            //    Console.WriteLine(item);
            //}

            //Except（与非）:排除相交项；延迟。即是从某集合中删除与另一个集合中相同的项。先遍历第一个集合，找出所有唯一的元素，然后再遍历第二个集合，返回第二个集合中所有未出现在前面所得元素集合中的元素。
            var sql8 = (from stu in db.stuInfo select stu.stuNo).Except(from sm in db.stuMarks select sm.stuNo);
            foreach (var item in sql8)
            {
                Console.WriteLine(item);
            }
            #endregion

            //去掉重复行
            var sql = (from stu in db.stuInfo
                      select stu.stuSex).Distinct();
            foreach (var item in sql)
            {
                Console.WriteLine(item);
            }
                     
        }
    }

 数据库的代码如下

create database StudentDB
go
use StudentDB
go
--班级表
create table classic
(
    classID int identity(1,1) primary key,
    className varchar(20) not null
)
go
insert into classic values('T102');
insert into classic values('T104');
insert into classic values('T110');
go
--学生表
CREATE TABLE stuInfo
(
 stuName    NVARCHAR(20)    NOT NULL,
 stuNo      NCHAR(6)        NOT NULL,
 stuSex     NCHAR(4)        NOT NULL,
 stuAge     int             NOT NULL,
 stuSeat    int             IDENTITY(1,1),
 classID    int             foreign key references classic(classID)
)      
GO
INSERT INTO stuInfo VALUES('张秋丽','s25301','女',18,1)
INSERT INTO stuInfo VALUES('李斯文','s25303','男',22,2)
INSERT INTO stuInfo VALUES('李文才','s25302','男',31,3)
INSERT INTO stuInfo VALUES('马英','s25304','女',25,2)
INSERT INTO stuInfo VALUES('孙红雷','s25305','男',32,3)
INSERT INTO stuInfo VALUES('欧阳俊雄','s25306','男',28,1)
INSERT INTO stuInfo VALUES('江琳','s25307','女',23,1)
go
--课程表
CREATE TABLE stuMarks
(ExamNo CHAR(7) primary key NOT NULL,
 stuNo NCHAR(6) NOT NULL,
 writtenExam SMALLINT NOT NULL,
 LabExam SMALLINT NOT NULL)
GO
INSERT INTO stuMarks VALUES('S271811','s25301',87,88)
INSERT INTO stuMarks VALUES('S271812','s25302',67,52)
INSERT INTO stuMarks VALUES('S271813','s25303',65,62)
INSERT INTO stuMarks VALUES('S271814','s25304',80,58)
INSERT INTO stuMarks VALUES('S271815','s25305',50,90)
INSERT INTO stuMarks VALUES('S271816','s25306',77,82)
go
alter table stuInfo
    add constraint PK_stuNo primary key(stuNo)
alter table stuMarks
    add constraint FK_stuno foreign key (stuNo) references stuinfo(stuno)
go
select * from classic
select * from stuInfo
select * from stuMarks

在项目中还要添加StudentDB.dbml文件。也就是linq  to sql的那个文件。把数据库中的表都拖放在那个文件中就OK 了