mysql知识点

mysql总结

基础概念

什么是mysql以及描述

mysql(dbms)就是一个操作文件的软件。对于数据库我们可以把它作为一个文件夹,而表就可以看作是一个文件(excel、csv)等等。

mysql属于什么类型的数据库

数据库分为关系型数据库和非关系型数据库。其中关系型数据库包括:sql server 、sqlite、mysql、orcle、db2等等;非关系型数据库包括:mongodb、redis等等。

基本操作

数据库

  • 创建数据库
    • create databases name default character set ‘utf8‘
  • 删除数据库
    • drop database name

用户

  • 创建
    • create user name%‘ip‘ indentified by password
  • 授权
    • grant viliges on database[.table] to user
  • 撤权
    • revoke viliges on database[.table] from user
  • 修改密码
    • set password for user = new_password

  • 创建表

    • create table name(id int not null auto_increment primary key, name char(10)) engine = innodb default character set ‘utf8‘;
  • 删除表

    • drop table name
    • 当涉及到多个相关联的表时,先删除有外键的表。
  • 外键

    • 一种约束,提高数据的安全性,分别查找,节省空间。

    • ` create table tb1(
          id int not null auto_increment primary key, 
          name char(10), 
          class_id int, 
          constraint foreign key (tb1.class_id) references tb2(id)
      ) engine = innodb character set ‘utf8‘;`
      
  • 索引

    • 创建

      • create index ix_name on table(column_name);
        
    • 删除

      • drop index ix_name on table;
  • 数据类型

    • 整型
      • int tinyint bigint
    • 浮点数
      • float double decimal
    • 字符
      • varchar char text
    • 日期
      • date datetime
  • 集合

    • 通过集合,在创建表的时候就可以使用集合的子集。

    • ` create table tb1(
          id int not null auto_increment primary key, 
          name char(10), 
          style set(‘red‘, ‘big‘, ‘blue‘,‘small‘)
      ) engine = innodb character set ‘utf8‘;`
      
  • 枚举

    •  ` create table tb1(    
           id int not null auto_increment primary key,     
           name char(10),     
           gender enum(‘男‘, ‘女‘)
       ) engine = innodb character set ‘utf8‘;`
      

      • insert into table(age, name) values(age_value, name_value);
      • insert into table(age, name) select age, name from table2;
        
    • 删除表
      • drop table name;
    • 删除内容
      • delete from table;
      • truncate from table;
    • 单表
      • select * from table;
    • 多表
      • select * from tb1, tb2 where tb1.id = tb2.id;
      • select * from tb1 left join tb2 on tb1.id = tb2.id;,inner join、left join、right join。left join 以左边的表格为基准在右边的的表格中进行匹配,若右边的表格中没有则为null。inner join作用类似于left join,但是它会就见值为null的行给屏蔽了。
    • update table_name set age = ? where id = ?

高级操作

触发器

  • 创建(before -->new/after -->odd)

    • delimiter //
      create trigger name  on table before/after insert/update/delete for each row
      begin 
      select * from table2;
      end //
      delimiter ;
      
  • 删除

    • drop trigger name;
      

存储过程

存储过程就是将多个操作语句封装在一起,通过一个名字调用。

  • 创建

    • delimiter //
      create procedure name()
      begin 
      select * from table;
      end //
      delimiter ;
      
  • 删除

    • drop procedure name;
      
  • 使用

    • call name();
      
  • 高级(含参数)

    • in

    • out

    • inout

    • delimiter //
      create procedure name(
          in var1 int,
          out var2 int,
          inout var3 int
      )
      begin 
      declare var4 int;
      set var4 = xxxx;
      end //
      delimiter ;
      

视图

  • 创建

    • create view name as (
      select * from table;
      )
      
  • 使用

    • select * from name;
      
  • 删除

    • drop view name;
      

分页性能优化

  • 数据较少时
    • select * from table limit 10;
  • 当数据较大时(当前页面的最大id -->max_id, 最小id-->min_id
    • 下一页
      • select * from table where id > max_id limit 10;
    • 上一页
      • select * from table where id < min_id order by id desc limit 10;
    • 特殊情况,当前处于192 193 194 195,要194的
      • select * from (select * from table where id > 192.max_id limit 20) order by id desc limit 10;

导致不能命中索引的情况

  • like
  • or
  • order by(主键除外)
  • >=(主键除外)
  • !=(主键除外)
  • 类型不一致(主键除外)

不推荐的情况

  • select *
  • 多使用count(1) 代替count(*)

设置变量

  • 自动增长步长

    • set session auto_increment_increment = xxx;基于会话
  • 自动增长的起始值

    • set session auto_increment_offset = xxx;
  • 设置变量

    • set session/global varible = xxx;

python操作数据库

基本

  • pymysql

    • import pymysql
      
      conn = pymysql 
      cursor = 
      sql = ‘select * from table‘
      cursor.execute(sql, [])
      result = cursor.fetchall()
      cursor.close()
      conn.close()
      

高级

  • sqlalchemy

mysql知识点

上一篇:[转]MyBatis - MyBatis使用log4j2显示sql和结果集


下一篇:docker安装oracle11g