Note Of Mysql

工作中常用的语法等，记录下

1、拼接字符串

## 拼接
SELECT concat("a","-","b")
#返回结果："a-b"
## 字符串拼接的聚合函数，group_concat以逗号分隔
SELECT user_id,count(address),group_concat(province)
FROM t_taobao_deliver_address
WHERE user_id='c0e9ad55d2774f31be0400784739cdcd'
GROUP BY user_id
#返回结果 c0e9ad55d2774f31be0400784739cdcd  2   湖北,江苏

2、增改删

## 查询服务器上存在的数据库
SHOW DATABASES
## 创建数据库,注意大小写
CREATE DATABASE scorecard_model
## 选择数据库,和hive一样
USE scorecard_model
## 查看数据库中的表
USE scorecard_model;
SHOW TABLES;
## 建表语句
USE scorecard_model;
DROP TABLE IF EXISTS `Newtable`;  
CREATE TABLE `Newtable` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `user_id` char(11),
  `price` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
## 显示表结构
DESCRIBE Newtable
## 插入数据
INSERT INTO Newtable (id, user_id, price)
VALUES
    (1, 'A', 4),
    (2, 'A', 2),
    (3, 'A', 7),
    (4, 'B', 1),
    (5, 'B', 10);
## 删除数据
## 删除表中id=1的数据
delete from Newtable where id=1;

3、实现row_number分组排序功能

## mysql和hive中有ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY price DESC)
## 以上面的newtable为例
## 按照price的大小对user_id分组降序排序
SELECT *,
(SELECT count(*)+1 FROM newtable WHERE user_id=t.user_id AND price >t.price)  AS index_sort
FROM 
(SELECT *
FROM newtable) as t

4、日期转换星期

## 返回星期几(对应1=周日,2=周一...)
SELECT DAYOFWEEK('2017-08-01')
## 返回星期几(对应0=周一,1=周二...)
SELECT DAYOFWEEK('2017-08-01')
## 返回date的月份中日期，在1到31范围内
SELECT DAYOFMONTH('2017-08-01')

5、查看执行计划

EXPLAIN sql

6、日期函数相关

# 字符串转日期
SELECT DATE_FORMAT( '2017-07-20', '%Y-%m-%d')
# 日期间隔 interval：DAY、MONTH、MINUTE 、SECOND 
# TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)   
SELECT TIMESTAMPDIFF(DAY ,'2017-07-20','2017-08-31')   # 42

7、分组拼接字符

## 返回结果:生成逗号分隔的字符串,类似:"北京，上海，南京"
SELECT group_concat(city)
FROM TABLE.A
GROUP BY user_id 

8、添加表注释

alter table test1 comment '修改后的表的注释';
alter table test1 modify column field_name int comment '修改后的字段注释';

9、空值NUILL变换

COALESCE(需要转换字段,替换的字段)
ifnull(需要转换字段,替换的字段)

10、按照指定分隔符切割字符串

## 逻辑大概是第几个分隔符之前的字符串
SELECT SUBSTRING_INDEX('湖北-黄石市','-',1) # 湖北
SELECT SUBSTRING_INDEX('湖北-黄石市','-',-1) # 黄石市

11、索引

## 添加索引加快检索速度
#### index_order_id索引名 sc_equipment_dim表名 order_id索引列
CREATE INDEX index_order_id ON sc_equipment_dim(order_id);

12、时间差

SELECT time_to_sec(timediff(t1.occ_tim,t2.occ_tim))