本文源碼：GitHub·點(diǎn)這里 || GitEE·點(diǎn)這里

一、連接查詢

圖解示意圖

1、建表語(yǔ)句

部門(mén)和員工關(guān)系表：

CREATE TABLE `tb_dept` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',`deptName` varchar(30) DEFAULT NULL COMMENT '部門(mén)名稱',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 '部門(mén)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：內(nèi)連接

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：全不連接

查詢兩張表互不關(guān)聯(lián)到的數(shù)據(jù)。

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

二、時(shí)間日期查詢

1、建表語(yǔ)句

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 '消費(fèi)金額',`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='消費(fèi)表';

2、日期統(tǒng)計(jì)案例

• 日期范圍內(nèi)首條數(shù)據(jù)

場(chǎng)景：產(chǎn)品日常運(yùn)營(yíng)活動(dòng)中，經(jīng)常見(jiàn)到這樣規(guī)則：活動(dòng)時(shí)間內(nèi)，首筆消費(fèi)滿多少，優(yōu)惠多少。

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) t1 GROUP BY t1.user_id ;

• 日期之間時(shí)差

場(chǎng)景：常用的倒計(jì)時(shí)場(chǎng)景

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

• 查詢今日數(shù)據(jù)

-- 方式一 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) ;

• 時(shí)間范圍統(tǒng)計(jì)

場(chǎng)景：統(tǒng)計(jì)近七日內(nèi)，消費(fèi)次數(shù)大于兩次的用戶。

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;

• 日期范圍內(nèi)平均值

場(chǎng)景：指定日期范圍內(nèi)的平均消費(fèi)，并排序。

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) t1 ORDER BY t1.avg_money DESC;

三、樹(shù)形表查詢

1、建表語(yǔ)句

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 '父級(jí)ID',`state` INT (11) NOT NULL DEFAULT '1' COMMENT '狀態(tài)：1啟用,2停用',`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改時(shí)間',PRIMARY KEY (id) ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '城市分類管理';

2、直接SQL查詢

SELECT t1.*, t2.parentName FROM ms_city_sort t1 LEFT 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、函數(shù)查詢

• 查詢父級(jí)名稱

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; endSELECT t1.*,get_city_parent_name(t1.parent_id) parentName FROM ms_city_sort t1 ;

• 查詢根節(jié)點(diǎn)子級(jí)

DROP FUNCTION IF EXISTS get_root_child; CREATE FUNCTION `get_root_child`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8BEGIN 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

總結(jié)

以上是生活随笔為你收集整理的MySQL基础篇(01)：经典实用查询案例，总结整理的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。