1、常规联表查询

创建两张数据库表如下:

mysql> select * from dept;+----+-----------+| id | deptName  |+----+-----------+|  3 | 教学部    |+----+-----------+1 row in set (0.00 sec)mysql> select * from employee;+----+---------+--------+---------------------+| id | empName | deptId | regTime             |+----+---------+--------+---------------------+|  1 | 老邢    |      3 | 2023-06-29 16:28:09 ||  4 | 老万    |      3 | 2023-06-29 16:28:47 ||  5 | 小磊    |      3 | 2023-06-29 16:28:48 |+----+---------+--------+---------------------+

联表查询 老邢属于哪个部门?

mysql> select employee.id, employee.empName, dept.deptName  #查什么    ->from  employee,dept #从哪查   多张表使用逗号隔开    ->where  employee.deptId = dept.id and employee.empName = "老邢";#约束的条件一定要带
+----+---------+-----------+| id | empName | deptName  |+----+---------+-----------+|  1 | 老邢    | 教学部    |+----+---------+-----------+

联表查询的时候对字段可以起别名

mysql> select e.id, e.empName, d.deptName    -> from employee e, dept d    -> where e.deptId = d.id;+----+---------+-----------+| id | empName | deptName  |+----+---------+-----------+|  1 | 老邢    | 教学部    ||  4 | 老万    | 教学部    ||  5 | 小磊    | 教学部    |+----+---------+-----------+mysql> select e.id as "编号", e.empName as "名字", d.deptName as "部门"    -> from employee e, dept d    -> where e.deptId = d.id;+--------+--------+-----------+| 编号   | 名字   | 部门      |+--------+--------+-----------+|      1 | 老邢   | 教学部    ||      4 | 老万   | 教学部    ||      5 | 小磊   | 教学部    |+--------+--------+-----------+

2、内连接和外连接

join 用于根据两个或多个表中的列之间的关系,从这些表中查询数据。

2.1、内连接  inner join

语法格式:

select 字段from 表1inner join 表2on 约束关系

例1:#查教学部有多少人

mysql> select d.deptName, e.empName    -> from dept d    -> inner join employee e    -> on d.id = e.deptId;+-----------+---------+| deptName  | empName |+-----------+---------+| 教学部    | 老邢    || 教学部    | 老万    || 教学部    | 小磊    |+-----------+---------+

例2:查看老邢属于哪个部门

mysql> select e.empName, d.deptName    -> from employee e    -> inner join dept d    -> on e.deptId = d.id    -> where e.empName = "老邢";+---------+-----------+| empName | deptName  |+---------+-----------+| 老邢    | 教学部    |+---------+-----------+

2.2、外连接a、左外连接

left join关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。也要展示。

语法格式:

select 字段 from 左表1 left outer join 右表2 on 约束条件

左边表全部显示,右边表去匹左边表,如果没有匹配上就显示null  例:

mysql> select d.deptName, e.empName    -> from dept d    -> left outer join employee e    -> on d.id = e.deptId;+-----------+---------+| deptName  | empName |+-----------+---------+| 教学部    | 老邢    || 教学部    | 老万    || 教学部    | 小磊    || 技术部    | NULL    |+-----------+---------+

b、右外连接

语法格式:

select 字段  from 左表1  right outer join 右表2 on  约束条件

以右边表为主右边表要全部显示,左边如果没有匹配到就显示为null  例:

mysql> select *    -> from employee e    -> right outer join dept d    -> on e.deptId = d.id;+------+---------+--------+---------------------+----+-----------+| id   | empName | deptId | regTime             | id | deptName  |+------+---------+--------+---------------------+----+-----------+|    1 | 老邢    |      1 | 2022-04-25 10:08:04 |  1 | 教学部    ||    2 | 雪珂    |      2 | 2022-04-25 10:08:17 |  2 | 行政部    ||    3 | 凯旗    |      3 | 2022-04-25 10:08:30 |  3 | 技术部    ||    4 | 小磊    |      1 | 2022-04-25 10:08:43 |  1 | 教学部    ||    5 | 帅栋    |      1 | 2022-04-25 10:08:56 |  1 | 教学部    || NULL | NULL    |   NULL | NULL                |  4 | 后勤部    |+------+---------+--------+---------------------+----+-----------+

2.3、一对一、一对多查询

例:现有学生表和老师表

一个老师对应着多个学生(一对多) 一个学生对应着咱们的一个老师(一对一)

a、一对一查询(使用内连接)

查询邱博的老师? 一对一

select ts.s_name, tt.t_namefrom t_student tsinner joint_teacher tton ts.teacher_id = tt.t_idwhere ts.s_name = "邱博"

b、一对多查询(使用内连接)

查询老邢所带的学生

select tt.t_name, ts.s_namefrom t_teacher ttinner join t_student tson tt.t_id = ts.teacher_idwhere tt.t_name = "老邢"

c、一对多查询(不使用内连接)

select tt.t_name, ts.s_namefrom t_student ts, t_teacher ttwhere ts.teacher_id = tt.t_id and tt.t_name = '老邢'

2.4、多对多查询

例:

学生和课程的关系
学生选课,
一个学生对应着多个课程
一个课程要被多个学生选择
一个课程对应着多个学生

创建学生表、课程表、学生_课程关联表,三表联查 两个inner join

#看所有学生选择了哪些的课程select ts.s_name, tc.c_namefrom t_stu tsinner join stu_course scon ts.s_id = sc.stu_idinner join t_course tcon sc.course_id = tc.c_id#查看狗蛋选择了哪些课程select ts.s_name, tc.c_namefrom t_stu tsinner join stu_course scon ts.s_id = sc.stu_idinner join t_course tcon sc.course_id = tc.c_idwhere ts.s_name = "狗蛋"#Java被哪些学生选择了先写课程表select *from t_course tcinner join stu_course scon tc.c_id = sc.course_idinner join t_stu tson sc.stu_id = ts.s_idwhere tc.c_name = "Java"