MySQL 连接查询 & 子查询

数据库优化 学习笔记

一、连接查询

1.1、inner join … on

• 内连接查询： 查询的结果为两个表匹配到的数据
• 语法： select ... from 表A inner join 表B on AB表连接的条件;，其中 inner 可以省略

	-- 不加连接条件, 则 A 表的每一条记录都对应 B 表的每一条记录输出
	select * from students inner join classes;

	-- 查询 有能够对应班级的学生以及班级信息, 显示学生及班级信息
	select * from students inner join classes on students.cls_id = classes.id;

	-- 按照要求显示学生信息、班级名称
	select students.*,classes.name from students inner join classes on students.cls_id = classes.id;

	-- 给数据表起名字
	select s.*,c.name from students as s inner join classes as c on s.cls_id = c.id;
	
	-- 在以上的查询中，将班级姓名显示在第1列
	select c.name, s.* from students as s inner join classes as c on s.cls_id = c.id;

	-- 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序
	select c.name, s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name;
	
	-- 当同一个班级的时候，按照学生的id进行从小到大排序
	select c.name, s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name,s.id asc;

1.2、left join … on

• 左连接查询： 查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用 null 填充
• 语法： select ... from 表A left join 表B on ... ;

	-- left join
	-- 查询每位学生对应的班级信息
	select * from students left join classes on students.cls_id = classes.id;
	select * from classes left join students on students.cls_id = classes.id;

• 条件不能用 where ，要用 having

	-- 查询没有对应班级信息的学生
	-- select ... from xxx as s left join xxx as c on..... having .....
	select * from students as s left join classes as c on s.cls_id = c.id having c.id is null;

1.3、right join … on（不常用）

• 右连接查询： 查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用 null 填充
• 语法： select ... from 表A right join 表B on ... ;
• 用法与左连接相似

	-- 查询每位学生对应的班级信息
	select * from students right join classes on students.cls_id = classes.id;
	select * from classes right join students on students.cls_id = classes.id;

---

二、子查询

• 查询的条件不能从表中直接得到，需要一次甚至多次，查询同一张表甚至不同的表得到
• 在一个 select 语句中嵌入了另外一个 select 语句，那么被嵌入的 select 语句称之为子查询语句
  
  

2.1、查询最高的男生信息

	-- 问题拆分
	select * from students where higt = 170;
	select max(higt) from students where gender='男';

	-- 初始版
	select * from students where higt = (select max(higt) from students where gender='男');

• 有问题，子查询的结果的确是最高男生的身高，但是，整条语句查询出来的信息不一定是这个男生的，因为可能存在有女生的身高和这个男生一样
• 所以，需要再主查询的条件处，添加性别限制 gender='男'

	-- 最终版
	select * from students where higt = (select max(higt) from students where gender='男') and gender='男';

2.2、查询出高于平均身高的信息

	-- 问题拆分
	select * from students where higt > 平均值;
	select avg(higt) from students;
	
	-- 结合
	select * from students where higt > (select avg(higt) from students);

2.3、列级子查询

	-- 问题拆分
	select * from students where cls_id in (有哪些班级);
	select id from classes;
	
	-- 查询学生的班级号能够对应的学生信息
	select * from students where cls_id in (select id from classes);