## 查詢操作 查找是數據庫操作中一個非常重要的技術。查詢一般就是使用filter、exclude以及get三個方法來實現。我們可以在調用這些方法的時候傳遞不同的參數來實現查詢需求。在ORM層面，這些查詢條件都是使用field+__+condition的方式來使用的。 ### 查詢條件 **exact** 使用精確的=進行查找。如果提供的是一個None，那么在SQL層面就是被解釋為NULL ``` article = Article.objects.get(id__exact=14) article = Article.objects.get(id__exact=None) 以上的兩個查找在翻譯為SQL語句 select * from article where id=14; select * from article where id IS NULL; article.query,可以得到Django執行的SQL語句。但是只能作用于QuerySet對象上。 ``` **iexact** 使用like進行查找。 ``` article = Article.objects.filter(title__iexact='hello world') 那么以上的查詢就等價于以下的SQL語句： select * from article where title like 'hello world'; ``` 注意上面這個sql語句，因為在MySQL中，沒有一個叫做ilike的。所以exact和iexact的區別實際上就是LIKE和=的區別，在大部分collation=utf8_general_ci情況下都是一樣的（collation是用來對字符串比較的）。 **contains** 大小寫敏感，判斷某個字段是否包含了某個數據。 ``` articles = Article.objects.filter(title__contains='hello') 在翻譯成SQL語句為如下： select * where title like binary '%hello%'; ``` 要注意的是，在使用contains的時候，翻譯成的sql語句左右兩邊是有百分號的，意味著使用的是模糊查詢。而exact翻譯成sql語句左右兩邊是沒有百分號的，意味著使用的是精確的查詢。 **icontains** 大小寫不敏感的匹配查詢。 ``` articles = Article.objects.filter(title__icontains='hello') 在翻譯成SQL語句為如下： select * where title like '%hello%'; ``` **in** 提取那些給定的field的值是否在給定的容器中。容器可以為list、tuple或者任何一個可以迭代的對象，包括QuerySet對象。 ``` articles = Article.objects.filter(id__in=[1,2,3]) 以上代碼在翻譯成SQL語句為如下： select *from articles where id in (1,2,3) 當然也可以傳遞一個QuerySet對象進去。 # 查找標題為hello的文章分類 articles = Article.objects.filter(title__icontains="hello") category = Category.objects.filter(article__in=articles) # 查找文章ID為1，2，3的文章分類 category = Category.objects.filter(article__id__in=[1,2,3]) ``` ### 根據關聯的表進行查詢 ``` 想要獲取文章標題中包含"hello"的所有的分類。那么可以通過以下代碼來實現： categories = Category.object.filter(article__title__contains("hello")) ``` **比較運算** ### gt ``` 某個field的值要大于給定的值。 # 將所有id大于4的文章全部都找出來。 articles = Article.objects.filter(id__gt=4) 將翻譯成以下SQL語句： select * from articles where id > 4; ``` ### gte 類似于gt，是大于等于。 ### lt 類似于gt是小于。 ### lte 類似于lt，是小于等于。 **range** 判斷某個field的值是否在給定的區間中。 ``` start_date = datetime(year=2019, month=12, day=1,hour=10,minute=0,second=0) end_date = datetime(year=2019, month=12, day=30,hour=10,minute=0,second=0) date_range = Common.objects.filter(test_date__range=(start_date,end_date)) 以上代碼的意思是提取所有發布時間在2019/1/1到2019/12/12之間的文章。 將翻譯成以下的SQL語句： SELECT `user_common`.`id`, `user_common`.`content`, `user_common`.`pid`, `user_common`.`test_date` FROM `user_common` WHERE `user_common`.`test_date` BETWEEN 2019-12-01 02:00:00 AND 2019-12-30 02:00:00。 ``` **date** 針對某些date或者datetime類型的字段。可以指定date的范圍。并且這個時間過濾，還可以使用鏈式調用。 ``` date_test = Common.objects.filter(test_date__date=datetime(year=2018,month=12,day=19)) print(date_test.query) print(date_test) 翻譯成SQL語句: SELECT `user_common`.`id`, `user_common`.`content`, `user_common`.`pid`, `user_common`.`test_date` FROM `user_common` WHERE DATE(`user_common`.`test_date`) = 2018-12-19 ``` **year** 根據年份進行查找。 ``` articles = Article.objects.filter(pub_date__year=2018) articles = Article.objects.filter(pub_date__year__gte=2017) 以上的代碼在翻譯成SQL語句為如下： select ... where pub_date between '2018-01-01' and '2018-12-31'; select ... where pub_date >= '2017-01-01'; ``` **time** 根據時間進行查找。示例代碼如下： ``` articles = Article.objects.filter(pub_date__time=time(hour=15,minute=21,second=10)) 以上的代碼是獲取每一天中15點21分10秒發表的所有文章。 # 查詢10秒到11秒之間的 start_date = time(hour=17,minute=21,second=10) end_date = time(hour=17,minute=21,second=11) date_test = Common.objects.filter(test_date__time__range = (start_date,end_date)) ``` ## 聚合函數 如果你用原生SQL，則可以使用聚合函數來提取數據。比如提取某個商品銷售的數量，那么可以使用Count，如果想要知道商品銷售的平均價格，那么可以使用Avg。 聚合函數是通過aggregate方法來實現的。 ``` from django.db import models class Author(models.Model): """作者模型""" name = models.CharField(max_length=100) age = models.IntegerField() email = models.EmailField() class Meta: db_table = 'author' class Publisher(models.Model): """出版社模型""" name = models.CharField(max_length=300) class Meta: db_table = 'publisher' class Book(models.Model): """圖書模型""" name = models.CharField(max_length=300) pages = models.IntegerField() price = models.FloatField() rating = models.FloatField() author = models.ForeignKey(Author,on_delete=models.CASCADE) publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE) class Meta: db_table = 'book' class BookOrder(models.Model): """圖書訂單模型""" book = models.ForeignKey("Book",on_delete=models.CASCADE) price = models.FloatField() class Meta: db_table = 'book_order' ``` ### 聚合函數的使用 1.Avg：求平均值。比如想要獲取所有圖書的價格平均值。那么可以使用以下代碼實現。 ``` from django.db.models import Avg from django.db import connection result = Book.objects.aggregate(Avg('price')) print(connection.queries) # 打印SQL語句 print(result) 以上的打印結果是： {"price__avg":23.0} 其中price__avg的結構是根據field__avg規則構成的。如果想要修改默認的名字，那么可以將Avg賦值給一個關鍵字參數。 result = Book.objects.aggregate(my_avg=Avg('price')) print(result) 那么以上的結果打印為： {"my_avg":23} ``` 2.Count：獲取指定的對象的個數。 ``` from django.db.models import Count result = Book.objects.aggregate(book_num=Count('id')) 以上的result將返回Book表中總共有多少本圖書。 Count類中，還有另外一個參數叫做distinct，默認是等于False，如果是等于True，那么將去掉那些重復的值。 比如要獲取作者表中所有的不重復的郵箱總共有多少個。 from djang.db.models import Count result = Author.objects.aggregate(count=Count('email',distinct=True)) # 統計每本圖書的銷量 result = Book.objects.annotate(book_nums=Count("bookorder")) for book in result: print("%s/%s"%(book.name,book.book_nums)) ``` 3.Max和Min：獲取指定對象的最大值和最小值。比如想要獲取Author表中，最大的年齡和最小的年齡分別是多少。 ``` from django.db.models import Max,Min result = Author.objects.aggregate(Max('age'),Min('age')) 如果最大的年齡是88,最小的年齡是18。那么以上的result將為： {"age__max":88,"age__min":18} # 統計每本售賣圖書的最大值和最小值 request = Book.objects.annotate(max=Max("bookorder__price"),min=Min("bookorder__price")) print(request) ``` 4.Sum：求指定對象的總和。比如要求圖書的銷售總額。 ``` from djang.db.models import Sum result = Book.objects.aggregate(total=Sum("price")) # 每一本圖書的銷售總額 result = Book.objects.annotate(total=Sum("bookorder__price")) # 統計2019年，銷售總額 result = BookOrder.objects.filter(create_time__year=2019).aggregate(total=Sum("price")) ``` ### aggregate和annotate的區別 1·aggregate：返回使用聚合函數后的字段和值。 2.annotate：在原來模型字段的基礎之上添加一個使用了聚合函數的字段，并且在使用聚合函數的時候，會使用當前這個模型的主鍵進行分組（group by）。 ``` # 求每一本圖書銷售的平均價格 result = Book.objects.aggregate(avg=Avg("bookorder__price")) print(result) print(connection.queries) result = Book.objects.annotate(avg=Avg("bookorder__price")) print(result) ``` ## F表達式和Q表達式 ### F表達式 F表達式是用來優化ORM操作數據庫的。比如我們要將公司所有員工的薪水都增加1000元，如果按照正常的流程，應該是先從數據庫中提取所有的員工工資到Python內存中，然后使用Python代碼在員工工資的基礎之上增加1000元，最后再保存到數據庫中。這里面涉及的流程就是，首先從數據庫中提取數據到Python內存中，然后在Python內存中做完運算，之后再保存到數據庫中。 ``` employees = Employee.objects.all() for employee in employees: employee.salary += 1000 employee.save() ``` 而我們的F表達式就可以優化這個流程，他可以不需要先把數據從數據庫中提取出來，計算完成后再保存回去，他可以直接執行SQL語句，就將員工的工資增加1000元。 ``` from djang.db.models import F Employee.objects.update(salary=F("salary")+1000) F表達式并不會馬上從數據庫中獲取數據，而是在生成SQL語句的時候，動態的獲取傳給F表達式的值。 ``` 比如如果想要獲取作者中，name和email相同的作者數據。如果不使用F表達式。 ``` authors = Author.objects.all() for author in authors: if author.name == author.email: print(author) 如果使用F表達式，那么一行代碼就可以搞定。示例代碼如下： from django.db.models import F authors = Author.objects.filter(name=F("email")) ``` ### Q表達式 如果想要實現所有價格高于100元，并且評分達到9.0以上評分的圖書。 ``` books = Book.objects.filter(price__gte=100,rating__gte=9) ``` 以上這個案例是一個并集查詢，可以簡單的通過傳遞多個條件進去來實現。 但是如果想要實現一些復雜的查詢語句，比如要查詢所有價格低于10元，或者是評分低于9分的圖書。那就沒有辦法通過傳遞多個條件進去實現了。這時候就需要使用Q表達式來實現了。 ``` from django.db.models import Q books = Book.objects.filter(Q(price__lte=10) | Q(rating__lte=9)) ``` 以上是進行或運算，當然還可以進行其他的運算，比如有&和~（非）等。一些用Q表達式的例子如下： ``` from django.db.models import Q # 獲取id等于3的圖書 books = Book.objects.filter(Q(id=3)) # 獲取id等于3，或者名字中包含文字"傳"的圖書 books = Book.objects.filter(Q(id=3)|Q(name__contains="傳")) # 獲取價格大于100，并且書名中包含"傳"的圖書 books = Book.objects.filter(Q(price__gte=100)&Q(name__contains="傳")) # 獲取書名包含"傳"，但是id不等于3的圖書 books = Book.objects.filter(Q(name__contains='傳') & ~Q(id=3)) ```