SQLite入门介绍（三）
SQLite入门介绍。相信经过前面的介绍，即使是以前没有接触过SQLite的朋友也已经可以熟练的用sqlite来创建表了，这就是明显的进步啊。但学习不止，数据库的知识怎么可能仅仅局限于创建表，现在让我们一起学习下SQLite更高级的语法应用。
JOIN
SQLite 的 Joins 子句用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段。
SQL 定义了三种主要类型的连接：
交叉连接 - CROSS JOIN
内连接 - INNER JOIN
外连接 - OUTER JOIN
既然是要连接多个数据表，那么我们就先创建两个表。
CREATE TABLE department(
id int primary key not null,
dept varchar not null
);
表中有3条数据：
id dept ---------- ---------- 1 IT Billing 2 Engineerin 3 Finance
CREATE TABLE person(
id int primary key not null,
name varchar not null,
age int not null,
dept_id int not null
);
表中有七条数据：
id name age dept_id ---------- ---------- ---------- ---------- 1 Jack 21 2 2 Mary 20 1 3 Mike 22 3 4 Amy 19 1 5 Alen 18 2 6 Ren 21 3 7 Lucy 20 1
交叉连接 - CROSS JOIN
交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 列，则结果表有 x+y 列，且有两个数据表数据相乘的数据量。由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。
下面是交叉连接（CROSS JOIN）的语法：
SELECT ... FROM table1 CROSS JOIN table2 ...
基于上面的表，我们可以写一个交叉连接（CROSS JOIN）：
select person.id, name, dept from person cross join department;
这样最终会返回21条数据。
id name dept ---------- ---------- ---------- 1 Jack IT Billing 1 Jack Engineerin 1 Jack Finance 2 Mary IT Billing 2 Mary Engineerin 2 Mary Finance 3 Mike IT Billing 3 Mike Engineerin 3 Mike Finance 4 Amy IT Billing 4 Amy Engineerin 4 Amy Finance 5 Alen IT Billing 5 Alen Engineerin 5 Alen Finance 6 Ren IT Billing 6 Ren Engineerin 6 Ren Finance 7 Lucy IT Billing 7 Lucy Engineerin 7 Lucy Finance
内连接 - INNER JOIN
内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。
内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。
下面是内连接（INNER JOIN）的语法：
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
为了避免冗余，并保持较短的措辞，可以使用 USING 表达式声明内连接（INNER JOIN）条件。这个表达式指定一个或多个列的列表：
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
select person.id, name, age, dept from person join department using(id);
将两个表id相同的数据输出。
id name age dept ---------- ---------- ---------- ---------- 1 Jack 21 IT Billing 2 Mary 20 Engineerin 3 Mike 22 Finance
自然连接（NATURAL JOIN）类似于 JOIN…USING，只是它会自动测试存在两个表中的每一列的值之间相等值：
SELECT ... FROM table1 NATURAL JOIN table2...
select person.id, name, age, dept from person natural join department;
id name age dept ---------- ---------- ---------- ---------- 1 Jack 21 IT Billing 2 Mary 20 Engineerin 3 Mike 22 Finance
基于上面的表，我们可以写一个内连接（INNER JOIN），如下所示：
select person.id, name, age, dept from person inner join department on person.dept_id = department.id;
id name age dept ---------- ---------- ---------- ---------- 1 Jack 21 Engineerin 2 Mary 20 IT Billing 3 Mike 22 Finance 4 Amy 19 IT Billing 5 Alen 18 Engineerin 6 Ren 21 Finance 7 Lucy 20 IT Billing
外连接 - OUTER JOIN
外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。
外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。
下面是左外连接（LEFT OUTER JOIN）的语法：
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
为了避免冗余，并保持较短的措辞，可以使用 USING 表达式声明内连接（INNER JOIN）条件。这个表达式指定一个或多个列的列表：
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
select * from person left outer join department using(id);
id name age dept_id dept ---------- ---------- ---------- ---------- ---------- 1 Jack 21 2 IT Billing 2 Mary 20 1 Engineerin 3 Mike 22 3 Finance 4 Amy 19 1 5 Alen 18 2 6 Ren 21 3 7 Lucy 20 1
自然连接（NATURAL JOIN）类似于 JOIN…USING，只是它会自动测试存在两个表中的每一列的值之间相等值：
SELECT ... FROM table1 NATURAL OUTER JOIN table2...
select * from person left natural outer join department;
id name age dept_id dept ---------- ---------- ---------- ---------- ---------- 1 Jack 21 2 IT Billing 2 Mary 20 1 Engineerin 3 Mike 22 3 Finance 4 Amy 19 1 5 Alen 18 2 6 Ren 21 3 7 Lucy 20 1
基于上面的表，我们可以写一个外连接（OUTER JOIN），如下所示：
insert into person values(8, 'John', 17, 0); //先添加一条新数据 select person.id, name, age, dept from person left outer join department on person.dept_id = department.id;
id name age dept ---------- ---------- ---------- ---------- 1 Jack 21 Engineerin 2 Mary 20 IT Billing 3 Mike 22 Finance 4 Amy 19 IT Billing 5 Alen 18 Engineerin 6 Ren 21 Finance 7 Lucy 20 IT Billing 8 John 17
Union
SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。
为了使用 UNION，每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度。
UNION 的基本语法如下：
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
这里给定的条件根据需要可以是任何表达式。
现在让我们用上面建立的两个表来举例：
sqlite> select person.id, name, age, dept from person inner join department on person.dept_id = department.id union select person.id, name, age, dept from person left outer join department using(id);
id name age dept ---------- ---------- ---------- ---------- 1 Jack 21 Engineerin 1 Jack 21 IT Billing 2 Mary 20 Engineerin 2 Mary 20 IT Billing 3 Mike 22 Finance 4 Amy 19 4 Amy 19 IT Billing 5 Alen 18 5 Alen 18 Engineerin 6 Ren 21 6 Ren 21 Finance 7 Lucy 20 7 Lucy 20 IT Billing 8 John 17
所以，union就是将两个select的结果合并后去掉相同数据。
UNION ALL
UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行。
适用于 UNION 的规则同样适用于 UNION ALL 运算符。这里就不提了，就是将两个select的结果拼接在一起。
NULL值
SQLite 的 NULL 是用来表示一个缺失值的项。表中的一个 NULL 值是在字段中显示为空白的一个值。
带有 NULL 值的字段是一个不带有值的字段。NULL 值与零值或包含空格的字段是不同的，理解这点是非常重要的。
IS NOT NULL
select * from person where name is not null;
IS NULL
select * from person where name is null;
因为我们在person表中设置的是NOT NULL，所以是不会有返回的值。
别名
可以暂时把表或列重命名为另一个名字，这被称为别名。使用表别名是指在一个特定的 SQLite 语句中重命名表。重命名是临时的改变，在数据库中实际的表的名称不会改变。
列别名用来为某个特定的 SQLite 语句重命名表中的列。
表 别名的基本语法如下：
SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];
列 别名的基本语法如下：
SELECT column_name AS alias_name FROM table_name WHERE [condition];
表别名 的用法：
select p.id, p.name, p.age, d.dept from person as p, department as d where p.dept_id = d.id;
id name age dept ---------- ---------- ---------- ---------- 1 Jack 21 Engineerin 2 Mary 20 IT Billing 3 Mike 22 Finance 4 Amy 19 IT Billing 5 Alen 18 Engineerin 6 Ren 21 Finance 7 Lucy 20 IT Billing
列别名 的实例：
select p.id as PERSON_ID, p.name as PERSON_NAME, p.age as PERSON_AGE, d.dept as PERSON_DEPT from person as p, department as d where p.dept_id = d.id;
PERSON_ID PERSON_NAME PERSON_AGE PERSON_DEPT ---------- ----------- ---------- ----------- 1 Jack 21 Engineerin 2 Mary 20 IT Billing 3 Mike 22 Finance 4 Amy 19 IT Billing 5 Alen 18 Engineerin 6 Ren 21 Finance 7 Lucy 20 IT Billing
上一篇： .NET中那些所谓的新语法之二：匿名类、匿名方法与扩展方法
下一篇： SQL经典教程-第一篇：基础