首页 > 数据库 > SQL Server >

SQLite入门介绍(三)

2017-04-24

SQLite入门介绍。相信经过前面的介绍,即使是以前没有接触过SQLite的朋友也已经可以熟练的用sqlite来创建表了,这就是明显的进步啊。但学习不止,数据库的知识怎么可能仅仅局限于创建表,现在让我们一起学习下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
相关文章
最新文章
热点推荐