MySQL数据库操作学习(4)多表查询

一.表查询补充

1.1、聚合筛选

having 对分组之后的数据再一次进行筛选

注意having通常和group by 一起使用

select * from 表名 group by 字段名  having 条件

# 查看性别个数大于8
select sex , count(id) as Sum from employee GROUP BY sex having count(id) > 8;

# 统计各个职位平均工资大于5000职位名
select post, avg(salary) from employee GROUP BY  post having avg(salary) > 5000;

补充

HAVING 和 WHERE 是 SQL 查询中用于筛选数据的两个关键字，它们虽然都可以用于限制结果集，但在使用时有一些区别：

1. WHERE 子句：

   • WHERE 子句用于在对表中的行进行过滤时指定条件。
   • WHERE 子句在查询中的位置通常位于 FROM 子句之后、GROUP BY 子句之前。
   • WHERE 子句中的条件用于筛选行，只有满足条件的行才会包含在结果集中。
   • WHERE 子句中的条件通常基于单个行的列值，而不是基于聚合函数的结果。

2. HAVING 子句：

   • HAVING 子句用于在对分组后的结果进行过滤时指定条件。
   • HAVING 子句在查询中通常位于 GROUP BY 子句之后。
   • HAVING 子句中的条件用于筛选分组后的结果，只有满足条件的分组才会包含在结果集中。
   • HAVING 子句中的条件通常基于聚合函数的结果，例如 COUNT()、SUM() 等。

总结来说，WHERE 子句用于筛选行，而 HAVING 子句用于筛选分组后的结果。在未使用 GROUP BY 子句时，通常只使用 WHERE 子句；而在使用 GROUP BY 子句进行分组后，可以使用 HAVING 子句来对分组后的结果进行进一步筛选。

希望这个解释能够帮助您理解 HAVING 和 WHERE 的区别。如果您有任何其他问题或需要进一步解释，请随时告诉我。

1.2、子查询

在 select 子句中，是可以嵌套select语句

select * from （select * from 表名 where 条件） as 名字 where 条件;

子查询：
select * from 表名 where 条件(select 字段名 from 表名 where 条件)

# 我们第一步查询王五工资
 select salary from employee where name = '王五';

# 第二步把上面的查询出来的结果作为一个条件限制查询
select * from employee where salary > (select salary from employee where name = '王五');

二、MySQL多表操作

1、表与表的关系

1.1、多表之间的关系

1. 一对一
	身份证对应你这个人

2. 一对多
	直播就是一对多
	
3.多对多
	城市和人

1.2、外键约束

foreign key (外键) : 建立表与表之间的某种约束，这个关系的存在可以加强表与表之间的关系

主表: 被外键链接进来（这里必须有一个唯一约束（主键，unique））
从表 设计外键约束进行链接表 不需要唯一约束

①一对一

# 创建一个主表

create  table t9 (
	id int PRIMARY key auto_increment,
	name char(5)
);


# 创建一个从表

CREATE table t10(
	id int,
	age int,

  # 外键的前置必须是唯一约束

t10id int unique,
foreign key (t10id) references t9(id)

);

foreign key (t10id) references t9(id) 这个语句是在创建表 t10 时定义了一个外键约束。让我解释一下这个语句的含义：

• foreign key (t10id): 这部分表示在表 t10 中创建一个外键约束，该外键约束作用在 t10id 列上。也就是说，t10id 列是外键列，将用于建立与其他表的关联。

• references t9(id): 这部分指定了外键列 t10id 在表 t10 中参考的是哪个表的哪个列。具体来说，它指定了 t10id 列在表 t10 中的值必须存在于表 t9 的 id 列中。换句话说，t10id 列的值必须是 t9 表中 id 列的值之一，否则会违反外键约束。

因此，这个外键约束确保了表 t10 中的 t10id 列的值必须是表 t9 中 id 列的值之一，从而建立了表 t10 和 t9 之间的关联关系。这有助于维护数据的一致性，确保在进行数据操作时，相关的数据是有效和一致的

insert into t9(name) VALUES
("001"),
("002"),
("003"),
("004"),
("005"),
("006"),
("007"),
("008"),
("009");

insert into t10 VALUES
(1,15 ,2),
(2,25 ,1),
(4,17 ,3),
(5,55 ,4),
(6,16 ,5),
(7,95 ,6);

表结构和数据如下：

表 t9：

+----+------+
| id | name |
+----+------+
| 1  | 001  |
| 2  | 002  |
| 3  | 003  |
| 4  | 004  |
| 5  | 005  |
| 6  | 006  |
| 7  | 007  |
| 8  | 008  |
| 9  | 009  |
+----+------+

表 t10：

+----+-----+-------+
| id | age | t10id |
+----+-----+-------+
| 1  | 15  | 2     |
| 2  | 25  | 1     |
| 4  | 17  | 3     |
| 5  | 55  | 4     |
| 6  | 16  | 5     |
| 7  | 95  | 6     |
+----+-----+-------+

在表 t10 中，t10id 列是一个外键，参考了表 t9 中的 id 列。t10id 列在表 t10 中设置了唯一约束，确保每个值都是唯一的。

②一对多

# 部门表

create table t11(
	id int primary key auto_increment,
	depName  char(10),
	job char(5)
);

# 员工表

create table t12(
	id int primary key  auto_increment,
	name char(5),
	t15id int,
	foreign key (t15id) references t11(id)
);


insert into t11(depName, job) VALUES
("财务部", "会计"),
("讲师部", "讲师"),
("开发部", "开发程序员"),
("其他", "保洁员");


insert into t12(name, t15id) VALUES
("小明", 2),
("笑脸", 3),
("海绵宝宝", 4),
("蜡笔小新", 1),
("xxxxx", 2),
("xxxx", 2);

根据您提供的 SQL 语句和插入数据，我来为您展示表 t11 和 t12 中的数据。让我查询一下：

表 t11 数据：

id	depName	job
1	财务部	会计
2	讲师部	讲师
3	开发部	开发程序员
4	其他	保洁员

表 t12 数据：

id	name	t15id
1	小明	2
2	笑脸	3
3	海绵宝宝	4
4	蜡笔小新	1
5	xxxxx	2
6	xxxx	2

③总结

我们不难发现当1对1的时候，只有主表需要一个主键。1对多的时候，两个表都需要主键

我们可以知道，主键的作用是用来给每个样本贴一个标签，使得每个样本都具有独立性，而外键的作用便是让具有关系的两个表的主键具有联系(1对1可以认为第二个表的主键恒为1)

最关键的一点是，1对1的表的外键是唯一不可重复的

而1对多的表是可以重复。

④删链接表的数据

如果主表数据由从表数据进行依赖，那么我们必须把从表的数据依赖清空，再删除

delete from t12 where t15id = 4;
delete from t11 where depName = "其他";

1.3 联合查询

三大链接：
内连接
左连接
右连接

inner join -- 内连接
left join  -- 左连接
right join -- 右连接

# 如果没有这种联合查询会出现笛卡尔积反应

select * from t11, t12;

笛卡尔积反应是指在关系型数据库中，当执行没有任何条件限制的两个表的连接操作时，会产生笛卡尔积现象。这种现象会导致结果集中包含两个表中所有可能的组合，即两个表的每一行都与另一个表的每一行进行组合，从而产生非常庞大的结果集。

笛卡尔积反应通常是由于在没有指定连接条件的情况下使用了 JOIN 操作，或者在 WHERE 子句中没有指定任何关联条件而进行了表的连接操作。这种情况下，数据库系统会对两个表中的所有行进行组合，导致结果集中包含了所有可能的组合，可能会对性能产生负面影响。

为避免笛卡尔积反应，应该在进行表连接操作时明确指定连接条件，确保连接操作只返回需要的数据组合。同时，在编写 SQL 查询时，应该注意避免无意义的连接或者未指定连接条件的情况，以避免产生意外的笛卡尔积结果。

# 内连接， 将两个表有关联的数据拼接在一起

select * from 表1 inner join 表2 on 连接条件(关联的字段)
select * from t11 inner JOIN t12 on t11.id = t12.t15id;

#t11和t12调换位置是没有影响的
# 内连接会返回那些在连接条件下能够找到匹配的行，如果没有找到匹配的行，那么结果集将为空。

# 左连接, 就是以左边为主，右边数据进行匹配，如果匹配不到的数据就是以null空值填充

select * from t11 left join t12 on t11.id = t12.t15id;

# 右连接, 就是以右边为主，左边数据进行匹配，如果匹配不到的数据就是以null空值填充

select * from  t12 right join t11 on t11.id = t12.t15id;

例子

假设我们有两个表 t11 和 t12，它们的结构如下：

表 t11:

| id | name  |
|----|-------|
| 1  | Alice |
| 2  | Bob   |
| 3  | Carol |

表 t12:

| id | age | t15id |
|----|-----|-------|
| 1  | 25  | 1     |
| 2  | 30  | 2     |

现在我们执行以下左连接查询：

SELECT * FROM t11 LEFT JOIN t12 ON t11.id = t12.t15id;

查询结果将会是：

| t11.id | t11.name | t12.id | t12.age | t12.t15id |
|--------|----------|--------|---------|-----------|
| 1      | Alice    | 1      | 25      | 1         |
| 2      | Bob      | 2      | 30      | 2         |
| 3      | Carol    | NULL   | NULL    | NULL      |

在这个例子中，左表 t11 中的所有行都包含在结果中。第一行和第二行分别与右表 t12 中的第一行和第二行匹配，而第三行在右表中找不到匹配的行，因此在右表的列中填充了 NULL 值。