mysql遇到的一些常用的查詢小問題

mysql遇到的一些常用的查詢小問題

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;