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)

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,793评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,567评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,342评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,825评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,814评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,680评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,033评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,687评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,175评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,668评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,775评论 1赞 332
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,419评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,020评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,978评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,206评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,092评论 2赞 351
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,510评论 2赞 343

推荐阅读更多精彩内容