多表查询

多表查询

多表查询有两种方式，联表查询和子查询

联表查询，即将两张表连在一起，在一张"大表"中查询

子查询，即两一个表查询的结果作为另一个表的查询条件

---

补充关键词

# concat_ws()  多个字段拼接
concat_ws(':', name, age, sex)  #  如，xliu:18:male


# exists 存在判断查询结果是否为空，只返回布尔值， True|False
返回True的时候外层查询语句执行，返回False的时候外层查询语句不再执行

select * from emp where exists
	(select id from emp where id>300);

---

联表查询

直接联表，不推荐

select * from dep, emp;  # 结果   笛卡尔积

select * from emp,dep where emp.dep_id = dep.id; # 改进，提供联表条件；但这样语意其实不算清晰

下面是正确的联表方式

# 联表方式	
inner join		# 内连接
left join		# 左连接
right join		# 右连接
union 			# 全连接


#1 inner join  内连接，需要使用一个判断联表的条件，只拼接两张表中公有的数据部分
select * from emp inner join dep on emp.dep_id = dep.id;


#2 left join   左连接，左表所有的数据都展示出来 没有对应的项就用NULL
select * from emp left join dep on emp.dep_id = dep.id;


#3 right join  右连接，右表所有的数据都展示出来 没有对应的项就用NULL
select * from emp right join dep on emp.dep_id = dep.id;
 

#4 union 全连接  左右两表所有的数据都展示出来，没有项的为NULL
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

---

子查询

一个表的查询结果作为另一个表的查询条件

select * from course where teacher_id in (select tid from teacher where tname='李平老师');

---

查询练习

# 1、查询所有的课程的名称以及对应的任课老师姓名
-- 思路：
<1> 课程名和老师名分别在两种表中且两种表又是直接关联的，联表获取

SELECT
	course.cname,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON course.cid = teacher.tid
	
	
# 4、查询平均成绩大于八十分的同学的姓名和平均成绩
--思路：
<1> 先在分数表中，按学生分组统计平积分大于80的学生，获取学生id，和平均分
<2> 学生名在另一个表中，联表：学生表和<1>的虚拟表
SELECT
	student.sname,
	t1.avg_num 
FROM
	student
	INNER JOIN ( SELECT student_id, avg(num) AS avg_num FROM score GROUP BY student_id HAVING avg(num) > 80 ) AS t1 ON student.sid = t1.student_id;


# 7、查询没有报李平老师课的学生姓名
--思路：
<1> 根据老师表和课程表获取李平老师教授课程的课程id,（子查询和联表都可以）
<2> 在分数表中，获取报了李老师课程id的学生，需要去重
<3> 然后学生表中选择不在<2>中id范围内的学生，即没报考的学生
SELECT
	student.sname 
FROM
	student 
WHERE
	student.sid NOT IN (
	SELECT DISTINCT
		score.student_id 
	FROM
		score 
	WHERE
	score.course_id IN ( SELECT cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) 
	)

# 8、查询没有同时选修物理课程和体育课程的学生姓名(选择物理或者选择体育的学生姓名)
--思路：
<1> 先获取物理课和体育课的课程id
<2>拿着课程id在分数表中过滤出报考了这些课程的学生，再分组，筛选出课程数只有一个的学生，获取学生id
<3> 最后通过联表或子查询的方式，获取学生姓名
SELECT
	sname 
FROM
	student
	INNER JOIN (
	SELECT
		score.student_id AS sid 
	FROM
		score 
	WHERE
		score.course_id IN ( SELECT cid FROM course WHERE cname IN ( '物理', '体育' ) ) 
	GROUP BY
		score.student_id 
	HAVING
	count( score.course_id ) = 1 
	) AS t1 ON student.sid = t1.si

# 9、查询挂科超过两门(包括两门)的学生姓名和班级
--思路:
<1> 先再分数表中筛选出分数小于60分的学生，按学生分组，并统计不即可课程大于等于两门的学生，获取学生id
<2> 再联表：班级表和学生表
SELECT
	student.sname,
	class.caption 
FROM
	student
	INNER JOIN class ON student.class_id = class.cid 
WHERE
	student.sid IN (
	SELECT
		score.student_id 
	FROM
		score 
	WHERE
	score.num < 60 GROUP BY score.student_id HAVING count( score.course_id ) >= 2 
	)