数据库基础

1. 数据库简介

之前写的程序变量中的数据都是保存在运行内存中的，因此无法对数据进行持久化存储，数据库是计算机中用来批量持久存储数据的方式，可以认为数据库就是一个电子化的文件柜，内部存储和管理电子数据。

市面上主流的数据库产品非常多，嵌入式中主要使用的数据库产品为SQLite，SQLite是一个轻量级的数据库，本体大小只有几兆，特别适合嵌入式系统。“麻雀虽小，五脏俱全”，SQLite基本上具备了其它数据所有的功能。

IBM公司在70年代研发了SQL语言，作为数据库编程的通用语言，在数据库领域，基本上都采用SQL语言进行操作，例如：增删改查等。

2. 开发环境

本次授课使用的开发环境为SQLiteSpy

，免安装，内部集成了SQLite数据库。

此软件的布局如下：

默认打开SQLiteSpy内部是不包含任何数据的，因此提供了一份儿数据以便于学习查询。SQLite的文件格式主要是：.db或.db3

学习数据为

，使用SQLiteSpy打开，就可以看到内部的数据了。打开方式如下：

打开之后，可以看到此文件中有四个数据表：

展开之后，可以看到每个表格的每一个列对应的参数：

3. 数据表内容

本次学习主要使用emp表，双击此表的名称，可以查看内容。

这个表是一个雇员表，记录某公司中14个员工的数据。

列也被称为字段，上表中每个字段的信息如下。

4. 简单查询

双击表名可以查询所有内容，实际上是因为SQLiteSpy软件帮我们执行了下面的语句：

SELECT * FROM 表名;

SELECT表示查询，*表示所有内容，FROM表示数据来源，表名实际替换具体的表名称，SQL语句中关键字不区分大小写，但是为了可读性，本次课程中关键字使用大写，变量使用小写，字符串区分大小写。例如：

-- 查询所有雇员表的内容SELECT * FROM emp;

为了讲课和编写方便，建议在notepad++中写SQL语句，直接粘贴到SQLiteSpy中。

上面的查询只能查询一个表中所有的内容，实际在开发中可能只需要查询一部分列，可以通过下面的语法格式：

SQL语句以分号作为一句的结束，中间可以随意换行。

上面的格式中，[]表示可选内容

【例子】查询所有雇员的编号（empno）、姓名（ename）、职位（job）、基本工资（sal）

SELECT empno,ename,job,sal FROM emp;

【练习】查询每个雇员的编号、姓名和基本年薪（月薪*12）

SELECT empno,ename,sal*12 FROM emp;

也可以给查询之后的列单独起别名，例如：

SELECT empno 编号,ename 名字,sal*12 年薪 FROM emp;

在实际开发中要减少中文的使用，后续如果使用别名统一为英文。

【例子】查询所有雇员的职位有哪些。

如果按照之前的方法，可以写出下面的语句：

SELECT job FROM emp;

这种情况可以使用DISTINCT关键字，消除重复项。

SELECT DISTINCT job FROM emp;

可以看到此公司只有五种岗位，需要注意的是，如果对多列的查询结果进行消除重复项，只有每一列都重复的结果才会被消除。

5. 限定查询

限定查询是在简单查询的基础上限定结果的中的行。

在第四节中，除了最后使用DISTINCT的例子，其它的查询结果都是14条，这是因为整个emp表中一共有14条数据。

一条数据库占用一行，也被称为一条记录。

限定查询可以只显示一部分记录，其语法格式如下。

主要通过WHERE子句来完成，支持以下运算：

● 关系运算

● 取值范围运算

● 基数范围运算

● 模糊查询

● 空判断

● 逻辑判断

5.1 关系运算

关系运算是最简单的运算符符号，常用的关系运算符有：

>、<、>=、<=、<>、!=、==

需要注意的是，<>相当于!=，即不等于。

【例子】查询工资不等于3000的雇员信息。

SELECT *FROM empWHERE sal<>3000;

【练习】

1. 查询基本工资小于1600的雇员信息。

SELECT * FROM emp WHERE sal<1600;

2. 查询姓名是SMITH的雇员信息。

-- 字符串可以使用双引号或单引号SELECT *FROM emp WHERE ename="SMITH";

3. 查询所有职位不是销售的雇员信息。

SELECT *FROM empWHERE job <> "SALESMAN";

5.2 取值范围运算

表示两个数的范围区间，这个区间是闭区间，包含两端。

【例子】查询所有在1981年雇佣的雇员信息。

SELECT * FROM emp WHERE hiredate BETWEEN '1981-01-01 00:00:00' AND '1981-12-31 23:59:59';

也可以只使用日期，不使用时间。

SELECT * FROM emp WHERE hiredate BETWEEN '1981-01-01' AND '1981-12-31';

【练习】查询工资范围在1200-1300之间的雇员姓名和职位。

SELECT ename,jobFROM empWHERE sal BETWEEN 1200 AND 1300;

注意，尽量直接写对应的数据类型。

5.3 基数范围运算

基数范围运算表示某个数据与一些待选数据是否匹配（匹配一个即可），使用关键字IN操作。

【例子】查询编号是7369、7566、7839和8899（不存在）的雇员信息。

SELECT * FROM emp WHERE empno IN(7369,7566,7839,8899);

也可以使用NOT IN取反。

SELECT * FROM emp WHERE empno NOT IN(7369,7566,7839,8899);

数据库中不存在的数据使用NULL表示，IN与NOT IN的选项中不能存在NULL。

5.4 模糊查询

模糊查询可以只输入一部分内容进行查询，主要通过关键字LIKE与两个符号配合。

● _

任意一个字符

● %

任意多个（0,1,......,n）字符

【例子】 查询命名以A开头的雇员信息。

SELECT * FROM emp WHERE ename LIKE 'A%';

【练习】

1. 查询所有姓名第二个字母是A的所有雇员信息

SELECT * FROM emp WHERE ename LIKE '_A%';

2. 查询姓名中包含A的所有雇员的信息

SELECT * FROM emp WHERE ename LIKE '%A%';

5.5 空判断

NULL表示无内容的状态。

【例子】查询不领取佣金comm的所有雇员信息。

SELECT * FROM emp WHERE comm==NULL;

这句话可以成功执行，但是执行后没有任何返回结果，因此是错误的，NULL值的判断需要使用IS关键字。

● IS NULL

判断是否为空

● NOT IS NULL或IS NOT NULL

判断是否不为空

上面的例子正确的做法是：

SELECT * FROM emp WHERE comm IS NULL;

【例子】查询领取佣金comm的所有雇员信息。

-- 注意NOT的位置SELECT * FROM emp WHERE NOT comm IS NULL;

SELECT * FROM emp WHERE comm IS NOT NULL;

5.6 逻辑判断

在SQLite中使用下面的关键字表示逻辑关系：

● AND

与，表示所有的条件都满足，结果才满足

● OR

或，表示满足任意一个条件，结果就满足

● NOT

非，将逻辑结果反转

【例子】查询所有工资高于1300的销售人员信息。

SELECT * FROM emp WHERE sal>1300 AND job='SALESMAN';

【练习】

1. 查询出工资范围不在1200~1300（闭区间）之间的员工信息。

SELECT * FROM empWHERE sal<1200 OR sal>1300;

2. 查询出10部门的经理信息。

SELECT * FROM emp WHERE deptno=10 AND job='MANAGER';

3. 查询出工资高于3000，或者职位是柜员的全部雇员信息。

SELECT * FROM emp WHERE sal>3000 OR job="CLERK";

4. 查询出所有职位不是柜员的信息

SELECT * FROM emp WHERE NOT job=='CLERK';

6. 查询排序

使用ORDER BY子句可以对查询的结果进行排序，语法格式如下。

使用ASC表示升序，使用DESC表示降序。

【例子】查询所有雇员的信息，要求按照基本工资从高到低排序。

SELECT * FROM emp ORDER BY sal DESC;

【例子】查询所有雇员的信息，要求按照基本工资从高到低排序，如果工资相同则按照雇佣日期从早到晚排序。

SELECT * FROM emp ORDER BY sal DESC,hiredate ASC;

【练习】查询所有雇员的姓名和年薪（基本工资*12），按照年薪降序排布。

SELECT ename,sal*12 annualFROM empORDER BY annual DESC;

在上面的语句中，SELECT子句早于ORDER BY执行，因此在SELECT中起的别名可以在ORDER BY子句中使用。如果提前使用别名SQLite会优化此操作，但是不建议。

7. 分页查询

对于大量的数据，有时候可能在程序中需要分为好几页展示。

分页查询使用关键字LIMIT和OFFSET完成。

LIMIT表示查询结果的数量，对应的是每页展示的数据量。

OFFSET表示丢弃掉之前的数据量。

【例子】查询前五个雇员的信息。

SELECT * FROM emp LIMIT 5;

【练习】

1. 查询公司薪水最高的三个雇员的信息。

SELECT *FROM empORDER BY sal DESCLIMIT 3;

2. 查询公司前一半雇佣（早期雇佣）的雇员信息。

SELECT * FROM emp ORDER BY hiredate ASC LIMIT 7;

【例子】查询公司薪金第4名到第8名的雇员信息。

SELECT * FROM emp LIMIT 5 OFFSET 3;

设定每页显示的数量为n，当前页码为m；

则分页查询的公示为：

SELECT * FROM 表名 LIMIT n OFFSET (m-1)*n;

【例子】每页显示6条数据，查询第二页的内容。

SELECT * FROM emp LIMIT 6 OFFSET 6;

【练习】

1. 每页显示3条数据，查询第5页内容。

SELECT *FROM emp LIMIT 3OFFSET 12;

2. 每页显示5条数据，分别查询所有页的内容。

-- 第一页SELECT * FROM emp LIMIT 5;-- 第二页SELECT * FROM emp LIMIT 5 OFFSET 5;-- 第三页SELECT * FROM emp LIMIT 5 OFFSET 10;

3. 取出emp表中9到12行的数据。

SELECT * FROM emp LIMIT 4 OFFSET 8;

8. 函数

数据库中内置了很多函数，可以实现很多数据处理功能。

8.1 字符串函数

8.1.1 大小写转换

转换大写

转换小写

【练习】查询所有雇员的姓名和职位，要求所有字母小写。

SELECT LOWER(ename),LOWER(job) FROM emp;

8.1.2 字符串长度

【练习】

1. 查询所有雇员的姓名和姓名的长度，并且按照姓名长度降序排列。

SELECT ename,LENGTH(ename) FROM emp ORDER BY LENGTH(ename) DESC;

2. 查询所有姓名长度为5的雇员信息。

SELECT ename,LENGTH(ename) len FROM emp WHERE len==5;

8.1.3 去除左右空格

8.1.4 字符串截取

函数名称：SUBSTR

输入参数：

● 参数1：要处理字符串或字符串列

● 参数2：截取的起始点，支持倒序（使用负数），不要使用0，从1开始

● 参数3：截取的长度，可以省略，省略后表示截取到最后

返回值：处理后的字符换或字符串列

-- 返回结果：HelloSELECT SUBSTR('Hello World!',1,5);

-- 返回结果：WorldSELECT SUBSTR('Hello World!',-6,5);

-- 返回结果：World!SELECT SUBSTR('Hello World!',-6);

【练习】显示每个雇员的姓名的前三个字母和职位的后三个字母。

SELECT SUBSTR(ename,1,3) shortname,SUBSTR(job,-3) shortjob FROM emp;

8.2 数字函数

8.2.1 四舍五入

函数名称：ROUND

输入参数：

● 参数1：要处理的数字或数字列

● 参数2：保留的小数位，可省略

返回值：处理后数字或数字列

8.2.2 求模

函数名称：MOD

输入参数：

● 参数1：被除数

● 参数2：除数

返回值：模，通常认为是余数

8.2.3 绝对值

8.3 日期时间函数

SQLite数据库中日期时间实际上是以字符串格式存储的，因此有时需要配合一些相关函数实现日期和时间的处理。

8.3.1 基本函数

上面三个函数可以获取当前的日期或时间，只需要传入参数'now'

8.3.2 儒略日差

此函数可以计算制定日期与儒略日（公元前4713年1月1日）的相差天数。

【例子】求出每个雇员的名称和雇佣的年数。

SELECT ename,(JULIANDAY('now')-JULIANDAY(hiredate))/365 FROM emp;

8.3.3 格式化

可以把时间和日期转换成想要的任何数据格式。

函数名称：STRFTIME

输入参数：

● 参数1：格式

● 参数2：时间或日期

返回值：处理后的时间或日期

【例子】几天是几月？

-- 基于格林尼治SELECT STRFTIME('%m','now');

-- 基于本地时间SELECT STRFTIME('%m','now','localtime');

【练习】查询所有在上半年雇佣的雇员信息。

此题可能会写成

SELECT * FROM emp WHERE STRFTIME('%m',hiredate)<7;

没有返回结果，但确实存在符合条件的数据。

8.3.4 转换函数

使用CAST函数手动转换为需要的数据类型，上一节中的练习题可以使用CAST函数转换为整数，再处理。

CAST函数需要配合AS关键字使用。

SELECT * FROM emp WHERE CAST(STRFTIME('%m',hiredate) AS INTEGER)<7;

8.4 空值函数

【例子】计算所有雇员的姓名和年薪（包括薪金和佣金）

NULL与任何数字进行数学计算后，都会把结果同化为NULL。

可以使用IFNULL函数处理空值，使其变为某个具体的数字，再参与运算。

函数名称：IFNULL

输入参数：

● 参数1：可能为NULL的列

● 参数2：如果参数1为NULL后等效替换的数值

返回值：如果参数1为NULL，返回值为参数2；如果参数1不为NULL，返回值为参数1。

回到上个例子中，可以使用IFNULL处理。

SELECT ename,(sal+IFNULL(comm,0))*12 FROM emp;

8.5 统计函数

这五个函数是使用频率最高的函数，如下所示。

● COUNT 计数

● SUM 求和

● AVG 平均值

● MAX 最大值

● MIN 最小值

【例子】统计雇员人数、一个月公司支付的总薪金、平均工资、最高和最低工资。

SELECT COUNT(*),SUM(sal),AVG(sal),MAX(sal),MIN(sal) FROM emp;

9. 多表查询

之前的查询都是在一张表中查询，实际上数据库可以同时查询多张表，主要针对FROM子句进行扩展。

【例子】统计emp表和dept表的数据量。

同时对emp表和dept表统计数据量。

56 = 14 * 4，同时查询多张会相乘。

为了验证多表的结合性，执行下面的查询操作。

SELECT * FROM emp,dept;

确实出现了56条数据，每一条数据都是两个表中原始数据的拼接。

实际上在结合的过程中，只有一条结合方式是有效的（红箭头），其它的结合方式都是无效数据（白箭头），这种生成冗余数据被称为笛卡尔积。

因此多表查询时，需要消除冗余的笛卡尔积。使emp表和dept表的部门号相等，如下所示。

SELECT * FROM emp,dept WHERE emp.deptno==dept.deptno;

上面的SQL语句通常会增加别名来简化。

SELECT * FROM emp e,dept d WHERE e.deptno==d.deptno;

【例子】查询每一个雇员的编号、姓名、职位、部门编号、部门名称、部门位置。

分析：

第一步，确定FROM子句。

emp表：empno、ename、job、deptno

dept表：dname、loc

FROM emp e,dept d

第二步，确定WHERE子句。

因为多表查询会生成笛卡尔积，为了消除笛卡尔积必须使用WHERE子句。

WHERE e.deptno=d.deptno

第三步，确定SELECT子句。

需要注意，如果是多表重名列，必须写清楚所属关系。

SELECT empno,ename,job,e.deptno,dname,loc

第四步，确定ORDER BY子句。

此题无排序。

第五步，整合代码，注意顺序。

SELECT empno,ename,job,e.deptno,dname,locFROM emp e,dept dWHERE e.deptno=d.deptno;

【例子】查询出每个雇员的编号、姓名、职位、工资、工资等级。

分析：

第一步，确定FROM子句。

emp表：empno、ename、job、sal

salgrade表：grade

FROM emp e,salgrade s

第二步，确定WHERE子句。

此题消除笛卡尔积不能通过等式判断，使用BETWEEN AND运算。

WHERE sal BETWEEN losal AND hisal

第三步，确定SELECT子句。

SELECT empno,ename,job,sal,grade

第四步，确定ORDER BY子句。

没有。

第五步，整合。

SELECT empno,ename,job,sal,gradeFROM emp e,salgrade sWHERE sal BETWEEN losal AND hisal;