07.模型详解 -- 多表查询

作者: __深蓝__ | 来源:发表于2019-03-11 15:07 被阅读0次

07.模型详解 -- 多表查询
07.模型详解 -- 查询函数
sqlalchemy多表联合查询
Django模型（四）
MySQL多表查询详解
Mybatis的多表操作
MySQL 多表连接查询详解
SQLAlchemy（四）
python面试题01
数据库基本操作3.0

通过对象进行多表查询

由一到多查询：一类模型类对象名.小写多类模型类类名_set.查询函数()
通过对象查询分成2步，先查到某本图书，再通过该图书对象查询该图书中的人物对象
实例演练： 查询id为1的图书中，所有人物的信息
修改views.py文件

def index(request):
    book = BookInfo.objects.get(id=1)
    persons = book.personinfo_set.all()

    ret = ''
    for p in persons:
        ret += str(p.id) + ", " + p.pname + ", " + str(p.pgender) \
               + ", " + p.pcomment + ", " + str(p.hbook_id)
        ret += '<br>'

    return HttpResponse(ret)

1, 曹操, True, 字孟德, 1
2, 刘备, True, 字玄德, 1
3, 诸葛亮, True, 字孔明, 1
4, 孙权, True, 字仲谋, 1

由多到一查询：多类模型类对象名.多类模型类中外键对应的属性名
通过对象查询分成2步，先查到某个人物，再通过该人物对象查询其所在的图书对象
实例演练： 查询id为1的人物所在的图书信息
修改views.py文件

def index(request):
    person = PersonInfo.objects.get(id=1)
    book = person.hbook

    ret = book.btitle + ", " + str(book.bpub_date) + ", " \
          + str(book.bread) + ", " + str(book.bcomment)

    return HttpResponse(ret)

三国演义, 1980-05-01, 12, 34

通过模型类进行多表查询

由一到多查询：一类模型类名.objects.filter(小写多类模型类名__属性名__条件运算符 = 值)
实例演练： 查询图书，要求图书中人物描述包含'德'字
修改views.py文件

def index(request):
    books = BookInfo.objects.filter(personinfo__pcomment__contains='德')

    ret = ''
    for book in books:
        ret += book.btitle + ", " + str(book.bpub_date) + ", " \
               + str(book.bread) + ", " + str(book.bcomment)
        ret += '<br>'
    return HttpResponse(ret)

三国演义, 1980-05-01, 12, 34
三国演义, 1980-05-01, 12, 34

如果没有__运算符部分，表示等于

由多到一查询：多类模型类名.objects.filter(多类模型类外键的属性名__一类模型类属性名__条件运算符 = 值)
实例演练： 查询“西游记”中的所有人物
修改views.py文件

def index(request):
    persons = PersonInfo.objects.filter(hbook__btitle='西游记')

    ret = ''
    for p in persons:
        ret += str(p.id) + ", " + p.pname + ", " + str(p.pgender) \
               + ", " + p.pcomment + ", " + str(p.hbook_id)
        ret += '<br>'

   return HttpResponse(ret)

14, 孙悟空, True, 唐僧的大徒弟, 4
15, 唐僧, True, 玄奘, 4
16, 猪八戒, True, 悟能, 4
17, 沙僧, True, 沙悟净, 4

多对多查询

对于复杂的多对多查询，可以使用原生SQL来处理，参考文档
语法格式：模型类名.objects.raw('SQL语句', params=None, translations=None)
实例演练： 查询所有新闻标题、内容及其类型
在Python Console中执行

list = NewsInfo.objects.raw('''SELECT * 
                               FROM app_newsinfo n, app_typeinfo t, app_newsinfo_ntype nt 
                               WHERE n.id = nt.newsinfo_id 
                               AND t.id = nt.typeinfo_id''')

用来查询的模型类，使用NewsInfo和TypeInfo都可以

返回值的类型为RawQuerySet

for l in list:
    print(l.ntitle, l.ncontent, l.tname)

互联网科技 马云已退出阿里旗下5家公司：官方称没这个打算 科技
宇宙探索 平行时空、多元宇宙真的存在？令人细思极恐 科技
中国军情 中国海军万吨巨舰的起点 原型就是这艘民船 科技
国际军情 美国国会议员：前总统吉米·卡特请缨亲赴朝鲜 军事
欧洲 欧盟高官警告：特朗普不要搞垮了世贸体系 军事
数码产品 苹果官方科普来了：全面认识Apple ID 国际
国际军情 美国国会议员：前总统吉米·卡特请缨亲赴朝鲜 国际
欧洲 欧盟高官警告：特朗普不要搞垮了世贸体系 国际
美国 伊拉克北部发生汽车炸弹袭击致1死7伤 国际

自连接

对于地区信息数据表，表结构非常相似，可以设计成一张表，通过自连接产生相关信息

上图中，通过查询地区表(AreaInfo)，可以产生省表和市区表
还可以通过自连接产生省市区对照表

通过Django实现自连接

修改models.py文件，添加AreaInfo模型类。
外键关连表使用self指向本类，null和blank参数允许为空，因为一级数据没有父数据

定义地区模型类AreaInfo，存储省、市、区县信息

class AreaInfo(models.Model):
    atitle = models.CharField(max_length=30)  # 地区名称
  
    # 上级地区
    aParent = models.ForeignKey('self', null=True, blank=True, on_delete=models.CASCADE)

进行数据迁移，生成数据表

python manage.py makemigrations
python manage.py migrate

生成数据表app_areainfo，并将外键aParent_id关联到本身的主键id上

添加测试数据
在SQLyog中执行插入语句

INSERT INTO app_areainfo VALUES
('210000', '辽宁省', NULL),
('210100', '沈阳市', '210000'),
('210102', '和平区', '210100'),
('210103', '沈河区', '210100'),
('210104', '大东区', '210100'),
('210105', '皇姑区', '210100'),
('210106', '铁西区', '210100'),
('210200', '大连市', '210000'),
('210202', '中山区', '210200'),
('210203', '西岗区', '210200'),
('210204', '沙河口区', '210200'),
('210211', '甘井子区', '210200'),
('210300', '鞍山市', '210000'),
('210302', '铁东区', '210300'),
('210303', '铁西区', '210300'),
('210304', '立山区', '210300'),
('210311', '千山区', '210300'),
('220000', '吉林省', NULL),
('220100', '长春市', '220000'),
('230000', '黑龙江省', NULL),
('230100', '哈尔滨市', '230000')

实例演练： 使用SQL语句分别查询省表、市区表、省市对照表

SELECT * FROM app_areainfo WHERE aParent_id IS NULL

SELECT * FROM app_areainfo WHERE aParent_id IS NOT NULL

SELECT p.atitle, c.atitle 
FROM app_areainfo p, app_areainfo c
WHERE p.id = c.aParent_id

通过Django实现查询
语法格式：查询上级，area.aParent，查询下级，area.areainfo_set.all()

实例演练： 查询沈阳市的上级地区
修改views.py文件

def area(request):
    area = AreaInfo.objects.get(pk=210100)  # 获得沈阳市的对象

    ret = area.atitle + ", " + area.aParent.atitle  # 通过对象查询上级
    return HttpResponse(ret)

沈阳市, 辽宁省

实例演练： 查询辽宁省的下级地区
修改views.py文件

def area(request):
    area = AreaInfo.objects.get(pk=210000)  # 获得辽宁省的对象

    ret = ""
    for area in area.areainfo_set.all():  # 通过对象查询下级
        ret += area.atitle
        ret += '<br>'

    return HttpResponse(ret)

沈阳市
大连市
鞍山市

课堂练习：

创建emp和dept表，表结构如下图：

添加测试数据

INSERT  INTO app_dept VALUES 
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');

INSERT INTO app_emp VALUES 
(7369,'SMITH','CLERK',7902,'1980-12-17','800.00',NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20','1600.00','300.00',30),
(7521,'WARD','SALESMAN',7698,'1981-02-22','1250.00','500.00',30),
(7566,'JONES','MANAGER',7839,'1981-04-02','2975.00',NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28','1250.00','1400.00',30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01','2850.00',NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09','2450.00',NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19','3000.00',NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08','1500.00','0.00',30),
(7876,'ADAMS','CLERK',7788,'1987-05-23','1100.00',NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03','950.00',NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03','3000.00',NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23','1300.00',NULL,10);

使用Django完成如下查询操作
查询dept表中的所有列信息
查询emp表中的员工姓名、月收入及部门编号
查询emp表中的部门编号及工种，并去掉重复行
查询emp表中的员工姓名及全年的收入
查询月收入大于2000的员工姓名及月收入
查询月收入在1000元到2000元的员工姓名、月收入及雇佣时间。
查询以S开头的员工姓名及月收入。
查询员工姓名中的第三个字符是A的员工姓名及月收入。
查询emp表中月收入是800的或是1250的员工姓名及部门编号
查询在部门20中岗位CLERK的所有雇员信息
查询工资高于2500或岗位为MANAGER的所有雇员信息
查询有奖金 (COMM不为空,且不为0) 的员工姓名，按工资排序
查询不带有'R'的雇员姓名

- end -