如何通过【MySQL】多表关联查询实现搜索？

问答2026-04-05 22:283阅读
摘要：关联查询（多表查询） 交叉连接（CROSS JOIN） 定义：返回两个表的笛卡尔积，即第一个表中的每一行，与第二个表中的每一行组合。如果涉及多于两个表，则依次进行。 🌟 青柠来相伴，
关联查询（多表查询） 交叉连接（CROSS JOIN） 定义：返回两个表的笛卡尔积，即第一个表中的每一行，与第二个表中的每一行组合。如果涉及多于两个表，则依次进行。 =======🌟青柠来相伴，代码更简单。🌟======= 📚本文所有内容，我都整理在了青柠合集里。👇 🎯搜索关注【青柠代码录】，即可查看所有合集文章~ =======🌟================🌟======= 语法： SELECT * FROM A, B (, C) -- 或者 SELECT * FROM A CROSS JOIN B (CROSS JOIN C) 特点：没有任何关联条件，结果集会很大，通常在实际应用中很少使用。 SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo; 笛卡尔积也称为交叉连接，英文是 CROSS JOIN。 在 SQL99 中也是使用 CROSS JOIN表示交叉连接。 它的作用就是可以把任意表进行连接，即使这两张表不相关。 笛卡尔积的错误会在下面条件下产生： 省略多个表的连接条件（或关联条件） 连接条件（或关联条件）无效 所有表中的所有行互相连接 为了避免笛卡尔积， 可以在WHERE 加入有效的连接条件。 内连接（INNER JOIN） 定义：基于一个匹配条件来结合两个表的数据。只有当连接条件满足时，才会返回记录。语法： SELECT * FROM A, B WHERE A.id = B.id -- 或者 SELECT * FROM A INNER JOIN B ON A.id = B.id 特点：可以缩写为 JOIN。用于检索多个表中同时符合某种条件的数据记录的集合。 分为三类 等值连接：ON A.id=B.id 不等值连接：ON A.id > B.id 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid 包括等值连接、非等值连接、自连接 -------------- 取交集 select 查询列表 from 表1 别名 join 表2 别名 on 连接条件; 特点： ①添加排序、分组、筛选 ②inner可以省略 ③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读 ④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集 1）等值连接 1）多表等值连接的结果为多表的交集部分 2）n表连接，至少需要n-1个连接条件 3）多表的顺序没有要求 4）一般需要为表起别名 5）可以搭配前面介绍的所有子句使用，比如排序、分组、筛选 案例1：查询女神名称和对应男神名称 SELECT beauty.id,NAME,boyname FROM beauty ,boys WHERE beauty.boyfriend_id=boys.id; #条件是等号为等值连接 2）非等值连接 案例1：查询员工的工资和工资级别 SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.lowest_sal AND g.highest_sal AND g.grade_level='A'; 3）自连接 #案例：查询 员工名和上级的名称 SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM employees e,employees m WHERE e.manager_id=m.employee_id; 外连接（LEFT JOIN / RIGHT JOIN） 左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN 包括左外连接、右外连接、全外连接。 用于查询一个表中有，另一个表没有的记录 特点： 1、外连接的查询结果为主表中的所有记录 如果从表中有和它匹配的，则显示匹配的值 如果从表中没有和它匹配的，则显示null 外连接查询结果=内连接结果+主表中有而从表没有的记录 2、左外连接，left join左边的是主表 右外连接，right join右边的是主表 3、左外和右外交换两个表的顺序，可以实现同样的效果 4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的 1）左外连接 #实现查询结果是A SELECT 字段列表 FROM A表 LEFT JOIN B表 ON 关联条件 WHERE 等其他子句; 查询男朋友不在男神表的的女神名 #左外连接 SELECT b.name,bo.* FROM beauty b LEFT OUTER JOIN boy bo ON b.boyfriend_id = bo.id WHERE b.id IS NULL; 2）右外连接 SELECT d.*,e.employee_id FROM employees e RIGHT OUTER JOIN departments d ON d.department_id = e.department_id WHERE e.employee_id IS NULL; 3）全外连接 USE girls; SELECT b.*,bo.* FROM beauty b FULL OUTER JOIN boys bo ON b.boyfriend_id = bo.id; 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。 联合查询（UNION 与 UNION ALL） SELECT*FROMAUNIONSELECT*FROMBUNION... 就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并如果使用UNION ALL，不会合并重复的记录行效率 UNION 高于 UNION ALL 合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。 合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。 语法格式： SELECT column,... FROM table1 UNION [ALL] SELECT column,... FROM table2 UNION 操作符返回两个查询的结果集的并集，去除重复记录。UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。 注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。 举例：查询部门编号>90或邮箱包含a的员工信息 #方式1 SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90; #方式2 SELECT * FROM employees WHERE email LIKE '%a%' UNION SELECT * FROM employees WHERE department_id>90; 举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息 SELECT id,cname FROM t_chinamale WHERE csex='男' UNION ALL SELECT id,tname FROM t_usmale WHERE tGender='male'; 全连接（FULL JOIN） MySQL不支持全连接可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用 SELECT*FROMALEFTJOINBONA.id=B.idUNIONSELECT*FROMARIGHTJOINBONA.id=B.id 表连接面试题 有2张表，1张R、1张S R表有ABC三列，S表有CD两列，表中各有三条记录。 R表 img S表 img 交叉连接(笛卡尔积): selectr.*,s.*fromr,s img 2.内连接结果： selectr.*,s.*fromrinnerjoinsonr.c=s.c img 3.左连接结果： selectr.*,s.*fromrleftjoinsonr.c=s.c img 4.右连接结果： selectr.*,s.*fromrrightjoinsonr.c=s.c img 5.全表连接的结果（MySql不支持，Oracle支持）： selectr.*,s.*fromrfulljoinsonr.c=s.c img 1.查询所有学生的学号、姓名、选课数、总成绩 select A.学号,A.姓名,count(B.课程号) as 选课数,sum(B.成绩) as 总成绩 from student as A left join score as B on A.学号=B.学号 group by B.学号; 2.查询平均成绩大于85的所有学生的学号、姓名和平均成绩 select A.学号,A.姓名,avg(B.成绩) as 平均成绩 from student as A left join score as B on A.学号=B.学号 group by B.学号 having avg(B.成绩)>85; 3.查询学生的选课情况：学号，姓名，课程号，课程名称 select A.学号,A.姓名,B.课程号,C.课程名称 from student as A left join score as B on A.学号=B.学号 inner join course as C on B.课程号=C.课程号; 4.查询出每门课程的及格人数和不及格人数 select 课程号, sum(case when 成绩>=60 then 1 else 0 end) as 及格人数, sum(case when 成绩<60 then 1 else 0 end) as 不及格人数 from score group by 课程号; 5.使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称 select A.课程号,A.课程名称, sum(case when B.成绩 between 85 and 100 then 1 else 0 end) as [100-85], sum(case when B.成绩<85 and B.成绩>=70 then 1 else 0 end) as [85-70], sum(case when B.成绩<70 and B.成绩>=60 then 1 else 0 end) as [70-60], sum(case when B.成绩<60 then 1 else 0 end) as [<60] from course as A left join score as B on A.课程号=B.课程号 group by A.课程号,A.课程名称; 6.查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名 select 学号,姓名 from student as A left join score as B on A.学号=B.学号 where 课程号='0003' and 成绩>80; Join查询图 img 代码实现 #中图：内连接 A∩B SELECT employee_id,last_name,department_name FROM employees e JOIN departments d ON e.`department_id` = d.`department_id`; #左上图：左外连接 SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id`; #右上图：右外连接 SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`; #左中图：A - A∩B SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL #右中图：B-A∩B SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`department_id` IS NULL #左下图：满外连接 # 左中图 + 右上图 A∪B SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL UNION ALL #没有去重操作，效率高 SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`; #右下图 #左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B） SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL UNION ALL SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`department_id` IS NULL Join 示例 建表语句 CREATE TABLE t_dept ( id INT(11) NOT NULL AUTO_INCREMENT, deptName VARCHAR(30) DEFAULT NULL, address VARCHAR(40) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARSET = utf8; CREATE TABLE t_emp ( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(20) DEFAULT NULL, age INT(3) DEFAULT NULL, deptId INT(11) DEFAULT NULL, empno int NOT NULL, PRIMARY KEY (id), KEY idx_dept_id (deptId) ) ENGINE = INNODB AUTO_INCREMENT = 1 CHARSET = utf8; INSERT INTO t_dept (deptName, address) VALUES ('华山', '华山'); INSERT INTO t_dept (deptName, address) VALUES ('丐帮', '洛阳'); INSERT INTO t_dept (deptName, address) VALUES ('峨眉', '峨眉山'); INSERT INTO t_dept (deptName, address) VALUES ('武当', '武当山'); INSERT INTO t_dept (deptName, address) VALUES ('明教', '光明顶'); INSERT INTO t_dept (deptName, address) VALUES ('少林', '少林寺'); INSERT INTO t_emp (NAME, age, deptId, empno) VALUES ('风清扬', 90, 1, 100001); INSERT INTO t_emp (NAME, age, deptId, empno) VALUES ('岳不群', 50, 1, 100002); INSERT INTO t_emp (NAME, age, deptId, empno) VALUES ('令狐冲', 24, 1, 100003); INSERT INTO t_emp (NAME, age, deptId, empno) VALUES ('洪七公', 70, 2, 100004); INSERT INTO t_emp (NAME, age, deptId, empno) VALUES ('乔峰', 35, 2, 100005); INSERT INTO t_emp (NAME, age, deptId, empno) VALUES ('灭绝师太', 70, 3, 100006); INSERT INTO t_emp (NAME, age, deptId, empno) VALUES ('周芷若', 20, 3, 100007); INSERT INTO t_emp (NAME, age, deptId, empno) VALUES ('张三丰', 100, 4, 100008); INSERT INTO t_emp (NAME, age, deptId, empno) VALUES ('张无忌', 25, 5, 100009); INSERT INTO t_emp (NAME, age, deptId, empno) VALUES ('韦小宝', 18, NULL, 100010); 案例 结合上面的查询图 表一：门派表 门派id，门派名，门派地址 表二：成员表 成员id，姓名，年龄，门派id，成员号 1.所有有门派人员的信息（要求显示门派名称） SELECT e.name,d.deptName FROM t_emp e INNER JOIN t_dept d ON e.deptId=d.id; 列出所有人员及其门派信息 SELECT e.name,d.deptName FROM t_emp e LEFT JOIN t_dept d ON e.deptId=d.id; 列出所有门派 SELECT * FROM t_dept; 所有无门派人士 SELECT * FROM t_emp WHERE deptId IS NULL; 所有无人门派 SELECT d.* FROM t_dept d LEFT JOIN t_emp e ON d.id=e.deptId WHERE e.deptId IS NULL; 所有人员和门派的对应关系 即 SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId=d.id UNION SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId=d.id; 所有没有入门派的人员和没人入的门派 SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId=d.id WHERE e.deptId IS NULL UNION SELECT * FROM t_dept d LEFT JOIN t_emp e ON d.id=e.deptId WHERE e.deptId IS NULL; 添加 CEO 字段 ALTER TABLE t_dept add CEO INT(11) ; update t_dept set CEO=2 where id=1; update t_dept set CEO=4 where id=2; update t_dept set CEO=6 where id=3; update t_dept set CEO=8 where id=4; update t_dept set CEO=9 where id=5; 8.1 求各个门派对应的掌门人名称 SELECT d.deptName,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id 8.2 求所有当上掌门人的平均年龄 SELECT AVG(e.age) FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id 8.3 求所有人物对应的掌门名称 SELECT ed.name '人物',c.name '掌门' FROM (SELECT e.name,d.ceo from t_emp e LEFT JOIN t_dept d on e.deptid=d.id) ed LEFT JOIN t_emp c on ed.ceo= c.id; SELECT e.name '人物',tmp.name '掌门' FROM t_emp e LEFT JOIN (SELECT d.id did,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id)tmp ON e.deptId=tmp.did; SELECT e1.name '人物',e2.name '掌门' FROM t_emp e1 LEFT JOIN t_dept d on e1.deptid = d.id LEFT JOIN t_emp e2 on d.ceo = e2.id ; SELECT e2.name '人物', (SELECT e1.name FROM t_emp e1 where e1.id= d.ceo) '掌门' from t_emp e2 LEFT JOIN t_dept d on e2.deptid=d.id; 其他知识点 区分重复的列名 多个表中有相同列时，必须在列名之前加上表名前缀。在不同表中具有相同列名的列，可以用表名加以区分。 SELECT employees.last_name, departments.department_name,employees.department_id FROM employees, departments WHERE employees.department_id = departments.department_id; 表的别名 使用别名可以简化查询。列名前使用表名前缀可以提高查询效率。 SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e , departments d WHERE e.department_id = d.department_id; 需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。 阿里开发规范： 【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。

相关推荐