• Linq to Sql ------------------------复习(一)


    类中的代码如下
      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 + "	" + item.stuName + "	" + item.stuSex + "	" + 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.学号 + "	" + item.姓名 + "	" + item.性别 + "	" );
     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 + "	" + firstObj.stuName);
     48             //var lastObj = sql3.ToList().Last();
     49             //Console.WriteLine("Last:" + lastObj.stuNo + "	" + 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 + "	" + 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+"	"+item.writtenExam+"	"+item.LabExam+"	总分:"+(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.学号 + "	" + item.笔试 + "	" + item.机试 + "	" + 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.班级+"	"+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.班级+"	"+item.性别+"	"+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+"	"+item1.stuSex+"	"+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+"	"+item.stuName+"	"+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     }
    View Code

     数据库的代码如下

     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
    View Code

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

  • 相关阅读:
    进制转换
    客户信息管理系统
    ORACLE PL/SQL编程
    Oracle性能优化
    Django-admin
    PyCharm激活
    Java容器源码攻坚战--第一战:Iterator
    Java总结之映射家族--Map概览
    Java总结之容器家族--Collection
    2.安卓基础之Activity启动方式
  • 原文地址:https://www.cnblogs.com/liujie1111/p/3606558.html
Copyright © 2020-2023  润新知