MySQL 多表查詢

標籤：null   語句   實現   name   ble   內串連   tab   條件運算式   fda   

MySQL——多表查詢

串連查詢：
  交叉串連：
      結果：返歸第一個表中複合查詢條件的資料行數乘以第二個表中複合查詢條件的行數
      文法格式：select 查詢內容 from 表1 cross join 表2；
      mysql> select * from boy;
    +------+--------+
    | hid  | bname  |
    +------+--------+
    | A    | apple  |
    | B    | banana |
    | C    | pear   |
    +------+--------+
    3 rows in set (0.00 sec)

    mysql> select * from girl;
    +------+-----------+
    | hid  | gname     |
    +------+-----------+
    | B    | lurry     |
    | C    | aliea     |
    | D    | 死宅女    |
    +------+-----------+
    3 rows in set (0.00 sec)

    mysql> select * from boy cross join girl;
    +------+--------+------+-----------+
    | hid  | bname  | hid  | gname     |
    +------+--------+------+-----------+
    | A    | apple  | B    | lurry     |
    | B    | banana | B    | lurry     |
    | C    | pear   | B    | lurry     |
    | A    | apple  | C    | aliea     |
    | B    | banana | C    | aliea     |
    | C    | pear   | C    | aliea     |
    | A    | apple  | D    | 死宅女    |
    | B    | banana | D    | 死宅女    |
    | C    | pear   | D    | 死宅女    |
    +------+--------+------+-----------+
    9 rows in set (0.00 sec)
    交叉串連的結果是兩個表中所有資料的組合
    
    
    內串連：又稱為簡單串連或自然串連
            在內串連查詢中，只有滿足條件的記錄才會出現在查詢結果中
        文法格式：select 查詢內容 from 表1 join 表2 on 表1.關係欄位 = 表2.關係欄位
        mysql> select * from con;
      +------+------+
      | id   | name |
      +------+------+
      |    1 | A    |
      |    2 | B    |
      |    3 | C    |
      |    4 | D    |
      +------+------+
      4 rows in set (0.00 sec)

      mysql> select * from com;
      +------+------+
      | id   | name |
      +------+------+
      |    1 | a    |
      |    2 | b    |
      |    5 | c    |
      |    7 | d    |
      +------+------+
      4 rows in set (0.00 sec)

      mysql> select con.name, com.name from con join com on con.id = com.id;
      +------+------+
      | name | name |
      +------+------+
      | A    | a    |
      | B    | b    |
      +------+------+
      2 rows in set (0.02 sec)
      還可以使用where語句實現同樣功能
      mysql> select con.name, com.name from con, com where con.id = com.id;
      +------+------+
      | name | name |
      +------+------+
      | A    | a    |
      | B    | b    |
      +------+------+
      2 rows in set (0.00 sec)
  
        外串連：
            結果：不僅包含符合查詢條件和串連條件的資料，而且還包含左表，右表，或兩個表中的所有資料。
            文法格式：select 查詢內容 from 表1 left|right join 表2 on 表1.關係欄位 = 表2.關係欄位 where 條件；
            左串連：返回結果為左表中的所有記錄和右表中合格記錄
            右串連：返回結果為右表中的所有記錄和左表中合格記錄
           
            左串連：如果左表中的某條記錄在右表中不存在，則為空白
            mysql> select con.name, com.name from con left join com on con.id = com.id;
      +------+------+
      | name | name |
      +------+------+
      | A    | a    |
      | B    | b    |
      | C    | NULL |
      | D    | NULL |
      +------+------+
      4 rows in set (0.01 sec)
    
         右串連：如果右表中的某條記錄在左表中不存在，則為空白
         mysql> select con.name, com.name from con right join com on con.id = com.id;
      +------+------+
      | name | name |
      +------+------+
      | A    | a    |
      | B    | b    |
      | NULL | c    |
      | NULL | d    |
      +------+------+
      4 rows in set (0.00 sec)
      
    可以通過添加限制條件，使結果更加準確
    

子查詢：
    一個查詢語句嵌套在另一個查詢語句內部
        mysql> select * from dep;
    +-----+-------+
    | did | dname |
    +-----+-------+
    |   1 | A     |
    |   2 | B     |
    |   3 | c     |
    |   5 | D     |
    +-----+-------+
    4 rows in set (0.00 sec)

    mysql> select * from emp;
    +----+-------+------+-----+
    | id | name  | age  | did |
    +----+-------+------+-----+
    |  1 | may   |   20 |   1 |
    |  2 | hfda  |   22 |   1 |
    |  3 | saef  |   20 |   2 |
    |  4 | qwedd |   20 |   4 |
    +----+-------+------+-----+
    4 rows in set (0.00 sec)

    where 型子查詢:內層sql的傳回值在where後作為條件運算式的一部分
   例句: select * from tableA where colA = (select colB from tableB where ...);
  
      mysql> select * from emp where did = (select did from dep where dname=‘A‘);
     +----+------+------+-----+
     | id | name | age  | did |
     +----+------+------+-----+
     |  1 | may  |   20 |   1 |
     |  2 | hfda |   22 |   1 |
     +----+------+------+-----+
     2 rows in set (0.00 sec)
  
   from 型子查詢:內層sql查詢結果,作為一張表,供外層的sql語句重新查詢
   例句:select * from (select * from ...) as tableName where ....
   
       mysql> select * from (select * from emp where age = 20) as emps where did = 1;
     +----+------+------+-----+
     | id | name | age  | did |
     +----+------+------+-----+
     |  1 | may  |   20 |   1 |
     +----+------+------+-----+
     1 row in set (0.00 sec)
   
    帶in關鍵字的子查詢：
        將內層查詢語句傳回值與外層查詢語句進行比較操作
        文法結構：select 查詢內容 from表1 where did [not] in (select did from 表2 where 限制條件)
        樣本：
       
    mysql> select * from dep where did in (select did from emp where age=20);
    +-----+-------+
    | did | dname |
    +-----+-------+
    |   1 | A     |
    |   2 | B     |
    +-----+-------+
    2 rows in set (0.10 sec) 
    首先將emp表中age=20的資料查出，然後根據did作為外層查詢的查詢條件
    
    mysql> select * from dep where did not in (select did from emp where age=20);
    +-----+-------+
    | did | dname |
    +-----+-------+
    |   3 | c     |
    |   5 | D     |
    +-----+-------+
    2 rows in set (0.08 sec)
    not in關鍵字與使用in關健字的查詢結果正好相反
    

  帶exists關鍵字的子查詢：
      exists關健字後可以是任意一個子查詢，這個子查詢的作用相當於測試，它不產生任何資料，只返回true或
      者false，只有當傳回值為true時，外層查詢才會執行。
      文法結構：select 查詢內容 from表1 where exists (select did from 表2 where 限制條件)      
      mysql> select * from dep where exists (select * from emp where age > 21);
    +-----+-------+
    | did | dname |
    +-----+-------+
    |   1 | A     |
    |   2 | B     |
    |   3 | c     |
    |   5 | D     |
    +-----+-------+
    4 rows in set (0.00 sec)
    由於在表emp中存在age>21的資料，則返回true，執行外層查詢
    由於exists關鍵字比in關鍵字運行效率高，所以在大資料量查詢時，推薦使用exists
    
    帶any的關鍵字子查詢：
        any關鍵字表示滿足其中的任意一個條件，它允許建立一個運算式對子查詢的傳回值列表進行比較，只要滿足內
        層子查詢的任意一個比較條件，就返回一個結果作為外層查詢條件。
        文法結構：select 查詢內容 from表1 where 欄位1 關係運算子 any(select 欄位1 from 表2)        
        mysql> select * from dep where did > any(select did from emp);
    +-----+-------+
    | did | dname |
    +-----+-------+
    |   2 | B     |
    |   3 | c     |
    |   5 | D     |
    +-----+-------+
    3 rows in set (0.03 sec)    
        先在表emp中查出所有的did，再將dep表中所有的，只要did大於已查詢出的did中任意一個，就將改資料查出
       
    帶all關鍵字的子查詢：
        all關鍵字與any關鍵字類似，但all關鍵字表示需要滿足所有子查詢返回結果
        文法結構：select 查詢內容 from表1 where 欄位1 關係運算子 all(select 欄位1 from 表2)
        mysql> select * from dep where did > all(select did from emp);
    +-----+-------+
    | did | dname |
    +-----+-------+
    |   5 | D     |
    +-----+-------+
    1 row in set (0.01 sec)
   
   
union查詢：顯示所有子查詢結果的並集，一般不再union查詢的子查詢中使用order by

     mysql> select * from boy;
    +------+--------+
    | hid  | bname  |
    +------+--------+
    | A    | apple  |
    | B    | banana |
    | C    | pear   |
    +------+--------+
    3 rows in set (0.00 sec)

    mysql> select * from girl;
    +------+-----------+
    | hid  | gname     |
    +------+-----------+
    | B    | banana    |
    | C    | aliea     |
    | D    | 死宅女    |
    +------+-----------+
    3 rows in set (0.00 sec)

    mysql> select * from boy
        -> union
        -> select * from girl;
    +------+-----------+
    | hid  | bname     |
    +------+-----------+
    | A    | apple     |
    | B    | banana    |
    | C    | pear      |
    | C    | aliea     |
    | D    | 死宅女    |
    +------+-----------+
    5 rows in set (0.00 sec) 
   

MySQL 多表查詢