一、基于物件的跨表查詢（正向反向查詢）
# 跨表查詢有兩種方式 -基于物件的跨表查詢：子查詢 -基于雙下劃線的跨表查詢：關聯查詢，連表查詢 # 基于物件的跨表查詢 -查詢主鍵為1的書籍的出版社所在的城市 # 基于物件的跨表查詢（子查詢） # 一對多 # 查詢主鍵為1的書籍的出版社所在的城市 # book=models.Book.objects.get(id=1) # 第一次查詢 # # book=models.Book.objects.filter(id=1).first() # publish=book.publish # 內部又執行了一次查詢，根據publish_id查詢publish # print(publish.addr) # 北京出版社出版的所有書籍 # publish=models.Publish.objects.get(name='北京出版社') # 第一次查詢了出版社 # books=publish.book_set.all() # 表名小寫_set # 第二次，根據出版社id，查詢所有書 # print(books) # 正向查詢：book表內有publish欄位 直接物件.欄位名 # 反向查詢：publish表內沒有book欄位，出版社物件.Book小寫_set.all() ### 一對一 # 查詢所有住址在山東的作者的姓名 # 反向查詢：author_detail沒有author欄位，author_detail.表名小寫 # author_detail=models.AuthorDetail.objects.filter(addr__contains='山東').first() # # 反向 # print(author_detail.author.name) # 查詢egon作者的地址 # 正向 # author=models.Author.objects.get(name='egon') # print(author.author_detail.addr) # 多對多關系查詢 #金梅所有作者的名字以及手機號 # book=models.Book.objects.get(name='金梅') # # 正向 # authors=book.authors.all() # for author in authors: # print(author.name) # print(author.author_detail.phone) # 反向 查詢egon出過的所有書籍的名字 # egon=models.Author.objects.get(name='egon') # books=egon.book_set.all() # for book in books: # print(book.name)
二、基于雙下劃線的跨表查詢
# 連表查詢 # 基于物件的跨表查詢，先查物件，通過物件再去查另一個物件（正向：欄位名，反向：表名小寫/表名小寫_set.all()） # 地址為山東的作者寫的所有書 # author_detail=models.AuthorDetail.objects.get(addr='山東') # author=author_detail.author # books=author.book_set.all() # print(books[0].name) # （作業）地址為山東的作者寫的所有書的出版社名字 ### 基于雙下劃線的跨表查之 一對多 # 正向：欄位名 # 反向:表名小寫 # filter，values,values_list(寫 __ 跨表) # 練習: 查詢北京出版社出版過的所有書籍的名字與價格(一對多) # SELECT `app01_book`.`name`, `app01_book`.`price` FROM `app01_publish` LEFT OUTER JOIN `app01_book` ON (`app01_publish`.`id` = `app01_book`.`publish_id`) WHERE `app01_publish`.`name` = '北京出版社' ; # res=models.Publish.objects.filter(name='北京出版社').values('book__name','book__price') # print(res) #SELECT `app01_book`.`name`, `app01_book`.`price` FROM `app01_book` INNER JOIN `app01_publish` ON (`app01_book`.`publish_id` = `app01_publish`.`id`) WHERE `app01_publish`.`name` = '北京出版社'; # res=models.Book.objects.filter(publish__name='北京出版社').values('name','price') # print(res) ## 多對多 # 練習: 查詢egon出過的所有書籍的名字,價格(多對多) #反向 # res=models.Author.objects.filter(name='egon').values('book__name','book__price') # print(res) # 正向 # res=models.Book.objects.filter(authors__name='egon').values('name','price') # print(res) #查詢egon的手機號 # res=models.Author.objects.filter(name='egon').values('author_detail__phone') # print(res) # res=models.AuthorDetail.objects.filter(author__name='egon').values('phone') # print(res)
三、進階連續跨表查詢
# 連續跨表 #查詢北京出版社出版過的所有書籍的名字以及作者的姓名 # res=models.Publish.objects.filter(name='北京出版社').values('book__name','book__authors__name') # print(res) # res=models.Book.objects.filter(publish__name='北京出版社').values('name','authors__name') # print(res) # res=models.Author.objects.filter(book__publish__name='北京出版社').values('book__name','name') # print(res) # 手機號以189開頭的作者出版過的所有 書籍名稱 以及 出版社名稱 # res=models.AuthorDetail.objects.filter(phone__startswith='189').values('author__book__name','author__book__publish__name') # print(res) # SELECT `app01_book`.`name`, `app01_publish`.`name` FROM `app01_author` INNER JOIN `app01_authordetail` ON (`app01_author`.`author_detail_id` = `app01_authordetail`.`id`) LEFT OUTER JOIN `app01_book_authors` ON (`app01_author`.`id` = `app01_book_authors`.`author_id`) LEFT OUTER JOIN `app01_book` ON (`app01_book_authors`.`book_id` = `app01_book`.`id`) LEFT OUTER JOIN `app01_publish` ON (`app01_book`.`publish_id` = `app01_publish`.`id`) WHERE `app01_authordetail`.`phone` LIKE '189%' ; res=models.Author.objects.filter(author_detail__phone__startswith='189').values('book__name','book__publish__name') print(res)
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/173198.html
標籤：架構設計