MYSQL多表关联查询与子查询

多表关联查询

关联查询又名连接查询，其主要包括了内连接，外连接，自连接，交叉连接等四个大类。
首先我们要清楚我们为什么要使用多表关联查询，肯定是因为我们想要显示的数据来自于两个或多个数据表内部，我们想要将其用某种联系(依赖)拼接起来，例如，一张学生人员表和一张科目成绩表，如果我们想要查看某个学生的各科成绩。此时就需要从学生表中取出学生的个人信息并且将其映射到科目成绩表内部查询出对应的成绩。这样一个过程就是要用到关联查询，重点有二，关系和内容。
首先，关系的建立，关系就是两张表均有的某个字段，用于确定依赖关系。常常被设置为主键和外键这样的联系。但是没有这样的主外键关系只要数据满足依赖关系则同样可以进行关联查询
其次，是对于内容的界定，主要用来区分我们的内连接，两种外连接的查询。其关系图如下：


此处我们进行一波环境搭建：

#1.1创建student和score表
CREATE  TABLE student (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY ,
s_name  VARCHAR(20)  NOT NULL ,
sex  VARCHAR(4) ,
birth  YEAR,
department  VARCHAR(20) ,
address  VARCHAR(50)
);
#1.2创建score表SQL代码如下：
CREATE  TABLE score (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
stu_id  INT(10)  NOT NULL ,
c_name  VARCHAR(20) ,
grade  INT(10)
);
#2.为student表和score表增加记录
#向student表插入记录的INSERT语句如下：
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
#向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

内连接

内连接就是表间的主键与外键相连，只取得键值一致的，可以获取双方表中的数据连接方式

inner join：代表选择的是两个表的交差部分。
语法如下：
	 SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

示例：进行内连接查询

从这里可以看见，就是两张表因为有关系所以可以拼接到一起查询。

外连接

1.左外连接

left join：代表选择的是前面一个表的全部。
左连接是以左表为标准，只查询在左边表中存在的数据，当然需要两个表中的键值一致。语法如下：
SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

示例：这里我们默认student为左表(写在左面的选手)

#左表插入新人物
INSERT INTO student VALUES( 907,'子龙', '男',1988,'计算机系', '湖南省衡阳市');
SELECT * FROM student;

#左连接查询
SELECT * FROM student LEFT JOIN score ON student.`id`=score.`stu_id`;  

2.右外连接

right join：代表选择的是后面一个表的全部
同理，右连接将会以右边作为基准，进行检索。语法如下：
SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

示例：

#右表插入新成绩
INSERT INTO score VALUES(NULL,888, '电竞',60);
SELECT * FROM score;

#右连接查询
SELECT * FROM student RIGHT JOIN score ON student.`id`=score.`stu_id`;  

由此，可以清晰的看出这几种连接之间的区别。

自连接

自连接是一种特殊的表连接，它是指相互连接的表在物理上同为一张表，但是逻辑上是多张表。自 连接通常用于表中的数据有层次结构，如区域表、菜单表、商品分类表等。
自连接顾名思义就是自己跟自己连接，参与连接的表都是同一张表。(通过给表取别名虚拟出)

示例：在同一张表内部与表内数据进行比较
需要自行对表内数据进行图形化界面的更新：

SELECT * FROM student AS s1,student AS s2 WHERE s1.`s_name`=s2.`big_borther`;

这里是将表内的人名和老大名字进行条件连接，新查询出了一张左侧为S1右侧为S2的逻辑表，在此表中，S1中的人是S2中人领导。利用这样一条逻辑记录，我们可以轻易的实现对个人和领导的年龄对比的筛选：

#筛选出比自己领导小的人
SELECT s2.`s_name` FROM student AS s1,student AS s2 WHERE s1.`s_name`=s2.`big_borther`
AND s1.`birth` = s2.`birth`;

交叉连接

笛卡尔乘积，一种数学运算。

#示例交叉
SELECT * FROM student CROSS JOIN score;

子查询

子查询是将一个查询语句嵌套在另一个查询语句中。内部嵌套其他select语句的查询，称为1.外查询或主查询
2.内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3.子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4.还可以包含比较运算符：= 、 !=、> 、<等

注意：
1、子查询要包含在括号内。
2、将子查询放在比较条件的右侧。
3、单行操作符对应单行子查询，多行操作符对应多行子查询

环境搭建

CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
e_name VARCHAR(10) NOT NULL,
e_sai DOUBLE NOT NULL,
department INT(10)
);

INSERT INTO emp VALUES (NULL,'张三',2800,8);
INSERT INTO emp VALUES (NULL,'李四',7800,9);
INSERT INTO emp VALUES (NULL,'王五',10056,10);
INSERT INTO emp VALUES (NULL,'赵六',10000,10);
INSERT INTO emp VALUES (NULL,'文八',12000,9);
INSERT INTO emp VALUES (NULL,'张四',35600,9);
INSERT INTO emp VALUES (NULL,'张五',56000,10);
INSERT INTO emp VALUES (NULL,'张起灵',106000,9);

查询示例

# 查询姓名和工资，要求工资=最低工资
SELECT e_name,e_sai FROM emp 
WHERE e_sai =(SELECT MIN(e_sai ) FROM emp); 

#查询工资高于10号部门的平均工资的员工信息
SELECT * FROM emp 
WHERE e_sai > (SELECT AVG(e_sai)FROM emp WHERE department=10);

#查询出比10号部门任何员工薪资高的员工信息
-- 法一
SELECT * FROM emp WHERE e_sai > (SELECT MAX(e_sai) FROM emp WHERE department=10)
AND department != 10;
-- 法二
SELECT * FROM emp WHERE e_sai > ALL (SELECT e_sai FROM emp WHERE department=10)
AND department != 10;

#查询出比10号部门任意一个员工薪资高的所有员工信息:只要比其中随便一个工资都可以
SELECT * FROM emp WHERE e_sai > ANY (SELECT e_sai FROM emp WHERE department=10)
AND department != 10;