联表查询，子查询

七种联表查询图解

核心三种联表查询

两张表联表查询

--Inner JOIN内连接
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` 
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`StudentNo`=r.`StudentNo`;
--right JOIn右连接
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` 
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`StudentNo`=r.`StudentNo`;
--left JOIN左连接
SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult` 
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`StudentNo`=r.`StudentNo`;

多张表联表查询

--三表查询
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON r.`StudentNo`=s.`StudentNo`
INNER JOIN `subject` AS su
ON su.`SubjectNo`=r.`SubjectNo`

思路
要查询的数据
从哪几个表中查 from表 XXX join 表 on交叉条件
假设存在一种多张表查询，慢慢来，先查询两张表再慢慢增加

FROM a LEFT JOIN b 以左边的表为基准
FROM a RIGHT JOIN b 右边的表为基准

自连接

表数据

CREATE TABLE `categore`(
    `categoreid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT'主题id',
    `pid` INT(10) NOT NULL COMMENT'父id',
    `categoreName` VARCHAR(50) NOT NULL COMMENT'主题名字',
    PRIMARY KEY(`categoreid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `categore`(`categoreid`,`pid`,`categoreName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

自己的表和自己的表链接 核心：拆为两张表才为两张一样的表

父类**

子类

操作：查询父类对应的子类信息

--查询父子信息 把一张表看作两个一样的表
SELECT a.`categoreName` AS '父类',b.`categoreName` AS '子类'
FROM `categore` AS a,`categore` AS b
WHERE a.`categoreid`=b.`pid`;