数据库总结

数据库

1. 事务

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
一个数据库事务通常包含对数据库进行读或写的一个操作序列。它的存在包含有以下两个目的：

为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

事务具有四个属性：

原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。

2. Django的ORM实现数据库事务操作

# 在视图函数中导入transaction
def xxx(request):
    from django.db import transaction  
    try:
        with transaction.atomic():
            models.XXX.objects.xxx()
            models.XXX.objects.xxx()
    except Exception as e:
            print(e)
    
    return HttpResponse("ok")

示例如下：

# models.py
from django.db import models

class Userinfo(models.Model):
    username=models.CharField("用户名",max_length=32)
    email=models.EmailField("邮箱",max_length=32)

class Group(models.Model):
    title=models.CharField("组名",max_length=32)

# views.py
from django.shortcuts import render,HttpResponse
from . import models

def index(request):

    from django.db import transaction

    try:
        with transaction.atomic():
            models.Userinfo.objects.create(username="python001",email="python001@qq.com")
            models.Group.objects.create(title="python002")

    except Exception as e:
        return HttpResponse("出现错误....")
    return HttpResponse("ok")

3. Redis

Redis是什么？

是一个完全开源免费的key-value内存数据库；
通常被认为是一个数据结构服务器，主要是因为其有着丰富的数据结构 strings、map、list、sets、sorted sets。
Redis特点

通常局限点来说，Redis也以消息队列的形式存在，作为内嵌的List存在，满足实时的高并发需求。在使用缓存的时候，redis比memcached具有更多的优势，并且支持更多的数据类型，把redis当作一个中间存储系统，用来处理高并发的数据库操作。

速度快：使用标准C写，所有数据都在内存中完成，读写速度分别达到10万/20万
持久化：对数据的更新采用Copy-on-write技术，可以异步地保存到磁盘上，主要有两种策略，一是根据时间，更新次数的快照（save 300 10 ）二是基于语句追加方式(Append-only file，aof)
自动操作：对不同数据类型的操作都是自动的，很安全
快速的主–从复制，官方提供了一个数据，Slave在21秒即完成了对Amazon网站10G key set的复制。
Sharding技术：很容易将数据分布到多个Redis实例中，数据库的扩展是个永恒的话题，在关系型数据库中，主要是以添加硬件、以分区为主要技术形式的纵向扩展解决了很多的应用场景，但随着web2.0、移动互联网、云计算等应用的兴起，这种扩展模式已经不太适合了，所以近年来，像采用主从配置、数据库复制形式的，Sharding这种技术把负载分布到多个特理节点上去的横向扩展方式用处越来越多。

Redis缺点

是数据库容量受到物理内存的限制,不能用作海量数据的高性能读写,因此Redis适合的场景主要局限在较小数据量的高性能操作和运算上。
Redis较难支持在线扩容，在集群容量达到上限时在线扩容会变得很复杂。为避免这一问题，运维人员在系统上线时必须确保有足够的空间，这对资源造成了很大的浪费。

4. 数据库引擎

数据库引擎

简述MyISAM与InnoDB区别

存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。
InnoDB：所有的表都保存在同一个数据文件中。
事务支持

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
InnoDB：提供事务支持事务，外部键等高级数据库功能。
外键

MyISAM：不支持
InnoDB：支持
全文索引

MyISAM：支持全文索引
Innodb：不支持全文索引
查询和更新

MyISAM：大量的select，用MyISAM
Innodb：大量的insert或update，用Innodb
MySQL存储引擎－－MyISAM与InnoDB区别

5. 请简述对左连接、右连接以及内连接的理解

left join（左连接）返回包括左表中的所有记录和右表中联结字段相等的记录
right join（右连接）返回包括右表中的所有记录和左表中联结字段相等的记录
inner join（内连接）只返回两个表中联结字段相等的行

6. Redis和MySQL的事务有什么区别？

Redis的事务（transaction）是一组命令的集合。事务同命令一样都是Redis最小的执行单元，一个事务中的命令要么都执行，要么都不执行。Redis事务的实现需要用到multi和exec两个命令，事务开始的时候要先想Redis服务器发送multi命令，然后依次发送需要再本次事务中处理的命令，最后再发送exec命令表示事务命令结束。
MySQL的事务特性，要求这组操作，要不全都成功，要不全都失败，这样就避免了某个操作成功而某个操作失败。利于数据的安全。

7. MySQL数据库的优化

优化索引、sql语句、分析慢查找；
设计表的时候严格根据数据库的设计范式来设计数据库；
使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘I/O；
优化硬件，采用SSD，使用磁盘队列技术（RAID0，RAID1，RAID5）等；
采用MySQL内部再带的表分区技术，把数据分成不同的文件，能够提高磁盘的读取效率；
垂直分表，把一些不经常读的数据放在一张表里，节约磁盘I/O；
主从分离读写；采用主从复制吧数据库的读操作和写入操作分离开来。
分析分表分机器（数据量特别大），主要的原理就是数据路由；
选择合适的表引擎，参数上的优化；
进行架构级别的缓存，静态化和分布式；
不采用全文索引；
采用更快的储存方式，例如NoSQL存储

8. Redis和MySQL的区别

redis（非关系型数据库）是一个内存数据库，所有数据都放在内存中，查询速度比较快。
MySQL（关系型数据库）无论数据还是索引都存放在硬盘中，到使用的时候才交换到内存中。数据访问时比较慢，能够处理远超过内存总量的数据。

9. 描述下表与视图的理解

视图是数据库数据的特定子集。可以禁止所有用户访问数据库表，而要求用户只能通过视图操作数据，这种方法可以保护用户和应用程序不受某些数据库修改的影响。
视图是抽象的，他在使用时，从表里提取出数据，形成虚的表。不过对他的操作有很多的限制。
而且视图是永远不会自己消失的除非你删除它。
视图适合于多表连接浏览时使用！不适合增、删、改，存储过程适合于使用较频繁的SQL语句，这样可以提高执行效率!

区别：
1、视图是已经编译好的sql语句。而表不是。
2、视图没有实际的物理记录。而表有。
3、表是内容，视图是窗口
4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
5、表是内模式，视图是外模式
6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
8、视图的建立和删除只影响视图本身，不影响对应的基本表。

10. 索引有什么作用？有哪些分类？有什么好处和坏处？

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。
他的作用就是提高查询效率。
分类：

普通索引
唯一索引
全文索引
单列索引、多列索引
组合索引（最左前缀）

优点：

在设计数据库时，通过创建一个惟一的索引，能够在索引和信息之间形成一对一的映射式的对应关系，增加数据的惟一性特点。
能提高数据的搜索及检索速度，符合数据库建立的初衷。
能够加快表与表之间的连接速度，这对于提高数据的参考完整性方面具有重要作用。
在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。
建立索引之后，在信息查询过程中可以使用优化隐藏器，这对于提高整个信息检索系统的性能具有重要意义。

缺点：

在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间，如果有必要建立起聚簇索引，所占用的空间还将进一步的增加
在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。

数据库练习

写SQL语句，对下面两张表实现处理

"""
table_a
name kecheng fenshu 
张三  语文  81
张三  数学  75
李四  语文  76
李四  数学  90
王五  语文  81
王五  数学  100

table_b
id name
1  张三
2  李四
3  王五
4  小六
"""

查询出每门课程都大于80分的学生姓名

1	SELECT DISTINCT name FROM Table_A WHERE name not in (SELECT name FROM Table_A WHERE fenshu<80);

查询语文成绩最大的学生姓名

1	SELECT name FROM Table_A WHERE kecheng='语文' ORDER BY fenshu DESC LIMIT 1;

查询没有成绩的学生姓名

1	SELECT name FROM Table_B WHERE name not in (SELECT DISTINCT name FROM Table_A);

编写SQL完成以下操作

表格定义

购买行为表

CREATE TABLE buy_action (
id INT(11) NOT NULL ,
user_id INT(11) NOT NULL ,
product_name TEXT NOT NULL ,
amount INT(11) NOT NULL ,
purchase_date DATETIME,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
;

用户表

CREATE TABLE user(
user_id INT(11) NOT NULL ,
name TEXT NOT NULL ,
PRIMARY KEY (user_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
;

INSERT buy_action VALUES 
(1,1,'奶粉',12,'2017-12-15'),
(2,1,'奶粉',13,'2017-12-15')
;


INSERT buy_action VALUES 
(3,1,'尿布',12,'2017-12-15'),
(4,1,'尿布',40,'2017-12-15')
;

INSERT user VALUES 
(1,'张三'),
(2,'李四')
;

1.查询用户名叫“张三”的购买行为

1	SELECT * FROM buy_action INNER JOIN user on buy_action.user_id=user.user_id WHERE name='张三';

2.查询商品名叫“奶粉”的今天的全部购买金额

1	SELECT product_name,sum(amount),purchase_date FROM buy_action WHERE product_name='奶粉' AND purchase_date=current_date();

3.批量查询每种商品今天的全部购买总额

1	SELECT product_name '产品名',sum(amount) '总金额' FROM buy_action WHERE purchase_date=current_date() GROUP BY product_name;

数据库