mysql知识详解（下）—— 连接查询

mysql之连接查询和子查询

连接查询

当需要在多个表里获取数据时候，比如在获取学生分数姓名以及代课老师等等，那么我们就需要使用连接查询。从而在两个甚至多个表中取出数据

两种规范

SQL92

• 等值连接
• 非等值连接
• 外连接
• 广义笛卡尔积

SQL99

• 交叉连接
• 自然连接
• using子句连接
• on子句连接
• 全外连接或者左，右外连接

---

SQL92

• SQL92的多表连接语法比较简洁，这种语法把多个数据表都放在from之后，多个表之间以逗号隔开，连接条件放在where之后，与查询条件之间用and连接。
• 按条件，如果要求两列值相等，则就是等值连接，否则为非等值连接，如果没有连接条件，则称为广义笛卡儿积。
• 语法格式：

select column1,column2...from table1,table2...[where join_condition]

• 多表连接查询有可能出现两个或者多个数据列具有相同的列名，则需要在这些同名列之间使用表名前缀或者表别名前缀作为限制，避免系统混淆。

select s.*,teacher_name from student_table s , teacher_table t where s.java_teacher=t.teacher_id;

执行上面的语句，可以理解为一个循环嵌套。伪码如下：
//依次遍历teacher_table
for t in teacher_table{
    //遍历student_table
    for s in student_table{
        //满足条件时
        if(s.java_teacher = t.teacher_id)
          output s+t
    }
}

• 相信这下应该理解了多表连接的原理了，如果求广义笛卡尔积，则where子句后没有任何连接条件，相当于没有if语句，则会产生N*M条结果。

select s.*,teacher_name from student_table s , teacher_table t

• 非等值连接也是如此

select s.*,teacher_name from student_table s , teacher_table t where s.java_teacher>t.teacher_id;

mysql不支持SQL92的左外连接，右外连接。

• SQL92外连接

SQL92的外连接就是在连接条件的列名后面增加括号包起来的外连接符，（+或者*，不同数据库不同），当外连接符出现在左边时候为左外连接，出现在右边时则称为右外连接。

外连接就是在外连接符所在的表增加一个“万能行”，这行记录的所有数据都是null，而且该行可以与另一个表所有不满足连接条件的记录进行匹配，通过这种方法，可以将另一个表中的所有记录选出来，不管这些记录是否满足连接条件。

SQL99

• SQL99的连接查询与SQL92的连接查询原理基本相似，不同的是SQL99的连接查询的可读性更强–查询用的多个数据表显式使用 xxx join连接，而不是排在from之后，from后只需要放置一个数据表，连接条件不再放在where之后，而是提供了专门的连接条件子句
• 交叉连接
  • 就是92里面的广义笛卡尔积。

select s.*,teacher_name from student_table s cross join teacher_table t;

• 自然连接
  • 自然连接表面没有连接条件，但是自然连接是以两个表中的同名列作为连接条件的，如果两个表中没有同名列，则自然连接和交叉连接效果完全一样，因为没有连接条件。

select s.*,teacher_name from student_table s natural join teacher_table t;

• using子句连接
  • using子句可以指定一列或者多列，用于显式指定两个表中的同名列作为连接条件。
  • 假如两个表里有多个同名列，如果使用natural join ，则会把所有的同名列当成连接条件，使用using子句，就可以显示指定使用那些同名列作为连接条件。

select s.* ,teacher_name from student_table s join teacher_table t using(teacher_id);
  
  使用using子句时，一定要保证两个表里有相同列名，否则就会出错

• on子句连接
  • 最常用的连接方式
  • SQL99语法连接条件放在on子句中指定，而且每个on子句只指定一个连接条件。这就意味着：如果需要进行N表连接，则需要有N-1个join…on对。

select s.*,teacher_name from student_table s join teacher_table t on s.java_teacher = t.teacher_id;

• 左，右，全外连接：这三种外连接fenbie使用left[outer]join,right[outer] join,full[outer]join。这三种连接条件一样通过on子句来指定，既可以是等值连接条件，也可以是非等值连接条件。
  • 使用左外连接，会将左边表中所有不满足连接条件的记录全部列出；使用右外连接将会把右边表中不满足连接条件的记录全部列出。
  • 使用全外连接，会将两个表里所有不满足连接条件的记录全部列出。

子查询

子查询就是在查询语句中嵌套另一个查询，子查询支持多层嵌套。

可以这样理解子查询，在一次查询完毕后，我们仍然得到的是一张表的数据，此时，就可以用另一张表和这个查询出来的表进行连接查询；或者查询出来的是一个数据，可以用这个数据作查询条件。这就是所谓的子查询。

• 子查询出现的两个位置
  • 出现在from语句后面当成数据表，又被称为行内视图。
  • 出现在where条件后作为过滤条件的值。
• 注意：
  • 子查询需要用括号括起来
  • 把子查询作为数据表时，为了方便不会混淆，可以为该子查询起别名。
  • 把子查询作为过滤条件时，将子查询放在比较运算符的右边，可以增加查询的可读性。
  • 把子查询作为过滤条件时，单行子查询使用单行运算符，多行子查询使用多行运算符。

情况一：
select * from (select * from student_table) t where t.java_teacher > 1 ;

情况二：
select * from student_table where java_teacher > (select teacher_id from teacher_table where teacher_name = 'YeeKu');

• 如果子查询返回多个值，根据实际情况则需要使用in any all等关键字来锁定条件

select * from student_table where student_id in (select student_id from teacher_table);

• in关键字：确定前面是否在后面的集合，如果在集合里有匹配项，则选中。
• not in ：与上面刚好相反
• any关键字：任意一个条件符合，则选中
• all关键： 所有条件匹配中，则选中

select * from student_table where student_id =any(select teacher_id from teacher_table);

这里不难看出  =any和in的作用相同。