MySQL基礎篇(01)：經典實用查詢案例，總結整理

本文源碼：GitHub·點這裡 || GitEE·點這裡

一、連接配接查詢

圖解示意圖

1、建表語句

部門和員工關系表：

CREATE TABLE `tb_dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `deptName` varchar(30) DEFAULT NULL COMMENT '部門名稱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `empName` varchar(20) DEFAULT NULL COMMENT '員工名稱',
  `deptId` int(11) DEFAULT '0' COMMENT '部門ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
           

2、七種連接配接查詢

• 圖1：左外連接配接

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 LEFT JOIN tb_emp t2 on t1.id=t2.deptId;
           

• 圖2：右外連接配接

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 RIGHT JOIN tb_emp t2 on t1.id=t2.deptId;
           

• 圖3：内連接配接

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 inner join tb_emp t2 on t1.id=t2.deptId;
           

• 圖4：左連接配接

查詢tb_dept表特有的地方。

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 LEFT JOIN tb_emp t2 on t1.id=t2.deptId
WHERE t2.deptId IS NULL;
           

• 圖5：右連接配接

查詢tb_emp表特有的地方。

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 RIGHT JOIN tb_emp t2 on t1.id=t2.deptId
WHERE t1.id IS NULL;
           

• 圖6：全連接配接

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 LEFT JOIN tb_emp t2 on t1.id=t2.deptId
UNION
select t1.*,t2.empName,t2.deptId 
from tb_dept t1 RIGHT JOIN tb_emp t2 on t1.id=t2.deptId
           

• 圖7：全不連接配接

查詢兩張表互不關聯到的資料。

select t1.*,t2.empName,t2.deptId 
from tb_dept t1 RIGHT JOIN tb_emp t2 on t1.id=t2.deptId
WHERE t1.id IS NULL
UNION
select t1.*,t2.empName,t2.deptId 
from tb_dept t1 LEFT JOIN tb_emp t2 on t1.id=t2.deptId
WHERE t2.deptId IS NULL
           

二、時間日期查詢

CREATE TABLE `ms_consume` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `user_id` int(11) NOT NULL COMMENT '使用者ID',
  `user_name` varchar(20) NOT NULL COMMENT '使用者名',
  `consume_money` decimal(20,2) DEFAULT '0.00' COMMENT '消費金額',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='消費表';
           

2、日期統計案例

• 日期範圍内首條資料

場景：産品日常營運活動中，經常見到這樣規則：活動時間内，首筆消費滿多少，優惠多少。

SELECT * FROM
	(
		SELECT * FROM ms_consume
		WHERE
			create_time 
		BETWEEN '2019-12-10 00:00:00' AND '2019-12-18 23:59:59'
		ORDER BY create_time
	) t1
GROUP BY t1.user_id ;
           

• 日期之間時差

場景：常用的倒計時場景

SELECT t1.*,
	   timestampdiff(SECOND,NOW(),t1.create_time) second_diff 
FROM ms_consume t1 WHERE t1.id='9' ;
           

• 查詢今日資料

-- 方式一
SELECT * FROM ms_consume 
WHERE DATE_FORMAT(NOW(),'%Y-%m-%d')=DATE_FORMAT(create_time,'%Y-%m-%d');
-- 方式二
SELECT * FROM ms_consume 
WHERE TO_DAYS(now())=TO_DAYS(create_time) ;
           

• 時間範圍統計

場景：統計近七日内，消費次數大于兩次的使用者。

SELECT user_id,user_name,COUNT(user_id) userIdSum 
FROM ms_consume WHERE create_time>date_sub(NOW(), interval '7' DAY) 
GROUP BY user_id  HAVING userIdSum>1;
           

• 日期範圍内平均值

場景：指定日期範圍内的平均消費，并排序。

SELECT * FROM
	(
		SELECT user_id,user_name,
			AVG(consume_money) avg_money
		FROM ms_consume t
		WHERE t.create_time BETWEEN '2019-12-10 00:00:00' 
							AND '2019-12-18 23:59:59'
		GROUP BY user_id
	) t1
ORDER BY t1.avg_money DESC;
           

三、樹形表查詢

CREATE TABLE ms_city_sort (
	`id` INT (11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
	`city_name` VARCHAR (50) NOT NULL DEFAULT '' COMMENT '城市名稱',
	`city_code` VARCHAR (50) NOT NULL DEFAULT '' COMMENT '城市編碼',
	`parent_id` INT (11) NOT NULL DEFAULT '0' COMMENT '父級ID',
	`state` INT (11) NOT NULL DEFAULT '1' COMMENT '狀态：1啟用,2停用',
	`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
	`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改時間',
	PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '城市分類管理';
           

2、直接SQL查詢

SELECT t1.*, t2.parentName
FROM ms_city_sort t1
LEFT JOIN (
	SELECT
		m1.id,m2.city_name parentName
	FROM
		ms_city_sort m1,ms_city_sort m2
	WHERE m1.parent_id = m2.id
	AND m1.parent_id > 0
) t2 ON t1.id = t2.id;
           

3、函數查詢

• 查詢父級名稱

DROP FUNCTION IF EXISTS get_city_parent_name;
CREATE FUNCTION `get_city_parent_name`(pid INT) 
RETURNS varchar(50) CHARSET utf8
begin 
    declare parentName VARCHAR(50) DEFAULT NULL;
    SELECT city_name FROM ms_city_sort WHERE id=pid into parentName;
    return parentName;
end

SELECT t1.*,get_city_parent_name(t1.parent_id) parentName FROM ms_city_sort t1 ;
           

• 查詢根節點子級

DROP FUNCTION IF EXISTS get_root_child;
CREATE FUNCTION `get_root_child`(rootId INT) 
    RETURNS VARCHAR(1000) CHARSET utf8
    BEGIN 
        DECLARE resultIds VARCHAR(500); 
        DECLARE nodeId VARCHAR(500);
        SET resultIds = '%'; 
		SET nodeId = cast(rootId as CHAR);
        WHILE nodeId IS NOT NULL DO 
			SET resultIds = concat(resultIds,',',nodeId);
            SELECT group_concat(id) INTO nodeId 
			FROM ms_city_sort WHERE FIND_IN_SET(parent_id,nodeId)>0;
        END WHILE; 
        RETURN resultIds; 
END  ;

SELECT * FROM ms_city_sort WHERE FIND_IN_SET(id,get_root_child(5)) ORDER BY id ;
           

四、源代碼位址

GitHub·位址
https://github.com/cicadasmile/mysql-data-base
GitEE·位址
https://gitee.com/cicadasmile/mysql-data-base