第十四节:Lambda、linq、SQL的相爱相杀(3)

一. SQL开篇

1. where用法

1      #region 封装EF调用SQL语句查询
2         public static List<T> ExecuteQuery<T>(string sql, params SqlParameter[] pars)
3         {
4             return db.Database.SqlQuery<T>(sql, pars).ToList();
5         }
6         #endregion
 1        #region 01-where用法
 2             {
 3                 //1. where用法
 4                 //1.1 查询账号为admin的用户信息
 5                 Console.WriteLine("---------------------------- 1. where用法   ----------------------------------------");
 6                 Console.WriteLine("---------------------------- 1.1 查询账号为admin的用户信息   ----------------------------------------");
 7                 string sql1 = @"select * from Sys_UserInfor where userAccount = 'admin'";
 8                 List<Sys_UserInfor> sUserList1 = ExecuteQuery<Sys_UserInfor>(sql1);
 9                 foreach (var item in sUserList1)
10                 {
11                     Console.WriteLine("用户名:{0},用户账号:{1},用户年龄:{2},用户性别:{3}", item.userName, item.userAccount, item.userAge, item.userSex);
12                 }
13                 //1.2  查询账号为中包含admin且性别为男的用户信息
14                 Console.WriteLine("---------------------------- 1.2  查询账号为中包含admin且性别为男的用户信息   ----------------------------------------");
15                 string sql2 = @"select * from Sys_UserInfor where userAccount like '%admin%' and userSex='男'";
16                 List<Sys_UserInfor> sUserList2 = ExecuteQuery<Sys_UserInfor>(sql2);
17                 foreach (var item in sUserList2)
18                 {
19                     Console.WriteLine("用户名:{0},用户账号:{1},用户年龄:{2},用户性别:{3}", item.userName, item.userAccount, item.userAge, item.userSex);
20                 }
21             }
22             #endregion

2. select用法

 1   #region 02-select用法
 2             {
 3                 //2. select用法 (SQL语句中,查询部分字段,必须要有个实体接收,不能用var类型接收)
 4                 //2.1 查询账号中包含 admin 的用户的 姓名、年龄和性别 三条信息
 5                 Console.WriteLine("---------------------------- 2. select用法   ----------------------------------------");
 6                 Console.WriteLine("-------------2.1 查询账号中包含 admin 的用户的 姓名、年龄和性别 三条信息 )-------------------------");
 7                 string sql1 = @"select userName,userAge,userSex from Sys_UserInfor where userAccount like '%admin%'";
 8                 List<model1> sUserList1 = ExecuteQuery<model1>(sql1);
 9                 sUserList1.ForEach(u =>
10                 {
11                     Console.WriteLine("用户名:{0},用户年龄:{1},用户性别:{2}", u.userName, u.userAge, u.userSex);
12                 });
13 
14             }
15             #endregion

 

3. order by用法

 1   #region 03-order by用法
 2             {
 3                 //区分:在Lambda中有 orderby(OrderByDescending、ThenBy、ThenByDescending),但在SQL中 只有order by  asc(默认是升序) 和order by desc
 4                 //需要按照多个条件进行升序或降序,格式为: order by x1, x2 descending,x3(表示先按照x1升序排,x1相同的话,再按照x2降序排,x2相同的话,在按照x3升序排列)
 5                 //3. Order By用法 (单条件升降序、多条件综合排序)
 6                 //3.1 查询delflag 为1 的所有用户信息,按照时间升序排列
 7                 Console.WriteLine("------3. orderby用法  (单条件升降序、多条件综合排序)-------------");
 8                 Console.WriteLine("--------------------- 3.1 查询delflag 为1 的所有用户信息,按照时间升序排列   ------------------------------");
 9 
10                 string sql1 = @"select * from Sys_UserInfor where delFlag =1 order by addTime";
11                 List<Sys_UserInfor> sUserList1 = ExecuteQuery<Sys_UserInfor>(sql1);
12                 foreach (var item in sUserList1)
13                 {
14                     Console.WriteLine("用户名:{0},用户账号:{1},用户年龄:{2},用户性别:{3},创建时间:{4}", item.userName, item.userAccount, item.userAge, item.userSex, item.addTime);
15                 }
16                 //3.2 查询delflag 为1 的所有用户信息,先按照时间升序排列,再按照年龄降序
17                 Console.WriteLine("---------------3.2 查询delflag 为1 的所有用户信息,先按照时间升序排列,再按照年龄降序----------------------");
18                 string sql2 = @"select * from Sys_UserInfor where delFlag =1 order by addTime,userAge desc";
19                 List<Sys_UserInfor> sUserList2 = ExecuteQuery<Sys_UserInfor>(sql2);
20                 foreach (var item in sUserList2)
21                 {
22                     Console.WriteLine("用户名:{0},用户账号:{1},用户年龄:{2},用户性别:{3},创建时间:{4}", item.userName, item.userAccount, item.userAge, item.userSex, item.addTime);
23                 }
24             }
25             #endregion

 

4. 多表关联查询

用到的用户表和用户登录记录表中的数据如下:

  

 SQL中的关联包括:内连接、外连接、全连接。

1.内连接:也叫等值连接,查询出来的结果是多表交叉共有的,关键字inner join进行连接,其中inner可以省略。

PS:SQL中一般没有给内连接分类的,但在linq中通常分为显示内连接和隐式内连接。

  注意:单纯的select * from a,b是笛卡尔乘积。比如a表有5条数据,b表有3条数据,那么最后的结果有5*3=15条数据。但是如果对两个表进行关联:select * from a,b where a.id = b.id 意思就变了,此时就等价于:select * from a inner join b on a.id = b.id。即就是内连接,但是这种写法并不符合规范,可能只对某些数据库管用,如sqlserver。推荐最好不要这样写。最好写成inner join的写法。

2.外连接:左外连接和右外连接.分别用left join 或right join 关键字来连接。

(1).左外连接:查询出JOIN左边表的全部数据,JOIN右边的表不匹配的数据用NULL来填充。

(2).右外连接:查询出JOIN右边表的全部数据,JOIN左边的表不匹配的数据用NULL来填充。 

 

 

 

 

 3.全连接:返回左表和右表中所有没有匹配的行,用关键字 full join来表示,适用于SQLServer,MySQL中没有这种用法

 1  //4.查询账号中含有admin的所有用户的用户昵称、账号、和登录信息
 2                 //4.1 内连接的写法1
 3                 Console.WriteLine("---------------04-多表关联查询--------------------");
 4                 Console.WriteLine("---------------4.1 内连接的写法1--------------------");
 5                 string sql1 = @"select a.userName,a.userAccount,b.loginCity,b.loginIp,b.loginTime
 6                                 from Sys_UserInfor a,LoginRecords b
 7                                 where a.id =b.userId";
 8                 List<model2> uList1 = ExecuteQuery<model2>(sql1);
 9                 foreach (var item in uList1)
10                 {
11                     Console.WriteLine("姓名:{0},账号:{1},登录城市:{2},登录IP:{3},登录时间:{4}", item.userName, item.userAccount, item.loginCity, item.loginIp, item.loginTime);
12                 }
13                 //4.2 内连接的写法2
14                 Console.WriteLine("---------------4.2 内连接的写法2 --------------------");
15                 string sql2 = @"select a.userName,a.userAccount,b.loginCity,b.loginIp,b.loginTime
16                                 from Sys_UserInfor a inner join LoginRecords b on  a.id =b.userId";
17 
18                 List<model2> uList2 = ExecuteQuery<model2>(sql2);
19                 foreach (var item in uList2)
20                 {
21                     Console.WriteLine("姓名:{0},账号:{1},登录城市:{2},登录IP:{3},登录时间:{4}", item.userName, item.userAccount, item.loginCity, item.loginIp, item.loginTime);
22                 }
23 
24                 //4.3 查询所有用户的登录信息(左外连接的方式)
25                 Console.WriteLine("-----------------------4.3 查询所有用户的登录信息(左外连接的方式)----------------------------");
26                 string sql3 = @"select a.userName,a.userAccount,b.loginCity,b.loginIp,b.loginTime
27                                 from Sys_UserInfor a left join LoginRecords b on  a.id =b.userId
28                                 ";
29                 List<model2> uList3 = ExecuteQuery<model2>(sql3);
30                 foreach (var item in uList3)
31                 {
32                     Console.WriteLine("姓名:{0},账号:{1},登录城市:{2},登录IP:{3},登录时间:{4}", item.userName, item.userAccount, item.loginCity, item.loginIp, item.loginTime);
33                 }
34 
35                 //4.4 查询所有用户的登录信息(右外连接的方式)
36                 Console.WriteLine("-----------------------4.4 查询所有用户的登录信息(右外连接的方式)----------------------------");
37                 string sql4 = @"select a.userName,a.userAccount,b.loginCity,b.loginIp,b.loginTime
38                                 from Sys_UserInfor a right join LoginRecords b on  a.id =b.userId
39                                 ";
40                 List<model2> uList4 = ExecuteQuery<model2>(sql4);
41                 foreach (var item in uList4)
42                 {
43                     Console.WriteLine("姓名:{0},账号:{1},登录城市:{2},登录IP:{3},登录时间:{4}", item.userName, item.userAccount, item.loginCity, item.loginIp, item.loginTime);
44                 }
45                 //4.5 查询所有用户的登录信息(全连接)
46                 Console.WriteLine("-----------------------4.5 查询所有用户的登录信息(全连接)----------------------------");
47                 string sql5 = @"select a.userName,a.userAccount,b.loginCity,b.loginIp,b.loginTime
48                                 from Sys_UserInfor a full join LoginRecords b on  a.id =b.userId
49                                 ";
50                 List<model2> uList5 = ExecuteQuery<model2>(sql5);
51                 foreach (var item in uList5)
52                 {
53                     Console.WriteLine("姓名:{0},账号:{1},登录城市:{2},登录IP:{3},登录时间:{4}", item.userName, item.userAccount, item.loginCity, item.loginIp, item.loginTime);
54                 }

 运行结果:

 

  5. group By用法

 group By分组(SQL中的group by分组和linq、lambda完全不同。特别注意:select指定的字段要么就要包含在Group By语句的后面,作为分组的依据;要么就要被包含在聚合函数中) 常见的聚合函数有:sum avg min max

 1   #region 05-group By分组
 2             {
 3                 //5. Group By分组(SQL中的group by分组和linq、lambda完全不同,
 4                 /*特别注意:select指定的字段要么就要包含在Group By语句的后面,作为分组的依据;要么就要被包含在聚合函数中)
 5                  常见的聚合函数有:sum avg min max
 6                  */
 7 
 8                 //5.1 根据用户的性别进行分类,并且统计不同性别的年龄总和
 9                 Console.WriteLine("-------------------- 5. GroupBy分组------------------------");
10                 Console.WriteLine("-------------------- 5.1 根据用户的性别进行分类,并且统计不同性别的年龄总和------------------------");
11                 string sql1 = @"select userSex,SUM(userAge) as TotalAges
12                                 from Sys_UserInfor
13                                 group by userSex";
14                 List<model3> sUserListGroup1 = ExecuteQuery<model3>(sql1);
15                 foreach (var item in sUserListGroup1)
16                 {
17                       Console.WriteLine("性别:{0},年龄总和:{1}", item.userSex, item.TotalAges);
18                 }
19                  
20                 //5.2 根据用户性别进行分类,并且统计不同性别年龄的最大值
21                 Console.WriteLine("-------------5.2 根据用户性别进行分类,并且统计不同性别年龄的最大值-------------------");
22                 string sql2 = @"select userSex,MAX(userAge) as TotalAges
23                                 from Sys_UserInfor
24                                 group by userSex";
25                 List<model3> sUserListGroup2 = ExecuteQuery<model3>(sql2);
26                 foreach (var item in sUserListGroup2)
27                 {
28                     Console.WriteLine("性别:{0},年龄最大值:{1}", item.userSex, item.TotalAges);
29                 }
30             }
31             #endregion

 

 

该文章不再更新,详见:https://www.cnblogs.com/yaopengfei/p/14035067.html

 

 

 

!

  • 作       者 : Yaopengfei(姚鹏飞)
  • 博客地址 : http://www.cnblogs.com/yaopengfei/
  • 声     明1 : 如有错误,欢迎讨论,请勿谩骂^_^。
  • 声     明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址,否则保留追究法律责任的权利。
 
posted @ 2017-08-10 11:57  Yaopengfei  阅读(798)  评论(1编辑  收藏