初始化數(shù)據(jù)值

• 查詢數(shù)據(jù)之前,先給定如下的初始化數(shù)據(jù),便于演示查詢語句.初始化數(shù)據(jù)中,一共有兩張表
  • 一張是user用戶表,用戶表中有用戶id作為主鍵, username用戶名.
  • 一張表的order訂單表,訂單的id作為主鍵,訂單的價(jià)格price,user_id作為外鍵.

--創(chuàng)建一個(gè)名叫mytable的表
CREATE DATABASE mytable;
USE mytable;
-- 用戶表(user) 
    create table `user` (                                  
            `id` int auto_increment primary key,                
            `username` varchar(50)  -- 用戶姓名                                               
        );
-- 訂單表(orders)
    create table `orders` (                                                  
            `id` int  auto_increment primary key,                                  
            `price` double,                                           
            `user_id` int                                       
        );
-- 給訂單表添加外鍵約束
    alter table orders add foreign key(user_id) references user(id); 

-- 向user表中添加數(shù)據(jù)
    insert into user values(3,'張三');
    insert into user values(4,'李四');
    insert into user values(5,'王五');
    insert into user values(6,'趙六');

-- 向orders 表中插入數(shù)據(jù)
    insert into orders values(1,1314,3);
    insert into orders values(2,1314,3);
    insert into orders values(3,15,4);
    insert into orders values(4,315,5);
    insert into orders values(5,1014,null);

• orders表中數(shù)據(jù)如下圖:

  
  

• user表中數(shù)據(jù)如下圖:

  
  

多表查詢之內(nèi)連接

• 多表查詢是在笛卡爾積的基礎(chǔ)之上的

笛卡爾積: 多張表的無條件聯(lián)合查詢,無任何的意義
如: 查詢a表和b表的所有字段,格式如下:
select a.*,b.* from a,b;

• 內(nèi)連接格式1:顯示的內(nèi)連接
  select a.*,b.* from a [inner] join b on ab表的連接條件;
  連接條件:表與表之間的連接關(guān)系,例如orders表中的user_id外鍵關(guān)聯(lián)于user表中的id.

• 內(nèi)連接格式2:隱式的內(nèi)連接
  select a.*,b.* from a,b where ab表的連接條件
  格式2使用起來容易

  注意:隱式內(nèi)連接的關(guān)鍵詞為where ,顯示內(nèi)連接的關(guān)鍵詞為join和on

• 練習(xí):
  查詢用戶的訂單,沒有訂單的用戶不顯示
  隱式的內(nèi)連接查詢:
  select user.*,orders.* from user,orders where user.id = orders.user_id;
  顯示的內(nèi)連接查詢
  select user.*,orders.* from user join orders on user.id = orders.user_id;
  兩種方式的查詢結(jié)果如下圖:
  

多表查詢之外連接

• 左外連接:
  select a.*,b.* from a left [outer] join b on ab表的連接條件;
  含義: 先展示join左邊a表的所有數(shù)據(jù),根據(jù)條件關(guān)聯(lián)查詢join右邊的b表,符合就展示出來,不符合條件的,以NULL值顯示
  例子:查詢所有用戶的訂單詳情
  user表在左邊,先顯示user的信息,再顯示訂單的信息
  select user.*,orders.* from user left join orders on user.id=orders.user_id;
  

  結(jié)果如上圖所示,符合條件的顯示出來了.趙六不符合條件,因此顯示為null.

• 右外連接:
  select a.*,b.* from a right [outer] join b on ab表的連接條件;
  含義:展示b表的所有數(shù)據(jù),并且把a(bǔ)表和b表滿足條件的數(shù)據(jù)展示出來

  例子:查詢所有訂單的用戶詳情
  右外連接:orders 在右
  select orders.*,user.* from user right join orders on user.id=orders.user_id;
  

  左外連接語句:
  SELECT orders.*,user.* FROM orders LEFT JOIN USER ON user.id=orders.user_id;
  結(jié)果與右外連接系統(tǒng)

多表查詢之子查詢

• 子查詢的含義是:一個(gè)查詢依賴另外一個(gè)查詢

1. 案例一:
   查詢用戶為張三的訂單
   第一步:在user表中獲取張三的id

   select id from user where username='張三';
   第二步:在訂單表中,查詢張三的訂單
   select * from orders where user_id = ? ;
   ?代表第一步的結(jié)果
   兩步合為一步:
   SELECT * FROM orders WHERE user_id = (SELECT id FROM USER WHERE username = '張三');
   查詢的結(jié)果如下圖:

2. 案例二:
   查詢出訂單的價(jià)格大于300的所有用戶信息耙蔑。
   第一步,先查詢出訂單價(jià)格大于300的用戶id
   select user_id from orders where price>300;
   查詢出的結(jié)果為3,3,5,null
   第二步:根據(jù)查詢的用戶id獲取用戶信息
   select * from user where id in(3,3,5,null);
   合二為一:
   select * from user where id in(select user_id from orders where price>300);
   

   執(zhí)行結(jié)果如上圖

3. 案例三:
   查詢訂單價(jià)格大于300的訂單信息以及相關(guān)用戶的信息.

   • 內(nèi)連接的查詢方法
     select orders.*,user.* from orders,user where orders.price>300;
     查詢結(jié)果如圖:
     

   出現(xiàn)的問題是兩張表出現(xiàn)笛卡爾積了
   原因是沒有寫user表和orders表的連接條件,即沒有寫外鍵orders.user_id=user.id
   改進(jìn)如下:
   select orders.*,user.* from orders,user where orders.user_id=user.id and orders.price>300;
   

   查詢結(jié)果如圖,把orders表和user表的相關(guān)信息都展示出來了,此方法為內(nèi)連接查詢.

   • 子查詢的查詢方法
     第一步,先把訂單價(jià)格大于300的訂單信息查詢出來
     select * from orders where price>300;
     

   查詢結(jié)果如上圖,可以看到查詢結(jié)果的表結(jié)構(gòu),與orders表結(jié)構(gòu)是一樣的,可以把當(dāng)前查詢出結(jié)果的表作為一個(gè)臨時(shí)表,把這個(gè)表與user表進(jìn)行內(nèi)連接查詢.
   select user.*,temp.* from user,(select * from orders where price>300) as temp where user.id = temp.user_id;
   查詢結(jié)果如下圖:與內(nèi)連接查詢的結(jié)果相同
   

   此方法的思路是,把第一步的查詢結(jié)果作為臨時(shí)表并起個(gè)別名為temp ,把這個(gè)temp表與user表進(jìn)內(nèi)連接,同時(shí)要注意臨時(shí)表需要用括號(hào)括起來.

   給臨時(shí)表起別名的格式如下:
   (表) [as] 表名` as可以省略

總結(jié)

1. 內(nèi)連接查詢:

   • 內(nèi)連接格式1:顯示的內(nèi)連接
     select a.*,b.* from a [inner] join b on ab表的連接條件;

   • 內(nèi)連接格式2:隱式的內(nèi)連接
     select a.*,b.* from a,b where ab表的連接條件

     注意:隱式內(nèi)連接的關(guān)鍵詞為where ,顯示內(nèi)連接的關(guān)鍵詞為join和on,先記住隱式內(nèi)連接即可

2. 外連接查詢:

   • 左外連接:select a.*,b.* from a left [outer] join b on ab表的連接條件;
   • 右外連接:select a.*,b.* from b right [outer] join a on 連接條件;

   注意:外連接的關(guān)鍵詞為join和on

3. 子查詢:

   • 思路一:把查詢的條件分解分解的,每一個(gè)查詢按照單表的查詢?nèi)プ?把上一步查詢的結(jié)果,傳遞給下一步的單表的查詢條件里面去,最終查詢出想要的結(jié)果.
   • 思路二:把第一步查詢的結(jié)果作為一張臨時(shí)表,把這個(gè)表起個(gè)別名,再與第二張表進(jìn)行內(nèi)連接查詢即可查詢出最終想要的結(jié)果.