江苏龙网

  1. 主页 > 生活百科 > >

MySQL经典实用查询案例，总结整理

查询mysql

一、连接查询图解示意图

MySQL经典实用查询案例，总结整理

文章插图
 
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_idHAVING 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;


推荐阅读


上一篇：兴山县成立有机白茶精深加工工程(技术中心

下一篇：８年日照碧波,百年理念崛起北方茶王