JavaWeb-30-数据库多表查询

多表查询

零、数据准备

• 将如下中准备好的多表查询数据准备的SQL脚本导入数据库中。

  -- 创建数据库并使用
  create database db03;
  use db03;
  
  -- 部门管理
  create table tb_dept
  (
      id          int unsigned primary key auto_increment comment '主键ID',
      name        varchar(10) not null unique comment '部门名称',
      create_time datetime    not null comment '创建时间',
      update_time datetime    not null comment '修改时间'
  ) comment '部门表';
  -- 插入测试数据
  insert into tb_dept (id, name, create_time, update_time)
  values (1, '学工部', now(), now()),
         (2, '教研部', now(), now()),
         (3, '咨询部', now(), now()),
         (4, '就业部', now(), now()),
         (5, '人事部', now(), now());
  
  
  -- 员工管理
  create table tb_emp
  (
      id          int unsigned primary key auto_increment comment 'ID',
      username    varchar(20)      not null unique comment '用户名',
      password    varchar(32) default '123456' comment '密码',
      name        varchar(10)      not null comment '姓名',
      gender      tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
      image       varchar(300) comment '图像',
      job         tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
      entrydate   date comment '入职时间',
      -- 部门 与 员工 的关系：一对多，因此在员工表中添加逻辑外键
      dept_id     int unsigned comment '部门ID',
      create_time datetime         not null comment '创建时间',
      update_time datetime         not null comment '修改时间'
  ) comment '员工表';
  -- 插入测试数据
  INSERT INTO tb_emp
  (id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time)
  VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', 2, now(), now()),
         (2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', 2, now(), now()),
         (3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', 2, now(), now()),
         (4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', 2, now(), now()),
         (5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', 2, now(), now()),
         (6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', 1, now(), now()),
         (7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', 1, now(), now()),
         (8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', 1, now(), now()),
         (9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', 1, now(), now()),
         (10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', 1, now(), now()),
         (11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 5, '2007-02-01', 3, now(), now()),
         (12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 5, '2008-08-18', 3, now(), now()),
         (13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 5, '2012-11-01', 3, now(), now()),
         (14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', 2, now(), now()),
         (15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', 2, now(), now()),
         (16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2007-01-01', 2, now(), now()),
         (17, 'chenyouliang', '123456', '陈友谅', 1, '17.jpg', NULL, '2015-03-21', NULL, now(), now());
  
  -- 查询表中所有数据
  select * from tb_dept;
  select * from tb_emp;
  

• 效果

  

  

---

一、概述

1. 介绍

• 多表查询: 指从多张表中查询数据

  

---

• 笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合 和 B集合)的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)
  

  ---

---

• 只需要使 员工表中的外键——部门ID 与 部门表中的主键ID 对应即可！

---

2. 分类

• 连接查询
  • 内连接：相当于查询A、B交集部分数据
  • 外连接
    • 左外连接：查询左表所有数据(包括两张表交集部分数据)
    • 右外连接：查询右表所有数据(包括两张表交集部分数据)
• 子查询

---

二、内连接

1. 介绍

• 内连接：查询的是两张表：A表 与 B表 之间 交集 的部分的数据

  

---

2. 语法

• 隐式内连接：

  select 字段列表  from  表1 , 表2  where  条件 ... ;
  

• 显式内连接：

  • inner：可以省略

  select 字段列表  from  表1 [ inner ]  join 表2 on 连接条件 ... ;
  

---

3. 需求实现

• 隐式内连接：

  

---

• 显式内连接

  

---

• 表起别名：

  

---

4. 问题

• 提问：

  

  

---

• 解答：

  

---

---

三、外连接

1. 介绍

• 左外连接：以左表为基准，会完全包含左表的数据，当然也包含两张表交集部分的数据

  

---

• 右外连接：以右表为基准，会完全包含右表的数据，当然也包含两张表交集部分的数据

  

---

2. 语法

• outer：可以省略不写

• 左外连接：

  select 字段列表  from  表1 left [ outer ] join 表2 on 连接条件 ... ;
  

• 右外连接：

  select 字段列表  from  表1 right [ outer ] join 表2 on 连接条件 ... ;
  

---

3. 需求实现

• 左外连接：

  

  ---

  

---

• 右外连接：

  

  

  ---

  

---

• 右外连接 可以替换成 左外连接：

  

---

---

四、子查询

1. 介绍

• SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

---

2. 语法

• 子查询外部的语句可以是 insert / update / delete / select 的任何一个，最常见的是 select。

select * from  t1  where column1 = ( select column1 from t2 … );

---

3. 分类

• 标量子查询：子查询返回的结果为单个值
• 列子查询：子查询返回的结果为一列
• 行子查询：子查询返回的结果为一行
• 表子查询：子查询返回的结果为多行多列

---

4. 标量-子查询

(1) 介绍

• 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

  

• 常用的操作符：= <> > >= < <=

---

(2) 需求实现

• 步骤：
  • ① 分析需求
  • ② 分解
  • ③ 合并

---

• 查询 “教研部” 的所有员工信息

  

---

• 查询在 “方东白” 入职之后的员工信息

  

---

---

5. 列-子查询

(1) 介绍

• 子查询返回的结果是一列（可以是多行）

  

• 常用的操作符：in 、not in等

---

(2) 需求实现

• 查询 “教研部” 与 “咨询部” 的部门ID

  

---

6. 行-子查询

(1) 介绍

• 子查询返回的结果是一行（可以是多列）。

  

• 常用的操作符：= 、<> 、in 、not in

---

(2) 需求实现

• 查询 “韦一笑” 的入职日期 及 职位相同的员工信息

  

  ---

  

  ---

  

  ---

  

  ---

  

---

7. 表-子查询

(1) 介绍

• 子查询返回的结果是多行多列，常作为临时表

  

• 常用的操作符：in

---

(2) 需求实现

• 查询 入职日期是 ‘2006-01-01’ 之后的员工信息，及其部门名称

  

  ---