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

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

图解示意图

部门和员工关系表：

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;

图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;

<END>

查询 连接 表语 日期 场景