sqllite select语句

SELECT column1, column2  FROM table1, table2

    WHERE [ conditions ]

    GROUP BY column1, column2

    HAVING [ conditions ]

    ORDER BY column1, column2

(1)交叉连接 - CROSS JOIN

FROM table1 CROSS JOIN table2

(2)内连接 - INNER JOIN

table1 [INNER] JOIN table2 ON conditional_expression

--为了避免冗余，并保持较短的措辞，可以使用 USING 表达式声明内连接（INNER JOIN）条件。这个表达式指定一个或多个列的列表：

FROM table1 JOIN table2 USING ( column1 ,... ) 

--自然连接（NATURAL JOIN）类似于 JOIN...USING，只是它会自动测试存在两个表中的每一列的值之间相等值：

FROM table1 NATURAL JOIN table2.

       FROM COMPANY INNER JOIN DEPARTMENT  ON COMPANY.ID = DEPARTMENT.EMP_ID;

(3)外连接 - OUTER JOIN

--外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。

FROM table1 LEFT OUTER JOIN table2 ON conditional_expression


FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) 


FROM COMPANY LEFT OUTER JOIN DEPARTMENT  ON COMPANY.ID = DEPARTMENT.EMP_ID;

(4)where语句

WHERE type = 'table' AND tbl_name = 'COMPANY';

WHERE AGE >= 25 AND SALARY >= 65000;

WHERE AGE IS NOT NULL;

WHERE NAME LIKE 'Ki%';

WHERE NAME GLOB 'Ki*';

WHERE AGE IN ( 25, 27 );

WHERE AGE NOT IN ( 25, 27 );

WHERE AGE BETWEEN 25 AND 27;

        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

WHERE SALARY LIKE '200%' --查找以 200 开头的任意值

WHERE SALARY LIKE '%200%' --查找任意位置包含 200 的任意值

WHERE SALARY LIKE '_00%' --查找第二位和第三位为 00 的任意值

WHERE SALARY LIKE '2_%_%' --查找以 2 开头，且长度至少为 3 个字符的任意值

WHERE SALARY LIKE '%2' --查找以 2 结尾的任意值

WHERE SALARY LIKE '_2%3' --查找第二位为 2，且以 3 结尾的任意值

WHERE SALARY LIKE '2___3' --查找长度为 5 位数，且以 2 开头以 3 结尾的任意值


(5)其他

UNION

UNION ALL

GROUP BY NAME ORDER BY NAME;

GROUP BY NAME ORDER BY NAME DESC;

HAVING count(name) < 2;

ORDER BY SALARY ASC;