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

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