mysql知识梳理

mysql知识梳理

基本命令

约定 数据库: my_db 表:my_table

• 数据库操作

  • //创建库
    create database [if not exists] my_db;
    
    //查看库
    show databases;
    

• 表操作

  • //创建表
    create table [if not exists] my_table(
    	id int unsigned auto_increment,
        name varchar(20) not null
    )engine=InnoDB default charset=utf8;
    

• 增删改查

  • insert into my_table [(id,name)] values (3,'chill');
    delete from my_table where id = 1;
    update my_table set name = 'modifyName' where id = 1;
    select * from my_table where id = 1;
    

• 索引 键

  • show keys from my_table
    show index from my_table
    
    //
    SELECT TABLE_NAME,COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_NAME = 'my_table'
    

• 修改表

  • //修改表选项
    alter table my_table ENGINE=MYISAM;
    //增加字段
    alter table my_table add address varchar;
    //修改表字段属性
    alter table my_table modify name text;
    //修改表字段
    alter table my_table change address new_address varchar;
    //增加普通索引
    alter table my_table add index index_name id;
    //删除索引
    alter table my_table drop index index_name;
    //增加唯一索引
    alter table my_table add unique unique_index_name id;
    //增加主键(唯一)
    alter table my_table add primary key pk_id id;
    //删除主键
    alter table my_table drop primary key;
    

• 增删改查

  • //最简例子 
    insert into my_table [(id,name)] values (3,'chill');
    delete from my_table where id = 1;
    update my_table set name = 'modifyName' where id = 1;
    select * from my_table where id = 1;
    //公式
    SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT
    

• 复杂查询

  • //union 可以代替in(value1,value2...) 效率可能更高
    SELECT select_expr UNION [ALL|DISTINCT] SELECT select_expr
    //内连接 连接结果不出现空行
    select * from my_table1 m1 [inner] join my_table2 m2 on m1.id = m2.id;
    //外连接  数据不存在也会出现在结果集
    select * from my_table1 m1 [left|right] outer join my_table2 m2 on m1.id =m2.id;
    

• 聚合函数

  • count()
    sum();
    max();
    min();
    avg();
    group_concat()