数据库总结

多表之间的关联最好是用逻辑上的关联，而不是物理上的关联，导致后期的扩展性差！！！

原生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));

多对多：

create table teacher(id int primary key auto_increment,name char(15));
create table student(id int primary key auto_increment,name char(15));
#中间表
create table tsr(
id int primary key auto_increment,
t_id int,s_id int,
foreign key(t_id) references teacher(id),
foreign key(s_id) references student(id)
);
现在老师和学生 都是主表  关系表是从表
先插入老师和学生数据
insert into teacher values
(1,"高跟"),
(2,"矮跟");

insert into student values
(1,"炜哥"),
(2,"仨疯");

# 插入对应关系
insert into tsr values
(null,1,1),
(null,1,2),
(null,2,2);

建表语句,中间表及插数据语句

一对一：

客户和学生
一个客户只能产生一个学生
一个学生只能对应一个客户
这样的关系是一对一
使用外键来关联,但是需要给外键加上唯一约束
客户和学生有主从关系,需要先建立客户,再建学生

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)