SQL之“多表查询”

前几篇中我们查询的数据都是在一张表中进行操作的，但是实际工作中我们期望的数据往往分散在不同的表中，这时就需要下面我将写到的内容：从多张表中去获取数据。

一、表的加法

表的加法用英文单词表示：union，是将两张表的数据按行合并在一起。表的加法会把两个表中重复的数据删除只保留一个。如果想要保留两个表中重复的行，用union all 查询语句。

练习：

二、表的联接

关系数据库里各个表之间如何建立起关系呢？
下图是4张表联接关系图，我们可以从中看出这4张表中的联接关系

表和表之间是通过列直接产生对应关系的，联接是通过表和表之间的关系将两个表合并在一起的操作，联接的类型有：

• 交叉联接（cross join）

如图，cross join是将一个表中的每一行都与另一张表中的每一行合并在一起。交叉联接在实际业务中用得较少，因为结果数行太多了，需要花费大量设备的支持且没有什么实际价值。但它是其他联接的基础。

练习:

• 内联接（inner join）

查找出同时存在于两张表中的数据。下图是内联接的联接过程：


我们看下内联接的SQL查询语句，有三个关键地方

1. from子句中由原来的只有一张表变为两张表，且用as关键字对两张表起了别名方便我们使用。select子句中所选列都加了表的别名，表名是从特定表取出的列。
2. from 子句中用inner join 将两张表联接起来。表明联接方式是内联接，选取出同时存在于两张表中的数据。
3. from 子句中的关键字on 后表示两个表是通过哪个列匹配产生关系。
   练习：
   
   

• 左联接（left join）

左联接会把左侧表中的数据全部取出来。我们看下左联接在这两个表上是如何和运行的：
左联接会将左侧的表作为主表并全部取出，右边的表中只选出和左边表相同学号的行并进行合并。如果左侧表某行在右侧没有对应学号，相应列则为空值。
左联接的SQL查询语句只是将上述内联接语句中的inner join变为left join，其他不变。


再看一个问题，图片中红色区域如何表示呢？


它是在左联接的基础上去掉了两个表共同的地方。在原来的SQL语句中加入
“where b.学号 is null”表示右边表的学号为空值，这样就选出来左边表去掉公共部分的数据。
练习：

通过上图区别，我想提一下在这里要注意一个问题：
NULL表示不可知不确定，NULL不与任何值相等（包括其本身）
IS NULL 判断某个字符是否为空，并不代表空字符或者是0；
=NULL 是判断某个值是否等于NULL。
总之，要判断一个数是否等于NULL只能用 IS NULL 或者 IS NOT NULL 来判断

• 右联接（right join）

右联接会将右侧表中的数据全部取出来。


练习：


同样在右联接的基础上再看一个问题，红色区域部分怎么表示呢，和左联接部分同理。

• 全联接（full join）

全联接的查询结果会返回左右表中的所有行，当左右表的行有匹配时两个表会进行合并，若某一行与另一个表没有匹配时，另一个表对应的值用空值来填充。这样两个表中的数据就都在联接结果中了。
MySQL是不支持全联接的，这里理解下全联接概念即可。
总结：什么时候用哪一种联接呢？
当实际工作业务中，要生成固定行数的表单或特别说明了哪一张表里的全部数据时会使用左联接或右联接，其他情况都用内联接来获取两个表的公共部分。
两个表进行联接时在from 子句中加入联接语句，并不会影响SQL查询语句的运行顺序：

三、 联接应用案例
案例一：
查询所有学生的学号、姓名、选课数、总成绩

案例二：查询平均成绩大于85的所有学生的学号、姓名和平均成绩

案例三：查询学生的选课情况：学号、姓名、课程号、课程名称

四、Case 表达式

使用case表达式可以帮助我们解决复杂的查询问题，它的作用相当于进行一个条件判断的函数，用来判断每一行是不是满足某个条件。
下图是case表达式的SQL语句，里面的when子句用来判断某行数据是否符合某个条件，如果符合条件就运行后面的then子句，case表达式也就此结束；如果不符合条件就进行下一个when子句。

如下图，要判断成绩及格或不及格就可以在select子句中用case表达式（注意：中间无逗号），并且给这列起了一个别名“是否几个”，会将查询结果放入里面。
对照下图可以看下此语句是怎样运行的：


接着再看下两个案例：
案例一：查询出每门课程的及格人数和不及格人数：

注意事项：

• else子句可以省略不写，这时会默认为else 为空值，但不建议省略。
• end不能省略不写。
• case表达式可以写到SQL语句的任意子句中。

案列二：
使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称。（这种涉及到自定义的分组，妥妥的用case表达式）

这里要用课程号与课程名称两个列来分组，理由是：group by本来只用课程号就可以了，但是查询结果要我们显示出课程名称，我们学习group by 子句时select 里的列名只能是group by子句里的列名。为了让查询结果同时显示出课程名称，故group by 子句里加入了课程名称。这里加入的前提是不影响分组结果，如果影响分组的话就不能加了。

五．sqlzoo题目练习

案例

--用户
create table stuInfo (
   stuNo int primary key identity(1,1),
   stuName varchar(50) not null,
   stuAge int not null,
   stuSex char(2) not null

)
drop table Exam
drop table stuInfo
create table Exam(
  ExamNo int identity(1,1) primary key,
  stuNo int foreign key references stuInfo(stuNo),
  score float 
)
--创建图书分类表
drop table BookType
create table BookType
(
    TypeNo nvarchar(5) primary key ,--类型编号
    Name nvarchar(20) not null--类型名称
)
--创建图书表
drop table Books
create table Books
(
    Id int primary key identity(1,1),--主键
    BookName nvarchar(50) not null,--图书名
    Author nvarchar(30) not null,--作者
    Price decimal(10,1) check(Price>0) not null,--价格
    TypeNo nvarchar(5) foreign key
    references BookType(TypeNo)--引用分类外键
)

insert into BookType values('A001','生物书'),('B001','历史书')

insert into Books values('三国演义','施耐庵',300,'B001')
insert into Books values('海洋世界','李白',100,'A001')
insert into Books values('水浒传','范仲淹',500,'B001')

select a.*,b.BookName,b.Author,b.Price from BookType a inner join Books b on a.TypeNo=b.TypeNo

insert into stuInfo values('张三',18,'男')
insert into stuInfo values('王五',18,'男')
insert into stuInfo values('老六',18,'男')
insert into stuInfo values('小妹',19,'女')
insert into stuInfo values('老四',20,'男')

delete from stuInfo where stuNo = 2
insert into Exam values(1,98.8)
insert into Exam values(3,65.6)
insert into Exam values(4,97)
insert into Exam values(5,80)
insert into Exam values(null,100)

delete from Exam

select * from Exam;

-- 自连接 （自己连接自己）等同于 交叉连接
select * from stuInfo

select s.*from stuInfo as s ,stuInfo as e 

-- 我现在只知道王五这个人  我又不知道他的年龄  我想要知道和他年龄一样的人

select s.*, e.* from (select * from stuInfo  where stuName = '王五' )as s ,stuInfo as e where s.stuAge = e.stuAge

-- 等值连接 （主外键连接）等同于内连接
select * from Exam;
select * from stuInfo;
select s.* ,e.* from stuInfo s ,Exam e where s.stuNo = e.stuNo and s.stuAge >20;
select s.* ,e.* from stuInfo s ,Exam e where s.stuNo = e.stuNo;
-- 内连接
select * from stuInfo

select s.stuNo,s.stuName ,e.score from stuInfo s inner join Exam e on s.stuNo = e.stuNo
select s.stuNo,s.stuName ,e.score from stuInfo s  join Exam e on s.stuNo = e.stuNo
select s.*,e.* from stuInfo s inner join Exam e on s.stuNo = e.stuNo where s.stuAge >0
select e.* from stuInfo s inner join Exam e on s.stuNo = e.stuNo where s.stuAge >0
select s.* from stuInfo s inner join Exam e on s.stuNo = e.stuNo where s.stuAge >0

-- 外连接

-- 左外连接  用stuInfo 去连接  Exam   outer 可以省略 最后其实我们还是通过主外键进行连接

select s.*,e.* from stuInfo s left outer join Exam e on s.stuNo = e.stuNo
select s.*,e.* from stuInfo s left outer join Exam e on s.stuNo = e.stuNo order by s.stuAge desc
select s.*,e.* from stuInfo s left outer join Exam e on s.stuNo = e.stuNo where s.stuAge >18
-- 右外连接

-- 这个是Exam为主表
select s.*,e.* from stuInfo s right outer join Exam e on e.stuNo = s.stuNo
-- 这个是stuInfo 为主表
select s.*,e.* from Exam e right outer join stuInfo s on e.stuNo = s.stuNo