提示文章写完后目录可以自动生成如何生成可参考右边的帮助文档文章目录关联查询什么是关联查询内连接左连接右连接union自连接with rollup合计多字段分组分组统计时select后字段列表的问题havingorder bylimit一、关联查询基础概念核心规则二、连接类型1. 内连接INNER JOIN2. 左连接LEFT JOIN3. 右连接RIGHT JOIN三、UNION 和 UNION ALL四、自连接SELF JOIN五、分组查询GROUP BY1. 基本用法2. 多字段分组3. WITH ROLLUP合计行六、HAVING 与 WHERE 的区别七、ORDER BY 与 LIMIT八、易错点关联查询什么是关联查询关联查询两个或更多个表一起查询。前提条件这些一起查询的表之间是有关系的一对一、一对多它们之间一定是有关联字段这个关联字段可能建立了外键也可能没有建立外键。比如员工表和部门表这两个表依靠“部门编号”进行关联。1凡是联合查询的两个表必须有“关联字段”关联字段是逻辑意义一样数据类型一样名字可以一样也可以不一样的两个字段。比如t_employeeA表中did和t_departmentB表中的did。关联字段其实就是“可以”建外键的字段。当然联合查询不要求一定建外键。2关联查询必须写关联条件关联条件的个数 n – 1n是联合查询的表的数量2个表一起联合查询关联条件数量是13个表一起联合查询关联条件数量是24个表一起联合查询关联条件数量是3否则就会出现笛卡尔积现象。3关联条件可以用on子句编写也可以写到where中但是建议用on单独编写这样可读性更好。每一个join后面都要加on子句。A inner|left|right join B on 关联条件A inner|left|right join B on 关联条件 inner|left|right join C on 关联条件内连接从结果来看其实内连接也就相当于扩展表通过某种方式连接多个表但是连接的一定是严丝合缝的比如这个A表中NULL,B表中不存在则不进行扩展select*fromt_employee where t_employee.did1;select*fromt_employee inner join t_department on t_employee.didt_department.did where t_department.did1;select *from t_employeeinner join t_department on t_employee.did t_department.didinner join t_job on t_employee.job_id t_job.jidwhere t_department.did 1;左连接select*fromt_employee;与内连接相比多了两个A独有的select*fromt_employee left join t_department on t_employee.didt_department.did;select*fromt_employee left join t_department on t_employee.didt_department.did left join t_job on t_employee.job_idt_job.jid;若是想去除交集部分则加上一个where进行筛选即可右连接select*fromt_employee;select*fromt_employee right join t_department on t_employee.didt_department.did;与内连接相比多了一个B独有的将独有的筛选出来select*fromt_employee right join t_department on t_employee.didt_department.did where t_employee.didisnull;union– union合并时要注意– 两个表要查询的结果字段是一样的– union all 表示直接合并结果保留重复的记录– union 表示合并结果时去重select *from t_employeeleft join t_department on t_employee.did t_department.didUNIONselect *from t_employeeright join t_department on t_employee.did t_department.did;union all自连接原来一个表比如5列自连接之后则是十列所以其实也就是调整后面一个表的行顺序use atguigu;select*fromt_employeeasemp inner join t_employeeasmgr on emp.midmgr.eid;with rollup合计--按照部门统计人数 select did,count(*)fromt_employee group by did;--按照部门统计人数并合计总数 select did,count(*)fromt_employee group by didwithrollup;select ifnull(did,合计)as部门编号,count(*)as人数fromt_employee group by didwithrollup;相当于多了最后一行汇总多字段分组MySQL 会把 did、job_id、gender 这三个字段的值组合起来看成一个整体。只有当这三个字段的值完全相同才认为是同一组。对每一组使用 COUNT(*) 进行计数。最终每组返回一行结果。--按照不同的部门不同的职位分别统计男和女的员工人数 select did,job_id,gender,count(*)fromt_employee group by did,job_id,gender;分组统计时select后字段列表的问题did 1 这一组有两条记录张三和李四。那 ename 这列到底应该显示 张三 还是 李四 呢MySQL 无法决定所以就报错了或在低版本中随机显示一个did 是分组字段同一个组里 did 值一定相同。COUNT(*) 是聚合函数对一组数据计算出一个结果。--分组统计时select后面只写和分组统计有关的字段--其他无关字段不要出现否则会引起歧义 select eid,ename,did,count(*)fromt_employee group by did;--eid,ename此时不应该出现在select后面 select did,count(*)fromt_employee group by did;havinghaving子句后面也写条件。where是对原表中的记录的筛选。where后面不能出现分组函数。having是对统计结果分组函数计算后的筛选。having后面能出现分组函数。--查询每一个部门薪资超过10000的男女员工的人数显示部门编号部门的名称性别人数--只显示人数低于3人的 select t_department.did,dname,gender,count(eid)fromt_employee right join t_department on t_employee.didt_department.did where salary10000group by t_department.did,gender having count(eid)3;order byasc代表升序desc代表降序默认升序。select t_department.did,dname,gender,COUNT(*)fromt_department LEFT JOIN t_employee on t_department.didt_employee.did WHERE salary10000GROUP BY t_department.did,gender HAVING COUNT(*)3ORDER BY count(*)limitlimit子句是用于分页显示结果。limit m,n表示从第m个记录开始找n条记录显示其中m是从0开始的总结一、关联查询基础概念关联查询将两个或两个以上有关联关系的表一起查询。核心规则参与关联的表之间必须有关联字段逻辑意义相同、数据类型一致的字段。关联条件个数 表数量 - 1。否则会出现笛卡尔积数据爆炸。关联条件推荐写在ON子句中可读性更好每个JOIN后面都要跟ON。二、连接类型1. 内连接INNER JOIN只返回两个表中关联条件匹配的记录交集。“严丝合缝”不匹配的记录不会出现。示例SELECTe.ename,d.dname,j.jnameFROMt_employee eINNERJOINt_department dONe.didd.didINNERJOINt_job jONe.job_idj.jid;2. 左连接LEFT JOIN以左表为主显示左表所有记录。右表匹配不上的记录右表字段显示为NULL。经典用法查找左表中有而右表中没有的记录SELECT*FROMt_employee eLEFTJOINt_department dONe.didd.didWHEREd.didISNULL;-- 找出没有部门的员工3. 右连接RIGHT JOIN以右表为主显示右表所有记录。左表匹配不上的记录左表字段显示为NULL。经典用法SELECT*FROMt_employee eRIGHTJOINt_department dONe.didd.didWHEREe.didISNULL;-- 找出没有员工的部门实际开发建议优先使用 LEFT JOIN逻辑更清晰易读。三、UNION 和 UNION ALL用于合并多个查询的结果集。UNION合并后自动去重性能稍慢。UNION ALL直接合并保留所有记录包括重复性能更好。注意合并的 SELECT 语句必须列数相同、顺序一致、数据类型兼容。四、自连接SELF JOIN同一个表自己与自己进行连接常用于上下级关系、树形结构查询。示例查询员工及其领导SELECTe.enameAS员工,m.enameAS领导FROMt_employee eLEFTJOINt_employee mONe.midm.eid;关键必须给同一张表起不同别名。五、分组查询GROUP BY1. 基本用法SELECTdid,COUNT(*)AS人数FROMt_employeeGROUPBYdid;2. 多字段分组SELECTdid,job_id,gender,COUNT(*)FROMt_employeeGROUPBYdid,job_id,gender;理解要点多个字段会组合成一个联合分组键只有当所有字段的值完全相同时才算同一组。3. WITH ROLLUP合计行SELECTIFNULL(did,合计)AS部门,COUNT(*)AS人数FROMt_employeeGROUPBYdidWITH ROLLUP;会在最后增加一行总计记录。六、HAVING 与 WHERE 的区别对比项WHEREHAVING执行时机分组之前分组之后筛选对象原始记录分组后的统计结果是否能用聚合函数不能可以AVG、COUNT、SUM等典型用途过滤原始数据过滤分组后的结果记忆口诀WHERE 筛原始HAVING 筛结果WHERE 前面过滤HAVING 后面统计。七、ORDER BY 与 LIMITORDER BY对最终结果进行排序。ASC升序默认、DESC降序。LIMIT m,n分页。从第m1条记录开始取n条记录m从 0 开始。推荐写法SELECT*FROMt_employeeORDERBYsalaryDESCLIMIT20,10;-- 第3页每页10条八、易错点关联条件个数不足n 张表关联至少需要 n-1 个ON条件否则产生笛卡尔积数据爆炸。LEFT JOIN / RIGHT JOIN 判断 NULL 时写错表LEFT JOIN要判断右表字段IS NULL。RIGHT JOIN要判断左表字段IS NULL。这是最经典也最容易错的点。GROUP BY 时 SELECT 出现非分组、非聚合字段如SELECT ename, did, COUNT(*)并GROUP BY did会因为ename在组内不唯一而报错only_full_group_by模式。WHERE 和 HAVING 混淆在HAVING中才能使用聚合函数如HAVING COUNT(*) 5。WHERE中使用聚合函数会直接报错。UNION时列数、顺序、类型不一致合并的多个 SELECT 必须结构完全一致。自连接忘记起别名自连接必须给同一张表起不同别名否则字段引用冲突。LIMIT m,n中 m 的含义理解错误m 是偏移量从 0 开始不是“第 m 条”。正确理解为“从第 m1 条开始取 n 条”。不写 ORDER BY 就使用 LIMIT 分页分页结果顺序不稳定强烈建议加上ORDER BY。连续使用多个 RIGHT JOIN可读性极差建议统一改写为LEFT JOIN。性能问题关联字段没有索引、多表关联过多、LIMIT偏移量过大等都会导致查询极慢。