python数据库基础

1.数据类型：（使用原则：够用就行，尽量使用范围小的）

    整数：int，bit
    小数：decimal
    字符串：varchar（可变长度），char（固定长度字符串）
    日期时间：date，time，datetime
    枚举类型（enum）
    特别说明
2.约束（限制）
主要：
    主键primary key：物理存储的顺序
    非空 not null 不允许填写空格
    唯一 unique：不允许重复
    默认default：数据项的默认值
    外键 foreign key：存储其他表的主键的字段
其他：
    unsigned 无符号
    auto increment 自增
4.常用术语：
    数据库: 数据库是一些关联表的集合。
    数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
    列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
    行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
    冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
    主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
    外键：外键用于关联两个表。
    复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
    索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
    参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
5.mysql数据库引擎：
    innodb  支持事务处理和行级锁（对数据元素进行修改时对整个数据元素进行加锁）
    myisam  不支持以上

6.常用命令行命令和语句
    注释：“--”
    在参数名后面加“~”可以保证参数的完整性而不会由于各种情况被误认为是两个参数

    进入客户端：mysql -u用户名 -p密码

    显示所有数据库：show databases;

    显示所有时间：select now（）;

    显示版本：select version（）;

    创建数据库：
        以默认参数创建：create database 数据库名
        指定编码格式等信息：create database 数据库名 charset=编码格式名

    查看数据库的创建信息（参数设置）：show create database 已创建的数据库名;

    删除数据库：删除数据库： drop database 数据库名;

    查看当前使用的数据库 select database();

    进入指定数据库：use 数据库名;

    创建数据表 create table 表名（字段 类型 约束 [,字段 类型 约束]）;

    查看数据表的字段结构：desc 表名;

    显示当前数据库的所有表名：show tables;

    向指定表插入数据： insert into 表名 values（字段值序列（按表定义时的顺序））
    显示表的结构：show create table 表名;
    选取指定表的所有数据：select * from 表名;
    开启时间监测功能： set profiling=1;
    显示历史查询的语句和执行时间：show profiles;
7.修改数据表：
        添加字段：alter table 数据表名 add 列名 类型;
        修改字段（非重命名）：alter table 数据表名 modify 列名 类型及约束;
        修改字段（重命名）：alter table 数据表名 change 原列名 新列名 类型及约束;
        删除字段：alter table 数据表名 drop 列名;
        删除表：drop table 表名;
8.修改数据表中的数据：
        插入数据：insert into 表名 values(字段值序列)    主键如果是自增的，值可以填0，null或者default
                部分插入：insert into 表名（非空字段和其他字段）values(字段值序列)
                多行插入：多个括号括起来的序列值，由“,”隔开

        删除数据：
            物理删除：delete from 表名 [where 条件];  不加条件删除所有数据    删除后的主键最好别用
            逻辑删除：添加一个标志字段 通过update更改数据的字段值
            一般使用逻辑删除
        修改数据：update 表名 set 字段名=字段值 where 条件;（where语句不加则修改表中所有数据）
        查询数据：
                select * from 表名 [where 条件];   *表示全部字段（列）
                select 字段名 [as 别名],字段名 [as 别名] from 表名 [where 条件];   字段名可颠倒

9.数据库查询：
    条件查询：
        比较运算符 >,>=,<=,=,!=(<>)
        逻辑运算符 and or
        模糊查询：like          字段名 like 字段值的满足的条件
            %替换一个或多个     name like "小%"  （以“小”字开头的name字段）  name like"%小"  name字段值以“小”字结尾
            _替换一个           name like "__"   （name字段值必须有两个字） name like "__%" name字段至少包含两个字符
        正则表达式匹配查询： rlike    字段名  like 正则表达式串

     范围查询：
        in：字段名 in (值集合)
        not in
        between and：字段名 between 值1 and 值2
        not between  and：字段名 not between 值1 and 值2   （切记不能再not后对between and 加括号，否则会报错）
        is null/is not null 判断字段值是否为空

10.排序：在查询条件最后加order by 字段名    默认从小到大  从大到小desc，当排序根据的字段值相等时，按照主键排序，
        多条件排序：order by 字段名1，字段名2，字段名3 在字段1的值相同的情况下按照字段值2来排序，如此地推，若最后一个参照的字段值相等，则按照主键从小到大排序

11.聚合函数：
    计数count：select count(*) [as 别名] from 表名 where 条件;
    最大值max：select max(字段名) from 表名[where 条件];
    总和sum：  select sum(字段名) from 表名[where 条件];
    最小值min：select mini(字段名) from 表名[where 条件];
    平均值avg：select avg(字段名) from 表名[where 条件];  等价于select sum(字段名)/count(*) from 表名[where 条件];
    取保留几位小数以后的值round(,位数)： select round(avg(字段名),2) from 表名[where 条件];


12.分组：必须和聚合配合使用，否则没有意义。
    group by 字段名:     select 字段名[,聚合函数()] from 表名 where 条件 group by 字段名    先将查询结构排序，再对每组分别执行select 语句
    group_concat(字段名,[其他字符,字段名])    配合group by 使用，将查询结果中的若干字段构造成group_cat()括号中的样子，再将同组的放在一行显示。默认用逗号隔开
    having 条件：        过滤掉不满足条件的分组与group by配合使用

13.结果数量限制：
    limit：    一般放最后
        limit 数量
        limit 起始位置（0开始），数量
14.连接查询：
    内连接（取交集） inner join：  select * from 表1  inner join 表2 on 表1.字段=表2.字段
    外连接：
        左连接     left join：以左边的表为基准进行连接查询
        右连接     right join
15.having和where 的区别：前者在之前语句的查询结果中判断条件是否满足，后者在原表中判断。
16.自关联：一个表的某个字段值用到了同表另一个字段值
17.子查询：建议少用
    查询条件中嵌套其他查询

18.表设计规范：
    范式：
        第一范式1nf     每个字段不可再拆分
        第二范式2nf     必须有主键，主键可以有多个字段，主键以外的字段必须依赖于全部的主键而不是部分主键
        第三范式3nf

19.python 链接数据库：
    总体步骤：
        连接数据库
        获取游标对象
        execute执行语句
        关闭游标对象
        关闭数据库连接

    from pymsql impirt *     #导入相关模块
    connection实例名 = connect(参数列表)  实例化connection对象
        参数：
            host：要连接的目标mysql主机ip，本机是"localhost"
            port：要连接的目标mysql主机端口，默认3306
            database：数据库的名称
            user：连接的用户名
            password：连接的密码
            charset：通信采用的编码方式，推荐utf8
    游标名 = connection实例名.cursor()  #实例化一个cursor游标对象
    结果数 = 游标名.execute(sql语句)    #执行一条sql语句，返回查询结果数量
    游标名.fetchone()  #得到上条执行语句的一个执行结果
    游标名.fetchmany(个数) #得到执行语句后的多条结果
    游标名.fetchall


20.增删改时要使用connection实例.commit()，撤销上次的执行使用connection实例.rollback()


21.mysql高级：
    视图：从若干表中直接或1查询出的结果组成的表。简单理解：一张虚拟的表，隔离了数据库和程序中的sql语句。视图不可以更新，是虚拟的不会立即创建执行，在视图被使用时才会临时创建，因此可以视图中的数据随着其相关表的数据的更新而更新
        语句:create view 视图名 as 查询语句
    事务：一个操作序列，这些操作要么都执行，要么都不执行，是一个不可分割的工作单位。
        mysql命令行默认每一条语句分别开启了事务，pymsql中默认不开启事务，需要手动提交和回滚。
        四大特性：acid
            原子性（atomicity）事务被视为不可再分割的基本单位，事务的所有的操作序列要么都执行成功并提交，要么全部失败回滚
            一致性（consistency）从一个一致性状态转移到另一个一致性状态，事务执行成功，数据库转移成一个状态，不成功则保持原状态。
            隔离性（isolation）事务所做的修改在最终提交以前，对其他事务是不可见。
            持久性（durablity）事务一旦成功提交，其所做的修改会永久保存到数据库。
        使用框架：
            方式一：
                start transaction;
                执行操作序列;
                commit;
            方式二：
                begin：
                操作序列
                commit;

    索引：一种特殊的文件，包含这对数据表里所有记录的引用指针。通俗理解为目录，提高数据库的查询速度
        create index 索引名 on 表名(字段(长度))  非字符串不用指定长度
        加入索引后，查询将会借助索引，明显提高查询速度
        目的：提高查询效率，类比字典
        原理：通过建立b-tree对记录的地址组织和排序，在查询时通过某些遍历算法可以快速定位记录的位置
        主键和外键会自动创建索引
        查看索引：show index from 表名;
        删除索引：drop index 索引名 on 表名;
        缺点：会增加表更新和插入的负担
    主从：