实用查询（MySQL经典实用查询案例）

实用查询（MySQL经典实用查询案例）

一、连接查询

图解示意图

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)DEFAULT0COMMENT部门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)DEFAULT0.00COMMENT消费金额,`create_time`datetimeDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8COMMENT=消费表;2、日期统计案例

日期范围内首条数据

场景：产品日常运营活动中，常常见到这样规则：活动时间内，首笔消费满多长，优惠多长。

SELECT*FROM(SELECT*FROMms_consumeWHEREcreate_timeBETWEEN2019-12-1000:00:00AND2019-12-1823:59:59ORDERBYcreate_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_timedate_sub(NOW(),interval7DAY)GROUPBYuser_idHAVINGuserIdSum1;

日期范围内平均值

场景：指定日期范围内的平均消费，并排序。

SELECT*FROM(SELECTuser_id,user_name,AVG(consume_money)avg_moneyFROMms_consumetWHEREt.create_timeBETWEEN2019-12-1000:00:00AND2019-12-1823:59:59GROUPBYuser_id)t1ORDERBYt1.avg_moneyDESC;三、树形表查询1、建表语句CREATETABLEms_city_sort(`id`INT(11)NOTNULLAUTO_INCREMENTCOMMENT主键ID,`city_name`VARCHAR(50)NOTNULLDEFAULTCOMMENT城市名称,`city_code`VARCHAR(50)NOTNULLDEFAULTCOMMENT城市编码,`parent_id`INT(11)NOTNULLDEFAULT0COMMENT父级ID,`state`INT(11)NOTNULLDEFAULT1COMMENT状态：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_id0)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;

END

来源：日常生活网

本文地址：https://www.15275.cn/mz/14118.html