首先进行表数据的创建，为后续多表查询提供基础支持。

create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';

insert into student values
(null, '黛绮丝', '2000100101'),
(null, '谢逊', '2000100102'),
(null, '殷天正', '2000100103'),
(null, '韦一笑', '2000100104');

create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';

insert into course values
(null, 'Java'),
(null, 'PHP'),
(null , 'MySQL') ,
(null, 'Hadoop');


create table student_course(
    id int auto_increment comment'主键' primary key ,
    studentid int not null comment '学生ID',
    courseid int not null comment'课程ID',
    constraint fk_courseid foreign key (courseid) references course(id),
    constraint fk_studentid foreign key (studentid) references student(id)
) comment '学生课程中间表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);


create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';

insert into salgrade values(1,0,3000);
insert into salgrade values(2,3001,5000);
insert into salgrade values(3,5001,8000);
insert into salgrade values(4,8001,10000);
insert into salgrade values(5,10001,15000);
insert into salgrade values(6,15001,20000);
insert into salgrade values(7,20001,25000);
insert into salgrade values(8,25001,30000);
select * from salgrade;

create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1: 男，2: 女',
    phone char(11) comment '手机号'
) comment '用户基本信息表';


create table tb_user_edu(
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool varchar(50) comment '中学',
    university varchar(50) comment '大学',
    userid int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';

insert into tb_user(id, name, age, gender, phone) values
(null,'黄渤',45,'1','18800001111'),
(null,'冰冰',35,'2','18800002222'),
(null,'码云',55,'1','18800008888'),
(null,'李彦宏',50,'1','18800009999');


insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values
(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

多表查询概述

在数据库操作中，多表查询是实现复杂数据检索的核心手段。当涉及多个相关联的表时，通常会生成笛卡尔积——即两个集合A与B中所有可能的组合情况。然而，在实际应用中需通过设定连接条件来消除无效的笛卡尔积，从而获取有意义的数据结果。

select * from emp1,dept where emp1.id = dept.id;

多表查询的主要分类

多表查询主要分为连接查询和子查询两大类，其中连接查询又可细分为内连接、外连接以及自连接。

一、连接查询

1. 内连接（Inner Join）

内连接用于获取两个表之间的交集部分数据，仅返回满足连接条件的记录。

• 隐式内连接：使用逗号分隔表名，并在WHERE子句中指定条件。
  语法格式：SELECT 字段列表 FROM 表1, 表2 WHERE 条件...

? ? ? select emp.name,dept.name from emp ,dept where emp.dep_id = dept.id;(查询不到null）
? ? ? select * from emp e, dept d where e.dept_id = d.id;

• 显式内连接：采用JOIN关键字明确表达连接逻辑，结构更清晰，推荐使用。
  语法格式：SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...

select e.name , d.name from emp e inner join dept d on e.dept_id =d.id;

2. 外连接（Outer Join）

外连接可以保留主表中的全部记录，即使另一表无匹配项也会显示，缺失字段以NULL填充。

• 左外连接：以左侧表为主表，返回其所有数据及与右表匹配的部分。
  语法格式：SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

select e.*,d.name from emp e  left [outer] join dept d on e.dept_id=d.id;

• 右外连接：以右侧表为主表，返回其所有数据及与左表匹配的部分。
  语法格式：SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

select d.* ,e.* from emp e right outer join dept d on d.id = e.dept_id;

例如：查询emp表的所有员工信息及其对应的部门信息，可通过左外连接实现，类似于Excel中的VLOOKUP功能。

3. 自连接（Self Join）

自连接指的是同一张表与其自身进行连接操作，常用于处理层级或上下级关系（如员工与经理），必须为表设置别名以便区分。

语法格式：SELECT 字段列表 FROM 表a 别名a JOIN 表a 别名b ON 条件...;

自连接既可以表现为内连接形式，也可以作为外连接使用，具体取决于业务需求。

-- 查询员工，及其所属领导的名字
select a.name, b.name from emp a,emp b where a.managerid =b.id;
-- 查询所有员工emp 及其领导名字emp，如果员工没有领导，也需要查询出来
select a.name,b.name from emp a left outer join emp b on a.managerid = b.id;

二、联合查询（Union Query）

联合查询通过UNION或UNION ALL将多个SELECT语句的结果合并成一个结果集。

• UNION ALL：直接合并所有结果，包含重复行。
• UNION：合并后自动去除重复记录。

注意：参与联合的各查询语句，其列数必须一致，且对应字段的数据类型应兼容。

-- select 字段列表 from 表a   ...  union[all]  select 字段列表 from 表b ....;
-- 将薪资低于5000 的员工 和年龄大于50的员工全部查询
select a.* from emp a where age >50 union select  b.* from emp b where salary<5000;

三、子查询（Subquery）

子查询是指在一个查询语句内部嵌套另一个SELECT语句，也称为嵌套查询。外部语句可以是SELECT、INSERT、UPDATE或DELETE等操作。

根据子查询返回结果的形式，可分为以下几种类型：

1. 标量子查询

返回单一值（一行一列），常用于与单个值比较。

常用操作符包括：=、<、>、<=、>=、<> 等。

示例场景：查找工资高于“平均工资”的员工信息。

-- 查询‘销售部’的所有员工信息   查询销售部门id、根据销售部门id查询员工
select * from emp where dept_id=(select id from dept where name ='销售部');
-- 入职之后的员工信息
select entrydate from emp where name='韦一笑';
select * from emp where entrydate >(select entrydate from emp where name='韦一笑');

2. 列子查询

返回一列多行的数据结果，通常用于判断某个值是否存在于该列中。

常用操作符：IN、NOT IN、ANY、ALL、SOME。

-- 销售部和市场部的所有员工信息
-- select id from dept where name= '销售部' or '市场部';
-- select * from emp where dept_id in (2,4);
select * from emp where dept_id in (select id from dept where name= '销售部' or '市场部');

-- 比研发所以工资都高的员工；
select * from emp where salary >(select max(salary) from emp where dept_id=(select id from dept where name ='研发部'));
select * from emp where salary >all (select salary from emp where dept_id = (select id from dept where name ='研发部'));
-- 比研发部任意高
select * from emp where salary >any (select salary from emp where dept_id = (select id from dept where name ='研发部'));

3. 行子查询

返回一行多列的数据，可用于与多字段同时比较。

常用操作符：=、<>、IN、NOT IN。

-- 张无忌的薪资和直系领导、查询与他的薪资及直属领导相同的信息；
select salary,managerid from emp where name='张无忌';
select * from emp where (salary,managerid)=(select salary,managerid from emp where name='张无忌');

4. 表子查询

返回一个虚拟的表结果，常用于FROM子句中作为临时表使用。

常用操作符：IN 或与其他表进行连接操作。

-- 查询两人的薪资和职位、在完成查询
select job,salary from emp where name = '小小' or'杨逍';
select * from emp where (job,salary) in (select job,salary from emp where name = '小小' or name='杨逍');
-- 入职日期是‘2006-01-01’之后的员工及其部门信息；
select * from emp where entrydate >'2006-01-01';
select e.*,d.* from (select * from emp where entrydate >'2006-01-01') e left join dept d on e.dept_id = d.id;

数据库表间关系设计原则

在进行数据库建模时，合理设计表之间的关系至关重要。常见的关联方式有以下三种：

dept_id

id

student_id

course_id

user_id

多表连接查询总结

INNER JOIN

JOIN

LEFT JOIN

RIGHT JOIN

SELECT ... FROM 表A, 表B WHERE 关联条件...

SELECT ... FROM 表A [INNER] JOIN 表B ON 关联条件...

SELECT ... FROM 表A LEFT [OUTER] JOIN 表B ON 条件...

SELECT ... FROM 表A RIGHT [OUTER] JOIN 表B ON 条件...

NULL

子查询分类详述

=

<>

>

<

>=

<=

IN

以下为练习过程中的相关记录与学习笔记整理：

-- 1.查询员工的姓名、年龄、职位、部门信息。(隐式内连接）多张表用，分隔
-- 连接条件 e.dept_id = d.id
select e.name,e.age,e.job,d.name from emp e,dept d where e.dept_id=d.id;
select e.name,e.age,e.job,d.name from emp e left join dept d on e.dept_id = d.id;
-- 2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息。(显示内连接）
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id = d.id where e.age <30;
select id from emp where age<30;
select e.name,e.age,e.job,d.name from  (select * from emp where age<30)  e left join dept d on e.dept_id = d.id;
-- 3.查询拥有员工的部门ID、部门名称。
select distinct d.id,d.name from emp e ,dept d where e.dept_id=d.id;-- (内连接)
select * from dept where id in (select dept_id from emp);
-- 4.查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来。(外连接）
select e.name,d.name from (select * from emp e where age>40) e left join dept d on e.dept_id=d.id;-- 没有null
select e.*,d.name from emp e left join dept d on e.dept_id = d.id where e.age >40;
-- 5.查询所有员工的工资等级。（emp salgrade）连接条件 emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select e.name,s.grade from emp e,salgrade s where e.salary >= s.losal and e.salary <= s.hisal;-- 隐式内连接，条件用where
select e.name,s.grade from emp e,salgrade s where e.salary between s.losal and s.hisal;
-- 6.查询"研发部" 所有员工的信息及工资等级。
-- emp salgrade dept e.salary between s.losal and s.hisal，e.dept_id=d.id
select e.*, s.grade, d.name
from emp e,
     dept d,
     salgrade s
where e.dept_id = d.id
  and (e.salary between s.losal and s.hisal)
  and d.name = '研发部';
-- 7.查询"研发部"员工的平均工资。
select avg(salary) from emp e ,dept d where e.dept_id=d.id and d.name ='研发部';
select avg(salary) from emp where dept_id =(select id from dept where name='研发部');
-- 多表查询通常更高效。数据库可优化连接过程，一次性完成数据匹配和聚合。	子查询通常效率较低。需先执行子查询得到一个结果（或列表），再执行外层查询。
-- 8.查询工资比"小小"高的员工信息。标量子查询
select salary from emp where name ='小小';
select * from emp where salary > (select salary from emp where name ='小小');
-- 9. 查询比平均薪资高的员工信息。
select * from emp where salary > (select avg(salary) from emp);
-- 10.查询低于本部门平均工资的员工信息。
select avg(e1.salary) from emp e1 where e1.dept_id =1;
select * from emp e2 where e2.salary <(select avg(e1.salary) from emp e1 where e1.dept_id =e2.dept_id);
-- 11.查询所有的部门信息,并统计部门的员工人数。
-- select *from dept;
-- select dept_id,count(dept_id) from emp group by dept_id ;
select * from dept d left join (select dept_id,count(dept_id) from emp group by dept_id ) e on d.id=e.dept_id;
select id,name ,(select count(*) from emp where emp.dept_id=dept.id) '人数' from dept;
-- 12.查询所有学生的选课情况,展示出学生名称,学号,课程名称
-- 连接条件 student.id=student_course.studentid,course.id = student_courseid
select s.name,s.no,c.name from student s,student_course sc,course c where s.id=sc.studentid and sc.courseid = c.id;
select s.name,s.no,ss.name '选课情况' from student s inner join (select sc.*,c.name from course c right join student_course sc on c.id=sc.courseid) ss on s.id=ss.studentid;
-- select sc.*,c.name from course c right join student_course sc on c.id=sc.courseid;
SELECT s.name AS ‘学生姓名‘, s.no AS ‘学号‘, c.name AS ‘课程名称‘
FROM student s
INNER JOIN student_course sc ON s.id = sc.studentid
INNER JOIN course c ON sc.courseid = c.id
ORDER BY s.id;-- 标准

查询“销售部”和“市场部”的全部员工信息，通常可以通过行子查询实现。这类查询返回的结果为单行多列的数据形式，适用于需要从一行中获取多个字段值的场景。

NOT IN

ANY

SOME

ALL

另一种情况是查找与某一员工在“部门和工资”两个维度上完全相同的其他员工。这种需求适合使用表子查询来完成，其结果是一个包含多行多列的数据表，可用于进一步的筛选或关联操作。

=

<>

IN

NOT IN

该表子查询的结果可作为派生表（derived table）使用，例如将查询结果以临时表的形式存在，供主查询进行后续的连接操作或条件过滤。

EXISTS

JOIN

四、使用建议

明确表间关系是基础：在编写连接查询之前，必须理清各表之间的逻辑关系，如一对一、一对多或多对多。这直接影响到

JOIN

语句中的连接条件与连接类型的选择。

连接条件至关重要：在

ON

子句中设定的关联条件，一般遵循“外键字段 = 主键字段”的原则。若条件设置错误，可能导致查询结果不准确，甚至产生笛卡尔积现象。

关于

INNER JOIN

与

LEFT JOIN

的选择：这两种连接方式最为常用。
当需要获取两表中均存在的匹配数据时，应选用

INNER JOIN

；
若需返回左表所有记录，并附带右表的匹配项，则应使用

LEFT JOIN

。
此外，

RIGHT JOIN

的功能可通过调换表顺序后改用

LEFT JOIN

来实现，有助于保持SQL风格的一致性。

合理定位子查询的使用：虽然子查询功能灵活，但在多数情况下可以被

JOIN

替代。只有当业务逻辑异常复杂，或使用连接难以清晰表达意图时，才推荐采用子查询。反之，在追求代码可读性或执行效率时，建议将子查询（特别是表子查询）重构为

JOIN

的形式。

性能优化提示：为参与连接的字段（尤其是外键）建立索引，能够显著提升查询效率，减少全表扫描带来的开销。

以上内容为学习过程中的总结记录，仅供参考。