实用查询(MySQL经典实用查询案例)

I .连接查询

示意图

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 ;

以上就是由优质生活领域创作者 嘉文社百科网小编 整理编辑的，如果觉得有帮助欢迎收藏转发~