mysql多表查詢

標籤：mysql

多表查詢：

        實現多表查詢是要實現表串連：

                                     交叉串連：（笛卡爾乘積) 

              例子：select * from student,courses;

                                      自然串連：（內串連）

自然串連例子：   

      select * from students,course where student,courses where student.cid1=courses.cid；

      

表的別名：

            select   s.name,c.cname from student as s,course as c where s.cid1=c.cid

            

            

                                        外串連：（左外串連、右外串連）

                                        

                                          左外串連：。。。。。left join。。。。。on。。。。

      左外串連例子：select s.name,c.cname from student as s left join courses as c on s.cid1=c.cid;查看student表中學習課程的人，如果有沒有選學課程的人顯示null

                                          右外串連：。。。。。right join。。。。on。。。。

                                          

    右外串連例子：select s.name,c.cname from student as s right join courses as c on s.cid1=c.cid; 查看courses表中課程被某人學習的顯示所學的人，沒有的就顯示null。

    

注意：在使用外串連的時候在指定條件，不再使用where而是使用on來指定條件。

                                    

                                    

                                    自串連：

       自串連的例子：select s.name as stu,c.name as teacher from students as s,student as c where s.sid=c.tid;

      

      

      

      

 子查詢：

      例子：尋找所有同學中年齡大於平均年齡的同學：

      

           select name from student where age > (select avg(age) from students);  在子查詢中子查詢只能是單值。

                

             （2）在IN中使用子查詢：

  例子：顯示老師年齡和學生年齡一樣的同學：

  

  select name from student where age in(select age from tutors);

  

 

 (3)以查詢結果為表重新查詢：

 

 

  顯示查詢出的表中年齡大於等於20的人：

  

    例子：select name，age from （select name，age from student）as t where t.age>=20;

    

    

    

把兩個查詢的結果集合起來：

      （select name,age from student） union (select tname,age from tutors) ;

      

   聯集查詢：union

 

 練習：挑選出courses表中沒有被students中的cid2學習的課程的課程名稱：

 

 mysql> select CID,Cname from courses where CID  not in( select distinct CID2 from students where CID2 is not null);

       挑選出沒有教授任何課程的老師：

 mysql>select TID,Tname from tutors where TID NOT IN(SELECT Tid from courses);

 

    挑出students表中CID1有兩個或兩個以上 同學學習了的同一門課程的課程名稱：

msyql> select CID,Cname from courses where CID IN(select CID1 from students group by CID1 HAVING COUNT(CID1)>=2);

     顯示每一位老師及其所教授的課程；沒有老師教授的課程的保持為null：

     

  mysql> select  b.Tname, a.Cname from tutors as b left join courses as a on a.TID=b.TID;

                     

      顯示每一位老師及其所教授的課程，沒有老師教授的課程將其老師顯示為空白：

   mysql>  select  b.Tname, a.Cname from tutors as b right join courses as a on a.TID=b.TID;

   

   顯示每位同學CID1課程的課程名及其講授了相關課程的老師的名稱：

        

       mysql>  select name,Cname,Tname from students,courses,tutors where students.CID1=courses.CID AND courses.TID=tutors.TID;  

       

 

視圖：

     視圖就是儲存下來的select語句；

          基於基表的查詢結果。

          

    建立視圖：create view

                 協助：help create view

  建立視圖例子：create view sct as (select name,Cname,Tname from students,courses,tutors where students.CID1=courses.CID AND courses.TID=tutors.TID);

                        show tables 可以看到視圖也被當做表展現出來了。

                        

        注意：一般情況下是不允許往視圖中插入資料的。

        

       show table status\G 查看錶的狀態可以區分出視圖和基表的區別。

       刪除視圖：drop view view_name 

                      刪除視圖的協助命令：help drop view

                      

                      

                      查看視圖是怎麼建立出來的命令

                          show  create view view_name

                          show create view   view_name\G               

                      物化視圖：就是因為我們建立的視圖是一次select查詢結果，所以當select語句查詢的結果非常大的時候，在對視圖進行二次查詢，就會造成伺服器緩衝消耗非常的大。所以我們可以把這樣的視圖給儲存下來這樣來減少緩衝的消耗。這樣的好處是把查詢的結果直接儲存下來下次不用select語句了。壞處在於當基表更新了怎麼辦。  因此對於那些不是經常更新且資料量很大的表建議使用物化視圖，對於那些更新比較快的不建議使用物化視圖。 mysql不支援物化視圖！

    

    

    注意：查看錶示怎麼建立的（也就是建立的語句）命令： show create table tb_name 

本文出自 “wangconggao” 部落格，請務必保留此出處http://wangconggao.blog.51cto.com/3253868/1579684

mysql多表查詢