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

　　实用查询（MySQL经典实用查询案例）一、连接查询
　　图解示意图
　　1、建表语句
　　部门和员工关系表：CREATETABLEtbdept（idint（11）NOTNULLAUTOINCREMENTCOMMENT39；主键ID39；，deptNamevarchar（30）DEFAULTNULLCOMMENT39；部门名称39；，PRIMARYKEY（id））ENGINEInnoDBAUTOINCREMENT6DEFAULTCHARSETutf8；CREATETABLEtbemp（idint（11）NOTNULLAUTOINCREMENTCOMMENT39；主键ID39；，empNamevarchar（20）DEFAULTNULLCOMMENT39；员工名称39；，deptIdint（11）DEFAULT39；039；COMMENT39；部门ID39；，PRIMARYKEY（id））ENGINEInnoDBAUTOINCREMENT8DEFAULTCHARSETutf8；2、七种连接查询
　　图1：左外连接selectt1。，t2。empName，t2。deptIdfromtbdeptt1LEFTJOINtbempt2ont1。idt2。deptId；
　　图2：右外连接selectt1。，t2。empName，t2。deptIdfromtbdeptt1RIGHTJOINtbempt2ont1。idt2。deptId；
　　图3：内连接selectt1。，t2。empName，t2。deptIdfromtbdeptt1innerjointbempt2ont1。idt2。deptId；
　　图4：左连接
　　查询tbdept表特有的地方。selectt1。，t2。empName，t2。deptIdfromtbdeptt1LEFTJOINtbempt2ont1。idt2。deptIdWHEREt2。deptIdISNULL；
　　图5：右连接
　　查询tbemp表特有的地方。selectt1。，t2。empName，t2。deptIdfromtbdeptt1RIGHTJOINtbempt2ont1。idt2。deptIdWHEREt1。idISNULL；
　　图6：全连接selectt1。，t2。empName，t2。deptIdfromtbdeptt1LEFTJOINtbempt2ont1。idt2。deptIdUNIONselectt1。，t2。empName，t2。deptIdfromtbdeptt1RIGHTJOINtbempt2ont1。idt2。deptId
　　图7：全不连接
　　查询两张表互不关联到的数据。selectt1。，t2。empName，t2。deptIdfromtbdeptt1RIGHTJOINtbempt2ont1。idt2。deptIdWHEREt1。idISNULLUNIONselectt1。，t2。empName，t2。deptIdfromtbdeptt1LEFTJOINtbempt2ont1。idt2。deptIdWHEREt2。deptIdISNULL二、时间日期查询1、建表语句CREATETABLEmsconsume（idint（11）NOTNULLAUTOINCREMENTCOMMENT39；主键ID39；，useridint（11）NOTNULLCOMMENT39；用户ID39；，usernamevarchar（20）NOTNULLCOMMENT39；用户名39；，consumemoneydecimal（20，2）DEFAULT39；0。0039；COMMENT39；消费金额39；，createtimedatetimeDEFAULTCURRENTTIMESTAMPCOMMENT39；创建时间39；，PRIMARYKEY（id））ENGINEInnoDBAUTOINCREMENT9DEFAULTCHARSETutf8COMMENT39；消费表39；；2、日期统计案例
　　日期范围内首条数据
　　场景：产品日常运营活动中，经常见到这样规则：活动时间内，首笔消费满多少，优惠多少。SELECTFROM（SELECTFROMmsconsumeWHEREcreatetimeBETWEEN39；2019121000：00：0039；AND39；2019121823：59：5939；ORDERBYcreatetime）t1GROUPBYt1。userid；
　　日期之间时差
　　场景：常用的倒计时场景SELECTt1。，timestampdiff（SECOND，NOW（），t1。createtime）seconddiffFROMmsconsumet1WHEREt1。id39；939；；
　　查询今日数据方式一SELECTFROMmsconsumeWHEREDATEFORMAT（NOW（），39；Ymd39；）DATEFORMAT（createtime，39；Ymd39；）；方式二SELECTFROMmsconsumeWHERETODAYS（now（））TODAYS（createtime）；
　　时间范围统计
　　场景：统计近七日内，消费次数大于两次的用户。SELECTuserid，username，COUNT（userid）userIdSumFROMmsconsumeWHEREcreatetimegt；datesub（NOW（），interval39；739；DAY）GROUPBYuseridHAVINGuserIdSumgt；1；
　　日期范围内平均值
　　场景：指定日期范围内的平均消费，并排序。SELECTFROM（SELECTuserid，username，AVG（consumemoney）avgmoneyFROMmsconsumetWHEREt。createtimeBETWEEN39；2019121000：00：0039；AND39；2019121823：59：5939；GROUPBYuserid）t1ORDERBYt1。avgmoneyDESC；三、树形表查询1、建表语句CREATETABLEmscitysort（idINT（11）NOTNULLAUTOINCREMENTCOMMENT39；主键ID39；，citynameVARCHAR（50）NOTNULLDEFAULT39；39；COMMENT39；城市名称39；，citycodeVARCHAR（50）NOTNULLDEFAULT39；39；COMMENT39；城市编码39；，parentidINT（11）NOTNULLDEFAULT39；039；COMMENT39；父级ID39；，stateINT（11）NOTNULLDEFAULT39；139；COMMENT39；状态：1启用，2停用39；，createtimedatetimeNOTNULLDEFAULTCURRENTTIMESTAMPCOMMENT39；创建时间39；，updatetimedatetimeNOTNULLDEFAULTCURRENTTIMESTAMPCOMMENT39；修改时间39；，PRIMARYKEY（id））ENGINEINNODBDEFAULTCHARSETutf8COMMENT39；城市分类管理39；；2、直接SQL查询SELECTt1。，t2。parentNameFROMmscitysortt1LEFTJOIN（SELECTm1。id，m2。citynameparentNameFROMmscitysortm1，mscitysortm2WHEREm1。parentidm2。idANDm1。parentidgt；0）t2ONt1。idt2。id；3、函数查询
　　查询父级名称DROPFUNCTIONIFEXISTSgetcityparentname；CREATEFUNCTIONgetcityparentname（pidINT）RETURNSvarchar（50）CHARSETutf8begindeclareparentNameVARCHAR（50）DEFAULTNULL；SELECTcitynameFROMmscitysortWHEREidpidintoparentName；returnparentName；endSELECTt1。，getcityparentname（t1。parentid）parentNameFROMmscitysortt1；
　　查询根节点子级DROPFUNCTIONIFEXISTSgetrootchild；CREATEFUNCTIONgetrootchild（rootIdINT）RETURNSVARCHAR（1000）CHARSETutf8BEGINDECLAREresultIdsVARCHAR（500）；DECLAREnodeIdVARCHAR（500）；SETresultIds39；39；；SETnodeIdcast（rootIdasCHAR）；WHILEnodeIdISNOTNULLDOSETresultIdsconcat（resultIds，39；，39；，nodeId）；SELECTgroupconcat（id）INTOnodeIdFROMmscitysortWHEREFINDINSET（parentid，nodeId）gt；0；ENDWHILE；RETURNresultIds；END；SELECTFROMmscitysortWHEREFINDINSET（id，getrootchild（5））ORDERBYid；
　　lt；ENDgt；