一. SQL 开篇
1. where用法
#region 封装EF调用SQL语句查询
public static List<T> ExecuteQuery<T>(string sql, params SqlParameter[] pars)
{
return db.Database.SqlQuery<T>(sql, pars).ToList();
}
#endregion
#region 01-where用法
{
//1. where用法
//1.1 查询账号为admin的用户信息
Console.WriteLine("---------------------------- 1. where用法 ----------------------------------------");
Console.WriteLine("---------------------------- 1.1 查询账号为admin的用户信息 ----------------------------------------");
string sql1 = @"select * from Sys_UserInfor where userAccount = 'admin'";
List<Sys_UserInfor> sUserList1 = ExecuteQuery<Sys_UserInfor>(sql1);
foreach (var item in sUserList1)
{
Console.WriteLine("用户名：{0},用户账号：{1},用户年龄：{2},用户性别：{3}", item.userName, item.userAccount, item.userAge, item.userSex);
}
//1.2 查询账号为中包含admin且性别为男的用户信息
Console.WriteLine("---------------------------- 1.2 查询账号为中包含admin且性别为男的用户信息 ----------------------------------------");
string sql2 = @"select * from Sys_UserInfor where userAccount like '%admin%' and userSex='男'";
List<Sys_UserInfor> sUserList2 = ExecuteQuery<Sys_UserInfor>(sql2);
foreach (var item in sUserList2)
{
Console.WriteLine("用户名：{0},用户账号：{1},用户年龄：{2},用户性别：{3}", item.userName, item.userAccount, item.userAge, item.userSex);
}
}
#endregion
2. select用法
#region 02-select用法
{
//2. select用法 (SQL语句中，查询部分字段，必须要有个实体接收，不能用var类型接收)
//2.1 查询账号中包含 admin 的用户的 姓名、年龄和性别 三条信息
Console.WriteLine("---------------------------- 2. select用法 ----------------------------------------");
Console.WriteLine("-------------2.1 查询账号中包含 admin 的用户的 姓名、年龄和性别 三条信息 )-------------------------");
string sql1 = @"select userName,userAge,userSex from Sys_UserInfor where userAccount like '%admin%'";
List<model1> sUserList1 = ExecuteQuery<model1>(sql1);
sUserList1.ForEach(u =>
{
Console.WriteLine("用户名：{0},用户年龄：{1},用户性别：{2}", u.userName, u.userAge, u.userSex);
}); }
#endregion
3. order by用法
#region 03-order by用法
{
//区分：在Lambda中有 orderby(OrderByDescending、ThenBy、ThenByDescending),但在SQL中 只有order by asc(默认是升序) 和order by desc
//需要按照多个条件进行升序或降序，格式为： order by x1, x2 descending,x3(表示先按照x1升序排，x1相同的话，再按照x2降序排，x2相同的话，在按照x3升序排列)
//3. Order By用法 (单条件升降序、多条件综合排序)
//3.1 查询delflag 为1 的所有用户信息,按照时间升序排列
Console.WriteLine("------3. orderby用法 (单条件升降序、多条件综合排序)-------------");
Console.WriteLine("--------------------- 3.1 查询delflag 为1 的所有用户信息,按照时间升序排列 ------------------------------"); string sql1 = @"select * from Sys_UserInfor where delFlag =1 order by addTime";
List<Sys_UserInfor> sUserList1 = ExecuteQuery<Sys_UserInfor>(sql1);
foreach (var item in sUserList1)
{
Console.WriteLine("用户名：{0},用户账号：{1},用户年龄：{2},用户性别：{3},创建时间：{4}", item.userName, item.userAccount, item.userAge, item.userSex, item.addTime);
}
//3.2 查询delflag 为1 的所有用户信息,先按照时间升序排列，再按照年龄降序
Console.WriteLine("---------------3.2 查询delflag 为1 的所有用户信息,先按照时间升序排列，再按照年龄降序----------------------");
string sql2 = @"select * from Sys_UserInfor where delFlag =1 order by addTime,userAge desc";
List<Sys_UserInfor> sUserList2 = ExecuteQuery<Sys_UserInfor>(sql2);
foreach (var item in sUserList2)
{
Console.WriteLine("用户名：{0},用户账号：{1},用户年龄：{2},用户性别：{3},创建时间：{4}", item.userName, item.userAccount, item.userAge, item.userSex, item.addTime);
}
}
#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中没有这种用法
//4.查询账号中含有admin的所有用户的用户昵称、账号、和登录信息
//4.1 内连接的写法1
Console.WriteLine("---------------04-多表关联查询--------------------");
Console.WriteLine("---------------4.1 内连接的写法1--------------------");
string sql1 = @"select a.userName,a.userAccount,b.loginCity,b.loginIp,b.loginTime
from Sys_UserInfor a,LoginRecords b
where a.id =b.userId";
List<model2> uList1 = ExecuteQuery<model2>(sql1);
foreach (var item in uList1)
{
Console.WriteLine("姓名：{0}，账号：{1}，登录城市：{2}，登录IP：{3}，登录时间：{4}", item.userName, item.userAccount, item.loginCity, item.loginIp, item.loginTime);
}
//4.2 内连接的写法2
Console.WriteLine("---------------4.2 内连接的写法2 --------------------");
string sql2 = @"select a.userName,a.userAccount,b.loginCity,b.loginIp,b.loginTime
from Sys_UserInfor a inner join LoginRecords b on a.id =b.userId"; List<model2> uList2 = ExecuteQuery<model2>(sql2);
foreach (var item in uList2)
{
Console.WriteLine("姓名：{0}，账号：{1}，登录城市：{2}，登录IP：{3}，登录时间：{4}", item.userName, item.userAccount, item.loginCity, item.loginIp, item.loginTime);
} //4.3 查询所有用户的登录信息（左外连接的方式）
Console.WriteLine("-----------------------4.3 查询所有用户的登录信息（左外连接的方式）----------------------------");
string sql3 = @"select a.userName,a.userAccount,b.loginCity,b.loginIp,b.loginTime
from Sys_UserInfor a left join LoginRecords b on a.id =b.userId
";
List<model2> uList3 = ExecuteQuery<model2>(sql3);
foreach (var item in uList3)
{
Console.WriteLine("姓名：{0}，账号：{1}，登录城市：{2}，登录IP：{3}，登录时间：{4}", item.userName, item.userAccount, item.loginCity, item.loginIp, item.loginTime);
} //4.4 查询所有用户的登录信息（右外连接的方式）
Console.WriteLine("-----------------------4.4 查询所有用户的登录信息（右外连接的方式）----------------------------");
string sql4 = @"select a.userName,a.userAccount,b.loginCity,b.loginIp,b.loginTime
from Sys_UserInfor a right join LoginRecords b on a.id =b.userId
";
List<model2> uList4 = ExecuteQuery<model2>(sql4);
foreach (var item in uList4)
{
Console.WriteLine("姓名：{0}，账号：{1}，登录城市：{2}，登录IP：{3}，登录时间：{4}", item.userName, item.userAccount, item.loginCity, item.loginIp, item.loginTime);
}
//4.5 查询所有用户的登录信息（全连接）
Console.WriteLine("-----------------------4.5 查询所有用户的登录信息（全连接）----------------------------");
string sql5 = @"select a.userName,a.userAccount,b.loginCity,b.loginIp,b.loginTime
from Sys_UserInfor a full join LoginRecords b on a.id =b.userId
";
List<model2> uList5 = ExecuteQuery<model2>(sql5);
foreach (var item in uList5)
{
Console.WriteLine("姓名：{0}，账号：{1}，登录城市：{2}，登录IP：{3}，登录时间：{4}", item.userName, item.userAccount, item.loginCity, item.loginIp, item.loginTime);
}
运行结果：
5. group By用法
group By分组(SQL中的group by分组和linq、lambda完全不同。特别注意:select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中) 常见的聚合函数有：sum avg min max
#region 05-group By分组
{
//5. Group By分组(SQL中的group by分组和linq、lambda完全不同,
/*特别注意:select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中)
常见的聚合函数有：sum avg min max
*/ //5.1 根据用户的性别进行分类，并且统计不同性别的年龄总和
Console.WriteLine("-------------------- 5. GroupBy分组------------------------");
Console.WriteLine("-------------------- 5.1 根据用户的性别进行分类，并且统计不同性别的年龄总和------------------------");
string sql1 = @"select userSex,SUM(userAge) as TotalAges
from Sys_UserInfor
group by userSex";
List<model3> sUserListGroup1 = ExecuteQuery<model3>(sql1);
foreach (var item in sUserListGroup1)
{
Console.WriteLine("性别：{0},年龄总和：{1}", item.userSex, item.TotalAges);
} //5.2 根据用户性别进行分类，并且统计不同性别年龄的最大值
Console.WriteLine("-------------5.2 根据用户性别进行分类，并且统计不同性别年龄的最大值-------------------");
string sql2 = @"select userSex,MAX(userAge) as TotalAges
from Sys_UserInfor
group by userSex";
List<model3> sUserListGroup2 = ExecuteQuery<model3>(sql2);
foreach (var item in sUserListGroup2)
{
Console.WriteLine("性别：{0},年龄最大值：{1}", item.userSex, item.TotalAges);
}
}
#endregion
6. 分页用法