SQL 语句中 “意想不到” 的操作

1. sql 中字符串截取的函数

原文链接:https://www.cnblogs.com/duanc/archive/2018/04/09/8760372.html

1.1 LEFT( createDate, 10 ) 从左边截取到第十个字符串

注意：起始位置的下标为 1 开始
如下图所示，有的时候我们想要的时间是年月日，而不需要时分秒。

SELECT LEFT
	( createDate, 10 ) 
FROM
	`joblevel`

结果如下：

 
 
 

1.2 RIGHT( createDate, 8 ) 从右边截取到第八个字符串

SELECT RIGHT
	( createDate, 8 ) 
FROM
	`joblevel`

 
 
 

1.3 SUBSTRING(name,5,3) 截取name这个字段 从第五个字符开始 只截取之后的3个字符

注意 : 含头不含尾

SELECT SUBSTRING('成都融资事业部',5,3)

结果：事业部

 
 
 

1.4 SUBSTRING(name,3) 截取name这个字段 从第三个字符开始，之后的所有个字符

SELECT SUBSTRING('成都融资事业部',3)

结果：融资事业部

 
 
 

1.5 SUBSTRING(name, -4) 截取name这个字段的第 4 个字符位置（倒数）开始取，直到结束

SELECT SUBSTRING('成都融资事业部',-4)

结果：资事业部

 
 
 

1.6 SUBSTRING(name, -4，2) 截取name这个字段的第 4 个字符位置（倒数）开始取，只截取之后的2个字符

SELECT SUBSTRING('成都融资事业部',-4,2)

结果：资事

注意：我们注意到在函数 substring(str,pos, len)中， pos 可以是负值，但 len 不能取负值。

 
 
 

1.7 substring_index(‘www.baidu.com’, ‘.’, 2) 截取第二个 ‘.’ 之前的所有字符

SELECT substring_index('www.baidu.com', '.', 2)

结果：www.baidu

 
 
 

1.8 substring_index(‘www.baidu.com’, ‘.’, -2) 截取第二个 ‘.’ （倒数）之后的所有字符

SELECT substring_index('www.baidu.com', '.', -2)

结果：baidu.com

 
 
 

1.9 SUBSTR(name, 1, CHAR_LENGTH(name)-3) 截取name字段，取除name字段后三位的所有字符

SELECT SUBSTR('成都融资事业部', 1, CHAR_LENGTH('成都融资事业部')-3) 

结果：成都融资

2. sql 语句中保留小数

 

2.1 ROUND() 函数

查询前：

保留一位小数

SELECT ROUND(contractTerm,1) FROM `employee`

查询后：

3. sql语句中的 Null 函数

3.1 ISNULL() 函数

ISNULL

使用指定的替换值替换 NULL。

语法

ISNULL ( check_expression , replacement_value )

参数
check_expression

将被检查是否为 NULL的表达式。check_expression 可以是任何类型的。

replacement_value

在 check_expression 为 NULL时将返回的表达式。replacement_value必须与 check_expresssion 具有相同的类型。

返回类型
返回与 check_expression 相同的类型。

注释
如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value

 

原文： https://www.cnblogs.com/xiaowie/p/8675092.html

 
 
 

3.2 IFNULL() 函数

博客内容：https://www.runoob.com/mysql/mysql-func-ifnull.html
 

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

IFNULL() 函数语法格式为：

IFNULL(expression, alt_value)

如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值。

参数	描述
expression	必须，要测试的值
alt_value	必须，expression 表达式为 NULL 时返回的值

MySQL中的ISNULL和IFNULL有区别：
ISNULL() 只是用来判断是否为空，不能实现替换功能；而 IFNULL() 不仅可以用来判断是否为空，还可以实现替换功能。

 
 
 

4. MySQL实现按天分组统计，无数据自动补0

博客来自：https://blog.csdn.net/qq_40596494/article/details/108531327

 
业务需求： 要在系统中加个统计功能，要求是按指定日期范围里按天分组统计数据量，并且要能够查看该时间段内每天的数据量。

解决办法：

1. 先用一个查询把指定日期范围的日期列表搞出来

SELECT @cdate:= date_add(@cdate,interval - 1 day) as date_str , 0 as date_count
FROM (SELECT @cdate:=date_add(CURDATE(),interval + 1 day) from 数据库随便个有数据的表名) t1
where @cdate > ‘2018-12-01’ and @cdate < ‘2018-12-31’

2. 业务统计查询也按上述日期查询给统计日期和数量设置别名

SELECT FROM_UNIXTIME(m.时间戳字段, ‘%Y-%m-%d’) as date_str , count(*) as date_count
from 业务数据表名 as m
group by FROM_UNIXTIME(m.时间戳字段, ‘%Y-%m-%d’)

3. 把两个查询用左连接合起，没数量的日期填0，完工

 SELECT t1.time, COALESCE(t2.date_total_count, 0) as count
 FROM(
   SELECT @cdate := date_add(@cdate, interval - 1 day) as time FROM(SELECT @cdate := date_add(CURDATE(), interval + 1 day) from tb_operate_log) tmp1  
 ) t1 
 LEFT JOIN(
     SELECT date_format(m.log_time, '%Y-%m-%d') as time, count(*) as date_total_count FROM tb_operate_log as m WHERE m.LOG_TIME and  LOG_DESC LIKE "更新%"   GROUP BY date_format(m.log_time, '%Y-%m-%d') 
 ) t2
 on t1.time = t2.time 
where 
t1.time<"2020-09-01" 
-- and t2.LOG_DESC LIKE "登录%" 
LIMIT 7

查询结果

5. mysql 根据经纬度计算直线距离

博客来自：https://www.cnblogs.com/wenBlog/p/11131182.html

• mysql距离计算，单位m，以及排序
• lon 经度 lat 纬度
• 一般地图上显示的坐标顺序为，纬度在前（范围-90~90），经度在后（范围-180~180）
  首先新建一张表，里面包含经纬度

SET FOREIGN_KEY_CHECKS=0;
 
-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `id` int(11) unsigned NOT NULL auto_increment COMMENT '自增主键',
  `name` varchar(50) NOT NULL COMMENT '名称',
  `lon` double(9,6) NOT NULL COMMENT '经度',
  `lat` double(8,6) NOT NULL COMMENT '纬度',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='商户表';
 
-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES ('1', '天津市区', '117.315575', '39.133462');
INSERT INTO `customer` VALUES ('2', '北京市区', '116.407999', '39.894073');
INSERT INTO `customer` VALUES ('3', '保定', '115.557124', '38.853490');
INSERT INTO `customer` VALUES ('4', '石家庄', '114.646458', '38.072369');
INSERT INTO `customer` VALUES ('5', '昌平区1', '116.367180', '40.009561');
INSERT INTO `customer` VALUES ('6', '海淀区2', '116.313425', '39.973078');
INSERT INTO `customer` VALUES ('7', '海淀区1', '116.329236', '39.987231');

然后我们开始用mysql自带的函数，计算customer表中，每个地方具体。

传入参数 纬度 40.0497810000 经度 116.3424590000

/*传入的参数为  纬度 纬度 经度 ASC升序由近至远 DESC 降序 由远到近 */
SELECT
    *,
    ROUND(
        6378.138 * 2 * ASIN(
            SQRT(
                POW(
                    SIN(
                        (
                            40.0497810000 * PI() / 180 - lat * PI() / 180
                        ) / 2
                    ),
                    2
                ) + COS(40.0497810000 * PI() / 180) * COS(lat * PI() / 180) * POW(
                    SIN(
                        (
                            116.3424590000 * PI() / 180 - lon * PI() / 180
                        ) / 2
                    ),
                    2
                )
            )
        ) * 1000
    ) AS juli
FROM
    customer
ORDER BY
    juli ASC

至此，我们就能清楚的查看到纬度 40.0497810000 经度 116.3424590000 距离customer表中的每个地区的距离（单位 m）

6378.138 * 2 是地球直径

 
 

6. SQL中对同一个字段不同值，进行数据统计

SELECT
	sum( CASE WHEN tbo.DINING_STYLE = 0 THEN tbod.NUM ELSE 0 END ) storeSold,
	sum( CASE WHEN tbo.DINING_STYLE = 1 THEN tbod.NUM ELSE 0 END ) deliverySold,
	sum( CASE WHEN tbo.DINING_STYLE = 3 THEN tbod.NUM ELSE 0 END ) otherWays 
FROM
	tb_orderdetail AS tbod
	LEFT JOIN tb_order AS tbo ON tbo.ID = tbod.ORDER_ID
	LEFT JOIN tb_orderstatus AS tbos ON tbos.ORDER_ID = tbo.ID 
WHERE
	tbod.STORE_ID = "4caa3f82520445a7b921f20fe0732241" 
	AND tbod.SKU_ID = "d9cb3e609ae842779d12a9301849c44e" 
	AND tbos.`STATUS` IN ( 2, 3, 4, 5, 6, 7, 9 ) 
	AND tbo.CREATE_TIME BETWEEN "2021-02-22 00:00:00" 
	AND "2021-02-22 23:59:59" 
GROUP BY
	tbo.DINING_STYLE

查询结果：

7. 根据出生日期获取年龄

SELECT 

 DATE_FORMAT( FROM_DAYS( TO_DAYS( NOW( ) ) - TO_DAYS( crew_info.birth ) ), '%Y' ) + 0 AS age

FROM crew_info

8. 计算两个日期的时间差

SELECT 
  TIMESTAMPDIFF
  (MONTH, crew_info.aboard_time ,DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
   as shipIn
FROM crew_info

9. 分组查询同一字段进行拼接

group_concat() 函数:

栗子:

     SELECT
        crew_info_auth.crew_id AS crewId,
        group_concat(uam_user_info.nick_nam) AS crewExecutive
        FROM
        crew_info_auth
        LEFT JOIN uam_user_info ON crew_info_auth.user_id=uam_user_info.user_id
        GROUP BY crew_info_auth.crew_id

10. 分页查询会遇到重复的数据

今天做了一个分页查询，发现第二页的后三条数据，和第三页的首页的前三条数据相同，但是位置不同。后来发现他们的创建时间都相同:

原因如下:https://blog.csdn.net/qq_37334435/article/details/113373779

🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱🐱

11. IN 查询取出数据排序问题

这个在高性能mysql里面有介绍，in查询其实是一个扫表的过程，in查询时，底层是先将in里面的id就从小到大排序优化，然后再执行in查询的，所以，最好是手动将id从小到大排序后再执行in查询。

 select * from user where id in (1,5,3,7) order by field (id,1,5,3,7);

或者使用

select * from user  where id in (1,5,3,7) order by instr('1,5,3,7',concat(',',id,',')),

12. MYSQL获取当前时间、前一天时间

原文：https://blog.csdn.net/ccxlct/article/details/123255109

1. 获取当前时间

# 带有时，分，秒
 select now();
 # 不带有时，分，秒
 select curdate();

2. 获取当前时间的前一天

select DATE_SUB(NOW(),INTERVAL 1 DAY)

12. 数据存在则更新，不存在则插入

存在则更新（不影响其他字段），不存在则插入：

insert into 表名(字段1,字段2) VALUES(值1,值2) ON DUPLICATE KEY UPDATE username=值3

如果 字段1 不存在（主键，索引或者唯一条件不存在），则执行插入语句，存在则执行更新语句，该更新只更新需要的字段，不影响其他字段的值。

**存在则更新（先删除后更新），不存在则插入： **

REPLACE INTO 表名(字段1,字段2) VALUES(值A,值B);

如果 字段1 不存在（主键，索引或者唯一条件不存在），则执行插入语句，存在则执行更新语句，该更新是将该条存在的记录删除，然后再插入，所以其他的字段的值都是 null。

存在则忽略，不存在插入：

INSERT IGNORE INTO 表名(字段1,字段2) VALUES(值A,值B);

如果 字段1 不存在（主键，索引或者唯一条件不存在），则执行插入语句，如果存在，直接忽略不修改任何数据。