mysql常用语法分享

最后更新时间: 2018-11-21 ，本文持续更新，每次调整会更新此处时间。

写在阅读前:技术文章是具有有效期的，在当下版本的软件可用，不代表未来技术更新迭代后还可以继续使用，比如一个版本的代码在另一个版本里面就不可用了，需要做些改造。使用时请以怀疑谨慎的态度对待一切，这一点是在看任何文章时都需要注意的。

目录

• mysql常用语法
  • 数据库操作
    • 查询所有表名
    • 查询所有表和表注释
    • 查询所有表及字段的属性信息(字段名/注释/字段类型/字段约束等)
    • 查询某个表字段的属性信息(字段名/注释/字段类型/字段约束等)
    • 查询数据库空间占用情况
  • DDL
    • 查看建表时的DDL
    • 创建表
    • 修改表注释
    • 修改字段注释
    • 增加列
    • 更改字段类型
    • 修改字段名
    • 索引
  • 表数据操作
    • 删除表数据/清理表空间
  • 函数操作
    • 时间函数
  • 进程,锁 操作
    • 查看进程，查看锁情况
    • kill进程
  • DML
    • 插入语句 insert into
  • 常用场景
    • 查看表中的重复数据

正文

mysql常用语法

这里记录一些常用的sql语法，包括一些使用频率比较高的，方便随时查询使用。

数据库操作

查询所有表名

show tables;

查询所有表和表注释

select 
    table_name 表名,
    table_comment 表注释
from information_schema.tables
where 
    table_schema='数据库名'
order by table_name
;

查询所有表及字段的属性信息(字段名/注释/字段类型/字段约束等)

select
    a.table_name 表名,
    a.table_comment 表注释,
    b.column_name 字段名,
    b.column_comment 字段注释,
    b.column_type 字段类型,
    b.column_key 约束
from information_schema.tables a
left join information_schema.columns b on a.table_name = b.table_name
where
    a.table_schema = '数据库名'
order by
    a.table_name
;

查询某个表字段的属性信息(字段名/注释/字段类型/字段约束等)

这一步就是在上面的查询中加入表名的条件，限定到具体的表。同时因为是单个表的，所以上面代码中的information_schema.tables 就有些冗余了，需要删除掉。结果如下：

select 
    column_name 字段名,
    column_comment 字段说明,
    column_type 字段类型,
    column_key 约束
from information_schema.columns
where 
    table_schema='数据库名'
and table_name='表名' 
;

或者用下面的语句，得到表的所有字段属性。

show full columns from 表名;

可以看出数据库的这些元数据信息都是存放在 information_schema 中，可以通过研究这个表自己发现更多有用的内容。

查询数据库空间占用情况

查询所有数据的大小

select concat(round(sum(DATA_LENGTH/1024/1024), 2),'MB')
as data from information_schema.tables;

这里是以MB的形式展现，也可以改为其他需要的形式展现。

查看指定数据库实例的大小，比如说数据库 testdb

select concat(round(sum(DATA_LENGTH/1024/1024), 2),'MB') as data,
  concat(round(sum(DATA_LENGTH/1024/1024/1024), 2),'GB') as data
from information_schema.tables where table_schema='testdb';

查看指定数据库的表的大小，比如说数据库 testdb 中的 table1 表

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB')as data
from information_schema.tables where table_schema='testdb'
and table_name='table1';

查看每个表的空间大小，按照数据量排序

select
  t.table_name,t.data,t.data1
from
(
select
a.table_name
,round(a.DATA_LENGTH/1024/1024, 2)as data
,concat(round(sum(a.DATA_LENGTH/1024/1024), 2),'MB') as data1
from information_schema.tables a
where a.table_schema='testdb'
group by a.table_name
)t
order by t.data desc
;

DDL

查看建表时的DDL

show create table 表名;

表名不需要加单引号,加了会报错。

创建表

一种通用示例如下：

create table if not exists tb1
(
    id bigint(20) not null auto_increment comment '主键',
    column1 bigint(20) not null default '0' comment '业务字段1',
    is_delete tinyint(1) unsigned not null default '0' comment '是否删除 0：正常 1：已删除',
    create_time timestamp not null default current_timestamp comment '创建时间',
    update_time timestamp not null default current_timestamp on update current_timestamp comment '更新时间',
    PRIMARY KEY (`id`),
    KEY `idx_is_delete` (`is_delete`) USING BTREE COMMENT '是否删除索引',
    KEY `idx_create_time` (`create_time`) USING BTREE COMMENT '创建时间索引',
    KEY `idx_update_time` (`update_time`) USING BTREE COMMENT '更新时间索引'
)comment  = 'tb1注释'
;

注意 id,is_delete,create_time,update_time 这四个字段是必须要加的。具体意义如下：
id 表的唯一主键，也是自增主键，确定数据的唯一性，同时也能表示一种顺序；
is_delete 是否删除字段，当删除数据时，将is_delete置为1，达到逻辑删除的效果。这样能完美的保存历史数据；同时当需要查询历史时，也能查看到历史数据；
create_time 标志着数据的创建时间，是时间上的一个起点；
update_time 标志着数据的最后更新时间，代表着时间上一个终点；同时还有一个用途，当每一份数据变更都存入数据仓库时，通过update_time便可以描绘出数据的完整生命周期。

修改表注释

alter table tb1 comment = '修改后的表注释信息';

修改字段注释

alter table tb1 modify column1 id int comment '主键ID';

增加列

alter table testtable add type bigint not null default 0 comment '类型' after id;

更改字段类型

alter table tb1 modify column1 double;

修改字段名

alter table tb1 change column1 column2 int;　

要指定新字段名及类型

或者采用下面的方式,同时修改字段类型、类型长度、默认值、注释等，或者修改单一元素，都是可选的。

alter  table tb1 modify column 字段名 数据类型 类型长度  默认值  注释; 
alter  table table1 modify  column column1 bigint default '0' comment '测试注释'; 

其中column1 是测试字段.

索引

查询表的现有索引

show index from table1;

增加索引(非唯一索引，只是作为提升查询性能使用)

alter table testtable add index idx_dt_date (`dt_date`) ;
---- 有时上面的无法添加,可使用下面的语句进行增加:
CREATE INDEX idx_update_time ON testtable (update_time);

也可以在建表时在主键后追加索引，这部分参照上面的建表语句。

增加主键id,设置自增主键

alter table tb add primary key(id);
alter table tb change id id int(10) not null auto_increment=1;

删除自增长的主键id

需要先删除自增长，再删除主键

alter table tb change id id int(10);//删除自增长
alter table tb drop primary key;//删除主建

表数据操作

删除表数据/清理表空间

如果是小表，可以下面语句直接操作：

# truncate table tb1;    -- 清空小表

truncate 是全表清空命令，truncate 和 delete 的细节区别后面会另外文档单独描述。

如果是大表需要分批删除(具体原因后面会讲到),下面以一个有时间字段dt为逻辑分区的表为例：

# select distinct dt from tb1; -- 查看分区
# select count(1) from tb1 where dt >= 201700101 and dt <20180101  ;  -- 查看数据量

-- 下面是大表分阶段删除
# delete from tb1 where dt >= 20160101 and dt <20170101;
# delete from tb1 where dt >= 20170101 and dt <20180101;
# delete from tb1 where dt >= 20180101 ;
*

表删除数据是需要时间的，不论是truncate还是delete,如果数据很大时，删除操作会造成数据库负载过高，直接卡住，导致其他任务无法进行。所以需要控制下删除的数据量。
此处以一年的数据为一次删除的量，这个量根据自己的数据库能力去测试选择，具体原则是以能够短时间内(eg: 3秒)迅速执行结束为标准划分，不影响数据库其他操作。

删除某段时间之前的数据：

-- 以删除七天之前的数据为例，即删除掉创建时间或者更新时间都不在近7天内的数据
delete from tb1
where create_time < date_sub(date_format(${dtparameter},'%Y-%m-%d 00:00:00'),interval 7 day)
or update_time < date_sub(date_format(${dtparameter},'%Y-%m-%d 00:00:00'),interval 7 day)
;

其中 ${dtparameter} 为时间参数，可以通过传参实现动态清理数据。

函数操作

时间函数

时间函数 yyyymmdd格式使用

select * from table1 where date_format(create_time,'%Y%m%d') >= str_to_date(20170101,'%Y%m%d')

时间减法

date_sub(date_format('20180101','%Y-%m-%d 00:00:00'),interval 7 day) 

对应的是20180101之前7天内的日期，将20180101换成参数的时候，就可以动态取近7天的数据。

时间比较

 date_format(dtdate1,'%Y%m%d') <= date_sub(date_format(${dtdate2},'%Y-%m-%d),interval 90 day)

小于90天之前的日期

进程,锁 操作

查看进程，查看锁情况

show processlist;

或者下面的形式查看

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;

锁排查

SELECT  
      ifnull(trx_id, 'null'),  
    ifnull(trx_state,'null'),  
      ifnull(trx_started,'null'),  
    ifnull(trx_wait_started,'null'),  
    ifnull(trx_weight,'null'),  
    ifnull(trx_mysql_thread_id,'null'),  
    ifnull(trx_query,'null'),  
    ifnull(trx_operation_state,'null'),  
    ifnull(TRX_TABLES_IN_USE,'null'),  
    ifnull(trx_rows_locked,'null'),  
    ifnull(trx_rows_modified,'null'),  
    ifnull(trx_tables_locked,'null')  
FROM  
    information_schema.innodb_trx;

kill进程

kill 554297054;  -- 554297054是进程ID

DML

插入语句 insert into

手动插入数据

单条插入

insert into  table1 (id, type,name)
           VALUES
             (10,1,'测试1');

多条插入

insert into  table1 (id, type,name)
           VALUES
             (10,1,'测试1');
             (11,2,'测试2');

常用场景

查看表中的重复数据

下面是个判断重复数据的示例

这里biz_id,biz_name,type是业务联合主键，三者确定唯一性。
1.对比总数,检测是否有重复值
select count(1),count(distinct biz_id,biz_name,type) from table1;
2.查询具体的重复数据id；
select  biz_id,biz_name,type,count(1) as cn  
from table1
group by biz_id,biz_name,type
having cn > 1
3.根据重复数据id 查询重复数据详情，判断重复原因。
select * from table1
where biz_id = 1  and biz_name = 2 and type = 1 ;
 ;

到此结束 this is end.