ORM 进阶操作
ORM多表操作
一、创建模型
作者模型：一个作者有姓名和年龄。
作者详细模型：把作者的详情放到详情表，包含生日，手机号，家庭住址等信息；作者详情模型和作者模型之间是一对一的关系。
出版商模型：出版商有名称，所在城市以及邮箱。
书籍模型： 书籍有书名和出版日期，一本书可能会有多个作者，一个作者也可以写多本书，所以作者和书籍的关系就是多对多的关联关系；一本书只应该由一个出版商出版，所以出版商和书籍是一对多关联关系。
from django.db import models class Author(models.Model): id = models.AutoField(primary_key=True) name = models.CharField(max_length=32) age = models.IntegerField() # on_delete=models.CASCADE 当删除关联表中的数据时，与之关联也删除 authorDetail = models.OneToOneField(to="AuthorDetail", on_delete=models.CASCADE) class AuthorDetail(models.Model): id = models.AutoField(primary_key=True) birthday = models.DateField() telephone = models.BigIntegerField() address = models.CharField(max_length=64) class Publisher(models.Model): id = models.AutoField(primary_key=True) name = models.CharField(max_length=32) city = models.CharField(max_length=32) email = models.EmailField() class Book(models.Model): id = models.AutoField(primary_key=True) title = models.CharField(max_length=32) publishDate = models.DateField() price = models.DecimalField(max_digits=5, decimal_places=2) # 与Publish建立一对多的关系，外键字段一般建立在多的一方 publisher = models.ForeignKey(to="Publisher", to_field="id", on_delete=models.CASCADE) # 与Author表建立多对多的关系，ManyToManyField通常设置在正向查询多的那一边，自动创建第三张表 authors = models.ManyToManyField(to="Author")
二、插入测试数据
从MySQL导出的数据脚本：数据库名为orm
/*
Navicat MySQL Data Transfer
Source Server : MyDatabase
Source Server Version : 50723
Source Host : localhost:3306
Source Database : orm
Target Server Type : MYSQL
Target Server Version : 50723
File Encoding : 65001
Date: 2018-11-21 17:00:14
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for app01_author
-- ----------------------------
DROP TABLE IF EXISTS `app01_author`;
CREATE TABLE `app01_author` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`age` int(11) NOT NULL,
`authorDetail_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `authorDetail_id` (`authorDetail_id`),
CONSTRAINT `app01_author_authorDetail_id_a86eedd2_fk_app01_authordetail_id` FOREIGN KEY (`authorDetail_id`) REFERENCES `app01_authordetail` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of app01_author
-- ----------------------------
INSERT INTO `app01_author` VALUES ('1', '喜洋洋', '6', '1');
INSERT INTO `app01_author` VALUES ('2', '灰太狼', '8', '2');
-- ----------------------------
-- Table structure for app01_authordetail
-- ----------------------------
DROP TABLE IF EXISTS `app01_authordetail`;
CREATE TABLE `app01_authordetail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`birthday` date NOT NULL,
`telephone` bigint(20) NOT NULL,
`address` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of app01_authordetail
-- ----------------------------
INSERT INTO `app01_authordetail` VALUES ('1', '2012-12-12', '13711111111', '羊村');
INSERT INTO `app01_authordetail` VALUES ('2', '2010-10-10', '13522222222', '狼堡');
-- ----------------------------
-- Table structure for app01_book
-- ----------------------------
DROP TABLE IF EXISTS `app01_book`;
CREATE TABLE `app01_book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(32) NOT NULL,
`publishDate` date NOT NULL,
`price` decimal(5,2) NOT NULL,
`publisher_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `app01_book_publisher_id_e407867a_fk_app01_publisher_id` (`publisher_id`),
CONSTRAINT `app01_book_publisher_id_e407867a_fk_app01_publisher_id` FOREIGN KEY (`publisher_id`) REFERENCES `app01_publisher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of app01_book
-- ----------------------------
INSERT INTO `app01_book` VALUES ('1', '时间中的孩子', '2018-11-01', '58.00', '1');
INSERT INTO `app01_book` VALUES ('2', '遮蔽的天空', '2018-09-05', '49.00', '2');
INSERT INTO `app01_book` VALUES ('3', '凝视太阳', '2018-10-20', '42.00', '1');
-- ----------------------------
-- Table structure for app01_book_authors
-- ----------------------------
DROP TABLE IF EXISTS `app01_book_authors`;
CREATE TABLE `app01_book_authors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`book_id` int(11) NOT NULL,
`author_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `app01_book_authors_book_id_author_id_36f1e11a_uniq` (`book_id`,`author_id`),
KEY `app01_book_authors_author_id_5acae95a_fk_app01_author_id` (`author_id`),
CONSTRAINT `app01_book_authors_author_id_5acae95a_fk_app01_author_id` FOREIGN KEY (`author_id`) REFERENCES `app01_author` (`id`),
CONSTRAINT `app01_book_authors_book_id_19c7077f_fk_app01_book_id` FOREIGN KEY (`book_id`) REFERENCES `app01_book` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of app01_book_authors
-- ----------------------------
INSERT INTO `app01_book_authors` VALUES ('1', '1', '1');
INSERT INTO `app01_book_authors` VALUES ('3', '2', '2');
INSERT INTO `app01_book_authors` VALUES ('2', '3', '1');
-- ----------------------------
-- Table structure for app01_publisher
-- ----------------------------
DROP TABLE IF EXISTS `app01_publisher`;
CREATE TABLE `app01_publisher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`city` varchar(32) NOT NULL,
`email` varchar(254) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of app01_publisher
-- ----------------------------
INSERT INTO `app01_publisher` VALUES ('1', '苹果出版社', '广州', 'gz@qq.com');
INSERT INTO `app01_publisher` VALUES ('2', '香蕉出版社', '深圳', 'sz@qq.com');
-- ----------------------------
-- Table structure for auth_group
-- ----------------------------
DROP TABLE IF EXISTS `auth_group`;
CREATE TABLE `auth_group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(80) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_group
-- ----------------------------
-- ----------------------------
-- Table structure for auth_group_permissions
-- ----------------------------
DROP TABLE IF EXISTS `auth_group_permissions`;
CREATE TABLE `auth_group_permissions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`group_id` int(11) NOT NULL,
`permission_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_group_permissions_group_id_permission_id_0cd325b0_uniq` (`group_id`,`permission_id`),
KEY `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` (`permission_id`),
CONSTRAINT `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),
CONSTRAINT `auth_group_permissions_group_id_b120cbf9_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_group_permissions
-- ----------------------------
-- ----------------------------
-- Table structure for auth_permission
-- ----------------------------
DROP TABLE IF EXISTS `auth_permission`;
CREATE TABLE `auth_permission` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`content_type_id` int(11) NOT NULL,
`codename` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_permission_content_type_id_codename_01ab375a_uniq` (`content_type_id`,`codename`),
CONSTRAINT `auth_permission_content_type_id_2f476e4b_fk_django_co` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_permission
-- ----------------------------
INSERT INTO `auth_permission` VALUES ('1', 'Can add log entry', '1', 'add_logentry');
INSERT INTO `auth_permission` VALUES ('2', 'Can change log entry', '1', 'change_logentry');
INSERT INTO `auth_permission` VALUES ('3', 'Can delete log entry', '1', 'delete_logentry');
INSERT INTO `auth_permission` VALUES ('4', 'Can add permission', '2', 'add_permission');
INSERT INTO `auth_permission` VALUES ('5', 'Can change permission', '2', 'change_permission');
INSERT INTO `auth_permission` VALUES ('6', 'Can delete permission', '2', 'delete_permission');
INSERT INTO `auth_permission` VALUES ('7', 'Can add user', '3', 'add_user');
INSERT INTO `auth_permission` VALUES ('8', 'Can change user', '3', 'change_user');
INSERT INTO `auth_permission` VALUES ('9', 'Can delete user', '3', 'delete_user');
INSERT INTO `auth_permission` VALUES ('10', 'Can add group', '4', 'add_group');
INSERT INTO `auth_permission` VALUES ('11', 'Can change group', '4', 'change_group');
INSERT INTO `auth_permission` VALUES ('12', 'Can delete group', '4', 'delete_group');
INSERT INTO `auth_permission` VALUES ('13', 'Can add content type', '5', 'add_contenttype');
INSERT INTO `auth_permission` VALUES ('14', 'Can change content type', '5', 'change_contenttype');
INSERT INTO `auth_permission` VALUES ('15', 'Can delete content type', '5', 'delete_contenttype');
INSERT INTO `auth_permission` VALUES ('16', 'Can add session', '6', 'add_session');
INSERT INTO `auth_permission` VALUES ('17', 'Can change session', '6', 'change_session');
INSERT INTO `auth_permission` VALUES ('18', 'Can delete session', '6', 'delete_session');
INSERT INTO `auth_permission` VALUES ('19', 'Can add book', '7', 'add_book');
INSERT INTO `auth_permission` VALUES ('20', 'Can change book', '7', 'change_book');
INSERT INTO `auth_permission` VALUES ('21', 'Can delete book', '7', 'delete_book');
INSERT INTO `auth_permission` VALUES ('22', 'Can add author detail', '8', 'add_authordetail');
INSERT INTO `auth_permission` VALUES ('23', 'Can change author detail', '8', 'change_authordetail');
INSERT INTO `auth_permission` VALUES ('24', 'Can delete author detail', '8', 'delete_authordetail');
INSERT INTO `auth_permission` VALUES ('25', 'Can add publisher', '9', 'add_publisher');
INSERT INTO `auth_permission` VALUES ('26', 'Can change publisher', '9', 'change_publisher');
INSERT INTO `auth_permission` VALUES ('27', 'Can delete publisher', '9', 'delete_publisher');
INSERT INTO `auth_permission` VALUES ('28', 'Can add author', '10', 'add_author');
INSERT INTO `auth_permission` VALUES ('29', 'Can change author', '10', 'change_author');
INSERT INTO `auth_permission` VALUES ('30', 'Can delete author', '10', 'delete_author');
-- ----------------------------
-- Table structure for auth_user
-- ----------------------------
DROP TABLE IF EXISTS `auth_user`;
CREATE TABLE `auth_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`password` varchar(128) NOT NULL,
`last_login` datetime(6) DEFAULT NULL,
`is_superuser` tinyint(1) NOT NULL,
`username` varchar(150) NOT NULL,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`email` varchar(254) NOT NULL,
`is_staff` tinyint(1) NOT NULL,
`is_active` tinyint(1) NOT NULL,
`date_joined` datetime(6) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_user
-- ----------------------------
-- ----------------------------
-- Table structure for auth_user_groups
-- ----------------------------
DROP TABLE IF EXISTS `auth_user_groups`;
CREATE TABLE `auth_user_groups` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_user_groups_user_id_group_id_94350c0c_uniq` (`user_id`,`group_id`),
KEY `auth_user_groups_group_id_97559544_fk_auth_group_id` (`group_id`),
CONSTRAINT `auth_user_groups_group_id_97559544_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),
CONSTRAINT `auth_user_groups_user_id_6a12ed8b_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_user_groups
-- ----------------------------
-- ----------------------------
-- Table structure for auth_user_user_permissions
-- ----------------------------
DROP TABLE IF EXISTS `auth_user_user_permissions`;
CREATE TABLE `auth_user_user_permissions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`permission_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_user_user_permissions_user_id_permission_id_14a6b632_uniq` (`user_id`,`permission_id`),
KEY `auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm` (`permission_id`),
CONSTRAINT `auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),
CONSTRAINT `auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of auth_user_user_permissions
-- ----------------------------
-- ----------------------------
-- Table structure for django_admin_log
-- ----------------------------
DROP TABLE IF EXISTS `django_admin_log`;
CREATE TABLE `django_admin_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`action_time` datetime(6) NOT NULL,
`object_id` longtext,
`object_repr` varchar(200) NOT NULL,
`action_flag` smallint(5) unsigned NOT NULL,
`change_message` longtext NOT NULL,
`content_type_id` int(11) DEFAULT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `django_admin_log_content_type_id_c4bce8eb_fk_django_co` (`content_type_id`),
KEY `django_admin_log_user_id_c564eba6_fk_auth_user_id` (`user_id`),
CONSTRAINT `django_admin_log_content_type_id_c4bce8eb_fk_django_co` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`),
CONSTRAINT `django_admin_log_user_id_c564eba6_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of django_admin_log
-- ----------------------------
-- ----------------------------
-- Table structure for django_content_type
-- ----------------------------
DROP TABLE IF EXISTS `django_content_type`;
CREATE TABLE `django_content_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_label` varchar(100) NOT NULL,
`model` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `django_content_type_app_label_model_76bd3d3b_uniq` (`app_label`,`model`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of django_content_type
-- ----------------------------
INSERT INTO `django_content_type` VALUES ('1', 'admin', 'logentry');
INSERT INTO `django_content_type` VALUES ('10', 'app01', 'author');
INSERT INTO `django_content_type` VALUES ('8', 'app01', 'authordetail');
INSERT INTO `django_content_type` VALUES ('7', 'app01', 'book');
INSERT INTO `django_content_type` VALUES ('9', 'app01', 'publisher');
INSERT INTO `django_content_type` VALUES ('4', 'auth', 'group');
INSERT INTO `django_content_type` VALUES ('2', 'auth', 'permission');
INSERT INTO `django_content_type` VALUES ('3', 'auth', 'user');
INSERT INTO `django_content_type` VALUES ('5', 'contenttypes', 'contenttype');
INSERT INTO `django_content_type` VALUES ('6', 'sessions', 'session');
-- ----------------------------
-- Table structure for django_migrations
-- ----------------------------
DROP TABLE IF EXISTS `django_migrations`;
CREATE TABLE `django_migrations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`applied` datetime(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of django_migrations
-- ----------------------------
INSERT INTO `django_migrations` VALUES ('1', 'contenttypes', '0001_initial', '2018-11-21 03:38:47.493316');
INSERT INTO `django_migrations` VALUES ('2', 'auth', '0001_initial', '2018-11-21 03:38:56.541332');
INSERT INTO `django_migrations` VALUES ('3', 'admin', '0001_initial', '2018-11-21 03:38:58.428935');
INSERT INTO `django_migrations` VALUES ('4', 'admin', '0002_logentry_remove_auto_add', '2018-11-21 03:38:58.491335');
INSERT INTO `django_migrations` VALUES ('5', 'app01', '0001_initial', '2018-11-21 03:39:03.209544');
INSERT INTO `django_migrations` VALUES ('6', 'contenttypes', '0002_remove_content_type_name', '2018-11-21 03:39:04.114346');
INSERT INTO `django_migrations` VALUES ('7', 'auth', '0002_alter_permission_name_max_length', '2018-11-21 03:39:04.816347');
INSERT INTO `django_migrations` VALUES ('8', 'auth', '0003_alter_user_email_max_length', '2018-11-21 03:39:05.643148');
INSERT INTO `django_migrations` VALUES ('9', 'auth', '0004_alter_user_username_opts', '2018-11-21 03:39:05.689948');
INSERT INTO `django_migrations` VALUES ('10', 'auth', '0005_alter_user_last_login_null', '2018-11-21 03:39:06.126749');
INSERT INTO `django_migrations` VALUES ('11', 'auth', '0006_require_contenttypes_0002', '2018-11-21 03:39:06.173549');
INSERT INTO `django_migrations` VALUES ('12', 'auth', '0007_alter_validators_add_error_messages', '2018-11-21 03:39:06.313949');
INSERT INTO `django_migrations` VALUES ('13', 'auth', '0008_alter_user_username_max_length', '2018-11-21 03:39:06.906750');
INSERT INTO `django_migrations` VALUES ('14', 'sessions', '0001_initial', '2018-11-21 03:39:07.437151');
-- ----------------------------
-- Table structure for django_session
-- ----------------------------
DROP TABLE IF EXISTS `django_session`;
CREATE TABLE `django_session` (
`session_key` varchar(40) NOT NULL,
`session_data` longtext NOT NULL,
`expire_date` datetime(6) NOT NULL,
PRIMARY KEY (`session_key`),
KEY `django_session_expire_date_a5c62663` (`expire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of django_session
-- ----------------------------
author表：
authordetail表：
book表：
book_authors表：
publisher表：
三、基于对象的跨表查询
一对多查询（Publisher 与 Book）
正向查询（按字段：publisher）
# 查询"时间中的孩子"书籍的出版社的邮箱 models.Book.objects.filter(title="时间中的孩子").first().publisher.email
反向查询（按表名：book_set）
# 查询"苹果出版社"出版的书籍名称 pub_obj = models.Publisher.objects.filter(name="苹果出版社").first() book_qs = pub_obj.book_set.all() for book in book_qs: print(book.title)
思路解析：出版社找书，属于反向查找，所以 pub_obj.book_set，一个出版社可能出版多本书，所以用 all() 接收，即 book_set.all()，接收到的书属于QuerySet，所以要用 for 循环取到每一个具体的书对象。
一对一查询（Author 与 AuthorDetail）
正向查询（按字段：authorDetail）
# 查询"灰太狼"作者的手机号 models.Author.objects.filter(name="灰太狼").first().authorDetail.telephone
反向查询（按表名：author）
因为是一对一查询，没有 _set，查出的结果只有一个！
# 查询地址在"羊村"的作者的名字 models.AuthorDetail.objects.filter(address="羊村").first().author.name
多对多查询（Author 与 Book）
正向查询（按字段：authors）
# 查询"遮蔽的天空"的作者的名字以及作者的生日 book_obj = models.Book.objects.filter(title="遮蔽的天空").first() authors_qs = book_obj.authors.all() for author in authors_qs: print(author.name, author.authorDetail.birthday)
反向查询（按表名：book_set）
# 查询"喜洋洋"出版过的书籍的名字 author_obj = models.Author.objects.filter(name="喜洋洋").first() book_qs = author_obj.book_set.all() for book in book_qs: print(book.title)
思路解析："查询 遮蔽的天空 的作者的名字以及作者的生日"，这题目让人看上去会觉得这本书的作者只有一个，这样的想法是错误的，一定要牢记查询的目标结果是否是有多个的，有多个结果，那么就用 all() 方法去接收，而 all() 方法返回的必然是 QuerySet，QuerySet能 .字段名 么？不能！！！头脑一定要非常清楚每一次查的结果到底是 object，还是QuerySet。
注意：
可以通过在 ForeignKey() 和 ManyToManyField() 中设置 related_name 的值来覆写 FOO_set 的名称。
例如，在 Book model 中修改如下：
models.ForeignKey(to="Publisher", to_field="id", on_delete=models.CASCADE) # 修改为 models.ForeignKey(to="Publisher", to_field="id", related_name="book", on_delete=models.CASCADE)
那么：
# 查询"苹果出版社"出版的书籍名称 pub_obj = models.Publisher.objects.filter(name="苹果出版社").first() book_qs = pub_obj.book.all() for book in book_qs: print(book.title)
对比上面一对一中的反向查询，如果设置了 related_name="book"，即用 book.all() 代替了 book_set.all()
四、基于双下划线的跨表查询
Django 还提供了一种直观而高效的方式在查询（lookups）中表示关联关系，它能自动确认 SQL JOIN 联系。要做跨关系查询，就使用两个下划线来链接模型（model）间关联字段的名称，直到最终链接到你想要的 model 为止。
关键点：正向查询按字段，反向查询按表名小写用来告诉ORM引擎join哪张表
一对多查询
题目：查询"苹果出版社"出版过的所有书籍的名字与价格
# 正向查询（按字段：publisher） models.Book.objects.filter(publisher__name="苹果出版社").values("title", "price") # 反向查询（按表名：book） models.Publisher.objects.filter(name="苹果出版社").values("book__title", "book__price")
一对一查询
题目：查询"灰太狼"作者的手机号
# 正向查询（按字段：authorDetail） models.Author.objects.filter(name="灰太狼").values("authorDetail__telephone") # 反向查询（按表名：author） models.AuthorDetail.objects.filter(author__name="灰太狼").values("telephone")
多对多查询
题目：查询"喜洋洋"出版过的所有书籍的名字
# 正向查询（按字段：authors） models.Book.objects.filter(authors__name="喜洋洋").values("title") # 反向查询（按表名：book） models.Author.objects.filter(name="喜洋洋").values("book__title")
进阶（连续跨表）
题目1：查询香蕉出版社出版过的所有书籍的名字以及作者的姓名
# 正向查询 models.Book.objects.filter(publisher__name="香蕉出版社").values("title", "authors__name") # 反向查询 models.Publisher.objects.filter(name="香蕉出版社").values("book__title", "book__authors__name")
题目2： 查询手机号以135开头的作者出版过的书籍名称以及书籍对应的出版社名称
# 正向查询 models.Book.objects.filter(authors__authorDetail__telephone__startswith="135").values("title", "publisher__name") # 反向查询 models.Author.objects.filter(authorDetail__telephone__startswith="135").values("book__title", "book__publisher__name")
注意：
反向查询时，如果定义了 related_name ，则用 related_name 设定的值替换表名，例如：
models.ForeignKey(to="Publisher", to_field="id", on_delete=models.CASCADE) # 修改为 models.ForeignKey(to="Publisher", to_field="id", related_name="xxx", on_delete=models.CASCADE)
例如：查询香蕉出版社出版过的所有书籍的名字以及作者的姓名
# 反向查询 models.Publisher.objects.filter(name="香蕉出版社").values("xxx__title", "xxx__authors__name")
五、聚合查询和分组查询
聚合（aggregate）
aggregate()是 QuerySet 的一个终止子句，意思是说，它返回一个包含一些键值对的字典。键的名称是聚合值的标识符，值是计算出来的聚合值。键的名称是按照字段和聚合函数的名称自动生成出来的。
from django.db.models import Avg,Sum,Max,Min,Count
示例：
# 获取所有书的平均值 models.Book.objects.all().aggregate(Avg("price")) # {'price__avg': 49.666667}
如果想要为聚合值指定一个名称，可以向聚合子句提供它；除此之外，可以添加多个参数：
models.Book.objects.all().aggregate(p_max=Max("price"), p_min=Min("price")) # {'p_min': Decimal('42.00'), 'p_max': Decimal('58.00')}
分组（annotate）
1. 单表查询的分组
# 原生SQL语句，按照部门分组求平均工资 select dept,avg(salary) from employee group by dept
ORM单表 --> 查询每个部门的平均工资
models.employee.objects.values("dept").annotate(avg=Avg("salary")).values("dept", "avg")
2. 连表查询的分组
# 原生SQL语句，按照部门分组求平均工资 select dept.name,avg(salary) from employee inner join dept on (employee.dept_id=dept.id) group by dept_id;
ORM查询：
from django.db.models import Avg models.Dept.objects.annotate(avg=Avg("employee__salary")).values("name", "avg") # <QuerySet [{'name': '保安部', 'avg': 1900.0}, {'name': '财务部', 'avg': 2750.0}, {'name': '人事部', 'avg': 3250.0}, {'name': '教学部', 'avg': None}]>
3. 更多示例
①统计每一本书的作者个数
book_qs = models.Book.objects.annotate(c=Count("authors")) for book in book_qs: print(book.c)
②统计出每个出版社卖的最便宜的书的价格
方法一：
pub_qs = models.Publisher.objects.annotate(min_p=Min("book__price")) for pub in pub_qs: print(pub.min_p)
方法二：
book_qs = models.Book.objects.values("publisher__name").annotate(min_p=Min("price")) for book in book_qs: print(book["min_p"])
③统计不止一个作者的图书
book_qs = models.Book.objects.annotate(c=Count("authors")).filter(c__gt=1) for book in book_qs: print(book.title)
④根据一本图书作者数量的多少对查询集 QuerySet 进行排序
book_qs = models.Book.objects.annotate(c=Count("authors")).order_by("c") for book in book_qs: print(book.title, book.c)
⑤查询各个作者出的书的总价格
author_qs = models.Author.objects.annotate(sum_p=Sum("book__price")).values("name", "sum_p") for author in author_qs: print(author["name"], author["sum_p"])
六、F查询和Q查询
F查询
在上面所有的例子中，我们构造的过滤器都只是将字段值与某个常量做比较。如果我们要对两个字段的值做比较，那该怎么做呢？
Django 提供 F() 来做这样的比较。F() 的实例可以在查询中引用字段，来比较同一个 model 实例中两个不同字段的值。
添加字段示例：
from django.db.models import F
①查询 卖出数(sale) 大于 库存数(store) 的所有书的书名
book_qs = models.Book.objects.filter(sale__gt=F("store")) for book in book_qs: print(book.title)
Django 支持 F() 对象之间以及 F() 对象和常数之间的加减乘除和取模的操作。
②刷单：把每一本书的卖出数加1乘以3
models.Book.objects.update(sale=(F("sale") + 1) * 3)
③修改操作也可以使用 F 函数；比如将每一本书的价格提高10元
models.Book.objects.all().update(price=F("price") + 10)
引申：
④给所有书名后面加上 (第一版)
from django.db.models.functions import Concat from django.db.models import Value models.Book.objects.all().update(title=Concat(F("title"), Value("("), Value("第一版"), Value(")")))
Q查询
filter() 等方法中的关键字参数查询都是一起进行 AND 的； 如果需要执行更复杂的查询（例如 OR 语句），可以使用 Q 对象。
from django.db.models import Q
示例①：对比 AND 和OR
# 查询价格小于50 且 卖出数大于300的书 models.Book.objects.filter(price__lt=50, sale__gt=300) # 查询价格小于50 或 卖出数大于300的书 models.Book.objects.filter(Q(price__lt=50) | Q(sale__gt=300))
示例②：查询作者名是 喜洋洋 或 灰太狼 的书
book_qs = models.Book.objects.filter(Q(authors__name="喜洋洋") | Q(authors__name="灰太狼")) for book in book_qs: print(book.title)
示例③：Q查询和字段查询同时存在时，字段查询要放在Q查询的后面（此时，这个字段和Q查询就是 "且" 关系）
book_qs = models.Book.objects.filter(Q(authors__name="喜洋洋") | Q(authors__name="灰太狼"), title__contains="太阳") for book in book_qs: print(book.title)
示例④：查询作者名是 灰太狼 且 出版社id不是1的书
book_qs = models.Book.objects.filter(Q(authors__name="灰太狼") & ~Q(publisher="1")).values("title") for book in book_qs: print(book["title"])
