1. 表结构创建维护

修改 primary key

# 删除原有主键
alter table tablename drop PRIMARY KEY
  
# 重新创建主键
alter table tablename ADD PRIMARY KEY('col_name')

添加索引

create index `idx_add_time` on ods.ods_oms_product (add_time);
  
ALTER TABLE ods.ods_ad原始表 ADD KEY INDEX  idx_date(`日期`);

添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

添加全文索引

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

添加字段

ALTER TABLE table_name ADD COLUMN column_name
VARCHAR(100) DEFAULT NULL COMMENT '新加字段' AFTER old_column;

添加唯一键

alter table question_tag_map add unique key `qtag2` (`question_id`,`question_tag_id`);

删除唯一键

alter table question_tag_map drop index `qtag2`;

复制表:
```
create table tb_test01 like tb_test02;
```

复制表数据:

insert into tb_test01 select * from tb_test02;

修改表名

alter table ods.`ods_oms_b2b_stock_out_order_detail_new` rename [to|as]  ods.`ods_oms_b2b_stock_out_order_detail`

修改列名 / 重命名字段

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型(长度);

添加时间戳字段

alter table quant_stk_calc_d_wxcp add update_time timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

2. 增删改查

2.1. 联合查询

多表联查

SELECT * FROM oms_biz.oms_stock_loan_out st1, oms_biz.oms_stock_loan_out_detail st2 
  where st1.stock_loan_out_id = st2.stock_loan_out_id;

多表查询相同字段名

SELECT st1.modified_time, st2.modified_time FROM oms_biz.oms_stock_loan_out st1, oms_biz.oms_stock_loan_out_detail st2;

2.2. CAST 函数

select cast('01-11-11' as DATE);

3. 清洗转换

3.1. 数据探查

查看某字段不重复的全部值

  SELECT DISTINCT(字段) FROM test.test;

3.2. 数据校验

正则表达式：检测科学计数法

  SELECT * FROM test.test WHERE 日期 regexp '[E|e]+';

检测空值

  SELECT * FROM test.test WHERE ISNULL(非空字段)=1;

3.3. 数据转换

SQL 替换字符串

update [表名] set 字段名 = replace(与前面一样的字段名,'原本内容','想要替换成什么')

3.4. 日期计算

对比两表的 datetime 字段是否在同一天

SELECT * FROM oms_biz.oms_stock_loan_out st1, oms_biz.oms_stock_loan_out_detail st2 
  where TO_DAYS(st1.modified_time) != TO_DAYS(st2.modified_time);

日期时间字段类型 datetime 转换为日期（年-月-日）的方法
- 方法1，使用DATE_FORMAT
```
DATE_FORMAT(time, '%Y-%m-%d')
```
- 方法2，使用 CAST
```
CAST(time AS   DATE)
```

查询指定日期

– 今天

  SELECT * FROM 表名 WHERE TO_DAYS(时间字段名) = TO_DAYS(NOW());

– 昨天

  SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS( 时间字段名) <= 1;

– 近7天

  SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名);

– 近30天

  SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名);

– 本月

  SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m’ );

– 上一月

  SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ ) , date_format( 时间字段名, ‘%Y%m’ ) ) =1;

– 查询本季度数据

  Select * from 表名 where QUARTER(时间字段名)=QUARTER(now());

– 查询上季度数据

  Select * from 表名 where QUARTER(时间字段名)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

– 查询本年数据

  Select * from 表名 where YEAR(时间字段名)=YEAR(NOW());

– 查询上年数据

  Select * from 表名 where year(时间字段名)=year(date_sub(now(),interval 1 year));

– 查询当前这周的数据

  SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段名,’%Y-%m-%d’)) = YEARWEEK(now());

– 查询上周的数据

  SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段名,’%Y-%m-%d’)) = YEARWEEK(now())-1;

– 查询上个月的数据

  Select * from 表名 where date_format(时间字段名,’%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),’%Y-%m’);

  Select * from 表名 where DATE_FORMAT(时间字段名,’%Y%m’) = DATE_FORMAT(CURDATE(),’%Y%m’);

  Select * from 表名 where WEEKOFYEAR(FROM_UNIXTIME(时间字段名,’%y-%m-%d’)) = WEEKOFYEAR(now());

  Select * from 表名 where MONTH(FROM_UNIXTIME(时间字段名,’%y-%m-%d’)) = MONTH(now());

  Select * from 表名 where YEAR(FROM_UNIXTIME(时间字段名,’%y-%m-%d’)) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = MONTH(now());

  Select * from 表名 where 时间字段名 between 上月最后一天 and 下月第一天;

– 查询当前月份的数据

  Select * from 表名 where date_format(时间字段名,’%Y-%m’)=date_format(now(),’%Y-%m’);

– 查询距离当前现在6个月的数据

  Select * from 表名 where 时间字段名 between date_sub(now(),interval 6 month) and now();

– 查询某个月的数据(查询18年10月份数据)

  Select * from 表名 where date_format(时间字段名,’%Y-%m’)=‘2018-10’;

  Select * from 表名 where date_format(时间字段名,’%Y-%m’)=date_format(‘2018-10-05’,’%Y-%m’);

4. 管理配置

卸载&清除残留文件

sudo apt purge mysql-*
  
sudo rm -rf /etc/mysql/ /var/lib/mysql
  
sudo apt autoremove 
  
sudo apt autoclean
  
或者：
  
sudo apt-get remove mysql-*
  
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

修改密码

use mysql;
  
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; --创建远程root用户并设置密码
  
flush privileges;

查看端口号
```
show global variables like 'port';
```

4. 异常处理

Invalid for this platform protocol requested
- 问题：
  - 使用 workbench 连接 MySQL 数据库，workbench 客户端连接失败，
- 报错：
```
Invalid for this platform protocol requested(MYSQL_PROTOCOL_SOCKET)
```
- 处理方法：
  - 删除现有的连接，从头开始、重新创建一个连接。

MySQL 常用命令汇总

表维护 & DB运维