您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > 是否可以直接说出下面查询的结果

是否可以直接说出下面查询的结果

发布时间:2019-11-20 13:33编辑:威尼斯官方网站登录浏览(135)

    制造两张表并插入一些数量

    create table class(
      class_id int,
      class_name varchar(20),
      class_grade char(1)
    );
    
    insert into class values (1,'语文','A');
    insert into class values (2,'数学','B');
    insert into class values (3,'英语','C');
    
    create table score(
      class_id int,
      stu_id varchar(20),
      Score int
    );
    
    insert into score values (1,'A001',91);
    insert into score values (2,'A001',95);
    insert into score values (1,'A002',82);
    insert into score values (2,'A002',87);
    insert into score values (3,'B003',65);
    

     

    查阅表中的数额

    mysql> select * from class;
    +----------+------------+-------------+
    | class_id | class_name | class_grade |
    +----------+------------+-------------+
    |        1 | 语文       | A           |
    |        2 | 数学       | B           |
    |        3 | 英语       | C           |
    +----------+------------+-------------+
    3 rows in set (0.00 sec)
    
    mysql> select * from score;
    +----------+--------+-------+
    | class_id | stu_id | Score |
    +----------+--------+-------+
    |        1 | A001   |    91 |
    |        2 | A001   |    95 |
    |        1 | A002   |    82 |
    |        2 | A002   |    87 |
    |        3 | B003   |    65 |
    +----------+--------+-------+
    5 rows in set (0.00 sec)
    
    mysql>
    

     

    相比上边几组查询结果
    --假设你对left join丰富熟习的话,先不用看结果,是还是不是足以一贯揭发上面查询的结果

    mysql> select * from class A left join score B on A.class_id=B.class_id;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |        1 | A001   |    91 |
    |        2 | 数学       | B           |        2 | A001   |    95 |
    |        1 | 语文       | A           |        1 | A002   |    82 |
    |        2 | 数学       | B           |        2 | A002   |    87 |
    |        3 | 英语       | C           |        3 | B003   |    65 |
    +----------+------------+-------------+----------+--------+-------+
    5 rows in set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id and 1=1;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |        1 | A001   |    91 |
    |        2 | 数学       | B           |        2 | A001   |    95 |
    |        1 | 语文       | A           |        1 | A002   |    82 |
    |        2 | 数学       | B           |        2 | A002   |    87 |
    |        3 | 英语       | C           |        3 | B003   |    65 |
    +----------+------------+-------------+----------+--------+-------+
    5 rows in set (0.01 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id and 1=0;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |     NULL | NULL   |  NULL |
    |        2 | 数学       | B           |     NULL | NULL   |  NULL |
    |        3 | 英语       | C           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql> select * from class A left join score B on 1=0;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |     NULL | NULL   |  NULL |
    |        2 | 数学       | B           |     NULL | NULL   |  NULL |
    |        3 | 英语       | C           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql> left join的最重要特点是:不管on后面是什么条件,都会返回左表中的所有行!
    

     

    mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='语文';
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |        1 | A001   |    91 |
    |        1 | 语文       | A           |        1 | A002   |    82 |
    |        2 | 数学       | B           |     NULL | NULL   |  NULL |
    |        3 | 英语       | C           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    4 rows in set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='数学';
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        2 | 数学       | B           |        2 | A001   |    95 |
    |        2 | 数学       | B           |        2 | A002   |    87 |
    |        1 | 语文       | A           |     NULL | NULL   |  NULL |
    |        3 | 英语       | C           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    4 rows in set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='英语';
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        3 | 英语       | C           |        3 | B003   |    65 |
    |        1 | 语文       | A           |     NULL | NULL   |  NULL |
    |        2 | 数学       | B           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    3 rows in set (0.01 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='体育';
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |     NULL | NULL   |  NULL |
    |        2 | 数学       | B           |     NULL | NULL   |  NULL |
    |        3 | 英语       | C           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql>
    

    只要on后边的尺码是左表中的列(and leftTable.colName='***'),左表中满意条件的行和右表中的行开展相称(遵照on leftTable.id=rightTable.id);左表中不满足条件的行,直接出口,其对应的右表中的列都以null。

     

    mysql> select * from class A left join score B on A.class_id=B.class_id and B.Score=90;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |     NULL | NULL   |  NULL |
    |        2 | 数学       | B           |     NULL | NULL   |  NULL |
    |        3 | 英语       | C           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    3 rows in set (0.01 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id and B.Score=65;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        3 | 英语       | C           |        3 | B003   |    65 |
    |        1 | 语文       | A           |     NULL | NULL   |  NULL |
    |        2 | 数学       | B           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    3 rows in set (0.01 sec)
    
    mysql>
    

    借使on前边的条件是右表中的列(and rightTable.colName='***'),首先会基于(and rightTable.colName='***')过滤掉右表中不知足条件的行;然后,左表中的行依据(on leftTable.id=rightTable.id)和右表中知足条件的行开展相配。

     

    mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='语文' and B.Score=90;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |     NULL | NULL   |  NULL |
    |        2 | 数学       | B           |     NULL | NULL   |  NULL |
    |        3 | 英语       | C           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='语文' and B.Score=91;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |        1 | A001   |    91 |
    |        2 | 数学       | B           |     NULL | NULL   |  NULL |
    |        3 | 英语       | C           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    3 rows in set (0.01 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='体育' and B.Score=90;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |     NULL | NULL   |  NULL |
    |        2 | 数学       | B           |     NULL | NULL   |  NULL |
    |        3 | 英语       | C           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id and A.class_name='体育' and B.Score=82;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |     NULL | NULL   |  NULL |
    |        2 | 数学       | B           |     NULL | NULL   |  NULL |
    |        3 | 英语       | C           |     NULL | NULL   |  NULL |
    +----------+------------+-------------+----------+--------+-------+
    3 rows in set (0.00 sec)
    
    mysql>
    

    /**********************过滤条件在on中时**********************/
    总计一下,假若 left join on leftTable.id=rightTable.id 后还应该有其余规格:
    (1)and leftTable.colName='***',过滤左表,可是左表不满意条件的行直接出口,并将右表对应部分置为null
    (2)and rightTable.colName='***',过滤右表,对左表未有影响
    (3)and leftTable.colName='***' and rightTable.colName='***',正是上边(1)和(2)一同发挥作用

    威尼斯官方网站,不管on前边有怎么样原则,left join都要再次回到左表中的全体行!

     

    mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='语文';
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |        1 | A001   |    91 |
    |        1 | 语文       | A           |        1 | A002   |    82 |
    +----------+------------+-------------+----------+--------+-------+
    2 rows in set (0.01 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='数学';
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        2 | 数学       | B           |        2 | A001   |    95 |
    |        2 | 数学       | B           |        2 | A002   |    87 |
    +----------+------------+-------------+----------+--------+-------+
    2 rows in set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='英语';
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        3 | 英语       | C           |        3 | B003   |    65 |
    +----------+------------+-------------+----------+--------+-------+
    1 row in set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='体育';
    Empty set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id where B.Score=90;
    Empty set (0.01 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id where B.Score=91;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |        1 | A001   |    91 |
    +----------+------------+-------------+----------+--------+-------+
    1 row in set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='语文' and B.Score=90;
    Empty set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='语文' and B.Score=91;
    +----------+------------+-------------+----------+--------+-------+
    | class_id | class_name | class_grade | class_id | stu_id | Score |
    +----------+------------+-------------+----------+--------+-------+
    |        1 | 语文       | A           |        1 | A001   |    91 |
    +----------+------------+-------------+----------+--------+-------+
    1 row in set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='体育' and B.Score=90;
    Empty set (0.00 sec)
    
    mysql> select * from class A left join score B on A.class_id=B.class_id where A.class_name='体育' and B.Score=91;
    Empty set (0.00 sec)
    
    mysql> 
    

    /**********************过滤条件在where中时**********************/

    过滤条件写在where中时,先依照where条件对表举行过滤,然后再试行left join

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:是否可以直接说出下面查询的结果

    关键词:

上一篇:威尼斯官方网站:应尽量制止全表扫描

下一篇:没有了