MySql-基本查询

基本查询

根据 Id 查询出一个 User 对象:

string connString = "Your connection string";
MySqlContext context = new MySqlContext(new MySqlConnectionFactory(connString));
IQuery<User> q = context.Query<User>();

q.Where(a => a.Id == 1).FirstOrDefault();
/*
 *生成的 sql:
 * SELECT `Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`,`Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime` 
   FROM `Users` AS `Users` 
   WHERE `Users`.`Id` = 1 
   LIMIT 0,1
 */

like 查询:

//注:在lambda里使用常量不会参数化处理,使用时切记要将值以变量的形式传进lambda
q.Where(a => a.Name.Contains("so") || a.Name.StartsWith("s") || a.Name.EndsWith("o")).ToList();
/*
 * SELECT 
 *      `Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime`,`Users`.`Id` AS `Id`,`Users`.`Name` AS `Name` 
 * FROM `Users` AS `Users` 
 * WHERE (`Users`.`Name` LIKE CONCAT('%',N'so','%') OR `Users`.`Name` LIKE CONCAT(N's','%') OR `Users`.`Name` LIKE CONCAT('%',N'o'))
 */

in 查询:

List<int> ids = new List<int>() { 1, 2, 3 };
q.Where(a => ids.Contains(a.Id)).ToList();
/*
 * Int32 ?P_0 = 1;
   Int32 ?P_1 = 2;
   Int32 ?P_2 = 3;
   SELECT `Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`,`Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime` 
   FROM `Users` AS `Users` 
   WHERE `Users`.`Id` IN (?P_0,?P_1,?P_2)
 */

/* in 子查询 */
/* IQuery<T>.ToList().Contains() 方法组合就会生成 in 子查询 sql 语句 */
users = q.Where(a => context.Query<City>().Select(c => c.Id).ToList().Contains((int)a.CityId)).ToList(); 
/*
 * SELECT 
 *      `Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime`,`Users`.`Id` AS `Id`,`Users`.`Name` AS `Name` 
 * FROM `Users` AS `Users` 
 * WHERE `Users`.`CityId` IN (SELECT `City`.`Id` AS `C` FROM `City` AS `City`)
 */

可以选取指定的字段,返回一个匿名类型:

q.Where(a => a.Id == 1).Select(a => new { a.Id, a.Name }).FirstOrDefault();
/*
 * 只会生成包含 Id 和 Name 两个字段的 sql 语句:
 * SELECT `Users`.`Id` AS `Id`,`Users`.`Name` AS `Name` 
   FROM `Users` AS `Users` 
   WHERE `Users`.`Id` = 1 
   LIMIT 0,1
 */

排序:

q.Where(a => a.Id > 0).OrderBy(a => a.Age).ThenBy(a => a.Id).ToList();
/*
 * SELECT `Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`,`Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime` 
   FROM `Users` AS `Users` 
   WHERE `Users`.`Id` > 0 
   ORDER BY `Users`.`Age` ASC,`Id` ASC
 */

分页:

q.Where(a => a.Id > 0).OrderBy(a => a.Age).TakePage(1, 20).ToList();
/*
 * SELECT `Users`.`Id` AS `Id`,`Users`.`Name` AS `Name`,`Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime`
   FROM `Users` AS `Users` 
   WHERE `Users`.`Id` > 0 
   ORDER BY `Users`.`Age` ASC 
   LIMIT 0,20
 */

distinct 查询:

q.Select(a => new { a.Name }).Distinct().ToList();
/*
 * SELECT DISTINCT `Users`.`Name` AS `Name` FROM `Users` AS `Users`
 */

exists 查询:

/* IQuery<T>.Any() 方法组合就会生成 exists 子查询 sql 语句 */
users = context.Query<User>().Where(a => context.Query<City>().Where(c => c.Id == a.CityId).Any()).ToList();
/*
 * SELECT 
 *      `Users`.`Gender` AS `Gender`,`Users`.`Age` AS `Age`,`Users`.`CityId` AS `CityId`,`Users`.`OpTime` AS `OpTime`,`Users`.`Id` AS `Id`,`Users`.`Name` AS `Name` 
 * FROM `Users` AS `Users` 
 * WHERE Exists (SELECT N'1' AS `C` FROM `City` AS `City` WHERE `City`.`Id` = `Users`.`CityId`)
 */

更多用法请查看使用进阶