欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Join语句

程序员文章站 2024-03-03 16:48:52
...

      

       一、Join语句几种方式

 

       1.内连接(INNER)

 

SELECT a.username,a.over,b.over
FROM user1 a
INNER JOIN user2 b
ON a.username = b.username;

 

 

          查询结果:a、b两表的公共数据

         

        2.左外连接(LEFT OUTER)

 

SELECT a.username,a.over,b.over
FROM user1 a
LEFT JOIN user2 b
ON a.username = b.username
WHERE b.username IS NOT NULL;

 

 

             查询结果:a表的所有数据 

 

         3.右外连接(RIGHT OUTER )

 

SELECT b.username,b.over,a.over
FROM user1 a
RIGHT JOIN user2 b
ON a.username = b.username
WHERE a.username IS NULL;

 

 

 

              查询结果:b表的所有数据 - ab两个表的公共数据

 

         4.全外连接(FULL OUTER)

 

SELECT a.username,a.over,b.over
FROM user1 a
FULL JOIN user2 b
ON a.username = b.username
WHERE a.username IS NULL OR b.username IS NULL;

 

  

                MySQL中不支持FULL OUTER JOIN 连接,故上面查询会出现错误

    

                   

SELECT a.username,a.over,b.over
FROM user1 a
LEFT JOIN user2 b  ON a.username = b.username
UNION  ALL
SELECT b.username,b.over,a.over
FROM user1 a
RIGHT JOIN user2 b  ON a.username = b.username
WHERE a.username IS NULL OR b.username IS NULL;

  

               查询结果:a,b表的所有数据 - a,b表的公共数据

 

          5.交叉连接(CROSS)或笛卡尔连接(cartesian)或叉乘(Product)

                   

SELECT a.username,a.over,b.over
FROM user1 a
CROSS JOIN user2 b
ON a.username = b.username

                 

                    一般要避免使用此查询

 

 

               

            二、Join语句的使用

               

             1.使用join更新表

                    

UPDATE  user1
SET over = '齐天大圣'
WHERE user1.username IN(
   SELECT b.username
   FROM user1 a  
   JOIN user2 b 
   ON a.username = b.username
);

  

                  MySQL不支持UPDATE操作,故上面更新操作会出现错误

                

                      

UPDATE user1  a   JOIN (
   SELECT b.username    
   FROM user1 a  JOIN user2 b  
   ON a.username = b.username
   ) b    ON  a.username = b.username
SET  a.over='齐天大圣';

                    

                    更新结果:将over字段的数据更新为‘齐天大圣’

 

              2.使用join优化子查询

                      

SELECT a.username,a.over,
(
   SELECT over FROM user2 b
   WHERE  a.username = b.username
   ) AS  over2  
 FROM user1 a;

               

                       查询结果:对a表中的每一条数据都进行子查询,很慢

                    

                  优化后:

                          

SELECT a.username,a.over,b.over  AS over2
FROM user1 a
LEFT JOIN user2 b  ON 
a.username = b.username

                         

                         优化后和优化前结果相同则为有效优化

                         查询结果:对a表中的每一条数据都进行子查询,较快

            

                 3.使用join优化聚合子查询

                         

SELECT a.username,b.timestr,b.kills
FROM user1 a  JOIN userkills b
ON a.id = b.userid
WHERE b.kills = (
   SELECT MAX(c.kills)
   FROM userkills c   WHERE c.userid = b.userid
);

                        

                          查询结果:各个username打怪(kills)最多的日期

 

                     避免子查询:

                               

SELECT a.username,b.timestr,b.kills
FROM user1 a
JOIN userkills b ON a.id = b.userid
JOIN userkills c ON c.userid = b.userid
GROUP BY a.username,b.timestr,b.kills
HAVING b.kills = MAX(c.kills);      

 

 

 

 

 

 

 

 

相关标签: join