[学习笔记] MySQL数据库基础之多表查询（一）

多仔ヾ发布于 2021-11-06 • 763 阅读

首页 · 随笔 · 生活 · 学习 · 搜索

· ·

时光大概如你所说，活在当下，看见远方。 - @多仔ヾ

##### 多表关系

1、一对多（多对一）：

（1）案例：部门与员工。

（2）关系：一个部门对应多个员工，一个员工对应一个部门。

（3）实现：在多的一方建立外键，只想一的一方的主键。

2、多对多：

（1）案例：学生与课程。

（2）关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择。

（3）实现：建立第三张中间表，中间表包含两个外键，分别关联两方主键。

3、一对一：

（1）案例：用户与用户详情。

（2）关系：一个用户对应一个详情数据。

（3）实现：在任意一方加入外键且为唯一外键，关联另一方主键。

-----

##### 多表查询概述

1、初始化多表查询测试环境，创建emp表与dept表并插入测试数据：

```sql
# 创建部门表
CREATE TABLE dept(
    id INT AUTO_INCREMENT COMMENT '部门ID' PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT '部门名称'
)COMMENT '部门表';

# 插入部门表数据
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,
'销售部'), (5, '总经办'), (6, '人事部');

# 创建员工表
CREATE TABLE emp(
    id INT AUTO_INCREMENT COMMENT '员工ID' PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    age INT COMMENT '年龄',
    job VARCHAR(20) COMMENT '职位',
    salary INT COMMENT '薪资',
    entrydate DATE COMMENT '入职时间',
    managerid INT COMMENT '直属领导ID',
    dept_id INT COMMENT '部门ID'
)comment '员工表';

# 添加部门表-员工表外键
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES
dept(id);

# 插入员工表数据
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);
```

2、笛卡尔积：

（1）在数学中，集合A和集合B的所有组合情况称为笛卡尔积。在MySQL中，即员工表emp所有的记录与部门表dept所有记录的所有组合情况。

```sql
SELECT * FROM emp, dept;
```

（2）在多表查询中，需要消除无效的笛卡尔积，只保留两张表关联部分的数据。

```sql
SELECT * FROM emp, dept WHERE emp.dept_id = dept.id
```

-----

##### 内连接

1、内连接：两张表交集部分的数据。

（1）隐式内连接：

```sql
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
```

（2）显式内连接：

```sql
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;
```

2、内连接的实现示例：

（1）查询每一个员工的姓名，及关联的部门的名称，使用隐式内连接实现：

```sql
SELECT emp.name , dept.name
FROM emp, dept
WHERE emp.dept_id = dept.id;
```

（2）查询每一个员工的姓名，及关联的部门的名称，使用显式内连接实现：

```sql
SELECT e.name, d.name
FROM emp AS e
    INNER JOIN dept AS d
        ON e.dept_id = d.id;
```

-----

##### 外连接

1、左外连接：查询左表的所有数据，包含左表和右表交集部分的数据。

```sql
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;
```

2、右外连接：查询右表的所有数据，包含左表和右表交集部分的数据。

```sql
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;
```

3、外连接的实现示例：

（1）查询所有员工的数据，和对应的部门信息：

```sql
SELECT e.*, d.name
FROM emp AS e
    LEFT JOIN dept AS d
        ON e.dept_id = d.id;
```

（2） 查询所有部门的数据，和对应的员工信息：

```sql
SELECT d.*, e.*
FROM dept AS d
    LEFT JOIN emp AS e
        ON e.dept_id = d.id;
```

如无特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：时光话 - https://l5v.cn/archives/345/

上一篇 [学习笔记] MySQL数据库基础之约束

下一篇 [学习笔记] MySQL数据库基础之多表查询（二）

关于 · 归档 · 邻居 · 搜索 · 网站地图