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);

这里不难看出  =anyin的作用相同。
©️2020 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页