2019-04-24

mysql常见的数据库引擎和区别：

MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。

　　InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。(提供行级锁

　　BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

　　Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

　　Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

　　Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

　　Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

　　Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

　　Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。

关系型数据库：Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL等

:指采用了关系模型来组织数据的数据库。简单来说，关系模式就是二维表格模型。

优点 （1）容易理解，二维表的结构非常贴近现实世界，二维表格，容易理解。

    （2）使用方便，通用的sql语句使得操作关系型数据库非常方便。

    （3）易于维护，数据库的ACID属性，大大降低了数据冗余和数据不一致的概率。

非关系型数据库：NoSql、Cloudant、MongoDb、redis、HBase

        优势：1. 性能NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。

              2. 可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

设置自增的起始值：

          alter table tablename auto_increment = 0;

    设置自增的步长：

      mysql

          基于会话级别：

          show session variables like 'auto_inc%'  查看全局变量

          set session auto_increment_increment = 2; 设置会话的步长

          set session auto_increment_offset = 10;  设置起始值

        全局级别：

          show global variables like 'auto_inc%' 

          set global auto_increment_increment = 200;设置全局的步长

          set global auto_increment_offset = 10;

    外键的变种：

        唯一索引

    不等于：   

    select * from tb1 where id !=1;

    select * from tb2 where id <> 1；

    select * from tb2 where id in (1,5,12);

    select * from tb2 where id not in (1,5,13);

    select *from tb2 where id between 5 and 12;//取到的是闭区间

    select * from tb2 where id in (select id from tb1);

    select * from tb2 where name like "a%"                 

    %表示多个字符  _是一个字符，%a%只要有a就行

    select * from tb12 limit 10;    //前几条

    select * from tb2    limit 1,1          //起始位置，往后取几条数据

    select * from tb2 limit 10 offset 20;    //limit后是取几条，offfset从第几条取

排序：

    select * from tb2 order by id desc(倒序)/asc（正序） limit 10; 取后10条数据

分组：

    select * from tb2 group by id ;

    sum max min avg count ;

    having

连表操作：

    select * from tb1 ,tb2 where tb1.id = tb2.id;

    select * from tb1 left join tb2 on tb1.id = tb2.id;//左侧表全部显示

    select * from tb1 right join tb2 on tb1.id = tb2.id;//右侧表全部显示即t b2

    select * from tb1 inner join tb2 on tb1.id = tb2.id;  //将出现null的一行隐藏

    临时表：

    select sid from (select * from score where num > 60) as B;

pymysql模块：

pip3 install pymysql

触发器： 当对某表的做增删改操作时，使用触发器自定义关联行为，

create triggr t1(视图名称) before insert on student(表名) for  each row

begin

insert into teacher(tname) values('sdaas')

values值New.name 为插入时的值

        Old.name  为插入，删除的值

end

修改终止符：delimiter //

          delimiter ;

函数：

内置函数：

执行函数

    时间格式化：

自定义函数：

存储过程：保存在mysql上的一个别名，一堆sql语句，调用别名进行使用

    保存在数据库中

  创建存储过程

  delimiter //

CREATE PROCEDURE p1()

BEGIN

select * from student;

select * from teacher;

END

delimiter ;

执行存储过程

call p1()

方式一：

mysql：存储过程

程序：调用存储过程

方式二：

  mysql：程序员

  程序：sql语句

方式三：

mysql：程序员

ORM框架：类和对象

1、存储过程传参：(in ,out, inout)

create PROCEDURE p(

IN  N1 int,

IN N2 INT)

BEGIN

select * from user where sid >N1；

END

2、参数out：

delimiter //

create PROCEDURE p2(

out n2 int,用于标识存储过程的执行结果

in n1 int)

begin

set n2 = 123123;

select * from student where sid >n1;

END //

delimiter ;

                3、事务

索引：

作用：

  约束

  加速查找

索引：

普通索引（加速查找）

主键索引（加速查找，不能为空，不能重复）

唯一索引（加速查找，不能重复）

联合索引（联合唯一）（多列索引）

  --联合主键索引

  --联合唯一索引

  --联合普通索引

加速查找:

快：

select * from tb where name = "asdf"

假设：

id name email

无索引:从前到后一次查找

  索引：

  id 创建额外文件（某种格式存储）

  name email 创建额外文件（某种格式存储）

索引种类：

hash索引：

单值索引快

范围索引慢

BTree索引（常用）：

原理：二叉树

    建立索引

    create index 索引名 on 表名（字段）  -- 针对字段进行建立索引

    drop index 索引名

    缺点：额外的文件保存特殊的数据结构

    查询快，插入更新删除慢

    命中索引：

    select * from user where email ="asdf"

    普通索引（加速查找）：

    --create index 索引名 on 表名（字段）  -- 针对字段进行建立索引

    --drop index 索引名

    唯一索引

    --create unique index 索引名 on 表名（字段）  -- 针对字段进行建立索引

    --drop unique index 索引名

  联合索引：（普通的索引）

      --create index 索引名 on 表名（字段1，字段2）  -- 针对字段进行建立索引

    --drop index 索引名

    索引名词补充：

          覆盖索引：在创建的索引文件中直接将数据取到

          select id from user where id = "111"

          索引合并：把多个单列索引合并使用

          select * from user where id="111" and email="aaa"

          最左前缀匹配：需要 

          create index ix_name on user (id,name)

          select * from user where id ="1" and name= "asdf"

    组合索引效率 > 索引合并：把多个单列索引合并使用

              --（name,id)

              select * from user where name = "sss" and id ='11'

              --name

              --id

              select * from user where id ="sss" and name= "ddfd"

    频繁查找的列创建索引：

    -创建索引

    -命中索引

            - like '%xx'

    select * from tb1 where name like '%cn';

- 使用函数

    select * from tb1 where reverse(name) = 'wupeiqi';

- or

    select * from tb1 where nid = 1 or email = 'seven@live.com';

    特别的：当or条件中有未建立索引的列才失效，以下会走索引

            select * from tb1 where nid = 1 or name = 'seven';

            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'

- 类型不一致

    如果列是字符串类型，传入条件是必须用引号引起来，不然...

    select * from tb1 where name = 999;

- !=

    select * from tb1 where name != 'alex'

    特别的：如果是主键，则还是会走索引

      select * from tb1 where nid != 123

- >

    select * from tb1 where name > 'alex'

    特别的：如果是主键或索引是整数类型，则还是会走索引

        select * from tb1 where nid > 123

        select * from tb1 where num > 123

- order by

    select email from tb1 order by name desc;

    当根据索引排序时候，选择的映射如果不是索引，则不走索引

    特别的：如果对主键排序，则还是走索引：

        select * from tb1 order by nid desc;

- 组合索引最左前缀

    如果组合索引为：(name,email)

    name and email      -- 使用索引

    name                -- 使用索引

    email                -- 不使用索引

3、时间

  执行计划,让mysql预估执行操作

  explain  预估：

  快：select * from user where id ='2'

  type:ref(索引扫描)

  慢：select * from user where name  = "alex"

  type:all(全表扫描)

4、DBA工作

慢日志

-执行时间 > 10

-未命中索引

-日志路径

配置：

内存：

  show variable like '%query%'

  set global 变量名 = 值

配置文件

--mysql的配置文件

mysql --defaults-file ='D:\my.conf'

    修改配置文件之后，需要重启服务

    5、分页：

    select * from user limit

    翻的页越大，越慢，因为月在后面扫描的记录条数就越长，就越慢

        解决方法一：索引表里面扫描

        select * from user where id in (

            select id from user limit 200,10)

方法二:select * from user where id >200 order by desc  limit 10;

记录当前页的最大或者最小的id，

页面只有上一页下一页：

当一类函数公用同样的参数对象的时候，可以转变为类进行  --分类

面向对象：数据和逻辑（属性和行为）组合在一起

函数编程：数据和逻辑分离

ORM框架:SQLAlchemy

-作用

提供简单的规则

自动转化为sql语句

-DBfirst 手动建表

-Code first 手动创建类和数据库，通过orm框架，自动生成表

创建数据库

连接数据库

类转换sql语句

子查询：

--query(表名)  ：要查询的表名  ，filter是条件限制，filter（）里面两个条件的话，是条件之间是and

#　条件

ret = session.query(Users).filter_by(name='alex').all()

ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()

ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()

--

ret = session.query(Users).filter(Users.id.in_([1,3,4])).all() -- 加下划线  这条语句的在user.id是1 3 4 中查询

ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() --  ~非（not）的意思  这条语句的在user.id是1 3 4 中查询

  --filter_by :  内部子查询 的限制条件                               

ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()

from sqlalchemy import and_, or_

--引入and_,or_

ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()

ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()

ret = session.query(Users).filter(

    or_(

        Users.id < 2,

        and_(Users.name == 'eric', Users.id > 3),

        Users.extra != ""

    )).all()

# 通配符

ret = session.query(Users).filter(Users.name.like('e%')).all()

ret = session.query(Users).filter(~Users.name.like('e%')).all()

# 限制(分页，直接切片)

ret = session.query(Users)[1:2]

# 排序

--order_by desc ,asc

ret = session.query(Users).order_by(Users.name.desc()).all()

ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组(group_by)

from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()

ret = session.query(

    func.max(Users.id),

    func.sum(Users.id),

    func.min(Users.id)).group_by(Users.name).all()

ret = session.query(

    func.max(Users.id),

    func.sum(Users.id),

    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

          --having  根据分组之后，进行二次筛选

# 连表

--query要查的两个表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all()  --默认为inner_join

ret = session.query(Person).join(Favor, isouter=True).all()  --未outer join

# 组合

q1 = session.query(Users.name).filter(Users.id > 2)

q2 = session.query(Favor.caption).filter(Favor.nid < 2)

ret = q1.union(q2).all()  #去重

q1 = session.query(Users.name).filter(Users.id > 2)

q2 = session.query(Favor.caption).filter(Favor.nid < 2)

ret = q1.union_all(q2).all()  #不去重

#临时表

--select * from (select * from A) as B

q1 = session.query(Users.name).filter(Users.id > 2).subquery()

ret = session.query(q1).all()

print(ret)