最后更新时间: 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.
