MySQL学习3--联表查询
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 表1 inner join 表2 on 约束关系
例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_name from t_student ts inner joint_teacher tt on ts.teacher_id = tt.t_id where ts.s_name = "邱博"
b、一对多查询(使用内连接)
查询老邢所带的学生
select tt.t_name, ts.s_name from t_teacher tt inner join t_student ts on tt.t_id = ts.teacher_id where tt.t_name = "老邢"
c、一对多查询(不使用内连接)
select tt.t_name, ts.s_name from t_student ts, t_teacher tt where ts.teacher_id = tt.t_id and tt.t_name = '老邢'
2.4、多对多查询
例:
学生和课程的关系
学生选课,
一个学生对应着多个课程
一个课程要被多个学生选择
一个课程对应着多个学生
创建学生表、课程表、学生_课程关联表,三表联查 两个inner join
#看所有学生选择了哪些的课程 select ts.s_name, tc.c_name from t_stu ts inner join stu_course sc on ts.s_id = sc.stu_id inner join t_course tc on sc.course_id = tc.c_id #查看狗蛋选择了哪些课程 select ts.s_name, tc.c_name from t_stu ts inner join stu_course sc on ts.s_id = sc.stu_id inner join t_course tc on sc.course_id = tc.c_id where ts.s_name = "狗蛋" #Java被哪些学生选择了 先写课程表 select * from t_course tc inner join stu_course sc on tc.c_id = sc.course_id inner join t_stu ts on sc.stu_id = ts.s_id where tc.c_name = "Java"
热门相关:有个人爱你很久 半仙 回眸医笑,冷王的神秘嫡妃 道君 变身蜘蛛侠