美文网首页
Mysql入门操作

Mysql入门操作

作者: 大雨滂沱在关外 | 来源:发表于2020-10-30 09:28 被阅读0次

    Mysql 基本概念

    Mysql Server-->DB1:create
    Mysql Server-->DB2:create
    DB1-->table1:create
    DB1-->table2:create
    table1-->row1:create
    table1-->row2:create
    

    Mysql Server

    graph LR
    A[Mysql Server] -->B(DB1)
    A-->C(DB2)
        C -->D[table1]
        C -->E[table2]
            D -->F(row1)
            D -->G(row2)
    
    id name remark
    1 zxm
    2 zzm
    30000000 sgf

    1.select * from tabe;

    2.select * from table where id =1 ;

    3.select * from table group by name having name = 'zxm';

    4.select * from table where id in (select id from table2);

    5.select * from table jion table2 on table.id = table2.id;

    6.select count(*) from table;

    7.select count(1) from table;

    8.select count(id) from table;

    SQL 查询流程

    Mysqlclient=>start: Mysql client
    cache=>operation: 查询缓存
    result=>condition: 没有?
    resolver=>operation: 解析器
    issyntaxerror=>condition: 语法解析成功?
    ispreprocessorerror=>condition: 预处理错误?
    syntaxerror=>operation: 语法错误 
    resolvetree=>operation: 解析树
    preprocessor=>operation: 预处理器 
    newresolvetree=>operation: 新解析树
    reactor=>operation: 查询优化器
    explain=>operation: 执行计划
    engine=>operation: 查询执行引擎
    api=>operation: 存储引擎
    end=>end: 查询结果
    error=>end: 语法解析错误
    preprocessorerror=>end: 预处理错误(列名不匹配,没有表权限)
    
    Mysqlclient->cache->result
    result(yes)->resolver->issyntaxerror
    issyntaxerror(yes)->resolvetree
    issyntaxerror(no)->error
    resolvetree->preprocessor->ispreprocessorerror
    ispreprocessorerror(yes)->newresolvetree
    ispreprocessorerror(no)->preprocessorerror
    newresolvetree->reactor
    reactor->explain
    explain->engine->api->end
    result(no)->end
    

    SQL优化

    graph LR
    A[Mysql Server] -->B(DB1)
    A-->C(DB2)
        C -->D[table1]
        C -->E[table2]
            D <-->F(百万数据)
            F -->H(索引)
            H -->L(复合索引失效 最左前缀原则)
            F -->I(分区)
            I -->M(什么字段分区 时间)
    

    1.创建表的时候

    create table user( 
    id bigint(64) primary key auto_increment, 
    username varchar(50) not null comment '用户名', 
    password varchar(50) not null comment '密码', 
    phone varchar(20) not null comment '电话', 
    create_by varchar(50) null comment '创建人', 
    create_time timestamp not null default CURRENT_TIMESTAMP(), 
    update_by varchar(50) null comment '修改人', 
    update_time timestamp null on update current_timestamp()                       
    )engine = innodb;
    create table role( 
    id bigint(64) primary key auto_increment, 
    rolename varchar(50) null comment '角色名称' 
    );    
    create table user_role( 
    id bigint(64) primary key auto_increment, 
    user_id bigint(64), 
    role_id bigint(64) 
    );     
    

    ​ 1.创建索引(where ,group by)

    ​ 2.表分区

    2.全表查询

    ​ 1.in

    start=>start: 开始
    user_role=>operation: user_role
    subquery2=>operation: subquery2
    user=>operation: user   
    end=>end: 结束
    start->user_role
    user_role->subquery2
    subquery2->user
    user->end
    
    

    ​ 2.where 1 = 1

    ​ 3.where name like %${name}

    ​ 4.where substr(name) like ${name}

    ​ 5.where name is null

    ​ 6.or

    3.执行计划

    explain select * from table;

    explain select * from table where id = 1;

    explain select * from table join table1 on table1.id = table.id;

    explain select * from table group by name having name = 'zxm';

    索引

    新增一条记录,mysql要做神马操作呢?

    相关文章

      网友评论

          本文标题:Mysql入门操作

          本文链接:https://www.haomeiwen.com/subject/mwyczctx.html