第十四节：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 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。
