mysql遇到的一些常用的查询小问题, 时间戳FROM_UNIXTIME date_format

1. count中的属性过滤 xxx or null

SELECT 
    DATE_FORMAT(create_time,'%Y-%m-%d') as r_create_time,
    COUNT((process_state=1 and unknow=1) or null) unknown,
    COUNT((process_state=1 and unknow=0 and ignore_state=0) or null) process_state,
    COUNT((process_state=1 and ignore_state=1) or null) ignore_state
FROM
    rs_plate_sure
GROUP BY r_create_time

2. GROUP_CONCAT组合字符串(遇到有分页的时候有用,比如查询某人所有车的车牌，但是作为一个属性显示在一行)

t_score

t_r

select GROUP_CONCAT(distinct(t_s.score)), t_s.no from t_score t_s left join t_r on t_s.id=t_r.id group by t_s.no

SELECT 
   GROUP_CONCAT( DISTINCT (if(t_r.id='2','特殊处理',t_r.data)  )), t_s.id
FROM
    t_score t_s
        LEFT JOIN
    t_r ON t_s.id = t_r.id
GROUP BY t_s.id

3. 找到最近流水的那一行

select b.* from  (SELECT id,max(score) score,no FROM t_score group by no) a
left join t_score b
on a.no = b.no and a.score= b.score;

4. 有left join order by 遇到分页的时候混乱

解决办法 order by 一个id 保证left join有序 如果oder by后的顺序依然有等值的就再加个属性来排序

5. update select 将其他表的内容 更新到目标表

UPDATE t_target t_tar
        LEFT JOIN
    (SELECT 
        ifnull(sum(ifnull(park_state,0)),0)sm, park_id
    FROM
        t_park_state
    GROUP BY park_id) r_st ON r_st.park_id = e_s.park_id
        LEFT JOIN
    t_park t_p ON t_p.park_id = e_s.park_id 
SET 
    t_tar.empty_num = (ifnull(t_p.total_space,0) - ifnull(r_st.sm,0))

存储过程添加字段

<update id="initBerthCordinatesTable">
	DELIMITER $$
	drop procedure if exists add_col_homework;
	create procedure add_col_homework()
	BEGIN
	IF NOT EXISTS ( SELECT
	COLUMN_NAME
	FROM
	information_schema.COLUMNS
	WHERE
	1 = 1
	AND TABLE_NAME ='t_detail_berth_ordinates'
	AND COLUMN_NAME ='min_berth_number')
	THEN
	ALTER TABLE `t_detail_berth_ordinates` ADD COLUMN `min_berth_number`
	varchar(36);
	end if;
	end;
	CALL add_col_homework;
	$$
</update>

存储IP4 地址

select INET_ATON('192.168.0.0')

自动更新的时间戳

CREATE TABLE `t_user5` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
   `updatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间随时更新',
   `createtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间是创建的时间，不随时更新',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

insert into test.t_user (id, name) values("1","Tom");
insert into test.t_user (id, name) values("2","Jack");
update test.t_user set name ='June' where id =2;
SELECT * FROM test.t_user;

时间处理

对于long
select FROM_UNIXTIME(ctime/1000,'%Y-%m-%d') dt 

对于datetime
date_format(create_at,'%Y%-%c%-%e')dt