【JavaWeb】数据库多表查询操作

一、笛卡尔积和内连接

现在有两张表：部门表和成员表
那如何查询出一个结果既显示成员又显示部门呢？

就需要用到笛卡尔积了，例如select * from member,department;查出来的数据就是成员表和部门表的乘积，就是将成员表里的每一条数据和部门表中的每一条数据匹配连接。

• 成员表一共有7条数据
• 部门表一共有4条数据
• 那根据笛卡尔积查询出来的结果一共4*7=28条数据。
  现在的问题来了，这样查出来的数据很冗余，不是自己想要的，我只想要指定条件下的数据，这样就用到了内连接查询。
  
  连接查询分为隐式内连接查询和显示内连接查询，这两者作用一样，只不过书写方式不同。
  ①隐式内连接：
  select * from +表A+表B+where+A表和B表的关联部分，eg：select * from menber,department where member.dept_id=department.id;
  ②显示内连接：
  select * from +表A+inner join 表B+on+A表和B表的关联部分，inner join就是内连接的意思，on相当于上面的where，是条件关键词。

二、三种外连接

外连接又分为左外连接和右外连接
左外连接： 显示左表的全部记录和右表符合连接条件的记录。
右外连接：显示右表的全部记录和左表符合连接条件的记录。

①左外连接
select * from +表A+left outer join +表B+on+A表和B表的关联部分，
left outer join：是左外连接

②右外连接
select * from +表A+right outer join +表B+on+A表和B表的关联部分，
rightouter join：是右外连接

③全外连接（mysql数据库不支持，oracle支持）
select * from +表A+full outer join +表B+on+A表和B表的关联部分，
full outer join：全外连接

三、四种连接方式图解
表A和表B，其中重合的部分是C。

①内连接
相当于C，就是把两种表重合的部分查询出来。

②左外连接
相当于A+C，左边的表加上另外一张表与之相交的部分。

③右外连接
相当于C+B，右边的表加上另外一张表与之相交的部分。

④全外连接
相当于A+B+C，查询出来两张表结合的全部数据，那么在mysql中怎么表示呢？
全外连接=左外连接+右外连接，但是这样的话，就多出来一个C部分，就需要去重啦。

语法格式是：
select * from member left outer join department on member.dept_id=department.id
union
select * from member right outer join department on member.dept_id=department.id

• union:会去掉重复的数据记录
• union all ：不会去掉重复的数据记录
• 注意第一条查询语句不用加分号。

三、关联子查询

现有需求：要求查询出年龄最小的部门成员信息。

①常规方法
先通过聚合函数查询出最小的年龄是多少，再根据查询到的最小年龄查询对应的成员信息，这样就可以了，但是出现了一个问题，就是需要访问两次数据库，并且第二次查询需要等到第一次查询结果出来后，才能执行sql。

②子查询
等于将常规方法中的两步结合成一步。
语法：A查询语句作为B查询语句的条件，那么A查询称之为子查询，B查询称之为主查询。子查询都要写在（）里面，且执行顺序先于主查询。

③all的用法
前面的两个方案中都是使用聚合函数min()来找出最小的年龄是多少。但是all语法也能达到这样的目的，语法是：
age<all(select age from member)
就是年龄小于查询出来的所有的年龄

四、in、any、some的用法

还是回到程序员和项目表的多对多的关系来。

现在有个需求：查询money大于10000的程序员信息。
分析：
第一步：先从中间表coder_project中查询出money大于10000的coder_id;
第二步：根据查询到的coder_id查询对应的coder信息。

我们学习了子查询，把这两步合并为一步

①in的使用

• 当子查询结果只有一个时，可以用=，也可以用in。
• 当子查询结果有多个时，不可以用=，只能用in。
  所以说in是包含=的。

②any的使用
虽然子查询结果有多个，如果一定要用=，怎么办，这时候就要用到any了，任意的意思，也就是=子查询查出的任意一个数据。

③some的使用
通any类似，就是=子查询查出的一些数据，some和any的作用一样。

五、as的使用

现有需求：查询money大于10000的程序员信息和对应的money。
分析：
第一步：我们从中间表coder_project中可以找到满足条件的coder_id和money，结果就是一张新的临时表，取名叫temp。
第二步：根据code_id(临时表的)找出所有对应的程序员信息（coder表的）和money（临时表的）

①思路分析：
第一步：将查询到的结果作为一张临时表temp，但是这张表本身是不存在的，我们没法直接在第二步中使用，temp表其实就是中间表coder_project中一部分满足条件的数据。
第二步：

• 我们需要对应程序员的所有信息（也就是coder.*）
• 我们需要temp中的money
• 从temp表、coder表中查询
• coder表中的id和temp表中的coder_id相同，也是内连接相等的部分。

②as定义临时表
利用查询的思路，将第一步的结果作为子查询，然后给它起一个别名，也就是temp，这样就能直接使用了。