本文源码:github·点这里 || gitee·点这里
图解示意图
部门和员工关系表:
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;
select t1.*,t2.empname,t2.deptid from tb_dept t1 left join tb_emp t2 on t1.id=t2.deptid;
select t1.*,t2.empname,t2.deptid from tb_dept t1 right join tb_emp t2 on t1.id=t2.deptid;
select t1.*,t2.empname,t2.deptid from tb_dept t1 inner join tb_emp t2 on t1.id=t2.deptid;
查询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;
查询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;
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
查询两张表互不关联到的数据。
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
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='消费表';
场景:产品日常运营活动中,经常见到这样规则:活动时间内,首笔消费满多少,优惠多少。
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;
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 = '城市分类管理';
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;
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添加新用户及为用户创建数据库和给用户分配权限方法介绍
Navicat出现无法远程连接MySql服务器问题的解决办法
网友评论