第八章 SQL查询(四)

一，多表联查

#如何同时从这两个表中取得数据？
#学生的姓名和成绩
#多表联查
# SELECT a.字段1，a.字段2,b.字段1，b.字段2 FROM 表1 a,表2 b where a.主键=b.外键
SELECT s.studentname,r.studentResult FROM student s,result r where s.studentNo=r.studentNo;
# 查询所有科目的成绩
SELECT s.SubjectName, r.studentResult FROM result r,`subject` s WHERE r.subjectNo=s.SubjectId;

二，内连接

内连接使用比较运算符根据每个表的通用列中的值匹配两个表中的行

# 查询所有科目的成绩
SELECT s.SubjectName, r.studentResult FROM result r,`subject` s WHERE r.subjectNo=s.SubjectId;
-- 查询所有科目的成绩
SELECT s.SubjectName, r.studentResult  FROM `subject` s INNER JOIN result r on r.subjectNo=s.SubjectId;

三表内连接

-- 所有的学生科目成绩
SELECT s.studentname,sb.SubjectName,r.studentResult FROM student s INNER JOIN result r on s.studentNo=r.studentNo INNER JOIN `subject` sb on r.subjectNo=sb.SubjectId; 

where的使用

where用在对内连接查询的数据进行筛选

-- 所有的学生java成绩
SELECT s.studentname,sb.SubjectName,r.studentResult FROM student s INNER JOIN result r on s.studentNo=r.studentNo INNER JOIN `subject` sb on r.subjectNo=sb.SubjectId
WHERE sb.SubjectName='java'; 

三，外连接

左外连接

主表（左表）Students中的数据逐条匹配表Result中的数据
1、匹配，返回到结果集
2、无匹配，NULL值返回到结果集

语法

select * from 表1（左表） left join 表2（右表） on 表1.id=表2.外键

查询学生 学号，姓名，及考试成绩

#查询学生 学号，姓名，及考试成绩
-- 多表联查
SELECT s.studentNo,s.studentname,r.studentResult FROM student s,result r WHERE s.studentNo=r.studentNo;
-- 内连接
SELECT s.studentNo,s.studentname,r.studentResult FROM student s INNER JOIN result r on s.studentNo=r.studentNo;
-- 左外链接
SELECT s.studentNo,s.studentname,r.studentResult FROM student s LEFT JOIN result r on s.studentNo=r.studentNo;

左连接：

会显示左表的所有数据，右表只显示符合条件的数据，不符合条件的数据会显示为null

右连接

右外连接的原理与左外连接相同
右表逐条去匹配记录；否则NULL填充

#右连接
#查询学生 学号，姓名，及考试成绩
SELECT r.studentResult,s.studentNo,s.studentname FROM  result r  RIGHT JOIN student s on s.studentNo=r.studentNo;

右连接：和左连接原理一样

用右表匹配左表的数据，右表显示所有，左表只显示符合条件的，不符合条件的显示为null

自连接

自己连接自己

如何查询比’张三’年龄大的学生信息?
1.我们需要先找到张三的出生日期
2.然后根据张三的出生日期进行查找
我们可以使用子查询

-- 如何查询比'张三'年龄大的学生信息?
# 1.查找张三的出生日期
SELECT borndate FROM student where studentname='张三';
# 2.根据张三的出生日期查找其他学生信息
SELECT*FROM student where borndate<(SELECT borndate FROM student where studentname='张三');

使用自连接

-- 自连接 同一个表，给不同的别名
SELECT s2.studentNo,s2.studentname FROM	student s1,student s2 where s1.studentname='张三' and s2.borndate<s1.borndate;

自然连接

# 自然连接  相当于省略了条件的内连接，自动判断条件
SELECT s.studentname,r.studentResult FROM student s NATURAL JOIN result r;
#查询没有参加考试的学生信息
# 自然外连接  和普通的外连接用法一致，以左表为主表进行匹配
SELECT s.studentname,r.studentResult FROM student s NATURAL LEFT JOIN result r;
#  where  is null 可以判断null值
SELECT s.studentname,r.studentResult FROM student s NATURAL LEFT JOIN result r WHERE r.studentResult IS null;