mysql多表关系与查询

一、多表关系

1.多表操作分类

1.1 多对一

关系举例：
多对一：多名学生在一个班级里
一对多主要是靠 “外键” 实现。在 “多” 的表中建立外键，指向 "一"的主键
一对多的关系在实际生产当中使用非常常见。
一对多的核心解决方案是：如何让记录能够正确的匹配到另外表中的数据

1.2 多对多

关系举例：

一个学生可以学习多门课程。一门课程也可以被多名学生学习。
多对多主要是第三张表来维护两张表之间的关系。第三张表主要记录两张表的主键来记录对应关系。
这两个字段分别使用外键来关联两张表的主键

1.3 一对一

一对一的关系，多用于单表拆分，将一张表的基础字段放在一张表中，详情信息放在另外一张表中，以提升操作效率。
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

1.4 区别

多对一：

​ 1.A表的一行数据只能和B表的某一行数据建立关系
​ 2.B表的一行数据可以和A表的多数据建立联系
多对多：
​ 1.A表的一行数据可以和B表的多行数据建立关系
​ 2.B表的一行数据也可以和A表的多行数据建立关系

2.多对一

在前边的 “约束” 中没有写外键约束，就是放在这里在举例。

2.1 外键概念

在一张表中指向外部表 “主键””的字段定义成为 “外键”.

2.2 外键语法

外键必须要通过语法指定才能称之为外键.
外键也可以不指定名称，系统会自动生成

[constraint 外键名] foreign key(当前表字段名) references 外部表(主键字段)

2.3 外键构成条件

外键字段必须与对应表的主键字段类型一致
外键字段本身要求是一个索引（创建外键会自动生成一个索引）

2.4 建立一（班级表）

create table class( 
    id int primary key auto_increment, 
    name varchar(50) not null unique 
)charset utf8;

2.5 建立多（学生表）

create table students( 
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int comment  "指向class表的主键",
    foreign key(c_id) references class(id)
);

2.6 新增班级数据

INSERT INTO class VALUES(NULL,"python"),
(NULL,"C"),
(NULL,"C++"),
(NULL,"java"),
(NULL,"go");

内容如下：

mysql> select * from class;
+----+--------+
| id | name   |
+----+--------+
|  2 | C      |
|  3 | C++    |
|  5 | go     |
|  4 | java   |
|  1 | python |
+----+--------+

2.7 新增学生表数据

INSERT INTO students VALUES(NULL,"张三",1),
(NULL,"李四",2),
(NULL,"王五",1),
(NULL,"王强",2),
(NULL,"孙红",4);

结果如下：

mysql> select * from students;
+----+--------+------+
| id | name   | c_id |
+----+--------+------+
|  1 | 张三   |    1 |
|  2 | 李四   |    2 |
|  3 | 王五   |    1 |
|  4 | 王强   |    2 |
|  5 | 孙红   |    4 |
+----+--------+------+

3.多对多

3.1 创建表1

创建武林高手表

CREATE TABLE persion(
	id INT AUTO_INCREMENT PRIMARY KEY,
	NAME VARCHAR(20)
)

插入数据

INSERT INTO persion VALUES(NULL,"张无忌"),
(NULL,"周芷若"),
(NULL,"谢逊"),
(NULL,"崆峒五老"),
(NULL,"黄衫女");

内容如下：

mysql> select * from persion;
+----+--------------+
| id | NAME         |
+----+--------------+
|  1 | 张无忌       |
|  2 | 周芷若       |
|  3 | 谢逊         |
|  4 | 崆峒五老     |
|  5 | 黄衫女       |
+----+--------------+

3.2 创建表2

创建武功秘籍表

CREATE TABLE manual(
	id INT AUTO_INCREMENT PRIMARY KEY,
	NAME VARCHAR(30)
) 

插入数据

INSERT INTO manual VALUES(NULL,"九阴真经"),
(NULL,"七伤拳"),
(NULL,"乾坤大挪移");

查询数据

mysql> select * from manual;
+----+-----------------+
| id | NAME            |
+----+-----------------+
|  1 | 九阴真经          |
|  2 | 七伤拳           |
|  3 | 乾坤大挪移        |
+----+-----------------+

3.2 创建关系表

两个字段分别关联到persion表和manual表的两个主键

CREATE TABLE PersionToManual(
	id INT AUTO_INCREMENT PRIMARY KEY,
	persion_id INT,
	manual_id INT,
	FOREIGN KEY(persion_id) REFERENCES persion(id),
	FOREIGN KEY(manual_id) REFERENCES manual(id)
)

插入数据

INSERT INTO PersionToManual VALUES(NULL,1,2),
(NULL,1,3),
(NULL,2,1),
(NULL,3,2),
(NULL,4,2),
(NULL,5,1)

查询结果如下：

mysql> SELECT * FROM PersionToManual;
+----+------------+-----------+
| id | persion_id | manual_id |
+----+------------+-----------+
|  1 |          1 |         2 |
|  2 |          1 |         3 |
|  3 |          2 |         1 |
|  4 |          3 |         2 |
|  5 |          4 |         2 |
|  6 |          5 |         1 |
+----+------------+-----------+

4.一对一

一对一关系，多用于表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
一对一的实现主要是靠 在任意一方加入外键，关联另外一方的主键，并且设置外键唯一（UNIQUE）

二、多表查询

1.内连接

内连接的连接条件必须是 外键id = 主表主键id
内连接查询的是两张表交集的部分。
这里的查询实例使用的是上边的class表和students表，这里在students表中插入了一条没有外键id的一行数据，为了能够明显的看出效果

INSERT INTO students VALUES(NULL,"高圆圆",NULL)

1.1 隐士内连接

语法：

SELECT 字段名 FROM 表1,表2 WHERE 表1.字段 = 表2.字段

。如果不加条件，直接查询连长表，就会出现笛卡尔乘积

SELECT * FROM students,class

使用隐士内连接进行查询。

SELECT * FROM students,class WHERE students.c_id = class.id

查询结果如下：结果中没有出现笛卡尔乘积

mysql> SELECT * FROM students,class WHERE students.c_id = class.id;
+----+--------+------+----+--------+
| id | name   | c_id | id | name   |
+----+--------+------+----+--------+
|  1 | 张三   |    1 |  1 | python |
|  2 | 李四   |    2 |  2 | C      |
|  3 | 王五   |    1 |  1 | python |
|  4 | 王强   |    2 |  2 | C      |
|  5 | 孙红   |    4 |  4 | java   |
+----+--------+------+----+--------+

1.2 显示内连接

语法: 其中inner可以省略

SELECT 字段名 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段

实例：这里为两张表起了别名。

SELECT  s.name, c.name FROM students s JOIN class c ON s.c_id = c.id

查询结果如下：

+--------+--------+
| name   | name   |
+--------+--------+
| 张三   | python |
| 李四   | C      |
| 王五   | python |
| 王强   | C      |
| 孙红   | java   |
+--------+--------+

2.外连接：

2.1 左外连接：

查询左表所有数据，以及两张表交集部分.这个交集部分是针对与笛卡尔积而言的
语法：

select * from 表1 left join 表2 on 条件;

实例：

SELECT * FROM students s LEFT JOIN class c ON s.c_id = c.id

结果如下：查询出了左表所有的数据以及交集部分

+----+-----------+------+------+--------+
| id | name      | c_id | id   | name   |
+----+-----------+------+------+--------+
|  1 | 张三      |    1 |    1 | python |
|  2 | 李四      |    2 |    2 | C      |
|  3 | 王五      |    1 |    1 | python |
|  4 | 王强      |    2 |    2 | C      |
|  5 | 孙红      |    4 |    4 | java   |
|  6 | 高圆圆    | NULL | NULL | NULL   |
+----+-----------+------+------+--------+

2.2 右外连接：

查询右表所有数据，以及两张表交集部分，这个交集部分是针对与笛卡尔积而言的

select * from 表1 rigth join 表2 on 条件;

实例：查询出了右表所有数据，以及两张表交集的数据

mysql> SELECT * FROM students s RIGHT JOIN class c ON s.c_id = c.id;
+------+--------+------+----+--------+
| id   | name   | c_id | id | name   |
+------+--------+------+----+--------+
|    4 | 王强   |    2 |  2 | C      |
|    2 | 李四   |    2 |  2 | C      |
| NULL | NULL   | NULL |  3 | C++    |
| NULL | NULL   | NULL |  5 | go     |
|    5 | 孙红   |    4 |  4 | java   |
|    3 | 王五   |    1 |  1 | python |
|    1 | 张三   |    1 |  1 | python |
+------+--------+------+----+--------+

3.子查询

3.1 分类

根据位置又可以分为：

在where之后出现、from后边出现、select之后出现的子查询

3.2 标量子查询

常用的操作符号：=,<> >,>= <,<=
这里还是使用class和students表
例子： 查询学习C语言的学生有谁

SELECT * FROM students WHERE c_id = (SELECT id FROM class WHERE NAME = "C")

3.2 列子查询

常用的操作符有

例子：

查询学习C或者C++的学生有谁

SELECT * FROM students WHERE c_id IN(SELECT id FROM class WHERE NAME = "c" OR NAME ="c++")

3.2 行子查询

这里没有合适数据，只写一下语法

select * from 表 where (字段1，字段2) = （子查询回来的两个字段结果）