实用查询(MySQL经典实用查询案例)
I .连接查询
示意图
1.表格构建语句
和部门员工关系表:
CREATETABLE`tb_dept`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT’主键ID’,`deptName`varchar(30)DEFAULTNULLCOMMENT’部门名称’,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8;CREATETABLE`tb_emp`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT’主键ID’,`empName`varchar(20)DEFAULTNULLCOMMENT’员工名称’,`deptId`int(11)DEFAULT’0’COMMENT’部门ID’,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=8DEFAULTCHARSET=utf8;
2.七种连接查询
图1:左侧外部连接
selectt1.*,t2.empName,t2.deptIdfromtb_deptt1LEFTJOINtb_empt2ont1.id=t2.deptId;
图2:右连接
selectt1.*,t2.empName,t2.deptIdfromtb_deptt1RIGHTJOINtb_empt2ont1.id=t2.deptId;
图3:内部连接
selectt1.*,t2.empName,t2.deptIdfromtb_deptt1innerjointb_empt2ont1.id=t2.deptId;
图4:左侧连接
查询tb_dept表的唯一位置。
selectt1.*,t2.empName,t2.deptIdfromtb_deptt1LEFTJOINtb_empt2ont1.id=t2.deptIdWHEREt2.deptIdISNULL;
图5:右侧连接
查询tb_emp表的唯一位置。
selectt1.*,t2.empName,t2.deptIdfromtb_deptt1RIGHTJOINtb_empt2ont1.id=t2.deptIdWHEREt1.idISNULL;
图6:完全连接
selectt1.*,t2.empName,t2.deptIdfromtb_deptt1LEFTJOINtb_empt2ont1.id=t2.deptIdUNIONselectt1.*,t2.empName,t2.deptIdfromtb_deptt1RIGHTJOINtb_empt2ont1.id=t2.deptId
图7:完全没有连接
查询两个表互不相关的数据。
selectt1.*,t2.empName,t2.deptIdfromtb_deptt1RIGHTJOINtb_empt2ont1.id=t2.deptIdWHEREt1.idISNULLUNIONselectt1.*,t2.empName,t2.deptIdfromtb_deptt1LEFTJOINtb_empt2ont1.id=t2.deptIdWHEREt2.deptIdISNULL
二、时间和日期查询
1.表格构建语句
CREATETABLE`ms_consume`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT’主键ID’,`user_id`int(11)NOTNULLCOMMENT’用户ID’,`user_name`varchar(20)NOTNULLCOMMENT’用户名’,`consume_money`decimal(20,2)DEFAULT’0.00’COMMENT’消费金额’,`create_time`datetimeDEFAULTCURRENT_TIMESTAMPCOMMENT’创建时间’,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8COMMENT=’消费表’;
2.日期统计案例
日期范围中的之一个数据
场景:在产品的日常运营活动中，我们经常会看到这样的规则:首付多少，活动时间内优惠多少。
SELECT*FROM(SELECT*FROMms_consumeWHEREcreate_timeBETWEEN’2019-12-1000:00:00’AND’2019-12-1823:59:59’ORDERBYcreate_time)t1GROUPBYt1.user_id;
日期之间的时差
场景:常用的倒计时场景
SELECTt1.*,timestampdiff(SECOND,NOW(),t1.create_time)second_diffFROMms_consumet1WHEREt1.id=’9′;
查询今天的数据
–方式一SELECT*FROMms_consumeWHEREDATE_FORMAT(NOW(),’%Y-%m-%d’)=DATE_FORMAT(create_time,’%Y-%m-%d’);–方式二SELECT*FROMms_consumeWHERETO_DAYS(now())=TO_DAYS(create_time);
时间范围统计
场景:统计最近七天消费超过两次的用户。
SELECTuser_id,user_name,COUNT(user_id)userIdSumFROMms_consumeWHEREcreate_time>date_sub(NOW(),interval’7’DAY)GROUPBYuser_idHAVINGuserIdSum>1;
日期范围内的平均值
场景:指定日期范围内的平均消耗量并排序。
SELECT*FROM(SELECTuser_id,user_name,AVG(consume_money)avg_moneyFROMms_consumetWHEREt.create_timeBETWEEN’2019-12-1000:00:00’AND’2019-12-1823:59:59’GROUPBYuser_id)t1ORDERBYt1.avg_moneyDESC;
三、树表查询
1.表格构建语句
CREATETABLEms_city_sort(`id`INT(11)NOTNULLAUTO_INCREMENTCOMMENT’主键ID’,`city_name`VARCHAR(50)NOTNULLDEFAULT”COMMENT’城市名称’,`city_code`VARCHAR(50)NOTNULLDEFAULT”COMMENT’城市编码’,`parent_id`INT(11)NOTNULLDEFAULT’0’COMMENT’父级ID’,`state`INT(11)NOTNULLDEFAULT’1’COMMENT’状态：1启用,2停用’,`create_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT’创建时间’,`update_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT’修改时间’,PRIMARYKEY(id))ENGINE=INNODBDEFAULTCHARSET=utf8COMMENT=’城市分类管理’;
2.直接SQL查询
SELECTt1.*,t2.parentNameFROMms_city_sortt1LEFTJOIN(SELECTm1.id,m2.city_nameparentNameFROMms_city_sortm1,ms_city_sortm2WHEREm1.parent_id=m2.idANDm1.parent_id>0)t2ONt1.id=t2.id;
3.函数查询
查询父名称
DROPFUNCTIONIFEXISTSget_city_parent_name;CREATEFUNCTION`get_city_parent_name`(pidINT)RETURNSvarchar(50)CHARSETutf8begindeclareparentNameVARCHAR(50)DEFAULTNULL;SELECTcity_nameFROMms_city_sortWHEREid=pidintoparentName;returnparentName;endSELECTt1.*,get_city_parent_name(t1.parent_id)parentNameFROMms_city_sortt1;
查询根节点子节点
DROPFUNCTIONIFEXISTSget_root_child;CREATEFUNCTION`get_root_child`(rootIdINT)RETURNSVARCHAR(1000)CHARSETutf8BEGINDECLAREresultIdsVARCHAR(500);DECLAREnodeIdVARCHAR(500);SETresultIds=’%’;SETnodeId=cast(rootIdasCHAR);WHILEnodeIdISNOTNULLDOSETresultIds=concat(resultIds,’,’,nodeId);SELECTgroup_concat(id)INTOnodeIdFROMms_city_sortWHEREFIND_IN_SET(parent_id,nodeId)>0;ENDWHILE;RETURNresultIds;END;SELECT*FROMms_city_sortWHEREFIND_IN_SET(id,get_root_child(5))ORDERBYid;
