多表查询

一、子查询

按返回结果分类:

  • 表子查询:返回的结果集是一个行的集合,N行N列(N>=1)。表子查询经常用于父查询的FROM子句中。
  • 行子查询:返回的结果集是一个列的集合,一行N列(N>=1)。行子查询可以用于福查询的FROM子句和WHERE子句中。
  • 列子查询:返回的结果集是一个行的集合,N行一列(N>=1)。
  • 标量子查询:返回的结果集是一个标量集合,一行一列,也就是一个标量值。可以指定一个标量表达式的任何地方,都可以用一个标量子查询。

 

1.1 表子查询

SELECT playerno
FROM 
    (SELECT playerno, sex
    FROM players
    WHERE playerno < 10) AS players10
WHERE sex='M';

  

1.2 行子查询

SELECT playerno
FROM players
WHERE (sex, town) = 
    (SELECT sex,
         town
    FROM players
    WHERE playerno = 100);

  

1.3 列子查询

SELECT playerno, name, town
FROM players
WHERE playerno IN 
    (SELECT playerno
    FROM players
    WHERE sex = 'F');

  

1.4 标题子查询

SELECT playerno
FROM players
WHERE year(birth_date) = 
    (SELECT year(birth_date)
    FROM players
    WHERE playerno = 27)
        AND playerno <> 27; 

  

 

二、join

1.1 inner join

SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id;
SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id;

  

1.2 left join

SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;

  

 

posted @ 2018-09-27 20:43 waterystone 阅读(...) 评论(...) 编辑 收藏