实用查询（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)DEFAULT'0'COMMENT'部门ID',  PRIMARYKEY(`id`)  )ENGINE=InnoDBAUTO_INCREMENT=8DEFAULTCHARSET=utf8;2、七种连接查询
　　图1：左外连接selectt1.*,t2.empName,t2.deptId  fromtb_deptt1LEFTJOINtb_empt2ont1.id=t2.deptId;
　　图2：右外连接selectt1.*,t2.empName,t2.deptId  fromtb_deptt1RIGHTJOINtb_empt2ont1.id=t2.deptId;
　　图3：内连接selectt1.*,t2.empName,t2.deptId  fromtb_deptt1innerjointb_empt2ont1.id=t2.deptId;
　　图4：左连接
　　查询tb_dept表特有的地方。selectt1.*,t2.empName,t2.deptId  fromtb_deptt1LEFTJOINtb_empt2ont1.id=t2.deptIdWHEREt2.deptIdISNULL;
　　图5：右连接
　　查询tb_emp表特有的地方。selectt1.*,t2.empName,t2.deptId  fromtb_deptt1RIGHTJOINtb_empt2ont1.id=t2.deptIdWHEREt1.idISNULL;
　　图6：全连接selectt1.*,t2.empName,t2.deptId  fromtb_deptt1LEFTJOINtb_empt2ont1.id=t2.deptIdUNIONselectt1.*,t2.empName,t2.deptId  fromtb_deptt1RIGHTJOINtb_empt2ont1.id=t2.deptId
　　图7：全不连接
　　查询两张表互不关联到的数据。selectt1.*,t2.empName,t2.deptId  fromtb_deptt1RIGHTJOINtb_empt2ont1.id=t2.deptIdWHEREt1.idISNULLUNIONselectt1.*,t2.empName,t2.deptId  fromtb_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_consume		WHERE  			create_time  		BETWEEN'2019-12-1000:00:00'AND'2019-12-1823:59:59'  		ORDERBYcreate_time  	)t1GROUPBYt1.user_id;
　　日期之间时差
　　场景：常用的倒计时场景SELECTt1.*,	timestampdiff(SECOND,NOW(),t1.create_time)second_diff  FROMms_consumet1WHEREt1.id='9';
　　查询今日数据--方式一SELECT*FROMms_consume  WHEREDATE_FORMAT(NOW(),'%Y-%m-%d')=DATE_FORMAT(create_time,'%Y-%m-%d');--方式二SELECT*FROMms_consume  WHERETO_DAYS(now())=TO_DAYS(create_time);
　　时间范围统计
　　场景：统计近七日内，消费次数大于两次的用户。SELECTuser_id,user_name,COUNT(user_id)userIdSum  FROMms_consumeWHEREcreate_time>date_sub(NOW(),interval'7'DAY)  GROUPBYuser_idHAVINGuserIdSum>1;
　　日期范围内平均值
　　场景：指定日期范围内的平均消费，并排序。SELECT*FROM  	(		SELECTuser_id,user_name,			AVG(consume_money)avg_money		FROMms_consumet		WHEREt.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(	SELECT  		m1.id,m2.city_nameparentName	FROM  		ms_city_sortm1,ms_city_sortm2	WHEREm1.parent_id=m2.id	ANDm1.parent_id>0)t2ONt1.id=t2.id;3、函数查询
　　查询父级名称DROPFUNCTIONIFEXISTSget_city_parent_name;CREATEFUNCTION`get_city_parent_name`(pidINT)  RETURNSvarchar(50)CHARSETutf8begin  declareparentNameVARCHAR(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)CHARSETutf8BEGIN  DECLAREresultIdsVARCHAR(500);  DECLAREnodeIdVARCHAR(500);SETresultIds='%';  		SETnodeId=cast(rootIdasCHAR);  WHILEnodeIdISNOTNULLDO  			SETresultIds=concat(resultIds,',',nodeId);SELECTgroup_concat(id)INTOnodeId  			FROMms_city_sortWHEREFIND_IN_SET(parent_id,nodeId)>0;ENDWHILE;  RETURNresultIds;  END;SELECT*FROMms_city_sortWHEREFIND_IN_SET(id,get_root_child(5))ORDERBYid;
　　<END>