多表之间的关联最好是用逻辑上的关联,而不是物理上的关联,导致后期的扩展性差!!!
原生sql
--mysql5.6
建表
一对多:
create table dept(id int primary key auto_increment,name char(20),job char(20));
create table emp(id int primary key auto_increment,name char(20),d_id int,foreign key(d_id) references dept(id));
多对多:
1 create table teacher(id int primary key auto_increment,name char(15));
2 create table student(id int primary key auto_increment,name char(15));
3 #中间表
4 create table tsr(
5 id int primary key auto_increment,
6 t_id int,s_id int,
7 foreign key(t_id) references teacher(id),
8 foreign key(s_id) references student(id)
9 );
10 现在老师和学生 都是主表 关系表是从表
11 先插入老师和学生数据
12 insert into teacher values
13 (1,"高跟"),
14 (2,"矮跟");
15
16 insert into student values
17 (1,"炜哥"),
18 (2,"仨疯");
19
20 # 插入对应关系
21 insert into tsr values
22 (null,1,1),
23 (null,1,2),
24 (null,2,2);
25
26 建表语句,中间表及插数据语句
一对一:
客户和学生
一个客户只能产生一个学生
一个学生只能对应一个客户
这样的关系是一对一
使用外键来关联,但是需要给外键加上唯一约束
客户和学生有主从关系,需要先建立客户,再建学生
create table customer(c_id int primary key auto_increment,
name char(20),phonenum char(11),addr char(20));
create table student1(s_id int primary key auto_increment,
name char(20),
class char(11),
number char(20),
housenum char(20),c_id int UNIQUE,
foreign key(c_id) references customer(c_id)
);
示例,建表语句
查询
单表查询:
不带关键字的查询
select {*|字段名|四则运行|聚合函数} from 表名 [where 条件]
1.* 表示查询所有字段
2.可以手动要查询的字段
3.字段的值可以进行加减乘除运算
4.聚合函数,用于统计
where是可选的
一对多查询五种方式:
1.笛卡尔积查询
select *from 表1,表n
查询结果是
将左表中的每条记录,与右表中的每条记录都关联一遍
因为他不知道什么样的对应关系是正确,只能帮你都对一遍
a表有m条记录,b表有n条记录
笛卡尔积结果为m * n 记录
需要自己筛选出正确的关联关系
select *from emp,dept where emp.dept_id = dept.id;
2.内连接查询就是笛卡尔积查询:[inner] join
select *from emp [inner] join dept;
select *from emp inner join dept where emp.dept_id = dept.id;
3.左外链接查询:left join
select *from emp left join dept on emp.dept_id = dept.id;
左表数据全部显示,右表只显示匹配上的
4.右外链接查询:right join
select *from emp right join dept on emp.dept_id = dept.id;
右表数据全部显示,左表只显示匹配上的
内和外的理解:内指的是匹配上的数据,外指的是没匹配上的数据
5.全外连接:union
select *from emp full join dept on emp.dept_id = dept.id; ##mysql不支持
union: 合并查询结果,默认会去重
select *from emp left join dept on emp.dept_id = dept.id
union
select *from emp right join dept on emp.dept_id = dept.id;
union 去除重复数据,只能合并字段数量相同的表
union all 不会去除重复数据
on,where关键字都是用于条件过滤,没有本质区别
在单表中where的作用是筛选过滤条件
只要是连接多表的条件就使用on,为了区分是单表还是多表,搞个新的名字就是on
在多表中on用于连接多表,满足条件就连接,不满足就不连接
多对多查询:
create table stu(id int primary key auto_increment,name char(10));
create table tea(id int primary key auto_increment,name char(10));
#中间表
create table tsr(id int primary key auto_increment,t_id int,s_id int,
foreign key(s_id) references stu(id),
foreign key(s_id) references stu(id));
#插数据
insert into stu values(null,"张三"),(null,"李李四");
insert into tea values(null,"chuck"),(null,"wer");
insert into tsr values(null,1,1),(null,1,2),(null,2,2);
#查询语句,多表查询过滤条件用on
select *from stu join tea join tsr
on stu.id = tsr.s_id and tea.id = tsr.t_id
where tea.name = "chuck";
子查询:
给你部门的的名称,查部门有哪些人?
第一步查到部门的id
第二部拿着id去员工表查询
select *from dept join emp on dept.id = emp.dept_id;
select *from emp join
# 使用子查询,得到每个部门的id以及部门的最高工资,形成一个虚拟表,把原始表和虚拟表连接在一起
(select dept_id,max(salary)as m from emp group by dept_id) as t1
# 如果这个人的部门编号等于虚拟表中的部门编号
on emp.dept_id = t1.dept_id
and
# 并且,如果这个人的工资等于虚拟表中的最高工资,就是你要找的人
emp.salary = t1.m;
orm增删改
--django
单表操作
增:
date类型,传的时候,可以传字符串(格式必须是:2018-06-17),可以传时间对象
ret=models.Book.objects.create(name='洪流吗',price=23.7,publish='北京出版社',pub_data='2018-06-17')
生成对象,再调save方法
book=models.Book(name='三国演义',price=46.89,publish='南京出版社',pub_data='2017-08-17')
book.save()
删:
ret=models.Book.objects.filter(pk=1).delete() --pk指主键
book=models.Book.objects.filter(pk=1).first()
book.delete()
改:
ret = models.Book.objects.filter(pk=2).update(name='ddd')
book=models.Book.objects.filter(pk=2).first()
book.name='XXX'
# 没有update这个方法的
# book.update()
# 既可以保存,又可以更新
book.save()
get方法
book = models.Book.objects.filter(pk=2).first()
# book拿到的是 book对象
#get查到的数据有且只有一条,如果多,少,都抛异常
book=models.Book.objects.get(name='XXX')
print(book.name)
查:
- 基于对象:
<1> all(): 查询所有结果
book=models.Book.objects.all()
<2> filter(**kwargs): 它包含了与所给筛选条件相匹配的对象
<3> get(**kwargs): 返回与所给筛选条件相匹配的对象,返回结果有且只有一个,超过一个或者没有都会抛出错误。
<4> exclude(**kwargs): 它包含与所给筛选条件不匹配的对象,exclude = 排除
<5> order_by(*field): 对查询结果排序('-id'),取负号即反转
<6> reverse(): 对查询结果反向排序,必须在order_by之后才能调用。
book=models.Book.objects.order_by('price').reverse()
<8> count(): 返回数据库中匹配查询(QuerySet)的对象数量。
book=models.Book.objects.all().count()
<9> first(): 返回第一条记录
<10> last(): 返回最后一条记录
<11> exists(): 如果QuerySet包含数据,就返回True,否则返回False
<12> values(*field): 返回一个ValueQuerySet——一个特殊的QuerySet,运行后不是一系列model的实例化对象,而是一个可迭代的字典序列
<13> values_list(*field): 它与values()非常相似,它返回的是一个元组序列,values返回的是一个字典序列
<14> distinct(): 从返回结果中剔除重复纪录
- 基于双下划线:
price__in:__前为字段名,后为对应方法;
Book.objects.filter(price__in=[100,200,300]) --在列表范围内
Book.objects.filter(price__gt=100) --大于
Book.objects.filter(price__lt=100) --小于
Book.objects.filter(price__gte=100) --大于等于
Book.objects.filter(price__lte=100) --小于等于
Book.objects.filter(price__range=[100,200]) --在100-200之间
Book.objects.filter(title__contains="python") --包含Python
Book.objects.filter(title__icontains="python") --包含Python且区分大小写
Book.objects.filter(title__startswith="py") --以py开头
Book.objects.filter(pub_date__year=2012) --获取对应年份,月,日数据
多表操作
class Book(models.Model):
nid = models.AutoField(primary_key=True)
name = models.CharField(max_length=32)
price = models.DecimalField(max_digits=5, decimal_places=2)
publish_date = models.DateField()
# 阅读数
# reat_num=models.IntegerField(default=0)
# 评论数
# commit_num=models.IntegerField(default=0)
publish = models.ForeignKey(to='Publish',to_field='nid',on_delete=models.CASCADE)
authors=models.ManyToManyField(to='Author')
def __str__(self):
return self.name
class Author(models.Model):
nid = models.AutoField(primary_key=True)
name = models.CharField(max_length=32)
age = models.IntegerField()
author_detail = models.OneToOneField(to='AuthorDatail',to_field='nid',unique=True,on_delete=models.CASCADE)
class AuthorDatail(models.Model):
nid = models.AutoField(primary_key=True)
telephone = models.BigIntegerField()
birthday = models.DateField()
addr = models.CharField(max_length=64)
class Publish(models.Model):
nid = models.AutoField(primary_key=True)
name = models.CharField(max_length=32)
city = models.CharField(max_length=32)
email = models.EmailField()
models.py
正向:关联关系在当前表中,从当前表去另一个表;正向查询按字段名
反向:关联关系不在当前表,从当前表去另一个表;反向查询按表名小写
一对一:
- 增加
author=models.Author.objects.create(name='小猴',age=16,author_detail_id=authordetail.pk)
一对多:
- 增加
# publish:可以传一个publish对象
publish=models.Publish.objects.get(pk=1)
print(publish.name)
ret=models.Book.objects.create(name='西游记',price=88,publish_date='2018-09-12',publish=publish)
# publish_id:传一个id
ret=models.Book.objects.create(name='三国演义',price=32,publish_date='2018-07-12',publish_id=publish.pk)
print(type(ret.publish))
- 修改
book=models.Book.objects.get(pk=2)
book.publish_id=2
# book.publish=出版社对象
book.save()
ret=models.Book.objects.filter(pk=2).update(publish=publish对象)
ret=models.Book.objects.filter(pk=2).update(publish_id=2)
- 删除
多对多:
- 增加
#给红楼梦这本书添加两个作者(lqz,egon)
book = models.Book.objects.get(pk=1)
#相当于拿到了第三张表
往第三章表中添加纪录(问题来了?要传对象还是传id),都支持
book.authors.add(1,2)
lqz = models.Author.objects.get(pk=1)
egon = models.Author.objects.get(pk=2)
book.authors.add(lqz,egon)
book.authors.add(*[lqz,egon])
- 删除
#remove字段authors的id和名字都可以(名字为通过id获取的)
book.authors.remove(2)
book.authors.remove(egon,lqz)
book.authors.remove(1,2)
book.authors.remove(*[1,2])
book.authors.remove(*[lqz,egon])
- 修改
#修改红楼梦这本书的作者为lqz和egon
#先清空(清空这本的所有作者记录)
book.authors.clear()
book.authors.add(1,2)
#id
book.authors.set(*[6,]) #这样不行
book.authors.set([6,]) #需要这样传
#对象
lqz=models.Author.objects.get(pk=2)
set 必须传一个可迭代对象
book.authors.set([lqz,]) #需要这样传
orm查询
基于对象的跨表查询(多次查询)
一对一:
-正向查询按字段
-反向查询按表名小写
一对多:
-正向查询按字段(正向查询一定会查出一个来)
-反向查询按表名小写_set.all()(返回结果是queryset对象)
多对多:
-正向查询按字段.all()(正向查询一定会查出多个来)
-反向查询按表名小写_set.all()(返回结果是queryset对象)
基于双下划线的跨表查询
-在filter和values中都可以做连表操作(也就是都可以写 __)
-正向查询按字段
-反向查询按表名小写
无论以谁做基表,没有效率之分
基于对象:
-
一对一
#查询lqz作者的地址(正向查询,按字段)
lqz=models.Author.objects.filter(name='lqz').first()
# 作者详情对象
print(lqz.author_detail.addr)
#查询地址为上海的,作者的名字(反向查询,按表名小写)
authordetail=models.AuthorDatail.objects.filter(addr='上海').first()
#拿到的是作者对象authordetail.author
print(authordetail.author.name)
-
一对多
#查询红楼梦这本书的出版社名字(正向,按字段)
book=models.Book.objects.get(pk=1)
#出版社对象 book.publish
print(book.publish.name)
#查询北京出版社出版的所有书名(反向查询按 表名小写_set.all())
publish=models.Publish.objects.get(pk=1)
#结果是queryset对象
books=publish.book_set.all()
for book in books:
print(book.name)
#查询以红开头的
books=publish.book_set.all().filter(name__startswith='红')
for book in books:
print(book.name)
-
多对多
#红楼梦这本书所有的作者(正向 字段)
book=models.Book.objects.get(pk=1)
# book.authors.all()拿到所有的作者,是一个queryset对象
authors=book.authors.all()
for author in authors:
print(author.name)
#查询egon写的所有书(反向 表名小写_set.all())
egon=models.Author.objects.get(pk=2)
#拿到的是queryset对象
books=egon.book_set.all()
for book in books:
print(book.name)
基于双下划线:
-
一对一
#查询lqz作者的名字,地址(正向查询,按字段)
ret=models.Author.objects.filter(name='lqz').values('name','author_detail__addr')
print(ret)
#查询地址为上海的作者的名字(反向,按表名小写)
ret=models.AuthorDatail.objects.filter(addr='上海').values('addr','author__name','author__age')
print(ret.query)
print(ret)
-
一对多
#查询红楼梦这本书的出版社的名字(正向 按字段)
ret=models.Book.objects.filter(name='红楼梦').values('name','publish__name')
print(ret)
#查询北京出版社出版的所有书的名字(反向 按表名小写)
ret=models.Publish.objects.filter(name='北京出版社').values('book__name')
print(ret)
-
多对多
#红楼梦这本书所有的作者名字(正向 按字段)
ret=models.Author.objects.filter(book__name='红楼梦').values('name')
print(ret)
ret=models.Book.objects.filter(name='红楼梦').values('authors__name')
print(ret)
#egon出版的所有书的名字(反向 表名小写)
ret=models.Book.objects.filter(authors__name='egon').values('name')
print(ret)
ret=models.Author.objects.filter(name='egon').values('book__name')
print(ret)
#查询北京出版社出版过的所有书籍的名字以及作者的姓名
ret=models.Publish.objects.filter(name='北京出版社').values('book__name','book__authors__name')
print(ret)
ret=models.Book.objects.filter(publish__name='北京出版社').values('name','authors__name')
print(ret)
ret=models.Author.objects.filter(book__publish__name='北京出版社').values('book__name','name')
print(ret)
#地址是以北开头的作者出版过的所有书籍名称以及出版社名称
#以authordetial为基表
ret = models.AuthorDatail.objects.filter(addr__startswith='北').values('author__book__name',
'author__book__publish__name')
print(ret)
#以book为基表
ret=models.Book.objects.filter(authors__author_detail__addr__startswith='北').values('name','publish__name')
print(ret.query)
#以author为基表
ret=models.Author.objects.filter(author_detail__addr__startswith='北').values('book__name','book__publish__name')
print(ret.query)