SqlSugar 1.基础查询

用到的表结构、数据、实体类

方便测试Demo

Student
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `StudentId` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `SchoolId` int(11) NOT NULL,
  PRIMARY KEY (`StudentId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `student` VALUES (1, 'Zzr', 1);
INSERT INTO `student` VALUES (2, 'Ls', 2);
INSERT INTO `student` VALUES (3, 'Wem', 3);
INSERT INTO `student` VALUES (4, 'Hpf', 4);
INSERT INTO `student` VALUES (5, 'Zzr', 5);
INSERT INTO `student` VALUES (6, 'hpf', 2);
INSERT INTO `student` VALUES (7, 'hpf', 2);
INSERT INTO `student` VALUES (9, 'hpf', 2);
INSERT INTO `student` VALUES (11, 'qwe', 0);
INSERT INTO `student` VALUES (12, 'qwe', 0);
public class Student
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }

        [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一 SchoolId是Student类里面的
        public School School { get; set; } //不能赋值
    }
1.查所有
List<Student> list = 
db.Queryable<Student>().ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`
2.按条件查询
List<Student> list = db.Queryable<Student>().Where(it=> it.Id.Equals("1")).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `student`  WHERE  (`Id` = 1)
3.多条件查询
List<Student> list1 = db.Queryable<Student().Where(it => it.SchoolId > 5 && it.Name == "G").ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE (( `SchoolId` > 5 ) AND ( `StudentName` = 'G' ))


List<Student> list2 = db.Queryable<Student>().Where(it => it.SchoolId > 5).Where(it => it.Name == "G").ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE ( `SchoolId` > 5 )  AND ( `StudentName` = 'G' )
4.动态OR查询
var exp = Expressionable.Create<Student>();
exp.OrIF(1==1,it=>it.Id.Equals("3"));
exp.Or(it => it.Name == "E");
List<Student> list = db.Queryable<Student().Where(exp.ToExpression()).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE ((`Id` = 3) OR( `StudentName` = 'E' ))
5.模糊查询
List<Student> list = db.Queryable<Student>().Where(it=>it.Id.Contains("4")).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE(`Id` like concat('%', 4, '%'))
6.根据主键查询
Student model1 = db.Queryable<Student>().InSingle(5); //通过主键查询

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE `Id` = '5'


Student model2 = db.Queryable<Student>().Single(it => it.Id == 5); 

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE( `Id` = '5')
7.查询第一条

First == Linq中的 FirstOrDefault 没有数据返回Null

 Student model = db.Queryable<Student>().First(it => it.Id == 1);
 
//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`   WHERE( `Id`= '8')  ORDER BY NOW()  LIMIT 0,1
8.查前几条
List<Student> list = db.Queryable<Student>().Take(3).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  LIMIT 0,3
9.数据行数
int count = db.Queryable<Student>().Count();

//SELECT Count(*) FROM `Student`
10.设置新表明
 List<Student> list1 = db.Queryable<Student>().AS("SS").ToList();
 
//SELECT `Id`,`SchoolId`,`StudentName` FROM `SS`

List<Student> list2 = db.Queryable<Student>().AS("dbo.Student").ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `dbo`.`Student`
11.是否存在记录
 bool b = db.Queryable<Student>().Any(it => it.Id == 9);
 
//SELECT 1 FROM `Student`  WHERE( `Id` = '9')
12.IN查询,IN的使用
int[] nums = new int[3] { 1, 3, 5 };
List<Student> list = db.Queryable<Student>().Where(it => nums.Contains(it.Id)).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE(`Id` IN(1, 3, 5))


string[] nums = new string[3] { "A", "F", "G" };
Expressionable<Student> exp = new Expressionable<Student>();
foreach (var num in nums)
{
    exp.Or(it => it.Name.Contains(num));
}
List<Student> list = db.Queryable<Student>().Where(exp.ToExpression()).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE (((`StudentName` like concat('%','A','%')) OR (`StudentName` like concat('%','F','%')) ) OR  (`StudentName` like concat('%','G','%')) )
13.NOT IN
int[] nums = new int[3] { 1, 3, 5 };
List<Student> list = db.Queryable<Student>().Where(it => !nums.Contains(it.Id)).ToList();

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE NOT (`Id` IN (1,3,5))
14.简单排序
 List<Student> list = db.Queryable<Student>().OrderBy(it => it.Id, OrderByType.Desc).ToList();
 
//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student` ORDER BY `Id`DESC
15.查询一列
List<string> list = db.Queryable<Student>().Select(it => it.Name).ToList();

//SELECT `StudentName` FROM `Student`
16.查询单条
Student cs = db.Queryable<Student>().Single(it => it.Id == 10);

//SELECT `Id`,`SchoolId`,`StudentName` FROM `Student`  WHERE( `Id` = '10')
17.获取最大值
int cs = db.Queryable<Student>().Max(it => it.Id);

//SELECT MAX(`Id`) FROM `Student`
18.获取最小值
 int cs = db.Queryable<Student>().Min(it => it.Id);
 
//SELECT MIN(`Id`) FROM `Student`
19.求和
int cs = db.Queryable<Student>().Sum(it => it.Id);

//SELECT SUM(`Id`) FROM `Student`
20.查询过滤某一个字段

仅支持单表查询

List<Student> list = db.Queryable<Student>().IgnoreColumns(it=>it.Name).ToList();

//SELECT `Id`,`SchoolId` FROM `Student`

📄文档参考:基础查询

  • 5
    点赞
  • 37
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

DotNeter-Hpf

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值