本文源码：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

二、时间日期查询

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_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;

三、树形表查询

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.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

巴特西

MySQL基础篇(01)：经典实用查询案例，总结整理

一、连接查询

1、建表语句

2、七种连接查询

二、时间日期查询

1、建表语句

2、日期统计案例

三、树形表查询

1、建表语句

2、直接SQL查询

3、函数查询

四、源代码地址

最新文章

热门文章