mysql 单表查询

单表查询

所有的继承自models.Model的类 都会有一个叫objects（管理器）

• 查询一个 （有且只能有一个）（少于一个或者多余一个都报错）
  返回值Movie对象

    	Movie.objects.get(mid=147)
    	
    	SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` WHERE `movie`.`mid` = 147
  

• 获得第一个

    	Movie.objects.first()	
  
    	SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` ORDER BY `movie`.`mid` ASC LIMIT 1
  

• 获得最后一个

    	Movie.objects.last()
  
    	SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` ORDER BY `movie`.`mid` DESC LIMIT 1
  

• 获得记录的总数

    	Movie.objects.count()
  
    	SELECT COUNT(*) AS `__count` FROM `movie
  

查询多个对象

• 获得所有的记录

    	Movie.objects.all()
    	# 在diango的1.11.6中默认只是获得21个，
    	# 懒加载
    	SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM 			`movie` LIMIT 21
  

• 切片 （不支持负数索引）
  Movie.objects.all()[20:40]
  底层直接使用了limit字句，可以自动的分页
  django的orm性能非常强大，能节省很多工作
  SELECT movie.mid, movie.mname, movie.mdesc, movie.mimg, movie.mlink FROM movie LIMIT 20 OFFSET 20

• 过滤
  集和（游标，结果集，查询集）
  Movie.objects.filter(mname=‘麻辣学院’) （麻辣学院为要查询的名称，下文爱情同理）

    SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` WHERE `movie`.`mname` = '麻辣学院' LIMIT 21
  

• 模糊查询

  • like
  • %(多个字符) _(一个字符)
  • SELECT * from movie WHERE mname LIKE ‘%爱情_’

• 查询爱情结尾的

    	SELECT * from movie WHERE mname LIKE '%爱情'
  
    	Movie.objects.filter(mname__endswith='爱情')
  
    	SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM 			`movie` WHERE `movie`.`mname` LIKE BINARY '%爱情' LIMIT 21
  

• 查询爱情开头的

    	SELECT * from movie WHERE mname LIKE '爱情%'
    	
    	Movie.objects.filter(mname__startswith='爱情')
    	
    	SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM 			`movie` WHERE `movie`.`mname` LIKE BINARY '爱情%' LIMIT 21
  

• 包含爱情的

    SELECT * from movie where mname like '%爱情%'
  
    Movie.objects.filter(mname__contains='爱情')
  
  
    SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` 		WHERE `movie`.`mname` LIKE BINARY '%爱情%' LIMIT 21
  

• 完全相等

    Movie.objects.filter(mname__exact='麻辣学院')
    Movie.objects.filter(mname='麻辣学院')
  
    SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` 		WHERE `movie`.`mname` = '麻辣学院' LIMIT 21
  

• 忽略大小写

    Movie.objects.filter(mname__istartswith='h')
  

• 查询某个字段是否为null

    Movie.objects.filter(mname__isnull=True)
  

• 多条件查询

    Movie.objects.filter(mname__contains='爱情',mid=147)
    
    	and
  
    	
    SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` WHERE (`movie`.`mname` LIKE BINARY '%爱情%' AND `movie`.`mid` = 147) LIMIT 21
  
    Movie.objects.filter(mname__contains='爱情').filter(mid=147)
  

• 部分查询

    Movie.objects.values('mname','mid').filter(mname__contains='爱情')
  

• 排除一部分

  Movie.objects.filter(mname__contains=‘爱情’).exclude(mname__startswith=‘爱情’)

    SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` 		WHERE (`movie`.`mname` LIKE BINARY '%爱情%' AND NOT (`movie`.`mname` LIKE BINARY '爱情%')) LIMIT 21
  

• 排序

    Movie.objects.order_by('mid')
  
    SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` 		ORDER BY `movie`.`mid` ASC LIMIT 21
  
  
    Movie.objects.order_by('-mid')
  
    SELECT `movie`.`mid`, `movie`.`mname`, `movie`.`mdesc`, `movie`.`mimg`, `movie`.`mlink` FROM `movie` 		ORDER BY `movie`.`mid` DESC LIMIT 21
  

日期查询

• 查询大于某个时间的记录

    Post.objects.filter(created__gt='2017-10-20')
  
    SELECT `post_post`.`id`, `post_post`.`title`, `post_post`.`created` FROM `post_post` WHERE 			`post_post`.`created` > '2017-10-20' LIMIT 21
  

• 查询最近一个月的帖子（查询最近不活跃的用户）

   def get_recent_month_posts():
       import datetime
       current = datetime.date.today()-datetime.timedelta(days=30)
       current = str(current)
       return Post.objects.filter(created__gt=current)
  

• 查询十月二十号–十一月二十号的所有的记录

  • 错误的

    Post.objects.filter(created__in=('2017-10-20','2017-11-20'))
    指的是这两个时间中的一个
    

  • 正确 （sql语句）

     Post.objects.filter(created__range=('2017-10-20','2017-11-20'))
    
     		
     SELECT `post_post`.`id`, `post_post`.`title`, `post_post`.`created` FROM `post_post` WHERE 			`post_post`.`created` BETWEEN '2017-10-20' AND '2017-11-20' LIMIT 21