连接表查询

1，前言

在此之前对于多表查询并未花精力去了解，近来才有机会重新学习，本文仅此记录。如有错误，请您指出，您的指点是我的荣幸！
为了了解这些，浏览了诸多博主的博客
参考自：这位博主

2，连接表

2.1，建表

student表：

student1表：

institute表：

2.2，内连接

等值连接，自然连接属于内连接

2.2.1，等值连接

-- 等值连接
-- student表和institute表
-- 等值连接
SELECT * FROM `student` s ,`institute` i WHERE s.`insName`=i.`insName`
-- 同样的效果
SELECT * FROM `student` s INNER JOIN `institute` i WHERE s.`insName`=i.`insName`

结果：

2.2.2，自然连接

-- 自然连接
-- 自然连接(Natural join)是一种特殊的等值连接，
-- 它要求两个关系中进行比较的分量必须是相同的属性组，
-- 并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。
SELECT * FROM `institute` NATURAL JOIN `student`

-- 若没有相同的属性组，则跟笛卡尔积一样
SELECT * FROM `institute` NATURAL JOIN `student1`


-- 推测
-- 笛卡尔积就是将两个关系R与S进行操作，所得的元组个数正是两个关系中的元组个数之积
-- 而等值连接就相当于在笛卡尔积的基础上，根据WHERE 列名1=列名2 这样的条件进行筛选，若where子条件没有一条符合，则为空
-- 而自然连接就是在等值连接的基础上，将等值连接的结果中，去掉重复的属性列（？何为重复？）

student 和 insitute 有相同的属性列，所以结果：

而 student1 和 institute 没有相同的属性列，则结果与两个表的笛卡尔积一样

注：曾对相同的属性列不是很了解，就做了个实验；个人推测相同的属性列指列名相同，但 是否也与列的属性相关并未求证（慎）

2.3，外连接

外连接有左外连接，右外连接，全连接（MySQL无全连接，故并不记录）

外连接不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行

2.3.1，左外连接/左连接

LEFT JOIN或LEFT OUTER JOIN
（以左表为主）

左外连接还返回左表中不符合连接条件单符合查询条件的数据行。

-- 左外连接/左连接
SELECT * FROM `institute` i LEFT JOIN `student` s ON i.`insName`=s.`insName` 

SELECT * FROM `institute` i LEFT OUTER JOIN `student` s ON i.`insName`=s.`insName`

结果：

显而易见，左表中那些不符合连接条件(i.insName=s.insName )但是符合查询条件(*)的记录亦被查询出来，而与这些记录对应的右表部分全部为null

2.3.2，右外连接/右连接

RIGHT JOIN 或 RIGHT OUTER JOIN
（以右表为主）
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。

-- 右外连接/右连接
SELECT * FROM `institute` i RIGHT JOIN `student` s ON i.`insName`=s.`insName`

SELECT * FROM `institute` i RIGHT OUTER JOIN `student` s ON i.`insName`=s.`insName`

结果：

2.4，双表连接查询

原理

-- sql查询的原理
-- 对两表求积（笛卡尔积）并用on条件和连接类型进行过滤形成中间表；然后根据where条件过滤中间表的记录，并根据select指定的列返回查询结果
SELECT * FROM `institute` i LEFT JOIN `student` s ON i.`insName`=s.`insName`

SELECT * FROM `institute` i LEFT JOIN `student` s ON i.`insName`=s.`insName` WHERE s.`insName`='历史学院'

2.5，7种join理论

（图片源于百度）

2.6，本文所用表的构建语句

若不需要可自行略过

-- 建立一个institute表，用来存储学院信息
CREATE TABLE `testjoin`.`institute`(
	`insId` INT(3) NOT NULL COMMENT '学院id', 
	`insName` VARCHAR(10) COMMENT '学院名称', 
	PRIMARY KEY (`insId`) 
 ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 



-- 建立一个student表，在学院名称一列的列名与institute是一致的
CREATE TABLE `testjoin`.`student`( 
	`stuId` INT(3) NOT NULL COMMENT '学生id', 
	`stuName` VARCHAR(10) COMMENT '学生姓名', 
	`insName` VARCHAR(10) COMMENT '所属学院', 
	PRIMARY KEY (`stuId`) 
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 


-- 建立一个student1表，因为他与institute并没有公共列
CREATE TABLE `testjoin`.`student1`( 
	`stuId` INT(3) NOT NULL COMMENT '学生id', 
	`stuName` VARCHAR(10) COMMENT '学生姓名', 
	`stuIns` VARCHAR(10) COMMENT '所属学院', 
	PRIMARY KEY (`stuId`) 
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 


-- 给student1表插入数据
INSERT INTO `testjoin`.`student` (`stuId`, `stuName`, `insName`) VALUES ('1', '张三', '计信学院');
INSERT INTO `testjoin`.`student` (`stuId`, `stuName`, `insName`) VALUES ('2', '李四', '文学院');  
INSERT INTO `testjoin`.`student` (`stuId`, `stuName`, `insName`) VALUES ('3', '王五', '历史学院'); 
INSERT INTO `testjoin`.`student` (`stuId`, `stuName`, `insName`) VALUES ('4', '赵六', '计信学院'); 
INSERT INTO `testjoin`.`student` (`stuId`, `stuName`, `insName`) VALUES ('5', '钱七', '物电学院'); 
INSERT INTO `testjoin`.`student` (`stuId`, `stuName`, `insName`) VALUES ('6', '孙八', '历史学院'); 


-- 给student1表插入数据
INSERT INTO `testjoin`.`student1` (`stuId`, `stuName`, `stuIns`) VALUES ('1', '张三', '计信学院');
INSERT INTO `testjoin`.`student1` (`stuId`, `stuName`, `stuIns`) VALUES ('2', '李四', '文学院');  
INSERT INTO `testjoin`.`student1` (`stuId`, `stuName`, `stuIns`) VALUES ('3', '王五', '历史学院'); 
INSERT INTO `testjoin`.`student1` (`stuId`, `stuName`, `stuIns`) VALUES ('4', '赵六', '计信学院'); 
INSERT INTO `testjoin`.`student1` (`stuId`, `stuName`, `stuIns`) VALUES ('5', '钱七', '物电学院'); 
INSERT INTO `testjoin`.`student1` (`stuId`, `stuName`, `stuIns`) VALUES ('6', '孙八', '历史学院'); 



-- 给institutie插入数据
INSERT INTO `testjoin`.`institute` (`insId`, `insName`) VALUES ('1', '计信学院'); 
INSERT INTO `testjoin`.`institute` (`insId`, `insName`) VALUES ('2', '历史学院'); 
INSERT INTO `testjoin`.`institute` (`insId`, `insName`) VALUES ('3', '物电学院'); 
INSERT INTO `testjoin`.`institute` (`insId`, `insName`) VALUES ('4', '文学院'); 
INSERT INTO `testjoin`.`institute` (`insId`, `insName`) VALUES ('5', '生物学院'); 
INSERT INTO `testjoin`.`institute` (`insId`, `insName`) VALUES ('6', '音乐学院');
INSERT INTO `testjoin`.`institute` (`insId`, `insName`) VALUES ('7', '政治学院'); 
INSERT INTO `testjoin`.`institute` (`insId`, `insName`) VALUES ('8', '法学院');