[Mysql]Mysql常用命令

1. 常规操作

进入数据库

use market;

改变字符编码

charset utf8;

导出数据

mysql \
-h 10.0.2.54 \
-P 3307 \
-u marketuser \
-e "use market; charset utf8; select * from RPT_ONLINE_KPI;" \
-p | sed 's/\t/,/g' > ./rpt_online_kpi_2015-12-11.txt

2. DDL操作

修改表的自增id值

alter table PMS_TP_CONFIG AUTO_INCREMENT=1;

新增字段

新增的字段添加到所有字段的最后

alter table PMS_TP_CONFIG add slot_counts int NOT NULL DEFAULT 0 COMMENT '坑位总数'

在指定字段之后新增字段

alter table PMS_TP_CONFIG add slot_counts int NOT NULL DEFAULT 0 COMMENT '坑位总数' after tpa_name;

删除字段

alter table PMS_TP_CONFIG drop column slot_counts;

修改字段类型

alter table PMS_COMBINEORDER_PRODUCT_RECOMMEND 
change column REC_PRODS REC_PRODS varchar(2048) not null comment '推荐商品';

建表

自增id、日期自动更新

-- Table "PMS_TP_CONFIG" DDL

DROP TABLE `PMS_TP_CONFIG`;
CREATE TABLE `PMS_TP_CONFIG` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `platform` varchar(16) NOT NULL COMMENT '终端平台',
  `page_id` int(16) NOT NULL COMMENT '页面id',
  `page_name` varchar(128) NOT NULL COMMENT '页面名称',
  `tpa` varchar(16) DEFAULT NULL COMMENT 'tpa',
  `tpa_name` varchar(128) DEFAULT NULL COMMENT 'tpa名称',
  `slot_counts` int(16) NOT NULL DEFAULT 0 COMMENT '坑位总数',
  `is_delete` int(4) NOT NULL DEFAULT 0 COMMENT '标识记录是否无效，0代表记录有效，1代表记录无效',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据刷新时间',
  PRIMARY KEY (`id`),
  KEY `page_id` (`page_id`),
  KEY `tpa` (`tpa`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='精准化自动打点配置表';

建立分区表

-- Table "PMS_ONLINE_KPI_REPORT" DDL

DROP TABLE `PMS_ONLINE_KPI_REPORT`;
CREATE TABLE `PMS_ONLINE_KPI_REPORT` (
  `id`              bigint(20)    NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `page_id`         bigint(16)    NOT NULL COMMENT '页面id',
  `section_id`      varchar(64)   NOT NULL COMMENT '栏位id',
  `algorithm_id`    bigint(16)    NOT NULL COMMENT '算法id',
  `platform`        varchar(16)   NOT NULL COMMENT '平台',
  `kpi_id`          bigint(8)     NOT NULL COMMENT '监控指标的类型',
  `kpi_value`       bigint(16)    NOT NULL COMMENT '监控指标的值',
  `update_time`     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据刷新时间',
  PRIMARY KEY (`id`,`update_time`),
  KEY `comb` (`page_id`,`section_id`,`algorithm_id`)
) 
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='精准化实时KPI指标监控表'
PARTITION BY RANGE ( UNIX_TIMESTAMP(update_time) ) (
  PARTITION pmin VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-01 00:00:00') ),
  PARTITION p201512 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-01-01 00:00:00') ),
  PARTITION p201601 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-02-01 00:00:00') ),
  PARTITION p201602 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-03-01 00:00:00') ),
  PARTITION p201603 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-04-01 00:00:00') ),
  PARTITION p201604 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-05-01 00:00:00') ),
  PARTITION p201605 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-06-01 00:00:00') ),
  PARTITION p201606 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-07-01 00:00:00') ),
  PARTITION p201607 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-08-01 00:00:00') ),
  PARTITION p201608 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-09-01 00:00:00') ),
  PARTITION p201609 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-10-01 00:00:00') ),
  PARTITION p201610 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-11-01 00:00:00') ),
  PARTITION p201611 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-12-01 00:00:00') ),
  PARTITION p201612 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00') ),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

3. DML操作

新增表注释

alter table pms_test COMMENT='推荐搭配表，用于详情页推荐搭配栏位';

显示表结构

show create table PMS_TP_CONFIG;

清空数据表

truncate table PMS_TP_CONFIG;

等同于delete from PMS_TP_CONFIG where 1=1;truncate属于完全清空，操作需要谨慎

插入数据

insert into PMS_TP_CONFIG(id,platform,page_id,page_name,tpa,tpa_name,slot_counts,is_delete)
values('19','APP','20000','APP购物车页','8_1','APP购物车页_无商品时猜你喜欢','20','0');

删除数据

delete from PMS_TP_CONFIG where id in (16,17);

更新数据

update PMS_TP_CONFIG set slot_counts=10 where id = 2;

将A表数据导入导B表

INSERT INTO PMS_ONLINE_KPI_REPORT (
    page_id,
    section_id,
    algorithm_id,
    platform,
    kpi_id,
    kpi_value,
    update_time
) SELECT
    page_id,
    section_id,
    algorithm_id,
    platform,
    kpi_id,
    kpi_value,
    update_time
  FROM
    RPT_ONLINE_KPI;

索引

查询索引

show index from PMS_ONLINE_KPI_REPORT;

新增索引

新增唯一索引

alter table pms_test add unique product_id(product_id);

新增联合索引

alter table `PMS_ONLINE_KPI_REPORT` add index `comb`(`kpi_id`,`update_time`);

删除索引

drop index comb on PMS_ONLINE_KPI_REPORT;