联表查询
JOIN
七种Jion:
连接查询
如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
查询两个表中的结果集中的交集
外连接 outer join
左外连接 left join(以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
右外连接 right join(以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
等值连接和非等值连接
操作 | 描述 |
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左边中返回所有的值,即使右表中没有匹配 |
right join | 会从右边中返回所有的值,即使左表中没有匹配 |
例:
result:
student:
-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;
/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
-- 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student AS s , result AS r
WHERE r.studentno = s.studentno
-- 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL
-- 内连接:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student AS s
INNER JOIN result AS r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
小结:
1.我要查询哪些数据 select....
2.从那几个表中查 FROM (表 XXX) join (连接表) ON (交叉条件)
3.假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加
4.FROM a left join b 这个就是把a表全部包括,再去找b中的东西,没有就是null(左连接)
5.FROM a right join b 这个就是把b表全部包括,再去找a中的东西,没有就是null(右连接)
自联接
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
例:
CREATE TABLE `category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `category` (`categoryid`, `pid`, `categoryname`)
VALUES ('2','1','信息技术'),
('3','1','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');
父类
先看pid,去找相同的pid(pid = 1),然后去列出对应的categoryid
categoryid | categoryName |
2 | 信息安全 |
3 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryid | categoryName |
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
操作:查询父类对应的子类关系
父类 | 子类 |
软件开发 | 数据库 |
信息安全 | 办公系统 |
软件开发 | web开发 |
美术设计 | ps技术 |
-- 查询父子信息:把一张表看成为两个一摸一样的表
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`
运行结果: