一、連接查詢
圖解示意圖
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.deptIdWHERE 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.deptIdWHERE t1.id IS NULL;
- 圖6：全連接
select t1.*,t2.empName,t2.deptId from tb_dept t1 LEFT JOIN tb_emp t2 on t1.id=t2.deptIdUNIONselect 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.deptIdWHERE t1.id IS NULLUNIONselect t1.*,t2.empName,t2.deptId from tb_dept t1 LEFT JOIN tb_emp t2 on t1.id=t2.deptIdWHERE t2.deptId IS NULL
二、時間日期查詢
1、建表語句
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_consumeWHEREcreate_time BETWEEN '2019-12-10 00:00:00' AND '2019-12-18 23:59:59'ORDER BY create_time) t1GROUP 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_moneyFROM ms_consume tWHERE t.create_time BETWEEN '2019-12-10 00:00:00' AND '2019-12-18 23:59:59'GROUP BY user_id) t1ORDER BY t1.avg_money DESC;
三、樹形表查詢
1、建表語句
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.parentNameFROM ms_city_sort t1LEFT JOIN (SELECTm1.id,m2.city_name parentNameFROMms_city_sort m1,ms_city_sort m2WHERE m1.parent_id = m2.idAND 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 utf8begin declare parentName VARCHAR(50) DEFAULT NULL; SELECT city_name FROM ms_city_sort WHERE id=pid into parentName; return parentName;endSELECT 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 ;
<END>
