联表查询JOIN ON ★★★

1、MySQL七种 join 理论

在这里插入图片描述

2、七种 join 理论练习

联表查询练习专用表SQL代码

-- 建表
CREATE TABLE tbl_dep(
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(22) DEFAULT NULL,
addr VARCHAR(22) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET=utf8;

CREATE TABLE tbl_emp(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(22) DEFAULT NULL,
`deptId` VARCHAR(22) DEFAULT NULL,
PRIMARY KEY (`id`)
#constraint `fk_deptId` foreign key (`deptId`) references tbl_dep(id);
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入数据
INSERT INTO tbl_dep(deptName,addr) VALUES('hr','111');
INSERT INTO tbl_dep(deptName,addr) VALUES('bd','112');
INSERT INTO tbl_dep(deptName,addr) VALUES('vb','113');
INSERT INTO tbl_dep(deptName,addr) VALUES('sd','114');
INSERT INTO tbl_dep(deptName,addr) VALUES('yy','115');

INSERT INTO tbl_emp(name,deptId) VALUES('k8',1); 
INSERT INTO tbl_emp(name,deptId) VALUES('k6',2); 
INSERT INTO tbl_emp(name,deptId) VALUES('k4',3); 
INSERT INTO tbl_emp(name,deptId) VALUES('k4',11);

tbl_dep 表
btl_emp 表

内连接练习(两表的共有部分)

内连接语法为

SELECT 字段名 FROM 表名 [AS] 表别名 INNER JOIN 表名 [AS] 表别名 ON条件
```
-- 内连接练习（两表共有部分）
SELECT * FROM tbl_dep d INNER JOIN tbl_emp e ON d.id=e.deptId;
```
左连接练习（左表的全部，右表不满足部分补NULL）

左连接语法为

SELECT 字段名 FROM 表名 [AS] 表别名 LEFT JOIN 表名 [AS] 表别名 ON条件
```
-- 左连接练习（左表的全部，右表不满足补NULL）
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId;
```
右连接（右表的全部，左表不满足部分补NULL）

右连接语法为

SELECT 字段名 FROM 表名 [AS] 表别名 RIGHT JOIN 表名 [AS] 表别名 ON条件
```
-- 右连接（右表的全部，左表不满足的补NULL）
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId;
```
特殊的左连接（显示为左表的独有的数据）
- 说明：查询 tbl_dep 表中跟 tbl_emp 表无关联关系的数据，即 tbl_dep 独占，且 tbl_emp 表的显示列补NULL；
- 特殊左连接语法为
  
  SELECT 字段名 FROM 表名 [AS] 表别名 LEFT JOIN 表名 [AS] 表别名 ON条件 WHERE条件
```
-- 特殊的左连接，（显示为左表的独有的数据）
-- 说明：查询 tbl_dep 表中跟 tbl_emp 表无关联关系的数据，即 tbl_dep 独占，且 tbl_emp 表的显示列补NULL；
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId WHERE e.deptId IS NULL;
```
特殊的右连接（显示为右表的独有的数据）
- 说明：查询 tbl_emp 表中跟 tbl_dep 表无关联关系的数据，即 tbl_emp 独占，且 tbl_dep 表的显示列补NULL；
- 特殊右连接语法为
  
  SELECT 字段名 FROM 表名 [AS] 表别名 RIGHT JOIN 表名 [AS] 表别名 ON条件 WHERE条件
```
-- 特殊的右连接（显示为右表的独有的数据 ）
-- 说明：查询 tbl_emp 表中跟 tbl_dep 表无关联关系的数据，即 tbl_emp 独占，且 tbl_dep 表的显示列补NULL；
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId WHERE d.id IS NULL;
```

全连接（显示全部数据）（mysql 不支持 full outer join）

全连接其实就是左右连接的合并形式

可以通过union去重

全连接依旧会像左右连接一样自动补null

-- 全连接（显示全部数据）（mysql 不支持 full outer join）
-- UNION ：有去重的功能。
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId UNION
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId;

mysql 不支持 full outer join

显示两表的独有的数据

显示两表独有数据其实就是特殊左右连接的合并

可以通过union去重

自动补null

-- 显示两表的独有的数据
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId WHERE e.deptId IS NULL UNION
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId WHERE d.id IS NULL;

3、自连接（了解即可）

所谓的自连接就是和自己的表连接

核心：一张表拆分为两张一样的表

自连接其实就是将一张表视为两张表，然后再建立父子关系

其精髓就在这里：
FROM `category` AS a,`category` AS b

自连接专用数据库表sql

CREATE TABLE `school1`.`category`(
`categoryid` INT(3) NOT NULL COMMENT 'id',
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1', 
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字', 
PRIMARY KEY (`categoryid`) 
)ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

INSERT INTO `school1`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
insert into `school1`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) values ('3', '1', '软件开发');
insert into `school1`.`category` (`categoryid`, `PId`, `categoryname`) values ('5', '1', '美术设计');
insert iNTO `School1`.`category` (`categoryid`, `pid`, `categorynamE`) VAlUES ('4', '3', '数据库'); 
insert into `school1`.`category` (`CATEgoryid`, `pid`, `categoryname`) values ('8', '2', '办公信息');
insert into `school1`.`category` (`categoryid`, `pid`, `CAtegoryname`) values ('6', '3', 'web开发'); 
inserT INTO `SCHool1`.`category` (`categoryid`, `pid`, `categoryname`) valueS ('7', '5', 'ps技术');

父类表

categoryid categoryname

2 信息技术

3 软件开发

5 美术设计
子类表

pid categoryid categoryname

3 4 数据库

3 6 web开发

5 7 ps技术

2 8 办公信息
查询父类对应的子类关系

父类子类

信息技术办公信息

软件开发数据库

软件开发 web开发

美术设计 ps技术

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

pid	categoryid	categoryname
3	4	数据库
3	6	web开发
5	7	ps技术
2	8	办公信息

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

上边这三个表先看懂，然后我们用 sql 语句去实现查询父子信息

sql 语句实现查询父子信息

-- 查询父子关系
SELECT a.`categoryname` AS '父类',b.`categoryname` AS '子类'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`;

4、联表查询练习

1.查询学员所属年级（学号，姓名，年级名称）

-- 1.查询学员所属年级（学号，姓名，年级名称）
SELECT `studentno`,`studentname`,`gradename`
FROM `student` s
INNER JOIN `grade` g
ON s.`gradeid`=g.`gradeid`;

2.查询科目所属年级（科目编号，科目名称，所属年级名称）

-- 2.查询科目所属年级（科目编号，科目名称，所属年级名称）
SELECT `subjectno`,`subjectname`,`gradename`
FROM `subject` s
INNER JOIN `grade` g
ON s.`gradeid`=g.`gradeid`;

3.查询参加 '数据库结构-1' 考试同学的信息：学号，学生姓名，科目名，分数

-- 3.查询参加 '数据库结构-1' 考试同学的信息：学号，学生姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname='数据库结构-1';